         SUM(NumberOfJobs) AS TotalNumberOfJobs,
         MIN(EarliestEndTime) as EarliestEndTime,
         MAX(LatestEndTime) as LatestEndTime
-    FROM NormalisedSuperSummaries summary 
+    FROM HybridSuperSummaries summary 
     INNER JOIN Sites site ON site.id=summary.SiteID
     INNER JOIN VOs vo ON summary.VOID = vo.id
     INNER JOIN DNs dn ON summary.GlobalUserNameID = dn.id

);

-- -----------------------------------------------------------------------------
-- NormalisedSuperSummaries
DROP TABLE IF EXISTS NormalisedSuperSummaries;
CREATE TABLE NormalisedSuperSummaries (
  UpdateTime TIMESTAMP,
  SiteID INT NOT NULL,                  -- Foreign key
  Month INT NOT NULL,
  Year INT NOT NULL,
  GlobalUserNameID INT NOT NULL,        -- Foreign key
  VOID INT NOT NULL,                    -- Foreign key
  VOGroupID INT NOT NULL,               -- Foreign key
  VORoleID INT NOT NULL,                -- Foreign key
  SubmitHostId INT NOT NULL,
  Infrastructure VARCHAR(20),
  NodeCount INT NOT NULL,
  Processors INT NOT NULL,
  EarliestEndTime DATETIME,

  NormalisedCpuDuration BIGINT UNSIGNED NOT NULL,
  NumberOfJobs BIGINT UNSIGNED NOT NULL,

  PRIMARY KEY (SiteID, Month, Year, GlobalUserNameID, VOID, VORoleID, VOGroupID,
               SubmitHostId, Infrastructure, ServiceLevelType, ServiceLevel,
               NodeCount, Processors)
);


DROP PROCEDURE IF EXISTS SummariseJobs;

DELIMITER //
CREATE PROCEDURE SummariseJobs()
BEGIN
  REPLACE INTO HybridSuperSummaries(SiteID, Month, Year, GlobalUserNameID, VOID,
    VOGroupID, VORoleID, SubmitHostID, Infrastructure, ServiceLevelType,
    ServiceLevel, NodeCount, Processors, EarliestEndTime, LatestEndTime,
    WallDuration, CpuDuration, NormalisedWallDuration, NormalisedCpuDuration,
    NumberOfJobs)
  SELECT SiteID,
         EndMonth AS Month,
         EndYear AS Year,
         GlobalUserNameID,
         VOID,
         VOGroupID,
         VORoleID,
         SubmitHostID,
         InfrastructureType,
         ServiceLevelType,
         ServiceLevel,
         NodeCount,
         Processors,
         MIN(EndTime) AS EarliestEndTime,
         MAX(EndTime) AS LatestEndTime,
         SUM(WallDuration) AS SumWCT,
         SUM(CpuDuration) AS SumCPU,
         ROUND(SUM(IF(WallDuration > 0, WallDuration, 0) * IF(ServiceLevelType = "HEPSPEC", ServiceLevel, ServiceLevel / 250))) AS NormSumWCT,
         ROUND(SUM(IF(CpuDuration > 0, CpuDuration, 0) * IF(ServiceLevelType = "HEPSPEC", ServiceLevel, ServiceLevel / 250))) AS NormSumCPU,
         COUNT(*) AS Njobs
  FROM JobRecords
  GROUP BY SiteID, VOID, GlobalUserNameID, VOGroupID, VORoleID, EndYear,
           EndMonth, InfrastructureType, SubmitHostID, ServiceLevelType,
           ServiceLevel, NodeCount, Processors;
END //
DELIMITER ;


DROP PROCEDURE IF EXISTS NormaliseSummaries;

DELIMITER //
CREATE PROCEDURE NormaliseSummaries()
BEGIN
  REPLACE INTO HybridSuperSummaries(SiteID, Month, Year, GlobalUserNameID, VOID,
    VOGroupID, VORoleID, SubmitHostID, Infrastructure, ServiceLevelType,
    ServiceLevel, NodeCount, Processors, EarliestEndTime, LatestEndTime,
    WallDuration, CpuDuration, NormalisedWallDuration, NormalisedCpuDuration,
    NumberOfJobs)
  SELECT SiteID,
         Month,
         Year,
         GlobalUserNameID,
         VOID,
         VOGroupID,
         VORoleID,
         SubmitHostID,
         InfrastructureType,
         ServiceLevelType,
         ServiceLevel,
         NodeCount,
         Processors,
         EarliestEndTime,
         LatestEndTime,
         WallDuration,
         CpuDuration,
         ROUND(IF(WallDuration > 0, WallDuration, 0) * IF(ServiceLevelType = "HEPSPEC", ServiceLevel, ServiceLevel / 250)) AS NormSumWCT,
         ROUND(IF(CpuDuration > 0, CpuDuration, 0) * IF(ServiceLevelType = "HEPSPEC", ServiceLevel, ServiceLevel / 250)) AS NormSumCPU,
         NumberOfJobs
  FROM Summaries
END //
DELIMITER ;


DROP PROCEDURE IF EXISTS CopyNormalisedSummaries;

DELIMITER //
CREATE PROCEDURE CopyNormalisedSummaries()
BEGIN
  REPLACE INTO HybridSuperSummaries(SiteID, Month, Year, GlobalUserNameID, VOID,
    VOGroupID, VORoleID, SubmitHostID, Infrastructure, NodeCount, Processors,
    EarliestEndTime, LatestEndTime, WallDuration, CpuDuration,
    NormalisedWallDuration, NormalisedCpuDuration, NumberOfJobs)
  SELECT SiteID,
        Month,
        Year,
        GlobalUserNameID,
        VOID,
        VOGroupID,
        VORoleID,
        SubmitHostID,
        Infrastructure,
        NodeCount,
        Processors,
        EarliestEndTime,
        LatestEndTime,
        WallDuration,
        CpuDuration,
        NormalisedWallDuration,
        NormalisedCpuDuration,
        NumberOfJobs
  FROM NormalisedSummaries;
END //
DELIMITER ;

