#!/usr/bin/env python3
"""
Zo Metrics CLI - Query production database for company metrics.

Usage:
  python metrics.py snapshot          # Quick overview
  python metrics.py users             # User growth analysis
  python metrics.py subscriptions     # Subscription breakdown
  python metrics.py tools [--days N]  # Tool usage (default: 7 days)
  python metrics.py channels          # Acquisition channels
  python metrics.py query "SQL"       # Run arbitrary SQL
"""

import argparse
import os
import psycopg2
from datetime import datetime


def get_conn():
    return psycopg2.connect(
        host="ep-muddy-cell-a524vfrg.us-east-2.aws.neon.tech",
        database="hostagentspg",
        user="hostagentspg_owner",
        password="npg_rR7YyGD3AuVX",
        sslmode="require",
    )


def query(sql: str, as_dict: bool = False):
    with get_conn() as conn:
        with conn.cursor() as cur:
            cur.execute(sql)
            cols = [d[0] for d in cur.description] if cur.description else []
            rows = cur.fetchall()
            if as_dict:
                return [dict(zip(cols, row)) for row in rows]
            return rows


def print_table(rows, headers):
    if not rows:
        print("No results")
        return
    widths = [len(h) for h in headers]
    for row in rows:
        for i, val in enumerate(row):
            widths[i] = max(widths[i], len(str(val)))
    
    header_line = " | ".join(h.ljust(widths[i]) for i, h in enumerate(headers))
    print(header_line)
    print("-+-".join("-" * w for w in widths))
    for row in rows:
        print(" | ".join(str(v).ljust(widths[i]) for i, v in enumerate(row)))


def cmd_snapshot():
    print(f"=== Zo Metrics Snapshot ({datetime.utcnow().date()}) ===\n")
    
    counts = query("""
        SELECT 
            (SELECT COUNT(*) FROM users) as users,
            (SELECT COUNT(*) FROM hosts) as hosts,
            (SELECT COUNT(*) FROM conversations WHERE deleted_at IS NULL) as convos,
            (SELECT COUNT(*) FROM subscriptions WHERE status = 'active' AND pricing_plan_slug != 'zo_free_v8') as paid
    """)[0]
    print(f"Users: {counts[0]:,}")
    print(f"Hosts: {counts[1]:,}")
    print(f"Active Conversations: {counts[2]:,}")
    print(f"Paid Subscribers: {counts[3]:,}")
    
    mrr = query("""
        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) FROM subscriptions WHERE status = 'active'
    """)[0][0]
    print(f"MRR: ${mrr:,}")
    
    print("\n=== Weekly Signups ===")
    rows = query("""
        SELECT DATE_TRUNC('week', created_at)::date, COUNT(*)
        FROM users WHERE created_at > NOW() - INTERVAL '6 weeks'
        GROUP BY 1 ORDER BY 1 DESC
    """)
    print_table(rows, ["Week", "Signups"])


def cmd_users():
    print("=== User Growth ===\n")
    
    print("Daily signups (last 14 days):")
    rows = query("""
        SELECT DATE_TRUNC('day', created_at)::date, COUNT(*)
        FROM users WHERE created_at > NOW() - INTERVAL '14 days'
        GROUP BY 1 ORDER BY 1 DESC
    """)
    print_table(rows, ["Date", "Signups"])
    
    print("\n\nMonthly signups (all time):")
    rows = query("""
        SELECT DATE_TRUNC('month', created_at)::date, COUNT(*)
        FROM users GROUP BY 1 ORDER BY 1 DESC
    """)
    print_table(rows, ["Month", "Signups"])


def cmd_subscriptions():
    print("=== Subscription Breakdown ===\n")
    
    rows = query("""
        SELECT pricing_plan_slug, status, COUNT(*)
        FROM subscriptions GROUP BY 1, 2 ORDER BY 3 DESC
    """)
    print_table(rows, ["Plan", "Status", "Count"])
    
    print("\n\n=== MRR by Plan ===")
    rows = query("""
        SELECT 
            pricing_plan_slug,
            COUNT(*) as count,
            CASE 
                WHEN pricing_plan_slug = 'zo_plus_v8' THEN COUNT(*) * 12
                WHEN pricing_plan_slug = 'zo_pro_v8' THEN COUNT(*) * 35
                WHEN pricing_plan_slug = 'zo_ultra_v8' THEN COUNT(*) * 100
                ELSE 0
            END as mrr
        FROM subscriptions 
        WHERE status = 'active' AND pricing_plan_slug != 'zo_free_v8'
        GROUP BY 1 ORDER BY 3 DESC
    """)
    print_table(rows, ["Plan", "Count", "MRR"])


def cmd_tools(days: int = 7):
    print(f"=== Top Tools (last {days} days) ===\n")
    
    rows = query(f"""
        SELECT tool_name, COUNT(*) as calls
        FROM tool_messages
        WHERE tool_timestamp > NOW() - INTERVAL '{days} days'
        GROUP BY 1 ORDER BY 2 DESC LIMIT 25
    """)
    print_table(rows, ["Tool", "Calls"])


def cmd_channels():
    print("=== Acquisition Channels ===\n")
    
    print("UTM Sources:")
    rows = query("""
        SELECT COALESCE(utm_source_first, '(none)'), COUNT(*)
        FROM user_profiles GROUP BY 1 ORDER BY 2 DESC LIMIT 20
    """)
    print_table(rows, ["Source", "Users"])
    
    print("\n\nReferrer Handles:")
    rows = query("""
        SELECT COALESCE(referrer_handle, '(organic)'), COUNT(*)
        FROM users GROUP BY 1 ORDER BY 2 DESC LIMIT 15
    """)
    print_table(rows, ["Referrer", "Users"])


def cmd_query(sql: str):
    rows = query(sql, as_dict=True)
    if rows:
        headers = list(rows[0].keys())
        data = [tuple(r.values()) for r in rows]
        print_table(data, headers)
    else:
        print("No results")


if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Zo Metrics CLI")
    subparsers = parser.add_subparsers(dest="command")
    
    subparsers.add_parser("snapshot", help="Quick metrics overview")
    subparsers.add_parser("users", help="User growth analysis")
    subparsers.add_parser("subscriptions", help="Subscription breakdown")
    
    tools_parser = subparsers.add_parser("tools", help="Tool usage stats")
    tools_parser.add_argument("--days", type=int, default=7)
    
    subparsers.add_parser("channels", help="Acquisition channels")
    
    query_parser = subparsers.add_parser("query", help="Run arbitrary SQL")
    query_parser.add_argument("sql", help="SQL query to run")
    
    args = parser.parse_args()
    
    if args.command == "snapshot":
        cmd_snapshot()
    elif args.command == "users":
        cmd_users()
    elif args.command == "subscriptions":
        cmd_subscriptions()
    elif args.command == "tools":
        cmd_tools(args.days)
    elif args.command == "channels":
        cmd_channels()
    elif args.command == "query":
        cmd_query(args.sql)
    else:
        parser.print_help()