/* ─────────────────────────────────────────────────────────────────────────────
   Slice 8Z-z-A · PC-facing Sites & POC register
   Reuses service_contract_sites + service_contract_assets (already exist).
   Adds:
     · service_contract_site_pocs      · multiple POCs per site (role-tagged)
     · confirmation stamp columns on service_contract_sites
     · perms for Projects/* proxy actions (Admin, PM, PC full · view for others)
   ───────────────────────────────────────────────────────────────────────────── */

/* ── Multi-POC per site ─────────────────────────────────────────────────────── */
CREATE TABLE IF NOT EXISTS service_contract_site_pocs (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_id INT UNSIGNED NOT NULL,
    poc_role ENUM('SITE_MANAGER','TECH','SECURITY','OPS','OTHER') NOT NULL DEFAULT 'OTHER',
    poc_name  VARCHAR(160) NOT NULL,
    poc_phone VARCHAR(40)  NULL,
    poc_email VARCHAR(160) NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    notes VARCHAR(500) NULL,
    created_by INT UNSIGNED NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted TINYINT(1) NOT NULL DEFAULT 0,
    KEY idx_site (site_id, deleted),
    KEY idx_primary (site_id, is_primary)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ── Confirmation stamp on the site (MySQL 8 · no IF NOT EXISTS on ADD COLUMN) ── */
ALTER TABLE service_contract_sites
    ADD COLUMN poc_confirmed_by INT UNSIGNED NULL,
    ADD COLUMN poc_confirmed_on TIMESTAMP NULL;

/* ── Backfill single-contact → primary POC row ──────────────────────────────── */
INSERT INTO service_contract_site_pocs (site_id, poc_role, poc_name, poc_phone, is_primary, created_on)
SELECT s.c0d, 'SITE_MANAGER', s.site_contact_name, s.site_contact_phone, 1, NOW()
  FROM service_contract_sites s
 WHERE s.site_contact_name IS NOT NULL
   AND TRIM(s.site_contact_name) <> ''
   AND NOT EXISTS (
       SELECT 1 FROM service_contract_site_pocs p WHERE p.site_id = s.c0d AND p.is_primary = 1 AND p.deleted = 0
   );

/* ── Perms · Admin + PM + PC full; Executive + FM + FS + Procurement view ──── */
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 (
    /* Site + asset CRUD already granted under svcSiteSaveAj / svcSiteRemoveAj /
       svcAssetSaveAj / svcAssetRemoveAj — Vue view reuses those endpoints.
       Only add what's new to z-A: */
    SELECT 'sites'               AS action_name UNION ALL
    SELECT 'sitesImportAj'                      UNION ALL
    SELECT 'sitePocSaveAj'                      UNION ALL
    SELECT 'sitePocRemoveAj'                    UNION ALL
    SELECT 'sitePocConfirmAj'                   UNION ALL
    SELECT 'sitesSnapshotAj'
) 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);

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 'sites'          AS action_name UNION ALL
    SELECT 'sitesSnapshotAj'
) a
WHERE g.user_group_name IN ('Executive','Finance Manager','Accountant','Field Supervisor','Procurement','Stores')
  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);
