-- Create_Procedure.Reference.usp_PopulateDates.sql

USE [Dates];

/*
EXECUTE [Reference].[usp_PopulateDates] @start_date = '2020-01-01', @end_date = '2024-12-31', @truncate = 1
SELECT x.* FROM [Reference].[Dates] AS x (NOLOCK);
*/

-- if it doesn't exist, create the procedure
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Reference].[usp_PopulateDates]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [Reference].[usp_PopulateDates] AS SELECT 1' 
END
GO

-- always alter the procedure
ALTER PROCEDURE [Reference].[usp_PopulateDates] 
(	
	@start_date datetime = '2020-01-01',
	@end_date	datetime = '2025-12-31',
	@truncate	bit = 1
) 
AS 

BEGIN 

SET NOCOUNT ON;

-- if necessary, truncate the table
IF (@truncate = 1)
BEGIN 
	TRUNCATE TABLE [Reference].[Dates];
END;

-- get current date
DECLARE @CurrentDate date;
DECLARE @CurrentFiscalYear int;

SET @CurrentDate		= getdate();
SET @CurrentFiscalYear	= 
		CASE 
			WHEN datepart(month, @CurrentDate) IN (1,2,3) THEN datepart(year, @CurrentDate)
			WHEN datepart(month, @CurrentDate) IN (4,5,6,7,8,9,10,11,12) THEN datepart(year, @CurrentDate) + 1
		END;

-- setup temp tables
IF object_id( N'tempdb..#temp_tally') IS NOT NULL
	DROP TABLE #temp_tally;
	
-- create and populate the Tally table of dates
SELECT TOP 50000
         IDENTITY(INT,1,1) AS i   
INTO #temp_tally   
FROM Master.dbo.SysColumns sc1,       
     Master.dbo.SysColumns sc2;

---- *** DEVELOPMENT check ***
--SELECT x.* 
--FROM #temp_tally AS x (NOLOCK);

;WITH Calendar_cte (i, CalendarDate)
AS
(
	SELECT i = 1, CalendarDate = @start_date
		UNION ALL
	SELECT i = 2, CalendarDate = @start_date + 1
		UNION ALL 
	SELECT i = i + 2, CalendarDate = @start_date + dateadd(day, i, 1)
	FROM	#temp_tally
)

-- populate the raw data into the [[Reference].Dates] table
INSERT INTO [Reference].[Dates]
	SELECT 
		-- General
		DateKey					= 10000 * datepart(year, c.CalendarDate) + 100 * datepart(month, c.CalendarDate) + 1 * datepart(day, c.CalendarDate),
		DateText				= convert(varchar, c.CalendarDate, 112),
		DateDate				= convert(date, c.CalendarDate),
		DateDatetime			= convert(datetime, c.CalendarDate),

		-- Year
		YearNumber				= datepart(year, c.CalendarDate),

		-- Quarter
		QuarterNumber			= m.QuarterNumber,
		QuarterEnglish			= m.QuarterEnglish,
		QuarterFrench			= m.QuarterFrench,
		YearQuarterNumber		= datepart(year, c.CalendarDate) * 10 + m.QuarterNumber,

		-- Month
		MonthNumber				= m.MonthNumber,
		MonthEnglish			= m.MonthEnglish,
		MonthFrench				= m.MonthFrench,
		MonthShortEnglish		= m.MonthShortEnglish,
		MonthShortFrench		= m.MonthShortFrench,
		MonthInitialEnglish		= m.MonthInitialEnglish,
		MonthInitialFrench		= m.MonthInitialFrench,
		YearMonthNumber			= datepart(year, c.CalendarDate) * 100 + m.MonthNumber,

		-- Day
		DayOfMonthNumber		= datepart(day, c.CalendarDate),
		DayOfWeekNumber			= d.DayOfWeekNumber,
		DayEnglish				= d.DayEnglish,
		DayFrench				= d.DayFrench,
		DayShortEnglish			= d.DayShortEnglish,
		DayShortFrench			= d.DayShortFrench,
		DayInitialEnglish		= d.DayInitialEnglish,
		DayInitialFrench		= d.DayInitialFrench,

		-- Fiscal Year
		FiscalYearNumber = 
			CASE 
				WHEN datepart(month, c.CalendarDate) IN (1,2,3) THEN datepart(year, c.CalendarDate)
				WHEN datepart(month, c.CalendarDate) IN (4,5,6) THEN datepart(year, c.CalendarDate) + 1
				WHEN datepart(month, c.CalendarDate) IN (7,8,9) THEN datepart(year, c.CalendarDate) + 1
				WHEN datepart(month, c.CalendarDate) IN (10,11,12) THEN datepart(year, c.CalendarDate) + 1
			END,
		FiscalYear	= 
			CASE 
				WHEN datepart(mm, c.CalendarDate) IN (1,2,3) THEN convert(varchar(4), datepart(yyyy, c.CalendarDate) - 1) + '-' + convert(varchar(4), datepart(yyyy, c.CalendarDate))
				WHEN datepart(mm, c.CalendarDate) IN (4,5,6) THEN convert(varchar(4), datepart(yyyy, c.CalendarDate)) + '-' + convert(varchar(4), datepart(yyyy, c.CalendarDate) + 1)
				WHEN datepart(mm, c.CalendarDate) IN (7,8,9) THEN convert(varchar(4), datepart(yyyy, c.CalendarDate)) + '-' + convert(varchar(4), datepart(yyyy, c.CalendarDate) + 1)
				WHEN datepart(mm, c.CalendarDate) IN (10,11,12) THEN convert(varchar(4), datepart(yyyy, c.CalendarDate)) + '-' + convert(varchar(4), datepart(yyyy, c.CalendarDate) + 1)
			END,

		-- Fiscal Quarter
		FiscalQuarterNumber		= m.FiscalQuarterNumber,	  
		FiscalQuarterEnglish	= m.FiscalQuarterEnglish,
		FiscalQuarterFrench		= m.FiscalQuarterFrench,
		FiscalYearQuarterNumber	= NULL, -- to be set below

		-- Holiday
		HolidayNameEnglish		= h.HolidayNameEnglish,
		HolidayNameFrench		= h.HolidayNameFrench,

		-- Offset
		YearOffset				= NULL, -- to be set below
		FiscalYearOffset		= NULL, -- to be set below
		QuarterOffset			= NULL, -- to be set below
		MonthOffset				= NULL, -- to be set below
		DayOffset				= NULL, -- to be set below

		-- Flags (fixed)
		IsWeekday				= d.IsWeekday,
		IsWeekendDay			= CASE WHEN d.IsWeekday = 0 THEN 1 ELSE 0 END,
		IsBusinessDay			= d.IsBusinessDay,
		IsHoliday				= CASE WHEN h.HolidayDate IS NOT NULL THEN 1 ELSE 0 END,

		-- Created/Modified
		CreatedBy				= user_name(),
		CreatedOn				= getdate(),
		ModifiedBy				= NULL,
		ModifiedOn				= NULL

FROM
	Calendar_cte AS c
	LEFT JOIN [Reference].[Months] AS m (NOLOCK)
		ON datepart(month, c.CalendarDate) = m.MonthNumber
	LEFT JOIN [Reference].[Days] AS d (NOLOCK)
		ON datepart(weekday, c.CalendarDate) = d.DayOfWeekNumber
	LEFT JOIN [Reference].[Holidays] AS h (NOLOCK)
		ON c.CalendarDate = h.HolidayDate
WHERE
	c.CalendarDate >= @start_date 
	AND c.CalendarDate <= @end_date;

-- update Fiscal Year Quarter Number
UPDATE [Reference].[Dates]  SET 	
	FiscalYearQuarterNumber = x.FiscalYearNumber * 10 + x.FiscalQuarterNumber
FROM
	[Reference].[Dates] AS x;

-- update Business Day
UPDATE [Reference].[Dates]  SET 	
	IsBusinessDay = 0
FROM
	[Reference].[Dates] AS x
WHERE
	x.IsWeekday = 1
	AND x.IsHoliday = 1;

-- update Offsets
UPDATE [Reference].[Dates] SET 	
	YearOffset			= datediff(year, @CurrentDate, x.DateDate),
	FiscalYearOffset	= x.FiscalYearNumber - @CurrentFiscalYear,
	QuarterOffset		= datediff(quarter, @CurrentDate, x.DateDate),
	MonthOffset			= datediff(month, @CurrentDate, x.DateDate),
	DayOffset			= datediff(day, @CurrentDate, x.DateDate)
FROM
	[Reference].[Dates] AS x;

---- *** DEVELOPMENT check ***
--SELECT x.* FROM [Reference].[Dates] AS x (NOLOCK);

-- teardown
IF object_id( N'tempdb..#temp_tally') IS NOT NULL
	DROP TABLE #temp_tally;
	
-- EOF
SET NOCOUNT OFF;

END