-- CREATE TABLE map_auto AS
    WITH unmapped AS (
            SELECT DISTINCT
                d.prod_prd_id,
                regexp_replace(d.prd_name, ' (\d+) (\d+ ?(MG|MCG|G(?!EL)|UI|IU))', ' \1.\2', 'g') AS fixed_name,
                x.concept_code as component
            FROM belg_source d
            JOIN prod_to_ing c ON c.prod_prd_id = d.prod_prd_id
            join drug_concept_stage x on
                c.prod_prd_id || ':' || c.comp_number = x.concept_name and
                x.concept_class_id = 'Ingredient'
            WHERE
                d.prod_prd_id NOT IN
                    (
                        SELECT drug_concept_code
                        FROM ds_stage
                    )
                AND d.prd_name NOT IN
                    (
                        SELECT prd_name
                        FROM devices_mapped
                    )
                AND regexp_match (d.prd_name, '(X| )\d+ ?(MCG|MG|G(?!EL))( |$)') is not null
                AND (ARRAY(SELECT unnest(regexp_matches(d.prd_name, '((?:\d+)\.?(?:\d+)? ?(?:MCG|MG|G(?!EL)|UI|IU) )', 'g')))) [3] IS NULL
            ),
        list AS ( --only 1 or 2 ingredient drugs can be processed this way; more is unreliable
            SELECT prod_prd_id
            FROM unmapped
            GROUP BY prod_prd_id
            HAVING count(component) < 3
            )

SELECT DISTINCT
    u.prod_prd_id,
    u.fixed_name,
-- amount 1
    substring (u.fixed_name, '[\d\.]+(?= ?(MG|MCG|G(?!EL)|UI|IU))') :: float AS a1,
-- unit 1
    substring (u.fixed_name, '[\d\.]+ ?(MG|MCG|G(?!EL)|UI|IU)') AS u1,
-- amount 2
    regexp_matches(u.fixed_name, '[\d\.]+(?= ?(MG|MCG|G(?!EL)|UI|IU))', 'g') AS a2,
-- unit 2
--  lower(substring((ARRAY(SELECT unnest(regexp_matches(u.fixed_name, '(?:(\d|\.)+)?(?:\d+ ?(?:MCG|MG|G(?!EL)|UI|IU))', 'g')))) [2], '[A-Z]+')) AS u2,
    min(u.component) OVER (PARTITION BY u.prod_prd_id) AS i1,
    max(u.component) OVER (PARTITION BY u.prod_prd_id) AS i2
FROM unmapped u
WHERE prod_prd_id IN (
        SELECT *
        FROM list
        );