<?php
// financial_reports.php
require 'db.php';
require 'helpers.php';
require_committee();

// Enable strict error reporting
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli1->set_charset('utf8mb4');

// Get committee information
$committeeId   = (int)($_SESSION['committee_id'] ?? 0);
$committeeName = (string)($_SESSION['committee_name'] ?? '');
$canAll        = (bool)($_SESSION['committee_can_all'] ?? false);
$scopeCollege  = isset($_SESSION['committee_college_id']) ? (int)$_SESSION['committee_college_id'] : null;

// ============================================
// INPUT VALIDATION AND SANITIZATION
// ============================================

// Report type validation
$allowed_report_types = ['summary', 'detailed', 'college', 'program', 'batch', 'semester', 'bank'];
$report_type = isset($_GET['report_type']) && in_array($_GET['report_type'], $allowed_report_types) 
    ? $_GET['report_type'] 
    : 'summary';

// Date range validation
$start_date = isset($_GET['start_date']) && preg_match('/^\d{4}-\d{2}-\d{2}$/', $_GET['start_date']) 
    ? $_GET['start_date'] 
    : date('Y-m-01', strtotime('-3 months')); // Default to 3 months ago
$end_date = isset($_GET['end_date']) && preg_match('/^\d{4}-\d{2}-\d{2}$/', $_GET['end_date']) 
    ? $_GET['end_date'] 
    : date('Y-m-t');

if ($start_date > $end_date) {
    $end_date = $start_date;
}

// Filter values with proper validation
$filter_college = isset($_GET['filter_college']) && ctype_digit($_GET['filter_college']) 
    ? (int)$_GET['filter_college'] 
    : null;
$filter_program = isset($_GET['filter_program']) && ctype_digit($_GET['filter_program']) 
    ? (int)$_GET['filter_program'] 
    : null;
$filter_batch = isset($_GET['filter_batch']) && ctype_digit($_GET['filter_batch']) 
    ? (int)$_GET['filter_batch'] 
    : null;
$filter_section = isset($_GET['filter_section']) && ctype_digit($_GET['filter_section']) 
    ? (int)$_GET['filter_section'] 
    : null;
$filter_semester = isset($_GET['filter_semester']) && ctype_digit($_GET['filter_semester']) 
    ? (int)$_GET['filter_semester'] 
    : null;
$filter_bank = isset($_GET['filter_bank']) && ctype_digit($_GET['filter_bank']) 
    ? (int)$_GET['filter_bank'] 
    : null;

// Payment status filter
$allowed_status = ['all', 'paid', 'unpaid', 'partial'];
$payment_status = isset($_GET['payment_status']) && in_array($_GET['payment_status'], $allowed_status) 
    ? $_GET['payment_status'] 
    : 'all';

// Export format
$allowed_export = ['html', 'pdf', 'excel'];
$export_format = isset($_GET['export']) && in_array($_GET['export'], $allowed_export) 
    ? $_GET['export'] 
    : 'html';

// Pagination
$per_page = 50;
$page = isset($_GET['page']) && ctype_digit($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
$offset = ($page - 1) * $per_page;

// ============================================
// BUILD FILTER CONDITIONS (REUSABLE)
// ============================================

$conditions = [];
$params = [];
$types = '';

// Permission scope
if (!$canAll && $scopeCollege !== null) {
    $conditions[] = "s.college_id = ?";
    $types .= 'i';
    $params[] = $scopeCollege;
} elseif (!$canAll && $scopeCollege === null) {
    $conditions[] = "1=0"; // No access
}

// College filter
if ($filter_college) {
    $conditions[] = "s.college_id = ?";
    $types .= 'i';
    $params[] = $filter_college;
}

// Program filter
if ($filter_program) {
    $conditions[] = "s.program_id = ?";
    $types .= 'i';
    $params[] = $filter_program;
}

// Batch filter
if ($filter_batch) {
    $conditions[] = "s.batch_id = ?";
    $types .= 'i';
    $params[] = $filter_batch;
}

// Section filter
if ($filter_section) {
    $conditions[] = "s.section_id = ?";
    $types .= 'i';
    $params[] = $filter_section;
}

// Bank filter (for payments)
if ($filter_bank) {
    $conditions[] = "p.bank_id = ?";
    $types .= 'i';
    $params[] = $filter_bank;
}

$where_clause = !empty($conditions) ? "WHERE " . implode(" AND ", $conditions) : "";

// ============================================
// EXPORT HANDLING
// ============================================

if ($export_format !== 'html') {
    ob_clean();
    
    if ($export_format === 'excel') {
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="financial_report_' . date('Y-m-d') . '.xls"');
        header('Cache-Control: max-age=0');
    } elseif ($export_format === 'pdf') {
        // You would need a PDF library like TCPDF or Dompdf
        // This is a placeholder - implement based on your PDF library
        header('Content-Type: application/pdf');
        header('Content-Disposition: attachment; filename="financial_report_' . date('Y-m-d') . '.pdf"');
    }
}

// ============================================
// REPORT QUERIES BASED ON TYPE
// ============================================

$report_data = [];
$grand_totals = [
    'total_paid' => 0,
    'total_unpaid' => 0,
    'total_receivable' => 0,
    'payment_count' => 0,
    'student_count' => 0
];

switch ($report_type) {
    case 'summary':
        // Summary report - overall financial snapshot
        $sql = "
            SELECT 
                DATE(p.created_at) as payment_date,
                COUNT(DISTINCT p.id) as payment_count,
                COUNT(DISTINCT p.student_id) as student_count,
                COALESCE(SUM(p.payment_amount), 0) as total_paid,
                b.id as bank_id,
                b.name as bank_name
            FROM payments p
            INNER JOIN banks b ON p.bank_id = b.id
            LEFT JOIN students s ON p.student_id = s.student_id
            WHERE p.deleted_at IS NULL
                AND DATE(p.created_at) BETWEEN ? AND ?
                " . ($filter_semester ? "AND p.semester_id = ?" : "") . "
                {$where_clause}
            GROUP BY DATE(p.created_at), b.id, b.name
            ORDER BY payment_date DESC, total_paid DESC
            LIMIT ? OFFSET ?
        ";
        
        $report_params = [$start_date, $end_date];
        $report_types = 'ss';
        
        if ($filter_semester) {
            $report_params[] = $filter_semester;
            $report_types .= 'i';
        }
        
        // Add filter params
        if (!empty($params)) {
            $report_params = array_merge($report_params, $params);
            $report_types .= $types;
        }
        
        // Add pagination
        $report_params[] = $per_page;
        $report_params[] = $offset;
        $report_types .= 'ii';
        
        $stmt = $mysqli1->prepare($sql);
        $stmt->bind_param($report_types, ...$report_params);
        $stmt->execute();
        $report_data = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        
        // Get total count for pagination
        $count_sql = "
            SELECT COUNT(DISTINCT CONCAT(DATE(p.created_at), '-', p.bank_id)) as total
            FROM payments p
            INNER JOIN banks b ON p.bank_id = b.id
            LEFT JOIN students s ON p.student_id = s.student_id
            WHERE p.deleted_at IS NULL
                AND DATE(p.created_at) BETWEEN ? AND ?
                " . ($filter_semester ? "AND p.semester_id = ?" : "") . "
                {$where_clause}
        ";
        
        $count_params = [$start_date, $end_date];
        $count_types = 'ss';
        
        if ($filter_semester) {
            $count_params[] = $filter_semester;
            $count_types .= 'i';
        }
        
        if (!empty($params)) {
            $count_params = array_merge($count_params, $params);
            $count_types .= $types;
        }
        
        $count_stmt = $mysqli1->prepare($count_sql);
        $count_stmt->bind_param($count_types, ...$count_params);
        $count_stmt->execute();
        $total_records = $count_stmt->get_result()->fetch_assoc()['total'] ?? 0;
        $count_stmt->close();
        
        // Calculate grand totals
        $totals_sql = "
            SELECT 
                COALESCE(SUM(p.payment_amount), 0) as total_paid,
                COUNT(DISTINCT p.id) as payment_count,
                COUNT(DISTINCT p.student_id) as student_count
            FROM payments p
            INNER JOIN banks b ON p.bank_id = b.id
            LEFT JOIN students s ON p.student_id = s.student_id
            WHERE p.deleted_at IS NULL
                AND DATE(p.created_at) BETWEEN ? AND ?
                " . ($filter_semester ? "AND p.semester_id = ?" : "") . "
                {$where_clause}
        ";
        
        $totals_stmt = $mysqli1->prepare($totals_sql);
        $totals_stmt->bind_param($count_types, ...$count_params);
        $totals_stmt->execute();
        $totals = $totals_stmt->get_result()->fetch_assoc();
        $totals_stmt->close();
        
        $grand_totals['total_paid'] = $totals['total_paid'] ?? 0;
        $grand_totals['payment_count'] = $totals['payment_count'] ?? 0;
        $grand_totals['student_count'] = $totals['student_count'] ?? 0;
        break;
        
    case 'college':
        // College-wise financial report
        $sql = "
            SELECT 
                c.id as college_id,
                c.name as college_name,
                COUNT(DISTINCT s.student_id) as total_students,
                COUNT(DISTINCT CASE WHEN r.is_paid = 1 THEN s.student_id END) as paid_students,
                COUNT(DISTINCT CASE WHEN r.is_paid = 0 THEN s.student_id END) as unpaid_students,
                COALESCE(SUM(CASE WHEN r.is_paid = 1 THEN r.payment_amount ELSE 0 END), 0) as total_paid,
                COALESCE(SUM(CASE WHEN r.is_paid = 0 THEN r.payment_amount ELSE 0 END), 0) as total_unpaid,
                COUNT(DISTINCT p.id) as payment_count,
                COALESCE(SUM(p.payment_amount), 0) as payment_total
            FROM colleges c
            LEFT JOIN students s ON c.id = s.college_id
            LEFT JOIN registrations r ON r.student_id = s.student_id AND r.deleted_at IS NULL
                " . ($filter_semester ? "AND r.semester_id = ?" : "") . "
            LEFT JOIN payments p ON p.student_id = s.student_id 
                AND p.deleted_at IS NULL
                AND DATE(p.created_at) BETWEEN ? AND ?
                " . ($filter_semester ? "AND p.semester_id = ?" : "") . "
            " . str_replace('s.', '', $where_clause) . "
            GROUP BY c.id, c.name
            ORDER BY total_paid DESC
            LIMIT ? OFFSET ?
        ";
        
        $report_params = [];
        $report_types = '';
        
        if ($filter_semester) {
            $report_params[] = $filter_semester;
            $report_types .= 'i';
        }
        
        $report_params[] = $start_date;
        $report_params[] = $end_date;
        $report_types .= 'ss';
        
        if ($filter_semester) {
            $report_params[] = $filter_semester;
            $report_types .= 'i';
        }
        
        // Add filter params (without s. prefix)
        if (!empty($params)) {
            $report_params = array_merge($report_params, $params);
            $report_types .= $types;
        }
        
        // Add pagination
        $report_params[] = $per_page;
        $report_params[] = $offset;
        $report_types .= 'ii';
        
        $stmt = $mysqli1->prepare($sql);
        $stmt->bind_param($report_types, ...$report_params);
        $stmt->execute();
        $report_data = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        
        // Get total count
        $count_sql = "SELECT COUNT(*) as total FROM colleges c";
        $count_stmt = $mysqli1->prepare($count_sql);
        $count_stmt->execute();
        $total_records = $count_stmt->get_result()->fetch_assoc()['total'] ?? 0;
        $count_stmt->close();
        
        // Calculate grand totals
        foreach ($report_data as $row) {
            $grand_totals['total_paid'] += $row['total_paid'];
            $grand_totals['total_unpaid'] += $row['total_unpaid'];
            $grand_totals['payment_count'] += $row['payment_count'];
            $grand_totals['student_count'] += $row['total_students'];
        }
        $grand_totals['total_receivable'] = $grand_totals['total_paid'] + $grand_totals['total_unpaid'];
        break;
        
    case 'program':
        // Program-wise financial report
        $sql = "
            SELECT 
                pr.id as program_id,
                pr.name as program_name,
                c.name as college_name,
                COUNT(DISTINCT s.student_id) as total_students,
                COUNT(DISTINCT CASE WHEN r.is_paid = 1 THEN s.student_id END) as paid_students,
                COALESCE(SUM(CASE WHEN r.is_paid = 1 THEN r.payment_amount ELSE 0 END), 0) as total_paid,
                COALESCE(SUM(CASE WHEN r.is_paid = 0 THEN r.payment_amount ELSE 0 END), 0) as total_unpaid,
                COUNT(DISTINCT p.id) as payment_count,
                COALESCE(SUM(p.payment_amount), 0) as payment_total
            FROM programs pr
            LEFT JOIN colleges c ON pr.college_id = c.id
            LEFT JOIN students s ON pr.id = s.program_id
            LEFT JOIN registrations r ON r.student_id = s.student_id AND r.deleted_at IS NULL
                " . ($filter_semester ? "AND r.semester_id = ?" : "") . "
            LEFT JOIN payments p ON p.student_id = s.student_id 
                AND p.deleted_at IS NULL
                AND DATE(p.created_at) BETWEEN ? AND ?
                " . ($filter_semester ? "AND p.semester_id = ?" : "") . "
            " . str_replace('s.', '', $where_clause) . "
            GROUP BY pr.id, pr.name, c.name
            ORDER BY total_paid DESC
            LIMIT ? OFFSET ?
        ";
        
        // Similar parameter binding as college report
        $report_params = [];
        $report_types = '';
        
        if ($filter_semester) {
            $report_params[] = $filter_semester;
            $report_types .= 'i';
        }
        
        $report_params[] = $start_date;
        $report_params[] = $end_date;
        $report_types .= 'ss';
        
        if ($filter_semester) {
            $report_params[] = $filter_semester;
            $report_types .= 'i';
        }
        
        if (!empty($params)) {
            $report_params = array_merge($report_params, $params);
            $report_types .= $types;
        }
        
        $report_params[] = $per_page;
        $report_params[] = $offset;
        $report_types .= 'ii';
        
        $stmt = $mysqli1->prepare($sql);
        $stmt->bind_param($report_types, ...$report_params);
        $stmt->execute();
        $report_data = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        
        // Get total count
        $count_sql = "SELECT COUNT(*) as total FROM programs pr";
        $count_stmt = $mysqli1->prepare($count_sql);
        $count_stmt->execute();
        $total_records = $count_stmt->get_result()->fetch_assoc()['total'] ?? 0;
        $count_stmt->close();
        
        // Calculate grand totals
        foreach ($report_data as $row) {
            $grand_totals['total_paid'] += $row['total_paid'];
            $grand_totals['total_unpaid'] += $row['total_unpaid'];
            $grand_totals['payment_count'] += $row['payment_count'];
            $grand_totals['student_count'] += $row['total_students'];
        }
        $grand_totals['total_receivable'] = $grand_totals['total_paid'] + $grand_totals['total_unpaid'];
        break;
        
    case 'bank':
        // Bank-wise financial report
        $sql = "
            SELECT 
                b.id as bank_id,
                b.name as bank_name,
                COUNT(DISTINCT p.id) as transaction_count,
                COUNT(DISTINCT p.student_id) as student_count,
                COALESCE(SUM(p.payment_amount), 0) as total_amount,
                MIN(DATE(p.created_at)) as first_payment,
                MAX(DATE(p.created_at)) as last_payment
            FROM banks b
            LEFT JOIN payments p ON p.bank_id = b.id 
                AND p.deleted_at IS NULL
                AND DATE(p.created_at) BETWEEN ? AND ?
                " . ($filter_semester ? "AND p.semester_id = ?" : "") . "
            LEFT JOIN students s ON p.student_id = s.student_id
            {$where_clause}
            GROUP BY b.id, b.name
            HAVING total_amount > 0
            ORDER BY total_amount DESC
            LIMIT ? OFFSET ?
        ";
        
        $report_params = [$start_date, $end_date];
        $report_types = 'ss';
        
        if ($filter_semester) {
            $report_params[] = $filter_semester;
            $report_types .= 'i';
        }
        
        if (!empty($params)) {
            $report_params = array_merge($report_params, $params);
            $report_types .= $types;
        }
        
        $report_params[] = $per_page;
        $report_params[] = $offset;
        $report_types .= 'ii';
        
        $stmt = $mysqli1->prepare($sql);
        $stmt->bind_param($report_types, ...$report_params);
        $stmt->execute();
        $report_data = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        
        // Get total count
        $count_sql = "
            SELECT COUNT(DISTINCT b.id) as total
            FROM banks b
            LEFT JOIN payments p ON p.bank_id = b.id 
                AND p.deleted_at IS NULL
                AND DATE(p.created_at) BETWEEN ? AND ?
                " . ($filter_semester ? "AND p.semester_id = ?" : "") . "
            LEFT JOIN students s ON p.student_id = s.student_id
            {$where_clause}
            HAVING COALESCE(SUM(p.payment_amount), 0) > 0
        ";
        
        $count_params = [$start_date, $end_date];
        $count_types = 'ss';
        
        if ($filter_semester) {
            $count_params[] = $filter_semester;
            $count_types .= 'i';
        }
        
        if (!empty($params)) {
            $count_params = array_merge($count_params, $params);
            $count_types .= $types;
        }
        
        $count_stmt = $mysqli1->prepare($count_sql);
        $count_stmt->bind_param($count_types, ...$count_params);
        $count_stmt->execute();
        $total_records = $count_stmt->get_result()->fetch_assoc()['total'] ?? 0;
        $count_stmt->close();
        
        // Calculate grand totals
        foreach ($report_data as $row) {
            $grand_totals['total_paid'] += $row['total_amount'];
            $grand_totals['payment_count'] += $row['transaction_count'];
            $grand_totals['student_count'] += $row['student_count'];
        }
        break;
        
    case 'detailed':
    default:
        // Detailed transaction report
        $sql = "
            SELECT 
                p.id as payment_id,
                p.payment_amount,
                p.created_at,
                p.reference_number,
                p.notes,
                s.student_id,
                s.student_name,
                s.student_name_eng,
                b.name as bank_name,
                c.name as college_name,
                pr.name as program_name,
                sem.name as semester_name
            FROM payments p
            INNER JOIN banks b ON p.bank_id = b.id
            INNER JOIN students s ON p.student_id = s.student_id
            LEFT JOIN colleges c ON s.college_id = c.id
            LEFT JOIN programs pr ON s.program_id = pr.id
            LEFT JOIN semesters sem ON p.semester_id = sem.id
            WHERE p.deleted_at IS NULL
                AND DATE(p.created_at) BETWEEN ? AND ?
                " . ($filter_semester ? "AND p.semester_id = ?" : "") . "
                " . ($payment_status === 'paid' ? "AND p.is_paid = 1" : ($payment_status === 'unpaid' ? "AND p.is_paid = 0" : "")) . "
                {$where_clause}
            ORDER BY p.created_at DESC
            LIMIT ? OFFSET ?
        ";
        
        $report_params = [$start_date, $end_date];
        $report_types = 'ss';
        
        if ($filter_semester) {
            $report_params[] = $filter_semester;
            $report_types .= 'i';
        }
        
        if (!empty($params)) {
            $report_params = array_merge($report_params, $params);
            $report_types .= $types;
        }
        
        $report_params[] = $per_page;
        $report_params[] = $offset;
        $report_types .= 'ii';
        
        $stmt = $mysqli1->prepare($sql);
        $stmt->bind_param($report_types, ...$report_params);
        $stmt->execute();
        $report_data = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        
        // Get total count
        $count_sql = "
            SELECT COUNT(*) as total
            FROM payments p
            INNER JOIN students s ON p.student_id = s.student_id
            WHERE p.deleted_at IS NULL
                AND DATE(p.created_at) BETWEEN ? AND ?
                " . ($filter_semester ? "AND p.semester_id = ?" : "") . "
                " . ($payment_status === 'paid' ? "AND p.is_paid = 1" : ($payment_status === 'unpaid' ? "AND p.is_paid = 0" : "")) . "
                {$where_clause}
        ";
        
        $count_params = [$start_date, $end_date];
        $count_types = 'ss';
        
        if ($filter_semester) {
            $count_params[] = $filter_semester;
            $count_types .= 'i';
        }
        
        if (!empty($params)) {
            $count_params = array_merge($count_params, $params);
            $count_types .= $types;
        }
        
        $count_stmt = $mysqli1->prepare($count_sql);
        $count_stmt->bind_param($count_types, ...$count_params);
        $count_stmt->execute();
        $total_records = $count_stmt->get_result()->fetch_assoc()['total'] ?? 0;
        $count_stmt->close();
        
        // Calculate grand totals
        $totals_sql = "
            SELECT 
                COALESCE(SUM(p.payment_amount), 0) as total_paid,
                COUNT(*) as payment_count,
                COUNT(DISTINCT p.student_id) as student_count
            FROM payments p
            INNER JOIN students s ON p.student_id = s.student_id
            WHERE p.deleted_at IS NULL
                AND DATE(p.created_at) BETWEEN ? AND ?
                " . ($filter_semester ? "AND p.semester_id = ?" : "") . "
                " . ($payment_status === 'paid' ? "AND p.is_paid = 1" : ($payment_status === 'unpaid' ? "AND p.is_paid = 0" : "")) . "
                {$where_clause}
        ";
        
        $totals_stmt = $mysqli1->prepare($totals_sql);
        $totals_stmt->bind_param($count_types, ...$count_params);
        $totals_stmt->execute();
        $totals = $totals_stmt->get_result()->fetch_assoc();
        $totals_stmt->close();
        
        $grand_totals['total_paid'] = $totals['total_paid'] ?? 0;
        $grand_totals['payment_count'] = $totals['payment_count'] ?? 0;
        $grand_totals['student_count'] = $totals['student_count'] ?? 0;
        break;
}

$total_pages = ceil($total_records / $per_page);

// ============================================
// GET FILTER NAMES FOR DISPLAY
// ============================================

$college_name = $program_name = $batch_name = $section_name = $semester_name = $bank_name = '';

if ($filter_college) {
    $stmt = $mysqli1->prepare("SELECT name FROM colleges WHERE id = ?");
    $stmt->bind_param('i', $filter_college);
    $stmt->execute();
    $college_name = $stmt->get_result()->fetch_assoc()['name'] ?? '';
    $stmt->close();
}

if ($filter_program) {
    $stmt = $mysqli1->prepare("SELECT name FROM programs WHERE id = ?");
    $stmt->bind_param('i', $filter_program);
    $stmt->execute();
    $program_name = $stmt->get_result()->fetch_assoc()['name'] ?? '';
    $stmt->close();
}

if ($filter_batch) {
    $stmt = $mysqli1->prepare("SELECT value FROM batches WHERE id = ?");
    $stmt->bind_param('i', $filter_batch);
    $stmt->execute();
    $batch_name = $stmt->get_result()->fetch_assoc()['value'] ?? '';
    $stmt->close();
}

if ($filter_section) {
    $stmt = $mysqli1->prepare("SELECT name FROM sections WHERE id = ?");
    $stmt->bind_param('i', $filter_section);
    $stmt->execute();
    $section_name = $stmt->get_result()->fetch_assoc()['name'] ?? '';
    $stmt->close();
}

if ($filter_semester) {
    $stmt = $mysqli1->prepare("SELECT name FROM semesters WHERE id = ?");
    $stmt->bind_param('i', $filter_semester);
    $stmt->execute();
    $semester_name = $stmt->get_result()->fetch_assoc()['name'] ?? '';
    $stmt->close();
}

if ($filter_bank) {
    $stmt = $mysqli1->prepare("SELECT name FROM banks WHERE id = ?");
    $stmt->bind_param('i', $filter_bank);
    $stmt->execute();
    $bank_name = $stmt->get_result()->fetch_assoc()['name'] ?? '';
    $stmt->close();
}

// ============================================
// HELPER FUNCTIONS
// ============================================

function build_pagination_url($page, $params = []) {
    $current_params = $_GET;
    $current_params['page'] = $page;
    return '?' . http_build_query($current_params);
}

function format_money($amount) {
    return number_format($amount, 2) . ' SDG';
}

// If export format is not HTML, output and exit
if ($export_format !== 'html') {
    if ($export_format === 'excel') {
        // Simple Excel export (CSV format)
        echo "Report Type," . ucfirst($report_type) . "\n";
        echo "Date Range," . $start_date . " to " . $end_date . "\n";
        echo "Generated," . date('Y-m-d H:i:s') . "\n";
        echo "\n";
        
        switch ($report_type) {
            case 'summary':
                echo "Date,Bank,Payments,Students,Total Amount\n";
                foreach ($report_data as $row) {
                    echo $row['payment_date'] . ",";
                    echo $row['bank_name'] . ",";
                    echo $row['payment_count'] . ",";
                    echo $row['student_count'] . ",";
                    echo $row['total_paid'] . "\n";
                }
                echo "\nGrand Total,,," . $grand_totals['payment_count'] . "," . $grand_totals['total_paid'] . "\n";
                break;
                
            case 'college':
                echo "College,Students,Paid Students,Unpaid Students,Total Paid,Total Unpaid,Payments,Payment Total\n";
                foreach ($report_data as $row) {
                    echo $row['college_name'] . ",";
                    echo $row['total_students'] . ",";
                    echo $row['paid_students'] . ",";
                    echo $row['unpaid_students'] . ",";
                    echo $row['total_paid'] . ",";
                    echo $row['total_unpaid'] . ",";
                    echo $row['payment_count'] . ",";
                    echo $row['payment_total'] . "\n";
                }
                break;
                
            case 'detailed':
                echo "Date,Student ID,Student Name,Bank,Amount,Reference,Semester\n";
                foreach ($report_data as $row) {
                    echo $row['created_at'] . ",";
                    echo $row['student_id'] . ",";
                    echo ($row['student_name'] ?: $row['student_name_eng']) . ",";
                    echo $row['bank_name'] . ",";
                    echo $row['payment_amount'] . ",";
                    echo ($row['reference_number'] ?: 'N/A') . ",";
                    echo ($row['semester_name'] ?: 'N/A') . "\n";
                }
                echo "\nGrand Total,,,," . $grand_totals['total_paid'] . ",,\n";
                break;
        }
    } elseif ($export_format === 'pdf') {
        // Placeholder for PDF generation
        echo "PDF Export - Implement with your PDF library";
    }
    exit;
}

// ============================================
// UI STARTS HERE (for HTML output)
// ============================================
include 'header.php';
?>

<!-- Include Cairo Font and other assets -->
<link href="https://fonts.googleapis.com/css2?family=Cairo:wght@400;600;700;800&display=swap" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/sweetalert2@11"></script>

<style>
  /* Apply Cairo font to all elements */
  * {
    font-family: 'Cairo', sans-serif !important;
  }
  
  body {
    background-color: #f8f9fa;
  }
  
  .report-header {
    background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
    color: white;
    padding: 20px;
    border-radius: 10px;
    margin-bottom: 20px;
  }
  
  .filter-card {
    background: white;
    border-radius: 10px;
    padding: 20px;
    margin-bottom: 20px;
    box-shadow: 0 2px 4px rgba(0,0,0,0.1);
  }
  
  .report-card {
    background: white;
    border-radius: 10px;
    padding: 20px;
    margin-bottom: 20px;
    box-shadow: 0 2px 4px rgba(0,0,0,0.1);
  }
  
  .stats-card {
    background: linear-gradient(135deg, #43e97b 0%, #38f9d7 100%);
    color: white;
    border-radius: 10px;
    padding: 15px;
    text-align: center;
  }
  
  .stats-card .number {
    font-size: 2rem;
    font-weight: bold;
  }
  
  .stats-card .label {
    font-size: 0.9rem;
    opacity: 0.9;
  }
  
  .table th {
    background-color: #f8f9fa;
    font-weight: 600;
  }
  
  .export-buttons {
    display: flex;
    gap: 10px;
    justify-content: flex-end;
  }
  
  .export-btn {
    padding: 8px 15px;
    border-radius: 5px;
    color: white;
    text-decoration: none;
    font-size: 0.9rem;
  }
  
  .export-btn.excel {
    background-color: #217346;
  }
  
  .export-btn.pdf {
    background-color: #f40f02;
  }
  
  .export-btn.print {
    background-color: #6c757d;
  }
  
  .pagination {
    margin-top: 20px;
    justify-content: center;
  }
  
  .badge-paid {
    background-color: #d1fae5;
    color: #065f46;
    padding: 3px 8px;
    border-radius: 12px;
    font-size: 0.75rem;
  }
  
  .badge-unpaid {
    background-color: #fee2e2;
    color: #991b1b;
    padding: 3px 8px;
    border-radius: 12px;
    font-size: 0.75rem;
  }
  
  .badge-partial {
    background-color: #fed7aa;
    color: #9a3412;
    padding: 3px 8px;
    border-radius: 12px;
    font-size: 0.75rem;
  }
  
  @media print {
    .no-print {
      display: none !important;
    }
    body {
      background-color: white;
    }
  }
</style>

<div class="container-fluid py-3" dir="rtl">
    <!-- Header -->
    <div class="report-header">
        <div class="d-flex justify-content-between align-items-center">
            <div>
                <h3 class="mb-0 fw-bold">التقارير المالية</h3>
                <p class="mb-0 opacity-75">تقارير متقدمة عن المدفوعات والمتحصلات</p>
            </div>
            <div>
                <span class="badge bg-white text-dark p-2">مرحباً، <?= htmlspecialchars($committeeName ?: 'المحاسب') ?></span>
                <a href="accountant_dashboard.php" class="btn btn-light btn-sm ms-2">
                    <i class="fas fa-arrow-right me-1"></i>
                    العودة للوحة التحكم
                </a>
            </div>
        </div>
    </div>
    
    <!-- Filter Form -->
    <div class="filter-card no-print">
        <h6 class="fw-bold mb-3"><i class="fas fa-filter me-2"></i>فلتر التقارير</h6>
        
        <form method="get" action="" id="filterForm">
            <div class="row">
                <div class="col-md-3">
                    <label class="form-label">نوع التقرير</label>
                    <select class="form-control" name="report_type">
                        <option value="summary" <?= $report_type === 'summary' ? 'selected' : '' ?>>تقرير ملخص المدفوعات</option>
                        <option value="detailed" <?= $report_type === 'detailed' ? 'selected' : '' ?>>تقرير تفصيلي</option>
                        <option value="college" <?= $report_type === 'college' ? 'selected' : '' ?>>تقرير حسب الكلية</option>
                        <option value="program" <?= $report_type === 'program' ? 'selected' : '' ?>>تقرير حسب البرنامج</option>
                        <option value="bank" <?= $report_type === 'bank' ? 'selected' : '' ?>>تقرير حسب البنك</option>
                    </select>
                </div>
                
                <div class="col-md-3">
                    <label class="form-label">من تاريخ</label>
                    <input type="date" class="form-control" name="start_date" value="<?= htmlspecialchars($start_date) ?>" required>
                </div>
                
                <div class="col-md-3">
                    <label class="form-label">إلى تاريخ</label>
                    <input type="date" class="form-control" name="end_date" value="<?= htmlspecialchars($end_date) ?>" required>
                </div>
                
                <div class="col-md-3">
                    <label class="form-label">حالة الدفع</label>
                    <select class="form-control" name="payment_status">
                        <option value="all" <?= $payment_status === 'all' ? 'selected' : '' ?>>الجميع</option>
                        <option value="paid" <?= $payment_status === 'paid' ? 'selected' : '' ?>>مدفوع</option>
                        <option value="unpaid" <?= $payment_status === 'unpaid' ? 'selected' : '' ?>>غير مدفوع</option>
                        <option value="partial" <?= $payment_status === 'partial' ? 'selected' : '' ?>>مدفوع جزئياً</option>
                    </select>
                </div>
            </div>
            
            <div class="row mt-3">
                <div class="col-md-3">
                    <label class="form-label">الكلية</label>
                    <select class="form-control" name="filter_college" id="filter_college">
                        <option value="">جميع الكليات</option>
                        <?php
                        $colleges_result = $mysqli1->query("SELECT id, name FROM colleges ORDER BY name");
                        while ($college = $colleges_result->fetch_assoc()) {
                            $selected = $filter_college == $college['id'] ? 'selected' : '';
                            echo "<option value=\"{$college['id']}\" $selected>" . htmlspecialchars($college['name']) . "</option>";
                        }
                        ?>
                    </select>
                </div>
                
                <div class="col-md-3">
                    <label class="form-label">البرنامج</label>
                    <select class="form-control" name="filter_program" id="filter_program">
                        <option value="">جميع البرامج</option>
                        <?php
                        $programs_result = $mysqli1->query("SELECT id, name FROM programs WHERE deleted_at IS NULL ORDER BY name");
                        while ($program = $programs_result->fetch_assoc()) {
                            $selected = $filter_program == $program['id'] ? 'selected' : '';
                            echo "<option value=\"{$program['id']}\" $selected>" . htmlspecialchars($program['name']) . "</option>";
                        }
                        ?>
                    </select>
                </div>
                
                <div class="col-md-3">
                    <label class="form-label">الدفعة</label>
                    <select class="form-control" name="filter_batch">
                        <option value="">جميع الدفعات</option>
                        <?php
                        $batches_result = $mysqli1->query("SELECT id, value FROM batches WHERE deleted_at IS NULL ORDER BY id");
                        while ($batch = $batches_result->fetch_assoc()) {
                            $selected = $filter_batch == $batch['id'] ? 'selected' : '';
                            echo "<option value=\"{$batch['id']}\" $selected>" . htmlspecialchars($batch['value']) . "</option>";
                        }
                        ?>
                    </select>
                </div>
                
                <div class="col-md-3">
                    <label class="form-label">الفصل الدراسي</label>
                    <select class="form-control" name="filter_semester">
                        <option value="">جميع الفصول</option>
                        <?php
                        $semesters_result = $mysqli1->query("SELECT id, name FROM semesters WHERE deleted_at IS NULL ORDER BY id DESC");
                        while ($semester = $semesters_result->fetch_assoc()) {
                            $selected = $filter_semester == $semester['id'] ? 'selected' : '';
                            echo "<option value=\"{$semester['id']}\" $selected>" . htmlspecialchars($semester['name']) . "</option>";
                        }
                        ?>
                    </select>
                </div>
            </div>
            
            <div class="row mt-3">
                <div class="col-md-3">
                    <label class="form-label">البنك</label>
                    <select class="form-control" name="filter_bank">
                        <option value="">جميع البنوك</option>
                        <?php
                        $banks_result = $mysqli1->query("SELECT id, name FROM banks ORDER BY name");
                        while ($bank = $banks_result->fetch_assoc()) {
                            $selected = $filter_bank == $bank['id'] ? 'selected' : '';
                            echo "<option value=\"{$bank['id']}\" $selected>" . htmlspecialchars($bank['name']) . "</option>";
                        }
                        ?>
                    </select>
                </div>
                
                <div class="col-md-6">
                    <label class="form-label">&nbsp;</label>
                    <div class="d-flex gap-2">
                        <button type="submit" class="btn btn-primary">
                            <i class="fas fa-search me-1"></i>
                            عرض التقرير
                        </button>
                        <a href="financial_reports.php" class="btn btn-secondary">
                            <i class="fas fa-sync-alt me-1"></i>
                            إعادة تعيين
                        </a>
                    </div>
                </div>
                
                <div class="col-md-3">
                    <label class="form-label">&nbsp;</label>
                    <div class="export-buttons">
                        <a href="?<?= http_build_query(array_merge($_GET, ['export' => 'excel'])) ?>" class="export-btn excel">
                            <i class="fas fa-file-excel me-1"></i>
                            Excel
                        </a>
                        <a href="?<?= http_build_query(array_merge($_GET, ['export' => 'pdf'])) ?>" class="export-btn pdf">
                            <i class="fas fa-file-pdf me-1"></i>
                            PDF
                        </a>
                        <button onclick="window.print()" class="export-btn print">
                            <i class="fas fa-print me-1"></i>
                            طباعة
                        </button>
                    </div>
                </div>
            </div>
        </form>
    </div>
    
    <!-- Report Summary Stats -->
    <div class="row mb-3">
        <div class="col-md-3">
            <div class="stats-card">
                <div class="number"><?= number_format($grand_totals['total_paid'] ?? 0) ?> SDG</div>
                <div class="label">إجمالي المدفوعات</div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="stats-card" style="background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%);">
                <div class="number"><?= number_format($grand_totals['payment_count'] ?? 0) ?></div>
                <div class="label">عدد المعاملات</div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="stats-card" style="background: linear-gradient(135deg, #4facfe 0%, #00f2fe 100%);">
                <div class="number"><?= number_format($grand_totals['student_count'] ?? 0) ?></div>
                <div class="label">عدد الطلاب</div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="stats-card" style="background: linear-gradient(135deg, #fa709a 0%, #fee140 100%);">
                <div class="number"><?= number_format($grand_totals['total_receivable'] ?? 0) ?> SDG</div>
                <div class="label">إجمالي المستحق</div>
            </div>
        </div>
    </div>
    
    <!-- Report Table -->
    <div class="report-card">
        <div class="d-flex justify-content-between align-items-center mb-3">
            <h6 class="fw-bold mb-0">
                <?php
                $titles = [
                    'summary' => 'ملخص المدفوعات اليومية',
                    'detailed' => 'تفاصيل المدفوعات',
                    'college' => 'تقرير الكليات',
                    'program' => 'تقرير البرامج',
                    'bank' => 'تقرير البنوك'
                ];
                echo $titles[$report_type] ?? 'التقرير المالي';
                ?>
            </h6>
            <div>
                <span class="badge bg-info p-2">
                    الفترة: <?= date('d/m/Y', strtotime($start_date)) ?> - <?= date('d/m/Y', strtotime($end_date)) ?>
                </span>
                <?php if ($filter_semester): ?>
                    <span class="badge bg-purple p-2 ms-1">
                        الفصل: <?= htmlspecialchars($semester_name) ?>
                    </span>
                <?php endif; ?>
            </div>
        </div>
        
        <div class="table-responsive">
            <table class="table table-bordered table-hover">
                <thead class="table-light">
                    <tr>
                        <?php if ($report_type === 'summary'): ?>
                            <th>التاريخ</th>
                            <th>البنك</th>
                            <th class="text-center">عدد المدفوعات</th>
                            <th class="text-center">عدد الطلاب</th>
                            <th class="text-end">المبلغ</th>
                        <?php elseif ($report_type === 'college'): ?>
                            <th>الكلية</th>
                            <th class="text-center">إجمالي الطلاب</th>
                            <th class="text-center">طلاب مدفوعين</th>
                            <th class="text-center">طلاب غير مدفوعين</th>
                            <th class="text-end">إجمالي المدفوع</th>
                            <th class="text-end">إجمالي غير المدفوع</th>
                            <th class="text-center">عدد المعاملات</th>
                            <th class="text-end">إجمالي المعاملات</th>
                        <?php elseif ($report_type === 'program'): ?>
                            <th>البرنامج</th>
                            <th>الكلية</th>
                            <th class="text-center">إجمالي الطلاب</th>
                            <th class="text-center">طلاب مدفوعين</th>
                            <th class="text-end">إجمالي المدفوع</th>
                            <th class="text-end">إجمالي غير المدفوع</th>
                            <th class="text-center">عدد المعاملات</th>
                            <th class="text-end">إجمالي المعاملات</th>
                        <?php elseif ($report_type === 'bank'): ?>
                            <th>البنك</th>
                            <th class="text-center">عدد المعاملات</th>
                            <th class="text-center">عدد الطلاب</th>
                            <th class="text-end">إجمالي المبلغ</th>
                            <th>أول معاملة</th>
                            <th>آخر معاملة</th>
                        <?php else: ?>
                            <th>التاريخ</th>
                            <th>رقم الطالب</th>
                            <th>اسم الطالب</th>
                            <th>الكلية</th>
                            <th>البرنامج</th>
                            <th>البنك</th>
                            <th>المبلغ</th>
                            <th>رقم المرجع</th>
                            <th>الفصل</th>
                        <?php endif; ?>
                    </tr>
                </thead>
                <tbody>
                    <?php if (empty($report_data)): ?>
                        <tr>
                            <td colspan="9" class="text-center text-muted py-3">
                                لا توجد بيانات للعرض
                            </td>
                        </tr>
                    <?php else: ?>
                        <?php foreach ($report_data as $row): ?>
                            <tr>
                                <?php if ($report_type === 'summary'): ?>
                                    <td><?= date('d/m/Y', strtotime($row['payment_date'])) ?></td>
                                    <td><?= htmlspecialchars($row['bank_name']) ?></td>
                                    <td class="text-center"><?= number_format($row['payment_count']) ?></td>
                                    <td class="text-center"><?= number_format($row['student_count']) ?></td>
                                    <td class="text-end fw-bold text-success"><?= number_format($row['total_paid']) ?> SDG</td>
                                <?php elseif ($report_type === 'college'): ?>
                                    <td><?= htmlspecialchars($row['college_name']) ?></td>
                                    <td class="text-center"><?= number_format($row['total_students']) ?></td>
                                    <td class="text-center">
                                        <span class="badge-paid"><?= number_format($row['paid_students']) ?></span>
                                    </td>
                                    <td class="text-center">
                                        <span class="badge-unpaid"><?= number_format($row['unpaid_students']) ?></span>
                                    </td>
                                    <td class="text-end text-success"><?= number_format($row['total_paid']) ?> SDG</td>
                                    <td class="text-end text-danger"><?= number_format($row['total_unpaid']) ?> SDG</td>
                                    <td class="text-center"><?= number_format($row['payment_count']) ?></td>
                                    <td class="text-end"><?= number_format($row['payment_total']) ?> SDG</td>
                                <?php elseif ($report_type === 'program'): ?>
                                    <td><?= htmlspecialchars($row['program_name']) ?></td>
                                    <td><?= htmlspecialchars($row['college_name']) ?></td>
                                    <td class="text-center"><?= number_format($row['total_students']) ?></td>
                                    <td class="text-center">
                                        <span class="badge-paid"><?= number_format($row['paid_students']) ?></span>
                                    </td>
                                    <td class="text-end text-success"><?= number_format($row['total_paid']) ?> SDG</td>
                                    <td class="text-end text-danger"><?= number_format($row['total_unpaid']) ?> SDG</td>
                                    <td class="text-center"><?= number_format($row['payment_count']) ?></td>
                                    <td class="text-end"><?= number_format($row['payment_total']) ?> SDG</td>
                                <?php elseif ($report_type === 'bank'): ?>
                                    <td><?= htmlspecialchars($row['bank_name']) ?></td>
                                    <td class="text-center"><?= number_format($row['transaction_count']) ?></td>
                                    <td class="text-center"><?= number_format($row['student_count']) ?></td>
                                    <td class="text-end fw-bold text-success"><?= number_format($row['total_amount']) ?> SDG</td>
                                    <td><?= date('d/m/Y', strtotime($row['first_payment'])) ?></td>
                                    <td><?= date('d/m/Y', strtotime($row['last_payment'])) ?></td>
                                <?php else: ?>
                                    <td><?= date('d/m/Y H:i', strtotime($row['created_at'])) ?></td>
                                    <td><code><?= htmlspecialchars($row['student_id']) ?></code></td>
                                    <td><?= htmlspecialchars($row['student_name'] ?: $row['student_name_eng']) ?></td>
                                    <td><?= htmlspecialchars($row['college_name'] ?? '—') ?></td>
                                    <td><?= htmlspecialchars($row['program_name'] ?? '—') ?></td>
                                    <td><?= htmlspecialchars($row['bank_name']) ?></td>
                                    <td class="text-end fw-bold text-success"><?= number_format($row['payment_amount']) ?> SDG</td>
                                    <td><?= htmlspecialchars($row['reference_number'] ?: '—') ?></td>
                                    <td><?= htmlspecialchars($row['semester_name'] ?? '—') ?></td>
                                <?php endif; ?>
                            </tr>
                        <?php endforeach; ?>
                        
                        <!-- Grand Total Row -->
                        <?php if ($report_type !== 'detailed' && !empty($report_data)): ?>
                            <tr class="table-primary fw-bold">
                                <td colspan="<?= $report_type === 'summary' ? 4 : ($report_type === 'college' ? 7 : ($report_type === 'program' ? 7 : ($report_type === 'bank' ? 5 : 0))) ?>" class="text-start">
                                    الإجمالي العام:
                                </td>
                                <td class="text-end">
                                    <?php if ($report_type === 'summary'): ?>
                                        <?= number_format($grand_totals['total_paid']) ?> SDG
                                    <?php elseif ($report_type === 'college'): ?>
                                        <?= number_format($grand_totals['total_paid']) ?> SDG
                                    <?php elseif ($report_type === 'program'): ?>
                                        <?= number_format($grand_totals['total_paid']) ?> SDG
                                    <?php elseif ($report_type === 'bank'): ?>
                                        <?= number_format($grand_totals['total_paid']) ?> SDG
                                    <?php endif; ?>
                                </td>
                                <?php if ($report_type === 'college'): ?>
                                    <td class="text-end"><?= number_format($grand_totals['total_unpaid']) ?> SDG</td>
                                    <td class="text-center"><?= number_format($grand_totals['payment_count']) ?></td>
                                    <td class="text-end"><?= number_format($grand_totals['total_paid']) ?> SDG</td>
                                <?php elseif ($report_type === 'program'): ?>
                                    <td class="text-end"><?= number_format($grand_totals['total_unpaid']) ?> SDG</td>
                                    <td class="text-center"><?= number_format($grand_totals['payment_count']) ?></td>
                                    <td class="text-end"><?= number_format($grand_totals['total_paid']) ?> SDG</td>
                                <?php endif; ?>
                            </tr>
                        <?php endif; ?>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
        
        <!-- Pagination -->
        <?php if ($total_pages > 1): ?>
        <nav aria-label="Page navigation" class="mt-3 no-print">
            <ul class="pagination justify-content-center">
                <?php if ($page > 1): ?>
                    <li class="page-item">
                        <a class="page-link" href="<?= build_pagination_url(1) ?>">
                            <i class="fas fa-angle-double-right"></i>
                        </a>
                    </li>
                    <li class="page-item">
                        <a class="page-link" href="<?= build_pagination_url($page - 1) ?>">
                            <i class="fas fa-angle-right"></i>
                        </a>
                    </li>
                <?php endif; ?>
                
                <?php
                $start_page = max(1, $page - 2);
                $end_page = min($total_pages, $page + 2);
                
                for ($i = $start_page; $i <= $end_page; $i++):
                ?>
                    <li class="page-item <?= $i == $page ? 'active' : '' ?>">
                        <a class="page-link" href="<?= build_pagination_url($i) ?>"><?= $i ?></a>
                    </li>
                <?php endfor; ?>
                
                <?php if ($page < $total_pages): ?>
                    <li class="page-item">
                        <a class="page-link" href="<?= build_pagination_url($page + 1) ?>">
                            <i class="fas fa-angle-left"></i>
                        </a>
                    </li>
                    <li class="page-item">
                        <a class="page-link" href="<?= build_pagination_url($total_pages) ?>">
                            <i class="fas fa-angle-double-left"></i>
                        </a>
                    </li>
                <?php endif; ?>
            </ul>
        </nav>
        <?php endif; ?>
    </div>
    
    <!-- Report Notes -->
    <div class="text-muted small mt-2 no-print">
        <i class="fas fa-info-circle me-1"></i>
        تم إنشاء التقرير في: <?= date('Y-m-d H:i:s') ?>
        <?php if ($filter_college): ?>
            <span class="mx-2">|</span> الكلية: <?= htmlspecialchars($college_name) ?>
        <?php endif; ?>
        <?php if ($filter_program): ?>
            <span class="mx-2">|</span> البرنامج: <?= htmlspecialchars($program_name) ?>
        <?php endif; ?>
        <?php if ($filter_bank): ?>
            <span class="mx-2">|</span> البنك: <?= htmlspecialchars($bank_name) ?>
        <?php endif; ?>
    </div>
</div>

<script>
// AJAX for dynamic filters (if needed)
$(document).ready(function() {
    $('#filter_college').change(function() {
        const collegeId = $(this).val();
        const programSelect = $('#filter_program');
        
        if (collegeId && collegeId !== '') {
            programSelect.prop('disabled', true);
            
            $.ajax({
                url: 'ajax/get_programs.php',
                type: 'GET',
                data: { college_id: collegeId },
                dataType: 'json',
                success: function(programs) {
                    programSelect.find('option:not(:first)').remove();
                    if (programs && programs.length > 0) {
                        $.each(programs, function(index, program) {
                            programSelect.append($('<option>', {
                                value: program.id,
                                text: program.name
                            }));
                        });
                    }
                    programSelect.prop('disabled', false);
                },
                error: function() {
                    console.error('Error loading programs');
                    programSelect.prop('disabled', false);
                }
            });
        } else {
            programSelect.find('option:not(:first)').remove();
            programSelect.prop('disabled', false);
        }
    });
    
    // Show loading overlay on form submit
    $('#filterForm').submit(function() {
        $('#loadingOverlay').show();
    });
});

// Print function
function printReport() {
    window.print();
}
</script>

<!-- Loading Overlay -->
<div class="loading-overlay" id="loadingOverlay" style="display: none; position: fixed; top: 0; left: 0; width: 100%; height: 100%; background: rgba(255,255,255,0.8); z-index: 9999; justify-content: center; align-items: center;">
    <div class="loading-spinner" style="width: 50px; height: 50px; border: 5px solid #f3f3f3; border-top: 5px solid #3E6B54; border-radius: 50%; animation: spin 1s linear infinite;"></div>
</div>

<style>
@keyframes spin {
    0% { transform: rotate(0deg); }
    100% { transform: rotate(360deg); }
}

.bg-purple {
    background-color: #6f42c1;
    color: white;
}
</style>

<?php include 'footer.php'; ?>
