<?php
declare(strict_types=1);

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);

require_once('includes/dbconnection.php');
require_once('vendor/autoload.php');

use Mpdf\Mpdf;
use Dotenv\Dotenv;

/* =========================
   AUTH CHECK
========================= */
if (!isset($_SESSION['sid']) || empty($_SESSION['sid'])) {
    header('Location: logout.php');
    exit;
}

/* =========================
   LOAD ENV (School Info)
========================= */
if (file_exists(__DIR__ . '/.env')) {
    $dotenv = Dotenv::createImmutable(__DIR__);
    $dotenv->load();
}

$school_name    = $_ENV['SCHOOL_NAME'] ?? 'SCHOOL MANAGEMENT SYSTEM';
$school_address = $_ENV['SCHOOL_ADDRESS'] ?? 'School Address';
$logo           = $_ENV['WEBSITE_LOGO'] ?? 'dist/img/logo.png';

/* =========================
   SANITIZE GET INPUT
========================= */
$class   = trim(filter_input(INPUT_GET, 'class', FILTER_SANITIZE_SPECIAL_CHARS) ?? '');
$pay_for = trim(filter_input(INPUT_GET, 'pay_for', FILTER_SANITIZE_SPECIAL_CHARS) ?? 'School Fees');
$session = trim(filter_input(INPUT_GET, 'session', FILTER_SANITIZE_SPECIAL_CHARS) ?? '');
$term    = trim(filter_input(INPUT_GET, 'Term', FILTER_SANITIZE_SPECIAL_CHARS) ?? '');
$section = trim(filter_input(INPUT_GET, 'arm', FILTER_SANITIZE_SPECIAL_CHARS) ?? '');

if (!$session || !$term) {
    die('Invalid parameters.');
}

$pay_for_upper = strtoupper($pay_for);

/* =========================
   MAIN STUDENT QUERY
========================= */
$query = "
    SELECT 
        s.studentno,
        s.studentRegNo,
        s.id,
        s.studentName, 
        s.class, 
        s.section, 
        s.stream, 
        s.status,
        s.demote_status,
        COALESCE(SUM(p.amount), 0) AS total_paid
    FROM students s
    LEFT JOIN payment p 
        ON s.id = p.student_id
        AND p.pay_for LIKE ?
        AND p.session = ?
        AND (
                (s.class IN ('JSS 3','SS 3')) 
            OR (p.Term = ?)
            )
    WHERE s.status = 1
";

$pay_param = $pay_for . '%';
$params = [$pay_param, $session, $term];
$types  = "sss";

if (!empty($class)) {
    $query .= " AND s.class = ?";
    $params[] = $class;
    $types .= "s";
}
if (!empty($section)) {
    $query .= " AND s.section = ?";
    $params[] = $section;
    $types .= "s";
}

$query .= " GROUP BY s.id 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 HEADER
========================= */
$html = "
<style>
body { font-family: sans-serif; }
table { width: 100%; border-collapse: collapse; margin-top: 10px; font-size: 11px; }
th, td { padding: 7px; border: 1px solid #444; }
th { background-color: #f2f2f2; }
.text-right { text-align: right; }
.text-center { text-align: center; }
.text-danger { color: #d9534f; }
.header-info { margin-bottom: 15px; border-bottom: 2px solid #444; padding-bottom: 10px; }
</style>

<div class='header-info text-center'>
    <img src='$logo' width='70'><br>
    <h2 style='margin-bottom:0;'>$school_name</h2>
    <p style='margin-top:5px;'>$school_address</p>
    <h3>$pay_for_upper DEFAULTERS REPORT</h3>
    <p>
        <strong>Session:</strong> $session |
        <strong>Term:</strong> $term |
        <strong>Class:</strong> " . ($class ?: 'All') . " " . ($section ?: '') . "
    </p>
</div>

<table>
<thead>
<tr>
    <th>#</th>
    <th>Adm No.</th>
    <th>Student Name</th>
    <th>Class</th>
    <th>Required</th>
    <th>Paid</th>
    <th>Balance</th>
</tr>
</thead>
<tbody>
";

$cnt = 1;
$total_due = 0;

/* =========================
   LOOP + EXACT FEE LOGIC
========================= */
while ($row = $result->fetch_assoc()) {

    $student_id = $row['id'];
    $stu_class  = $row['class'];
    $stu_stream = $row['stream'];
    $stu_arm    = $row['section'];
    $totalPaid  = (float)$row['total_paid'];
    $adm_no     = $row['studentno'] ?? $row['studentRegNo'];

    /* A. Determine Student Type */
    $student_type = 'Old';
    $typeQuery = $con->prepare("
        SELECT pay_for FROM payment 
        WHERE student_id=? 
        AND class=? 
        AND session=? 
        AND Term=? 
        AND pay_for LIKE '%School Fees%' 
        LIMIT 1
    ");
    $typeQuery->bind_param("isss", $student_id, $stu_class, $session, $term);
    $typeQuery->execute();
    $typeResult = $typeQuery->get_result()->fetch_assoc();

    if ($typeResult && strpos($typeResult['pay_for'], 'New Student') !== false) {
        $student_type = 'New';
    }

    /* B. Base Fee Lookup */
    $requiredFee = 0;

    $feeQ = $con->prepare("
        SELECT amount FROM fee_records 
        WHERE class=? AND stream=? AND name=? 
        AND student_type=? AND session=? AND term=? 
        LIMIT 1
    ");
    $feeQ->bind_param("ssssss", $stu_class, $stu_stream, $pay_for, $student_type, $session, $term);
    $feeQ->execute();
    $fResult = $feeQ->get_result()->fetch_assoc();
    if ($fResult) {
        $requiredFee = (float)$fResult['amount'];
    }

    /* Fallback to fees table */
    if ($requiredFee == 0) {
        $feeQ2 = $con->prepare("SELECT amount FROM fees WHERE class=? AND name=? LIMIT 1");
        $feeQ2->bind_param("ss", $stu_class, $pay_for);
        $feeQ2->execute();
        $fallback = $feeQ2->get_result()->fetch_assoc();
        if ($fallback) {
            $requiredFee = (float)$fallback['amount'];
        }
    }

    /* C. SS1 Repeater */
    if ($stu_class === 'SS 1' && $row['demote_status'] == 1 && $term === 'First Term') {
        $repQ = $con->prepare("SELECT amount FROM fees WHERE class=? AND name='SS 1 Repeater Fees' LIMIT 1");
        $repQ->bind_param("s", $stu_class);
        $repQ->execute();
        if ($repRes = $repQ->get_result()->fetch_assoc()) {
            $requiredFee = (float)$repRes['amount'];
        }
    }

    /* D. SS1 First Term Override */
    if ($term === 'First Term' && $stu_class === 'SS 1') {
        $special_name = "First Term $stu_class School Fees";
        $sfq = $con->prepare("SELECT amount FROM fees WHERE class=? AND name=? LIMIT 1");
        $sfq->bind_param("ss", $stu_class, $special_name);
        $sfq->execute();
        if ($sfRow = $sfq->get_result()->fetch_assoc()) {
            $requiredFee = (float)$sfRow['amount'];
        }
    }

    /* E. JSS3 / SS3 Special Logic */
    if (in_array($stu_class, ['JSS 3','SS 3'])) {

        $requiredFee1 = 0;
        $requiredFee2 = 0;

        $special_name = "First Term $stu_class School Fees";

        $sfq = $con->prepare("
            SELECT amount FROM fee_records 
            WHERE class=? AND name=? AND stream=? 
            AND session=? AND term='First Term' LIMIT 1
        ");
        $sfq->bind_param("ssss", $stu_class, $special_name, $stu_stream, $session);
        $sfq->execute();
        if ($sfRow = $sfq->get_result()->fetch_assoc()) {
            $requiredFee1 = (float)$sfRow['amount'];
        }

        $sfq1 = $con->prepare("
            SELECT amount FROM fee_records 
            WHERE class=? AND name='School Fees' 
            AND stream=? AND session=? AND term=? LIMIT 1
        ");
        $sfq1->bind_param("ssss", $stu_class, $stu_stream, $session, $term);
        $sfq1->execute();
        if ($sfRow1 = $sfq1->get_result()->fetch_assoc()) {
            $requiredFee2 = (float)$sfRow1['amount'] * 2;
        }

        $requiredFee = $requiredFee1 + $requiredFee2;
    }

    $balance = $requiredFee - $totalPaid;

    if ($balance > 0) {

        $total_due += $balance;

        $html .= "
        <tr>
            <td>$cnt</td>
            <td>" . htmlspecialchars($adm_no) . "</td>
            <td>" . htmlspecialchars($row['studentName']) . "</td>
            <td>$stu_class ($stu_arm)</td>
            <td class='text-right'>" . number_format($requiredFee, 2) . "</td>
            <td class='text-right'>" . number_format($totalPaid, 2) . "</td>
            <td class='text-danger text-right'><strong>" . number_format($balance, 2) . "</strong></td>
        </tr>";

        $cnt++;
    }
}

/* GRAND TOTAL ROW */
$html .= "
<tr>
    <td colspan='6' class='text-right'><strong>Grand Total Owed:</strong></td>
    <td class='text-danger text-right'><strong>" . number_format($total_due, 2) . "</strong></td>
</tr>
</tbody>
</table>";

/* =========================
   GENERATE PDF
========================= */

$filename = "Defaulters_" . preg_replace('/\s+/', '_', $class ?: 'All') . "_" . preg_replace('/\s+/', '_', $term) . ".pdf";

$mpdf = new Mpdf([
    'margin_left' => 10,
    'margin_right' => 10,
    'margin_top' => 15,
    'margin_bottom' => 15,
]);

$mpdf->SetTitle("Defaulter List Report");
$mpdf->SetAuthor($school_name);
$mpdf->WriteHTML($html);
$mpdf->Output($filename, 'D');
exit;