-- PROCEDURE: internal.pupdatelearnerfromsourcelearnerfirstusabledata(bigint)

-- DROP PROCEDURE IF EXISTS internal.pupdatelearnerfromsourcelearnerfirstusabledata(bigint);

CREATE OR REPLACE PROCEDURE internal.pupdatelearnerfromsourcelearnerfirstusabledata(
	par_sourcedataimportid bigint)
LANGUAGE 'plpgsql'
AS $BODY$
/* , */
/* @Schools			varchar(2000) = 'ALL' */
DECLARE
    var_TranCountOnEntry INTEGER
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
    = @@TRANCOUNT
    */;
    var_DataImportDescription VARCHAR(250) DEFAULT 'First Usable Data Update: ' || CAST (par_SourceDataImportID AS VARCHAR(30));
    var_DataContractDataDate TIMESTAMP WITHOUT TIME ZONE;
    var_Year SMALLINT;
	var_DataImportID BIGINT DEFAULT 0;
/*
    var_DataImportID BIGINT DEFAULT (SELECT
        dataimportid
        FROM t$dataimportidtable
        LIMIT 1);
*/

/*
    pupdatelearnerfromsourcelearner$ReturnCode INTEGER;
    pupdatedataimport$ReturnCode INTEGER;
*/    

BEGIN
   
   Drop table if exists t$data;
    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
            di.datacontractdatadate, 
            date_part('year', di.datacontractdatadate)
        INTO var_DataContractDataDate, 
            var_Year
        FROM dbo.dataimportdatacontract_staging AS di
        WHERE di.dataimportid = par_SourceDataImportID;
RAISE NOTICE 'var_DataContractDataDate: %', var_DataContractDataDate;   
RAISE NOTICE 'var_Year: %', var_Year;   

        CREATE TEMPORARY TABLE t$firstusabledataschools
        AS
        SELECT
            sl.schoolid
            FROM (SELECT DISTINCT
                schoolid
                FROM dbo.sourcelearner AS x
                WHERE year = var_Year) AS sl
            JOIN internal.processingschool AS yfud
                ON sl.schoolid = yfud.schoolid AND LOWER(yfud.item) = LOWER('Year First Usable Data') AND date_part('year', yfud.timestamp) = var_Year
            LEFT OUTER JOIN internal.processingschool AS yfudu
                ON sl.schoolid = yfudu.schoolid AND LOWER(yfudu.item) = LOWER('Year First Usable Data - Update') AND date_part('year', yfudu.timestamp) = var_Year
            -- WHERE yfudu.schoolid IS NULL
			;
            
        -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, 'New First Usable Data Schools: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
RAISE NOTICE 't$firstusabledataschools: %', var_Year;   

        IF EXISTS (SELECT * FROM t$firstusabledataschools) THEN
			RAISE NOTICE 'Exists';
            CREATE TEMPORARY TABLE t$dataimportidtable
                (dataimportid BIGINT,
                description VARCHAR(1024));

            -- select * from internal.pnewdataimportid(par_SourceDataImportID := NULL, var_DataImportDescription, null, 0);
            INSERT INTO t$dataimportidtable
            SELECT * FROM internal.pnewdataimportid(par_SourceDataImportID, var_DataImportDescription, null, 0);
            -- RAISE 'Error %, severity %, state % was raised. Message: %.', '50000', 0, 0, 'EXEC internal.pUpdateLearnerFromSourceLearner' USING ERRCODE = '50000';
			
			var_DataImportID = (SELECT			
					dataimportid
					FROM t$dataimportidtable
					LIMIT 1);
		
RAISE NOTICE 't$dataimportidtable: %', var_Year;   
RAISE NOTICE 'var_DataImportID: %', var_DataImportID;   
RAISE NOTICE 'var_Year: %', var_Year;   

            CALL internal.pupdatelearnerfromsourcelearner(var_DataImportID, var_Year, 0, 0, 1);

RAISE NOTICE 'CALL internal.pupdatelearnerfromsourcelearner';   

            /* , @Schools=@Schools */
            PERFORM * from internal.pupdatedataimport(var_DataImportID, 'Success');
RAISE NOTICE 'CALL internal.pupdatedataimport';   

            IF EXISTS (SELECT
                *
                FROM internal.dataimportlog
                WHERE dataimportid = var_DataImportID AND LOWER(description) LIKE LOWER('PersonHasProgramme%')) THEN
				RAISE NOTICE 'select internal.dataimportlog';   
                CALL internal.pupdatepersonprogrammeactivity(4::smallint);
                CALL internal.pupdatepersonprogrammeactivity(8::smallint);
            END IF;
            DROP TABLE IF EXISTS t$dataimportidtable;
        END IF;

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