USE [IEOSMultiTenancy];

GO
/****** Object:  Table [dbo].[MultiTenancyMapping]    Script Date: 5/23/2019 10:23:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MultiTenancyMapping](
    [Id] [uniqueidentifier] NOT NULL,
    [UserGroupName] [nvarchar](max) NOT NULL,
    [ConnectionString] [nvarchar](max) NOT NULL,
    [CreatedDatetime] [datetime] NOT NULL,
 CONSTRAINT [PK_MultiTenancyMapping] PRIMARY KEY CLUSTERED 
(
    [Id] 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]


USE [IEOSMultiTenancy];  
GO   
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ID_ma5367353631';
go
CREATE CERTIFICATE IDSCANDBCERT
   WITH SUBJECT = 'IDSCANDBCERT';  
GO  

CREATE SYMMETRIC KEY IDSCANDBENCKEY  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE IDSCANDBCERT;  
GO  

USE [IEOSMultiTenancy];
GO
/****** Object:  StoredProcedure [dbo].[AddAndEncrypt]    Script Date: 14/06/2019 16:14:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

USE [IEOSMultiTenancy];  
GO  
CREATE PROCEDURE [dbo].[AddAndEncrypt]    
	@ConnectionString nvarchar(max),
	@UserGroupName NVarChar(max)	
AS
BEGIN

OPEN SYMMETRIC KEY IDSCANDBENCKEY
   DECRYPTION BY CERTIFICATE IDSCANDBCERT;  

Declare @encryptedConnectionString nvarchar(Max); 

SET @encryptedConnectionString = EncryptByKey(Key_GUID('IDSCANDBENCKEY'), @ConnectionString);

INSERT INTO [dbo].[MultiTenancyMapping]
           ([Id]
           ,[UserGroupName]
           ,[ConnectionString]
           ,[CreatedDatetime])
     VALUES (NEWID(),@UserGroupName, @encryptedConnectionString, GETDATE())
END

GO  
CREATE PROCEDURE [dbo].[GetDecryptedItem]      
 	@UserGroupName NVarChar(max)	
AS
BEGIN


OPEN SYMMETRIC KEY IDSCANDBENCKEY  
   DECRYPTION BY CERTIFICATE IDSCANDBCERT;  

SELECT Id, UserGroupName, CONVERT(nvarchar(max), DecryptByKey(ConnectionString)) AS 'ConnectionString', CreatedDatetime FROM MultiTenancyMapping WHERE UserGroupName = @UserGroupName
CLOSE SYMMETRIC KEY IDSCANDBENCKEY;

END

GO  
CREATE PROCEDURE [dbo].[GetDecryptedItems]      

AS
BEGIN

OPEN SYMMETRIC KEY IDSCANDBENCKEY  
   DECRYPTION BY CERTIFICATE IDSCANDBCERT;  

SELECT
Id, UserGroupName, CONVERT(nvarchar(max), DecryptByKey(ConnectionString)) AS 'ConnectionString'
, CreatedDatetime FROM MultiTenancyMapping;
 
CLOSE SYMMETRIC KEY IDSCANDBENCKEY;

END

GO  
CREATE PROCEDURE [dbo].[UpdateAndEncrypt]     
    	@ConnectionString nvarchar(max),
	@UserGroupName NVarChar(max),
        @GroupID uniqueidentifier		

AS
BEGIN

OPEN SYMMETRIC KEY IDSCANDBENCKEY
   DECRYPTION BY CERTIFICATE IDSCANDBCERT;  

Declare @encryptedConnectionString nvarchar(Max); 

SET @encryptedConnectionString = EncryptByKey(Key_GUID('IDSCANDBENCKEY'), @ConnectionString);

UPDATE [dbo].[MultiTenancyMapping]
        SET [UserGroupName] = @UserGroupName
           ,[ConnectionString] = @encryptedConnectionString
           ,[CreatedDatetime] = GETDATE() WHERE Id = @GroupID
END