<?php
// ─── GET /api/jobcards/report.php ────────────────────────────────────────
// Returns completed jobcards with full detail for the report view
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();

$dateFrom = trim($_GET['date_from'] ?? '');
$dateTo   = trim($_GET['date_to']   ?? '');
$teamId   = (int) ($_GET['team_id'] ?? 0);
$jcNo     = (int) ($_GET['jc_no']   ?? 0);

// Default to-date to today if not provided, but no from-date default
if (!$dateTo && !$jcNo) $dateTo = date('Y-m-d');

$where  = ["j.jc_current_status = 'COMPLETE'"];
$params = [];

if ($dateFrom) { $where[] = 'DATE(j.date_created) >= ?'; $params[] = $dateFrom; }
if ($dateTo)   { $where[] = 'DATE(j.date_created) <= ?'; $params[] = $dateTo;   }
if ($teamId)   { $where[] = 'j.team_assigned_id = ?';    $params[] = $teamId;   }
if ($jcNo)     { $where[] = 'j.jc_no = ?';               $params[] = $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.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
     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  " . implode(' AND ', $where) . "
     ORDER BY j.jc_no DESC
     LIMIT 300",
    $params
);

foreach ($rows as &$jc) {
    $jid = $jc['record_id'];

    $jc['payments'] = $db->rows(
        "SELECT jp.amount, jp.payment_method, jp.date_time, u.username
         FROM   jobcard_payments jp
         LEFT JOIN users u ON u.record_id = jp.user_id
         WHERE  jp.jobcard_id = ? ORDER BY jp.date_time ASC",
        [$jid]
    );
    $jc['total_paid'] = array_sum(array_column($jc['payments'], 'amount'));

    $timeline = $db->rows(
        "SELECT type, meters FROM jobcard_timeline WHERE jobcard_id = ?",
        [$jid]
    );
    $totals = ['drilling'=>0,'rieming'=>0,'casing'=>0,'blasting'=>0];
    foreach ($timeline as $entry) {
        $type = strtoupper($entry['type']);
        $m    = (float)($entry['meters'] ?? 0);
        if (in_array($type, ['DRILLING_STOP','DRILLING_PAUSE']))                             $totals['drilling'] += $m;
        if (in_array($type, ['RIEMING_STOP','RIEM_STOP','RIEMING_PAUSED','REIMING_STOP']))   $totals['rieming']  += $m;
        if (in_array($type, ['CASING_STOP','CASING_PAUSE']))                                 $totals['casing']   += $m;
        if (in_array($type, ['BLASTING_STOP','BLASTING_PAUSE']))                             $totals['blasting'] += $m;
    }
    $jc['totals'] = $totals;
}
unset($jc);

Response::ok($rows);