-- Slice 4: Tender Pricing perms
-- Idempotent: uses INSERT IGNORE on the unique (user_group_id, controller_allowed, action_allowed) tuple
-- If no unique index exists we clean any duplicates afterwards.

-- Editors: CRM (owns bid build), Finance Manager (verifies), Administrator (fallback)
-- 9 CRUD/build actions
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Tenders', a.act, 0, NOW()
FROM (
  SELECT 'pricingCreateVersionAj' AS act UNION ALL
  SELECT 'pricingUpdateVersionAj'       UNION ALL
  SELECT 'pricingAddSectionAj'          UNION ALL
  SELECT 'pricingUpdateSectionAj'       UNION ALL
  SELECT 'pricingDeleteSectionAj'       UNION ALL
  SELECT 'pricingAddLineAj'             UNION ALL
  SELECT 'pricingUpdateLineAj'          UNION ALL
  SELECT 'pricingDeleteLineAj'          UNION ALL
  SELECT 'pricingSubmitAj'              UNION ALL
  SELECT 'pricingApplyAj'
) a
CROSS JOIN user_groups g
WHERE g.user_group_name IN ('CRM','Finance Manager','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 = a.act
  );

-- Viewers of the Pricing XLSX export: everyone who can already view a tender
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Tenders', 'pricingXlsx', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('CRM','Finance Manager','Administrator','Project Manager','Executive','Projects Coordinator')
  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 = 'pricingXlsx'
  );
