import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.text.NumberFormat;

/**
	Runs the test query set for mini-project 3
	based on mini-project 2 code
	@author michael ratanapintha
 */
public class TestQueries {

	/** This "scale factor" controls how how many times each test query gets run -- the relationship is a linear proportion */
	private static int scaleFactor = 10;
	private static String databaseName = "project3";

	private static final String postgreSQLDriver = "org.postgresql.Driver";
	private static final String postgreSQLUrlBase = "jdbc:postgresql:";
	private static final String postgreSQLUser = "suciu";
	private static final String postgreSQLPassword = "1";

	private static Connection connection;

    // ----------------------------------------------------------------
    // Examine these queries: you need to tune the database accordingly
    // (and also see below)
    //
    // Queries over R
	private static final String R1_sql = "select count(*) from R where R.A = ?";
	private static PreparedStatement R1_statement;

	private static final String R2_sql = "select count(*) from R where R.A = ? and R.B = ?";
	private static PreparedStatement R2_statement;

	private static final String R3_sql = "select count(*) from R where R.A = ? and R.B > ? and R.B < ?";
	private static PreparedStatement R3_statement;

	private static final String R4_sql = "select count(*) from R where  R.C > ? and R.C < ?";
	private static PreparedStatement R4_statement;

    // Updates over R
	private static final String Rinsert_sql = "insert into R(K,A,B,C,E) values (?,?,?,?,?)";
	private static PreparedStatement Rinsert_statement;

	private static final String Rdelete_sql = "delete from R where K=?";
	private static PreparedStatement Rdelete_statement;

    //
    // Queries over S, T
	private static final String ST_sql = "select count(*) from S, T where S.B = T.K and S.C = ? and T.E = ?";
	private static PreparedStatement ST_statement;

    // Qeries over U
        private static final String UAeq0Select_sql = "select count(*) from U where U.A = 0 and U.B > ?"; // A=0
	private static PreparedStatement UAeq0Select_statement;

        private static final String UAgt0Select_sql = "select count(*) from U where U.A = 1+abs(?) and U.B > ?"; // A!=0
	private static PreparedStatement UAgt0Select_statement;

    // More queries/updates over U
        private static final String UAeq0Insert_sql = "insert into U(K,A,B) values (?, 0, ?)"; // A=0
	private static PreparedStatement UAeq0Insert_statement;

        private static final String UAgt0Insert_sql = "insert into U(K,A,B) values (?, 1+abs(?), ?)";  // A!=0
	private static PreparedStatement UAgt0Insert_statement;

	private static final String UAgt0Delete_sql = "delete from U where A = 1+abs(?)";
	private static PreparedStatement UAgt0Delete_statement;

    // Helper queries: ignore them
	private static final String Rmaxkey_sql = "select max(K) from R";
	private static PreparedStatement Rmaxkey_statement;

	private static final String Umaxkey_sql = "select max(K) from U";
	private static PreparedStatement Umaxkey_statement;

	// the random number generator
	private static Random rndGen = new Random();

	public static void main (String args[]) throws Exception {
		// get the database name and scale factor
		if (args.length > 2) {
			System.err.println ("Usage: java TestQueries [database] [scale factor]");
			System.exit(1);
		} 
		if (args.length >= 1) {
			databaseName = args[0];
		}
		if (args.length == 2) {
			try {
				scaleFactor = Integer.parseInt(args[1]);
			} catch (NumberFormatException e) {
				System.err.println ("Unable to parse scale factor given as: \"" + args[1] + "\"");
				System.exit(2);
			}
		}

		System.out.println ("Running the test query workload in database " + databaseName);
		
		openConnection();
		prepareStatements();

		initParams();

		System.out.println ();
		long readRTime = testReadR();
		System.out.println ("The SELECT queries on relation R took " + readRTime + " ms.");
		long STTime = testST();
		System.out.println ("The SELECT queries on relations S and T took " + STTime + " ms.");
		long readUTime = testReadU();
		System.out.println ("The SELECT queries on relation U took " + readUTime + " ms.");
		long writeRTime = testWriteR();
		System.out.println ("The INSERT and DELETE queries on relation R took " + writeRTime + " ms.");
		long writeUTime = testWriteU();
		System.out.println ("The INSERT and DELETE queries on relation U took " + writeUTime + " ms.");

		closeConnection();

		long totalTime = readRTime + STTime + readUTime + writeRTime + writeUTime;
		System.out.println ();
		System.out.println ("Test query set completed.");
		System.out.println ("Total time: " + totalTime + " ms.");

	}

	public static void openConnection() throws Exception {
		Class.forName(postgreSQLDriver).newInstance();
		connection = DriverManager.getConnection(postgreSQLUrlBase + databaseName, // connection
				postgreSQLUser, // user
				postgreSQLPassword); // password
	}

	public static void closeConnection() throws Exception {
		connection.close();
	}

	public static void prepareStatements() throws Exception {
		R1_statement = connection.prepareStatement(R1_sql);
		R2_statement = connection.prepareStatement(R2_sql);
		R3_statement = connection.prepareStatement(R3_sql);
		R4_statement = connection.prepareStatement(R4_sql);
		Rinsert_statement = connection.prepareStatement(Rinsert_sql);
		Rdelete_statement = connection.prepareStatement(Rdelete_sql);
		ST_statement = connection.prepareStatement(ST_sql);
		UAeq0Select_statement = connection.prepareStatement(UAeq0Select_sql);
		UAeq0Insert_statement = connection.prepareStatement(UAeq0Insert_sql);
		UAgt0Select_statement = connection.prepareStatement(UAgt0Select_sql);
		UAgt0Insert_statement = connection.prepareStatement(UAgt0Insert_sql);
		UAgt0Delete_statement = connection.prepareStatement(UAgt0Delete_sql);

		Rmaxkey_statement = connection.prepareStatement(Rmaxkey_sql);
		Umaxkey_statement = connection.prepareStatement(Umaxkey_sql);
	}
	
	/** Initialize statement parameters which (as yet) will not change */
	public static void initParams () throws Exception {
		UAeq0Insert_statement.setInt(1, -300);
		UAeq0Insert_statement.setInt(2, -400);
		
		UAgt0Insert_statement.setInt(1, -400);
		UAgt0Insert_statement.setInt(2, 500);
		UAgt0Insert_statement.setInt(3, 700);
		
		UAgt0Delete_statement.setInt(1, 1000);
	}
	
    public static void executeQueryWithRandomParameters (
		PreparedStatement s, 
		int np
	) throws Exception {
		s.clearParameters();
		for (int i=1; i<=np; i++) s.setInt(i, rndGen.nextInt());
		// ignore the resultsets by closing them immediately
		s.executeQuery().close();
    }


    // ---------------------------------------------------
    // Here is where the queries in the workload are executed
    // When you have competing choices for your tunings, examine the relative frequency of these queries
    // (don't spend too much time reading these frequences, most are really meaningless)
    //
    // SELECT queries on R
	public static long testReadR () throws Exception {
		long startTime = System.currentTimeMillis();
		for (int i = 1; i <= 20 * scaleFactor; i++) {
		    executeQueryWithRandomParameters(R1_statement, 1);
		    executeQueryWithRandomParameters(R2_statement, 2);
		    executeQueryWithRandomParameters(R3_statement, 3);
		}
		for (int i = 1; i <= 100 * scaleFactor; i++) {
		    executeQueryWithRandomParameters(R4_statement, 2);
		}
		long endTime = System.currentTimeMillis();
		return (endTime - startTime);
	}

    // SELECT queries on S, T
	public static long testST () throws Exception {
		long startTime = System.currentTimeMillis();
		for (int i = 1; i <= 400 * scaleFactor; i++) {
		    executeQueryWithRandomParameters(ST_statement, 2);
		}
		long endTime = System.currentTimeMillis();
		return (endTime - startTime);
	}

    // SELECT queries on U
	public static long testReadU () throws Exception {
		long startTime = System.currentTimeMillis();
		// The queries that set A=0 or filter by A=0 execute 10 times more often.
		// And, each time they execute, they take longer to run (why is that?  see the 
		// connection generator program).
		for (int i = 1; i <= 300 * scaleFactor; i++) {
		    executeQueryWithRandomParameters(UAeq0Select_statement, 1);
		}
		for (int i = 1; i <= 10 * scaleFactor; i++) {
		    executeQueryWithRandomParameters(UAgt0Select_statement, 2);
		}
		long endTime = System.currentTimeMillis();
		return (endTime - startTime);
	}

    // INSERT/DELET queries over R
	public static long testWriteR () throws Exception {
		int maxKey = maxKeyR();
		
		long startTime = System.currentTimeMillis();
		for (int i = 1; i <= 2000 * scaleFactor; i++) {
		    // insert a random tuple; however, make sure key is out of range
			Rinsert_statement.clearParameters();
			Rinsert_statement.setInt(1, maxKey + i);
			Rinsert_statement.setInt(2, rndGen.nextInt());
			Rinsert_statement.setInt(3, rndGen.nextInt());
			Rinsert_statement.setInt(4, rndGen.nextInt());
			Rinsert_statement.setInt(5, rndGen.nextInt());
			Rinsert_statement.executeUpdate();

			// delete a random tuple
			Rdelete_statement.clearParameters();
			Rdelete_statement.setInt(1, i * maxKey / (2000 * scaleFactor));
			try { Rdelete_statement.executeUpdate(); } catch (Exception e) { };
		}
		long endTime = System.currentTimeMillis();
		return (endTime - startTime);
	}

	public static int maxKeyR () throws Exception {
		ResultSet maxkeyTbl = Rmaxkey_statement.executeQuery();
		int maxKey = 0;
		if (maxkeyTbl.next()) { 
			maxKey = maxkeyTbl.getInt(1);
		} else {
			System.err.println("Something is wrong: Max key value for R could not be read");
			System.exit(1);
		}
		maxkeyTbl.close();
		return (maxKey);
	}

    // INSERT/DELETE queries over U
	public static long testWriteU () throws Exception {
		int maxKey;

		long startTime = System.currentTimeMillis();
		// The queries that set A=0 or filter by A=0 are executed 10 times more often.
		// And, each time they execute, they take longer to run (why is that?  see the 
		// connection generator program).
		maxKey = maxKeyU();
		for (int i = 1; i <= 1000 * scaleFactor; i++) {
			UAeq0Insert_statement.setInt(1, maxKey + i);
			UAeq0Insert_statement.setInt(2, rndGen.nextInt());
			UAeq0Insert_statement.executeUpdate();
		}
		
		maxKey = maxKeyU();
		for (int i = 1; i <= 100 * scaleFactor; i++) {
			UAgt0Insert_statement.setInt(1, maxKey + i);
			UAgt0Insert_statement.executeUpdate();
			UAgt0Delete_statement.executeUpdate();
		}
		long endTime = System.currentTimeMillis();
		return (endTime - startTime);
	}

	public static int maxKeyU () throws Exception {
		ResultSet maxkeyTbl = Umaxkey_statement.executeQuery();
		int maxKey = 0;
		if (maxkeyTbl.next()) { 
			maxKey = maxkeyTbl.getInt(1);
		} else {
			System.err.println("Something is wrong: Max key value for U could not be read");
			System.exit(1);
		}
		maxkeyTbl.close();
		return (maxKey);
	}

}