--
-- BA/20110407: CR021535 & RQ787/Campaign code and Project codes
--
-- FIXES:
-- BA/2011-05-24: CR024180/Fixing a potential Lock Request Timeout. There's no need
--                to always add the constraints if they already exists.
-- BA/2011-04-12: Fixing incompatibility with SQL Server 2000

-- Campaign codes
IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = 'CampaignCodes')
  CREATE TABLE [dbo].[CampaignCodes](
                               [PK_CampaignCode] [smallint] NOT NULL,
                               [Text] [varchar](50) NOT NULL,
                               [Period] [int] NOT NULL,
                               [FromDate] [datetime] NULL,
                               [ToDate] [datetime] NULL,
   CONSTRAINT [PK_CampaignCodes] PRIMARY KEY CLUSTERED 
  (
                               [PK_CampaignCode] 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

-- Project codes
IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = 'ProjectCodes')
  CREATE TABLE [dbo].[ProjectCodes](
                               [PK_ProjectCode] [smallint] NOT NULL,
                               [Name] [varchar](50) NOT NULL,
                               [Number] [varchar](50) NOT NULL,
                               [Color] [int] NOT NULL,
                               [Period] [int] NOT NULL,
                               [FromDate] [datetime] NULL,
                               [ToDate] [datetime] NULL,
   CONSTRAINT [PK_ProjectCodes] PRIMARY KEY CLUSTERED 
  (
                               [PK_ProjectCode] 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

-- RESERVATIONS

-- Delete unused constraint
IF EXISTS (SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_RESERVATIONS_CancellationCodes')
  ALTER TABLE Reservations DROP CONSTRAINT FK_RESERVATIONS_CancellationCodes

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects(NOLOCK) 
    INNER JOIN SysColumns(NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'FK_CampaignCode'
    WHERE SysObjects.Name = 'Reservations'
)
ALTER TABLE Reservations ADD FK_CampaignCode SMALLINT NULL 
GO

IF NOT EXISTS (SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_RESERVATIONS_CampaignCodes')
  ALTER TABLE [dbo].[RESERVATIONS]  WITH CHECK ADD  CONSTRAINT [FK_RESERVATIONS_CampaignCodes] FOREIGN KEY([FK_CampaignCode])
  REFERENCES [dbo].[CampaignCodes] ([PK_CampaignCode])
  GO

--ALTER TABLE [dbo].[RESERVATIONS] CHECK CONSTRAINT [FK_RESERVATIONS_CampaignCodes]
--GO

-- RESERVATIONMAIN
IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects(NOLOCK) 
    INNER JOIN SysColumns(NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'FK_ProjectCode'
    WHERE SysObjects.Name = 'ReservationMain'
)
ALTER TABLE ReservationMain ADD FK_ProjectCode SMALLINT NULL 
GO

IF NOT EXISTS (SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_RESERVATIONMAIN_ProjectCodes')
  ALTER TABLE [dbo].[RESERVATIONMAIN]  WITH CHECK ADD  CONSTRAINT [FK_RESERVATIONMAIN_ProjectCodes] FOREIGN KEY([FK_ProjectCode])
 REFERENCES [dbo].[ProjectCodes] ([PK_ProjectCode])
  GO

--ALTER TABLE [dbo].[RESERVATIONMAIN] CHECK CONSTRAINT [FK_RESERVATIONMAIN_ProjectCodes]
--GO

-- BA/20140130
IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = 'CampaignCodesProducts')
  CREATE TABLE [dbo].[CampaignCodesProducts](
	  [PK_CampaignCode] [smallint] NOT NULL,
	  [FK_HotelCustomerNumber] [int] NOT NULL,
	  [FK_ProductNumber] [int] NOT NULL,
	  [Enabled] [bit] NOT NULL,
	  [Dummy] [float] NOT NULL,
   CONSTRAINT [PK_CampaignCodesHotelProductsXTab] PRIMARY KEY CLUSTERED 
  (
	  [PK_CampaignCode] ASC,
	  [FK_HotelCustomerNumber] ASC,
	  [FK_ProductNumber] 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_CampaignCodesProducts_Products')
  ALTER TABLE [dbo].[CampaignCodesProducts]  WITH NOCHECK ADD  CONSTRAINT [FK_CampaignCodesProducts_Products] FOREIGN KEY([FK_ProductNumber])
  REFERENCES [dbo].[Products] ([ProductNumber])
  GO

--  ALTER TABLE [dbo].[CampaignCodesProducts] CHECK CONSTRAINT [FK_CampaignCodesProducts_Products]
--  GO


