IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ReferenceNumber'
    AND SysObjects.Name = 'CustomerOrdreBook'

)
begin

  -- Delete hole customordrebook and remove primary keys
  delete from customerOrdrebook 
  where ReadOnly = 0


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

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 = 'ReservationStatus'
    AND SysObjects.Name = 'CustomerOrdreBook'
)
  ALTER TABLE CustomerOrdreBook ADD ReservationStatus int NOT NULL DEFAULT -1
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'
    AND SysObjects.Name = 'CustomerOrdreBook'
)
  ALTER TABLE CustomerOrdreBook ADD ReferenceNumber int NULL
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'FolioNumber'
    AND SysObjects.Name = 'CustomerOrdreBook'
)
  ALTER TABLE CustomerOrdreBook ADD FolioNumber int NULL
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'InvoiceNumber'
    AND SysObjects.Name = 'CustomerOrdreBook'
)
  ALTER TABLE CustomerOrdreBook ADD InvoiceNumber int NULL
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'Correction'
    AND SysObjects.Name = 'CustomerOrdreBook'
)
  ALTER TABLE CustomerOrdreBook ADD Correction bit NULL
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'WarePayment'
    AND SysObjects.Name = 'CustomerOrdreBook'
)
  ALTER TABLE CustomerOrdreBook ADD WarePayment smallint NULL
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'WareType'
    AND SysObjects.Name = 'CustomerOrdreBook'
)
  ALTER TABLE CustomerOrdreBook ADD WareType smallint NULL
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ContactpersonCustomerNumber'
    AND SysObjects.Name = 'CustomerOrdreBook'
)
  ALTER TABLE CustomerOrdreBook ADD ContactpersonCustomerNumber int NULL
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'
    AND SysObjects.Name = 'CustomerOrdreBook'
)
  ALTER TABLE CustomerOrdreBook ADD GuestNumber smallint NULL
GO

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

	CREATE NONCLUSTERED INDEX [IX_CustomerOrdreBook] ON [dbo].[CustomerOrdreBook] 
	(
		[CustomerNumber] DESC,
		[ContactpersonCustomerNumber] DESC,
		[SegmentNumber] ASC,
		[DateForValue] ASC,
		[HotelCustomerNumber] ASC,
		[ReservationStatus] ASC,
		[ReadOnly] ASC
	)
end
GO