SELECT * FROM claude_usage_logs
WHERE request_type LIKE 'mcp_%'
ORDER BY created_at DESC;

-- Billing suggestions
SELECT * FROM encounter_billing_suggestions;

-- Readmission predictions
SELECT * FROM readmission_risk_predictions;

-- SDOH detections
SELECT * FROM sdoh_passive_detections;

-- Cultural coach translations
SELECT * FROM cultural_health_translations;

-- Handoff summaries
SELECT * FROM care_handoff_summaries;

-- CCM eligibility scores
SELECT * FROM ccm_eligibility_assessments;

-- Welfare check dispatches
SELECT * FROM welfare_check_dispatches;

SELECT
  DATE(created_at) as date,
  COUNT(*) as total_requests,
  SUM(input_tokens) as input_tokens,
  SUM(output_tokens) as output_tokens,
  SUM(cost) as total_cost,
  SUM(CASE WHEN from_cache THEN 1 ELSE 0 END) as cached_requests,
  ROUND(
    100.0 * SUM(CASE WHEN from_cache THEN 1 ELSE 0 END) / COUNT(*),
    2
  ) as cache_hit_rate
FROM claude_usage_logs
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(created_at)
ORDER BY date DESC;

SELECT
  request_type,
  COUNT(*) as requests,
  SUM(cost) as total_cost,
  AVG(cost) as avg_cost_per_request,
  MAX(cost) as max_cost,
  SUM(input_tokens + output_tokens) as total_tokens
FROM claude_usage_logs
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY request_type
ORDER BY total_cost DESC;

SELECT
  model,
  COUNT(*) as requests,
  SUM(cost) as total_cost,
  AVG(response_time_ms) as avg_response_time,
  SUM(input_tokens) as input_tokens,
  SUM(output_tokens) as output_tokens
FROM claude_usage_logs
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY model
ORDER BY total_cost DESC;

-- Calculate savings from caching
WITH cache_stats AS (
  SELECT
    COUNT(*) FILTER (WHERE from_cache = true) as cached,
    COUNT(*) FILTER (WHERE from_cache = false) as uncached,
    SUM(cost) FILTER (WHERE from_cache = true) as cached_cost,
    SUM(cost) FILTER (WHERE from_cache = false) as uncached_cost,
    AVG(input_tokens) FILTER (WHERE from_cache = false) as avg_uncached_tokens
  FROM claude_usage_logs
  WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT
  cached,
  uncached,
  cached + uncached as total_requests,
  ROUND(100.0 * cached / (cached + uncached), 2) as cache_hit_rate,
  ROUND(cached_cost, 4) as cached_cost,
  ROUND(uncached_cost, 4) as uncached_cost,
  ROUND(cached_cost + uncached_cost, 4) as total_cost,
  -- Estimated cost without caching
  ROUND(
    (cached * avg_uncached_tokens * 0.000003) + uncached_cost,
    4
  ) as estimated_cost_without_cache,
  ROUND(
    ((cached * avg_uncached_tokens * 0.000003) + uncached_cost) - (cached_cost + uncached_cost),
    4
  ) as estimated_savings
FROM cache_stats;

-- Check current month spending vs. budget
WITH monthly_costs AS (
  SELECT
    DATE_TRUNC('month', created_at) as month,
    SUM(cost) as total_cost
  FROM claude_usage_logs
  WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)
  GROUP BY DATE_TRUNC('month', created_at)
)
SELECT
  month,
  ROUND(total_cost, 2) as spent,
  100.00 as budget,  -- $100/month default budget
  ROUND(100.0 * total_cost / 100.00, 2) as budget_used_percent,
  ROUND(100.00 - total_cost, 2) as remaining
FROM monthly_costs;

-- Identify most expensive operations
SELECT
  request_type,
  user_id,
  DATE(created_at) as date,
  COUNT(*) as request_count,
  SUM(cost) as total_cost
FROM claude_usage_logs
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY request_type, user_id, DATE(created_at)
HAVING SUM(cost) > 1.00  -- Operations costing >$1
ORDER BY total_cost DESC
LIMIT 20;

💰 AI COST MONITOR
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

📊 COST SUMMARY (Last 7 Days)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Total Requests:        2,847
Total Cost:            $12.43
Avg Cost/Request:      $0.0044
Cache Hit Rate:        87.3%
Estimated Savings:     $42.15 (from caching)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
💸 COST BY SKILL
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. Readmission Predictor      $4.82  (38.8%)  |████████████░░░░|
2. Billing Code Suggester     $2.41  (19.4%)  |██████░░░░░░░░░░|
3. CCM Eligibility Scorer     $1.93  (15.5%)  |█████░░░░░░░░░░░|
4. Cultural Health Coach      $1.35  (10.9%)  |███░░░░░░░░░░░░░|
5. SDOH Passive Detector      $0.89  (7.2%)   |██░░░░░░░░░░░░░░|
6. Handoff Synthesizer        $0.67  (5.4%)   |█░░░░░░░░░░░░░░░|
7. Welfare Check Dispatcher   $0.36  (2.9%)   |█░░░░░░░░░░░░░░░|

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
🤖 MODEL USAGE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Claude Sonnet 4.5:
  Requests: 247
  Cost: $8.92 (71.7%)
  Avg Response: 1,247ms

Claude Haiku 4.5:
  Requests: 2,600
  Cost: $3.51 (28.2%)
  Avg Response: 423ms

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
📈 DAILY TREND
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Nov 15: $2.14  |████████░░░░░░░░░░░░| 387 requests
Nov 14: $1.89  |███████░░░░░░░░░░░░░| 412 requests
Nov 13: $1.76  |██████░░░░░░░░░░░░░░| 398 requests
Nov 12: $1.92  |███████░░░░░░░░░░░░░| 401 requests
Nov 11: $1.45  |█████░░░░░░░░░░░░░░░| 289 requests
Nov 10: $0.98  |███░░░░░░░░░░░░░░░░░| 234 requests (weekend)
Nov 9:  $1.29  |████░░░░░░░░░░░░░░░░| 267 requests (weekend)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
💳 BUDGET STATUS (November 2025)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Monthly Budget:        $100.00
Current Spending:      $47.23 (47.2%)
Remaining:             $52.77 (52.8%)
Days Remaining:        16 days
Projected Total:       $89.56 ✅

Status: 🟢 ON TRACK

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
⚡ OPTIMIZATION RECOMMENDATIONS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

✅ Cache performance: Excellent (87.3% hit rate)
✅ Model selection: Appropriate (Haiku for 91% of requests)
✅ Batch processing: Active (Welfare Check using daily batches)

💡 Suggestions:
  1. Consider caching readmission predictions for 24h (save $1.20/day)
  2. Review high-cost users (user_abc123: $3.45 this week)
  3. Enable auto-apply for billing codes >95% confidence (reduce review time)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━