package com.megatim.fdxcommons.tools.database.queries;

import com.megatim.fdxcommons.model.enumeration.TypeDonnee;
import static com.megatim.fdxcommons.model.enumeration.TypeDonnee.ALPHANUMERIQUE;
import static com.megatim.fdxcommons.model.enumeration.TypeDonnee.DATE;
import static com.megatim.fdxcommons.model.enumeration.TypeDonnee.DECIMAL;
import static com.megatim.fdxcommons.model.enumeration.TypeDonnee.NUMERIQUE;
import com.megatim.fdxcommons.model.integration.ColumnDefinition;
import com.megatim.fdxcommons.tools.database.contrat.AppColumnDefinition;
import com.megatim.fdxcommons.tools.database.contrat.QueryMetaData;
import com.megatim.fdxcommons.tools.database.contrat.TableName;
import com.megatim.fdxcommons.tools.database.exceptions.BadColumnTypeException;
import com.megatim.fdxcommons.tools.database.tables.appcolumns.AppColumnDefinitions;
import java.sql.Connection;
import java.sql.JDBCType;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import javax.naming.NamingException;

/**
 *
 * @author Gabuntu
 */
public class CreateTableIfNotExistQuery implements QueryMetaData<Void> {

    private final Connection connection;
    private final AppColumnDefinitions appColumnDefinitions;
    private final TableName tableName;
    private final List<ColumnDefinition> columnDefinitions;

    public CreateTableIfNotExistQuery(
            TableName tableName,
            List<ColumnDefinition> columnDefinitions,
            AppColumnDefinitions appColumnDefinitions,
            Connection connection
    ) {
        this.connection = connection;
        this.appColumnDefinitions = appColumnDefinitions;
        this.tableName = tableName;
        this.columnDefinitions = columnDefinitions;
    }

    @Override
    public Void execute() throws SQLException, NamingException, BadColumnTypeException {

        String query = query(tableName, columnDefinitions);

        try ( Statement stmt = connection.createStatement()) {
            stmt.execute(query);
        }

        return null;
    }

    private String query(TableName tableName, List<ColumnDefinition> columns) throws BadColumnTypeException, SQLException {

        StringBuilder query = new StringBuilder("CREATE TABLE IF NOT EXISTS " + tableName.name(connection) + "(");
        List<AppColumnDefinition> primaryKeys = new ArrayList<>();

        //Ordonner les colonnes par ordre croissant des positions, afin de les insérer dans cet ordre
        Collections.sort(columns, (ColumnDefinition c1, ColumnDefinition c2) -> Integer.valueOf(c1.getPosition()).compareTo(c2.getPosition()));

        for (ColumnDefinition columnDefinition : columns) {
            query.append(columnDefinition.getName().toLowerCase()).append(" ").append(sqlColumnType(columnDefinition)).append(", ");
        }

        for (AppColumnDefinition appColumnDefinition : appColumnDefinitions.appColumns()) {
            query.append(columnDefinitonString(appColumnDefinition)).append(", ");

            if (appColumnDefinition.isPrimaryKey()) {
                primaryKeys.add(appColumnDefinition);
            }
        }
        if (!primaryKeys.isEmpty()) {
            query.append(" PRIMARY KEY(");
            primaryKeys.stream().forEach(pk -> query.append(pk.name()).append(", "));

            //Retirer la dernière virgule
            query.replace(query.length() - 2, query.length(), "));");
        } else {
            //Retirer la dernière virgule
            query.replace(query.length() - 2, query.length(), ");");
        }

        return query.toString();
    }

    private String sqlColumnType(ColumnDefinition column) throws BadColumnTypeException {
        switch (TypeDonnee.fromValeur(column.getTypeDonnee())) {
            case ALPHANUMERIQUE:
                return jdbcType(column) + "(" + column.getTaille() + ")";
            case NUMERIQUE:
                return jdbcType(column).getName();
            case DATE:
                return jdbcType(column).getName() + " NULL";
            case DECIMAL:
                return jdbcType(column) + "(" + column.getTaille() + "," + column.getTaillePartieDecimale() + ")";
        }
        throw new BadColumnTypeException("Type de colonne incorrect " + column.getTypeDonnee());
    }

    private JDBCType jdbcType(ColumnDefinition column) throws BadColumnTypeException {
        switch (TypeDonnee.fromValeur(column.getTypeDonnee())) {
            case NUMERIQUE:
                return column.getTaille() < 5 ? JDBCType.SMALLINT : JDBCType.BIGINT;
            case DECIMAL:
                return JDBCType.DECIMAL;
            case DATE:
                return JDBCType.TIMESTAMP;
            case ALPHANUMERIQUE:
                return JDBCType.VARCHAR;
        }
        throw new BadColumnTypeException("Type de colonne incorrect " + column.getName());
    }

    private String columnDefinitonString(AppColumnDefinition column) throws BadColumnTypeException {
        StringBuilder definition = new StringBuilder();
        definition
                .append(column.name().toLowerCase())
                .append(" ")
                .append(column.type())
                .append(" ")
                .append(column.isNull() ? "NULL " : "NOT NULL ");
        if (column.defaultValue() != null) {
            definition
                    .append("DEFAULT ")
                    .append(column.defaultValue());
        }
        return definition.toString();
    }

}