alter table ds_stage -- add mapped ingredient's concept_id to aid next step in dealing with dublicates
add concept_id int4
;
update ds_stage
set concept_id =
    (
        select concept_id_2
        from relationship_to_concept
        where
            concept_code_1 = ingredient_concept_code and
            precedence = 1
    )
;
--Fix ingredients that got replaced/mapped as same one (e.g. Sodium ascorbate + Ascorbic acid => Ascorbic acid)
drop table if exists ds_split
;
create table ds_split as
select distinct
    drug_concept_code,
    min (ingredient_concept_code :: bigint) over (partition by drug_concept_code, concept_id) :: varchar as ingredient_concept_code, --one at random
    sum (amount_value) over (partition by drug_concept_code, concept_id) as amount_value,
    amount_unit,
    sum (numerator_value) over (partition by drug_concept_code, concept_id) as numerator_value,
    numerator_unit,
    denominator_value,
    denominator_unit,
    null :: int4 as box_size,
    concept_id
from ds_stage
where
    (drug_concept_code, concept_id) in
    (
        SELECT drug_concept_code, concept_id
        FROM ds_stage
        GROUP BY drug_concept_code, concept_id
        HAVING COUNT(*) > 1
    )
;
delete from ds_stage
where
    (drug_concept_code, concept_id) in
    (
        SELECT drug_concept_code, concept_id
        FROM ds_split
    )
;
insert into ds_stage
select *
from ds_split
;
alter table ds_stage
drop column concept_id
;