/* 
 * _____________________________________________________________________________
 * 
 * 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.Column;
import it.inaf.ia2.tsm.api.contract.Status;
import it.inaf.ia2.tsm.api.contract.Table;
import it.inaf.ia2.tsm.api.contract.TapSchema;
import it.inaf.ia2.tsm.api.contract.TapSchemaEntity;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Utility class that contains static methods for managing {@link Column}s into
 * the database.
 *
 * @author Sonia Zorba {@literal <zorba at oats.inaf.it>}
 */
public class DaoColumn {

    private static final Logger log = LoggerFactory.getLogger(DaoColumn.class);

    private static boolean equalsOneOf(String string, String... values) {
        for (String value : values) {
            if (string.equals(value)) {
                return true;
            }
        }
        return false;
    }

    /**
     * Returns the list of all the columns names given a schema name and a table
     * name, also if these objects have never been added into the TAP_SCHEMA.
     * This can be useful to retrieve the source database structure.
     */
    public static List<String> getAllColumnsNames(DBWrapper dbWrapper, TapSchema tapSchema, String schemaName, String tableSimpleName) throws SQLException {
        final List<String> allColumns = new ArrayList<>();

        DataSource dataSource = TSMUtil.getSchemaDataSource(dbWrapper, tapSchema, schemaName);
        DatabaseType dbType = TSMUtil.getSchemaDatabaseType(dbWrapper, tapSchema, schemaName);

        String query;
        if (dbType == DatabaseType.MYSQL) {
            query = String.format("SHOW COLUMNS FROM `%s`.`%s`", schemaName, tableSimpleName);
        } else if (dbType == DatabaseType.POSTGRES) {
            query = "SELECT column_name FROM information_schema.columns WHERE table_schema = '" + schemaName + "' AND table_name = '" + tableSimpleName + "'";
        } else {
            throw new UnsupportedOperationException("Database type " + dbType + " not supported");
        }

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

        try (Connection connection = dataSource.getConnection();
                Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery(query)) {

            while (resultSet.next()) {

                String columnName;
                if (dbType == DatabaseType.MYSQL) {
                    columnName = resultSet.getString("Field");
                } else if (dbType == DatabaseType.POSTGRES) {
                    columnName = resultSet.getString("column_name");
                } else {
                    throw new UnsupportedOperationException("Database type " + dbType + " not supported");
                }

                allColumns.add(columnName);
            }
        }

        return allColumns;
    }

    /**
     * For performance reasons all columns of a {@link Table} are loaded
     * together using this method. Columns {@link Status} is at first set as
     * {@code LOADED}.
     */
    protected static List<Column> loadAllTableColumns(DBWrapper dbWrapper, TapSchema tapSchema, String schemaName, Table table) throws SQLException {
        String tableSimpleName = table.getName();
        final List<Column> allColumns = new ArrayList<>();

        DataSource dataSource = TSMUtil.getSchemaDataSource(dbWrapper, tapSchema, schemaName);
        DatabaseType dbType = TSMUtil.getSchemaDatabaseType(dbWrapper, tapSchema, schemaName);

        String query;
        if (dbType == DatabaseType.MYSQL) {
            query = String.format("SHOW COLUMNS FROM `%s`.`%s`", schemaName, tableSimpleName);
        } else if (dbType == DatabaseType.POSTGRES) {
            query = "SELECT c.column_name, c.data_type, r.contype AS column_type, c.character_maximum_length, c.numeric_precision\n" //, c.numeric_precision_radix
                    + "FROM information_schema.columns c\n"
                    + "JOIN pg_catalog.pg_tables t ON c.table_schema = t.schemaname AND c.table_name = t.tablename\n"
                    + "LEFT JOIN pg_catalog.pg_constraint r ON c.ordinal_position = ANY(r.conkey) AND r.conrelid = (t.schemaname || '.' || t.tablename)::regclass::oid\n"
                    + "WHERE t.schemaname = '" + schemaName + "' AND t.tablename = '" + tableSimpleName + "'";
        } else {
            throw new UnsupportedOperationException("Database type " + dbType + " not supported");
        }

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

        try (Connection connection = dataSource.getConnection();
                Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery(query)) {

            while (resultSet.next()) {

                String columnName;
                if (dbType == DatabaseType.MYSQL) {
                    columnName = resultSet.getString("Field");
                } else if (dbType == DatabaseType.POSTGRES) {
                    columnName = resultSet.getString("column_name");
                } else {
                    throw new UnsupportedOperationException("Database type " + dbType + " not supported");
                }

                boolean indexed = false, primaryKey = false;

                // Key type
                if (dbType == DatabaseType.MYSQL) {
                    String key = resultSet.getString("Key");
                    primaryKey = key.equals("PRI");
                    indexed = equalsOneOf(key, "PRI", "UNI", "MUL");
                } else if (dbType == DatabaseType.POSTGRES) {
                    String columnType = resultSet.getString("column_type");
                    if (columnType != null) {
                        primaryKey = "p".equals(columnType);
                        indexed = equalsOneOf(columnType, "p", "f", "u");
                    }
                } else {
                    throw new UnsupportedOperationException("Database type " + dbType + " not supported");
                }

                // Datatype and Size
                int size = 0;
                String datatype;

                if (dbType == DatabaseType.MYSQL) {
                    String type = resultSet.getString("Type").toLowerCase();

                    if (type.startsWith("int")) {
                        datatype = "adql:INTEGER";
                    } else if (type.startsWith("smallint")) {
                        datatype = "adql:SMALLINT";
                    } else if (type.startsWith("bigint")) {
                        datatype = "adql:BIGINT";
                    } else if (type.startsWith("float")) {
                        datatype = "adql:REAL";
                    } else if (type.startsWith("char")) {
                        int beginIndex = type.indexOf('(');
                        int endIndex = type.indexOf(')');
                        size = Integer.parseInt(type.substring(beginIndex + 1, endIndex));
                        datatype = "adql:CHAR";
                    } else if (type.startsWith("varchar")) {
                        int beginIndex = type.indexOf('(');
                        int endIndex = type.indexOf(')');
                        size = Integer.parseInt(type.substring(beginIndex + 1, endIndex));
                        datatype = "adql:VARCHAR";
                    } else if (type.contains("timestamp")) {
                        datatype = "adql:TIMESTAMP";
                    } else {
                        datatype = "adql:" + type.toUpperCase();
                    }
                } else if (dbType == DatabaseType.POSTGRES) {
                    String type = resultSet.getString("data_type");

                    if (type.startsWith("int")) {
                        datatype = "adql:INTEGER";
                    } else if (type.startsWith("smallint")) {
                        datatype = "adql:SMALLINT";
                    } else if (type.startsWith("bigint")) {
                        datatype = "adql:BIGINT";
                    } else if (type.startsWith("double") || type.startsWith("real")) {
                        datatype = "adql:REAL";
                    } else if (type.startsWith("character varying")) {
                        datatype = "adql:VARCHAR";
                        size = resultSet.getInt("character_maximum_length");
                    } else if (type.startsWith("char")) {
                        datatype = "adql:CHAR";
                        size = resultSet.getInt("character_maximum_length");
                    } else if (type.contains("timestamp")) {
                        datatype = "adql:TIMESTAMP";
                    } else {
                        datatype = "adql:" + type.toUpperCase();
                    }
                } else {
                    throw new UnsupportedOperationException("Database type " + dbType + " not supported");
                }

                Integer arraySize = null; // TODO (v 1.1)

                Column column = new ColumnImpl(dbWrapper, tapSchema, table, columnName, indexed, primaryKey, datatype, size, arraySize);

                allColumns.add(column);
            }
        }

        return allColumns;
    }

    /**
     * Retrieves saved {@code Column}s from the database and add them into the
     * specified {@code TapSchema}.
     */
    protected static void fillSavedColumns(DBWrapper dbWrapper, final TapSchema tapSchema) throws SQLException {

        log.debug("fillSavedColumns");

        SelectQueryBuilder selectQueryBuilder = new SelectQueryBuilder(dbWrapper.getTapSchemaDatabaseType(), tapSchema, TapSchema.COLUMNS_TABLE) {

            @Override
            protected TapSchemaEntity getEntity(ResultSet rs) throws SQLException {
                String tableCompleteName = rs.getString("table_name");
                String columnName = rs.getString("column_name");

                String[] tableNameSplit = tableCompleteName.split(Pattern.quote("."));
                String schemaName = tableNameSplit[0];
                String tableSimpleName = tableNameSplit[1];

                Table table = tapSchema.getChild(schemaName).getChild(tableSimpleName);
                if (table == null) {
                    return null;
                }
                Column column = table.addChild(columnName);
                column.setStatus(Status.ADDED_PERSISTED);
                return column;
            }
        };

        selectQueryBuilder.executeQuery(dbWrapper.getTapSchemaConnection());
    }

    /**
     * Save a new {@code Column} into the TAP_SCHEMA schema.
     */
    protected static void insertNewColumn(DatabaseType dbType, Connection connection, TapSchema tapSchema, Column column) throws SQLException {

        log.debug("insertNewColumn");

        InsertQueryBuilder insertQueryBuilder = new InsertQueryBuilder(dbType, tapSchema, column, TapSchema.COLUMNS_TABLE);
        insertQueryBuilder.executeQuery(connection);
    }

    /**
     * Updates an existing {@code Column}.
     */
    protected static void updateColumn(DatabaseType dbType, Connection connection, TapSchema tapSchema, Column column) throws SQLException {

        UpdateQueryBuilder updateQueryBuilder = new UpdateQueryBuilder(dbType, tapSchema, column, TapSchema.COLUMNS_TABLE, "column_name = ? AND table_name = ?");

        String query = updateQueryBuilder.getQuery();

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

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

            int i = updateQueryBuilder.addStatementValues(statement);
            statement.setString(i, column.getName());
            statement.setString(i + 1, column.getTableCompleteName());

            statement.executeUpdate();
        }
    }
}