<?php
require_once('../../includes/config.php');
header('Content-Type: application/json');

try {
    /* =========================
       INPUTS
    ========================== */
    $month = $_GET['month'] ?? '';
    $class = $_GET['class'] ?? 'all';
    $medium_id = $_GET['medium_id'] ?? 'all'; 

    $selectedYearId = $_GET['academic_year_id'] ?? '';
    $yearIdToUse = !empty($selectedYearId)
        ? $selectedYearId
        : (defined('APP_YEAR_ID') ? APP_YEAR_ID : 0);

    if (empty($month)) {
        throw new Exception("Month is required");
    }

    if (empty($yearIdToUse) || $yearIdToUse == 0) {
        throw new Exception("No academic year context found");
    }

    /* =========================
       SQL QUERY (Stream Removed)
    ========================== */
    $sql = "
    SELECT 
        e.roll_no,
        e.student_id,
        s.student_name,
        s.parent_phone,
        c.class_name,
        sec.section_name,
        m.medium_name,
        fa.id AS assign_id,
        fa.due_month,
        fa.total_amount AS assigned_total,
        COALESCE(fa.payment_status, 'Unassigned') AS payment_status,
        ft.fee_title,
        ft.amount,

        -- Total Paid from payments table
        (SELECT IFNULL(SUM(fp.amount_paid), 0)
         FROM fee_payments fp
         WHERE fp.fee_assign_id = fa.id) AS total_collected,

        -- Total Discount from payments table
        (SELECT IFNULL(SUM(fp.discount_amount), 0)
         FROM fee_payments fp
         WHERE fp.fee_assign_id = fa.id) AS total_discount,

        -- Calculate actual remaining balance
        (IFNULL(fa.total_amount, 0) - (
            (SELECT IFNULL(SUM(fp.amount_paid), 0) FROM fee_payments fp WHERE fp.fee_assign_id = fa.id) + 
            (SELECT IFNULL(SUM(fp.discount_amount), 0) FROM fee_payments fp WHERE fp.fee_assign_id = fa.id)
        )) AS remaining_balance

    FROM enrollments e
    INNER JOIN students s ON e.student_id = s.id
    INNER JOIN classes c ON e.class_id = c.id
    LEFT JOIN sections sec ON e.section_id = sec.id
    LEFT JOIN mediums m ON e.medium_id = m.id
    LEFT JOIN fee_assign fa 
        ON fa.student_id = e.student_id 
        AND fa.due_month = :month
    LEFT JOIN fee_types ft ON fa.fee_type_id = ft.id
    WHERE e.academic_year_id = :year_id
    ";

    $params = [
        ':month'   => $month,
        ':year_id' => $yearIdToUse
    ];

    // Filter by Class
    if ($class !== 'all' && !empty($class)) {
        $sql .= " AND e.class_id = :class_id";
        $params[':class_id'] = $class;
    }

    // Filter by Medium
    if ($medium_id !== 'all' && !empty($medium_id)) {
        $sql .= " AND e.medium_id = :medium_id";
        $params[':medium_id'] = $medium_id;
    }

    $sql .= " ORDER BY CAST(e.roll_no AS UNSIGNED) ASC";

    /* =========================
       EXECUTION
    ========================== */
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    /* =========================
       STATUS & MATH CALCULATION
    ========================== */
    foreach ($rows as &$row) {
        $assigned  = (float)($row['assigned_total'] ?? 0);
        $paid      = (float)$row['total_collected'];
        $discount  = (float)$row['total_discount'];
        
        $calculated_remaining = $assigned - ($paid + $discount);

        $row['assigned_total']    = $assigned;
        $row['total_collected']   = $paid;
        $row['total_discount']    = $discount; 
        $row['remaining_balance'] = ($calculated_remaining < 0) ? 0 : $calculated_remaining;

        // Determine Status
        if ($assigned > 0 && $row['remaining_balance'] <= 0) {
            $row['payment_status'] = 'Paid';
        } elseif (($paid + $discount) > 0) {
            $row['payment_status'] = 'Partial';
        } else {
            $row['payment_status'] = 'Pending';
        }
    }

    echo json_encode([
        "status" => true,
        "data"   => $rows
    ]);

} catch (Exception $e) {
    http_response_code(500);
    echo json_encode([
        "status"  => false,
        "message" => $e->getMessage()
    ]);
}
?>