-- vd20130813: New table for some Best western stuff on customer

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WITH (NOLOCK) 
               WHERE 
                 id = object_id(N'[dbo].[BestWestern]') AND 
                 OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN

  CREATE TABLE BestWestern(
    BWR_ID INTEGER IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    BWR_CustomerID VARCHAR(20),	
    BWR_CreationDate DATETIME DEFAULT GETDATE() NOT NULL,
    BWR_UpdateDate DATETIME DEFAULT GETDATE() NOT NULL,
    BWR_BirthDate DATETIME,
    BWR_PointsExpiryDate DATETIME,	
    BWR_Status TINYINT DEFAULT 4 NOT NULL,
    BWR_Tier VARCHAR(50),
    BWR_Program VARCHAR(50),
    BWR_Points INTEGER DEFAULT 0 NOT NULL,
    BWR_NightsToNextTier SMALLINT,
    BWR_NightsToMaintainTier SMALLINT)

  ALTER TABLE Customer ADD BestWestern INTEGER 
  ALTER TABLE ReservationAddress ADD FK_BestWestern INTEGER
  
  ALTER TABLE Customer ADD CONSTRAINT FK_Customer_BestWesternFK 
    FOREIGN KEY (BestWestern) 
    REFERENCES BestWestern (BWR_ID)
    
  ALTER TABLE ReservationAddress ADD CONSTRAINT FK_ReservationAddress_BestWesternFK 
    FOREIGN KEY (FK_BestWestern) 
    REFERENCES BestWestern (BWR_ID)

END
