IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[PersonelHidden]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[PersonelHidden]
  (
    [HiddenDate] [DATETIME] NOT NULL,
    [FK_UserIDNumber] [int] NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [SocialSecurityNumber] [varchar](16) NULL,
    [DateOfBirth] [varchar](8) NULL,
    [Address1] [varchar](50) NULL,
    [Address2] [varchar](50) NULL,
    [Address3] [varchar](50) NULL,
    [ZipCodeNumber] [int] NULL,
    [EmploymentDate] [varchar](8) NULL,
    [BankAccountNumber] [varchar](50) NULL,
    [BankName] [varchar](30) NULL,
    [EMailHotel] [varchar](255) NULL,
    [EMailPrivate] [varchar](255) NULL,
    [MobilePhoneNumber] [varchar](50) NULL,
    [HomepageURL] [varchar](255) NULL,
    [FaxNumberHotel] [varchar](30) NULL,
    [FaxNumberPrivate] [varchar](30) NULL,
    [PhonePrivate] [varchar](30) NULL,
    [PhoneHotel] [varchar](30) NULL,
    [CardCode] [varchar](1000) NULL,
    [MailSignature] [nvarchar](4000) NULL,
    [EmailPassword] [varchar](255) NULL,
    [EmailOffice365LoginUsername] [varchar](255) NULL,
    [EmailOffice365LoginPassword] [varchar](255) NULL,
    [PasswordHash] [varchar](100) NOT NULL,
    [PinCodeHash] [varchar](100) NULL,
    [Obsolete_Passwd] [varchar](10) NULL,
    CONSTRAINT [PK_PersonelHidden] PRIMARY KEY CLUSTERED 
    (
      [FK_UserIDNumber] ASC
    )
  ) ON [PRIMARY]
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'StaffAnonymized'
    WHERE SysObjects.Name = 'Personel'
)
BEGIN
  ALTER TABLE Personel ADD StaffAnonymized 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 = 'ForceAnonymization'
    WHERE SysObjects.Name = 'Personel'
)
BEGIN
  ALTER TABLE Personel ADD ForceAnonymization 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 = 'AnonymizeDate'
    WHERE SysObjects.Name = 'Personel'
)
BEGIN
  ALTER TABLE Personel ADD AnonymizeDate DATETIME 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 = 'DeletedDate'
    WHERE SysObjects.Name = 'Personel'
)
BEGIN
  ALTER TABLE Personel ADD DeletedDate DATETIME NULL 
END
GO

UPDATE Personel SET DeletedDate = GETDATE() WHERE DeletedDate IS NULL AND MarkAsDeleted = 1