-- Materialized view that pre-aggregates llm_calls by trace_id.
-- The /observability/traces page reads from this view instead of scanning
-- the full 1.4M-row llm_calls table on every request.
--
-- Refresh schedule: pg_cron job every 5 minutes (set up separately).
--   SELECT cron.schedule('refresh_trace_summaries', '*/5 * * * *',
--          'REFRESH MATERIALIZED VIEW CONCURRENTLY trace_summaries');

CREATE MATERIALIZED VIEW IF NOT EXISTS trace_summaries AS
SELECT
  trace_id,
  COUNT(*)::int                                         AS call_count,
  COALESCE(SUM(llm_cost), 0)                            AS total_cost,
  COALESCE(SUM(total_tokens), 0)                         AS total_tokens,
  COUNT(*) FILTER (WHERE status = 'failed')::int         AS failed_calls,
  BOOL_OR(parsing_status = 'partial')                    AS has_partial,
  ARRAY_AGG(DISTINCT call_type)                          AS call_types,
  MIN(created_at)                                        AS first_seen,
  MAX(created_at)                                        AS last_seen
FROM llm_calls
WHERE trace_id IS NOT NULL
GROUP BY trace_id;

-- Unique index on trace_id is required for REFRESH MATERIALIZED VIEW CONCURRENTLY
CREATE UNIQUE INDEX IF NOT EXISTS "trace_summaries_trace_id_idx"
ON trace_summaries (trace_id);

-- Support ORDER BY last_seen DESC + pagination
CREATE INDEX IF NOT EXISTS "trace_summaries_last_seen_idx"
ON trace_summaries (last_seen DESC);
