-- Slice 8Y · Project view · PM surface for goods projects.
-- 1) Add 3 columns to c_projects for the PM readiness stamp (Card C).
-- 2) Grant perms for the two new AJAX actions: pmReviewAj (PM+Admin) and reassignPmAj (Exec+Admin).

-- ── 1. Schema · pm_confirmed_at + pm_confirmation_notes + pm_confirmed_by ────
-- Idempotent via information_schema check so this script can be replayed.
SET @has_col := (SELECT COUNT(*) FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'c_projects'
                    AND column_name = 'pm_confirmed_at');
SET @sql := IF(@has_col = 0,
    'ALTER TABLE c_projects
        ADD COLUMN pm_confirmed_at    DATETIME NULL AFTER migrated_note,
        ADD COLUMN pm_confirmed_by    INT UNSIGNED NULL AFTER pm_confirmed_at,
        ADD COLUMN pm_confirmation_notes TEXT NULL AFTER pm_confirmed_by',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 2. Perms · pmReviewAj = Project Manager + Administrator ─────────────────
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Projects', 'pmReviewAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Project Manager','Administrator')
  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 = 'pmReviewAj'
);

-- ── 3. Perms · reassignPmAj = Executive + Administrator ────────────────────
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Projects', 'reassignPmAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Executive','Administrator')
  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 = 'reassignPmAj'
);
