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

import it.inaf.ia2.tsm.api.contract.DatabaseType;
import it.inaf.ia2.tsm.api.contract.TapSchema;
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 final List<InconsistentValue> inconsistencies;
    private final List<String> unexisingSchemas;
    private final List<String> unexisingTables;
    private final Map<String, String> unexisingColumns;

    public ConsistencyChecks() {
        inconsistencies = new ArrayList<>();
        unexisingSchemas = new ArrayList<>();
        unexisingTables = new ArrayList<>();
        unexisingColumns = new HashMap<>();
    }

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

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

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

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

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

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

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