<?php


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


    public function getTotalEnrolledStudents()
    {
        $query = "SELECT COUNT(DISTINCT student_id)  AS total_students FROM students WHERE status ='enrolled'";
        $result = mysqli_query($this->db, $query);
        $row = mysqli_fetch_assoc($result);
        return $row['total_students'];
    }


    // Method to return total payments made by all students
    public function getTotalPayments()
    {
        $query = "SELECT SUM(amount) AS total_payments FROM student_payments";
        $result = mysqli_query($this->db, $query);
        $row = mysqli_fetch_assoc($result);
        return $row['total_payments'];
    }



    public function getPaymentsPerMonth()
    {
        $currentYear = Date('Y');
        $query = "SELECT
        YEAR(payament_date) AS year,
        MONTH(payament_date) AS month,
        SUM(amount) AS total_payments
        FROM student_payments
        WHERE YEAR(payament_date) = $currentYear 
        GROUP BY MONTH(payament_date)";
        $result = mysqli_query($this->db, $query);
        $row = mysqli_fetch_all($result, MYSQLI_ASSOC);
        return $row;
    }

    // public function totalDue() {
    //     //$currentYear = Date('Y');
    //     $query = "SELECT SUM(difference) as total_difference 
    //     FROM 
    //     ( SELECT s_id, SUM(amount) as total_paid, SUM(amount) - 300000 as difference 
    //     FROM student_payments 
    //     GROUP BY s_id 
    //     ORDER BY difference
    //     ASC ) as subquery";
    //     $result = mysqli_query($this->db, $query);
    //     $row = mysqli_fetch_all($result, MYSQLI_ASSOC);
    //     return $row;
    // }

    public function getTotalDueUnpaid()
    {
        // Calculate total fees
        $query = "SELECT
        (SELECT SUM(fees.ammount) 
        FROM student_fees 
        JOIN fees
        ON student_fees.fees_id = fees.id)  
        -
        (SELECT SUM(student_payments.amount) FROM student_payments)
        
        AS Due;
        ";
        $result = mysqli_query($this->db, $query);
        $row = mysqli_fetch_assoc($result);
        $due = $row['Due'];
        return $due;
    }

    public function getAllDues()
    {
        $query = "
        SELECT
        (SELECT COALESCE(SUM(fees.ammount), 0)
         FROM student_fees
         JOIN fees ON fees.id = student_fees.fees_id
         WHERE student_fees.student_id = 4) -
    
        (SELECT COALESCE(SUM(student_payments.ammount), 0)
         FROM student_payments
         WHERE student_payments.student_id = 4) AS due,
    
        students.fullname, -- Add fields from another table
        students.en_id
    FROM
        students 
    WHERE students.student_id = 4
    
    ORDER BY due 
    DESC
    
        ";
        $result = mysqli_query($this->db, $query);
        $data =  mysqli_fetch_all($result, MYSQLI_ASSOC);
        return $data;
    }

    // public function getFiveTopDues() {
    //     $query = "
    //         SELECT
    //         (SELECT COALESCE(SUM(fees.ammount), 0)
    //         FROM student_fees
    //         JOIN fees ON fees.id = student_fees.fees_id
    //         WHERE student_fees.student_id = 4) -

    //         (SELECT COALESCE(SUM(student_payments.ammount), 0)
    //         FROM student_payments
    //         WHERE student_payments.student_id = 4) AS due,

    //         students.fullname, -- Add fields from another table
    //         students.en_id
    //         FROM
    //             students 
    //         WHERE students.student_id = 4

    //         ORDER BY due 
    //         DESC
    //         LIMIT
    //         5
    //     ";
    //     $result = mysqli_query($this->db, $query);
    //     $data =  mysqli_fetch_all($result, MYSQLI_ASSOC);
    //     return $data;
    // }

    public function getRevenueOverDues()
    {
        $query = "";
    }
}
