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

UPDATE ArrangementLines
SET ArrangementLineUniqueID = ((ArrangementLines.ArrangementLineNumber & 0x7ffff /*19 bits*/) * 4096 /* << 12 */) | (ArrangementLines.ArrangementLineNumber / 524288  /* >> 19 */)
WHERE ISNULL(ArrangementLineUniqueID, 0) = 0

IF NOT EXISTS (SELECT Name FROM SysIndexes WITH (NOLOCK) WHERE Name = 'IX_ArrangementLineUniqueID')
BEGIN
  CREATE UNIQUE NONCLUSTERED INDEX [IX_ArrangementLineUniqueID] ON [dbo].[ArrangementLines]
  (
    [ArrangementLineUniqueID] ASC
  )
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ArrangementLinesTicket]') AND type in (N'U'))
BEGIN
  CREATE TABLE [dbo].[ArrangementLinesTicket]
  (
    [ArrangementLineUniqueId] [int] NOT NULL,
    [Firstname] [varchar](50) NULL,
    [Lastname] [varchar](50) NULL,
    [Phone] [varchar](50) NULL,
    [ImageContentType] [varchar](50) NULL,
    [Image] [varbinary](max) NULL,
    [DateOfBirth] [datetime] NULL,
    SentToProducer BIT NULL
    CONSTRAINT [PK_ArrangementLinesTicket] PRIMARY KEY CLUSTERED 
    (
      [ArrangementLineUniqueId] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  ALTER TABLE [dbo].[ArrangementLinesTicket]  WITH NOCHECK ADD  CONSTRAINT [FK_ArrangementLinesTicket_ArrangementLines1] FOREIGN KEY([ArrangementLineUniqueId])
  REFERENCES [dbo].[ArrangementLines] ([ArrangementLineUniqueID])

  ALTER TABLE [dbo].[ArrangementLinesTicket] NOCHECK CONSTRAINT [FK_ArrangementLinesTicket_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 = 'DateOfBirth'
    WHERE SysObjects.Name = 'ArrangementLinesTicket'
  )
  BEGIN
    ALTER TABLE ArrangementLinesTicket ADD DateOfBirth DATETIME NULL 
  END
  ELSE
  BEGIN
    IF (SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ArrangementLinesTicket' AND COLUMN_NAME = 'DateOfBirth') = 'DATE'
      ALTER TABLE ArrangementLinesTicket ALTER COLUMN DateOfBirth DATETIME 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 = 'ArrangementLineUniqueId'
    WHERE SysObjects.Name = 'ArrangementLinesTicket'
  )
  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'ArrangementLinesTicket'

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

    EXEC sp_rename 'dbo.ArrangementLinesTicket.ArrangementLineNumber', 'ArrangementLineUniqueId', 'COLUMN'
  END
  IF EXISTS
  (
    SELECT * 
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_NAME ='FK_ArrangementLinesTicket_ArrangementLines'
  )
  BEGIN
    ALTER TABLE [dbo].[ArrangementLinesTicket]  DROP CONSTRAINT [FK_ArrangementLinesTicket_ArrangementLines]
  END
  IF NOT EXISTS(
    SELECT *
    FROM sys.key_constraints
    WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'ArrangementLinesTicket'
  )
  BEGIN
    ALTER TABLE [dbo].[ArrangementLinesTicket] ADD CONSTRAINT PK_ArrangementLinesTicket PRIMARY KEY CLUSTERED (ArrangementLineUniqueId)
  END

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

IF NOT EXISTS
(
  SELECT * 
  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
  WHERE CONSTRAINT_NAME ='FK_ArrangementLinesTicket_ArrangementLines1'
)
BEGIN
  ALTER TABLE [dbo].[ArrangementLinesTicket]  WITH NOCHECK ADD  CONSTRAINT [FK_ArrangementLinesTicket_ArrangementLines1] FOREIGN KEY([ArrangementLineUniqueId])
  REFERENCES [dbo].[ArrangementLines] ([ArrangementLineUniqueID])

  ALTER TABLE [dbo].[ArrangementLinesTicket] NOCHECK CONSTRAINT [FK_ArrangementLinesTicket_ArrangementLines1]
END
GO
