WITH
  facility_cases AS (
    SELECT raw_facility_cases.*, 'facility'::text AS SOURCE, (CASE role_order WHEN 0 THEN 'patient'
                                                           WHEN 1 THEN 'student'
                                                           WHEN 2 THEN 'requester'
                                                           WHEN 3 THEN 'responder'
                                           END) role_name
      FROM (SELECT acf.case_id, fu.user_id, MAX(CASE fu.role_name WHEN 'patient' THEN 0
                                                                  WHEN 'student' THEN 1
                                                                  WHEN 'requester' THEN 2
                                                                  WHEN 'responder' THEN 3
                                                END) role_order
      FROM facility_user fu
      LEFT JOIN access_case_facility acf
      ON fu.facility_id = acf.facility_id
      WHERE fu.user_id = 320
      AND fu.role_name != 'patient'
      GROUP BY acf.case_id, fu.user_id) raw_facility_cases),
  user_cases AS (
    SELECT case_id, user_id, role_name, options, 'case'::text AS SOURCE
      FROM access_case_user
      WHERE user_id = 320),
  patient_cases AS (
    SELECT cc.id AS case_id, patient.user_id, 'patient'::text AS role_name, 'patient'::text AS SOURCE
    FROM click_case cc
    JOIN patient ON cc.patient_id = patient.id
    WHERE patient.user_id = 320),
  without_followup_visible_cases AS (
    SELECT COALESCE(fc.case_id, uc.case_id, pc.case_id) AS case_id,
           COALESCE(fc.user_id, uc.user_id, pc.user_id) AS user_id,
           COALESCE(uc.role_name, fc.role_name, pc.role_name) AS role_name,
           COALESCE(uc.options, '{}') AS options,
           COALESCE(uc.SOURCE, fc.SOURCE, pc.SOURCE)::text AS SOURCE
      FROM facility_cases fc
      FULL OUTER JOIN user_cases uc
      ON fc.case_id = uc.case_id
      FULL OUTER JOIN patient_cases pc
      ON fc.case_id = pc.case_id),
  visible_cases AS (
    SELECT COALESCE(cc2.id, wfvc2.case_id) AS case_id,
           COALESCE(wfvc.user_id, wfvc2.user_id) AS user_id,
           COALESCE(wfvc.role_name, wfvc2.role_name) AS role_name,
           COALESCE(wfvc.options, wfvc2.options) AS options,
           COALESCE(wfvc.SOURCE, wfvc2.SOURCE)::text AS SOURCE
      FROM click_case cc2
      JOIN without_followup_visible_cases wfvc
      ON cc2.parent_id = wfvc.case_id
      FULL OUTER JOIN without_followup_visible_cases wfvc2
      ON cc2.id = wfvc2.case_id
)
SELECT * FROM visible_cases