CREATE OR REPLACE  VIEW dbo.vwschoolenvironmentprogramme (schoolid, emiscode, name, entityid, locationdemographicid, 
	giscoordinates, addressid, schoolfeestructureid, schoolquintileid, englishlanguagelevelid, schooltypeid, 
	schoolstatusid, schoolenvironmentid, environmentid, environmenttypeid, environmentenglishlanguagelevelid, 
	programmeenvironmentid, programmeid, implementationpartnerid, implementationstatusid, programmecode, 
	implementationtypeid, programmemanagementlevelid, programmeusername, programmepassword, organisationid, 
	learnerpassword, activefromgradeid, activetogradeid, activegradecount, activegradeselfmanagedcount, 
	activegradesallselfmanaged, implementationstatusdate, implementationstatuspipelinedate, 
	implementationstatusplanneddate, implementationstatusactivedate, implementationstatusdeactivateddate, launchdate, 
	facilitatorcount) AS
SELECT
    s.schoolid, s.emiscode, s.name, s.entityid, s.locationdemographicid, s.giscoordinates, s.addressid, s.schoolfeestructureid, s.schoolquintileid, s.englishlanguagelevelid, s.schooltypeid, s.schoolstatusid, se.schoolenvironmentid, e.environmentid, e.environmenttypeid, e.englishlanguagelevelid AS environmentenglishlanguagelevelid, pe.programmeenvironmentid, pe.programmeid, pe.implementationpartnerid, pe.implementationstatusid, pe.programmecode, pe.implementationtypeid, pe.programmemanagementlevelid, pe.programmeusername, pe.programmepassword, pe.organisationid, pe.learnerpassword, g.fromgradeid AS activefromgradeid, g.togradeid AS activetogradeid, COALESCE(g.gradecount, 0) AS activegradecount, COALESCE(g.selfmanagedgradecount, 0) AS activegradeselfmanagedcount, aws_sqlserver_ext.tomsbit(CASE
        WHEN g.gradecount = g.selfmanagedgradecount THEN 1
        ELSE 0
    END) AS activegradesallselfmanaged, pe.sysstarttime AS implementationstatusdate, pepipeline.sysstarttime AS implementationstatuspipelinedate, peplanned.sysstarttime AS implementationstatusplanneddate, peactive.sysstarttime AS implementationstatusactivedate, pedeactive.sysstarttime AS implementationstatusdeactivateddate, pe.launchdate, e.facilitatorcount
    FROM dbo.school AS s
    JOIN dbo.schoolenvironment AS se
        ON s.schoolid = se.schoolid
    JOIN dbo.environment AS e
        ON se.environmentid = e.environmentid
    JOIN dbo.programmeenvironment AS pe
        ON e.environmentid = pe.environmentid
    LEFT OUTER JOIN (SELECT
        programmeenvironmentid, MIN(gradeid) AS fromgradeid, MAX(gradeid) AS togradeid, COUNT(*) AS gradecount, SUM(CASE
            WHEN t.learnermanagementlevelid = 2 THEN 1
            ELSE 0
        END) AS selfmanagedgradecount
        FROM dbo.programmeenvironmenthastargetgrade AS t
        WHERE clock_timestamp() >= t.activefrom
        GROUP BY t.programmeenvironmentid) AS g
        ON pe.programmeenvironmentid = g.programmeenvironmentid

    LEFT OUTER JOIN (
          SELECT DISTINCT * FROM (
              SELECT c.*
                       FROM dbo.programmeenvironment c
                       WHERE c.implementationstatusid = 1
              UNION
              SELECT d.*
                       FROM dbo.programmeenvironmenthistory d
                       WHERE d.implementationstatusid = 1
            ) e
            ORDER BY e.sysendtime DESC
        ) pepipeline on pepipeline.programmeenvironmentid = pe.programmeenvironmentid AND pepipeline.implementationstatusid = 1

/*                
    CROSS JOIN LATERAL (SELECT
        *
        FROM (SELECT
            *
            FROM dbo.programmeenvironment AS c
            WHERE c.programmeenvironmentid = pe.programmeenvironmentid AND c.implementationstatusid = 1
        UNION
        SELECT
            *
            FROM dbo.programmeenvironmenthistory AS c
            WHERE c.programmeenvironmentid = pe.programmeenvironmentid AND c.implementationstatusid = 1) AS c
        ORDER BY c.sysendtime DESC NULLS FIRST
        LIMIT 1) AS pepipeline
*/

    LEFT OUTER JOIN (
          SELECT DISTINCT * FROM (
              SELECT c.*
                       FROM dbo.programmeenvironment c
                       WHERE c.implementationstatusid = 13
              UNION
              SELECT d.*
                       FROM dbo.programmeenvironmenthistory d
                       WHERE d.implementationstatusid = 13
            ) e
            ORDER BY e.sysendtime DESC
        ) peplanned on peplanned.programmeenvironmentid = pe.programmeenvironmentid AND peplanned.implementationstatusid = 13

/*
    CROSS JOIN LATERAL (SELECT
        *
        FROM (SELECT
            *
            FROM dbo.programmeenvironment AS c
            WHERE c.programmeenvironmentid = pe.programmeenvironmentid AND c.implementationstatusid = 13
        UNION
        SELECT
            *
            FROM dbo.programmeenvironmenthistory AS c
            WHERE c.programmeenvironmentid = pe.programmeenvironmentid AND c.implementationstatusid = 13) AS c
        ORDER BY c.sysendtime DESC NULLS FIRST
        LIMIT 1) AS peplanned
*/

    LEFT OUTER JOIN (
          SELECT DISTINCT * FROM (
              SELECT c.*
                       FROM dbo.programmeenvironment c
                       WHERE c.implementationstatusid = 4
              UNION
              SELECT d.*
                       FROM dbo.programmeenvironmenthistory d
                       WHERE d.implementationstatusid = 4
            ) e
            ORDER BY e.sysendtime DESC
        ) peactive on peactive.programmeenvironmentid = pe.programmeenvironmentid AND peactive.implementationstatusid = 4

/*
    CROSS JOIN LATERAL (SELECT
        *
        FROM (SELECT
            *
            FROM dbo.programmeenvironment AS c
            WHERE c.programmeenvironmentid = pe.programmeenvironmentid AND c.implementationstatusid = 4
        UNION
        SELECT
            *
            FROM dbo.programmeenvironmenthistory AS c
            WHERE c.programmeenvironmentid = pe.programmeenvironmentid AND c.implementationstatusid = 4) AS c
        ORDER BY c.sysendtime DESC NULLS FIRST
        LIMIT 1) AS peactive
*/

    LEFT OUTER JOIN (
          SELECT DISTINCT * FROM (
              SELECT c.*
                       FROM dbo.programmeenvironment c
                       WHERE c.implementationstatusid = 2
              UNION
              SELECT d.*
                       FROM dbo.programmeenvironmenthistory d
                       WHERE d.implementationstatusid = 2
            ) e
            ORDER BY e.sysendtime DESC
        ) pedeactive on pedeactive.programmeenvironmentid = pe.programmeenvironmentid AND pedeactive.implementationstatusid = 2

/*
    CROSS JOIN LATERAL (SELECT
        *
        FROM (SELECT
            *
            FROM dbo.programmeenvironment AS c
            WHERE c.programmeenvironmentid = pe.programmeenvironmentid AND c.implementationstatusid = 2
        UNION
        SELECT
            *
            FROM dbo.programmeenvironmenthistory AS c
            WHERE c.programmeenvironmentid = pe.programmeenvironmentid AND c.implementationstatusid = 2) AS c
        ORDER BY c.sysendtime DESC NULLS FIRST
        LIMIT 1) AS pedeactive;
*/        