<?php
/**
 * ProjectStarter — per-project-type onboarding checklists.
 *
 * On Exec approval of TENDER_CONTRACT the project is auto-provisioned with three seats
 * (Accountant, Procurement, Project Manager) and this model seeds a project-type-specific
 * task pack that the Project Manager works through:
 *
 *   - Goods:    7 tasks (PC nomination → PO review → scrub → source → LPO batch → invoice)
 *   - Services: 6 tasks (PC nomination → PM plan → generators → float → Field Supervisor → callout)
 *   - Works:    7 tasks (PC nomination → BOQ → phase planning → Field Supervisor → first CR → LPO)
 *
 * Certain task keys are "seat nominations" — completing them hydrates a role column on
 * c_projects and writes a c_projects_role_changes audit row. The mapping is table-driven so
 * we can add more seats (Field Supervisor, Warehouse Lead, ...) without special-casing.
 *
 * Templates live here (not in DB) — cheap to iterate, easy to audit in code review.
 */
class ProjectStarter extends Model
{
    /**
     * task_key → seat column mapping. On complete() of a matching task, the owner user is
     * written onto c_projects (col_id + col_name) and a role_changes row is logged.
     */
    private const SEAT_TASKS = [
        'ASSIGN_PC' => ['col_id' => 'pc_id', 'col_name' => 'pc_name', 'slot' => 'PROJECTS_COORDINATOR', 'label' => 'Projects Coordinator'],
        'ASSIGN_FS' => ['col_id' => 'fs_id', 'col_name' => 'fs_name', 'slot' => 'FIELD_SUPERVISOR',     'label' => 'Field Supervisor'],
    ];

    /* ── Templates per project_type ───────────────────────────────────────── */

    public function templateFor(string $projectType): array
    {
        $type = strtolower(trim($projectType));
        switch ($type) {
            case 'goods':    return $this->goodsPack();
            case 'services': return $this->servicesPack();
            case 'works':    return $this->worksPack();
            default:         return $this->goodsPack();
        }
    }

    private function common_pc(): array
    {
        // For seat-nomination tasks the owner_slot is the NOMINEE's role — the reassign
        // dropdown on ProjectStarter/index then correctly lists PCs (not PMs). The PM
        // still drives the interaction; the assigned owner IS the seat holder that gets
        // written onto c_projects at complete().
        return [
            'task_key'         => 'ASSIGN_PC',
            'task_label'       => 'Nominate Projects Coordinator',
            'task_description' => 'Choose the PC who will run day-to-day project coordination. On save, the PC is written onto the project and appears in their queue.',
            'owner_slot'       => 'PROJECTS_COORDINATOR',
            'sequence'         => 10,
            'due_offset_days'  => 1,
            'target_url_template' => 'ProjectStarter/index/{project_id}',
        ];
    }

    private function goodsPack(): array
    {
        return [
            $this->common_pc(),
            [
                'task_key' => 'REVIEW_CLIENT_PO', 'task_label' => 'Review the auto-created client PO',
                'task_description' => 'Baseline PO seeded from the approved tender pricing. Confirm items, quantities, delivery terms.',
                'owner_slot' => 'PROJECTS_COORDINATOR', 'sequence' => 20, 'due_offset_days' => 2,
                'target_url_template' => 'GoodsClientPos/forProject/{project_id}',
            ],
            [
                'task_key' => 'PC_SCRUB_PO', 'task_label' => 'PC scrub client PO items',
                'task_description' => 'Set default margin, verify line specs, prepare for procurement sourcing.',
                'owner_slot' => 'PROJECTS_COORDINATOR', 'sequence' => 30, 'due_offset_days' => 4,
                'target_url_template' => 'GoodsScrub/scrubPo/{project_id}',
            ],
            [
                'task_key' => 'PROC_SOURCE', 'task_label' => 'Procurement source items',
                'task_description' => 'Choose sourcing method per line, raise supplier RFQs, assess quotes.',
                'owner_slot' => 'PROCUREMENT', 'sequence' => 40, 'due_offset_days' => 10,
                'target_url_template' => 'GoodsRfq/fromProject/{project_id}',
            ],
            [
                'task_key' => 'DRAFT_LPO_BATCH', 'task_label' => 'Draft supplier LPO batch',
                'task_description' => 'Assemble awarded quotes into the LPO batch, submit for approval chain.',
                'owner_slot' => 'PROCUREMENT', 'sequence' => 50, 'due_offset_days' => 12,
                'target_url_template' => 'Lpos/draft/{project_id}',
            ],
            [
                'task_key' => 'APPROVE_LPO_BATCH', 'task_label' => 'LPO batch approvals',
                'task_description' => 'PC → PM → FM → Exec chain for the LPO batch (auto-tracked via fin_chains).',
                'owner_slot' => 'PROJECT_MANAGER', 'sequence' => 60, 'due_offset_days' => 15,
                'target_url_template' => 'FinChains/myQueue',
            ],
            [
                'task_key' => 'DRAFT_INVOICE', 'task_label' => 'Draft customer invoice after delivery',
                'task_description' => 'On delivery completion, PC drafts the customer invoice; walks Approver chain.',
                'owner_slot' => 'PROJECTS_COORDINATOR', 'sequence' => 70, 'due_offset_days' => 25,
                'target_url_template' => 'Sales/draft/{project_id}',
            ],
        ];
    }

    private function servicesPack(): array
    {
        return [
            $this->common_pc(),
            [
                'task_key' => 'SEED_PM_PLAN', 'task_label' => 'Seed service PM plan',
                'task_description' => 'Create the preventive-maintenance plan header + task list for the site.',
                'owner_slot' => 'PROJECTS_COORDINATOR', 'sequence' => 20, 'due_offset_days' => 3,
                'target_url_template' => 'ServicePmPlans/create/{project_id}',
            ],
            [
                'task_key' => 'REG_EQUIPMENT', 'task_label' => 'Register on-site equipment',
                'task_description' => 'Add branch generators / AC units / other tracked assets so PM cycles can run.',
                'owner_slot' => 'PROJECTS_COORDINATOR', 'sequence' => 30, 'due_offset_days' => 5,
                'target_url_template' => 'Assets/addForProject/{project_id}',
            ],
            [
                'task_key' => 'RAISE_FLOAT', 'task_label' => 'Raise float requisition',
                'task_description' => 'Kick off the requisition ladder — PC drafts, PM/FM/Exec approve, Accountant issues cash.',
                'owner_slot' => 'PROJECTS_COORDINATOR', 'sequence' => 40, 'due_offset_days' => 7,
                'target_url_template' => 'ServiceRequisitions/create/{project_id}',
            ],
            [
                'task_key' => 'ASSIGN_FS', 'task_label' => 'Assign Field Supervisor',
                'task_description' => 'Nominate the on-site supervisor who will log visits, callouts and DPRs. Completing hydrates the FS seat on the project.',
                // owner_slot = nominee role so the reassign dropdown lists Field Supervisors.
                'owner_slot' => 'FIELD_SUPERVISOR', 'sequence' => 50, 'due_offset_days' => 8,
                'target_url_template' => 'ProjectStarter/index/{project_id}',
            ],
            [
                'task_key' => 'CALLOUT_PLAYBOOK', 'task_label' => 'Link callout playbook',
                'task_description' => 'Choose the SLA & escalation policy that governs unplanned callouts on this site.',
                'owner_slot' => 'PROJECT_MANAGER', 'sequence' => 60, 'due_offset_days' => 10,
                'target_url_template' => 'SpCallouts/playbook/{project_id}',
            ],
        ];
    }

    private function worksPack(): array
    {
        return [
            $this->common_pc(),
            [
                'task_key' => 'IMPORT_BOQ', 'task_label' => 'Import BOQ from tender',
                'task_description' => 'Load the priced BOQ (from tender pricing) into the works schedule for phase planning.',
                'owner_slot' => 'PROJECTS_COORDINATOR', 'sequence' => 20, 'due_offset_days' => 3,
                'target_url_template' => 'WorksBoq/view/{project_id}',
            ],
            [
                'task_key' => 'PHASE_PLANNING', 'task_label' => 'Phase planning header + first phase',
                'task_description' => 'Set the phase list, materials/tools/manpower budgets for the opening phase.',
                'owner_slot' => 'PROJECTS_COORDINATOR', 'sequence' => 30, 'due_offset_days' => 6,
                'target_url_template' => 'WorksPhases/plan/{project_id}',
            ],
            [
                'task_key' => 'ASSIGN_FS', 'task_label' => 'Assign Field Supervisor',
                'task_description' => 'Nominate the on-site supervisor who owns the DPRs and issues the first PR1s. Completing hydrates the FS seat on the project.',
                // owner_slot = nominee role so the reassign dropdown lists Field Supervisors.
                'owner_slot' => 'FIELD_SUPERVISOR', 'sequence' => 40, 'due_offset_days' => 8,
                'target_url_template' => 'ProjectStarter/index/{project_id}',
            ],
            [
                'task_key' => 'FIRST_CR', 'task_label' => 'Raise first Contingency Request',
                'task_description' => 'Field team raises the opening CR (materials & labour needed to mobilise).',
                'owner_slot' => 'FIELD_SUPERVISOR', 'sequence' => 50, 'due_offset_days' => 10,
                'target_url_template' => 'WorksCrs/create/{project_id}',
            ],
            [
                'task_key' => 'CR_APPROVALS', 'task_label' => 'CR approval ladder',
                'task_description' => 'PC → PM → FM → Exec ladder on the CR (via next_check_status).',
                'owner_slot' => 'PROJECT_MANAGER', 'sequence' => 60, 'due_offset_days' => 12,
                'target_url_template' => 'WorksCrs/create/{project_id}',
            ],
            [
                'task_key' => 'DRAFT_LPO', 'task_label' => 'Draft first supplier LPO',
                'task_description' => 'Convert awarded CR items into a supplier LPO; submit for approval.',
                'owner_slot' => 'PROCUREMENT', 'sequence' => 70, 'due_offset_days' => 15,
                'target_url_template' => 'Lpos/draft/{project_id}',
            ],
        ];
    }

    /* ── Seed on project creation ─────────────────────────────────────────── */

    /**
     * Insert one row per template task for the new project.
     * PM-owned rows are pre-assigned to the passed PM.
     * Idempotent — if any row already exists for (project_id, task_key), that row is skipped.
     */
    public function seed(int $projectId, string $projectType, int $pmId, string $pmName): int
    {
        if ($projectId <= 0) return 0;
        $db    = new DB();
        $today = new DateTimeImmutable('today');
        $inserted = 0;
        foreach ($this->templateFor($projectType) as $t) {
            $existing = $db->mySelect("SELECT c0d FROM project_starter_tasks WHERE project_id = :p AND task_key = :k AND deleted = 0", [':p' => $projectId, ':k' => $t['task_key']]);
            if (!empty($existing)) continue;
            $ownerId = 0; $ownerName = '';
            if ($t['owner_slot'] === 'PROJECT_MANAGER' && $pmId > 0) { $ownerId = $pmId; $ownerName = $pmName; }
            $dueDate = $today->modify('+' . (int)$t['due_offset_days'] . ' days')->format('Y-m-d');
            $db->insertUpdate(
                "INSERT INTO project_starter_tasks
                    (project_id, task_key, task_label, task_description, owner_slot,
                     owner_user_id, owner_user_name, sequence, due_offset_days, due_date,
                     target_url_template, status, created_by, created_on)
                 VALUES (:p, :k, :lbl, :desc, :slot,
                         :oid, :on, :sq, :off, :dd,
                         :url, 'OPEN', :cb, NOW())",
                [
                    ':p'    => $projectId, ':k' => $t['task_key'], ':lbl' => $t['task_label'],
                    ':desc' => $t['task_description'], ':slot' => $t['owner_slot'],
                    ':oid'  => $ownerId, ':on' => $ownerName, ':sq' => $t['sequence'],
                    ':off'  => $t['due_offset_days'], ':dd' => $dueDate,
                    ':url'  => $t['target_url_template'], ':cb' => $pmId,
                ]
            );
            $inserted++;
        }
        // Slice 7: single roll-up ping to the PM (rather than one-per-task, which would flood the bell).
        if ($inserted > 0 && $pmId > 0) {
            try {
                if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
                (new Notifications())->notify(
                    $pmId, 'STARTER_TASK_ASSIGNED',
                    'Onboarding checklist ready — ' . $inserted . ' task(s)',
                    'Nominate the Projects Coordinator first, then work down the list.',
                    'ProjectStarter/index/' . $projectId,
                    'c_projects', $projectId, $pmId, $pmName
                );
            } catch (\Throwable $e) { /* best-effort */ }
        }
        return $inserted;
    }

    /* ── Read ─────────────────────────────────────────────────────────────── */

    public function tasksFor(int $projectId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM project_starter_tasks WHERE project_id = :p AND deleted = 0 ORDER BY sequence, c0d",
            [':p' => $projectId]
        ) ?: [];
    }

    public function findTask(int $taskId): ?array
    {
        $db = new DB();
        $r = $db->mySelect("SELECT * FROM project_starter_tasks WHERE c0d = :id AND deleted = 0", [':id' => $taskId]);
        return $r[0] ?? null;
    }

    /** All OPEN tasks currently assigned to a given user (for the "My onboarding tasks" queue). */
    public function openForUser(int $userId): array
    {
        return $this->openForUserFiltered($userId, []);
    }

    /**
     * OPEN tasks for a user, with filter + pagination for the myQueue screen.
     * $filter: q (project_no/name/task_label search), project_type, customer_id.
     * When $filter['page_size'] > 0 applies LIMIT/OFFSET.
     */
    public function openForUserFiltered(int $userId, array $filter = []): array
    {
        $db = new DB();
        [$where, $params] = $this->buildQueueFilter($userId, $filter);
        $sql = "SELECT t.*, p.project_no, p.project_name, p.project_type, p.project_customer, p.project_customer_id
                  FROM project_starter_tasks t
                  JOIN c_projects p ON p.c0d = t.project_id
                 WHERE " . implode(' AND ', $where) . "
                 ORDER BY t.due_date ASC, t.sequence ASC";
        $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 count under the same filter — for pagination footer. */
    public function countOpenForUser(int $userId, array $filter = []): int
    {
        $db = new DB();
        [$where, $params] = $this->buildQueueFilter($userId, $filter);
        $r = $db->mySelect(
            "SELECT COUNT(*) n
               FROM project_starter_tasks t
               JOIN c_projects p ON p.c0d = t.project_id
              WHERE " . implode(' AND ', $where),
            $params
        );
        return (int)($r[0]['n'] ?? 0);
    }

    /** Distinct customers across this user's OPEN starter tasks — for the filter dropdown. */
    public function distinctCustomersInQueue(int $userId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT DISTINCT p.project_customer_id AS c0d, p.project_customer AS name
               FROM project_starter_tasks t
               JOIN c_projects p ON p.c0d = t.project_id
              WHERE t.owner_user_id = :u AND t.status = 'OPEN' AND t.deleted = 0
                AND p.project_customer_id > 0
              ORDER BY p.project_customer",
            [':u' => $userId]
        ) ?: [];
    }

    private function buildQueueFilter(int $userId, array $filter): array
    {
        $where  = ["t.owner_user_id = :u", "t.status = 'OPEN'", 't.deleted = 0'];
        $params = [':u' => $userId];
        if (!empty($filter['project_type'])) { $where[] = 'p.project_type = :pt'; $params[':pt'] = $filter['project_type']; }
        if (!empty($filter['customer_id']))  { $where[] = 'p.project_customer_id = :c'; $params[':c'] = (int)$filter['customer_id']; }
        if (!empty($filter['q'])) {
            $where[] = '(p.project_no LIKE :q OR p.project_name LIKE :q OR t.task_label LIKE :q)';
            $params[':q'] = '%' . $filter['q'] . '%';
        }
        return [$where, $params];
    }

    /* ── Write ────────────────────────────────────────────────────────────── */

    public function assignOwner(int $taskId, int $userId, string $userName, int $actorId): void
    {
        $t = $this->findTask($taskId);
        if (!$t) throw new RuntimeException('Task not found.');
        if ($t['status'] !== 'OPEN') throw new RuntimeException('Only OPEN tasks can be reassigned.');
        $db = new DB();
        $db->insertUpdate(
            "UPDATE project_starter_tasks SET owner_user_id = :u, owner_user_name = :un, edited_by = :a, edited_on = NOW() WHERE c0d = :id",
            [':u' => $userId, ':un' => $userName, ':a' => $actorId, ':id' => $taskId]
        );
        // Slice 7: notify the new owner (unless they're reassigning to themselves).
        if ($userId > 0 && $userId !== (int)$t['owner_user_id']) {
            try {
                if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
                (new Notifications())->notify(
                    $userId, 'STARTER_TASK_ASSIGNED',
                    'New onboarding task: ' . (string)$t['task_label'],
                    'Assigned on project #' . (int)$t['project_id'] . '. Due ' . (string)($t['due_date'] ?? ''),
                    'ProjectStarter/index/' . (int)$t['project_id'],
                    'project_starter_tasks', $taskId, $actorId, ''
                );
            } catch (\Throwable $e) { /* best-effort */ }
        }
    }

    /**
     * Mark a task DONE. Special-case: ASSIGN_PC hydrates c_projects.pc_id/name and writes
     * a c_projects_role_changes audit row so the PC nomination is treated as a first-class seat.
     */
    public function complete(int $taskId, int $actorId, string $actorName, string $notes = ''): void
    {
        $t = $this->findTask($taskId);
        if (!$t) throw new RuntimeException('Task not found.');
        if ($t['status'] !== 'OPEN') throw new RuntimeException('Task is already ' . $t['status'] . '.');
        if ((int)$t['owner_user_id'] <= 0) throw new RuntimeException('Assign an owner before completing.');

        $db = new DB();

        // Seat-nomination tasks (ASSIGN_PC, ASSIGN_FS, ...) hydrate a c_projects column and
        // log the change to c_projects_role_changes. Table-driven so we can add new seats
        // without another special-case branch.
        $seat = self::SEAT_TASKS[$t['task_key']] ?? null;
        if ($seat !== null) {
            $projectId = (int)$t['project_id'];
            $colId     = (string)$seat['col_id'];
            $colName   = (string)$seat['col_name'];
            $slot      = (string)$seat['slot'];
            // Column names in the query are static (from a hard-coded const) so a string
            // interpolation here is safe — no user input reaches the SQL identifier.
            $oldRow    = $db->mySelect("SELECT `$colId` AS old_id, `$colName` AS old_name FROM c_projects WHERE c0d = :p", [':p' => $projectId]);
            $oldId     = (int)($oldRow[0]['old_id']   ?? 0);
            $oldName   = (string)($oldRow[0]['old_name'] ?? '');
            $db->insertUpdate(
                "UPDATE c_projects SET `$colId` = :nid, `$colName` = :nnm, edited_by = :a, edited_on = NOW() WHERE c0d = :p",
                [':nid' => (int)$t['owner_user_id'], ':nnm' => (string)$t['owner_user_name'], ':a' => $actorId, ':p' => $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, :slot, :oid, :onm, :nid, :nnm, :cb, :cbn, :r, NOW())",
                [
                    ':p'   => $projectId, ':slot' => $slot,
                    ':oid' => $oldId, ':onm' => $oldName,
                    ':nid' => (int)$t['owner_user_id'], ':nnm' => (string)$t['owner_user_name'],
                    ':cb'  => $actorId, ':cbn' => $actorName,
                    ':r'   => 'Nominated via ' . (string)$t['task_key'] . ' starter task',
                ]
            );
        }

        $db->insertUpdate(
            "UPDATE project_starter_tasks SET
                 status = 'DONE', completed_by = :a, completed_by_name = :an, completed_on = NOW(),
                 notes = COALESCE(NULLIF(:nt,''), notes),
                 edited_by = :a, edited_on = NOW()
               WHERE c0d = :id",
            [':a' => $actorId, ':an' => $actorName, ':nt' => $notes, ':id' => $taskId]
        );
    }

    public function skip(int $taskId, int $actorId, string $actorName, string $reason): void
    {
        $t = $this->findTask($taskId);
        if (!$t) throw new RuntimeException('Task not found.');
        if ($t['status'] !== 'OPEN') throw new RuntimeException('Task is already ' . $t['status'] . '.');
        if (trim($reason) === '') throw new RuntimeException('A reason is required when skipping.');
        $db = new DB();
        $db->insertUpdate(
            "UPDATE project_starter_tasks SET
                 status = 'SKIPPED', completed_by = :a, completed_by_name = :an, completed_on = NOW(),
                 notes = :nt, edited_by = :a, edited_on = NOW()
               WHERE c0d = :id",
            [':a' => $actorId, ':an' => $actorName, ':nt' => $reason, ':id' => $taskId]
        );
    }

    public function reopen(int $taskId, int $actorId): void
    {
        $t = $this->findTask($taskId);
        if (!$t) throw new RuntimeException('Task not found.');
        if ($t['status'] === 'OPEN') return;
        $db = new DB();
        $db->insertUpdate(
            "UPDATE project_starter_tasks SET
                 status = 'OPEN', completed_by = 0, completed_by_name = '', completed_on = NULL,
                 edited_by = :a, edited_on = NOW()
               WHERE c0d = :id",
            [':a' => $actorId, ':id' => $taskId]
        );
    }

    /** Substitute {project_id} placeholder in a target_url_template with the actual id. */
    public function resolveUrl(array $task): string
    {
        return str_replace('{project_id}', (string)(int)$task['project_id'], (string)$task['target_url_template']);
    }
}
