/* 
 * FILES:	TestAssignment2.java 
 * CLASSNAME: 	TestAssignment2
 * DESCRIPTION: Test driver for testing JDBC part of CSC343 Assignment2 (JDBC part)
 * MISC:	1) Assumes that classpath is configured to 
 * 		   include postgresql driver
 */

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.*;
import java.util.Vector;

class TestAssignment2
{
	Connection conn;
	Assignment2 a2;
	
	String method[] = new String[11];	/* stores the method name */
	boolean result[] = new boolean[11];	/* stores success status of each method */

	// constructor
	TestAssignment2()
	{
		conn = null;
		try{
		a2 = new Assignment2();
		}catch(Exception s)
		{	
			System.out.println("Error");
		}
		// init all method names
		method[0] = "connectDB()";
		method[1] = "insertStudent()";
		method[2] = "getStudentsCount()";
		method[3] = "getStudentInfo()";
		method[4] = "chgDept()";
		method[5] = "deleteDept()";
		method[6] = "listCourses()";
		method[7] = "updateGrades()";
		method[8] = "query7()";
		method[9] = "updateDB()";
		method[10] = "disconnectDB()";

		for ( int i = 0; i < 11; i++ )
			result[i] = false;
	}

	public static void main(String args[])
	{
		// check for number of arguments
		if ( args.length < 2 || args.length > 3 )
		{
			System.out.println("Usage: java TestAssignment2 dbname " + 
					"username [password]");
			System.exit(1);
		}

		TestAssignment2 ta2 = new TestAssignment2();

		// test all methods one at a time
		if(args.length==3)
		    ta2.result[0] = ta2.TestconnectDB(args[0], args[1], args[2]);
		else
		    ta2.result[0] = ta2.TestconnectDB(args[0],args[1],null);

		System.out.println("\nOverall Test Status: " + ta2.getTestStatus(ta2.result[0]));
		
		ta2.result[1] = ta2.TestinsertStudent();
		System.out.println("\nOverall Test Status: " + ta2.getTestStatus(ta2.result[1]));

		ta2.result[2] = ta2.TestgetStudentsCount();	
		System.out.println("\nOverall Test Status: " + ta2.getTestStatus(ta2.result[2]));

		ta2.result[3] = ta2.TestgetStudentInfo();		
		System.out.println("\nOverall Test Status: COMPARE");

		ta2.result[4] = ta2.TestchgDept();		
		System.out.println("\nOverall Test Status: " + ta2.getTestStatus(ta2.result[4]));

		ta2.result[5] = ta2.TestdeleteDept();		
		System.out.println("\nOverall Test Status: " + ta2.getTestStatus(ta2.result[5]));

		ta2.result[6] = ta2.TestlistCourses();		
		System.out.println("\nOverall Test Status: COMPARE");

		ta2.result[7] = ta2.TestupdateGrades();		
		System.out.println("\nOverall Test Status: COMPARE");

		ta2.result[8] = ta2.Testquery7(); //run ammendgrades as the last test		
		System.out.println("\nOverall Test Status: COMPARE");

		ta2.result[9] = ta2.TestupdateDB();
		System.out.println("\nOverall Test Status: " + ta2.getTestStatus(ta2.result[9]));

		ta2.result[10] = ta2.TestdisconnectDB();
		System.out.println("\nOverall Test Status: " + ta2.getTestStatus(ta2.result[10]));

		//ta2.printResults();
	}

	boolean TestconnectDB(String dbname, String username, String password)
	{
		try
		{
			String url = "jdbc:postgresql:" + dbname;

			// Establish our own connection to database
			// (for verification/testing purposes)
			conn = DriverManager.getConnection(url, username, password);

			System.out.println("-----------------------------------------------");
			System.out.println("-----------------------------------------------");
			System.out.println("Testing connectDB() method...");
			boolean success = a2.connectDB(url, username, password);
			System.out.println("Return Value: " + success);

			if ( success == true )
				return true;
		}
		catch (SQLException se)
		{
			System.err.println("SQL Exception in main(). " +
					"<Message>: " + se.getMessage());
		}

		return false;
	}

	boolean TestinsertStudent()
	{
		try
		{
			System.out.println("---------------------------------------------");
			System.out.println("-----------------------------------------------");
			System.out.println("Testing insertStudent() method...");
			System.out.println("Inserting record ('5200', 'Tsirogiannis', 'Dimitris', '27', 'M', 'Computer Science', '3')");
			
			// verify from database directly
			Statement stmt = null;
			ResultSet rs = null;
			int stud_count = 0;
			int new_stud_count = 0;
			String queryString;

			// verify total no of rows and newly inserted from department table
			stmt = conn.createStatement();
			queryString = "select count(*) from student";
			rs = stmt.executeQuery(queryString);

			if ( rs.next() )
				stud_count = rs.getInt(1);

			boolean success1 = a2.insertStudent(5200, "Tsirogiannis", "Dimitris", 27, "M", "Computer Science", 3);
			
			System.out.println("Return Value: " + success1);
			
			// insert a student in a non-existing dept
			System.out.println("Inserting record ('5200', 'Tsirogiannakis', 'Dimitrakis', '27', 'M', 'Computer', '3')");
			boolean success2 = a2.insertStudent(5300, "Tsirogiannakis", "Dimitrakis", 27, "M", "Computer", 3);
			System.out.println("Return Value: " + success2);

			// verify from database directly
			stmt = null;
			rs = null;

			// verify total no of rows and newly inserted from department table
			stmt = conn.createStatement();
			queryString = "select count(*) from student";
			rs = stmt.executeQuery(queryString);

			if ( rs.next() )
				new_stud_count = rs.getInt(1);

			if ( success1 == true && success2 == false && new_stud_count - stud_count == 1)
				return true;
		}
		catch (SQLException se)
		{
			System.err.println("SQL Exception in main(). " +
					"<Message>: " + se.getMessage());
		}
		return false;
	}

	boolean TestgetStudentsCount()
	{
		try{
			System.out.println("------------------------------------------------");
			System.out.println("------------------------------------------------");
			System.out.println("Testing getStudentsCount() method...");
			System.out.println("Getting # of students for Computer Science...");
			int correct_count = 0;
			int count1 = a2.getStudentsCount("Computer Science");
			System.out.println("Return value: " + count1);
			// verify from database directly
			Statement stmt = null;
			ResultSet rs = null;
			String queryString;

			// verify total no of rows and newly inserted from department table
			stmt = conn.createStatement();
			queryString = "select count(*) from student s, department d where d.dcode = s.dcode and d.dname = 'Computer Science'";
			rs = stmt.executeQuery(queryString);

			if ( rs.next() )
				correct_count = rs.getInt(1);

			System.out.println("Getting # of students for Computer...");

			int count2 = a2.getStudentsCount("Computer");

			System.out.println("Return value: " + count2);

			if (count1 == correct_count && count2 == -1)
				return true;

		}catch (SQLException se)
		{
			System.err.println("SQL Exception in main(). " +
					"<Message>: " + se.getMessage());
		}
		return false;
	}


	boolean TestgetStudentInfo()
	{
		try{
			System.out.println("-----------------------------------------------");
			System.out.println("-----------------------------------------------");
			System.out.println("Testing getStudentInfo() method...");
			System.out.println("Getting info for student '5001'...");

			String info = a2.getStudentInfo(5001);
			System.out.println("Return value: \n" + info);
						
			System.out.println("\nCorrect Value: \n" + "Homer:Simpson:M:45:4:Computer Science");

			if (info.indexOf("Homer") != -1 && info.indexOf("Simpson") != -1 && info.indexOf("Computer Science") != -1)
				return true;

		}catch (Exception se)
		{
			System.err.println("SQL Exception in main(). " +
					"<Message>: " + se.getMessage());
		}
		return false;
	}	


	boolean TestdeleteDept()
	{
		try 
		{
			System.out.println("-----------------------------------------------");
			System.out.println("-----------------------------------------------");
			System.out.println("Testing deleteDept() method...");
			System.out.println("Deleting record ('104', 'Mech. Eng.')");

			boolean success1 = a2.deleteDept("104");

			System.out.println("Return Value: " + success1);

			System.out.println("Deleting record ('105', 'non existing dept')");

			boolean success2 = a2.deleteDept("105");

			System.out.println("Return Value: " + success2);

			// verify from database directly
			Statement stmt = null;
			ResultSet rs = null;
			int dept_count = 0;
			int count = 0;
			String queryString;

			// verify total no of rows and newly inserted from department table
			stmt = conn.createStatement();
			queryString = "select count(*) from department";
			rs = stmt.executeQuery(queryString);

			if ( rs.next() )
				dept_count = rs.getInt(1);

			queryString = "select count(*) from department where " +
					" dname = 'Mech. Eng.'";
			rs = stmt.executeQuery(queryString);

			if ( rs.next() )
				count = rs.getInt(1);

			if ( success1 == true && success2 == false && 
					dept_count == 4 && count == 0)
				return true;
			}
		catch (SQLException se)
		{
			System.err.println("SQL Exception in main(). " +
					"<Message>: " + se.getMessage());
		}
		return false;
	}

	
	// This method compares output against hard coded values
	boolean TestchgDept()
	{
		try{

		System.out.println("-----------------------------------------------");
		System.out.println("-----------------------------------------------");
		System.out.println("Testing chgDept() method...");
		boolean retval = a2.chgDept("101", "Math");
		System.out.println("Return Value: " + retval);
		
		Statement stmt = null;
		ResultSet rs = null;
		String new_name = "";
		String queryString = "select dname from department where dcode = '101'";
		stmt = conn.createStatement();
		rs = stmt.executeQuery(queryString);

		if ( rs.next() )
			new_name = rs.getString(1);
		
		if (new_name.indexOf("Math") != -1)
			return true;

		}catch(SQLException se)
		{
			System.err.println("SQL Exception in main(). " +
					"<Message>: " + se.getMessage());
		}
		return false;
	}

	// This method lists the courses of a student with a given sid
	boolean TestlistCourses()
	{
		try{
			System.out.println("-----------------------------------------------");
			System.out.println("-----------------------------------------------");
			System.out.println("Testing listCourses() method...");
			System.out.println("Listing courses for student 5001...");
			String courses = a2.listCourses(5001);
			System.out.println("Return Value: \n" + courses);

			Vector vec = new Vector();
			vec.add("Intro. to Prog.:Computer Science:5:2005:82");
			vec.add("Intro. to Algorithm:Computer Science:9:2005:78");
			vec.add("Intro. to Databases:Computer Science:9:2006:0");
			vec.add("Intro. to Databases:Computer Science:1:2005:80");
 			vec.add("Intro. to Databases:Computer Science:5:2005:81");
			vec.add("Discrete Mathematics:Mathematics:1:2005:95");
			
			System.out.println("\nCorrect Value: \n" + vec.elementAt(0) + "#\n" +
					vec.elementAt(1) + "#\n" +vec.elementAt(2) + "#\n" +vec.elementAt(3) + "#\n" +
					vec.elementAt(4) + "#\n" +vec.elementAt(5));
			
		}catch (Exception se)
		{
			System.err.println("SQL Exception in main(). " +
					"<Message>: " + se.getMessage());
		}
		return true;
	}
	
	// This method compares output against hard coded values
	boolean TestupdateGrades()
	{
		try
		{
		System.out.println("-----------------------------------------------");
		System.out.println("-----------------------------------------------");
		System.out.println("Testing updateGrades() method...");
		boolean retval = a2.updateGrades(404);
		System.out.println("Return Value: " + retval);

		if ( retval != true )
			return false;

		// verify from database directly
		Statement stmt = null;
		ResultSet rs = null;
		int dept_count = 0;
		int count = 0;
		String queryString;
		int sid;
		float grade;

		// verify total no of rows and newly inserted from department table
		stmt = conn.createStatement();
		queryString = "select sid, grade from studentcourse where csid = '404'";
		rs = stmt.executeQuery(queryString);
		
		System.out.println("Return values: ");
		for ( int index = 0; rs.next(); index++)
		{	
			sid = rs.getInt(1);
			grade = rs.getFloat(2);
			System.out.println("\n" + sid + " " + grade);
		}
		System.out.println("Correct Values: ");
		System.out.println("\n5001 85.8 \n5011 77.0 \n5301 99.0");

		}
		catch (SQLException se)
		{
			System.err.println("SQL Exception in main(). " +
					"<Message>: " + se.getMessage());
		}
		return true;
	}

	boolean Testquery7()
	{
		try{
			System.out.println("--------------------------------------");
			System.out.println("--------------------------------------");
			System.out.println("Testing query7() method...");
			
			String result = a2.query7();
			System.out.println("Return Value: \n" + result);

			Vector vec = new Vector();

			vec.add("Intro. to Algorithm:9:2005:79.33");
			vec.add("Intro. to Prog.:5:2005:75.00");			
			System.out.println("\nCorrect Value: \n" + vec.elementAt(0) + "#\n" + vec.elementAt(1));
			
		}catch(Exception se){
			System.err.println("SQL Exception in main(). " +
					"<Message>: " + se.getMessage());
		}
		return true;	
	}

	boolean TestupdateDB()
	{
		try{
			System.out.println("------------------------------------");
			System.out.println("------------------------------------");
			System.out.println("Testing updateDB() method...");

			boolean result = a2.updateDB();
			
			Statement stmt = null;
			ResultSet rs = null;
			int dept_count = 0;
			int count = 0;
			String queryString;
			int sid = 0;
			String fname = "";
			String lname = "";
			// verify total no of rows and newly inserted from department table
			stmt = conn.createStatement();
			queryString = "select * from femaleStudents";
			rs = stmt.executeQuery(queryString);

			if ( rs.next() )
			{
				sid = rs.getInt(1);	
				fname = rs.getString(2);
				lname = rs.getString(3);
			}
			if (sid == 5411 && fname.indexOf("Olivia") != -1 && lname.indexOf("Benson") != -1)
				return true;

		}catch(SQLException se)
		{
			System.err.println("SQL Exception in main(). " +
				"<Message>: " + se.getMessage());
		}
		return false;
	}
	
	
	boolean TestdisconnectDB()
	{
		System.out.println("---------------------------------------------");
		System.out.println("-----------------------------------------------");
		System.out.println("Testing disconnectDB() method...");
		boolean success = a2.disconnectDB();
		System.out.println("Return Value: " + success);

		if ( success == true )
			return true;

		return false;
		
	}

	void printResults()	
	{
		String str;
		
		System.out.println("-----------------------------------------------");
		System.out.println("                     Summary                   ");
		System.out.println("-----------------------------------------------");
		for ( int i = 0; i < 11; i++ )
		{
			str = getTestStatus(result[i]);	
			System.out.println(method[i] + "\t" + str);
		}	
	}

	String getTestStatus(boolean val)
	{
		if (val == true)
			return "SUCCESS";
		else
			return "FAILURE";
	}	
}