--we are only interested to find brand names that have 'stable' ingredient sets: with one possible ingredient combination
drop table if exists brand_rx
;
create table brand_rx as
with bn_to_i as
    (
        select
            c.concept_id as b_id,
            r.concept_id_2 as i_id,
            c.concept_name as concept_name,
            count (r.concept_id_2) over (partition by c.concept_id) as cnt_direct
        from concept c
        join concept_relationship r on
            r.relationship_id = 'Brand name of' and
            c.concept_id = r.concept_id_1
        join concept c2 on
            c2.concept_class_id = 'Ingredient' and
            c2.concept_id = r.concept_id_2
        where
            c.vocabulary_id in ('RxNorm', 'RxNorm Extension') and
            c.concept_class_id = 'Brand Name' and
            c.invalid_reason is null
    ),
bn_to_i_dp as --what possible ingredient sets drug products give us
    (
        select distinct
            c.concept_id as b_id,
            r.concept_id_2 as dp_id,
            d.ingredient_concept_id as i_id,
            count (d.ingredient_concept_id) over (partition by r.concept_id_2) as cnt_drug
        from concept c
        join concept_relationship r on
            r.relationship_id = 'Brand name of' and
            c.concept_id = r.concept_id_1
        join concept c2 on
            c2.concept_class_id != 'Ingredient' and --only combinations and ingredient themselves can have brand names;
            c2.concept_id = r.concept_id_2
        join drug_strength d on
            c2.concept_id = d.drug_concept_id
        where
            c.vocabulary_id in ('RxNorm', 'RxNorm Extension') and
            c.concept_class_id = 'Brand Name' and
            c.invalid_reason is null
    )
select distinct
    b.b_id,
    b.concept_name,
    b.i_id
from bn_to_i b
left join bn_to_i_dp d on
    d.b_id = b.b_id and
    b.cnt_direct > d.cnt_drug
where d.b_id is null
;
insert into brand_rx
--preserve also bn that are consistent inside RxN
with bn_to_i as
    (
        select
            c.concept_id as b_id,
            r.concept_id_2 as i_id,
            c.concept_name as concept_name,
            count (r.concept_id_2) over (partition by c.concept_id) as cnt_direct
        from concept c
        join concept_relationship r on
            r.relationship_id = 'Brand name of' and
            c.concept_id = r.concept_id_1
        join concept c2 on
            c2.concept_class_id = 'Ingredient' and
            c2.concept_id = r.concept_id_2
        where
            c.concept_id not in (select b_id from brand_rx) and --avoid duplication
            c.vocabulary_id = 'RxNorm' and
            c.concept_class_id = 'Brand Name' and
            c.invalid_reason is null and
            exists
            -- there are RxNorm Drug products with r.concept_id_2 as an ingredient
                (
                    select
                    from drug_strength d
                    join concept x on
                        d.drug_concept_id = x.concept_id and
                        x.vocabulary_id = 'RxNorm' and
                        x.concept_class_id != 'Ingredient' and
                        d.ingredient_concept_id = r.concept_id_2
                    -- with that brand name and ingredient
                    join concept_relationship cr on
                        cr.concept_id_1 = x.concept_id and
                        relationship_id = 'Has brand name' and
                        cr.concept_id_2 = c.concept_id
                    where d.invalid_reason is null
                )
    ),
bn_to_i_dp as --what possible ingredient sets drug RxN products give us
    (
        select distinct
            c.concept_id as b_id,
            r.concept_id_2 as dp_id,
            d.ingredient_concept_id as i_id,
            count (d.ingredient_concept_id) over (partition by r.concept_id_2) as cnt_drug
        from concept c
        join concept_relationship r on
            r.relationship_id = 'Brand name of' and
            c.concept_id = r.concept_id_1
        join concept c2 on
            c2.concept_class_id != 'Ingredient' and --only combinations and ingredient themselves can have brand names;
            c2.concept_id = r.concept_id_2
        join drug_strength d on
            c2.concept_id = d.drug_concept_id
        where
            c.concept_id not in (select b_id from brand_rx) and --avoid duplication
            c.vocabulary_id = 'RxNorm' and
            c.concept_class_id = 'Brand Name' and
            c.invalid_reason is null and
            d.invalid_reason is null
    )
select distinct
    b.b_id,
    b.concept_name,
    b.i_id
from bn_to_i b
left join bn_to_i_dp d on
    d.b_id = b.b_id and
    b.cnt_direct > d.cnt_drug
where d.b_id is null
;