<?php
/*
|--------------------------------------------------------------------------
| OUTSTANDING FEES (ONLINE PAYMENT ALIGNED)
|--------------------------------------------------------------------------
| - Uses fee_payment as source of truth
| - Groups by student + term + session
| - Reflects webhook-updated balances
*/

// --- Filters ---
$filters = [];
$params = [];

if (!empty($_GET['term'])) {
    $filters[] = "fp.fee_term = ?";
    $params[] = $_GET['term'];
}
if (!empty($_GET['session'])) {
    $filters[] = "fp.fee_session = ?";
    $params[] = $_GET['session'];
}
if (!empty($_GET['class'])) {
    $filters[] = "fp.student_class = ?";
    $params[] = $_GET['class'];
}

$whereClause = $filters ? 'WHERE ' . implode(' AND ', $filters) : '';

// --- Query Outstanding Fees ---
$sql = "
SELECT 
    fp.reg_number,
    fp.student_name,
    fp.student_class,
    fp.fee_term,
    fp.fee_session,
    SUM(fp.amount_paid) AS total_paid,
    MAX(fp.student_balance) AS student_balance,
    MAX(fp.payment_status) AS payment_status
FROM fee_payment fp
" . $whereClause . "
GROUP BY fp.reg_number, fp.student_class, fp.fee_term, fp.fee_session
HAVING student_balance > 0
ORDER BY fp.student_class, fp.student_name
";

$stmt = $connection->prepare($sql);
if ($filters) {
    $types = str_repeat('s', count($params));
    $stmt->bind_param($types, ...$params);
}
$stmt->execute();
$result = $stmt->get_result();
?>

<div class='page-title'>
  <div>
    <h1><i class='fa fa-laptop'></i> Fee Management</h1>
    <p>Outstanding Fees</p>
  </div>
</div>

<!-- FILTER -->
<div class="card mb-3">
  <div class="panel panel-primary">
    <div class="panel-heading text-center"><b>Filter Outstanding Fees</b></div>
    <div class="panel-body">
      <form method="GET" class="form-row">
        <div class="form-group col-md-3">
          <label>Term</label>
          <select name="term" class="form-control" required>
            <option value="">-- Select Term --</option>
            <?php foreach (["First Term","Second Term","Third Term"] as $t): ?>
              <option value="<?= $t ?>" <?= ($_GET['term'] ?? '') === $t ? 'selected' : '' ?>><?= $t ?></option>
            <?php endforeach; ?>
          </select>
        </div>

        <div class="form-group col-md-3">
          <label>Session</label>
          <select name="session" class="form-control" required>
            <option value="">-- Select Session --</option>
            <?php
            $sess = $connection->query("SELECT DISTINCT sessions FROM sessions");
            while ($s = $sess->fetch_assoc()):
            ?>
              <option value="<?= $s['sessions'] ?>" <?= ($_GET['session'] ?? '') === $s['sessions'] ? 'selected' : '' ?>>
                <?= $s['sessions'] ?>
              </option>
            <?php endwhile; ?>
          </select>
        </div>

        <div class="form-group col-md-3">
          <label>Class</label>
          <select name="class" class="form-control">
            <option value="">-- All Classes --</option>
            <?php
            $classes = $connection->query("SELECT DISTINCT classes FROM classes");
            while ($c = $classes->fetch_assoc()):
            ?>
              <option value="<?= $c['classes'] ?>" <?= ($_GET['class'] ?? '') === $c['classes'] ? 'selected' : '' ?>>
                <?= $c['classes'] ?>
              </option>
            <?php endwhile; ?>
          </select>
        </div>

        <div class="form-group col-md-3">
          <label>&nbsp;</label>
          <button class="btn btn-primary btn-block">View Outstanding</button>
        </div>
      </form>
    </div>
  </div>
</div>

<div class='card'>
  <div class='panel panel-primary'>
    <div class='panel-heading d-flex justify-content-between align-items-center'>
      <h4 class='text-center flex-grow-1'>Outstanding Bills (Students Owing)</h4>
      <button class="btn btn-danger btn-sm" onclick="printDiv('outstandingTable')">
        <i class="fa fa-print"></i> Print PDF
      </button>
    </div>
    <div class='panel-body table-responsive' id="outstandingTable">
      <?php if ($result && $result->num_rows > 0): ?>
        <table class='table table-bordered table-striped' id='myTable'>
          <thead>
            <tr class="text-center">
              <th>S/N</th>
              <th>Reg. Number</th>
              <th>Name</th>
              <th>Class</th>
              <th>Term</th>
              <th>Session</th>
              <th>Amount Paid (₦)</th>
              <th>Outstanding (₦)</th>
              <th>Status</th>
            </tr>
          </thead>
          <tbody class='text-center'>
            <?php 
            $sn = 1;
            $totalOutstanding = 0; // Initialize total outstanding
            while ($row = $result->fetch_assoc()): 
              $balance = max(0, (float)$row['student_balance']);
              $totalOutstanding += $balance; // Add to total
              $statusBadge = $balance > 0 ? 'warning' : 'success';
              $statusText = $balance > 0 ? 'Pending' : 'Paid';
              // Name abbreviation logic
              $name = trim($row['student_name']);
              $parts = preg_split('/\s+/', $name);
              if (count($parts) >= 3) {
                  $displayName = strtoupper($parts[0][0]).'.'.strtoupper($parts[1][0]).'. '.$parts[2];
              } elseif (count($parts) === 2) {
                  $displayName = strtoupper($parts[0][0]).'. '.$parts[1];
              } else {
                  $displayName = $name;
              }
            ?>
              <tr>
                <td><?= $sn++ ?></td>
                <td><?= htmlspecialchars($row['reg_number']) ?></td>
                <td><?= htmlspecialchars($displayName) ?></td>
                <td><?= htmlspecialchars($row['student_class']) ?></td>
                <td><?= htmlspecialchars($row['fee_term']) ?></td>
                <td><?= htmlspecialchars($row['fee_session']) ?></td>
                <td class="text-success">₦<?= number_format($row['total_paid'],2) ?></td>
                <td class="<?= $balance > 1000 ? 'text-danger' : 'text-warning' ?>">
                  <strong>₦<?= number_format($balance,2) ?></strong>
                </td>
                <td><span class="badge badge-<?= $statusBadge ?>"><?= $statusText ?></span></td>
              </tr>
            <?php endwhile; ?>
          </tbody>
          <tfoot>
            <tr class="text-center bg-light">
              <td colspan="7"><b>Total Outstanding (₦)</b></td>
              <td colspan="2" class="text-danger"><b>₦<?= number_format($totalOutstanding,2) ?></b></td>
            </tr>
          </tfoot>
        </table>
      <?php else: ?>
        <div class='alert alert-success text-center'>
          🎉 All students have cleared their fees. No outstanding balances found.
        </div>
      <?php endif; ?>
    </div>
  </div>
</div>

<script>
function printDiv(divId) {
  const content = document.getElementById(divId).innerHTML;
  const myWindow = window.open('', '', 'width=900,height=700');
  myWindow.document.write('<html><head><title>Print PDF</title>');
  myWindow.document.write('<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">');
  myWindow.document.write('</head><body>');
  myWindow.document.write(content);
  myWindow.document.write('</body></html>');
  myWindow.document.close();
  myWindow.focus();
  myWindow.print();
  myWindow.close();
}
</script>