-- M9: bounded observability for application and digital-employee runs.
-- Trace rows intentionally store metadata only; prompts, tool arguments and
-- model responses remain outside this table.

CREATE TABLE IF NOT EXISTS gateway.agent_traces (
    id uuid PRIMARY KEY,
    request_id text NOT NULL,
    api_key_id uuid REFERENCES gateway.api_keys(id) ON DELETE SET NULL,
    tenant_id uuid,
    trace_type text NOT NULL CHECK (trace_type IN ('application', 'digital_employee')),
    target_id uuid,
    target_code text NOT NULL CHECK (length(target_code) BETWEEN 1 AND 128),
    conversation_id text NOT NULL DEFAULT '' CHECK (length(conversation_id) <= 128),
    status text NOT NULL DEFAULT 'running' CHECK (status IN ('running', 'success', 'error')),
    started_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    finished_at timestamptz,
    latency_ms integer,
    retrieval_count integer NOT NULL DEFAULT 0 CHECK (retrieval_count >= 0),
    model_call_count integer NOT NULL DEFAULT 0 CHECK (model_call_count >= 0),
    tool_call_count integer NOT NULL DEFAULT 0 CHECK (tool_call_count >= 0),
    error text NOT NULL DEFAULT '' CHECK (length(error) <= 4000),
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE INDEX IF NOT EXISTS agent_traces_started_idx
    ON gateway.agent_traces (started_at DESC, id DESC);
CREATE INDEX IF NOT EXISTS agent_traces_request_idx
    ON gateway.agent_traces (request_id, started_at DESC);
CREATE INDEX IF NOT EXISTS agent_traces_target_idx
    ON gateway.agent_traces (trace_type, target_code, started_at DESC);
CREATE INDEX IF NOT EXISTS agent_traces_status_idx
    ON gateway.agent_traces (status, started_at DESC);

CREATE TABLE IF NOT EXISTS gateway.agent_trace_spans (
    id uuid PRIMARY KEY,
    trace_id uuid NOT NULL REFERENCES gateway.agent_traces(id) ON DELETE CASCADE,
    parent_id uuid REFERENCES gateway.agent_trace_spans(id) ON DELETE SET NULL,
    span_type text NOT NULL CHECK (span_type IN ('model', 'tool', 'retrieval')),
    name text NOT NULL CHECK (length(name) BETWEEN 1 AND 256),
    status text NOT NULL DEFAULT 'running' CHECK (status IN ('running', 'success', 'error')),
    started_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    finished_at timestamptz,
    latency_ms integer,
    provider_code text,
    model text,
    input_tokens bigint NOT NULL DEFAULT 0 CHECK (input_tokens >= 0),
    output_tokens bigint NOT NULL DEFAULT 0 CHECK (output_tokens >= 0),
    round integer NOT NULL DEFAULT 0 CHECK (round >= 0),
    error text NOT NULL DEFAULT '' CHECK (length(error) <= 4000),
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE INDEX IF NOT EXISTS agent_trace_spans_trace_idx
    ON gateway.agent_trace_spans (trace_id, started_at, id);
CREATE INDEX IF NOT EXISTS agent_trace_spans_type_idx
    ON gateway.agent_trace_spans (span_type, started_at DESC);

ALTER TABLE gateway.application_runs
    ADD COLUMN IF NOT EXISTS trace_id uuid REFERENCES gateway.agent_traces(id) ON DELETE SET NULL;
ALTER TABLE gateway.digital_employee_runs
    ADD COLUMN IF NOT EXISTS trace_id uuid REFERENCES gateway.agent_traces(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS application_runs_trace_idx
    ON gateway.application_runs (trace_id) WHERE trace_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS digital_employee_runs_trace_idx
    ON gateway.digital_employee_runs (trace_id) WHERE trace_id IS NOT NULL;