/*
 * Click nbfs://nbhost/SystemFileSystem/Templates/Licenses/license-default.txt to change this license
 * Click nbfs://nbhost/SystemFileSystem/Templates/Classes/Class.java to edit this template
 */
package com.megatim.fdxcommons.tools.database.queries.json;

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.exceptions.BadColumnTypeException;
import com.megatim.fdxcommons.tools.database.tables.appcolumns.AppColumnDefinitions;
import com.megatim.fdxcommons.tools.database.tables.appcolumns.IndexColumnDefinition;
import com.megatim.fdxcommons.tools.database.tables.appcolumns.ParentIdColumnDefinition;
import com.megatim.fdxcommons.model.pojo.JsonTableDefinition;
import java.sql.Connection;
import java.sql.JDBCType;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author ASUS
 */
public class CreateJsonTableIfNotExistQuery implements QueryMetaData<Void> {

    private final Connection connection;
    private final AppColumnDefinitions appColumnDefinitions;
    private final JsonTableDefinition tableDefinition;
    private final List<String> queries = new ArrayList<>();

    public CreateJsonTableIfNotExistQuery(Connection connection, AppColumnDefinitions appColumnDefinitions, JsonTableDefinition tableDefinition) {
        this.connection = connection;
        this.appColumnDefinitions = appColumnDefinitions;
        this.tableDefinition = tableDefinition;
    }

    @Override
    public Void execute() throws Exception {
        query(tableDefinition);

        for (String query : queries) {
            System.out.println(query);
        }

        try ( Statement stmt = connection.createStatement()) {
            for (String query : queries) {
                stmt.execute(query);
            }
        }
        return null;
    }

    private void query(JsonTableDefinition jsonTableDefinition) throws BadColumnTypeException {
        StringBuilder query = new StringBuilder("CREATE TABLE IF NOT EXISTS " + jsonTableDefinition.getTableName() + "(");
        List<AppColumnDefinition> primaryKeys = new ArrayList<>();

        for (ColumnDefinition colDef : jsonTableDefinition.getColumnDefs()) {
            query.append(colDef.getName().toLowerCase()).append(" ").append(sqlColumnType(colDef));
            if (colDef.isRequired()) {
                query.append(" NOT NULL");
            }
            query.append(", ");
        }

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

            if (appColumnDefinition.isPrimaryKey()) {
                primaryKeys.add(appColumnDefinition);
            }
        }
        //Defintion de la colonne fdx_parent_id
        if (jsonTableDefinition.getParentTableName() != null) {
            query.append(columnDefinitonString(new ParentIdColumnDefinition())).append(", ");
        }

        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(), "");
        }

        //Contrainte de clé étrangère sur fdx_parent_id
        if (jsonTableDefinition.getParentTableName() != null) {
            query.append(parentIdDefintion(jsonTableDefinition.getParentTableName(), new ParentIdColumnDefinition().name(), new IndexColumnDefinition().name()));
        }
        query.append(");");

        queries.add(query.toString());

        if (!jsonTableDefinition.getTablesDefinition().isEmpty()) {
            for (JsonTableDefinition tableDef : jsonTableDefinition.getTablesDefinition()) {
                query(tableDef);
            }
        }
    }

    private String parentIdDefintion(String parentTableName, String parentIdColumnName, String indexColumnName) {
        StringBuilder builder = new StringBuilder("");
        builder.append(", FOREIGN KEY (").append(parentIdColumnName).append(") REFERENCES ").append(parentTableName).append("(").append(indexColumnName).append(")");

        return builder.toString();
    }

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

    private JDBCType jdbcType(ColumnDefinition colDef) throws BadColumnTypeException {
        switch (TypeDonnee.fromValeur(colDef.getTypeDonnee())) {
            case NUMERIQUE:
                return colDef.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 " + colDef.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();
    }
}