package Engine;

import java.sql.*; // for standard JDBC programs
import java.util.*;

public class DBConnection {

    private static String url = "jdbc:mysql://185.13.227.167:3306/michiai147_domin";
    private static String user = "michiai147_domin";
    private static String pass = "groep1domin2016";
    private static Connection connection;
    private ResultSet resultset;
    private PreparedStatement prepStatemnt;
    private String[] arrayResults;
    private ArrayList<String[]> data = new ArrayList<>();
    private boolean control = false;

    public DBConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        connectDB();
    }

    private void connectDB() throws SQLException {
        connection = DriverManager.getConnection(url, user, pass);
    }

    public void dataToArrayList(ResultSet result) throws SQLException {
        ResultSetMetaData metaData = resultset.getMetaData();
        int columns = metaData.getColumnCount()+1;

        while (resultset.next()) {
            arrayResults = new String[columns-1];
            for (int i = 1; i < columns; i++) {
                arrayResults[i-1] = resultset.getString(i);
                if (i == metaData.getColumnCount()) {
                    data.add(arrayResults);
                }
            }
        }
    }

    private void setSelectQuery(String table, String columns, String where) throws SQLException {
        prepStatemnt = connection.prepareStatement("SELECT " + columns + " "
                + "FROM " + table + " "
                + (!table.equals("Cards") ? "" : "JOIN CardSets ON Cards.SetID = CardSets.SetID ")
						                		+ "JOIN CardTypes ON Cards.TypeID = CardTypes.TypeID "
        		+ (!columns.contains("Effect") ? "" : "JOIN CardEffects ON Cards.CardID = CardEffects.Card JOIN Effects ON CardEffects.Effects = Effects.IDEffect ")
                + (where.equals("") ? "" : "WHERE " + where));

        System.out.println(prepStatemnt);
        dataToArrayList(this.resultset = prepStatemnt.executeQuery());
        close();
    }

    public ArrayList<String[]> getSelectQuery(String table, String columns, String where) throws SQLException {
        if (data.isEmpty()) {
            connectDB();
            setSelectQuery(table, columns, where);
            close();
        } else {
            data.clear();
            getSelectQuery(table, columns, where);
        }
        return data;
    }

    private void setUpdateQuery(String table, String set, String where) throws SQLException {
        prepStatemnt = connection.prepareStatement("UPDATE " + table + " "
                + "SET " + set + " "
                + (where.equals("") ? "" : "WHERE " + where));
        int check = prepStatemnt.executeUpdate();
        if (check > 0) {
            this.control = true;
        }
        close();
    }

    public boolean executeUpdateQuery(String table, String set, String where) throws SQLException {
        connectDB();
        setUpdateQuery(table, set, where);
        close();
        return control;
    }

    private void setInsertQuery(String table, String columns, String values) throws SQLException {
        prepStatemnt = connection.prepareStatement("INSERT INTO " + table + " (" + columns + ") VALUES(" + values + ")");
        int check = prepStatemnt.executeUpdate();
        if (check > 0) {
            control = true;
        }
        close();
    }

    public boolean executeInsertQuery(String table, String columns, String values) throws SQLException {
        setInsertQuery(table, columns, values);
        return control;
    }

    public void close() {
        try {
            if (resultset != null) {
                resultset.close();
            }

            if (prepStatemnt != null) {
                prepStatemnt.close();
            }

            if (connection != null) {
                connection.close();
            }
        } catch (Exception e) {

        }
    }
}