<?php
/**
 * WorkRequests — the field-to-workshop pipeline.
 *
 * Driver reports → Ops Manager approves → Procurement sources → Fleet Manager
 * converts to Work Order → WO closes → WR auto-completes.
 *
 * The convertToWo() method is the interesting bit: it creates a work_orders
 * row from the WR (truck, vendor, kind mapped from urgency), copies each WR
 * item to work_order_parts, and cross-links wr.wo_id and wo.source_wr_id so
 * the two records know about each other.
 */
class WorkRequests extends Model
{
    private function s($v): string { return trim((string)$v); }
    private function m($v): float  { return (float)str_replace(',', '', (string)$v); }
    private function n($v): ?int   { $v = preg_replace('/[^0-9\-]/', '', (string)$v); return $v === '' ? null : (int)$v; }
    private function nn(int $v): ?int { return $v > 0 ? $v : null; }

    private const URGENCY_TO_WO_KIND = [
        'low'        => 'planned',
        'normal'     => 'planned',
        'high'       => 'breakdown',
        'breakdown'  => 'breakdown',
    ];

    /** WR-item.category → work_order_parts.category (identical set, straight pass-through). */
    private const ITEM_CAT_TO_WO_PART_CAT = [
        'spare_parts' => 'spare_parts',
        'tyres'       => 'tyres',
        'batteries'   => 'batteries',
        'lubricants'  => 'lubricants',
        'consumables' => 'consumables',
    ];

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

        if (!empty($filters['q'])) {
            $where[] = '(w.wr_no LIKE :q OR w.symptom LIKE :q OR w.fault_location LIKE :q OR t.reg_no LIKE :q OR d.full_name LIKE :q)';
            $params[':q'] = '%' . $filters['q'] . '%';
        }
        if (!empty($filters['status']))   { $where[] = 'w.status   = :st';   $params[':st']  = $this->s($filters['status']); }
        if (!empty($filters['urgency']))  { $where[] = 'w.urgency  = :u';    $params[':u']   = $this->s($filters['urgency']); }
        if (!empty($filters['truck_id'])) { $where[] = 'w.truck_id = :tid';  $params[':tid'] = (int)$filters['truck_id']; }
        if (!empty($filters['from_date'])){ $where[] = 'DATE(w.reported_at) >= :fd'; $params[':fd'] = $this->s($filters['from_date']); }
        if (!empty($filters['to_date']))  { $where[] = 'DATE(w.reported_at) <= :td'; $params[':td'] = $this->s($filters['to_date']); }

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

        $countRs = $db->mySelect(
            "SELECT COUNT(*) AS n
               FROM work_requests w
          LEFT JOIN trucks   t ON t.c0d = w.truck_id
          LEFT JOIN drivers  d ON d.c0d = w.driver_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 w.c0d, w.wr_no, w.status, w.urgency, w.reported_at, w.symptom,
                    w.estimated_cost, w.approved_amount, w.currency,
                    w.wo_id,
                    t.reg_no AS truck_reg, t.make, t.model,
                    d.full_name AS driver_name,
                    wo.wo_no AS wo_no
               FROM work_requests w
          LEFT JOIN trucks   t   ON t.c0d = w.truck_id
          LEFT JOIN drivers  d   ON d.c0d = w.driver_id
          LEFT JOIN work_orders wo ON wo.c0d = w.wo_id
              WHERE $whereSql
           ORDER BY w.status IN ('submitted','approved','sourcing','ready') DESC,
                    w.urgency = 'breakdown' DESC,
                    w.urgency = 'high' DESC,
                    w.reported_at DESC
              LIMIT $perPage OFFSET $offset",
            $params
        ) ?: [];

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

    public function statusCounts(): array
    {
        $rows = (new DB())->mySelect(
            "SELECT status, COUNT(*) AS n FROM work_requests WHERE deleted = 0 AND tenant_id = :t GROUP BY status",
            [':t' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1]
        ) ?: [];
        $out = ['draft'=>0,'submitted'=>0,'approved'=>0,'sourcing'=>0,'ready'=>0,'wo_created'=>0,'completed'=>0,'rejected'=>0,'cancelled'=>0];
        foreach ($rows as $r) $out[$r['status']] = (int)$r['n'];
        return $out;
    }

    public function byId(int $id): array
    {
        $rs = (new DB())->mySelect(
            "SELECT w.*,
                    t.reg_no AS truck_reg, t.make, t.model, t.current_odometer,
                    d.full_name AS driver_name, d.phone_1 AS driver_phone,
                    v.vendor_name AS sourced_vendor_name,
                    wo.wo_no AS wo_no
               FROM work_requests w
          LEFT JOIN trucks   t  ON t.c0d = w.truck_id
          LEFT JOIN drivers  d  ON d.c0d = w.driver_id
          LEFT JOIN vendors  v  ON v.c0d = w.sourced_vendor_id
          LEFT JOIN work_orders wo ON wo.c0d = w.wo_id
              WHERE w.c0d = :id AND w.deleted = 0
              LIMIT 1",
            [':id' => $id]
        );
        return $rs[0] ?? [];
    }

    public function transitionHistory(int $wrId): array
    {
        return (new DB())->mySelect(
            "SELECT * FROM work_request_transitions WHERE work_request_id = :id ORDER BY action_at ASC, c0d ASC",
            [':id' => $wrId]
        ) ?: [];
    }

    public function nextWrNo(): string
    {
        $rs = (new DB())->mySelect(
            "SELECT wr_no FROM work_requests WHERE wr_no LIKE 'WR-%' ORDER BY c0d DESC LIMIT 1", []
        );
        $last = (!empty($rs) && preg_match('/(\d+)$/', $rs[0]['wr_no'], $m)) ? (int)$m[1] : 0;
        return 'WR-' . str_pad((string)($last + 1), 5, '0', STR_PAD_LEFT);
    }

    /* =========================================================
     *  Header CRUD
     * ========================================================= */
    public function create(array $d, int $uid): array
    {
        $truckId = (int)($d['truck_id'] ?? 0);
        if (!$truckId) return ['ok' => false, 'msg' => 'Truck is required.', 'id' => 0];
        $symptom = $this->s($d['symptom'] ?? '');
        if ($symptom === '') return ['ok' => false, 'msg' => 'Please describe what is wrong.', 'id' => 0];

        $r = (new DB())->insertUpdate(
            "INSERT INTO work_requests
                (tenant_id, wr_no, truck_id, driver_id, urgency,
                 reported_at, reported_by, truck_odometer, fault_location,
                 symptom, currency, status,
                 created_by, created_on)
             VALUES
                (:tenant, :wr, :tid, :did, :urg,
                 NOW(), :uid, :odo, :loc,
                 :sym, :cur, :status,
                 :uid, NOW())",
            [
                ':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1,
                ':wr'     => $this->nextWrNo(),
                ':tid'    => $truckId,
                ':did'    => $this->nn((int)($d['driver_id'] ?? 0)),
                ':urg'    => $this->s(($d['urgency'] ?? '') ?: 'normal'),
                ':uid'    => $uid,
                ':odo'    => $this->n($d['truck_odometer'] ?? ''),
                ':loc'    => $this->s($d['fault_location'] ?? ''),
                ':sym'    => $symptom,
                ':cur'    => $this->s(($d['currency'] ?? '') ?: 'TZS'),
                ':status' => $this->s(($d['status'] ?? '') ?: 'draft'),
            ]
        );
        $id = (int)$r['last_id'];
        if ($id > 0) $this->logTransition($id, null, $d['status'] ?? 'draft', $uid, '', 'Request created');
        return ['ok' => $r['success'] > 0, 'msg' => $r['success'] > 0 ? 'Work request created.' : 'Save failed.', 'id' => $id];
    }

    public function update(int $id, array $d, int $uid): array
    {
        $w = $this->byId($id);
        if (!$w) return ['ok' => false, 'msg' => 'Not found.', 'id' => 0];
        if (in_array($w['status'], ['wo_created','completed','rejected','cancelled'], true)) {
            return ['ok' => false, 'msg' => 'Cannot edit — status is ' . $w['status'] . '.', 'id' => 0];
        }
        (new DB())->insertUpdate(
            "UPDATE work_requests SET
                truck_id = :tid, driver_id = :did, urgency = :urg,
                truck_odometer = :odo, fault_location = :loc, symptom = :sym,
                notes = :notes, currency = :cur,
                edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [
                ':tid'  => (int)($d['truck_id'] ?? $w['truck_id']),
                ':did'  => $this->nn((int)($d['driver_id'] ?? 0)),
                ':urg'  => $this->s(($d['urgency'] ?? '') ?: 'normal'),
                ':odo'  => $this->n($d['truck_odometer'] ?? ''),
                ':loc'  => $this->s($d['fault_location'] ?? ''),
                ':sym'  => $this->s($d['symptom'] ?? ''),
                ':notes'=> $this->s($d['notes'] ?? ''),
                ':cur'  => $this->s(($d['currency'] ?? '') ?: $w['currency']),
                ':uid'  => $uid,
                ':id'   => $id,
            ]
        );
        return ['ok' => true, 'msg' => 'Work request updated.', 'id' => $id];
    }

    /* =========================================================
     *  Lifecycle transitions
     * ========================================================= */
    public function submit(int $id, int $uid, string $uname): array
    {
        $w = $this->byId($id);
        if (!$w) return ['ok' => false, 'msg' => 'Not found.'];
        if ($w['status'] !== 'draft') return ['ok' => false, 'msg' => 'Only draft requests can be submitted.'];
        (new DB())->insertUpdate(
            "UPDATE work_requests SET status = 'submitted', submitted_by = :uid, submitted_at = NOW(),
                                       edited_by = :uid, edited_on = NOW() WHERE c0d = :id",
            [':uid' => $uid, ':id' => $id]
        );
        $this->logTransition($id, 'draft', 'submitted', $uid, $uname, 'Submitted for Ops Manager approval');
        return ['ok' => true, 'msg' => 'Submitted for approval.'];
    }

    public function approve(int $id, ?float $approvedAmount, string $notes, int $uid, string $uname): array
    {
        $w = $this->byId($id);
        if (!$w) return ['ok' => false, 'msg' => 'Not found.'];
        if ($w['status'] !== 'submitted') return ['ok' => false, 'msg' => 'Only submitted requests can be approved.'];
        $amt = $approvedAmount ?? (float)$w['estimated_cost'];
        (new DB())->insertUpdate(
            "UPDATE work_requests SET status = 'approved',
                                       approved_by = :uid, approved_at = NOW(),
                                       approved_amount = :amt, approval_notes = :nt,
                                       edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':uid' => $uid, ':amt' => $amt, ':nt' => $this->s($notes), ':id' => $id]
        );
        $this->logTransition($id, 'submitted', 'approved', $uid, $uname, 'Approved up to ' . $w['currency'] . ' ' . number_format($amt, 2));
        return ['ok' => true, 'msg' => 'Approved · procurement can now source parts.'];
    }

    public function reject(int $id, string $reason, int $uid, string $uname): array
    {
        $w = $this->byId($id);
        if (!$w) return ['ok' => false, 'msg' => 'Not found.'];
        if (!in_array($w['status'], ['draft','submitted','approved'], true)) {
            return ['ok' => false, 'msg' => 'Cannot reject at status ' . $w['status'] . '.'];
        }
        (new DB())->insertUpdate(
            "UPDATE work_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]
        );
        $this->logTransition($id, $w['status'], 'rejected', $uid, $uname, $this->s($reason) ?: 'Rejected');
        return ['ok' => true, 'msg' => 'Rejected.'];
    }

    public function startSourcing(int $id, int $uid, string $uname): array
    {
        $w = $this->byId($id);
        if (!$w) return ['ok' => false, 'msg' => 'Not found.'];
        if ($w['status'] !== 'approved') return ['ok' => false, 'msg' => 'Only approved requests can enter sourcing.'];
        (new DB())->insertUpdate(
            "UPDATE work_requests SET status = 'sourcing',
                                       sourcing_by = :uid, sourcing_started_at = NOW(),
                                       edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':uid' => $uid, ':id' => $id]
        );
        $this->logTransition($id, 'approved', 'sourcing', $uid, $uname, 'Procurement started sourcing');
        return ['ok' => true, 'msg' => 'Sourcing started.'];
    }

    public function markReady(int $id, array $d, int $uid, string $uname): array
    {
        $w = $this->byId($id);
        if (!$w) return ['ok' => false, 'msg' => 'Not found.'];
        if (!in_array($w['status'], ['sourcing','approved'], true)) {
            return ['ok' => false, 'msg' => 'Only sourcing/approved requests can be marked ready.'];
        }
        (new DB())->insertUpdate(
            "UPDATE work_requests SET status = 'ready',
                                       ready_at = NOW(), ready_by = :uid,
                                       sourcing_notes = COALESCE(NULLIF(:notes,''), sourcing_notes),
                                       sourced_vendor_id = :vid,
                                       edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [
                ':uid'   => $uid,
                ':notes' => $this->s($d['sourcing_notes'] ?? ''),
                ':vid'   => $this->nn((int)($d['sourced_vendor_id'] ?? 0)),
                ':id'    => $id,
            ]
        );
        $this->logTransition($id, $w['status'], 'ready', $uid, $uname, 'Parts ready · workshop to schedule');
        return ['ok' => true, 'msg' => 'Marked ready · Fleet Manager can now open a Work Order.'];
    }

    public function cancel(int $id, string $reason, int $uid, string $uname): array
    {
        $w = $this->byId($id);
        if (!$w) return ['ok' => false, 'msg' => 'Not found.'];
        if (in_array($w['status'], ['wo_created','completed','cancelled','rejected'], true)) {
            return ['ok' => false, 'msg' => 'Cannot cancel — status is ' . $w['status'] . '.'];
        }
        (new DB())->insertUpdate(
            "UPDATE work_requests 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, $w['status'], 'cancelled', $uid, $uname, $this->s($reason) ?: 'Cancelled');
        return ['ok' => true, 'msg' => 'Cancelled.'];
    }

    /**
     * Convert a READY Work Request into an OPEN Work Order.
     * Copies the WR items to work_order_parts (parts + consumables only —
     * labour lines become labour_cost on the WO header).
     */
    public function convertToWo(int $id, int $uid, string $uname): array
    {
        $w = $this->byId($id);
        if (!$w) return ['ok' => false, 'msg' => 'Not found.'];
        if ($w['status'] !== 'ready') return ['ok' => false, 'msg' => 'Only READY work requests can become Work Orders.'];
        if ($w['wo_id']) return ['ok' => false, 'msg' => 'A Work Order already exists for this request.'];

        $kind = self::URGENCY_TO_WO_KIND[$w['urgency']] ?? 'planned';
        $priority = $w['urgency'] === 'breakdown' ? 'urgent' : ($w['urgency'] === 'high' ? 'high' : 'normal');

        $db = new DB();

        // Create the WO
        $woModel = new WorkOrders();
        $woNo    = $woModel->nextWoNo();
        $r = $db->insertUpdate(
            "INSERT INTO work_orders
                (tenant_id, wo_no, truck_id, vendor_id, kind, priority, status,
                 opened_on, opened_by, opened_km,
                 fault_description, currency, source_wr_id,
                 created_by, created_on)
             VALUES
                (:tenant, :wn, :tid, :vid, :k, :pr, 'open',
                 CURDATE(), :uid, :okm,
                 :fd, :cur, :wr,
                 :uid, NOW())",
            [
                ':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1,
                ':wn'     => $woNo,
                ':tid'    => (int)$w['truck_id'],
                ':vid'    => $this->nn((int)($w['sourced_vendor_id'] ?? 0)),
                ':k'      => $kind,
                ':pr'     => $priority,
                ':uid'    => $uid,
                ':okm'    => $this->nn((int)($w['truck_odometer'] ?? 0)),
                ':fd'     => $w['symptom'],
                ':cur'    => $w['currency'],
                ':wr'     => $id,
            ]
        );
        if ($r['success'] < 1) return ['ok' => false, 'msg' => 'WO creation failed.'];
        $woId = (int)$r['last_id'];

        // Copy WR items → WO parts (parts + consumables only; labour_cost is separate on WO)
        $items = (new WorkRequestItems())->forRequest($id);
        $labourCost = 0.0;
        $externalCost = 0.0;
        foreach ($items as $it) {
            $qty       = (float)$it['qty'] ?: 1;
            $unitCost  = (float)($it['actual_cost'] ?? $it['unit_cost_est']);
            $totalCost = round($qty * $unitCost, 2);
            if ($it['item_type'] === 'labour') { $labourCost += $totalCost; continue; }
            if ($it['item_type'] === 'external_service') { $externalCost += $totalCost; continue; }
            // Regular parts
            $partCat = self::ITEM_CAT_TO_WO_PART_CAT[$it['category']] ?? 'spare_parts';
            $db->insertUpdate(
                "INSERT INTO work_order_parts
                    (work_order_id, part_name, category, qty, unit_cost, total_cost,
                     gl_account_code, notes, created_by, created_on)
                 VALUES (:wo, :nm, :cat, :qty, :uc, :tc, :gl, :notes, :uid, NOW())",
                [
                    ':wo'    => $woId,
                    ':nm'    => $it['description'],
                    ':cat'   => $partCat,
                    ':qty'   => round($qty, 3),
                    ':uc'    => round($unitCost, 2),
                    ':tc'    => $totalCost,
                    ':gl'    => null, // WorkOrders model will resolve from category on close
                    ':notes' => $it['notes'] ?? '',
                    ':uid'   => $uid,
                ]
            );
        }
        // Set WO labour + external + recompute total
        $db->insertUpdate(
            "UPDATE work_orders
                SET labour_cost = :lc, external_cost = :ec,
                    parts_cost = (SELECT COALESCE(SUM(total_cost),0) FROM work_order_parts WHERE work_order_id = :id AND deleted = 0),
                    total_cost = (SELECT COALESCE(SUM(total_cost),0) FROM work_order_parts WHERE work_order_id = :id AND deleted = 0) + :lc + :ec,
                    edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':id' => $woId, ':lc' => $labourCost, ':ec' => $externalCost, ':uid' => $uid]
        );

        // Link WR → WO and flip WR status
        $db->insertUpdate(
            "UPDATE work_requests SET status = 'wo_created', wo_id = :woid,
                                       edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':woid' => $woId, ':uid' => $uid, ':id' => $id]
        );
        $this->logTransition($id, 'ready', 'wo_created', $uid, $uname, 'Converted to ' . $woNo);
        return ['ok' => true, 'msg' => 'Work Order ' . $woNo . ' created from this request.', 'wo_id' => $woId, 'wo_no' => $woNo];
    }

    /**
     * Called from WorkOrders::close() when a closed WO has a source_wr_id.
     * Marks the parent WR as completed and stamps the closure.
     */
    public function markCompletedByWo(int $wrId, int $uid, string $uname, string $woNo): void
    {
        (new DB())->insertUpdate(
            "UPDATE work_requests SET status = 'completed',
                                       completed_at = NOW(), completed_by = :uid,
                                       edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id AND status = 'wo_created'",
            [':uid' => $uid, ':id' => $wrId]
        );
        $this->logTransition($wrId, 'wo_created', 'completed', $uid, $uname, "WO {$woNo} closed");
    }

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

    /* =========================================================
     *  Helpers
     * ========================================================= */
    private function logTransition(int $wrId, ?string $from, string $to, int $uid, string $uname, string $note = ''): void
    {
        (new DB())->insertUpdate(
            "INSERT INTO work_request_transitions (work_request_id, from_status, to_status, actor_id, actor_name, action_at, note)
             VALUES (:wr, :from, :to, :uid, :name, NOW(), :note)",
            [':wr' => $wrId, ':from' => $from, ':to' => $to, ':uid' => $uid, ':name' => $uname, ':note' => $note]
        );
    }
}
