USE [EnVisage]
GO

IF NOT EXISTS(SELECT * FROM sys.columns 
            WHERE Name = N'HolidayId' AND Object_ID = Object_ID(N'Holiday'))
BEGIN
ALTER table [dbo].[Holiday] Add [HolidayId] [uniqueidentifier] NULL
end
go

IF NOT EXISTS(SELECT * FROM sys.columns 
            WHERE Name = N'EffectiveChangeDate' AND Object_ID = Object_ID(N'Holiday'))
BEGIN
ALTER table [dbo].[Holiday] Add [EffectiveChangeDate] [datetime] NULL default CONVERT([datetime],'1950-01-01',(0))
end
go

IF NOT EXISTS(SELECT * FROM sys.columns 
            WHERE Name = N'CreatedAt' AND Object_ID = Object_ID(N'Holiday'))
BEGIN
ALTER table [dbo].[Holiday] Add [CreatedAt] [datetime] NULL default GetDate()
end
go

UPDATE [dbo].[Holiday] SET HolidayId = Id WHERE (HolidayId is NULL)
GO
UPDATE [dbo].[Holiday] SET EffectiveChangeDate = CONVERT([datetime],'1950-01-01',(0)) 
	WHERE EffectiveChangeDate IS NULL
GO
UPDATE [dbo].[Holiday] SET CreatedAt = GetDate() WHERE CreatedAt IS NULL
GO

ALTER table [dbo].[Holiday] alter column [HolidayId] [uniqueidentifier] NOT NULL
GO
ALTER table [dbo].[Holiday] alter column [EffectiveChangeDate] [datetime] NOT NULL
GO
ALTER table [dbo].[Holiday] alter column [CreatedAt] [datetime] NOT NULL
GO

IF NOT EXISTS(SELECT * 
FROM sys.indexes WHERE name='IX_Holiday' AND object_id = OBJECT_ID('Holiday'))
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX [IX_Holiday] ON [dbo].[Holiday]
	(
		[HolidayId] ASC,
		[EffectiveChangeDate] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
GO