<?php
/**
 * WorkOrders — the workshop's job register with an auto-post on close.
 *
 * Lifecycle:
 *   open → in_progress → completed → closed
 *                                  └→ cancelled (any pre-closed state)
 *
 * On close(), the model posts one balanced JE with debit lines split by
 * parts category (5120 Spare Parts, 5130 Tyres, 5140 Batteries, 5150
 * Lubricants, 5160 Workshop Consumables) plus 5110 Repairs & Maintenance
 * for labour and 5170 External Workshop for third-party invoice cost.
 * Credit line: 2010 Accounts Payable (total). Every line carries truck_id,
 * vendor_id, and work_order_id so reports slice cost per truck / per vendor
 * / per part category cleanly.
 */
class WorkOrders 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; }

    /**
     * Rewired to the client's COA on 2026-08-27 (Turn 1.5 · slice 21).
     * Per client's decision to keep the COA lean, every workshop debit
     * (labour, external service, parts, tyres, lubes, consumables) lands
     * in 7013 M/Vehicles Running Costs. Analytical tags (truck_id,
     * work_order_id, expense category) carry the drill on the JE line.
     */
    private const PAYMENT_METHOD_TO_CREDIT = [
        'cash'          => '3003',   // Cash
        'petty_cash'    => '3003',   // Cash (lumped)
        'mobile_money'  => '3006',   // Mobile Money Float
        'bank_transfer' => '3001',   // Bank - CRDB
        'cheque'        => '3001',   // Bank - CRDB
        'fuel_card'     => '3009',   // Fuel Card Account
    ];
    private const AP_CODE     = '4009';   // Trade Payables (was 2010)
    private const LABOUR_CODE = '7013';   // M/Vehicles Running Costs (was 5110)
    private const EXTERNAL_CODE = '7013'; // M/Vehicles Running Costs (was 5170)

    /** Parts category → GL account. All roll up to 7013; category kept for reporting. */
    private const CATEGORY_TO_GL = [
        'spare_parts'  => '7013',
        'tyres'        => '7013',
        'batteries'    => '7013',
        'lubricants'   => '7013',
        'consumables'  => '7013',
    ];

    /* =========================================================
     *  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.wo_no LIKE :q OR w.fault_description LIKE :q OR w.work_performed LIKE :q OR t.reg_no LIKE :q OR v.vendor_name LIKE :q)';
            $params[':q'] = '%' . $filters['q'] . '%';
        }
        if (!empty($filters['status']))    { $where[] = 'w.status   = :st'; $params[':st']  = $this->s($filters['status']); }
        if (!empty($filters['kind']))      { $where[] = 'w.kind     = :k';  $params[':k']   = $this->s($filters['kind']); }
        if (!empty($filters['priority']))  { $where[] = 'w.priority = :pr'; $params[':pr']  = $this->s($filters['priority']); }
        if (!empty($filters['truck_id']))  { $where[] = 'w.truck_id = :tid';$params[':tid'] = (int)$filters['truck_id']; }
        if (!empty($filters['vendor_id'])) { $where[] = 'w.vendor_id= :vid';$params[':vid'] = (int)$filters['vendor_id']; }
        if (!empty($filters['from_date'])) { $where[] = 'w.opened_on >= :fd';$params[':fd'] = $this->s($filters['from_date']); }
        if (!empty($filters['to_date']))   { $where[] = 'w.opened_on <= :td';$params[':td'] = $this->s($filters['to_date']); }

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

        $countRs = $db->mySelect(
            "SELECT COUNT(*) AS n
               FROM work_orders w
          LEFT JOIN trucks   t ON t.c0d = w.truck_id
          LEFT JOIN vendors  v ON v.c0d = w.vendor_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.wo_no, w.kind, w.priority, w.status,
                    w.opened_on, w.started_at, w.completed_at, w.closed_at,
                    w.fault_description, w.total_cost, w.currency,
                    t.reg_no AS truck_reg, t.make, t.model,
                    v.vendor_name AS vendor_name,
                    je.je_no AS je_no
               FROM work_orders w
          LEFT JOIN trucks   t   ON t.c0d = w.truck_id
          LEFT JOIN vendors  v   ON v.c0d = w.vendor_id
          LEFT JOIN journal_entries je ON je.c0d = w.je_id
              WHERE $whereSql
           ORDER BY w.status IN ('open','in_progress') DESC,
                    w.priority = 'urgent' DESC,
                    w.opened_on DESC, w.c0d DESC
              LIMIT $perPage OFFSET $offset",
            $params
        ) ?: [];

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

    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,
                    v.vendor_name AS vendor_name, v.vendor_phone_1 AS vendor_phone,
                    je.je_no AS je_no
               FROM work_orders w
          LEFT JOIN trucks   t  ON t.c0d = w.truck_id
          LEFT JOIN vendors  v  ON v.c0d = w.vendor_id
          LEFT JOIN journal_entries je ON je.c0d = w.je_id
              WHERE w.c0d = :id AND w.deleted = 0
              LIMIT 1",
            [':id' => $id]
        );
        return $rs[0] ?? [];
    }

    public function partsForWo(int $woId): array
    {
        return (new DB())->mySelect(
            "SELECT c0d, part_no, part_name, category, qty, unit_cost, total_cost, gl_account_code, notes, created_on
               FROM work_order_parts
              WHERE work_order_id = :id AND deleted = 0
           ORDER BY category, c0d",
            [':id' => $woId]
        ) ?: [];
    }

    public function partsSumByCategory(int $woId): array
    {
        return (new DB())->mySelect(
            "SELECT category, SUM(total_cost) AS s
               FROM work_order_parts
              WHERE work_order_id = :id AND deleted = 0
           GROUP BY category",
            [':id' => $woId]
        ) ?: [];
    }

    public function nextWoNo(): string
    {
        $rs = (new DB())->mySelect(
            "SELECT wo_no FROM work_orders WHERE wo_no LIKE 'WO-%' ORDER BY c0d DESC LIMIT 1", []
        );
        $last = (!empty($rs) && preg_match('/(\d+)$/', $rs[0]['wo_no'], $m)) ? (int)$m[1] : 0;
        return 'WO-' . 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];

        $r = (new 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, created_by, created_on)
             VALUES
                (:tenant, :wn, :tid, :vid, :k, :pr, 'open',
                 :od, :uid, :okm,
                 :fd, :cur, :uid, NOW())",
            [
                ':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1,
                ':wn'     => $this->nextWoNo(),
                ':tid'    => $truckId,
                ':vid'    => $this->nn((int)($d['vendor_id'] ?? 0)),
                ':k'      => $this->s(($d['kind']     ?? '') ?: 'planned'),
                ':pr'     => $this->s(($d['priority'] ?? '') ?: 'normal'),
                ':od'     => $this->s($d['opened_on'] ?? '') ?: date('Y-m-d'),
                ':uid'    => $uid,
                ':okm'    => $this->n($d['opened_km'] ?? ''),
                ':fd'     => $this->s($d['fault_description'] ?? ''),
                ':cur'    => $this->s(($d['currency'] ?? '') ?: 'TZS'),
            ]
        );
        return ['ok' => $r['success'] > 0, 'msg' => $r['success'] > 0 ? 'Work order created.' : 'Save failed.', 'id' => (int)$r['last_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'], ['closed','cancelled'], true)) {
            return ['ok' => false, 'msg' => 'Cannot edit — status is ' . $w['status'] . '.', 'id' => 0];
        }
        (new DB())->insertUpdate(
            "UPDATE work_orders SET
                truck_id = :tid, vendor_id = :vid, kind = :k, priority = :pr,
                opened_on = :od, opened_km = :okm,
                fault_description = :fd, work_performed = :wp,
                labour_hours = :lh, labour_rate = :lr, labour_cost = :lc,
                external_cost = :ec, currency = :cur, notes = :notes,
                edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [
                ':tid'  => (int)($d['truck_id'] ?? $w['truck_id']),
                ':vid'  => $this->nn((int)($d['vendor_id'] ?? 0)),
                ':k'    => $this->s(($d['kind'] ?? '') ?: 'planned'),
                ':pr'   => $this->s(($d['priority'] ?? '') ?: 'normal'),
                ':od'   => $this->s($d['opened_on'] ?? '') ?: $w['opened_on'],
                ':okm'  => $this->n($d['opened_km'] ?? ''),
                ':fd'   => $this->s($d['fault_description'] ?? ''),
                ':wp'   => $this->s($d['work_performed']    ?? ''),
                ':lh'   => $this->m($d['labour_hours']      ?? 0),
                ':lr'   => $this->m($d['labour_rate']       ?? 0),
                ':lc'   => $this->m($d['labour_cost']       ?? (($this->m($d['labour_hours'] ?? 0)) * ($this->m($d['labour_rate'] ?? 0)))),
                ':ec'   => $this->m($d['external_cost']     ?? 0),
                ':cur'  => $this->s(($d['currency'] ?? '') ?: $w['currency']),
                ':notes'=> $this->s($d['notes'] ?? ''),
                ':uid'  => $uid,
                ':id'   => $id,
            ]
        );
        return ['ok' => true, 'msg' => 'Work order updated.', 'id' => $id];
    }

    /* =========================================================
     *  Parts sub-CRUD
     * ========================================================= */
    public function addPart(int $woId, array $d, int $uid): array
    {
        $w = $this->byId($woId);
        if (!$w) return ['ok' => false, 'msg' => 'Work order not found.'];
        if (in_array($w['status'], ['closed','cancelled'], true)) {
            return ['ok' => false, 'msg' => 'Cannot add parts — WO is ' . $w['status'] . '.'];
        }
        $name = $this->s($d['part_name'] ?? '');
        if ($name === '') return ['ok' => false, 'msg' => 'Part name is required.'];

        $qty       = $this->m($d['qty']       ?? 0) ?: 1;
        $unitCost  = $this->m($d['unit_cost'] ?? 0);
        $totalCost = round($qty * $unitCost, 2);
        $category  = $this->s(($d['category'] ?? '') ?: 'spare_parts');
        $glCode    = $this->s($d['gl_account_code'] ?? '') ?: (self::CATEGORY_TO_GL[$category] ?? '5120');

        $r = (new DB())->insertUpdate(
            "INSERT INTO work_order_parts
                (work_order_id, part_no, part_name, category, qty, unit_cost, total_cost, gl_account_code, notes, created_by, created_on)
             VALUES (:wo, :pn, :nm, :cat, :qty, :uc, :tc, :gl, :notes, :uid, NOW())",
            [
                ':wo'    => $woId,
                ':pn'    => $this->s($d['part_no'] ?? ''),
                ':nm'    => $name,
                ':cat'   => $category,
                ':qty'   => round($qty, 3),
                ':uc'    => round($unitCost, 2),
                ':tc'    => $totalCost,
                ':gl'    => $glCode,
                ':notes' => $this->s($d['notes'] ?? ''),
                ':uid'   => $uid,
            ]
        );
        // Recompute WO parts_cost + total_cost
        $this->recomputeCosts($woId, $uid);
        return ['ok' => $r['success'] > 0, 'msg' => $r['success'] > 0 ? 'Part added.' : 'Save failed.', 'id' => (int)$r['last_id']];
    }

    public function deletePart(int $partId, int $uid): array
    {
        $db = new DB();
        $rs = $db->mySelect("SELECT work_order_id FROM work_order_parts WHERE c0d = :id", [':id' => $partId]);
        if (empty($rs)) return ['ok' => false, 'msg' => 'Part not found.'];
        $woId = (int)$rs[0]['work_order_id'];
        $w = $this->byId($woId);
        if ($w && in_array($w['status'], ['closed','cancelled'], true)) {
            return ['ok' => false, 'msg' => 'Cannot delete parts — WO is ' . $w['status'] . '.'];
        }
        $db->insertUpdate(
            "UPDATE work_order_parts SET deleted = 1 WHERE c0d = :id",
            [':id' => $partId]
        );
        $this->recomputeCosts($woId, $uid);
        return ['ok' => true, 'msg' => 'Part removed.'];
    }

    private function recomputeCosts(int $woId, int $uid): void
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT COALESCE(SUM(total_cost),0) AS s FROM work_order_parts WHERE work_order_id = :id AND deleted = 0",
            [':id' => $woId]
        );
        $partsCost = (float)($rs[0]['s'] ?? 0);
        $db->insertUpdate(
            "UPDATE work_orders
                SET parts_cost = :p,
                    total_cost = :p + labour_cost + external_cost,
                    edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':p' => $partsCost, ':uid' => $uid, ':id' => $woId]
        );
    }

    /* =========================================================
     *  Lifecycle transitions
     * ========================================================= */
    public function start(int $id, int $uid): array
    {
        $w = $this->byId($id);
        if (!$w) return ['ok' => false, 'msg' => 'Not found.'];
        if ($w['status'] !== 'open') return ['ok' => false, 'msg' => 'Only open WOs can be started.'];
        (new DB())->insertUpdate(
            "UPDATE work_orders SET status = 'in_progress', started_at = NOW(), started_by = :uid,
                                     edited_by = :uid, edited_on = NOW() WHERE c0d = :id",
            [':uid' => $uid, ':id' => $id]
        );
        // Set truck status = 'maintenance' while WO is in progress
        (new DB())->insertUpdate(
            "UPDATE trucks SET status = 'maintenance', edited_by = :uid, edited_on = NOW() WHERE c0d = :tid AND status = 'active'",
            [':uid' => $uid, ':tid' => (int)$w['truck_id']]
        );
        return ['ok' => true, 'msg' => 'Work started. Truck marked in maintenance.'];
    }

    public function complete(int $id, int $uid): array
    {
        $w = $this->byId($id);
        if (!$w) return ['ok' => false, 'msg' => 'Not found.'];
        if ($w['status'] !== 'in_progress') return ['ok' => false, 'msg' => 'Only in-progress WOs can be completed.'];
        (new DB())->insertUpdate(
            "UPDATE work_orders SET status = 'completed', completed_at = NOW(), completed_by = :uid,
                                     edited_by = :uid, edited_on = NOW() WHERE c0d = :id",
            [':uid' => $uid, ':id' => $id]
        );
        return ['ok' => true, 'msg' => 'Marked complete — ready to close & book cost.'];
    }

    /**
     * Close — post the maintenance JE and release the truck back to service.
     */
    public function close(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'], ['completed','in_progress'], true)) {
            return ['ok' => false, 'msg' => 'Only completed or in-progress WOs can be closed.'];
        }

        // Refresh costs from parts (in case admin edited after last recompute)
        $this->recomputeCosts($id, $uid);
        $w = $this->byId($id);
        $total = (float)$w['total_cost'];
        if ($total <= 0) return ['ok' => false, 'msg' => 'Total cost is zero — add parts or labour before closing.'];

        // Build JE lines: one Dr per category with a total, plus labour + external, plus AP Cr.
        $tags = [
            'truck_id'      => (int)$w['truck_id'],
            'vendor_id'     => (int)($w['vendor_id'] ?? 0) ?: 0,
            'work_order_id' => $id,
        ];
        $lines = [];

        foreach ($this->partsSumByCategory($id) as $r) {
            $gl = self::CATEGORY_TO_GL[$r['category']] ?? '5120';
            $lines[] = array_merge([
                'account_code' => $gl,
                'debit'        => (float)$r['s'],
                'credit'       => 0,
                'narrative'    => 'Parts · ' . ucwords(str_replace('_',' ', $r['category'])) . ' · ' . $w['wo_no'],
            ], $tags);
        }
        if ((float)$w['labour_cost'] > 0) {
            $lines[] = array_merge([
                'account_code' => self::LABOUR_CODE,
                'debit'        => (float)$w['labour_cost'],
                'credit'       => 0,
                'narrative'    => 'Labour · ' . number_format((float)$w['labour_hours'], 2) . ' hrs · ' . $w['wo_no'],
            ], $tags);
        }
        if ((float)$w['external_cost'] > 0) {
            $lines[] = array_merge([
                'account_code' => self::EXTERNAL_CODE,
                'debit'        => (float)$w['external_cost'],
                'credit'       => 0,
                'narrative'    => 'External workshop · ' . $w['wo_no'],
            ], $tags);
        }
        $lines[] = array_merge([
            'account_code' => self::AP_CODE,
            'debit'        => 0,
            'credit'       => $total,
            'narrative'    => 'Payable for ' . $w['wo_no'],
        ], $tags);

        if (count($lines) < 2) return ['ok' => false, 'msg' => 'Nothing to post — check parts and cost values.'];

        $jp  = new JournalPosting();
        $res = $jp->post([
            'entry_date'     => $this->s($d['close_date'] ?? '') ?: date('Y-m-d'),
            'source_type'    => 'WORK_ORDER',
            'source_id'      => $id,
            'source_ref'     => $w['wo_no'],
            'narrative'      => 'Maintenance · ' . ($w['work_performed'] ?: $w['fault_description'] ?: $w['wo_no']),
            'currency'       => $w['currency'],
            'fx_rate'        => 1.0,
            'posted_by'      => $uid,
            'posted_by_name' => $uname,
            'lines'          => $lines,
        ]);
        if (!$res['ok']) return ['ok' => false, 'msg' => 'JE posting failed: ' . $res['msg']];

        // Compute downtime
        $downtime = null;
        if (!empty($w['started_at']) && !empty($w['completed_at'])) {
            $downtime = round((strtotime($w['completed_at']) - strtotime($w['started_at'])) / 3600, 2);
        }

        (new DB())->insertUpdate(
            "UPDATE work_orders SET status = 'closed', closed_at = NOW(), closed_by = :uid,
                                     downtime_hrs = :dt, je_id = :jeid,
                                     work_performed = COALESCE(NULLIF(:wp,''), work_performed),
                                     edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':uid' => $uid, ':dt' => $downtime, ':jeid' => $res['id'], ':wp' => $this->s($d['work_performed'] ?? ''), ':id' => $id]
        );
        // Release the truck back to active
        (new DB())->insertUpdate(
            "UPDATE trucks SET status = 'active', edited_by = :uid, edited_on = NOW() WHERE c0d = :tid AND status = 'maintenance'",
            [':uid' => $uid, ':tid' => (int)$w['truck_id']]
        );

        // If this WO was created from a Work Request, mark the WR completed
        if (!empty($w['source_wr_id'])) {
            (new WorkRequests())->markCompletedByWo((int)$w['source_wr_id'], $uid, $uname, (string)$w['wo_no']);
        }

        return ['ok' => true, 'msg' => 'Closed · JE ' . $res['je_no'] . ' posted · truck released.'];
    }

    public function cancel(int $id, string $reason, int $uid): array
    {
        $w = $this->byId($id);
        if (!$w) return ['ok' => false, 'msg' => 'Not found.'];
        if (in_array($w['status'], ['closed','cancelled'], true)) {
            return ['ok' => false, 'msg' => 'Cannot cancel — status is ' . $w['status'] . '.'];
        }
        (new DB())->insertUpdate(
            "UPDATE work_orders SET status = 'cancelled', cancelled_at = NOW(), cancelled_by = :uid,
                                     cancel_reason = :rr, edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':uid' => $uid, ':rr' => $this->s($reason), ':id' => $id]
        );
        // If truck was pushed to maintenance for this WO, release it (best-effort — doesn't check for other open WOs)
        (new DB())->insertUpdate(
            "UPDATE trucks SET status = 'active', edited_by = :uid, edited_on = NOW()
              WHERE c0d = :tid AND status = 'maintenance'
                AND NOT EXISTS (SELECT 1 FROM work_orders wo2 WHERE wo2.truck_id = trucks.c0d AND wo2.status IN ('open','in_progress') AND wo2.deleted = 0 AND wo2.c0d <> :id)",
            [':uid' => $uid, ':tid' => (int)$w['truck_id'], ':id' => $id]
        );
        return ['ok' => true, 'msg' => 'Cancelled.'];
    }

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