/*
PharmaceuticalSpecialties_Superset

Wire the database Users to these new AD groups

    DEV L-CI-AP-SQL-D-HCI-DataProtectionKeys_R and L-CI-AP-SQL-D-HCI-DataProtectionKeys_RW
    INT L-CI-AP-SQL-I-HCI-DataProtectionKeys_R and L-CI-AP-SQL-I-HCI-DataProtectionKeys_RW
    PROD L-CI-AP-SQL-P-HCI-DataProtectionKeys_R and L-CI-AP-SQL-P-HCI-DataProtectionKeys_RW

01.10.2025, TSC
*/

BEGIN TRANSACTION 

SET XACT_ABORT ON;
SET NOCOUNT ON;

DECLARE @q NVARCHAR(MAX)='';
DECLARE @db sysname='DataProtectionKeys';
DECLARE @env VARCHAR(111);
DECLARE @groups TABLE(env VARCHAR(111) NOT NULL, grp_name VARCHAR(111) NOT NULL);
SELECT @env = CASE 
    WHEN @@SERVERNAME='SWMDATASQLDEV01' THEN 'dev'
    WHEN @@SERVERNAME='SWMDATASQLINT01' THEN 'int'
    WHEN @@SERVERNAME='SWMDATASQLPRD01' THEN 'prod'
    WHEN @@SERVERNAME='SWSQLMDQAS05'    THEN 'prod'
    ELSE 'unknown'
END;
DECLARE @tplLogins NVARCHAR(MAX)='
IF NOT EXISTS (
    SELECT 1
    FROM sys.server_principals
    WHERE name = ''@grp@''
)
BEGIN
    SET @sql = 
        N''CREATE LOGIN [@grp@] '' +
        N''FROM WINDOWS '' +
        N''WITH DEFAULT_DATABASE=[master];'';
    EXEC (@sql);
    
    PRINT ''created login [@grp@]'';
END

';
DECLARE @tplUser NVARCHAR(MAX)='
IF NOT EXISTS (
    SELECT 1
    FROM sys.database_principals
    WHERE name = N''@grp@''
)
BEGIN
    CREATE USER [@grp@] 
    FOR LOGIN [@grp@];
    PRINT ''Added user [@grp@]'';
END 

IF EXISTS (
    SELECT 1
    FROM sys.database_principals
    WHERE name = N''@grp@''
)
BEGIN
    GRANT EXECUTE TO [@grp@];
    --PRINT ''Granted EXECUTE to [@grp@]'';
    
    ALTER ROLE [db_datareader] ADD MEMBER [@grp@]
    --PRINT ''Granted db_datareader to [@grp@]'';

    PRINT ''Granted read and execute permissions to [@grp@]'';
    IF ''@grp@'' like ''%[_]RW''
    BEGIN
        ALTER ROLE [db_datawriter] ADD MEMBER [@grp@];
        PRINT ''Granted db_datawriter to [@grp@]'';
    END 

    
END
 
'

INSERT INTO @groups ([env],[grp_name])
VALUES('dev',  'CENTRALINFRA\L-CI-AP-SQL-D-HCI-DataProtectionKeys_R')
     ,('dev',  'CENTRALINFRA\L-CI-AP-SQL-D-HCI-DataProtectionKeys_RW')
     ,('int',  'CENTRALINFRA\L-CI-AP-SQL-I-HCI-DataProtectionKeys_R')
     ,('int',  'CENTRALINFRA\L-CI-AP-SQL-I-HCI-DataProtectionKeys_RW')
     ,('prod', 'CENTRALINFRA\L-CI-AP-SQL-P-HCI-DataProtectionKeys_R')
     ,('prod', 'CENTRALINFRA\L-CI-AP-SQL-P-HCI-DataProtectionKeys_RW')

;
SELECT @env
IF @env IN ('dev','int','prod')
BEGIN
    SET @q='use master
go
BEGIN TRANSACTION 
SET XACT_ABORT ON;
SET NOCOUNT ON;

IF NOT EXISTS (
    SELECT 1
    FROM sys.databases d
    WHERE name='''+@db+'''
)
BEGIN
    RAISERROR(''The target db %s does not exists (yet?)'',16,5,'''+@db+''');
    RETURN
END


DECLARE @sql nvarchar(MAX)='''';
';

    --#region login
    SELECT @q = @q + REPLACE(@tplLogins, '@grp@',g.[grp_name])
    FROM @groups g
    WHERE [g].[env] = @env
    AND g.[grp_name] LIKE '%\%';
    --#endregion login

    --#region users
    SET @q = @q +'
use '+@db+'
GO
    ';

    SELECT @q = @q + REPLACE(@tplUser,'@grp@', g.[grp_name])
    FROM @groups g
    WHERE g.[env] = @env;
    --#endregion users

    SET @q = @q +'
ROLLBACK TRANSACTION 
'
    PRINT ''
    
    --#region print whole dyn sql
    DECLARE @String NVARCHAR(MAX) = @q 
    DECLARE @CurrentEnd BIGINT; /* track the length of the next substring */
    DECLARE @offset tinyint; /*tracks the amount of offset needed */
    set @string = replace(  replace(@string, char(13) + char(10), char(10))   , char(13), char(10))

    WHILE LEN(@String) > 1
    BEGIN
        IF CHARINDEX(CHAR(10), @String) between 1 AND 4000
        BEGIN
               SET @CurrentEnd =  CHARINDEX(char(10), @String) -1
               set @offset = 2
        END
        ELSE
        BEGIN
               SET @CurrentEnd = 4000
                set @offset = 1
        END   
        PRINT SUBSTRING(@String, 1, @CurrentEnd) 
        set @string = SUBSTRING(@String, @CurrentEnd+@offset, LEN(@String))   
    END /*End While loop*/
    --#endregion print whole dyn sql
END 

ROLLBACK TRANSACTION