 ;
 commit;
 
--- Create full set of extensions with all attribute and negative concept_id
+-- Create full set of extensions with all attribute (left side of full_corpus)
 -- It includes the existing concepts with positive existing concept_id
 drop table extension_attribute purge;
 create table extension_attribute nologging as

;
commit;

select * from q_mf;
select * from pc_stage;
select * from ex where concept_id<0;
-- create an xxx version of the existing (later all) packs
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, drug_concept_id, amount, box_size, bn_code, mf_code,
  case
    when mf_code is not null then 'Marketed Product'
    when box_size is not null and bn_code is not null then 'Branded Pack Box'
    when box_size is not null then 'Clinical Pack Box'
    when bn_code is not null then 'Branded Pack'
    else 'Clinical Pack'
  end as concept_class_id
*/
from pc_stage
-- Component drug
left join maps_to on drug_concept_code=from_code
where maps_to.to_id is null;

select 

  ) qr on qr.q_dcode=etc.c_code 
  left join ( -- Obtain Brand Name
    select concept_code_1, r_code, r_vocab from internal_relationship_stage join x_bn on q_code=concept_code_2
  ) q_bn on q_bn.concept_code_1=pcs.pack_concept_code
  left join ( -- Obtain Supplier
    select concept_code_1, r_code, r_vocab from internal_relationship_stage join x_mf on q_code=concept_code_2
  ) q_mf on q_mf.concept_code_1=pcs.pack_concept_code
) using ()
;
commit;

-- Create new to existing based on components and their amount, but not box_size, brand name and supplier (see below)
drop table pack_q_to_r purge;
create table pack_q_to_r as
select * 
from (
  select distinct pp.pack_concept_code, pp.pack_concept_id as r_id,
    case 
      when nvl(cbn.concept_code, ' ')=nvl(cp.bn_code, ' ') and nvl(cbn.vocabulary_id, ' ')=nvl(cp.brand_vocab, ' ') -- RxNorm does only provide unreliable Brand Name info for Branded Drugs
        and nvl(cmf.concept_code, ' ')=nvl(cp.supplier_code, ' ') and nvl(cmf.vocabulary_id, ' ')=nvl(cp.supplier_vocab, ' ') then 'Marketed'
      when nvl(cbn.concept_code, ' ')=nvl(cp.bn_code, ' ') and nvl(cbn.vocabulary_id, ' ')=nvl(cp.brand_vocab, ' ') 
        and cmf.concept_code is null then 'Branded' 
      when nvl(cmf.concept_code, ' ')=nvl(cp.supplier_code, ' ') and nvl(cmf.vocabulary_id, ' ')=nvl(cp.supplier_vocab, ' ')
        and cbn.concept_code is null then 'Supplied'
      when nvl(cmf.concept_code, cbn.concept_code) is null then 'Clinical'
      else 'Neither'
    end as match
  from (
    select pack_concept_code, pack_concept_id, count(8) as cnt -- the number of content components and their amount per matching pack
-- get those pack/component combinations that are mappable and whose amount matches
    from (
      select * 
-- extract the translatable drug components per pack
      from (
        select pack_concept_code, concept_id as drug_concept_id, amount from complete_pack join concept on concept_code=drug_concept_code and vocabulary_id=drug_vocab
      )
-- extract the drug components per pack in RxE
      join ( -- from RxE
        select distinct pack_concept_id, drug_concept_id, amount from pack_content
      ) using (drug_concept_id, amount)
    ) p_p 
    group by pack_concept_code, pack_concept_id
  ) pp
  -- join to new with same number of components
  join (
    select pack_concept_code, count(8) as cnt from complete_pack group by pack_concept_code
  ) pq on pq.pack_concept_code=pp.pack_concept_code and pq.cnt=pp.cnt
  -- join to existing with same amount of components
  join (
    select pack_concept_id, count(8) as cnt from pack_content group by pack_concept_id
  ) pr on pr.pack_concept_id=pp.pack_concept_id and pr.cnt=pp.cnt
  join complete_pack cp on cp.pack_concept_code=pp.pack_concept_code
  -- find brand name in Rx/e and match
  join concept p on p.concept_id=pp.pack_concept_id -- to check whether it's a Branded Pack, which doesn't always have a relationship to a Brand Name
  left join concept_relationship rb on rb.concept_id_1=pp.pack_concept_id and rb.relationship_id='Has brand name' --!!! check for ingredients problems
  left join concept cbn on (cbn.concept_id=rb.concept_id_2 or cbn.concept_id=1 and (p.concept_class_id='Branded Pack' and p.vocabulary_id='RxNorm'))-- need to find out if RxNorm or existing RxNorm Extension
  -- find supplier in Rx/e and match
  left join concept_relationship rs on rs.concept_id_1=pp.pack_concept_id and rs.relationship_id='Has marketed form'
  left join concept cmf on cmf.concept_id=rs.concept_id_2 -- need to find out if RxNorm or RxNorm Extension
)
-- create 4 columns out of rows 
pivot (
  min(r_id) for match in ('Clinical' as clinical, 'Branded' as branded, 'Supplied' as supplied, 'Marketed' as marketed, 'Neither' as neither)
)
;
commit;

drop table pack_name purge;

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 including packs (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
