package dal;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Dal {

    private Connection con;
    private Statement stat;

    String connectionUrl;

    //--------------------SKAPAR KOPPLING TILL DATABASEN--------------------//

    public Dal() {

        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            connectionUrl = "jdbc:sqlserver://Ramy\\MSSQLSERVER;" +
                    "database=DB2;" +
                    "user=ramy;" +
                    "password=123456";

            con = getConnection();
            System.out.println("Connected.");

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println(e.getMessage());
            System.exit(0);
        }
    }
    public Connection getConnection() throws SQLException {
        return con = DriverManager.getConnection(connectionUrl);
    }

    //--------------------ADDING TO THE DATABASE--------------------//

    //Add a new student to the database
    public int addStudent(String spnr, String sname) throws SQLException {
        stat = con.createStatement();
        int result = stat.executeUpdate("insert into Student values ('" + spnr + "','" + sname + "');"  );
        return result;
    }
    //Add a new course to the database
    public int addCourse(String ccode, String cname, int cpoint) throws SQLException {
        stat = con.createStatement();
        int result = stat.executeUpdate("insert into Course values ('" + ccode + "','" + cname + "','" + cpoint + "');"  );
        return result;
    }
    //Add a student to a current course in the database (register student)
    public int addStudies(String spnr, String ccode) throws SQLException {
        stat = con.createStatement();
        int result = stat.executeUpdate("insert into Studies values ('" + spnr + "','" + ccode + "');"  );
        return result;
    }
    //Add a student to a finished course in the database (register grade)

    public int addStudied(String spnr, String ccode, String grade) throws SQLException {
        stat = con.createStatement();
        int result = stat.executeUpdate("insert into Studied values ('" + spnr + "','" + ccode + "','" + grade + "');"
                + "delete from studies "
                + "where spnr='"+ spnr +"' and "
                + "ccode='"+ ccode +"';");
        return result;
    }
    //--------------------REMOVING IN THE DATABASE--------------------//

    //remove course
    public int removeCourse(String ccode) throws SQLException {
        stat = con.createStatement();
        int result = stat.executeUpdate("delete from course where ccode='"+ccode+"';" );
        return result;
    }

    //delete Student
    public int removeStudent(String spnr) throws SQLException {
        stat = con.createStatement();
        int result = stat.executeUpdate("delete from student where spnr='"+spnr+"';" );
        return result;
    }

    //delete student from course
    public int removeStudentFromCourse(String spnr, String ccode) throws SQLException {
        stat = con.createStatement();
        int result = stat.executeUpdate("delete from studies where spnr='"+spnr+ "' "
                + "and ccode='"+ccode+"';" );
        return result;
    }

    //--------------------SEARCHING IN THE DATABASE--------------------//

    //Find a student in the database (by spnr)
    public ResultSet getStudent(String spnr) throws SQLException {
        stat = con.createStatement();
        ResultSet result = stat.executeQuery("select s.spnr as 'Identity number', s.sname as Name "
                + "from Student s "
                + "where spnr='"+ spnr +"';");
        return result;
    }

    //Find a course in the database (by course code)
    public ResultSet getCourse(String ccode) throws SQLException {
        stat = con.createStatement();
        ResultSet result = stat.executeQuery("select c.ccode as Code, c.cname as Name, c.cpoint as Points "
                + "from Course c "
                + "where ccode='"+ ccode +"';");
        return result;
    }

    //Find result for a course (all students on course and their grade)
    public ResultSet getCourseResult(String ccode) throws SQLException {
        stat = con.createStatement();
        ResultSet result = stat.executeQuery("select s.spnr as 'Identity number', s.sname as Name, st.grade as Grade "
                + "from Student s join studied st "
                + "on st.spnr=s.spnr where st.ccode='"+ ccode +"';");
        return result;
    }

    //Find the grade for a certain student at a certain course
    public ResultSet getStudentResult(String spnr, String ccode) throws SQLException{
        stat = con.createStatement();
        ResultSet result = stat.executeQuery("select c.cname as 'Course name', st.spnr as 'Identity number', st.grade as Grade "
                + "from Studied st "
                + "inner join Course c "
                + "on c.ccode=st.ccode "
                + "where st.spnr='"+spnr+"' "
                + "and st.ccode='"+ccode+"';");
        return result;
    }

    //Find all courses in the database                                                                                  //NYTT
    public ResultSet getAllCourses() throws SQLException {
        stat = con.createStatement();
        ResultSet result = stat.executeQuery("select c.ccode as Code, c.cname as Name, c.cpoint as Points "
                + "from Course c "
                + "order by cname;");
        return result;
    }

    //Find all courses that a certain student is studying
    public ResultSet getCoursesStudentIsStudying(String spnr) throws SQLException {
        stat = con.createStatement();
        ResultSet result = stat.executeQuery("select c.ccode as Code, c.cname as Name, c.cpoint as Points "
                + "from course c "
                + "join studies st "
                + "on st.ccode=c.ccode "
                + "where st.spnr='"+ spnr +"';");
        return result;
    }

    // Find a list of all students in the database
    public ResultSet getAllStudents() throws SQLException {
        stat = con.createStatement();
        ResultSet result = stat.executeQuery("select s.spnr as 'Identity number', s.sname as Name "
                + "from Student s "
                + "order by sname");
        return result;
    }

    //--------------------SEARCHES FOR GRADE A AND B--------------------//

    //Find the students that are not ready with course a certain course
    public ResultSet getStudentsNotReadyWithCourse(String ccode) throws SQLException {
        stat = con.createStatement();
        ResultSet result = stat.executeQuery("select s.spnr as 'Identity number', s.sname as Name "
                + "from student s "
                + "join studies st "
                + "on s.spnr=st.spnr "
                + "where st.ccode='"+ ccode +"'"
                + "order by sname;");
        return result;
    }

    // Get the percentage of students that have the grade "A" in a certain course
    public double getProcentageOfA(String ccode) throws SQLException {
        stat = con.createStatement();

        ResultSet rs = stat.executeQuery("select count(*) as nr "
                + "from studied "
                + "where ccode='"+ ccode +"' "
                + "and grade ='A' "
                + "union "
                + "select count(*) as nr "
                + "from studied "
                + "where ccode='"+ccode+"'");

        rs.next();
        if(rs.getInt(1) != 0){

            double result1 = rs.getInt("nr");
            rs.next();
            double result2 = rs.getInt("nr");
            double result = ((result1/result2)*(100));
            return result;
        }
        else {
            double resultNull = 0;
            return resultNull;
        }
    }

    // get the course with highest flow
    public ResultSet getHighestFlow()throws SQLException {
        stat = con.createStatement();
        ResultSet rs = stat.executeQuery("select top 5 c.ccode 'Course code', c.cname as 'Course name', "
                + "sum((case when grade != 'u' then 1 else 0 end)) * 100 "
                + "/(sum(case when grade like '_' then 1 else 0 end)) as [Passed %] "
                + "from studied s, course c where s.ccode = c.ccode "
                + "group by c.cname, c.ccode "
                + "order by [Passed %] DESC");
        return rs;
    }
}