<?php 
require_once('db_connection.php');
class Course  
{
    private $db;
    function __construct($dbconn){
        $this->db = $dbconn;
    }


    public function getAllCourses() {
        $query = "SELECT * FROM fees";
        $result =  mysqli_query($this->db,$query);
        $data = mysqli_fetch_all( $result,MYSQLI_ASSOC);
        return $data;
    }
    
      public function addCourse($name,$price,$desc) {
        $query = "INSERT INTO `fees` (`name`, ammount, `description`) VALUES ('$name', '$price', '$desc')";
        $result =  mysqli_query($this->db,$query);
            
    }

      // Function to create a new entry in the student_course table
      public function createStudentCourse($studentId, $courseId, $enrollmentDate, $year) {
        $query = "INSERT INTO student_course (student_id, course_id, en_date, year) VALUES (?, ?, ?, ?)";
        $stmt = $this->db->prepare($query);
        $stmt->bind_param("ssss", $studentId, $courseId, $enrollmentDate, $year);
        
        // Execute the statement
        if ($stmt->execute()) {
            return true; // Entry created successfully
        } else {
            return false; // Failed to create entry
        }
    }

         // Function to get all students by course with respective joins
         public function getAllStudentsByCourse($courseId) {
            $query = "SELECT  *
                      FROM student_course 
                       JOIN students  ON student_course.student_id = students.student_id
                       JOIN courses ON student_course.course_id =  courses.course_id
                      WHERE student_course.course_id = $courseId";
            $result = mysqli_query($this->db, $query);
            return mysqli_fetch_all($result, MYSQLI_BOTH);
        }

        public function getAllStudentsByCourseAndYear($courseId, $year){
            $query = "SELECT  *
            FROM student_course 
             JOIN students  ON student_course.student_id = students.student_id
             JOIN courses ON student_course.course_id =  courses.course_id
            WHERE student_course.course_id = $courseId AND student_course.year = $year" ;
            $result = mysqli_query($this->db, $query);
            return mysqli_fetch_all($result, MYSQLI_BOTH);
        }

      
    

}