CREATE OR REPLACE PROCEDURE internal.pupdateschooldatasource()
AS 
$BODY$
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
    */
    CREATE TEMPORARY TABLE t$dataimport
    (dataimportid BIGINT,
        datadate DATE,
        datasourcetypeid INTEGER,
        description VARCHAR(255));
    WITH dataimport (dataimportid, datasourcetypeid, datacontractdatadate, datacontractdescription)
    AS (SELECT
        dataimportid, datasourcetypeid, datacontractdatadate, datacontractdescription
        FROM dbo.dataimportdatacontract_staging AS di
        WHERE di.datasourcetypeid IN (8, 14, 18) AND
        /* Learner - DDD, Learner - CEMIS, Learner - CEMIS (Simple) */
        di.dataimportstatus = 'Success'), maxdataimport (dataimportid)
    AS (SELECT
        MAX(dataimportid)
        FROM dataimport
        GROUP BY datacontractdescription)
    INSERT INTO t$dataimport (dataimportid, datasourcetypeid, datadate, description)
    SELECT
        di.dataimportid, di.datasourcetypeid, di.datacontractdatadate, di.datacontractdescription
        FROM dataimport AS di
        JOIN maxdataimport AS dix
            ON di.dataimportid = dix.dataimportid
        ORDER BY di.dataimportid NULLS FIRST;
RAISE NOTICE 'CREATE TEMPORARY TABLE t$dataimport';

    CREATE TEMPORARY TABLE t$schoolsubmissiondate
    (schoolid INTEGER NOT NULL,
        datasourcetypeid SMALLINT NOT NULL,
        submissiondate DATE NOT NULL);
    INSERT INTO t$schoolsubmissiondate (schoolid, datasourcetypeid, submissiondate)
    SELECT
        s.schoolid,
        CASE
            WHEN x.datasourcetypeid = 8 THEN 1
            WHEN x.datasourcetypeid = 14 THEN 2
            WHEN x.datasourcetypeid = 18 THEN 2
            ELSE NULL
        END, x.lastsubmissiondate
        FROM (SELECT
            l.emiscode, di.datasourcetypeid, l.lastsubmissiondate
            FROM t$dataimport AS di
            JOIN dbo.learner_staging AS l
                ON di.dataimportid = l.dataimportid
            GROUP BY l.emiscode, di.datasourcetypeid, l.lastsubmissiondate) AS x
        JOIN dbo.school AS s
            ON LOWER(x.emiscode) = LOWER(s.emiscode);
RAISE NOTICE 'CREATE TEMPORARY TABLE t$schoolsubmissiondate';

    -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, 'Source Data: %d rows', "var_@ROWCOUNT" USING ERRCODE = '50000';
    DELETE FROM dbo.schoolhasdatasource
    USING (SELECT
        schoolid, datasourcetypeid, MIN(submissiondate) AS minsubmissiondate
        FROM t$schoolsubmissiondate
        GROUP BY schoolid, datasourcetypeid) AS tf, t$schoolsubmissiondate AS s, dbo.schoolhasdatasource AS t
        WHERE s.schoolid IS NULL AND (t.schoolid = s.schoolid AND t.datasourcetypeid = s.datasourcetypeid AND t.submissiondate = s.submissiondate AND (t.schoolid = tf.schoolid AND t.datasourcetypeid = tf.datasourcetypeid AND t.submissiondate >= tf.minsubmissiondate));
RAISE NOTICE 'DELETE FROM dbo.schoolhasdatasource';

    -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, 'SchoolHasDataSource: %d deleted', "var_@ROWCOUNT" USING ERRCODE = '50000';
    /* UPDATE T SET */
    /* FROM SchoolHasDataSource T */
    /* JOIN #SchoolSubmissionDate S ON T.SchoolID = S.SchoolID AND T.DataSourceTypeID = S.DataSourceTypeID AND T.SubmissionDate = S.SubmissionDate; */
    /* -- RAISERROR('SchoolHasDataSource: %d updated', 0, 0, @@ROWCOUNT) WITH NOWAIT; */
    INSERT INTO dbo.schoolhasdatasource (schoolid, datasourcetypeid, submissiondate)
    SELECT
        s.schoolid, s.datasourcetypeid, s.submissiondate
        FROM t$schoolsubmissiondate AS s
        LEFT OUTER JOIN dbo.schoolhasdatasource AS t
            ON t.schoolid = s.schoolid AND t.datasourcetypeid = s.datasourcetypeid AND t.submissiondate = s.submissiondate
        WHERE t.schoolid IS NULL;
RAISE NOTICE 'INSERT INTO dbo.schoolhasdatasource';

        
    -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, 'SchoolHasDataSource: %d inserted', "var_@ROWCOUNT" USING ERRCODE = '50000';
    /*
    
    DROP TABLE IF EXISTS t$dataimport;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
    /*
    
    DROP TABLE IF EXISTS t$schoolsubmissiondate;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
END;
$BODY$
LANGUAGE plpgsql;