/*    ==Scripting Parameters==

    Source Server Version : SQL Server 2017 (14.0.2027)
    Source Database Engine Edition : Microsoft SQL Server Express Edition
    Source Database Engine Type : Standalone SQL Server

    Target Server Version : SQL Server 2012
    Target Database Engine Edition : Microsoft SQL Server Express Edition
    Target Database Engine Type : Standalone SQL Server
*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[quotes_QuoteItemPriceChecks]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[quotes_QuoteItemPriceChecks](
	[recId] [int] IDENTITY(1,1) NOT NULL,
	[QuoteItemRecId] [int] NOT NULL,
	[PreviousQuoteItemId] [int] NOT NULL,
	[QuoteNumber] [varchar](64) NOT NULL,
	[QuoteDate] [datetime] NOT NULL,
	[SellPrice] [decimal](18, 2) NOT NULL,
	[PriceCheckType] [int] NOT NULL,
	[OriginalSellPrice] [decimal](18, 2) NOT NULL,
	[CreatedDate] [datetime2](7) NOT NULL,
	[CreatedDateUtc] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_quotes_QuoteItemPriceCheck] PRIMARY KEY CLUSTERED 
(
	[recId] 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 sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DF_quotes_QuoteItemPriceChecks_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[quotes_QuoteItemPriceChecks] ADD  CONSTRAINT [DF_quotes_QuoteItemPriceChecks_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DF_quotes_QuoteItemPriceChecks_CreatedDateUtc]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[quotes_QuoteItemPriceChecks] ADD  CONSTRAINT [DF_quotes_QuoteItemPriceChecks_CreatedDateUtc]  DEFAULT (getutcdate()) FOR [CreatedDateUtc]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_quotes_QuoteItemPriceChecks_publ_QuoteItems]') AND parent_object_id = OBJECT_ID(N'[dbo].[quotes_QuoteItemPriceChecks]'))
ALTER TABLE [dbo].[quotes_QuoteItemPriceChecks]  WITH CHECK ADD  CONSTRAINT [FK_quotes_QuoteItemPriceChecks_publ_QuoteItems] FOREIGN KEY([QuoteItemRecId])
REFERENCES [dbo].[publ_QuoteItems] ([recId])
ON DELETE CASCADE
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_quotes_QuoteItemPriceChecks_publ_QuoteItems]') AND parent_object_id = OBJECT_ID(N'[dbo].[quotes_QuoteItemPriceChecks]'))
ALTER TABLE [dbo].[quotes_QuoteItemPriceChecks] CHECK CONSTRAINT [FK_quotes_QuoteItemPriceChecks_publ_QuoteItems]
GO

-- UPDATE DATA

INSERT INTO quotes_QuoteItemPriceChecks
	([QuoteItemRecId]
    ,[PreviousQuoteItemId]
    ,[QuoteNumber]
    ,[QuoteDate]
    ,[SellPrice]
    ,[PriceCheckType]
    ,[OriginalSellPrice]
	,[CreatedDate]
	,[CreatedDateUtc])
SELECT 	
	[QuoteItemRecId] = qi.[recId]
	,[PreviousQuoteItemId] = ISNULL((SELECT TOP 1 prev.recId FROM publ_QuoteItems prev WHERE prev.itemID = qi.[PriceCheck_QuoteItemId]), 0)
	,[QuoteNumber] = qi.[PriceCheck_QuoteNumber]
	,[QuoteDate] = qi.[PriceCheck_QuoteDate]
	,[SellPrice] = qi.[PriceCheck_NewSellPrice]
	,[PriceCheckType] = 1 -- PriceCheckType.Quoted
	,[OriginalSellPrice] = qi.[PriceCheck_OldSellPrice]
	,[CreatedDate] = qi.[PriceCheck_CheckDate]
	,[CreatedDateUtc] = DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), qi.[PriceCheck_CheckDate])
FROM publ_QuoteItems qi
WHERE qi.recId NOT IN (SELECT pc.QuoteItemRecId FROM quotes_QuoteItemPriceChecks pc) 
	AND qi.[PriceCheck_QuoteItemId] IS NOT NULL
	AND qi.[PriceCheck_IsChecked] = 1