<?php
ob_start();
header('Content-Type: application/json');

// 1. Load config
require("../../includes/config.php");

// Clean environment
error_reporting(0);
ini_set('display_errors', 0);

/**
 * SMART ID DETECTION
 */
$yid = (!empty($_GET['academic_year_id'])) ? $_GET['academic_year_id'] : (defined('APP_YEAR_ID') ? APP_YEAR_ID : 0);

if (empty($yid) || $yid == 0) {
    ob_clean();
    echo json_encode(['status' => 'error', 'message' => 'No active Academic Year ID identified.']);
    exit;
}

try {
    // 2. Fetch Academic Year Dates
    $stmt = $pdo->prepare("SELECT start_date, end_date FROM academic_years WHERE id = ?");
    $stmt->execute([$yid]);
    $ay = $stmt->fetch();

    if (!$ay) throw new Exception("Academic Year record not found for ID: $yid");

    // Force period to full Academic Year
    $start = $ay['start_date'];
    $end = $ay['end_date'];

    // 3. Dynamic Pivot for Fees
    $ftStmt = $pdo->prepare("SELECT DISTINCT fee_title FROM fee_types WHERE academic_year_id = ?");
    $ftStmt->execute([$yid]);
    $fee_titles = $ftStmt->fetchAll(PDO::FETCH_COLUMN);

    $pivot_sql = "";
    foreach ($fee_titles as $title) {
        $safe_title = str_replace(' ', '_', $title);
        $pivot_sql .= "SUM(CASE WHEN ft.fee_title = '$title' THEN (SELECT COALESCE(SUM(fp.amount_paid),0) FROM fee_payments fp WHERE fp.fee_assign_id = fa.id AND fp.payment_date BETWEEN '$start' AND '$end') ELSE 0 END) AS `{$safe_title}_paid`, ";
        $pivot_sql .= "SUM(CASE WHEN ft.fee_title = '$title' THEN fa.remaining_amount ELSE 0 END) AS `{$safe_title}_unpaid`, ";
    }

    // 4. Main Data Query (Stream Join and Grouping Removed)
    $sql = "SELECT 
                c.class_name, 
                COUNT(DISTINCT e.student_id) as student_count, 
                $pivot_sql 
                SUM(fa.remaining_amount) as total_class_unpaid,
                (SELECT COALESCE(SUM(amount_paid),0) 
                 FROM fee_payments fp 
                 JOIN fee_assign fa2 ON fp.fee_assign_id = fa2.id 
                 JOIN enrollments e2 ON fa2.student_id = e2.student_id
                 WHERE e2.class_id = c.id 
                 AND e2.academic_year_id = ? 
                 AND fp.payment_date BETWEEN ? AND ?) as total_class_paid
            FROM classes c
            JOIN enrollments e ON c.id = e.class_id
            JOIN fee_assign fa ON e.student_id = fa.student_id
            JOIN fee_types ft ON fa.fee_type_id = ft.id
            WHERE e.academic_year_id = ?
            GROUP BY c.id
            ORDER BY c.id ASC";
    
    $bStmt = $pdo->prepare($sql);
    $bStmt->execute([$yid, $start, $end, $yid]);
    $breakdown = $bStmt->fetchAll(PDO::FETCH_ASSOC);

    // 5. Summary Totals
    $sStmt = $pdo->prepare("SELECT 
        (SELECT COALESCE(SUM(total_amount),0) FROM fee_assign fa JOIN enrollments e ON fa.student_id = e.student_id WHERE e.academic_year_id = ?) as total_assigned,
        (SELECT COALESCE(SUM(amount_paid),0) FROM fee_payments p JOIN enrollments e ON p.student_id = e.student_id WHERE e.academic_year_id = ? AND p.payment_date BETWEEN ? AND ?) as period_collected,
        (SELECT COALESCE(SUM(discount_amount),0) FROM fee_payments p JOIN enrollments e ON p.student_id = e.student_id WHERE e.academic_year_id = ? AND p.payment_date BETWEEN ? AND ?) as total_discount,
        (SELECT COALESCE(SUM(remaining_amount),0) FROM fee_assign fa JOIN enrollments e ON fa.student_id = e.student_id WHERE e.academic_year_id = ?) as total_unpaid");
    
    $sStmt->execute([$yid, $yid, $start, $end, $yid, $start, $end, $yid]);
    $summary = $sStmt->fetch(PDO::FETCH_ASSOC);

    // Final Output
    ob_clean(); 
    echo json_encode([
        'status' => 'success',
        'academic_period' => ['start' => $start, 'end' => $end],
        'summary' => $summary,
        'fee_columns' => $fee_titles,
        'breakdown' => $breakdown
    ]);

} catch (Exception $e) {
    ob_clean();
    echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
}
?>