-- Columns

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE
	TABLE_NAME = 'publ_QuoteItems' AND
	COLUMN_NAME = 'PriceCheck_IsChecked')
BEGIN
	ALTER TABLE [dbo].[publ_QuoteItems] ADD [PriceCheck_IsChecked] bit null
END
GO

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE
	TABLE_NAME = 'publ_QuoteItems' AND
	COLUMN_NAME = 'PriceCheck_CheckDate')
BEGIN
	ALTER TABLE [dbo].[publ_QuoteItems] ADD [PriceCheck_CheckDate] datetime2 null
END
GO

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

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

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

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE
	TABLE_NAME = 'publ_QuoteItems' AND
	COLUMN_NAME = 'PriceCheck_OldSellPrice')
BEGIN
	ALTER TABLE [dbo].[publ_QuoteItems] ADD [PriceCheck_OldSellPrice] decimal(18,2) null
END
GO

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE
	TABLE_NAME = 'publ_QuoteItems' AND
	COLUMN_NAME = 'PriceCheck_NewSellPrice')
BEGIN
	ALTER TABLE [dbo].[publ_QuoteItems] ADD [PriceCheck_NewSellPrice] decimal(18,2) null
END
GO

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE
	TABLE_NAME = 'publ_QuoteItems' AND
	COLUMN_NAME = 'CatList_CostPrice')
BEGIN
	ALTER TABLE [dbo].[publ_QuoteItems] ADD [CatList_CostPrice] decimal(18,2) null
END
GO

-- Data Migrate

update publ_QuoteItems set
	CatList_CostPrice = (select top 1 p.CatListCostPrice from quotes_QuoteItemsPriceCheck p where p.QuoteItemId = recId)
	,PriceCheck_IsChecked =  isnull((select 1 from quotes_QuoteItemsPriceCheck p where p.QuoteItemId = recId and p.CheckDate is not null), 0)
	,PriceCheck_NewSellPrice = (select top 1 p.NewSellPrice from quotes_QuoteItemsPriceCheck p where p.QuoteItemId = recId)
	,PriceCheck_OldSellPrice = (select top 1 p.OldSellPrice from quotes_QuoteItemsPriceCheck p where p.QuoteItemId = recId)
	,PriceCheck_QuoteDate = (select top 1 p.QuoteDate from quotes_QuoteItemsPriceCheck p where p.QuoteItemId = recId)
	,PriceCheck_QuoteItemId  = (select top 1 p.ReferenceQuoteItemId from quotes_QuoteItemsPriceCheck p where p.QuoteItemId = recId)
	,PriceCheck_QuoteNumber = (select top 1 p.QuoteNumber from quotes_QuoteItemsPriceCheck p where p.QuoteItemId = recId)
	,PriceCheck_CheckDate = ((select top 1 p.CheckDate from quotes_QuoteItemsPriceCheck p where p.QuoteItemId = recId))