<?php
// Live dashboard stats — polled by index.php every few seconds.
session_start();
if (!isset($_SESSION['admin_id'])) { http_response_code(401); exit; }
require_once __DIR__ . '/../config/db.php';
header('Content-Type: application/json');

$now = time();

// ── Headline counters ─────────────────────────────────────────────────────────
$totalParcels = (int)$pdo->query("SELECT COUNT(*) FROM parcels")->fetchColumn();
$doneParcels  = (int)$pdo->query("SELECT COUNT(*) FROM parcels WHERE survey_status = 'completed'")->fetchColumn();
$inProgress   = (int)$pdo->query("SELECT COUNT(*) FROM parcels WHERE survey_status = 'in_progress'")->fetchColumn();
$totalSurveys = (int)$pdo->query("SELECT COUNT(*) FROM surveys")->fetchColumn();
$todaySurveys = (int)$pdo->query("SELECT COUNT(*) FROM surveys WHERE DATE(survey_date) = CURDATE()")->fetchColumn();
$pendingSync  = (int)$pdo->query("SELECT COUNT(*) FROM surveys WHERE sync_status = 'local'")->fetchColumn();
$pct = $totalParcels ? round($doneParcels / $totalParcels * 100, 1) : 0;

// ── Per-surveyor today productivity + live presence ───────────────────────────
// Surveys done today per surveyor
$todayBy = [];
$q = $pdo->query("
    SELECT surveyor_id, COUNT(*) AS c
    FROM surveys
    WHERE DATE(survey_date) = CURDATE()
    GROUP BY surveyor_id
");
foreach ($q->fetchAll() as $r) $todayBy[(int)$r['surveyor_id']] = (int)$r['c'];

// Latest GPS ping per surveyor (last 24h) for presence
$lastPing = [];
$q = $pdo->query("
    SELECT ul.surveyor_id, ul.recorded_at, ul.battery_level
    FROM user_locations ul
    INNER JOIN (
        SELECT surveyor_id, MAX(recorded_at) AS max_t
        FROM user_locations
        WHERE recorded_at > NOW() - INTERVAL 24 HOUR
        GROUP BY surveyor_id
    ) latest ON ul.surveyor_id = latest.surveyor_id AND ul.recorded_at = latest.max_t
");
foreach ($q->fetchAll() as $r) {
    $lastPing[(int)$r['surveyor_id']] = [
        'ts'      => strtotime($r['recorded_at']),
        'battery' => (int)$r['battery_level'],
    ];
}

$surveyors = $pdo->query("
    SELECT id, name FROM surveyors
    WHERE role = 'surveyor' AND is_active = 1
    ORDER BY name
")->fetchAll();

$team = [];
$activeNow = 0;
foreach ($surveyors as $sv) {
    $id   = (int)$sv['id'];
    $ping = $lastPing[$id] ?? null;
    $diff = $ping ? ($now - $ping['ts']) : null;
    $online = ($diff !== null && $diff < 300);   // < 5 min = online
    if ($online) $activeNow++;
    if ($diff === null)        $seen = 'no GPS today';
    elseif ($diff < 60)        $seen = 'just now';
    elseif ($diff < 3600)      $seen = floor($diff/60).' min ago';
    elseif ($diff < 86400)     $seen = floor($diff/3600).'h ago';
    else                       $seen = date('M j H:i', $ping['ts']);
    $team[] = [
        'id'       => $id,
        'name'     => $sv['name'],
        'today'    => $todayBy[$id] ?? 0,
        'online'   => $online,
        'recent'   => ($diff !== null && $diff < 1800),
        'last_seen'=> $seen,
        'battery'  => $ping['battery'] ?? null,
    ];
}
// Sort: most productive today first
usort($team, fn($a, $b) => $b['today'] <=> $a['today']);

// ── Recent submissions feed ───────────────────────────────────────────────────
$recent = $pdo->query("
    SELECT s.id, s.survey_date, s.owner_name, s.ward_no, s.created_at,
           p.parcel_no, p.zone, sv.name AS by_name
    FROM surveys s
    LEFT JOIN parcels p    ON p.id = s.parcel_id
    LEFT JOIN surveyors sv ON sv.id = s.surveyor_id
    ORDER BY s.created_at DESC LIMIT 12
")->fetchAll();
$recentOut = [];
foreach ($recent as $r) {
    $ts = strtotime($r['created_at'] ?? '') ?: 0;
    $d  = $ts ? $now - $ts : null;
    if      ($d === null)  $ago = '';
    elseif  ($d < 60)      $ago = 'just now';
    elseif  ($d < 3600)    $ago = floor($d/60).'m ago';
    elseif  ($d < 86400)   $ago = floor($d/3600).'h ago';
    else                   $ago = date('M j', $ts);
    $recentOut[] = [
        'id'        => (int)$r['id'],
        'parcel_no' => $r['parcel_no'] ?? '—',
        'zone'      => $r['zone'] ?? '',
        'ward_no'   => $r['ward_no'] ?? '',
        'owner'     => $r['owner_name'] ?? '',
        'by'        => $r['by_name'] ?? '',
        'ago'       => $ago,
    ];
}

echo json_encode([
    'server_ts'     => $now,
    'pct'           => $pct,
    'total_parcels' => $totalParcels,
    'done_parcels'  => $doneParcels,
    'in_progress'   => $inProgress,
    'remaining'     => $totalParcels - $doneParcels,
    'total_surveys' => $totalSurveys,
    'today_surveys' => $todaySurveys,
    'pending_sync'  => $pendingSync,
    'active_now'    => $activeNow,
    'team'          => $team,
    'recent'        => $recentOut,
]);
