package dbtest;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

public class DBTest {
    private static Random random = new Random();

    public static void main(String args[ ])
    {
        try {
            testFunction();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void testFunction() throws SQLException {
        random.setSeed(System.currentTimeMillis());
        long startTime = 0;
        long prepStatementStartTime = 0, endTime = 0;
        List<Integer> statsID = new ArrayList<Integer>();
        List<Integer> playersID = new ArrayList<Integer>();
        Connection connection = DBUtil.getConnection();
        connection.setAutoCommit(false);
        try {
            startTime = System.currentTimeMillis();
            Statement statement = null;

            statement = connection.createStatement();

            StringBuilder queryBuilder = new StringBuilder(100);

            queryBuilder.append("SELECT * FROM bb_batter_season_stat limit 10000");

            System.out.println("Selecting 10000 elements from DB");
            ResultSet result = statement.executeQuery(queryBuilder.toString());

            if (!result.next()) {
                System.out.println("No doctor with given id found");
                return;
            }

            do {
                Integer id = result.getInt("id");
                Integer playerID = result.getInt("bb_players_id");
                statsID.add(id);
                playersID.add(playerID);
            } while (result.next());

            System.out.println("Query returned " + statsID.size() + " elements");
            queryBuilder = new StringBuilder(100);
            queryBuilder.append("update bb_batter_season_stat SET \n" +
                    "G=?,\n" +
                    "ABvsL=?,\n" +
                    "ABvsR=?,\n" +
                    "RvsL=?,\n" +
                    "RvsR=?,\n" +
                    "HvsL=?,\n" +
                    "HvsR=?,\n" +
                    "d2BvsL=?,\n" +
                    "d2BvsR=?,\n" +
                    "t3BvsL=?,\n" +
                    "t3BvsR=?,\n" +
                    "HRvsL=?,\n" +
                    "HRvsR=?,\n" +
                    "RBlvsL=?,\n" +
                    "RBlvsR=?,\n" +
                    "BBvsL=?,\n" +
                    "BBvsR=?,\n" +
                    "SOvsL=?,\n" +
                    "SOvsR=?,\n" +
                    "AVGvsL=?,\n" +
                    "AVGvsR=?,\n" +
                    "OBPvsL=?,\n" +
                    "OBPvsR=?,\n" +
                    "SLGvsL=?,\n" +
                    "SLGvsR=?,\n" +
                    "OPS=?,\n" +
                    "SB=?,\n" +
                    "XBH=?,\n" +
                    "CS=?,\n" +
                    "SFvsL=?,\n" +
                    "SFvsR=?,\n" +
                    "TB=?,\n" +
                    "IBB=?,\n" +
                    "HBP=?,\n" +
                    "GO=?,\n" +
                    "FO=?,\n" +
                    "NP=?,\n" +
                    "PAvsL=?,\n" +
                    "PAvsR=?,\n" +
                    "AB_int=?,\n" +
                    "R_int=?,\n" +
                    "H_int=?,\n" +
                    "d2B_int=?,\n" +
                    "t3B_int=?,\n" +
                    "HR_int=?,\n" +
                    "RBI_int=?,\n" +
                    "AVG_int=?,\n" +
                    "SB_int=?,\n" +
                    "G_field=?,\n" +
                    "GS_field=?,\n" +
                    "INN_field=?,\n" +
                    "PO_field=?,\n" +
                    "A_field=?,\n" +
                    "E_field=?,\n" +
                    "DP_field=?,\n" +
                    "PB_field=?,\n" +
                    "SB_field=?,\n" +
                    "CS_field=?,\n" +
                    "RF_field=?,\n" +
                    "FPCT_field=?\n" +
                    "WHERE id=?;");
            PreparedStatement preparedStatement = connection.prepareStatement(queryBuilder.toString());
            int repeate_amount = 100;
            System.out.println("Starting second part. Repeat amount: " + repeate_amount + " stats size: " + statsID.size() );

            prepStatementStartTime = System.currentTimeMillis();

            for (int j = 0; j < repeate_amount; ++j) {
                for (int counter = 0; counter < statsID.size(); ++counter) {
                    for (int i = 0; i < 19; ++i) {
                        preparedStatement.setInt(i + 1, getRandomSmallInt());
                    }

                    for (int i = 20; i <= 26; ++i) {
                        preparedStatement.setFloat(i, getRandomFloat());
                    }

                    for (int i = 27; i <= 46; ++i) {
                        preparedStatement.setInt(i, getRandomSmallInt());
                    }

                    preparedStatement.setFloat(47, getRandomFloat());

                    for (int i = 48; i <= 50; ++i) {
                        preparedStatement.setInt(i, getRandomSmallInt());
                    }

                    preparedStatement.setFloat(51, getRandomFloat());

                    for (int i = 52; i <= 58; ++i) {
                        preparedStatement.setInt(i, getRandomSmallInt());
                    }

                    preparedStatement.setFloat(59, getRandomFloat());
                    preparedStatement.setFloat(60, getRandomFloat());
                    preparedStatement.setShort(61, (short) (int) statsID.get(counter));
                    preparedStatement.addBatch();
                }
                preparedStatement.executeBatch();
            }
            connection.commit();

            endTime = System.currentTimeMillis();

            System.out.println("First part: " + (prepStatementStartTime - startTime));
            System.out.println("Second part: " + (endTime - prepStatementStartTime) + " ms");
            System.out.println("Second part per update: " + (float)(endTime - prepStatementStartTime) / (float)(repeate_amount * statsID.size()) + " ms");
        } finally {
            connection.close();
        }

        try {
            Thread.currentThread().sleep(10000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }

    private static float getRandomFloat() {
        return random.nextFloat();
    }


    private static short getRandomSmallInt() {
        return (short)random.nextInt(65535);
    }
}