/*
  Created by Kent Johansen
  February 2016
  Initialice CustomerOTAAction table.
  ActionGroup: TOtaActionType. 0=OtaATReservation. 1=OtaATRate. 2=OtaATInventory
  ActionType: 
    OtaATReservation: OtaResActionBook, OtaResActionCancel, OtaResActionReBook, OtaResActionReinstate, OtaResActionNoShow,
      OtaResActionModify, OtaResActionCheckIn, OtaResActionCheckOut
*/
SET NOCOUNT ON

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[CustomerOTAAction]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[CustomerOTAAction](
  	[FKCustomerNumber] [int] NOT NULL,
	[FKRequestorId] [int] NOT NULL,
	[FKCustomerId] [int] NOT NULL,
	[ActionGroup] [int] NOT NULL,
	[ActionType] [int] NOT NULL,
   CONSTRAINT [PK_CustomerOTAAction] PRIMARY KEY CLUSTERED 
  (
	[FKCustomerNumber] ASC,
	[FKRequestorId] ASC,
	[FKCustomerId] ASC,
	[ActionGroup] ASC,
	[ActionType] ASC
  )
  )

  ALTER TABLE [dbo].[CustomerOTAAction]  WITH NOCHECK ADD  CONSTRAINT [FK_CustomerOTAAction_Customer] FOREIGN KEY([FKCustomerNumber])
  REFERENCES [dbo].[Customer] ([CustomerNumber])

  ALTER TABLE [dbo].[CustomerOTAAction] CHECK CONSTRAINT [FK_CustomerOTAAction_Customer]

  ALTER TABLE [dbo].[CustomerOTAAction]  WITH NOCHECK ADD  CONSTRAINT [FK_CustomerOTAAction_CustomerCustomerId] FOREIGN KEY([FKCustomerId])
  REFERENCES [dbo].[Customer] ([CustomerNumber])

  ALTER TABLE [dbo].[CustomerOTAAction] CHECK CONSTRAINT [FK_CustomerOTAAction_CustomerCustomerId]

  ALTER TABLE [dbo].[CustomerOTAAction]  WITH NOCHECK ADD  CONSTRAINT [FK_CustomerOTAAction_OTARequestor] FOREIGN KEY([FKRequestorId])
  REFERENCES [dbo].[OTARequestor] ([RequestorID])

  ALTER TABLE [dbo].[CustomerOTAAction] CHECK CONSTRAINT [FK_CustomerOTAAction_OTARequestor]
 
  DECLARE @CustomerNumber INT
  DECLARE @RequestorId INT
  DECLARE @CustomerId INT

  DECLARE db_cur CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
    SELECT CustomerNumber, RequestorId, CustomerId
    FROM CustomerOTA WITH (NOLOCK)
  OPEN db_cur

  FETCH NEXT FROM db_cur INTO @CustomerNumber, @RequestorId, @CustomerId

  WHILE @@FETCH_STATUS = 0
  BEGIN
    INSERT INTO CustomerOTAAction VALUES (@CustomerNumber, @RequestorId, @CustomerId, 0, 0)
    INSERT INTO CustomerOTAAction VALUES (@CustomerNumber, @RequestorId, @CustomerId, 0, 1)
    INSERT INTO CustomerOTAAction VALUES (@CustomerNumber, @RequestorId, @CustomerId, 0, 2)
    INSERT INTO CustomerOTAAction VALUES (@CustomerNumber, @RequestorId, @CustomerId, 0, 3)
    INSERT INTO CustomerOTAAction VALUES (@CustomerNumber, @RequestorId, @CustomerId, 0, 4)
    INSERT INTO CustomerOTAAction VALUES (@CustomerNumber, @RequestorId, @CustomerId, 0, 5)

    INSERT INTO CustomerOTAAction VALUES (@CustomerNumber, @RequestorId, @CustomerId, 2, 0)
    INSERT INTO CustomerOTAAction VALUES (@CustomerNumber, @RequestorId, @CustomerId, 2, 1)

    INSERT INTO CustomerOTAAction VALUES (@CustomerNumber, @RequestorId, @CustomerId, 3, 0)
    INSERT INTO CustomerOTAAction VALUES (@CustomerNumber, @RequestorId, @CustomerId, 3, 1)

    FETCH NEXT FROM db_cur INTO @CustomerNumber, @RequestorId, @CustomerId
  END

  CLOSE db_cur
  DEALLOCATE db_cur
END