/*
 * 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.tables;

import com.megatim.fdxcommons.model.dataproduction.CommonDataProduction;
import com.megatim.fdxcommons.model.enumeration.CriteriaLogicConnector;
import com.megatim.fdxcommons.model.enumeration.Operateur;
import com.megatim.fdxcommons.model.integration.json.JsonStructure;
import com.megatim.fdxcommons.model.pojo.JsonTableDefinition;
import com.megatim.fdxcommons.tools.database.contrat.JsonStructureIterator;
import com.megatim.fdxcommons.tools.database.contrat.TableName;
import com.megatim.fdxcommons.tools.database.exceptions.TypeFichierDataBadRequest;
import com.megatim.fdxcommons.tools.database.queries.CountQuery;
import com.megatim.fdxcommons.tools.database.queries.JsonCountQuery;
import com.megatim.fdxcommons.tools.database.queries.JsonSelectQuery;
import com.megatim.fdxcommons.tools.database.queries.LatestDataProductionTokenQuery;
import com.megatim.fdxcommons.tools.database.queries.MaxDataProductionIdQuery;
import com.megatim.fdxcommons.tools.database.queries.metadata.AliasedQueryCriterion;
import com.megatim.fdxcommons.tools.database.queries.metadata.JsonFdxTableStructure;
import com.megatim.fdxcommons.tools.database.queries.metadata.JsonParsedQueryCriterion;
import com.megatim.fdxcommons.tools.database.queries.metadata.JsonSelectQueryResult;
import com.megatim.fdxcommons.tools.database.queries.metadata.QueryCriterion;
import com.megatim.fdxcommons.tools.database.tables.appcolumns.DataProductionIdColumnDefinition;
import com.megatim.fdxcommons.tools.utils.JsonDataUtil;
import com.megatim.fdxcommons.tools.utils.JsonTableUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import javax.naming.NamingException;

/**
 *
 * @author ASUS
 */
public abstract class JsonFdxTable {

    protected final TableName tableName;
    protected final String codeTypeFichier;
    protected final String referentielVersion;
    protected final JsonStructure jsonStructure;
    private final List<Boolean> cachedNatureProduction = new ArrayList<>(1);

    protected JsonFdxTable(String referentielVersion, String codeTypeFichier, JsonStructure jsonStructure) {
        this.tableName = new FdxTableName(referentielVersion, codeTypeFichier);
        this.codeTypeFichier = codeTypeFichier;
        this.referentielVersion = referentielVersion;
        this.jsonStructure = jsonStructure;
    }

    public JsonFdxTableStructure jsonStructure(Connection connection) {
        return new JsonFdxTableStructure(referentielVersion, codeTypeFichier, connection);
    }

    public JsonSelectQueryResult selectAllAddedAndUpdated(QueryCriterion queryCriterion, Connection connection) throws NamingException, SQLException, Exception {
        JsonTableDefinition tableDefinition = tableDefinition(connection);
        Map<String, String> tablesNameToAlias = new HashMap<>();

        String stringTableName = tableName.name(connection);
        String rootTableAlias = tableAlias(tableDefinition, stringTableName);
        tablesNameToAlias.put(stringTableName, rootTableAlias);

        QueryCriterion finalCriterion = isReferentiel(connection) ? queryCriterion : addLatestDataProductionCriterion(queryCriterion, rootTableAlias, connection);
        JsonSelectQueryResult result = new JsonDataUtil().merge(new JsonSelectQuery(tableDefinition, finalCriterion, connection, tablesNameToAlias).execute(), jsonStructure, false);
        setToken(connection, result);

        return result;
    }

    private String tableAlias(JsonTableDefinition tableDefinition, String tableName) {
        Map<String, String> tablesNameToAlias = new JsonTableUtil(tableDefinition).tablesNameToAlias();
        return tablesNameToAlias.get(tableName);
    }

    public JsonSelectQueryResult selectResult(QueryCriterion queryCriterion, Connection connection) throws NamingException, SQLException, Exception {
        JsonTableDefinition tableDefinition = tableDefinition(connection);
        Map<String, String> tablesNameToAlias = new JsonTableUtil(tableDefinition).tablesNameToAlias();
        QueryCriterion aliasedQueryCriterion = new AliasedQueryCriterion(tableDefinition, parsedQueryCriterion(queryCriterion, connection).parsedCriterion(), tablesNameToAlias).aliasedQueryCriterion();
        QueryCriterion finalCriterion = isReferentiel(connection) ? aliasedQueryCriterion : addLatestDataProductionCriterion(aliasedQueryCriterion, tablesNameToAlias.get(tableName.name(connection)), connection);

        JsonSelectQueryResult result = new JsonDataUtil().merge(new JsonSelectQuery(tableDefinition, finalCriterion, connection, tablesNameToAlias).execute(), jsonStructure, false);
        setToken(connection, result);

//        System.out.println("---------------------------------- final rows---------------------------- lastIndexRead = " + result.lastIndexRead());
//        for (Map<String, Object> m : result.rows()) {
//            System.out.println(m);
//        }
        return result;
    }

    public JsonSelectQueryResult selectResult(QueryCriterion queryCriterion, Connection connection, int limit, int offset) throws NamingException, SQLException, Exception {
        JsonTableDefinition tableDefinition = tableDefinition(connection);
        Map<String, String> tablesNameToAlias = new JsonTableUtil(tableDefinition).tablesNameToAlias();
        QueryCriterion aliasedQueryCriterion = new AliasedQueryCriterion(tableDefinition, parsedQueryCriterion(queryCriterion, connection).parsedCriterion(), tablesNameToAlias).aliasedQueryCriterion();
        QueryCriterion finalCriterion = isReferentiel(connection) ? aliasedQueryCriterion : addLatestDataProductionCriterion(aliasedQueryCriterion, tablesNameToAlias.get(tableName.name(connection)), connection);

        JsonSelectQueryResult result = new JsonDataUtil().merge(new JsonSelectQuery(tableDefinition, finalCriterion, connection, tablesNameToAlias, limit, offset).execute(), jsonStructure, false);
        setToken(connection, result);

        return result;
    }

    public JsonSelectQueryResult selectResultWithAllColumns(QueryCriterion queryCriterion, Connection connection) throws NamingException, SQLException, Exception {
        JsonTableDefinition tableDefinition = tableDefinition(connection);
        Map<String, String> tablesNameToAlias = new JsonTableUtil(tableDefinition).tablesNameToAlias();
        QueryCriterion aliasedQueryCriterion = new AliasedQueryCriterion(tableDefinition, parsedQueryCriterion(queryCriterion, connection).parsedCriterion(), tablesNameToAlias).aliasedQueryCriterion();
        QueryCriterion finalCriterion = isReferentiel(connection) ? aliasedQueryCriterion : addLatestDataProductionCriterion(aliasedQueryCriterion, tablesNameToAlias.get(tableName.name(connection)), connection);

        JsonSelectQueryResult result = new JsonDataUtil().merge(new JsonSelectQuery(tableDefinition, finalCriterion, connection, tablesNameToAlias).execute(), jsonStructure, true);
        setToken(connection, result);

        return result;
    }

    /**
     * Ne compte que les éléments de la table racine
     *
     * @param queryCriterion
     * @param connection
     * @return
     * @throws NamingException
     * @throws SQLException
     * @throws Exception
     */
    public Long count(QueryCriterion queryCriterion, Connection connection) throws NamingException, SQLException, Exception {
        JsonTableDefinition tableDefinition = tableDefinition(connection);
        String stringTableName = tableName.name(connection);
        String rootTableAlias = tableAlias(tableDefinition, stringTableName);
        Map<String, String> tablesNameToAlias = new JsonTableUtil(tableDefinition).tablesNameToAlias();

        QueryCriterion aliasedQueryCriterion = new AliasedQueryCriterion(tableDefinition, parsedQueryCriterion(queryCriterion, connection).parsedCriterion(), tablesNameToAlias).aliasedQueryCriterion();
        QueryCriterion finalCriterion = isReferentiel(connection) ? aliasedQueryCriterion : addLatestDataProductionCriterion(aliasedQueryCriterion, rootTableAlias, connection);

        return new JsonCountQuery(tableDefinition,
                finalCriterion,
                connection,
                tablesNameToAlias
        ).execute();
    }

    /**
     *
     * @param queryCriterion
     * @param connection
     * @return
     * @throws SQLException
     * @throws NamingException
     * @throws Exception
     */
    public Long countOnAppColumn(QueryCriterion queryCriterion, Connection connection) throws SQLException, NamingException, Exception {
        JsonTableDefinition tableDefinition = tableDefinition(connection);
        String stringTableName = tableName.name(connection);
        String rootTableAlias = tableAlias(tableDefinition, stringTableName);
        Map<String, String> tablesNameToAlias = new JsonTableUtil(tableDefinition).tablesNameToAlias();
        QueryCriterion finalCriterion = isReferentiel(connection) ? queryCriterion : addLatestDataProductionCriterion(queryCriterion, rootTableAlias, connection);

        return new JsonCountQuery(
                tableDefinition,
                finalCriterion,
                connection,
                tablesNameToAlias
        ).execute();
    }

    /**
     * Ne compte que les éléments de la table racine
     *
     * @param queryCriterion
     * @param connection
     * @return
     * @throws NamingException
     * @throws SQLException
     * @throws Exception
     */
    public synchronized Long countRegardlessOfNatureProduction(QueryCriterion queryCriterion, Connection connection) throws NamingException, SQLException, Exception {
        List<String[]> results = new CountQuery(
                tableName.name(connection),
                connection,
                parsedQueryCriterion(queryCriterion, connection).parsedCriterion(),
                Arrays.asList()
        ).execute();
        return !results.isEmpty()
                ? Long.valueOf(results.get(0)[0])
                : 0;
    }

    public synchronized JsonSelectQueryResult selectResultRegardlessOfNatureProduction(QueryCriterion queryCriterion, Connection connection) throws NamingException, SQLException, Exception {
        JsonTableDefinition tableDefinition = tableDefinition(connection);
        Map<String, String> tablesNameToAlias = new JsonTableUtil(tableDefinition).tablesNameToAlias();
        QueryCriterion aliasedQueryCriterion = new AliasedQueryCriterion(tableDefinition, parsedQueryCriterion(queryCriterion, connection).parsedCriterion(), tablesNameToAlias).aliasedQueryCriterion();
        JsonSelectQueryResult result = new JsonDataUtil().merge(new JsonSelectQuery(tableDefinition, aliasedQueryCriterion, connection, tablesNameToAlias).execute(), jsonStructure, true);

        setToken(connection, result);

        return result;
    }

    public JsonFdxTableColumns tableColumns() {
        return new JsonFdxTableColumns(jsonStructure);
    }

    public JsonParsedQueryCriterion parsedQueryCriterion(QueryCriterion queryCriterion, Connection connection) throws NamingException, SQLException {
        return new JsonParsedQueryCriterion(queryCriterion, tableColumns(), new JsonStructureIterator(tableName.name(connection), jsonStructure).columnsDefinitionWithFullQualifiedName());
    }

    public JsonTableDefinition tableDefinition(Connection connection) throws SQLException {
        return new JsonStructureIterator(tableName.name(connection), jsonStructure).tableDefinition();
    }

    public String latestDataProductionToken(Connection connection) throws SQLException {
        return new LatestDataProductionTokenQuery(connection, referentielVersion, codeTypeFichier).execute();
    }

    public boolean isReferentiel(Connection connection) throws NamingException, SQLException {

        if (cachedNatureProduction.isEmpty()) {

            String query = "SELECT natureproduction "
                    + "FROM natureproductionfichier "
                    + "WHERE typefichier_code = ? AND referentiel_version = ?";

            try ( PreparedStatement statement = connection.prepareStatement(query)) {

                statement.setString(1, codeTypeFichier);
                statement.setString(2, referentielVersion);

                try ( ResultSet resultSet = statement.executeQuery()) {
                    if (resultSet.next()) {
                        cachedNatureProduction.add(resultSet.getString("natureproduction").equals("REFERENTIELLE"));
                    } else {
                        cachedNatureProduction.add(Boolean.TRUE);
                    }
                }
            }

        }

        return cachedNatureProduction.get(0);
    }

    public TableName getTableName() {
        return tableName;
    }

    public abstract void createTable(Connection connection) throws NamingException, SQLException, Exception;

    public abstract void update(LinkedHashMap<String, Object> dataToUpdate, QueryCriterion queryCriterion, CommonDataProduction dataProduction, Connection connection) throws TypeFichierDataBadRequest, SQLException, NamingException, Exception;

    private QueryCriterion addLatestDataProductionCriterion(QueryCriterion queryCriterion, String rootTableAlias, Connection connection) throws SQLException {
        Long latestDataProductionId = new MaxDataProductionIdQuery(tableName.name(connection), connection).execute();

        if (latestDataProductionId > 0) {

            return new QueryCriterion(null,
                    null,
                    null,
                    CriteriaLogicConnector.AND,
                    Arrays.asList(queryCriterion,
                            new QueryCriterion(rootTableAlias.isEmpty() ? "" : rootTableAlias + "." + new DataProductionIdColumnDefinition().name(),
                                    latestDataProductionId,
                                    Operateur.EQUALS,
                                    null,
                                    Arrays.asList()))
            );
        } else {
            return queryCriterion;
        }
    }

    private void setToken(Connection connection, JsonSelectQueryResult result) throws SQLException {
        String token = latestDataProductionToken(connection);
        result.setToken(token);
    }
}