DROP TRIGGER IF EXISTS MoveToTemporary;

DELIMITER /
CREATE TRIGGER MoveToTemporary
BEFORE UPDATE ON CurrentDiskUsage
FOR EACH ROW BEGIN
    INSERT INTO DiskUsageTemporary(node, role, stage, sizes, counts, lifetime, dirname, status, last_update)
    VALUES (OLD.node, OLD.role, OLD.stage, OLD.sizes, OLD.counts, OLD.lifetime, OLD.dirname, OLD.status, NOW());
END;
/
DELIMITER ;

DROP PROCEDURE IF EXISTS UpdateDaily;

DELIMITER /
CREATE PROCEDURE UpdateDaily()
BEGIN
   -- Compute statistics for the previous days and store them into DiskUsageHistoryDaily
   INSERT INTO DiskUsageHistoryDaily(node, role, stage, lifetime, dirname, day, sizes, counts, status)
   SELECT node, role, stage, lifetime, dirname, DATE(last_update), AVG(sizes), AVG(counts), status 
     FROM DiskUsageTemporary WHERE DATE(last_update)<DATE(NOW()) 
     GROUP BY node, role, stage, lifetime, dirname, DATE(last_update), status;
   DELETE FROM DiskUsageTemporary WHERE DATE(last_update)<DATE(NOW());
   UPDATE Misc SET lastHistoryUpdatedDay = DAYOFYEAR(NOW());
END;
/
DELIMITER ;

-- -------------------------------

DROP PROCEDURE IF EXISTS UpdateWeekly;

DELIMITER /
CREATE PROCEDURE UpdateWeekly()
BEGIN
  -- DELETE entries from two weeks ago
  -- DELETE FROM DiskUsageHistoryDaily WHERE WEEKOFYEAR(day) < WEEKOFYEAR(NOW())-2;
  -- Compute statistics for the previous weeks and store them into DiskUsageHistoryWeek
  INSERT INTO DiskUsageHistoryWeekly(node, role, stage, lifetime, dirname, week, sizes, counts, status)
    SELECT node, role, stage, lifetime, dirname, STR_TO_DATE(CONCAT(YEAR(day), WEEKOFYEAR(day), ' Sunday'), '%X%V %W') AS my_date, AVG(sizes), AVG(counts), status
    FROM DiskUsageHistoryDaily
    WHERE WEEKOFYEAR(day) = WEEKOFYEAR(NOW())-1
    GROUP BY node, role, stage, lifetime, dirname, WEEKOFYEAR(day), status;
  DELETE FROM DiskUsageHistoryDaily WHERE WEEKOFYEAR(day) < WEEKOFYEAR(NOW())-1;
  UPDATE Misc SET lastHistoryUpdatedWeek = WEEKOFYEAR(NOW());
END;
/
DELIMITER ;
