import java.sql.*;
import com.sybase.jdbcx.SybDriver;
import java.io.*;
import java.util.*;
import java.io.InputStream;
import java.io.InputStreamReader;

//Jason Kim
//CSE670 AU 05
//Assignment 8
class DBnav {
  public static void main (String args[]) {
 BufferedReader buffr;
	  //get sybase driver
	  try {
      SybDriver sybdriver =
      (SybDriver) Class
         . forName("com.sybase.jdbc2.jdbc.SybDriver")
         . newInstance();
sybdriver.setVersion(
     com.sybase.jdbcx.SybDriver.VERSION_5 );
DriverManager.registerDriver(sybdriver);
//get connection
      Connection connection = null;
String server = "database";
String port = "4101";
String database = "c670aa";
String username = args[0];
String password = args[1];
String url =   "jdbc:sybase:Tds:"
             + server + ":"
             + port + "/"
             + database;
connection =
  DriverManager.getConnection(url, username, password);

      Statement stat = connection.createStatement(
     ResultSet.TYPE_SCROLL_SENSITIVE,
     ResultSet.CONCUR_READ_ONLY);
		//set tables up from figure 1.2 pg 7


		//create Student Table
		stat.executeUpdate(
     "CREATE TABLE STUDENT (Name CHAR(10), StudentNumber INT(10), Class INT(10), Major CHAR(10), PRIMARY KEY(StudentNumber))" );
	//insert values into student
		stat.executeUpdate(
     "INSERT INTO STUDENT VALUES ( 'Smith', '17', '1', 'CS')" );
		stat.executeUpdate(
	 "INSERT INTO STUDENT VALUES ( 'Brown', '8', '2', 'CS')");

		//create Course Table
		stat.executeUpdate(
	"CREATE TABLE COURSE (CourseName CHAR(30), CourseNumber VARCHAR(30), CreditHours INT(10), Department CHAR(10), PRIMARY KEY(CourseNumber))");
// insert values into course
		stat.executeUpdate(
	"INSERT INTO COURSE VALUES ( 'Intro to Computer Science', 'CS1310', '4', 'CS' )");
		stat.executeUpdate(
	"INSERT INTO COURSE VALUES ( 'Data Structures', 'CS3320', '4', 'CS' )");
		stat.executeUpdate(
	"INSERT INTO COURSE VALUES ( 'Discrete Mathematics', 'MATH2410', '3', 'MATH' )");
		stat.executeUpdate(
	"INSERT INTO COURSE VALUES ( 'Database', 'CS3380', '3', 'CS' )");

		//create section table
		stat.executeUpdate(
	"CREATE TABLE SECTION (SectionIdentifier INT(10), CourseNumber VARCHAR(30), Semester CHAR(10), Year CHAR(10), Instructor CHAR(20), PRIMARY KEY(SectionIdentifier), FOREIGN KEY(CourseNumber) REFERENCES COURSE(CourseNumber))");
// insert values into section
		stat.executeUpdate(
	"INSERT INTO SECTION VALUES ('85','MATH2410','Fall','98','King')");
		
		stat.executeUpdate(
			"INSERT INTO SECTION VALUES ('92','CS1310','Fall','98','Anderson')");
		
		stat.executeUpdate(
			"INSERT INTO SECTION VALUES ('102','CS3320','Spring','99','Knuth')");
		
		stat.executeUpdate(
			"INSERT INTO SECTION VALUES ('112','MATH2410','Fall','99','Chang')");
		
		stat.executeUpdate(
			"INSERT INTO SECTION VALUES ('119','CS1310','Fall','99','Anderson')");
		
		stat.executeUpdate(
			"INSERT INTO SECTION VALUES ('135','CS3380','Fall','99','Stone')");

		//create table grade_report
		stat.executeUpdate(
	"CREATE TABLE GRADE_REPORT (StudentNumber INT(10), SectionIdentifier INT(10), Grade CHAR(10), FOREIGN KEY(StudentNumber) REFERENCES STUDENT(StudentNumber), FOREIGN KEY(SectionIdentifier) REFERENCES SECTION(SectionIdentifier))");
//insert values into table
		stat.executeUpdate(
			"INSERT INTO GRADE_REPORT ('17','112','B')");
		stat.executeUpdate(
			"INSERT INTO GRADE_REPORT ('17','119','C')");
		stat.executeUpdate(
			"INSERT INTO GRADE_REPORT ('8','85','A')");
		stat.executeUpdate(
			"INSERT INTO GRADE_REPORT ('8','92','A')");
		stat.executeUpdate(
			"INSERT INTO GRADE_REPORT ('8','102','B')");
		stat.executeUpdate(
			"INSERT INTO GRADE_REPORT ('8','135','A')");
//create table Prequisite
		stat.executeUpdate(
	"CREATE TABLE PREREQUISITE (CourseNumber INT(10), PrequisiteNumber VARCHAR(30), FOREIGN KEY(CourseNumber) REFERENCES COURSE(CourseNumber), FOREIGN KEY(PrequisiteNumber) REFERENCES COURSE(CourseNumber))");
//insert values into table
		stat.executeUpdate(
     "INSERT INTO PREREQUISITE VALUES ( 'CS3380', 'CS3320')" );
		stat.executeUpdate(
			"INSERT INTO PREREQUISITE VALUES ( 'CS3380', 'MATH2410')" );
		stat.executeUpdate(
			"INSERT INTO PREREQUISITE VALUES ( 'CS3320', 'CS1310')" );

		  //read student name 
		  // Read a line of text from the user.
		  InputStreamReader isr = new InputStreamReader(System.in);
		  buffr = new BufferedReader(isr);//input from console
		  String inputname = buffr.readLine();

		  //run queries
		ResultSet query =
			stat.executeQuery("SELECT * FROM STUDENT WHERE Name='" + inputname + "'");
		  String snum = query.getString("StudentNumber");
		  stat.executeQuery("SELECT * FROM GRADE_REPORT WHERE StudentNumber='" + snum + "'");
		  String grade;
		  double gpa=0,total=0,numclass=0;
		  while( query.relative( 1 ) ){
  
			  grade = query.getString( "Grade" );
			  if(grade=="A")
			  {
				  total=total+4;
			  }
			  else if(grade=="B")
			  {
				  total=total+3;
			  }
			  else if(grade=="C")
			  {
				  total=total+2;
			  }
			  else if(grade=="D")
			  {
				  total=total+1;
			  }
			  else
				  total=total+0;

			  numclass=numclass+1;
			  }
		
		gpa = total/numclass;

		System.out.println("Student Name: " + inputname + " GPA is: " + gpa);
		stat.close();
      
		connection.close();
    }
    catch (Exception e) { System.out.println( "error " + e ); }
} }