IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[WareTicketType]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[WareTicketType]
  (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FK_WareNumber] [int] NOT NULL,
    [NumberOfUsages] [int] NULL,
    [NumberOfDays] [int] NULL,
    [PeriodStartType] [int] NULL,
    [TerminationType] [int] NULL,
    [TerminationCount] [int] NULL,
    [TerminationInitMonths] [int] NULL,
    [CutOffDays] [int] NULL,
    [IsSubscription] [bit] NULL,
    [ProportionatelyPayment] [bit] NULL,
    CONSTRAINT [PK_WareTicketType] PRIMARY KEY CLUSTERED 
    (
      [Id] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[WareTicketType]  WITH CHECK ADD  CONSTRAINT [FK_WareTicketType_Ware] FOREIGN KEY([FK_WareNumber])
  REFERENCES [dbo].[Ware] ([WareNumber])

  ALTER TABLE [dbo].[WareTicketType] CHECK CONSTRAINT [FK_WareTicketType_Ware]
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'TerminationType'
    WHERE SysObjects.Name = 'WareTicketType'
)
BEGIN
  ALTER TABLE WareTicketType ADD TerminationType INT NULL 
  -- None has been added to PeriodType. Added this script should ensure that update only run once.
  UPDATE WareTicketType
  SET PeriodStartType = PeriodStartType + 1
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 = 'TerminationCount'
    WHERE SysObjects.Name = 'WareTicketType'
)
BEGIN
  ALTER TABLE WareTicketType ADD TerminationCount INT NULL 
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 = 'TerminationInitMonths'
    WHERE SysObjects.Name = 'WareTicketType'
)
BEGIN
  ALTER TABLE WareTicketType ADD TerminationInitMonths INT NULL 
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 = 'CutOffDays'
    WHERE SysObjects.Name = 'WareTicketType'
)
BEGIN
  ALTER TABLE WareTicketType ADD CutOffDays INT NULL 
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 = 'IsSubscription'
    WHERE SysObjects.Name = 'WareTicketType'
)
BEGIN
  ALTER TABLE WareTicketType ADD IsSubscription BIT NULL 
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 = 'ProportionatelyPayment'
    WHERE SysObjects.Name = 'WareTicketType'
)
BEGIN
  ALTER TABLE WareTicketType ADD ProportionatelyPayment BIT NULL 
END
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[WareTicketTypePeriod]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[WareTicketTypePeriod]
  (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FK_WareNumber] [int] NOT NULL,
    [DayType] [int] NULL,
    [PeriodFrom] [datetime] NULL,
    [PeriodTo] [datetime] NULL,
    [TimeFrom] [time](7) NULL,
    [TimeTo] [time](7) NULL,
    CONSTRAINT [PK_WareTicketTypePeriod] PRIMARY KEY CLUSTERED 
    (
      [Id] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[WareTicketTypePeriod]  WITH CHECK ADD  CONSTRAINT [FK_WareTicketTypePeriod_Ware] FOREIGN KEY([FK_WareNumber])
  REFERENCES [dbo].[Ware] ([WareNumber])

  ALTER TABLE [dbo].[WareTicketTypePeriod] CHECK CONSTRAINT [FK_WareTicketTypePeriod_Ware]
END

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[ReservationSubscription]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[ReservationSubscription]
  (
    [FK_ReferenceNumber] [int] NOT NULL,
    [FK_GuestNumber] [smallint] NOT NULL,
    [ArrangementLineUniqueID] [int] NOT NULL,
    [SubscriptionDate] [datetime] NOT NULL,
    [TerminationDate] [datetime] NULL,
    [LastUpdDate] [datetime] NULL,
    [FK_OnlinePaymentId] [int] NULL,
    [ServiceToken] [varchar](100) NULL,
    [OnBordingURL] [varchar](1000) NULL,
    [AgreementID] [varchar](100) NULL,
    CONSTRAINT [PK_ReservationSubscription] PRIMARY KEY CLUSTERED 
    (
      [FK_ReferenceNumber] ASC,
      [FK_GuestNumber] ASC,
      [ArrangementLineUniqueID] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[ReservationSubscription] ADD  CONSTRAINT [DF_ReservationSubscription_LastUpdDate]  DEFAULT (getdate()) FOR [LastUpdDate]

  ALTER TABLE [dbo].[ReservationSubscription]  WITH CHECK ADD  CONSTRAINT [FK_ReservationSubscription_ArrangementLines] FOREIGN KEY([ArrangementLineUniqueID])
  REFERENCES [dbo].[ArrangementLines] ([ArrangementLineUniqueID])

  ALTER TABLE [dbo].[ReservationSubscription] CHECK CONSTRAINT [FK_ReservationSubscription_ArrangementLines]

  ALTER TABLE [dbo].[ReservationSubscription]  WITH CHECK ADD  CONSTRAINT [FK_ReservationSubscription_OnlinePayment] FOREIGN KEY([FK_OnlinePaymentId])
  REFERENCES [dbo].[OnlinePayment] ([Id])

  ALTER TABLE [dbo].[ReservationSubscription] CHECK CONSTRAINT [FK_ReservationSubscription_OnlinePayment]

  ALTER TABLE [dbo].[ReservationSubscription]  WITH CHECK ADD  CONSTRAINT [FK_ReservationSubscription_Reservations] FOREIGN KEY([FK_ReferenceNumber], [FK_GuestNumber])
  REFERENCES [dbo].[RESERVATIONS] ([ReferenceNumber], [GuestNumber])

  ALTER TABLE [dbo].[ReservationSubscription] CHECK CONSTRAINT [FK_ReservationSubscription_Reservations]
END