 ;
 commit;
 
--- Preferred Brand Name translations. Most of them are one-to-one
+-- Preferred Brand Name translations. Usually brands are one-to-one
 drop table x_bn purge;
 create table x_bn nologging as
 select distinct 

join drug_concept_stage f on f.concept_code=ir.concept_code_2 and f.concept_class_id='Dose Form' and f.domain_id='Drug' -- Dose Form of a drug
;

-- Create table with Brand Name information for each drug (if exists)
drop table q_bn purge;
create table q_bn nologging as
select distinct ir.concept_code_1 as concept_code, b.concept_code as bn_code -- distinct only because source contains duplicated maps

;
commit;

-- create a complete set of packs with attributes
drop table existing_pack purge;
create table existing_pack as
select distinct -- because the content in some packs, albeit different in drug_concept_stage, becomes identical after mapping
  pack_concept_code, maps_to.to_id as drug_concept_id, nvl(amount, 0) as amount, nvl(box_size, 0) as bs, nvl(bn_id, 0) as bn_id, nvl(mf_id, 0) as mf_id,
  case
    when mf_id is not null then 'Marketed Product'
    when box_size is not null and bn_id is not null then 'Branded Pack Box'
    when box_size is not null then 'Clinical Pack Box'
    when bn_id is not null then 'Branded Pack'
    else 'Clinical Pack'
  end as concept_class_id
from pc_stage
-- Component drug
join maps_to on drug_concept_code=from_code -- all components should exist, either in RxE or in the new tables
left join ( -- Obtain Brand Name if exists, could be more than one effective r_to_c
  select distinct concept_code, nvl(x.bn_id, ex.bn_id) as bn_id from q_bn left join x_bn x using(bn_code) left join extension_bn ex using(bn_code)
) q_bn on q_bn.concept_code=pack_concept_code 
left join ( -- Obtain Supplier if exists, could be more than one effective r_to_c
  select distinct concept_code, nvl(x.mf_id, ex.mf_id) as mf_id from q_mf left join x_mf x using(mf_code) left join extension_mf ex using(mf_code)
) q_mf on q_mf.concept_code=pack_concept_code
;
commit;

-- Create pack hierarchy
drop table complete_pack purge;
create table complete_pack as
select * from existing_pack where concept_class_id='Marketed Product';

-- Branded Pack Box. Definition: bn and bs, but no mf.
insert into complete_pack
select pack_concept_code, drug_concept_id, amount, bs, bn_id, 0 as mf_id, 'Branded Pack Box' as concept_class_id 
from (
  select pack_concept_code, drug_concept_id, amount, bs, bn_id from complete_pack
union
  select pack_concept_code, drug_concept_id, amount, bs, bn_id from existing_pack where bs!=0 and bn_id!=0 and mf_id=0
);
commit;

-- Clinical Pack Box. Definition: bs, but no bn or mf.
insert into complete_pack
select pack_concept_code, drug_concept_id, amount, bs, 0 as bn_id, 0 as mf_id, 'Clinical Pack Box' as concept_class_id 
from (
  select pack_concept_code, drug_concept_id, amount, bs from complete_pack
union
  select pack_concept_code, drug_concept_id, amount, bs from existing_pack where bs!=0 and bn_id=0 and mf_id=0
);

-- Branded Pack. Definition: bn, but no bs or mf.
insert into complete_pack
select pack_concept_code, drug_concept_id, amount, 0 as bs, bn_id, 0 as mf_id, 'Branded Pack' as concept_class_id 
from (
  select pack_concept_code, drug_concept_id, amount, bn_id from complete_pack
union
  select pack_concept_code, drug_concept_id, amount, bn_id from existing_pack where bn_id!=0 and bs=0 and mf_id=0
);

-- Clinical Pack. Definition: neither bn, bs nor mf.
insert into complete_pack
select pack_concept_code, drug_concept_id, amount, 0 as bs, 0 as bn_id, 0 as mf_id, 'Clinical Pack' as concept_class_id 
from (
  select pack_concept_code, drug_concept_id, amount from complete_pack
union
  select pack_concept_code, drug_concept_id, amount from existing_pack where bn_id=0 and bs=0 and mf_id=0
);
commit;

-- Match to existing 
drop table qr_pack purge;
create table qr_pack nologging as
with q_pc as (
  select pack_concept_code, drug_concept_id, amount, bs, bn_id, mf_id, cnt from complete_pack join(select pack_concept_code, count(8) as cnt from complete_pack group by pack_concept_code having count(8)>1) using(pack_concept_code)
),
pc as (
  select pack_concept_id, drug_concept_id, nvl(amount, 0) as amount, nvl(box_size, 0) as bs, nvl(bn_id, 0) as bn_id, nvl(mf_id, 0) as mf_id
  from pack_content pc
  left join r_bn on drug_concept_id=r_bn.concept_id
  left join r_mf on drug_concept_id=r_mf.concept_id
  join concept on pack_concept_id=concept.concept_id and vocabulary_id='RxNorm'
),
r_pc as (-- includes counts of components
  select pack_concept_id, drug_concept_id, amount, bs, bn_id, mf_id, cnt from pc join(select pack_concept_id, count(8) as cnt from pc group by pack_concept_id having count(8)>1) using(pack_concept_id)
)
select distinct pack_concept_code, pack_concept_id from q_pc join r_pc using(drug_concept_id, amount, bs, bn_id, mf_id, cnt)
order by 1;
commit;

drop table pack_name purge;
