package preexer1;

import java.util.Scanner;
import java.sql.*;
import java.text.*;
public class PreExer1 {

    private static Statement st;
    private static ResultSet rs;
    private static Connection con;
    String smonth, syear;
    static Scanner in = new Scanner(System.in);

    public PreExer1() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://10.4.45.174:3306/information_schema", "root", "root");
            st = con.createStatement();
        } catch (Exception ex) {
            System.out.println("Error: " + ex);

 }
    }
    public static void login(String user, String pass) {
        try {
            Scanner scan = new Scanner(System.in);
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://10.4.45.174:3306/information_schema", ""+user+"", ""+pass+"");
            st = con.createStatement();
            String query = "show databases";
            rs = st.executeQuery(query);
            while(rs.next()) {
                System.out.println(rs.getString(1));
            }

            System.out.print("Database: ");
            String db = scan.next();

            query = "use "+db+"";
            st.executeUpdate(query);

            view(user);
        } catch (Exception ex) {
            System.out.println("Error: " + ex);
        }
    }


    public static void Grades() {
        try {
            int userID;
            System.out.println("Enter Student ID: ");
            int studid = in.nextInt();
            String query = "SELECT distinct code, prelim, midterm, prefinal, round((prelim+midterm+preFinal)/3,0) as final FROM grades, subjects, students, enrolled where(students.SID ="+studid+" = enrolled.Students_sid and subjects.subid = enrolled.Subjects_subid and enrolled.eid = grades.Enrolled_eid)";
            rs = st.executeQuery(query);
            System.out.println("SubjCode----Prelim----Midterm----PFinal");
            while(rs.next()) {
                String c = rs.getString("code");
                String pr = rs.getString("prelim");
                String mid = rs.getString("midterm");
                String pf = rs.getString("prefinal");
                String f = rs.getString("FINAL");
                System.out.println(c + "----------" + pr + "----------" + mid + "----------" + pf + "----------" + f);
            }
        }catch(Exception ex) {
            System.out.println(ex);
        }
    }

    public static void classList() {
         try {
            System.out.println("Enter Subject ID: ");
            int subid = in.nextInt();
            String query = "select distinct sid, Gender, lastname, firsntame, middlename from subjects, enrolled, students where("+subid+" =Enrolled.Subjects_subid and students.sid=enrolled.Students_sid)";

            rs = st.executeQuery(query);
            System.out.println("Studid   Studname   StudGender");
            while (rs.next()) {
                String s = rs.getString("sid");
                String f = rs.getString("firsntame");
                String g = rs.getString("Gender");
                System.out.println(s + "----------" + f + "----------" + g);
            }
        } catch(Exception ex) {
            System.out.println(ex);
        }
    }

    public static void insertGrades() {
        try {
            System.out.print("Enter Students ID: ");
            int sid = in.nextInt();
            System.out.print("Enter Subject ID: ");
            int subid = in.nextInt();
            System.out.print("Enter Prelim Grade: ");
            int prelim = in.nextInt();
            System.out.print("Enter Midterm Grade: ");
            int midterm = in.nextInt();
            System.out.print("Enter Prefinal Grade: ");
            int prefinal = in.nextInt();


            int eid = 0;
            String query = "select eid from enrolled where("+sid+" = enrolled.Students_sid and "+subid+" =enrolled.Subjects_subid)";
            rs = st.executeQuery(query);

            while(rs.next()) {
                eid = rs.getInt("eid");
            }
            query = "insert into grades values("+prelim+","+midterm+","+prefinal+","+eid+")";
            st.executeUpdate(query);

        }catch(Exception ex) {
            System.out.println(ex);
        }
    }



    public void getTime(){
        try {
            String query = "select month(now()) as month, year(now()) as year";
            rs = st.executeQuery(query);

            rs.next();
            smonth = rs.getString("month");
            syear = rs.getString("year");
            int m = Integer.valueOf(smonth);
            int y = Integer.valueOf(syear);

            createTable(m,y);
        }
        catch(Exception ex) {
            System.out.println(ex);
        }
    }

    public static void createTable(int month, int year) {
        String x = null;
        if(month!=6 && month!=10) {
            System.out.print("Enrollment Closed");
        }

        if(month==6){
            x = "1st";
        }
        else if(month==10){
            x = "2nd";
        }
        System.out.println();

        String y = "Sy"+year+""+x+"sem";

        try {
            String fsem = "CREATE SCHEMA IF NOT EXISTS `"+y+"` DEFAULT CHARACTER SET utf8 ";
            st.executeUpdate(fsem);
            fsem = "USE `"+y+"`";
            st.executeUpdate(fsem);
            fsem = "CREATE TABLE IF NOT EXISTS `"+y+"`.`students` (\n" +
                   "  `sid` INT NOT NULL AUTO_INCREMENT,\n" +
                   "  `firstname` VARCHAR(45) NULL,\n" +
                   "  `middlename` VARCHAR(45) NULL,\n" +
                   "  `lastname` VARCHAR(45) NULL,\n" +
                   "  `Gender` VARCHAR(10) NULL,\n" +
                   "  `Address` VARCHAR(45) NULL,\n" +
                   "  PRIMARY KEY (`sid`))\n" +
                   "ENGINE = InnoDB";
            st.executeUpdate(fsem);
            fsem = "CREATE TABLE IF NOT EXISTS `"+y+"`.`Teachers` (\n" +
                    "`tid` INT NOT NULL AUTO_INCREMENT,\n" +
                    "`firstname` VARCHAR(45) NULL,\n" +
                    "`middlename` VARCHAR(45) NULL,\n" +
                    "`lastname` VARCHAR(45) NULL,\n" +
                    "`Division` VARCHAR(45) NULL,\n" +
                    "PRIMARY KEY (`tid`))\n" +
                    "ENGINE = InnoDB";
            st.executeUpdate(fsem);
            fsem = "CREATE TABLE IF NOT EXISTS `"+y+"`.`Subjects` (\n" +
                   "`subid` INT NOT NULL AUTO_INCREMENT,\n" +
                   "`code` VARCHAR(45) NULL,\n" +
                   "`description` VARCHAR(45) NULL,\n" +
                   "`units` INT NULL,\n" +
                   "`isMajor` TINYINT(1) NULL,\n" +
                   "`Teachers_tid` INT NOT NULL,\n" +
                   "PRIMARY KEY (`subid`),\n" +
                   "INDEX `fk_Subjects_Teachers1_idx` (`Teachers_tid` ASC),\n" +
                   "CONSTRAINT `fk_Subjects_Teachers1`\n" +
                   "FOREIGN KEY (`Teachers_tid`)\n" +
                   "REFERENCES `"+y+"`.`Teachers` (`tid`)\n" +
                   "ON DELETE NO ACTION\n" +
                   "ON UPDATE NO ACTION)\n" +
                   "ENGINE = InnoDB";
            st.executeUpdate(fsem);
            fsem = "CREATE TABLE IF NOT EXISTS `"+y+"`.`Enrolled` (\n" +
                   "`eid` INT NOT NULL AUTO_INCREMENT,\n" +
                   "`students_sid` INT NOT NULL,\n" +
                   "`Subjects_subid` INT NOT NULL,\n" +
                   "PRIMARY KEY (`eid`),\n" +
                   "INDEX `fk_Enrolled_students_idx` (`students_sid` ASC),\n" +
                   "INDEX `fk_Enrolled_Subjects1_idx` (`Subjects_subid` ASC),\n" +
                   "CONSTRAINT `fk_Enrolled_students`\n" +
                   "FOREIGN KEY (`students_sid`)\n" +
                   "REFERENCES `"+y+"`.`students` (`sid`)\n" +
                   "ON DELETE NO ACTION\n" +
                   "ON UPDATE NO ACTION,\n" +
                   "CONSTRAINT `fk_Enrolled_Subjects1`\n" +
                   "FOREIGN KEY (`Subjects_subid`)\n" +
                   "REFERENCES `"+y+"`.`Subjects` (`subid`)\n" +
                   "ON DELETE NO ACTION\n" +
                   "ON UPDATE NO ACTION)\n" +
                   "ENGINE = InnoDB\n";
            st.executeUpdate(fsem);

            fsem = "CREATE TABLE IF NOT EXISTS `"+y+"`.`Grades` (\n" +
                   "`prelim` INT NULL,\n" +
                   "`midterm` INT NULL,\n" +
                   "`prefinal` INT NULL,\n" +
                   "`Enrolled_eid` INT NOT NULL,\n" +
                   "INDEX `fk_Grades_Enrolled1_idx` (`Enrolled_eid` ASC),\n" +
                   "CONSTRAINT `fk_Grades_Enrolled1`\n" +
                   "FOREIGN KEY (`Enrolled_eid`)\n" +
                   "REFERENCES `"+y+"`.`Enrolled` (`eid`)\n" +
                   "ON DELETE NO ACTION\n" +
                   "ON UPDATE NO ACTION)\n" +
                   "ENGINE = InnoDB";
            st.executeUpdate(fsem);

            System.out.println("Database Created");

        }catch(Exception ex) {
            System.out.println(ex);
        }

    }


    public static void view(String user) {
        try {
            String query = "select * from students where sid = '"+user+"'";
            rs = st.executeQuery(query);
            while(rs.next()) {
                String fn = rs.getString("firstname");
                String mn = rs.getString("middlename");
                String ln = rs.getString("lastname");
                String g = rs.getString("gender");
                String ad = rs.getString("address");

                System.out.println("Name: " +fn + " " + mn + " " + ln);
                System.out.println("Gender: " + g);
                System.out.println("Address: " + ad);
            }
        }
        catch(Exception ex) {
            System.out.println(ex);
        }
    }

    public static void register() {
        try {
            Scanner scan = new Scanner(System.in);
            String query = "show databases";
            rs = st.executeQuery(query);

            while(rs.next()) {
                System.out.println(rs.getString(1));
            }
            System.out.print("Database: ");
            String db = scan.next();

            query = "use "+db+"";
            st.executeUpdate(query);

            System.out.print("Enter firstname: ");
            String fname = scan.next();
            System.out.print("Enter middlename: ");
            String mname = scan.next();
            System.out.print("Enter lastname: ");
            String lname = scan.next();
            System.out.print("Enter gender: ");
            String g = scan.next();
            System.out.print("Enter address: ");
            String add = scan.next();

            query = "insert into students(firstname, middlename, lastname, gender, address) values('"+fname+"', '"+mname+"', '"+lname+"', '"+g+"', '"+add+"')";
            st.executeUpdate(query);

            String user = id(fname, mname, lname, g, add);
            String pass = fname;

            System.out.println(user);

            grant(user, pass, db);


        }
        catch(Exception ex) {
            System.out.println("Error: " + ex);
        }
    }

    public static String id(String fname, String mname, String lname, String g, String add) {
        String user = null;
        try {
            String query = "select sid from students order by sid desc limit 1";
            rs = st.executeQuery(query);
            rs.next();
                user = rs.getString("sid");
        }
        catch(Exception ex) {
            System.out.println(ex);
        }

        return user;
    }

    public static void grant(String user, String pass, String db){
        try {
            String query = "create user '"+user+"'@10.4.45.171 identified by '"+pass+"'";
            st.executeUpdate(query);

            query = "grant select on "+db+".* to '"+user+"'@10.4.45.171 identified by '"+pass+"'";
             st.executeQuery(query);

        }

        catch(Exception ex) {
            System.out.println("Error: " + ex);
        }
        login(user, pass);
    }

}