<?php
$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[] = "s.class = ?";
  $params[] = $_GET['class'];
}
if (!empty($_GET['reg_number'])) {
  $filters[] = "s.reg_number = ?";
  $params[] = $_GET['reg_number'];
}

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

$sql = "
SELECT 
  s.reg_number, s.firstname, s.othername, s.lastname, s.passport, s.class, 
  fp.fee_session, fp.fee_term,
  SUM(fp.amount_paid) AS total_paid, 
  MAX(fp.date_of_upload) AS last_payment_date,
  MAX(fp.teller_no) AS last_teller,
  MAX(fp.payment_status) AS last_status,
  fs.amount AS expected_fee
FROM fee_payment fp
JOIN students s ON s.reg_number = fp.reg_number
JOIN fee_structure fs ON fs.class = s.class AND fs.session = fp.fee_session AND fs.term = fp.fee_term
$whereClause
GROUP BY s.reg_number, fp.fee_session, fp.fee_term
ORDER BY fp.fee_session DESC, fp.fee_term DESC, last_payment_date DESC
";

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

if ($result->num_rows === 0) {
  $_SESSION['msg'] = "No matching payment records found.";
  $_SESSION['type'] = "danger";
}
?>

<style>
  .form-inline .form-control {
    min-width: 180px;
  }
  .passport-img {
    width: 40px;
    height: 40px;
    border-radius: 50%;
    object-fit: cover;
  }
  .table th,
  .table td {
    vertical-align: middle !important;
  }
</style>

<div class="page-title">
  <div>
    <h1><i class="fa fa-money"></i> Fee Payment History</h1>
    <p>Detailed student fee payment logs</p>
  </div>  
</div>

<div class="card">
  <div class="panel panel-primary">
    <div class="panel-heading">
      <h4 class="text-center">Search Payment Records</h4>
    </div>
    <div class="panel-body">
      <form method="GET" class="form-row align-items-end">
        <div class="form-group col-md-3">
          <label>Term</label>
          <select name="term" class="form-control">
            <option value="">-- All Terms --</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">
            <option value="">-- All Sessions --</option>
            <?php
            $sess = $connection->query("SELECT DISTINCT sessions FROM sessions");
            while ($s = $sess->fetch_assoc()):
              $val = $s['sessions'];
            ?>
              <option value="<?= $val ?>" <?= ($_GET['session'] ?? '') == $val ? 'selected' : '' ?>><?= $val ?></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()):
              $val = $c['classes'];
            ?>
              <option value="<?= $val ?>" <?= ($_GET['class'] ?? '') == $val ? 'selected' : '' ?>><?= $val ?></option>
            <?php endwhile; ?>
          </select>
        </div>

        <div class="form-group col-md-3">
          <label>Reg Number</label>
          <input type="text" name="reg_number" value="<?= $_GET['reg_number'] ?? '' ?>" class="form-control" placeholder="e.g. STD12345">
        </div>

        <div class="form-group col-md-12">
          <button type="submit" class="btn btn-primary btn-block">Search History</button>
        </div>
      </form>
    </div>
  </div>
</div>

<br>

<div class="card">
  <div class="panel panel-primary">
    <div class="panel-heading">
      <h4 class="text-center">Payment History Table</h4>
    </div>
    <div class="panel-body table-responsive">
      <table class="table table-bordered table-striped mb-0">
        <thead class="thead-light text-center">
          <tr>
            <!--th>Photo</th-->
            <th>Reg No</th>
            <th>Name</th>
            <th>Class</th>
            <th>Session</th>
            <th>Term</th>
            <th>Expected Fee (₦)</th>
            <th>Total Paid (₦)</th>
            <th>Balance (₦)</th>
            <th>Teller No</th>
            <th>Date</th>
            <th>Status</th>
          </tr>
        </thead>
        <tbody>
          <?php while ($row = $result->fetch_assoc()):
            $photo = $row['passport'] ? "../../{$row['passport']}" : "../../uploads/students/none.jpg";
            $name = $row['firstname'] . ' ' . $row['othername'] . ' ' . $row['lastname'];
            $expected = $row['expected_fee'];
            $paid = $row['total_paid'];
            $balance = $expected - $paid;
          ?>
          <tr class="text-center">
            <!--td><img src="<?= $photo ?>" class="passport-img"></td-->
            <td><?= $row['reg_number'] ?></td>
            <td><?= strlen($name) > 25 ? '<span title="' . htmlspecialchars($name) . '">' . substr($row['firstname'], 0, 1) . '. ' . substr($row['othername'], 0, 1) . '. ' . $row['lastname'] . '</span>' : $name ?></td>
            <td><?= $row['class'] ?></td>
            <td><?= $row['fee_session'] ?></td>
            <td><?= $row['fee_term'] ?></td>
            <td><b>₦<?= number_format($expected, 2) ?></b></td>
            <td class="text-success">₦<?= number_format($paid, 2) ?></td>
            <td class="<?= $balance > 1000 ? 'text-danger' : 'text-success' ?>">₦<?= number_format($balance, 2) ?></td>
            <td><?= $row['last_teller'] ?></td>
            <td><?= $row['last_payment_date'] ?></td>
            <td>
              <?= $row['last_status'] === 'Cleared'
                ? "<span class='badge badge-success'>Cleared</span>"
                : "<span class='badge badge-warning'>Pending</span>" ?>
            </td>
          </tr>
          <?php endwhile; ?>
        </tbody>
      </table>
    </div>
  </div>
</div>
