IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects  WITH (NOLOCK) 
    INNER JOIN SysColumns  WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ReservationStatus'
    WHERE SysObjects.Name = 'MISTable'
)
begin
  IF EXISTS (SELECT name FROM SysIndexes  WITH (NOLOCK) WHERE name = 
            'PK_MISTABLE')
  begin
	ALTER TABLE MISTable
	Drop CONSTRAINT [PK_MISTABLE]
  end

  ALTER TABLE MISTable ADD ReservationStatus SMALLINT NOT NULL DEFAULT -1

  ALTER TABLE MISTable WITH NOCHECK 
  ADD CONSTRAINT [PK_MISTABLE] PRIMARY KEY CLUSTERED 
  (
	[DateStamp] ASC,
	[SegmentNumber] ASC,
	[PointOfSaleNumber] ASC,
	[RoomTypeNumber] ASC,
	[ValueType] ASC,
	[HotelCustomerNumber] ASC,
	[ReservationStatus] ASC
  ) ON [PRIMARY]
 
end
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects  WITH (NOLOCK) 
    INNER JOIN SysColumns  WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'StaffID'
    WHERE SysObjects.Name = 'MISTable'
)
begin
  IF EXISTS (SELECT name FROM SysIndexes  WITH (NOLOCK) WHERE name = 
            'PK_MISTABLE')
  begin
	ALTER TABLE MISTable
	Drop CONSTRAINT [PK_MISTABLE]
  end

  ALTER TABLE MISTable ADD StaffID INT NOT NULL DEFAULT -1
  ALTER TABLE MISTable ADD CampaignCode SMALLINT NOT NULL DEFAULT -1
  ALTER TABLE MISTable ADD ProjectCode SMALLINT NOT NULL DEFAULT -1

  ALTER TABLE MISTable WITH NOCHECK 
  ADD CONSTRAINT [PK_MISTABLE] PRIMARY KEY CLUSTERED 
  (
	[DateStamp] ASC,
	[SegmentNumber] ASC,
	[PointOfSaleNumber] ASC,
	[RoomTypeNumber] ASC,
	[ValueType] ASC,
	[HotelCustomerNumber] ASC,
	[ReservationStatus] ASC,
	[StaffID] ASC,
	[CampaignCode] ASC,
	[ProjectCode] ASC
  ) ON [PRIMARY]
 
end
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects  WITH (NOLOCK) 
    INNER JOIN SysColumns  WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'AccountNumber'
    WHERE SysObjects.Name = 'MISTable'
)
begin
  IF EXISTS (SELECT name FROM SysIndexes  WITH (NOLOCK) WHERE name = 
            'PK_MISTABLE')
  begin
	ALTER TABLE MISTable
	Drop CONSTRAINT [PK_MISTABLE]
  end

  ALTER TABLE MISTable ADD AccountNumber INT NOT NULL DEFAULT -1

  ALTER TABLE MISTable WITH NOCHECK 
  ADD CONSTRAINT [PK_MISTABLE] PRIMARY KEY CLUSTERED 
  (
	[DateStamp] ASC,
	[SegmentNumber] ASC,
	[PointOfSaleNumber] ASC,
	[RoomTypeNumber] ASC,
	[ValueType] ASC,
	[HotelCustomerNumber] ASC,
	[ReservationStatus] ASC,
	[StaffID] ASC,
	[CampaignCode] ASC,
	[ProjectCode] ASC,
	[AccountNumber] ASC
  ) ON [PRIMARY]
 
end
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects  WITH (NOLOCK) 
    INNER JOIN SysColumns  WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'MISData'
    WHERE SysObjects.Name = 'MISTable'
)
begin
  IF EXISTS (SELECT name FROM SysIndexes  WITH (NOLOCK) WHERE name = 
            'PK_MISTABLE')
  begin
	ALTER TABLE MISTable
	Drop CONSTRAINT [PK_MISTABLE]
  end

  ALTER TABLE MISTable ADD MISData INT NOT NULL DEFAULT 1

  ALTER TABLE MISTable WITH NOCHECK 
  ADD CONSTRAINT [PK_MISTABLE] PRIMARY KEY CLUSTERED 
  (
	[DateStamp] ASC,
	[SegmentNumber] ASC,
	[PointOfSaleNumber] ASC,
	[RoomTypeNumber] ASC,
	[ValueType] ASC,
	[HotelCustomerNumber] ASC,
	[ReservationStatus] ASC,
	[StaffID] ASC,
	[CampaignCode] ASC,
	[ProjectCode] ASC,
	[AccountNumber] ASC,
	[MISData] ASC
  ) ON [PRIMARY]
 
end
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects  WITH (NOLOCK) 
    INNER JOIN SysColumns  WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'DepartmentNumber'
    WHERE SysObjects.Name = 'MISTable'
)
begin
  IF EXISTS (SELECT name FROM SysIndexes  WITH (NOLOCK) WHERE name = 
            'PK_MISTABLE')
  begin
	ALTER TABLE MISTable
	Drop CONSTRAINT [PK_MISTABLE]
  end

  ALTER TABLE MISTable ADD DepartmentNumber INT NOT NULL DEFAULT -1

  ALTER TABLE MISTable WITH NOCHECK 
  ADD CONSTRAINT [PK_MISTABLE] PRIMARY KEY CLUSTERED 
  (
	[DateStamp] ASC,
	[SegmentNumber] ASC,
	[PointOfSaleNumber] ASC,
	[RoomTypeNumber] ASC,
	[ValueType] ASC,
	[HotelCustomerNumber] ASC,
	[ReservationStatus] ASC,
	[StaffID] ASC,
	[CampaignCode] ASC,
	[ProjectCode] ASC,
	[AccountNumber] ASC,
	[MISData] ASC,
	[DepartmentNumber] ASC
  ) ON [PRIMARY]
 
end
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects  WITH (NOLOCK) 
    INNER JOIN SysColumns  WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ProductWareLinkNumber'
    WHERE SysObjects.Name = 'MISTable'
)
begin
  IF EXISTS (SELECT name FROM SysIndexes  WITH (NOLOCK) WHERE name = 
            'PK_MISTABLE')
  begin
	ALTER TABLE MISTable
	Drop CONSTRAINT [PK_MISTABLE]
  end

  ALTER TABLE MISTable ADD ProductWareLinkNumber INT NOT NULL DEFAULT -1

  ALTER TABLE MISTable WITH NOCHECK 
  ADD CONSTRAINT [PK_MISTABLE] PRIMARY KEY CLUSTERED 
  (
	[DateStamp] ASC,
	[SegmentNumber] ASC,
	[PointOfSaleNumber] ASC,
	[RoomTypeNumber] ASC,
	[ValueType] ASC,
	[HotelCustomerNumber] ASC,
	[ReservationStatus] ASC,
	[StaffID] ASC,
	[CampaignCode] ASC,
	[ProjectCode] ASC,
	[AccountNumber] ASC,
	[MISData] ASC,
	[DepartmentNumber] ASC,
	[ProductWareLinkNumber] ASC
  ) ON [PRIMARY]
 
end
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects  WITH (NOLOCK) 
    INNER JOIN SysColumns  WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ReferenceNumber'
    WHERE SysObjects.Name = 'MISTable'
)
begin
  IF EXISTS (SELECT name FROM SysIndexes  WITH (NOLOCK) WHERE name = 
            'PK_MISTABLE')
  begin
	ALTER TABLE MISTable
	Drop CONSTRAINT [PK_MISTABLE]
  end

  ALTER TABLE MISTable ADD ReferenceNumber INT NOT NULL DEFAULT -1
  ALTER TABLE MISTable ADD FolioNumber INT NOT NULL DEFAULT -1

  ALTER TABLE MISTable WITH NOCHECK 
  ADD CONSTRAINT [PK_MISTABLE] PRIMARY KEY CLUSTERED 
  (
	[DateStamp] ASC,
	[SegmentNumber] ASC,
	[PointOfSaleNumber] ASC,
	[RoomTypeNumber] ASC,
	[ValueType] ASC,
	[HotelCustomerNumber] ASC,
	[ReservationStatus] ASC,
	[StaffID] ASC,
	[CampaignCode] ASC,
	[ProjectCode] ASC,
	[AccountNumber] ASC,
	[MISData] ASC,
	[DepartmentNumber] ASC,
	[ProductWareLinkNumber] ASC,
	[ReferenceNumber] ASC,
	[FolioNumber] ASC
  ) ON [PRIMARY]
 
end
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects  WITH (NOLOCK) 
    INNER JOIN SysColumns  WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'IsPackage'
    WHERE SysObjects.Name = 'MISTable'
)
begin


  IF EXISTS (SELECT name FROM SysIndexes  WITH (NOLOCK) WHERE name = 
            'PK_MISTABLE')
  begin
	ALTER TABLE MISTable Drop CONSTRAINT [PK_MISTABLE]
  end

  Delete from MISTABLE where MISData = 0
  ALTER TABLE MISTable ADD IsPackage INT NOT NULL DEFAULT 0

  ALTER TABLE MISTable WITH NOCHECK 
  ADD CONSTRAINT [PK_MISTABLE] PRIMARY KEY CLUSTERED 
  (
	[DateStamp] ASC,
	[SegmentNumber] ASC,
	[PointOfSaleNumber] ASC,
	[RoomTypeNumber] ASC,
	[ValueType] ASC,
	[HotelCustomerNumber] ASC,
	[ReservationStatus] ASC,
	[StaffID] ASC,
	[CampaignCode] ASC,
	[ProjectCode] ASC,
	[AccountNumber] ASC,
	[IsPackage] ASC,
	[DepartmentNumber] ASC,
	[ProductWareLinkNumber] ASC,
	[ReferenceNumber] ASC,
	[FolioNumber] ASC
  ) ON [PRIMARY]
 
end
GO

-- Start all over with mistable. Remove primary keys. Mis is going to be detailed. Just like ordrebook.

IF EXISTS (SELECT name FROM SysIndexes  WITH (NOLOCK) WHERE name = 
        'PK_MISTABLE')
begin
ALTER TABLE MISTable
Drop CONSTRAINT [PK_MISTABLE]
end
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects  WITH (NOLOCK) 
    INNER JOIN SysColumns  WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'GuestNumber'
    WHERE SysObjects.Name = 'MISTable'
)
begin
  ALTER TABLE MISTable ADD GuestNumber smallint NULL
end
GO


IF NOT EXISTS (SELECT name FROM SysIndexes  WITH (NOLOCK) WHERE name = 
            'MISTABLE_INDEX')
begin

	CREATE NONCLUSTERED INDEX [MISTABLE_INDEX] ON [dbo].[MISTable] 
	(
		[DateStamp] ASC,
		[SegmentNumber] ASC,
		[PointOfSaleNumber] ASC,
		[RoomTypeNumber] ASC,
		[ValueType] ASC,
		[ReservationStatus] ASC,
		[StaffID] ASC,
		[CampaignCode] ASC,
		[ProjectCode] ASC,
		[AccountNumber] ASC,
		[DepartmentNumber] ASC
	)
end

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects  WITH (NOLOCK) 
    INNER JOIN SysColumns  WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'Room_HallType'
    WHERE SysObjects.Name = 'MISTable'
)
begin
  ALTER TABLE MISTable ADD Room_HallType tinyint NOT NULL default (0)
  ALTER TABLE MISTable ADD CorrectionMoved bit NOT NULL default (0)
  ALTER TABLE MISTable ADD CorrectionDeleted bit NOT NULL default (0)
  ALTER TABLE MISTable ADD Text varchar(100) NULL
  ALTER TABLE MISTable ADD WareGroupNumber int NULL
  
  -- Alter IsPackage column. Change to type Bit.
  	DECLARE @ntable nvarchar(50)
	DECLARE @ncolumn nvarchar(50) 
	DECLARE @nconstraint nvarchar(50)
	SELECT @ntable = 'MISTable', @ncolumn = 'IsPackage'
	SELECT @nconstraint = object_name(cdefault) from syscolumns
	where name = @ncolumn AND id = object_id(@ntable)
	if isnull(@nconstraint, '') <> ''
		exec(' alter table ' + @ntable + ' drop constraint ' + @nconstraint)
	ALTER TABLE MISTable ALTER COLUMN IsPackage bit NOT NULL
	ALTER TABLE MISTable ADD CONSTRAINT DF_MISTable_IsPackage DEFAULT (0) FOR IsPackage
  
end
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects  WITH (NOLOCK) 
    INNER JOIN SysColumns  WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'OwnerReservation'
    WHERE SysObjects.Name = 'MISTable'
)
begin
  ALTER TABLE MISTable ADD OwnerReservation bit NOT NULL default (0)
end
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects  WITH (NOLOCK) 
    INNER JOIN SysColumns  WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ProductNumber'
    WHERE SysObjects.Name = 'MISTable'
)
begin
  ALTER TABLE MISTable ADD ProductNumber int NULL
end
GO