CREATE SCHEMA IF NOT EXISTS test_infra;
SET SCHEMA 'test_infra';

CREATE OR REPLACE FUNCTION trigger_set_timestamp() RETURNS TRIGGER
LANGUAGE plpgsql AS $func$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END $func$;

-- tbl edge_jobs
CREATE TABLE IF NOT EXISTS edge_jobs (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    elapsed BIGINT,
    started TIMESTAMPTZ,
    finished TIMESTAMPTZ,
    version TEXT,
    path TEXT,
    job TEXT,
    workflow TEXT,
    number TEXT CONSTRAINT unique_number UNIQUE, -- runID + unique string
    run TEXT, -- runID
    tests_run INTEGER,
    tests_failed INTEGER,
    passed BOOLEAN,
    repos TEXT,
    repo_commit TEXT
);

CREATE OR REPLACE TRIGGER edge_jobs_timestamp BEFORE
UPDATE ON edge_jobs FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();

-- tbl edge_job_metadatas
CREATE TABLE IF NOT EXISTS edge_job_metadatas (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    edge_job UUID NOT NULL REFERENCES edge_jobs(id) ON DELETE CASCADE,
    key TEXT,
    value TEXT
);

CREATE INDEX IF NOT EXISTS idx_edge_job_fkey ON edge_job_metadatas (edge_job);
CREATE INDEX IF NOT EXISTS idx_edge_job_fkey_inc_key_value ON edge_job_metadatas (edge_job) INCLUDE (key, value);

CREATE OR REPLACE TRIGGER edge_job_metadatas_timestamp BEFORE
UPDATE ON edge_job_metadatas FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();

-- tbl edge_job_tests
CREATE TABLE IF NOT EXISTS edge_job_tests (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    edge_job UUID NOT NULL REFERENCES edge_jobs(id) ON DELETE CASCADE,
    name TEXT,
    suite TEXT,
    time BIGINT, -- convert to some other time / duration type?
    failed BOOLEAN,
    failure_text TEXT
);

CREATE INDEX IF NOT EXISTS idx_edge_job_fkey ON edge_job_tests (edge_job);

CREATE OR REPLACE TRIGGER edge_job_tests_timestamp BEFORE
UPDATE ON edge_job_tests FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();