CREATE SCHEMA IF NOT EXISTS thoth_sessions;
REVOKE ALL ON SCHEMA thoth_sessions FROM PUBLIC;

CREATE TABLE IF NOT EXISTS thoth_sessions.principals (
    id bigserial PRIMARY KEY,
    issuer text NOT NULL,
    subject text NOT NULL,
    display_name text,
    created_at timestamptz NOT NULL DEFAULT pg_catalog.now(),
    updated_at timestamptz NOT NULL DEFAULT pg_catalog.now(),
    UNIQUE (issuer, subject)
);

CREATE TABLE IF NOT EXISTS thoth_sessions.principal_preferences (
    principal_id bigint PRIMARY KEY REFERENCES thoth_sessions.principals(id) ON DELETE CASCADE,
    preferences jsonb NOT NULL DEFAULT '{}'::jsonb,
    updated_at timestamptz NOT NULL DEFAULT pg_catalog.now()
);

CREATE TABLE IF NOT EXISTS thoth_sessions.sessions (
    id uuid PRIMARY KEY,
    principal_id bigint NOT NULL REFERENCES thoth_sessions.principals(id),
    manifest jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT pg_catalog.now(),
    updated_at timestamptz NOT NULL DEFAULT pg_catalog.now()
);
CREATE INDEX IF NOT EXISTS sessions_principal_id_created_at_idx
ON thoth_sessions.sessions (principal_id, created_at DESC);

CREATE TABLE IF NOT EXISTS thoth_sessions.session_artifacts (
    session_id uuid NOT NULL REFERENCES thoth_sessions.sessions(id) ON DELETE CASCADE,
    artifact_key text NOT NULL,
    content text NOT NULL,
    updated_at timestamptz NOT NULL DEFAULT pg_catalog.now(),
    PRIMARY KEY (session_id, artifact_key)
);

CREATE TABLE IF NOT EXISTS thoth_sessions.review_decisions (
    session_id uuid NOT NULL REFERENCES thoth_sessions.sessions(id) ON DELETE CASCADE,
    seq integer NOT NULL CHECK (seq > 0),
    ts timestamptz NOT NULL,
    phase integer,
    type text NOT NULL,
    subject text NOT NULL,
    detail text NOT NULL DEFAULT '',
    rationale text NOT NULL DEFAULT '',
    retracts integer,
    PRIMARY KEY (session_id, seq)
);

CREATE TABLE IF NOT EXISTS thoth_sessions.audit_log (
    id bigserial PRIMARY KEY,
    occurred_at timestamptz NOT NULL DEFAULT pg_catalog.now(),
    action text NOT NULL,
    session_id uuid NOT NULL,
    actor_issuer text NOT NULL,
    actor_subject text NOT NULL,
    owner_issuer text NOT NULL,
    owner_subject text NOT NULL
);