-- Slice 8L-TenderItems · perms
-- Read (itemsListAj): same audience as Tenders/view (CRM + PM + PC + FM + Exec + Procurement + Admin).
-- Write (add/update/remove/reorder/bulkImport): CRM primary, Admin fallback.

INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Tenders', 'itemsListAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('CRM','Project Manager','Projects Coordinator','Finance Manager','Executive','Procurement','Administrator')
  AND NOT EXISTS (
    SELECT 1 FROM user_groups_perms p
    WHERE p.user_group_id = g.c0d
      AND p.controller_allowed = 'Tenders'
      AND p.action_allowed     = 'itemsListAj'
  );

INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Tenders', act.action_allowed, 0, NOW()
FROM user_groups g
CROSS JOIN (
    SELECT 'itemsAddAj'        AS action_allowed UNION ALL
    SELECT 'itemsUpdateAj'                       UNION ALL
    SELECT 'itemsRemoveAj'                       UNION ALL
    SELECT 'itemsReorderAj'                      UNION ALL
    SELECT 'itemsBulkImportAj'
) act
WHERE g.user_group_name IN ('CRM','Administrator')
  AND NOT EXISTS (
    SELECT 1 FROM user_groups_perms p
    WHERE p.user_group_id = g.c0d
      AND p.controller_allowed = 'Tenders'
      AND p.action_allowed     = act.action_allowed
  );
