 									 where c.vocabulary_id='SNOMED')))
     WHERE rnc = 1;	
 	
-	create index x_cc_2cd on tmp_concept_class (concept_code);
-	
+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 = 'context-dependent category'
-                     THEN
-                        'Context-dependent'
-                     WHEN F7 = 'attribute'
-                     THEN
-                        'Attribute'
-                     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 = 'metadata'
-                     THEN
-                        'Model Comp'
-                     ELSE
-                        'Undefined'
-                  END
+                 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:

insert into peak (peak_code, peak_domain_id) values (118246004, 'Measurement');	-- 'Laboratory test finding' - child of excluded Sample observation
insert into peak (peak_code, peak_domain_id) values (71388002, 'Procedure'); --'Procedure'
insert into peak (peak_code, peak_domain_id) values (304252001, 'Procedure'); -- Resuscitate
insert into peak (peak_code, peak_domain_id) values (304253006, 'Procedure'); --DNR
insert into peak (peak_code, peak_domain_id) values (113021009, 'Procedure'); -- Cardiovascular measurement
insert into peak (peak_code, peak_domain_id) values (297249002, 'Observation'); --Family history of procedure
insert into peak (peak_code, peak_domain_id) values (14734007, 'Observation'); --Administrative procedure

                            AND a.descendant_concept_code = d.concept_code);
   END LOOP;
END;	
;
COMMIT;

-- Update top guy
update domain_snomed set domain_id = 'Metadata' where concept_code = 138875005;

-- Method 2: For those that slipped through the cracks assign domains by using the class_concept_id
-- Check out which these are and potentially fix and re-run Method 1
UPDATE domain_snomed d
   SET d.domain_id =
          (SELECT CASE c.concept_class_id
                  WHEN 'Admin Concept' THEN 'Note Type'
                  WHEN 'Attribute' THEN 'Observation'
                  WHEN 'Body Structure' THEN 'Spec Anatomic Site'
                  WHEN 'Clinical Finding' THEN 'Condition'
                  WHEN 'Context-dependent' THEN 'Observation'
                  WHEN 'Event' THEN 'Observation'
                  WHEN 'Inactive Concept' THEN 'Metadata'
                  WHEN 'Linkage Assertion' THEN 'Observation'
                  WHEN 'Location' THEN 'Observation'
                  WHEN 'Model Comp' THEN 'Metadata'
                  WHEN 'Morph Abnormality' THEN 'Observation'
                  WHEN 'Namespace Concept' THEN 'Metadata'
                  WHEN 'Navi Concept' THEN 'Metadata'
                  WHEN 'Observable Entity' THEN 'Observation'
                  WHEN 'Organism' THEN 'Observation'
                  WHEN 'Pharma/Biol Product' THEN 'Drug'
                  WHEN 'Physical Force' THEN 'Observation'
                  WHEN 'Physical Object' THEN 'Device'
                  WHEN 'Procedure' THEN 'Procedure'
                  WHEN 'Qualifier Value' THEN 'Observation'
                  WHEN 'Record Artifact' THEN 'Note Type'
                  WHEN 'Social Context' THEN 'Metadata'
                  WHEN 'Special Concept' THEN 'Metadata'
                  WHEN 'Specimen' THEN 'Specimen'
                  WHEN 'Staging / Scales' THEN 'Observation'
                  WHEN 'Substance' THEN 'Observation'
                  ELSE 'Observation'
                END
             FROM concept_stage c
            WHERE     c.concept_code = d.concept_code
                  AND C.VOCABULARY_ID = 'SNOMED')
 WHERE d.domain_id = 'Not assigned';
COMMIT;

-- 11.7. Update concept_stage from newly created domains.
-- drop index idx_domain_cc;
CREATE INDEX idx_domain_cc
   ON domain_snomed (concept_code);
   
