/* Examples dimensions */

/* Create and fill DIM_CALENDAR */
/* Create external table with holidays data */
DROP TABLE l0_ext.HOLIDAYS;
CREATE TABLE l0_ext.HOLIDAYS (
	HOLIDAY date, /* Date when holiday stands */
	COUNTRY varchar(10), /* Country for which holidays stands */
	COMMENT varchar(255), /* Comment - can be anything - typically name of the holiday */
	PRIMARY KEY (HOLIDAY, COUNTRY)
);

/*
	Now use file insertHolidaysUS.sql to insert US holidays to table HOLIDAYS (years 2020-2050)
	I generated this file from python code in file getHolidays.py - you can edit file as needed for your'e purposes like changing the country or year period
	
	Python code:
	from datetime import date
	import re
	import holidays

	for i in range(2020, 2051):
    		for date, name in sorted(holidays.US(i).items()):
        		print('insert into l0_ext.HOLIDAYS values(\'' + date.strftime("%Y-%m-%d") + '\', \'US\', \'' + name.replace("\'", "\'\'") + '\');')
    
*/

/* Select from new created table with inserted data */
SELECT *
FROM l0_ext.HOLIDAYS;

/* Create dimension DIM_CALENDAR */
DROP TABLE l2.DIM_CALENDAR;
CREATE TABLE l2.DIM_CALENDAR (
	DIM_CALENDAR_DATE date PRIMARY KEY,
	WEEK_DAY smallint NOT NULL, /* Sunday = 0, Monday = 1, ..., Saturday = 6 */
	WEEK_DAY_DESCR varchar(50) NOT NULL, /* Texts: Sunday, Moday, ..., Saturday */
	WEEK_DAY_DESCR_SHORT varchar(10), /* Short texts: Sun, Mon, ..., Sat */
	WEEK_NUMBER smallint NOT NULL, /* Number of week of year */
	DIM_PERIOD_ID varchar(10) NOT NULL, /* Period in format like '202301' */
	QUARTER_ID varchar(10) NOT NULL, /* Quarter in format like 'Q4 2023' */
	FLAG_WORKING_DAY smallint NOT NULL, /* Flag 1/0 -> 1 = working day (not weekend, not holiday) */
	FLAG_WEEKEND smallint NOT NULL, /* Flag 1/0  -> 1 = weekend (Satruday or Sunday) */
	FLAG_HOLIDAY smallint NOT NULL /* Flag 1/0 -> 1 = holiday */
);

/*
	Insert data to DIM_CALENDAR using SQL recursive to generate calendar and join information about holidays
	This will be load file LOAD_DIM_CALENDAR.sql	
*/
TRUNCATE TABLE l2.DIM_CALENDAR;
INSERT INTO l2.DIM_CALENDAR (DIM_CALENDAR_DATE, WEEK_DAY, WEEK_DAY_DESCR, WEEK_DAY_DESCR_SHORT, WEEK_NUMBER, DIM_PERIOD_ID, QUARTER_ID, FLAG_WORKING_DAY, FLAG_WEEKEND, FLAG_HOLIDAY)
WITH RECURSIVE tree as (
	SELECT '2020-01-01'::date DATE_DAY
	
	UNION
    
	SELECT DATE_DAY + 1
    FROM tree
	WHERE DATE_DAY < '2050-12-31'::date
)
SELECT tree.DATE_DAY AS DIM_CALENDAR_DATE,
	extract(dow FROM tree.DATE_DAY) AS WEEK_DAY,
	CASE
		WHEN extract(dow FROM tree.DATE_DAY) = 0 THEN 'Sunday'
		WHEN extract(dow FROM tree.DATE_DAY) = 1 THEN 'Monday'
		WHEN extract(dow FROM tree.DATE_DAY) = 2 THEN 'Tuesday'
		WHEN extract(dow FROM tree.DATE_DAY) = 3 THEN 'Wednesday'
		WHEN extract(dow FROM tree.DATE_DAY) = 4 THEN 'Thursday'
		WHEN extract(dow FROM tree.DATE_DAY) = 5 THEN 'Friday'
		WHEN extract(dow FROM tree.DATE_DAY) = 6 THEN 'Saturday'
	END AS WEEK_DAY_DESCR,
	CASE
		WHEN extract(dow FROM tree.DATE_DAY) = 0 THEN 'Sun'
		WHEN extract(dow FROM tree.DATE_DAY) = 1 THEN 'Mon'
		WHEN extract(dow FROM tree.DATE_DAY) = 2 THEN 'Tue'
		WHEN extract(dow FROM tree.DATE_DAY) = 3 THEN 'Wed'
		WHEN extract(dow FROM tree.DATE_DAY) = 4 THEN 'Thu'
		WHEN extract(dow FROM tree.DATE_DAY) = 5 THEN 'Fri'
		WHEN extract(dow FROM tree.DATE_DAY) = 6 THEN 'Sat'
	END AS WEEK_DAY_DESCR_SHORT,
	extract(week FROM tree.DATE_DAY) AS WEEK_NUMBER,
	to_char(tree.DATE_DAY::date, 'YYYYMM') AS DIM_PERIOD_ID,
	to_char(tree.DATE_DAY::date, '"Q"Q YYYY') AS QUARTER_ID,
	CASE
		WHEN extract(dow FROM tree.DATE_DAY) IN (0, 6) THEN 0
		WHEN hol.HOLIDAY IS NOT NULL THEN 0
		ELSE 1
	END AS FLAG_WORKING_DAY,
	CASE WHEN extract(dow FROM tree.DATE_DAY) IN (0, 6) THEN 1 ELSE 0 END AS FLAG_WEEEKEND,
	CASE WHEN hol.HOLIDAY IS NOT NULL THEN 1 ELSE 0 END AS FLAG_HOLIDAY
FROM tree
	LEFT JOIN l0_ext.HOLIDAYS hol ON hol.COUNTRY = 'US' AND hol.HOLIDAY = tree.DATE_DAY
-- ORDER BY DIM_CALENDAR_DATE
;

SELECT *
FROM l2.DIM_CALENDAR cal;


/* Create and fill dimension DIM_PERIOD */
/* Create dimension DIM_PERIOD */
DROP TABLE l2.DIM_PERIOD;
CREATE TABLE l2.DIM_PERIOD (
	DIM_PERIOD_ID VARCHAR(10) PRIMARY KEY,
	PERIOD_FIRST_DATE date NOT NULL,
	PERIOD_LAST_DATE date NOT NULL,
	QUARTER_ID VARCHAR(10) NOT NULL,
	CNT_WD int NOT NULL,
	QUARTER_FIRST_DATE date NOT NULL,
	QUARTER_LAST_DATE date NOT NULL,
	CNT_QTD_WD int NOT NULL,
	YEAR_FIRST_DATE date NOT NULL,
	CNT_YTD_WD int NOT NULL
);

/* Insert data to DIM_PERIOD using DIM_CALENDAR as aggregation */
TRUNCATE TABLE l2.DIM_PERIOD;
INSERT INTO l2.DIM_PERIOD (DIM_PERIOD_ID, PERIOD_FIRST_DATE, PERIOD_LAST_DATE, QUARTER_ID, CNT_WD, QUARTER_FIRST_DATE, QUARTER_LAST_DATE, CNT_QTD_WD, YEAR_FIRST_DATE, CNT_YTD_WD)
SELECT cal.DIM_PERIOD_ID,
	min(cal.DIM_CALENDAR_DATE) AS PERIOD_FIRST_DATE, /* First day of month */
	max(cal.DIM_CALENDAR_DATE) AS PERIOD_LAST_DATE, /* Last day of month */
	cal.QUARTER_ID,
	sum(cal.FLAG_WORKING_DAY) AS CNT_WD, /* Count of working day in month - advantage to have boolean values in integer as 1/0 came in handy there */
	date_trunc('quarter', cal.DIM_CALENDAR_DATE)::date AS QUARTER_FIRST_DATE, /* First day of quarter */
	(date_trunc('quarter', cal.DIM_CALENDAR_DATE) + interval '3 months' - interval '1 day')::date AS QUARTER_LAST_DATE /* Last day of quarter */,
	sum(sum(cal.FLAG_WORKING_DAY)) over (partition by cal.QUARTER_ID order by cal.DIM_PERIOD_ID) AS CNT_QTD_WD, /* Count of working days in quarter in Quarter To Date form (cumulative by month) - using windowed function (explained in later capter) */
	date_trunc('year', cal.DIM_CALENDAR_DATE)::date AS YEAR_FIRST_DATE, /* First day of year */
	sum(sum(cal.FLAG_WORKING_DAY)) over (partition by date_trunc('year', cal.DIM_CALENDAR_DATE)::date order by cal.DIM_PERIOD_ID) AS CNT_YTD_WD /* Count of working days in year in Year To Date form (cumulative by month) */
FROM l2.DIM_CALENDAR cal
GROUP BY cal.DIM_PERIOD_ID, cal.QUARTER_ID,
	date_trunc('quarter', cal.DIM_CALENDAR_DATE)::date,
	(date_trunc('quarter', cal.DIM_CALENDAR_DATE) + interval '3 months' - interval '1 day')::date,
	date_trunc('year', cal.DIM_CALENDAR_DATE)::date,
	case when cal.DIM_CALENDAR_DATE < cal.DIM_CALENDAR_DATE then cal.FLAG_WORKING_DAY else 0 end
-- ORDER BY cal.DIM_PERIOD_ID
;

SELECT *
FROM l2.DIM_PERIOD per;