<?php
require_once __DIR__ . '/includes/auth.php';
requireLogin();

$pdo = getPDO();
$role = currentRole();
$clrccode = currentClrcCode();

// ---- Handle soft delete / restore (POST) ----
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action'], $_POST['cwsn_code'])) {
    $code = $_POST['cwsn_code'];
    $stmt = $pdo->prepare('SELECT clrccode, status FROM cwsn WHERE cwsn_code = ?');
    $stmt->execute([$code]);
    $rec = $stmt->fetch();
    $canEdit = $rec && ($role === 'admin' || ($role === 'clrc' && $rec['clrccode'] === $clrccode));

    if ($canEdit) {
        if ($_POST['action'] === 'delete') {
            $pdo->prepare("UPDATE cwsn SET status = '1' WHERE cwsn_code = ?")->execute([$code]);
        } elseif ($_POST['action'] === 'restore') {
            $pdo->prepare("UPDATE cwsn SET status = '0' WHERE cwsn_code = ?")->execute([$code]);
        }
    }
    header('Location: cwsn_list.php?' . http_build_query($_GET));
    exit;
}

// ---- Filters ----
$search       = trim($_GET['search'] ?? '');
$statusFilter = $_GET['status'] ?? '0';                 // '0' Complete, '1' Deleted, '2' Incomplete, 'all'
$clrcFilter   = $role === 'clrc' ? $clrccode : ($_GET['clrc'] ?? 'all');
$disFilter    = $_GET['disability'] ?? 'all';
$page         = max(1, (int)($_GET['page'] ?? 1));
$perPage      = 25;

$where = [];
$params = [];
if ($statusFilter !== 'all') { $where[] = 'c.status = ?'; $params[] = $statusFilter; }
if ($clrcFilter !== 'all' && $clrcFilter) { $where[] = 'c.clrccode = ?'; $params[] = $clrcFilter; }
if ($disFilter !== 'all') { $where[] = 'c.disability_id = ?'; $params[] = $disFilter; }
if ($search !== '') { $where[] = 'c.cwsn_name LIKE ?'; $params[] = $search . '%'; }
$whereSql = $where ? 'WHERE ' . implode(' AND ', $where) : '';

// ---- CSV export (current filters, not just current page) ----
if (isset($_GET['export'])) {
    $sql = "SELECT c.cwsn_code, c.cwsn_name, c.gender, c.dob, cl.clrcname, cu.cluname,
                   d.disability_name, c.class, c.pen, c.status
            FROM cwsn c
            LEFT JOIN clrc cl ON cl.clrccode = c.clrccode
            LEFT JOIN cluster cu ON cu.clucd = c.clucd
            LEFT JOIN disability d ON d.disability_id = c.disability_id
            $whereSql ORDER BY c.cwsn_name";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);

    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="cwsn_list_export.csv"');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['CWSN Code', 'Name', 'Gender', 'DOB', 'CLRC', 'School', 'Disability', 'Class', 'PEN', 'Status']);
    while ($r = $stmt->fetch()) {
        $statusLabel = $r['status'] === '1' ? 'Deleted' : ($r['status'] === '2' ? 'Incomplete' : 'Complete');
        fputcsv($out, [
            $r['cwsn_code'], $r['cwsn_name'], $r['gender'],
            $r['dob'] ? date('d-m-Y', strtotime($r['dob'])) : '',
            $r['clrcname'], $r['cluname'], $r['disability_name'], $r['class'], $r['pen'], $statusLabel,
        ]);
    }
    fclose($out);
    exit;
}

// ---- Count + page of results ----
$countStmt = $pdo->prepare("SELECT COUNT(*) c FROM cwsn c $whereSql");
$countStmt->execute($params);
$total = (int)$countStmt->fetch()['c'];
$totalPages = max(1, (int)ceil($total / $perPage));
$page = min($page, $totalPages);
$offset = ($page - 1) * $perPage;

$sql = "SELECT c.cwsn_code, c.cwsn_name, c.gender, c.dob, c.clrccode, c.class, c.pen, c.status,
               cl.clrcname, cu.cluname, d.disability_name
        FROM cwsn c
        LEFT JOIN clrc cl ON cl.clrccode = c.clrccode
        LEFT JOIN cluster cu ON cu.clucd = c.clucd
        LEFT JOIN disability d ON d.disability_id = c.disability_id
        $whereSql
        ORDER BY c.cwsn_name
        LIMIT $perPage OFFSET $offset";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll();

$clrcs = $pdo->query('SELECT clrccode, clrcname FROM clrc ORDER BY clrcname')->fetchAll();
$disabilities = $pdo->query('SELECT disability_id, disability_name FROM disability ORDER BY disability_name')->fetchAll();

function calcAge(?string $dob): string {
    if (!$dob) return '';
    try {
        $d = new DateTime($dob);
        return (string)$d->diff(new DateTime())->y;
    } catch (Exception $e) {
        return '';
    }
}
function statusBadge(string $status): string {
    if ($status === '1') return '<span class="badge-rose">Deleted</span>';
    if ($status === '2') return '<span class="badge-amber">Incomplete</span>';
    return '<span class="badge-green">Complete</span>';
}
function qs(array $overrides = []): string {
    return htmlspecialchars(http_build_query(array_merge($_GET, $overrides)));
}

$pageTitle = 'CWSN Records';
require __DIR__ . '/includes/header.php';
?>
<style>
  .badge{display:inline-flex;align-items:center;border-radius:9999px;padding:.15rem .6rem;font-size:.72rem;font-weight:600}
  .badge-green{background:#d1fae5;color:#047857}
  .badge-rose{background:#ffe4e6;color:#be123c}
  .badge-amber{background:#fef3c7;color:#b45309}
  table.data-table th{position:sticky;top:0;background:#f1f5f9;text-align:left;font-size:.7rem;font-weight:700;text-transform:uppercase;letter-spacing:.03em;color:#64748b;padding:.6rem .75rem;border-bottom:1px solid #e2e8f0;white-space:nowrap}
  table.data-table td{padding:.5rem .75rem;font-size:.85rem;border-bottom:1px solid #f1f5f9;white-space:nowrap}
  table.data-table tbody tr:hover{background:#eff6ff}
</style>

<div class="space-y-4">
  <div class="flex flex-col gap-3 sm:flex-row sm:items-center sm:justify-between">
    <div>
      <h1 class="text-lg font-bold text-slate-800">CWSN Records</h1>
      <p class="text-sm text-slate-500">Manage Children With Special Needs data</p>
    </div>
    <div class="flex gap-2">
      <a href="cwsn_list.php?<?= qs(['export' => 1]) ?>" class="btn btn-secondary">Export CSV</a>
      <a href="cwsn_form.php" class="btn btn-primary">+ Add CWSN</a>
    </div>
  </div>

  <form method="get" class="card p-3 space-y-3">
    <div class="flex flex-col gap-2 sm:flex-row">
      <input type="text" name="search" value="<?= htmlspecialchars($search) ?>" placeholder="Search by name (starts with)..."
             class="field-input flex-1">
      <select name="status" class="field-select sm:w-48" onchange="this.form.submit()">
        <option value="0" <?= $statusFilter === '0' ? 'selected' : '' ?>>Complete only</option>
        <option value="2" <?= $statusFilter === '2' ? 'selected' : '' ?>>Incomplete only</option>
        <option value="1" <?= $statusFilter === '1' ? 'selected' : '' ?>>Deleted only</option>
        <option value="all" <?= $statusFilter === 'all' ? 'selected' : '' ?>>All</option>
      </select>
      <?php if ($role === 'admin'): ?>
      <select name="clrc" class="field-select sm:w-56" onchange="this.form.submit()">
        <option value="all">All CLRCs</option>
        <?php foreach ($clrcs as $c): ?>
          <option value="<?= htmlspecialchars($c['clrccode']) ?>" <?= $clrcFilter === $c['clrccode'] ? 'selected' : '' ?>>
            <?= htmlspecialchars($c['clrcname']) ?>
          </option>
        <?php endforeach; ?>
      </select>
      <?php endif; ?>
      <select name="disability" class="field-select sm:w-56" onchange="this.form.submit()">
        <option value="all">All disabilities</option>
        <?php foreach ($disabilities as $d): ?>
          <option value="<?= htmlspecialchars($d['disability_id']) ?>" <?= $disFilter === $d['disability_id'] ? 'selected' : '' ?>>
            <?= htmlspecialchars($d['disability_name']) ?>
          </option>
        <?php endforeach; ?>
      </select>
      <button type="submit" class="btn btn-secondary">Filter</button>
    </div>
  </form>

  <div class="card overflow-hidden">
    <div class="overflow-x-auto" style="max-height:65vh;overflow-y:auto">
      <table class="data-table w-full">
        <thead>
          <tr>
            <th>CWSN Code</th><th>Name</th><th>Gender</th><th>Age</th><th>CLRC</th>
            <th>School / Cluster</th><th>Disability</th><th>Class</th><th>PEN</th><th>Status</th>
            <th class="text-right">Actions</th>
          </tr>
        </thead>
        <tbody>
          <?php if (empty($rows)): ?>
            <tr><td colspan="11" class="text-center py-8 text-slate-400">No records found.</td></tr>
          <?php endif; ?>
          <?php foreach ($rows as $rec):
            $canEdit = $role === 'admin' || ($role === 'clrc' && $rec['clrccode'] === $clrccode);
          ?>
          <tr>
            <td class="font-mono text-xs text-slate-500"><?= htmlspecialchars($rec['cwsn_code']) ?></td>
            <td class="font-medium text-slate-800"><?= htmlspecialchars($rec['cwsn_name']) ?></td>
            <td><?= htmlspecialchars($rec['gender']) ?></td>
            <td><?= calcAge($rec['dob']) ?></td>
            <td><?= htmlspecialchars($rec['clrcname'] ?? $rec['clrccode']) ?></td>
            <td><?= htmlspecialchars($rec['cluname'] ?? '') ?></td>
            <td><?= htmlspecialchars($rec['disability_name'] ?? '') ?></td>
            <td><?= htmlspecialchars($rec['class'] ?? '') ?></td>
            <td class="font-mono text-xs"><?= htmlspecialchars($rec['pen'] ?? '—') ?></td>
            <td><?= statusBadge($rec['status']) ?></td>
            <td class="text-right">
              <?php if ($canEdit): ?>
                <a href="cwsn_form.php?id=<?= urlencode($rec['cwsn_code']) ?>" class="text-blue-600 hover:underline text-xs font-medium">Edit</a>
                <?php if ($rec['status'] !== '1'): ?>
                  <form method="post" class="inline" onsubmit="return confirm('Mark this record as deleted?');">
                    <input type="hidden" name="cwsn_code" value="<?= htmlspecialchars($rec['cwsn_code']) ?>">
                    <input type="hidden" name="action" value="delete">
                    <button type="submit" class="text-rose-600 hover:underline text-xs font-medium ml-2">Delete</button>
                  </form>
                <?php else: ?>
                  <form method="post" class="inline">
                    <input type="hidden" name="cwsn_code" value="<?= htmlspecialchars($rec['cwsn_code']) ?>">
                    <input type="hidden" name="action" value="restore">
                    <button type="submit" class="text-emerald-600 hover:underline text-xs font-medium ml-2">Restore</button>
                  </form>
                <?php endif; ?>
              <?php endif; ?>
            </td>
          </tr>
          <?php endforeach; ?>
        </tbody>
      </table>
    </div>
    <div class="flex items-center justify-between border-t border-slate-100 px-4 py-3">
      <span class="text-xs text-slate-400"><?= $total ?> record<?= $total === 1 ? '' : 's' ?> — page <?= $page ?> of <?= $totalPages ?></span>
      <div class="flex gap-2">
        <a href="cwsn_list.php?<?= qs(['page' => max(1, $page - 1)]) ?>"
           class="btn btn-secondary <?= $page <= 1 ? 'pointer-events-none opacity-50' : '' ?>">Prev</a>
        <a href="cwsn_list.php?<?= qs(['page' => min($totalPages, $page + 1)]) ?>"
           class="btn btn-secondary <?= $page >= $totalPages ? 'pointer-events-none opacity-50' : '' ?>">Next</a>
      </div>
    </div>
  </div>
</div>
<?php require __DIR__ . '/includes/footer.php'; ?>
