-- PROCEDURE: internal.pupdatepersonprogrammeactivity(smallint, integer)

-- DROP PROCEDURE IF EXISTS internal.pupdatepersonprogrammeactivity(smallint, integer);

CREATE OR REPLACE PROCEDURE internal.pupdatepersonprogrammeactivity(
	par_programmeid smallint,
	par_debugmode integer DEFAULT 0)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    var_Sql TEXT;
    var_Table TEXT;
    var_MovementCol TEXT;
    var_MovementTypeCol TEXT;
    var_ToTValidFrom DATE;
BEGIN
   DROP TABLE IF EXISTS t$php;
   DROP TABLE IF EXISTS t$persontemp;
   DROP TABLE IF EXISTS t$persontempmain;
   DROP TABLE IF EXISTS t$persontemparchive;
   
    IF par_ProgrammeID = 4 THEN
		var_ToTValidFrom := CAST ((SELECT
            value::date
            FROM dbo.systemparameter
            WHERE LOWER(category) = LOWER('Reading Eggs') AND LOWER(subcategory) = LOWER('Time on Task') AND LOWER(name) = LOWER('Valid From')) AS DATE);
        var_Table := 'dbo.LearnerDataReadingEggs';
        var_MovementCol := 'ABS(CASE WHEN D."date" >= '''||var_ToTValidFrom||'''::date THEN (COALESCE(D."re week time (sec)",0) + COALESCE(D."rex week time (sec)",0) + COALESCE(D."fp week time (sec)",0) ) ELSE (COALESCE(D.re_golden_eggs,0) - COALESCE(D1.re_golden_eggs,0)) END)';
        var_MovementTypeCol := 'CASE WHEN D."date" >= '''||var_ToTValidFrom||'''::date THEN 1 ELSE 0 END';
        
    ELSE
        IF par_ProgrammeID = 6 THEN
            var_Table := 'dbo.LearnerDataMathseeds';
            var_MovementCol := 'COALESCE(D."week total time (sec)",0)';
            var_MovementTypeCol := '1';
            var_ToTValidFrom := CAST ((SELECT
                value
                FROM dbo.systemparameter
                WHERE LOWER(category) = LOWER('MathSeeds') AND LOWER(subcategory) = LOWER('Time on Task') AND LOWER(name) = LOWER('Valid From')) AS DATE);
        END IF;
    END IF;

    BEGIN
        RAISE NOTICE 'var_Table: % ',var_Table;
        IF var_Table IS NOT NULL THEN
		
		RAISE NOTICE 'DELETE FROM dbo.PersonProgrammeActivity';
		
		DELETE FROM dbo.PersonProgrammeActivity WHERE ProgrammeID = par_ProgrammeID;
		
		RAISE NOTICE 'CREATE TEMP TABLE t$PHP';
		
		CREATE TEMP TABLE t$PHP 
		AS
		SELECT
			PP.PersonID,
			MIN(CASE WHEN PP.registerdate IS NULL THEN '9999-12-31' ELSE PP.registerdate END) registerdate,
			CAST(MAX(PP.SysEndTime) AS date) deregisterdate
		FROM
		(
			SELECT PersonID, RegisterDate, SysStartTime, SysEndTime	FROM dbo.PersonHasProgramme WHERE ProgrammeID = par_ProgrammeID
			UNION
			SELECT PersonID, RegisterDate, SysStartTime, SysEndTime	FROM dbo.PersonHasProgrammeHistory WHERE ProgrammeID = par_ProgrammeID
		) PP
		GROUP BY PP.PersonID;

		CREATE INDEX IX1 ON t$PHP(PersonID);
		
		RAISE NOTICE 'CREATE TEMP TABLE t$persontemp';
		
					
		var_Sql = 'CREATE TEMP TABLE t$persontemp
			AS
			SELECT T.PersonID, MIN(T."date") FirstAppearDate, MAX(CASE WHEN T."teacher name" IS NULL THEN NULL ELSE T."date" END) LastAppearDate
			FROM {TABLE} T
			LEFT JOIN t$PHP P ON P.PersonID = T.PersonID
			WHERE P.PersonID IS NULL
			GROUP BY T.PersonID;';

		var_Sql := REPLACE(var_Sql, '{TABLE}', var_Table);
		
		EXECUTE var_sql;
		RAISE NOTICE 'INSERT INTO t$PHP';
		
		INSERT INTO t$PHP(PersonID, RegisterDate, DeregisterDate)
		SELECT PersonID, FirstAppearDate, CASE WHEN TRUNC(DATE_PART('day', NOW() - LastAppearDate)/7) > 1 THEN LastAppearDate ELSE '9999-12-31' END
		FROM t$persontemp X;
		
		RAISE NOTICE 'CREATE TEMP TABLE t$persontempmain';
		
		var_Sql = 'CREATE TEMP TABLE t$persontempmain
			AS
			SELECT 
					D.PersonID, 
					CASE WHEN MIN(D.PrevDate) < MIN(D."date") THEN MIN(D.PrevDate) ELSE MIN(D."date") END FirstAppearDate,
					MAX(D."date") LastAppearDate,
					MIN(CASE WHEN D.Movement > 0 THEN D."date" ELSE NULL END) FirstMovementDate,
					MAX(CASE WHEN D.Movement > 0 THEN D."date" ELSE NULL END) LastMovementDate
				FROM
				(
					SELECT
						D."date",
						D1."date" PrevDate,
						D.PersonID, 
						{MOVEMENTCOL} Movement,
						{MOVEMENTTYPECOL} MovementType
					FROM {TABLE} D
					LEFT JOIN {TABLE} D1 ON D.PersonID = D1.PersonID AND D."date" = D1."date" + (''7 days'')::INTERVAL
				) D
				GROUP BY D.PersonID;';
				
		var_Sql := REPLACE(var_Sql, '{TABLE}', var_Table);
        var_Sql := REPLACE(var_Sql, '{MOVEMENTCOL}', var_MovementCol);
        var_Sql := REPLACE(var_Sql, '{MOVEMENTTYPECOL}', var_MovementTypeCol);
		
		EXECUTE var_sql;
		
		RAISE NOTICE 'CREATE TEMP TABLE t$persontemparchive';
		
		var_Sql = 'CREATE TEMP TABLE t$persontemparchive
			AS
			SELECT 
					D.PersonID, 
					CASE WHEN MIN(D.PrevDate) < MIN(D."date") THEN MIN(D.PrevDate) ELSE MIN(D."date") END FirstAppearDate,
					MAX(D."date") LastAppearDate,
					MIN(CASE WHEN D.Movement > 0 THEN D."date" ELSE NULL END) FirstMovementDate,
					MAX(CASE WHEN D.Movement > 0 THEN D."date" ELSE NULL END) LastMovementDate
				FROM
				(
					SELECT
						D."date",
						D1."date" PrevDate,
						D.PersonID, 
						{MOVEMENTCOL} Movement,
						{MOVEMENTTYPECOL} MovementType
					FROM {TABLE}Archive D
					JOIN dbo.PersonHistory PH ON D.PersonID = PH.PersonID
					LEFT JOIN {TABLE}Archive D1 ON D.PersonID = D1.PersonID AND D."date" = D1."date" + (''7 days'')::INTERVAL
					WHERE PH.SysStartTime = PH.SysEndTime
						AND D."date" >= '''||var_ToTValidFrom||'''::date
				) D
				GROUP BY D.PersonID;';
				
		var_Sql := REPLACE(var_Sql, '{TABLE}', var_Table);
        var_Sql := REPLACE(var_Sql, '{MOVEMENTCOL}', var_MovementCol);
        var_Sql := REPLACE(var_Sql, '{MOVEMENTTYPECOL}', var_MovementTypeCol);
		
		EXECUTE var_sql;
		
		RAISE NOTICE 'INSERT INTO dbo.PersonProgrammeActivity';
		
		INSERT INTO dbo.PersonProgrammeActivity AS target (PersonID, ProgrammeID, FirstAppearDate, LastAppearDate, FirstMovementDate, LastMovementDate, RegisterDate, DeregisterDate)
			SELECT PersonID, ProgrammeID, FirstAppearDate, LastAppearDate, FirstMovementDate, LastMovementDate, RegisterDate, DeregisterDate
		FROM
		(
			SELECT 
				P.PersonID,
				par_ProgrammeID ProgrammeID,
				NULLIF(CASE WHEN P.RegisterDate IS NULL THEN CASE WHEN M.PersonID IS NOT NULL THEN M.FirstAppearDate ELSE A.FirstAppearDate END ELSE P.RegisterDate END,'9999-12-31') RegisterDate,
				CASE WHEN TRUNC(DATE_PART('day', NOW() - M.LastAppearDate)/7) >= 1 THEN M.LastAppearDate ELSE CAST(NULLIF(P.DeregisterDate,'9999-12-31') AS date) END DeregisterDate,
				CASE WHEN M.PersonID IS NOT NULL THEN M.FirstAppearDate   ELSE A.FirstAppearDate   END FirstAppearDate,
				CASE WHEN M.PersonID IS NOT NULL THEN M.LastAppearDate    ELSE A.LastAppearDate    END LastAppearDate,
				CASE WHEN M.PersonID IS NOT NULL THEN M.FirstMovementDate ELSE A.FirstMovementDate END FirstMovementDate,
				CASE WHEN M.PersonID IS NOT NULL THEN M.LastMovementDate  ELSE A.LastMovementDate  END LastMovementDate
			FROM t$PHP P
			LEFT JOIN t$persontempmain M ON P.PersonID = M.PersonID
			LEFT JOIN t$persontemparchive A ON P.PersonID = A.PersonID
		) AS source				
		ON CONFLICT (PersonID,ProgrammeID) DO UPDATE SET
				FirstAppearDate   = excluded.FirstAppearDAte,
				LastAppearDate    = excluded.LastAppearDate,
				FirstMovementDate = excluded.FirstMovementDate,
				LastMovementDate  = excluded.LastMovementDate,
				RegisterDate      = excluded.RegisterDate,
				DeregisterDate    = excluded.DeregisterDate;
		RAISE NOTICE 'INSERT INTO dbo.PersonProgrammeActivity done';
            var_Sql := '	
	
		INSERT INTO dbo.PersonProgrammeActivity AS target (PersonID, ProgrammeID, FirstAppearDate, LastAppearDate, FirstMovementDate, LastMovementDate, RegisterDate, DeregisterDate)
			SELECT PersonID, ProgrammeID, FirstAppearDate, LastAppearDate, FirstMovementDate, LastMovementDate, RegisterDate, DeregisterDate
		FROM
		(
			SELECT 
				P.PersonID,
				'||par_ProgrammeID||' ProgrammeID,
				NULLIF(CASE WHEN P.RegisterDate IS NULL THEN CASE WHEN M.PersonID IS NOT NULL THEN M.FirstAppearDate ELSE A.FirstAppearDate END ELSE P.RegisterDate END,''9999-12-31'') RegisterDate,
				CASE WHEN TRUNC(DATE_PART(''day'', NOW() - M.LastAppearDate)/7) >= 1 THEN M.LastAppearDate ELSE CAST(NULLIF(P.DeregisterDate,''9999-12-31'') AS date) END DeregisterDate,
				CASE WHEN M.PersonID IS NOT NULL THEN M.FirstAppearDate   ELSE A.FirstAppearDate   END FirstAppearDate,
				CASE WHEN M.PersonID IS NOT NULL THEN M.LastAppearDate    ELSE A.LastAppearDate    END LastAppearDate,
				CASE WHEN M.PersonID IS NOT NULL THEN M.FirstMovementDate ELSE A.FirstMovementDate END FirstMovementDate,
				CASE WHEN M.PersonID IS NOT NULL THEN M.LastMovementDate  ELSE A.LastMovementDate  END LastMovementDate
			FROM t$PHP P
			LEFT JOIN
			(
				SELECT 
					D.PersonID, 
					CASE WHEN MIN(D.PrevDate) < MIN(D."date") THEN MIN(D.PrevDate) ELSE MIN(D."date") END FirstAppearDate,
					MAX(D."date") LastAppearDate,
					MIN(CASE WHEN D.Movement > 0 THEN D."date" ELSE NULL END) FirstMovementDate,
					MAX(CASE WHEN D.Movement > 0 THEN D."date" ELSE NULL END) LastMovementDate
				FROM
				(
					SELECT
						D."date",
						D1."date" PrevDate,
						D.PersonID, 
						{MOVEMENTCOL} Movement,
						{MOVEMENTTYPECOL} MovementType
					FROM {TABLE} D
					LEFT JOIN {TABLE} D1 ON D.PersonID = D1.PersonID AND D."date" = D1."date" + (''7 days'')::INTERVAL
				) D
				GROUP BY D.PersonID	
			) M ON P.PersonID = M.PersonID
			LEFT JOIN
			(
				SELECT 
					D.PersonID, 
					CASE WHEN MIN(D.PrevDate) < MIN(D."date") THEN MIN(D.PrevDate) ELSE MIN(D."date") END FirstAppearDate,
					MAX(D."date") LastAppearDate,
					MIN(CASE WHEN D.Movement > 0 THEN D."date" ELSE NULL END) FirstMovementDate,
					MAX(CASE WHEN D.Movement > 0 THEN D."date" ELSE NULL END) LastMovementDate
				FROM
				(
					SELECT
						D."date",
						D1."date" PrevDate,
						D.PersonID, 
						{MOVEMENTCOL} Movement,
						{MOVEMENTTYPECOL} MovementType
					FROM {TABLE}Archive D
					JOIN dbo.PersonHistory PH ON D.PersonID = PH.PersonID
					LEFT JOIN {TABLE}Archive D1 ON D.PersonID = D1.PersonID AND D."date" = D1."date" + (''7 days'')::INTERVAL
					WHERE PH.SysStartTime = PH.SysEndTime
						AND D."date" >= '''||var_ToTValidFrom||'''::date
				) D
				GROUP BY D.PersonID
			) A ON P.PersonID = A.PersonID
		) AS source				
		ON CONFLICT (PersonID,ProgrammeID) DO UPDATE SET
				FirstAppearDate   = excluded.FirstAppearDAte,
				LastAppearDate    = excluded.LastAppearDate,
				FirstMovementDate = excluded.FirstMovementDate,
				LastMovementDate  = excluded.LastMovementDate,
				RegisterDate      = excluded.RegisterDate,
				DeregisterDate    = excluded.DeregisterDate;

		';
/* MERGE NOT SEPORTED IN POSTGRES */
-- 		MERGE INTO dbo.PersonProgrammeActivity AS target
-- 		USING
-- 		(
-- 			SELECT 
-- 				P.PersonID,
-- 				'||par_ProgrammeID||' ProgrammeID,
-- 				NULLIF(CASE WHEN P.RegisterDate IS NULL THEN CASE WHEN M.PersonID IS NOT NULL THEN M.FirstAppearDate ELSE A.FirstAppearDate END ELSE P.RegisterDate END,''9999-12-31'') RegisterDate,
-- 				CASE WHEN DATEDIFF(week,M.LastAppearDate,GETDATE()) >= 1 THEN M.LastAppearDate ELSE CAST(NULLIF(P.DeregisterDate,''9999-12-31'') AS date) END DeregisterDate,
-- 				CASE WHEN M.PersonID IS NOT NULL THEN M.FirstAppearDate   ELSE A.FirstAppearDate   END FirstAppearDate,
-- 				CASE WHEN M.PersonID IS NOT NULL THEN M.LastAppearDate    ELSE A.LastAppearDate    END LastAppearDate,
-- 				CASE WHEN M.PersonID IS NOT NULL THEN M.FirstMovementDate ELSE A.FirstMovementDate END FirstMovementDate,
-- 				CASE WHEN M.PersonID IS NOT NULL THEN M.LastMovementDate  ELSE A.LastMovementDate  END LastMovementDate
-- 			FROM t$PHP P
-- 			LEFT JOIN
-- 			(
-- 				SELECT 
-- 					D.PersonID, 
-- 					CASE WHEN MIN(D.PrevDate) < MIN(D."Date") THEN MIN(D.PrevDate) ELSE MIN(D."Date") END FirstAppearDate,
-- 					MAX(D."Date") LastAppearDate,
-- 					MIN(CASE WHEN D.Movement > 0 THEN D."Date" ELSE NULL END) FirstMovementDate,
-- 					MAX(CASE WHEN D.Movement > 0 THEN D."Date" ELSE NULL END) LastMovementDate
-- 				FROM
-- 				(
-- 					SELECT
-- 						D."Date",
-- 						D1."Date" PrevDate,
-- 						D.PersonID, 
-- 						{MOVEMENTCOL} Movement,
-- 						{MOVEMENTTYPECOL} MovementType
-- 					FROM {TABLE} D
-- 					LEFT JOIN {TABLE} D1 ON D.PersonID = D1.PersonID AND D."Date" = D1."Date" + (''7 days'')::INTERVAL
-- 				) D
-- 				GROUP BY D.PersonID	
-- 			) M ON P.PersonID = M.PersonID
-- 			LEFT JOIN
-- 			(
-- 				SELECT 
-- 					D.PersonID, 
-- 					CASE WHEN MIN(D.PrevDate) < MIN(D."Date") THEN MIN(D.PrevDate) ELSE MIN(D."Date") END FirstAppearDate,
-- 					MAX(D."Date") LastAppearDate,
-- 					MIN(CASE WHEN D.Movement > 0 THEN D."Date" ELSE NULL END) FirstMovementDate,
-- 					MAX(CASE WHEN D.Movement > 0 THEN D."Date" ELSE NULL END) LastMovementDate
-- 				FROM
-- 				(
-- 					SELECT
-- 						D."Date",
-- 						D1."Date" PrevDate,
-- 						D.PersonID, 
-- 						{MOVEMENTCOL} Movement,
-- 						{MOVEMENTTYPECOL} MovementType
-- 					FROM {TABLE}Archive D
-- 					JOIN dbo.PersonHistory PH ON D.PersonID = PH.PersonID
-- 					LEFT JOIN {TABLE}Archive D1 ON D.PersonID = D1.PersonID AND D."Date" = D1."Date" + (''7 days'')::INTERVAL
-- 					WHERE PH.SysStartTime = PH.SysEndTime
-- 						AND D."Date" >= var_ToTValidFrom
-- 				) D
-- 				GROUP BY D.PersonID
-- 			) A ON P.PersonID = A.PersonID
-- 		) AS source
		
-- 		ON target.PersonID = source.PersonID AND target.ProgrammeID = source.ProgrammeID
-- 		WHEN NOT MATCHED THEN
-- 			INSERT (PersonID, ProgrammeID, FirstAppearDate, LastAppearDate, FirstMovementDate, LastMovementDate, RegisterDate, DeregisterDate)
-- 			VALUES (PersonID, ProgrammeID, FirstAppearDate, LastAppearDate, FirstMovementDate, LastMovementDate, RegisterDate, DeregisterDate)
-- 		WHEN MATCHED THEN
-- 			UPDATE SET
-- 				FirstAppearDate   = source.FirstAppearDAte,
-- 				LastAppearDate    = source.LastAppearDate,
-- 				FirstMovementDate = source.FirstMovementDate,
-- 				LastMovementDate  = source.LastMovementDate,
-- 				RegisterDate      = source.RegisterDate,
-- 				DeregisterDate    = source.DeregisterDate;

-- 		';
            var_Sql := REPLACE(var_Sql, '{TABLE}', var_Table);
            var_Sql := REPLACE(var_Sql, '{MOVEMENTCOL}', var_MovementCol);
            var_Sql := REPLACE(var_Sql, '{MOVEMENTTYPECOL}', var_MovementTypeCol);
            /* PRINT @Sql */
-- 			raise notice 'var_sql: %',var_sql;
        ELSE

			DELETE FROM dbo.PersonProgrammeActivity WHERE ProgrammeID = par_programmeid;

			CREATE TEMP TABLE t$PHP AS
			SELECT
				PP.PersonID,
				MIN(CASE WHEN PP.RegisterDate IS NULL THEN '9999-12-31' ELSE PP.RegisterDate END) RegisterDate,
				CAST(MAX(PP.SysEndTime) AS date) DeregisterDate		
			FROM
			(
				SELECT PersonID, RegisterDate, SysStartTime, SysEndTime	FROM dbo.PersonHasProgramme WHERE ProgrammeID = par_ProgrammeID
				UNION
				SELECT PersonID, RegisterDate, SysStartTime, SysEndTime	FROM dbo.PersonHasProgrammeHistory WHERE ProgrammeID = par_ProgrammeID
			) PP
			GROUP BY PP.PersonID;

			CREATE INDEX IX1 ON t$PHP(PersonID);

			INSERT INTO dbo.PersonProgrammeActivity AS target (PersonID, ProgrammeID, RegisterDate, DeregisterDate)
			SELECT PersonID, ProgrammeID, RegisterDate, DeregisterDate
			FROM
			(
				SELECT 
					P.PersonID,
					par_ProgrammeID ProgrammeID,
					NULLIF(P.RegisterDate,'9999-12-31') RegisterDate,
					NULLIF(P.DeregisterDate,'9999-12-31') DeregisterDate
				FROM t$PHP P
			) AS source	

			ON CONFLICT (PersonID,ProgrammeID) DO UPDATE dbo.PersonProgrammeActivity  SET
					RegisterDate      = excluded.RegisterDate,
					DeregisterDate    = excluded.DeregisterDate
			;
	
       END IF;

        IF par_DebugMode >= 1 THEN
            RAISE NOTICE '%', var_Sql;
        END IF;

        IF par_DebugMode = 0 THEN
            /*
            "7672 - Severity CRITICAL - Automatic conversion of this command is not supported. Perform a manual conversion."
            EXEC sp_executesql @stmt=@Sql, @params=N'@ProgrammeID smallint, @ToTValidFrom date', @ProgrammeID=@ProgrammeID, @ToTValidFrom=@ToTValidFrom
            */
-- 			Execute var_Sql;
        END IF;
		
--         COMMIT;
       
--         EXCEPTION
--             WHEN OTHERS THEN
--                     ROLLBACK;
               
    END;
END;
$BODY$;
