SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[MISHistory]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[MISHistory]
  (
    [SystemDate] [date] NOT NULL,
    [DateStamp] [date] NOT NULL,
    [FK_HotelCustomerNumber] [int] NOT NULL,
    [FK_DepartmentNumber] [smallint] NOT NULL,
    [FK_AccountNumber] [int] NOT NULL,
    [FK_SegmentNumber] [smallint] NOT NULL,
    [FK_PointOfSaleNumber] [smallint] NOT NULL,
    [FK_PurposeCodeNumber] [int] NOT NULL,
    [FK_GuestTypeNumber] [smallint] NOT NULL,
    [FK_ProdutNumber] [int] NOT NULL,
    [Room_HallType] [tinyint] NOT NULL,
    [RoomTypeNumber] [int] NOT NULL,
    [ValueType] [tinyint] NOT NULL,
    [Adult] [int] NULL,
    [Children1] [int] NULL,
    [Children2] [int] NULL,
    [NumberOfRooms] [int] NULL,
    [Price] [money] NULL,
    [VATOfPrice] [money] NULL,
    [Package] [money] NULL,
    [VATOfPackage] [money] NULL,
    [FK_WareNumber] [int] NOT NULL,
    [FK_StaffId] [int] NOT NULL,
    [FK_ReferenceNumber] [int] NOT NULL,
    [ActualWareCount] [int] NULL,
    [BookedWareCount] [int] NULL,
    [FK_CampaignCode] [smallint] NOT NULL,
    [FK_ProjectCode] [smallint] NOT NULL,
    [ReservationStatus] [smallint] NOT NULL,
    [FK_CountryNumber] [int] NOT NULL,
    [FK_FolioNumber] [int] NOT NULL,
    [FK_GuestNumber] [smallint] NOT NULL
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[MISHistory]  WITH NOCHECK ADD  CONSTRAINT [FK_MISHistory_Accounts] FOREIGN KEY([FK_AccountNumber])
  REFERENCES [dbo].[Accounts] ([AccountNumber])

  ALTER TABLE [dbo].[MISHistory] NOCHECK CONSTRAINT [FK_MISHistory_Accounts]

  ALTER TABLE [dbo].[MISHistory]  WITH NOCHECK ADD  CONSTRAINT [FK_MISHistory_Customer] FOREIGN KEY([FK_HotelCustomerNumber])
  REFERENCES [dbo].[Customer] ([CustomerNumber])

  ALTER TABLE [dbo].[MISHistory] NOCHECK CONSTRAINT [FK_MISHistory_Customer]

  ALTER TABLE [dbo].[MISHistory]  WITH NOCHECK ADD  CONSTRAINT [FK_MISHistory_Departments] FOREIGN KEY([FK_DepartmentNumber])
  REFERENCES [dbo].[Departments] ([PK_Department])

  ALTER TABLE [dbo].[MISHistory] NOCHECK CONSTRAINT [FK_MISHistory_Departments]

  ALTER TABLE [dbo].[MISHistory]  WITH NOCHECK ADD  CONSTRAINT [FK_MISHistory_GuestTypes] FOREIGN KEY([FK_GuestTypeNumber])
  REFERENCES [dbo].[GuestTypes] ([TypeNumber])

  ALTER TABLE [dbo].[MISHistory] NOCHECK CONSTRAINT [FK_MISHistory_GuestTypes]

  ALTER TABLE [dbo].[MISHistory]  WITH NOCHECK ADD  CONSTRAINT [FK_MISHistory_Personel] FOREIGN KEY([FK_StaffId])
  REFERENCES [dbo].[Personel] ([UserIDNumber])

  ALTER TABLE [dbo].[MISHistory] NOCHECK CONSTRAINT [FK_MISHistory_Personel]

  ALTER TABLE [dbo].[MISHistory]  WITH NOCHECK ADD  CONSTRAINT [FK_MISHistory_PointOfSales] FOREIGN KEY([FK_PointOfSaleNumber])
  REFERENCES [dbo].[PointOfSales] ([Number])

  ALTER TABLE [dbo].[MISHistory] NOCHECK CONSTRAINT [FK_MISHistory_PointOfSales]

  ALTER TABLE [dbo].[MISHistory]  WITH NOCHECK ADD  CONSTRAINT [FK_MISHistory_Products] FOREIGN KEY([FK_ProdutNumber])
  REFERENCES [dbo].[Products] ([ProductNumber])

  ALTER TABLE [dbo].[MISHistory] NOCHECK CONSTRAINT [FK_MISHistory_Products]

  ALTER TABLE [dbo].[MISHistory]  WITH NOCHECK ADD  CONSTRAINT [FK_MISHistory_PurposeCodes] FOREIGN KEY([FK_PurposeCodeNumber])
  REFERENCES [dbo].[PurposeCodes] ([PurposeCodeNumber])

  ALTER TABLE [dbo].[MISHistory] NOCHECK CONSTRAINT [FK_MISHistory_PurposeCodes]

  ALTER TABLE [dbo].[MISHistory]  WITH NOCHECK ADD  CONSTRAINT [FK_MISHistory_ReservationMain] FOREIGN KEY([FK_ReferenceNumber])
  REFERENCES [dbo].[ReservationMain] ([REFERENCENUMBER])

  ALTER TABLE [dbo].[MISHistory] NOCHECK CONSTRAINT [FK_MISHistory_ReservationMain]

  ALTER TABLE [dbo].[MISHistory]  WITH NOCHECK ADD  CONSTRAINT [FK_MISHistory_Segments] FOREIGN KEY([FK_SegmentNumber])
  REFERENCES [dbo].[Segments] ([Number])

  ALTER TABLE [dbo].[MISHistory] NOCHECK CONSTRAINT [FK_MISHistory_Segments]

  ALTER TABLE [dbo].[MISHistory]  WITH NOCHECK ADD  CONSTRAINT [FK_MISHistory_Ware] FOREIGN KEY([FK_WareNumber])
  REFERENCES [dbo].[Ware] ([WareNumber])

  ALTER TABLE [dbo].[MISHistory] NOCHECK CONSTRAINT [FK_MISHistory_Ware]

  ALTER TABLE [dbo].[MISHistory]  WITH NOCHECK ADD  CONSTRAINT [FK_MISHistory_Folios] FOREIGN KEY([FK_FolioNumber])
  REFERENCES [dbo].[Folios] ([FolioNumber])

  ALTER TABLE [dbo].[MISHistory] NOCHECK CONSTRAINT [FK_MISHistory_Folios]

  ALTER TABLE [dbo].[MISHistory]  WITH NOCHECK ADD  CONSTRAINT [FK_MISHistory_Reservations] FOREIGN KEY([FK_ReferenceNumber], [FK_GuestNumber])
  REFERENCES [dbo].[Reservations] ([ReferenceNumber], [GuestNumber])

  ALTER TABLE [dbo].[MISHistory] NOCHECK CONSTRAINT [FK_MISHistory_Reservations]
END
ELSE
BEGIN
  -- drop primary key
  DECLARE @PK VARCHAR(255)

	SELECT @PK = so1.name 
  FROM sysobjects so1
	INNER JOIN sysobjects so2 ON so1.parent_obj = so2.id 
  AND so2.name = 'MisHistory' 
  AND so1.xtype = 'pk'
  
  IF ISNULL(@PK, '') <> ''
		EXEC('ALTER TABLE dbo.MisHistory DROP CONSTRAINT ' + @PK)

  IF NOT EXISTS
  (
    SELECT SysColumns.Name 
    FROM SysObjects WITH (NOLOCK) 
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
      AND SysColumns.Name = 'FK_WareNumber'
      WHERE SysObjects.Name = 'MISHistory'
  )
  BEGIN
    ALTER TABLE MISHistory ADD FK_WareNumber INT NULL
  END

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

  IF NOT EXISTS
  (
    SELECT SysColumns.Name 
    FROM SysObjects WITH (NOLOCK) 
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
      AND SysColumns.Name = 'FK_ProjectCode'
      WHERE SysObjects.Name = 'MISHistory'
  )
  BEGIN
    ALTER TABLE MISHistory ADD FK_ProjectCode INT NULL
  END

  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 = 'MISHistory'
  )
  BEGIN
    ALTER TABLE MISHistory ADD ReservationStatus INT NULL
  END

  IF NOT EXISTS
  (
    SELECT SysColumns.Name 
    FROM SysObjects WITH (NOLOCK) 
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
      AND SysColumns.Name = 'FK_CountryNumber'
      WHERE SysObjects.Name = 'MISHistory'
  )
  BEGIN
    ALTER TABLE MISHistory ADD FK_CountryNumber INT NULL
  END

  IF NOT EXISTS
  (
    SELECT SysColumns.Name 
    FROM SysObjects WITH (NOLOCK) 
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
      AND SysColumns.Name = 'FK_FolioNumber'
      WHERE SysObjects.Name = 'MISHistory'
  )
  BEGIN
    ALTER TABLE MISHistory ADD FK_FolioNumber INT NULL
  END

  IF NOT EXISTS
  (
    SELECT SysColumns.Name 
    FROM SysObjects WITH (NOLOCK) 
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
      AND SysColumns.Name = 'FK_GuestNumber'
      WHERE SysObjects.Name = 'MISHistory'
  )
  BEGIN
    ALTER TABLE MISHistory ADD FK_GuestNumber SMALLINT NULL
  END
END
GO

UPDATE MISHistory SET FK_CampaignCode = 0 WHERE FK_CampaignCode IS NULL
ALTER TABLE MISHistory ALTER COLUMN FK_CampaignCode SMALLINT NOT NULL

UPDATE MISHistory SET FK_ProjectCode = 0 WHERE FK_ProjectCode IS NULL
ALTER TABLE MISHistory ALTER COLUMN FK_ProjectCode SMALLINT NOT NULL

UPDATE MISHistory SET ReservationStatus = 9 WHERE ReservationStatus IS NULL
ALTER TABLE MISHistory ALTER COLUMN ReservationStatus SMALLINT NOT NULL

UPDATE MISHistory SET FK_CountryNumber = 0 WHERE FK_CountryNumber IS NULL
ALTER TABLE MISHistory ALTER COLUMN FK_CountryNumber INT NOT NULL

UPDATE MISHistory SET FK_FolioNumber = 0 WHERE FK_FolioNumber IS NULL
ALTER TABLE MISHistory ALTER COLUMN FK_FolioNumber INT NOT NULL

UPDATE MISHistory SET FK_GuestNumber = 0 WHERE FK_GuestNumber IS NULL
ALTER TABLE MISHistory ALTER COLUMN FK_GuestNumber SMALLINT NOT NULL