import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Database {

    private static String database, instance, username, password, jdbcUrl;
    private static Connection connection;
    private static String[] optionColumns;

    static {
        database = "OptionsSpreadsheet";
        instance = "optionsspreadsheetprogram:us-central1:optionsspreadsheet";
        username = "root";
        password = "Olibslic1234";
        jdbcUrl = String.format(
                "jdbc:mysql://google/%s?cloudSqlInstance=%s&"
                        + "socketFactory=com.google.cloud.sql.mysql.SocketFactory", database, instance);

        try {
            connection = DriverManager.getConnection(jdbcUrl, username, password);

            String getColumnQuery = "SHOW COLUMNS FROM optionsQuotes";
            List<HashMap> queryResult = query(getColumnQuery);

            optionColumns = new String[queryResult.size()];
            for(int i = 0; i < queryResult.size(); i++) {
                optionColumns[i] = queryResult.get(i).get("COLUMN_NAME").toString();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static List<HashMap> query(String query) throws SQLException {
        ArrayList results = new ArrayList();

        if(connection != null && connection.isValid(0)) {
            try(Statement statement = connection.createStatement()) {
                ResultSet resultSet = statement.executeQuery(query);
                ResultSetMetaData md = resultSet.getMetaData();
                int columnCount = md.getColumnCount();

                while(resultSet.next()) {
                    HashMap row = new HashMap(columnCount);
                    for(int i = 1; i<=columnCount; ++i) {
                        row.put(md.getColumnName(i), resultSet.getObject(i));
                    }
                    results.add(row);
                }
                return results;
            }
        }
        System.out.println("CONNECTION NOT FORMED");
        return null;
    }

    public static void insertOptionList(List<HashMap> options) throws Exception {
        clearTable("optionsQuotes");

        String putCommand = "INSERT INTO optionsQuotes VALUES (";

        for(int i = 0; i < optionColumns.length-1; i++) {
            putCommand += "?, ";
        }
        putCommand += "?)";

        PreparedStatement putStatement = connection.prepareStatement(putCommand);

        for(HashMap option: options) {
            for(int i = 1; i <= optionColumns.length; i++) {
                putStatement.setObject(i, option.get(optionColumns[i-1]));
            }
            putStatement.addBatch();
            putStatement.clearParameters();
        }

        putStatement.executeBatch();
    }

    public static void clearTable(String table) throws SQLException {
        if(connection != null && connection.isValid(0)) {
            try(Statement statement = connection.createStatement()) {
                statement.executeUpdate("TRUNCATE " + table);
            }
        }
    }

    public static void insertValues(String table, String[] values) throws Exception{
        String command = "INSERT INTO " + table + " VALUES (";

        for(String value: values) {
            command += value + ", ";
        }

        command += ")";

        if(connection != null && connection.isValid(0)) {
            try(Statement statement = connection.createStatement()) {
                statement.executeUpdate(command);
            }
        }
    }
}