DROP VIEW portsbuildstats_statistics_category_portname_pkgname;
DROP VIEW portsbuildstats_statistics_category_portname_flavor_pkgname;
DROP VIEW portsbuildstats_statistics_category_portname_flavor;
DROP VIEW portsbuildstats_statistics_category_portname;
DROP VIEW portsbuildstats_statistics_category;
DROP VIEW portsbuildstats_statistics;

DROP VIEW portsbuildstats_with_duration;

CREATE VIEW portsbuildstats_with_duration AS
  SELECT p.category, p.portname, p.flavor, p.pkgname, p.start, p.finished,
         make_interval(0, 0, 0, 0,
                       extract(days from (p.finished - p.start))::int * 24 + extract(hours from (p.finished - p.start))::int,
                       extract(minutes from (p.finished - p.start))::int,
                       extract(seconds from (p.finished - p.start))::int) as duration
    FROM portsbuildstats p;

CREATE VIEW portsbuildstats_statistics AS
  SELECT COUNT(p.portname), MIN(p.duration), MAX(p.duration), AVG(p.duration),
         (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY p.duration)) AS median,
         (SELECT mode() WITHIN GROUP (ORDER BY p.duration)) AS mode, SUM(duration)
    FROM portsbuildstats_with_duration p;

CREATE VIEW portsbuildstats_statistics_category AS
  SELECT p.category, COUNT(p.portname), MIN(p.duration), MAX(p.duration), AVG(p.duration),
         (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY p.duration)) AS median,
         (SELECT mode() WITHIN GROUP (ORDER BY p.duration)) AS mode, SUM(duration)
    FROM portsbuildstats_with_duration p
    GROUP BY p.category
    ORDER BY p.category;

CREATE VIEW portsbuildstats_statistics_category_portname AS
  SELECT p.category, p.portname, COUNT(p.portname), MIN(p.duration), MAX(p.duration), AVG(p.duration),
         (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY p.duration)) AS median,
         (SELECT mode() WITHIN GROUP (ORDER BY p.duration)) AS mode, SUM(duration)
    FROM portsbuildstats_with_duration p
    GROUP BY p.category, p.portname
    ORDER BY p.category, p.portname;

CREATE VIEW portsbuildstats_statistics_category_portname_flavor AS
  SELECT p.category, p.portname, p.flavor, COUNT(p.portname), MIN(p.duration), MAX(p.duration), AVG(p.duration),
         (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY p.duration)) AS median,
         (SELECT mode() WITHIN GROUP (ORDER BY p.duration)) AS mode, SUM(duration)
    FROM portsbuildstats_with_duration p
    GROUP BY p.category, p.portname, p.flavor
    ORDER BY p.category, p.portname, p.flavor;

CREATE VIEW portsbuildstats_statistics_category_portname_flavor_pkgname AS
  SELECT p.category, p.portname, p.flavor, p.pkgname, COUNT(p.portname), MIN(p.duration), MAX(p.duration), AVG(p.duration),
         (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY p.duration)) AS median,
         (SELECT mode() WITHIN GROUP (ORDER BY p.duration)) AS mode, SUM(duration)
    FROM portsbuildstats_with_duration p
    GROUP BY p.category, p.portname, p.flavor, p.pkgname
    ORDER BY p.category, p.portname, p.flavor, p.pkgname;

CREATE VIEW portsbuildstats_statistics_category_portname_pkgname AS
  SELECT p.category, p.portname, p.pkgname, COUNT(p.portname), MIN(p.duration), MAX(p.duration), AVG(p.duration),
         (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY p.duration)) AS median,
         (SELECT mode() WITHIN GROUP (ORDER BY p.duration)) AS mode, SUM(duration)
    FROM portsbuildstats_with_duration p
    GROUP BY p.category, p.portname, p.pkgname
    ORDER BY p.category, p.portname, p.pkgname;

-- EOF
