                             AND a.descendant_concept_code = d.concept_code);
    END LOOP;
 END;	
-COMMIT;		
+;
+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 'Clinical Finding' THEN 'Condition'
-                     WHEN 'Procedure' THEN 'Procedure'
-                     WHEN 'Pharma/Biol Product' THEN 'Drug'
-                     WHEN 'Physical Object' THEN 'Device'
-                     WHEN 'Model comp' THEN 'Metadata'
-                     ELSE 'Observation'
-                  END
+                  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.
+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);
    

									 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 = '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
             FROM tmp_concept_class cc
            WHERE cc.concept_code = cs.concept_code)
	WHERE cs.vocabulary_id='SNOMED';
			
--5.	Choice of concept_name in SNOMED and synonyms
--1st Fix concept_names using tmp table:

COMMIT;

-- 11.4. Find other peak concepts (orphans) that are missed from the above manual list, and assign them a domain_id based on heuristic. 
-- This is a catch for those circumstances if the SNOMED hierarchy as changed and the peak list is no longer complete
INSERT INTO peak -- before doing that check first out without the insert
   SELECT DISTINCT
          c.concept_code AS peak_code,
