 with previous_mappings AS
-    (SELECT concept_id_1, array_agg(concept_id_2 ORDER BY concept_id_2 DESC) AS old_maps_to
+    (SELECT concept_id_1, c.standard_concept, array_agg(concept_id_2 ORDER BY concept_id_2 DESC) AS old_maps_to
         FROM devv5.concept_relationship cr
         JOIN devv5.concept c
         ON cr.concept_id_1 = c.concept_id

        AND cr.relationship_id IN ('Maps to', 'Maps to value')
        AND cr.invalid_reason IS NULL

        GROUP BY concept_id_1
        ),

     current_mapping AS

AND c.concept_class_id IN ('Lab Test'
                           --,'Survey', 'Answer', 'Clinical Observation' --TODO: postponed for now
                           )

UNION ALL

SELECT concept_code_1 AS concept_code, c.concept_name AS concept_name, crm.relationship_id AS relationship_id, 'concept_relationship_manual' AS flag,
       cc.concept_id AS target_concept_id,
       cc.concept_code AS target_concept_code,
       cc.concept_name AS target_concept_name,
       cc.concept_class_id AS target_concept_class_id,
       cc.standard_concept AS target_standard_concept,
       cc.invalid_reason AS target_invalid_reason,
       cc.domain_id AS target_domain_id,
       cc.vocabulary_id AS target_vocabulary_id

FROM dev_loinc.concept_relationship_manual crm
JOIN dev_loinc.concept c ON c.concept_code = crm.concept_code_1 AND c.vocabulary_id = 'LOINC'  AND crm.invalid_reason IS NULL
JOIN dev_loinc.concept cc ON cc.concept_code = crm.concept_code_2 AND cc.vocabulary_id = crm.vocabulary_id_2

ORDER BY concept_code, flag
;



