<?php
class TripRequests 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;
    }

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

        if (!empty($filters['q'])) {
            $where[] = '(tr.request_no LIKE :q OR tr.cargo_description LIKE :q OR tr.origin_address LIKE :q OR tr.destination_address LIKE :q OR c.fname LIKE :q)';
            $params[':q'] = '%' . $filters['q'] . '%';
        }
        if (!empty($filters['status']))      { $where[] = 'tr.status      = :st';  $params[':st']  = $this->s($filters['status']); }
        if (!empty($filters['priority']))    { $where[] = 'tr.priority    = :pr';  $params[':pr']  = $this->s($filters['priority']); }
        if (!empty($filters['customer_id'])) { $where[] = 'tr.customer_id = :cid'; $params[':cid'] = (int)$filters['customer_id']; }
        if (!empty($filters['from_date']))   { $where[] = 'tr.pickup_date >= :fd'; $params[':fd']  = $this->s($filters['from_date']); }
        if (!empty($filters['to_date']))     { $where[] = 'tr.pickup_date <= :td'; $params[':td']  = $this->s($filters['to_date']); }

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

        $countRs = $db->mySelect(
            "SELECT COUNT(*) AS n
               FROM trip_requests tr
          LEFT JOIN customers c ON c.c0d = tr.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 tr.c0d, tr.request_no, tr.customer_id, tr.cargo_type,
                    tr.cargo_description, tr.weight_kg, tr.origin_address,
                    tr.destination_address, tr.pickup_date, tr.quote_amount,
                    tr.currency, tr.priority, tr.status, tr.requested_at,
                    c.fname AS customer_name, c.customer_no,
                    r.name  AS route_name, r.code AS route_code
               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 $whereSql
           ORDER BY (tr.status='pending_review') DESC, tr.priority='urgent' DESC,
                    tr.pickup_date ASC, tr.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 tr.*, c.fname AS customer_name, c.customer_no, c.phone_1 AS customer_phone,
                    c.customer_poc, c.customer_poc_phone,
                    r.name AS route_name, r.code AS route_code, r.route_type,
                    r.origin_city AS route_origin, r.destination_city AS route_destination,
                    r.distance_km AS route_distance
               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.c0d = :id AND tr.deleted = 0
              LIMIT 1",
            [':id' => $id]
        );
        return $rs[0] ?? [];
    }

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

    public function create(array $d, int $uid): array
    {
        $desc = $this->s($d['cargo_description'] ?? '');
        if ($desc === '' && empty($d['customer_id'])) {
            return ['ok' => false, 'msg' => 'Cargo description or customer is required.', 'id' => 0];
        }

        $db = new DB();
        $r  = $db->insertUpdate(
            "INSERT INTO trip_requests
                (tenant_id, request_no, customer_id, route_id, cargo_type, cargo_description,
                 weight_kg, volume_m3, packages_count,
                 origin_address, destination_address,
                 pickup_date, pickup_time, delivery_date, delivery_time,
                 quote_amount, currency, priority, status,
                 requested_by, requested_at, notes,
                 created_by, created_on)
             VALUES
                (:tenant, :rn, :cid, :rid, :ct, :cd,
                 :wt, :vol, :pk,
                 :oa, :da,
                 :pd, :pt, :dd, :dt,
                 :qa, :cur, :prio, :status,
                 :uid, NOW(), :notes,
                 :uid, NOW())",
            [
                ':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1,
                ':rn'     => $this->nextRequestNo(),
                ':cid'    => !empty($d['customer_id']) ? (int)$d['customer_id'] : null,
                ':rid'    => !empty($d['route_id'])    ? (int)$d['route_id']    : null,
                ':ct'     => $this->s(($d['cargo_type'] ?? '') ?: 'general'),
                ':cd'     => $desc,
                ':wt'     => $this->m($d['weight_kg'] ?? ''),
                ':vol'    => $this->m($d['volume_m3'] ?? ''),
                ':pk'     => !empty($d['packages_count']) ? (int)$d['packages_count'] : null,
                ':oa'     => $this->s($d['origin_address']      ?? ''),
                ':da'     => $this->s($d['destination_address'] ?? ''),
                ':pd'     => !empty($d['pickup_date'])   ? $this->s($d['pickup_date'])   : null,
                ':pt'     => !empty($d['pickup_time'])   ? $this->s($d['pickup_time'])   : null,
                ':dd'     => !empty($d['delivery_date']) ? $this->s($d['delivery_date']) : null,
                ':dt'     => !empty($d['delivery_time']) ? $this->s($d['delivery_time']) : null,
                ':qa'     => $this->m($d['quote_amount'] ?? ''),
                ':cur'    => $this->s(($d['currency'] ?? '') ?: (defined('DEFAULT_CURRENCY') ? DEFAULT_CURRENCY : 'TZS')),
                ':prio'   => $this->s(($d['priority'] ?? '') ?: 'normal'),
                ':status' => $this->s(($d['status'] ?? '')   ?: 'draft'),
                ':notes'  => $this->s($d['notes'] ?? ''),
                ':uid'    => $uid,
            ]
        );
        return ['ok' => $r['success'] > 0, 'msg' => $r['success'] > 0 ? 'Trip request created.' : 'Save failed.', 'id' => (int)$r['last_id']];
    }

    public function update(int $id, array $d, int $uid): array
    {
        if (!$this->byId($id)) return ['ok' => false, 'msg' => 'Trip request not found.', 'id' => 0];

        $db = new DB();
        $db->insertUpdate(
            "UPDATE trip_requests SET
                customer_id = :cid, route_id = :rid, cargo_type = :ct, cargo_description = :cd,
                weight_kg = :wt, volume_m3 = :vol, packages_count = :pk,
                origin_address = :oa, destination_address = :da,
                pickup_date = :pd, pickup_time = :pt, delivery_date = :dd, delivery_time = :dt,
                quote_amount = :qa, currency = :cur, priority = :prio,
                notes = :notes,
                edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id AND deleted = 0",
            [
                ':cid'   => !empty($d['customer_id']) ? (int)$d['customer_id'] : null,
                ':rid'   => !empty($d['route_id'])    ? (int)$d['route_id']    : null,
                ':ct'    => $this->s(($d['cargo_type'] ?? '') ?: 'general'),
                ':cd'    => $this->s($d['cargo_description'] ?? ''),
                ':wt'    => $this->m($d['weight_kg'] ?? ''),
                ':vol'   => $this->m($d['volume_m3'] ?? ''),
                ':pk'    => !empty($d['packages_count']) ? (int)$d['packages_count'] : null,
                ':oa'    => $this->s($d['origin_address']      ?? ''),
                ':da'    => $this->s($d['destination_address'] ?? ''),
                ':pd'    => !empty($d['pickup_date'])   ? $this->s($d['pickup_date'])   : null,
                ':pt'    => !empty($d['pickup_time'])   ? $this->s($d['pickup_time'])   : null,
                ':dd'    => !empty($d['delivery_date']) ? $this->s($d['delivery_date']) : null,
                ':dt'    => !empty($d['delivery_time']) ? $this->s($d['delivery_time']) : null,
                ':qa'    => $this->m($d['quote_amount'] ?? ''),
                ':cur'   => $this->s(($d['currency'] ?? '') ?: 'TZS'),
                ':prio'  => $this->s(($d['priority'] ?? '') ?: 'normal'),
                ':notes' => $this->s($d['notes'] ?? ''),
                ':uid'   => $uid,
                ':id'    => $id,
            ]
        );
        return ['ok' => true, 'msg' => 'Trip request updated.', 'id' => $id];
    }

    /**
     * Status transitions:
     *   draft → pending_review  via submit()
     *   pending_review → approved  via approve()
     *   pending_review → rejected  via reject()
     *   any-open → cancelled  via cancel()
     * Once dispatched, only Trip lifecycle actions can change it.
     */
    public function submit(int $id, int $uid): array
    {
        $r = $this->byId($id);
        if (!$r) return ['ok' => false, 'msg' => 'Not found.'];
        if ($r['status'] !== 'draft') return ['ok' => false, 'msg' => 'Only draft requests can be submitted.'];
        $db = new DB();
        $db->insertUpdate(
            "UPDATE trip_requests SET status = 'pending_review', edited_by = :uid, edited_on = NOW() WHERE c0d = :id",
            [':uid' => $uid, ':id' => $id]
        );
        return ['ok' => true, 'msg' => 'Submitted for review.'];
    }

    public function approve(int $id, int $uid): array
    {
        $r = $this->byId($id);
        if (!$r) return ['ok' => false, 'msg' => 'Not found.'];
        if ($r['status'] !== 'pending_review') return ['ok' => false, 'msg' => 'Only pending requests can be approved.'];
        $db = new DB();
        $db->insertUpdate(
            "UPDATE trip_requests SET status = 'approved', approved_by = :uid, approved_at = NOW(),
                                       edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':uid' => $uid, ':id' => $id]
        );
        return ['ok' => true, 'msg' => 'Approved.'];
    }

    public function reject(int $id, int $uid, string $reason): array
    {
        $r = $this->byId($id);
        if (!$r) return ['ok' => false, 'msg' => 'Not found.'];
        if (!in_array($r['status'], ['pending_review','draft'], true)) return ['ok' => false, 'msg' => 'Only draft/pending requests can be rejected.'];
        $db = new DB();
        $db->insertUpdate(
            "UPDATE trip_requests SET status = 'rejected', rejected_by = :uid, rejected_at = NOW(),
                                       reject_reason = :rr, edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':uid' => $uid, ':rr' => $this->s($reason), ':id' => $id]
        );
        return ['ok' => true, 'msg' => 'Rejected.'];
    }

    public function cancel(int $id, int $uid): array
    {
        $r = $this->byId($id);
        if (!$r) return ['ok' => false, 'msg' => 'Not found.'];
        if (in_array($r['status'], ['dispatched','cancelled','rejected'], true)) return ['ok' => false, 'msg' => 'Cannot cancel — status is ' . $r['status'] . '.'];
        $db = new DB();
        $db->insertUpdate(
            "UPDATE trip_requests SET status = 'cancelled', edited_by = :uid, edited_on = NOW() WHERE c0d = :id",
            [':uid' => $uid, ':id' => $id]
        );
        return ['ok' => true, 'msg' => 'Cancelled.'];
    }

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