IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetPasswordHashTEMP]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].GetPasswordHashTEMP
GO
CREATE FUNCTION dbo.GetPasswordHashTEMP
(	
	@Password nvarchar(100)
)
RETURNS varchar(100)
AS
BEGIN
	RETURN CONVERT(VARCHAR(MAX),HASHBYTES('SHA2_256', LOWER(@Password)),2)	
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 = 'PasswordHash'
    WHERE SysObjects.Name = 'Personel'
)
BEGIN
	ALTER TABLE dbo.Personel 
		ADD PasswordHash varchar(100) NOT NULL CONSTRAINT DF_Personel_PasswordHash DEFAULT ''
	exec sp_executesql N'
	ALTER TABLE dbo.Personel
		DROP CONSTRAINT DF_Personel_PasswordHash
	UPDATE dbo.Personel SET PasswordHash=ISNULL(dbo.GetPasswordHashTEMP([PASSWD]),'''') WHERE PASSWD<>''''
	'
END
GO
IF EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'Passwd'
    WHERE SysObjects.Name = 'Personel'
)
BEGIN
	EXEC sp_rename 'Personel.Passwd', 'Obsolete_Passwd', 'COLUMN'; 
END
GO
IF EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'Obsolete_Passwd'
    WHERE SysObjects.Name = 'Personel'
)
BEGIN
	exec sp_executesql N'
	ALTER TABLE dbo.Personel
		ALTER COLUMN Obsolete_Passwd varchar(10) NULL
	'
END
GO
IF EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'Obsolete_Passwd'
    WHERE SysObjects.Name = 'Personel'
)
BEGIN
	exec sp_executesql N'
	UPDATE dbo.Personel SET PasswordHash=ISNULL(dbo.GetPasswordHashTEMP(Obsolete_Passwd),'''') WHERE  Obsolete_Passwd IS NOT NULL AND Obsolete_Passwd<>'''' AND PasswordHash='''' 
	'
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 = 'PinCodeHash'
    WHERE SysObjects.Name = 'Personel'
)
BEGIN
	ALTER TABLE dbo.Personel 
		ADD PinCodeHash varchar(100) NULL 
END
GO
SET NOCOUNT ON;

DECLARE @UserTypeNumber INT
DECLARE @UserIdNumber INT
DECLARE @UserIdName VARCHAR(100)
DECLARE @PassWd VARCHAR(100)= dbo.GetPasswordHashTEMP('Picasso')
IF @PassWd IS NULL
	SET @PassWd = 'D866AB8638EF26B9D9194A8E3E6FCBDFFD7DB9E4C2389798BFE6D56B46590AF8'

DECLARE db_cur_per CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
	SELECT 5 UserTypeNumber, 'PicassoOnline' UserIdName
	UNION ALL SELECT 8, 'ChannelBooking'
	UNION ALL SELECT 9, 'PicassoMobile'
	UNION ALL SELECT 10,'DigitalService'

OPEN db_cur_per
FETCH NEXT FROM db_cur_per INTO @UserTypeNumber, @UserIdName

WHILE @@FETCH_STATUS = 0
BEGIN
  
  --PRINT CAST(@UserTypeNumber AS VARCHAR) + ' ' + @UserIdName

  IF NOT EXISTS(SELECT * FROM Personel WITH (NOLOCK) WHERE UserTypeNumber = @UserTypeNumber)
  BEGIN
    EXEC GetNextNumber 'PERSONELIDENTITY', 1, @UserIDNumber OUTPUT
    INSERT INTO Personel (UserIDNumber, UserIdCode, FirstName, LastName, PasswordHash, UserTypeNumber) VALUES (@UserIdNumber, @UserIdName, @UserIdName, @UserIdName, @PassWd, @UserTypeNumber)
  END
  ELSE
  BEGIN
    IF NOT EXISTS(SELECT * FROM Personel WITH (NOLOCK) WHERE UserIdCode = @UserIdName)   
	BEGIN
      UPDATE Personel SET UserIdCode = @UserIdName, FirstName = @UserIdName, LastName = @UserIdName WHERE UserTypeNumber = @UserTypeNumber
	END
  END

  FETCH NEXT FROM db_cur_per INTO @UserTypeNumber, @UserIdName
END

CLOSE db_cur_per  
DEALLOCATE db_cur_per 
GO
DROP FUNCTION dbo.GetPasswordHashTEMP
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PersonelPicture]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[PersonelPicture](
		[UserIDNumber] [int] NOT NULL,
		[ImageType] [varchar](10) NOT NULL,
		[ImageData] [image] NOT NULL,
	 CONSTRAINT [PK_PersonelPicture] PRIMARY KEY CLUSTERED 
	(
		[UserIDNumber] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

	ALTER TABLE [dbo].[PersonelPicture]  WITH CHECK ADD  CONSTRAINT [FK_PersonelPicture_Personel] FOREIGN KEY([UserIDNumber])
	REFERENCES [dbo].[Personel] ([UserIDNumber])

	ALTER TABLE [dbo].[PersonelPicture] CHECK CONSTRAINT [FK_PersonelPicture_Personel]
END
GO
