Vérifier qu'à chaque modification de valideur d'une structure prescriptrice, les valideurs de la structure d'accompagnement sont aussi créés

[celineung]

No description provided.

[celineung]

pour mettre à jour les valideurs des agences d'accompagnement:

-- suprimer les valideurs des utilisateurs de structures d'accompagnement qui n'ont que le role de valideur
WITH agenciesWithRefersTo AS (SELECT id
FROM agencies
WHERE refers_to_agency_id is not null)
DELETE FROM users__agencies
WHERE
  users__agencies.agency_id in (select agenciesWithRefersTo.id from agenciesWithRefersTo)
  AND roles = '["validator"]'::jsonb

-- suprimer les roles "validator" des utilisateurs de structures d'accompagnement qui ont plusieurs roles
WITH agenciesWithRefersTo AS (SELECT id
                              FROM agencies
                              WHERE refers_to_agency_id is not null)
UPDATE users__agencies
SET roles = (
  SELECT jsonb_agg(role)
  FROM jsonb_array_elements_text(roles) AS role
  WHERE role != 'validator'
)
WHERE users__agencies.agency_id in (select agenciesWithRefersTo.id from agenciesWithRefersTo)
  and roles ? 'validator';

-- ajouter les validateurs de l'agence prescriptrice parente
-- incluant les valideurs qui ne recoivent pas les mails
WITH agenciesWithRefersTo AS (SELECT id, refers_to_agency_id as parentId
                              FROM agencies
                              WHERE refers_to_agency_id is not null),
     usersAgenciesToCopy AS (
       SELECT user_id, agency_id, is_notified_by_email
       FROM users__agencies as parent
       WHERE parent.agency_id not in (SELECT id from agenciesWithRefersTo)
       and parent.roles ? 'validator'
       and not parent.roles ? 'to-review')
INSERT INTO users__agencies(user_id, agency_id, is_notified_by_email, roles)
SELECT usersAgenciesToCopy.user_id, agenciesWithRefersTo.id as agencyId, usersAgenciesToCopy.is_notified_by_email, '["validator"]'::jsonb
FROM usersAgenciesToCopy
       JOIN agenciesWithRefersTo on usersAgenciesToCopy.agency_id = agenciesWithRefersTo.parentId
WHERE TRUE
ON CONFLICT (user_id, agency_id) DO UPDATE
  SET roles = users__agencies.roles || '["validator"]'::jsonb