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

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

// Drivers/Install only see their own team's jobcards
$teamFilter = '';
$params     = [];

if (in_array($role, ['driver', 'install'])) {
    $teamId = $db->scalar(
        "SELECT record_id FROM teams WHERE name = ?", [$user['team_id']]
    );
    if ($teamId) {
        $teamFilter = ' AND j.team_assigned_id = ?';
        $params[]   = (int) $teamId;
    }
}

// Optional GET filters
$statusFilter = trim($_GET['status'] ?? '');
$jcNo         = trim($_GET['jc_no'] ?? '');

if ($statusFilter) {
    $teamFilter .= ' AND j.jc_current_status = ?';
    $params[]    = $statusFilter;
}
if ($jcNo) {
    $teamFilter .= ' AND j.jc_no = ?';
    $params[]    = (int) $jcNo;
}

$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.other_number, j.alternate_number,
            j.jc_current_status, j.water_strike, j.water_flow,
            j.interested_in_pump, j.slip_no,
            t.name AS team_name,
            u.username AS created_by,
            -- Derive live status from the latest timeline entry
            COALESCE(
              (SELECT tl.type
               FROM   jobcard_timeline tl
               WHERE  tl.jobcard_id = j.record_id
                 AND  tl.type NOT IN ('OTHER')
               ORDER BY tl.record_id DESC
               LIMIT 1),
              j.jc_current_status
            ) AS live_status,
            -- Current bit serial from last start/resume that isn't followed by a stop
            (SELECT tl2.serial_number
             FROM   jobcard_timeline tl2
             WHERE  tl2.jobcard_id = j.record_id
               AND  tl2.serial_number IS NOT NULL
               AND  tl2.serial_number != ''
             ORDER BY tl2.record_id DESC
             LIMIT 1
            ) AS current_serial,
            -- Total meters drilled
            COALESCE(
              (SELECT SUM(tl3.meters)
               FROM   jobcard_timeline tl3
               WHERE  tl3.jobcard_id = j.record_id
                 AND  tl3.meters > 0),
              0
            ) AS total_meters
     FROM   jobcards j
     LEFT JOIN teams t ON t.record_id = j.team_assigned_id
     LEFT JOIN users u ON u.record_id = j.user_id
     WHERE  1=1 $teamFilter
     ORDER BY j.record_id DESC
     LIMIT 300",
    $params
);

Response::ok($rows);