CREATE OR REPLACE PROCEDURE dbo.plearnersregisterfromsourcelearner(
    par_executionid uuid DEFAULT NULL::uuid,
    par_schools character varying DEFAULT NULL::character varying,
    par_programmes character varying DEFAULT NULL::character varying,
    par_silent numeric DEFAULT 0,
    par_debugmode integer DEFAULT 0,
    INOUT p_refcur refcursor DEFAULT NULL::refcursor)
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_DataDate DATE;
    var_SourceYear SMALLINT;
    var_RowCount INTEGER;
    var_SourceDataImportIDs VARCHAR(2048);
    var_DataSource VARCHAR(2048);
    var_DataImportID BIGINT;
    var_Message VARCHAR(2048);
    pupdatedataimport$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
    */
    var_DataDate := clock_timestamp();
    var_SourceYear := date_part('year', var_DataDate);
    Raise Notice '%',par_Programmes;
    CREATE TEMPORARY TABLE t$programmeidtable
    (programmeid SMALLINT);
RAISE NOTICE 'CREATE TEMPORARY TABLE t$programmeidtable';  
    IF LOWER(par_Programmes) = LOWER('ALL') THEN
        INSERT INTO t$programmeidtable
        SELECT
            4
        UNION
        SELECT
            8
        UNION
        SELECT
            9;
    ELSE
        INSERT INTO t$programmeidtable
        SELECT
            p.programmeid
            FROM dbo.programme AS p
            JOIN (SELECT
                value
                FROM string_split(par_Programmes, ',')
                    AS f) AS f
                ON LOWER(f.value) = LOWER(p.code) OR LOWER(f.value) = LOWER(p.name) OR
                CASE
                    WHEN aws_sqlserver_ext.ISNUMERIC(value) THEN CAST (value AS INTEGER)
                    ELSE NULL
                END = p.programmeid;
    END IF;
    CREATE TEMPORARY TABLE t$schoolslist
    (emiscode VARCHAR(9),
        schoolid INTEGER);
RAISE NOTICE 'CREATE TEMPORARY TABLE t$schoolslist';  

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

    IF (LOWER(par_Schools) IN (LOWER('WC'), LOWER('CEMIS'))) THEN
        INSERT INTO t$schoolslist
        SELECT
            emiscode, schoolid
            FROM dbo.school_dw
            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_dw
                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_dw
                    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);
    CREATE TEMPORARY TABLE t$schools
    (schoolid INTEGER,
        emiscode VARCHAR(9),
        schoolname VARCHAR(100),
        programmeenvironmentid INTEGER,
        programmeid SMALLINT,
        implementationstatusid SMALLINT,
        grades VARCHAR(100),
        gradecount SMALLINT);
RAISE NOTICE 'CREATE TEMPORARY TABLE t$schools';      
 

    INSERT INTO t$schools
        (SchoolID, EMISCode, SchoolName, ProgrammeEnvironmentID, ProgrammeID, ImplementationStatusID)
        SELECT DISTINCT SEP.SchoolID, SEP.EMISCode, SEP.Name, SEP.ProgrammeEnvironmentID, SEP.ProgrammeID, SEP.ImplementationStatusID
        FROM dbo.vwschoolenvironmentprogramme_dw SEP
        JOIN t$schoolslist SX ON SEP.SchoolID = SX.SchoolID
        JOIN t$programmeidtable P ON SEP.ProgrammeID = P.ProgrammeID
/*
        JOIN (SELECT DISTINCT PE.ProgrammeEnvironmentID 
              FROM dbo.programmeenvironment_dw
                FOR SYSTEM_TIME 
                FROM '2020-01-01' TO '9999-12-31' PE 
              WHERE PE.ImplementationStatusID in (4,13)) PEACTIVE ON SEP.ProgrammeEnvironmentID = PEACTIVE.ProgrammeEnvironmentID
*/
        WHERE 1=1
            AND SEP.ProgrammeManagementLevelID = 1 --only look at automatic
            AND LENGTH(SEP.EMISCode) = 9
            AND SEP.EMISCOde::numeric = 1 -- filter out ECD and AS
            AND SEP.ImplementationStatusID IN (4,13);

    -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, '#Schools: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
    CREATE UNIQUE INDEX ix_t$schools_ix1 ON t$schools
        (schoolid, programmeid);
    CREATE UNIQUE INDEX ix_t$schools_ix2 ON t$schools
        (programmeenvironmentid);
    CREATE TEMPORARY TABLE t$uniqueschools
    AS
    SELECT DISTINCT
        schoolid, emiscode, schoolname
        FROM t$schools;
RAISE NOTICE 'CREATE TEMPORARY TABLE t$uniqueschools';              
    -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, '#UniqueSchools: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
    CREATE TEMPORARY TABLE t$schoolgrades
    (schoolid INTEGER,
        programmeid SMALLINT,
        gradeid INTEGER,
        managementlevel INTEGER);
RAISE NOTICE 'CREATE TEMPORARY TABLE t$schoolgrades';                  
    /*
    [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
    INSERT #SchoolGrades
        (SchoolID, ProgrammeID, GradeID, ManagementLevel)
        SELECT S.SchoolID, S.ProgrammeID, TG.GradeID, TG.LearnerManagementLevelID
        FROM #Schools S
        JOIN ClickDW.dbo.ProgrammeEnvironmentHasTargetGrade FOR SYSTEM_TIME AS OF @DataDate TG ON S.ProgrammeEnvironmentID = TG.ProgrammeEnvironmentID AND TG.ActiveFrom <= @DataDate
        WHERE TG.LearnerManagementLevelID = 1
    */

    INSERT INTO t$schoolgrades
        (SchoolID, ProgrammeID, GradeID, ManagementLevel)
    SELECT S.SchoolID, S.ProgrammeID, TG.GradeID, TG.LearnerManagementLevelID
    FROM t$schools S
/* 
    JOIN dbo.programmeenvironmenthastargetgrade_dw 
        FOR SYSTEM_TIME AS OF @DataDate TG ON S.ProgrammeEnvironmentID = TG.ProgrammeEnvironmentID AND TG.ActiveFrom <= @DataDate
*/ 
    JOIN dbo.programmeenvironmenthastargetgrade_dw TG ON S.ProgrammeEnvironmentID = TG.ProgrammeEnvironmentID AND TG.ActiveFrom <= var_DataDate

    WHERE TG.LearnerManagementLevelID = 1;

    -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, '#SchoolGrades: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
    CREATE UNIQUE INDEX ix_t$schoolgrades_ix1 ON t$schoolgrades
        (schoolid, programmeid, gradeid);
    CREATE TEMPORARY TABLE t$uniqueschoolgrades
    AS
    SELECT DISTINCT
        schoolid, gradeid
        FROM t$schoolgrades;
RAISE NOTICE 'CREATE TEMPORARY TABLE t$uniqueschoolgrades';                          
    /* SELECT * FROM #UniqueSchoolGrades WHERE SchoolID = 100357 */
    -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, '#UniqueSchoolGrades: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
    CREATE TEMP TABLE t$learners(
        LastSubmissionDate Date,
        SchoolName character varying(100),
        Emiscode character varying(10),
        dddlearnerid integer,
        Firstname character varying(50),
        LastName character varying(50),
        AccessionNumber text,
        IDNumber text,
        DateOfBirth Date,
        Gender text,
        Race text,
        LuritsNumber text,
        Citizenship text,
        PassportNumber text,
        DateJoined Date,
        DateLeft Date,
        HomeLanguage text,
        InstructionLanguage text,
        Grade smallint,
        Class integer,
        ClassName text,
        CEMISLearnerID text,
        idNumberInvalid character varying(30),
        idNumberInvalidReason character varying(50),
        SourceDataImportId bigint,
        PersonID integer
    );
    
    
    Insert into t$learners
    --AS
    /* DISTINCT */
    (SELECT
        var_DataDate AS "LastSubmissionDate", s.schoolname, s.emiscode, l.dddlearnerid, l.firstname, l.lastname, NULLIF(l.accessionnumber, '') AS "AccessionNumber", NULLIF(l.idnumber, '') AS "IDNumber", l.dateofbirth AS "DateOfBirth", NULLIF(l.gender, '') AS "Gender", NULLIF(l.race, '') AS "Race", NULLIF(l.luritsnumber, '') AS "LuritsNumber", NULLIF(l.citizenship, '') AS "Citizenship", NULLIF(l.passportnumber, '') AS "PassportNumber", l.datejoined AS "DateJoined", l.dateleft AS "DateLeft", NULLIF(l.homelanguage, '') AS "HomeLanguage", NULLIF(l.instructionlanguage, '') AS "InstructionLanguage", l.grade AS "Grade", l.class AS "Class", NULLIF(l.classname, '') AS "ClassName", NULLIF(l.cemislearnerid, '') AS "CEMISLearnerID", idnumberinvalid, idnumberinvalidreason, sourcedataimportid, l.mappedpersonid AS "PersonID"
        FROM dbo.sourcelearner_dw AS l
        JOIN t$uniqueschools AS s
            ON LOWER(l.emiscode) = LOWER(s.emiscode)
        JOIN t$uniqueschoolgrades AS sg
            ON s.schoolid = sg.schoolid AND l.grade = sg.gradeid
        WHERE l.mappedpersonid IS NULL AND l.mappedschoollearnerid IS NULL AND l.year = var_SourceYear AND l.dateleft IS NULL
        ORDER BY s.emiscode NULLS FIRST, lastname NULLS FIRST, firstname NULLS FIRST);
RAISE NOTICE 'CREATE TEMPORARY TABLE t$learners';  

    
    /*
    [7833 - Severity CRITICAL - Migration @@rowcount function in current context is not supported. Perform a manual conversion.]
    SET @RowCount = @@ROWCOUNT;
    */
    -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, '#Learners: %d', var_RowCount USING ERRCODE = '50000';

    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;
        SELECT
            string_agg(sourcedataimportid, ',')
            INTO var_SourceDataImportIDs
            FROM (SELECT DISTINCT
                CAST (sourcedataimportid AS VARCHAR(2048)) AS sourcedataimportid
                FROM t$learners) AS l;

        IF var_SourceDataImportIDs IS NULL THEN
            var_SourceDataImportIDs := CAST ((SELECT
                MAX(sourcedataimportid)
                FROM dbo.sourcelearner_dw) AS VARCHAR(2048));
        END IF;
        var_DataSource := 'SourceLearner-Registration: ' || var_SourceDataImportIDs;
RAISE NOTICE 'var_DataSource: %',var_DataSource;
        -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, '%s', var_DataSource USING ERRCODE = '50000';
        /* IF @SourceDataImportIDs <> '' */
        /* BEGIN */
        /* SELECT @DataImportID = DI.DataImportID */
        /* FROM DataImportDataContract DI */
        /* WHERE DI.DataSourceTypeID = 11 */
        /* AND DI.DataImportStatus = 'Success' */
        /* AND DI.DataContractDataSource = @DataSource */
        /* END */

        IF var_DataImportID IS NOT NULL THEN
            Raise Notice 'Data Import done before';
        ELSE
            CREATE TEMPORARY TABLE newdataimportidtable$plearnersregisterfromsourcelearner
            (datacontractid BIGINT,
                dataimportid BIGINT,
                createtime TIMESTAMP WITHOUT TIME ZONE,
                starttime TIMESTAMP WITHOUT TIME ZONE,
                value1 character varying,
                value2 character varying);
RAISE NOTICE 'CREATE TEMPORARY TABLE newdataimportidtable$plearnersregisterfromsourcelearner';                                              
           -- SELECT dbo.pnewdataimportid$TMP(par_DataSourceType := 'Learner - Manual', par_DataSource := var_DataSource, par_Description := var_DataSource, par_ExecutionID := par_ExecutionID, par_DataDate := var_DataDate);
            RAISE NOTICE 'called pnewdataimportid ';
            Raise Notice 'par_DataSource %', var_DataSource;
            Raise Notice 'par_Description %', var_DataSource ;
            Raise Notice 'par_ExecutionID %', par_ExecutionID;
            Raise Notice 'par_DataDate %', var_DataDate;
            INSERT INTO newdataimportidtable$plearnersregisterfromsourcelearner
                SELECT datacontractid, dataimportid, createtime, starttime, value1, value2 
                from dbo.pnewdataimportid('Learner - Manual', var_DataSource,var_DataDate) LIMIT 1;
            Raise Notice 'call newId successful';
            SELECT
                dataimportid
                INTO var_DataImportID
                FROM newdataimportidtable$plearnersregisterfromsourcelearner;
            Raise Notice 'Import ID Assigned';
            INSERT INTO dbo.learner (dataimportid, rownumber, lastsubmissiondate, province, region, district, circuit, cluster, schoolname, emiscode, dddlearnerid, firstname, lastname, accessionnumber, idnumber, dateofbirth, gender, race, luritsnumber, citizenship, passportnumber, datejoined, dateleft, homelanguage, instructionlanguage, grade, class, classname, extid, extidtype, personid, idnumberinvalidreason, idnumberinvalid, cemislearnerid)
            SELECT
                var_DataImportID, ROW_NUMBER() OVER (ORDER BY emiscode, lastname, firstname), lastsubmissiondate, NULL, NULL, NULL, NULL, NULL, schoolname, emiscode, dddlearnerid, firstname, lastname, accessionnumber, idnumber, dateofbirth, gender, race, luritsnumber, citizenship, passportnumber, datejoined, dateleft, homelanguage, instructionlanguage, grade, class, classname,
               -- Raise Notice 'Case1';
                CASE
                    
                    WHEN dddlearnerid IS NOT NULL THEN CAST (dddlearnerid AS VARCHAR(20))
                    WHEN cemislearnerid IS NOT NULL THEN cemislearnerid
                    ELSE NULL
                END,
                
                CASE
                    --Raise Notice 'Case2';
                    WHEN dddlearnerid IS NOT NULL THEN 'DDD'
                    WHEN cemislearnerid IS NOT NULL THEN 'CEMIS'
                    ELSE NULL
                END, personid, idnumberinvalidreason, idnumberinvalid, cemislearnerid
                FROM t$learners AS l;
            Raise Notice 'Update import';
            GET DIAGNOSTICS var_RowCount = ROW_COUNT;
            var_Message := 'Completed. ' || CAST (var_RowCount AS VARCHAR(20)) || ' rows imported.';
            Raise Notice '%',var_Message;
            CALL  dbo.pupdatedataimport(par_DataImportID := var_DataImportID, par_DataImportStatus := 'Success', par_Message := var_Message, return_code => pupdatedataimport$ReturnCode);
            Raise Notice 'called update importID';
            DROP TABLE IF EXISTS newdataimportidtable$plearnersregisterfromsourcelearner;
        END IF;
        
        SELECT
            COUNT(*)
            INTO var_RowCount
            FROM dbo.learner
            WHERE dataimportid = var_DataImportID;
        -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %. Argument: %', '50000', 0, 0, '%d Learner(s) to register (DataImportID=%I64d)', var_RowCount, var_DataImportID USING ERRCODE = '50000';
        Raise Notice '%', var_TranCountOnEntry;
        IF par_Silent = 0 THEN
        Raise Notice 'var_DataImportID %', var_DataImportID;
        Raise Notice 'Row count %', var_RowCount;
            OPEN p_refcur FOR
            SELECT
                var_DataImportID AS Dataimportid, var_RowCount AS LearnerCount;
        END IF;

        --IF var_TranCountOnEntry = 0 THEN
        Raise Notice 'commiting';
            COMMIT;
       -- END IF;
        EXCEPTION
            WHEN OTHERS THEN
                
                IF var_TranCountOnEntry = 0 THEN
                    ROLLBACK;
                    RAISE Exception 'Error';
                END IF;
                
                
    END;


  
END;
$BODY$;
