/*   Copyright 2014 BossLetsPlays(Matthew Rogers), TheJeterLP (Joey Peter)
 *
 *   Licensed under the Apache License, Version 2.0 (the "License");
 *   you may not use this file except in compliance with the License.
 *   You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 *   Unless required by applicable law or agreed to in writing, software
 *   distributed under the License is distributed on an "AS IS" BASIS,
 *   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *   See the License for the specific language governing permissions and
 *   limitations under the License.
 */
package com.blp.minotaurus.utils;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.blp.minotaurus.Minotaurus;
import com.blp.minotaurus.bukkit.MessageManager;
import com.blp.minotaurus.database.Database;

/**
 * <strong>Project:</strong> Minotaurus <br>
 *
 * <strong>Class:</strong> SqlHelper
 *
 * @author <a href = "http://youtube.com/BossLetsPlays"> BossLetsPlays</a>
 *
 */
public class SqlHelper {

    private static Database db;
    private final static String separator = ", "; //NOTE: The separator contains a space already!
    private final static char keyMarker = '`';
    private final static char placeHolder = '?';

    /**
     * Precautionary method to initialize database to ensure it is not null
     */
    public static void init() {
        db = Minotaurus.getDB();
    }

    /**
     * Inserts values into the specified table if the table does not already contain the primary key's value
     *
     * @param tableName The name of the table. <strong>Note:</strong> On creation of the table, the name must have ` surrounding it, however when using this method, exclude the `
     * @param keys An <code>ArrayList</code> of the table data names, again with the ` excluded
     * @param values An <code>ArrayList</code> of <code>Object</code>s to be inserted into its corresponding key (the order they are added to the array defines this).
     * Supported data types: <code>String, Integer/int, Double/double</code>
     * @param primaryIndex The primary key's index, if the key is the first parameter, index = 0
     * @throws Exception when the array length of <code>keys</code> is not equal to the length of <code>values</code>
     */
    public static void insert(String tableName, List<String> keys, List<Object> values, int primaryIndex) throws Exception {
        if (keys.size() != values.size()) throw new Exception("The number of keys(" + keys.size() + ") must match the number of values(" + values.size() + ") !");
        if (keys.isEmpty() || values.isEmpty()) return;
        if (exists(tableName, keys.get(primaryIndex), values.get(primaryIndex))) return;
        StringBuilder query = new StringBuilder("INSERT INTO " + keyMarker + tableName + keyMarker + " (");
        for (int i = 0; i < keys.size(); i++) {
            if (i == keys.size() - 1) query.append(keyMarker).append(keys.get(i)).append(keyMarker).append(")");
            else query.append(keyMarker).append(keys.get(i)).append(keyMarker).append(separator);
        }

        query.append(" VALUES (");

        for (int i = 0; i < values.size(); i++) {
            if (i == values.size() - 1) query.append(placeHolder).append(");");
            else query.append(placeHolder).append(separator);
        }

        final PreparedStatement statement = db.getPreparedStatement(query.toString());

        for (int i = 1; i <= values.size(); i++) {
            if (values.get(i) instanceof String) statement.setString(i, (String) values.get(i));
            else if (values.get(i) instanceof Integer) statement.setInt(i, (Integer) values.get(i));
            else if (values.get(i) instanceof Double) statement.setDouble(i, (Double) values.get(i));
        }

        MessageManager.debug(query.toString());
        statement.executeUpdate();
        db.closeStatement(statement);
    }

    /**
     * Updates the table
     *
     * @param tableName The table being updated
     * @param key The key to update
     * @param value The value to set the key to
     * @param primaryKey The primary key of the table
     * @param primaryValue The primary value where this update will occur
     */
    public static void update(String tableName, String key, String value, String primaryKey, String primaryValue) {
        try {
            PreparedStatement statement = db.getPreparedStatement("UPDATE " + keyMarker + tableName + keyMarker + " SET " + keyMarker + key + keyMarker + " = ?"
                    + " WHERE " + keyMarker + primaryKey + keyMarker + " = ?;");
            statement.setString(1, value);
            statement.setString(2, primaryValue);
            statement.executeUpdate();
            db.closeStatement(statement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Updates the table
     *
     * @param tableName The table being updated
     * @param key The key to update
     * @param value The value to set the key to
     * @param primaryKey The primary key of the table
     * @param primaryValue The primary value where this update will occur
     */
    public static void update(String tableName, String key, double value, String primaryKey, double primaryValue) {
        try {
            PreparedStatement statement = db.getPreparedStatement("UPDATE " + keyMarker + tableName + keyMarker + " SET " + keyMarker + key + keyMarker + " = ?"
                    + " WHERE " + keyMarker + primaryKey + keyMarker + " = ?;");
            statement.setDouble(1, value);
            statement.setDouble(2, primaryValue);
            statement.executeUpdate();
            db.closeStatement(statement);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Updates the table
     *
     * @param tableName The table being updated
     * @param key The key to update
     * @param value The value to set the key to
     * @param primaryKey The primary key of the table
     * @param primaryValue The primary value where this update will occur
     */
    public static void update(String tableName, String key, Object value, String primaryKey, Object primaryValue) {
        try {
            PreparedStatement statement = db.getPreparedStatement("UPDATE " + keyMarker + tableName + keyMarker + " SET " + keyMarker + key + keyMarker + " = ?"
                    + " WHERE " + keyMarker + primaryKey + keyMarker + " = ?;");
            if (value instanceof String) statement.setString(1, (String) value);
            else if (value instanceof Integer) statement.setInt(1, (Integer) value);
            else if (value instanceof Double) statement.setDouble(1, (Double) value);
            if (primaryValue instanceof String) statement.setString(2, (String) primaryValue);
            else if (primaryValue instanceof Integer) statement.setInt(2, (Integer) primaryValue);
            else if (primaryValue instanceof Double) statement.setDouble(2, (Double) primaryValue);
            statement.executeUpdate();
            db.closeStatement(statement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Checks if the value already exists in the table
     *
     * @param tableName The table to look in for the value, exclude the `
     * @param key The data name that the value might or might not belong to, exclude the `
     * @param value The value to look for. Supported data types: <code>String, Integer/int, Double/double</code>
     * @return true if the value exists in the table as the key's value
     */
    public static boolean exists(String tableName, String key, Object value) {
        try {
            final PreparedStatement statement = db.getPreparedStatement("SELECT " + keyMarker + key + keyMarker + " FROM " + keyMarker + tableName + keyMarker
                    + " WHERE " + keyMarker + key + keyMarker + " = ?;");
            if (value instanceof String) statement.setString(1, (String) value);
            else if (value instanceof Integer) statement.setInt(1, (Integer) value);
            else if (value instanceof Double) statement.setDouble(1, (Double) value);
            final ResultSet rs = statement.executeQuery();
            boolean next = rs.next();
            db.closeStatement(statement);
            db.closeResultSet(rs);
            return next;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * Deletes the data set from the table
     *
     * @param tableName The table the value is stored in, exclude the `
     * @param key The primary key that will have its values deleted, exclude the `
     * @param value The value the primary key contains, exclude the `
     */
    public static void delete(String tableName, String key, Object value) {
        try {
            final PreparedStatement statement = db.getPreparedStatement("DELETE FROM " + keyMarker + tableName + keyMarker + " WHERE " + keyMarker + key + keyMarker + " = ?;");
            if (value instanceof String) statement.setString(1, (String) value);
            else if (value instanceof Integer) statement.setInt(1, (Integer) value);
            else if (value instanceof Double) statement.setDouble(1, (Double) value);
            statement.executeUpdate();
            db.closeStatement(statement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * @param tableName The table to obtain the value from, exclude the `
     * @param key The key of the value to obtain, exclude the `
     * @param primaryKey The primary key of the data set to get the value from, exclude the `
     * @param primaryValue The value of the primary key being checked, exclude the `
     * @return the value located at the key in the table
     */
    public static String getString(String tableName, String key, String primaryKey, Object primaryValue) {
        try {
            final PreparedStatement statement = db.getPreparedStatement("SELECT " + keyMarker + key + keyMarker + " FROM " + keyMarker + tableName + keyMarker
                    + " WHERE " + keyMarker + primaryKey + keyMarker + " = ?;");
            if (primaryValue instanceof String) statement.setString(1, (String) primaryValue);
            else if (primaryValue instanceof Integer) statement.setInt(1, (Integer) primaryValue);
            else if (primaryValue instanceof Double) statement.setDouble(1, (Double) primaryValue);
            final ResultSet rs = statement.executeQuery();
            String result = "";
            if (rs.next()) result = rs.getString(key);
            db.closeStatement(statement);
            db.closeResultSet(rs);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            return "NULL";
        }
    }

    /**
     * @param tableName The table to obtain the value from, exclude the `
     * @param key The key of the value to obtain, exclude the `
     * @param primaryKey The primary key of the data set to get the value from, exclude the `
     * @param primaryValue The value of the primary key being checked, exclude the `
     * @return the value located at the key in the table
     */
    public static double getDouble(String tableName, String key, String primaryKey, Object primaryValue) {
        try {
            final PreparedStatement statement = db.getPreparedStatement("SELECT " + keyMarker + key + keyMarker + " FROM " + keyMarker + tableName + keyMarker
                    + " WHERE " + keyMarker + primaryKey + keyMarker + " = ?;");
            if (primaryValue instanceof String) statement.setString(1, (String) primaryValue);
            else if (primaryValue instanceof Integer) statement.setInt(1, (Integer) primaryValue);
            else if (primaryValue instanceof Double) statement.setDouble(1, (Double) primaryValue);
            final ResultSet rs = statement.executeQuery();

            double result = -1.0;
            if (rs.next())
                result = rs.getDouble(key);
            db.closeStatement(statement);
            db.closeResultSet(rs);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0.0;
        }
    }

    /**
     * @param tableName The table to obtain the value from, exclude the `
     * @param key The key of the value to obtain, exclude the `
     * @param primaryKey The primary key of the data set to get the value from, exclude the `
     * @param primaryValue The value of the primary key being checked, exclude the `
     * @return the value located at the key in the table
     */
    public static int getInt(String tableName, String key, String primaryKey, Object primaryValue) {
        try {
            final PreparedStatement statement = db.getPreparedStatement("SELECT " + keyMarker + key + keyMarker + " FROM " + keyMarker + tableName + keyMarker
                    + " WHERE " + keyMarker + primaryKey + keyMarker + " = ?;");
            if (primaryValue instanceof String) statement.setString(1, (String) primaryValue);
            else if (primaryValue instanceof Integer) statement.setInt(1, (Integer) primaryValue);
            else if (primaryValue instanceof Double) statement.setDouble(1, (Double) primaryValue);
            final ResultSet rs = statement.executeQuery();
            int result = -1;
            if (rs.next()) result = rs.getInt(key);
            db.closeStatement(statement);
            db.closeResultSet(rs);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }

}
