package derbytest;

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.Date;
import java.util.Properties;

/**
 * one very FAT class for testing purposes
 */
public class FatTest {

    private String framework = "embedded";
    private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
    private String protocol = "jdbc:derby:";

    static public void main(String[] args) {
        new FatTest().run();
    }

    /**
     * loading driver (getted from Derby examples)
     */
    private void loadDriver(String driver) {
        try {
            Class.forName(driver).newInstance();
            System.out.println("Loaded the appropriate driver");
        } catch (ClassNotFoundException cnfe) {
            System.err.println("\nUnable to load the JDBC driver " + driver);
            System.err.println("Please check your CLASSPATH.");
            cnfe.printStackTrace(System.err);
        } catch (InstantiationException ie) {
            System.err.println(
                    "\nUnable to instantiate the JDBC driver " + driver);
            ie.printStackTrace(System.err);
        } catch (IllegalAccessException iae) {
            System.err.println(
                    "\nNot allowed to access the JDBC driver " + driver);
            iae.printStackTrace(System.err);
        }
    }

    /**
     * printing SQL exceprion (getted from Derby examples)
     * @param e - exception
     */
    private void printSQLException(SQLException e) {
        // Unwraps the entire exception chain to unveil the real cause of the
        // Exception.
        while (e != null) {
            System.err.println("\n----- SQLException -----");
            System.err.println("  SQL State:  " + e.getSQLState());
            System.err.println("  Error Code: " + e.getErrorCode());
            System.err.println("  Message:    " + e.getMessage());
            e.printStackTrace();
            // for stack traces, refer to derby.log or uncomment this:
            //e.printStackTrace(System.err);
            e = e.getNextException();
        }
    }

    /**
     * starting derby database (getted from Derby examples)
     */
    private Connection startDB() throws SQLException {
        Properties props = new Properties(); // connection properties
        // providing a user name and password is optional in the embedded
        // and derbyclient frameworks
        props.put("user", "user1");
        props.put("password", "user1");

        String dbName = "derbyDB"; // the name of the database
        Connection conn = DriverManager.getConnection(protocol + dbName + ";create=true", props);
//            conn = DriverManager.getConnection(protocol + dbName + " ", props);
        System.out.println("Connected to and created database " + dbName);
        return conn;
    }

    /**
     * stopping DB (getted from Derby examples)
     */
    private void stopDB() {
        if (framework.equals("embedded")) {
            try {
                // the shutdown=true attribute shuts down Derby
                DriverManager.getConnection("jdbc:derby:;shutdown=true");

            // To shut down a specific database only, but keeep the
            // engine running (for example for connecting to other
            // databases), specify a database in the connection URL:
            //DriverManager.getConnection("jdbc:derby:" + dbName + ";shutdown=true");
            } catch (SQLException se) {
                if (((se.getErrorCode() == 50000) && ("XJ015".equals(se.getSQLState())))) {
                    // we got the expected exception
                    System.out.println("Derby shut down normally");
                // Note that for single database shutdown, the expected
                // SQL state is "08006", and the error code is 45000.
                } else {
                    // if the error code or SQLState is different, we have
                    // an unexpected exception (shutdown failed)
                    System.err.println("Derby did not shut down normally");
                    printSQLException(se);
                }
            }
        }
    }
    private String[][] createSchemaCommands = {
        {"create table TESTBIG " +
            "( " +
            "  CLIENT       int not null, " +
            "  ORDER_ID     int not null, " +
            "  ORDER_AMOUNT int not null " +
            ")", "Created table TESTBIG"
        },
        {"alter table TESTBIG add constraint TESTBIG_PK primary key (CLIENT, ORDER_ID)", "Alter table TESTBIG"},
        {"create table TESTTOTALS " +
            "( " +
            "  CLIENT       int not null, " +
            "  CLIENT_TOTAL int " +
            ") ", "Created table TESTTOTALS"
        },
        {"alter table TESTTOTALS add constraint TESTTOTALS_PK primary key (CLIENT)", "Alter table TESTTOTALS"},
        {" CREATE PROCEDURE calculateTotal() \n" +
            " PARAMETER STYLE JAVA " +
            " MODIFIES SQL DATA LANGUAGE JAVA EXTERNAL NAME \n" +
            "'derbytest.FatTest.calculateTotalStored'", "Ыtored procedure created"
        },
    };
    private String[][] dropSchemaCommands = {
        {"DROP TABLE testbig", "DROP TABLE testbig"},
        {"drop table TESTTOTALS", "drop table TESTTOTALS"},
        {"DROP PROCEDURE calculateTotal", "DROP PROCEDURE calculateTotal"},
    };
    private String[][] increaseRowNumberCommands = {
        {"INSERT INTO USER1.TESTBIG " +
            "SELECT client, order_id + (select max(order_id) from USER1.TESTBIG) + 1, order_amount " +
            "FROM USER1.TESTBIG", "doubling amount of records in TESTBIG table"
        },
    };
    final int TOTALRECORDS = 250000;
    final int DEVIDER = 10;
    final int SHIFT = DEVIDER / 2 + 1;

    private void insertData(PreparedStatement ps) throws SQLException {

        int client;
        int order_id;
        int order_amount;
        for (int i = 0; i < TOTALRECORDS; i++) {
            client = (i / DEVIDER + 1);
            order_id = (i % DEVIDER);
            order_amount = (i % DEVIDER - SHIFT);

            ps.setInt(1, client);
            ps.setInt(2, order_id);
            ps.setInt(3, order_amount);
            ps.executeUpdate();
        }

    }

    private void populateDB(Connection conn, Statement s) throws SQLException {
        if (work_type == WORK_TYPE.DATA_DROP_AND_CREATE || work_type == WORK_TYPE.DATA_FROM_SCRATCH) {
            if (work_type == WORK_TYPE.DATA_DROP_AND_CREATE) {
                for (String[] sqlCommand : dropSchemaCommands) {
                    s.execute(sqlCommand[0]);
                    System.out.println(sqlCommand[1]);
                }
            }

            for (String[] sqlCommand : createSchemaCommands) {
                s.execute(sqlCommand[0]);
                System.out.println(sqlCommand[1]);
            }

            conn.commit();

            String sql = "INSERT INTO testbig VALUES(?, ?, ?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            insertData(ps);
            conn.commit();

            for (String[] sqlCommand : increaseRowNumberCommands) {
                s.execute(sqlCommand[0]);
                System.out.println(sqlCommand[1]);
            }
            for (String[] sqlCommand : increaseRowNumberCommands) {
                s.execute(sqlCommand[0]);
                System.out.println(sqlCommand[1]);
            }
            conn.commit();
        }
    }

    /**
     * main function, that do all stuff.
     */
    public void run() {
        System.out.println("SimpleApp starting in " + framework + " mode");

        loadDriver(driver);

        Connection conn;
        /* This ArrayList usage may cause a warning when compiling this class
         * with a compiler for J2SE 5.0 or newer. We are not using generics
         * because we want the source to support J2SE 1.4.2 environments. */
//        PreparedStatement psInsert;
//        PreparedStatement psUpdate;
        Statement s;
//        ResultSet rs;
        try {
            conn = startDB();
            conn.setAutoCommit(false);
            s = conn.createStatement();

            populateDB(conn, s);
            System.out.println("Database populated");

            calculation(conn, s);

            stopDB();

        } catch (SQLException sqle) {
            printSQLException(sqle);
        }
    }

    enum WORK_TYPE {

        /**
         * defines should we create tables and procuderes from SQL-scripts
         */
        DATA_FROM_SCRATCH,
        /**
         * defines should we drop previous created tables and procuderes
         * and then create new ones
         */
        DATA_DROP_AND_CREATE,
        /**
         * do nothing - use data generated at previous launch of application
         */
        DATA_USE_OLD,
    };
    final private WORK_TYPE work_type = WORK_TYPE.DATA_DROP_AND_CREATE;

    /**
     * Only inside this method speed is calculated.
     * IMPORTANT: speed of this method and methods below are calculated and important.
     */
    private void calculation(Connection conn, Statement s) throws SQLException {
        Date begin;
        Date end;
        String sql;

        //Calculation with using of static method
        for (int i = 0; i < 5; i++) {
            s = conn.createStatement();

            sql = "DELETE FROM testtotals";
            s.execute(sql);

            begin = new Date();
            FatTest.calculateTotalClient(conn);
            end = new Date();
            System.out.println("TIMING client code " + (end.getTime() - begin.getTime()));
            conn.commit();
//            System.out.println("Committed the transaction");
        }

        //Calculation with using of stored procedure
        for (int i = 0; i < 5; i++) {
            s = conn.createStatement();

            sql = "DELETE FROM testtotals";
            s.execute(sql);

            begin = new Date();
            sql = "CALL calculateTotal()";
            s.execute(sql);
            end = new Date();
            System.out.println("TIMING stored procedure " + (end.getTime() - begin.getTime()));
            conn.commit();
//            System.out.println("Committed the transaction");
        }
    }

    static private void calculateTotalCommon(Connection connection) throws SQLException {

        int totalAmount = 0;
        int lastClient = 0;
        Statement s = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = s.executeQuery("SELECT CLIENT, ORDER_ID, ORDER_AMOUNT FROM TESTBIG");

        PreparedStatement updateData = connection.prepareStatement("UPDATE testtotals SET " +
                " client_total = client_total + ? " +
                " WHERE client = ?");
        PreparedStatement insertData = connection.prepareStatement("INSERT INTO testtotals " +
                " (client, client_total) " +
                " VALUES (?, ?) ");

        while (rs.next()) {
            int client = rs.getInt(1);
//                int order_id = rs.getInt(2);
            int order_amount = rs.getInt(3);
//                System.out.println("Client: " + client + ", " + order_id + ", " + order_amount);

            if (lastClient == 0) {
                lastClient = client;
                totalAmount = 0;
            }

            if (lastClient != client) {

                /**
                 * Emulation of merge function
                 * first update existing data. If nothing updated then INSERT.
                 */
                updateData.setInt(1, totalAmount);
                updateData.setInt(2, lastClient);
                int sqlRowCount = updateData.executeUpdate();

                if (sqlRowCount == 0) {
                    insertData.setInt(1, lastClient);
                    insertData.setInt(2, totalAmount);
                    sqlRowCount = insertData.executeUpdate();
                }

                lastClient = client;
                totalAmount = order_amount;
            } else {
                totalAmount = totalAmount + order_amount;
            }
        }

        /**
         * Emulation of merge function
         */
        updateData.setInt(1, totalAmount);
        updateData.setInt(2, lastClient);
        int sqlRowCount = updateData.executeUpdate();

        if (sqlRowCount == 0) {
            insertData.setInt(1, lastClient);
            insertData.setInt(2, totalAmount);
            sqlRowCount = insertData.executeUpdate();
        }


        rs.close();
        s.close();
        connection.commit();
    }

    static public void calculateTotalStored() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:default:connection");
        calculateTotalCommon(connection);
        connection.close();
    }

    static public void calculateTotalClient(Connection connection) throws SQLException {
        calculateTotalCommon(connection);
    }
}