/*
 * Copyright 2003 Sun Microsystems, Inc.  ALL RIGHTS RESERVED.
 * Use of this software is authorized pursuant to the terms of the license found at
 * http://developer.java.sun.com/berkeley_license.html.
 */

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.*;
import java.util.StringTokenizer;

public class Lab11  {

	public static void main(String args[]) {

        //Set connection info.
		//TODO Replace <db-name> with the name of your empty database
		String url = "jdbc:sqlite:/44907087_P2";
		Connection con;
		Statement ddl, stmt, stmt2;
		PreparedStatement pstmt;

		//Load the driver class
		try {
			Class.forName("SQLite.JDBCDriver");

		} catch(java.lang.ClassNotFoundException e) {
			System.err.print("ClassNotFoundException: ");
			System.err.println(e.getMessage());
		}

		try {
            //Create a connection to the db
			con = DriverManager.getConnection(url, "myLogin", "myPassword");

			//Create tables
			ddl = con.createStatement();
			
			//TODO SQL DDL for creating our tables. Refer to assignment for schema.
			String createPlayerGames = "CREATE TABLE playerGames ( pid INTEGER, gid INTEGER, score INTEGER);";
			String createTopScores = "CREATE TABLE topScores ( pid INTEGER, gid INTEGER, score INTEGER);";
			ddl.executeUpdate(createPlayerGames);
			ddl.executeUpdate(createTopScores);

            //Read in player_games.txt content and insert into the database.
			try{
				  BufferedReader br = new BufferedReader(new FileReader("player_games.txt"));
				  String strLine;
				  int linenum = 1;
				  
				  //This is the total number of lines to read from player_games.txt. 
				  //If you attempt to read the entire file (10000 lines) you will be waiting for about an hour.
				  //For the lab we will read 1000 lines only, which will still take a few minutes.
				  int totalLines = 1000;

				  //TODO Declare the prepared statement for inserting data into playerGames table.
				  //Refer to java.sql.PreparedStatement javadoc for more info.
				  String preparedInsertStatement = "INSERT INTO playerGames (pid, gid, score) VALUES (?,?,?);";
				  pstmt = con.prepareStatement(preparedInsertStatement);

				  // PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?");
				  //pstmt.setBigDecimal(1, 153833.00)
				  //pstmt.setInt(2, 110592)
				  
				  //Read File Line By Line
				  System.out.println("Building playerGames table from file:");
				  double start = System.currentTimeMillis();
				  System.out.print("|          | 0%\r");
				  while ((strLine = br.readLine()) != null && linenum<=totalLines)   {
					//TODO: Extract values from the line read.
					  StringTokenizer tkn = new StringTokenizer(strLine, "\t");
					  String pid = tkn.nextToken();
					  String gid = tkn.nextToken();
					  String scr = tkn.nextToken();
					//TODO: Set the values in the prepared statement to the values read from the file.
					  pstmt.setString(1, pid);
					  pstmt.setString(2, gid);
					  pstmt.setString(3, scr);
					  
					//Execute the prepared statement.
					pstmt.executeUpdate();

                    //Update progress bar
                    int bars = linenum/(totalLines/10);
					int percent = linenum/totalLines*100;
                    String bar = "|";
                    for(int i=0; i<10; i++) {
                       bar += (i<bars)?"=":" ";
                    }
                    bar = bar+"| " + percent + "%\r";
                    System.out.print(bar);
					linenum++;
				  }
				  double total = System.currentTimeMillis()-start;
				  System.out.println("Done!          in "+total+"ms");
				  br.close();
				    }
			catch (Exception e){//Catch exception if any
				  System.err.println("Error: " + e.getMessage());
				  }

			stmt = con.createStatement();
			stmt2 = con.createStatement();

            //TODO: Query for selecting the top score for each player and the game id where they got the score.
			String selectScores = "SELECT pid, gid, MAX(score) AS highscore FROM playerGames GROUP BY pid;";
			ResultSet rs = stmt.executeQuery(selectScores);

            System.out.println("Inserting top scores into topScores table:");
			double start = System.currentTimeMillis();
			System.out.print("|          | 0%\r");
			int linenum =1;
			rs.last();
			int totalLines = rs.getRow();
			rs.first();
			while (rs.next()) {
			//TODO: For every row in the result set, add to topScores table.
				int pid = rs.getInt("pid");
				int gid = rs.getInt("gid");
				int scr = rs.getInt("highscore");
				String insertTopScore = "INSERT INTO topScores (pid, gid, score) VALUES ("+pid+","+gid+","+scr+");";
				
				//Execute the insert statement
				stmt2.executeUpdate(insertTopScore);
				
				//Update progress bar
				int bars = linenum/(totalLines/10);
				int percent = linenum*100/totalLines;
				String bar = "|";
				for(int i=0; i<10; i++) {
					bar += (i<bars)?"=":" ";
				}
				bar = bar+"| " + percent + "%\r";
				System.out.print(bar);
				linenum++;
			}
			double total = System.currentTimeMillis()-start;
			System.out.println("Done!          in "+total+"ms");

			//Confirm it works by outputting the contents of topScores table to the console.
			String getTopScores = "SELECT * FROM topScores";
			Statement getScores = con.createStatement();
			ResultSet scores = getScores.executeQuery(getTopScores);

            System.out.println("Contents of topScores:");
			//TODO: For every row in the result set, output the data in a "nice" format to the console.
            while(scores.next()) {
            	System.out.println("PID: "+scores.getInt("pid")+"\t GID: "+scores.getInt("gid")+"\t HIGHSCORE: "+scores.getInt("score"));
            }

			stmt.close();
			stmt2.close();
			con.close();

		} catch(SQLException ex) {
			System.err.print("SQLException: ");
			System.err.println(ex.getMessage());
		}
	}
}