/* Create l0_erp tables */
/* Create table INVOICE_HEADER from ERP system */
DROP TABLE IF EXISTS l0_erp.INVOICE_HEADER;
CREATE TABLE l0_erp.INVOICE_HEADER (
	INVOICE_ID		VARCHAR(10)	PRIMARY KEY,	/* Invoice id - number in format padded to 10 chars with starting zeroes like '0001245784' */
	ORDER_ID		VARCHAR(10)	NOT NULL,		/* Order Id, relation to order, number in format padded to 10 chars with starting zeroes like '0001245784' */
	INVOICE_DATE	DATE		NOT NULL,
	PAYMENT_DATE	DATE		NOT NULL,		/* Due date of invoice */
	CUSTOMER_ID		VARCHAR(10)	NOT NULL		/* Customer Id, relation to customer, number in format padded to 10 chars with starting zeroes like '000124584' */
);

/* Create table INVOICE_ITEM from ERP system */
DROP TABLE IF EXISTS l0_erp.INVOICE_ITEM;
CREATE TABLE l0_erp.INVOICE_ITEM (
	INVOICE_ID		VARCHAR(10),				/* Invoice id - number in format padded to 10 chars with starting zeroes like '0001245784' */
	PRODUCT_ID		VARCHAR(10),				/* Product id - number in format padded to 10 chars with starting zeroes like '0001245784' */
	AMMOUNT			INT				NOT NULL,	/* Number of pieces */
	PRICE			DECIMAL(25, 5) 	NOT NULL,	/* Price withou VAT */
	PRICE_VAT		DECIMAL(25, 5)	NOT NULL,	/* Price with VAT */
	PRIMARY KEY (INVOICE_ID, PRODUCT_ID)
);



/* Insert example data into created tables */
/* Insert data into INVOICE_ITEM table */
TRUNCATE TABLE l0_erp.INVOICE_ITEM;
INSERT INTO l0_erp.INVOICE_ITEM (INVOICE_ID, PRODUCT_ID, AMMOUNT, PRICE, PRICE_VAT)
VALUES
    ('0001245784', '0001245784', 10, 100.00, 120.00),
    ('0001245784', '0001245785', 5, 50.00, 60.00),
    ('0001245784', '0001245786', 8, 80.00, 96.00),
    ('0001245785', '0001245787', 3, 30.00, 36.00),
    ('0001245785', '0001245788', 15, 150.00, 180.00),
    ('0001245786', '0001245789', 6, 60.00, 72.00),
    ('0001245786', '0001245790', 4, 40.00, 48.00),
    ('0001245787', '0001245791', 7, 70.00, 84.00),
    ('0001245787', '0001245792', 2, 20.00, 24.00),
    ('0001245788', '0001245793', 9, 90.00, 108.00),
    ('0001245788', '0001245794', 4, 40.00, 48.00),
    ('0001245788', '0001245795', 12, 120.00, 144.00),
    ('0001245788', '0001245796', 6, 60.00, 72.00),
    ('0001245789', '0001245798', 3, 30.00, 36.00);

/* Insert data into INVOICE_ITEM table */
TRUNCATE TABLE l0_erp.INVOICE_ITEM;
INSERT INTO l0_erp.INVOICE_ITEM (INVOICE_ID, PRODUCT_ID, AMMOUNT, PRICE, PRICE_VAT)
VALUES
    ('0001245784', '0001245784', 10, 100.00, 120.00),
    ('0001245784', '0001245785', 5, 50.00, 60.00),
    ('0001245784', '0001245786', 8, 80.00, 96.00),
    ('0001245785', '0001245787', 3, 30.00, 36.00),
    ('0001245785', '0001245788', 15, 150.00, 180.00),
    ('0001245786', '0001245789', 6, 60.00, 72.00),
    ('0001245786', '0001245790', 4, 40.00, 48.00),
    ('0001245787', '0001245791', 7, 70.00, 84.00),
    ('0001245787', '0001245792', 2, 20.00, 24.00),
    ('0001245788', '0001245793', 9, 90.00, 108.00),
    ('0001245788', '0001245794', 4, 40.00, 48.00),
    ('0001245788', '0001245795', 12, 120.00, 144.00),
    ('0001245788', '0001245796', 6, 60.00, 72.00),
    ('0001245789', '0001245798', 3, 30.00, 36.00);



/* Create fact table FACT_INVOICE */
DROP TABLE IF EXISTS l2.FACT_INVOICE;
CREATE TABLE l2.FACT_INVOICE (
    FACT_INVOICE_ID	VARCHAR(255)	PRIMARY KEY,
    DIM_PERIOD_ID	VARCHAR(10)		NOT NULL,
    INVOICE_ID		VARCHAR(10)		NOT NULL,
    ORDER_ID		VARCHAR(10)		NOT NULL,
    DIM_CUSTOMER_ID	VARCHAR(10)		NOT NULL,
    DIM_PRODUCT_ID	VARCHAR(10)		NOT NULL,
    INVOICE_DATE	DATE			NOT NULL,
    PAYMENT_DATE	DATE			NOT NULL,
    AMMOUNT			INT				NOT NULL,
    PRICE			DECIMAL(25, 5)	NOT NULL,
    PRICE_VAT		DECIMAL(25, 5)	NOT NULL
);



/* Load data into table FACT_INVOICE for specified PERIOD by PARAM - manually/generated by script or framework/using procedure */
/* Delete data for specified period in PARAM */
DELETE
FROM l2.FACT_INVOICE src
WHERE src.DIM_PERIOD_ID = '202310' /* PARAM - param period for which load of fact table has been laoded */

/* Load new data for specified period in PARAM */
INSERT INTO l2.FACT_INVOICE (FACT_INVOICE_ID, DIM_PERIOD_ID, INVOICE_ID, ORDER_ID, DIM_CUSTOMER_ID, DIM_PRODUCT_ID, INVOICE_DATE, PAYMENT_DATE, AMMOUNT, PRICE, PRICE_VAT)
SELECT concat(removeLeadingZeroes(hdr.INVOICE_ID), '.', removeLeadingZeroes(itm.PRODUCT_ID)) AS FACT_INVOICE_ID,
	periodFromDate(hdr.INVOICE_DATE) AS DIM_PERIOD_ID,
	removeLeadingZeroes(hdr.INVOICE_ID) AS INVOICE_ID,
	removeLeadingZeroes(hdr.ORDER_ID) AS ORDER_ID,
	removeLeadingZeroes(hdr.CUSTOMER_ID) AS DIM_CUSTOMER_ID,
	removeLeadingZeroes(itm.PRODUCT_ID) AS DIM_PRODUCT_ID,
	hdr.INVOICE_DATE, hdr.PAYMENT_DATE,
	itm.AMMOUNT, itm.PRICE, itm.PRICE_VAT
FROM l0_erp.INVOICE_HEADER hdr
	LEFT JOIN l0_erp.INVOICE_ITEM itm ON itm.INVOICE_ID = hdr.INVOICE_ID
WHERE periodFromDate(hdr.INVOICE_DATE) = '202310' /* PARAM - param period for which load of fact table has been laoded */