<?php
session_name('super_admin_session');
if (!is_dir(dirname(__DIR__) . '/session_data/super')) { mkdir(dirname(__DIR__) . '/session_data/super', 0700, true); } session_save_path(dirname(__DIR__) . '/session_data/super');
session_set_cookie_params(['path' => '/', 'httponly' => true, 'samesite' => 'Strict']);
session_start();
error_reporting(0);
include('includes/dbconnection.php');
require_once('vendor/autoload.php'); // Include mPDF

use Mpdf\Mpdf;
use Dotenv\Dotenv;

// Load environment variables
$dotenv = Dotenv::createImmutable(__DIR__);
$dotenv->load();

$school_name    = $_ENV['SCHOOL_NAME'];
$school_address = $_ENV['SCHOOL_ADDRESS'];
$logo           = $_ENV['WEBSITE_LOGO'];

// Collect filters (GET from downloadFilteredPDF)
$class   = trim($_GET['class']   ?? '');
$pay_for = trim($_GET['pay_for'] ?? '');
$session = trim($_GET['session'] ?? '');
$term    = trim($_GET['Term']    ?? '');
$section = trim($_GET['arm']     ?? '');

$pay_foru = strtoupper($pay_for);

// 1. ✅ Refined Base Query
// We use COALESCE and ensure the payment join includes session/term filters 
// so we don't accidentally sum payments from other terms.
$query = "
    SELECT 
        s.studentno AS admission_no,
        s.studentRegNo,
        s.studentName, 
        s.class, 
        s.section, 
        s.stream,
        s.demote_status,
        f.name   AS fee_name,
        f.amount AS default_required_fee,
        (SELECT COALESCE(SUM(p.amount), 0) 
         FROM payment p 
         WHERE p.studentno = s.studentno 
         AND p.pay_for = ? 
         " . (!empty($session) ? " AND p.session = ?" : "") . "
         " . (!empty($term) ? " AND p.Term = ?" : "") . "
        ) AS total_paid
    FROM students s
    LEFT JOIN fees f 
        ON s.class = f.class
        AND (s.stream = f.stream OR f.stream IS NULL OR f.stream = '' OR s.stream = '')
        AND f.name = ?
    WHERE s.status <> 3
";

// Prepare parameters dynamically based on if session/term are provided
$params = [$pay_for];
if (!empty($session)) $params[] = $session;
if (!empty($term))    $params[] = $term;
$params[] = $pay_for; // For the f.name join

$types = "s" . (!empty($session) ? "s" : "") . (!empty($term) ? "s" : "") . "s";

// ✅ Add Class/Section Filters
if (!empty($class)) {
    $query .= " AND s.class = ?";
    $params[] = $class;
    $types .= "s";
}
if (!empty($section)) {
    $query .= " AND s.section = ?";
    $params[] = $section;
    $types .= "s";
}

$query .= " ORDER BY s.class, s.section, s.studentName ASC";

$stmt = $con->prepare($query);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$result = $stmt->get_result();

// --- PDF HTML Construction ---
$html = "
<style>
    table { width: 100%; border-collapse: collapse; font-size: 11px; }
    th, td { padding: 6px; text-align: left; border: 1px solid #ccc; }
    th { background-color: #f2f2f2; color: #333; }
    .text-center { text-align: center; }
    .header-info { margin-bottom: 15px; }
</style>
<div class='text-center'>
    <img src='$logo' width='60'><br>
    <h2>$school_name</h2>
    <p>$school_address</p>
    <hr>
    <h4>$pay_foru PAYMENT BALANCE REPORT</h4>
</div>
<div class='header-info'>
    <table style='border:none;'>
        <tr style='border:none;'>
            <td style='border:none;'><strong>Session:</strong> $session</td>
            <td style='border:none;'><strong>Term:</strong> $term</td>
            <td style='border:none;'><strong>Class:</strong> $class ($section)</td>
        </tr>
    </table>
</div>

<table>
    <thead>
        <tr>
            <th>#</th>
            <th>Admission No.</th>
            <th>Student Name</th>
            <th>Fee Name</th>
            <th>Amount to Pay</th>
            <th>Amount Paid</th>
            <th>Amount to Balance</th>
            <th>%</th>
        </tr>
    </thead>
    <tbody>";

$cnt = 1;
$grand_total_paid = 0;

while ($row = $result->fetch_assoc()) {
    $requiredFee = $row['default_required_fee'] ?? 0;
    $totalPaid   = $row['total_paid'];
    $current_student_no = $row['admission_no'];

    // 2. ✅ School Fees Complex Logic Integration
    if ($pay_for == 'School Fees') {
        $st_class  = $row['class'];
        $st_stream = $row['stream'];

        // Determine if New or Old for this specific term
        $pTypeStmt = $con->prepare("SELECT pay_for FROM payment WHERE studentno = ? AND session = ? AND Term = ? AND pay_for LIKE 'School Fees%' LIMIT 1");
        $pTypeStmt->bind_param("sss", $current_student_no, $session, $term);
        $pTypeStmt->execute();
        $pTypeRes = $pTypeStmt->get_result()->fetch_assoc();
        
        $studentPayFor = $pTypeRes['pay_for'] ?? 'School Fees';
        $student_type = (strpos($studentPayFor, 'New Student') !== false) ? 'New' : 'Old';

        // Fetch custom fee from fee_records
        $feeRec = $con->prepare("SELECT amount FROM fee_records WHERE class = ? AND stream = ? AND name = ? AND student_type = ? AND session = ? AND term = ? ORDER BY created_at DESC LIMIT 1");
        $feeRec->bind_param("ssssss", $st_class, $st_stream, $studentPayFor, $student_type, $session, $term);
        $feeRec->execute();
        $feeRecRes = $feeRec->get_result()->fetch_assoc();
        
        if ($feeRecRes) {
            $requiredFee = $feeRecRes['amount'];
        }

        // Special Case: SS 1 repeaters
        if ($st_class === 'SS 1' && ($row['demote_status'] == 1) && $term === 'First Term') {
            // Logic to override or keep required fee based on your specific school policy
        }

        // Special Case: Specific Term Fees for JSS3/SS1/SS3
        if ($term === 'First Term' && in_array($st_class, ['JSS 3', 'SS 1', 'SS 3'])) {
            $spec_name = "First Term $st_class School Fees";
            $specStmt = $con->prepare("SELECT amount FROM fees WHERE class = ? AND name = ? LIMIT 1");
            $specStmt->bind_param("ss", $st_class, $spec_name);
            $specStmt->execute();
            $specRes = $specStmt->get_result()->fetch_assoc();
            if($specRes) $requiredFee = $specRes['amount'];
        }
    }

    $balance = max(0, $requiredFee - $totalPaid);
    $percentage = ($requiredFee > 0) ? ($totalPaid / $requiredFee) * 100 : 0;
    $grand_total_paid += $totalPaid;

    $html .= "<tr>
                <td>$cnt</td>
                <td>{$row['admission_no']}</td>
                <td>" . htmlspecialchars($row['studentName']) . "</td>
                <td>$pay_for</td>
                <td>" . number_format($requiredFee, 2) . "</td>
                <td>" . number_format($totalPaid, 2) . "</td>
                <td>" . number_format($balance, 2) . "</td>
                <td>" . round($percentage, 1) . "%</td>
              </tr>";
    $cnt++;
}

$html .= "<tr>
            <td colspan='5' style='text-align:right'><strong>Total Collected:</strong></td>
            <td colspan='3'><strong>" . number_format($grand_total_paid, 2) . "</strong></td>
          </tr>
        </tbody>
    </table>";

// ✅ Build dynamic filename
$filename_parts = [];
if (!empty($pay_for)) $filename_parts[] = preg_replace('/\s+/', '_', $pay_for);
if (!empty($class))   $filename_parts[] = preg_replace('/\s+/', '_', $class);
if (!empty($section)) $filename_parts[] = preg_replace('/\s+/', '_', $section);
if (!empty($term))    $filename_parts[] = preg_replace('/\s+/', '_', $term);
if (!empty($session)) $filename_parts[] = preg_replace('/\s+/', '_', $session);

$filename = "Fee_Balance_Report";
if (!empty($filename_parts)) {
    $filename .= "_" . implode("_", $filename_parts);
}
$filename .= ".pdf";

// ✅ Generate PDF
$mpdf = new Mpdf(['mode' => 'utf-8']);
$mpdf->SetAutoPageBreak(true, 15);
$mpdf->SetHeader("$school_name||Fee Balance Report");
$mpdf->SetFooter("Generated on " . date("M d, Y H:i A") . "||Page {PAGENO}");
$mpdf->WriteHTML($html);
$mpdf->Output($filename, 'D');

?>