--
-- BA/20111115: CR035489/OTA
--
-- FIXES:
-- BA/20130207: Adding AttentionLinkID, AttentionName, ChannelTypeID, CredentialTypeID to the CustomerOTA Table.
-- LJE/20130201: OTAAgent, OTAMessage, OTAConfiguration added. Please notice, that OTAConfiguration has FK to OTARequestor!
-- BA/20121211: CR050792/Adding HotelCodeIncoming, HotelName, HTNGHeaderActive, Password, PostingURL & 
--              UserName to the CustomerOTA table.
-- BA/20111123: CR036858/Adding CustomerID to CustomerOTA. 
--              Adding Foriegnkeys FK_CustomerOTA_OTARequestor, FK_CustomerOTA_Products & FK_CustomerOTA_CUSTOMER.
-- BA/20111129: CR036858/See the email sheet for details.
--              We need to add CustomerID to the Primary key.
-- BA/20160301: Skipping WebUpdate 8.0 makes this script fail!
--


IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = 'OTAAliases')
CREATE TABLE [dbo].[OTAAliases](
	[PK_ID] [int] NOT NULL,
	[FK_RequestorID] [int] NOT NULL,
	[FK_LookupID] [int] NOT NULL,
	[FK_ReferenceID] [int] NULL,
	[Kind] [int] NOT NULL,
	[Alias] [varchar](100) NOT NULL,
	[SortOrder] [int] NULL,
 CONSTRAINT [PK_OTARoomTypes] PRIMARY KEY CLUSTERED 
(
	[PK_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

IF NOT EXISTS (SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_OTAAliases_OTARequestor')
ALTER TABLE [dbo].[OTAAliases]  WITH NOCHECK ADD  CONSTRAINT [FK_OTAAliases_OTARequestor] FOREIGN KEY([FK_RequestorID])
REFERENCES [dbo].[OTARequestor] ([RequestorID])
GO

IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = 'OTARequestor')
CREATE TABLE [dbo].[OTARequestor](
           [RequestorID] [int] NOT NULL,
           [CompanyName] [char](100) NULL,
           [ID] [char](100) NULL,
CONSTRAINT [PK_OTA] PRIMARY KEY CLUSTERED 
(
           [RequestorID] ASC
) ON [PRIMARY]
) ON [PRIMARY] 

GO

IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = 'CustomerOTA')
CREATE TABLE [dbo].[CustomerOTA](
           [CustomerNumber] [int] NOT NULL,
           [RequestorID] [int] NOT NULL,
           [ChainCode] [varchar](100) NULL,
           [BrandCode] [varchar](100) NULL,
           [HotelCode] [varchar](100) NOT NULL,
           [AvailabilityType] [int] NOT NULL,
           [AvailabilityID] [int] NOT NULL,
           [ProductID] [int] NULL,
CONSTRAINT [PK_CustomerOTA] PRIMARY KEY CLUSTERED 
(
           [CustomerNumber] ASC,
           [RequestorID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO

-- 20111123: CR036858
IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects(NOLOCK) 
    INNER JOIN SysColumns(NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'CustomerID'
    WHERE SysObjects.Name = 'CustomerOTA'
)
ALTER TABLE CustomerOTA ADD CustomerID INT NULL 
GO

IF NOT EXISTS (SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_CustomerOTA_OTARequestor')
ALTER TABLE [dbo].[CustomerOTA]  WITH CHECK ADD  CONSTRAINT [FK_CustomerOTA_OTARequestor] FOREIGN KEY([RequestorID])
REFERENCES [dbo].[OTARequestor] ([RequestorID])
GO


IF NOT EXISTS (SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_CustomerOTA_Products')
ALTER TABLE [dbo].[CustomerOTA]  WITH CHECK ADD  CONSTRAINT [FK_CustomerOTA_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ProductNumber])
GO

IF NOT EXISTS (SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_CustomerOTA_CUSTOMER')
ALTER TABLE [dbo].[CustomerOTA]  WITH CHECK ADD  CONSTRAINT [FK_CustomerOTA_CUSTOMER] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customer] ([CustomerNumber])
GO

-- BA/20111129: CR036858/See the email sheet for details.
--              We need to add CustomerID to the Primary key.
IF NOT EXISTS
(
  SELECT K.TABLE_NAME, K.COLUMN_NAME, K.CONSTRAINT_NAME 
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C 
  JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K 
    ON C.TABLE_NAME = K.TABLE_NAME AND 
       C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG AND 
       C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA AND 
       C.CONSTRAINT_NAME = K.CONSTRAINT_NAME 
  WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY' AND 
        K.TABLE_NAME = 'CustomerOTA' AND
        K.COLUMN_NAME = 'CustomerID'
)
  ALTER TABLE [dbo].[CustomerOTA] DROP CONSTRAINT PK_CustomerOTA
  GO

ALTER TABLE CustomerOTA ALTER COLUMN CustomerID INT NOT NULL 
GO

IF NOT EXISTS (SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'PK_CustomerOTA')
  ALTER TABLE CustomerOTA
  ADD CONSTRAINT PK_CustomerOTA PRIMARY KEY (CustomerNumber, RequestorID, CustomerID)
  GO

-- BA/20121211: CR050792
IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects(NOLOCK) 
    INNER JOIN SysColumns(NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'HotelCodeIncoming'
    WHERE SysObjects.Name = 'CustomerOTA'
)
ALTER TABLE 
  [dbo].[CustomerOTA] 
    ADD [HotelCodeIncoming] VARCHAR(100) NULL,
        [HotelName] VARCHAR(100) NULL, 
        [HTNGHeaderActive] BIT NULL ,
        [Password] VARCHAR(100) NULL, 
        [PostingURL] VARCHAR(300) NULL;
GO

-- RN/20121212: CR050838
IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects(NOLOCK) 
    INNER JOIN SysColumns(NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'Username'
    WHERE SysObjects.Name = 'CustomerOTA'
)
ALTER TABLE 
  [dbo].[CustomerOTA] 
    ADD [Username] VARCHAR(100) NULL;
GO

-- BA/20121227: Adding UpdateRealTime field to CustomerOTA
IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects(NOLOCK) 
    INNER JOIN SysColumns(NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'UpdateRealTime'
    WHERE SysObjects.Name = 'CustomerOTA'
)
ALTER TABLE 
  [dbo].[CustomerOTA] 
    ADD [UpdateRealTime] BIT NULL;
GO

-- BA/20130110: Adding HTNGFromSystemID & HTNGToSystemID to CustomerOTA
IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects(NOLOCK) 
    INNER JOIN SysColumns(NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'HTNGFromSystemID'
    WHERE SysObjects.Name = 'CustomerOTA'
)
ALTER TABLE 
  [dbo].[CustomerOTA] 
ADD [HTNGFromSystemID] VARCHAR(100) NULL,
    [HTNGToSystemID] VARCHAR(100) NULL;
GO

-- LJE/20130201: OTAAgent, OTAMessage, OTAConfiguration added. Please notice, that OTAConfiguration has FK to OTARequestor!
-- OTAAgent.
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OTAAgent]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	CREATE TABLE [dbo].[OTAAgent](
		[OTAAgentId]  int IDENTITY(1000,1) -- Begin with index 1000 for AgentId.
		,[Description] varchar(100) NULL
		,[Enabled] int NULL	
		,CONSTRAINT [PK_OTAAgentId] PRIMARY KEY CLUSTERED 
		  (
			[OTAAgentId] ASC
		  )--WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
		  ON [PRIMARY]
	) ON [PRIMARY]
	GO

-- OTAMessage
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OTAMessage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	CREATE TABLE [dbo].[OTAMessage](
		[OTAMessageId]  int IDENTITY(1,1)
		,[MessageName] varchar(100) NULL
		,[StartDate] datetime NULL
		,[EndDate] datetime NULL
		,[SpanInMinutes] int -- What time span to look ahead?
		,[IntervalInMinutes] int -- Frequency
		,[HotelNumber] int NULL
		,[Enabled] int NULL
		,CONSTRAINT [PK_OTAMessageId] PRIMARY KEY CLUSTERED 
		  (
			[OTAMessageId] ASC
		  )--WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
		  ON [PRIMARY]
	) ON [PRIMARY]
	GO
	

-- OTAConfiguration.
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OTAConfiguration]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	CREATE TABLE [dbo].[OTAConfiguration](
		[OTAConfigurationId]  int IDENTITY(1,1)
		,[BrandCode] varchar(100) NULL
		,[ChainCode] varchar(100) NULL
		,[HotelCode] varchar(100) NULL
		,[AltHotelCode] varchar(100) NULL
		,[MessageId] int NULL
		,[RequestorId] int NULL
		,[AgentId] int NULL
		,[Description] varchar(100) NULL
		,[Enabled] int NULL	
		,CONSTRAINT [PK_OTAConfigurationId] PRIMARY KEY CLUSTERED 
		  (
			 [OTAConfigurationId] ASC
		  )
		  --WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
		  ON [PRIMARY]
		,CONSTRAINT [FK_OTAConfiguration__OTAAgent] FOREIGN KEY (AgentId) REFERENCES OTAAgent(OTAAgentId)
		,CONSTRAINT [FK_OTAConfiguration__OTARequestor] FOREIGN KEY (RequestorId) REFERENCES OTARequestor(RequestorID)
		,CONSTRAINT [FK_OTAConfiguration__OTAMessage] FOREIGN KEY (MessageId) REFERENCES OTAMessage(OTAMessageId)
	) ON [PRIMARY]
	GO

-- BA/20130207: Adding AttentionLinkID, AttentionName, ChannelTypeID, CredentialTypeID to the CustomerOTA Table.
IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects(NOLOCK) 
    INNER JOIN SysColumns(NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'AttentionLinkID'
    WHERE SysObjects.Name = 'CustomerOTA'
)
ALTER TABLE 
  [dbo].[CustomerOTA] 
ADD [AttentionLinkID]      INT NULL,
    [AttentionName]    VARCHAR(50) NULL,
    [ChannelTypeID]    INT NULL,
    [CredentialTypeID] INT NULL;

-- BA/20150928: Adding SortOrder to OTAAliases
IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects(NOLOCK) 
    INNER JOIN SysColumns(NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'SortOrder'
    WHERE SysObjects.Name = 'OTAAliases'
)
ALTER TABLE 
  [dbo].[OTAAliases] 
ADD [SortOrder]      INT NULL;


-- BA/20151026: Adding CommissionKind to CustomerOTA
IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects(NOLOCK) 
    INNER JOIN SysColumns(NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'CommissionKind'
    WHERE SysObjects.Name = 'CustomerOTA'
)
ALTER TABLE 
  [dbo].[CustomerOTA] 
ADD [CommissionKind]      INT NULL;

