/*
DROP TABLE SBX.STG_OPER ;
DROP TABLE SBX.ODS_OPER ;
DROP TABLE SBX.ETL_FILE_LOAD ;
DROP TABLE SBX.DDS_ST_PRODUCT_METRICS ;
DROP TABLE SBX.DDS_HUB_PRODUCT ;
*/

### 0. Create ETL file metadata table

CREATE TABLE IF NOT EXISTS SBX.ETL_FILE_LOAD (
	FILE_ID AUTO_INCREMENT(1, 1, 1) NOT NULL,
	SOURCE VARCHAR(128) NOT NULL,
	FILE_NAME VARCHAR(64) NOT NULL,
	BUSINESS_DT DATE NOT NULL,
	LOAD_TS TIMESTAMP NOT NULL,
	PRIMARY KEY (FILE_ID, SOURCE, FILE_NAME, BUSINESS_DT) ENABLED 
)
ORDER BY SOURCE, FILE_NAME, BUSINESS_DT 
UNSEGMENTED ALL NODES
;

### 1.1. Create STAGE table

CREATE TABLE IF NOT EXISTS SBX.STG_OPER (
	BUSINESS_DT DATE NOT NULL,
	DT DATE NOT NULL,
	PLANT_PRODUCT_ID VARCHAR(64) NOT NULL,
	PLAN NUMERIC(18,5),
	FACT NUMERIC(18,5), 
	PRIMARY KEY (DT, PLANT_PRODUCT_ID)
) 
ORDER BY 
	DT,
	PLANT_PRODUCT_ID 
SEGMENTED BY HASH(DT, PLANT_PRODUCT_ID) ALL NODES
;

### 1.2. Populate file metadata

-- SELECT * FROM SBX.ETL_FILE_LOAD ORDER BY FILE_ID ;
INSERT INTO SBX.ETL_FILE_LOAD (
	SOURCE ,
	FILE_NAME ,
	BUSINESS_DT, 
	LOAD_TS
) 
SELECT	DISTINCT
	'ERP' AS SOURCE ,
	'STG_OPER' AS FILE_NAME ,
	stg.BUSINESS_DT, 
	GETDATE() as LOAD_TS
FROM SBX.STG_OPER stg
	LEFT JOIN SBX.ETL_FILE_LOAD fl
		ON fl.SOURCE = 'ERP'
			AND fl.FILE_NAME = 'STG_OPER'
			AND fl.BUSINESS_DT = stg.BUSINESS_DT			
WHERE fl.FILE_ID IS NULL		
;

### 2.1. Create ODS table

CREATE TABLE IF NOT EXISTS SBX.ODS_OPER (
	FILE_ID INTEGER NOT NULL, 
	LOAD_TS TIMESTAMP NOT NULL, 
	BUSINESS_DT DATE NOT NULL,
	DT DATE NOT NULL,
	PLANT_PRODUCT_ID VARCHAR(64) NOT NULL,
	PLAN NUMERIC(18,5),
	FACT NUMERIC(18,5), 
	PRIMARY KEY (FILE_ID, DT, PLANT_PRODUCT_ID) ENABLED 
)
ORDER BY 
	FILE_ID,
	DT, 
	PLANT_PRODUCT_ID 
SEGMENTED BY HASH(DT, PLANT_PRODUCT_ID) ALL NODES
PARTITION BY DT GROUP BY CALENDAR_HIERARCHY_DAY(DT, 1, 2)
;

### 2.2. Create VIEW to populate ODS 

CREATE OR REPLACE VIEW SBX.V_STG_OPER_ODS_OPER AS
SELECT 
	fl.FILE_ID , 
	fl.LOAD_TS ,
	src.BUSINESS_DT ,
	src.DT , 
	src.PLANT_PRODUCT_ID , 
	src.PLAN , 
	src.FACT
FROM SBX.STG_OPER src
	INNER JOIN ( 
		SELECT 
			FILE_ID , 
			LOAD_TS ,
			SOURCE ,
			FILE_NAME ,
			BUSINESS_DT
		FROM SBX.ETL_FILE_LOAD 
		LIMIT 1 OVER (PARTITION BY SOURCE, FILE_NAME, BUSINESS_DT ORDER BY LOAD_TS DESC)
		) fl
			ON fl.SOURCE = 'ERP'
				AND fl.FILE_NAME = 'STG_OPER'
				AND fl.BUSINESS_DT = src.BUSINESS_DT
	LEFT JOIN SBX.ODS_OPER trg 
		ON fl.FILE_ID = trg.FILE_ID
			AND src.BUSINESS_DT = trg.BUSINESS_DT
			AND src.DT = trg.DT
			AND src.PLANT_PRODUCT_ID = trg.PLANT_PRODUCT_ID
WHERE trg.FILE_ID IS NULL
;

### 2.3. Populate ODS table

-- SELECT FILE_ID, BUSINESS_DT, count(1) FROM SBX.ODS_OPER GROUP BY 1, 2 ;
INSERT INTO SBX.ODS_OPER ( 
	FILE_ID , 
	LOAD_TS ,
	BUSINESS_DT , 
	DT , 
	PLANT_PRODUCT_ID ,  
	PLAN , 
	FACT 
) 
SELECT 
	src.FILE_ID , 
	src.LOAD_TS ,
	src.BUSINESS_DT , 
	src.DT , 
	src.PLANT_PRODUCT_ID , 
	src.PLAN , 
	src.FACT
FROM SBX.V_STG_OPER_ODS_OPER src
;

### 3.1. Create DDS HUB table

CREATE TABLE IF NOT EXISTS SBX.DDS_HUB_PRODUCT (
	HK_PLANT_PRODUCT_ID INTEGER NOT NULL,
	FILE_ID INTEGER NOT NULL, 
	LOAD_TS TIMESTAMP NOT NULL,
	PLANT_PRODUCT_ID VARCHAR(64) NOT NULL,
	PRIMARY KEY (HK_PLANT_PRODUCT_ID) ENABLED
) 
ORDER BY HK_PLANT_PRODUCT_ID
SEGMENTED BY HK_PLANT_PRODUCT_ID ALL NODES
;

### 3.2. Create VIEW to populate HUB table

CREATE OR REPLACE VIEW SBX.V_STG_OPER_DDS_HUB_PRODUCT AS
SELECT DISTINCT
	HASH(src.PLANT_PRODUCT_ID) AS HK_PLANT_PRODUCT_ID ,
	fl.FILE_ID , 
	fl.LOAD_TS , 
	src.PLANT_PRODUCT_ID
FROM SBX.STG_OPER src
	INNER JOIN ( 
		SELECT 
			FILE_ID , 
			LOAD_TS ,
			SOURCE ,
			FILE_NAME ,
			BUSINESS_DT
		FROM SBX.ETL_FILE_LOAD 
		LIMIT 1 OVER (PARTITION BY SOURCE, FILE_NAME, BUSINESS_DT ORDER BY LOAD_TS DESC)
		) fl
			ON fl.SOURCE = 'ERP'
				AND fl.FILE_NAME = 'STG_OPER'
				AND fl.BUSINESS_DT = src.BUSINESS_DT
	LEFT JOIN SBX.DDS_HUB_PRODUCT trg 
		ON HASH(src.PLANT_PRODUCT_ID) = trg.HK_PLANT_PRODUCT_ID		
WHERE trg.HK_PLANT_PRODUCT_ID IS NULL
;

### 3.3. Populate HUB table

INSERT INTO SBX.DDS_HUB_PRODUCT ( 
	HK_PLANT_PRODUCT_ID ,
	FILE_ID , 
	LOAD_TS ,
	PLANT_PRODUCT_ID
) 
SELECT 
	src.HK_PLANT_PRODUCT_ID ,
	src.FILE_ID , 
	src.LOAD_TS ,
	src.PLANT_PRODUCT_ID 
FROM SBX.V_STG_OPER_DDS_HUB_PRODUCT src
;

### 4.1. Create DDS SATELLITE table

CREATE TABLE IF NOT EXISTS SBX.DDS_ST_PRODUCT_METRICS (
	HK_PLANT_PRODUCT_ID INTEGER NOT NULL,
	FILE_ID INTEGER NOT NULL, 
	LOAD_TS TIMESTAMP NOT NULL,
	HASHDIFF INTEGER NOT NULL,
	DT DATE NOT NULL,
	PLAN NUMERIC(18,5),
	FACT NUMERIC(18,5), 
	PRIMARY KEY (HK_PLANT_PRODUCT_ID, HASHDIFF) ENABLED
) 
ORDER BY 
	HK_PLANT_PRODUCT_ID ,
	LOAD_TS
SEGMENTED BY HK_PLANT_PRODUCT_ID ALL NODES
PARTITION BY DT GROUP BY CALENDAR_HIERARCHY_DAY(DT, 1, 2)
;

### 4.2. Create VIEW to populate SATELLITE table

CREATE OR REPLACE VIEW SBX.V_STG_OPER_DDS_ST_PRODUCT_METRICS AS
SELECT
	HASH(src.PLANT_PRODUCT_ID) AS HK_PLANT_PRODUCT_ID ,
	fl.FILE_ID , 
	fl.LOAD_TS ,
	HASH(src.DT, src.PLAN, src.FACT) AS HASHDIFF ,
	src.DT ,
	src.PLAN ,
	src.FACT
FROM SBX.STG_OPER src
	INNER JOIN ( 
		SELECT 
			FILE_ID , 
			LOAD_TS ,
			SOURCE ,
			FILE_NAME ,
			BUSINESS_DT
		FROM SBX.ETL_FILE_LOAD 
		LIMIT 1 OVER (PARTITION BY SOURCE, FILE_NAME, BUSINESS_DT ORDER BY LOAD_TS DESC)
		) fl
			ON fl.SOURCE = 'ERP'
				AND fl.FILE_NAME = 'STG_OPER'
				AND fl.BUSINESS_DT = src.BUSINESS_DT
	LEFT JOIN SBX.DDS_ST_PRODUCT_METRICS trg 
		ON HASH(src.PLANT_PRODUCT_ID) = trg.HK_PLANT_PRODUCT_ID	
			AND HASH(src.DT, src.PLAN, src.FACT) = trg.HASHDIFF
WHERE trg.HK_PLANT_PRODUCT_ID IS NULL
;

### 4.3. Populate SATELLITE table

/* SELECT * FROM (
SELECT
	count(1) OVER (PARTITION BY HK_PLANT_PRODUCT_ID, DT) AS cnt	
	, * 
	
FROM SBX.DDS_ST_PRODUCT_METRICS
) a
WHERE a.cnt > 1
ORDER BY HK_PLANT_PRODUCT_ID, DT ; */

INSERT INTO SBX.DDS_ST_PRODUCT_METRICS ( 
	HK_PLANT_PRODUCT_ID ,
	FILE_ID , 
	LOAD_TS ,
	HASHDIFF ,
	DT ,
	PLAN ,
	FACT
)
SELECT 
	src.HK_PLANT_PRODUCT_ID ,
	src.FILE_ID , 
	src.LOAD_TS ,
	src.HASHDIFF ,
	src.DT ,
	src.PLAN ,
	src.FACT
FROM SBX.V_STG_OPER_DDS_ST_PRODUCT_METRICS src
;

### 5.0. VIEW to get actual data

/* SELECT * FROM OPERSVOD_STG.V_DDS_OPER
ORDER BY PLANT_PRODUCT_ID, DT ; */

CREATE OR REPLACE VIEW OPERSVOD_STG.V_DDS_OPER AS
SELECT 
	PLANT_PRODUCT_ID ,
	DT ,
	PLAN ,
	FACT	
FROM SBX.DDS_HUB_PRODUCT hub
	INNER JOIN SBX.DDS_ST_PRODUCT_METRICS st
		ON hub.HK_PLANT_PRODUCT_ID = st.HK_PLANT_PRODUCT_ID
LIMIT 1 OVER (PARTITION BY PLANT_PRODUCT_ID, DT ORDER BY st.LOAD_TS DESC)
;

### 6.0. Sample MART on actual data

SELECT
	PLANT_PRODUCT_ID ,
	DATE_TRUNC('MONTH', DT)::DATE AS MNTH,
	avg(PLAN) AS AVG_PLAN ,
	avg(FACT) AS AVG_FACT
FROM OPERSVOD_STG.V_DDS_OPER
GROUP BY 
	PLANT_PRODUCT_ID ,
	DATE_TRUNC('MONTH', DT)::DATE
;

### 7.0. Now get new FILE with new data and 2 more fields added: BP, PPR

-- SELECT * FROM SBX.ETL_FILE_LOAD ORDER BY FILE_ID ;
INSERT INTO SBX.ETL_FILE_LOAD (
	SOURCE ,
	FILE_NAME ,
	BUSINESS_DT, 
	LOAD_TS
) 
SELECT	DISTINCT
	'ERP' AS SOURCE ,
	'STG_OPER' AS FILE_NAME ,
	stg.BUSINESS_DT, 
	GETDATE() as LOAD_TS
FROM SBX.STG_OPER stg
	LEFT JOIN SBX.ETL_FILE_LOAD fl
		ON fl.SOURCE = 'ERP'
			AND fl.FILE_NAME = 'STG_OPER'
			AND fl.BUSINESS_DT = stg.BUSINESS_DT			
WHERE fl.FILE_ID IS NULL		
;

### 7.1. Create DDS SATELLITE TABLE FOR NEW ATTRIBUTES

CREATE TABLE IF NOT EXISTS SBX.DDS_ST_PRODUCT_METRICS_NEW (
	HK_PLANT_PRODUCT_ID INTEGER NOT NULL,
	FILE_ID INTEGER NOT NULL, 
	LOAD_TS TIMESTAMP NOT NULL,
	HASHDIFF INTEGER NOT NULL,
	DT DATE NOT NULL,
	BP NUMERIC(18,5),
	PPR NUMERIC(18,5), 
	PRIMARY KEY (HK_PLANT_PRODUCT_ID, HASHDIFF) ENABLED
) 
ORDER BY 
	HK_PLANT_PRODUCT_ID ,
	LOAD_TS
SEGMENTED BY HK_PLANT_PRODUCT_ID ALL NODES
PARTITION BY DT GROUP BY CALENDAR_HIERARCHY_DAY(DT, 1, 2)
;

### 7.2. Create VIEW to populate SATELLITE table

CREATE OR REPLACE VIEW SBX.V_STG_OPER_DDS_ST_PRODUCT_METRICS_NEW AS
SELECT
	HASH(src.PLANT_PRODUCT_ID) AS HK_PLANT_PRODUCT_ID ,
	fl.FILE_ID , 
	fl.LOAD_TS ,
	HASH(src.DT, src.BP, src.PPR) AS HASHDIFF ,
	src.DT ,
	src.BP ,
	src.PPR
FROM SBX.STG_OPER src
	INNER JOIN ( 
		SELECT 
			FILE_ID , 
			LOAD_TS ,
			SOURCE ,
			FILE_NAME ,
			BUSINESS_DT
		FROM SBX.ETL_FILE_LOAD 
		LIMIT 1 OVER (PARTITION BY SOURCE, FILE_NAME, BUSINESS_DT ORDER BY LOAD_TS DESC)
		) fl
			ON fl.SOURCE = 'ERP'
				AND fl.FILE_NAME = 'STG_OPER'
				AND fl.BUSINESS_DT = src.BUSINESS_DT
	LEFT JOIN SBX.DDS_ST_PRODUCT_METRICS_NEW trg 
		ON HASH(src.PLANT_PRODUCT_ID) = trg.HK_PLANT_PRODUCT_ID	
			AND HASH(src.DT, src.BP, src.PPR) = trg.HASHDIFF
WHERE trg.HK_PLANT_PRODUCT_ID IS NULL
;

### 7.3. Populate SATELLITE table

INSERT INTO SBX.DDS_ST_PRODUCT_METRICS_NEW ( 
	HK_PLANT_PRODUCT_ID ,
	FILE_ID , 
	LOAD_TS ,
	HASHDIFF ,
	DT ,
	BP ,
	PPR
)
SELECT 
	src.HK_PLANT_PRODUCT_ID ,
	src.FILE_ID , 
	src.LOAD_TS ,
	src.HASHDIFF ,
	src.DT ,
	src.BP ,
	src.PPR
FROM SBX.V_STG_OPER_DDS_ST_PRODUCT_METRICS_NEW src
;

### 8.0. NEW VIEW to get actual data

/* SELECT * FROM OPERSVOD_STG.V_DDS_OPER_NEW
ORDER BY PLANT_PRODUCT_ID, DT ; */

CREATE OR REPLACE VIEW OPERSVOD_STG.V_DDS_OPER_NEW AS
SELECT 
	hub.PLANT_PRODUCT_ID ,
	st1.DT ,
	st1.PLAN ,
	st1.FACT ,
	st2.BP ,
	st2.PPR
FROM SBX.DDS_HUB_PRODUCT hub
	INNER JOIN SBX.DDS_ST_PRODUCT_METRICS st1
		ON hub.HK_PLANT_PRODUCT_ID = st1.HK_PLANT_PRODUCT_ID
	INNER JOIN SBX.DDS_ST_PRODUCT_METRICS_NEW st2
		ON hub.HK_PLANT_PRODUCT_ID = st2.HK_PLANT_PRODUCT_ID
WHERE st1.DT = st2.DT
LIMIT 1 OVER (PARTITION BY hub.PLANT_PRODUCT_ID, st1.DT ORDER BY st1.LOAD_TS DESC)
;

### 9.0. Sample MART on actual data

SELECT
	PLANT_PRODUCT_ID ,
	DATE_TRUNC('MONTH', DT)::DATE AS MNTH,
	avg(PLAN) AS AVG_PLAN ,
	avg(FACT) AS AVG_FACT ,
	avg(BP) AS AVG_BP ,
	avg(PPR) AS AVG_PPR
FROM OPERSVOD_STG.V_DDS_OPER_NEW
GROUP BY 
	PLANT_PRODUCT_ID ,
	DATE_TRUNC('MONTH', DT)::DATE
;