BEGIN;

DROP FUNCTION IF EXISTS pretty_table_name(TEXT, TEXT);

CREATE FUNCTION pretty_table_name(schema_name TEXT, table_name TEXT)
    RETURNS TEXT AS
$pretty_table_name$
BEGIN

    IF (schema_name = '') OR (schema_name = 'public') THEN
        RETURN table_name;
    ELSE
        RETURN schema_name || '.' || table_name;
    END IF;

END;
$pretty_table_name$
    LANGUAGE 'plpgsql'
    IMMUTABLE;


DROP FUNCTION IF EXISTS run_hours();

CREATE FUNCTION run_hours() RETURNS DOUBLE PRECISION AS
$run_hours$
DECLARE
    start_time          TIMESTAMP WITH TIME ZONE;
    end_time            TIMESTAMP WITH TIME ZONE;
BEGIN

    SELECT t."now" INTO STRICT start_time FROM times t WHERE t."text" = 'start';
    SELECT t."now" INTO STRICT end_time FROM times t WHERE t."text" = 'end';

    RETURN EXTRACT(EPOCH FROM (end_time-start_time))/3600.0;
END;
$run_hours$  
    LANGUAGE 'plpgsql'
    STABLE;


DROP FUNCTION IF EXISTS sar_time_boundaries();

CREATE FUNCTION sar_time_boundaries(OUT start_time TIMESTAMP WITH TIME ZONE,
                                    OUT end_time TIMESTAMP WITH TIME ZONE) RETURNS RECORD AS
$sar_time_boundaries$
DECLARE
    sar_start       TIMESTAMP WITH TIME ZONE;
    sar_end         TIMESTAMP WITH TIME ZONE;    
BEGIN

    SELECT MIN(measurement_time), MAX(measurement_time) INTO sar_start, sar_end FROM cpu;
    
    start_time = date_trunc('minute', sar_start) + '5 minute'::INTERVAL;
    end_time := date_trunc('minute', sar_end) - '5 minute'::INTERVAL;

    RETURN;

END;
$sar_time_boundaries$
    LANGUAGE 'plpgsql';


DROP FUNCTION IF EXISTS log_time_boundaries();

CREATE FUNCTION log_time_boundaries(OUT start_time TIMESTAMP WITH TIME ZONE,
                                    OUT end_time TIMESTAMP WITH TIME ZONE) RETURNS RECORD AS
$log_time_boundaries$
DECLARE
    log_start       TIMESTAMP WITH TIME ZONE;
    log_end         TIMESTAMP WITH TIME ZONE;    
BEGIN

    SELECT minimum, maximum INTO log_start, log_end FROM log_range;
    
    start_time = date_trunc('minute', log_start) + '5 minute'::INTERVAL;
    end_time := date_trunc('minute', log_end) - '5 minute'::INTERVAL;

    RETURN;

END;
$log_time_boundaries$
    LANGUAGE 'plpgsql';


DROP FUNCTION IF EXISTS create_time_boundaries();

CREATE FUNCTION create_time_boundaries(OUT sar_start  TIMESTAMP WITH TIME ZONE,
                                       OUT sar_end    TIMESTAMP WITH TIME ZONE,
                                       OUT log_start  TIMESTAMP WITH TIME ZONE,
                                       OUT log_end    TIMESTAMP WITH TIME ZONE) RETURNS RECORD AS
$create_time_boundaries$
DECLARE
    start_time       TIMESTAMP WITH TIME ZONE;
    end_time         TIMESTAMP WITH TIME ZONE;
BEGIN

    SELECT * FROM sar_time_boundaries() INTO sar_start, sar_end;
    SELECT * FROM log_time_boundaries() INTO log_start, log_end;
    
    -- Calculate provisional range
    
    IF log_start > sar_start THEN
        start_time := log_start;
    ELSE
        start_time := sar_start;
    END IF;
    
    IF log_end < sar_end THEN
        end_time := log_end;
    ELSE
        end_time := sar_end;
    END IF;
    
    -- If this range is too small, accept the individual ranges; otherwise, use it
    -- to cover both.
    
    IF (end_time - start_time) >= '2 hours 30 minutes'::INTERVAL THEN
        sar_start := start_time;
        sar_end   := end_time;
        log_start := start_time;
        log_end   := end_time;
    END IF;
    
    DROP TABLE IF EXISTS time_boundaries;
    
    CREATE UNLOGGED TABLE time_boundaries AS
        SELECT sar_start, sar_end, log_start, log_end;

    RETURN;

END;
$create_time_boundaries$
    LANGUAGE 'plpgsql';


DROP FUNCTION IF EXISTS create_log_minutes();

CREATE FUNCTION create_log_minutes() RETURNS VOID AS
$create_log_minutes$
DECLARE
    start_time          TIMESTAMP WITH TIME ZONE;
    end_time            TIMESTAMP WITH TIME ZONE;
BEGIN

    SELECT log_start, log_end INTO start_time, end_time FROM time_boundaries;
    
    DROP TABLE IF EXISTS log_minutes;
    
    CREATE UNLOGGED TABLE log_minutes AS
        SELECT gs.i AS minstart, gs.i + INTERVAL '1 minute' AS minend 
            FROM generate_series(start_time, end_time, INTERVAL '1 minute')
                    AS gs(i);
        
END;
$create_log_minutes$
    LANGUAGE 'plpgsql';

COMMIT;

