CREATE OR REPLACE FUNCTION generate_counters()
RETURNS TRIGGER AS $$
DECLARE
    repo_url TEXT;
BEGIN
    repo_url = NEW.webhook#>'{repository, html_url}';

    -- repo counter
    INSERT INTO cdp_data.build_counter AS c (id, next_value) VALUES (repo_url, 1)
    ON CONFLICT (id) DO UPDATE SET next_value = c.next_value + 1
    RETURNING next_value;

    -- branch counter
    INSERT INTO cdp_data.build_counter AS c (id, next_value)
    VALUES (repo_url || coalesce(NEW.webhook#>'{pull_request, base, ref}', NEW.webhook#>'{ref}', 1)
    ON CONFLICT (id) DO UPDATE SET next_value = c.next_value + 1
    RETURNING next_value;

    -- pr counter
    INSERT INTO cdp_data.build_counter AS c (id, next_value)
    VALUES (repo_url || NEW.webhook#>'{pull_request, number}', 1)
    ON CONFLICT (id) DO UPDATE SET next_value = c.next_value + 1
    RETURNING next_value;

    RETURN new;
END;
$$ LANGUAGE PLPSQL;

CREATE TRIGGER insert_counters BEFORE INSERT ON cdp_data.lifecycle
FOR EACH ROW EXECURE PROCEDURE generate_counters();