package io.delphiplatform.api.util;

import com.healthmarketscience.sqlbuilder.SelectQuery;
import com.healthmarketscience.sqlbuilder.SelectQuery.JoinType;
import com.healthmarketscience.sqlbuilder.dbspec.Column;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbColumn;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbJoin;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbSchema;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbSpec;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbTable;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.stream.Stream;

import io.delphiplatform.api.v3.constant.DbSchemaConstants;
import io.delphiplatform.api.v3.constant.DecibelTableNames;

public class SelectQueryHelper {

    private final DbSpec dbSpec = new DbSpec();
    private final DbSchema dbSchema;
    private DbTable rootTable;
    private Map<String, String> tableAliases = new HashMap<>();
    private final Map<String, DbTable> dbTables = new HashMap<>();
    private final Map<String, Map<String, DbColumn>> dbColumns = new HashMap<>();
    private final List<DbJoin> dbJoins = new ArrayList<>();
    private SelectQueryTableHelper rootTableHelper;

    private SelectQueryHelper(String schema) {
        this.dbSchema = new DbSchema(this.dbSpec, schema);
    }

    public static SelectQueryHelper forDecibelSchema() {
        return forSchema(DbSchemaConstants.DECIBEL)
            .setAliases(DecibelTableNames.tableAliases);
    }

    public static SelectQueryHelper forSchema(String schema) {
        return new SelectQueryHelper(schema);
    }

    public SelectQueryHelper setAliases(Map<String, String> tableAliases) {
        if (tableAliases != null) {
            this.tableAliases = tableAliases;
        }
        return this;
    }

    public SelectQueryHelper addTableAlias(String tableName, String tableAlias) {
        this.tableAliases.put(tableName, tableAlias);
        return this;
    }

    public SelectQueryTableHelper fromTable(String tableName) {
        String tableAlias = getAliasForTable(tableName);
        return fromTable(tableName, tableAlias);
    }

    public SelectQueryTableHelper fromTable(String tableName, String tableAlias) {
        this.rootTable = getOrCreateTable(tableName, tableAlias);
        this.rootTableHelper = new SelectQueryTableHelper(this, this.rootTable);
        return chain();
    }

    public SelectQueryTableHelper chain() {
        return this.rootTableHelper;
    }

    public DbTable getJoinedTable(String tableName) {
        return dbTables.get(tableName);
    }

    public DbColumn getDbColumnForTable(String tableName, String columnName) {
        DbTable table = Optional.of(dbTables.get(tableName))
            .orElseThrow(() -> new IllegalArgumentException("Table does not processed to get columns: " + tableName));
        return getOrCreateColumn(table, columnName);
    }

    public SelectQuery buildQuery() {
        DbJoin[] joins = this.dbJoins.stream().toArray(DbJoin[]::new);
        Column[] selectColumns = rootTable.getColumns().stream().toArray(Column[]::new);
        return new SelectQuery()
            .addFromTable(rootTable)
            .addColumns(selectColumns)
            .addJoins(JoinType.LEFT_OUTER, joins);
    }

    private Map<String, DbColumn> getColumnsForTable(DbTable table) {
        return dbColumns.get(table.getAlias());
    }

    private String getAliasForTable(String tableName) {
        return Optional.ofNullable(this.tableAliases.get(tableName))
            .orElse(tableName);
    }

    private DbTable getOrCreateTable(String tableName) {
        String tableAlias = getAliasForTable(tableName);
        return getOrCreateTable(tableName, tableAlias);
    }

    private DbTable getOrCreateTable(String tableName, String tableAlias) {
        if (dbTables.containsKey(tableName)) {
            return dbTables.get(tableName);
        }
        DbTable table = new DbTable(dbSchema, tableName, tableAlias);
        dbTables.put(tableName, table);
        dbColumns.put(table.getAlias(), new HashMap<>());
        return table;
    }

    private DbColumn getOrCreateColumn(DbTable table, String columnName) {
        Map<String, DbColumn> tableColumns = getColumnsForTable(table);
        if (tableColumns.containsKey(columnName)) {
            return tableColumns.get(columnName);
        }
        DbColumn column = new DbColumn(table, columnName, null);
        tableColumns.put(columnName, column);
        return column;
    }

    private void addJoin(String fromTableName, String fromColumnName, String toTableName, String toColumnName) {
        DbTable fromTable = getOrCreateTable(fromTableName);
        DbTable toTable = getOrCreateTable(toTableName);
        DbColumn fromColumn = getOrCreateColumn(fromTable, fromColumnName);
        DbColumn toColumn = getOrCreateColumn(toTable, toColumnName);
        DbColumn[] fromColumns = new DbColumn[]{fromColumn};
        DbColumn[] toColumns = new DbColumn[]{toColumn};
        DbJoin join = new DbJoin(this.dbSpec, fromTable, toTable, fromColumns, toColumns);
        dbJoins.add(join);
    }

    public static class SelectQueryTableHelper {

        private final SelectQueryHelper sqHelper;
        private final DbTable fromTable;

        public SelectQueryTableHelper(SelectQueryHelper sqHelper, DbTable fromTable) {
            this.sqHelper = sqHelper;
            this.fromTable = fromTable;
        }

        public SelectQueryTableHelper addSelectColumns(String... columns) {
            Stream.of(columns).forEach(columnName ->
                fromTable.addColumn(
                    getOrCreateColumn(fromTable, columnName)
                )
            );
            return this;
        }

        public SelectQueryTableHelper addColumns(String... columns) {
            Stream.of(columns).forEach(columnName -> getOrCreateColumn(fromTable, columnName));
            return this;
        }

        public SelectQueryTableJoinHelper joinOn(String columnName) {
            return new SelectQueryTableJoinHelper(this, columnName);
        }

        public SelectQueryHelper done() {
            return sqHelper;
        }

        private DbColumn getOrCreateColumn(DbTable table, String columnName) {
            return sqHelper.getOrCreateColumn(table, columnName);
        }

        private SelectQueryTableHelper join(String fromTableName, String fromColumnName, String toTableName,
            String toColumnName) {
            fromTableName = Optional.ofNullable(fromTableName).orElse(this.fromTable.getName());
            this.sqHelper.addJoin(fromTableName, fromColumnName, toTableName, toColumnName);
            return this;
        }
    }

    public static class SelectQueryTableJoinHelper {

        private final SelectQueryTableHelper sqTableHelper;
        private String fromTable;
        private final String fromColumnName;

        public SelectQueryTableJoinHelper(SelectQueryTableHelper sqTableHelper, String columnName) {
            this.sqTableHelper = sqTableHelper;
            this.fromColumnName = columnName;
        }

        public SelectQueryTableJoinHelper fromTable(String tableName) {
            this.fromTable = tableName;
            return this;
        }

        public SelectQueryTableHelper toTable(String tableName, String columnName) {
            return sqTableHelper.join(fromTable, fromColumnName, tableName, columnName);
        }
    }
}