-- Slice 8Z-v · Services PM Workspace on Projects/view
--
-- Adds the machinery the PM needs to onboard a services project without
-- leaving the project page:
--
--   Batch A · pm_spec_confirmed_* on service_contracts — PM confirms tender answers
--   Batch E · project_service_overhead_annotations — PM refines bid overheads with actual notes
--   Batch F · 3 TEXT cols on c_projects — PM structured instructions to PC
--   Batch G · project_ops_documents — Tanzania-tuned operational documents library
--
-- Batches B (Sites) + C (SLAs) reuse existing service_contract_sites / _assets / _slas
-- tables — this migration adds no new schema for those.

/* ── Batch A · PM spec confirmation ───────────────────────────────── */
ALTER TABLE service_contracts
  ADD COLUMN pm_spec_confirmed_on TIMESTAMP NULL AFTER activated_at,
  ADD COLUMN pm_spec_confirmed_by INT UNSIGNED NULL AFTER pm_spec_confirmed_on,
  ADD COLUMN pm_spec_confirmed_by_name VARCHAR(160) NULL AFTER pm_spec_confirmed_by;

/* ── Batch E · PM overhead annotations against the tender pricing pool ── */
CREATE TABLE IF NOT EXISTS project_service_overhead_annotations (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NOT NULL,
    tender_overhead_id INT UNSIGNED NOT NULL COMMENT 'FK → pricing_version_overheads.c0d (live-read from tender)',
    actual_amount DECIMAL(20,2) NULL COMMENT 'PM annotates the projected/actual vs the bid amount',
    notes TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_by_name VARCHAR(160) NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_by INT UNSIGNED NULL,
    edited_on TIMESTAMP NULL,
    UNIQUE KEY uq_project_overhead (project_id, tender_overhead_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ── Batch F · PM → PC structured instructions (services only) ────── */
ALTER TABLE c_projects
  ADD COLUMN pm_svc_templates_needed TEXT NULL COMMENT 'PM tells PC which visit / callout / retirement templates to prepare',
  ADD COLUMN pm_svc_sites_instructions TEXT NULL COMMENT 'PM tells PC how to prioritise / batch site onboarding',
  ADD COLUMN pm_svc_team_instructions TEXT NULL COMMENT 'PM tells PC which FS crew members / vehicles / toolkits to assemble';

/* ── Batch G · Operational Documents library (Tanzania-tuned subtypes) ── */
CREATE TABLE IF NOT EXISTS project_ops_documents (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NOT NULL,

    subtype ENUM(
        'SIGNED_CONTRACT',        -- MANDATORY
        'CAR_INSURANCE',          -- MANDATORY · Contractor's All-Risk
        'WORKERS_COMP',           -- MANDATORY · Workers Comp policy
        'WCF_PROOF',              -- MANDATORY · Workers Compensation Fund
        'NSSF_PROOF',             -- MANDATORY · National Social Security Fund
        'OSHA_METHOD_STATEMENT',  -- MANDATORY · OSHA site safety plan
        'TRA_EFISCAL_CERT',       -- MANDATORY · TRA e-fiscal invoicing setup
        'SITE_ACCESS_PERMIT',     -- conditional · one per site for telecoms towers
        'HSE_PLAN',               -- conditional
        'VEHICLE_INSURANCE',      -- conditional · FS vehicles
        'OTHER'
    ) NOT NULL DEFAULT 'OTHER',

    label VARCHAR(200) NULL,
    original_filename VARCHAR(255) NOT NULL,
    stored_path VARCHAR(500) NOT NULL,
    mime_type VARCHAR(120) NULL,
    size_bytes INT UNSIGNED NOT NULL DEFAULT 0,
    validity_until DATE NULL COMMENT 'Insurance / permits expire — drives renewal alerts',
    notes VARCHAR(500) NULL,

    deleted TINYINT(1) NOT NULL DEFAULT 0,
    uploaded_by INT UNSIGNED NULL,
    uploaded_by_name VARCHAR(160) NULL,
    uploaded_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    KEY project_idx (project_id, deleted, subtype)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ── Perms ────────────────────────────────────────────────────────── */

-- Writes: PM + PC + Admin
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Projects', a.action_name, 0, NOW()
FROM user_groups g
CROSS JOIN (
    SELECT 'confirmSpecAj'          AS action_name UNION ALL
    SELECT 'svcInstructionsSaveAj'                 UNION ALL
    SELECT 'svcOverheadNoteSaveAj'                 UNION ALL
    SELECT 'svcSiteSaveAj'                         UNION ALL
    SELECT 'svcSiteRemoveAj'                       UNION ALL
    SELECT 'svcAssetSaveAj'                        UNION ALL
    SELECT 'svcAssetRemoveAj'                      UNION ALL
    SELECT 'svcSlaSaveAj'                          UNION ALL
    SELECT 'svcSlaSeedFromSpecAj'                  UNION ALL
    SELECT 'opsDocUploadAj'                        UNION ALL
    SELECT 'opsDocRemoveAj'
) a
WHERE g.user_group_name IN ('Administrator','Project Manager','Projects Coordinator')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed='Projects' AND p.action_allowed = a.action_name);

-- Reads (download / list): all senior roles that see the project
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Projects', a.action_name, 0, NOW()
FROM user_groups g
CROSS JOIN (
    SELECT 'opsDocDownload' AS action_name UNION ALL
    SELECT 'opsDocPreview'
) a
WHERE g.user_group_name IN ('Administrator','Project Manager','Projects Coordinator','Executive','Finance Manager','Accountant','CRM','Field Supervisor')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed='Projects' AND p.action_allowed = a.action_name);
