Adapt Isolation / Quarantine Date of running cases

[maennchen]

SELECT SET_CONFIG('versioning.originator_id', '', true);
SELECT SET_CONFIG('versioning.origin', 'migration', true);
UPDATE
  cases update_case
  SET
      phases = ARRAY_REPLACE(
          update_case.phases,
          replace_case.search_phase,
          replace_case.replace_phase
      )
  FROM (
    SELECT
      cases.uuid AS case_uuid,
      people_with_data.uuid AS person_uuid,
      people_with_data.first_name AS first_name,
      people_with_data.last_name AS last_name,
      people_with_data.email AS email,
      people_with_data.mobile AS mobile,
      people_with_data.landline AS landline,
      active_phases -> 'details' ->> '__type__' AS phase_type,
      (active_phases ->> 'end')::date AS original_end_date,
      GREATEST(
        (
          (active_phases ->> 'end')::date -
          CASE
            WHEN active_phases -> 'details' ->> '__type__' = 'index' THEN INTERVAL '5 days'
            WHEN active_phases -> 'details' ->> '__type__' = 'possible_index' THEN INTERVAL '2 days'
          END
        )::date,
        CURRENT_DATE
      ) AS new_end_date,
      active_phases AS search_phase,
      active_phases || JSONB_BUILD_OBJECT(
        'end',
        GREATEST(
          (
            (active_phases ->> 'end')::date -
            CASE
              WHEN active_phases -> 'details' ->> '__type__' = 'index' THEN INTERVAL '5 days'
              WHEN active_phases -> 'details' ->> '__type__' = 'possible_index' THEN INTERVAL '2 days'
            END
          )::date,
          CURRENT_DATE
        )
      ) AS replace_phase
    FROM cases
    JOIN
      UNNEST(cases.phases) AS active_phases
      ON
        NOW() BETWEEN (active_phases ->> 'start')::date AND (active_phases ->> 'end')::date AND
        (active_phases ->> 'inserted_at')::timestamp <= '2022-01-13 15:14:00'
    JOIN
      (
        SELECT
          people.uuid,
          people.first_name,
          people.last_name,
          MIN(mobile_contact_methods->>'value') AS mobile,
          MIN(email_contact_methods->>'value') AS email,
          MIN(landline_contact_methods->>'value') AS landline
        FROM people
        LEFT JOIN
          UNNEST(people.contact_methods) AS mobile_contact_methods
          ON mobile_contact_methods->>'type' = 'mobile'
        LEFT JOIN
          UNNEST(people.contact_methods) AS email_contact_methods
          ON mobile_contact_methods->>'type' = 'email'
        LEFT JOIN
          UNNEST(people.contact_methods) AS landline_contact_methods
          ON landline_contact_methods->>'type' = 'landline'
        GROUP BY people.uuid
      ) AS people_with_data
      ON people_with_data.uuid = cases.person_uuid
  ) AS replace_case
  WHERE replace_case.case_uuid = update_case.uuid
  RETURNING
      replace_case.case_uuid,
      replace_case.person_uuid,
      replace_case.first_name,
      replace_case.last_name,
      replace_case.email,
      replace_case.mobile,
      replace_case.landline,
      replace_case.phase_type,
      replace_case.original_end_date,
      replace_case.new_end_date;