#!/bin/bash
# Generate a quick metrics snapshot
# Usage: ./snapshot.sh

export PGHOST=ep-muddy-cell-a524vfrg.us-east-2.aws.neon.tech
export PGDATABASE=hostagentspg
export PGUSER=hostagentspg_owner
export PGPASSWORD=npg_rR7YyGD3AuVX
export PGSSLMODE=require
export PGCHANNELBINDING=require

echo "=== Zo Metrics Snapshot ($(date -u +%Y-%m-%d)) ==="
echo ""

psql -t -c "
SELECT 'Total Users: ' || COUNT(*) FROM users;
"

psql -t -c "
SELECT 'Total Hosts: ' || COUNT(*) FROM hosts;
"

psql -t -c "
SELECT 'Active Conversations: ' || COUNT(*) FROM conversations WHERE deleted_at IS NULL;
"

echo ""
echo "=== Subscriptions ==="
psql -c "
SELECT pricing_plan_slug as plan, status, COUNT(*) as count
FROM subscriptions 
GROUP BY 1, 2 ORDER BY 3 DESC;
"

echo "=== Paid MRR Estimate ==="
psql -t -c "
SELECT '\$' || SUM(CASE 
  WHEN pricing_plan_slug = 'zo_plus_v8' THEN 12
  WHEN pricing_plan_slug = 'zo_pro_v8' THEN 35
  WHEN pricing_plan_slug = 'zo_ultra_v8' THEN 100
  ELSE 0
END) as mrr
FROM subscriptions WHERE status = 'active';
"

echo ""
echo "=== Weekly Signups (last 4 weeks) ==="
psql -c "
SELECT DATE_TRUNC('week', created_at)::date as week, COUNT(*) as signups
FROM users
WHERE created_at > NOW() - INTERVAL '4 weeks'
GROUP BY 1 ORDER BY 1 DESC;
"

echo ""
echo "=== Top 10 Tools (last 7 days) ==="
psql -c "
SELECT tool_name, COUNT(*) as calls
FROM tool_messages
WHERE tool_timestamp > NOW() - INTERVAL '7 days'
GROUP BY 1 ORDER BY 2 DESC LIMIT 10;
"