-- sql
-- $Source: gm_brand-ca.sql
-- $Revision: 1
-- $Author: Sebastian Hilbert, Vaibhav S Banait, GNUmed project
-- ---------------------------
-- Description:
-- script for importing Indian drug data
-- … into GNUmed ref.branded_drug
-- … renamed as 'install-data-pack.sql' within GNUmed data packs
-- … configurational name = variants of 'Drugs, brand, <Country>'
-- … zip archive name = variants of 'drugs_brand_COUNTRY.zip
-- Caveats:
-- 1. The data in this pack will have been acquired directly from a CSV file maintained by Dr. Banait
-- 2. It best avoids duplication in brand information when the praxis branded drugs info
--    was obtained only from earlier Canadian data packs or from FreeDiams Canadian db.
-- 3. The data pack will register new drugs as though they were from source FreeDiams. This is in 
--    order to leverage interoperability with FreeDiams' HCDPD-derived Canadian db source.
-- ---------------------------
-- References / links:
-- GNUmed git repository, data packs and project
--   git://gitorious.org/gnumed/gnumed.git
--   http://wiki.gnumed.de/bin/view/Gnumed/GmManualReferenceData
--   http://www.gnumed.de/downloads/data/data-packs.conf
--   http://wiki.gnumed.de
-- ----------------------

-- uncomment the following to run from psql (instead of from GNUmed GUI)
-- set default_transaction_read_only to off;

-----------------------------------------------------------------------------
-- ************************************************************
-- set ON_ERROR_STOP 1
-- ************************************************************
-- ---------------------------------------------------------------------------
\set ON_ERROR_STOP 1

-----------------------------------------------------------------------------
-- ************************************************************
-- create staging table
-- ************************************************************
-- -----------------------------------------------------------------------------
-- ---------------------------------------------
\unset ON_ERROR_STOP
DROP TABLE staging.branded_drug CASCADE;
DROP TABLE staging.consumable_substance CASCADE;
DROP TABLE staging.vaccine CASCADE;
DROP TABLE staging.ingred CASCADE;
DROP TABLE staging.ingred2ind CASCADE;
\set ON_ERROR_STOP 1

begin;

CREATE TABLE staging.branded_drug
(
  description text NOT NULL,
  preparation text NOT NULL,
  atc_code text,
  is_fake boolean NOT NULL DEFAULT false,
  fk_data_source integer,
  external_code text,
  external_code_type text,
  drug_code integer
);

CREATE TABLE staging.consumable_substance
(
  description text,
  atc_code text,
  amount numeric,
  unit text,
  drug_code integer
);

CREATE TABLE staging.ingred (
    drug_code integer,
    ingred_code integer,
    description varchar(240),
    supplied varchar(1),
    strength numeric,
    strength_unit varchar(40),
    strength_type varchar(40),
    dosage_value varchar(20),
    base varchar(1),
    dosage_unit varchar(40),
    notes varchar(2000)
);

CREATE TABLE staging.vaccine
(
	is_live boolean,
	external_code varchar,
	route varchar
);

CREATE TABLE staging.ingred2ind (
	ingred_code integer,
	fk_indication integer
);

-- -----------------------------------------------------------------------------
-- *********************
-- import staging data
-- *********************
-- -----------------------------------------------------------------------------
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = staging, pg_catalog;

--
-- Data for Name: branded_drug; Type: TABLE DATA; Schema: staging; Owner: gm-dbo
--

INSERT INTO staging.branded_drug (description, preparation, atc_code, is_fake, fk_data_source, external_code, external_code_type, drug_code) VALUES ('Tapazole 5mg Tablet', 'tablet', 'H03BB02', false, 0, '00015741', 'FreeDiams::CA_HCDPD::DIN', 9);
INSERT INTO staging.branded_drug (description, preparation, atc_code, is_fake, fk_data_source, external_code, external_code_type, drug_code) VALUES ('Aventyl', 'capsule', 'N06AA10', false, 0, '00015229', 'FreeDiams::CA_HCDPD::DIN', 15);

--
-- PostgreSQL database dump complete
--

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = staging, pg_catalog;

--
-- Data for Name: ingred; Type: TABLE DATA; Schema: staging; Owner: gm-dbo
--

INSERT INTO staging.ingred (drug_code, ingred_code, description, supplied, strength, strength_unit, strength_type, dosage_value, base, dosage_unit, notes) VALUES (9518, 3182, 'OXYQUINOLINE', 'I', 0.5, '%', '', '', 'N', '', '8-HYDROXYQUINOLINE 0.4% CHG TO 0.5% AS PER VDD 2006.04.05');
INSERT INTO staging.ingred (drug_code, ingred_code, description, supplied, strength, strength_unit, strength_type, dosage_value, base, dosage_unit, notes) VALUES (10804, 3182, 'OXYQUINOLINE', 'I', 0.14, '%', '', '', 'N', '%', 'Historic Freeform Text: 8-HYDROXYQUINOLINE');
INSERT INTO staging.ingred (drug_code, ingred_code, description, supplied, strength, strength_unit, strength_type, dosage_value, base, dosage_unit, notes) VALUES (1338, 3047, 'ALDIOXA', 'I', 0.2, '%', '', '', 'N', '', 'Historic Freeform Text: ALDIOXA');
INSERT INTO staging.ingred (drug_code, ingred_code, description, supplied, strength, strength_unit, strength_type, dosage_value, base, dosage_unit, notes) VALUES (21328, 3049, 'MANGANESE OXIDE', 'I', 15, 'G', '', '', 'N', 'KG', 'Historic Freeform Text: MANGANESE OXIDE');


--
-- PostgreSQL database dump complete
--

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = staging, pg_catalog;

--
-- Data for Name: consumable_substance; Type: TABLE DATA; Schema: staging; Owner: gm-dbo
--

INSERT INTO staging.consumable_substance (description, atc_code, amount, unit, drug_code) VALUES ('aldioxa', NULL, 0.2, '%', 1338);
INSERT INTO staging.consumable_substance (description, atc_code, amount, unit, drug_code) VALUES ('meglumine iodipamide', NULL, 26.9, '%', 37573);
INSERT INTO staging.consumable_substance (description, atc_code, amount, unit, drug_code) VALUES ('eugenol', NULL, 13.7, 'g / 100 g', 4875);

--
-- PostgreSQL database dump complete
--

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = staging, pg_catalog;

--
-- Data for Name: vaccine; Type: TABLE DATA; Schema: staging; Owner: gm-dbo
--

INSERT INTO staging.vaccine (is_live, external_code, route) VALUES (NULL, '00075124', 'INTRADERMAL');
INSERT INTO staging.vaccine (is_live, external_code, route) VALUES (NULL, '00428833', 'SUBCUTANEOUS');
INSERT INTO staging.vaccine (is_live, external_code, route) VALUES (NULL, '00431648', 'SUBCUTANEOUS');

--
-- PostgreSQL database dump complete
--



-- ===========================================================
-- add to ref.branded_drug if does not already exist
-- ===========================================================

-- provide for linkage to FreeDiams if does not exist
INSERT INTO Ref.data_source (
  version,
  name_short,
  name_long,
  source
)
SELECT '0.54', 'FreeDiams', '"Free Diams" Drug Database Frontend', 'http://ericmaeker.fr/FreeMedForms/di-manual/index.html' FROM Ref.data_source
WHERE NOT EXISTS (SELECT 1 FROM Ref.data_source WHERE position('FreeDiams' in name_short) > 0)
LIMIT 1
;

-- update staging.branded_drug with in-database foreign key to fk_data_source
UPDATE staging.branded_drug
SET fk_data_source =
(SELECT pk from Ref.data_source WHERE position('FreeDiams' in name_short) > 0)
;

-- add to ref.branded_drug where does not already exist
INSERT INTO ref.branded_drug (	
	description,
	preparation,
	atc_code,
	is_fake,
	fk_data_source,
	external_code,
	external_code_type
) 
SELECT DISTINCT ON (s_bd.external_code) initcap(s_bd.description), lower(preparation), atc_code, FALSE, (SELECT pk from Ref.data_source WHERE position('FreeDiams' in name_short) > 0), s_bd.external_code, 'FreeDiams::CA_HCDPD::DIN'
	FROM staging.branded_drug AS s_bd
	WHERE s_bd.external_code NOT IN
		(
		SELECT r_bd.external_code
		FROM ref.branded_drug AS r_bd
		WHERE r_bd.external_code_type = s_bd.external_code_type
		)
;

-- previous worked:
-- SELECT DISTINCT ON (s_bd.external_code) initcap(s_bd.description), lower(preparation), FALSE, (SELECT pk from Ref.data_source WHERE position('FreeDiams' in name_short) > 0), s_bd.external_code, 'FreeDiams::CA_HCDPD::DIN'
-- 	FROM staging.branded_drug AS s_bd
-- 	WHERE s_bd.external_code NOT IN
-- 		(
-- 		SELECT r_bd.external_code
-- 		FROM ref.branded_drug AS r_bd
-- 		WHERE r_bd.fk_data_source IN (
-- 			SELECT pk from Ref.data_source WHERE position('FreeDiams' in name_short) > 0
-- 			)
-- 		)
-- ;


-- alternative to test... perhaps computationally faster:
-- SELECT DISTINCT ON (s_bd.external_code) initcap(s_bd.description), lower(preparation), FALSE, (SELECT pk from Ref.data_source WHERE position('FreeDiams' in name_short) > 0), s_bd.external_code, 'FreeDiams::CA_HCDPD::DIN'
-- 	FROM staging.branded_drug AS s_bd
-- 	WHERE NOT EXISTS
-- 		(
-- 		SELECT s_bd.external_code
-- 		FROM ref.branded_drug AS r_bd
-- 		WHERE r_bd.fk_data_source IN (
-- 			SELECT pk from Ref.data_source WHERE position('FreeDiams' in name_short) > 0
-- 			)
-- 		);


-- ===========================================================
-- add to ref.consumable_substance
-- where does not already exist, and provided amounts are >= 0
-- ===========================================================

INSERT INTO ref.consumable_substance (
	description,
	atc_code,
	amount,
	unit
)
SELECT DISTINCT ON (description, amount, unit)
		lower(description),
		atc_code,
		amount,
		lower(unit)
	FROM
		staging.consumable_substance s_cs
	WHERE
		NOT EXISTS (
			SELECT 1
			FROM
				ref.consumable_substance r_cs
			WHERE
				UPPER(r_cs.description) = UPPER(s_cs.description)
					AND
				r_cs.amount = s_cs.amount
					AND
				UPPER(r_cs.unit) = UPPER(s_cs.unit)
			LIMIT 1
		)
		AND s_cs.amount >= 0
;

-- ==================================================================
-- create links ref.branded_drug.pk <--> ref.consumable_substance.pk
-- ==================================================================

-- create ~ 21,500 links
INSERT INTO ref.lnk_substance2brand (
	fk_brand,
	fk_substance
)
SELECT DISTINCT r_bd.pk, r_cs.pk
FROM ref.branded_drug r_bd
  INNER JOIN staging.branded_drug s_bd
  ON (r_bd.external_code = s_bd.external_code AND r_bd.fk_data_source = s_bd.fk_data_source)
   INNER JOIN staging.consumable_substance s_cs
   ON s_bd.drug_code = s_cs.drug_code
     INNER JOIN ref.consumable_substance r_cs
     ON (
         UPPER(s_cs.description) = UPPER(r_cs.description)
         AND s_cs.amount = r_cs.amount 
         AND UPPER(s_cs.unit) = UPPER(r_cs.unit)
         )
	WHERE
		NOT EXISTS (
			SELECT 1
			FROM
				ref.lnk_substance2brand r_ls
			WHERE
				r_bd.pk = r_ls.fk_brand
					AND
				r_cs.pk = r_ls.fk_substance
			LIMIT 1
		)
;

-- ===================================================
-- VACCINE import & creation
-- ===================================================
-- working from staging vaccines, where the branded drug is not yet represented
-- among 'production' vaccines.
-- Error-free insertion depends on
-- 1) matching branded substance to route by which it is administered
--   e.g. intramuscular, intradermal
--    which must already exist in clin.vacc_route
-- 2) matching to at least one indication-per-ingredient
-- Owing to foreign key restraints, these require to be wrapped inside a transaction
-- Also, short of writing a function, the indications easier to stage than directly insert.
-- Result is
-- ~ 72 vaccines, 68 unique products ~ (17 live) of which 4 have 2 routes (IM, SC)
-- (these address ~ 283 indications).
-- 145 indications

-- ===================================================
-- Map ingredients to indications
-- ===================================================
-- For the 'vaccine import' transaction, we need two parts.
-- The first part is new branded vaccines --> into clin.vaccine
-- The second part is more-laborious:
-- ... needs one-to-many mappings of ingredients to indications
-- ... e.g. some single 'ingredients' immunize against e.g. meningococcal, diphtheria
-- ... whereas some (pertactin) are non-specific adjuvants
-- then will need to insert not-yet-existing link pairs
-- ... {vaccine pk, indication}


-- populate staging table with mapped indications
-- ~ 476 records (108 distinct ingredient codes)
--  note: e.g. a 23-valent vaccine aganist pneumonococcus only constitutes one distinct indication


INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('cholera' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('cholera' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('cholera' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('diphtheria' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('diphtheria' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('diphtheria' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('haemophilus' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('haemophilus influenzae' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('haemophilus' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('hepatitis a' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('hepatitis a' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('hepatitis a' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('hepatitis b' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('hepatitis b' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('hepatitis b' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('papillomavirus' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('papillomavirus' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('papillomavirus' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('h1n1' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('h1n1' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('h1n1' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('h3n2' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('h3n2' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('h3n2' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('influenza' in lower(s_i.description)) > 0 AND position('haemophilus' in lower(s_i.description)) = 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('influenza (seasonal' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('influenza' in lower(s_i.description)) > 0 AND position('haemophilus' in lower(s_i.description)) = 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('haemagglutinin-strain b' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('influenza (seasonal' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('haemagglutinin-strain b' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('japanese' in lower(s_i.description)) > 0 AND position('enceph' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('japanese b' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('japanese' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('measles' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('measles' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('measles' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('mening' in lower(s_i.description)) > 0
 	AND position(' gr' in lower(s_i.description)) > 0 AND position('p a' in lower(s_i.description)) > 0
 	THEN
		(SELECT id FROM clin.vacc_indication WHERE position('meningococcus a' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('mening' in lower(s_i.description)) > 0
 	AND position(' gr' in lower(s_i.description)) > 0 AND position('p a' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('mening' in lower(s_i.description)) > 0
 	AND position(' gr' in lower(s_i.description)) > 0 AND position('p c' in lower(s_i.description)) > 0
 	THEN
		(SELECT id FROM clin.vacc_indication WHERE position('meningococcus c' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('mening' in lower(s_i.description)) > 0
 	AND position(' gr' in lower(s_i.description)) > 0 AND position('p c' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('mening' in lower(s_i.description)) > 0
 	AND position(' gr' in lower(s_i.description)) > 0 AND position('p w' in lower(s_i.description)) > 0
 	THEN
		(SELECT id FROM clin.vacc_indication WHERE position('meningococcus w' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('mening' in lower(s_i.description)) > 0
 	AND position(' gr' in lower(s_i.description)) > 0 AND position('p w' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('mening' in lower(s_i.description)) > 0
 	AND position(' gr' in lower(s_i.description)) > 0 AND position('p y' in lower(s_i.description)) > 0
 	THEN
		(SELECT id FROM clin.vacc_indication WHERE position('meningococcus y' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('mening' in lower(s_i.description)) > 0
 	AND position(' gr' in lower(s_i.description)) > 0 AND position('p y' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('mumps' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('mumps' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('mumps' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('pertussis' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('pertussis' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('pertussis' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('pneumococc' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('pneumococcus' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('pneumococc' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('polio' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('polio' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('polio' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('rabies' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('rabies' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('rabies' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('rotavirus' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('rotavirus' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('rotavirus' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('rubella' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('rubella' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('rubella' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('salmonella typhi' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('salmonella typhi' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('salmonella typhi' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('tetanus' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('tetanus' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('tetanus' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('tick-borne' in lower(s_i.description)) > 0 AND position('enceph' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('tick-borne' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('tick-borne' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('bacillus calmette-guerin' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('tuberculosis' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('bacillus calmette-guerin' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('varicella' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('varicella' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('varicella' in lower(s_i.description)) > 0;

INSERT INTO staging.ingred2ind (ingred_code, fk_indication)
SELECT s_i.ingred_code,
	CASE WHEN position('yellow fever' in lower(s_i.description)) > 0 THEN
		(SELECT id FROM clin.vacc_indication WHERE position('yellow fever' in lower(clin.vacc_indication.description)) > 0)
	END
FROM staging.ingred s_i
WHERE position('yellow fever' in lower(s_i.description)) > 0;


-- ===================================================
-- Insert vaccines and then create to-indication links
-- ===================================================
-- NOTE column id_route has been pulled out of the insert
-- it had been represented, in the select, by c_vr.id
-- and
--	INNER JOIN clin.vacc_route c_vr
--	  ON UPPER(c_vr.description) = UPPER(s_v.route)



INSERT INTO clin.vaccine  (
  is_live,
  fk_brand
)
SELECT
	CASE WHEN EXISTS
    	(SELECT 1
    	FROM ref.branded_drug AS r_bd
    	INNER JOIN staging.vaccine s_v
    	   ON s_v.external_code = r_bd.external_code
    	INNER JOIN staging.branded_drug s_bd
    	  ON r_bd.external_code = s_bd.external_code AND r_bd.external_code_type = s_bd.external_code_type
    	INNER JOIN staging.ingred s_i
    	  ON s_bd.drug_code = s_i.drug_code
    	AND position(' live ' in lower(s_i.description)) > 0
    	LIMIT 1
    	) THEN TRUE ELSE NULL
	END,
	r_bd.pk
	FROM ref.branded_drug AS r_bd
	INNER JOIN staging.vaccine s_v
	   ON s_v.external_code = r_bd.external_code
	INNER JOIN staging.branded_drug s_bd
	  ON r_bd.external_code = s_bd.external_code AND r_bd.external_code_type = s_bd.external_code_type
	AND NOT EXISTS
			(SELECT 1
			FROM clin.vaccine c_v
			INNER JOIN ref.branded_drug as r_bd
			  ON c_v.fk_brand = r_bd.pk
			WHERE r_bd.external_code = s_bd.external_code AND r_bd.external_code_type = s_bd.external_code_type
			LIMIT 1
			)
;


-- 	AND NOT EXISTS
-- 			(SELECT 1
-- 			FROM clin.vaccine c_v
-- 			INNER JOIN ref.branded_drug as r_bd
-- 			  ON c_v.fk_brand = r_bd.pk
-- 			WHERE r_bd.fk_data_source = (SELECT pk from Ref.data_source WHERE position('FreeDiams' in name_short) > 0)
-- 			AND s_v.external_code = r_bd.external_code
-- 			LIMIT 1
-- 			)


-- 	  ON r_bd.external_code = s_bd.external_code AND r_bd.fk_data_source = s_bd.fk_data_source
--	WHERE r_bd.fk_data_source = (SELECT pk from Ref.data_source WHERE position('FreeDiams' in name_short) > 0)


-- ======================================================
-- Create the links
-- ======================================================

INSERT INTO clin.lnk_vaccine2inds (
  fk_indication,
  fk_vaccine
)
SELECT DISTINCT s_i2i.fk_indication, c_v.pk
FROM staging.ingred2ind s_i2i
INNER JOIN staging.ingred s_i
  ON s_i.ingred_code = s_i2i.ingred_code
INNER JOIN staging.branded_drug s_bd
  ON s_bd.drug_code = s_i.drug_code
INNER JOIN ref.branded_drug r_bd
  ON r_bd.external_code = s_bd.external_code AND r_bd.external_code_type = s_bd.external_code_type
INNER JOIN clin.vaccine c_v
  ON c_v.fk_brand = r_bd.pk
WHERE s_i2i.fk_indication > 0
AND NOT EXISTS
			(SELECT 1
			FROM clin.lnk_vaccine2inds c_lvi
			WHERE fk_indication = s_i2i.fk_indication
			AND fk_vaccine = c_v.pk
			LIMIT 1
			)
;

commit;

-- =================
-- cleanup and exit
-- =================

\unset ON_ERROR_STOP
DROP TABLE staging.branded_drug CASCADE;
DROP TABLE staging.consumable_substance CASCADE;
DROP TABLE staging.vaccine CASCADE;
DROP TABLE staging.ingred CASCADE;
DROP TABLE staging.ingred2ind CASCADE;
\set ON_ERROR_STOP 1

-- end