SET search_path TO eiaodw;

DROP AGGREGATE IF EXISTS array_accum (anyelement);
CREATE AGGREGATE array_accum (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);


SELECT timenow() AS Time, 'Computing DpageTestRatio' AS Task;

INSERT INTO matviews.ScenarioTestRatio(ScenarioID, BarrierComputationID, TestRunID, TestRatio)
SELECT  SBScenarioID, SBBarrierComputationID, SBTestRunID, 
         SUM(CASE WHEN ResultID>0 THEN 1 ELSE 0 END)::float/SUM(1)::float AS pageTestRatio
 FROM    ScenarioCoverage NATURAL JOIN
         Subject NATURAL JOIN
         TestResult NATURAL JOIN
         BarrierComputationVersion RIGHT JOIN
         (SELECT DISTINCT ScenarioID AS SBScenarioID, BarrierComputationID AS SBBarrierComputationID, TestRunID AS SBTestRunID 
          FROM Scenario, BarrierComputationVersion
          WHERE TestRunID = 5087
         ) AS foo 
 ON ScenarioID=foo.SBScenarioID AND BarrierComputationID=foo.SBBarrierComputationID AND TestRunID=foo.SBTestRunID
 GROUP BY SBTestRunID, SBBarrierComputationID, SBScenarioID;



SELECT timenow() as time, 'Computing DsiteTestRatio' as task;

INSERT INTO matviews.SiteTestRatio(SiteID, BarrierComputationID, TestRunID, TestRatio)
SELECT SBSiteID, SBBarrierComputationID, SBTestRunID, 
        SUM(CASE WHEN ResultID>0 THEN 1 ELSE 0 END)::float/SUM(1)::float AS siteTestRatio
 FROM   Subject NATURAL JOIN 
        TestResult NATURAL JOIN 
        BarrierComputationVersion RIGHT JOIN
        (SELECT DISTINCT SiteID AS SBSiteID, BarrierComputationID AS SBBarrierComputationID, TestRunID AS SBTestRunID 
         FROM Scenario, BarrierComputationVersion
         WHERE TestRunID = 5087
        ) AS foo 
 ON SiteID=foo.SBSiteID AND BarrierComputationID=foo.SBBarrierComputationID AND TestRunID=foo.SBTestRunID
 GROUP BY SBTestRunID, SBBarrierComputationID, SBSiteID;



SELECT timenow() as time, 'Computing DpageContent' as task;

-- Only find counts for those pairs of inclusions types and mime types that actually exist,
-- i.e., avoid all those that just give 0
-- 
INSERT INTO matviews.ScenarioContent(ScenarioID, MimeTypeID, InclusionTypeID, TestRunID, Result)
SELECT ScenarioID, MimeTypeID, InclusionTypeID, TestRunID, COUNT(*) FROM
TechnologyFinding NATURAL JOIN Subject NATURAL JOIN ScenarioCoverage
WHERE TestRunID = 5087
GROUP BY ScenarioID, MimeTypeID, InclusionTypeID, TestRunID;



SELECT timenow() as time, 'Computing DsiteContent' as task;

-- Only find counts for those pairs of inclusions types and mime types that actually exist,
-- i.e., avoid all those that just give 0
-- 
INSERT INTO matviews.SiteContent(SiteID, MimeTypeID, InclusionTypeID, TestRunID, Result)
SELECT SiteID, MimeTypeID, InclusionTypeID, TestRunID, occs::float / scncnt
FROM
  (SELECT SiteID, TestRunID, COUNT(ScenarioID) AS scncnt
   FROM Scenario 
   WHERE TestRunID = 5087 
   GROUP BY SiteID, TestRunID) AS foo
NATURAL JOIN
  (SELECT SiteID, MimeTypeID, InclusionTypeID, TestRunID, SUM(Result) AS occs
   FROM matviews.ScenarioContent NATURAL JOIN Scenario
   WHERE TestRunID = 5087
   GROUP BY SiteID, MimeTypeID, InclusionTypeID, TestRunID) AS bar;




-- Fill GroupCache from here --

SELECT timenow() as time, 'Finding different SiteIDs' as task;

INSERT INTO matviews.GroupCache(GroupType, GroupCode, TestRunID, "Group")
SELECT 'all', 'all', TestRunID, array_accum(DISTINCT SiteID) FROM Scenario
WHERE TestRunID = 5087
GROUP BY TestRunID;


SELECT timenow() as time, 'Finding SiteIDs in NACE groups' as task;

-- Find for each NACE code all the SiteIDs belonging to it. 
INSERT INTO matviews.GroupCache(GroupType, GroupCode, TestRunID, "Group")
SELECT 'nace' AS name, NaceCode, TestRunID, array_accum(DISTINCT SiteID)
FROM datastaging.Nace NATURAL JOIN
     datastaging.Site NATURAL JOIN 
     datastaging.Resource NATURAL JOIN 
     datastaging.ResourceVersion 
     WHERE TestRunID = 5087 
GROUP BY name, NaceCode, TestRunID;
-- alternatively use the following if all those with empty results {}
-- should also be found
-- SELECT 'nace', NaceCode, 5087, 
--         ARRAY(SELECT DISTINCT SiteID 
--              FROM datastaging.Site as s NATURAL JOIN 
--                   datastaging.Resource NATURAL JOIN 
--                   datastaging.ResourceVersion 
--              WHERE TestRunID = 5087 
--              AND s.NaceID = Nace.NaceID
--        ) 
-- FROM datastaging.Nace;


SELECT timenow() as time, 'Finding SiteIDs with unknown NUTS data' as task;

-- Find for each NUTS3 code all the SiteIDs belonging to it.
INSERT INTO matviews.GroupCache(GroupType, GroupCode, TestRunID, "Group")
SELECT 'nuts' AS name , 'Unknown' as unknc3, TestRunID, array_accum(DISTINCT SiteID) 
FROM (SELECT DISTINCT SiteID, TestRunID FROM
   datastaging.NutsLevel3 NATURAL JOIN 
   datastaging.Site NATURAL JOIN 
   datastaging.Resource NATURAL JOIN
   datastaging.ResourceVersion WHERE TestRunID = 5087 AND NutsLevel3ID = -1) AS foo
GROUP BY name, unknc3, TestRunID;



SELECT timenow() as time, 'Finding SiteIDs in NUTS3 groups' as task;

-- Find for each NUTS3 code all the SiteIDs belonging to it.
INSERT INTO matviews.GroupCache(GroupType, GroupCode, TestRunID, "Group")
SELECT 'nuts' AS name , Nuts3Code, TestRunID, array_accum(DISTINCT SiteID) 
FROM (SELECT DISTINCT Nuts3Code, SiteID, TestRunID FROM
   datastaging.NutsLevel3 NATURAL JOIN 
   datastaging.Site NATURAL JOIN 
   datastaging.Resource NATURAL JOIN
   datastaging.ResourceVersion WHERE TestRunID = 5087 AND Nuts3Code IS NOT NULL) AS foo
GROUP BY name, Nuts3Code, TestRunID;


SELECT timenow() as time, 'Finding SiteIDs in NUTS2 groups' as task;

INSERT INTO matviews.GroupCache(GroupType, GroupCode, TestRunID, "Group")
SELECT 'nuts' AS name , Nuts2Code, TestRunID, array_accum(DISTINCT SiteID) 
FROM (SELECT DISTINCT Nuts2Code, SiteID, TestRunID FROM
   datastaging.NutsLevel2 NATURAL JOIN
   datastaging.NutsLevel3 NATURAL JOIN 
   datastaging.Site NATURAL JOIN 
   datastaging.Resource NATURAL JOIN
   datastaging.ResourceVersion WHERE TestRunID = 5087 AND Nuts2Code IS NOT NULL) AS foo
GROUP BY name, Nuts2Code, TestRunID;


SELECT timenow() as time, 'Finding SiteIDs in NUTS1 groups' as task;

INSERT INTO matviews.GroupCache(GroupType, GroupCode, TestRunID, "Group")
SELECT 'nuts' AS name , Nuts1Code, TestRunID, array_accum(DISTINCT SiteID) 
FROM (SELECT DISTINCT Nuts1Code, SiteID, TestRunID FROM
   datastaging.NutsLevel1 NATURAL JOIN
   datastaging.NutsLevel2 NATURAL JOIN
   datastaging.NutsLevel3 NATURAL JOIN 
   datastaging.Site NATURAL JOIN 
   datastaging.Resource NATURAL JOIN
   datastaging.ResourceVersion WHERE TestRunID = 5087 AND Nuts1Code IS NOT NULL) AS foo
GROUP BY name, Nuts1Code, TestRunID;



SELECT timenow() as time, 'Finding SiteIDs in countries' as task;

INSERT INTO matviews.GroupCache(GroupType, GroupCode, TestRunID, "Group")
SELECT 'nuts' AS name , CountryAbbreviation, TestRunID, array_accum(DISTINCT SiteID) 
FROM (SELECT DISTINCT CountryAbbreviation, SiteID, TestRunID FROM
   datastaging.Country NATURAL JOIN
   datastaging.NutsLevel1 NATURAL JOIN
   datastaging.NutsLevel2 NATURAL JOIN
   datastaging.NutsLevel3 NATURAL JOIN 
   datastaging.Site NATURAL JOIN 
   datastaging.Resource NATURAL JOIN
   datastaging.ResourceVersion WHERE TestRunID = 5087 AND CountryAbbreviation IS NOT NULL) AS foo
GROUP BY name, CountryAbbreviation, TestRunID;


SELECT timenow() as time, 'Finding SiteIDs in EU groups' as task;

INSERT INTO matviews.GroupCache(GroupType, GroupCode, TestRunID, "Group")
SELECT 'nuts' AS name , EUStatus, TestRunID, array_accum(DISTINCT SiteID) 
FROM (SELECT DISTINCT SiteID, TestRunID,
                      (CASE WHEN EUMember = 'EU member' THEN 'EU' 
                            WHEN EUMember = 'Applicant country' THEN 'AC'
                            WHEN EUMember = 'Outside EU' THEN 'NOTEU'
                            ELSE NULL
                       END) AS EUStatus FROM
   datastaging.Country NATURAL JOIN
   datastaging.NutsLevel1 NATURAL JOIN
   datastaging.NutsLevel2 NATURAL JOIN
   datastaging.NutsLevel3 NATURAL JOIN 
   datastaging.Site NATURAL JOIN 
   datastaging.Resource NATURAL JOIN
   datastaging.ResourceVersion WHERE TestRunID = 5087 AND EUMember != 'Unknown') AS foo
GROUP BY name, EUStatus, TestRunID;


-- There are two ways to access this from StoredProcedures.sql so we have to
-- duplicate the data but with other codes... Further we have to add those
-- with 'Uknown' membership.
INSERT INTO matviews.GroupCache(GroupType, GroupCode, TestRunID, "Group")
SELECT 'eumembership' AS name, 
        (CASE WHEN GroupCode = 'EU' THEN 'EU member'
              WHEN GroupCode = 'AC' THEN 'Applicant country'
              WHEN GroupCode = 'NOTEU' THEN 'Outside EU'
         END),
        TestRunID, "Group" 
FROM matviews.GroupCache WHERE TestRunID = 5087 AND GroupType = 'nuts'
AND GroupCode IN ('EU', 'AC', 'NOTEU');

INSERT INTO matviews.GroupCache(GroupType, GroupCode, TestRunID, "Group")
SELECT 'eumembership' AS name , EUMember, TestRunID, array_accum(DISTINCT SiteID) 
FROM (SELECT DISTINCT SiteID, TestRunID, EUMember FROM
   datastaging.Country NATURAL JOIN
   datastaging.NutsLevel1 NATURAL JOIN
   datastaging.NutsLevel2 NATURAL JOIN
   datastaging.NutsLevel3 NATURAL JOIN 
   datastaging.Site NATURAL JOIN 
   datastaging.Resource NATURAL JOIN
   datastaging.ResourceVersion WHERE TestRunID = 5087 AND EUMember = 'Unknown') AS foo
GROUP BY name, EUMember, TestRunID;


SELECT timenow() as time, 'Done filling the materialized views!' as status;