---- Insert Script for Sundays and Holidays into tbEntityTime and tbEntityTimeException  ----
print 'Loading Sundays and Holidays into tbEntityTime and tbEntityTimeException'

IF DB_NAME() = 'Chick-fil-A' OR DB_NAME() LIKE '%CFA%'
BEGIN

	SET XACT_ABORT ON
	
	-- Create a temp table of Entity IDs that are stores.
	CREATE TABLE #Stores(EntityId bigint, Country varchar(50))
	
	INSERT INTO #Stores (EntityId, Country)
		SELECT EntityId, Country
		FROM tbEntityType et
			JOIN tbEntity e on e.EntityTypeID = et.EntityTypeID
		WHERE et.Description = 'Store'
	CREATE CLUSTERED INDEX IDX_Stores_Temp on #Stores(EntityId)

	BEGIN TRANSACTION
	
	-- Update existing rows in tbEntityTime that have entries for Sunday but do not have IsClosed checked.
	UPDATE dbo.tbEntityTime
	SET IsClosed = 1
	WHERE DayOfWeek = 1 AND IsClosed <> 1
	
	-- SELECT * FROM dbo.tbEntityTime

	-- Add Christmas to the tbEntityTimeException table for each entity for the next 10 years.
	-- Insert where it doesn't already exist.
	DECLARE @christmasDay DateTime = '2013-12-25'
	DECLARE @christmasLoopCount int = 0
	
	WHILE (@christmasLoopCount < 10)
	BEGIN
		Insert Into dbo.tbEntityTimeException (EntityID, CalendarDay, OpenTime, CloseTime, Comment, IsClosed)
			SELECT #Stores.EntityId, @christmasDay, ':', ':', 'Christmas Day', 1
			FROM #Stores
				LEFT JOIN dbo.tbEntityTimeException ete on ete.EntityID = #Stores.EntityId and ete.CalendarDay = @christmasDay
			WHERE ete.EntityID IS NULL

		SET @christmasDay = DATEADD(year, 1, @christmasDay)
		SET @christmasLoopCount = @christmasLoopCount + 1
	END

	-- Add Thanksgiving to the tbEntityTimeException table for each entity for the next 10 years.
	-- Insert where it doesn't already exist. Only applies to USA stores.
	DECLARE @thanksgivingDay DateTime
	DECLARE @thanksgivingLoopCount int = 0
	
	WHILE (@thanksgivingLoopCount < 10)
	BEGIN
		SET @thanksgivingDay = CASE
			WHEN @thanksgivingLoopCount = 0 THEN '2013-11-28' 
			WHEN @thanksgivingLoopCount = 1 THEN '2014-11-27' 
			WHEN @thanksgivingLoopCount = 2 THEN '2015-11-26' 
			WHEN @thanksgivingLoopCount = 3 THEN '2016-11-24' 
			WHEN @thanksgivingLoopCount = 4 THEN '2017-11-23' 
			WHEN @thanksgivingLoopCount = 5 THEN '2018-11-22' 
			WHEN @thanksgivingLoopCount = 6 THEN '2019-11-28' 
			WHEN @thanksgivingLoopCount = 7 THEN '2020-11-26' 
			WHEN @thanksgivingLoopCount = 8 THEN '2021-11-25' 
			WHEN @thanksgivingLoopCount = 9 THEN '2022-11-24'
		END
				
		INSERT INTO dbo.tbEntityTimeException (EntityID, CalendarDay, OpenTime, CloseTime, Comment, IsClosed)
		SELECT #Stores.EntityId, @thanksgivingDay, ':', ':', 'Thanksgiving Day', 1
		FROM #Stores
			LEFT JOIN dbo.tbEntityTimeException ete on ete.EntityID = #Stores.EntityId and ete.CalendarDay = @thanksgivingDay
		WHERE ete.EntityID IS NULL AND #Stores.Country = 'USA'

		SET @thanksgivingLoopCount = @thanksgivingLoopCount + 1
	END
	
	-- SELECT * FROM dbo.tbEntityTimeException

	COMMIT TRANSACTION 

	-- Clean up
	DROP INDEX #Stores.IDX_Stores_Temp
	DROP TABLE #Stores
	
END
GO