-- 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
	DROP TABLE IF EXISTS t$userlist;
	DROP TABLE IF EXISTS t$username;
	DROP TABLE IF EXISTS t$tempusername;
	DROP TABLE IF EXISTS t$tempuserlist;
	DROP TABLE IF EXISTS t$syncusername;
	
    /*
    [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
        */
		RAISE NOTICE 'CREATE TEMPORARY TABLE t$username';
		CREATE TEMPORARY TABLE t$username
		AS
	  	SELECT DISTINCT PP.UserName
		FROM dbo.PersonHasProgramme PP --FOR SYSTEM_TIME ALL 
		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 'CREATE TEMPORARY TABLE t$userlist';
	   
	   /* Create temp table for user list without usernames */
	CREATE TEMPORARY TABLE t$userlist
	AS
	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;
	   
		/* Get list of usernames */
		CREATE TEMPORARY TABLE t$tempuserlist
		AS
		SELECT  concat(LOWER(LEFT(aws_commons.fstripcharacters( trim(U.firstname),var_InvalidEmailChars),1))
				, LOWER(aws_commons.fstripcharacters(trim(U.lastname),var_InvalidEmailChars))
		) as username,
				  u.personid
		FROM t$userlist U;
		 CREATE INDEX IF NOT EXISTS ix_t$tempuserlist_ix1 ON  t$tempuserlist
					(username);
		
		/* Extract the username without email and numbers */
		CREATE TEMPORARY TABLE t$tempusername
		AS
		select aws_commons.fstripcharacters(split_part(username,'@',1),'0123456789') AS username
		from t$username;
		
		/* Update usernames */
		UPDATE dbo.personhasprogramme as S
		SET username = concat(T.username, 
					  CASE WHEN coalesce(C.usercount, 0) = 0 THEN '' ELSE (C.usercount+1)::varchar END,
					  '@click.org.za')
			, usernamedate = clock_timestamp()		
		FROM t$tempuserlist T
		left join (SELECT DISTINCT username ,count(*) as usercount FROM t$tempusername group by username) as C on C.username = T.username
		WHERE S.personid = T.personid and S.programmeid = 9;
		
		
        -- 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$userlist;
	DROP TABLE IF EXISTS t$username;
	DROP TABLE IF EXISTS t$tempusername;
	DROP TABLE IF EXISTS t$tempuserlist;
	DROP TABLE IF EXISTS t$syncusername;
	DROP TABLE IF EXISTS t$schoolslist;   
END;
$BODY$;
