/*
========================================================

Name: Kristian Perez

Panther-ID: x x x -5695012

Course: COP 4710

Assignment#: 5

Due: Thu, Oct 8, 2020

I hereby certify that this work is my own and none of
it is the work of any other person.

                   Signature: Kristian Perez

=========================================================
For each input line,
if the input project number value is invalid, print a invalid input message
otherwise, output the following:
    - project number
    - name of the project
    - name of the department that controls the project
    - name of the manager of the department
    - the number of work assignments for the project
Also, list the average number of work assignments per (valid) project (a single value for the entire input file)
Assume that the input file contains at least one valid project number.
 */

//STEP 1. Import required packages
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;

import javax.swing.JOptionPane;
import javax.swing.JPasswordField;
import java.util.Scanner;

import java.io.File;			//Used for handling a file as input
import java.io.FileNotFoundException;

public class asg5 {
    static private boolean debug = true;

    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String URL = "jdbc:mysql://localhost:3306/";
    //static final String URL = "jdbc:mysql://127.0.0.1:3306/";

    //  Database credentials
    static final String DBASE = "company";
    static final String USER = "root";
    static final String PASS = "";

    public static void main(String[] args) {

        String inppnum;		//The pNumber in the file
	String pName = "";	//Project name
        Scanner console = new Scanner(System.in);
        Connection conn = null;
	ResultSet rs;
        PreparedStatement stmt = null;
	int currentAssignment = 0;	//For the current pNum # of assignments (used for parsing)
  	int totalAssignments = 0;	//Total number of assignments for all given VALID pNums
	double numberOfProjects = 0;	//Number of VALID pNums
	double avgAssignments = 0.0;	//totalAssignments/numOfProjects

        try {
            //STEP 2: Register JDBC driver
            Class.forName(JDBC_DRIVER);

            //STEP 3: Open a connection
            conn = DriverManager.getConnection(URL + DBASE, USER, PASS);

            if (conn != null) {
                System.out.println();
                System.out.println("Successfully connected to the " + DBASE + " database");
                System.out.println();
                String selectPName = "SELECT pname, dname, fname, lname, count(*) FROM project p, department d, employee e, works_on w WHERE pnumber = ?" + //"?" is the pNum on file
		"AND dname IN (SELECT dname FROM department WHERE p.dnum = d.dnumber)" + //First get dName by using dNum of given pNum
		"AND fname IN (SELECT fname FROM employee WHERE e.ssn = d.mgrssn AND d.dnumber IN (SELECT dnumber FROM department WHERE p.dnum = d.dnumber))" +	//Find first and last name of manager using mgrssn
		"AND lname IN (SELECT lname FROM employee WHERE e.ssn = d.mgrssn AND d.dnumber IN (SELECT dnumber FROM department WHERE p.dnum = d.dnumber))" +
		"AND pnumber IN (SELECT pnumber FROM works_on WHERE w.pno = pnumber)"; //Find number of projects in work_ons table using pNum on file
                stmt = conn.prepareStatement(selectPName);
                System.out.print("\nEnter input file name: ");	//Get input file name
                String inputFile = console.nextLine();

		//Used for reading input file
		File inFile = new File(inputFile);
		Scanner read = new Scanner(inFile);

		//Header -- Printed once so outside of loop
		System.out.printf("%-7s %-15s %-15s %-16s %-16s\n", "Proj#", "ProjName", "Department", "Manager", "# of assignments");
		System.out.println("------- -------------   --------------- --------------   ----------------");
		
		while(read.hasNextLine())	//While there is still pNumbers in the input file
		{
		inppnum = read.nextLine();	//Get new pNum
                //STEP 4: Execute a query
                stmt.clearParameters();
                stmt.setString(1, inppnum); // replaces the first argument (?) with inppnum value
                //if (debug) // stmt is a PreparedStatement object and cannot be displayed
                //  System.out.println("SQL was:\n "+stmt.toString());
                rs = stmt.executeQuery();

                //STEP 5: Extract data from result set
                while (rs.next()) 
		{
                    //Retrieve all desired outputs
                    pName = rs.getString(1);
		    String dName = rs.getString(2);
		    String fName = rs.getString(3);
		    String lName = rs.getString(4);
		    String count = rs.getString(5);

                    SQLWarning warning = stmt.getWarnings();
                    if (warning != null) {
                        System.out.println("\n---Warning---\n");
                        while (warning != null) {
                            System.out.println("Message: " + warning.getMessage());
                            System.out
                                .println("SQLState: " + warning.getSQLState());
                            System.out.print("Vendor error code: ");
                            System.out.println(warning.getErrorCode());
                            System.out.println("");
                            warning = warning.getNextWarning();
                        }
                    }
		    //Keep count of total assignments
	  	    currentAssignment = Integer.parseInt(count);
		    totalAssignments = totalAssignments + currentAssignment;
                    //Display values
		    if(pName != null)	//if VALID pNum
		    {
		    numberOfProjects++;	//VALID to increment numberOfProjects
		    System.out.printf("%-7s %-15s %-15s %-16s %-16s\n", inppnum, pName, dName, fName + " " + lName, count);
                    //System.out.println("\nOutput:\n  " + pName + " " + dName + " " + fName + "," + lName + " " + count); 
		    }
		    else	//if INVALID pNum
		    {
		    System.out.println("Invalid input project number: " + inppnum);
		    }
                }
		rs.close();
		}
		read.close();	//Close scanner, done with it
		avgAssignments = totalAssignments/numberOfProjects; //Calculate and display average
		System.out.println("\nThe average number of work assignments per valid project is: " + avgAssignments);
                
            }
        } catch (SQLException se) {
            //Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            //Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            //finally block used to close resources
            //STEP 6: Clean-up environment
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException se2) {} // nothing we can do
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            } //end finally try
        } //end try
        System.out.println("\nBye!");
    } //end main
} //end TestDB3