/*
 * _____________________________________________________________________________
 * 
 * INAF - OATS National Institute for Astrophysics - Astronomical Observatory of
 * Trieste INAF - IA2 Italian Center for Astronomical Archives
 * _____________________________________________________________________________
 * 
 * Copyright (C) 2016 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 it.inaf.ia2.tsm.datalayer.DatabaseType;
import it.inaf.ia2.tsm.datalayer.DBWrapper;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * TODO: Move queries into data layer classes.
 *
 * @author Sonia Zorba {@literal <zorba at oats.inaf.it>}
 */
public class ConsistencyChecks implements Serializable {

    private static final long serialVersionUID = 4412404312756740093L;
    private final static Logger LOG = LoggerFactory.getLogger(ConsistencyChecks.class);

    public static class UnexistingColumn implements Serializable {

        private static final long serialVersionUID = -4898369878807200093L;

        private final String completeTableName;
        private final String columnName;

        private UnexistingColumn(String completeTableName, String columnName) {
            this.completeTableName = completeTableName;
            this.columnName = columnName;
        }

        public String getCompleteTableName() {
            return completeTableName;
        }

        public String getColumnName() {
            return columnName;
        }

        @Override
        public String toString() {
            return String.format("%s.%s", completeTableName, columnName);
        }
    }

    public static class UnexistingKey implements Serializable {

        private static final long serialVersionUID = 7891439129072900628L;

        private final String keyId;
        private final String fromTable;
        private final String[] fromColumns;
        private final String targetTable;
        private final String[] targetColumns;

        private UnexistingKey(String keyId, String fromTable, String[] fromColumns, String targetTable, String[] targetColumns) {
            this.keyId = keyId;
            this.fromTable = fromTable;
            this.fromColumns = fromColumns;
            this.targetTable = targetTable;
            this.targetColumns = targetColumns;
        }

        public String getKeyId() {
            return keyId;
        }

        public String getFromTable() {
            return fromTable;
        }

        public String[] getFromColumns() {
            return fromColumns;
        }

        public String getTargetTable() {
            return targetTable;
        }

        public String[] getTargetColumns() {
            return targetColumns;
        }

        private String getColumnsString(String[] columns) {
            StringBuilder sb = new StringBuilder();
            boolean first = true;
            for (String column : columns) {
                if (!first) {
                    sb.append(",");
                }
                sb.append(column);
                first = false;
            }
            return sb.toString();
        }

        @Override
        public String toString() {
            return String.format("[%s] %s(%s) -> %s(%s)", keyId,
                    fromTable, getColumnsString(fromColumns),
                    targetTable, getColumnsString(targetColumns));
        }
    }

    private final List<InconsistentValue> inconsistencies;
    private final Set<String> unexisingSchemas;
    private final Set<String> unexisingTables;
    private final List<UnexistingColumn> unexistingColumns;
    private final List<UnexistingKey> unexistingKeys;

    public ConsistencyChecks() {
        inconsistencies = new ArrayList<>();
        unexisingSchemas = new HashSet<>();
        unexisingTables = new HashSet<>();
        unexistingColumns = new ArrayList<>();
        unexistingKeys = new ArrayList<>();
    }

    public void addInconsistency(InconsistentValue problemDescription) {
        inconsistencies.add(problemDescription);
    }

    public List<InconsistentValue> getInconsistencies() {
        return inconsistencies;
    }

    public Set<String> getUnexisingSchemas() {
        return unexisingSchemas;
    }

    public void addUnexistingSchema(String schemaName) {
        unexisingSchemas.add(schemaName);
    }

    public Set<String> getUnexisingTables() {
        return unexisingTables;
    }

    public void addUnexistingTable(String schemaName, String tableSimpleName) {
        unexisingTables.add(schemaName + "." + tableSimpleName);
    }

    public List<UnexistingColumn> getUnexisingColumns() {
        return unexistingColumns;
    }

    public void addUnexistingColumn(String completeTableName, String columnName) {
        unexistingColumns.add(new UnexistingColumn(completeTableName, columnName));
    }

    public void addUnexistingKey(String keyId, String fromTable, String[] fromColumns, String targetTable, String[] targetColumns) {
        if (keyId == null) {
            throw new IllegalArgumentException("key_id can't be null");
        }
        unexistingKeys.add(new UnexistingKey(keyId, fromTable, fromColumns, targetTable, targetColumns));
    }

    public List<UnexistingKey> getUnexistingKeys() {
        return unexistingKeys;
    }

    private Set<String> getKeysToRemove(Connection conn, String tapSchemaNameEscaped, DatabaseType dbType, String like) throws SQLException {
        Set<String> ret = new HashSet<>();
        String query = String.format("SELECT key_id from %s.%s WHERE from_table LIKE ? OR target_table LIKE ?", tapSchemaNameEscaped, TSMUtil.escapeName("keys", dbType));
        try (PreparedStatement ps = conn.prepareStatement(query)) {
            ps.setString(1, like + "%");
            ps.setString(2, like + "%");
            LOG.debug("Executing query: {} [{}]", query, like);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    ret.add(rs.getString("key_id"));
                }
            }
        }
        return ret;
    }

    public boolean isInconsistent() {
        return !inconsistencies.isEmpty() || !unexisingSchemas.isEmpty() || !unexisingTables.isEmpty() || !unexistingColumns.isEmpty();
    }

    private void keysToRemoveFromUnexistingColumns(Connection conn, String tapSchemaNameEscaped, Set<String> keysToRemoveIds) throws SQLException {
        for (UnexistingColumn unexistingColumn : unexistingColumns) {

            StringBuilder sb = new StringBuilder();
            sb.append("SELECT k.key_id AS key_id\n");
            sb.append("FROM ");
            sb.append(tapSchemaNameEscaped);
            sb.append(".`keys` k\n");
            sb.append("JOIN ");
            sb.append(tapSchemaNameEscaped);
            sb.append(".key_columns c ON k.key_id = c.key_id\n");
            sb.append("WHERE (k.from_table = ? AND c.from_column = ?) OR (k.target_table = ? AND c.target_column = ?)");

            String query = sb.toString();

            try (PreparedStatement ps = conn.prepareStatement(query)) {
                ps.setString(1, unexistingColumn.getCompleteTableName());
                ps.setString(2, unexistingColumn.getColumnName());
                ps.setString(3, unexistingColumn.getCompleteTableName());
                ps.setString(4, unexistingColumn.getColumnName());

                LOG.debug("Executing query {}", query);

                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        keysToRemoveIds.add(rs.getString("key_id"));
                    }
                }
            }
        }
    }

    public void amendTapSchema(DBWrapper dbWrapper, TapSchema tapSchema) throws SQLException {

        Set<String> keysToRemoveIds = new HashSet<>();

        DatabaseType dbType = dbWrapper.getTapSchemaDatabaseType();
        DataSource dataSource = dbWrapper.getTapSchemaDataSource();

        String tapSchemaNameEscaped = TSMUtil.escapeName(tapSchema.getName(), dbType);

        String query;
        try (Connection conn = dataSource.getConnection()) {

            for (String schema : unexisingSchemas) {
                keysToRemoveIds.addAll(getKeysToRemove(conn, tapSchemaNameEscaped, dbType, schema));
            }
            for (String table : unexisingTables) {
                keysToRemoveIds.addAll(getKeysToRemove(conn, tapSchemaNameEscaped, dbType, table));
            }

            keysToRemoveFromUnexistingColumns(conn, tapSchemaNameEscaped, keysToRemoveIds);

            for (UnexistingKey unexistingKey : unexistingKeys) {
                keysToRemoveIds.add(unexistingKey.getKeyId());
            }

            conn.setAutoCommit(false);
            LOG.debug("Starting transaction");

            try {
                // Removing all key_columns
                for (String keyId : keysToRemoveIds) {
                    query = String.format("DELETE FROM %s.%s WHERE key_id = ?", tapSchemaNameEscaped, TSMUtil.escapeName("key_columns", dbType));
                    try (PreparedStatement ps = conn.prepareStatement(query)) {
                        ps.setString(1, keyId);
                        LOG.debug("Executing query {} [{}]", query, keyId);
                        ps.executeUpdate();
                    }
                }

                // Removing all keys
                for (String keyId : keysToRemoveIds) {
                    query = String.format("DELETE FROM %s.%s WHERE key_id = ?", tapSchemaNameEscaped, TSMUtil.escapeName("keys", dbType));
                    try (PreparedStatement ps = conn.prepareStatement(query)) {
                        ps.setString(1, keyId);
                        LOG.debug("Executing query {} [{}]", query, keyId);
                        ps.executeUpdate();
                    }
                }

                // Removing all columns
                for (UnexistingColumn unexistingColumn : unexistingColumns) {
                    query = String.format("DELETE FROM %s.%s WHERE table_name = ? AND column_name = ?", tapSchemaNameEscaped, TSMUtil.escapeName("columns", dbType));
                    try (PreparedStatement ps = conn.prepareStatement(query)) {
                        ps.setString(1, unexistingColumn.getCompleteTableName());
                        ps.setString(2, unexistingColumn.getColumnName());
                        LOG.debug("Executing query {} [{}, {}]", query, unexistingColumn.getCompleteTableName(), unexistingColumn.getColumnName());
                        ps.executeUpdate();
                    }
                }
                for (String table : unexisingTables) {
                    query = String.format("DELETE FROM %s.%s WHERE table_name = ?", tapSchemaNameEscaped, TSMUtil.escapeName("columns", dbType));
                    try (PreparedStatement ps = conn.prepareStatement(query)) {
                        ps.setString(1, table);
                        LOG.debug("Executing query {} [{}]", query, table);
                        ps.executeUpdate();
                    }
                }
                for (String schema : unexisingSchemas) {
                    query = String.format("DELETE FROM %s.%s WHERE table_name LIKE ?", tapSchemaNameEscaped, TSMUtil.escapeName("columns", dbType));
                    try (PreparedStatement ps = conn.prepareStatement(query)) {
                        ps.setString(1, schema + "%");
                        LOG.debug("Executing query {} [{}%]", query, schema);
                        ps.executeUpdate();
                    }
                }

                // Removing all tables
                for (String table : unexisingTables) {
                    query = String.format("DELETE FROM %s.%s WHERE table_name = ?", tapSchemaNameEscaped, TSMUtil.escapeName("tables", dbType));
                    try (PreparedStatement ps = conn.prepareStatement(query)) {
                        ps.setString(1, table);
                        LOG.debug("Executing query {} [{}]", query, table);
                        ps.executeUpdate();
                    }
                }
                for (String schema : unexisingSchemas) {
                    query = String.format("DELETE FROM %s.%s WHERE schema_name = ?", tapSchemaNameEscaped, TSMUtil.escapeName("tables", dbType));
                    try (PreparedStatement ps = conn.prepareStatement(query)) {
                        ps.setString(1, schema);
                        LOG.debug("Executing query {} [{}]", query, schema);
                        ps.executeUpdate();
                    }
                }

                // Removing all schemas
                for (String schema : unexisingSchemas) {
                    query = String.format("DELETE FROM %s.%s WHERE schema_name = ?", tapSchemaNameEscaped, TSMUtil.escapeName("schemas", dbType));
                    try (PreparedStatement ps = conn.prepareStatement(query)) {
                        ps.setString(1, schema);
                        LOG.debug("Executing query {} [{}]", query, schema);
                        ps.executeUpdate();
                    }
                }

                conn.commit();
            } catch (SQLException e) {
                LOG.error("Exception detected. Executing rollback!", e);
                try {
                    conn.rollback();
                    conn.setAutoCommit(true);
                } catch (SQLException er) {
                    LOG.error("Exception during rollback", er);
                    throw er;
                }
            }
        }
    }
}