/*
 * _____________________________________________________________________________
 * 
 * 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.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @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);

    private static class UnexistingKeyColumn {

        private final String keyId;
        private final String fromColumn;
        private final String targetColumn;

        private UnexistingKeyColumn(String keyId, String fromColumn, String targetColumn) {
            this.keyId = keyId;
            this.fromColumn = fromColumn;
            this.targetColumn = targetColumn;
        }

        public String getKeyId() {
            return keyId;
        }

        public String getFromColumn() {
            return fromColumn;
        }

        public String getTargetColumn() {
            return targetColumn;
        }
    }

    private final List<InconsistentValue> inconsistencies;
    private final Set<String> unexisingSchemas;
    private final Set<String> unexisingTables;
    private final Map<String, String> unexisingColumns;
    private final Set<String> unexistingKeys;
    private final List<UnexistingKeyColumn> unexistingKeyColumns;

    public ConsistencyChecks() {
        inconsistencies = new ArrayList<>();
        unexisingSchemas = new HashSet<>();
        unexisingTables = new HashSet<>();
        unexisingColumns = new HashMap<>();
        unexistingKeys = new HashSet<>();
        unexistingKeyColumns = 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 Map<String, String> getUnexisingColumns() {
        return unexisingColumns;
    }

    public void addUnexistingColumn(String completeTableName, String columnName) {
        unexisingColumns.put(completeTableName, columnName);
    }

    public void addUnexistingKey(String keyId) {
        if (keyId == null) {
            throw new IllegalArgumentException("key_id can't be null");
        }
        unexistingKeys.add(keyId);
    }

    public Set<String> getUnexistingKeys() {
        return unexistingKeys;
    }

    public void addUnexistingKeyColumn(String keyId, String fromColumn, String targetColumn) {
        unexistingKeyColumns.add(new UnexistingKeyColumn(keyId, fromColumn, targetColumn));
    }

    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() || !unexisingColumns.isEmpty();
    }

    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));
            }

            for (Map.Entry<String, String> entry : unexisingColumns.entrySet()) {
                query = "select k.key_id AS key_id\n"
                        + "FROM `keys` k\n"
                        + "JOIN key_columns c ON k.key_id = c.key_id\n"
                        + "WHERE (k.from_table = ? AND c.from_column = ?) OR (k.target_table = ? AND c.target_column = ?)";

                try (PreparedStatement ps = conn.prepareStatement(query)) {
                    ps.setString(1, entry.getKey());
                    ps.setString(2, entry.getValue());
                    ps.setString(3, entry.getKey());
                    ps.setString(4, entry.getValue());

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

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

            keysToRemoveIds.addAll(unexistingKeys);

            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 (Map.Entry<String, String> entry : unexisingColumns.entrySet()) {
                    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, entry.getKey());
                        ps.setString(2, entry.getValue());
                        LOG.debug("Executing query {} [{}, {}]", query, entry.getKey(), entry.getValue());
                        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;
                }
            }
        }
    }
}