<?php
// ─── GET /api/maps/jobcards.php ──────────────────────────────────────────
define('ROOT', dirname(__DIR__, 2));
require_once ROOT . '/core/DB.php';
require_once ROOT . '/core/Response.php';
require_once ROOT . '/core/Auth.php';

Auth::require();
$db = DB::get();

$status  = trim($_GET['status']  ?? '');
$team_id = trim($_GET['team_id'] ?? '');
$search  = trim($_GET['search']  ?? '');

$where  = ["j.drill_co_ordinates IS NOT NULL", "j.drill_co_ordinates != ''"];
$params = [];

if ($status)  { $where[] = 'j.jc_current_status = ?'; $params[] = $status; }
if ($team_id) { $where[] = 'j.team_assigned_id = ?';  $params[] = (int)$team_id; }
if ($search)  {
    $where[] = '(j.client_name LIKE ? OR j.address LIKE ? OR j.jc_no LIKE ?)';
    $params[] = "%$search%"; $params[] = "%$search%"; $params[] = "%$search%";
}

$rows = $db->rows(
    "SELECT j.record_id, j.jc_no, j.date_created, j.action_date,
            j.client_name, j.address, j.contact_number,
            j.jc_current_status, j.water_strike, j.water_flow,
            j.interested_in_pump, j.drill_co_ordinates,
            j.compressor_hours, j.diesel_start, j.diesel_stop,
            j.slip_no, j.estimated_liters,
            t.name AS team_name
     FROM   jobcards j
     LEFT JOIN teams t ON t.record_id = j.team_assigned_id
     WHERE  " . implode(' AND ', $where) . "
     ORDER BY j.record_id DESC
     LIMIT 1000",
    $params
);

// Parse coordinates
$out = [];
foreach ($rows as $r) {
    $raw = trim($r['drill_co_ordinates'] ?? '');

    // Format: "-24.766927, 31.159595"
    if (preg_match('/(-?\d+\.\d+)\s*,\s*(-?\d+\.\d+)/', $raw, $m)) {
        $r['lat'] = (float) $m[1];
        $r['lng'] = (float) $m[2];
        unset($r['drill_co_ordinates']); // keep payload lean
        $r['coords_raw'] = $raw;
        $out[] = $r;
    }
}

Response::ok([
    'jobcards' => $out,
    'total'    => count($out),
]);