IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[GDPRApprove]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  ALTER TABLE GDPRApprove ALTER COLUMN Description TEXT NULL
  UPDATE GDPRApprove SET DescriptionShort = ' ' WHERE ISNULL(DescriptionShort, '') = ''
  ALTER TABLE GDPRApprove ALTER COLUMN DescriptionShort VARCHAR(250) NOT NULL

  IF NOT EXISTS
  (
    SELECT SysColumns.Name 
    FROM SysObjects WITH (NOLOCK) 
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
      AND SysColumns.Name = 'IncludedInCommonTerms'
      WHERE SysObjects.Name = 'GDPRApprove'
  )
    ALTER TABLE GDPRApprove ADD IncludedInCommonTerms BIT NULL 

  IF NOT EXISTS
  (
    SELECT SysColumns.Name 
    FROM SysObjects WITH (NOLOCK) 
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
      AND SysColumns.Name = 'EnableForSendDataCheck'
      WHERE SysObjects.Name = 'GDPRApprove'
  )
    ALTER TABLE GDPRApprove ADD EnableForSendDataCheck BIT NULL 

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

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[GDPRApprove]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[GDPRApprove]
  (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [DescriptionShort] [varchar](250) NOT NULL,
    [Description] [text] NULL,
    [GDPRApprovalType] [smallint] NOT NULL,
    [Enable] [bit] NOT NULL,
    [IncludedInCommonTerms] [bit] NULL,
    [EnableForSendDataCheck] [bit] NULL,    
    [ExpireMonth] [INT] NULL,
    CONSTRAINT [PK_GDPRApproval] PRIMARY KEY CLUSTERED 
    (
      [Id] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[GDPRApprove] ADD  CONSTRAINT [DF_GDPRApprove_Enable]  DEFAULT ((0)) FOR [Enable]

  ALTER TABLE [dbo].[GDPRApprove]  WITH CHECK ADD  CONSTRAINT [FK_GDPRApprove_GDPRApprove] FOREIGN KEY([Id])
  REFERENCES [dbo].[GDPRApprove] ([Id])

  ALTER TABLE [dbo].[GDPRApprove] CHECK CONSTRAINT [FK_GDPRApprove_GDPRApprove]

  CREATE TABLE [dbo].[GDPRApproveCustomerXTab]
  (
    [FK_CustomerNumber] [int] NOT NULL,
    [FK_GDPRApproveId] [int] NOT NULL,
    [FK_UserIdNumberApprove] [int] NOT NULL,
    [FK_UserIdNumberReject] [int] NULL,
    [DateApprove] [datetime] NULL,
    [DateReject] [datetime] NULL,
    CONSTRAINT [PK_GDPRApproveCustomerXTab] PRIMARY KEY CLUSTERED 
    (
      [FK_CustomerNumber] ASC,
      [FK_GDPRApproveId] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[GDPRApproveCustomerXTab]  WITH NOCHECK ADD  CONSTRAINT [FK_GDPRApproveCustomerXTab_Customer] FOREIGN KEY([FK_CustomerNumber])
  REFERENCES [dbo].[Customer] ([CustomerNumber])

  ALTER TABLE [dbo].[GDPRApproveCustomerXTab] CHECK CONSTRAINT [FK_GDPRApproveCustomerXTab_Customer]

  ALTER TABLE [dbo].[GDPRApproveCustomerXTab]  WITH NOCHECK ADD  CONSTRAINT [FK_GDPRApproveCustomerXTab_GDPRApprove] FOREIGN KEY([FK_GDPRApproveId])
  REFERENCES [dbo].[GDPRApprove] ([Id])

  ALTER TABLE [dbo].[GDPRApproveCustomerXTab] CHECK CONSTRAINT [FK_GDPRApproveCustomerXTab_GDPRApprove]

  ALTER TABLE [dbo].[GDPRApproveCustomerXTab]  WITH NOCHECK ADD  CONSTRAINT [FK_GDPRApproveCustomerXTab_PersonelApprove] FOREIGN KEY([FK_UserIdNumberApprove])
  REFERENCES [dbo].[PERSONEL] ([UserIDNumber])

  ALTER TABLE [dbo].[GDPRApproveCustomerXTab] NOCHECK CONSTRAINT [FK_GDPRApproveCustomerXTab_PersonelApprove]

  ALTER TABLE [dbo].[GDPRApproveCustomerXTab]  WITH NOCHECK ADD  CONSTRAINT [FK_GDPRApproveCustomerXTab_PersonelReject] FOREIGN KEY([FK_UserIdNumberReject])
  REFERENCES [dbo].[PERSONEL] ([UserIDNumber])

  ALTER TABLE [dbo].[GDPRApproveCustomerXTab] CHECK CONSTRAINT [FK_GDPRApproveCustomerXTab_PersonelReject]
END

--DECLARE @ContentID INTEGER
--DECLARE @TechotelID INTEGER

IF NOT EXISTS (SELECT 1 FROM GDPRApprove WHERE GDPRAPPROVALTYPE = 1)
  INSERT INTO GDPRApprove (Name, Description, DescriptionShort, GDPRApprovalType, Enable) VALUES (
  'Picasso News letter', 
  'Jeg giver samtykke til at hotellet må sende mig nyhedsbreve. Jeg er indforstået med at mine kontaktoplysninger opbevares i hotellets kundekartotek.',
  'Nyhedsbreve', 1, 1)
ELSE
  UPDATE GDPRApprove 
  SET Description = 'Jeg giver samtykke til at hotellet må sende mig nyhedsbreve. Jeg er indforstået med at mine kontaktoplysninger opbevares i hotellets kundekartotek.'
  WHERE GDPRAPPROVALTYPE = 1 AND Description IS NULL

/*
SELECT @ContentID = MIN(ID)
FROM GDPRApprove WITH (NOLOCK)
WHERE GDPRAPPROVALTYPE = 1 AND ENABLE = 1

SELECT @TechotelID = MIN(UserIdNumber) 
FROM Personel WITH (NOLOCK) 
WHERE UserTypeNumber = 2

IF ISNULL(@ContentID, 0) > 0  AND ISNULL(@TechotelID, 0) > 0
BEGIN
  IF NOT EXISTS (SELECT 1 FROM GDPRApproveCustomerXTab (NOLOCK)
                 WHERE FK_GDPRApproveId = @ContentID)
  BEGIN
    INSERT INTO GDPRApproveCustomerXTab (FK_CustomerNumber, FK_GDPRApproveId, FK_UserIdNumberApprove, FK_UserIdNumberReject, DateApprove, DateReject)

      SELECT CUSTOMERNUMBER, @ContentID, @TechotelID, NULL, CURRENT_TIMESTAMP, NULL
      FROM CUSTOMER (NOLOCK)
      WHERE ISNULL(NONEWS, 0) = 0 AND ISNULL(MARKASDELETED, 0) = 0 AND CUSTOMERTYPENUMBER <> 7
  END
END
*/