CREATE OR REPLACE PROCEDURE internal.pgenerateusernamesmatific(IN par_schools VARCHAR DEFAULT 'ALL')
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_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
        LEFT OUTER JOIN dbo.personhasprogramme AS o
            ON pp.personid = o.personid AND o.programmeid <> pp.programmeid
        WHERE pp.programmeid = 8 AND o.personid IS NULL 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;
    */
    drop table if exists t$schoolslist;
    CREATE TEMPORARY TABLE t$schoolslist
    (emiscode VARCHAR(9),
        schoolid INTEGER);

    IF (LOWER(par_Schools) = LOWER('ALL')) THEN
        INSERT INTO t$schoolslist
        SELECT
            emiscode, schoolid
            FROM dbo.school;
    END IF;

    IF (LOWER(par_Schools) IN (LOWER('WC'), LOWER('CEMIS'))) THEN
        INSERT INTO t$schoolslist
        SELECT
            emiscode, schoolid
            FROM dbo.school
            WHERE LOWER(LEFT(emiscode, 1)) = LOWER('1');
    ELSE
        IF (LOWER(par_Schools) IN (LOWER('NotWC'), LOWER('DDD'))) THEN
            INSERT INTO t$schoolslist
            SELECT
                emiscode, schoolid
                FROM dbo.school
                WHERE LOWER(LEFT(emiscode, 1)) <> LOWER('1');
        ELSE
            IF (aws_sqlserver_ext.isnumeric(LEFT(par_Schools, 1)) = 1) THEN
                INSERT INTO t$schoolslist
                SELECT
                    emiscode, schoolid
                    FROM dbo.school
                    WHERE emiscode IN (SELECT
                        value
                        FROM string_split(par_Schools, ','));
            END IF;
        END IF;
    END IF;
    CREATE INDEX ix_t$schoolslist_ix1 ON t$schoolslist
        (emiscode);
    CREATE INDEX ix_t$schoolslist_ix2 ON t$schoolslist
        (schoolid);

    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;

        CREATE TEMPORARY TABLE t$username
        AS
        SELECT DISTINCT
            pp.username
            FROM dbo.personhasprogramme AS pp
            JOIN dbo.person AS p
                ON pp.personid = p.personid
            JOIN dbo.schoollearner AS sl
                ON p.personid = sl.personid
            JOIN t$schoolslist AS s
                ON sl.schoolid = s.schoolid
            JOIN dbo.schoollearneryear AS sly
                ON sl.schoollearnerid = sly.schoollearnerid
            LEFT OUTER JOIN dbo.personhasprogramme AS o
                ON pp.personid = o.personid AND o.programmeid <> pp.programmeid
            WHERE pp.programmeid = 8 AND sl.enddate = '9999-12-31' AND sly.year = date_part('year', clock_timestamp()) AND o.personid IS NULL AND pp.username IS NOT NULL;
        CREATE UNIQUE INDEX ix_t$username_ix ON t$username
            (username);
        -- CALL internal.ptemporalstatus('dbo.PersonHasProgramme', 0, par_Silent := 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
        OPEN crsr;
        FETCH NEXT FROM crsr INTO var_PersonID, var_FirstName, var_LastName;

        WHILE (CASE FOUND::INT
            WHEN 0 THEN - 1
            ELSE 0
        END) = 0 LOOP
            var_UserNameCount := 1;

            WHILE 1 = 1 LOOP
                /*
                [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMAT(VARCHAR,VARCHAR) function. Create a user-defined function.]
                SET @UserName = LOWER(@FirstName + LEFT(@LastName,1)) + FORMAT(@UserNameCount, '000');
                */
                IF NOT EXISTS (SELECT
                    *
                    FROM t$username
                    WHERE LOWER(username) = LOWER(var_UserName)) 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 = 8;
                    var_RowCount := (var_RowCount + 1)::INT;
                    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;
        -- 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, '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.
    */
END;
$BODY$
LANGUAGE plpgsql;