import java.io.IOException;
import java.sql.*;

/**
 * Created by eli on 12/02/2017.
 */
public class ass4 {

    public static void main(String[] argv) throws SQLException, IOException {
        String userName = "user8";
        String password = "HTYH3fYS";
        String host = "jdbc:oracle:thin:@132.72.40.216:1522:orcl";          // jdbc:oracle:thin:@132.72.40.216:1522:orcl
        Connection conn = connectOracleDB(host, userName, password);
        if (conn == null) {
            return;
        }

        Statement stmt = conn.createStatement();
        dropAndCreate(stmt);
        insertDataToTables(stmt);
        testFunc(conn);

        try {
            if (stmt != null) {
                conn.close();
                System.out.println("\nConnection closed");
            }
        } catch (SQLException e) {
            System.out.println("Cannot close connection");
            e.printStackTrace();
        }

    }


    public static Connection connectOracleDB(String host, String userName, String password) {
        System.out.println("-------- Oracle JDBC Connection Testing ------");

        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");

        } catch (ClassNotFoundException e) {

            System.out.println("Where is your Oracle JDBC Driver?");
            e.printStackTrace();
            return null;

        }

        System.out.println("Oracle JDBC Driver Registered!");

        Connection connection = null;

        try {

            connection = DriverManager.getConnection(host, userName, password);

        } catch (SQLException e) {

            System.out.println("Connection Failed! Check output console");
            e.printStackTrace();
            return null;

        }

        if (connection != null) {
            System.out.println("You made it, take control your database now!");
            return connection;

        } else {
            System.out.println("Failed to make connection!");
            return null;
        }
    }

    public static void dropTables(Statement stmt) throws SQLException {

        String drop1 = "DROP TABLE EmployeesShifts";
        String drop2 = "DROP TABLE Salaries";
        String drop3 = "DROP TABLE Employees";
        String drop4 = "DROP TABLE Expertise";
        String drop5 = "DROP TABLE ShiftsTypes";
        String drop6 = "DROP TABLE Weeks";
        String drop7 = "DROP TABLE Days";

        stmt.executeUpdate(drop1);
        stmt.executeUpdate(drop2);
        stmt.executeUpdate(drop3);
        stmt.executeUpdate(drop4);
        stmt.executeUpdate(drop5);
        stmt.executeUpdate(drop6);
        stmt.executeUpdate(drop7);
    }

    public static void createTables(Statement stmt) throws SQLException {

        String createExpertiseTable = "CREATE TABLE Expertise" +
                " (expr_id INTEGER not NULL," +
                " name VARCHAR(255)," +
                " PRIMARY KEY ( expr_id ))";
        stmt.executeUpdate(createExpertiseTable);

        String createEmployeesTable = "CREATE TABLE Employees" +
                " (eid INTEGER not NULL," +
                " name VARCHAR(255)," +
                " expr_id INTEGER not NULL," +
                " PRIMARY KEY ( eid )," +
                " FOREIGN KEY ( expr_id ) references Expertise)";
        stmt.executeUpdate(createEmployeesTable);

        String createDaysTable = "CREATE TABLE Days" +
                " (did INTEGER not NULL," +
                " name VARCHAR(255)," +
                " PRIMARY KEY ( did ))";
        stmt.executeUpdate(createDaysTable);

        String createWeeksTable = "CREATE TABLE Weeks" +
                " (wid INTEGER not NULL," +
                " name VARCHAR(255)," +
                " PRIMARY KEY ( wid ))";
        stmt.executeUpdate(createWeeksTable);

        String createShiftsTable = "CREATE TABLE ShiftsTypes" +
                " (sid INTEGER not NULL," +
                " name VARCHAR(255)," +
                " quota INTEGER," +
                " curr_quota INTEGER," +
                " PRIMARY KEY (sid ))";
        stmt.executeUpdate(createShiftsTable);

        String createSalariesTable = "CREATE TABLE Salaries" +
                " (expr_id INTEGER not NULL," +
                " sid INTEGER not NULL," +
                " salary INTEGER not NULL," +
                " PRIMARY KEY ( expr_id,sid )," +
                " FOREIGN KEY (expr_id) references Expertise," +
                " FOREIGN KEY (sid) references ShiftsTypes)";
        stmt.executeUpdate(createSalariesTable);

        String createEmployeesShifts = "CREATE TABLE EmployeesShifts" +
                " (eid INTEGER not NULL," +
                " wid INTEGER not NULL," +
                " did INTEGER not NULL," +
                " sid INTEGER not NULL," +
                " PRIMARY KEY ( eid,wid,did,sid )," +
                " FOREIGN KEY (eid) references Employees," +
                " FOREIGN KEY (wid) references Weeks," +
                " FOREIGN KEY (did) references Days," +
                " FOREIGN KEY (sid) references ShiftsTypes)";
        stmt.executeUpdate(createEmployeesShifts);
    }

    public static void dropAndCreate(Statement stmt) throws SQLException {
        System.out.println("DB is droping...");
        dropTables(stmt);
        System.out.println("DB is droped...");
        System.out.println("Creating tables...");
        createTables(stmt);
        System.out.println("Tables created...");
    }

    public static void insertDataToExpertise(Statement stmt) throws SQLException {
        stmt.executeUpdate("INSERT INTO Expertise VALUES (1, 'beginner')");
        stmt.executeUpdate("INSERT INTO Expertise VALUES (2, 'regular')");
        stmt.executeUpdate("INSERT INTO Expertise VALUES (3, 'expert')");
        stmt.executeUpdate("INSERT INTO Expertise VALUES (4, 'very expert')");
        stmt.executeUpdate("INSERT INTO Expertise VALUES (5, 'very very expert')");
    }

    public static void insertDataToDays(Statement stmt) throws SQLException {
        stmt.executeUpdate("INSERT INTO Days VALUES (1, 'sunday')");
        stmt.executeUpdate("INSERT INTO Days VALUES (2, 'monday')");
        stmt.executeUpdate("INSERT INTO Days VALUES (3, 'tuesday')");
        stmt.executeUpdate("INSERT INTO Days VALUES (4, 'wednesday')");
        stmt.executeUpdate("INSERT INTO Days VALUES (5, 'thursday')");
        stmt.executeUpdate("INSERT INTO Days VALUES (6, 'friday')");
        stmt.executeUpdate("INSERT INTO Days VALUES (7, 'saturday')");
    }

    public static void insertDataToWeeks(Statement stmt) throws SQLException {
        stmt.executeUpdate("INSERT INTO Weeks VALUES (1, 'first')");
        stmt.executeUpdate("INSERT INTO Weeks VALUES (2, 'second')");
        stmt.executeUpdate("INSERT INTO Weeks VALUES (3, 'third')");
        stmt.executeUpdate("INSERT INTO Weeks VALUES (4, 'forth')");
    }

    public static void insertDataToShiftsTypes(Statement stmt) throws SQLException {
        stmt.executeUpdate("INSERT INTO ShiftsTypes VALUES (1,'dayTime',10,10)");
        stmt.executeUpdate("INSERT INTO ShiftsTypes VALUES (2,'midDay',20,20)");
        stmt.executeUpdate("INSERT INTO ShiftsTypes VALUES (3,'night',30,30)");
    }

    public static void insertDataToSalaries(Statement stmt) throws SQLException {
        stmt.executeUpdate("INSERT INTO Salaries VALUES (1,1,50)");
        stmt.executeUpdate("INSERT INTO Salaries VALUES (4,1,100)");
        stmt.executeUpdate("INSERT INTO Salaries VALUES (5,2,200)");

        //common shift
        stmt.executeUpdate("INSERT INTO Salaries VALUES (1,3,500)");
        stmt.executeUpdate("INSERT INTO Salaries VALUES (4,3,1500)");
        stmt.executeUpdate("INSERT INTO Salaries VALUES (5,3,2500)");

        // line for A-3
        stmt.executeUpdate("INSERT INTO Salaries VALUES (1,2,20)");
        stmt.executeUpdate("INSERT INTO Salaries VALUES (4,2,1700)");
    }

    public static void insertDataToTables(Statement stmt) throws SQLException {
        insertDataToExpertise(stmt);
        insertDataToDays(stmt);
        insertDataToWeeks(stmt);
        insertDataToShiftsTypes(stmt);
        insertDataToSalaries(stmt);
        System.out.println("Data inserted successfully to DB...");
    }

    // wid-week id
    // did - day id
    // sid - shift type
    public static void expensePerShift(Connection conn, String wid ,String did, String sid) throws SQLException {

        String get_expr_id;
        String get_sal;
        ResultSet rs1,rs2;
        int eid ,expr_id ,salaries_sum = 0;

        //getting all the eids of the employees that are working in that exact shift.
        String get_eids = "SELECT eid FROM EmployeesShifts WHERE wid = " + wid + " AND did= " + did + " AND sid= " + sid;
        rs1 = conn.createStatement().executeQuery(get_eids);

        // for each employee , we getting his salary.
        while (rs1.next()) {
            // getting specific employee eid
            eid = rs1.getInt("eid");
            //getting expr_id for that specific employee
            get_expr_id = "SELECT expr_id FROM Employees WHERE eid =" + eid ;
            rs2 = conn.createStatement().executeQuery(get_expr_id);
            rs2.next();
            expr_id = rs2.getInt("expr_id");
            // getting employee's salary
            get_sal = "SELECT salary FROM Salaries WHERE sid= " + sid + " AND expr_id = " + expr_id;
            rs2 = conn.createStatement().executeQuery(get_sal);
            rs2.next();
            salaries_sum = salaries_sum + rs2.getInt("salary");

        }
        System.out.println("\n\nThe total salary expenses of shift: week_id: " +wid +", day_id: "+ did + ", shift_type: "+sid);
        System.out.println(salaries_sum);

    }

    public static void employeSalary(Connection conn, String eid ,int[] weeks) throws SQLException {

        int sum =0,expr_id,sid;
        String get_expr_id,get_sids, get_salary_per_shift;
        ResultSet rs1,rs2,rs3;

        //getting expr_id for that specific employee
        get_expr_id = "SELECT expr_id FROM Employees WHERE eid =" + eid ;
        rs1 = conn.createStatement().executeQuery(get_expr_id);
        rs1.next();
        expr_id = rs1.getInt("expr_id");

        for(int i = 0; i < weeks.length; i++){
            get_sids = "SELECT sid FROM EmployeesShifts WHERE wid = " + weeks[i] +  " AND eid= " + eid;
            rs2 = conn.createStatement().executeQuery(get_sids);
            while (rs2.next()) {
                //getting shift id for that specific shift and employee
                sid = rs2.getInt("sid");
                get_salary_per_shift = "SELECT salary FROM Salaries WHERE sid= " + sid + " AND expr_id = " + expr_id;
                rs3 = conn.createStatement().executeQuery(get_salary_per_shift);
                rs3.next();
                sum = sum + rs3.getInt("salary");
            }
        }

        System.out.println("Salary for employee : " + eid + "\n salary: " + sum);
    }

    public static void employeSalaryForMonth(Connection conn, String eid ) throws SQLException {
        int[] weeks  = {1,2,3,4};
        employeSalary(conn,eid,weeks);
    }

    public static void checkEmployeeSalary(Connection conn, String eid) throws SQLException {

        int expr_id,sid,maxSalaryMorning = 0  , maxSalaryMidDay =0, maxSalaryEvening =0 ,salary;
        int[] weeks = {1,2,3,4};
        String get_expr_id,get_sids, get_salary_per_shift;
        ResultSet rs1,rs2,rs3;

        //getting expr_id for that specific employee
        get_expr_id = "SELECT expr_id FROM Employees WHERE eid =" + eid ;
        rs1 = conn.createStatement().executeQuery(get_expr_id);
        rs1.next();
        expr_id = rs1.getInt("expr_id");


        for(int i = 0; i < weeks.length; i++){
            get_sids = "SELECT sid FROM EmployeesShifts WHERE wid = " + weeks[i] +  " AND eid= " + eid;
            rs2 = conn.createStatement().executeQuery(get_sids);
            while (rs2.next()) {
                //getting shift id for that specific shift and employee
                sid = rs2.getInt("sid");
                get_salary_per_shift = "SELECT salary FROM Salaries WHERE sid= " + sid + " AND expr_id = " + expr_id;
                rs3 = conn.createStatement().executeQuery(get_salary_per_shift);
                rs3.next();

                salary = rs3.getInt("salary");
                switch(sid){

                    case 1:
                        if (salary > maxSalaryMorning)
                            maxSalaryMorning = salary;
                        break;
                    case 2:
                        if (salary > maxSalaryMidDay)
                            maxSalaryMidDay = salary;
                        break;
                    case 3:
                        if (salary > maxSalaryEvening)
                            maxSalaryEvening = salary;
                        break;

                }


            }
        }

        if (maxSalaryMorning > maxSalaryMidDay){
            System.out.println("Morning salary  >  MidDay salary");
        }
        if (maxSalaryMidDay > maxSalaryEvening){
            System.out.println("MidDay salary  >  Evening salary");
        }
    }

    public static void insertEmployeeShift(Connection conn, int eid, int wid, int did, int sid) throws SQLException {

        String get_curr_quota = "SELECT curr_quota FROM ShiftsTypes WHERE sid =" + sid ;
        ResultSet rs1 = conn.createStatement().executeQuery(get_curr_quota);
        rs1.next();
        int quota = rs1.getInt("curr_quota");

        // Getting quota from DB - inserting only if > 0.
        if (quota > 0){
            String query = "INSERT INTO EmployeesShifts VALUES ("+ eid +","+ wid +","+ did + ","+ sid+ ")";
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(query);

            quota--;

            String insert_updated_quota = "UPDATE ShiftsTypes SET curr_quota = "+ quota + " WHERE sid = "+sid ;
            stmt.executeUpdate(insert_updated_quota);
            System.out.println("Shift quota updated from: "+ (quota+1) +", to: "+ quota);
        }
        else{
            System.out.println("Sorry, the shift is full.");
        }

    }

        public static void testFunc(Connection conn) throws SQLException {
        System.out.println("\n~~~~~  Our testfunc results :  ~~~~~");

        String sql1 = "INSERT INTO Employees VALUES (301463550,'Ron Klar', 1)";
        String sql2 = "INSERT INTO Employees VALUES (204558439,'Eli Tsinberg', 4)";
        String sql3 = "INSERT INTO Employees VALUES (123456789,'Tom Weiss', 5)";


        Statement stmt = conn.createStatement();
        stmt.executeUpdate(sql1);
        stmt.executeUpdate(sql2);
        stmt.executeUpdate(sql3);

        // B-question
        insertEmployeeShift(conn,301463550,1,1,1);
        insertEmployeeShift(conn,301463550,1,2,3);
        insertEmployeeShift(conn,301463550,4,5,2);
        insertEmployeeShift(conn,204558439,2,2,2);
        insertEmployeeShift(conn,204558439,1,2,3);
        insertEmployeeShift(conn,123456789,3,3,3);
        insertEmployeeShift(conn,123456789,1,2,3);

        // A-2 question
        expensePerShift(conn,"1","2","3");  // common shift expenses
        expensePerShift(conn,"1","1","1");

        // A-2 question
        int[] weeks = {1,3};
        System.out.println("\n\nRon earn in weeks 1,3: ");
        employeSalary(conn,"301463550",weeks);
        int[] weeks1 = {2,4};
        System.out.println("\nEli earn in weeks 2,4: ");
        employeSalary(conn,"204558439",weeks1);

        System.out.println("\n\nRon earn in the whole month: ");
        employeSalaryForMonth(conn,"301463550");
        System.out.println("\nEli earn in the whole month: ");
        employeSalaryForMonth(conn,"204558439");


        //A-3 question
        System.out.println("\n\nCheck employee salary for Ron: ");
        checkEmployeeSalary(conn,"301463550");

        System.out.println("\nCheck employee salary for Eli: ");
        checkEmployeeSalary(conn,"204558439");

    }
}