import java.sql.*; // JDBC stuff.
import java.io.*;  // Reading user input.



public class StudentPortal


{
    /* This is the driving engine of the program. It parses the
     * command-line arguments and calls the appropriate methods in
     * the other classes.
     *
     * You should edit this file in two ways:
     * 	1) 	Insert your database username and password (no @medic1!)
     *		in the proper places.
     *	2)	Implement the three functions getInformation, registerStudent
     *		and unregisterStudent.
     */




    public static void main(String[] args)
    {
        if (args.length == 1) {
            try {

                //Connects to the database
                DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
                String url = "jdbc:oracle:thin:@db.student.chalmers.se:1521/kingu.ita.chalmers.se";
                String userName = "htda357_073"; // Your username goes here!
                String password = "htda357_073"; // Your password goes here!*/
                Connection conn = DriverManager.getConnection(url, userName, password);


                String student = args[0]; // This is the identifier for the student.

                BufferedReader input = new BufferedReader(new InputStreamReader(System.in));

                System.out.println("\nWelcome, student #" + args[0]);
                while(true) {
                    System.out.println("\nPlease choose a mode of operation: \ninformation, register, unregister or quit");
                    System.out.print("? > ");
                    String mode = input.readLine();
                    if ((new String("information")).startsWith(mode.toLowerCase())) {
						/* Information mode */
                        getInformation(conn, student);
                    } else if ((new String("register")).startsWith(mode.toLowerCase())) {
						/* Register student mode */
                        System.out.print("Register for what course? > ");
                        String course = input.readLine();
                        registerStudent(conn, student, course);
                    } else if ((new String("unregister")).startsWith(mode.toLowerCase())) {
						/* Unregister student mode */
                        System.out.print("Unregister from what course? > ");
                        String course = input.readLine();
                        unregisterStudent(conn, student, course);
                    } else if ((new String("quit")).startsWith(mode.toLowerCase())) {
                        System.out.println("Goodbye!");
                        break;
                    } else {
                        System.out.println("Unknown argument, please choose either " +
                                "information, register, unregister or quit!");
                        continue;
                    }
                }
                conn.close();
            } catch (SQLException e) {
                System.err.println(e);
                System.exit(2);
            } catch (IOException e) {
                System.err.println(e);
                System.exit(2);
            }


        } else {

            System.err.println("Wrong number of arguments");
            System.exit(3);
        }
    }

    static void getInformation(Connection conn, String student) throws SQLException {


        System.out.println("Information for student " + student);
        System.out.println("------------------------------------");

        // Declare statement
        Statement statement = conn.createStatement();

        // Fetch and print basic student info
        ResultSet results_basic = statement.executeQuery("SELECT studentname, programmename, branchname FROM STUDENTSFOLLOWING WHERE STUDENTSFOLLOWING.idd = " + student);
        results_basic.next();
        System.out.println("Name: " + results_basic.getString("studentname"));
        System.out.println("Programme: " + results_basic.getString("programmename"));
        System.out.println("Branch: " + results_basic.getString("branchname"));
        results_basic.close();

        // Fetch and print read courses
        ResultSet results_courses = statement.executeQuery("SELECT coursename, code, credits, grade FROM PASSEDCOURSES WHERE PASSEDCOURSES.idd = " + student);
        System.out.println("\nRead courses (name (code), credits: grade)");
        while(results_courses.next()) {
            System.out.println(" " + results_courses.getString("coursename") + " ("
                    + results_courses.getString("code") + "),"
                    + results_courses.getString("credits") + ": "
                    + results_courses.getString("grade") + "");
        }
        results_courses.close();

        // Fetch course and registration status
        ResultSet results_regstatus = statement.executeQuery("SELECT REGISTRATIONS.coursename, REGISTRATIONS.code, credits, status " +
                "FROM REGISTRATIONS LEFT JOIN AllCourses ON REGISTRATIONS.code = AllCourses.code " +
                "WHERE REGISTRATIONS.idd = " + student);
        System.out.println("\nRegistered courses (name (code), credits: status)");
        while(results_regstatus.next()) {
            System.out.println(" " + results_regstatus.getString("coursename") + " ("
                    + results_regstatus.getString("code") + "), "
                    + results_regstatus.getString("credits") + ": "
                    + results_regstatus.getString("status") + "");
        }
        results_regstatus.close();

        // Fetch Research and Math credits, and passed seminar courses.
        ResultSet results_re_ma_se = statement.executeQuery("SELECT passedmathcredits, passedresearchcredits, passedseminarcourses, passedcredits, qualifiesforgraduation " +
                "FROM PATHTOGRADUATION WHERE PATHTOGRADUATION.idd = " + student);
        results_re_ma_se.next();
        System.out.println("\nSeminar courses taken = " + results_re_ma_se.getString("passedseminarcourses"));
        System.out.println("Math credits taken  = " + results_re_ma_se.getString("passedmathcredits"));
        System.out.println("Research credits taken = " + results_re_ma_se.getString("passedresearchcredits"));
        System.out.println("Total credits taken = " + results_re_ma_se.getString("passedcredits"));
        System.out.println("Fulfills the requirements for graduation = " + results_re_ma_se.getString("qualifiesForGraduation"));
        results_re_ma_se.close();
        System.out.println("------------------------------------");


    }

    static void registerStudent(Connection conn, String student, String course)


    {

        try {

            Statement statement = conn.createStatement();
            Statement statement_2 = conn.createStatement();


            //Fetches full info for student and Course based on primary keys
            ResultSet student_map = statement.executeQuery("SELECT studentname FROM STUDENTSFOLLOWING WHERE STUDENTSFOLLOWING.idd = " + student);
            ResultSet courses_map = statement_2.executeQuery("SELECT coursename FROM ALLCOURSES WHERE AllCourses.code = " + course);
            student_map.next();
            String student_name = student_map.getString("studentname");
            student_map.close();
            courses_map.next();
            String course_name = courses_map.getString("coursename");
            courses_map.close();


            //Executes query to register student
            statement.executeUpdate("INSERT INTO Registrations VALUES ('" + student_name + "',  '" + student + "',  '" + course_name + "',  '" + course + "',  'registered')");


            ResultSet statusResults = statement.executeQuery("SELECT status, queuenumber FROM REGISTRATIONS, WAITING WHERE REGISTRATIONS.idd = " + student + " AND REGISTRATIONS.code = " + course);

            statusResults.next();
            System.out.println("You are now  " + statusResults.getString("status") + " for course " + course_name);
            if (statusResults.getString("status").equals("waiting")){
                System.out.print(" with the queue number " + statusResults.getString("queuenumber"));

            }
            statusResults.close();
            // Course TDA381 Concurrent Programming is full, you are put in the waiting list as number 2.



        } catch (SQLException e) {
            System.out.println("ERROR CODE = " + e.getErrorCode());

            if (e.getErrorCode() == 20001){
                System.out.println("You're already enrolled to this course");
            }
            else if (e.getErrorCode() == 20003){
                System.out.println("You do not have the prerequisites");
            }

            e.printStackTrace();

        }
    }


    static void unregisterStudent(Connection conn, String student, String course)
    {

            try {

                Statement statement = conn.createStatement();

                //Executes query to register student
                statement.executeUpdate("DELETE FROM Registrations WHERE ('" + student + "' =  idd  AND '" + course + "' =  code)");
                // Fix this
                System.out.println("Successfully deleted student ID #'" +student+ "' from course code '" +course+ "'");


            } catch (SQLException e) {
                e.printStackTrace();
            }
    }
}