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

-- tbl artifact_versions
CREATE TABLE IF NOT EXISTS artifact_versions (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    image TEXT NOT NULL,
    tag TEXT,
    sha256_digest TEXT NOT NULL CHECK (length(sha256_digest) = 64),
    UNIQUE (image, sha256_digest),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

-- fn_artifact_version_id_for gets the unique id for a new or existing artifact_version. if two callers try to insert the same record
-- at the same time, the conflict is resolved by retrying, ie the second caller will find the existing id instead
CREATE OR REPLACE FUNCTION fn_artifact_version_id_for(_artifact UUID, _tag TEXT , _sha256_digest TEXT, OUT _artifact_version_id UUID)
LANGUAGE plpgsql AS $func$
BEGIN
    LOOP
        SELECT id
        FROM artifact_versions
        WHERE artifact = _artifact
        INTO _artifact_version_id
        FOR SHARE; -- lock row until end of tx
        EXIT WHEN FOUND;

        INSERT INTO artifact_versions (artifact, tag, sha256_digest)
        VALUES (_artifact, _tag, _sha256_digest)
        ON CONFLICT (artifact, sha256_digest) DO UPDATE SET id = excluded.id WHERE false
        RETURNING id
        INTO _artifact_version_id;
        EXIT WHEN FOUND;
    END LOOP;
END $func$;

-- tbl artifacts
CREATE TABLE IF NOT EXISTS artifacts (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    project TEXT,
    repository TEXT,
    artifact_version UUID NOT NULL REFERENCES artifact_versions(id) ON DELETE RESTRICT, -- prevent delete of artifact_version if it is still present in a registry
    UNIQUE (project, repository, artifact_version),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

-- fn_artifact_id_for gets the unique id for a new or existing artifact. if two callers try to insert the same record
-- at the same time, the conflict is resolved by retrying, ie the second caller will find the existing id instead
CREATE OR REPLACE FUNCTION fn_artifact_id_for(_image TEXT, OUT _artifact_id UUID)
LANGUAGE plpgsql AS $func$
BEGIN
    LOOP
        SELECT id
        FROM artifacts
        WHERE image = _image
        INTO _artifact_id
        FOR SHARE; -- lock row until end of tx
        EXIT WHEN FOUND;

        INSERT INTO artifacts (image)
        VALUES (_image)
        ON CONFLICT (image) DO UPDATE SET id = excluded.id WHERE false
        RETURNING id
        INTO _artifact_id;
        EXIT WHEN FOUND;
    END LOOP;
END $func$;

-- tbl clusters
CREATE TABLE IF NOT EXISTS clusters (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    name TEXT,
    endpoint TEXT,
    version_major INT NOT NULL,
    version_minor INT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

-- tbl artifact_installations
CREATE TABLE IF NOT EXISTS artifact_installations (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    cluster UUID NOT NULL REFERENCES clusters(id),
    artifact_version UUID NOT NULL REFERENCES artifact_versions(id),
    UNIQUE (cluster, artifact_version),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

-- tbl config_sources
CREATE TABLE IF NOT EXISTS config_sources (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    name TEXT,
    url TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

-- tbl synced_clusters
CREATE TABLE IF NOT EXISTS synced_clusters (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    cluster UUID NOT NULL REFERENCES clusters(id),
    config_source UUID NOT NULL REFERENCES config_sources(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

-- tbl observed_states
CREATE TABLE IF NOT EXISTS observed_states (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    observed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    cluster UUID NOT NULL REFERENCES clusters(id) ON DELETE CASCADE,
    artifact_version UUID NOT NULL REFERENCES artifact_versions(id) ON DELETE CASCADE,
    UNIQUE (cluster, artifact_version),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

-- tbl desired_states
CREATE TABLE IF NOT EXISTS desired_states (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

-- tbl watched_resources
CREATE TABLE IF NOT EXISTS watched_resources (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    cluster UUID NOT NULL REFERENCES clusters(id) ON DELETE CASCADE,
    api_version TEXT NOT NULL,
    kind TEXT NOT NULL,
    resource JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_uid ON watched_resources ((resource ['metadata'] ['uid']));

CREATE INDEX IF NOT EXISTS idx_gvknn ON watched_resources (
    cluster,
    (resource ['apiVersion']),
    (resource ['kind']),
    (resource ['metadata'] ['namespace']),
    (resource ['metadata'] ['name'])
);

CREATE INDEX IF NOT EXISTS idx_gin ON watched_resources USING GIN (resource);

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

-- tbl remote_commands
CREATE TABLE IF NOT EXISTS remote_commands (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    cluster UUID NOT NULL REFERENCES clusters(id) ON DELETE CASCADE,
    command_type TEXT NOT NULL,
    command_args TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_cluster ON remote_commands (cluster);

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


-- TODO(dk185217): experimental. this is essentially just the complete history, append only. it surely wont
--                 scale, but should provide interesting data early
-- tbl watched_resource_observations
CREATE TABLE IF NOT EXISTS watched_resource_observations (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    cluster UUID NOT NULL REFERENCES clusters(id) ON DELETE CASCADE,
    api_version TEXT NOT NULL,
    kind TEXT NOT NULL,
    resource JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_uid ON watched_resource_observations ((resource ['metadata'] ['uid']));
CREATE INDEX IF NOT EXISTS idx_gvknn ON watched_resource_observations (
    cluster,
    (resource ['apiVersion']),
    (resource ['kind']),
    (resource ['metadata'] ['namespace']),
    (resource ['metadata'] ['name'])
);

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

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

CREATE INDEX IF NOT EXISTS idx_cluster_id_inc_key_value ON cluster_labels (cluster) INCLUDE (key, value);
CREATE INDEX IF NOT EXISTS idx_key_value ON cluster_labels (key, value);

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

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

CREATE INDEX IF NOT EXISTS idx_artifact_id_inc_key_value ON artifact_labels (artifact) INCLUDE (key, value);

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

-- tbl kinform_pubsub_subscriptions
-- TODO(dk18517): this table is currently a temp. cache, drop at startup, but not in future
DROP TABLE IF EXISTS kinform_pubsub_subscriptions;
CREATE TABLE IF NOT EXISTS kinform_pubsub_subscriptions (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    subscription TEXT NOT NULL,
    -- TODO(dk18517): ref. int. via fk on projects
    project TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_uid ON kinform_pubsub_subscriptions (subscription, project);

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

-- tbl kinform_sessions
CREATE TABLE IF NOT EXISTS kinform_sessions (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    cluster UUID NOT NULL REFERENCES clusters(id) ON DELETE CASCADE,
    session UUID NOT NULL UNIQUE,
    last_heartbeat TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_last_heartbeat ON kinform_sessions (last_heartbeat);

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