<?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 __DIR__ . '/includes/session_term_helper.php';
// Fetch current session and term (with gap/fallback support)
$stResult = resolveCurrentSessionTerm($con);
$currentSessionId   = $stResult['session_id'];
$currentSessionName = $stResult['session_name'];
$currentTermId      = $stResult['term_id'];
$currentTermName    = $stResult['term_name'];


if (!isset($_SESSION['sid']) || strlen($_SESSION['sid']) == 0) {

    header('location:logout.php');

    exit();

}



/**

 * 1. AUTOMATIC DEFAULTS

 */



$currentSessionId = null;

$currentSessionStr = null;

$currentSessionQuery = "SELECT id, session_name

                        FROM school_sessions

                        WHERE start_date <= CURDATE()

                          AND end_date >= CURDATE()

                        ORDER BY start_date ASC

                        LIMIT 1";

$currentSessionResult = $con->query($currentSessionQuery);



if ($currentSessionResult && $currentSessionResult->num_rows > 0) {

    $currentSession = $currentSessionResult->fetch_assoc();

    $currentSessionId = $currentSession['id'];

    $currentSessionStr = $currentSession['session_name'];

    $currentSessionResult->free(); // Free result set

}



// --- 3. Fetch Current Term (mysqli prepared) ---



$currentTermId = null;

$currentTermStr = null;

if ($currentSessionId !== null) {

    // Use prepared statement for current term (prevent potential injection if $currentSessionId was user-provided, though here it's database-fetched)

    $termQuery = "SELECT id, term_name

                  FROM school_terms

                  WHERE session_id = ?

                    AND start_date <= CURDATE()

                    AND end_date >= CURDATE()

                  ORDER BY start_date ASC

                  LIMIT 1";

    $stmt = $con->prepare($termQuery);

    if ($stmt) {

        $stmt->bind_param("i", $currentSessionId);

        $stmt->execute();

        $termResult = $stmt->get_result();

        $termRow = $termResult->fetch_assoc();

        $currentTermId = $termRow['id'] ?? null;

        $currentTermStr = $termRow['term_name'] ?? null;

        $stmt->close();

        unset($termRow);

    }

}

/**

 * 2. RETRIEVE POST DATA

 */

$session = $_POST['session'] ?? $currentSessionStr;

$term    = $_POST['Term'] ?? $currentTermStr;

$class   = trim($_POST['class'] ?? '');

$section = trim($_POST['arm'] ?? '');

$pay_for = trim($_POST['pay_for'] ?? 'School Fees'); 



$result = false;



/**

 * 3. DYNAMIC SQL CONSTRUCTION

 */

if (!empty($pay_for)) {

    $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.studentno ORDER BY s.class ASC, s.section ASC, s.studentName ASC";



    $stmt = $con->prepare($query);

    if ($stmt) {

        $stmt->bind_param($types, ...$params);

        $stmt->execute();

        $result = $stmt->get_result();

    }

}

?>



<!DOCTYPE html>

<html lang="en">

<?php @include("includes/head.php"); ?>

<body class="hold-transition sidebar-mini layout-fixed">

<div class="wrapper">

    <?php @include("includes/header.php"); ?>

    <?php @include("includes/sidebar.php"); ?>



    <div class="content-wrapper">

            <section class="content-header premium-header">

    <div class="container-fluid">

        <div class="header-glass-card">

            <div class="row align-items-center">

                <div class="col-md-8">

                    <div class="title-section">

                        <div class="icon-box bg-primary-gradient shadow-sm">

                            <i class="fas fa-wallet"></i>

                        </div>

                        <div>

                            <h1 class="premium-title text-primary">

                                Payment Management

                                <span class="premium-subtitle text-secondary">Defaulter List</span>

                            </h1>
                    <div class="mt-1">
                      <?php if ($currentSessionName): ?>
                        <span class="badge badge-primary mr-1">
                          <i class="fas fa-calendar-alt"></i>
                          <?= htmlspecialchars($currentSessionName) ?>
                        </span>
                      <?php endif; ?>
                      <?php if ($currentTermName): ?>
                        <span class="badge badge-success">
                          <i class="fas fa-bookmark"></i>
                          <?= htmlspecialchars($currentTermName) ?>
                        </span>
                      <?php else: ?>
                        <span class="badge badge-warning">
                          <i class="fas fa-exclamation-triangle"></i> No active term
                        </span>
                      <?php endif; ?>
                    </div>

                        </div>

                    </div>

                </div>

                <div class="col-md-4">

                    <nav aria-label="breadcrumb">

                        <ol class="breadcrumb premium-breadcrumb">

                            <li class="breadcrumb-item"><a href="dashboard.php" class="text-primary">Home</a></li>

                            <li class="breadcrumb-item active" aria-current="page">Defaulter List</li>

                        </ol>

                    </nav>

                </div>

            </div>

        </div>

    </div>

</section>



        <section class="content">

            <div class="container-fluid">

                <div class="card card-primary">

                    <div class="card-header">

                        <h3 class="card-title"> <i class="fas fa-exclamation-triangle"></i> Filter Defaulters</h3>

                    </div>

                    <div class="card-body">

                        <form method="POST">

                            <div class="row">

                                <div class="col-md-3">

                                    <label>Session</label>

                                    <select name="session" id="session" class="form-control select2">

                                        <?php

                                        $sq = $con->query("SELECT session_name FROM school_sessions ORDER BY id DESC");

                                        while($r = $sq->fetch_assoc()){

                                            echo "<option value='".$r['session_name']."' ".($r['session_name']==$session?'selected':'').">".$r['session_name']."</option>";

                                        }

                                        ?>

                                    </select>

                                </div>

                                <div class="col-md-3">

                                    <label>Term</label>

                                    <select name="Term" id="Term" class="form-control select2">

                                        <?php

                                        $tq = $con->query("SELECT DISTINCT term_name FROM school_terms ORDER BY term_name ASC");

                                        while($r = $tq->fetch_assoc()){

                                            echo "<option value='".$r['term_name']."' ".($r['term_name']==$term?'selected':'').">".$r['term_name']."</option>";

                                        }

                                        ?>

                                    </select>

                                </div>

                                <div class="col-md-2">

                                    <label>Class</label>

                                    <select name="class" id="class" class="form-control select2" onchange="fetchArms()">

                                        <option value="">All Classes</option>

                                        <?php

                                        $cq = $con->query("SELECT DISTINCT class FROM class_section ORDER BY class");

                                        while($r = $cq->fetch_assoc()){

                                            echo "<option value='".$r['class']."' ".($r['class']==$class?'selected':'').">".$r['class']."</option>";

                                        }

                                        ?>

                                    </select>

                                </div>

                                <div class="col-md-2">

                                    <label>Arm</label>

                                    <select name="arm" id="arm" class="form-control select2">

                                        <option value="">All Arms</option>

                                    </select>

                                </div>

                                <div class="col-md-2">

                                    <label>&nbsp;</label>

                                    <button type="submit" class="btn btn-success btn-block"><i class="fa fa-search"></i> Generate</button>

                                    <button type="button" class="btn btn-danger btn-block" onclick="downloadFilteredPDF()">

                                        <i class="fas fa-file-pdf"></i> Download PDF

                                    </button>

                                </div>

                            </div>

                            <input type="hidden" name="pay_for" id="pay_for" value="School Fees">

                        </form>

                    </div>

                </div>



                <div class="card">

                    <div class="card-body">

                  <table id="example1" class="table table-bordered table-hover">

                            <thead>

                                <tr>

                                    <th>#</th>

                                    <th>Admission No.</th>

                                    <th>Student Name</th>

                                    <th>Class</th>

                                    <th>Required Amount</th>

                                    <th>Paid</th>

                                    <th class="text-danger">Balance</th>

                                    <th>Status</th>

                                </tr>

                            </thead>

                            <tbody>

                                <?php 

                                if ($result && $result->num_rows > 0) {

                                    $cnt = 1;

                                    while ($row = $result->fetch_assoc()) {

                                        $student_id = $row['id'];

                                        $stu_class  = $row['class'];

                                        $stu_stream = $row['stream'];

                                        $totalPaid  = $row['total_paid'];

                                        $stu_arm = $row['section'];



                                        // Determination Logic

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

                                        $student_type = ($typeResult && strpos($typeResult['pay_for'], 'New Student') !== false) ? 'New' : 'Old';



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

                                        $fRow = $feeQ->get_result()->fetch_assoc();

                                        $requiredFee = $fRow['amount'] ?? 0;



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

                                            $requiredFee = $feeQ2->get_result()->fetch_assoc()['amount'] ?? 0;

                                        }



                                        // Special calculations

                                        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 = $repRes['amount']; }

                                        }



                                        if ($term === 'First Term' && in_array($stu_class, ['SS 1'])) {

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

                                            $sfq = $con->prepare("SELECT amount FROM fee_records WHERE class=? AND name=? AND stream=?  LIMIT 1");

                                            $sfq->bind_param("sss", $stu_class, $special_name, $stu_stream);

                                            $sfq->execute();

                                            if($sfRow = $sfq->get_result()->fetch_assoc()){ $requiredFee = $sfRow['amount']; }

                                        }



                                        if (($term === 'First Term' || $term === 'Second Term' || $term === 'Third Term') && in_array($stu_class, ['JSS 3','SS 3'])) {

                                            $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, $currentSessionStr);

                                            $sfq->execute();

                                            if($sfRow = $sfq->get_result()->fetch_assoc()){ $requiredFee1 = $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, $currentSessionStr, $currentTermStr);

                                            $sfq1->execute();

                                            if($sfRow1 = $sfq1->get_result()->fetch_assoc()){ $requiredFee2 = $sfRow1['amount'] * 2; }   

                                            

                                            $requiredFee = $requiredFee1 + $requiredFee2;

                                        }



                                        $balance = $requiredFee - $totalPaid;

                                        if ($balance > 0) {

                                ?>

                                    <tr>

                                        <td><?= $cnt++; ?></td>

                                        <td><?= htmlspecialchars($row['studentno'] ?? $row['studentRegNo']); ?></td>

                                        <td><?= htmlspecialchars($row['studentName']); ?></td>

                                        <td><?= "$stu_class ($stu_arm)"; ?></td>

                                        <td><?= number_format($requiredFee, 2); ?></td>

                                        <td class="text-success"><?= number_format($totalPaid, 2); ?></td>

                                        <td class="text-danger font-weight-bold"><?= number_format($balance, 2); ?></td>

                                        <td><span class="badge badge-danger">Owing</span></td>

                                    </tr>

                                <?php 

                                        }

                                    } 

                                } ?>

                            </tbody>

                        </table>

                    </div>

                </div>

            </div>

        </section>

    </div>

    <?php @include("includes/footer.php"); ?>

</div>

<?php @include("includes/foot.php"); ?>

  <script src="dist/js/select2.min.js"></script>

<script>

        $(document).ready(function() {

      $('#class, #session, #Term, #arm').select2();

    });

    function downloadFilteredPDF() {

      // Corrected: Grabbing values by ID

      var session = document.getElementById("session").value;

      var term    = document.getElementById("Term").value;

      var classs  = document.getElementById("class").value;

      var section = document.getElementById("arm").value;

      var pay_for = document.getElementById("pay_for").value;



      var url = "generate_pdf_defaulter.php?pay_for=" + encodeURIComponent(pay_for);

      url += "&session=" + encodeURIComponent(session);

      url += "&Term=" + encodeURIComponent(term);



      if (classs) { url += "&class=" + encodeURIComponent(classs); }

      if (section) { url += "&arm=" + encodeURIComponent(section); }



      window.location.href = url;

    }



    function fetchArms() {

        var classVal = document.getElementById("class").value;

        var armSelect = document.getElementById("arm");

        if(!classVal) {

            armSelect.innerHTML = '<option value="">All Arms</option>';

            return;

        }

        fetch("fetch_arms.php?class=" + encodeURIComponent(classVal))

            .then(res => res.json())

            .then(data => {

                armSelect.innerHTML = '<option value="">All Arms</option>';

                data.forEach(arm => {

                    let opt = document.createElement("option");

                    opt.value = arm; opt.textContent = arm;

                    armSelect.appendChild(opt);

                });

            });

    }

</script>

</body>

</html>