<?php
session_start();
require_once __DIR__ . '/db.php';

/**
 * User Management (Admin) — View-based (Option B)
 * - Guna VIEW v_members_ui untuk derive District/Work Location berkuatkuasa.
 * - Search, filter, sort, paginate
 * - Export CSV (respects current filters)
 * - Responsive
 */

// ===== Auth: admin only =====
if (!isset($_SESSION['user']) || (($_SESSION['user']['role'] ?? '') !== 'admin')) {
    header("Location: dashboard.php");
    exit;
}

// ===== CSRF token =====
if (empty($_SESSION['csrf_token'])) {
    $_SESSION['csrf_token'] = bin2hex(random_bytes(16));
}
$CSRF = $_SESSION['csrf_token'];

// ===== Helpers =====
function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }

function table_exists(mysqli $conn, string $table): bool {
    $sql = "SELECT COUNT(*) AS c
              FROM INFORMATION_SCHEMA.TABLES
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?";
    $stmt = $conn->prepare($sql);
    if (!$stmt) return false;
    $stmt->bind_param('s', $table);
    $stmt->execute();
    $res = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    return ((int)($res['c'] ?? 0)) > 0;
}

function write_audit(mysqli $conn, int $adminId, ?int $memberId, string $action): void {
    if (!table_exists($conn,'audit_logs')) return;
    $stmt = $conn->prepare("INSERT INTO audit_logs (admin_id, member_id, action) VALUES (?, ?, ?)");
    if ($stmt) { $stmt->bind_param("iis", $adminId, $memberId, $action); $stmt->execute(); $stmt->close(); }
}

/**
 * Build WHERE + params + types untuk penapisan.
 * $alias:
 *   ''   → guna terus nama kolum (untuk SELECT direct dari view)
 *   'v'  → prefix "v." (untuk join v_members_ui v JOIN members m)
 */
function build_filters(string $alias = ''): array {
    global $q, $role_f, $status, $loc_f, $dist_f, $valid_roles;

    $prefix = $alias !== '' ? $alias . '.' : '';

    $where  = [];
    $params = [];
    $types  = '';

    if ($q !== '') {
        $where[] = "("
                 . "{$prefix}full_name LIKE ? OR "
                 . "{$prefix}staff_number LIKE ? OR "
                 . "{$prefix}email LIKE ? OR "
                 . "{$prefix}district_name_effective LIKE ? OR "
                 . "{$prefix}work_location_effective LIKE ?"
                 . ")";
        $like = "%$q%";
        array_push($params, $like,$like,$like,$like,$like);
        $types .= 'sssss';
    }

    if (in_array($role_f, $valid_roles, true)) {
        $where[] = "{$prefix}role = ?";
        $params[] = $role_f; $types .= 's';
    }

    if ($status === 'active') {
        $where[] = "{$prefix}status = 1";
    } elseif ($status === 'inactive') {
        $where[] = "{$prefix}status = 0";
    }

    if ($loc_f !== '') {
        $where[] = "{$prefix}work_location_effective = ?";
        $params[] = $loc_f; $types .= 's';
    }

    if ($dist_f > 0) {
        $where[] = "{$prefix}district_id_effective = ?";
        $params[] = $dist_f; $types .= 'i';
    }

    $wsql = $where ? 'WHERE '.implode(' AND ', $where) : '';

    return [$wsql, $params, $types];
}

// ===== Guard: pastikan VIEW wujud =====
if (!table_exists($conn, 'v_members_ui')) {
    http_response_code(500);
    echo "<p style='font-family:system-ui;max-width:700px;margin:24px auto;padding:14px;border:1px solid #fee2e2;background:#fef2f2;border-radius:12px;color:#7f1d1d'>
            <strong>v_members_ui</strong> belum wujud. Sila jalankan skrip <code>CREATE OR REPLACE VIEW v_members_ui ...</code> (Option B) dahulu.
          </p>";
    exit;
}

// ===== Filters (GET) =====
$q        = trim($_GET['q'] ?? '');
$role_f   = $_GET['role']   ?? 'all';    // all|admin|exco|ajk|member
$status   = $_GET['status'] ?? 'all';    // all|active|inactive
$loc_f    = trim($_GET['loc'] ?? '');    // work_location_effective (nama)
$dist_f   = (int)($_GET['district'] ?? 0); // district_id_effective

$valid_roles = ['admin','exco','ajk','member'];

// Sorting (whitelist)
// NOTE: 'location' disamakan kepada work_location_effective
$sort     = $_GET['sort'] ?? 'full_name';      // full_name|created_at|location|role
$dir      = strtolower($_GET['dir'] ?? 'asc'); // asc|desc
$sortable = [
    'full_name'  => 'full_name',
    'created_at' => 'created_at',
    'location'   => 'work_location_effective',
    'role'       => 'role',
];
$orderCol = $sortable[$sort] ?? 'full_name';
$orderDir = ($dir === 'desc') ? 'DESC' : 'ASC';

// Pagination
$allowedPer = [10,20,50,100];
$per_page = (int)($_GET['per'] ?? 20);
if (!in_array($per_page, $allowedPer, true)) $per_page = 20;
$page   = max(1, (int)($_GET['page'] ?? 1));
$offset = ($page - 1) * $per_page;

// Build WHERE utk paparan (guna v_members_ui direct, tanpa alias)
list($wsql, $params, $types) = build_filters('');

// ===== Export CSV (ikut penapis semasa) =====
if (isset($_GET['export']) && $_GET['export'] === 'csv') {

    // Build WHERE utk export — guna alias 'v' (v_members_ui v)
    list($wsqlExp, $paramsExp, $typesExp) = build_filters('v');

    header('Content-Type: text/csv; charset=UTF-8');
    header('Content-Disposition: attachment; filename="members_export_' . date('Ymd_His') . '.csv"');
    header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');

    $out = fopen('php://output', 'w');

    // ORDER BY: prefix ikut alias v.
    $orderColExp = $orderCol;
    $colsNeedingPrefix = [
        'full_name',
        'created_at',
        'work_location_effective',
        'role',
    ];
    if (in_array($orderColExp, $colsNeedingPrefix, true)) {
        $orderColExp = 'v.' . $orderColExp;
    }

    $sqlExp = "
        SELECT
            m.*,
            v.district_name_effective AS district_name_effective,
            v.work_location_effective AS work_location_effective
        FROM v_members_ui v
        JOIN members m ON m.id = v.id
        {$wsqlExp}
        ORDER BY {$orderColExp} {$orderDir}
    ";

    $stExp = $conn->prepare($sqlExp);
    if ($typesExp !== '') {
        $stExp->bind_param($typesExp, ...$paramsExp);
    }
    $stExp->execute();
    $rs = $stExp->get_result();

    $headersWritten = false;
    $headerKeys = [];

    while ($row = $rs->fetch_assoc()) {
        // Buang password_hash kalau ada
        if (array_key_exists('password_hash', $row)) {
            unset($row['password_hash']);
        }

        // Tulis header hanya sekali — berdasarkan key row pertama
        if (!$headersWritten) {
            $headerKeys = array_keys($row);
            fputcsv($out, $headerKeys);
            $headersWritten = true;
        }

        // Tukar status 1/0 kepada Active/Inactive
        if (array_key_exists('status', $row)) {
            $row['status'] = ((int)$row['status'] === 1) ? 'Active' : 'Inactive';
        }

        // Pastikan order kolum konsisten ikut $headerKeys
        $line = [];
        foreach ($headerKeys as $k) {
            $line[] = $row[$k] ?? '';
        }

        fputcsv($out, $line);
    }

    fclose($out);
    exit;
}

// ===== Flash (optional passthrough via query) =====
$flash_ok  = isset($_GET['success']) ? trim($_GET['success']) : '';
$flash_err = isset($_GET['error'])   ? trim($_GET['error'])   : '';

// ===== Dropdown Daerah (penapis) =====
$districtOptions = [];
$districtMap = []; // id => name (untuk label)
if ($resD = $conn->query("SELECT id, name FROM districts WHERE COALESCE(is_active,1)=1 ORDER BY name ASC")) {
    while ($d = $resD->fetch_assoc()) {
        $districtOptions[] = ['id'=>(int)$d['id'],'name'=>$d['name']];
        $districtMap[(int)$d['id']] = $d['name'];
    }
}
// Kekalkan pilihan jika admin tapis daerah yang kebetulan inactive
if ($dist_f > 0 && !isset($districtMap[$dist_f])) {
    $stTmp = $conn->prepare("SELECT name FROM districts WHERE id=? LIMIT 1");
    if ($stTmp) {
        $stTmp->bind_param('i', $dist_f);
        if ($stTmp->execute()) {
            $nm = $stTmp->get_result()->fetch_column();
            if ($nm) { array_unshift($districtOptions, ['id'=>$dist_f,'name'=>$nm]); $districtMap[$dist_f] = $nm; }
        }
        $stTmp->close();
    }
}

// ===== Dropdown Lokasi (penapis) =====
// Ambil dari master work_locations(active) + UNION nama effective dari view (cover legacy string)
$locOptions = [];
$sqlLoc = "
    SELECT name AS loc FROM work_locations WHERE COALESCE(active,1)=1
    UNION
    SELECT DISTINCT work_location_effective AS loc
    FROM v_members_ui
    WHERE work_location_effective IS NOT NULL AND work_location_effective <> ''
    ORDER BY loc ASC
";
if ($res = $conn->query($sqlLoc)) {
    while ($r = $res->fetch_assoc()) {
        $locOptions[] = $r['loc'];
    }
}
if ($loc_f !== '' && !in_array($loc_f, $locOptions, true)) {
    array_unshift($locOptions, $loc_f);
}

// ===== Count (pagination) =====
$countSql = "SELECT COUNT(*) AS c FROM v_members_ui $wsql";
$stCount = $conn->prepare($countSql);
if ($types !== '') $stCount->bind_param($types, ...$params);
$stCount->execute();
$cRes = $stCount->get_result();
$total_rows = ($cRes && $cRes->num_rows) ? (int)$cRes->fetch_assoc()['c'] : 0;
$stCount->close();

$total_pages = max(1, (int)ceil($total_rows / $per_page));
if ($page > $total_pages) { $page = $total_pages; $offset = ($page - 1) * $per_page; }

// ===== Fetch list (paparan) =====
$sql = "SELECT
            id, full_name, staff_number, email, role, status, ack_form,
            waris_name, waris_phone,
            district_name_effective AS district_name,
            work_location_effective AS work_location,
            created_at
        FROM v_members_ui
        $wsql
        ORDER BY {$orderCol} {$orderDir}
        LIMIT ".(int)$per_page." OFFSET ".(int)$offset;
$st = $conn->prepare($sql);
if ($types !== '') $st->bind_param($types, ...$params);
$st->execute();
$result = $st->get_result();

// ===== View =====
ob_start();
?>
<style>
  :root{
    --bg:#0b1220; --card:#ffffff; --muted:#64748b; --text:#0f172a; --border:#e5e7eb;
    --pri:#2563eb; --pri-ink:#1e40af; --ok:#10b981; --ok-ink:#065f46;
    --table-head:#f8fafc; --row:#ffffff; --row-alt:#fcfcfd; --row-hover:#f1f5f9;
    --chip-bg:#eef2ff; --chip-ink:#3730a3; --chip-bd:#c7d2fe;
    --pad-y: 16px; --pad-x: 14px; --font: 14.5px;
  }
  *{ box-sizing:border-box; } body{ margin:0; font-family:system-ui,-apple-system,Segoe UI,Roboto; color:var(--text); background:#f6f7fb; }
  .wrap{ max-width:1250px; margin:0 auto; padding:22px 16px 36px; }
  .page-title{ margin:0 0 8px; font-size: clamp(20px,2.2vw,26px); }
  .sub{ margin:0 0 16px; font-size:13px; color:#6b7280; }
  .flash{ padding:10px 12px; border-radius:10px; margin:0 0 14px; border:1px solid transparent; }
  .flash.ok{ background:#ecfdf5; border-color:#a7f3d0; color:#065f46; }
  .flash.err{ background:#fef2f2; border-color:#fecdd3; color:#9f1239; }

  .filters{ display:grid; grid-template-columns: 1.2fr repeat(7, max-content) 1fr; gap:10px; align-items:center; margin-bottom:14px; }
  .input, .select, .btn{ padding:10px 12px; border:1px solid var(--border); border-radius:10px; background:#fff; font-size:14px; }
  .btn{ cursor:pointer; font-weight:700; }
  .btn.primary{ background:var(--pri); border-color:var(--pri); color:#fff; }
  .btn.primary:hover{ background:var(--pri-ink); }
  .btn.export{ background:var(--ok); border-color:var(--ok); color:#fff; }
  .btn.icon{ display:inline-flex; align-items:center; gap:8px; }
  .btn.reset{ background:#f3f4f6; border-color:#e5e7eb; color:#111827; }
  .btn.reset:hover{ background:#e5e7eb; }

  .card{ background:#fff; border-radius:16px; border:1px solid rgba(0,0,0,.06); box-shadow: 0 8px 24px rgba(2,6,23,.08); overflow:hidden; }
  .card-head{ padding:12px 14px; background:#fff; border-bottom:1px solid var(--border); display:flex; align-items:center; justify-content:space-between; flex-wrap:wrap; gap:8px; }
  .card-head .info{ color:#64748b; font-size:13px; }

  table{ width:100%; border-collapse:separate; border-spacing:0; }
  thead th{ position: sticky; top:0; z-index:2; background:var(--table-head); text-align:left; padding:12px 14px; border-bottom:1px solid var(--border); font-size:12.5px; color:#334155; letter-spacing:.2px; }
  tbody td{ padding: var(--pad-y) var(--pad-x); border-bottom:1px solid var(--border); font-size: var(--font); background:var(--row); }
  tbody tr:nth-child(even) td{ background:var(--row-alt); }
  tbody tr:hover td{ background:var(--row-hover); }
  td.sticky, th.sticky{ position: sticky; left:0; z-index:1; background:inherit; }

  .pill { display:inline-block; padding:5px 10px; border-radius:999px; font-size:12px; background:var(--chip-bg); color:var(--chip-ink); border:1px solid var(--chip-bd); }
  .badge { display:inline-block; padding:5px 10px; border-radius:999px; font-size:12px; font-weight:700; border:1px solid transparent; }
  .badge.active{ background:#ecfdf5; color:#065f46; border-color:#a7f3d0; }
  .badge.inactive{ background:#fef2f2; color:#9f1239; border-color:#fecdd3; }

  .row-actions{ display:flex; gap:8px; flex-wrap:wrap; }
  .btn.mini{ padding:8px 10px; border-radius:8px; font-size:12.5px; }

  .pagination{ display:flex; align-items:center; justify-content:space-between; gap:10px; padding:12px 14px; background:#fff; border-top:1px solid var(--border); }
  .pages{ display:flex; gap:6px; flex-wrap:wrap; }
  .pages a{ padding:8px 11px; border:1px solid var(--border); border-radius:10px; text-decoration:none; color:#111827; font-weight:700; }
  .pages .cur{ background:#111827; color:#fff; }
  .meta{ font-size:12.5px; color:#64748b; }

  @media (max-width: 980px){
    .filters{ grid-template-columns: 1fr 1fr; grid-auto-rows: auto; }
    thead{ display:none; }
    tbody, tr, td{ display:block; width:100%; }
    tr{ margin:12px; border:1px solid var(--border); border-radius:12px; overflow:hidden; box-shadow:0 4px 10px rgba(2,6,23,.06); }
    td{ border:none; border-top:1px solid #f1f5f9; }
    td:first-child{ border-top:none; }
    td::before{ content: attr(data-th); display:block; font-weight:700; color:#334155; margin-bottom:4px; font-size:12px; }
    td.sticky, th.sticky{ position:static; }
    .pagination{ flex-direction:column; align-items:flex-start; gap:8px; }
  }
</style>

<div class="wrap">
  <h2 class="page-title">User Management</h2>
  <p class="sub">Urus ahli: cari, tapis, susun, eksport CSV. Data District & Work Location dipapar <em>berdasarkan master</em> melalui <code>v_members_ui</code>.</p>

  <?php if ($flash_ok): ?><div class="flash ok"><?=h($flash_ok)?></div><?php endif; ?>
  <?php if ($flash_err): ?><div class="flash err"><?=h($flash_err)?></div><?php endif; ?>

  <form class="filters" method="get">
    <input class="input" type="text" name="q" value="<?= h($q) ?>" placeholder="Cari nama / staf / email / daerah / lokasi…">

    <select class="select" name="role" title="Role">
      <option value="all" <?= $role_f==='all'?'selected':'' ?>>Semua role</option>
      <?php foreach ($valid_roles as $r): ?>
        <option value="<?= $r ?>" <?= $role_f===$r?'selected':'' ?>><?= ucfirst($r) ?></option>
      <?php endforeach; ?>
    </select>

    <select class="select" name="status" title="Status">
      <option value="all" <?= $status==='all'?'selected':'' ?>>Semua status</option>
      <option value="active" <?= $status==='active'?'selected':'' ?>>Active</option>
      <option value="inactive" <?= $status==='inactive'?'selected':'' ?>>Inactive</option>
    </select>

    <!-- District filter (effective) -->
    <select class="select" name="district" title="Daerah">
      <option value="0" <?= $dist_f===0?'selected':'' ?>>Semua daerah</option>
      <?php foreach ($districtOptions as $d): ?>
        <option value="<?= (int)$d['id'] ?>" <?= $dist_f===(int)$d['id']?'selected':'' ?>><?= h($d['name']) ?></option>
      <?php endforeach; ?>
    </select>

    <!-- Work Location filter (effective) -->
    <select class="select" name="loc" title="Lokasi">
      <option value="" <?= $loc_f===''?'selected':'' ?>>Semua lokasi</option>
      <?php foreach ($locOptions as $opt): ?>
        <option value="<?= h($opt) ?>" <?= $loc_f===$opt?'selected':'' ?>><?= h($opt) ?></option>
      <?php endforeach; ?>
    </select>

    <select class="select" name="sort" title="Susun">
      <option value="full_name"  <?= $sort==='full_name'?'selected':'' ?>>Nama</option>
      <option value="created_at" <?= $sort==='created_at'?'selected':'' ?>>Tarikh Daftar</option>
      <option value="location"   <?= $sort==='location'?'selected':'' ?>>Lokasi</option>
      <option value="role"       <?= $sort==='role'?'selected':'' ?>>Role</option>
    </select>
    <select class="select" name="dir" title="Arah">
      <option value="asc"  <?= $dir==='asc'?'selected':'' ?>>ASC</option>
      <option value="desc" <?= $dir==='desc'?'selected':'' ?>>DESC</option>
    </select>

    <select class="select" name="per" title="Per page">
      <?php foreach ($allowedPer as $n): ?>
        <option value="<?= $n ?>" <?= $per_page===$n?'selected':'' ?>><?= $n ?>/page</option>
      <?php endforeach; ?>
    </select>

    <button class="btn primary" type="submit">Terap</button>
    <a class="btn reset" href="user-management.php">Reset</a>

    <button class="btn export icon" type="submit" name="export" value="csv">Export CSV</button>
  </form>

  <div class="card">
    <div class="card-head">
      <div class="info">
        <?= number_format($total_rows) ?> rekod • Halaman <?= $page ?> / <?= $total_pages ?> •
        Susun: <strong><?= h(ucfirst(str_replace('_',' ',$sort))) ?></strong> (<?= strtoupper($dir) ?>)
        <?= $role_f==='all'?'':' • Role: '.h(ucfirst($role_f)) ?>
        <?= $dist_f>0 ? ' • Daerah: '.h($districtMap[$dist_f] ?? ('#'.$dist_f)) : '' ?>
        <?= $loc_f ? ' • Lokasi: '.h($loc_f):'' ?>
      </div>
      <div class="info">Klik <strong>Nama</strong> atau butang <strong>Edit</strong> untuk kemaskini.</div>
    </div>

    <div class="table-wrap">
      <table role="table" aria-label="User Management">
        <thead>
          <tr>
            <th class="sticky">Nama</th>
            <th>No. Staf</th>
            <th>Email</th>
            <th>Daerah</th>
            <th>Lokasi</th>
            <th>Peranan</th>
            <th>Status</th>
            <th>Waris (Ringkas)</th>
            <th>Actions</th>
          </tr>
        </thead>
        <tbody>
        <?php if ($result->num_rows === 0): ?>
          <tr><td data-th="Tiada data" colspan="9"><em style="color:#64748b">Tiada rekod dijumpai.</em></td></tr>
        <?php else: ?>
          <?php while ($row = $result->fetch_assoc()): ?>
            <tr>
              <td class="sticky" data-th="Nama">
                <div style="font-weight:700;">
                  <a href="user-edit.php?id=<?= (int)$row['id'] ?>" style="text-decoration:none;color:inherit;">
                    <?= h($row['full_name']) ?>
                  </a>
                </div>
              </td>

              <td data-th="No. Staf"><?= h($row['staff_number']) ?></td>
              <td data-th="Email"><?= h($row['email']) ?></td>
              <td data-th="Daerah"><?= h($row['district_name'] ?: '-') ?></td>
              <td data-th="Lokasi"><?= h($row['work_location'] ?: '-') ?></td>

              <td data-th="Peranan">
                <span class="pill" style="background:#e0f2fe;border-color:#bae6fd;color:#075985;">
                  <?= h(ucfirst($row['role'])) ?>
                </span>
              </td>

              <td data-th="Status">
                <span class="badge <?= ((int)$row['status']===1 ? 'active' : 'inactive') ?>">
                  <?= ((int)$row['status']===1 ? 'Active' : 'Inactive') ?>
                </span>
              </td>

              <td data-th="Waris">
                <div class="sub">
                  <?php
                    $wname = trim((string)($row['waris_name'] ?? ''));
                    $wphone= trim((string)($row['waris_phone'] ?? ''));
                    echo h($wname ?: '-');
                    if ($wphone) echo ' · '.h($wphone);
                  ?>
                </div>
              </td>

              <td data-th="Actions">
                <div class="row-actions">
                  <a href="user-edit.php?id=<?= (int)$row['id'] ?>" class="btn mini" style="background:#0ea5e9;border-color:#0ea5e9;color:#fff;">Edit</a>
                  <?php if (!empty($row['ack_form'])): ?>
                    <a href="generate_form.php?user_id=<?= (int)$row['id'] ?>" target="_blank" class="btn mini" style="background:#22c55e;border-color:#22c55e;color:#fff;">Borang Pengesahan</a>
                  <?php endif; ?>
                </div>
              </td>
            </tr>
          <?php endwhile; ?>
        <?php endif; ?>
        </tbody>
      </table>
    </div>

    <!-- Pagination -->
    <div class="pagination">
      <div class="meta">
        Menunjukkan <strong><?= $total_rows ? ($offset+1) : 0 ?></strong>–<strong><?= min($offset+$per_page, $total_rows) ?></strong> daripada <strong><?= $total_rows ?></strong> rekod
      </div>
      <div class="pages">
        <?php
          $qs = $_GET; unset($qs['page']); $base = http_build_query($qs);
          $prev = max(1, $page-1); $next = min($total_pages, $page+1);
          $start = max(1, $page-2); $end = min($total_pages, $page+2);
        ?>
        <a href="?<?= $base ?>&page=1"      <?= $page===1?'style="pointer-events:none;opacity:.5"':'' ?>>« First</a>
        <a href="?<?= $base ?>&page=<?= $prev ?>" <?= $page===1?'style="pointer-events:none;opacity:.5"':'' ?>>‹ Prev</a>
        <?php for ($i=$start; $i<=$end; $i++): ?>
          <a href="?<?= $base ?>&page=<?= $i ?>" class="<?= $i===$page?'cur':'' ?>"><?= $i ?></a>
        <?php endfor; ?>
        <a href="?<?= $base ?>&page=<?= $next ?>" <?= $page===$total_pages?'style="pointer-events:none;opacity:.5"':'' ?>>Next ›</a>
        <a href="?<?= $base ?>&page=<?= $total_pages ?>" <?= $page===$total_pages?'style="pointer-events:none;opacity:.5"':'' ?>>Last »</a>
      </div>
    </div>
  </div>
</div>

<?php
$content = ob_get_clean();
include __DIR__ . '/layout.php';
