-- Create_Table.Reference.Holidays.sql

USE [Dates]
GO

DROP TABLE IF EXISTS [Reference].[Holidays]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Reference].[Holidays]') AND type in (N'U'))
BEGIN
CREATE TABLE [Reference].[Holidays](
	-- General
	[HolidayKey]				int IDENTITY(1,1) NOT NULL,
	[HolidayYear]				int NOT NULL,
	[HolidayDate]				date NOT NULL,
	[HolidayDayOfWeek]			varchar(50) NOT NULL,
	[HolidayNameEnglish]		varchar(50) NOT NULL,
	[HolidayNameFrench]			varchar(50) NOT NULL,
	[Notes]						varchar(50) NULL,

	-- Created/Modified
	[CreatedBy]					varchar(50) NOT NULL,
	[CreatedOn]					datetime NOT NULL,
	[ModifiedBy]				varchar(50) NULL,
	[ModifiedOn]				datetime NULL,
 CONSTRAINT [PK_Reference_Holidays] PRIMARY KEY CLUSTERED 
(
	[HolidayKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO


IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Reference_Holidays_CreatedBy]') AND type = 'D')
BEGIN
ALTER TABLE [Reference].[Holidays] ADD  CONSTRAINT [DF_Reference_Holidays_CreatedBy]  DEFAULT (user_name()) FOR [CreatedBy]
END
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Reference_Holidays_CreatedOn]') AND type = 'D')
BEGIN
ALTER TABLE [Reference].[Holidays] ADD  CONSTRAINT [DF_Reference_Holidays_CreatedOn]  DEFAULT (getdate()) FOR [CreatedOn]
END
GO