<?php
/**
 * WorksPhases — stable containers of work on a Works project.
 *
 * A phase says WHICH BOQ items get delivered in that phase (with a planned quantity
 * that may be a subset of contract_qty) and WHICH activities the site will perform.
 * The CR (Consolidated Request) is a re-submittable child of a phase — see WorksCrs.
 *
 * Sum-of-planned-qty across phases for a given BOQ item should stay ≤ contract_qty;
 * we soft-warn but don't hard-block (a phase can overrun; the next phase absorbs less).
 */
class WorksPhases extends Model
{
    /* ── Phase header ─────────────────────────────────────────────────── */

    public function create(int $projectId, array $d, int $userId): int
    {
        if ($projectId <= 0) throw new RuntimeException('project_id required.');
        if (trim((string)($d['title'] ?? '')) === '') throw new RuntimeException('Phase title required.');
        $db = new DB();
        $nextNo = (int)($db->mySelect(
            "SELECT COALESCE(MAX(phase_no),0)+1 n FROM works_phases WHERE project_id = :p AND deleted = 0",
            [':p' => $projectId]
        )[0]['n'] ?? 1);
        $fb = $db->insertUpdate(
            "INSERT INTO works_phases
                (project_id, phase_no, code, title, description, planned_start, planned_end,
                 status, created_by, created_on)
             VALUES (:p, :n, :c, :t, :d, :ps, :pe,
                     'PLANNED', :u, NOW())",
            [
                ':p' => $projectId, ':n' => $nextNo,
                ':c' => (string)($d['code'] ?? ''),
                ':t' => (string)$d['title'],
                ':d' => (string)($d['description'] ?? ''),
                ':ps' => !empty($d['planned_start']) ? (string)$d['planned_start'] : null,
                ':pe' => !empty($d['planned_end'])   ? (string)$d['planned_end']   : null,
                ':u' => $userId,
            ]
        );
        return (int)($fb['last_id'] ?? 0);
    }

    public function updateHeader(int $phaseId, array $d, int $userId): void
    {
        $ph = $this->findById($phaseId);
        if (!$ph) throw new RuntimeException('Phase not found.');
        if ($ph['status'] === 'CLOSED' || $ph['status'] === 'CANCELLED') {
            throw new RuntimeException('Phase is ' . $ph['status'] . ' — cannot edit.');
        }
        $db = new DB();
        $db->insertUpdate(
            "UPDATE works_phases SET
                code = :c, title = :t, description = :d,
                planned_start = :ps, planned_end = :pe,
                edited_by = :u, edited_on = NOW()
              WHERE c0d = :id",
            [
                ':c' => (string)($d['code'] ?? $ph['code']),
                ':t' => (string)($d['title'] ?? $ph['title']),
                ':d' => (string)($d['description'] ?? $ph['description']),
                ':ps' => !empty($d['planned_start']) ? (string)$d['planned_start'] : ($ph['planned_start'] ?: null),
                ':pe' => !empty($d['planned_end'])   ? (string)$d['planned_end']   : ($ph['planned_end']   ?: null),
                ':u' => $userId, ':id' => $phaseId,
            ]
        );
    }

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

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

    /** Called by WorksCrs when a CR goes ACTIVE — flips phase PLANNED → ACTIVE. */
    public function markActive(int $phaseId): void
    {
        $db = new DB();
        $db->insertUpdate(
            "UPDATE works_phases SET status = 'ACTIVE', actual_start = COALESCE(actual_start, CURDATE()), edited_on = NOW()
              WHERE c0d = :id AND status IN ('PLANNED','ACTIVE')",
            [':id' => $phaseId]
        );
    }

    public function markClosed(int $phaseId, int $userId): void
    {
        $db = new DB();
        $db->insertUpdate(
            "UPDATE works_phases SET status = 'CLOSED', actual_end = CURDATE(), edited_by = :u, edited_on = NOW()
              WHERE c0d = :id",
            [':u' => $userId, ':id' => $phaseId]
        );
    }

    /**
     * Whether a phase can be closed: at least one CR must exist and every non-cancelled CR
     * must be CLOSED or SUPERSEDED. Roles: PC / PM / Admin.
     */
    public function canClose(int $phaseId, string $userGroup): array
    {
        $ph = $this->findById($phaseId);
        if (!$ph)                                  return ['ok' => false, 'msg' => 'Phase not found.'];
        if (!in_array($ph['status'], ['PLANNED','ACTIVE'], true)) {
            return ['ok' => false, 'msg' => 'Phase already ' . $ph['status'] . '.'];
        }
        if (!in_array($userGroup, ['Projects Coordinator','Project Manager','Administrator'], true)) {
            return ['ok' => false, 'msg' => 'Only PC / PM / Admin can close a phase.'];
        }
        $db = new DB();
        $crs = $db->mySelect(
            "SELECT c0d, cr_no, status FROM works_crs WHERE phase_id = :p AND deleted = 0",
            [':p' => $phaseId]
        ) ?: [];
        if (empty($crs)) return ['ok' => false, 'msg' => 'Phase has no CRs — cannot close an empty phase.'];
        $open = array_filter($crs, fn($c) => !in_array($c['status'], ['CLOSED','SUPERSEDED','CANCELLED'], true));
        if (!empty($open)) {
            $refs = array_map(fn($r) => $r['cr_no'] . ' (' . $r['status'] . ')', array_slice($open, 0, 3));
            return ['ok' => false, 'msg' => 'Open CRs blocking close: ' . implode(', ', $refs) . (count($open) > 3 ? ' …' : '')];
        }
        return ['ok' => true, 'msg' => ''];
    }

    /** Close a phase — enforces canClose(). Stamps closed_by/on. */
    public function close(int $phaseId, int $userId, string $userName, string $userGroup): array
    {
        $chk = $this->canClose($phaseId, $userGroup);
        if (!$chk['ok']) return $chk;
        $db = new DB();
        $db->insertUpdate(
            "UPDATE works_phases
                SET status = 'CLOSED', actual_end = CURDATE(),
                    closed_by = :u, closed_by_name = :un, closed_on = NOW(),
                    edited_by = :u, edited_on = NOW()
              WHERE c0d = :id",
            [':u' => $userId, ':un' => $userName, ':id' => $phaseId]
        );
        return ['ok' => true, 'msg' => 'Phase closed.'];
    }

    /* ── Phase → BOQ item bindings ────────────────────────────────────── */

    /** Attach a BOQ item to this phase with a planned qty (sub-portion of the contract qty). */
    public function attachBoqItem(int $phaseId, int $boqItemId, float $plannedQty, string $notes = ''): int
    {
        if ($plannedQty <= 0) throw new RuntimeException('planned_qty must be > 0.');
        $db = new DB();
        // Idempotent: if the item is already attached, update the planned_qty.
        $existing = $db->mySelect(
            "SELECT c0d FROM works_phase_boq_items WHERE phase_id = :p AND boq_item_id = :i",
            [':p' => $phaseId, ':i' => $boqItemId]
        );
        if (!empty($existing)) {
            $db->insertUpdate(
                "UPDATE works_phase_boq_items SET planned_qty = :q, notes = :nt WHERE c0d = :id",
                [':q' => $plannedQty, ':nt' => $notes, ':id' => (int)$existing[0]['c0d']]
            );
            return (int)$existing[0]['c0d'];
        }
        $fb = $db->insertUpdate(
            "INSERT INTO works_phase_boq_items (phase_id, boq_item_id, planned_qty, notes, created_on)
             VALUES (:p, :i, :q, :nt, NOW())",
            [':p' => $phaseId, ':i' => $boqItemId, ':q' => $plannedQty, ':nt' => $notes]
        );
        return (int)($fb['last_id'] ?? 0);
    }

    public function boqItemsFor(int $phaseId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT pbi.c0d, pbi.boq_item_id, pbi.planned_qty, pbi.notes,
                    bi.item_no, bi.description, bi.unit, bi.contract_qty, bi.contract_rate,
                    bi.cumulative_qty_done
               FROM works_phase_boq_items pbi
               JOIN works_boq_items bi ON bi.c0d = pbi.boq_item_id AND bi.deleted = 0
              WHERE pbi.phase_id = :p
              ORDER BY bi.c0d",
            [':p' => $phaseId]
        ) ?: [];
    }

    /* ── Phase activities ─────────────────────────────────────────────── */

    public function addActivity(int $phaseId, array $d): int
    {
        if (trim((string)($d['title'] ?? '')) === '') throw new RuntimeException('Activity title required.');
        $db = new DB();
        $nextNo = (int)($db->mySelect(
            "SELECT COALESCE(MAX(activity_no),0)+1 n FROM works_phase_activities WHERE phase_id = :p AND deleted = 0",
            [':p' => $phaseId]
        )[0]['n'] ?? 1);
        $fb = $db->insertUpdate(
            "INSERT INTO works_phase_activities
                (phase_id, activity_no, code, title, description, unit, planned_qty,
                 linked_boq_item_id, created_on)
             VALUES (:p, :n, :c, :t, :d, :u, :q,
                     :bi, NOW())",
            [
                ':p' => $phaseId, ':n' => $nextNo,
                ':c' => (string)($d['code'] ?? ''),
                ':t' => (string)$d['title'],
                ':d' => (string)($d['description'] ?? ''),
                ':u' => (string)($d['unit'] ?? ''),
                ':q' => (float)($d['planned_qty'] ?? 0),
                ':bi' => (int)($d['linked_boq_item_id'] ?? 0),
            ]
        );
        return (int)($fb['last_id'] ?? 0);
    }

    public function activitiesFor(int $phaseId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM works_phase_activities WHERE phase_id = :p AND deleted = 0 ORDER BY activity_no",
            [':p' => $phaseId]
        ) ?: [];
    }
}
