<?php
/**
 * ServiceContracts — Slice 8S-2
 *
 * Contract data: sites + assets + SLA overrides + auto-generated PM schedule.
 * Once ACTIVE the schedule feeds PmServices (Slice S3) daily via cron.
 */
class ServiceContracts extends Model
{
    public function nextContractNo(): string
    {
        $db  = new DB();
        $yr  = date('Y');
        $r   = $db->mySelect("SELECT contract_no FROM service_contracts WHERE contract_no LIKE :p ORDER BY c0d DESC LIMIT 1", [':p' => 'SC-' . $yr . '-%']);
        $next = 1;
        if (!empty($r) && preg_match('/SC-\d{4}-(\d+)/', (string)$r[0]['contract_no'], $m)) $next = ((int)$m[1]) + 1;
        return sprintf('SC-%s-%04d', $yr, $next);
    }

    public function list(string $status = ''): array
    {
        $db = new DB();
        $where = "WHERE c.deleted = 0";
        $params = [];
        if ($status !== '') { $where .= " AND c.status = :s"; $params[':s'] = $status; }
        return $db->mySelect(
            "SELECT c.c0d, c.contract_no, c.customer_name, c.currency, c.contract_value, c.monthly_retainer,
                    c.start_date, c.end_date, c.status, c.spare_policy, c.invoice_cadence, c.created_on,
                    p.project_no, p.project_name,
                    (SELECT COUNT(*) FROM service_contract_sites  WHERE contract_id = c.c0d AND is_active = 1) AS site_count,
                    (SELECT COUNT(*) FROM service_contract_assets WHERE contract_id = c.c0d AND is_active = 1) AS asset_count,
                    (SELECT COUNT(*) FROM service_contract_pm_schedule WHERE contract_id = c.c0d AND is_active = 1) AS pm_count
               FROM service_contracts c
          LEFT JOIN c_projects p ON p.c0d = c.project_id
               $where
              ORDER BY c.c0d DESC",
            $params
        ) ?: [];
    }

    public function find(int $id): array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT c.*, p.project_no, p.project_name
               FROM service_contracts c
          LEFT JOIN c_projects p ON p.c0d = c.project_id
              WHERE c.c0d = :id AND c.deleted = 0",
            [':id' => $id]
        );
        return $r[0] ?? [];
    }

    public function sitesFor(int $contractId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT s.*, (SELECT COUNT(*) FROM service_contract_assets a WHERE a.site_id = s.c0d AND a.is_active = 1) AS asset_count
               FROM service_contract_sites s WHERE s.contract_id = :c ORDER BY s.name",
            [':c' => $contractId]
        ) ?: [];
    }

    public function assetsFor(int $contractId, ?int $siteId = null): array
    {
        $db = new DB();
        $where = "WHERE a.contract_id = :c";
        $params = [':c' => $contractId];
        if ($siteId !== null) { $where .= " AND a.site_id = :s"; $params[':s'] = $siteId; }
        return $db->mySelect(
            "SELECT a.*, s.name AS site_name, s.site_code,
                    t.code AS asset_type_code, t.name AS asset_type_name, t.icon AS asset_icon
               FROM service_contract_assets a
          LEFT JOIN service_contract_sites s ON s.c0d = a.site_id
          LEFT JOIN asset_types t ON t.c0d = a.asset_type_id
               $where
              ORDER BY s.name, a.asset_no",
            $params
        ) ?: [];
    }

    public function slasFor(int $contractId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT s.*, c.code, c.name, c.sla_response_hrs AS default_response, c.sla_restoration_hrs AS default_restore
               FROM service_contract_slas s
               JOIN callout_categories c ON c.c0d = s.category_id
              WHERE s.contract_id = :c
              ORDER BY c.priority",
            [':c' => $contractId]
        ) ?: [];
    }

    public function scheduleFor(int $contractId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT sc.*, a.asset_no, a.serial_no, a.make, a.model,
                    s.name AS site_name, t.code AS asset_type_code, t.icon AS asset_icon,
                    tpl.code AS template_code, tpl.name AS template_name, tpl.version AS template_version
               FROM service_contract_pm_schedule sc
               JOIN service_contract_assets a  ON a.c0d = sc.contract_asset_id
          LEFT JOIN service_contract_sites s   ON s.c0d = a.site_id
          LEFT JOIN asset_types t              ON t.c0d = a.asset_type_id
          LEFT JOIN pm_templates tpl           ON tpl.c0d = sc.template_id
              WHERE sc.contract_id = :c
              ORDER BY sc.next_due_on, s.name",
            [':c' => $contractId]
        ) ?: [];
    }

    /**
     * Slice 8S-9 · Re-run the Services checklist auto-tick on the linked project after every
     * mutation. Best-effort — never throws (a checklist quirk mustn't break contract editing).
     */
    private function syncChecklistForContract(int $contractId): void
    {
        try {
            $db  = new DB();
            $r   = $db->mySelect("SELECT project_id FROM service_contracts WHERE c0d = :id", [':id' => $contractId]);
            $pid = !empty($r) ? (int)$r[0]['project_id'] : 0;
            if ($pid <= 0) return;
            if (!class_exists('Projects')) require_once ROOT . '/app/models/Projects.php';
            (new Projects())->syncServicesChecklist($pid);
        } catch (\Throwable $e) { /* best-effort */ }
    }

    public function saveHeader(array $d, int $uid, string $uname): int
    {
        $projectId = (int)($d['project_id'] ?? 0);
        if ($projectId <= 0) throw new InvalidArgumentException('project_id required.');
        $db = new DB();
        $id = (int)($d['c0d'] ?? 0);
        $data = [
            ':pid' => $projectId,
            ':cid' => !empty($d['customer_id']) ? (int)$d['customer_id'] : null,
            ':cn'  => trim((string)($d['customer_name'] ?? '')) ?: null,
            ':sd'  => trim((string)($d['start_date'] ?? '')) ?: null,
            ':ed'  => trim((string)($d['end_date'] ?? '')) ?: null,
            ':cv'  => (float)($d['contract_value'] ?? 0),
            ':cur' => strtoupper(trim((string)($d['currency'] ?? 'TZS'))) ?: 'TZS',
            ':mr'  => isset($d['monthly_retainer']) && $d['monthly_retainer'] !== '' ? (float)$d['monthly_retainer'] : null,
            ':sp'  => strtoupper((string)($d['spare_policy'] ?? 'COMPANY')),
            ':ic'  => strtoupper((string)($d['invoice_cadence'] ?? 'MONTHLY_RETAINER')),
            ':pt'  => isset($d['payment_terms_days']) && $d['payment_terms_days'] !== '' ? (int)$d['payment_terms_days'] : 30,
            ':lr'  => isset($d['labour_rate_per_hr']) && $d['labour_rate_per_hr'] !== '' ? (float)$d['labour_rate_per_hr'] : null,
            ':cf'  => isset($d['callout_fee']) && $d['callout_fee'] !== '' ? (float)$d['callout_fee'] : null,
            ':em'  => isset($d['emergency_multiplier']) && $d['emergency_multiplier'] !== '' ? (float)$d['emergency_multiplier'] : 2.00,
            ':pm'  => isset($d['parts_markup_pct']) && $d['parts_markup_pct'] !== '' ? (float)$d['parts_markup_pct'] : 25.00,
            ':mkm' => isset($d['mileage_rate_per_km']) && $d['mileage_rate_per_km'] !== '' ? (float)$d['mileage_rate_per_km'] : null,
            ':fc'  => isset($d['free_callouts_per_month']) && $d['free_callouts_per_month'] !== '' ? (int)$d['free_callouts_per_month'] : 0,
            ':rc'  => strtoupper((string)($d['report_cadence'] ?? 'MONTHLY')),
            ':nt'  => trim((string)($d['notes'] ?? '')) ?: null,
        ];
        if ($id > 0) {
            $data[':id'] = $id;
            $db->insertUpdate(
                "UPDATE service_contracts SET
                    project_id=:pid, customer_id=:cid, customer_name=:cn,
                    start_date=:sd, end_date=:ed, contract_value=:cv, currency=:cur, monthly_retainer=:mr,
                    spare_policy=:sp, invoice_cadence=:ic, payment_terms_days=:pt,
                    labour_rate_per_hr=:lr, callout_fee=:cf, emergency_multiplier=:em,
                    parts_markup_pct=:pm, mileage_rate_per_km=:mkm, free_callouts_per_month=:fc,
                    report_cadence=:rc, notes=:nt, edited_on=NOW()
                  WHERE c0d=:id",
                $data
            );
            $this->syncChecklistForContract($id);
            return $id;
        }
        $data[':no'] = $this->nextContractNo();
        $data[':u']  = $uid;
        $data[':un'] = $uname;
        $fb = $db->insertUpdate(
            "INSERT INTO service_contracts
                (contract_no, project_id, customer_id, customer_name, start_date, end_date, contract_value, currency,
                 monthly_retainer, spare_policy, invoice_cadence, payment_terms_days,
                 labour_rate_per_hr, callout_fee, emergency_multiplier, parts_markup_pct, mileage_rate_per_km,
                 free_callouts_per_month, report_cadence, status, notes, created_by, created_by_name)
             VALUES
                (:no, :pid, :cid, :cn, :sd, :ed, :cv, :cur, :mr, :sp, :ic, :pt,
                 :lr, :cf, :em, :pm, :mkm, :fc, :rc, 'DRAFT', :nt, :u, :un)",
            $data
        );
        $newId = (int)($fb['last_id'] ?? 0);
        if ($newId > 0) $this->syncChecklistForContract($newId);
        return $newId;
    }

    /**
     * Slice 8Z-y · PM confirms the scope (sites target + equipment mix) they committed
     * to execution. Persists an equipment_scope_confirmed_json snapshot so the coverage
     * matrix has a stable denominator. Idempotent — re-calling refreshes the stamp.
     */
    public function saveScopeConfirmation(int $contractId, array $d, int $uid, string $uname): void
    {
        $c = $this->find($contractId);
        if (empty($c)) throw new RuntimeException('Contract not found.');
        $sitesTarget = (isset($d['sites_target']) && $d['sites_target'] !== '') ? (int)$d['sites_target'] : null;
        // equipment_json can be sent as JSON string or array — normalise
        $equipRaw = $d['equipment_json'] ?? '';
        if (is_array($equipRaw)) $equipJson = json_encode($equipRaw, JSON_UNESCAPED_UNICODE);
        else {
            $s = trim((string)$equipRaw);
            $equipJson = ($s === '' ? null : $s);
        }
        $db = new DB();
        $db->insertUpdate(
            "UPDATE service_contracts
                SET sites_target_confirmed         = :st,
                    equipment_scope_confirmed_json = :eq,
                    scope_confirmed_on             = NOW(),
                    scope_confirmed_by             = :u,
                    scope_confirmed_by_name        = :un,
                    edited_on                      = NOW()
              WHERE c0d = :id",
            [':st' => $sitesTarget, ':eq' => $equipJson, ':u' => $uid, ':un' => $uname, ':id' => $contractId]
        );
    }

    /**
     * Slice 8Z-v · PM confirms the DRAFT contract's spec (rates, cadences, gates)
     * matches the tender bid answers. Marks the moment on the contract; no state
     * change beyond that. UI uses this to flip the "confirm" toggle to green.
     * Any PM (or Admin) can toggle — idempotent (re-calling refreshes the stamp).
     */
    public function confirmSpec(int $contractId, int $uid, string $uname): void
    {
        $c = $this->find($contractId);
        if (empty($c)) throw new RuntimeException('Contract not found.');
        $db = new DB();
        $db->insertUpdate(
            "UPDATE service_contracts
                SET pm_spec_confirmed_on = NOW(),
                    pm_spec_confirmed_by = :u,
                    pm_spec_confirmed_by_name = :un,
                    edited_on = NOW()
              WHERE c0d = :id",
            [':u' => $uid, ':un' => $uname, ':id' => $contractId]
        );
    }

    public function activate(int $contractId, int $uid): void
    {
        $c = $this->find($contractId);
        if (empty($c)) throw new RuntimeException('Contract not found.');
        if ($c['status'] !== 'DRAFT') throw new RuntimeException('Only DRAFT contracts can be activated (current: ' . $c['status'] . ').');
        // Guard: needs at least one site + one asset
        $sites = $this->sitesFor($contractId);
        if (empty($sites)) throw new RuntimeException('Add at least one site first.');
        $assets = $this->assetsFor($contractId);
        if (empty($assets)) throw new RuntimeException('Add at least one asset first.');
        $db = new DB();
        $db->insertUpdate(
            "UPDATE service_contracts SET status='ACTIVE', activated_at=NOW(), edited_on=NOW() WHERE c0d=:id",
            [':id' => $contractId]
        );
        $this->syncChecklistForContract($contractId);
    }

    public function saveSite(array $d): int
    {
        $cid  = (int)($d['contract_id'] ?? 0);
        $name = trim((string)($d['name'] ?? ''));
        if ($cid <= 0 || $name === '') throw new InvalidArgumentException('contract_id + name required.');
        $db = new DB();
        $id = (int)($d['c0d'] ?? 0);
        $data = [
            ':c'    => $cid, ':sc' => trim((string)($d['site_code'] ?? '')) ?: null, ':n' => $name,
            ':pa'   => trim((string)($d['physical_address'] ?? '')) ?: null,
            ':rg'   => trim((string)($d['region'] ?? '')) ?: null,
            ':dt'   => trim((string)($d['district'] ?? '')) ?: null,
            ':lat'  => isset($d['gps_lat']) && $d['gps_lat'] !== '' ? (float)$d['gps_lat'] : null,
            ':lng'  => isset($d['gps_lng']) && $d['gps_lng'] !== '' ? (float)$d['gps_lng'] : null,
            ':scn'  => trim((string)($d['site_contact_name'] ?? '')) ?: null,
            ':scp'  => trim((string)($d['site_contact_phone'] ?? '')) ?: null,
            ':an'   => trim((string)($d['access_notes'] ?? '')) ?: null,
            ':a'    => !empty($d['is_active']) ? 1 : 0,
        ];
        if ($id > 0) {
            $data[':id'] = $id;
            $db->insertUpdate(
                "UPDATE service_contract_sites SET
                    site_code=:sc, name=:n, physical_address=:pa, region=:rg, district=:dt,
                    gps_lat=:lat, gps_lng=:lng, site_contact_name=:scn, site_contact_phone=:scp,
                    access_notes=:an, is_active=:a
                  WHERE c0d=:id",
                $data
            );
            $this->syncChecklistForContract($cid);
            return $id;
        }
        $fb = $db->insertUpdate(
            "INSERT INTO service_contract_sites
                (contract_id, site_code, name, physical_address, region, district, gps_lat, gps_lng,
                 site_contact_name, site_contact_phone, access_notes, is_active)
             VALUES (:c, :sc, :n, :pa, :rg, :dt, :lat, :lng, :scn, :scp, :an, :a)",
            $data
        );
        $this->syncChecklistForContract($cid);
        return (int)($fb['last_id'] ?? 0);
    }

    public function deleteSite(int $siteId): void
    {
        $db = new DB();
        $r = $db->mySelect("SELECT contract_id FROM service_contract_sites WHERE c0d = :id", [':id' => $siteId]);
        $cid = !empty($r) ? (int)$r[0]['contract_id'] : 0;
        $refs = $db->mySelect("SELECT COUNT(*) n FROM service_contract_assets WHERE site_id = :s", [':s' => $siteId]);
        if (!empty($refs) && (int)$refs[0]['n'] > 0) throw new RuntimeException('Site has assets — remove them first, or deactivate.');
        $db->insertUpdate("DELETE FROM service_contract_sites WHERE c0d = :id", [':id' => $siteId]);
        if ($cid > 0) $this->syncChecklistForContract($cid);
    }

    public function saveAsset(array $d): int
    {
        $cid   = (int)($d['contract_id'] ?? 0);
        $sid   = (int)($d['site_id'] ?? 0);
        $atId  = (int)($d['asset_type_id'] ?? 0);
        if ($cid <= 0 || $sid <= 0 || $atId <= 0) throw new InvalidArgumentException('contract_id + site_id + asset_type_id required.');
        $db = new DB();
        $id = (int)($d['c0d'] ?? 0);
        $data = [
            ':c'   => $cid, ':s' => $sid, ':at' => $atId,
            ':po'  => !empty($d['service_pack_id_override']) ? (int)$d['service_pack_id_override'] : null,
            ':sp'  => isset($d['spec_id']) && $d['spec_id'] !== '' ? (int)$d['spec_id'] : null,   // Slice 8Z-z-B · blueprint link
            ':an'  => trim((string)($d['asset_no'] ?? '')) ?: null,
            ':sn'  => trim((string)($d['serial_no'] ?? '')) ?: null,
            ':mk'  => trim((string)($d['make'] ?? '')) ?: null,
            ':md'  => trim((string)($d['model'] ?? '')) ?: null,
            ':sv'  => isset($d['size_value']) && $d['size_value'] !== '' ? (int)$d['size_value'] : null,
            ':su'  => trim((string)($d['size_unit'] ?? 'kVA')) ?: null,
            ':id_' => trim((string)($d['install_date'] ?? '')) ?: null,
            ':we'  => trim((string)($d['warranty_end'] ?? '')) ?: null,
            ':hr'  => isset($d['hours_run']) && $d['hours_run'] !== '' ? (int)$d['hours_run'] : null,
            ':mj'  => trim((string)($d['meta_json'] ?? '')) ?: null,
            ':a'   => !empty($d['is_active']) ? 1 : 0,
        ];
        if ($id > 0) {
            $data[':id'] = $id;
            $db->insertUpdate(
                "UPDATE service_contract_assets SET
                    site_id=:s, asset_type_id=:at, service_pack_id_override=:po, spec_id=:sp, asset_no=:an,
                    serial_no=:sn, make=:mk, model=:md, size_value=:sv, size_unit=:su,
                    install_date=:id_, warranty_end=:we, hours_run=:hr, meta_json=:mj, is_active=:a
                  WHERE c0d=:id",
                $data
            );
            $this->syncChecklistForContract($cid);
            return $id;
        }
        $fb = $db->insertUpdate(
            "INSERT INTO service_contract_assets
                (contract_id, site_id, asset_type_id, service_pack_id_override, spec_id, asset_no,
                 serial_no, make, model, size_value, size_unit, install_date, warranty_end,
                 hours_run, meta_json, is_active)
             VALUES (:c, :s, :at, :po, :sp, :an, :sn, :mk, :md, :sv, :su, :id_, :we, :hr, :mj, :a)",
            $data
        );
        $this->syncChecklistForContract($cid);
        return (int)($fb['last_id'] ?? 0);
    }

    public function deleteAsset(int $assetId): void
    {
        $db = new DB();
        $r = $db->mySelect("SELECT contract_id FROM service_contract_assets WHERE c0d = :id", [':id' => $assetId]);
        $cid = !empty($r) ? (int)$r[0]['contract_id'] : 0;
        $refs = $db->mySelect("SELECT COUNT(*) n FROM service_contract_pm_schedule WHERE contract_asset_id = :a", [':a' => $assetId]);
        if (!empty($refs) && (int)$refs[0]['n'] > 0) {
            // Delete schedule rows too — asset is going away
            $db->insertUpdate("DELETE FROM service_contract_pm_schedule WHERE contract_asset_id = :a", [':a' => $assetId]);
        }
        $db->insertUpdate("DELETE FROM service_contract_assets WHERE c0d = :id", [':id' => $assetId]);
        if ($cid > 0) $this->syncChecklistForContract($cid);
    }

    public function saveSla(array $d): int
    {
        $cid  = (int)($d['contract_id'] ?? 0);
        $cat  = (int)($d['category_id'] ?? 0);
        if ($cid <= 0 || $cat <= 0) throw new InvalidArgumentException('contract_id + category_id required.');
        $db = new DB();
        // upsert
        $data = [
            ':c'  => $cid, ':cat' => $cat,
            ':r'  => isset($d['sla_response_hrs']) && $d['sla_response_hrs'] !== '' ? (int)$d['sla_response_hrs'] : null,
            ':rs' => isset($d['sla_restoration_hrs']) && $d['sla_restoration_hrs'] !== '' ? (int)$d['sla_restoration_hrs'] : null,
            ':p'  => isset($d['penalty_pct_per_hr']) && $d['penalty_pct_per_hr'] !== '' ? (float)$d['penalty_pct_per_hr'] : null,
            ':n'  => trim((string)($d['notes'] ?? '')) ?: null,
        ];
        $db->insertUpdate(
            "INSERT INTO service_contract_slas (contract_id, category_id, sla_response_hrs, sla_restoration_hrs, penalty_pct_per_hr, notes)
             VALUES (:c, :cat, :r, :rs, :p, :n)
             ON DUPLICATE KEY UPDATE sla_response_hrs=VALUES(sla_response_hrs), sla_restoration_hrs=VALUES(sla_restoration_hrs),
                penalty_pct_per_hr=VALUES(penalty_pct_per_hr), notes=VALUES(notes)",
            $data
        );
        $this->syncChecklistForContract($cid);
        return 1;
    }

    /* ── Slice 8S-5 · Service Consumption Billing ────────────────────── */

    /**
     * Calculate the billable consumption for a contract in a specific month.
     * Reads all RESOLVED callouts + REVIEWED/CLIENT_ACCEPTED PMs in the period,
     * tallies: retainer + callout fees (with free-callouts subtraction and emergency
     * multiplier) + labour + parts (marked up) + mileage.
     *
     * Returns an array of line rows ready to insert as sales_items, plus the total.
     */
    public function calcConsumption(int $contractId, int $year, int $month): array
    {
        $c = $this->find($contractId);
        if (empty($c)) throw new RuntimeException('Contract not found.');
        $db = new DB();
        $periodStart = sprintf('%04d-%02d-01', $year, $month);
        $periodEnd   = date('Y-m-t', strtotime($periodStart));

        $lines = [];
        $currency = (string)$c['currency'];

        // 1. Retainer (always billed, whether or not there was activity)
        if ($c['invoice_cadence'] === 'MONTHLY_RETAINER' || $c['invoice_cadence'] === 'CONSUMPTION') {
            $retainer = (float)($c['monthly_retainer'] ?? 0);
            if ($retainer > 0) {
                $lines[] = ['description' => 'Monthly retainer · ' . date('F Y', strtotime($periodStart)),
                            'qty' => 1, 'unit' => 'month', 'unit_price' => $retainer, 'line_total' => $retainer];
            }
        }

        // 2. Callouts resolved in period
        $callouts = $db->mySelect(
            "SELECT co.c0d, co.callout_no, co.reported_at, co.resolved_at, co.is_emergency, co.labour_hrs, co.mileage_km,
                    cat.name AS cat_name,
                    s.name AS site_name, a.asset_no
               FROM callouts co
          LEFT JOIN callout_categories cat ON cat.c0d = co.category_id
          LEFT JOIN service_contract_sites s   ON s.c0d = co.site_id
          LEFT JOIN service_contract_assets a  ON a.c0d = co.asset_id
              WHERE co.contract_id = :c
                AND co.status IN ('RESOLVED','CLIENT_ACCEPTED','CLOSED')
                AND co.resolved_at BETWEEN :ps AND :pe
              ORDER BY co.resolved_at",
            [':c' => $contractId, ':ps' => $periodStart . ' 00:00:00', ':pe' => $periodEnd . ' 23:59:59']
        ) ?: [];

        $freeQuota = (int)($c['free_callouts_per_month'] ?? 0);
        $calloutFee = (float)($c['callout_fee'] ?? 0);
        $emergencyMult = (float)($c['emergency_multiplier'] ?? 1);
        $labourRate = (float)($c['labour_rate_per_hr'] ?? 0);
        $mileageRate = (float)($c['mileage_rate_per_km'] ?? 0);

        $freeUsed = 0;
        $totLabourHrs = 0.0; $totMileageKm = 0.0;
        foreach ($callouts as $co) {
            // Callout fee (subject to free quota)
            if ($calloutFee > 0) {
                if ($freeUsed < $freeQuota) {
                    $freeUsed++;
                    // no charge — but add a $0 line for transparency
                    $lines[] = ['description' => 'Callout ' . $co['callout_no'] . ' · ' . $co['site_name'] . ' [FREE within quota]',
                                'qty' => 1, 'unit' => 'callout', 'unit_price' => 0, 'line_total' => 0];
                } else {
                    $unit = (int)$co['is_emergency'] === 1 ? round($calloutFee * $emergencyMult, 2) : $calloutFee;
                    $lines[] = ['description' => 'Callout ' . $co['callout_no'] . ' · ' . $co['site_name']
                                    . ((int)$co['is_emergency'] === 1 ? ' (emergency ×' . number_format($emergencyMult,1) . ')' : ''),
                                'qty' => 1, 'unit' => 'callout', 'unit_price' => $unit, 'line_total' => $unit];
                }
            }
            $totLabourHrs += (float)($co['labour_hrs'] ?? 0);
            $totMileageKm += (float)($co['mileage_km'] ?? 0);
        }

        // 3. Labour (aggregated across all callouts in period)
        if ($labourRate > 0 && $totLabourHrs > 0) {
            $lt = round($labourRate * $totLabourHrs, 2);
            $lines[] = ['description' => 'Labour · ' . count($callouts) . ' callout' . (count($callouts) === 1 ? '' : 's') . ' in ' . date('F Y', strtotime($periodStart)),
                        'qty' => round($totLabourHrs, 2), 'unit' => 'hrs', 'unit_price' => $labourRate, 'line_total' => $lt];
        }

        // 4. Mileage
        if ($mileageRate > 0 && $totMileageKm > 0) {
            $mt = round($mileageRate * $totMileageKm, 2);
            $lines[] = ['description' => 'Mileage · site visits in ' . date('F Y', strtotime($periodStart)),
                        'qty' => round($totMileageKm, 2), 'unit' => 'km', 'unit_price' => $mileageRate, 'line_total' => $mt];
        }

        // 5. Parts used in callouts (respect spare_policy: COMPANY = bill; CLIENT/CONSIGNMENT/FIXED_PACK = don't)
        if ($c['spare_policy'] === 'COMPANY') {
            $markupPct = (float)($c['parts_markup_pct'] ?? 0);
            $partsUsed = $db->mySelect(
                "SELECT p.sku, p.name, cpu.qty, cpu.unit_cost_at_use, p.uom
                   FROM callout_parts_used cpu
                   JOIN callouts co   ON co.c0d = cpu.callout_id
              LEFT JOIN spare_parts p  ON p.c0d = cpu.part_id
                  WHERE co.contract_id = :c
                    AND co.status IN ('RESOLVED','CLIENT_ACCEPTED','CLOSED')
                    AND co.resolved_at BETWEEN :ps AND :pe
                    AND cpu.source IN ('STOCK','PROCURED','ADHOC')",
                [':c' => $contractId, ':ps' => $periodStart . ' 00:00:00', ':pe' => $periodEnd . ' 23:59:59']
            ) ?: [];
            $pmParts = $db->mySelect(
                "SELECT p.sku, p.name, pu.qty, pu.unit_cost_at_use, p.uom
                   FROM pm_service_parts_used pu
                   JOIN pm_services ps ON ps.c0d = pu.pm_service_id
              LEFT JOIN spare_parts p   ON p.c0d = pu.part_id
                  WHERE ps.contract_id = :c
                    AND ps.status IN ('DONE','REVIEWED','CLIENT_ACCEPTED')
                    AND ps.finished_at BETWEEN :ps AND :pe
                    AND pu.source IN ('STOCK','PROCURED','ADHOC')",
                [':c' => $contractId, ':ps' => $periodStart . ' 00:00:00', ':pe' => $periodEnd . ' 23:59:59']
            ) ?: [];
            foreach (array_merge($partsUsed, $pmParts) as $p) {
                $qty  = (float)$p['qty'];
                $cost = (float)($p['unit_cost_at_use'] ?? 0);
                if ($qty <= 0 || $cost <= 0) continue;
                $sell = round($cost * (1 + $markupPct / 100), 2);
                $lt   = round($qty * $sell, 2);
                $lines[] = ['description' => 'Part · ' . ($p['sku'] ?? '') . ' ' . ($p['name'] ?? ''),
                            'qty' => round($qty, 2), 'unit' => (string)($p['uom'] ?? 'ea'), 'unit_price' => $sell, 'line_total' => $lt];
            }
        }

        $subtotal = 0.0; foreach ($lines as $l) $subtotal += (float)$l['line_total'];
        return [
            'contract'    => $c,
            'period'      => $periodStart,
            'currency'    => $currency,
            'lines'       => $lines,
            'subtotal'    => round($subtotal, 2),
            'callout_count' => count($callouts),
        ];
    }

    /**
     * Draft a service invoice for a period. Idempotent — skips if one already exists
     * for this contract in this month with source_module='services'.
     */
    public function draftConsumptionInvoice(int $contractId, int $year, int $month, int $uid, string $uname): array
    {
        $calc = $this->calcConsumption($contractId, $year, $month);
        if (empty($calc['lines'])) return ['ok' => false, 'msg' => 'Nothing to bill in that period.'];

        $db = new DB();
        $c  = $calc['contract'];
        $periodStart = $calc['period'];
        $ym = date('Y-m', strtotime($periodStart));
        // Idempotent guard
        $exist = $db->mySelect(
            "SELECT c0d FROM sales
              WHERE source_module = 'services' AND deleted = 0
                AND customer_id = :cust AND DATE_FORMAT(invoice_date, '%Y-%m') = :ym",
            [':cust' => (int)$c['customer_id'], ':ym' => $ym]
        );
        if (!empty($exist)) return ['ok' => false, 'msg' => 'Draft already exists for ' . $ym . ' (sale id ' . (int)$exist[0]['c0d'] . ').'];

        $today = date('Y-m-d');
        $terms = (int)($c['payment_terms_days'] ?? 30);
        $due   = date('Y-m-d', strtotime('+' . $terms . ' days'));
        $narr  = 'Service consumption · ' . $c['contract_no'] . ' · ' . date('F Y', strtotime($periodStart));

        $fb = $db->insertUpdate(
            "INSERT INTO sales
                (project_id, customer_id, customer_name, source_module, invoice_date, due_date,
                 currency, subtotal, vat_rate, vat_amount, total, invoice_workflow_status, narrative,
                 drafted_by, drafted_by_name, payment_terms_days)
             VALUES (:p, :c, :cn, 'services', :d, :dd, :cur, :sub, 0, 0, :tot, 'DRAFT', :nt, :u, :un, :pt)",
            [':p'=>(int)$c['project_id'], ':c'=>(int)$c['customer_id'], ':cn'=>$c['customer_name'],
             ':d'=>$today, ':dd'=>$due, ':cur'=>$calc['currency'], ':sub'=>$calc['subtotal'],
             ':tot'=>$calc['subtotal'], ':nt'=>$narr, ':u'=>$uid, ':un'=>$uname, ':pt'=>$terms]
        );
        $saleId = (int)($fb['last_id'] ?? 0);
        $i = 1;
        foreach ($calc['lines'] as $ln) {
            $db->insertUpdate(
                "INSERT INTO sales_items (sale_id, item_no, description, qty, unit, unit_price, line_total)
                 VALUES (:s, :i, :d, :q, :u, :up, :lt)",
                [':s'=>$saleId, ':i'=>$i++, ':d'=>$ln['description'], ':q'=>$ln['qty'], ':u'=>$ln['unit'],
                 ':up'=>$ln['unit_price'], ':lt'=>$ln['line_total']]
            );
        }
        return ['ok'=>true, 'sale_id'=>$saleId, 'lines'=>count($calc['lines']), 'subtotal'=>$calc['subtotal'], 'callouts'=>$calc['callout_count']];
    }

    /**
     * Auto-generate PM schedule from each active asset:
     *   for each asset → find applicable templates (asset_type_id + cadence_type=MONTHS|HOURS_RUN + cadence_value set)
     *   → create a schedule row with next_due_on = today + cadence (fresh installs) or last_done + cadence
     * Idempotent — deletes non-executed rows first, then re-generates.
     */
    public function generateSchedule(int $contractId): array
    {
        $c = $this->find($contractId);
        if (empty($c)) throw new RuntimeException('Contract not found.');
        $assets = $this->assetsFor($contractId);
        if (empty($assets)) return ['ok' => false, 'msg' => 'No assets on contract.'];

        $db = new DB();
        // Wipe existing schedule rows for this contract (they'll be recomputed).
        // NOTE: in Slice S3, executed PMs live in pm_services (separate table) — those aren't touched.
        $db->insertUpdate("DELETE FROM service_contract_pm_schedule WHERE contract_id = :c", [':c' => $contractId]);

        $added = 0;
        foreach ($assets as $a) {
            $templates = $db->mySelect(
                "SELECT c0d, cadence_type, cadence_value FROM pm_templates
                  WHERE asset_type_id = :a AND is_active = 1 AND cadence_value IS NOT NULL AND cadence_value > 0",
                [':a' => (int)$a['asset_type_id']]
            ) ?: [];
            foreach ($templates as $t) {
                $intervalMo = null; $intervalHr = null; $nextDue = null;
                switch ($t['cadence_type']) {
                    case 'MONTHS':
                        $intervalMo = (int)$t['cadence_value'];
                        $nextDue    = date('Y-m-d', strtotime('+' . $intervalMo . ' months'));
                        break;
                    case 'QUARTERS':
                        $intervalMo = (int)$t['cadence_value'] * 3;
                        $nextDue    = date('Y-m-d', strtotime('+' . $intervalMo . ' months'));
                        break;
                    case 'WEEKS':
                        $nextDue    = date('Y-m-d', strtotime('+' . ((int)$t['cadence_value']) . ' weeks'));
                        break;
                    case 'DAYS':
                        $nextDue    = date('Y-m-d', strtotime('+' . ((int)$t['cadence_value']) . ' days'));
                        break;
                    case 'HOURS_RUN':
                        $intervalHr = (int)$t['cadence_value'];
                        $nextDue    = null; // computed against hours_run
                        break;
                    default: continue 2;
                }
                $db->insertUpdate(
                    "INSERT INTO service_contract_pm_schedule
                        (contract_id, contract_asset_id, template_id, next_due_on, interval_months, interval_hrs, is_active)
                     VALUES (:c, :a, :t, :nd, :im, :ih, 1)",
                    [':c' => $contractId, ':a' => (int)$a['c0d'], ':t' => (int)$t['c0d'],
                     ':nd' => $nextDue, ':im' => $intervalMo, ':ih' => $intervalHr]
                );
                $added++;
            }
        }
        $this->syncChecklistForContract($contractId);
        return ['ok' => true, 'added' => $added];
    }
}
