CREATE OR REPLACE FUNCTION pg_temp.daily_test_results_by_job(test_substring TEXT, job_name TEXT) RETURNS TABLE (
    name TEXT,
    day_start TIMESTAMP WITH TIME ZONE,
    current_successes BIGINT,
    current_flakes BIGINT,
    current_failures BIGINT,
    current_runs BIGINT,
    current_working_percentage NUMERIC(5,2)
) AS $$
BEGIN
    RETURN QUERY
    WITH results AS (
        SELECT
            tests.name,
            DATE_TRUNC('day', pjr.timestamp) AS day_start, -- Changed to 'day'
            COALESCE(count(
                    CASE WHEN pjrt.status = 1 THEN
                        1
                    ELSE
                        NULL::integer
                    END), 0::bigint) AS current_successes,
            COALESCE(count(
                    CASE WHEN pjrt.status = 13 THEN
                        1
                    ELSE
                        NULL::integer
                    END), 0::bigint) AS current_flakes,
            COALESCE(count(
                    CASE WHEN pjrt.status = 12 THEN
                        1
                    ELSE
                        NULL::integer
                    END), 0::bigint) AS current_failures,
            count(*) AS current_runs
        FROM
            prow_job_run_tests pjrt
            INNER JOIN prow_job_runs pjr ON pjr.id = pjrt.prow_job_run_id
            INNER JOIN prow_jobs pj ON pj.id = pjr.prow_job_id
            INNER JOIN tests ON pjrt.test_id = tests.id
        WHERE
            tests.name LIKE test_substring
            AND pj.name LIKE job_name
        GROUP BY
            tests.name,
            day_start -- Updated to group by day
        ORDER BY
            day_start, tests.name ASC -- Updated to order by day
    )
    SELECT
        *,
        ROUND((results.current_successes + results.current_flakes) * 100.0 / NULLIF(results.current_runs, 0), 2) AS current_working_percentage
    FROM
        results;
END;
$$ LANGUAGE plpgsql;

--- Example, TODO: replace parameters
SELECT day_start, current_working_percentage FROM pg_temp.daily_test_results_by_job('%events should not repeat pathologically for ns/openshift-dns', '%4.15%serial%');

