import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class benchmark {
    static String inputFile = "C:/SQLite/FrenchDB/database_14_3.sqlite";
    static String outputFile = "C:/SQLite/CreatedDB/benchmarkDB.db";
    static int nbrRow = 1;
    static int nbrCol = 10;
    static int nbrIter = 5;

    public static void main(String[] args) throws SQLException, IOException {
        Connection connInput = DriverManager.getConnection("jdbc:sqlite:" + inputFile);
        Connection connOutput = DriverManager.getConnection("jdbc:sqlite:" + outputFile);

        createDb(connOutput, getStructureDb(connInput), nbrCol);
        String res = executeBenchmark(connInput, connOutput);

        csvWriter(res);
        showGraph();

        connInput.close();
        connOutput.close();
    }

    public static void showGraph() throws IOException {
        String[] command = {"C:\\Users\\ksiry\\AppData\\Local\\Continuum\\anaconda3\\envs\\work_env\\python.exe", "C:\\Users\\ksiry\\Documents\\Java Scripts\\benchmark_insert\\src\\graph.py"};
        ProcessBuilder processBuilder = new ProcessBuilder(command);
        Process process = processBuilder.start();
    }

    public static void csvWriter(String res) throws FileNotFoundException {
        PrintWriter writer = new PrintWriter(new File("C:/Users/ksiry/Desktop/test.csv"));
        StringBuilder builder = new StringBuilder();
        builder.append(res);
        writer.write(builder.toString());
        writer.close();
        System.out.println("Finish !");
    }

    public static String executeBenchmark(Connection connInput, Connection connOutput) throws SQLException {
        String finalRes = null;
        String head = "";
        String batchString = "Batch";
        String exeUpdString = "ExecuteUpdate";

        for (int i = 0; i < 6; i++) {
            long moyBatch = 0;
            long moyExUpd = 0;
            for (int j = 0; j < nbrIter; j++) {
                ArrayList<ResultSet> dataRows = selectRows(connInput, getStructureDb(connInput), nbrRow);
                long res = insertRowsBatch(dataRows, nbrRow, nbrCol, connOutput, getStructureDb(connInput));
                deleteRows(getStructureDb(connInput), connOutput);
                moyBatch = (j * moyBatch + res) / (j + 1);
            }
            for (int j = 0; j < nbrIter; j++) {
                ArrayList<ResultSet> dataRows = selectRows(connInput, getStructureDb(connInput), nbrRow);
                long res = insertRowsExUpd(dataRows, nbrRow, nbrCol, connOutput, getStructureDb(connInput));
                deleteRows(getStructureDb(connInput), connOutput);
                moyExUpd = (j * moyExUpd + res) / (j + 1);
            }
            head += "," + nbrRow;
            batchString += "," + (moyBatch / 1e6);
            exeUpdString += "," + (moyExUpd / 1e6);
            finalRes = head + "\n" + batchString + "\n" + exeUpdString;
            System.out.println("Boucle des " + nbrRow + " Terminée !");
            nbrRow *= 10;
        }

        return finalRes;
    }

    public static ArrayList<ResultSet> getStructureDb(Connection connInput) throws SQLException {
        DatabaseMetaData databaseMetaData = connInput.getMetaData();
        ResultSet tables = databaseMetaData.getTables(null, null, null, new String[] { "TABLE" });
        ResultSet columns = null;
        ArrayList<ResultSet> col = new ArrayList<ResultSet>();

        while (tables.next()) {
            columns = databaseMetaData.getColumns(null, null, tables.getString("TABLE_NAME"), null);
            col.add(columns);
        }

        return col;
    }

    public static String getPrimaryKey(ResultSet col) throws SQLException {
        Connection connInput = DriverManager.getConnection("jdbc:sqlite:" + inputFile);
        DatabaseMetaData databaseMetaData = connInput.getMetaData();
        String pk = "";

        ResultSet primaryKey = databaseMetaData.getPrimaryKeys(null, null, col.getString("TABLE_NAME"));
        if (primaryKey.next()) {
            pk = primaryKey.getString("COLUMN_NAME");
        }

        return pk;
    }

    public static ResultSet getForeignKey(ResultSet col) throws SQLException {
        Connection connInput = DriverManager.getConnection("jdbc:sqlite:" + inputFile);
        DatabaseMetaData databaseMetaData = connInput.getMetaData();

        ResultSet foreignKey = databaseMetaData.getImportedKeys(null, null, col.getString("TABLE_NAME"));

        return foreignKey;
    }

    public static void createDb(Connection connOutput, ArrayList<ResultSet> col, int nbrCol) throws SQLException {
        Statement statement = connOutput.createStatement();
        String query_createdb = null;

        for (int i = 0; i < col.size(); i++) {
            query_createdb = "CREATE TABLE IF NOT EXISTS " + col.get(i).getString("TABLE_NAME") + " (";

            String pk = getPrimaryKey(col.get(i));
            ResultSet foreignKey = getForeignKey(col.get(i));

            ArrayList<String> pk_table = new ArrayList<String>();
            ArrayList<String> pk_col = new ArrayList<String>();
            ArrayList<String> fk_col = new ArrayList<String>();

            ArrayList<String> cur_col = new ArrayList<String>();

            int curCol = 0;

            while (foreignKey.next()) {
                pk_table.add(foreignKey.getString("PKTABLE_NAME"));
                pk_col.add(foreignKey.getString("PKCOLUMN_NAME"));
                fk_col.add(foreignKey.getString("FKCOLUMN_NAME"));
            }

            while (col.get(i).next() && curCol < nbrCol) {
                String auto_incr = "";
                String prim_key = "";
                String references = "";
                String nullable = "";

                cur_col.add(col.get(i).getString("COLUMN_NAME"));

                if (col.get(i).getString("IS_AUTOINCREMENT").equals("YES")) {
                    auto_incr = " ASC AUTOINCREMENT";
                }

                if (col.get(i).getString("IS_NULLABLE").equals("NO")) {
                    nullable = " NOT NULL";
                }

                if (col.get(i).getString("COLUMN_NAME").equals(pk)) {
                    prim_key = " PRIMARY KEY";
                }

                for (int j = 0; j < fk_col.size(); j++) {
                    if (col.get(i).getString("COLUMN_NAME").equals(fk_col.get(j))) {
                        references = " REFERENCES " + pk_table.get(j) + " (" + pk_col.get(j) + ")";
                    }
                }

                query_createdb += "[" + col.get(i).getString("COLUMN_NAME") + "]" + " "
                        + col.get(i).getString("TYPE_NAME") + prim_key + auto_incr + references + nullable + ", ";
                curCol++;
            }
            query_createdb = query_createdb.substring(0, query_createdb.length() - 2);
            query_createdb += ");";

            statement.executeUpdate(query_createdb);

            foreignKey.close();
        }

        statement.close();
    }

    public static ArrayList<ResultSet> selectRows(Connection connInput, ArrayList<ResultSet> col, int nbrRow) throws SQLException {
        Statement statement = connInput.createStatement();
        ResultSet data = null;
        ArrayList<ResultSet> dataRows = new ArrayList<ResultSet>();

        for (int i = 0; i < col.size(); i++) {
            String nRow = " LIMIT " + (nbrRow * 10);

            String query_selectrows = "SELECT * FROM " + col.get(i).getString("TABLE_NAME") + nRow;
            data = statement.executeQuery(query_selectrows);
            dataRows.add(data);
        }

        return dataRows;
    }

    public static long insertRowsBatch(ArrayList<ResultSet> dataRows, int nbrRow, int nbrCol, Connection connOutput, ArrayList<ResultSet> col) throws SQLException {
        ArrayList<String> cur_col = new ArrayList<String>();
        long time = 0;
        String prefix = "";

        for (int i = 0; i < col.size(); i++) {
            StringBuilder query_insertrows = new StringBuilder();
            query_insertrows.append("INSERT INTO " + col.get(i).getString("TABLE_NAME") + " (");

            while (col.get(i).next()) {
                cur_col.add(col.get(i).getString("COLUMN_NAME"));
            }

            prefix = "";
            for (int j = 0; j < cur_col.size() && j < nbrCol; j++) {
                query_insertrows.append(prefix);
                prefix = ",";
                query_insertrows.append("[" + cur_col.get(j) + "]");
            }
            query_insertrows.append(") VALUES (");
            prefix = "";
            for (int j = 0; j < cur_col.size() && j < nbrCol; j++) {
                query_insertrows.append(prefix);
                prefix = ",";
                query_insertrows.append("?");
            }
            query_insertrows.append(")");

            PreparedStatement preparedStatement = connOutput.prepareStatement(query_insertrows.toString());
            int count = 0;

            while (dataRows.get(i).next()) {
                long startTime = 0;
                long endTime = 0;

                for (int j = 0; j < cur_col.size() && j < nbrCol; j++) {
                    String str = dataRows.get(i).getString(cur_col.get(j));

                    if (str != null) {
                        preparedStatement.setString(j + 1, str);
                    } else {
                        preparedStatement.setString(j + 1, null);
                    }
                }

                startTime = System.nanoTime();
                preparedStatement.addBatch();
                endTime = System.nanoTime();

                time += (endTime - startTime);

                startTime = 0;
                endTime = 0;

                if (count > nbrRow) {
                    connOutput.setAutoCommit(false);
                    startTime = System.nanoTime();
                    preparedStatement.executeBatch();
                    endTime = System.nanoTime();
                    connOutput.setAutoCommit(true);

                    count = 0;
                } else {
                    count++;
                }
                time += (endTime - startTime);
            }
            connOutput.setAutoCommit(false);
            preparedStatement.executeBatch();
            connOutput.setAutoCommit(true);
        }

        return time;
    }

    public static long insertRowsExUpd(ArrayList<ResultSet> dataRows, int nbrRow, int nbrCol, Connection connOutput, ArrayList<ResultSet> col) throws SQLException {
        ArrayList<String> cur_col = new ArrayList<String>();
        long time = 0;
        Statement statement = connOutput.createStatement();
        String prefix = "";

        for (int i = 0; i < col.size(); i++) {
            StringBuilder query_insertrows = new StringBuilder();
            query_insertrows.append("INSERT INTO " + col.get(i).getString("TABLE_NAME") + " (");

            while (col.get(i).next()) {
                cur_col.add(col.get(i).getString("COLUMN_NAME"));
            }

            prefix = "";
            for (int j = 0; j < cur_col.size() && j < nbrCol; j++) {
                query_insertrows.append(prefix);
                prefix = ",";
                query_insertrows.append("[" + cur_col.get(j) + "]");
            }
            query_insertrows.append(") VALUES (");

            StringBuilder query_insert = query_insertrows;
            int count = 0;

            while (dataRows.get(i).next()) {
                long startTime = 0;
                long endTime = 0;

                prefix = "";
                for (int j = 0; j < cur_col.size() && j < nbrCol; j++) {
                    String str = dataRows.get(i).getString(cur_col.get(j));
                    query_insertrows.append(prefix);
                    prefix = ",";
                    if(str != null)
                    {
                        query_insert.append("'" + str.replace("'", "''") + "'");
                    }
                    else
                    {
                        query_insert.append("''");
                    }
                }
                query_insert.append(")");

                if(count > nbrRow)
                {
                    startTime = System.nanoTime();
                    statement.executeUpdate(query_insert.toString());
                    endTime = System.nanoTime();
                    time += (endTime - startTime);
                    query_insert = query_insertrows;
                    count = 0;
                    query_insert.append(",(");
                }
                else
                {
                    query_insert.append(",(");
                }
                count++;
            }
        }

        return time;
    }


    public static void deleteRows(ArrayList<ResultSet> col, Connection connOutput) throws SQLException {
        Statement statement = connOutput.createStatement();

        for (int i = 0; i < col.size(); i++) {
            String query_deleterows = "DELETE FROM " + col.get(i).getString("TABLE_NAME");
            statement.executeUpdate(query_deleterows);
        }

        statement.close();
    }

}