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

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

/**
 * Tetapan & penapis
 * - date_field: official_member_since (default) | created_at
 * - start, end: yyyy-mm-dd
 */
$dateField = ($_GET['date_field'] ?? 'official_member_since') === 'created_at'
           ? 'created_at' : 'official_member_since';
$start = trim($_GET['start'] ?? '');
$end   = trim($_GET['end']   ?? '');

// WHERE: Active only + julat tarikh
$where = ["status = 1"]; // Active sahaja
$types = '';
$params = [];

if ($start !== '') { $where[] = "$dateField >= ?"; $types .= 's'; $params[] = $start . ' 00:00:00'; }
if ($end   !== '') { $where[] = "$dateField <= ?"; $types .= 's'; $params[] = $end   . ' 23:59:59'; }
$whereSql = $where ? 'WHERE ' . implode(' AND ', $where) : '';

// Utility: buang bukan digit dari IC (elak bergantung pada REGEXP_REPLACE)
$CLEAN_IC = "REPLACE(REPLACE(REPLACE(REPLACE(IFNULL(ic_number,''), '-', ''), ' ', ''), '/', ''), '.', '')";
$LAST_DIGIT = "CAST(RIGHT($CLEAN_IC, 1) AS UNSIGNED)";

/**
 * Ambil ringkasan ikut race.
 * DB diisi dengan nilai race: Malay | Chinese | Indian | Others
 * Kita paparkan label BM: Melayu | Cina | India | Lain-lain
 */
$sql = "
  SELECT
    COALESCE(race, 'Others') AS race,
    SUM(CASE WHEN ($LAST_DIGIT % 2) = 1 THEN 1 ELSE 0 END) AS male_count,
    SUM(CASE WHEN ($LAST_DIGIT % 2) = 0 THEN 1 ELSE 0 END) AS female_count
  FROM members
  $whereSql
  GROUP BY COALESCE(race, 'Others')
";

$stmt = $conn->prepare($sql);
if ($types !== '') { $stmt->bind_param($types, ...$params); }
$stmt->execute();
$res = $stmt->get_result();

/** Pemetaan lajur keluaran */
$order = ['Malay','Chinese','Indian','Others']; // ikut susunan tetap
$label = ['Malay'=>'Melayu','Chinese'=>'Cina','Indian'=>'India','Others'=>'Lain-lain'];

$data = [];
foreach ($order as $k) $data[$k] = ['male'=>0,'female'=>0];

while ($row = $res->fetch_assoc()) {
    $race = in_array($row['race'], $order, true) ? $row['race'] : 'Others';
    $data[$race]['male']   = (int)$row['male_count'];
    $data[$race]['female'] = (int)$row['female_count'];
}
$stmt->close();

/** Jumlah keseluruhan */
$totals = ['male'=>0, 'female'=>0];
foreach ($data as $vals) {
    $totals['male']   += $vals['male'];
    $totals['female'] += $vals['female'];
}

/** Export CSV */
if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=report_race_gender.csv');
    $out = fopen('php://output', 'w');

    // Baris maklumat penapis
    fputcsv($out, ['Date field:', $dateField, 'Start:', $start, 'End:', $end]);
    // Header
    $header = array_merge(['Jantina'], array_map(fn($k)=>$label[$k], $order), ['Jumlah']);
    fputcsv($out, $header);

    // Lelaki
    $row = ['Lelaki'];
    foreach ($order as $k) $row[] = $data[$k]['male'];
    $row[] = $totals['male'];
    fputcsv($out, $row);

    // Perempuan
    $row = ['Perempuan'];
    foreach ($order as $k) $row[] = $data[$k]['female'];
    $row[] = $totals['female'];
    fputcsv($out, $row);

    // Jumlah
    $row = ['Jumlah'];
    foreach ($order as $k) $row[] = $data[$k]['male'] + $data[$k]['female'];
    $row[] = $totals['male'] + $totals['female'];
    fputcsv($out, $row);

    fclose($out);
    exit;
}

// ===== View =====
ob_start();
?>
<style>
.card{background:#fff;border:1px solid #e5e7eb;border-radius:10px}
.card-body{padding:12px 14px}
.table-wrap{width:100%;overflow-x:auto;margin-top:10px}
table{border-collapse:collapse;width:100%;min-width:640px}
th,td{border:1px solid #d1d5db;padding:8px 10px;text-align:center}
thead th{background:#f8fafc}
.form{display:flex;gap:8px;flex-wrap:wrap;align-items:center;margin:10px 0}
.input,.select,.btn{padding:8px;border:1px solid #d1d5db;border-radius:8px}
.btn{background:#2563eb;color:#fff;border-color:#2563eb;cursor:pointer}
.btn.secondary{background:#f3f4f6;color:#111;border-color:#d1d5db}
.small{font-size:12px;color:#6b7280}
</style>

<div class="content">
  <h2>📊 Bilangan Keahlian Mengikut Kaum & Jantina (Active sahaja)</h2>

  <div class="card">
    <div class="card-body">
      <form method="get" class="form">
        <select name="date_field" class="select">
          <option value="official_member_since" <?= $dateField==='official_member_since'?'selected':'' ?>>
            Tarikh Ahli Rasmi (official_member_since)
          </option>
          <option value="created_at" <?= $dateField==='created_at'?'selected':'' ?>>
            Tarikh Cipta Rekod (created_at)
          </option>
        </select>
        <input type="date" name="start" value="<?= htmlspecialchars($start) ?>" class="input">
        <input type="date" name="end"   value="<?= htmlspecialchars($end)   ?>" class="input">
        <button class="btn" type="submit">Generate</button>
        <a class="btn secondary" href="?">Reset</a>
        <a class="btn secondary" href="?<?= http_build_query(['date_field'=>$dateField,'start'=>$start,'end'=>$end,'export'=>'csv']) ?>">Export CSV</a>
      </form>

      <div class="small">Nota: Laporan ini hanya kira ahli berstatus <strong>Active</strong>. Jantina ditentukan mengikut <em>digit terakhir</em> IC.</div>

      <div class="table-wrap">
        <table>
          <thead>
            <tr>
              <th>Jantina</th>
              <?php foreach ($order as $k): ?>
                <th><?= htmlspecialchars($label[$k]) ?></th>
              <?php endforeach; ?>
              <th>Jumlah</th>
            </tr>
          </thead>
          <tbody>
            <tr>
              <th>Lelaki</th>
              <?php foreach ($order as $k): ?>
                <td><?= $data[$k]['male'] ?></td>
              <?php endforeach; ?>
              <td><?= $totals['male'] ?></td>
            </tr>
            <tr>
              <th>Perempuan</th>
              <?php foreach ($order as $k): ?>
                <td><?= $data[$k]['female'] ?></td>
              <?php endforeach; ?>
              <td><?= $totals['female'] ?></td>
            </tr>
            <tr>
              <th>Jumlah</th>
              <?php foreach ($order as $k): ?>
                <td><?= $data[$k]['male'] + $data[$k]['female'] ?></td>
              <?php endforeach; ?>
              <td><?= $totals['male'] + $totals['female'] ?></td>
            </tr>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</div>

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