-- ============================================================
-- CSM Evidence Verification Query
-- Frozen snapshot: 2026-07-11
-- Database: opencode_memory (PostgreSQL 16)
-- Connection: postgresql://opencode_memory@localhost:5432/opencode_memory
-- Run: psql -f csm-verify.sql
-- ============================================================

\echo '=== CSM Evidence Snapshot — 2026-07-11 ==='
\echo ''

-- 1. Total memories (live + archived)
\echo '--- Total memories ---'
SELECT
  count(*) AS total,
  count(*) FILTER (WHERE status = 'live') AS live,
  count(*) FILTER (WHERE status = 'archived') AS archived
FROM memories;

-- 2. Memory links (graph edges)
\echo '--- Memory links ---'
SELECT count(*) AS total_links FROM memory_links;

-- 3. Memory chunks (for vector recall)
\echo '--- Memory chunks ---'
SELECT count(*) AS total_chunks FROM memory_chunks;

-- 4. Experience packets
\echo '--- Experience packets ---'
SELECT count(*) AS total_packets FROM experience_packets;

-- 5. Compaction events
\echo '--- Compaction events ---'
SELECT count(*) AS total_compactions FROM compaction_events;

-- 6. Tokens saved (sum of before_tokens - after_tokens across compactions)
\echo '--- Tokens saved ---'
SELECT
  sum(before_tokens - after_tokens) AS tokens_saved,
  round(avg((1.0 - after_tokens::real / nullif(before_tokens, 0)) * 100), 1) AS avg_reduction_pct
FROM compaction_events
WHERE before_tokens > 0;

-- 7. Recall events + session coverage
\echo '--- Recall events ---'
SELECT
  count(*) AS total_recall_events,
  count(DISTINCT session_id) AS sessions_with_telemetry
FROM recall_logs;

-- 8. Total sessions (full span)
\echo '--- Total sessions ---'
SELECT
  count(*) AS total_sessions,
  min(created_at) AS first_session,
  max(created_at) AS last_session
FROM sessions;

-- 9. Sessions without recall telemetry (pre-Phase 6 rollout)
\echo '--- Sessions without recall telemetry ---'
SELECT count(*) AS sessions_without_telemetry
FROM sessions s
WHERE NOT EXISTS (
  SELECT 1 FROM recall_logs r WHERE r.session_id = s.id
);

-- 10. Belief knowledge store
\echo '--- Belief knowledge ---'
SELECT
  kind,
  count(*) AS count
FROM belief_knowledge_store
GROUP BY kind
ORDER BY count DESC;

-- 11. Self-model capabilities
\echo '--- Self-model capabilities ---'
SELECT
  capability,
  confidence,
  evidence_count
FROM self_model_capabilities
ORDER BY capability;

-- 12. Work ledger changes
\echo '--- Work ledger changes ---'
SELECT count(*) AS total_changes FROM work_ledger_changes;

-- 13. Embeddings coverage
\echo '--- Embedding coverage ---'
SELECT
  count(*) AS total,
  count(*) FILTER (WHERE embedding IS NOT NULL) AS with_embedding
FROM memories;

-- 14. Recall quality distribution
\echo '--- Recall quality distribution ---'
SELECT
  source,
  count(*) AS events
FROM recall_logs
GROUP BY source
ORDER BY events DESC;

\echo ''
\echo '=== End of verification ==='