DECLARE intezmenyCursor CURSOR LOCAL FOR
    SELECT C_AZONOSITO FROM T_INTEZMENY WHERE TOROLT = 'F'

DECLARE @intezmenyAzonosito NVARCHAR(30)
DECLARE @schema NVARCHAR(50)
DECLARE @schemaUser NVARCHAR(50)
DECLARE @sql NVARCHAR(max)

OPEN intezmenyCursor
FETCH NEXT FROM intezmenyCursor INTO @intezmenyAzonosito

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @schema = 'KR_'+ @intezmenyAzonosito + '_Schema'
    SET @schemaUser = 'KR_'+ @intezmenyAzonosito + '_user'
    
    IF NOT EXISTS (SELECT 1 FROM MASTER.dbo.syslogins WHERE name = @SchemaUser and dbname = 'master')
    BEGIN
        -- create user login
        SET @Sql = N'CREATE LOGIN ['+@SchemaUser+'] WITH PASSWORD=N''Porcica1.'', DEFAULT_LANGUAGE= British '
        EXECUTE sp_executesql @Sql
    END


    IF NOT EXISTS (SELECT 1 FROM sys.sysusers WHERE name= @SchemaUser)
    BEGIN
        --create user in Kreta database
        SET @Sql = N'CREATE USER ['+@SchemaUser+'] FOR LOGIN ['+@SchemaUser+'] WITH DEFAULT_SCHEMA=['+@Schema+'] '
        EXECUTE sp_executesql @Sql

        ----create schema
        --SET @Sql = N'CREATE SCHEMA ['+@Schema+'] AUTHORIZATION [dbo] '
        --EXECUTE sp_executesql @Sql

        --apply permissions to schemas
        SET @Sql = N'GRANT SELECT ON SCHEMA::['+@Schema+'] TO ['+@SchemaUser+']
        GRANT EXECUTE ON SCHEMA::['+@Schema+'] TO ['+@SchemaUser+']
        GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO ['+@SchemaUser+']
        GRANT DELETE ON SCHEMA::['+@Schema+'] TO ['+@SchemaUser+']
        GRANT UPDATE ON SCHEMA::['+@Schema+'] TO ['+@SchemaUser+']
        GRANT INSERT ON SCHEMA::['+@Schema+'] TO ['+@SchemaUser+']'

        EXECUTE sp_executesql @Sql

        --Allow user to connect to database
        SET @Sql = N'GRANT CONNECT TO ['+@SchemaUser+']'
        EXECUTE sp_executesql @Sql
    END
    ELSE
    BEGIN
        SET @Sql = N'ALTER USER ['+@SchemaUser+'] WITH LOGIN = ['+@SchemaUser+'] '
        EXECUTE sp_executesql @Sql
    END

    FETCH NEXT FROM intezmenyCursor INTO @intezmenyAzonosito
END

CLOSE intezmenyCursor
DEALLOCATE intezmenyCursor
go
exec sp_change_users_login 'Update_One', 'kreta_tech_user', 'kreta_tech_user'
go


  /*
    EMAIL CÍMEK átírása
  */
PRINT 'E-mail címek átírása teszt@ekreta.hura'
UPDATE dbo.T_INTEZMENYADATOK SET C_EMAILCIM = 'teszt@ekreta.hu' WHERE C_EMAILCIM > ''
UPDATE dbo.T_INTEZMENYADATOK SET C_ADMINEMAILCIM = 'teszt@ekreta.hu' WHERE C_ADMINEMAILCIM > ''
UPDATE dbo.T_MUKODESIHELY SET C_EMAILCIM = 'teszt@ekreta.hu' WHERE C_EMAILCIM > ''
UPDATE dbo.T_EMAIL SET C_EMAILCIM = CONCAT('teszt', ID,'@ekreta.hu') WHERE C_EMAILCIM > ''
UPDATE dbo.T_INTEZMENY SET C_FENNTARTOEMAILCIM = 'teszt@ekreta.hu' WHERE C_FENNTARTOEMAILCIM > ''
UPDATE dbo.T_NEBULO SET C_TKEMAILCIM = 'teszt@ekreta.hu' WHERE C_TKEMAILCIM > ''
UPDATE dbo.T_NEBULO SET C_TKEMAILCIM2 = 'teszt@ekreta.hu' WHERE C_TKEMAILCIM2 > ''
GO

/*Config-ok DEV-re állítása*/
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 1
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'https://ginop.ekreta-dev.hu/sso' WHERE C_CONFIGTIPUSID = 2
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 3
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = '-' WHERE C_CONFIGTIPUSID = 4
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 5
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 6
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'https://eugyintezes2.ekreta-dev.hu/kreta/kerelmek' WHERE C_CONFIGTIPUSID = 7
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 8
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'https://eugyintezes2.ekreta-dev.hu/kreta/uzenetek' WHERE C_CONFIGTIPUSID = 9
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 10
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = '-' WHERE C_CONFIGTIPUSID = 11
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 12
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 13
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 14
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 15
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = '-' WHERE C_CONFIGTIPUSID = 16
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 17
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = '-' WHERE C_CONFIGTIPUSID = 18
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 19
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 20
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = '-' WHERE C_CONFIGTIPUSID = 21
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 22
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 23
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 24
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 25
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 26
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'https://tudasbazis.ekreta.hu/' WHERE C_CONFIGTIPUSID = 28
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 29
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'https://tudasbazis.ekreta.hu/' WHERE C_CONFIGTIPUSID = 30
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'https://tudasbazis.ekreta.hu/' WHERE C_CONFIGTIPUSID = 31
UPDATE T_INTEZMENYCONFIG SET [C_ERTEK] = 'false' WHERE C_CONFIGTIPUSID = 32