---"reboot" drug_concept_stage
---set concept_class_id = 'Supplier' where concept_class_id = 'Manufacturer', find this and set supplier in the beginning 
-drop table drug_concept_stage; 
-;
-create table drug_concept_stage as select * from
-drug_concept_stage_existing
- -- ok, what the hell? seems that this step gave most of mistakes, cause I use some misterious drug_concept_stage_existing -- check why I do so
-;
-select * from drug_concept_stage_existing
+
+CREATE INDEX drug_cnc_st_l_ix ON drug_concept_stage (lower (concept_name))
+;
+CREATE INDEX drug_cnc_st_ix ON drug_concept_stage ( concept_name)
+;
+CREATE INDEX drug_cnc_st_c_ix ON drug_concept_stage ( concept_code)
+;
+exec DBMS_STATS.GATHER_TABLE_STATS (ownname => USER, tabname  => 'drug_concept_stage', cascade  => true)
 ;
 --work with manual table 
 drop table ingred_to_ingred_FINAL_BY_Lena;

join concept c on a.concept_code = c.concept_code and c.vocabulary_id = 'SNOMED' and a.concept_class_id like 'Branded Drug%'
 join concept_relationship r on r.concept_id_1 = c.concept_id 
 join concept d on r.concept_id_2 = d.concept_id and d.vocabulary_id = 'SNOMED'
join drug_concept_stage cs on cs.concept_code  = d.concept_code and cs.concept_class_id like 'Clinical Drug%')
;
--duplicates due to ONLY DEPRECATED to NON-DEPRECATED DRUGS relationship
DELETE from Branded_to_clinical a 
where exists (select 1 from 
(

 b.concept_code as concept_code_2, b.concept_name as concept_name_2, b.invalid_reason 
 from drug_concept_stage a 
 join drug_concept_stage b on a.concept_name like  '%('||b.concept_name||')%'
 where b.concept_class_id ='Supplier'
 and a.concept_class_id like 'Branded Drug%' and a.concept_code not in (select concept_code from non_drug_full)
 ;
 */
 --CLinical Drug to ingredients using existing relationship, later this relationship will be updated with ds_stage table
; 

 drop table Clinical_to_Ingred;
 create table Clinical_to_Ingred as
select distinct a.concept_code as concept_code_1, a.concept_name as concept_name_1, cs.concept_code as concept_code_2, cs.concept_name as concept_name_2, cs.INSERT_ID 
