<?php
// Smart Defaulters Tracker
// Pulls directly from fee_payment table (with student details joined)

// Filter options
$filter_class   = mysqli_real_escape_string($connection, $_GET['filter_class'] ?? '');
$filter_session = mysqli_real_escape_string($connection, $_GET['filter_session'] ?? '');
$filter_term    = mysqli_real_escape_string($connection, $_GET['filter_term'] ?? '');

// Base conditions
$where_clauses = ["fp.student_balance > 0"];

if (!empty($filter_class)) {
    $where_clauses[] = "fp.student_class = '$filter_class'";
}
if (!empty($filter_session)) {
    $where_clauses[] = "fp.fee_session = '$filter_session'";
}
if (!empty($filter_term)) {
    $where_clauses[] = "fp.fee_term = '$filter_term'";
}

$where_sql = implode(' AND ', $where_clauses);

// Build query
$sql = "
    SELECT
        fp.id,
        fp.reg_number,
        fp.student_name AS name,
        fp.student_class AS class,
        fp.fee_term,
        fp.fee_session,
        COALESCE(s.guardian_phone, '') AS phone,
        (COALESCE(fp.amount_paid, 0) + COALESCE(fp.student_balance, 0)) AS total_billed,
        COALESCE(fp.amount_paid, 0) AS total_paid,
        COALESCE(fp.student_balance, 0) AS balance_due
    FROM fee_payment fp
    LEFT JOIN students s ON s.reg_number = fp.reg_number
    WHERE {$where_sql}
    ORDER BY fp.student_balance DESC
";

// Export CSV handler
if (isset($_GET['export']) && $_GET['export'] == '1') {
    $export_res = mysqli_query($connection, $sql);
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=fee_defaulters_' . date('Y-m-d') . '.csv');
    $output = fopen('php://output', 'w');
    fputcsv($output, ['S/N', 'Reg Number', 'Student Name', 'Class', 'Session', 'Term', 'Guardian Phone', 'Total Billed (NGN)', 'Amount Paid (NGN)', 'Balance Due (NGN)']);

    if ($export_res && ($export_res instanceof mysqli_result)) {
        $idx = 1;
        while ($row = mysqli_fetch_assoc($export_res)) {
            fputcsv($output, [
                $idx++,
                $row['reg_number'],
                $row['name'],
                $row['class'],
                $row['fee_session'],
                $row['fee_term'],
                $row['phone'],
                $row['total_billed'],
                $row['total_paid'],
                $row['balance_due']
            ]);
        }
    }
    fclose($output);
    exit;
}

$defaulters = mysqli_query($connection, $sql);
$total_defaulters = ($defaulters && ($defaulters instanceof mysqli_result)) ? mysqli_num_rows($defaulters) : 0;

// Grand totals calculation based on current filter
$calc_sql = "
    SELECT 
        COUNT(*) AS count_debtors,
        COALESCE(SUM(fp.student_balance), 0) AS total_debt,
        COALESCE(SUM(fp.amount_paid), 0) AS total_collected
    FROM fee_payment fp
    WHERE {$where_sql}
";
$calc_res = mysqli_query($connection, $calc_sql);
$calc_data = ($calc_res && ($calc_res instanceof mysqli_result)) ? mysqli_fetch_assoc($calc_res) : ['count_debtors' => 0, 'total_debt' => 0, 'total_collected' => 0];

$grand_balance = (float)($calc_data['total_debt'] ?? 0);
$filtered_paid = (float)($calc_data['total_collected'] ?? 0);

// Total all-time fee collected
$paid_all_res = mysqli_query($connection, "SELECT COALESCE(SUM(amount_paid), 0) AS t FROM fee_payment");
$total_paid_all = ($paid_all_res && ($paid_all_res instanceof mysqli_result) && ($rr = mysqli_fetch_assoc($paid_all_res))) ? (float)$rr['t'] : 0;

// Sessions list
$sessions = mysqli_query($connection, "SELECT DISTINCT fee_session FROM fee_payment WHERE fee_session IS NOT NULL AND fee_session != '' ORDER BY fee_session DESC");
// Terms list
$terms    = mysqli_query($connection, "SELECT DISTINCT fee_term FROM fee_payment WHERE fee_term IS NOT NULL AND fee_term != '' ORDER BY fee_term ASC");
// Classes list
$classes  = mysqli_query($connection, "SELECT DISTINCT classes FROM classes ORDER BY classes ASC");
?>

<div class="page-title">
    <div>
        <h1><i class="fa fa-exclamation-triangle text-danger"></i> Smart Defaulters Tracker</h1>
        <p>Students with outstanding fee balances — auto-calculated from fee payment records</p>
    </div>
</div>

<!-- Summary Cards -->
<div class="row" style="margin-bottom: 20px;">
    <div class="col-md-4">
        <div class="card border-0 shadow-sm p-3 text-white" style="background: linear-gradient(135deg,#c0392b,#e74c3c); padding: 20px; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1);">
            <h2 style="margin-top: 0; font-weight: bold;"><?= number_format($total_defaulters) ?></h2>
            <p class="mb-0" style="margin-bottom: 0;"><i class="fa fa-users mr-1"></i> Students with Outstanding Debt</p>
        </div>
    </div>
    <div class="col-md-4">
        <div class="card border-0 shadow-sm p-3 text-white" style="background: linear-gradient(135deg,#e67e22,#f39c12); padding: 20px; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1);">
            <h2 style="margin-top: 0; font-weight: bold;">₦<?= number_format($grand_balance, 2) ?></h2>
            <p class="mb-0" style="margin-bottom: 0;"><i class="fa fa-money mr-1"></i> Outstanding Balance (Filtered)</p>
        </div>
    </div>
    <div class="col-md-4">
        <div class="card border-0 shadow-sm p-3 text-white" style="background: linear-gradient(135deg,#27ae60,#2ecc71); padding: 20px; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1);">
            <h2 style="margin-top: 0; font-weight: bold;">₦<?= number_format($total_paid_all, 2) ?></h2>
            <p class="mb-0" style="margin-bottom: 0;"><i class="fa fa-check-circle mr-1"></i> Total Fees Collected (All-Time)</p>
        </div>
    </div>
</div>

<!-- Filters -->
<div class="card border-0 shadow-sm" style="background: #fff; padding: 15px; border-radius: 8px; margin-bottom: 20px; box-shadow: 0 2px 4px rgba(0,0,0,0.05);">
    <form class="form-inline" method="GET" action="defaulters_tracker" style="display: flex; flex-wrap: wrap; gap: 10px; align-items: center;">
        <label class="mr-2 font-weight-bold" style="font-weight: bold;">Filter by:</label>
        
        <select name="filter_class" class="form-control form-control-sm" onchange="this.form.submit()">
            <option value="">-- All Classes --</option>
            <?php if ($classes && ($classes instanceof mysqli_result)): ?>
                <?php while ($c = mysqli_fetch_row($classes)): ?>
                    <option value="<?= htmlspecialchars($c[0]) ?>" <?= $filter_class == $c[0] ? 'selected' : '' ?>><?= htmlspecialchars($c[0]) ?></option>
                <?php endwhile; ?>
            <?php endif; ?>
        </select>

        <select name="filter_session" class="form-control form-control-sm" onchange="this.form.submit()">
            <option value="">-- All Sessions --</option>
            <?php if ($sessions && ($sessions instanceof mysqli_result)): ?>
                <?php while ($s = mysqli_fetch_row($sessions)): ?>
                    <option value="<?= htmlspecialchars($s[0]) ?>" <?= $filter_session == $s[0] ? 'selected' : '' ?>><?= htmlspecialchars($s[0]) ?></option>
                <?php endwhile; ?>
            <?php endif; ?>
        </select>

        <select name="filter_term" class="form-control form-control-sm" onchange="this.form.submit()">
            <option value="">-- All Terms --</option>
            <?php if ($terms && ($terms instanceof mysqli_result)): ?>
                <?php while ($t = mysqli_fetch_row($terms)): ?>
                    <option value="<?= htmlspecialchars($t[0]) ?>" <?= $filter_term == $t[0] ? 'selected' : '' ?>><?= htmlspecialchars($t[0]) ?></option>
                <?php endwhile; ?>
            <?php endif; ?>
        </select>

        <a href="defaulters_tracker" class="btn btn-sm btn-default">Reset Filters</a>
    </form>
</div>

<!-- Defaulters Table -->
<div class="card border-0 shadow-sm" style="background: #fff; padding: 20px; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.05);">
    <div style="display: flex; justify-content: space-between; align-items: center; margin-bottom: 15px;">
        <h4 style="margin: 0; font-weight: bold;">Fee Defaulters List</h4>
        <?php
        $export_url = "defaulters_tracker?export=1"
            . (!empty($filter_class) ? "&filter_class=" . urlencode($filter_class) : "")
            . (!empty($filter_session) ? "&filter_session=" . urlencode($filter_session) : "")
            . (!empty($filter_term) ? "&filter_term=" . urlencode($filter_term) : "");
        ?>
        <a href="<?= $export_url ?>" class="btn btn-sm btn-success">
            <i class="fa fa-download mr-1"></i> Export to CSV
        </a>
    </div>

    <div class="table-responsive">
        <table class="table table-bordered table-hover" id="myTable" width="100%">
            <thead>
                <tr class="bg-primary" style="background-color: #337ab7; color: #fff;">
                    <th>#</th>
                    <th>Student Name</th>
                    <th>Reg No</th>
                    <th>Class</th>
                    <th>Session</th>
                    <th>Term</th>
                    <th>Guardian Phone</th>
                    <th>Total Billed</th>
                    <th>Amount Paid</th>
                    <th>Balance Due</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php if ($total_defaulters > 0 && $defaulters && ($defaulters instanceof mysqli_result)): ?>
                    <?php 
                    $sn = 1;
                    while ($row = mysqli_fetch_assoc($defaulters)): 
                        $urgency = $row['balance_due'] >= 50000 ? 'danger' : ($row['balance_due'] >= 20000 ? 'warning' : '');
                    ?>
                    <tr class="<?= $urgency ?>">
                        <td><?= $sn++ ?></td>
                        <td><strong><?= htmlspecialchars($row['name'] ?? '') ?></strong></td>
                        <td><?= htmlspecialchars($row['reg_number'] ?? '') ?></td>
                        <td><?= htmlspecialchars($row['class'] ?? '') ?></td>
                        <td><?= htmlspecialchars($row['fee_session'] ?? '') ?></td>
                        <td><?= htmlspecialchars($row['fee_term'] ?? '') ?></td>
                        <td><?= htmlspecialchars($row['phone'] ?? 'N/A') ?></td>
                        <td>₦<?= number_format((float)$row['total_billed'], 2) ?></td>
                        <td class="text-success" style="color: #27ae60; font-weight: bold;">₦<?= number_format((float)$row['total_paid'], 2) ?></td>
                        <td class="text-danger" style="color: #c0392b; font-weight: bold;">₦<?= number_format((float)$row['balance_due'], 2) ?></td>
                        <td>
                            <a href="mailbox" class="btn btn-xs btn-warning" title="Send message via mailbox">
                                <i class="fa fa-envelope"></i>
                            </a>
                            <a href="send_sms" class="btn btn-xs btn-primary" title="Send SMS">
                                <i class="fa fa-paper-plane"></i>
                            </a>
                        </td>
                    </tr>
                    <?php endwhile; ?>
                <?php else: ?>
                    <tr>
                        <td colspan="11" class="text-center" style="padding: 40px; color: #7f8c8d;">
                            <i class="fa fa-check-circle fa-3x text-success" style="display: block; margin-bottom: 10px; color: #2ecc71;"></i>
                            <strong>No defaulters found for the selected criteria! All fees are cleared.</strong>
                        </td>
                    </tr>
                <?php endif; ?>
            </tbody>
        </table>
    </div>
</div>
