-- Slice 8c-CustomerRetention · WorksCustomerRetention perms

/* ── Read pool: same 8-role list as supplier retention ─────────────── */
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'WorksCustomerRetention', 'pool', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN (
    'Projects Coordinator','Project Manager','Field Supervisor',
    'Procurement','Finance Manager','Accountant','Executive','Administrator'
)
  AND NOT EXISTS (
    SELECT 1 FROM user_groups_perms p
    WHERE p.user_group_id = g.c0d
      AND p.controller_allowed = 'WorksCustomerRetention'
      AND p.action_allowed     = 'pool'
  );

/* ── Record + delete release: FM / Admin only (matches supplier side) ─ */
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'WorksCustomerRetention', a.act, 0, NOW()
FROM (SELECT 'recordReleaseAj' AS act UNION ALL SELECT 'deleteReleaseAj') a
CROSS JOIN user_groups g
WHERE g.user_group_name IN ('Finance Manager','Administrator')
  AND NOT EXISTS (
    SELECT 1 FROM user_groups_perms p
    WHERE p.user_group_id = g.c0d
      AND p.controller_allowed = 'WorksCustomerRetention'
      AND p.action_allowed     = a.act
  );
