/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package school.database;

import java.sql.*;
import java.util.ArrayList;


public class DatabaseDAO {

    private static final String URL = "jdbc:mysql://localhost:3306/school_db?useTimezone=true&serverTimezone=GMT";
    private static final String USER = "root";
    private static final String PASSWORD = "123456";

    private static final String GET_ALL_CLASSES_QUERY = "SELECT id, name FROM class ORDER BY name";

    private static final String GET_STUDENTS_IN_CLASS_QUERY =
            "SELECT s.id, s.name, s.surname, s.birthday, s.phone\n" +
            "FROM student s JOIN class_student_connection csc\n" +
            "ON csc.student_id = s.id\n" +
            "JOIN class c\n" +
            "ON csc.class_id = c.id\n" +
            "WHERE c.id = ?";

    private static final String GET_STUDENT_GRADES_QUERY =
            "SELECT s.name, gv.values, g.grade_date\n" +
            "FROM grade g JOIN subject s\n" +
            "ON g.subject_id = s.id\n" +
            "JOIN student st\n" +
            "ON g.student_id = st.id\n" +
            "JOIN grade_value gv\n" +
            "ON g.mark_id = gv.id\n" +
            "WHERE st.id = ?";


    private Connection conn;

    public DatabaseDAO() {
        try {
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public ArrayList<SchoolClass> readAllClassesFromDB() {
        ArrayList<SchoolClass> result = new ArrayList<>();

        try (PreparedStatement stmt = conn.prepareStatement(GET_ALL_CLASSES_QUERY)) {
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                SchoolClass schoolClass = new SchoolClass(id, name);
                result.add(schoolClass);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return result;
    }

    public ArrayList<Student> readStudentsInClassFromDB(int classID) {
        ArrayList<Student> result = new ArrayList<>();

        try (PreparedStatement stmt = conn.prepareStatement(GET_STUDENTS_IN_CLASS_QUERY)) {
            stmt.setInt(1, classID);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                int studentID = rs.getInt("id");
                String firstName = rs.getString("name");
                String lastName = rs.getString("surname");
                Date birthday = rs.getDate("birthday");
                Student student = new Student(studentID, firstName, lastName, "PATRON", birthday);
                result.add(student);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return result;
    }

    public ArrayList<Grade> readGradesFromDB(int studentID) {
        ArrayList<Grade> result = new ArrayList<>();
        
        try (PreparedStatement stmt = conn.prepareStatement(GET_STUDENT_GRADES_QUERY)) {
            stmt.setInt(1, studentID);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                String subject = rs.getString("name");
                int value = rs.getInt("values");
                java.util.Date date = rs.getDate("grade_date");
                Grade grade = new Grade(subject, value, date.toString());
                result.add(grade);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        
        return result;
    }
}