-- Slice 8a · GoodsScrub perms + project-scoped alias perms
-- PC does the work; Procurement + PM + Admin can view.

INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'GoodsScrub', a.act, 0, NOW()
FROM (
    SELECT 'scrubPo'         AS act UNION ALL
    SELECT 'scrubItem'              UNION ALL
    SELECT 'saveItemAj'             UNION ALL
    SELECT 'markScrubbedAj'         UNION ALL
    SELECT 'unscrubAj'
) a
CROSS JOIN user_groups g
WHERE g.user_group_name IN ('Projects Coordinator','Project Manager','Procurement','Administrator')
  AND NOT EXISTS (
    SELECT 1 FROM user_groups_perms p
     WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'GoodsScrub' AND p.action_allowed = a.act
  );

-- Grant Exec + FM read on GoodsScrub landing (Slice 7 cross-role visibility)
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'GoodsScrub', a.act, 0, NOW()
FROM (SELECT 'scrubPo' AS act UNION ALL SELECT 'scrubItem') a
CROSS JOIN user_groups g
WHERE g.user_group_name IN ('Executive','Finance Manager')
  AND NOT EXISTS (
    SELECT 1 FROM user_groups_perms p
     WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'GoodsScrub' AND p.action_allowed = a.act
  );

-- Grant the two project-scoped aliases (piggyback on existing GoodsClientPos / GoodsRfq perms)
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT DISTINCT p.user_group_id, p.controller_allowed, 'forProject', 0, NOW()
FROM user_groups_perms p
WHERE p.controller_allowed = 'GoodsClientPos' AND p.action_allowed = 'view'
  AND NOT EXISTS (
    SELECT 1 FROM user_groups_perms x
     WHERE x.user_group_id = p.user_group_id AND x.controller_allowed = 'GoodsClientPos' AND x.action_allowed = 'forProject'
  );

INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT DISTINCT p.user_group_id, p.controller_allowed, act.act, 0, NOW()
FROM user_groups_perms p
CROSS JOIN (SELECT 'fromProject' AS act UNION ALL SELECT 'assessQuotes') act
WHERE p.controller_allowed = 'GoodsRfq' AND p.action_allowed = 'view'
  AND NOT EXISTS (
    SELECT 1 FROM user_groups_perms x
     WHERE x.user_group_id = p.user_group_id AND x.controller_allowed = 'GoodsRfq' AND x.action_allowed = act.act
  );
