/**
 * Name: Carlos Masson
 * 
 * Panther-ID: x x x -2059
 * 
 * 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: Carlos Masson
 */

package asg5;
import java.sql.*;
import java.io.*;
import java.util.Scanner;
import javax.swing.JOptionPane;

public class asg5{
	static public boolean debug = true;
	
	static final String JDBC_DRIVER = "com.sql.cj.jdbc.Driver"; 
	
	public static void main(String[] args) {
		String filePath, projectNumber, url, dataBase, userName, passWord;
		double sum= 0.0; 
		int time = 0; 
		Scanner console = new Scanner(System.in);
		Connection conn = null;
		PreparedStatement statement1 = null, statement2=null,statement3 =null;
		
		url = JOptionPane.showInputDialog(null, 
				"Enter the Database URL: ", 
				"Database URL", 
				JOptionPane.PLAIN_MESSAGE);
		
		url = "jdbc:sql://" + url + ":3306/";
		
		dataBase = JOptionPane.showInputDialog(null, 
				"Enter the Database You Want To Connect To: ", 
				"Database", 
				JOptionPane.PLAIN_MESSAGE);
		
		userName = JOptionPane.showInputDialog(null, 
				"Enter the username of database: ", 
				"UserName", 
				JOptionPane.PLAIN_MESSAGE);
		
		passWord = JOptionPane.showInputDialog(null, 
				"Enter the password of database: ",
				"PassWord", 
				JOptionPane.PLAIN_MESSAGE);
		
		try {
			Class.forName(JDBC_DRIVER); 

            conn = DriverManager.getConnection(url+dataBase,userName,passWord);
            
            if(conn != null) { 
            	System.out.println("Database Successfully Connected to: " + dataBase);
            	
            	String firstQuery = "SELECT pname from project WHERE pnumber = 7 GROUP BY pname";
            	statement1 = conn.prepareStatement(firstQuery);
            	
            	String secondQuery = "SELECT D.dname, E.fname, E.lname FROM project P, employee E, department D WHERE P.pnumber = 7 and P.dnum = D.dnumber and E.ssn = D.mgrssn GROUP BY D.dname, E.fname, E.lname";
            	statement2 = conn.prepareStatement(secondQuery);
            	
            	String thirdQuery = "SELECT count(pno) FROM works_on WHERE pno = 1";
            	statement3 = conn.prepareStatement(thirdQuery);
            	
            	System.out.println("Enter input file name: ");
            	filePath = console.nextLine();
            	System.out.println("Proj#   ProjName        Department      Manager         # of assignments");
            	System.out.println("------- -------------   --------------- --------------  ----------------");
            	
            	try {
            		File inputFile = new File(filePath);
            		Scanner scanner = new Scanner(inputFile);
            		
            		while(scanner.hasNext()) {
            			projectNumber = scanner.nextLine();
            			
            			statement1.clearParameters();
            			statement1.setString(1, projectNumber);
            			ResultSet resultSet1 = statement1.executeQuery();
            			if(resultSet1.next()) {
            				String projectName = resultSet1.getString(1);
            				System.out.printf("%-8s", projectNumber);
            				System.out.printf("%-18s", projectNumber);
            				
            				statement2.clearParameters();
            				statement2.setString(1, projectNumber);
            				ResultSet resultSet2 = statement2.executeQuery();
            				
            				if(resultSet2.next()) {
            					String depName = resultSet2.getString(1);
            					String employeeName = resultSet2.getString(2);
            					System.out.printf("%-19s", depName);
            					System.out.printf("%-26s", employeeName);
            					
            					statement3.clearParameters();
                				statement3.setString(1, projectNumber);
                				ResultSet resultSet3 = statement3.executeQuery();
                				
                				if(resultSet3.next()) {
                					String numofassign = resultSet3.getString(1);
                					double numofAssign = Double.parseDouble(numofassign);
                					sum = sum + numofAssign;
                					time++;
                					System.out.printf("%-40s\n", numofassign);
                				}
            					resultSet3.close();
            					
            				}
            				resultSet2.close();
            				
            			} else {
            				System.out.println("invalid input project number: " + projectNumber);
            				
            			}
            			resultSet1.close();
            		}
            		System.out.println("\nThe average number of work assignments per valid project is: " + (sum/time));
            		scanner.close();
            	}catch(FileNotFoundException e) {
        			e.printStackTrace();
        			
        		}
            }
            System.out.println("\n See Ya Later!");
            
		} catch(SQLException e) {
            	e.printStackTrace();
            	
        } catch(Exception e) {
            	e.printStackTrace();
            	
        } finally {
        	
            	try {
            		if(statement1 != null) statement1.close();
            			
            	}catch(SQLException e){
            		e.printStackTrace();
            		
            	}
            	
            	try {
            		if(conn != null) {
            			conn.close();
            		}
            	}catch(SQLException e){
            		e.printStackTrace();
            	}
            		
        }
		console.close();
		
	}
}