USE [HCITools];

IF NOT EXISTS(SELECT 1 FROM sys.[schemas] s WHERE s.[name]='delphix')
BEGIN 
    EXEC('CREATE SCHEMA [delphix]');
END 
GO

IF OBJECT_ID('delphix.delphix_schema_changes') IS NOT NULL 
BEGIN
    DROP PROCEDURE delphix.delphix_schema_changes;
END
GO

IF OBJECT_ID('dbo.delphix_schema_changes') IS NOT NULL 
BEGIN
    DROP PROCEDURE dbo.delphix_schema_changes;
END


GO
/*
OC-945

This procedure is used by Delphix to alter the schema in the databases that must be masked.
It will add new columns to existing tables, that will be used by the masking engine.

The following fields are added:
    int arizona.dbo.addresse
        TT_
*/
CREATE PROCEDURE delphix.delphix_schema_changes
    @removeSchemaChanges BIT = 0
AS
BEGIN 
    SET XACT_ABORT ON;
    SET NOCOUNT ON;

    DECLARE @tblAlter TABLE(dbName VARCHAR(50) NOT NULL, queryAlterTxt NVARCHAR(MAX) NOT NULL, queryUpdateTxt NVARCHAR(MAX) NOT NULL);

    IF @removeSchemaChanges = 1
    BEGIN
        INSERT INTO @tblAlter ([dbName]
                              ,[queryAlterTxt]
                              ,[queryUpdateTxt])
        VALUES(
            'arizona'
            ,'
IF EXISTS(
    SELECT 1
    FROM [INFORMATION_SCHEMA].[COLUMNS] c
    WHERE c.[TABLE_NAME]=''address''
    AND c.[TABLE_SCHEMA]=''dbo''
    AND c.[COLUMN_NAME]=''TT_masking''
)
BEGIN
    IF (OBJECT_ID(''DF_TT_masking'') IS NOT NULL)
    BEGIN
	    ALTER TABLE dbo.[Address] DROP CONSTRAINT [DF_TT_masking];
    END
    ALTER TABLE dbo.[Address] DROP COLUMN [TT_masking];
END
            '
            ,''
        )
        ,(
            'arizona_delphix'
            ,'
IF EXISTS(
    SELECT 1
    FROM [INFORMATION_SCHEMA].[COLUMNS] c
    WHERE c.[TABLE_NAME]=''address''
    AND c.[TABLE_SCHEMA]=''dbo''
    AND c.[COLUMN_NAME]=''TT_masking''
)
BEGIN
    IF (OBJECT_ID(''DF_TT_masking'') IS NOT NULL)
    BEGIN
	    ALTER TABLE dbo.[Address] DROP CONSTRAINT [DF_TT_masking];
    END
    
    ALTER TABLE dbo.[Address] DROP COLUMN [TT_masking];
END
            '
            ,''
        );
    END 
    ELSE 
    BEGIN
        INSERT INTO @tblAlter ([dbName]
                              ,[queryAlterTxt]
                              ,[queryUpdateTxt])
        VALUES(
    'arizona'
    ,'
IF NOT EXISTS(
    SELECT 1
    FROM [INFORMATION_SCHEMA].[COLUMNS] c
    WHERE c.[TABLE_NAME]=''address''
    AND c.[TABLE_SCHEMA]=''dbo''
    AND c.[COLUMN_NAME]=''TT_masking''
)
BEGIN
    ALTER TABLE dbo.[Address] ADD TT_masking VARCHAR(50) NOT NULL CONSTRAINT DF_TT_masking DEFAULT ''MASK'';
END
'
    ,'
--set everything to be masked
UPDATE ad
    SET ad.TT_masking = ''MASK''
FROM      Arizona.dbo.Address ad
WHERE ad.TT_masking <> ''MASK'';

--exclude from masking what we want to keep
UPDATE ad
    SET ad.TT_masking = ''PERSIST''
FROM      Arizona.dbo.Address ad
  JOIN      Arizona.dbo.Customer cst
    ON [cst].[CUST_address]    = [ad].[Address_ID]
  LEFT JOIN Arizona.dbo.[Subsidiary] sub
    ON [cst].[CUST_subsidiary] = [sub].[Subsidiary_ID]
 WHERE      [sub].[Subsidiary_ID] IS NOT NULL
   AND      ( [ad].[AD_last_name] LIKE ''TEST %''
           OR [ad].[AD_name] LIKE ''TEST %''
           OR [ad].[AD_last_name] LIKE ''Assura%''
           OR [ad].[AD_last_name] LIKE ''PRESCRIPTION%''
           OR [ad].[AD_last_name] LIKE ''RETETE%''
           OR [ad].[AD_last_name] LIKE ''USER%''
           OR [ad].[AD_last_name] LIKE ''ONBOARD%''
           OR [ad].[AD_last_name] LIKE ''SUSPICIOUS%''
           OR [ad].[AD_last_name] LIKE ''DOE''
           OR [ad].[AD_last_name] LIKE ''GIALLO%''
           OR [ad].[AD_last_name] LIKE ''TOLKIEN%''
           OR [ad].[AD_last_name] LIKE ''DEMO %''
           OR [ad].[AD_last_name] LIKE ''DOCUMEDIS%''
           OR [ad].[AD_last_name]  = ''DEMO''
           OR [ad].[AD_last_name] LIKE ''OMNICHANNEL%''
           OR [ad].[AD_last_name] LIKE ''LASTNAME%''
           OR [ad].[AD_last_name] LIKE ''MeiXin%''
           OR [ad].[AD_last_name] LIKE ''DIPLOMATE%''
           OR [ad].[AD_last_name] LIKE ''NO %''
           OR [ad].[AD_last_name] LIKE ''ONLY%''
           OR [ad].[AD_last_name] LIKE ''RDL%''
           OR [ad].[AD_last_name] LIKE ''USER%''
           OR [ad].[AD_last_name] LIKE ''GALEXIS%''
           OR [ad].[AD_last_name] LIKE ''APOS%''
           OR [ad].[AD_last_name] LIKE ''APOSANDMAGENTO%''
           OR [ad].[AD_last_name] LIKE ''Activate''
           OR [ad].[AD_last_name] LIKE ''Sunstore''
           OR [ad].[AD_last_name] LIKE ''Coopvitality''
           OR [ad].[AD_last_name] LIKE ''Ama Plus Sun''
           OR [ad].[AD_last_name] LIKE ''Ama Plus Cvi''
           OR [ad].[AD_last_name] LIKE ''Sun Plus Cvi''
           OR [ad].[AD_last_name] LIKE ''Ama Sun Cvi''
           OR [ad].[AD_last_name] LIKE ''test%[0-9]%''
           OR [ad].[AD_first_name] LIKE ''test%[0-9]%''
           OR [ad].[AD_name] LIKE ''%SUNCLUB%''
           OR [ad].[AD_name] LIKE ''%QUALITY%''
           OR [ad].[AD_name] LIKE ''%STARCARD%''
           OR [ad].[AD_name] LIKE ''%CLIENT%''
           OR [ad].[AD_name] LIKE ''%MAGENTO%''
           OR [ad].[AD_name] LIKE ''%RENOUVELLEMENT%''
           OR [ad].[AD_name] LIKE ''%Mitarbeiter%''
           OR [ad].[AD_name] LIKE ''% DEV %''
           OR [ad].[AD_name] LIKE ''CERN Service%''
           OR [ad].[AD_name] LIKE ''BALEXERT CENTRE%''
           OR [ad].[AD_name] LIKE ''G.H.O.L.%''
           OR [ad].[AD_first_name] LIKE ''Test %''
           OR [ad].[AD_first_name] = ''Test''
           OR [ad].[AD_first_name] LIKE ''ONLYAPOS%''
           OR [ad].[AD_first_name] LIKE ''In APOS%''
           OR [ad].[AD_last_name]  = ''DEMO''
           OR ( 
              [ad].[Address_ID]  < 1500000000
              AND NOT EXISTS (SELECT 3 FROM [dbo].[Employee] WHERE EM_address = ad.Address_ID)
              AND NOT EXISTS (SELECT 3 FROM [dbo].[Customer] WHERE CUST_address = ad.Address_ID)
              )
            )
            AND [ad].[TT_masking] <> ''PERSIST''

UPDATE ad
    SET [ad].[TT_masking] = ''PERSIST''
  FROM      Arizona.dbo.PH_prescriber ph
 INNER JOIN Arizona.dbo.Address ad
    ON [ad].[Address_ID] = [ph].[PHPR_address]
 WHERE      [ph].[PHPR_deactivation_date] IS NULL
 AND        [ad].[TT_masking] <> ''PERSIST'';
'
    )
    ,(
    'arizona_delphix'
    ,'
IF NOT EXISTS(
    SELECT 1
    FROM [INFORMATION_SCHEMA].[COLUMNS] c
    WHERE c.[TABLE_NAME]=''address''
    AND c.[TABLE_SCHEMA]=''dbo''
    AND c.[COLUMN_NAME]=''TT_masking''
)
BEGIN
    ALTER TABLE dbo.[Address] ADD TT_masking VARCHAR(50) NOT NULL CONSTRAINT DF_TT_masking DEFAULT ''MASK'';
END
'
    ,'
--set everything to be masked
UPDATE ad
    SET ad.TT_masking = ''MASK''
FROM      Arizona.dbo.Address ad
WHERE ad.TT_masking <> ''MASK'';

--exclude from masking what we want to keep
UPDATE ad
    SET ad.TT_masking = ''PERSIST''
FROM      Arizona.dbo.Address ad
  JOIN      Arizona.dbo.Customer cst
    ON [cst].[CUST_address]    = [ad].[Address_ID]
  LEFT JOIN Arizona.dbo.[Subsidiary] sub
    ON [cst].[CUST_subsidiary] = [sub].[Subsidiary_ID]
 WHERE      [sub].[Subsidiary_ID] IS NOT NULL
   AND      ( [ad].[AD_last_name] LIKE ''TEST %''
           OR [ad].[AD_name] LIKE ''TEST %''
           OR [ad].[AD_last_name] LIKE ''Assura%''
           OR [ad].[AD_last_name] LIKE ''PRESCRIPTION%''
           OR [ad].[AD_last_name] LIKE ''RETETE%''
           OR [ad].[AD_last_name] LIKE ''USER%''
           OR [ad].[AD_last_name] LIKE ''ONBOARD%''
           OR [ad].[AD_last_name] LIKE ''SUSPICIOUS%''
           OR [ad].[AD_last_name] LIKE ''DOE''
           OR [ad].[AD_last_name] LIKE ''GIALLO%''
           OR [ad].[AD_last_name] LIKE ''TOLKIEN%''
           OR [ad].[AD_last_name] LIKE ''DEMO %''
           OR [ad].[AD_last_name] LIKE ''DOCUMEDIS%''
           OR [ad].[AD_last_name]  = ''DEMO''
           OR [ad].[AD_last_name] LIKE ''OMNICHANNEL%''
           OR [ad].[AD_last_name] LIKE ''LASTNAME%''
           OR [ad].[AD_last_name] LIKE ''MeiXin%''
           OR [ad].[AD_last_name] LIKE ''DIPLOMATE%''
           OR [ad].[AD_last_name] LIKE ''NO %''
           OR [ad].[AD_last_name] LIKE ''ONLY%''
           OR [ad].[AD_last_name] LIKE ''RDL%''
           OR [ad].[AD_last_name] LIKE ''USER%''
           OR [ad].[AD_last_name] LIKE ''GALEXIS%''
           OR [ad].[AD_last_name] LIKE ''APOS%''
           OR [ad].[AD_last_name] LIKE ''APOSANDMAGENTO%''
           OR [ad].[AD_last_name] LIKE ''Activate''
           OR [ad].[AD_last_name] LIKE ''Sunstore''
           OR [ad].[AD_last_name] LIKE ''Coopvitality''
           OR [ad].[AD_last_name] LIKE ''Ama Plus Sun''
           OR [ad].[AD_last_name] LIKE ''Ama Plus Cvi''
           OR [ad].[AD_last_name] LIKE ''Sun Plus Cvi''
           OR [ad].[AD_last_name] LIKE ''Ama Sun Cvi''
           OR [ad].[AD_last_name] LIKE ''test%[0-9]%''
           OR [ad].[AD_first_name] LIKE ''test%[0-9]%''
           OR [ad].[AD_name] LIKE ''%SUNCLUB%''
           OR [ad].[AD_name] LIKE ''%QUALITY%''
           OR [ad].[AD_name] LIKE ''%STARCARD%''
           OR [ad].[AD_name] LIKE ''%CLIENT%''
           OR [ad].[AD_name] LIKE ''%MAGENTO%''
           OR [ad].[AD_name] LIKE ''%RENOUVELLEMENT%''
           OR [ad].[AD_name] LIKE ''%Mitarbeiter%''
           OR [ad].[AD_name] LIKE ''% DEV %''
           OR [ad].[AD_name] LIKE ''CERN Service%''
           OR [ad].[AD_name] LIKE ''BALEXERT CENTRE%''
           OR [ad].[AD_name] LIKE ''G.H.O.L.%''
           OR [ad].[AD_first_name] LIKE ''Test %''
           OR [ad].[AD_first_name] = ''Test''
           OR [ad].[AD_first_name] LIKE ''ONLYAPOS%''
           OR [ad].[AD_first_name] LIKE ''In APOS%''
           OR [ad].[AD_last_name]  = ''DEMO''
           OR ( 
              [ad].[Address_ID]  < 1500000000
              AND NOT EXISTS (SELECT 3 FROM [dbo].[Employee] WHERE EM_address = ad.Address_ID)
              AND NOT EXISTS (SELECT 3 FROM [dbo].[Customer] WHERE CUST_address = ad.Address_ID)
              )
            )
            AND [ad].[TT_masking] <> ''PERSIST''

UPDATE ad
    SET [ad].[TT_masking] = ''PERSIST''
  FROM      Arizona.dbo.PH_prescriber ph
 INNER JOIN Arizona.dbo.Address ad
    ON [ad].[Address_ID] = [ph].[PHPR_address]
 WHERE      [ph].[PHPR_deactivation_date] IS NULL
 AND        [ad].[TT_masking] <> ''PERSIST'';
'
        );
    END

    /* declare variables */
    DECLARE @dbName VARCHAR(50), @queryAlterTxt NVARCHAR(MAX), @queryUpdateTxt NVARCHAR(MAX), @queryComplete NVARCHAR(MAX);
    
    DECLARE csrAlter CURSOR FAST_FORWARD READ_ONLY FOR 
        SELECT [dbName],
                [queryAlterTxt],
                [queryUpdateTxt]
        FROM @tblAlter s
        WHERE EXISTS (
            SELECT 1
            FROM sys.databases d
            WHERE d.[name] = s.[dbName]
        )
    
    OPEN csrAlter
    
    FETCH NEXT FROM csrAlter INTO @dbName, @queryAlterTxt, @queryUpdateTxt
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @queryComplete='USE '+@dbName+';'+CHAR(13)+CHAR(10)+@queryAlterTxt;
        --PRINT @queryComplete;
        EXEC(@queryComplete);

        SET @queryComplete='USE '+@dbName+';'+CHAR(13)+CHAR(10)+@queryUpdateTxt;
        --PRINT @queryComplete;
        EXEC(@queryComplete);

        FETCH NEXT FROM csrAlter INTO @dbName, @queryAlterTxt, @queryUpdateTxt
    END
    
    CLOSE csrAlter
    DEALLOCATE csrAlter
END 
GO

BEGIN TRANSACTION 
EXEC delphix.[delphix_schema_changes] @removeSchemaChanges = 0

EXEC('
SELECT ''arizona'' as db_name, COUNT(1) AS cnt, TT_masking
FROM arizona.dbo.[Address]
GROUP BY TT_masking
')

IF EXISTS(SELECT 1 FROM sys.[databases] WHERE name ='arizona_delphix')
    EXEC('
    SELECT ''arizona_delphix'' as db_name, COUNT(1) AS cnt, TT_masking
    FROM arizona_delphix.dbo.[Address]
    GROUP BY TT_masking
    ')

EXEC delphix.[delphix_schema_changes] @removeSchemaChanges = 1
SELECT TOP 10 * FROM [Arizona].[dbo].[Address];


ROLLBACK TRANSACTION 
--COMMIT TRANSACTION