CREATE OR REPLACE FUNCTION check_value_in_bounds(v int, min_v int, max_v int) RETURNS void AS $$
-- Checks if v is in bounds, else throws exception
BEGIN
    IF v > max_v THEN
        RAISE EXCEPTION 'value too high: % > %', v, max_v;
    END IF;

    IF min_v > v THEN
        RAISE EXCEPTION 'value too low: % < %', v, min_v;
    END IF;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION parse_cron_part(cron_part text, min_v int, max_v int) RETURNS int[] AS $$
-- parses one part of crontab string and checks values to be in bounds
DECLARE
    result int[];
    draft_result int[];
    parts text[];
    part text;
BEGIN
    IF cron_part = '*' THEN
        RETURN result;
    END IF;

    IF strpos(cron_part, ',') > 0 THEN
        FOREACH part IN ARRAY string_to_array(cron_part, ',') LOOP
            result := result || parse_cron_part(part, min_v, max_v);
        END LOOP;

        RETURN result;
    END IF;

    IF strpos(cron_part, '-') > 0 THEN
        parts := string_to_array(cron_part, '-');
        PERFORM check_value_in_bounds(parts[1]::int, min_v, max_v);
        PERFORM check_value_in_bounds(parts[2]::int, min_v, max_v);
        result := ARRAY(SELECT generate_series(parts[1]::int, parts[2]::int));
        RETURN result;
    END IF;

    IF strpos(cron_part, '/') > 0 THEN
        parts := string_to_array(cron_part, '/');
        IF parts[1] <> '*' THEN
            PERFORM check_value_in_bounds(parts[1]::int, min_v, max_v);
        END IF;
        PERFORM check_value_in_bounds(parts[2]::int, min_v, max_v);

        draft_result := ARRAY(SELECT generate_series(min_v, max_v));
        FOR i IN 1..cardinality(draft_result) LOOP
            IF draft_result[i] % parts[2]::int = 0 THEN
                IF parts[1] <> '*' THEN
                    result := result || (draft_result[i] + parts[1]::int);
                ELSE
                    result := result || draft_result[i];
                END IF;
            END IF;
        END LOOP;

        RETURN result;
    END IF;

    PERFORM check_value_in_bounds(cron_part::int, min_v, max_v);

    RETURN ARRAY[cron_part::int];
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION date_matches_crontab(crontab text, dt timestamp) RETURNS boolean AS $$
-- matches date to given crontab string
DECLARE
    parts text[];
    seconds int[];
    minutes int[];
    hours int[];
    days int[];
    months int[];
    dows int[];
    years int[];
    i int;
BEGIN
    parts := string_to_array(crontab, ' ');

    IF cardinality(parts) <> 7 THEN
        RAISE EXCEPTION 'Crontab string must consist of 7 parts (s, m, h, d, m, dow, year)';
    END IF;

    seconds := parse_cron_part(parts[1], 0, 59);
    minutes := parse_cron_part(parts[2], 0, 59);
    hours := parse_cron_part(parts[3], 0, 23);
    days := parse_cron_part(parts[4], 1, 31);
    months := parse_cron_part(parts[5], 1, 12);
    dows := parse_cron_part(parts[6], 0, 7);
    years := parse_cron_part(parts[7], 0, 9999);

    IF cardinality(dows) > 0 THEN
        FOR i IN 1..cardinality(dows) LOOP
            dows[i] := dows[i] % 7;
        END LOOP;
    END IF;

    IF not(cardinality(seconds) > 0 AND trunc(EXTRACT(second FROM dt)) = ANY(seconds)) THEN
        RETURN false;
    END IF;

    IF not(cardinality(minutes) > 0 AND EXTRACT(minute FROM dt) = ANY(minutes)) THEN
        RETURN false;
    END IF;

    IF not(cardinality(hours) > 0 AND EXTRACT(hour FROM dt) = ANY(hours)) THEN
        RETURN false;
    END IF;

    IF not(cardinality(days) > 0 AND EXTRACT(day FROM dt) = ANY(days)) THEN
        RETURN false;
    END IF;

    IF not(cardinality(months) > 0 AND EXTRACT(month FROM dt) = ANY(months)) THEN
        RETURN false;
    END IF;

    IF not(cardinality(dows) > 0 AND EXTRACT(dow FROM dt) = ANY(dows)) THEN
        RETURN false;
    END IF;

    IF not(cardinality(years) > 0 AND EXTRACT(year FROM dt) = ANY(years)) THEN
        RETURN false;
    END IF;

    RETURN true;
END;
$$ LANGUAGE plpgsql;