 COMMIT;
 
 -- 11.5. Start building domains, preassign all them with "Not assigned"
-
+-- drop table domain_snomed;
 CREATE TABLE domain_snomed
 AS
    SELECT concept_code, CAST ('Not assigned' AS VARCHAR2 (20)) AS domain_id

    WHERE rn = 1 AND active = 1;
COMMIT;	
	

--3 Create temporary table with extracted class information and terms ordered by some good precedence
CREATE TABLE tmp_concept_class
AS

									 where c.vocabulary_id='SNOMED')))
    WHERE rnc = 1;	
	
create index x_cc_2cd on tmp_concept_class (concept_code);

--4 Create reduced set of classes 
UPDATE concept_stage cs
   SET concept_class_id =
          (SELECT CASE
                 WHEN F7 = 'disorder' THEN 'Clinical Finding'
                 WHEN F7 = 'procedure' THEN 'Procedure'
                 WHEN F7 = 'finding' THEN 'Clinical Finding'
                 WHEN F7 = 'organism' THEN 'Organism'
                 WHEN F7 = 'body structure' THEN 'Body Structure'
                 WHEN F7 = 'substance' THEN 'Substance'
                 WHEN F7 = 'product' THEN 'Pharma/Biol Product'
                 WHEN F7 = 'event' THEN 'Event'
                 WHEN F7 = 'qualifier value' THEN 'Qualifier Value'
                 WHEN F7 = 'observable entity' THEN 'Observable Entity'
                 WHEN F7 = 'situation' THEN 'Context-dependent'
                 WHEN F7 = 'occupation' THEN 'Social Context'
                 WHEN F7 = 'regime/therapy' THEN 'Procedure'
                 WHEN F7 = 'morphologic abnormality' THEN 'Morph Abnormality'
                 WHEN F7 = 'physical object' THEN 'Physical Object'
                 WHEN F7 = 'specimen' THEN 'Specimen'
                 WHEN F7 = 'environment' THEN 'Location'
                 WHEN F7 = 'environment / location' THEN 'Location'
                 WHEN F7 = 'context-dependent category' THEN 'Context-dependent'
                 WHEN F7 = 'attribute' THEN 'Attribute'
                 WHEN F7 = 'linkage concept' THEN 'Linkage Concept'
                 WHEN F7 = 'assessment scale' THEN 'Staging / Scales'
                 WHEN F7 = 'person' THEN 'Social Context'
                 WHEN F7 = 'cell' THEN 'Body Structure'
                 WHEN F7 = 'geographic location' THEN 'Location'
                 WHEN F7 = 'cell structure' THEN 'Body Structure'
                 WHEN F7 = 'ethnic group' THEN 'Social Context'
                 WHEN F7 = 'tumor staging' THEN 'Staging / Scales'
                 WHEN F7 = 'religion/philosophy' THEN 'Social Context'
                 WHEN F7 = 'record artifact' THEN 'Record Artifact'
                 WHEN F7 = 'physical force' THEN 'Physical Force'
                 WHEN F7 = 'foundation metadata concept' THEN 'Model Comp'
                 WHEN F7 = 'namespace concept' THEN 'Namespace Concept'
                 WHEN F7 = 'administrative concept' THEN 'Admin Concept'
                 WHEN F7 = 'biological function' THEN 'Biological Function'
                 WHEN F7 = 'living organism' THEN 'Organism'
                 WHEN F7 = 'life style' THEN 'Social Context'
                 WHEN F7 = 'contextual qualifier' THEN 'Qualifier Value'
                 WHEN F7 = 'staging scale' THEN 'Staging / Scales'
                 WHEN F7 = 'life event - finding' THEN 'Event'
                 WHEN F7 = 'social concept' THEN 'Social Context'
                 WHEN F7 = 'core metadata concept' THEN 'Model Comp'
                 WHEN F7 = 'special concept' THEN 'Special Concept'
                 WHEN F7 = 'racial group' THEN 'Social Context'
                 WHEN F7 = 'therapy' THEN 'Procedure'
                 WHEN F7 = 'external anatomical feature' THEN 'Body Structure'
                 WHEN F7 = 'organ component' THEN 'Body Structure'
                 WHEN F7 = 'physical device' THEN 'Physical Object'
                 WHEN F7 = 'linkage concept' THEN 'Linkage Concept'
                 WHEN F7 = 'link assertion' THEN 'Linkage Assertion'
                 WHEN F7 = 'metadata' THEN 'Model Comp'
                 WHEN F7 = 'navigational concept' THEN 'Navi Concept'
                 WHEN F7 = 'inactive concept' THEN 'Inactive Concept'
                 ELSE 'Undefined'
                 END
             FROM tmp_concept_class cc
            WHERE cc.concept_code = cs.concept_code)
	WHERE cs.vocabulary_id='SNOMED';

-- Assign top SNOMED concept
update concept_stage set concept_class_id='Model Comp' where concept_code=138875005 and vocabulary_id='SNOMED';
			
--5.	Choice of concept_name in SNOMED and synonyms
--1st Fix concept_names using tmp table:
