-- PROCEDURE: internal.pgenerateusernamesreflectivelearning()

-- DROP PROCEDURE IF EXISTS internal.pgenerateusernamesreflectivelearning();

CREATE OR REPLACE PROCEDURE internal.pgenerateusernamesreflectivelearning(
	)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    var_TranCountOnEntry INTEGER
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
    = @@TRANCOUNT
    */;
    var_RowCount INTEGER DEFAULT 0;
    var_InvalidEmailChars VARCHAR(100) DEFAULT E'%[]"''(),:;<>\\/@';
    var_PersonID INTEGER;
    var_FirstName VARCHAR(50);
    var_LastName VARCHAR(50);
    var_UserName VARCHAR(50);
    var_UserNameCount INTEGER;
    crsr CURSOR FOR
    SELECT
        pp.personid, p.firstname, p.lastname
        FROM dbo.personhasprogramme AS pp
        JOIN dbo.person AS p
            ON pp.personid = p.personid
        CROSS JOIN LATERAL (SELECT
            COUNT(*) AS cnt
            FROM dbo.personhasprogramme AS o
            JOIN t$syncusername AS s
                ON o.programmeid = s.programmeid
            WHERE pp.personid = o.personid) AS o
        WHERE pp.programmeid = 9 AND o.Cnt = 0 AND pp.username IS NULL;
    ptemporalstatus$par_StatusChanged NUMERIC;
    ptemporalstatus$ReturnCode INTEGER;
BEGIN
    /*
    [7810 - Severity CRITICAL - PostgreSQL doesn't support the SET NOCOUNT. If need try another way to send message back to the client application.]
    SET NOCOUNT ON;
    */
    IF aws_sqlserver_ext.object_id('tempdb..#SchoolsList') IS NULL THEN
        drop table if exists t$schoolslist;
        CREATE TEMPORARY TABLE t$schoolslist
        (emiscode VARCHAR(9),
            schoolid INTEGER);
        CREATE INDEX ix_t$schoolslist_ix1 ON t$schoolslist
            (emiscode);
        CREATE INDEX ix_t$schoolslist_ix2 ON t$schoolslist
            (schoolid);
    END IF;

    BEGIN
        IF var_TranCountOnEntry = 0 THEN
            /*
            [7807 - Severity CRITICAL - PostgreSQL does not support explicit transaction management in functions. Perform a manual conversion.]
            BEGIN TRAN
            */
            BEGIN
            END;
        END IF;

        IF aws_sqlserver_ext.object_id('tempdb..#UserName') IS NOT NULL THEN
            DROP TABLE dbo.t$UserName;
        END IF;
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
        SELECT DISTINCT PP.UserName
        		INTO #UserName
        		FROM PersonHasProgramme FOR SYSTEM_TIME ALL PP
        		WHERE PP.ProgrammeID = 9
        			AND PP.UserName IS NOT NULL
        */
        CREATE UNIQUE INDEX IF NOT EXISTS ix_t$username_ix1 ON t$username
            (username);
		RAISE NOTICE 'CREATE TEMPORARY TABLE t$syncusername';
        CREATE TEMPORARY TABLE t$syncusername
        (programmeid INTEGER);
        INSERT INTO t$syncusername
        SELECT
            CAST (value AS INTEGER)
            FROM dbo.systemparameter
            WHERE LOWER(category) = LOWER('Processing') AND LOWER(subcategory) = LOWER('Learners') AND LOWER(name) = LOWER('Sync UserName') AND LOWER(value1) = LOWER('9');
        -- CALL internal.ptemporalstatus('dbo.PersonHasProgramme', 0, par_Silent := 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
       RAISE NOTICE 'DONE INSERT t$syncusername';
	   	OPEN crsr;
        FETCH NEXT FROM crsr INTO var_PersonID, var_FirstName, var_LastName;
		RAISE NOTICE 'DONE FETCH';
-- 		RETURN; --Testing
        WHILE (CASE FOUND::INT
            WHEN 0 THEN - 1
            ELSE 0
        END) = 0 LOOP
            var_UserNameCount = 0;
-- 			RAISE NOTICE 'UPDATE dbo.personhasprogramme';
            WHILE 1 = 1 LOOP
                /*
                [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMAT(VARCHAR,VARCHAR) function. Create a user-defined function.]
                SET @UserName = LOWER(LEFT(ClickCOMMON.dbo.fStripCharacters(@FirstName,@InvalidEmailChars),1))
                					+ LOWER(ClickCOMMON.dbo.fStripCharacters(@LastName,@InvalidEmailChars))
                					+ CASE WHEN @UserNameCount = 0 THEN '' ELSE FORMAT(@UserNameCount, '0') END
                					+ '@click.org.za'
                */
                IF NOT EXISTS (SELECT
                    *
                    FROM t$username
                    WHERE username = var_UserName::VARCHAR) THEN
                    INSERT INTO t$username
                    SELECT
                        var_UserName;
                    -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %. Argument: %. Argument: %. Argument: %', '50000', 0, 0, '%s %s %d => %s', var_FirstName, var_LastName, var_UserNameCount, var_UserName USING ERRCODE = '50000';
                    
					UPDATE dbo.personhasprogramme
                    SET username = var_UserName, usernamedate = clock_timestamp()
                        WHERE personid = var_PersonID AND programmeid = 9;
                    var_RowCount = (var_RowCount + 1)::INT;
					RAISE NOTICE 'ROWCOUNT: %',var_RowCount;
                    EXIT;
                ELSE
                    var_UserNameCount = (var_UserNameCount + 1)::INT;
                END IF;
            END LOOP;
            FETCH NEXT FROM crsr INTO var_PersonID, var_FirstName, var_LastName;
        END LOOP;
        CLOSE crsr;
		RAISE NOTICE 'END WHILE LOOP';
        -- CALL internal.ptemporalstatus('dbo.PersonHasProgramme', 1, par_Silent := 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
        -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, 'Reflective Learning Usernames set: %d', var_RowCount USING ERRCODE = '50000';

--         IF var_TranCountOnEntry = 0 THEN
--             COMMIT;
--         END IF;
--         EXCEPTION
--             WHEN OTHERS THEN
--                 IF var_TranCountOnEntry = 0 THEN
--                     ROLLBACK;
--                 END IF;
                -- RAISE;
    END;
    /*
    
    DROP TABLE IF EXISTS t$schoolslist;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
    /*
    
    DROP TABLE IF EXISTS t$username;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
    /*
    
    DROP TABLE IF EXISTS t$syncusername;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
END;
$BODY$;
