 (concept_class_id like '%Drug%' or 
 concept_class_id like 'Device')
 and invalid_reason is null
-or concept_code in (select concept_code_1 from ingred_to_ingred_FINAL_BY_Lena where concept_code_2 is null) --"standard ingredient"
+or concept_code in (select concept_code_1 from ingr_to_ingr where concept_code_2 is null) --"standard ingredient"
 
   ;
   --add newly created ingredients
   insert into  drug_concept_stage (CONCEPT_NAME,DOMAIN_ID,VOCABULARY_ID,CONCEPT_CLASS_ID,STANDARD_CONCEPT,CONCEPT_CODE,VALID_START_DATE,VALID_END_DATE,INVALID_REASON, source_concept_class_id)
  select distinct                  INGR_NAME        ,'Drug', 'dm+d', 'Ingredient', 'S', INGR_CODE, TO_DATE ('19700101', 'yyyymmdd'), TO_DATE ('20991231', 'yyyymmdd'), '', 'Ingredient' from lost_ingr_to_rx_with_OMOP  where INGR_CODE !='OMOP18'
-
  ;
+--make ingredients that don't have replacement "Standard"
+update drug_concept_stage set STANDARD_CONCEPT ='S' where concept_code in (
+ SELECT concept_code
+      FROM drug_concept_stage
+        LEFT JOIN internal_relationship_stage ON concept_code_1 = concept_code
+      WHERE concept_class_id = 'Ingredient' AND   standard_concept IS  NULL AND  concept_code_2 IS NULL
+      )
+      ;
+commit
+;
  --it's OK, classes used in algorihms
 update drug_concept_stage set concept_class_id = 'Drug Product' where concept_class_id like '%Pack%' or concept_class_id like '%Drug%'
 ;

--recheck in future about complicated dosages
drop table  clin_dr_to_ingr_two;
create table  clin_dr_to_ingr_two as 
select distinct a.*, concept_code_2 as ingredient_concept_code, concept_name_2 as ingredient_concept_name, insert_id_2 
from drug_concept_stage_tmp_0 a 
join Clinical_to_Ingred_tmp  b on 
 a.concept_code = b.concept_code_1 

;
commit
;
--to do 
-- replace new OMOPs with existing = done




-- add mappings to new RxE = check what happens if we use old codes
-- formalize manaul table = check what's not mapped = try to use two cycles aproach!
-- change ppm to 0.001 of %
-- links to deprecated ATCs and ingredients 





drop table r_to_c;

create table r_to_c as 
  select r.* from relationship_to_concept r join concept on concept_id=r.concept_id_2 and vocabulary_id in ('RxNorm', 'RxNorm Extension', 'UCUM') where r.concept_code_1 is not null
