 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; */+-- 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
         ELSE 'ACTIVE'
       END AS status
FROM sources.loinc_hierarchy lh
  LEFT JOIN sources.loinc_part p --  to get a validity of concept ( a 'status' field)
ON lh.code = p.partnumber -- LOINC Attribute
WHERE code LIKE 'LP%' -- all LOINC Hierсrchy concepts have 'LP' at the beginning of the names (including 427 undefined concepts and LOINC panels)
AND   TRIM(code) NOT IN (SELECT TRIM(partnumber)
                         FROM sources.loinc_partlink pl
                         WHERE pl.LinkTypeName = 'Primary'
                         AND   pl.PartTypeName IN ('SYSTEM','METHOD','PROPERTY','TIME','COMPONENT','SCALE')) --  pick non-primary Parts and 427 Undefined attributes (excluding Primary LOINC Parts)
)
SELECT DISTINCT trim(s.PartDisplayName) AS concept_name,
                CASE
		WHEN (
				PartDisplayName ~* 'directive|^age\s+|lifetime risk|alert|attachment|\s+date|comment|\s+note|consent|identifier|\s+time|\s+number' -- manually defined word patterns indicating the 'Observation' domain
				OR PartDisplayName ~* 'date and time|coding system|interpretation|status|\s+name|\s+report|\s+id$|s+id\s+|version|instruction|known exposure|priority|ordered|available|requested|issued|flowsheet|\s+term'
				OR PartDisplayName ~* 'reported|not yet categorized|performed|risk factor|device|administration|\s+route$|suggestion|recommended|narrative|ICD code|reference'
				OR PartDisplayName ~* 'reviewed|information|intention|^Reason for|^Received|Recommend|provider|subject|summary|time\s+'
				)
			AND PartDisplayName !~* 'thrombin time|clotting time|bleeding time|clot formation|kaolin activated time|closure time|protein feed time|Recalcification time|reptilase time|russell viper venom time'
			AND PartDisplayName !~* 'implanted device|dosage\.vial|isolate|within lymph node|cancer specimen|tumor|chromosome|inversion|bioavailable'
			THEN 'Observation'
		ELSE 'Measurement'  -- AVOF-1579
		END AS domain_id,
                'LOINC' AS vocabulary_id,
                CASE WHEN s.parttypename = 'SYSTEM' THEN 'LOINC System'
                     WHEN s.parttypename = 'METHOD' THEN 'LOINC Method'
                     WHEN s.parttypename = 'PROPERTY' THEN 'LOINC Property'
                     WHEN s.parttypename = 'TIME' THEN 'LOINC Time'
                     WHEN s.parttypename = 'COMPONENT' THEN 'LOINC Component'
                     WHEN s.parttypename = 'SCALE' THEN 'LOINC Scale'
                     ELSE 'LOINC Hierarchy'
                     END AS concept_class_id,
  		CASE WHEN s.parttypename = 'LOINC Hierarchy' THEN 'C' ELSE NULL END AS standard_concept, --  LOINC Hierarchy concepts should be 'Classification', LOINC Attributes - 'Non-standard'
                s.PartNumber AS concept_code, -- LOINC Attribute or Hierarchy concept
                COALESCE(c.valid_start_date, v.latest_update) AS valid_start_date,-- preserve the 'devv5.valid_start_date' for already existing concepts
                CASE WHEN s.status = 'DEPRECATED'
                     THEN CASE WHEN c.valid_end_date <= latest_update
                               THEN c.valid_end_date -- preserve 'devv5.valid_end_date' for already existing DEPRECATED concepts
                               ELSE GREATEST(COALESCE(c.valid_start_date, v.latest_update), -- assign LOINC 'latest_update' as 'valid_end_date' for new concepts which have to be deprecated in the current release
                                                      latest_update - 1) END -- assign LOINC 'latest_update-1' as 'valid_end_date' for already existing concepts, which have to be deprecated in the current release
                     ELSE to_date('20991231', 'yyyymmdd') END as valid_end_date, -- default value of 31-Dec-2099 for the rest
                CASE WHEN s.status IN ('ACTIVE', 'INACTIVE') THEN NULL  -- define concept validity according to the 'status' field
                     WHEN s.status = 'DEPRECATED' THEN 'D'
                     ELSE 'X' END AS invalid_reason    --IF there are any changes in LOINC source we don't know about. GenericUpdate() will fail in case of 'X' in invalid_reason field
FROM s
JOIN vocabulary v ON v.vocabulary_id = 'LOINC'
LEFT JOIN concept c ON c.concept_code = s.PartNumber -- already existing LOINC concepts

c3.concept_code AS concept_code_2, -- SNOMED Attribute code
'LOINC' AS vocabulary_id_1,
'SNOMED' AS vocabulary_id_2,
CASE WHEN c2.concept_name in ('Time aspect', 'Process duration')  THEN 'Has time aspect'
WHEN c2.concept_name in ('Component','Process output') THEN 'Has component'
WHEN c2.concept_name = 'Direct site' THEN 'Has dir proc site'
WHEN c2.concept_name = 'Inheres in' THEN 'Inheres in'
WHEN c2.concept_name = 'Property type' THEN 'Has property'
WHEN c2.concept_name = 'Scale type' THEN 'Has scale type'
WHEN c2.concept_name = 'Technique' THEN 'Has technique'
WHEN c2.concept_name = 'Precondition' THEN 'Has precondition'
