BEGIN;

DROP FUNCTION IF EXISTS busy_cores(OUT TIMESTAMP WITH TIME ZONE, 
                                   OUT INTEGER,
                                   OUT INTEGER,
                                   OUT INTEGER,
                                   OUT INTEGER);

CREATE FUNCTION busy_cores(OUT measurement_time TIMESTAMP WITH TIME ZONE,
                           OUT cores_at_90 INTEGER,
                           OUT cores_at_75 INTEGER,
                           OUT cores_at_50 INTEGER,
                           OUT cores_at_25 INTEGER
                          ) RETURNS SETOF RECORD AS
$busy_cores$
BEGIN

    RETURN QUERY
        SELECT
            cpu.measurement_time,
            COALESCE(cores_at_90.cores_at_90, 0) AS cores_at_90,
            COALESCE(cores_at_75.cores_at_75, 0) AS cores_at_75,
            COALESCE(cores_at_50.cores_at_50, 0) AS cores_at_50,
            COALESCE(cores_at_25.cores_at_25, 0) AS cores_at_25
        FROM cpu
        LEFT OUTER JOIN (
            SELECT cpu.measurement_time, COUNT(*)::INTEGER AS cores_at_90
            FROM cpu
            WHERE cpu <> 'all' AND peridle <= 10
            GROUP BY cpu.measurement_time
            ) AS cores_at_90
            ON cores_at_90.measurement_time = cpu.measurement_time
        LEFT OUTER JOIN (
            SELECT cpu.measurement_time, COUNT(*)::INTEGER AS cores_at_75
            FROM cpu
            WHERE cpu <> 'all' AND peridle <= 25 AND peridle > 10
            GROUP BY cpu.measurement_time
            ) AS cores_at_75
            ON cores_at_75.measurement_time = cpu.measurement_time
        LEFT OUTER JOIN (
            SELECT cpu.measurement_time, COUNT(*)::INTEGER AS cores_at_50
            FROM cpu
            WHERE cpu <> 'all' AND peridle <= 50 AND peridle > 25
            GROUP BY cpu.measurement_time
            ) AS cores_at_50
            ON cores_at_50.measurement_time = cpu.measurement_time
        LEFT OUTER JOIN (
            SELECT cpu.measurement_time, COUNT(*)::INTEGER AS cores_at_25
            FROM cpu
            WHERE cpu <> 'all' AND peridle <= 75 AND peridle > 50
            GROUP BY cpu.measurement_time
            ) AS cores_at_25
            ON cores_at_25.measurement_time = cpu.measurement_time
        WHERE cpu.cpu = 'all'
        ORDER BY cpu.measurement_time;
END;
$busy_cores$
    LANGUAGE 'plpgsql';

DROP FUNCTION IF EXISTS busiest_core(OUT TIMESTAMP WITH TIME ZONE,
                                     OUT NUMERIC,
                                     OUT NUMERIC,
                                     OUT NUMERIC,
                                     OUT NUMERIC);

CREATE FUNCTION busiest_core(OUT measurement_time TIMESTAMP WITH TIME ZONE,
                             OUT peruser NUMERIC,
                             OUT persys NUMERIC,
                             OUT periowait NUMERIC,
                             OUT perhyper NUMERIC
                             ) RETURNS SETOF RECORD AS
$busiest_core$
BEGIN

    RETURN QUERY
        SELECT
            peak_busy.measurement_time,
            peak_busy.peruser,
            COALESCE(peak_busy.persys, 0) + COALESCE(peak_busy.persystem, 0) AS persys,
            peak_busy.periowait,
            COALESCE(peak_busy.perguest, 0) + COALESCE(peak_busy.persteal, 0) AS perhyper
        FROM (
            SELECT cpu.*, rank() OVER (PARTITION BY cpu.measurement_time ORDER BY peridle) AS busiest
                FROM cpu
                WHERE cpu <> 'all' ) AS peak_busy
        WHERE peak_busy.busiest = 1
        ORDER BY peak_busy.measurement_time;

END;
$busiest_core$
    LANGUAGE 'plpgsql';

    
DROP FUNCTION IF EXISTS average_core(OUT TIMESTAMP WITH TIME ZONE,
                                     OUT NUMERIC,
                                     OUT NUMERIC,
                                     OUT NUMERIC,
                                     OUT NUMERIC);

CREATE FUNCTION average_core(OUT measurement_time TIMESTAMP WITH TIME ZONE,
                             OUT peruser NUMERIC,
                             OUT persys NUMERIC,
                             OUT periowait NUMERIC,
                             OUT perhyper NUMERIC) RETURNS SETOF RECORD AS
$average_core$
BEGIN

    RETURN QUERY
        SELECT
            cpu.measurement_time,
            cpu.peruser,
            COALESCE(cpu.persys, 0) + COALESCE(cpu.persystem, 0) AS persys,
            cpu.periowait,
            COALESCE(cpu.perguest, 0) + COALESCE(cpu.persteal, 0) AS perhyper
        FROM cpu
        WHERE cpu = 'all'
        ORDER BY cpu.measurement_time;
END;
$average_core$
    LANGUAGE 'plpgsql';

COMMIT;