<?php
/**
 * ApprovalChain · app/models/ApprovalChain.php
 *
 * Reusable approval-workflow engine. Every module that needs governance
 * (LPO, Payment Request, Payroll Run, Loan Drawdown, Grant Disbursement, ...)
 * works the same way:
 *
 *   1.  Module's migration seeds a row in fin_chain_defs + N rows in
 *       fin_chain_steps describing the steps (e.g. PC_REVIEW → PM → FM → EXEC).
 *   2.  When the originator clicks "Submit for Approval", call
 *           $AC->submit('GOODS_LPO_APPROVAL', 'GOODS_LPO_BATCH', $batch_id, ...);
 *       Engine creates a fin_approvals row at step 1, logs SUBMIT.
 *   3.  Approvers see the entity in their queue via
 *           $AC->myQueue($user_id, $user_group_name)
 *       and act via approve / sendBack / decline.
 *   4.  Module page can show progress + action bar by embedding the
 *       _approval_chain_strip partial, passing the approval row.
 *
 * Tables: fin_chain_defs, fin_chain_steps, fin_approvals, fin_approval_log
 */
class ApprovalChain extends Model
{
    // ── internal helpers ─────────────────────────────────────────────────
    private function fetchAll(string $sql, array $params, string $tag): array
    {
        $re = [];
        try {
            $DB = new DB();
            $cx = $DB->dBCxn();
            $st = $cx->prepare($sql);
            $st->execute($params);
            $re = $st->fetchAll(PDO::FETCH_ASSOC);
            $cx = null;
        } catch (\Throwable $e) {
            error_log('ApprovalChain::' . $tag . ' ' . $e->getMessage());
        }
        return $re;
    }

    // Track lastInsertId inside the same connection as the exec() that produced it.
    private int $_lastInsertId = 0;

    private function exec(string $sql, array $params, string $tag): int
    {
        try {
            $DB = new DB();
            $cx = $DB->dBCxn();
            $st = $cx->prepare($sql);
            $st->execute($params);
            $n  = $st->rowCount();
            $this->_lastInsertId = (int) $cx->lastInsertId();
            $cx = null;
            return $n;
        } catch (\Throwable $e) {
            error_log('ApprovalChain::' . $tag . ' ' . $e->getMessage());
            return 0;
        }
    }

    private function lastId(): int
    {
        return $this->_lastInsertId;
    }

    private function normaliseGroup(string $g): string
    {
        return strtoupper(trim($g));
    }

    /** Insert an audit log row. */
    private function log(int $approval_id, ?int $from_step, ?int $to_step, string $decision,
                         int $uid, string $uname, string $ugroup, string $comment): void
    {
        $this->exec(
            "INSERT INTO fin_approval_log
                (approval_id, from_step_id, to_step_id, decision,
                 decided_by, decided_by_name, decided_by_group, decided_on, comment)
             VALUES
                (:aid, :fs, :ts, :dec, :uid, :uname, :ug, NOW(), :cmt)",
            [
                ':aid' => $approval_id, ':fs' => $from_step, ':ts' => $to_step, ':dec' => $decision,
                ':uid' => $uid, ':uname' => $uname, ':ug' => $this->normaliseGroup($ugroup),
                ':cmt' => $comment
            ], 'log'
        );
    }

    // ── CHAIN DEFINITIONS (registry) ─────────────────────────────────────
    public function allChains(): array
    {
        return $this->fetchAll(
            "SELECT d.c0d, d.chain_key, d.title, d.description, d.status, d.created_on,
                    (SELECT COUNT(*) FROM fin_chain_steps s WHERE s.chain_id = d.c0d AND s.deleted=0) AS step_count,
                    (SELECT COUNT(*) FROM fin_approvals a WHERE a.chain_id = d.c0d AND a.status='IN_PROGRESS' AND a.deleted=0) AS in_flight_count
               FROM fin_chain_defs d
              WHERE d.deleted = 0
              ORDER BY d.title ASC",
            [], 'allChains'
        );
    }

    public function chainByKey(string $key): array
    {
        $rs = $this->fetchAll(
            "SELECT * FROM fin_chain_defs WHERE chain_key = :k AND deleted = 0 LIMIT 1",
            [':k' => $key], 'chainByKey'
        );
        return $rs[0] ?? [];
    }

    public function chainById(int $id): array
    {
        $rs = $this->fetchAll(
            "SELECT * FROM fin_chain_defs WHERE c0d = :id AND deleted = 0 LIMIT 1",
            [':id' => $id], 'chainById'
        );
        return $rs[0] ?? [];
    }

    public function stepsForChain(int $chain_id): array
    {
        return $this->fetchAll(
            "SELECT * FROM fin_chain_steps WHERE chain_id = :cid AND deleted = 0 ORDER BY step_no ASC",
            [':cid' => $chain_id], 'stepsForChain'
        );
    }

    public function stepById(int $step_id): array
    {
        $rs = $this->fetchAll(
            "SELECT * FROM fin_chain_steps WHERE c0d = :id AND deleted = 0 LIMIT 1",
            [':id' => $step_id], 'stepById'
        );
        return $rs[0] ?? [];
    }

    private function firstStep(int $chain_id): array
    {
        $rs = $this->fetchAll(
            "SELECT * FROM fin_chain_steps WHERE chain_id = :cid AND deleted = 0 ORDER BY step_no ASC LIMIT 1",
            [':cid' => $chain_id], 'firstStep'
        );
        return $rs[0] ?? [];
    }

    private function nextStep(int $chain_id, int $current_step_no): array
    {
        $rs = $this->fetchAll(
            "SELECT * FROM fin_chain_steps
              WHERE chain_id = :cid AND deleted = 0 AND step_no > :n
              ORDER BY step_no ASC LIMIT 1",
            [':cid' => $chain_id, ':n' => $current_step_no], 'nextStep'
        );
        return $rs[0] ?? [];
    }

    private function prevStep(int $chain_id, int $current_step_no): array
    {
        $rs = $this->fetchAll(
            "SELECT * FROM fin_chain_steps
              WHERE chain_id = :cid AND deleted = 0 AND step_no < :n
              ORDER BY step_no DESC LIMIT 1",
            [':cid' => $chain_id, ':n' => $current_step_no], 'prevStep'
        );
        return $rs[0] ?? [];
    }

    // ── APPROVAL INSTANCES (state) ───────────────────────────────────────
    public function approvalById(int $id): array
    {
        $rs = $this->fetchAll(
            "SELECT a.*, d.chain_key, d.title AS chain_title,
                    s.step_no AS current_step_no, s.step_key AS current_step_key,
                    s.step_label AS current_step_label, s.role_required AS current_step_role,
                    s.is_terminal AS current_step_terminal
               FROM fin_approvals a
               JOIN fin_chain_defs d ON d.c0d = a.chain_id
               LEFT JOIN fin_chain_steps s ON s.c0d = a.current_step_id
              WHERE a.c0d = :id AND a.deleted = 0 LIMIT 1",
            [':id' => $id], 'approvalById'
        );
        return $rs[0] ?? [];
    }

    /** Look up the (one) active approval row for a given entity, if any. */
    public function approvalFor(string $entity_type, int $entity_id): array
    {
        $rs = $this->fetchAll(
            "SELECT a.*, d.chain_key, d.title AS chain_title,
                    s.step_no AS current_step_no, s.step_key AS current_step_key,
                    s.step_label AS current_step_label, s.role_required AS current_step_role,
                    s.is_terminal AS current_step_terminal
               FROM fin_approvals a
               JOIN fin_chain_defs d ON d.c0d = a.chain_id
               LEFT JOIN fin_chain_steps s ON s.c0d = a.current_step_id
              WHERE a.entity_type = :et AND a.entity_id = :eid AND a.deleted = 0
              ORDER BY a.c0d DESC LIMIT 1",
            [':et' => $entity_type, ':eid' => $entity_id], 'approvalFor'
        );
        return $rs[0] ?? [];
    }

    public function historyFor(int $approval_id): array
    {
        return $this->fetchAll(
            "SELECT l.*,
                    sf.step_no AS from_step_no, sf.step_label AS from_step_label,
                    st.step_no AS to_step_no,   st.step_label AS to_step_label,
                    COALESCE(NULLIF(TRIM(l.decided_by_name), ''),
                             NULLIF(TRIM(u.full_name), ''),
                             CONCAT('User #', l.decided_by)) AS actor_display
               FROM fin_approval_log l
               LEFT JOIN fin_chain_steps sf ON sf.c0d = l.from_step_id
               LEFT JOIN fin_chain_steps st ON st.c0d = l.to_step_id
               LEFT JOIN users u            ON u.c0d = l.decided_by
              WHERE l.approval_id = :aid
              ORDER BY l.c0d ASC",
            [':aid' => $approval_id], 'historyFor'
        );
    }

    /**
     * Slice 8Z-p · Smart-route an approval row to the best detail page for the reviewer.
     *
     * Some entities have a rich native detail page (Tenders/view, Projects/view) that
     * already embeds the approval strip — approvers get full decision context there.
     * Others (lpo, payment_schedule) have bespoke inline context on FinChains/view so
     * we keep those on the generic approval page. Everything else falls back to the
     * generic approval page.
     *
     * Called by FinChains/myQueue for the "Open" button, but reusable anywhere an
     * approval needs a canonical detail URL.
     */
    public function entityDetailUrl(array $approvalRow): string
    {
        $type = (string)($approvalRow['entity_type'] ?? '');
        $eid  = (int)($approvalRow['entity_id'] ?? 0);
        $aid  = (int)($approvalRow['c0d'] ?? 0);

        // Tender-driven chains: entity_id IS the tender_id — jump to the right tab on Tenders/view.
        if ($type === 'TENDER_CONTRACT') return PROOT . 'Tenders/view/' . $eid . '#tab-contract';
        if ($type === 'TENDER_PRICING')  return PROOT . 'Tenders/view/' . $eid . '#tab-pricing';

        // CRM onboarding: entity_id is crm_project_onboarding.c0d — one lookup to reach the project page.
        if ($type === 'crm_project_onboarding') {
            $r = $this->fetchAll(
                "SELECT project_id FROM crm_project_onboarding WHERE c0d = :id LIMIT 1",
                [':id' => $eid], 'entityDetailUrlCrmOnb'
            );
            $pid = (int)($r[0]['project_id'] ?? 0);
            if ($pid > 0) return PROOT . 'Projects/view/' . $pid;
        }

        // Everything else (lpo, payment_schedule, payee_account, goods_project_onboarding,
        // goods_rfq, sales, WORKS_CR, WORKS_VARIATION, SERVICE_REQUISITION…) stays on the
        // generic approval page — those entities either have bespoke inline context on
        // FinChains/view or no dedicated detail page yet.
        return PROOT . 'FinChains/view/' . $aid;
    }

    /**
     * Approvals currently sitting on a step the given user's group is allowed to act on.
     * Pass $chain_key='' to list across all chains.
     */
    public function myQueue(int $user_id, string $user_group, string $chain_key = '', string $entity_type = '', int $page = 1, int $perPage = 25, string $q = ''): array
    {
        $params = [':ug' => $this->normaliseGroup($user_group)];
        $extra  = '';
        if ($chain_key !== '')   { $params[':ck'] = $chain_key;    $extra .= " AND d.chain_key = :ck"; }
        if ($entity_type !== '') { $params[':et'] = $entity_type;  $extra .= " AND a.entity_type = :et"; }
        // Slice 8Z-p · free-text search on ref_label, chain title and submitter name — covers most needles.
        $q = trim($q);
        if ($q !== '') {
            $params[':q'] = '%' . $q . '%';
            $extra .= " AND (a.ref_label LIKE :q OR d.title LIKE :q OR a.submitted_by_name LIKE :q)";
        }

        // Total count for pagination
        $total = 0;
        $cRs = $this->fetchAll(
            "SELECT COUNT(*) AS n
               FROM fin_approvals a
               JOIN fin_chain_defs  d ON d.c0d = a.chain_id
               JOIN fin_chain_steps s ON s.c0d = a.current_step_id
              WHERE a.status = 'IN_PROGRESS' AND a.deleted = 0
                AND (UPPER(s.role_required) = :ug OR s.role_required = '*')
                $extra",
            $params, 'myQueueCount'
        );
        if (!empty($cRs)) $total = (int)$cRs[0]['n'];

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

        $rows = $this->fetchAll(
            "SELECT a.c0d, a.entity_type, a.entity_id, a.ref_label, a.summary_json,
                    a.submitted_by, a.submitted_by_name, a.submitted_on, a.return_url,
                    d.chain_key, d.title AS chain_title,
                    s.step_no, s.step_key, s.step_label, s.role_required
               FROM fin_approvals a
               JOIN fin_chain_defs  d ON d.c0d = a.chain_id
               JOIN fin_chain_steps s ON s.c0d = a.current_step_id
              WHERE a.status = 'IN_PROGRESS'
                AND a.deleted = 0
                AND (UPPER(s.role_required) = :ug OR s.role_required = '*')
                $extra
              ORDER BY a.submitted_on ASC, a.c0d ASC
              LIMIT $perPage OFFSET $offset",
            $params, 'myQueue'
        );
        return ['rows' => $rows, 'total' => $total, 'page' => $page, 'per_page' => $perPage, 'pages' => (int)ceil($total / $perPage)];
    }

    /** All in-flight approvals (for Finance/Exec admin visibility). */
    public function allInFlight(string $chain_key = ''): array
    {
        $params = [];
        $extra  = '';
        if ($chain_key !== '') {
            $params[':ck'] = $chain_key;
            $extra .= " AND d.chain_key = :ck";
        }
        return $this->fetchAll(
            "SELECT a.c0d, a.entity_type, a.entity_id, a.ref_label, a.status,
                    a.submitted_by, a.submitted_by_name, a.submitted_on, a.return_url,
                    d.chain_key, d.title AS chain_title,
                    s.step_no, s.step_key, s.step_label, s.role_required
               FROM fin_approvals a
               JOIN fin_chain_defs  d ON d.c0d = a.chain_id
               LEFT JOIN fin_chain_steps s ON s.c0d = a.current_step_id
              WHERE a.status = 'IN_PROGRESS'
                AND a.deleted = 0
                $extra
              ORDER BY a.submitted_on DESC, a.c0d DESC",
            $params, 'allInFlight'
        );
    }

    public function recentDecided(int $limit = 50): array
    {
        $limit = max(1, min(500, $limit));
        return $this->fetchAll(
            "SELECT a.c0d, a.entity_type, a.entity_id, a.ref_label, a.status,
                    a.submitted_by, a.submitted_by_name, a.submitted_on, a.decided_on,
                    d.chain_key, d.title AS chain_title
               FROM fin_approvals a
               JOIN fin_chain_defs d ON d.c0d = a.chain_id
              WHERE a.status IN ('APPROVED','DECLINED','CANCELLED')
                AND a.deleted = 0
              ORDER BY a.decided_on DESC, a.c0d DESC
              LIMIT $limit",
            [], 'recentDecided'
        );
    }

    // ── STATE TRANSITIONS (the actual engine) ────────────────────────────
    /**
     * Originate an approval. Creates the fin_approvals row at step 1 and logs SUBMIT.
     * Returns ['ok'=>bool,'msg'=>string,'approval_id'=>int].
     */
    public function submit(array $d): array
    {
        $chain_key   = (string) ($d['chain_key'] ?? '');
        $entity_type = (string) ($d['entity_type'] ?? '');
        $entity_id   = (int)    ($d['entity_id'] ?? 0);
        $ref_label   = (string) ($d['ref_label'] ?? '');
        $summary     = (string) ($d['summary_json'] ?? '');
        $return_url  = (string) ($d['return_url'] ?? '');
        $uid         = (int)    ($d['user_id'] ?? 0);
        $uname       = (string) ($d['user_name'] ?? '');
        $ugroup      = (string) ($d['user_group'] ?? '');

        if ($chain_key === '' || $entity_type === '' || $entity_id <= 0) {
            return ['ok' => false, 'msg' => 'chain_key, entity_type, entity_id are required.', 'approval_id' => 0];
        }
        $chain = $this->chainByKey($chain_key);
        if (empty($chain) || strtoupper($chain['status']) !== 'ACTIVE') {
            return ['ok' => false, 'msg' => 'Chain "' . $chain_key . '" not found or inactive.', 'approval_id' => 0];
        }
        $first = $this->firstStep((int) $chain['c0d']);
        if (empty($first)) {
            return ['ok' => false, 'msg' => 'Chain has no steps configured.', 'approval_id' => 0];
        }
        // Prevent dup active approval per (entity_type, entity_id).
        $existing = $this->approvalFor($entity_type, $entity_id);
        if (!empty($existing) && $existing['status'] === 'IN_PROGRESS') {
            return ['ok' => false, 'msg' => 'Already submitted for approval.', 'approval_id' => (int) $existing['c0d']];
        }
        // If a prior terminal/declined approval exists, soft-delete it to free the unique key.
        if (!empty($existing)) {
            $this->exec(
                "UPDATE fin_approvals SET deleted = 1, edited_by = :uid, edited_on = NOW() WHERE c0d = :id",
                [':uid' => $uid, ':id' => (int) $existing['c0d']], 'submit.retireOld'
            );
        }

        $this->exec(
            "INSERT INTO fin_approvals
                (chain_id, entity_type, entity_id, current_step_id, status,
                 ref_label, summary_json, return_url,
                 submitted_by, submitted_by_name, submitted_on,
                 created_by, created_on, edited_by, edited_on, deleted)
             VALUES
                (:cid, :et, :eid, :sid, 'IN_PROGRESS',
                 :ref, :sum, :ret,
                 :uid, :uname, NOW(),
                 :uid, NOW(), :uid, NOW(), 0)",
            [
                ':cid' => (int) $chain['c0d'], ':et' => $entity_type, ':eid' => $entity_id,
                ':sid' => (int) $first['c0d'],
                ':ref' => $ref_label, ':sum' => $summary !== '' ? $summary : null,
                ':ret' => $return_url !== '' ? $return_url : null,
                ':uid' => $uid, ':uname' => $uname
            ], 'submit.insert'
        );
        $appr_id = $this->lastId();
        $this->log($appr_id, null, (int) $first['c0d'], 'SUBMIT', $uid, $uname, $ugroup, (string) ($d['comment'] ?? 'Submitted for approval.'));
        return ['ok' => true, 'msg' => 'Submitted for approval.', 'approval_id' => $appr_id];
    }

    /**
     * Advance to the next step. If the current step is terminal, mark the approval APPROVED.
     */
    public function approve(array $d): array
    {
        $aid    = (int)    ($d['approval_id'] ?? 0);
        $uid    = (int)    ($d['user_id'] ?? 0);
        $uname  = (string) ($d['user_name'] ?? '');
        $ugroup = (string) ($d['user_group'] ?? '');
        $cmt    = (string) ($d['comment'] ?? '');
        $assignments = is_array($d['assignments'] ?? null) ? $d['assignments'] : [];

        $a = $this->approvalById($aid);
        if (empty($a))                            { return ['ok' => false, 'msg' => 'Approval not found.']; }
        if ($a['status'] !== 'IN_PROGRESS')       { return ['ok' => false, 'msg' => 'Approval is not in progress.']; }
        $ok = $this->mayActOnStep((int) $a['current_step_id'], $ugroup);
        if (!$ok)                                  { return ['ok' => false, 'msg' => 'Your role cannot act on this step.']; }

        $from_step_id = (int) $a['current_step_id'];
        $curStep      = $this->stepById($from_step_id);
        $required     = $this->requiredSlotsForStep($curStep);
        if (!empty($required)) {
            $missing = [];
            foreach ($required as $slot) {
                if (empty($assignments[$slot]) || (int)($assignments[$slot]['user_id'] ?? 0) <= 0) $missing[] = $slot;
            }
            if (!empty($missing)) return ['ok' => false, 'msg' => 'Assign: ' . implode(', ', $missing) . ' before approving.'];
        }

        // Accumulate step payload (each slot: user_id + user_name + assigned_by + assigned_at)
        $payload = $this->decodePayload((string)($a['step_payload_json'] ?? ''));
        foreach ($required as $slot) {
            $u = $assignments[$slot];
            $payload['assignments'][$slot] = [
                'user_id'       => (int)$u['user_id'],
                'user_name'     => (string)($u['user_name'] ?? ''),
                'assigned_by'   => $uid,
                'assigned_by_name' => $uname,
                'assigned_at'   => date('Y-m-d H:i:s'),
                'step_id'       => $from_step_id,
                'step_key'      => (string)($curStep['step_key'] ?? ''),
            ];
        }
        $payloadJson = json_encode($payload);

        $terminal     = (int) ($a['current_step_terminal'] ?? 0) === 1;
        $next         = $terminal ? [] : $this->nextStep((int) $a['chain_id'], (int) $a['current_step_no']);

        if ($terminal || empty($next)) {
            // No more steps → APPROVED
            $this->exec(
                "UPDATE fin_approvals SET
                     current_step_id   = NULL,
                     status            = 'APPROVED',
                     step_payload_json = :pl,
                     decided_on        = NOW(),
                     edited_by         = :uid,
                     edited_on         = NOW()
                   WHERE c0d = :id",
                [':pl' => $payloadJson, ':uid' => $uid, ':id' => $aid], 'approve.finalise'
            );
            $this->log($aid, $from_step_id, null, 'APPROVE', $uid, $uname, $ugroup, $cmt);
            return ['ok' => true, 'msg' => 'Approved · chain complete.', 'final' => true];
        }

        $this->exec(
            "UPDATE fin_approvals SET
                 current_step_id   = :ns,
                 step_payload_json = :pl,
                 edited_by         = :uid,
                 edited_on         = NOW()
               WHERE c0d = :id",
            [':ns' => (int) $next['c0d'], ':pl' => $payloadJson, ':uid' => $uid, ':id' => $aid], 'approve.advance'
        );
        $this->log($aid, $from_step_id, (int) $next['c0d'], 'APPROVE', $uid, $uname, $ugroup, $cmt);
        return ['ok' => true, 'msg' => 'Approved · advanced to ' . $next['step_label'] . '.', 'final' => false];
    }

    /** Split the comma-separated slot list on a step into an array of slot names. */
    public function requiredSlotsForStep(array $step): array
    {
        $raw = trim((string)($step['required_assignments'] ?? ''));
        if ($raw === '') return [];
        return array_values(array_filter(array_map(function ($s) { return strtoupper(trim($s)); }, explode(',', $raw))));
    }

    /** Decode a fin_approvals.step_payload_json blob into an array (always with ['assignments'=>[]]). */
    public function decodePayload(string $json): array
    {
        $out = ['assignments' => []];
        if ($json === '') return $out;
        $tmp = json_decode($json, true);
        if (is_array($tmp)) {
            $out['assignments'] = is_array($tmp['assignments'] ?? null) ? $tmp['assignments'] : [];
        }
        return $out;
    }

    /**
     * Read all active users that belong to any of the given group names.
     * Used by the approve dialog to populate the slot dropdowns.
     * Slot names ("ACCOUNTANT", "PROJECT_MANAGER") map to the equivalently-named user_group_name.
     */
    public function usersInSlot(string $slot): array
    {
        // slot -> candidate user_group_name values (allow legacy variants)
        $map = [
            'ACCOUNTANT'          => ['Accountant'],
            'PROCUREMENT'         => ['Procurement'],
            'PROJECT_MANAGER'     => ['Project Manager', 'Projects Manager'],
            'PROJECTS_COORDINATOR'=> ['Projects Coordinator', 'Project Coordinator'],
            'FINANCE_MANAGER'     => ['Finance Manager'],
            'EXECUTIVE'           => ['Executive'],
            'CRM'                 => ['CRM'],
            'FIELD_SUPERVISOR'    => ['Field Supervisor'],
            'STORES'              => ['Stores'],
        ];
        $groups = $map[$slot] ?? [$slot];
        $ph = [];
        $params = [];
        foreach ($groups as $i => $g) { $k = ':g' . $i; $ph[] = $k; $params[$k] = $g; }
        return $this->fetchAll(
            "SELECT c0d, full_name, user_group_name FROM users
              WHERE deleted = 0 AND status = 'ACTIVE' AND user_group_name IN (" . implode(',', $ph) . ")
              ORDER BY full_name ASC",
            $params, 'usersInSlot'
        );
    }

    /** Send back to a prior step (defaults to the first step → originator). */
    public function sendBack(array $d): array
    {
        $aid           = (int)    ($d['approval_id'] ?? 0);
        $uid           = (int)    ($d['user_id'] ?? 0);
        $uname         = (string) ($d['user_name'] ?? '');
        $ugroup        = (string) ($d['user_group'] ?? '');
        $cmt           = (string) ($d['comment'] ?? '');
        $target_step_id = (int)   ($d['target_step_id'] ?? 0);

        if ($cmt === '') { return ['ok' => false, 'msg' => 'A comment is required when sending back.']; }

        $a = $this->approvalById($aid);
        if (empty($a))                       { return ['ok' => false, 'msg' => 'Approval not found.']; }
        if ($a['status'] !== 'IN_PROGRESS')  { return ['ok' => false, 'msg' => 'Approval is not in progress.']; }
        if (!$this->mayActOnStep((int) $a['current_step_id'], $ugroup)) {
            return ['ok' => false, 'msg' => 'Your role cannot act on this step.'];
        }
        $from_step_id = (int) $a['current_step_id'];

        // Default target: the previous step. If none, go back to step 1.
        $target = $target_step_id > 0 ? $this->stepById($target_step_id) : $this->prevStep((int) $a['chain_id'], (int) $a['current_step_no']);
        if (empty($target)) { $target = $this->firstStep((int) $a['chain_id']); }
        if (empty($target)) { return ['ok' => false, 'msg' => 'No earlier step found to send back to.']; }
        // Don't allow sending forward via "send back"
        if ((int) $target['step_no'] >= (int) $a['current_step_no']) {
            return ['ok' => false, 'msg' => 'Target step must be earlier in the chain.'];
        }

        $this->exec(
            "UPDATE fin_approvals SET
                 current_step_id = :ts,
                 edited_by       = :uid,
                 edited_on       = NOW()
               WHERE c0d = :id",
            [':ts' => (int) $target['c0d'], ':uid' => $uid, ':id' => $aid], 'sendBack.update'
        );
        $this->log($aid, $from_step_id, (int) $target['c0d'], 'SEND_BACK', $uid, $uname, $ugroup, $cmt);
        return ['ok' => true, 'msg' => 'Sent back to ' . $target['step_label'] . '.'];
    }

    /**
     * Bounce the whole approval back to the originator (CRM). Used when the current-step
     * actor wants the submitter to edit and resubmit — semantically distinct from `sendBack`
     * (which walks the chain to a prior step) and `decline` (which is a hard reject).
     * Marks the approval CANCELLED so a fresh `submit` can supersede it; logs a SEND_BACK entry
     * so the audit trail reads naturally.
     */
    public function returnToOriginator(array $d): array
    {
        $aid    = (int)    ($d['approval_id'] ?? 0);
        $uid    = (int)    ($d['user_id'] ?? 0);
        $uname  = (string) ($d['user_name'] ?? '');
        $ugroup = (string) ($d['user_group'] ?? '');
        $cmt    = (string) ($d['comment'] ?? '');

        if ($cmt === '') return ['ok' => false, 'msg' => 'A comment is required when returning to the originator.'];

        $a = $this->approvalById($aid);
        if (empty($a))                      return ['ok' => false, 'msg' => 'Approval not found.'];
        if ($a['status'] !== 'IN_PROGRESS') return ['ok' => false, 'msg' => 'Approval is not in progress.'];
        if (!$this->mayActOnStep((int) $a['current_step_id'], $ugroup)) {
            return ['ok' => false, 'msg' => 'Your role cannot act on this step.'];
        }
        $from_step_id = (int) $a['current_step_id'];

        $this->exec(
            "UPDATE fin_approvals SET
                 current_step_id = NULL,
                 status          = 'CANCELLED',
                 decided_on      = NOW(),
                 edited_by       = :uid,
                 edited_on       = NOW()
               WHERE c0d = :id",
            [':uid' => $uid, ':id' => $aid], 'returnToOriginator.update'
        );
        $this->log($aid, $from_step_id, null, 'SEND_BACK', $uid, $uname, $ugroup,
                   'Returned to originator: ' . $cmt);
        return ['ok' => true, 'msg' => 'Returned to originator — they can edit and resubmit.'];
    }

    /** Hard reject — chain status becomes DECLINED. */
    public function decline(array $d): array
    {
        $aid    = (int)    ($d['approval_id'] ?? 0);
        $uid    = (int)    ($d['user_id'] ?? 0);
        $uname  = (string) ($d['user_name'] ?? '');
        $ugroup = (string) ($d['user_group'] ?? '');
        $cmt    = (string) ($d['comment'] ?? '');

        if ($cmt === '') { return ['ok' => false, 'msg' => 'A comment is required when declining.']; }

        $a = $this->approvalById($aid);
        if (empty($a))                       { return ['ok' => false, 'msg' => 'Approval not found.']; }
        if ($a['status'] !== 'IN_PROGRESS')  { return ['ok' => false, 'msg' => 'Approval is not in progress.']; }
        if (!$this->mayActOnStep((int) $a['current_step_id'], $ugroup)) {
            return ['ok' => false, 'msg' => 'Your role cannot act on this step.'];
        }
        $from_step_id = (int) $a['current_step_id'];

        $this->exec(
            "UPDATE fin_approvals SET
                 current_step_id = NULL,
                 status          = 'DECLINED',
                 decided_on      = NOW(),
                 edited_by       = :uid,
                 edited_on       = NOW()
               WHERE c0d = :id",
            [':uid' => $uid, ':id' => $aid], 'decline.update'
        );
        $this->log($aid, $from_step_id, null, 'DECLINE', $uid, $uname, $ugroup, $cmt);
        return ['ok' => true, 'msg' => 'Declined.'];
    }

    /** Originator-initiated cancel (chain becomes CANCELLED). */
    public function cancel(array $d): array
    {
        $aid    = (int)    ($d['approval_id'] ?? 0);
        $uid    = (int)    ($d['user_id'] ?? 0);
        $uname  = (string) ($d['user_name'] ?? '');
        $ugroup = (string) ($d['user_group'] ?? '');
        $cmt    = (string) ($d['comment'] ?? '');

        $a = $this->approvalById($aid);
        if (empty($a))                              { return ['ok' => false, 'msg' => 'Approval not found.']; }
        if ($a['status'] !== 'IN_PROGRESS')         { return ['ok' => false, 'msg' => 'Approval is not in progress.']; }
        if ((int) $a['submitted_by'] !== $uid) {
            // Allow admin override only
            if (!in_array($this->normaliseGroup($ugroup), ['ADMINISTRATOR','DATA MANAGER','MANAGEMENT','FINANCE'], true)) {
                return ['ok' => false, 'msg' => 'Only the originator (or an administrator) can cancel.'];
            }
        }
        $from_step_id = (int) $a['current_step_id'];

        $this->exec(
            "UPDATE fin_approvals SET
                 current_step_id = NULL,
                 status          = 'CANCELLED',
                 decided_on      = NOW(),
                 edited_by       = :uid,
                 edited_on       = NOW()
               WHERE c0d = :id",
            [':uid' => $uid, ':id' => $aid], 'cancel.update'
        );
        $this->log($aid, $from_step_id, null, 'CANCEL', $uid, $uname, $ugroup, $cmt);
        return ['ok' => true, 'msg' => 'Cancelled.'];
    }

    /** Check whether a user (by group) may act on a given step. */
    public function mayActOnStep(int $step_id, string $user_group): bool
    {
        $s = $this->stepById($step_id);
        if (empty($s)) { return false; }
        $req = $this->normaliseGroup((string) ($s['role_required'] ?? ''));
        if ($req === '' || $req === '*') { return true; }
        $ug  = $this->normaliseGroup($user_group);
        // Admin override
        if (in_array($ug, ['ADMINISTRATOR','DATA MANAGER','MANAGEMENT'], true)) { return true; }
        if ($ug === $req) { return true; }
        /* Role-family equivalence — a step whose role_required is stored
           as a single canonical label (e.g. 'FINANCE') should also let
           adjacent members of the same job family act ('FINANCE MANAGER',
           'ACCOUNTANT'). Mirrors the frontend's stageReviewList actor
           groups so the UI's "Approve" button and the server both agree.
           Each entry lists ALL groups considered equivalent for that
           family — membership is symmetric. */
        $families = [
            ['FINANCE', 'FINANCE MANAGER', 'ACCOUNTANT'],
            ['PROJECT MANAGER', 'PROJECTS MANAGER'],
            ['PROJECTS COORDINATOR', 'C PROJECTS', 'PROJECT COORDINATOR'],
            ['EXECUTIVE', 'CEO', 'MANAGING DIRECTOR'],
            ['PROCUREMENT'],
        ];
        foreach ($families as $fam) {
            if (in_array($req, $fam, true) && in_array($ug, $fam, true)) {
                return true;
            }
        }
        return false;
    }

    // ── CHAIN-DEF REGISTRATION (called by other modules' migrations) ─────
    /**
     * Idempotent: register a chain definition + its steps. Called from
     * other modules' install/seed code so chains travel with their owner.
     * Steps array shape:
     *   [['step_key' => 'PC_REVIEW', 'step_label' => 'PC Review', 'role_required' => 'PROJECTS COORDINATOR', 'is_terminal' => 0], ...]
     */
    public function registerChain(string $chain_key, string $title, string $description, array $steps, int $uid = 0): array
    {
        if ($chain_key === '' || empty($steps)) { return ['ok' => false, 'msg' => 'chain_key and steps required.']; }

        $existing = $this->chainByKey($chain_key);
        if (empty($existing)) {
            $this->exec(
                "INSERT INTO fin_chain_defs (chain_key, title, description, status, created_by, created_on, edited_by, edited_on, deleted)
                 VALUES (:k, :t, :d, 'ACTIVE', :uid, NOW(), :uid, NOW(), 0)",
                [':k' => $chain_key, ':t' => $title, ':d' => $description, ':uid' => $uid], 'registerChain.insertDef'
            );
            $chain_id = $this->lastId();
        } else {
            $chain_id = (int) $existing['c0d'];
            $this->exec(
                "UPDATE fin_chain_defs SET title = :t, description = :d, edited_by = :uid, edited_on = NOW() WHERE c0d = :id",
                [':t' => $title, ':d' => $description, ':uid' => $uid, ':id' => $chain_id], 'registerChain.updateDef'
            );
            // Soft-delete prior step rows; re-insert below.
            $this->exec(
                "UPDATE fin_chain_steps SET deleted = 1, edited_by = :uid, edited_on = NOW() WHERE chain_id = :id",
                [':uid' => $uid, ':id' => $chain_id], 'registerChain.retireSteps'
            );
        }

        $i = 0;
        foreach ($steps as $s) {
            $i++;
            $this->exec(
                "INSERT INTO fin_chain_steps
                    (chain_id, step_no, step_key, step_label, role_required, is_terminal, description, sort_order,
                     created_by, created_on, edited_by, edited_on, deleted)
                 VALUES
                    (:cid, :n, :k, :l, :role, :term, :descr, :sort, :uid, NOW(), :uid, NOW(), 0)",
                [
                    ':cid' => $chain_id, ':n' => $i,
                    ':k'   => (string) ($s['step_key'] ?? ''),
                    ':l'   => (string) ($s['step_label'] ?? ''),
                    ':role'=> $this->normaliseGroup((string) ($s['role_required'] ?? '*')),
                    ':term'=> empty($s['is_terminal']) ? 0 : 1,
                    ':descr'=> (string) ($s['description'] ?? ''),
                    ':sort'=> (int) ($s['sort_order'] ?? $i),
                    ':uid' => $uid
                ], 'registerChain.insertStep'
            );
        }
        return ['ok' => true, 'msg' => 'Chain registered.', 'chain_id' => $chain_id];
    }
}
