 -- Translation of individual ingredients, whether found in r or not
 -- x_pattern is for i_combo translations in Drug Forms, x_ing for translations of individual ingredients, such as in extension_uds
 -- Get all the ones in x_pattern, which contains everything that is translated somewhere
-drop table if exists x_ing;
-create table x_ing as
-select distinct qi_combo, first_value(ri_combo) over (partition by qi_combo order by prec, cnt desc, qi_combo) as ri_combo
-from (
-  select qi_combo, ri_combo, prec, count(*) as cnt from x_pattern where ri_combo not like '%-%' group by qi_combo, ri_combo, prec
-) as s0
-;
+DROP TABLE IF EXISTS x_ing;
+CREATE UNLOGGED TABLE x_ing AS
+SELECT DISTINCT s0.qi_combo,
+	FIRST_VALUE(s0.ri_combo) OVER (
+		PARTITION BY s0.qi_combo ORDER BY s0.prec,
+			s0.cnt DESC,
+			s0.qi_combo
+		) AS ri_combo
+FROM (
+	SELECT qi_combo,
+		ri_combo,
+		prec,
+		COUNT(*) AS cnt
+	FROM x_pattern
+	WHERE ri_combo NOT LIKE '%-%'
+	GROUP BY qi_combo,
+		ri_combo,
+		prec
+	) AS s0;
 
 -- Add any translations not found in the data, but provided by the input tables even if no drug contains them
 -- (x_i_combo contains only ingredients that have a drug as a descendent)
-insert into x_ing
-select distinct
-  concept_code_1 as qi_combo,
-  first_value (i_code) over (partition by concept_code_1 order by precedence) as ri_combo -- pick the best translation
-from r_to_c rc
-join ing_stage on i_id=concept_id_2 -- limit to ingredients and get xxx-code
-where not exists (
-  select 1 from x_ing where qi_combo=concept_code_1
-)
-;
+INSERT INTO x_ing
+SELECT DISTINCT rtc.concept_code_1 AS qi_combo,
+	FIRST_VALUE(i.i_code) OVER (
+		PARTITION BY rtc.concept_code_1 ORDER BY rtc.precedence
+		) AS ri_combo -- pick the best translation
+FROM r_to_c rtc
+JOIN ing_stage i ON i.i_id = rtc.concept_id_2 -- limit to ingredients and get xxx-code
+WHERE NOT EXISTS (
+		SELECT 1
+		FROM x_ing x_int
+		WHERE x_int.qi_combo = rtc.concept_code_1
+		);
 
 -- Preferred Dose Form translations. These may be a little optimistic, as DFGs are fairly broad
-drop table if exists x_df;
-create table x_df as
-select distinct 
-  df_code,
-  first_value(df_id) over (partition by df_code order by prec, cnt desc, df_id) as df_id, -- pick the most common translation in the data
-  first_value(dfg_id) over (partition by df_code order by prec, cnt desc, df_id) as dfg_id, -- pick the most common translation in the data
-  first_value(concept_name) over (partition by df_code order by prec, cnt desc, df_id) as concept_name -- and the corresponding name
-from (
-  select df_code, df_id, prec, count(8) as cnt from x_pattern where df_id!=0 group by df_code, df_id, prec
-) as s0
-join concept on concept_id=df_id
-left join dfg using(df_id)
-;
+DROP TABLE IF EXISTS x_df;
+CREATE UNLOGGED TABLE x_df AS
+SELECT DISTINCT s0.df_code,
+	FIRST_VALUE(s0.df_id) OVER (
+		PARTITION BY s0.df_code ORDER BY s0.prec,
+			s0.cnt DESC,
+			s0.df_id
+		) AS df_id, -- pick the most common translation in the data
+	FIRST_VALUE(d.dfg_id) OVER (
+		PARTITION BY s0.df_code ORDER BY s0.prec,
+			s0.cnt DESC,
+			s0.df_id
+		) AS dfg_id, -- pick the most common translation in the data
+	FIRST_VALUE(c.concept_name) OVER (
+		PARTITION BY s0.df_code ORDER BY s0.prec,
+			s0.cnt DESC,
+			s0.df_id
+		) AS concept_name -- and the corresponding name
+FROM (
+	SELECT df_code,
+		df_id,
+		prec,
+		COUNT(*) AS cnt
+	FROM x_pattern
+	WHERE df_id <> 0
+	GROUP BY df_code,
+		df_id,
+		prec
+	) AS s0
+JOIN concept c ON c.concept_id = s0.df_id
+LEFT JOIN dfg d ON d.df_id = s0.df_id;
 
 -- Add the ones that are not translated in the data
-insert into x_df
-select df_code, df_id, dfg.dfg_id, concept_name
-from qr_df join concept on df_id=concept_id
-left join dfg using(df_id)
-where df_code not in (select df_code from x_df) -- don't translate the ones already there
-and df_prec=1
-;
+INSERT INTO x_df
+SELECT q.df_code,
+	q.df_id,
+	d.dfg_id,
+	c.concept_name
+FROM qr_df q
+JOIN concept c ON c.concept_id = q.df_id
+LEFT JOIN dfg d ON d.df_id = q.df_id
+WHERE NOT EXISTS (
+		SELECT 1
+		FROM x_df x_int
+		WHERE x_int.df_code = q.df_code
+		) -- don't translate the ones already there
+	AND q.df_prec = 1;
 
 -- Preferred Brand Name translations. Usually brands are one-to-one
-drop table if exists x_bn;
-create table x_bn as
-select distinct 
-  bn_code,
-  first_value(bn_id) over (partition by bn_code order by prec, cnt desc, bn_id) as bn_id, -- pick the most common translation in the data
-  first_value(concept_name) over (partition by bn_code order by prec, cnt desc, bn_id) as concept_name 
-from (
-  select bn_code, bn_id, prec, count(8) as cnt from x_pattern where bn_id!=0 group by bn_code, bn_id, prec
-) as s0
-join concept on concept_id=bn_id
-;
+DROP TABLE IF EXISTS x_bn;
+CREATE UNLOGGED TABLE x_bn AS
+SELECT DISTINCT s0.bn_code,
+	FIRST_VALUE(s0.bn_id) OVER (
+		PARTITION BY s0.bn_code ORDER BY s0.prec,
+			s0.cnt DESC,
+			s0.bn_id
+		) AS bn_id, -- pick the most common translation in the data
+	FIRST_VALUE(c.concept_name) OVER (
+		PARTITION BY s0.bn_code ORDER BY s0.prec,
+			s0.cnt DESC,
+			s0.bn_id
+		) AS concept_name
+FROM (
+	SELECT bn_code,
+		bn_id,
+		prec,
+		COUNT(*) AS cnt
+	FROM x_pattern
+	WHERE bn_id <> 0
+	GROUP BY bn_code,
+		bn_id,
+		prec
+	) AS s0
+JOIN concept c ON c.concept_id = s0.bn_id;
 
 -- Add the ones that are not translated in the data
-insert into x_bn
-select bn_code, bn_id, concept_name
-from qr_bn join concept on concept_id=bn_id
-where bn_code not in (select bn_code from x_bn) -- don't translate the ones already there
-and bn_prec=1
-;
+INSERT INTO x_bn
+SELECT q.bn_code,
+	q.bn_id,
+	c.concept_name
+FROM qr_bn q
+JOIN concept c ON c.concept_id = q.bn_id
+WHERE NOT EXISTS (
+		SELECT 1
+		FROM x_bn x_int
+		WHERE x_int.bn_code = q.bn_code
+		) -- don't translate the ones already there
+	AND q.bn_prec = 1;
 
 -- Preferred Supplier translations
-drop table if exists x_mf;
-create table x_mf as
-select distinct 
-  mf_code,
-  first_value(mf_id) over (partition by mf_code order by prec, cnt desc, mf_id) as mf_id, -- pick the most common translation in the data
-  first_value(concept_name) over (partition by mf_code order by prec, cnt desc, mf_id) as concept_name 
-from (
-  select mf_code, mf_id, prec, count(8) as cnt from x_pattern where mf_id!=0 group by mf_code, mf_id, prec
-) as s0
-join concept on concept_id=mf_id
-;
+DROP TABLE IF EXISTS x_mf;
+CREATE UNLOGGED TABLE x_mf AS
+SELECT DISTINCT s0.mf_code,
+	FIRST_VALUE(s0.mf_id) OVER (
+		PARTITION BY s0.mf_code ORDER BY s0.prec,
+			s0.cnt DESC,
+			s0.mf_id
+		) AS mf_id, -- pick the most common translation in the data
+	FIRST_VALUE(c.concept_name) OVER (
+		PARTITION BY s0.mf_code ORDER BY s0.prec,
+			s0.cnt DESC,
+			s0.mf_id
+		) AS concept_name
+FROM (
+	SELECT mf_code,
+		mf_id,
+		prec,
+		COUNT(*) AS cnt
+	FROM x_pattern
+	WHERE mf_id <> 0
+	GROUP BY mf_code,
+		mf_id,
+		prec
+	) AS s0
+JOIN concept c ON c.concept_id = s0.mf_id;
 
 -- Add the ones that are not translated in the data
-insert into x_mf
-select mf_code, mf_id, concept_name
-from qr_mf join concept on concept_id=mf_id
-where mf_code not in (select mf_code from x_mf) -- don't translate the ones already there
-and mf_prec=1
-;
+INSERT INTO x_mf
+SELECT q.mf_code,
+	q.mf_id,
+	c.concept_name
+FROM qr_mf q
+JOIN concept c ON c.concept_id = q.mf_id
+WHERE NOT EXISTS (
+		SELECT 1
+		FROM x_mf x_int
+		WHERE x_int.mf_code = q.mf_code
+		) -- don't translate the ones already there
+	AND q.mf_prec = 1;
 
 /*********************************************************
 * 8. Build extensions for ing, uds, combo, df, bn and mf *

END $_$;

-- Clean up tables
drop view r_to_c;
drop sequence ds_seq;
drop sequence xxx_seq;
drop sequence extension_id;
drop table q_ing;
drop table ds_rounded;
drop table q_uds;
drop table q_ds;
drop table q_combo;
drop table q_quant;
drop table q_df;
drop table q_bn;
drop table q_mf;
drop table q_bs;
drop table q_existing;
drop table ing_stage;
drop table r_ing;
drop table r_uds;
drop table r_ds;
drop table r_combo;
drop table r_quant;
drop table r_df;
drop table r_bn;
drop table r_mf;
drop table r_bs;
drop table r_existing;
drop table qr_uds;
drop table qr_ds;
drop table qr_d_combo;
drop table qr_i;
drop table qr_i_combo;
drop table qr_quant;
drop table qr_ing;
drop table qr_df;
drop table qr_bn;
drop table qr_mf;
drop table x_unit;
drop table dfg;
drop table x_pattern;
drop table x_ing;
drop table x_df;
drop table x_bn;
drop table x_mf;
drop table extension_i;
drop table extension_uds;
drop table reduce_euds;
drop table extension_ds;
drop table extension_combo;
drop table extension_df;
drop table extension_bn;
drop table extension_mf;
drop table full_corpus;
drop table q_breakup;
drop table r_breakup;
drop table extension_attribute;
drop table maps_to;
drop table rxnorm_unit;
drop table spelled_out;
drop table extension_name;
drop table q_existing_pack;
drop table r_existing_pack;
drop table full_pack;
drop table pack_attribute;
drop table pack_name;

drop sequence omop_seq;
drop table rl;
drop table ex;
drop table xxx_replace;


-- Auto-generate all names 
-- Create RxNorm-style units. UCUM units have no normalized abbreviation
DROP TABLE IF EXISTS rxnorm_unit;
CREATE UNLOGGED TABLE rxnorm_unit (
	rxn_unit VARCHAR(20),
	concept_id int4
	);

INSERT INTO rxnorm_unit VALUES 
 ('ORGANISMS', 45744815), -- Organisms, UCUM calls it {bacteria}
 ('%', 8554),
 ('ACTUAT', 45744809), -- actuation
