<?php
class Projects extends Model
{
    /**
     * Canonical onboarding checklist items, seeded per project on create.
     *
     * Slice 8Z-y1 · The list is now type-aware: Services projects get 10 extra items covering
     * the maintenance-specific things CRM must confirm (sites, equipment list, spare policy,
     * callout structure, SLA agreement, invoicing frequency, pricing model, service pack
     * agreement, part-replacement process). Goods and Works fall back to the 9 canonical items.
     */
    public static function DEFAULT_CHECKLIST(?string $projectType = null): array
    {
        $common = [
            ['key' => 'CONTRACT_RECEIVED',  'label' => 'Contract received'],
            ['key' => 'TENDER_AWARD_LETTER','label' => 'Tender award letter'],
            ['key' => 'INSURANCE_CERT',     'label' => 'Insurance certificate'],
            ['key' => 'BANKERS_GUARANTEE',  'label' => 'Bankers guarantee'],
            ['key' => 'KICKOFF_MEETING',    'label' => 'Kick-off meeting held'],
            ['key' => 'PC_ASSIGNED',        'label' => 'Projects Coordinator assigned'],
            ['key' => 'PM_ASSIGNED',        'label' => 'Project Manager assigned'],
            ['key' => 'HSE_PLAN',           'label' => 'HSE plan filed'],
            ['key' => 'MOBILIZATION_PLAN',  'label' => 'Mobilisation plan'],
        ];
        $type = strtolower((string)$projectType);
        if ($type === 'services') {
            return array_merge($common, [
                ['key' => 'SVC_SITES_LIST',           'label' => 'Sites listed (count + addresses)'],
                ['key' => 'SVC_EQUIPMENT_LIST',       'label' => 'Equipment listed per site (type + make + model + size)'],
                ['key' => 'SVC_MAINTENANCE_STRUCTURE','label' => 'PM + Callout structure agreed'],
                ['key' => 'SVC_CALLOUT_CATEGORY_SLA', 'label' => 'Callout SLA targets confirmed (Emergency / Urgent / Routine)'],
                ['key' => 'SVC_SPARE_POLICY',         'label' => 'Spare policy agreed (Company / Client / Consignment / Fixed pack)'],
                ['key' => 'SVC_SERVICE_PACK',         'label' => 'Service pack confirmed (client-supplied list or ours)'],
                ['key' => 'SVC_INVOICING_CADENCE',    'label' => 'Invoicing frequency agreed (retainer / per-event / milestone / consumption)'],
                ['key' => 'SVC_CALLOUT_PAYMENT',      'label' => 'Callout payment structure agreed (fee + emergency multiplier + free quota)'],
                ['key' => 'SVC_PRICING_MODEL',        'label' => 'Pricing model agreed (labour rate + mileage + parts markup)'],
                ['key' => 'SVC_PART_REPLACEMENT',     'label' => 'Adhoc part replacement request process defined'],
            ]);
        }
        // goods + works keep the 9 canonical items for now
        return $common;
    }

    public function all(): array
    {
        return $this->listAll([]);
    }

    /**
     * Slice 8S-9 · Auto-tick the Services checklist based on real data on the linked service contract.
     *
     * 9 of the 10 SVC_* items map to concrete data conditions on service_contracts (and its sites /
     * assets / SLAs / schedule / packs). The 10th — SVC_PART_REPLACEMENT — has no natural anchor
     * (it confirms a policy SOP was written) so it stays manual.
     *
     * Called from:
     *   • Every ServiceContracts write (saveHeader / saveSite / saveAsset / saveSla / activate /
     *     generateSchedule) so the checklist reflects data in real time.
     *   • ProjectsController::syncServicesChecklistAjAction — a "Refresh checks" button on the CRM
     *     onboarding view.
     *   • OnboardingController::submitAj as a pre-flight before the "all-complete" gate.
     *
     * Returns [ 'evaluated' => N, 'flipped_on' => [keys], 'flipped_off' => [keys] ].
     * Non-services projects are skipped (returns empty).
     */
    public function syncServicesChecklist(int $projectId): array
    {
        $db = new DB();
        $p  = $db->mySelect(
            "SELECT project_type FROM c_projects WHERE c0d = :id AND deleted = 0",
            [':id' => $projectId]
        );
        if (empty($p) || strtolower((string)$p[0]['project_type']) !== 'services') {
            return ['evaluated' => 0, 'flipped_on' => [], 'flipped_off' => []];
        }

        // Prefer the most-recently-created active/draft contract for this project.
        $contracts = $db->mySelect(
            "SELECT c0d, spare_policy, invoice_cadence, callout_fee, emergency_multiplier,
                    free_callouts_per_month, labour_rate_per_hr, parts_markup_pct, mileage_rate_per_km
               FROM service_contracts
              WHERE project_id = :p AND deleted = 0
              ORDER BY c0d DESC LIMIT 1",
            [':p' => $projectId]
        );
        $contract = $contracts[0] ?? null;

        // Rule fns. Each returns bool — true means the checklist item should be TICKED.
        $rules = [
            'SVC_SITES_LIST' => function () use ($db, $contract): bool {
                if (!$contract) return false;
                $r = $db->mySelect("SELECT COUNT(*) n FROM service_contract_sites WHERE contract_id = :c AND is_active = 1", [':c' => (int)$contract['c0d']]);
                return (int)($r[0]['n'] ?? 0) > 0;
            },
            'SVC_EQUIPMENT_LIST' => function () use ($db, $contract): bool {
                if (!$contract) return false;
                // Every active site must have ≥1 active asset. Ok if there are no sites (returns 0 for both).
                $r = $db->mySelect(
                    "SELECT
                        (SELECT COUNT(*) FROM service_contract_sites WHERE contract_id = :c1 AND is_active = 1) AS site_count,
                        (SELECT COUNT(DISTINCT site_id) FROM service_contract_assets WHERE contract_id = :c2 AND is_active = 1) AS sites_with_assets",
                    [':c1' => (int)$contract['c0d'], ':c2' => (int)$contract['c0d']]
                );
                $sc = (int)($r[0]['site_count'] ?? 0);
                $sw = (int)($r[0]['sites_with_assets'] ?? 0);
                return $sc > 0 && $sw >= $sc;
            },
            'SVC_MAINTENANCE_STRUCTURE' => function () use ($db, $contract): bool {
                if (!$contract) return false;
                $r = $db->mySelect("SELECT COUNT(*) n FROM service_contract_pm_schedule WHERE contract_id = :c AND is_active = 1", [':c' => (int)$contract['c0d']]);
                return (int)($r[0]['n'] ?? 0) > 0;
            },
            'SVC_CALLOUT_CATEGORY_SLA' => function () use ($db, $contract): bool {
                if (!$contract) return false;
                $r = $db->mySelect("SELECT COUNT(*) n FROM service_contract_slas WHERE contract_id = :c", [':c' => (int)$contract['c0d']]);
                return (int)($r[0]['n'] ?? 0) > 0;
            },
            'SVC_SPARE_POLICY' => function () use ($contract): bool {
                return $contract && !empty($contract['spare_policy']);
            },
            'SVC_SERVICE_PACK' => function () use ($db, $contract): bool {
                if (!$contract) return false;
                // Ticked when either (a) any asset has an explicit override pack OR
                // (b) at least one asset auto-matches a service_pack via type + size range.
                $r = $db->mySelect(
                    "SELECT COUNT(*) n
                       FROM service_contract_assets a
                       JOIN service_packs p
                         ON p.asset_type_id = a.asset_type_id AND p.is_active = 1
                        AND (p.size_min IS NULL OR p.size_min <= COALESCE(a.size_value, 0))
                        AND (p.size_max IS NULL OR p.size_max >= COALESCE(a.size_value, 0))
                      WHERE a.contract_id = :c AND a.is_active = 1
                        AND (a.service_pack_id_override IS NOT NULL OR p.c0d IS NOT NULL)",
                    [':c' => (int)$contract['c0d']]
                );
                return (int)($r[0]['n'] ?? 0) > 0;
            },
            'SVC_INVOICING_CADENCE' => function () use ($contract): bool {
                return $contract && !empty($contract['invoice_cadence']);
            },
            'SVC_CALLOUT_PAYMENT' => function () use ($contract): bool {
                if (!$contract) return false;
                return $contract['callout_fee'] !== null
                    && $contract['emergency_multiplier'] !== null
                    && $contract['free_callouts_per_month'] !== null;
            },
            'SVC_PRICING_MODEL' => function () use ($contract): bool {
                if (!$contract) return false;
                return $contract['labour_rate_per_hr'] !== null
                    && $contract['parts_markup_pct']   !== null
                    && $contract['mileage_rate_per_km']!== null;
            },
            // SVC_PART_REPLACEMENT stays manual — no natural data anchor.
        ];

        $items = $db->mySelect(
            "SELECT c0d, checklist_key, is_complete FROM crm_project_checklist_items
              WHERE project_id = :p AND checklist_key IN ('" . implode("','", array_keys($rules)) . "')",
            [':p' => $projectId]
        ) ?: [];
        $flippedOn  = [];
        $flippedOff = [];
        foreach ($items as $it) {
            $key = (string)$it['checklist_key'];
            if (!isset($rules[$key])) continue;
            $shouldTick = (bool)$rules[$key]();
            $current    = (int)$it['is_complete'] === 1;
            if ($shouldTick && !$current) {
                $db->insertUpdate(
                    "UPDATE crm_project_checklist_items SET is_complete = 1, completed_on = NOW(), completed_by = 0 WHERE c0d = :id",
                    [':id' => (int)$it['c0d']]
                );
                $flippedOn[] = $key;
            } elseif (!$shouldTick && $current) {
                $db->insertUpdate(
                    "UPDATE crm_project_checklist_items SET is_complete = 0, completed_on = NULL, completed_by = NULL WHERE c0d = :id",
                    [':id' => (int)$it['c0d']]
                );
                $flippedOff[] = $key;
            }
        }
        return ['evaluated' => count($items), 'flipped_on' => $flippedOn, 'flipped_off' => $flippedOff];
    }

    /**
     * List projects with filters. When $filter['page_size'] > 0, applies LIMIT/OFFSET.
     * Filters: q (project_no/name/customer/tender ref search), project_type, customer_id,
     * origin, crm_status, project_lifecycle_stage.
     */
    public function listAll(array $filter = []): array
    {
        $db = new DB();
        [$where, $params] = $this->buildFilter($filter);
        $sql = "SELECT c0d, project_no, project_name, project_type, project_customer,
                       project_customer_id, project_tender_reference,
                       origin, crm_status, migrated_flag, project_lifecycle_stage,
                       contract_value, currency, project_start_date, project_end_date,
                       project_manager_name, pc_name
                  FROM c_projects
                 WHERE " . implode(' AND ', $where) . "
                 ORDER BY c0d DESC";
        $pageSize = (int)($filter['page_size'] ?? 0);
        if ($pageSize > 0) {
            $page   = max(1, (int)($filter['page'] ?? 1));
            $offset = ($page - 1) * $pageSize;
            $sql   .= sprintf(' LIMIT %d OFFSET %d', $pageSize, $offset);
        }
        return $db->mySelect($sql, $params) ?: [];
    }

    /** Total row count for the same filter set — used by the pagination footer. */
    public function countAll(array $filter = []): int
    {
        $db = new DB();
        [$where, $params] = $this->buildFilter($filter);
        $r = $db->mySelect("SELECT COUNT(*) n FROM c_projects WHERE " . implode(' AND ', $where), $params);
        return (int)($r[0]['n'] ?? 0);
    }

    /** Distinct customers present on the projects table — for the filter dropdown. */
    public function distinctCustomers(): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT DISTINCT project_customer_id AS c0d, project_customer AS name
               FROM c_projects
              WHERE deleted = 0 AND project_customer_id > 0 AND project_customer <> ''
              ORDER BY project_customer",
            []
        ) ?: [];
    }

    private function buildFilter(array $filter): array
    {
        $where  = ['deleted = 0'];
        $params = [];
        if (!empty($filter['project_type'])) { $where[] = 'project_type = :pt'; $params[':pt'] = $filter['project_type']; }
        if (!empty($filter['customer_id']))  { $where[] = 'project_customer_id = :c'; $params[':c'] = (int)$filter['customer_id']; }
        if (!empty($filter['origin']))       { $where[] = 'origin = :o'; $params[':o'] = $filter['origin']; }
        if (!empty($filter['crm_status']))   { $where[] = 'crm_status = :cs'; $params[':cs'] = $filter['crm_status']; }
        if (!empty($filter['stage']))        { $where[] = 'project_lifecycle_stage = :st'; $params[':st'] = $filter['stage']; }
        if (!empty($filter['q'])) {
            $where[] = '(project_no LIKE :q OR project_name LIKE :q OR project_customer LIKE :q OR project_tender_reference LIKE :q)';
            $params[':q'] = '%' . $filter['q'] . '%';
        }
        return [$where, $params];
    }

    public function byId(int $id): array
    {
        $db = new DB();
        $rs = $db->mySelect("SELECT * FROM c_projects WHERE c0d = :id AND deleted = 0 LIMIT 1", [':id' => $id]);
        return $rs[0] ?? [];
    }

    /**
     * Slice 8Y · PM stamps the project as reviewed with a full briefing packet:
     *   $payload: [
     *     'confirmation_notes' => ..,   // general PM confirmation
     *     'notes_pc'           => ..,   // handover briefing to Projects Coordinator
     *     'notes_procurement'  => ..,   // sourcing preferences + brand/quality notes for Procurement
     *     'suggested_items'    => ..,   // scope items PM thinks are missing from tender
     *   ]
     * Idempotent — re-calling refreshes timestamp + last-write-wins.
     */
    public function pmReview(int $projectId, array $payload, int $userId, string $userName): void
    {
        $p = $this->byId($projectId);
        if (!$p) throw new RuntimeException('Project not found.');
        if ((int)$p['project_manager_id'] !== $userId
            && strtoupper((string)($_SESSION['user_group_name'] ?? '')) !== 'ADMINISTRATOR') {
            throw new RuntimeException('Only the assigned Project Manager can mark the project reviewed.');
        }
        $db = new DB();
        $db->insertUpdate(
            "UPDATE c_projects
                SET pm_confirmed_at        = NOW(),
                    pm_confirmed_by        = :u,
                    pm_confirmation_notes  = :n,
                    pm_notes_pc            = :npc,
                    pm_notes_procurement   = :npr,
                    pm_suggested_items     = :ns,
                    edited_by              = :u,
                    edited_on              = NOW()
              WHERE c0d = :id",
            [
                ':u'   => $userId,
                ':n'   => (string)($payload['confirmation_notes'] ?? ''),
                ':npc' => (string)($payload['notes_pc']           ?? ''),
                ':npr' => (string)($payload['notes_procurement']  ?? ''),
                ':ns'  => (string)($payload['suggested_items']    ?? ''),
                ':id'  => $projectId,
            ]
        );
    }

    /**
     * Slice 8Z-v · PM writes structured services instructions to PC (templates, sites, team).
     * Independent of pmReview() so PM can iterate on instructions without re-triggering the
     * "review confirmed" timestamp.
     */
    public function saveSvcInstructions(int $projectId, array $payload, int $userId): void
    {
        $p = $this->byId($projectId);
        if (!$p) throw new RuntimeException('Project not found.');
        if ((string)($p['project_type'] ?? '') !== 'services') {
            throw new RuntimeException('PM services-instructions only apply to services projects.');
        }
        $ug = strtoupper((string)($_SESSION['user_group_name'] ?? ''));
        $isPm    = (int)$p['project_manager_id'] === $userId;
        $isAdmin = $ug === 'ADMINISTRATOR';
        if (!$isPm && !$isAdmin) throw new RuntimeException('Only the assigned PM (or Admin) can save services instructions.');
        $db = new DB();
        $db->insertUpdate(
            "UPDATE c_projects
                SET pm_svc_templates_needed   = :t,
                    pm_svc_sites_instructions = :s,
                    pm_svc_team_instructions  = :m,
                    edited_by                 = :u,
                    edited_on                 = NOW()
              WHERE c0d = :id",
            [
                ':t' => trim((string)($payload['templates_needed']   ?? '')) ?: null,
                ':s' => trim((string)($payload['sites_instructions'] ?? '')) ?: null,
                ':m' => trim((string)($payload['team_instructions']  ?? '')) ?: null,
                ':u' => $userId,
                ':id'=> $projectId,
            ]
        );
    }

    /**
     * Slice 8Y · PM assigns the Projects Coordinator. Notifies the new PC. Logs a role_change.
     * Optional start_date + end_date persist to c_projects.project_start_date/_end_date so
     * PM can set the goods project window at the same beat as the PC handover.
     */
    public function assignPc(int $projectId, int $newPcId, string $reason, int $actorId, string $actorName, string $actorGroup, ?string $startDate = null, ?string $endDate = null): void
    {
        $p = $this->byId($projectId);
        if (!$p) throw new RuntimeException('Project not found.');
        $g = strtoupper(trim($actorGroup));
        $isPm = (int)$p['project_manager_id'] === $actorId;
        if (!$isPm && !in_array($g, ['ADMINISTRATOR'], true)) {
            throw new RuntimeException('Only the assigned Project Manager or an Administrator can assign the PC.');
        }
        $db = new DB();
        $newPc = $db->mySelect(
            "SELECT c0d, full_name FROM users WHERE c0d = :u AND user_group_name = 'Projects Coordinator' AND status = 'ACTIVE' AND deleted = 0",
            [':u' => $newPcId]
        );
        if (empty($newPc)) throw new RuntimeException('New PC must be an active Projects Coordinator.');

        $oldPcId   = (int)($p['pc_id'] ?? 0);
        $oldPcName = (string)($p['pc_name'] ?? '');
        $newPcName = (string)$newPc[0]['full_name'];
        if ($oldPcId === $newPcId && empty($startDate) && empty($endDate)) {
            throw new RuntimeException('That user is already the PC on this project.');
        }

        // Normalise dates: empty → NULL, else 'Y-m-d'. Validate end >= start when both present.
        $sd = !empty($startDate) ? substr((string)$startDate, 0, 10) : null;
        $ed = !empty($endDate)   ? substr((string)$endDate,   0, 10) : null;
        if ($sd && $ed && $ed < $sd) throw new RuntimeException('End date cannot be before start date.');

        if ($oldPcId !== $newPcId) {
            $db->insertUpdate(
                "UPDATE c_projects SET
                    pc_id = :nid, pc_name = :nn,
                    project_start_date = COALESCE(:sd, project_start_date),
                    project_end_date   = COALESCE(:ed, project_end_date),
                    edited_by = :a, edited_on = NOW()
                  WHERE c0d = :id",
                [':nid' => $newPcId, ':nn' => $newPcName, ':sd' => $sd, ':ed' => $ed, ':a' => $actorId, ':id' => $projectId]
            );
            $db->insertUpdate(
                "INSERT INTO c_projects_role_changes
                    (project_id, role_slot, old_user_id, old_user_name, new_user_id, new_user_name,
                     changed_by, changed_by_name, reason, changed_on)
                 VALUES (:p, 'PROJECTS_COORDINATOR', :oi, :on, :ni, :nn, :cb, :cbn, :r, NOW())",
                [
                    ':p'  => $projectId, ':oi' => $oldPcId, ':on' => $oldPcName,
                    ':ni' => $newPcId,   ':nn' => $newPcName,
                    ':cb' => $actorId,   ':cbn' => $actorName,
                    ':r'  => trim($reason) === '' ? 'PC assigned by PM' : $reason,
                ]
            );
            try {
                if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
                $body = 'Assigned by ' . $actorName;
                if ($sd || $ed) $body .= '. Project window: ' . ($sd ?: '—') . ' → ' . ($ed ?: '—');
                if (trim($reason) !== '') $body .= '. Reason: ' . $reason;
                (new Notifications())->notify(
                    $newPcId, 'PROJECT_ASSIGNED_PC',
                    'You are the PC on ' . (string)$p['project_no'],
                    $body,
                    'Projects/view/' . $projectId, 'c_projects', $projectId, $actorId, $actorName
                );
            } catch (\Throwable $e) { /* best-effort */ }
        } else {
            // Same PC + dates changed only — just update the window.
            $db->insertUpdate(
                "UPDATE c_projects SET
                    project_start_date = COALESCE(:sd, project_start_date),
                    project_end_date   = COALESCE(:ed, project_end_date),
                    edited_by = :a, edited_on = NOW()
                  WHERE c0d = :id",
                [':sd' => $sd, ':ed' => $ed, ':a' => $actorId, ':id' => $projectId]
            );
        }
    }

    /**
     * Slice 8Y · Executive / Admin reassigns the PM.
     * Writes new seat to c_projects, appends an audit row to c_projects_role_changes,
     * and fans a bell notification to the new PM.
     */
    public function reassignPm(int $projectId, int $newPmId, string $reason, int $actorId, string $actorName, string $actorGroup): void
    {
        if (trim($reason) === '') throw new RuntimeException('A reason is required when reassigning the PM.');
        $p = $this->byId($projectId);
        if (!$p) throw new RuntimeException('Project not found.');
        $g = strtoupper(trim($actorGroup));
        if (!in_array($g, ['EXECUTIVE','ADMINISTRATOR'], true)) {
            throw new RuntimeException('Only Executive or Administrator can reassign the PM.');
        }

        $db = new DB();
        $newPm = $db->mySelect(
            "SELECT c0d, full_name FROM users WHERE c0d = :u AND user_group_name = 'Project Manager' AND status = 'ACTIVE' AND deleted = 0",
            [':u' => $newPmId]
        );
        if (empty($newPm)) throw new RuntimeException('New PM must be an active Project Manager.');

        $oldPmId   = (int)($p['project_manager_id'] ?? 0);
        $oldPmName = (string)($p['project_manager_name'] ?? '');
        $newPmName = (string)$newPm[0]['full_name'];
        if ($oldPmId === $newPmId) throw new RuntimeException('That user is already the PM on this project.');

        $db->insertUpdate(
            "UPDATE c_projects
                SET project_manager_id = :nid, project_manager_name = :nn,
                    pm_confirmed_at = NULL, pm_confirmed_by = NULL, pm_confirmation_notes = NULL,
                    edited_by = :a, edited_on = NOW()
              WHERE c0d = :id",
            [':nid' => $newPmId, ':nn' => $newPmName, ':a' => $actorId, ':id' => $projectId]
        );

        // Audit trail — same table onContractApproved uses for initial seat assignments.
        $db->insertUpdate(
            "INSERT INTO c_projects_role_changes
                (project_id, role_slot, old_user_id, old_user_name, new_user_id, new_user_name,
                 changed_by, changed_by_name, reason, changed_on)
             VALUES (:p, 'PROJECT_MANAGER', :oi, :on, :ni, :nn, :cb, :cbn, :r, NOW())",
            [
                ':p'  => $projectId, ':oi' => $oldPmId, ':on' => $oldPmName,
                ':ni' => $newPmId,   ':nn' => $newPmName,
                ':cb' => $actorId,   ':cbn' => $actorName, ':r' => $reason,
            ]
        );

        // Notify the new PM.
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            (new Notifications())->notify(
                $newPmId, 'PROJECT_ASSIGNED_PM',
                'You are the new PM on ' . (string)$p['project_no'],
                'Reassigned by ' . $actorName . '. Reason: ' . $reason,
                'Projects/view/' . $projectId, 'c_projects', $projectId, $actorId, $actorName
            );
        } catch (\Throwable $e) { /* best-effort */ }
    }

    /** Role change history for a project — used by the reassign PM audit dialog. */
    public function roleChangesFor(int $projectId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM c_projects_role_changes WHERE project_id = :p ORDER BY changed_on DESC, c0d DESC",
            [':p' => $projectId]
        ) ?: [];
    }

    public function nextProjectNo(): string
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT project_no FROM c_projects
              WHERE project_no REGEXP '^PRJ-[0-9]{4}-[0-9]+$'
              ORDER BY c0d DESC LIMIT 1",
            []
        );
        $year = (int) date('Y');
        if (!empty($rs) && preg_match('/^PRJ-(\d{4})-(\d+)$/', $rs[0]['project_no'], $m)) {
            $seq = ((int)$m[1] === $year) ? ((int)$m[2] + 1) : 1;
        } else {
            $seq = 1;
        }
        return sprintf('PRJ-%d-%04d', $year, $seq);
    }

    public function create(array $d, int $uid, string $uname): array
    {
        $errs = [];
        foreach (['project_name','project_type','project_customer_id','origin'] as $req) {
            if (empty($d[$req])) $errs[] = ucwords(str_replace('_',' ',$req));
        }
        if (!in_array($d['project_type'] ?? '', ['goods','services','works'], true)) {
            $errs[] = 'project_type must be goods|services|works';
        }
        $origin = (string)($d['origin'] ?? '');
        if (!in_array($origin, ['TENDER_BDM','TENDER_PUBLIC','DIRECT_AWARD','MIGRATED'], true)) {
            $errs[] = 'origin invalid';
        }
        if ($origin === 'TENDER_BDM' && empty($d['linked_bdm_plan_id'])) {
            $errs[] = 'linked_bdm_plan_id required for TENDER_BDM';
        }
        if ($origin === 'TENDER_PUBLIC' && empty($d['linked_nest_tender_id'])) {
            $errs[] = 'linked_nest_tender_id required for TENDER_PUBLIC';
        }
        // Slice 8j-DirectAwardHardening — direct awards need a real, defensible written trail.
        // Client-side JS is easy to bypass; server enforces:
        //   · non-empty · min 20 chars · not a placeholder (test/tbc/tba/n/a/asdf/todo/-/./? etc)
        if ($origin === 'DIRECT_AWARD') {
            $daj = trim((string)($d['direct_award_justification'] ?? ''));
            $normalised = strtolower(preg_replace('/\s+/', ' ', $daj));
            $placeholders = ['test','tbc','tba','todo','asdf','n/a','na','-','.','?','none','xxx','pending','later','tbd'];
            if ($daj === '') {
                $errs[] = 'direct_award_justification required for DIRECT_AWARD';
            } elseif (in_array($normalised, $placeholders, true)) {
                // Check placeholder BEFORE length so "tbc" is caught as placeholder, not too-short
                $errs[] = 'direct_award_justification looks like a placeholder — write a real audit-worthy explanation';
            } elseif (mb_strlen($daj) < 20) {
                $errs[] = 'direct_award_justification too short (min 20 chars — describe who awarded, why us, pre-agreed terms)';
            }
        }
        if ($errs) {
            return ['ok' => false, 'msg' => 'Please fix: ' . implode('; ', $errs), 'id' => 0];
        }

        // Look up customer name from id
        $db     = new DB();
        $custRs = $db->mySelect("SELECT fname, lname FROM customers WHERE c0d = :id AND deleted = 0", [':id' => (int)$d['project_customer_id']]);
        $custNm = !empty($custRs) ? trim(($custRs[0]['fname'] ?? '') . ' ' . ($custRs[0]['lname'] ?? '')) : '';

        $migrated = ($origin === 'MIGRATED') ? 1 : 0;
        $crm      = ($origin === 'MIGRATED') ? 'MIGRATED' : 'NOT_STARTED';

        $r = $db->insertUpdate(
            "INSERT INTO c_projects
                (project_no, project_name, project_type, project_customer_id, project_customer,
                 project_tender_reference, origin, linked_bdm_plan_id, linked_nest_tender_id,
                 direct_award_justification, project_start_date, project_end_date,
                 contract_value, currency, default_margin_pct, region, district, ward,
                 crm_status, migrated_flag, migrated_note, project_lifecycle_stage,
                 created_by, edited_by)
             VALUES
                (:pn, :nm, :pt, :cid, :cnm, :tref, :org, :bpid, :ntid, :daj, :sd, :ed,
                 :cv, :cur, :mgn, :rg, :ds, :wd, :crm, :mig, :mgn2, 'NEW', :uid, :uid)",
            [
                ':pn'   => $this->nextProjectNo(),
                ':nm'   => trim((string)$d['project_name']),
                ':pt'   => (string)$d['project_type'],
                ':cid'  => (int)$d['project_customer_id'],
                ':cnm'  => $custNm,
                ':tref' => (string)($d['project_tender_reference'] ?? ''),
                ':org'  => $origin,
                ':bpid' => (int)($d['linked_bdm_plan_id'] ?? 0),
                ':ntid' => (int)($d['linked_nest_tender_id'] ?? 0),
                ':daj'  => (string)($d['direct_award_justification'] ?? ''),
                ':sd'   => !empty($d['project_start_date']) ? $d['project_start_date'] : null,
                ':ed'   => !empty($d['project_end_date'])   ? $d['project_end_date']   : null,
                ':cv'   => (float)($d['contract_value'] ?? 0),
                ':cur'  => (string)($d['currency'] ?? 'TZS'),
                ':mgn'  => (float)($d['default_margin_pct'] ?? 25),
                ':rg'   => (string)($d['region'] ?? ''),
                ':ds'   => (string)($d['district'] ?? ''),
                ':wd'   => (string)($d['ward'] ?? ''),
                ':crm'  => $crm,
                ':mig'  => $migrated,
                ':mgn2' => (string)($d['migrated_note'] ?? ''),
                ':uid'  => $uid,
            ]
        );

        $pid = (int)$r['last_id'];
        if ($pid > 0) {
            // Seed onboarding row + 9 checklist items
            $db->insertUpdate(
                "INSERT INTO crm_project_onboarding
                    (project_id, contract_ref, contract_value, contract_currency, contract_date, created_by, edited_by)
                 VALUES (:pid, :cref, :cv, :cur, :cd, :uid, :uid)",
                [
                    ':pid'  => $pid,
                    ':cref' => (string)($d['project_tender_reference'] ?? ''),
                    ':cv'   => (float)($d['contract_value'] ?? 0),
                    ':cur'  => (string)($d['currency'] ?? 'TZS'),
                    ':cd'   => !empty($d['project_start_date']) ? $d['project_start_date'] : null,
                    ':uid'  => $uid,
                ]
            );
            foreach (self::DEFAULT_CHECKLIST((string)($d['project_type'] ?? '')) as $item) {
                $db->insertUpdate(
                    "INSERT INTO crm_project_checklist_items (project_id, checklist_key, checklist_label, is_complete)
                     VALUES (:pid, :k, :l, 0)",
                    [':pid' => $pid, ':k' => $item['key'], ':l' => $item['label']]
                );
            }
        }
        return ['ok' => $pid > 0, 'msg' => $pid > 0 ? 'Project created.' : 'Save failed.', 'id' => $pid];
    }
}
