<?php
require_once __DIR__ . '/_auth.php';
require_once __DIR__ . '/_layout.php';

/* ==========================================================
   Filters
========================================================== */
$course_id = (int)($_GET['course_id'] ?? 0);
$status_in = $_GET['status'] ?? '';
$status = in_array($status_in, ['UNPAID','PARTIAL','PAID'], true) ? $status_in : '';
$q       = trim($_GET['q'] ?? '');

$date_from = $_GET['date_from'] ?? '';
$date_to   = $_GET['date_to'] ?? '';

$df = $date_from !== '' ? date('Y-m-d', strtotime($date_from)) : '';
$dt = $date_to   !== '' ? date('Y-m-d', strtotime($date_to))   : '';

/* ==========================================================
   Lookups
========================================================== */
// Courses (active first)
$courses = mysqli_query($conn, "SELECT id,name FROM courses ORDER BY is_active DESC, name ASC");

/* ==========================================================
   WHERE clauses for "current balances" (fee_payments join)
   NOTE: These reflect the CURRENT state (not date ranged).
========================================================== */
$where = " WHERE 1=1 ";
if ($course_id > 0) {
  $where .= " AND fp.course_id = {$course_id} ";
}
if ($status !== '') {
  $esc = mysqli_real_escape_string($conn, $status);
  $where .= " AND fp.status = '{$esc}' ";
}
if ($q !== '') {
  $qs = mysqli_real_escape_string($conn, $q);
  // search by student name or course
  $where .= " AND (s.full_name LIKE '%{$qs}%' OR c.name LIKE '%{$qs}%') ";
}

/* ==========================================================
   CURRENT KPIs (from fee_payments)
   - Total Invoiced = SUM(total_fee)
   - Cash Received (to date) = SUM(paid_amount)
   - Discount (current) = SUM(discount)
   - Extra charges (current) = SUM(extra_charges)
   - Balance = SUM(total_fee - (paid_amount + discount - extra_charges))
========================================================== */
$kpi_sql = "
SELECT
  COALESCE(SUM(fp.total_fee),0)               AS total_invoiced,
  COALESCE(SUM(fp.paid_amount),0)             AS total_paid,
  COALESCE(SUM(fp.discount),0)                AS total_discount,
  COALESCE(SUM(fp.extra_charges),0)           AS total_extra,
  COALESCE(SUM(fp.total_fee - (fp.paid_amount + fp.discount - fp.extra_charges)),0) AS total_balance
FROM fee_payments fp
JOIN students s ON s.id=fp.student_id
JOIN courses  c ON c.id=fp.course_id
{$where}
";
$kpi_res = mysqli_query($conn, $kpi_sql);
$kpi = mysqli_fetch_assoc($kpi_res);

/* ==========================================================
   PERIOD KPIs (from fee_history) — respect date range + filters
========================================================== */
$ph_where = " WHERE 1=1 ";
if ($df !== '') $ph_where .= " AND fh.paid_date >= '{$df} 00:00:00' ";
if ($dt !== '') $ph_where .= " AND fh.paid_date <= '{$dt} 23:59:59' ";
if ($course_id > 0) $ph_where .= " AND r.course_id = {$course_id} ";
if ($q !== '') {
  $qs = mysqli_real_escape_string($conn, $q);
  $ph_where .= " AND (s.full_name LIKE '%{$qs}%' OR c.name LIKE '%{$qs}%') ";
}

$period_sql = "
SELECT
  COALESCE(SUM(fh.paid_now),0)      AS period_cash,
  COALESCE(SUM(fh.discount),0)      AS period_discount,
  COALESCE(SUM(fh.extra_charges),0) AS period_extra
FROM fee_history fh
JOIN students s ON s.id = fh.student_id
JOIN registrations r ON r.student_id = s.id
JOIN courses c ON c.id = r.course_id
{$ph_where}
";
$period_res = mysqli_query($conn, $period_sql);
$period = mysqli_fetch_assoc($period_res);

/* ==========================================================
   PER-COURSE SUMMARY (current balances)
========================================================== */
$sum_sql = "
SELECT
  c.id AS cid, c.name AS course,
  COUNT(*)                                         AS students,
  COALESCE(SUM(fp.total_fee),0)                    AS total_invoiced,
  COALESCE(SUM(fp.paid_amount),0)                  AS total_paid,
  COALESCE(SUM(fp.discount),0)                     AS total_discount,
  COALESCE(SUM(fp.extra_charges),0)                AS total_extra,
  COALESCE(SUM(fp.total_fee - (fp.paid_amount + fp.discount - fp.extra_charges)),0) AS total_balance
FROM fee_payments fp
JOIN courses c  ON c.id=fp.course_id
JOIN students s ON s.id=fp.student_id
" . ($course_id>0 || $status!=='' || $q!=='' ? $where : " WHERE 1=1 ") . "
GROUP BY c.id, c.name
ORDER BY c.name
";
$sum_res = mysqli_query($conn, $sum_sql);

/* ==========================================================
   Unpaid / Partial students (current)
========================================================== */
$up_sql = "
SELECT 
  fp.id, fp.student_id, fp.course_id, fp.status,
  s.full_name, c.name AS course,
  fp.total_fee, fp.paid_amount, fp.discount, fp.extra_charges,
  (fp.total_fee - (fp.paid_amount + fp.discount - fp.extra_charges)) AS balance
FROM fee_payments fp
JOIN students s ON s.id=fp.student_id
JOIN courses  c ON c.id=fp.course_id
{$where}
AND fp.status IN ('UNPAID','PARTIAL')
ORDER BY balance DESC, s.full_name
";
$up_res = mysqli_query($conn, $up_sql);

/* ==========================================================
   Recent payments (history) — respect date range; else last 50
========================================================== */
$hist_lim = ($df==='' && $dt==='') ? "LIMIT 50" : "";
$hist_sql = "
SELECT fh.paid_date, fh.paid_now, fh.discount, fh.extra_charges, fh.received_by,
       s.full_name, c.name AS course
FROM fee_history fh
JOIN students s     ON s.id = fh.student_id
JOIN registrations r ON r.student_id = s.id
JOIN courses c      ON c.id = r.course_id
{$ph_where}
ORDER BY fh.paid_date DESC
{$hist_lim}
";
$hist_res = mysqli_query($conn, $hist_sql);

/* ==========================================================
   UI
========================================================== */
admin_header('Fees Overview');
?>

<h2 style="display:flex;align-items:center;gap:10px;margin:0 0 8px;color:#2563eb">
  Fees Overview
  <span style="font-size:12px;font-weight:600;color:#1e40af;background:#dbeafe;border:1px solid #bfdbfe;border-radius:999px;padding:2px 8px">
    Snapshot & Analytics
  </span>
</h2>

<!-- Filters -->
<div class="card mt-3">
  <form method="get" class="grid" style="grid-template-columns:repeat(12,1fr);gap:10px">
    <div class="col-3">
      <label>Course</label>
      <select name="course_id">
        <option value="0">All Courses</option>
        <?php while($c = mysqli_fetch_assoc($courses)): ?>
          <option value="<?=$c['id']?>" <?=$course_id==$c['id']?'selected':''?>><?=h($c['name'])?></option>
        <?php endwhile; ?>
      </select>
    </div>
    <div class="col-2">
      <label>Status (Current)</label>
      <select name="status">
        <option value="">All</option>
        <option <?=$status==='UNPAID'?'selected':''?>>UNPAID</option>
        <option <?=$status==='PARTIAL'?'selected':''?>>PARTIAL</option>
        <option <?=$status==='PAID'?'selected':''?>>PAID</option>
      </select>
    </div>
    <div class="col-2">
      <label>Date From (Period)</label>
      <input type="date" name="date_from" value="<?=h($df)?>">
    </div>
    <div class="col-2">
      <label>Date To (Period)</label>
      <input type="date" name="date_to" value="<?=h($dt)?>">
    </div>
    <div class="col-2">
      <label>Search</label>
      <input name="q" value="<?=h($q)?>" placeholder="Student or course">
    </div>
    <div class="col-1" style="display:flex;align-items:flex-end">
      <button class="btn primary" style="width:100%">Apply</button>
    </div>
    <div class="col-1" style="display:flex;align-items:flex-end">
      <a class="btn" href="fees_overview.php" style="width:100%">Reset</a>
    </div>
  </form>
</div>

<!-- KPI Cards -->
<div class="grid mt-3">
  <div class="card col-3">
    <div style="font-size:12px;color:#64748b">Total Invoiced (Current)</div>
    <div style="font-size:24px;font-weight:800;margin-top:6px"><?=number_format((float)$kpi['total_invoiced'])?></div>
  </div>
  <div class="card col-3">
    <div style="font-size:12px;color:#64748b">Cash Received (to date)</div>
    <div style="font-size:24px;font-weight:800;margin-top:6px"><?=number_format((float)$kpi['total_paid'])?></div>
  </div>
  <div class="card col-3">
    <div style="font-size:12px;color:#64748b">Discount (current)</div>
    <div style="font-size:24px;font-weight:800;margin-top:6px"><?=number_format((float)$kpi['total_discount'])?></div>
  </div>
  <div class="card col-3">
    <div style="font-size:12px;color:#64748b">Balance (due)</div>
    <div style="font-size:24px;font-weight:800;margin-top:6px;color:#dc2626"><?=number_format((float)$kpi['total_balance'])?></div>
  </div>
</div>

<!-- Period KPIs -->
<div class="grid mt-3">
  <div class="card col-4">
    <div style="font-size:12px;color:#64748b">Cash Received (Period)</div>
    <div style="font-size:22px;font-weight:800;margin-top:6px"><?=number_format((float)$period['period_cash'])?></div>
  </div>
  <div class="card col-4">
    <div style="font-size:12px;color:#64748b">Discount Given (Period)</div>
    <div style="font-size:22px;font-weight:800;margin-top:6px"><?=number_format((float)$period['period_discount'])?></div>
  </div>
  <div class="card col-4">
    <div style="font-size:12px;color:#64748b">Extra Charges (Period)</div>
    <div style="font-size:22px;font-weight:800;margin-top:6px"><?=number_format((float)$period['period_extra'])?></div>
  </div>
</div>

<!-- Per-course summary -->
<div class="card mt-3">
  <h3 style="margin:0 0 10px;color:#2563eb">Per-Course Summary (Current)</h3>
  <div class="table-wrap">
    <table>
      <thead>
        <tr>
          <th>Course</th>
          <th style="text-align:center">Students</th>
          <th style="text-align:right">Invoiced</th>
          <th style="text-align:right">Paid</th>
          <th style="text-align:right">Discount</th>
          <th style="text-align:right">Extra</th>
          <th style="text-align:right">Balance</th>
        </tr>
      </thead>
      <tbody>
        <?php
        $hasCourse=false;
        while($r=mysqli_fetch_assoc($sum_res)):
          $hasCourse=true; ?>
          <tr>
            <td><?=h($r['course'])?></td>
            <td style="text-align:center"><?= (int)$r['students'] ?></td>
            <td style="text-align:right"><?= number_format((float)$r['total_invoiced']) ?></td>
            <td style="text-align:right"><?= number_format((float)$r['total_paid']) ?></td>
            <td style="text-align:right"><?= number_format((float)$r['total_discount']) ?></td>
            <td style="text-align:right"><?= number_format((float)$r['total_extra']) ?></td>
            <td style="text-align:right;<?= ((float)$r['total_balance']>0?'color:#dc2626;font-weight:700':'') ?>">
              <?= number_format((float)$r['total_balance']) ?>
            </td>
          </tr>
        <?php endwhile; ?>
        <?php if(!$hasCourse): ?>
          <tr><td colspan="7" style="text-align:center;color:#64748b;padding:16px">No data for current filters.</td></tr>
        <?php endif; ?>
      </tbody>
    </table>
  </div>
</div>

<!-- Unpaid / Partial -->
<div class="card mt-3">
  <h3 style="margin:0 0 10px;color:#dc2626">Unpaid / Partial Students (Current)</h3>
  <div class="table-wrap">
    <table>
      <thead>
        <tr>
          <th>Student</th>
          <th>Course</th>
          <th style="text-align:right">Total</th>
          <th style="text-align:right">Paid</th>
          <th style="text-align:right">Discount</th>
          <th style="text-align:right">Extra</th>
          <th style="text-align:right">Balance</th>
          <th style="text-align:center">Status</th>
        </tr>
      </thead>
      <tbody>
        <?php
        $hasUp=false;
        while($u=mysqli_fetch_assoc($up_res)):
          $hasUp=true;
          $badge = $u['status']==='PARTIAL'
            ? '<span class="badge warn">PARTIAL</span>'
            : '<span class="badge danger">UNPAID</span>';
        ?>
          <tr>
            <td><?=h($u['full_name'])?></td>
            <td><?=h($u['course'])?></td>
            <td style="text-align:right"><?=number_format((float)$u['total_fee'])?></td>
            <td style="text-align:right"><?=number_format((float)$u['paid_amount'])?></td>
            <td style="text-align:right"><?=number_format((float)$u['discount'])?></td>
            <td style="text-align:right"><?=number_format((float)$u['extra_charges'])?></td>
            <td style="text-align:right;color:#dc2626;font-weight:700"><?=number_format((float)$u['balance'])?></td>
            <td style="text-align:center"><?=$badge?></td>
          </tr>
        <?php endwhile; ?>
        <?php if(!$hasUp): ?>
          <tr><td colspan="8" style="text-align:center;color:#64748b;padding:16px">All clear — no unpaid/partial matches for current filters.</td></tr>
        <?php endif; ?>
      </tbody>
    </table>
  </div>
</div>

<!-- Recent Payments / Period Payments -->
<div class="card mt-3">
  <h3 style="margin:0 0 10px;color:#2563eb">
    <?= ($df||$dt) ? 'Payments in Selected Period' : 'Recent Payments' ?>
  </h3>
  <div class="table-wrap">
    <table>
      <thead>
        <tr>
          <th>Date</th>
          <th>Student</th>
          <th>Course</th>
          <th style="text-align:right">Paid</th>
          <th style="text-align:right">Discount</th>
          <th style="text-align:right">Extra</th>
          <th style="text-align:center">Received By</th>
        </tr>
      </thead>
      <tbody>
        <?php
        $hasHist=false;
        while($h=mysqli_fetch_assoc($hist_res)):
          $hasHist=true; ?>
          <tr>
            <td><?=h($h['paid_date'])?></td>
            <td><?=h($h['full_name'])?></td>
            <td><?=h($h['course'])?></td>
            <td style="text-align:right"><?=number_format((float)$h['paid_now'])?></td>
            <td style="text-align:right"><?=number_format((float)$h['discount'])?></td>
            <td style="text-align:right"><?=number_format((float)$h['extra_charges'])?></td>
            <td style="text-align:center"><?=h($h['received_by'])?></td>
          </tr>
        <?php endwhile; ?>
        <?php if(!$hasHist): ?>
          <tr><td colspan="7" style="text-align:center;color:#64748b;padding:16px">No payments found.</td></tr>
        <?php endif; ?>
      </tbody>
    </table>
  </div>
  <div style="font-size:12px;color:#64748b;margin-top:8px">
    Tip: Set a date range above to see period totals and filter this table.
  </div>
</div>

<?php admin_footer(); ?>
