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

      // Function to create a new student fee record
      public function createStudentFee($studentId, $feesId) {
        $query = "INSERT INTO student_fees (student_id, fees_id) VALUES (?, ?)";
        $stmt = $this->db->prepare($query);
        $stmt->bind_param("ii", $studentId, $feesId);
        
        // Execute the statement
        if ($stmt->execute()) {
            return true; // Record created successfully
        } else {
            return false; // Failed to create record
        }
    }

     // Function to add student payment
     public function addStudentPayment($studentID, $dueId, $amount, $paymentDate) {
        $dueId = 1;
        $query = "INSERT INTO `student_payments` ( `student_id`, `due_id`, `ammount`, `payament_date`) VALUES ( '$studentID', '1', $amount, '$paymentDate')";
        $result = mysqli_query($this->db,$query);
        if($result){
            return true;
        }
     
    }
    
    
     public function editStudentPayment($paymentId, $ammount) {
        $dueId = 1;
        $query = "UPDATE `student_payments` SET `ammount` = '$ammount' WHERE `student_payments`.`id` = $paymentId";
        $result = mysqli_query($this->db,$query);
       
     
    }
    
       public function deletePayment($paymentId) {
      
        $query = "DELETE FROM `student_payments` WHERE `student_payments`.`id` = $paymentId";
        $result = mysqli_query($this->db,$query);
       
     
    }
    
    

        // Function to get all student fees with their respective joins
        public function getAllStudentFees() {
            $query = "SELECT * 
                      FROM student_fees 
                       JOIN students  ON students.student_id = student_fees.student_id
                       JOIN fees  ON student_fees.fees_id = fees.id";
            $result = mysqli_query($this->db,$query);
            $data = mysqli_fetch_all($result,MYSQLI_ASSOC);
            return $data;
        }

            // Function to get all student fees with their respective joins
        public function getSingleStudentFees($studentId) {
            $query = "SELECT * 
                      FROM student_fees 
                       JOIN students  ON students.student_id = student_fees.student_id
                       JOIN fees  ON student_fees.fees_id = fees.id 
                       WHERE students.student_id = $studentId
                       ";
            $result = mysqli_query($this->db,$query);
            $data = mysqli_fetch_all($result,MYSQLI_ASSOC);
            return $data;
        }


        public function getTotalPaymentsForStudent($studentId) {
            $query = "SELECT SUM(student_payments.amount) AS 'totalAmmount' from student_payments WHERE st_id = $studentId";
            $result = mysqli_query($this->db,$query);
            $data = mysqli_fetch_all($result,MYSQLI_ASSOC);
            return $data;
        }

        public function getAllPayments() {
            $query = "SELECT * from student_payments JOIN students ON students.student_id = student_payments.s_id";
            $result = mysqli_query($this->db,$query);
            $data = mysqli_fetch_all($result,MYSQLI_ASSOC);
            return $data;
        }
        
        public function getPaymentById($id) {
            $query = "SELECT * from student_payments JOIN students ON students.student_id = student_payments.student_id WHERE id=$id";
            $result = mysqli_query($this->db,$query);
            $data = mysqli_fetch_all($result,MYSQLI_ASSOC);
            return $data;
        }

        public function getAllPaymentsDate($fromDate,$toDate){
            $query = "SELECT 
                students.en_id, 
                students.fullname, 
                students.work, 
                students.address, 
                students.phone_number, 
                IFNULL(SUM(student_payments.amount), 0) AS total_paid, 
                MAX(student_payments.payament_date) AS payment_date, 
                fees.ammount AS course_price,
                (IFNULL(fees.ammount, 0) - IFNULL(SUM(student_payments.amount), 0)) AS remaining_balance,
                fees.name AS course_name,
                student_payments.id AS payment_id
            FROM 
                students 
            LEFT JOIN 
                student_course ON students.en_id = student_course.s_id
            LEFT JOIN 
                fees ON student_course.course_id = fees.id
            LEFT JOIN 
                student_payments ON students.student_id = student_payments.st_id
            WHERE 
                students.status = 'enrolled' 
                AND student_payments.payament_date BETWEEN '$fromDate' AND '$toDate'
            GROUP BY students.student_id";
            $result = mysqli_query($this->db,$query);
            $data = mysqli_fetch_all($result,MYSQLI_ASSOC);
            return $data;
        }




}
