#! /bin/bash

##################################################
# Before running this script, please connect to 
# a Postgres database server first:
#          ssh db2srv1
# and change the 'database' and 'username' variables.
##################################################


########Test a single student###########
students=$1   #a single student

a2results=a2.results 
current=`pwd` #current working path

########Change the following database name################
database=csc343h-yourid

#######Change the following username####################
username=yourid

total=0

##student list in current working path
list=studentList 

rm $list
touch $list

##the directory containing all test results
allresultsPath=a2-allresults

##test the existence of the directory
if [ ! -d $allresultsPath ]
then
    mkdir $allresultsPath
fi

for name in $students
do
	path=$current/A2/$name/A2/
	if [ -d $path ]
	then
		echo ********** OK *************>> $results
	else
		path=$current/A2/$name/A2-late/
	fi
	
	if [ -d $path ] 
	then
		echo ************* DIRECTORY FOUND ***********>> $results
		cd $path
		total=`expr $total + 1`
		results=$name.$a2results
		rm $results
		rm $results.ps
 
		touch $results
		
		delay=0

		echo $name >>$current/$list	

		echo "********Test the SQL part of Assignment 2 for $name********" >>$results
		echo >>$results

	
		echo *********Test existence of files**********>>$results
		echo >>$results
		if [ ! -f Assignment2.java ]; then
		    echo "Unable to find Assignment2.java ... " >>$results
		    else
		    echo "Assignment2.java exists.">>$results
		fi
		if [ ! -f a2sql ]; then
		    echo "Unable to find a2sql ... ">>$results
		    else
		    echo "a2sql exists.">>$results
		fi
		if [ ! -f a2drop ]; then
		    echo "Unable to find a2drop ... ">>$results
		    else
		    echo "a2drop exists.">>$results
		fi
		if [ ! -f a2tables ]; then
		    echo "Unable to find a2tables ... ">>$results
		    else
		    echo "a2tables exists.">>$results
		fi
		echo >> $results

		#echo ***********Find the submission time********>>$results
		#echo >>$results
		#submission=`stat -c%Y $submit_path/$name/A2/a2sql`
		#due=`date --date='19 November 2006 13:00:00' +%s`
		#difference=`expr $submission - $due`
		#if [ $difference -gt 0 ]
		#then
		#    delay=`expr \( $difference / \( 60 \* 60 \) \) + 1`
		#else
		#    delay=0
		#fi
		#echo **********This submission is late by $delay hr\(s\).********>>$results
		#echo >>$results

	
		echo "********Create all the tables from A2.DDL************" >>$results
		psql -d $database -f $current/A2.DDL
		echo $?
		if [ "$?" != 0 ]
		    then echo ********Failed to create tables from A2.DDL********>>$results 2>&1
		fi
		echo "=============================================">>$results
		echo >>$results

		echo "********Populate the sample instances from A2-inserts.sql************" >>$results
		psql -d $database -f $current/A2_inserts.sql
		echo $?
		if [ "$?" != 0 ]
		    then echo ********Failed to populate instances from A2-inserts.sql********>>$results 2>&1
		fi
		echo "=============================================">>$results
		echo >>$results

		echo "******Drop all the output tables, Query1, Query2,..., Query7, and hasPrereqs********" >>$results
		psql -d $database -f $current/a2drop.allOutputTables 
		echo "=============================================">>$results
		echo >>$results

	     	echo "********Create all output tables, Query1,..., Query7 from a2tables********">>$results
		psql -d $database -f a2tables 
		echo "=============================================">>$results
		echo >>$results

		echo "********Run a2sql*********">>$results
		psql -d $database -f a2sql
		echo "=============================================">>$results
		echo >>$results

		echo **********RUNNING THE SOLUTION**************
		psql -d $database -f $current/A2_sol.sql
	
		echo "******** YOUR RESULT FOR Q1********">>$results
        	psql -d $database -c "select * from Query1;" >>$results 2>&1
		echo >>$results
		echo "******** EXPECTED RESULT FOR Q1********">>$results
        	psql -d $database -c "select * from Query1Ans;" >>$results 2>&1
        	echo "=============================================">>$results
        	echo >>$results

		echo "******** YOUR RESULT FOR Q2********">>$results
        	psql -d $database -c "select * from Query2;" >>$results 2>&1
		echo >>$results
		echo "******** EXPECTED RESULT FOR Q2********">>$results
        	psql -d $database -c "select * from Query2Ans;" >>$results 2>&1
        	echo "=============================================">>$results
        	echo >>$results

		echo "******** YOUR RESULT FOR Q3********">>$results
        	psql -d $database -c "select * from Query3;" >>$results 2>&1
		echo >>$results
		echo "******** EXPECTED RESULT FOR Q3********">>$results
        	psql -d $database -c "select * from Query3Ans;" >>$results 2>&1
        	echo "=============================================">>$results
        	echo >>$results

		echo "******** YOUR RESULT FOR Q4********">>$results
        	psql -d $database -c "select * from Query4;" >>$results 2>&1
		echo >>$results
		echo "******** EXPECTED RESULT FOR Q4********">>$results
        	psql -d $database -c "select * from Query4Ans;" >>$results 2>&1
        	echo "=============================================">>$results
        	echo >>$results

		echo "******** YOUR RESULT FOR Q5********">>$results
        	psql -d $database -c "select * from Query5;" >>$results 2>&1
		echo >>$results
		echo "******** EXPECTED RESULT FOR Q5********">>$results
        	psql -d $database -c "select * from Query5Ans;" >>$results 2>&1
        	echo "=============================================">>$results
        	echo >>$results

		echo "******** YOUR RESULT FOR Q6********">>$results
        	psql -d $database -c "select * from Query6;" >>$results 2>&1
		echo >>$results
		echo "******** EXPECTED RESULT FOR Q6********">>$results
        	psql -d $database -c "select * from Query6Ans;" >>$results 2>&1
        	echo "=============================================">>$results
        	echo >>$results

		echo "******** YOUR RESULT FOR Q7********">>$results
        	psql -d $database -c "select * from Query7;" >>$results 2>&1
		echo >>$results
		echo "******** EXPECTED RESULT FOR Q7********">>$results
        	psql -d $database -c "select * from Query7Ans;" >>$results 2>&1
        	echo "=============================================">>$results
        	echo >>$results
		

		echo ************Testing JDBC part ******************

		echo "********Create all the tables from A2.DDL************" >>$results
		psql -d $database -f $current/A2.DDL
		echo $?
		if [ "$?" != 0 ]
		    then echo ********Failed to create tables from A2.DDL********>>$results 2>&1
		fi
		echo "=============================================">>$results
		echo >>$results

		echo "********Populate the sample instances from A2-inserts.sql************" >>$results
		psql -d $database -f $current/A2_inserts.sql
		echo $?
		if [ "$?" != 0 ]
		    then echo ********Failed to populate instances from A2-inserts.sql********>>$results 2>&1
		fi
		echo "=============================================">>$results
		echo >>$results

		echo "********Drop all the views by a2drop************" >>$results
		psql -d $database -f a2drop 
		echo "=============================================">>$results
		echo >>$results

		echo "******Drop all the output tables, Query1, Query2,..., Query7********" >>$results
		psql -d $database -f $current/a2drop.allOutputTables 
		echo "=============================================">>$results
		echo >>$results

	     	echo "********Create all output tables, Query1,..., Query7 from a2tables********">>$results
		psql -d $database -f a2tables 
		echo "=============================================">>$results
		echo >>$results

		echo >>$results
		echo >>$results
		echo ************Testing JDBC part******************>>$results
		echo >>$results

		javac Assignment2.java >>$results 2>&1
		
		cp $current/TestAssignment2.java TestAssignment2.java

		javac TestAssignment2.java

		java TestAssignment2 $database $username >>$results 2>&1
	    	
		rm -rf *.class 

		echo "*********Drop all view by a2drop**********"
		psql -d $database -f a2drop
		
		echo enscript $name\'s resutls into PS file
		enscript -2r -G -b$name -p$results.ps $results

		echo "*********Copying results to a2-allresults directory*****"
		cp $results $current/$allresultsPath/
		cp $results.ps $current/$allresultsPath/
	else
		echo *************** DIRECTORY CANNOT BE FOUND ************>>$results
	fi
done
echo $total