-- Slice 8b-4b · ServiceRequisitions perms

-- PC creates + edits + submits + cancels; PM/FM/Exec see chain
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'ServiceRequisitions', a.act, 0, NOW()
FROM (
    SELECT 'index'    AS act UNION ALL
    SELECT 'view'            UNION ALL
    SELECT 'create'          UNION ALL
    SELECT 'myQueue'         UNION ALL
    SELECT 'createAj'        UNION ALL
    SELECT 'addLineAj'       UNION ALL
    SELECT 'deleteLineAj'    UNION ALL
    SELECT 'submitAj'        UNION ALL
    SELECT 'cancelAj'
) a
CROSS JOIN user_groups g
WHERE g.user_group_name IN ('Projects Coordinator','Project Manager','Administrator')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'ServiceRequisitions' AND p.action_allowed = a.act);

-- Accountant + FM + Admin get everything above PLUS the issue action.
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'ServiceRequisitions', a.act, 0, NOW()
FROM (
    SELECT 'index'   AS act UNION ALL
    SELECT 'view'           UNION ALL
    SELECT 'myQueue'        UNION ALL
    SELECT 'issueAj'
) a
CROSS JOIN user_groups g
WHERE g.user_group_name IN ('Accountant','Finance Manager')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'ServiceRequisitions' AND p.action_allowed = a.act);

INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'ServiceRequisitions', 'issueAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name = 'Administrator'
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'ServiceRequisitions' AND p.action_allowed = 'issueAj');

-- Read-only for Executive
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'ServiceRequisitions', a.act, 0, NOW()
FROM (SELECT 'index' AS act UNION ALL SELECT 'view') a
CROSS JOIN user_groups g
WHERE g.user_group_name = 'Executive'
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'ServiceRequisitions' AND p.action_allowed = a.act);
