 
 --12. Load NDC into temporary table from 'product'
 DROP TABLE IF EXISTS main_ndc;
-CREATE UNLOGGED TABLE main_ndc AS SELECT * FROM concept_stage WHERE 1=0;
+CREATE UNLOGGED TABLE main_ndc (
+	concept_name TEXT,
+	long_concept_name TEXT,
+	domain_id TEXT,
+	vocabulary_id TEXT,
+	concept_class_id TEXT,
+	standard_concept TEXT,
+	concept_code TEXT,
+	valid_start_date DATE,
+	valid_end_date DATE,
+	invalid_reason TEXT
+	);
 
 CREATE OR REPLACE VIEW prod --for using INDEX 'idx_f1_product'
 AS

							) AS s2
						)
				SELECT t1.*,
					--aggregated unique DOSAGEFORMNAME
					(
						SELECT string_agg(DOSAGEFORMNAME, ', ' ORDER BY DOSAGEFORMNAME)
						FROM (
							SELECT DISTINCT P.DOSAGEFORMNAME
							FROM prod p
							WHERE p.concept_code = t1.concept_code
							) AS s3
						) AS DOSAGEFORMNAME,
					--aggregated unique ROUTENAME
					(
						SELECT string_agg(ROUTENAME, ', ' ORDER BY ROUTENAME)
						FROM (
							SELECT DISTINCT P.ROUTENAME
							FROM prod p
							WHERE p.concept_code = t1.concept_code
							) AS s4
						) AS ROUTENAME,
					--aggregated unique NONPROPRIETARYNAME
					(
						SELECT string_agg(NONPROPRIETARYNAME, ', ' ORDER BY NONPROPRIETARYNAME)
						FROM (
							SELECT DISTINCT lower(P.NONPROPRIETARYNAME) NONPROPRIETARYNAME
							FROM prod p
							WHERE p.concept_code = t1.concept_code
							ORDER BY NONPROPRIETARYNAME limit 14
							) AS s5
						) AS NONPROPRIETARYNAME,
					--multiple formulations flag
					(
						SELECT count(lower(P.NONPROPRIETARYNAME))
						FROM prod p
						WHERE p.concept_code = t1.concept_code
						HAVING count(DISTINCT lower(P.NONPROPRIETARYNAME)) > 1
						) AS MULTI_NONPROPRIETARYNAME,
					(
						SELECT string_agg(brand_name, ', ' ORDER BY brand_name)
						FROM (
							SELECT DISTINCT CASE 
									WHEN (
											lower(proprietaryname) <> lower(nonproprietaryname)
											OR nonproprietaryname IS NULL
											)
										THEN LOWER(TRIM(CONCAT (

WHERE NOT EXISTS (
		SELECT 1
		FROM concept_stage cs_int
		WHERE LOWER(s.concept_code) = LOWER(cs_int.concept_code)
		);

--7. Add full SPL names into concept_synonym_stage
INSERT INTO concept_synonym_stage (
	synonym_concept_code,
	synonym_vocabulary_id,
	synonym_name,
	language_concept_id
	)
SELECT concept_code AS synonym_concept_code,
	'SPL' AS vocabulary_id,
	TRIM(SUBSTR(CASE 
				WHEN brand_name IS NULL
					THEN TRIM(long_concept_name)
				ELSE CONCAT (
						TRIM(long_concept_name),
						' [',
						brand_name,
						']'
						)
				END, 1, 1000)) AS synonym_name,
	4180186 AS language_concept_id -- English
FROM --get unique and aggregated data from source
	(
	SELECT concept_code,
		CASE 
			WHEN multi_nonproprietaryname IS NULL
				THEN CONCAT (
						nonproprietaryname,
						' ' || aggr_dose,
						' ' || routename,
						' ',
						dosageformname
						)
			ELSE CONCAT (
					'Multiple formulations: ',
					nonproprietaryname,
					' ' || aggr_dose,
					' ' || routename,
					' ',
					dosageformname
					)
			END AS long_concept_name,
		SUBSTR(brand_name, 1, 255) AS brand_name
	FROM (
		WITH t AS (
				SELECT concept_code,
					GetAggrDose(active_numerator_strength, active_ingred_unit) aggr_dose
				FROM (
					SELECT concept_code,
						STRING_AGG(active_numerator_strength, '; ' ORDER BY CONCAT (
								active_numerator_strength,
								active_ingred_unit
								)) AS active_numerator_strength,
						STRING_AGG(active_ingred_unit, '; ' ORDER BY CONCAT (
								active_numerator_strength,
								active_ingred_unit
								)) AS active_ingred_unit
					FROM (
						SELECT DISTINCT GetDistinctDose(active_numerator_strength, active_ingred_unit, 1) AS active_numerator_strength,
							GetDistinctDose(active_numerator_strength, active_ingred_unit, 2) AS active_ingred_unit,
							SUBSTR(productid, devv5.INSTR(productid, '_') + 1) AS concept_code
						FROM sources.product
						) AS s0
					GROUP BY concept_code
					) AS s2
				)
		SELECT t1.*,
			--aggregated unique dosageformname
			(
				SELECT STRING_AGG(dosageformname, ', ' ORDER BY dosageformname)
				FROM (
					SELECT DISTINCT p.dosageformname
					FROM prod p
					WHERE p.concept_code = t1.concept_code
					) AS s3
				) AS dosageformname,
			--aggregated unique routename
			(
				SELECT STRING_AGG(routename, ', ' ORDER BY routename)
				FROM (
					SELECT DISTINCT p.routename
					FROM prod p
					WHERE p.concept_code = t1.concept_code
					) AS s4
				) AS routename,
			--aggregated unique nonproprietaryname
			(
				SELECT STRING_AGG(nonproprietaryname, ', ' ORDER BY nonproprietaryname)
				FROM (
					SELECT DISTINCT LOWER(p.nonproprietaryname) nonproprietaryname
					FROM prod p
					WHERE p.concept_code = t1.concept_code
					ORDER BY nonproprietaryname LIMIT 14
					) AS s5
				) AS nonproprietaryname,
			--multiple formulations flag
			(
				SELECT COUNT(LOWER(P.nonproprietaryname))
				FROM prod p
				WHERE p.concept_code = t1.concept_code
				HAVING COUNT(DISTINCT LOWER(P.nonproprietaryname)) > 1
				) AS multi_nonproprietaryname,
			(
				SELECT STRING_AGG(brand_name, ', ' ORDER BY brand_name)
				FROM (
					SELECT DISTINCT CASE 
							WHEN (
									LOWER(proprietaryname) <> LOWER(nonproprietaryname)
									OR nonproprietaryname IS NULL
									)
								THEN LOWER(TRIM(CONCAT (
												proprietaryname,
												' ',
												proprietarynamesuffix
												)))
							ELSE NULL
							END AS brand_name
					FROM prod p
					WHERE p.concept_code = t1.concept_code
					ORDER BY brand_name LIMIT 49 --brand_name may be too long for concatenation
					) AS s6
				) AS brand_name
		FROM t t1
		) AS s7
	) s;

DROP VIEW prod;

