DROP VIEW IF EXISTS examination_documents;
CREATE VIEW examination_documents AS
SELECT e.id as examinationId,
  array_remove(array_agg(va.original_document_link), null) as "originalVerificationArea",
  array_remove(array_agg(va.translated_document_link), null) as "translatedVerificationArea",
  array_remove(array_agg(ar.original_document_link), null) as "originalAmikResult",
  array_remove(array_agg(ar.translated_document_link), null) as "translatedAmikResult",
  array_remove(array_agg(me.original_document_link), null) as "originalManualEvaluation",
  array_remove(array_agg(me.translated_document_link), null) as "translatedManualEvaluation",
  array_remove(array_agg(bi.original_document_link), null) as "originalBrasterInformation",
  array_remove(array_agg(bi.translated_document_link), null) as "translatedBrasterInformation",
  array_remove(array_agg(pfe.original_document_link), null) as "originalProcessingFailureEvaluation",
  array_remove(array_agg(pfe.translated_document_link), null) as "translatedProcessingFailureEvaluation"
FROM examinations e
LEFT JOIN tasks t ON t.examination_id = e.id
LEFT JOIN braster_information bi ON bi.task_id = t.id
LEFT JOIN manual_evaluations me ON me.task_id = t.id
LEFT JOIN amik_results ar ON ar.task_id = t.id AND (me.original_document_link IS NULL) -- AND task must be analogous to ResultLogic:333
LEFT JOIN processing_failure_evaluations pfe ON pfe.task_id = t.id
LEFT JOIN verification_area_limitations_results va ON va.task_id = t.id
GROUP BY e.id;

DROP VIEW IF EXISTS examinations_view CASCADE;
CREATE VIEW examinations_view AS
SELECT
    e.id AS "id",
    COALESCE(e.procedure_ended, e.created_at) AS "examinationDate",
    e.additional_data AS "additionalData",
    e.device_number as "deviceNumber",
    u.name as "unitName",
    u.id as "unitId",
    c.code as "unitCountry",
    CONCAT(us.first_name,' ',us.last_name) as "userName",
    us.id as "userId",
    p.id as "patientId",
    p.first_name as "patientFirstName",
    p.last_name as "patientLastName",
    p.external_patient_id AS "patientExternalId",
    EXISTS(SELECT * FROM amik_results amik JOIN tasks t2 ON t2.id = amik.task_id WHERE t2.examination_id = e.id)
        AND NOT EXISTS(SELECT * FROM manual_evaluations me JOIN tasks t2 ON t2.id = me.task_id WHERE t2.examination_id = e.id AND me.published_ai = false)
        AND COALESCE((SELECT s.name FROM examinations_exam_statuses es JOIN exam_statuses s ON es.status_id = s.id WHERE es.examination_id = e.id ORDER BY es.created_at DESC LIMIT 1) IN ('AUTOMATIC_PDF_READY', 'FINAL_PDF_READY', 'BAI_READY'), false)
        AS "automaticResult",
    CASE WHEN EXISTS(SELECT * FROM sir_results sir JOIN tasks t2 ON t2.id = sir.task_id WHERE t2.examination_id = e.id) THEN e.included_in_sir ELSE null END as "sir",
    EXISTS(SELECT * FROM manual_evaluations me JOIN tasks t2 ON t2.id = me.task_id WHERE t2.examination_id = e.id) as "manualResult",
    EXISTS(SELECT * FROM braster_information bi JOIN tasks t2 ON t2.id = bi.task_id WHERE t2.examination_id = e.id) as "additionalDocument",
    e.result_read AS "isRead",
    e.included_in_sir AS "inSir",
    (SELECT es.name FROM examinations_exam_statuses ees JOIN exam_statuses es ON ees.status_id = es.id WHERE ees.examination_id = e.id ORDER BY ees.created_at DESC LIMIT 1) AS "currentStatus",
    (SELECT ees.created_at FROM examinations_exam_statuses ees JOIN exam_statuses es ON ees.status_id = es.id WHERE ees.examination_id = e.id ORDER BY ees.created_at DESC LIMIT 1) AS "orderDate",
    ARRAY(SELECT es.name || ' - ' || TO_CHAR(ees.created_at, 'DD.MM.YYYY, HH24:MI') FROM examinations_exam_statuses ees JOIN exam_statuses es ON ees.status_id = es.id WHERE ees.examination_id = e.id ORDER BY ees.created_at DESC) AS "statusHistory",
    ARRAY(SELECT pf.label::text FROM patient_features_patients pfp JOIN patient_features pf ON pfp.patient_features_id = pf.id WHERE pfp.patient_id = p.id AND pfp.deleted_at IS NULL) AS "patientAttributes",
    ARRAY(SELECT c.label::text FROM users_categories uc JOIN categories c ON uc.category_id = c.id WHERE uc.user_id = us.id AND uc.deleted_at IS NULL) AS "userCategories",
    ARRAY(SELECT c.label::text FROM units_categories uc JOIN categories c ON uc.category_id = c.id WHERE uc.unit_id = u.id AND uc.deleted_at IS NULL) AS "unitCategories",
    array_to_string(ARRAY(SELECT uap.parent_id FROM units_all_parents uap WHERE uap.id = u.id), ',') as "unitWithParentIds",
    CASE WHEN EXISTS(
        SELECT * FROM examination_documents ed WHERE ed.examinationId = e.id AND
                                                     (cardinality(ed."originalAmikResult") > 0 OR
                                                      cardinality(ed."originalBrasterInformation") > 0 OR
                                                      cardinality(ed."originalManualEvaluation") > 0 OR
                                                      cardinality(ed."originalVerificationArea") > 0 OR
                                                      cardinality(ed."originalProcessingFailureEvaluation") > 0 OR
                                                      cardinality(ed."translatedAmikResult") > 0 OR
                                                      cardinality(ed."translatedBrasterInformation") > 0 OR
                                                      cardinality(ed."translatedManualEvaluation") > 0 OR
                                                      cardinality(ed."translatedVerificationArea") > 0 OR
                                                      cardinality(ed."translatedProcessingFailureEvaluation") > 0))
      THEN TRUE ELSE FALSE END as hasDocuments
FROM examinations e
         LEFT JOIN units u ON e.unit_id = u.id
         LEFT JOIN patients p ON e.patient_id = p.id
--       In case of CONSUMER or PRO_BASIC join standard User. Otherwise join Examiner.
         LEFT JOIN users us ON us.id = e.examiner_id
         LEFT JOIN territories te ON u.territory_id = te.id
         LEFT JOIN countries c ON te.country_id = c.id;