drop index if exists idx_crs
;
create index idx_crs on concept_relationship_stage (concept_code_1, vocabulary_id_1, concept_code_2, vocabulary_id_2)
;
analyze concept_relationship_stage
;
--to do:
-- Unify attribute-relationship-ids; LOINC has 5 up to attributes, can't have more
-- add other metrics
-- write filters on conflicting components; use filters similar to icd10pcs
--optimize w/ indices
select --relationship_id
    l1.*,
    x1.*
from concept_stage l1 --LOINC concept
join concept_relationship_stage r1 on --R1 is LOINC concept vs. SNOMED attribute (Specimen)
    (r1.concept_code_1, r1.vocabulary_id_1) = (l1.concept_code, l1.vocabulary_id) and
    l1.vocabulary_id = 'LOINC' and
    r1.relationship_id = 'Has direct site' --aka LOINC 'System'
join concept sa1 on --SNOMED attribute concept
    (r1.concept_code_2, r1.vocabulary_id_2) = (sa1.concept_code, sa1.vocabulary_id) and
    sa1.vocabulary_id = 'SNOMED'
join concept_relationship_stage r2 on --R2 is LOINC concept vs. SNOMED attribute (Component)
    (r2.concept_code_1, r2.vocabulary_id_1) = (l1.concept_code, l1.vocabulary_id) and
    r2.relationship_id = 'Has component' --aka LOINC 'Component'
join concept sa2 on --SNOMED attribute concept
    (r2.concept_code_2, r2.vocabulary_id_2) = (sa2.concept_code, sa2.vocabulary_id) and
    sa2.vocabulary_id = 'SNOMED'
join concept_relationship t1 on --pick up SNOMED combination
    t1.concept_id_2 = sa1.concept_id
join concept_relationship t2 on
    t1.concept_id_1 = t2.concept_id_1 and
    t2.concept_id_2 = sa2.concept_id
join concept x1 on
    x1.concept_id = t1.concept_id_1 and
    x1.vocabulary_id = 'SNOMED'
join devv5.concept_ancestor a on
    a.ancestor_concept_id = 4297090 and -- SNOMED "Evaluation"
    a.descendant_concept_id = x1.concept_id
where
    sa2.concept_id != 4146516 --SNOMED generic 'component'
limit 10
;