/**
 * @author Joshua Tepaß, Alexander Juckel & Larissa Brinkhus
 * @version 1.0
 */
package aufg7;

import java.util.*;
import java.sql.*;

public class Aufg7OPTIMUM
{
    static Connection conn;

    public static void main(String[] args) throws SQLException
    {
        conn = connectToDB("joshua","Le3ugubU");
        System.out.println("Verbindung hergestellt");

        createTPSdb(10);
    }
/**
* Connects to Database "aufgabe7" on the local SQL server
* @throws SQLException
* */
    public static Connection connectToDB(String user, String password) throws SQLException
    {
        Connection testconnection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3307/aufgabe7",
                user,
                password);
        return testconnection;
    }

/**
 * Drops the tables if they already exists
 * Creates tables
 */
    public static void createTables() throws SQLException
    {
        Statement stmt = conn.createStatement();

        stmt.executeUpdate("USE aufgabe7");

        stmt.executeUpdate("DROP TABLE IF EXISTS history");
        stmt.executeUpdate("DROP TABLE IF EXISTS tellers");
        stmt.executeUpdate("DROP TABLE IF EXISTS accounts");
        stmt.executeUpdate("DROP TABLE IF EXISTS branches");

        String branches = "CREATE TABLE IF NOT EXISTS branches("
                + "branchid INT NOT NULL,"
                + "branchname VARCHAR(20) NOT NULL,"
                + "balance INT NOT NULL,"
                + "address VARCHAR(68) NOT NULL,"
                + "PRIMARY KEY (branchid))";

        stmt.executeUpdate(branches);
        System.out.println("Tabelle branches erstellt");


        String accounts = "CREATE TABLE IF NOT EXISTS accounts("
                + "accid INT NOT NULL,"
                + "name VARCHAR(20) NOT NULL,"
                + "balance INT NOT NULL,"
                + "branchid INT NOT NULL,"
                + "address VARCHAR(68) NOT NULL,"
                + "PRIMARY KEY (accid),"
                + "FOREIGN KEY (branchid) REFERENCES branches (branchid))";

        stmt.executeUpdate(accounts);
        System.out.println("Tabelle accounts erstellt");


        String tellers = "CREATE TABLE IF NOT EXISTS tellers("
                + "tellerid INT NOT NULL,"
                + "tellername VARCHAR(20) NOT NULL,"
                + "balance INT NOT NULL,"
                + "branchid INT NOT NULL,"
                + "address VARCHAR(68) NOT NULL,"
                + "PRIMARY KEY (tellerid),"
                + "FOREIGN KEY (branchid) REFERENCES branches (branchid))";

        stmt.executeUpdate(tellers);
        System.out.println("Tabelle tellers erstellt");


        String history = "CREATE TABLE IF NOT EXISTS history("
                + "accid INT NOT NULL,"
                + "tellerid INT NOT NULL,"
                + "delta INT NOT NULL,"
                + "branchid INT NOT NULL,"
                + "accbalance INT NOT NULL,"
                + "cmmnt VARCHAR(30) NOT NULL,"
                + "FOREIGN KEY (accid) REFERENCES accounts (accid),"
                + "FOREIGN KEY (tellerid) REFERENCES tellers (tellerid),"
                + "FOREIGN KEY (branchid) REFERENCES branches (branchid))";

        stmt.executeUpdate(history);
        System.out.println("Tabelle history erstellt");

    }

    static SplittableRandom random = new SplittableRandom();
/*
 * Fill tables
 */
    public static void fillTables(int n) throws SQLException
    {
        conn.setAutoCommit(false);

        long startTime = System.currentTimeMillis();

        String str68 = "12345678901234567890123456789012345678901234567890123456789012345678";
        String str20 = "12345678901234567890";

        PreparedStatement branches = conn.prepareStatement("INSERT INTO branches(branchid,branchname,balance,address) VALUES (?,?,?,?)");
        PreparedStatement accounts = conn.prepareStatement("INSERT INTO accounts(accid,name,balance,branchid,address) VALUES (?,?,?,?,?)");
        PreparedStatement tellers = conn.prepareStatement("INSERT INTO tellers(tellerid,tellername,balance,branchid,address) VALUES (?,?,?,?,?)");

        for(int i=1;i<=n;i++)
        {
            branches.setInt(1, i);
            branches.setString(2, str20);
            branches.setInt(3, 0);
            branches.setString(4, str68);
            branches.executeUpdate();
        }
        System.out.println("Branches gefüllt");

        for(int j=1;j<=n*100000;j++)
        {
            if(j%10000==0)
            {
                conn.commit();
            }
            accounts.setInt(1, j);
            accounts.setString(2, str20);
            accounts.setInt(3, 0);
            accounts.setInt(4, (int)(random.nextDouble()*n+1));
            accounts.setString(5, str68);
            accounts.executeUpdate();
        }
        System.out.println("Accounts gefüllt");

        for(int k=1;k<=n*10;k++)
        {
            tellers.setInt(1, k);
            tellers.setString(2, str20);
            tellers.setInt(3, 0);
            tellers.setInt(4, (int)(random.nextDouble()*n+1));
            tellers.setString(5, str68);
            tellers.executeUpdate();
        }
        System.out.println("Tellers gefüllt");

        long stopTime = System.currentTimeMillis();
        double elapsedTime = (stopTime - startTime)/1000;
        System.out.println(elapsedTime);

        conn.commit();
    }

    public static void createTPSdb(int n) throws SQLException
    {
        createTables();
        fillTables(n);
    }
}