-- drop indexes

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_Quotes]') AND name = N'ix_publ_Quotes_itemId')
DROP INDEX [ix_publ_Quotes_itemId] ON [dbo].[publ_Quotes]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_Quotes]') AND name = N'idx_publ_Quotes_QuoteNumber')
DROP INDEX [idx_publ_Quotes_QuoteNumber] ON [dbo].[publ_Quotes]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_Quotes]') AND name = N'idx_publ_Quotes_Date')
DROP INDEX [idx_publ_Quotes_Date] ON [dbo].[publ_Quotes]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_Quotes]') AND name = N'idx_publ_Quotes_createdById')
DROP INDEX [idx_publ_Quotes_createdById] ON [dbo].[publ_Quotes]
GO

-- create columns

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_Quotes' AND COLUMN_NAME = 'CreatedBy')
BEGIN
	ALTER TABLE [dbo].[publ_Quotes] ADD [CreatedBy] [int] NULL
END
GO

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_Quotes' AND COLUMN_NAME = 'CreatedByDisplay')
BEGIN
	ALTER TABLE [dbo].[publ_Quotes] ADD [CreatedByDisplay] [varchar](64) NULL
END
GO

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_Quotes' AND COLUMN_NAME = 'CreatedDate')
BEGIN
	ALTER TABLE [dbo].[publ_Quotes] ADD [CreatedDate] [datetime] NULL
END
GO

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_Quotes' AND COLUMN_NAME = 'LastUpdatedBy')
BEGIN
	ALTER TABLE [dbo].[publ_Quotes] ADD [LastUpdatedBy] [int] NULL
END
GO

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_Quotes' AND COLUMN_NAME = 'LastUpdatedDate')
BEGIN
	ALTER TABLE [dbo].[publ_Quotes] ADD [LastUpdatedDate] [datetime] NULL
END
GO

-- Update new column values

UPDATE [publ_Quotes] SET
	[CreatedBy] = (SELECT TOP 1 si.[createdBy] FROM [pal_SurfaceItem] si WHERE si.[recId] = [itemID])
	,[CreatedDate] = (SELECT TOP 1 si.[dateCreated] FROM [pal_SurfaceItem] si WHERE si.[recId] = [itemID])
	,[LastUpdatedBy] = (SELECT TOP 1 si.[updatedBy] FROM [pal_SurfaceItem] si WHERE si.[recId] = [itemID])
	,[LastUpdatedDate] = (SELECT TOP 1 si.[dateUpdated] FROM [pal_SurfaceItem] si WHERE si.[recId] = [itemID])
WHERE [CreatedBy] IS NULL OR [CreatedDate] IS NULL OR [LastUpdatedBy] IS NULL OR [LastUpdatedDate] IS NULL

UPDATE [publ_Quotes] SET [CreatedByDisplay] = (SELECT TOP 1 u.userDisplay FROM [pal_vUserShared] u WHERE u.recId = [CreatedBy])
WHERE [CreatedByDisplay] IS NULL

-- create indexes

CREATE NONCLUSTERED INDEX [idx_publ_Quotes_createdById] ON [dbo].[publ_Quotes]
(
	[CreatedBy] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_publ_Quotes_Date] ON [dbo].[publ_Quotes]
(
	[None_QuoteInformation_QuoteDate] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [idx_publ_Quotes_QuoteNumber] ON [dbo].[publ_Quotes]
(
	[QuoteInformation_QuoteNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_publ_Quotes_itemId] ON [dbo].[publ_Quotes]
(
	[itemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
