BEGIN;

--- admin groups assignment against roles

INSERT INTO USER_GROUP_MEMBERSHIP (Group_ID, USER_ID)
SELECT DISTINCT (SELECT ID FROM KEYCLOAK_GROUP WHERE NAME = 'agents_permission') AS Group_ID, rm.USER_ID
FROM KEYCLOAK_ROLE r
INNER JOIN USER_ROLE_MAPPING rm ON r.ID = rm.ROLE_ID
WHERE r.REALM_ID = 'expertflow' AND r.CLIENT_ROLE = 'f' AND r.NAME = 'admin'
ON CONFLICT DO NOTHING;

INSERT INTO USER_GROUP_MEMBERSHIP (Group_ID, USER_ID)
SELECT DISTINCT (SELECT ID FROM KEYCLOAK_GROUP WHERE NAME = 'senior_agents_permission') AS Group_ID, rm.USER_ID
FROM KEYCLOAK_ROLE r
INNER JOIN USER_ROLE_MAPPING rm ON r.ID = rm.ROLE_ID
WHERE r.REALM_ID = 'expertflow' AND r.CLIENT_ROLE = 'f' AND r.NAME = 'admin'
ON CONFLICT DO NOTHING;


--- supervisor groups assignment against role


INSERT INTO USER_GROUP_MEMBERSHIP (Group_ID, USER_ID)
SELECT DISTINCT (SELECT ID FROM KEYCLOAK_GROUP WHERE NAME = 'agents_permission') AS Group_ID, rm.USER_ID
FROM KEYCLOAK_ROLE r
INNER JOIN USER_ROLE_MAPPING rm ON r.ID = rm.ROLE_ID
WHERE r.REALM_ID = 'expertflow' AND r.CLIENT_ROLE = 'f' AND r.NAME = 'supervisor'
ON CONFLICT DO NOTHING;

INSERT INTO USER_GROUP_MEMBERSHIP (Group_ID, USER_ID)
SELECT DISTINCT (SELECT ID FROM KEYCLOAK_GROUP WHERE NAME = 'senior_agents_permission') AS Group_ID, rm.USER_ID
FROM KEYCLOAK_ROLE r
INNER JOIN USER_ROLE_MAPPING rm ON r.ID = rm.ROLE_ID
WHERE r.REALM_ID = 'expertflow' AND r.CLIENT_ROLE = 'f' AND r.NAME = 'supervisor'
ON CONFLICT DO NOTHING;

--- agents group assignment.

INSERT INTO USER_GROUP_MEMBERSHIP (Group_ID, USER_ID)
SELECT DISTINCT (SELECT ID FROM KEYCLOAK_GROUP WHERE NAME = 'agents_permission') AS Group_ID, rm.USER_ID
FROM KEYCLOAK_ROLE r
INNER JOIN USER_ROLE_MAPPING rm ON r.ID = rm.ROLE_ID
WHERE r.REALM_ID = 'expertflow' AND r.CLIENT_ROLE = 'f' AND r.NAME = 'agent'
ON CONFLICT DO NOTHING;


COMMIT;
