-- Slice 8Y · PM briefing surface — notes to PC + Procurement, suggested extra items, PC assignment perm.

-- 1) Three text fields for PM's handover briefing.
SET @has_col := (SELECT COUNT(*) FROM information_schema.columns
                  WHERE table_schema = DATABASE() AND table_name = 'c_projects'
                    AND column_name = 'pm_notes_pc');
SET @sql := IF(@has_col = 0,
    'ALTER TABLE c_projects
        ADD COLUMN pm_notes_pc          TEXT NULL AFTER pm_confirmation_notes,
        ADD COLUMN pm_notes_procurement TEXT NULL AFTER pm_notes_pc,
        ADD COLUMN pm_suggested_items   TEXT NULL AFTER pm_notes_procurement',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2) assignPcAj perm — Project Manager + Administrator (PM assigns their PC).
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Projects', 'assignPcAj', 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 = 'assignPcAj'
);

-- 3) xlsx / pdf export perms — anyone with view perm on Projects.
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Projects', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'xlsx' AS ac UNION ALL SELECT 'pdf') actions
WHERE EXISTS (
    SELECT 1 FROM user_groups_perms p
    WHERE p.user_group_id = g.c0d
      AND p.controller_allowed = 'Projects'
      AND p.action_allowed = 'view'
)
  AND NOT EXISTS (
    SELECT 1 FROM user_groups_perms p2
    WHERE p2.user_group_id = g.c0d
      AND p2.controller_allowed = 'Projects'
      AND p2.action_allowed = actions.ac
);
