<?php
class Trips extends Model
{
    private function s($v): string { return trim((string)$v); }
    private function m($v): ?float { $v = str_replace(',', '', (string)$v); return $v === '' ? null : (float)$v; }
    private function n($v): ?int   { $v = preg_replace('/[^0-9\-]/', '', (string)$v); return $v === '' ? null : (int)$v; }

    /* =========================================================
     *  Read
     * ========================================================= */
    public function listPaginated(int $page, int $perPage, array $filters = []): array
    {
        $where  = ['t.deleted = 0', 't.tenant_id = :tenant'];
        $params = [':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1];

        if (!empty($filters['q'])) {
            $where[] = '(t.trip_no LIKE :q OR t.cargo_description LIKE :q OR d.full_name LIKE :q OR tr.reg_no LIKE :q OR c.fname LIKE :q)';
            $params[':q'] = '%' . $filters['q'] . '%';
        }
        if (!empty($filters['status']))      { $where[] = 't.status = :st';       $params[':st']  = $this->s($filters['status']); }
        if (!empty($filters['driver_id']))   { $where[] = 't.driver_id = :did';   $params[':did'] = (int)$filters['driver_id']; }
        if (!empty($filters['truck_id']))    { $where[] = 't.truck_id = :tid';    $params[':tid'] = (int)$filters['truck_id']; }
        if (!empty($filters['customer_id'])) { $where[] = 't.customer_id = :cid'; $params[':cid'] = (int)$filters['customer_id']; }

        $whereSql = implode(' AND ', $where);
        $db = new DB();

        $countRs = $db->mySelect(
            "SELECT COUNT(*) AS n
               FROM trips t
          LEFT JOIN drivers   d  ON d.c0d  = t.driver_id
          LEFT JOIN trucks    tr ON tr.c0d = t.truck_id
          LEFT JOIN customers c  ON c.c0d  = t.customer_id
              WHERE $whereSql", $params
        );
        $total = (int)($countRs[0]['n'] ?? 0);

        $page    = max(1, $page);
        $perPage = max(1, min(200, $perPage));
        $offset  = ($page - 1) * $perPage;

        $rows = $db->mySelect(
            "SELECT t.c0d, t.trip_no, t.status, t.planned_pickup, t.actual_start, t.actual_end,
                    t.cargo_description, t.weight_kg, t.actual_km, t.planned_km,
                    t.origin_address, t.destination_address,
                    t.invoice_amount, t.quote_amount, t.currency,
                    d.full_name AS driver_name, d.c0d AS driver_id,
                    tr.reg_no   AS truck_reg,   tr.c0d AS truck_id,
                    c.fname     AS customer_name, c.c0d AS customer_id,
                    r.name      AS route_name,   r.c0d  AS route_id
               FROM trips t
          LEFT JOIN drivers    d  ON d.c0d  = t.driver_id
          LEFT JOIN trucks    tr  ON tr.c0d = t.truck_id
          LEFT JOIN customers  c  ON c.c0d  = t.customer_id
          LEFT JOIN routes     r  ON r.c0d  = t.route_id
              WHERE $whereSql
           ORDER BY (t.status IN ('dispatched','in_transit')) DESC,
                    COALESCE(t.actual_start, t.planned_pickup) DESC,
                    t.c0d DESC
              LIMIT $perPage OFFSET $offset",
            $params
        ) ?: [];

        return ['rows' => $rows, 'total' => $total];
    }

    public function byId(int $id): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT t.*,
                    d.full_name AS driver_name, d.phone_1 AS driver_phone,
                    d.photo_url AS driver_photo, d.employee_no AS driver_employee_no,
                    d.licence_no AS driver_licence_no, d.licence_class AS driver_licence_class,
                    d.licence_expires AS driver_licence_expires,
                    tr.reg_no   AS truck_reg, tr.make, tr.model, tr.year AS truck_year,
                    tr.photo_url AS truck_photo, tr.capacity_kg AS truck_capacity_kg,
                    tr.current_odometer AS truck_current_odometer,
                    tr.fuel_tank_liters AS truck_fuel_tank_liters,
                    c.fname     AS customer_name, c.customer_no,
                    r.name      AS route_name, r.code AS route_code, r.distance_km AS route_distance,
                    req.request_no
               FROM trips t
          LEFT JOIN drivers        d  ON d.c0d  = t.driver_id
          LEFT JOIN trucks        tr  ON tr.c0d = t.truck_id
          LEFT JOIN customers      c  ON c.c0d  = t.customer_id
          LEFT JOIN routes         r  ON r.c0d  = t.route_id
          LEFT JOIN trip_requests  req ON req.c0d = t.trip_request_id
              WHERE t.c0d = :id AND t.deleted = 0
              LIMIT 1",
            [':id' => $id]
        );
        return $rs[0] ?? [];
    }

    /** Kanban data for the Vue 3 dispatch board — 4 buckets. */
    public function boardData(): array
    {
        $db = new DB();
        $tenant = defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1;

        $approvedRequests = $db->mySelect(
            "SELECT tr.c0d, tr.request_no, tr.cargo_description, tr.cargo_type,
                    tr.weight_kg, tr.origin_address, tr.destination_address,
                    tr.pickup_date, tr.pickup_time, tr.priority, tr.quote_amount, tr.currency,
                    c.fname AS customer_name, c.c0d AS customer_id,
                    r.name  AS route_name,    r.c0d AS route_id, r.distance_km
               FROM trip_requests tr
          LEFT JOIN customers c ON c.c0d = tr.customer_id
          LEFT JOIN routes    r ON r.c0d = tr.route_id
              WHERE tr.status = 'approved' AND tr.deleted = 0 AND tr.tenant_id = :t
           ORDER BY tr.priority = 'urgent' DESC, tr.pickup_date ASC",
            [':t' => $tenant]
        ) ?: [];

        $selectTrips = "SELECT t.c0d, t.trip_no, t.status, t.cargo_description, t.weight_kg,
                               t.origin_address, t.destination_address,
                               t.planned_pickup, t.actual_start, t.delivered_at,
                               t.currency, t.invoice_amount, t.quote_amount,
                               d.full_name AS driver_name, d.c0d AS driver_id,
                               tr.reg_no   AS truck_reg,   tr.c0d AS truck_id,
                               c.fname     AS customer_name
                          FROM trips t
                     LEFT JOIN drivers   d  ON d.c0d  = t.driver_id
                     LEFT JOIN trucks    tr ON tr.c0d = t.truck_id
                     LEFT JOIN customers c  ON c.c0d  = t.customer_id
                         WHERE t.deleted = 0 AND t.tenant_id = :t AND t.status = :st
                      ORDER BY COALESCE(t.actual_start, t.planned_pickup, t.dispatched_at) ASC";

        $dispatched = $db->mySelect($selectTrips, [':t' => $tenant, ':st' => 'dispatched']) ?: [];
        $inTransit  = $db->mySelect($selectTrips, [':t' => $tenant, ':st' => 'in_transit']) ?: [];
        $delivered  = $db->mySelect($selectTrips, [':t' => $tenant, ':st' => 'delivered'])  ?: [];

        return [
            'approved_requests' => $approvedRequests,
            'dispatched'        => $dispatched,
            'in_transit'        => $inTransit,
            'delivered'         => $delivered,
        ];
    }

    public function nextTripNo(): string
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT trip_no FROM trips WHERE trip_no LIKE 'TRIP-%' ORDER BY c0d DESC LIMIT 1", []
        );
        $last = 0;
        if (!empty($rs[0]['trip_no']) && preg_match('/(\d+)$/', $rs[0]['trip_no'], $mm)) {
            $last = (int)$mm[1];
        }
        return 'TRIP-' . str_pad((string)($last + 1), 5, '0', STR_PAD_LEFT);
    }

    /* =========================================================
     *  Write / Transition
     * ========================================================= */

    /**
     * Dispatch: create a trip from an approved trip request, snapshotting the
     * request's cargo/route/customer. Also updates the parent trip request to
     * 'dispatched' status.
     */
    public function dispatchFromRequest(int $requestId, array $d, int $uid): array
    {
        $driverId = (int)($d['driver_id'] ?? 0);
        $truckId  = (int)($d['truck_id']  ?? 0);
        if (!$driverId || !$truckId)   return ['ok' => false, 'msg' => 'Driver and truck are required.', 'id' => 0];

        $tr = (new TripRequests())->byId($requestId);
        if (!$tr)                              return ['ok' => false, 'msg' => 'Trip request not found.', 'id' => 0];
        if ($tr['status'] !== 'approved')      return ['ok' => false, 'msg' => 'Only approved requests can be dispatched (current: ' . $tr['status'] . ').', 'id' => 0];

        // Combine pickup date + time into planned_pickup DATETIME
        $plannedPickup   = $this->combineDateTime($tr['pickup_date']  , $tr['pickup_time']);
        $plannedDelivery = $this->combineDateTime($tr['delivery_date'], $tr['delivery_time']);

        $tripNo = $this->nextTripNo();
        $db     = new DB();
        $r      = $db->insertUpdate(
            "INSERT INTO trips
                (tenant_id, trip_no, trip_request_id, driver_id, truck_id,
                 customer_id, route_id, cargo_type, cargo_description, weight_kg,
                 origin_address, destination_address,
                 planned_pickup, planned_delivery,
                 planned_km, quote_amount, currency,
                 status, dispatched_by, dispatched_at, notes,
                 created_by, created_on)
             VALUES
                (:tenant, :tn, :rid, :did, :tid,
                 :cid, :routeid, :ct, :cd, :wt,
                 :oa, :da,
                 :pp, :pd,
                 :pkm, :qa, :cur,
                 'dispatched', :uid, NOW(), :notes,
                 :uid, NOW())",
            [
                ':tenant'  => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1,
                ':tn'      => $tripNo,
                ':rid'     => $requestId,
                ':did'     => $driverId,
                ':tid'     => $truckId,
                ':cid'     => !empty($tr['customer_id']) ? (int)$tr['customer_id'] : null,
                ':routeid' => !empty($tr['route_id'])    ? (int)$tr['route_id']    : null,
                ':ct'      => $tr['cargo_type']        ?? null,
                ':cd'      => $tr['cargo_description'] ?? null,
                ':wt'      => isset($tr['weight_kg']) && $tr['weight_kg'] !== '' ? (float)$tr['weight_kg'] : null,
                ':oa'      => $tr['origin_address']      ?? null,
                ':da'      => $tr['destination_address'] ?? null,
                ':pp'      => $plannedPickup,
                ':pd'      => $plannedDelivery,
                ':pkm'     => isset($tr['route_distance']) && $tr['route_distance'] !== '' ? (float)$tr['route_distance'] : null,
                ':qa'      => isset($tr['quote_amount']) && $tr['quote_amount'] !== '' ? (float)$tr['quote_amount'] : null,
                ':cur'     => $tr['currency'] ?? 'TZS',
                ':notes'   => $this->s($d['notes'] ?? ''),
                ':uid'     => $uid,
            ]
        );

        if ($r['success'] < 1) return ['ok' => false, 'msg' => 'Trip creation failed.', 'id' => 0];
        $tripId = (int)$r['last_id'];

        // Log the initial status
        $this->logTransition($tripId, null, 'dispatched', $uid, 'Trip dispatched from request ' . $tr['request_no']);

        // Copy the request's planned stops into execution-side stops on the trip
        $copied = (new TripStops())->copyRequestStopsToTrip($requestId, $tripId, $uid);
        if ($copied > 0) $this->logTransition($tripId, 'dispatched', 'dispatched', $uid, "Copied {$copied} planned stop(s) from request");

        // Mark the source trip request as dispatched (uses its own edited_by columns)
        $db->insertUpdate(
            "UPDATE trip_requests SET status = 'dispatched', edited_by = :uid, edited_on = NOW() WHERE c0d = :rid",
            [':uid' => $uid, ':rid' => $requestId]
        );

        return ['ok' => true, 'msg' => 'Dispatched as ' . $tripNo . '.', 'id' => $tripId, 'trip_no' => $tripNo];
    }

    /** dispatched → in_transit — driver started the trip (records odometer_start) */
    public function start(int $id, array $d, int $uid): array
    {
        $t = $this->byId($id);
        if (!$t) return ['ok' => false, 'msg' => 'Trip not found.'];
        if ($t['status'] !== 'dispatched') return ['ok' => false, 'msg' => 'Only dispatched trips can be started (current: ' . $t['status'] . ').'];

        $odo = $this->n($d['odometer_start'] ?? '') ?? null;
        $db  = new DB();
        $db->insertUpdate(
            "UPDATE trips SET status = 'in_transit',
                              started_by = :uid, started_at = NOW(), actual_start = NOW(),
                              odometer_start = :odo,
                              edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':uid' => $uid, ':odo' => $odo, ':id' => $id]
        );
        $this->logTransition($id, 'dispatched', 'in_transit', $uid, 'Trip started');
        return ['ok' => true, 'msg' => 'Trip in transit.'];
    }

    /** in_transit → delivered — cargo dropped, awaiting close-out paperwork */
    public function markDelivered(int $id, int $uid): array
    {
        $t = $this->byId($id);
        if (!$t) return ['ok' => false, 'msg' => 'Trip not found.'];
        if ($t['status'] !== 'in_transit') return ['ok' => false, 'msg' => 'Only in-transit trips can be marked delivered (current: ' . $t['status'] . ').'];

        $db = new DB();
        $db->insertUpdate(
            "UPDATE trips SET status = 'delivered',
                              delivered_by = :uid, delivered_at = NOW(),
                              edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':uid' => $uid, ':id' => $id]
        );
        $this->logTransition($id, 'in_transit', 'delivered', $uid, 'Cargo delivered');
        return ['ok' => true, 'msg' => 'Marked delivered.'];
    }

    /** delivered → closed — final paperwork: odometer_end, invoice_amount, notes. */
    public function close(int $id, array $d, int $uid): array
    {
        $t = $this->byId($id);
        if (!$t) return ['ok' => false, 'msg' => 'Trip not found.'];
        if (!in_array($t['status'], ['delivered','in_transit'], true))
            return ['ok' => false, 'msg' => 'Only delivered or in-transit trips can be closed (current: ' . $t['status'] . ').'];

        $odoEnd  = $this->n($d['odometer_end'] ?? '');
        $invoice = $this->m($d['invoice_amount'] ?? '');
        $notes   = $this->s($d['notes'] ?? '');

        // Compute actual_km if both odometer readings present
        $actualKm = null;
        if ($odoEnd !== null && $t['odometer_start'] !== null && $odoEnd > (int)$t['odometer_start']) {
            $actualKm = $odoEnd - (int)$t['odometer_start'];
        }

        $db = new DB();
        $db->insertUpdate(
            "UPDATE trips SET status = 'closed',
                              closed_by = :uid, closed_at = NOW(),
                              actual_end = NOW(),
                              odometer_end = :odo,
                              actual_km = :km,
                              invoice_amount = :inv,
                              notes = COALESCE(NULLIF(:notes, ''), notes),
                              edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':uid' => $uid, ':odo' => $odoEnd, ':km' => $actualKm, ':inv' => $invoice, ':notes' => $notes, ':id' => $id]
        );

        // Also bump the truck's current_odometer so it's up-to-date for the next trip
        if ($odoEnd !== null) {
            $db->insertUpdate(
                "UPDATE trucks SET current_odometer = :odo, edited_by = :uid, edited_on = NOW()
                  WHERE c0d = :tid AND current_odometer < :odo",
                [':odo' => $odoEnd, ':uid' => $uid, ':tid' => (int)$t['truck_id']]
            );
        }

        $this->logTransition($id, $t['status'], 'closed', $uid, 'Trip closed' . ($invoice !== null ? ' · invoice ' . number_format($invoice, 2) : ''));
        return ['ok' => true, 'msg' => 'Trip closed.'];
    }

    public function cancel(int $id, string $reason, int $uid): array
    {
        $t = $this->byId($id);
        if (!$t) return ['ok' => false, 'msg' => 'Trip not found.'];
        if (in_array($t['status'], ['closed','cancelled'], true))
            return ['ok' => false, 'msg' => 'Cannot cancel — trip is ' . $t['status'] . '.'];

        $db = new DB();
        $db->insertUpdate(
            "UPDATE trips SET status = 'cancelled',
                              cancelled_by = :uid, cancelled_at = NOW(),
                              cancel_reason = :rr,
                              edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':uid' => $uid, ':rr' => $this->s($reason), ':id' => $id]
        );
        $this->logTransition($id, $t['status'], 'cancelled', $uid, $this->s($reason) ?: 'Cancelled');
        return ['ok' => true, 'msg' => 'Trip cancelled.'];
    }

    public function softDelete(int $id, int $uid): array
    {
        $db = new DB();
        $db->insertUpdate(
            "UPDATE trips SET deleted = 1, edited_by = :uid, edited_on = NOW() WHERE c0d = :id",
            [':uid' => $uid, ':id' => $id]
        );
        return ['ok' => true];
    }

    /* =========================================================
     *  Helpers
     * ========================================================= */
    public function statusHistory(int $tripId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT l.*, u.full_name AS actor_name
               FROM trip_status_log l
          LEFT JOIN users u ON u.c0d = l.changed_by
              WHERE l.trip_id = :id
           ORDER BY l.changed_at ASC, l.c0d ASC",
            [':id' => $tripId]
        ) ?: [];
    }

    private function logTransition(int $tripId, ?string $from, string $to, int $uid, string $note = ''): void
    {
        (new DB())->insertUpdate(
            "INSERT INTO trip_status_log (trip_id, from_status, to_status, changed_by, changed_at, note)
             VALUES (:tid, :from, :to, :uid, NOW(), :note)",
            [':tid' => $tripId, ':from' => $from, ':to' => $to, ':uid' => $uid, ':note' => $note]
        );
    }

    private function combineDateTime(?string $date, ?string $time): ?string
    {
        $date = trim((string)$date); $time = trim((string)$time);
        if ($date === '') return null;
        return $date . ' ' . ($time !== '' ? $time : '00:00:00');
    }
}
