-- FUNCTION: reporting.pvalidatecemislearner(bigint, numeric, numeric, integer)

-- DROP FUNCTION IF EXISTS reporting.pvalidatecemislearner(bigint, numeric, numeric, integer);

CREATE OR REPLACE FUNCTION reporting.pvalidatecemislearner(
	par_dataimportid bigint,
	par_selectissues numeric DEFAULT 0,
	par_updatedataimport numeric DEFAULT 1,
	par_debugmode integer DEFAULT 0)
    RETURNS TABLE(loglevel character varying, dataid integer, code integer, description character varying, data character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
/*
SELECT
    DataContractDescription             'FileName',
    CAST(DataContractDataDate AS date)  'FileDate',
    DataImportID                        'DataImportID'
FROM ClickSTAGING.dbo.DataImportDataContract
WHERE DataSourceTypeID = 14 AND DataImportStatus = 'Success'
ORDER BY DataContractDataDate DESC
*/
DECLARE
    var_Date DATE;
    var_Year SMALLINT;
    var_RowCount INTEGER;
    var_ValidationProblemCount INTEGER;
BEGIN
    /*
    New Learners                    Where a new CEMIS Learner ID appears in the file.
    
    Possible duplicate learners     Where two learners (same school, same first name, same last name, same grade) exists, regardless of the CEMIS id
    
    Learner Left                    If a CEMIS learner id does not appear in the extract and the CEMIS learner id was in previous extracts
    
    Learner in 2 schools            Where the same CEMIS learner ID appears in two schools.
    
    Learner movement                If a CEMIS learner id was previously in another school
    (and the learner has been removed from that school due to a learner left condition) but is now linked to a new school.
    
    Learner Rejoins                 Where a CEMIS learner id was previously marked as leaving the school (disappeared from the file)
    but is now in the school again (reappeared in the file)
    */
    /*
    [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 temp tables */
	
	DROP TABLE IF EXISTS t$datavalidationignore;
	DROP TABLE IF EXISTS t$dataimportlog;
 	DROP TABLE IF EXISTS t$fileschoolgradelearnercount;
 	DROP TABLE IF EXISTS t$dataimport;   
 	DROP TABLE IF EXISTS t$activeschoolgrade;
 	DROP TABLE IF EXISTS t$previousfileschoolgradelearnercount;
 
    SELECT
        CAST (di.datacontractdatadate AS DATE), date_part('year', di.datacontractdatadate)
        INTO var_Date, var_Year
        FROM dbo.dataimportdatacontract AS di
        WHERE di.dataimportid = par_DataImportID;
    CREATE TEMPORARY TABLE t$datavalidationignore
    (code INTEGER,
        dataid INTEGER,
        description VARCHAR(255),
        ignore NUMERIC(1, 0) DEFAULT (1));
RAISE NOTICE 'CREATE TEMPORARY TABLE t$datavalidationignore';     

    INSERT INTO t$datavalidationignore (code, dataid, description, ignore)
    SELECT
        dv.code, dv.dataid,
        CASE
            WHEN STRPOS(dv.description, ':') >= 1 THEN LEFT(dv.description, STRPOS(dv.description, ':'))
            ELSE dv.description
        END, COALESCE(ignore::int, 1)
        FROM dbo.datavalidationignore dv
        WHERE dv.dataimportid = par_DataImportID;
RAISE NOTICE 'INSERT INTO t$datavalidationignore';        

    CREATE UNIQUE INDEX ix_t$datavalidationignore_ix ON t$datavalidationignore
        (code, dataid, description, ignore);

    CREATE TEMPORARY TABLE t$dataimportlog
    (dataimportid BIGINT NOT NULL,
        logtimestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT (clock_timestamp()),
        loglevelid SMALLINT NOT NULL,
        dataid INTEGER NOT NULL,
        code INTEGER NULL,
        description VARCHAR(255) NULL,
        data VARCHAR(2048) NULL);
RAISE NOTICE 'CREATE TEMPORARY TABLE t$dataimportlog'; 

    CREATE TEMPORARY TABLE t$fileschoolgradelearnercount
    AS
    SELECT
        l.emiscode,
        CASE
            WHEN LOWER(l.grade) = LOWER('PRE-R') THEN - 1
            WHEN LOWER(l.grade) = LOWER('R') THEN 0
            ELSE CAST (l.grade AS SMALLINT)
        END AS gradeid, COUNT(*) AS learnercount, CAST (NULL AS INTEGER) AS schoolid
        FROM dbo.learner AS l
        WHERE l.dataimportid = par_DataImportID AND LOWER(l.grade) <> LOWER('PRE-R')
        GROUP BY l.emiscode,
        CASE
            WHEN LOWER(l.grade) = LOWER('PRE-R') THEN - 1
            WHEN LOWER(l.grade) = LOWER('R') THEN 0
            ELSE CAST (l.grade AS SMALLINT)
        END;
RAISE NOTICE 'CREATE TEMPORARY TABLE t$fileschoolgradelearnercount'; 

    UPDATE t$fileschoolgradelearnercount AS x
    SET schoolid = s.schoolid
    FROM dbo.school_dw AS s
        WHERE LOWER(x.emiscode) = LOWER(s.emiscode);
RAISE NOTICE 'UPDATE t$fileschoolgradelearnercount'; 

    CREATE INDEX ix_t$fileschoolgradelearnercount_ix1 ON t$fileschoolgradelearnercount
        (schoolid, gradeid);
    /* get previous successful dataimport per emiscode */

    CREATE TEMPORARY TABLE t$dataimport
    (emiscode VARCHAR(9),
        dataimportid BIGINT,
        date DATE,
        filename VARCHAR(250),
        seq INTEGER);
RAISE NOTICE 'CREATE TEMPORARY TABLE t$dataimport'; 
RAISE NOTICE 'var_Date: %',var_Date;
RAISE NOTICE 'var_Year: %',var_Year;
-- return; -- Testing
    WITH emiscodes (emiscode)
    AS (SELECT DISTINCT
        emiscode
        FROM dbo.learner
        WHERE dataimportid = par_DataImportID), emiscodeprevimport (emiscode, dataimportid, datacontractdatadate, datacontractdescription, seq)
    AS (SELECT
        e.emiscode, di.dataimportid, di.datacontractdatadate, di.datacontractdescription, ROW_NUMBER() OVER (PARTITION BY e.emiscode ORDER BY di.datacontractdatadate DESC) AS seq
        FROM emiscodes AS e
--         CROSS JOIN LATERAL (SELECT
--             di.dataimportid
--             FROM dbo.dataimportdatacontract AS di
--             JOIN dbo.learner AS l
--                 ON di.dataimportid = l.dataimportid
--             WHERE di.datasourcetypeid IN (14, 18) AND di.dataimportstatus = 'Success' AND di.datacontractdatadate <= var_Date AND date_part('year', di.datacontractdatadate) = var_Year AND di.dataimportid <> par_DataImportID AND l.emiscode = e.emiscode AND l.grade <> 'PRE-R'
--             ORDER BY di.datacontractdatadate DESC NULLS FIRST
--             LIMIT 1) AS dix
		JOIN  (SELECT DISTINCT
            l.emiscode,di.datacontractdatadate,di.dataimportid
            FROM dbo.dataimportdatacontract AS di
            JOIN dbo.learner AS l
                ON di.dataimportid = l.dataimportid
            WHERE di.datasourcetypeid IN (14, 18) AND di.dataimportstatus = 'Success' AND di.datacontractdatadate <= var_Date AND date_part('year', di.datacontractdatadate) = var_Year AND di.dataimportid <> par_DataImportID  AND l.grade <> 'PRE-R'
            ORDER BY di.datacontractdatadate DESC NULLS FIRST, di.dataimportid
            ) AS dix ON dix.emiscode = e.emiscode
        JOIN dbo.dataimportdatacontract AS di
            ON di.dataimportid = dix.dataimportid)
    INSERT INTO t$dataimport (emiscode, dataimportid, date, filename, seq)
    SELECT
        emiscode, dataimportid, datacontractdatadate, datacontractdescription, seq
        FROM emiscodeprevimport
        ORDER BY emiscode NULLS FIRST, seq NULLS FIRST;
RAISE NOTICE 'INSERT INTO t$dataimport';         

    /* get active grades per school */
    CREATE TEMPORARY TABLE t$activeschoolgrade
    AS
    SELECT DISTINCT
        s.schoolid, tg.gradeid
        FROM (SELECT DISTINCT
            schoolid
            FROM t$fileschoolgradelearnercount) AS s
        JOIN dbo.vwschoolenvironmentprogramme_dw AS sep
            ON s.schoolid = sep.schoolid
        JOIN dbo.programmeenvironmenthastargetgrade_dw AS tg
            ON sep.programmeenvironmentid = tg.programmeenvironmentid AND var_Date >= tg.activefrom AND tg.learnermanagementlevelid = 1
        WHERE sep.implementationstatusid IN (1, 13, 4) AND sep.programmeid IN (4, 8) AND tg.gradeid <> - 1;
RAISE NOTICE 'CREATE TEMPORARY TABLE t$activeschoolgrade';

    CREATE INDEX ix_t$activeschoolgrade_ix1 ON t$activeschoolgrade
        (schoolid, gradeid)
    /* get grades received in previous imports */
    ;

    CREATE TEMPORARY TABLE t$previousfileschoolgradelearnercount
    AS
    WITH previousfileschoolgradelearnercount (emiscode, gradeid, learnercount)
    AS (SELECT
        l.emiscode,
        CASE
            WHEN l.grade = 'PRE-R' THEN - 1
            WHEN l.grade = 'R' THEN 0
            ELSE CAST (l.grade AS SMALLINT)
        END AS gradeid, COUNT(*) AS learnercount
        FROM t$dataimport AS di
        JOIN dbo.learner AS l
            ON di.dataimportid = l.dataimportid AND di.emiscode = l.emiscode
        WHERE di.seq = 1 AND l.grade <> 'PRE-R'
        GROUP BY l.emiscode,
        CASE
            WHEN l.grade = 'PRE-R' THEN - 1
            WHEN l.grade = 'R' THEN 0
            ELSE CAST (l.grade AS SMALLINT)
        END)
    SELECT
        s.schoolid, s.emiscode, pf.gradeid, pf.learnercount
        FROM previousfileschoolgradelearnercount AS pf
        JOIN dbo.school_dw AS s
            ON LOWER(pf.emiscode) = LOWER(s.emiscode);
RAISE NOTICE 'CREATE TEMPORARY TABLE t$previousfileschoolgradelearnercount';

    /* update active grades to include grades received. */
    /* INSERT #ActiveSchoolGrade(SchoolID, GradeID) */
    /* SELECT PF.SchoolID, PF.GradeID */
    /* FROM #PreviousFileSchoolGradeLearnerCount PF */
    /* LEFT JOIN #ActiveSchoolGrade ASG ON PF.SchoolID = ASG.SchoolID AND PF.GradeID = ASG.GradeID */
    /* WHERE ASG.SchoolID IS NULL; */
    var_ValidationProblemCount = 0;
    /* Check that received grades that are active */
    
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    INSERT INTO t$DataImportLog(DataImportID, LogLevelID, DataID, Code, Description, Data)
        SELECT par_DataImportID, 2, A.SchoolID, 20001, concat('%s (%s) missing grade %d', S.Name, S.EMISCode, A.GradeID), null
        FROM t$ActiveSchoolGrade A
        JOIN dbo.school_dw S ON A.SchoolID = S.SchoolID
        LEFT JOIN t$FileSchoolGradeLearnerCount LC ON A.SchoolID = LC.SchoolID AND A.GradeID = LC.GradeID
        WHERE LC.GradeID IS NULL;
	*/
    
	INSERT INTO t$DataImportLog(DataImportID, LogLevelID, DataID, Code, Description, Data)
		SELECT par_DataImportID, 2, A.SchoolID, 20001, concat('%s (%s) missing grade %d', S.Name, S.EMISCode, A.GradeID), null
		FROM t$ActiveSchoolGrade A
		JOIN dbo.school_dw S ON A.SchoolID = S.SchoolID
		LEFT JOIN t$FileSchoolGradeLearnerCount LC ON A.SchoolID = LC.SchoolID AND A.GradeID = LC.GradeID
		WHERE LC.GradeID IS NULL;
RAISE NOTICE 'INSERT INTO t$DataImportLog1';

    GET DIAGNOSTICS var_RowCount = ROW_COUNT;
    -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, 'Grades missing from schools: %d', var_RowCount USING ERRCODE = '50000';
    /* SELECT @Date, * FROM #ActiveSchoolGrade WHERE SchoolID = 31 */
    /* Grade count similiar to previous */
    
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    INSERT #DataImportLog(DataImportID, LogLevelID, DataID, Code, [Description], [Data])
        SELECT @DataImportID, 2, S.SchoolID, 20002, FORMATMESSAGE('%s (%s) grade %d difference: %d vs %d ', S.[Name], S.EMISCode, C.GradeID, C.LearnerCount, P.LearnerCount), null
        FROM #FileSchoolGradeLearnerCount C
        JOIN #ActiveSchoolGrade A ON C.SchoolID = A.SchoolID AND C.GradeID = A.GradeID
        JOIN ClickDW.dbo.school_dw S ON C.SchoolID = S.SchoolID
        JOIN #PreviousFileSchoolGradeLearnerCount P ON C.EMISCode = P.EMISCode AND C.GradeID = P.GradeID
        WHERE (CAST(ABS(C.LearnerCount - P.LearnerCount) AS float) / NULLIF(CAST(C.LearnerCount AS float),0)) >= 0.1
    */

	INSERT INTO t$DataImportLog(DataImportID, LogLevelID, DataID, Code, Description, Data)
		SELECT par_DataImportID, 2, S.SchoolID, 20002, CONCAT('%s (%s) grade %d difference: %d vs %d ', S.Name, S.EMISCode, C.GradeID, C.LearnerCount, P.LearnerCount), null
		FROM t$FileSchoolGradeLearnerCount C
		JOIN t$ActiveSchoolGrade A ON C.SchoolID = A.SchoolID AND C.GradeID = A.GradeID
		JOIN dbo.school_dw S ON C.SchoolID = S.SchoolID
		JOIN t$PreviousFileSchoolGradeLearnerCount P ON C.EMISCode = P.EMISCode AND C.GradeID = P.GradeID
		WHERE (CAST(ABS(C.LearnerCount - P.LearnerCount) AS float) / COALESCE(CAST(C.LearnerCount AS float),0)) >= 0.1;	
RAISE NOTICE 'INSERT INTO t$DataImportLog2';

    GET DIAGNOSTICS var_RowCount = ROW_COUNT;
    -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, 'Grade difference too high: %d', var_RowCount USING ERRCODE = '50000';
    /* Grade Incomplete  (25) */

    IF EXISTS (SELECT
        *
        FROM t$datavalidationignore) THEN
        DELETE FROM t$dataimportlog AS DIL
        USING t$datavalidationignore AS dvi
            WHERE dil.code = dvi.code AND dil.dataid = dvi.dataid AND
            CASE
                WHEN STRPOS(dil.description, ':') >= 1 THEN LEFT(dil.description, STRPOS(dil.description, ':'))
                ELSE dil.description
            END = dvi.description AND dvi.ignore = 1;
RAISE NOTICE 'DELETE FROM t$dataimportlog';

    END IF;
    var_ValidationProblemCount = (SELECT
        COUNT(*)
        FROM t$dataimportlog);

    INSERT INTO dbo.dataimportlog (dataimportid, logtimestamp, loglevelid, dataid, code, description, data)
    SELECT
        di.dataimportid, di.logtimestamp, di.loglevelid, di.dataid, di.code, di.description, di.data
        FROM t$dataimportlog di;
RAISE NOTICE 'INSERT INTO dbo.dataimportlog';        

    IF par_selectissues = 1 THEN 
        RETURN QUERY
        SELECT
		    --'efddfb'::character varying AS loglevel, 1 as dataid,  2 as code, 'description'::character varying as description, 'data'::character varying as data;
            ll.code AS loglevel, l.dataid, l.code, l.description, COALESCE(l.data, '') AS data
            FROM t$dataimportlog AS l
            JOIN dbo.loglevel AS ll
                ON l.loglevelid = ll.loglevelid
            ORDER BY l.loglevelid NULLS FIRST, l.dataid NULLS FIRST;
RAISE NOTICE 'RETURN QUERY';        

    END IF;

    IF par_UpdateDataImport = 1 THEN
        IF var_ValidationProblemCount > 0 THEN
            /*
            [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
            UPDATE DI SET
                            DataImportStatus = 'Validation Failed',
                            [Message] = FORMATMESSAGE('%d validation issues. See DataImportLog.', @ValidationProblemCount)
                        FROM DataImport DI
                        WHERE DI.DataImportID = @DataImportID
            */
			UPDATE dbo.DataImport 
			SET DataImportStatus = 'Validation Failed',
				Message = concat('%d validation issues. See DataImportLog.', var_ValidationProblemCount)
			FROM dbo.DataImport DI
			WHERE DI.DataImportID = par_DataImportID;	
RAISE NOTICE 'UPDATE DataImport';        

            BEGIN
            END;
        END IF;
    END IF;
    
    DROP TABLE IF EXISTS t$datavalidationignore;
  
    /*
    
    Temporary table must be removed before end of the function.
    */
  
    
    DROP TABLE IF EXISTS t$dataimportlog;
 
    /*
    
    Temporary table must be removed before end of the function.
    */
    
    
    DROP TABLE IF EXISTS t$fileschoolgradelearnercount;
   
    /*
    
    Temporary table must be removed before end of the function.
    */
   
    
    DROP TABLE IF EXISTS t$dataimport;
   
    /*
    
    Temporary table must be removed before end of the function.
    */
   
    
    DROP TABLE IF EXISTS t$activeschoolgrade;
   
    /*
    
    Temporary table must be removed before end of the function.
    */
    
    
    DROP TABLE IF EXISTS t$previousfileschoolgradelearnercount;
   
    /*
    
    Temporary table must be removed before end of the function.
    */
END;
$BODY$;

ALTER FUNCTION reporting.pvalidatecemislearner(bigint, numeric, numeric, integer)
    OWNER TO postgres;
