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

/* ---------------- KPIs ---------------- */
$kpi = [
  'pending'  => (int)mysqli_fetch_assoc(mysqli_query($conn, "SELECT COUNT(*) c FROM admission_requests WHERE status='PENDING'"))['c'],
  'students' => (int)mysqli_fetch_assoc(mysqli_query($conn, "SELECT COUNT(*) c FROM students"))['c'],
  'batches'  => (int)mysqli_fetch_assoc(mysqli_query($conn, "SELECT COUNT(*) c FROM batches WHERE status IN ('Scheduled','Ongoing')"))['c'],
  'regs'     => (int)mysqli_fetch_assoc(mysqli_query($conn, "SELECT COUNT(*) c FROM registrations"))['c'],
  'courses'  => (int)mysqli_fetch_assoc(mysqli_query($conn, "SELECT COUNT(*) c FROM courses WHERE is_active=1"))['c'],
];

/* ---------------- Fee summary (schema-safe) ---------------- */
$totSql = "
  SELECT
    SUM(IFNULL(paid_amount,0)) AS collected,
    SUM(
      GREATEST(
        0,
        IFNULL(total_fee,0) + IFNULL(extra_charges,0) - IFNULL(discount,0) - IFNULL(paid_amount,0)
      )
    ) AS pending,
    SUM(CASE WHEN status='PARTIAL' THEN IFNULL(paid_amount,0) ELSE 0 END) AS partial_paid
  FROM fee_payments
";
$totals = mysqli_fetch_assoc(mysqli_query($conn, $totSql));
$total_income  = (float)($totals['collected'] ?? 0);
$total_pending = (float)($totals['pending'] ?? 0);
$total_partial = (float)($totals['partial_paid'] ?? 0);

/* ---------------- Recent fee history (uses updated_at) ---------------- */
$fee_history = mysqli_query($conn, "
  SELECT f.id, s.full_name, c.name AS course,
         IFNULL(f.total_fee,0) AS total_fee,
         IFNULL(f.paid_amount,0) AS paid_amount,
         IFNULL(f.discount,0) AS discount,
         IFNULL(f.extra_charges,0) AS extra_charges,
         f.status, f.updated_at
  FROM fee_payments f
  JOIN students s ON s.id=f.student_id
  JOIN courses  c ON c.id=f.course_id
  ORDER BY f.updated_at DESC
  LIMIT 10
");

/* ---------------- Monthly collected (last 6 months) ---------------- */
$monthRows = mysqli_query($conn, "
  SELECT DATE_FORMAT(updated_at, '%Y-%m') ym,
         SUM(IFNULL(paid_amount,0)) total
  FROM fee_payments
  WHERE updated_at IS NOT NULL
    AND updated_at >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
  GROUP BY ym
  ORDER BY ym
");
$collectedByMonth = [];
while ($m = mysqli_fetch_assoc($monthRows)) {
  $collectedByMonth[$m['ym']] = (float)$m['total'];
}
$labels = [];
$data   = [];
// build last 6 months (including current)
for ($i = 5; $i >= 0; $i--) {
  $ym = date('Y-m', strtotime("-$i months"));
  $labels[] = date('M y', strtotime("$ym-01"));
  $data[]   = isset($collectedByMonth[$ym]) ? $collectedByMonth[$ym] : 0;
}

/* ---------------- Latest items ---------------- */
$latestRequests = mysqli_query($conn, "
  SELECT id, full_name, requested_course, preferred_timing, created_at
  FROM admission_requests
  ORDER BY created_at DESC
  LIMIT 6
");

$latestStudents = mysqli_query($conn, "
  SELECT id, full_name, email, created_at
  FROM students
  ORDER BY created_at DESC
  LIMIT 6
");

$latestBatches  = mysqli_query($conn, "
  SELECT id, name, start_date, status
  FROM batches
  ORDER BY start_date DESC, id DESC
  LIMIT 6
");

admin_header('Dashboard');
?>
<style>
  h2{margin:0 0 10px;color:#1e3a8a}
  .grid{display:grid;gap:12px}
  .kpis{grid-template-columns:repeat(auto-fit,minmax(180px,1fr))}
  .card{background:#fff;border:1px solid var(--border);border-radius:12px;padding:16px;box-shadow:var(--shadow)}
  .card h3{margin:0 0 8px;color:#1e40af}
  .val{font-size:28px;font-weight:800;color:#0f172a}
  .lbl{font-size:12px;color:#64748b;text-transform:uppercase}
  table{width:100%;border-collapse:collapse}
  th,td{padding:9px 10px;border-bottom:1px solid #e5e7eb;text-align:left;vertical-align:middle}
  thead th{background:#f1f5f9;font-size:13px}
  .muted{color:#64748b;font-size:12px}
  .section{background:#fff;border:1px solid #e5e7eb;border-radius:12px;padding:14px;box-shadow:var(--shadow)}
  .grid2{display:grid;grid-template-columns:repeat(auto-fit,minmax(420px,1fr));gap:12px}
  .chart{height:280px;margin-top:6px}
  .badge{padding:2px 7px;border-radius:999px;border:1px solid #e5e7eb;font-size:11px}
  .kpi-ok {background:#dcfce7;border-color:#86efac;color:#166534}
  .kpi-warn{background:#fff7ed;border-color:#fed7aa;color:#9a3412}
  .kpi-info{background:#eff6ff;border-color:#bfdbfe;color:#1e40af}
  .right{ text-align:right }
</style>

<h2>Academy Dashboard</h2>

<!-- KPI CARDS -->
<div class="grid kpis">
  <div class="card"><div class="lbl">Pending Requests</div><div class="val"><?=$kpi['pending']?></div></div>
  <div class="card"><div class="lbl">Total Students</div><div class="val"><?=$kpi['students']?></div></div>
  <div class="card"><div class="lbl">Active Batches</div><div class="val"><?=$kpi['batches']?></div></div>
  <div class="card"><div class="lbl">Registrations</div><div class="val"><?=$kpi['regs']?></div></div>
  <div class="card"><div class="lbl">Active Courses</div><div class="val"><?=$kpi['courses']?></div></div>
</div>

<!-- FEE REPORT -->
<div class="section mt-3">
  <h3>💰 Fee Report Overview</h3>
  <div class="grid2">
    <div class="grid" style="grid-template-columns:repeat(12,1fr)">
      <div class="card kpi-info"  style="grid-column:span 12">
        <div class="lbl">Total Income (collected)</div>
        <div class="val">PKR <?=number_format($total_income)?></div>
      </div>
      <div class="card kpi-warn" style="grid-column:span 6">
        <div class="lbl">Pending (overall balance)</div>
        <div class="val">PKR <?=number_format($total_pending)?></div>
      </div>
      <div class="card" style="grid-column:span 6;background:#f1f5f9;border-color:#e2e8f0">
        <div class="lbl">Partial Paid (sum of paid in partial rows)</div>
        <div class="val">PKR <?=number_format($total_partial)?></div>
      </div>
    </div>

    <div class="card">
      <div class="lbl">Collected by Month (last 6 months)</div>
      <canvas id="feeChart" class="chart"></canvas>
    </div>
  </div>
</div>

<!-- Recent Fee History -->
<div class="section mt-3">
  <h3>Recent Fee History</h3>
  <table>
    <thead>
      <tr>
        <th>#</th><th>Student</th><th>Course</th>
        <th class="right">Net Payable</th>
        <th class="right">Paid</th>
        <th class="right">Balance</th>
        <th>Status</th><th>Updated</th>
      </tr>
    </thead>
    <tbody>
    <?php if($fee_history && mysqli_num_rows($fee_history)>0): ?>
      <?php while($f=mysqli_fetch_assoc($fee_history)):
        $net = (float)$f['total_fee'] + (float)$f['extra_charges'] - (float)$f['discount'];
        if ($net < 0) $net = 0;
        $paid = (float)$f['paid_amount'];
        $bal  = max(0, $net - $paid);
        $badgeBg = ($f['status']=='PAID' ? '#dcfce7' : ($f['status']=='PARTIAL' ? '#fff7ed' : '#fee2e2'));
      ?>
        <tr>
          <td><?=$f['id']?></td>
          <td><?=h($f['full_name'])?></td>
          <td><?=h($f['course'])?></td>
          <td class="right">PKR <?=number_format($net)?></td>
          <td class="right">PKR <?=number_format($paid)?></td>
          <td class="right">PKR <?=number_format($bal)?></td>
          <td><span class="badge" style="background: <?=$badgeBg?>"><?=$f['status']?></span></td>
          <td><?= $f['updated_at'] ? date('d M, Y', strtotime($f['updated_at'])) : '-' ?></td>
        </tr>
      <?php endwhile; ?>
    <?php else: ?>
      <tr><td colspan="8" class="muted">No fee history available</td></tr>
    <?php endif; ?>
    </tbody>
  </table>
  <div style="text-align:right;margin-top:6px">
    <a class="btn" href="fee_control.php">Open Fee Control →</a>
    <a class="btn" href="fees_report.php" style="margin-left:6px">Full Fee Report →</a>
  </div>
</div>

<!-- Latest Requests & Students -->
<div class="grid2 mt-3">
  <div class="section">
    <h3>Latest Admission Requests</h3>
    <table>
      <thead><tr><th>ID</th><th>Name</th><th>Course</th><th>Timing</th><th>Date</th></tr></thead>
      <tbody>
      <?php if($latestRequests && mysqli_num_rows($latestRequests)>0): while($r=mysqli_fetch_assoc($latestRequests)): ?>
        <tr>
          <td><?=$r['id']?></td>
          <td><?=h($r['full_name'])?></td>
          <td><?=h($r['requested_course'])?></td>
          <td><?=h($r['preferred_timing'])?></td>
          <td><?=date('d M',strtotime($r['created_at']))?></td>
        </tr>
      <?php endwhile; else: ?>
        <tr><td colspan="5" class="muted">No recent requests</td></tr>
      <?php endif; ?>
      </tbody>
    </table>
    <div style="text-align:right;margin-top:6px"><a class="btn" href="requests_pending.php">View All →</a></div>
  </div>

  <div class="section">
    <h3>New Students</h3>
    <table>
      <thead><tr><th>ID</th><th>Name</th><th>Email</th><th>Joined</th></tr></thead>
      <tbody>
      <?php if($latestStudents && mysqli_num_rows($latestStudents)>0): while($s=mysqli_fetch_assoc($latestStudents)): ?>
        <tr>
          <td><?=$s['id']?></td>
          <td><?=h($s['full_name'])?></td>
          <td><?=h($s['email'])?></td>
          <td><?=date('d M',strtotime($s['created_at']))?></td>
        </tr>
      <?php endwhile; else: ?>
        <tr><td colspan="4" class="muted">No new students</td></tr>
      <?php endif; ?>
      </tbody>
    </table>
    <div style="text-align:right;margin-top:6px"><a class="btn" href="students.php">View All →</a></div>
  </div>
</div>

<!-- Recent Batches -->
<div class="section mt-3">
  <h3>Recent Batches</h3>
  <table>
    <thead><tr><th>ID</th><th>Name</th><th>Start</th><th>Status</th></tr></thead>
    <tbody>
    <?php if($latestBatches && mysqli_num_rows($latestBatches)>0): while($b=mysqli_fetch_assoc($latestBatches)): ?>
      <tr>
        <td><?=$b['id']?></td>
        <td><?=h($b['name'])?></td>
        <td><?=date('d M',strtotime($b['start_date']))?></td>
        <td><?=h($b['status'])?></td>
      </tr>
    <?php endwhile; else: ?>
      <tr><td colspan="4" class="muted">No batches</td></tr>
    <?php endif; ?>
    </tbody>
  </table>
  <div style="text-align:right;margin-top:6px"><a class="btn" href="batches_list.php">View All →</a></div>
</div>

<!-- ChartJS -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
const feeCtx = document.getElementById('feeChart').getContext('2d');
new Chart(feeCtx, {
  type: 'bar',
  data: {
    labels: <?= json_encode($labels) ?>,
    datasets: [{
      label: 'Collected (PKR)',
      data: <?= json_encode($data) ?>,
      backgroundColor: '#2563eb'
    }]
  },
  options: {
    plugins: { legend: { display: false } },
    scales: { y: { beginAtZero: true } }
  }
});
</script>

<?php admin_footer(); ?>
