/* ─────────────────────────────────────────────────────────────────────────────
   Slice 8Z-z-A' · Project-level POCs
   Contract-scoped contacts that apply to every site by default. Site-specific
   POCs in service_contract_site_pocs override the project defaults per site.
   ───────────────────────────────────────────────────────────────────────────── */

CREATE TABLE IF NOT EXISTS service_contract_pocs (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contract_id INT UNSIGNED NOT NULL,
    poc_role ENUM('ACCOUNT_MANAGER','REGIONAL_OPS','TECH_LEAD','FINANCE','ESCALATION','OTHER') NOT NULL DEFAULT 'ACCOUNT_MANAGER',
    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_contract (contract_id, deleted),
    KEY idx_primary  (contract_id, is_primary)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ── Perms · Admin + PM + PC full · Executive + FM + FS + Procurement + Stores 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 (
    SELECT 'projectPocSaveAj'    AS action_name UNION ALL
    SELECT 'projectPocRemoveAj'
) 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);
