/*
 * _____________________________________________________________________________
 * 
 * INAF - OATS National Institute for Astrophysics - Astronomical Observatory of
 * Trieste INAF - IA2 Italian Center for Astronomical Archives
 * _____________________________________________________________________________
 * 
 * Copyright (C) 2017 Istituto Nazionale di Astrofisica
 * 
 * This program is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License Version 3 as published by the
 * Free Software Foundation.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
 * details.
 * 
 * You should have received a copy of the GNU General Public License along with
 * this program; if not, write to the Free Software Foundation, Inc., 51
 * Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
 */
package it.inaf.ia2.tsm;

import static it.inaf.ia2.tsm.TapSchema.COLUMNS_TABLE;
import static it.inaf.ia2.tsm.TapSchema.KEYS_TABLE;
import static it.inaf.ia2.tsm.TapSchema.KEY_COLUMNS_TABLE;
import static it.inaf.ia2.tsm.TapSchema.SCHEMAS_TABLE;
import static it.inaf.ia2.tsm.TapSchema.TABLES_TABLE;
import it.inaf.ia2.tsm.datalayer.DBBroker;
import it.inaf.ia2.tsm.model.ColumnModel;
import it.inaf.ia2.tsm.model.SchemaModel;
import it.inaf.ia2.tsm.model.TableModel;
import it.inaf.ia2.tsm.model.TypeMapping;
import it.inaf.ia2.tsm.model.TypesMapping;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.regex.Pattern;

/**
 * Loads the TAP_SCHEMA and performs consistency checking.
 *
 * @author Sonia Zorba {@literal <zorba at oats.inaf.it>}
 */
public class TapSchemaLoader {

    private final TapSchema tapSchema;
    private final DBBroker tapSchemaDBBroker;
    private ConsistencyChecks consistencyChecks;

    public static ConsistencyChecks loadExistingTapSchema(TapSchema tapSchema) throws SQLException {
        TapSchemaLoader loader = new TapSchemaLoader(tapSchema);
        return loader.loadExistingTapSchema();
    }

    private TapSchemaLoader(TapSchema tapSchema) {
        this.tapSchema = tapSchema;
        tapSchemaDBBroker = tapSchema.getTapSchemaDBBroker();
    }

    private ConsistencyChecks loadExistingTapSchema() throws SQLException {

        consistencyChecks = new ConsistencyChecks();

        loadAndCheckSchemata();
        loadAndCheckTables();
        loadAndCheckColumns();
        loadAndCheckKeys();

        checkModel(tapSchema.getTapSchemaModel());
        checkObscore();

        return consistencyChecks;
    }

    private void checkObscore() {
        if (tapSchema.isHasObscore()) {
            String ivoaSchemaName = tapSchema.getIvoaSchemaName();
            if (tapSchema.getChild(ivoaSchemaName) == null) {
                if (tapSchema.isAddable(tapSchema.getRealSchemaName(ivoaSchemaName))) {
                    consistencyChecks.setObscoreToAdd(true);
                } else {
                    consistencyChecks.setMissingObscore(true);
                }
            } else {
                checkModel(tapSchema.getIvoaSchemaModel());
            }
        }
    }

    private void checkModel(SchemaModel schemaModel) {

        String schemaName = schemaModel.getName();
        if (TapSchema.STANDARD_TAP_SCHEMA_NAME.equals(schemaName)) {
            schemaName = tapSchema.getName();
        }

        Schema schema = tapSchema.getChild(schemaName);

        for (TableModel tableModel : schemaModel.getTables()) {

            String tableName = tableModel.getName();
            Table table = schema.getChild(tableName);

            if (table == null) {
                if (schema.isAddable(tableModel.getName())) {
                    consistencyChecks.addTableToAdd(schema.getName(), tableName);
                } else {
                    consistencyChecks.addMissingTable(schema.getName(), tableName);
                }
            } else {

                for (ColumnModel columnModel : tableModel.getColumns()) {

                    Column column = table.getChild(columnModel.getName());

                    ColumnHolder ch = new ColumnHolder(schemaName,
                            table.getName(), columnModel.getName());

                    if (column == null) {
                        if (columnModel.isMandatory()) {
                            if (table.isAddable(columnModel.getName())) {
                                consistencyChecks.addColumnToAdd(ch);
                            } else {
                                consistencyChecks.addMissingColumn(ch, columnModel);
                            }
                        } else if (table.isAddable(columnModel.getName())) {
                            consistencyChecks.addUnaddedOptionalColumn(ch);
                        }
                    } else {
                        // Data type checking
                        String originalDataType = (String) column.getMetadata(Column.ORIGINAL_DATATYPE_KEY);
                        String modelDataType = (String) column.getMetadata(Column.DATATYPE_KEY);
                        TypeMapping originalType = TypesMapping.getTypeMapping(originalDataType, tapSchema.getDataTypeMode());
                        TypeMapping modelType = TypesMapping.getTypeMapping(modelDataType, tapSchema.getDataTypeMode());
                        if (originalType.getJavaType() != modelType.getJavaType()) {
                            consistencyChecks.addWrongDataType(ch, originalDataType, modelDataType, columnModel.getType(), columnModel.getSize());
                        }
                    }
                }
            }
        }
    }

    private boolean equalsOneOf(String key, String... names) {
        for (String name : names) {
            if (name.equals(key)) {
                return true;
            }
        }
        return false;
    }

    private boolean hasFixedValue(Column column, String key) {
        EntityProperty ep = column.getProperty(key);
        if (!ep.isUpdatable()) {
            return true;
        }

        // Here we check fixed values for columns having a XML model.
        // We need to be aware that the user could alter these structures
        // adding custom tables and columns, so we have to check for null
        // parts of the models.
        //
        Schema parentSchema = column.getParent().getParent();

        SchemaModel schemaModel;
        if (parentSchema.getName().equals(tapSchema.getName())) {

            if (parentSchema.getName().equals(tapSchema.getName())) {
                schemaModel = tapSchema.getTapSchemaModel();
            } else {
                schemaModel = tapSchema.getIvoaSchemaModel();
            }

            if (schemaModel != null) {
                TableModel tableModel = schemaModel.getTable(column.getParent().getName());
                if (tableModel != null) {
                    ColumnModel columnModel = tableModel.get(column.getName());
                    if (columnModel != null) {
                        if (parentSchema.getName().equals(tapSchema.getName())) {
                            // is a TAP_SCHEMA column
                            return equalsOneOf(key, Column.STD_KEY, Column.PRINCIPAL_KEY);
                        } else {
                            // is an obscore column
                            return equalsOneOf(key, Column.STD_KEY, Column.PRINCIPAL_KEY,
                                    Column.UCD_KEY, Column.UNIT_KEY, Column.UTYPE_KEY, Column.DATATYPE_KEY);
                        }
                    }
                }
            }
        }

        return false;
    }

    private Object getCompatibleIntOrBoolValue(String key, boolean value) {
        if (tapSchema.getTapSchemaModel().getTable(COLUMNS_TABLE).get(key).getJavaType() == Integer.class) {
            return value ? 1 : 0;
        }
        return value;
    }

    private Object getCorrectValue(Column column, String key) {

        EntityProperty ep = column.getProperty(key);
        if (!ep.isUpdatable()) {
            return column.getMetadata(key);
        }

        Schema parentSchema = column.getParent().getParent();
        SchemaModel schemaModel;
        if (parentSchema.getName().equals(tapSchema.getName())) {
            schemaModel = tapSchema.getTapSchemaModel();
        } else {
            schemaModel = tapSchema.getIvoaSchemaModel();
        }

        ColumnModel columnModel = schemaModel.getTable(column.getParent().getName()).get(column.getName());
        switch (key) {
            case Column.STD_KEY:
                return getCompatibleIntOrBoolValue(Column.STD_KEY, columnModel.isStandard());
            case Column.PRINCIPAL_KEY:
                return getCompatibleIntOrBoolValue(Column.PRINCIPAL_KEY, columnModel.isPrincipal());
            case Column.UCD_KEY:
                return columnModel.getUcd();
            case Column.UNIT_KEY:
                return columnModel.getUnit();
            case Column.UTYPE_KEY:
                return columnModel.getUtype();
            case Column.DATATYPE_KEY:
                return TypesMapping.getDataType(columnModel.getType(), tapSchema.getDataTypeMode());
        }

        throw new RuntimeException("Unable to retrieve correct value for " + key);
    }

    private void loadSavedProperties(TapSchemaEntity tapSchemaEntity, Map<String, Object> savedProperties) {
        for (Map.Entry<String, Object> entry : savedProperties.entrySet()) {

            String key = entry.getKey();
            Object savedValue = entry.getValue();

            if (tapSchemaEntity instanceof Column) {
                Column column = (Column) tapSchemaEntity;
                if (hasFixedValue(column, key)) {
                    Object correctValue = getCorrectValue(column, key);
                    if (!Objects.equals(savedValue, correctValue)) {
                        InconsistentColumnProperty inconsistentValue = new InconsistentColumnProperty(
                                new ColumnHolder(column),
                                key,
                                savedValue,
                                correctValue
                        );
                        consistencyChecks.addInconsistency(inconsistentValue);
                    }
                }
            }

            tapSchemaEntity.initProperty(key, savedValue);
        }
    }

    private void loadAndCheckSchemata() throws SQLException {
        for (Map<String, Object> schemaProps : tapSchemaDBBroker.getSavedItems(tapSchema.getRealName(), tapSchema.getTableModel(SCHEMAS_TABLE))) {
            String schemaName = (String) schemaProps.get(Schema.SCHEMA_NAME_KEY);
            Schema schema = tapSchema.addChild(schemaName);
            if (schema == null) {
                consistencyChecks.addUnexistingSchema(schemaName);
            } else {
                loadSavedProperties(schema, schemaProps);
                schema.setStatus(Status.ADDED_PERSISTED);
            }
        }
    }

    private void loadAndCheckTables() throws SQLException {
        for (Map<String, Object> tableProps : tapSchemaDBBroker.getSavedItems(tapSchema.getRealName(), tapSchema.getTableModel(TABLES_TABLE))) {
            String tableCompleteName = (String) tableProps.get(Table.TABLE_NAME_KEY);
            String[] tableNameSplit = tableCompleteName.split(Pattern.quote("."));
            String schemaName = tableNameSplit[0];
            String tableName = tableNameSplit[1];
            Schema schema = tapSchema.getChild(schemaName, Status.ADDED_PERSISTED);
            if (schema == null) {
                consistencyChecks.addUnexistingSchema(schemaName);
            } else {
                Table table = schema.addChild(tableName);
                if (table == null) {
                    consistencyChecks.addUnexistingTable(schemaName, tableName);
                } else {
                    loadSavedProperties(table, tableProps);
                    table.setStatus(Status.ADDED_PERSISTED);
                }
            }
        }
    }

    private void loadAndCheckColumns() throws SQLException {
        for (Map<String, Object> columnProps : tapSchemaDBBroker.getSavedItems(tapSchema.getRealName(), tapSchema.getTableModel(COLUMNS_TABLE))) {
            String tableCompleteName = (String) columnProps.get(Column.TABLE_NAME_KEY);
            String[] tableNameSplit = tableCompleteName.split(Pattern.quote("."));
            String schemaName = tableNameSplit[0];
            String tableName = tableNameSplit[1];
            String columnName = (String) columnProps.get(Column.COLUMN_NAME_KEY);
            Schema schema = tapSchema.getChild(schemaName, Status.ADDED_PERSISTED);
            if (schema == null) {
                consistencyChecks.addUnexistingSchema(schemaName);
            } else {
                Table table = schema.getChild(tableName, Status.ADDED_PERSISTED);
                if (table == null) {
                    consistencyChecks.addUnexistingTable(schemaName, tableName);
                } else {
                    Column column = table.addChild(columnName);
                    if (column == null) {
                        consistencyChecks.addUnexistingColumn(schemaName, tableName, columnName);
                    } else {
                        loadSavedProperties(column, columnProps);
                        column.setStatus(Status.ADDED_PERSISTED);
                    }
                }
            }
        }
    }

    private void loadAndCheckKeys() throws SQLException {
        List<Map<String, Object>> keysProps = tapSchemaDBBroker.getSavedItems(tapSchema.getRealName(), tapSchema.getTableModel(KEYS_TABLE));
        List<Map<String, Object>> keysColumnsProps = tapSchemaDBBroker.getSavedItems(tapSchema.getRealName(), tapSchema.getTableModel(KEY_COLUMNS_TABLE));
        for (Map<String, Object> keyProp : keysProps) {

            String fromTable = (String) keyProp.get(Key.FROM_TABLE_KEY);
            String targetTable = (String) keyProp.get(Key.TARGET_TABLE_KEY);
            String keyId = (String) keyProp.get(Key.ID_KEY);
            assert keyId != null;

            List<Map<String, Object>> kcPropsById = new ArrayList<>();
            for (Map<String, Object> kcp : keysColumnsProps) {
                String keyColumnId = (String) kcp.get(KeyColumn.KEY_ID_KEY);
                assert keyColumnId != null;
                if (keyColumnId.equals(keyId)) {
                    kcPropsById.add(kcp);
                }
            }

            // Searching the key
            boolean keyFound = false;
            for (Key key : tapSchema.getAllKeys()) {
                if (key.getFromTableCompleteName().equals(fromTable) && key.getTargetTableCompleteName().equals(targetTable)) {
                    // Search the key columns having proper key id

                    // Verifying the matching
                    List<KeyColumn> matchedKeyColumns = new ArrayList<>();
                    if (kcPropsById.size() == key.getKeyColumns().size()) {
                        for (Map<String, Object> kcp : kcPropsById) {
                            String fromColumn = (String) kcp.get(KeyColumn.FROM_COLUMN_KEY);
                            String targetColumn = (String) kcp.get(KeyColumn.TARGET_COLUMN_KEY);
                            for (KeyColumn keyColumn : key.getKeyColumns()) {
                                if (keyColumn.getFromColumn().equals(fromColumn)
                                        && keyColumn.getTargetColumn().equals(targetColumn)) {
                                    matchedKeyColumns.add(keyColumn);
                                }
                            }
                        }
                    }
                    if (kcPropsById.size() == matchedKeyColumns.size()) {
                        keyFound = true;
                        int index = 0;
                        loadSavedProperties(key, keyProp);
                        for (Map<String, Object> kcp : kcPropsById) {
                            KeyColumn kc = matchedKeyColumns.get(index);
                            loadSavedProperties(kc, kcp);
                            index++;
                        }
                    }
                }
            }

            if (!keyFound) {
                boolean setKeyToRemove = true;
                if (setKeyToRemove) {
                    String[] fromColumns = new String[kcPropsById.size()];
                    String[] targetColumns = new String[kcPropsById.size()];
                    int i = 0;
                    for (Map<String, Object> kcp : kcPropsById) {
                        fromColumns[i] = (String) kcp.get(KeyColumn.FROM_COLUMN_KEY);
                        targetColumns[i] = (String) kcp.get(KeyColumn.TARGET_COLUMN_KEY);
                        i++;
                    }
                    consistencyChecks.addUnexistingKey(keyId, fromTable, fromColumns, targetTable, targetColumns);
                }
            }
        }
    }
}