/* ─────────────────────────────────────────────────────────────────────────────
   Slice 8Z-z-G · Callout closure completeness
   Adds investigation / RCA / snags / closure stamps to callouts + perms for
   PC-driven findings save, client-accept and close endpoints.
   ───────────────────────────────────────────────────────────────────────────── */

ALTER TABLE callouts
    ADD COLUMN investigation_notes TEXT NULL COMMENT 'What FS actually found on site',
    ADD COLUMN root_cause          TEXT NULL COMMENT 'RCA — plain language',
    ADD COLUMN snags               TEXT NULL COMMENT 'Outstanding items after resolution',
    ADD COLUMN closed_at           DATETIME NULL,
    ADD COLUMN closed_by           INT UNSIGNED NULL,
    ADD COLUMN closed_by_name      VARCHAR(160) NULL;

/* ── Perms · findings / accept / close ─────────────────────────────────────── */
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Callouts', a.action_name, 0, NOW()
FROM user_groups g
CROSS JOIN (
    SELECT 'saveFindingsAj' AS action_name UNION ALL
    SELECT 'clientAcceptAj'                UNION ALL
    SELECT 'closeAj'
) a
WHERE g.user_group_name IN ('Administrator','Project Manager','Projects Coordinator','Field Supervisor')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id=g.c0d AND p.controller_allowed='Callouts' AND p.action_allowed=a.action_name);
