<?php
// =========================
// 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) : '';

// =========================
// Fee Summary Query
// =========================
$sql = "
SELECT 
    fp.student_class AS class,
    fp.fee_term AS term,
    fp.fee_session AS session,
    COUNT(DISTINCT fp.reg_number) AS total_students,
    COALESCE(SUM(fp.total_fee), 0) AS expected_fee,
    COALESCE(SUM(fp.amount_paid), 0) AS total_paid
FROM fee_payment fp
$whereClause
GROUP BY fp.student_class, fp.fee_term, fp.fee_session
ORDER BY fp.student_class, fp.fee_term, fp.fee_session
";

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

// Totals
$totalExpected = 0;
$totalPaid = 0;
$totalBalance = 0;

$modals = '';
?>

<div class="page-title">
  <h1><i class="fa fa-money"></i> Fee Summary Report</h1>
  <p>Expected vs Paid (Based on Recorded Payments)</p>
</div>

<!-- FILTER -->
<div class="card">
  <div class="panel panel-primary">
    <div class="panel-heading text-center"><b>Filter Summary</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 Summary</button>
        </div>
      </form>
    </div>
  </div>
</div>

<br>

<!-- =========================
     Fee Summary Table
     ========================= -->
<div class="card">
  <div class="panel panel-primary">
    <div class="panel-heading text-center d-flex justify-content-between align-items-center">
      <b>Fee Summary</b>
      <button class="btn btn-danger btn-sm" onclick="printDiv('feeSummaryWrapper')">
        <i class="fa fa-print"></i> Print PDF
      </button>
    </div>
    <div class="panel-body table-responsive" id="feeSummaryWrapper">
      <table class="table table-bordered table-striped">
        <thead>
          <tr class="text-center">
            <th>Class</th>
            <th>Term</th>
            <th>Session</th>
            <th>Expected (₦)</th>
            <th>Paid (₦)</th>
            <th>Balance (₦)</th>
            <th>Details</th>
          </tr>
        </thead>
        <tbody>
        <?php
        while ($row = $result->fetch_assoc()):
            $balance = max(0, $row['expected_fee'] - $row['total_paid']);
            $totalExpected += $row['expected_fee'];
            $totalPaid += $row['total_paid'];
            $totalBalance += $balance;
            $modalId = md5($row['class'].$row['term'].$row['session']);
        ?>
          <tr class="text-center">
            <td><?= $row['class'] ?></td>
            <td><?= $row['term'] ?></td>
            <td><?= $row['session'] ?></td>
            <td>₦<?= number_format($row['expected_fee'],2) ?></td>
            <td class="text-success">₦<?= number_format($row['total_paid'],2) ?></td>
            <td class="<?= $balance>0?'text-danger':'text-success' ?>">₦<?= number_format($balance,2) ?></td>
            <td>
              <button class="btn btn-info btn-sm" data-toggle="modal" data-target="#<?= $modalId ?>">View</button>
            </td>
          </tr>

<?php
ob_start();
?>
<div class="modal fade" id="<?= $modalId ?>">
  <div class="modal-dialog modal-lg">
    <div class="modal-content">
      <div class="modal-header bg-primary text-white">
        <h5><?= "{$row['class']} | {$row['term']} | {$row['session']}" ?></h5>
        <button class="close text-white" data-dismiss="modal">&times;</button>
      </div>
      <div class="modal-body table-responsive">
        <table class="table table-bordered table-sm">
          <thead>
            <tr>
              <th>Reg No</th>
              <th>Name</th>
              <th>Total Fee (₦)</th>
              <th>Paid (₦)</th>
              <th>Balance (₦)</th>
              <th>Payment Type</th>
              <th>Status</th>
            </tr>
          </thead>
          <tbody>
<?php
$std = $connection->prepare("
SELECT reg_number, student_name, total_fee, amount_paid, family_id
FROM fee_payment
WHERE student_class=? AND fee_term=? AND fee_session=?
");
$std->bind_param("sss",$row['class'],$row['term'],$row['session']);
$std->execute();
$students = $std->get_result();

while($s = $students->fetch_assoc()):
    $s_balance = max(0, $s['total_fee']-$s['amount_paid']);
    $statusBadge = $s_balance>0?'warning':'success';
    $statusText = $s_balance>0?'Pending':'Paid';
    $ptypeName = $s['family_id'] ? 'Family' : 'Individual';
    $ptypeColor = $s['family_id'] ? 'badge-primary' : 'badge-success';
?>
<tr>
  <td><?= $s['reg_number'] ?></td>
  <td><?= $s['student_name'] ?></td>
  <td>₦<?= number_format($s['total_fee'],2) ?></td>
  <td>₦<?= number_format($s['amount_paid'],2) ?></td>
  <td class="<?= $s_balance>0?'text-danger':'text-success' ?>">₦<?= number_format($s_balance,2) ?></td>
  <td><span class="badge <?= $ptypeColor ?>"><?= $ptypeName ?></span></td>
  <td><span class="badge badge-<?= $statusBadge ?>"><?= $statusText ?></span></td>
</tr>
<?php endwhile; ?>
          </tbody>
        </table>
      </div>
      <div class="modal-footer">
        <button class="btn btn-secondary" data-dismiss="modal">Close</button>
      </div>
    </div>
  </div>
</div>
<?php
$modals .= ob_get_clean();
endwhile;
?>
        </tbody>
        <tfoot class="bg-light text-center">
          <tr>
            <td colspan="3"><b>Totals</b></td>
            <td><b>₦<?= number_format($totalExpected,2) ?></b></td>
            <td class="text-success"><b>₦<?= number_format($totalPaid,2) ?></b></td>
            <td class="text-danger"><b>₦<?= number_format($totalBalance,2) ?></b></td>
            <td></td>
          </tr>
        </tfoot>
      </table>
      <?= $modals ?>
    </div>
  </div>
</div>

<script>
function printDiv(divId){
    var content = document.getElementById(divId).innerHTML;
    var mywindow = window.open('', 'Print', 'height=600,width=1000');
    mywindow.document.write('<html><head><title>Fee Summary</title>');
    mywindow.document.write('<link rel="stylesheet" href="path-to-bootstrap.css">'); // replace with your bootstrap path
    mywindow.document.write('</head><body>');
    mywindow.document.write(content); // includes all modals
    mywindow.document.write('</body></html>');
    mywindow.document.close();
    mywindow.print();
}
</script>