-- ACP session cost accounting + budgets + reaper inputs.
-- Column-order rule: ALTER ADD COLUMN appends at the table end, so every
-- SELECT/RETURNING that reuses the table model must list the new columns LAST.

-- ============ AgentKind: model price link + default budget caps ============
ALTER TABLE acp_agent_kinds ADD COLUMN model_id UUID REFERENCES llm_models(id) ON DELETE SET NULL;
ALTER TABLE acp_agent_kinds ADD COLUMN max_cost_usd NUMERIC(12,6);
ALTER TABLE acp_agent_kinds ADD COLUMN max_tokens BIGINT;
ALTER TABLE acp_agent_kinds ADD COLUMN max_wall_clock_seconds INT;

-- ============ Sessions: running accumulators + budget snapshot + reaper inputs ============
ALTER TABLE acp_sessions ADD COLUMN prompt_tokens BIGINT NOT NULL DEFAULT 0;
ALTER TABLE acp_sessions ADD COLUMN completion_tokens BIGINT NOT NULL DEFAULT 0;
ALTER TABLE acp_sessions ADD COLUMN thinking_tokens BIGINT NOT NULL DEFAULT 0;
ALTER TABLE acp_sessions ADD COLUMN total_cost_usd NUMERIC(14,6) NOT NULL DEFAULT 0;
ALTER TABLE acp_sessions ADD COLUMN last_activity_at TIMESTAMPTZ NOT NULL DEFAULT now();
ALTER TABLE acp_sessions ADD COLUMN budget_max_cost_usd NUMERIC(12,6);
ALTER TABLE acp_sessions ADD COLUMN budget_max_tokens BIGINT;
ALTER TABLE acp_sessions ADD COLUMN budget_max_wall_clock_seconds INT;
ALTER TABLE acp_sessions ADD COLUMN terminated_reason TEXT;

-- Index for the reaper to scan active sessions by last activity cheaply.
CREATE INDEX idx_acp_sessions_active_activity
  ON acp_sessions(last_activity_at)
  WHERE status IN ('starting','running');

-- ============ Per-turn usage ledger (mirrors llm_usage) ============
CREATE TABLE acp_session_usage (
  id                BIGSERIAL PRIMARY KEY,
  session_id        UUID NOT NULL REFERENCES acp_sessions(id) ON DELETE CASCADE,
  user_id           UUID NOT NULL,
  project_id        UUID NOT NULL,
  agent_kind_id     UUID NOT NULL,
  model_id          UUID,
  prompt_tokens     BIGINT NOT NULL DEFAULT 0,
  completion_tokens BIGINT NOT NULL DEFAULT 0,
  thinking_tokens   BIGINT NOT NULL DEFAULT 0,
  cost_usd          NUMERIC(14,6) NOT NULL DEFAULT 0,
  source_event_id   BIGINT REFERENCES acp_events(id) ON DELETE SET NULL,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_acp_session_usage_session    ON acp_session_usage(session_id);
CREATE INDEX idx_acp_session_usage_created     ON acp_session_usage(created_at);
CREATE INDEX idx_acp_session_usage_user_created ON acp_session_usage(user_id, created_at);
CREATE INDEX idx_acp_session_usage_project_created ON acp_session_usage(project_id, created_at);
-- De-dup: a given source event must produce at most one ledger row.
CREATE UNIQUE INDEX uq_acp_session_usage_source_event
  ON acp_session_usage(source_event_id)
  WHERE source_event_id IS NOT NULL;