<?php
require_once 'db.php';
session_start();
if (!isset($_SESSION['user']) || ($_SESSION['user']['role'] ?? '') !== 'admin') {
  http_response_code(403); exit('Admins only');
}

$keyword  = trim($_GET['q'] ?? '');
$location = trim($_GET['loc'] ?? '');

$conds  = ['m.status = 0'];
$params = [];
$types  = '';

if ($keyword !== '') {
  $conds[] = '(m.full_name LIKE ? OR m.staff_number LIKE ? OR m.email LIKE ? OR m.phone LIKE ?)';
  $kw = "%$keyword%";
  array_push($params, $kw, $kw, $kw, $kw);
  $types .= 'ssss';
}
if ($location !== '') {
  $conds[] = 'm.location = ?';
  $params[] = $location;
  $types .= 's';
}
$where = 'WHERE ' . implode(' AND ', $conds);

/* ===== SQL: official_since ikut profil (fallback berturutan) =====
   1) m.official_member_since
   2) DATE(m.ajk_approved_at)
   3) DATE(min approval_logs AJK)
   4) DATE(min approval_logs Admin)
*/
$sql = <<<SQL
SELECT
  m.full_name, m.staff_number, m.email, m.phone, m.location,
  COALESCE(
    m.official_member_since,
    DATE(m.ajk_approved_at),
    DATE(al_ajk.first_approved_at),
    DATE(al_admin.first_approved_at)
  ) AS official_since,
  m.deactivated_at,
  dr.reason_text AS deactivated_reason
FROM members m
LEFT JOIN (
  SELECT member_id, MIN(`timestamp`) AS first_approved_at
  FROM approval_logs
  WHERE role='ajk' AND action='approved'
  GROUP BY member_id
) al_ajk ON al_ajk.member_id = m.id
LEFT JOIN (
  SELECT member_id, MIN(`timestamp`) AS first_approved_at
  FROM approval_logs
  WHERE role='admin' AND action='approved'
  GROUP BY member_id
) al_admin ON al_admin.member_id = m.id
LEFT JOIN deactivate_reasons dr
  ON dr.id = m.deactivated_reason_id
{$where}
ORDER BY m.full_name ASC
SQL;

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

$filename = 'inactive_users_' . date('Ymd_His') . '.csv';
header('Content-Type: text/csv; charset=utf-8');
header("Content-Disposition: attachment; filename=$filename");
$fp = fopen('php://output', 'w');

fputcsv($fp, ['Full Name','Staff Number','Email','Phone','Location','Official Since','Inactive Date','Inactive Reason']);

while ($row = $res->fetch_assoc()) {
  $official = !empty($row['official_since']) ? date('Y-m-d', strtotime($row['official_since'])) : '';
  $inactive = !empty($row['deactivated_at']) ? date('Y-m-d', strtotime($row['deactivated_at'])) : '';
  fputcsv($fp, [
    $row['full_name'] ?? '',
    $row['staff_number'] ?? '',
    $row['email'] ?? '',
    $row['phone'] ?? '',
    $row['location'] ?? '',
    $official,
    $inactive,
    $row['deactivated_reason'] ?? '',
  ]);
}
fclose($fp);
