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

// only local helper that doesn't collide with global h()
function valid_date($d){ return (bool)preg_match('/^\d{4}-\d{2}-\d{2}$/', $d); }

/* -----------------------------
   Filters (GET)
------------------------------*/
$q           = trim($_GET['q']            ?? '');
$course_id   = (int)($_GET['course_id']   ?? 0);
$fee_type    = $_GET['fee_type']          ?? '';
$status      = $_GET['status']            ?? '';
$from_date   = $_GET['from_date']         ?? '';
$to_date     = $_GET['to_date']           ?? '';
$paid_from   = $_GET['paid_from']         ?? '';
$paid_to     = $_GET['paid_to']           ?? '';
$min_balance = (int)($_GET['min_balance'] ?? 0);
$export      = (int)($_GET['export']      ?? 0);

if (!in_array($fee_type, ['', 'PER_COURSE','PER_MONTH'], true)) $fee_type='';
if (!in_array($status,   ['', 'due','cleared'], true))          $status='';
if ($from_date && !valid_date($from_date)) $from_date='';
if ($to_date   && !valid_date($to_date))   $to_date='';
if ($paid_from && !valid_date($paid_from)) $paid_from='';
if ($paid_to   && !valid_date($paid_to))   $paid_to='';

/* -----------------------------
   Dropdown data
------------------------------*/
$courses_rs = mysqli_query($conn, "SELECT id,name FROM courses ORDER BY name");
$courses_list = [];
while($c = mysqli_fetch_assoc($courses_rs)) $courses_list[] = $c;

/* -----------------------------
   Core query
------------------------------*/
$where = " WHERE 1=1 ";
if ($q !== '') {
  $q_esc = mysqli_real_escape_string($conn, $q);
  $where .= " AND (r.reg_no LIKE '%$q_esc%' OR s.full_name LIKE '%$q_esc%' OR c.name LIKE '%$q_esc%') ";
}
if ($course_id > 0) $where .= " AND r.course_id = $course_id ";
if ($fee_type !== '') $where .= " AND c.fee_type = '".mysqli_real_escape_string($conn,$fee_type)."' ";
if ($from_date !== '') $where .= " AND DATE(r.approved_at) >= '".mysqli_real_escape_string($conn,$from_date)."' ";
if ($to_date   !== '') $where .= " AND DATE(r.approved_at) <= '".mysqli_real_escape_string($conn,$to_date)."' ";

$sql = "
SELECT
  r.id AS reg_id,
  r.reg_no,
  r.approved_at,
  s.full_name,
  c.name AS course_name,
  c.duration_months,
  c.fee_type,
  c.course_fee,
  c.monthly_fee,
  COALESCE(SUM(p.amount),0) AS paid_total,
  MAX(p.paid_at) AS last_paid_at
FROM registrations r
JOIN students s ON s.id = r.student_id
JOIN courses  c ON c.id = r.course_id
LEFT JOIN payments p ON p.registration_id = r.id
$where
GROUP BY r.id
";

$having = [];
if ($paid_from !== '' || $paid_to !== '') {
  $cond = [];
  if ($paid_from !== '') $cond[] = " (MAX(p.paid_at) >= '".mysqli_real_escape_string($conn,$paid_from)." 00:00:00') ";
  if ($paid_to   !== '') $cond[] = " (MAX(p.paid_at) <= '".mysqli_real_escape_string($conn,$paid_to)." 23:59:59') ";
  if ($cond) $having[] = implode(' AND ', $cond);
}
if ($having) $sql .= " HAVING ".implode(' AND ', $having);

$sql .= " ORDER BY r.approved_at DESC LIMIT 2000";

$rs = mysqli_query($conn, $sql);
if(!$rs){ die("Query failed: ".mysqli_error($conn)); }

/* -----------------------------
   Build rows + totals
------------------------------*/
$rows=[]; $totals=['count'=>0,'total_due_all'=>0,'paid_all'=>0,'balance_all'=>0,'cleared'=>0,'due'=>0];

while($row = mysqli_fetch_assoc($rs)) {
  if ($row['fee_type']==='PER_MONTH') {
    $total = (int)$row['monthly_fee'] * (int)$row['duration_months'];
    $model = 'Per Month';
  } else {
    $total = (int)$row['course_fee'];
    $model = 'Per Course';
  }
  $paid = (int)$row['paid_total'];
  $bal  = max(0, $total - $paid);

  $ok = true;
  if ($status==='due'     && $bal<=0) $ok=false;
  if ($status==='cleared' && $bal>0 ) $ok=false;
  if ($min_balance>0 && $bal<$min_balance) $ok=false;
  if (!$ok) continue;

  $rows[] = [
    'reg_id'=>(int)$row['reg_id'],
    'reg_no'=>$row['reg_no'],
    'approved_at'=>$row['approved_at'],
    'student'=>$row['full_name'],
    'course'=>$row['course_name'],
    'duration'=>(int)$row['duration_months'],
    'model'=>$model,
    'total'=>$total,
    'paid'=>$paid,
    'balance'=>$bal,
    'last_paid_at'=>$row['last_paid_at']
  ];

  $totals['count']++;
  $totals['total_due_all'] += $total;
  $totals['paid_all']      += $paid;
  $totals['balance_all']   += $bal;
  if ($bal>0) $totals['due']++; else $totals['cleared']++;
}

/* -----------------------------
   CSV export
------------------------------*/
if ($export===1){
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename=fees_report.csv');
  echo "\xEF\xBB\xBF";
  $out=fopen('php://output','w');
  fputcsv($out, ['Reg No','Student','Course','Model','Duration (mo)','Total','Paid','Balance','Approved At','Last Paid At']);
  foreach($rows as $r){
    fputcsv($out, [$r['reg_no'],$r['student'],$r['course'],$r['model'],$r['duration'],$r['total'],$r['paid'],$r['balance'],$r['approved_at'],$r['last_paid_at']]);
  }
  fputcsv($out, []);
  fputcsv($out, ['TOTALS','','','',$totals['count'],$totals['total_due_all'],$totals['paid_all'],$totals['balance_all'],'','']);
  fclose($out); exit;
}

/* -----------------------------
   UI
------------------------------*/
admin_header('Fees Report');
?>

<!-- SCOPED CSS to avoid clashes -->
<style>
  #feesReport * { box-sizing: border-box; }
  #feesReport .kpi .card { background:#fff; }
  #feesReport .kpi .card div.label{font-size:12px;color:#475569}
  #feesReport .kpi .card div.value{font-size:28px;font-weight:800;color:#0f172a}

  #feesReport .filters input,
  #feesReport .filters select{
    height:38px; padding:8px 10px;
  }

  #feesReport .table-wrap{
    overflow:auto;
    border-radius:10px;
    border:1px solid var(--border, #e5e7eb);
  }

  #feesReport table{
    width:100%;
    border-collapse:separate;
    border-spacing:0;
    min-width: 980px;
  }
  #feesReport th, #feesReport td{
    padding:10px 12px;
    border-bottom:1px solid #e5e7eb;
    vertical-align:middle;
    white-space:nowrap;
  }
  #feesReport thead th{
    position: sticky; top:0; z-index: 1;
    background:#f1f5f9;
    font-weight:700;
    color:#0f172a;
  }
  #feesReport tbody tr:hover{ background:#f8fafc; }

  #feesReport .badge{
    padding:2px 8px; border-radius:999px; font-size:12px; font-weight:700;
    display:inline-block; border:1px solid transparent;
  }
  #feesReport .badge.due{ background:#fee2e2; color:#991b1b; border-color:#fecaca; }
  #feesReport .badge.ok { background:#dcfce7; color:#166534; border-color:#86efac; }

  /* responsive: filters grid stacks nicely */
  @media (max-width: 900px){
    #feesReport .col-12, #feesReport .col-6 { grid-column: span 12 !important; }
    #feesReport table{ min-width: 760px; }
  }
</style>

<div id="feesReport">
  <h2 style="display:flex;align-items:center;gap:10px;margin:0 0 8px">
    Fees Report (Advanced)
    <span class="badge ok" style="border-color:#bfdbfe;background:#dbeafe;color:#1e40af">Full ledger summary</span>
  </h2>

  <!-- Filters -->
  <div class="card col-12 filters" style="margin-top:12px">
    <form method="get" style="display:grid;gap:10px">
      <div class="grid" style="grid-template-columns:repeat(12,1fr);gap:10px">
        <div style="grid-column:span 4">
          <label>Search</label>
          <input name="q" value="<?=h($q)?>" placeholder="Reg No / Student / Course">
        </div>
        <div style="grid-column:span 4">
          <label>Course</label>
          <select name="course_id">
            <option value="0">— Any —</option>
            <?php foreach($courses_list as $c): ?>
              <option value="<?=$c['id']?>" <?=$course_id==$c['id']?'selected':''?>><?=h($c['name'])?></option>
            <?php endforeach; ?>
          </select>
        </div>
        <div style="grid-column:span 4">
          <label>Fee Type</label>
          <select name="fee_type">
            <option value="">— Any —</option>
            <option value="PER_COURSE" <?=$fee_type==='PER_COURSE'?'selected':''?>>Per Course</option>
            <option value="PER_MONTH"  <?=$fee_type==='PER_MONTH'?'selected':''?>>Per Month</option>
          </select>
        </div>
      </div>

      <div class="grid" style="grid-template-columns:repeat(12,1fr);gap:10px">
        <div style="grid-column:span 3">
          <label>Approved From</label>
          <input type="date" name="from_date" value="<?=h($from_date)?>">
        </div>
        <div style="grid-column:span 3">
          <label>Approved To</label>
          <input type="date" name="to_date" value="<?=h($to_date)?>">
        </div>
        <div style="grid-column:span 3">
          <label>Last Payment From</label>
          <input type="date" name="paid_from" value="<?=h($paid_from)?>">
        </div>
        <div style="grid-column:span 3">
          <label>Last Payment To</label>
          <input type="date" name="paid_to" value="<?=h($paid_to)?>">
        </div>
      </div>

      <div class="grid" style="grid-template-columns:repeat(12,1fr);gap:10px">
        <div style="grid-column:span 3">
          <label>Status</label>
          <select name="status">
            <option value="">— Any —</option>
            <option value="due" <?=$status==='due'?'selected':''?>>Due</option>
            <option value="cleared" <?=$status==='cleared'?'selected':''?>>Cleared</option>
          </select>
        </div>
        <div style="grid-column:span 3">
          <label>Min Balance (PKR)</label>
          <input type="number" name="min_balance" value="<?=$min_balance?>" min="0" step="100">
        </div>
        <div style="grid-column:span 6; display:flex;gap:8px;align-items:flex-end;justify-content:flex-end">
          <button class="btn">Apply</button>
          <a class="btn" href="fees_report.php">Reset</a>
          <a class="btn primary" href="fees_report.php?<?=h(http_build_query(array_merge($_GET,['export'=>1])))?>">Export CSV</a>
        </div>
      </div>
    </form>
  </div>

  <!-- KPIs -->
  <div class="grid kpi col-12 mt-3" style="grid-template-columns:repeat(12,1fr);gap:10px">
    <div class="card" style="grid-column:span 3">
      <div class="label">Registrations</div>
      <div class="value"><?=number_format($totals['count'])?></div>
    </div>
    <div class="card" style="grid-column:span 3">
      <div class="label">Total Amount (All)</div>
      <div class="value"><?=number_format($totals['total_due_all'])?></div>
    </div>
    <div class="card" style="grid-column:span 3">
      <div class="label">Total Paid</div>
      <div class="value"><?=number_format($totals['paid_all'])?></div>
    </div>
    <div class="card" style="grid-column:span 3">
      <div class="label">Total Balance</div>
      <div class="value"><?=number_format($totals['balance_all'])?></div>
    </div>
  </div>

  <!-- Table -->
  <div class="card col-12 mt-3">
    <div class="table-wrap">
      <table>
        <thead>
          <tr>
            <th>Reg No</th>
            <th style="text-align:left">Student</th>
            <th style="text-align:left">Course</th>
            <th>Model</th>
            <th>Dur (mo)</th>
            <th style="text-align:right">Total</th>
            <th style="text-align:right">Paid</th>
            <th style="text-align:right">Balance</th>
            <th>Approved</th>
            <th>Last Payment</th>
            <th>Flag</th>
          </tr>
        </thead>
        <tbody>
          <?php if(empty($rows)): ?>
            <tr><td colspan="11" style="text-align:center;padding:24px;color:#64748b">No results found.</td></tr>
          <?php else: foreach($rows as $r): ?>
            <tr>
              <td style="text-align:center;font-weight:700"><?=h($r['reg_no'])?></td>
              <td><?=h($r['student'])?></td>
              <td><?=h($r['course'])?></td>
              <td style="text-align:center"><?=h($r['model'])?></td>
              <td style="text-align:center"><?= (int)$r['duration'] ?></td>
              <td style="text-align:right"><?= number_format($r['total']) ?></td>
              <td style="text-align:right"><?= number_format($r['paid']) ?></td>
              <td style="text-align:right;font-weight:700"><?= number_format($r['balance']) ?></td>
              <td style="text-align:center"><?=h(substr($r['approved_at'],0,10))?></td>
              <td style="text-align:center"><?=h($r['last_paid_at'] ? substr($r['last_paid_at'],0,16) : '-')?></td>
              <td style="text-align:center">
                <?php if($r['balance']>0): ?>
                  <span class="badge due">Due</span>
                <?php else: ?>
                  <span class="badge ok">Cleared</span>
                <?php endif; ?>
              </td>
            </tr>
          <?php endforeach; endif; ?>
        </tbody>

        <?php if(!empty($rows)): ?>
        <tfoot>
          <tr>
            <th colspan="5" style="text-align:right">TOTALS</th>
            <th style="text-align:right"><?=number_format($totals['total_due_all'])?></th>
            <th style="text-align:right"><?=number_format($totals['paid_all'])?></th>
            <th style="text-align:right"><?=number_format($totals['balance_all'])?></th>
            <th colspan="3" style="text-align:right">
              <?=$totals['cleared']?> Cleared • <?=$totals['due']?> Due
            </th>
          </tr>
        </tfoot>
        <?php endif; ?>
      </table>
    </div>
    <div style="display:flex;justify-content:flex-end;font-size:12px;color:#64748b;margin-top:8px">
      Tip: Sticky header keeps column titles visible while scrolling.
    </div>
  </div>
</div>

<script>
  // focus search on load
  document.querySelector('#feesReport input[name="q"]')?.focus();
</script>

<?php admin_footer(); ?>
