import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

import org.bukkit.configuration.ConfigurationSection;

import com.google.common.base.Joiner;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import us.opalprison.core.OPalCore;
import us.opalprison.core.utils.Lambda;

public class MySQL {

    private static MySQL instance;
    private Connection connection;
    private OPalCore plugin;

    public MySQL(OPalCore plugin) {
        this.plugin = plugin;
        instance = this;
    }

    /**
     * Gets a map of values of length num. If orderedBy is specified results
     * will be ordered descending. Advanced selection allows for the database to
     * do division or multiplication but should not be used to try to obtain
     * extra columns.
     *
     * Keys and values in the returned map should never be null.
     *
     * @param table table to access
     * @param advancedSelection column or calculated value to get
     * @param orderedBy column or equation to order by
     * @param num number of rows to get
     * @return
     */
    public <K> Map<K, Object> getRows(Table table, Column<K> keyColumn,
                                      String advancedSelection, String orderedBy, int num) {
        String query =
                "SELECT " + keyColumn + "," + advancedSelection + " FROM " + table;
        if (orderedBy != null) {
            query += " ORDER BY " + orderedBy + " DESC";
        }
        query += " LIMIT " + num;

        checkConnectionOpen();

        try (Statement s = connection.createStatement()) {
            ResultSet rs = s.executeQuery(query);
            Map<K, Object> result = Maps.newLinkedHashMap();
            while (rs.next()) {
                K key = keyColumn.dataType().deserialize(rs.getObject(1));
                Object value = rs.getObject(2);
                if (value == null) {
                    continue;
                }

                result.put(key, value);
            }
            return result;
        } catch (SQLException e) {
            plugin.getLogger().severe("Error with MySQL: " + e.getMessage());
        }

        return null;
    }

    /**
     * Retrieves a value from the database which is related to a certain key
     *
     * @param targetColumn column containing the desired value
     * @param table table to access
     * @param keyColumn column containing the key
     * @param key key
     * @return value if it exists otherwise null
     */
    public <K, V> V select(Column<V> targetColumn, Table table,
                           Column<K> keyColumn, K key) {
        String query =
                "SELECT " + targetColumn + " FROM " + table + " WHERE " + keyColumn
                        + " = '" + keyColumn.dataType().serialize(key) + "' LIMIT 1";

        checkConnectionOpen();

        try (Statement s = connection.createStatement()) {
            ResultSet rs = s.executeQuery(query);
            while (rs.next()) {
                Object o = rs.getObject(1);
                return o == null ? null
                        : targetColumn.dataType().deserialize(o);
            }
        } catch (SQLException e) {
            plugin.getLogger().severe("Error with MySQL: " + e.getMessage());
        }

        return null;
    }

    public List<List<Object>> selectAll(Table table) {
        String query = "SELECT * FROM " + table;
        checkConnectionOpen();
        List<List<Object>> rows = Lists.newArrayList();
        try (Statement s = connection.createStatement()) {
            ResultSet rs = s.executeQuery(query);
            while (rs.next()) {
                rows.add(IntStream.range(0, rs.getMetaData().getColumnCount())
                        .mapToObj(
                                Lambda.ofInt(rs::getObject, this::handleException))
                        .collect(Collectors.toList()));
            }
        } catch (SQLException e) {
            handleException(e);
        }

        return rows;
    }

    public void handleException(Exception e) {
        plugin.getLogger().severe("Error with MySQL: " + e.getMessage());
    }

    public <K, V> void update(Table table, Column<V> valueColumn, V value,
                              Column<K> keyColumn, K key) {
        String update = "UPDATE " + table + " SET " + valueColumn
                + " = ? WHERE " + keyColumn + " = ?";

        checkConnectionOpen();

        try (PreparedStatement s = connection.prepareStatement(update)) {
            s.setObject(1, valueColumn.dataType().serialize(value));
            s.setObject(2, keyColumn.dataType().serialize(key));
            s.executeUpdate();
        } catch (SQLException e) {
            plugin.getLogger().severe("Error with MySQL: " + e.getMessage());
        }
    }

    public <K> void deleteRow(Table table, Column<K> keyColumn, K key) {
        String update = "DELETE FROM " + table + " WHERE " + keyColumn + " = "
                + keyColumn.dataType().serialize(key);
        checkConnectionOpen();
        try (PreparedStatement s = connection.prepareStatement(update)) {
            s.executeUpdate();
        } catch (SQLException e) {
            handleException(e);
        }
    }

    public <K> void makeNewRow(Table table, Column<K> keyColumn, K key) {
        checkConnectionOpen();
        try (Statement s = connection.createStatement()) {
            String query =
                    "SELECT " + keyColumn + " FROM " + table + " WHERE " + keyColumn
                            + " = '" + keyColumn.dataType().serialize(key) + "'";

            ResultSet rs = s.executeQuery(query);
            if (!rs.next()) {
                String columnNames = Joiner.on(", ").join(table.getColumns());
                String values = table.getColumns().stream().map(c -> {
                    if (c.equals(keyColumn)) {
                        return keyColumn.dataType().serialize(key);
                    }

                    return c.dataType().serializedDef();
                }).map(o -> "'" + o.toString() + "'")
                        .reduce((o1, o2) -> o1 + ", " + o2).orElse("");
                query = "INSERT INTO " + table + " (" + columnNames
                        + ") VALUES (" + values + ")";

                s.executeUpdate(query);
            }
        } catch (SQLException e) {
            plugin.getLogger().severe("Error with MySQL: " + e.getMessage());
        }
    }

    public void createTable(Table table) {
        checkConnectionOpen();
        List<String> namesWithTypes = table.getNamesWithTypes();
        executeUpdate("CREATE TABLE IF NOT EXISTS " + table + " ("
                + Joiner.on(", ").join(namesWithTypes) + ")");
        namesWithTypes.stream().forEach(s -> {
            String value = table.getColumns().stream()
                    .filter(c -> c.name().equalsIgnoreCase(s.split(" ")[0]))
                    .map(c -> c.dataType().serializedDef().toString()).findAny()
                    .orElse("''");
            executeUpdate("ALTER TABLE " + table + " ADD COLUMN " + s
                    + " DEFAULT " + value);
        });
    }

    private void executeUpdate(String update) {
        try (Statement s = connection.createStatement()) {
            s.executeUpdate(update);
        } catch (SQLException e) {
            plugin.getLogger().severe("Error with MySQL: " + e.getMessage());
        }
    }

    private void checkConnectionOpen() {
        try {
            if (connection == null || connection.isClosed()) {
                openConnection();
            }
        } catch (SQLException e1) {

        }
    }

    private void openConnection() {
        closeConnection();

        ConfigurationSection c =
                plugin.getConfig().getConfigurationSection("database");

        String host = c.getString("host");
        String port = c.getString("port");
        String name = c.getString("name");
        String user = c.getString("user");
        String pass = c.getString("pass");

        try {
            connection = DriverManager.getConnection(
                    "jdbc:mysql://" + host + ":" + port + "/" + name, user, pass);
        } catch (SQLException e) {
            plugin.getLogger().severe("Error with MySQL: " + e.getMessage());
        }
    }

    public void closeConnection() {
        if (connection == null) {
            return;
        }

        try {
            connection.close();
        } catch (SQLException e) {
            plugin.getLogger().severe("Error with MySQL: " + e.getMessage());
        }

        connection = null;
    }

    public static MySQL getInstance() {
        return instance;
    }

}