SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[TicketStatistics]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[TicketStatistics]
  (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [DateStamp] [datetime] NOT NULL,
    [NumberOfAdult] [int] NULL,
    [NumberOfChildren1] [int] NULL,
    [NumberOfChildren2] [int] NULL,
    [FK_ArrangementUniqueId] [int] NULL,
    CONSTRAINT [PK_TicketStatistics] PRIMARY KEY CLUSTERED 
    (
      [Id] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[TicketStatistics]  WITH CHECK ADD  CONSTRAINT [FK_TicketStatistics_ArrangementLines1] FOREIGN KEY([FK_ArrangementUniqueId])
  REFERENCES [dbo].[ArrangementLines] ([ArrangementLineUniqueID])

  ALTER TABLE [dbo].[TicketStatistics] CHECK CONSTRAINT [FK_TicketStatistics_ArrangementLines1]
END
ELSE
BEGIN
  IF NOT EXISTS
  (
    SELECT SysColumns.Name 
    FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
      AND SysColumns.Name = 'FK_ArrangementUniqueId'
    WHERE SysObjects.Name = 'TicketStatistics'
  )
  BEGIN
    DECLARE @DefaultConstraint SYSNAME
    DECLARE @DropStatement NVARCHAR(500)

    SELECT @DefaultConstraint = name
    FROM sys.key_constraints
    WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'TicketStatistics'

    IF @DefaultConstraint <> ''
    BEGIN
      SET @DropStatement = 'ALTER TABLE TicketStatistics DROP CONSTRAINT ' + @DefaultConstraint
      EXEC (@DropStatement)
    END

    EXEC sp_rename 'dbo.TicketStatistics.FK_ArrangementLineNumber', 'FK_ArrangementUniqueId', 'COLUMN'
  END
END
GO

IF NOT EXISTS
(
  SELECT * 
  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
  WHERE CONSTRAINT_NAME ='FK_TicketStatistics_ArrangementLines1'
)
BEGIN
  ALTER TABLE [dbo].[TicketStatistics]  WITH CHECK ADD  CONSTRAINT [FK_TicketStatistics_ArrangementLines1] FOREIGN KEY([FK_ArrangementUniqueId])
  REFERENCES [dbo].[ArrangementLines] ([ArrangementLineUniqueID])

  ALTER TABLE [dbo].[TicketStatistics] CHECK CONSTRAINT [FK_TicketStatistics_ArrangementLines1]
END
GO