 TRUNCATE TABLE concept_synonym_stage;
 TRUNCATE TABLE pack_content_stage;
 TRUNCATE TABLE drug_strength_stage;
-TRUNCATE TABLE concept_relationship_manual;
 
 --Prepare tables
 --RUN PRELOAD_STAGE

END $_$;
	

-- At the end, the three tables concept_stage, concept_relationship_stage and concept_synonym_stage should be ready to be fed into the generic_update.sql script


/*-- CHECK
SELECT c.concept_code AS concept_code_1, cr.relationship_id, cc.concept_code AS concept_code_2
FROM concept_relationship cr
JOIN concept c
    ON cr.concept_id_1 = c.concept_id
JOIN concept cc
    ON cr.concept_id_2 = cc.concept_id
WHERE (c.vocabulary_id = 'LOINC' AND cc.vocabulary_id = 'LOINC')
  AND relationship_id IN ('Has kind', 'Has role', 'Has type of service', 'Has subject matter', 'Has Answer', 'Maps to', 'Panel contains', 'Subsumes', 'Has setting')
AND cr.invalid_reason IS NULL
AND cr.concept_id_1 != cr.concept_id_2
EXCEPT (SELECT crs.concept_code_1, relationship_id, crs.concept_code_2
    FROM concept_relationship_stage crs
    WHERE crs.vocabulary_id_1 = 'LOINC' AND crs.vocabulary_id_2 = 'LOINC')
ORDER BY concept_code_1; */
)
WITH s AS
(
-- pick Primary LOINC Parts
SELECT DISTINCT pl.PartNumber, p.PartDisplayName, pl.parttypename, p.status
FROM sources.loinc_partlink pl -- contains links between LOINC Measurements/Observations AND LOINC Parts
JOIN sources.loinc_part p -- contains LOINC Parts and defines their validity ('status' field)
