#!/usr/bin/env python3
# query_optimizer_ci.py — Find slow queries and get fixes

import psycopg2
import anthropic
import json
import sys

def find_slow_queries(conn) -> list:
    """Get slowest queries from pg_stat_statements."""
    cur = conn.cursor()
    cur.execute("""
        SELECT query, calls, total_exec_time, mean_exec_time, max_exec_time, rows
        FROM pg_stat_statements
        WHERE mean_exec_time > 100  -- queries taking >100ms on average
        AND query NOT LIKE '%pg_stat%'
        ORDER BY total_exec_time DESC
        LIMIT 10
    """)
    return [dict(zip([d[0] for d in cur.description], row)) for row in cur.fetchall()]

def get_query_plan(conn, query: str) -> str:
    """Get EXPLAIN ANALYZE for a query."""
    cur = conn.cursor()
    try:
        # Clean up the query for EXPLAIN (remove parameterized values)
        explain_query = f"EXPLAIN (ANALYZE, BUFFERS) {query}"
        cur.execute(explain_query)
        return "\n".join([row[0] for row in cur.fetchall()])
    except Exception as e:
        return f"Error: {e}"

def optimize_with_claude(query: str, plan: str) -> str:
    """Use Claude to suggest optimizations."""
    client = anthropic.Anthropic()

    prompt = f"""PostgreSQL Slow Query Analysis

Query:
{query}

EXPLAIN ANALYZE Output:
{plan}

Provide:
1. What is the bottleneck in this query plan?
2. Suggest 3 specific fixes (indexes, rewrites, statistics updates)
3. Include exact DDL statements I can run
4. Estimate performance improvement for each fix
5. Which fix should I implement first?"""

    message = client.messages.create(
        model="claude-opus-4-6",
        max_tokens=2048,
        messages=[{"role": "user", "content": prompt}]
    )

    return message.content[0].text

if __name__ == "__main__":
    # Connect to Postgres
    conn = psycopg2.connect(
        host="localhost",
        database="mydb",
        user="postgres"
    )

    slow_queries = find_slow_queries(conn)

    for i, q in enumerate(slow_queries, 1):
        print(f"\n=== Slow Query #{i} ===")
        print(f"Average time: {q['mean_exec_time']:.2f}ms")
        print(f"Total time spent: {q['total_exec_time']:.0f}ms")
        print(f"Call count: {q['calls']}")
        print(f"\nQuery:\n{q['query'][:200]}...\n")

        plan = get_query_plan(conn, q['query'])
        analysis = optimize_with_claude(q['query'], plan)
        print(f"Claude's Analysis:\n{analysis}")

        if i >= 3:  # Limit to top 3 in CI
            break

    conn.close()

# monitor_production_queries.py — Daily report of slow queries

import subprocess
import anthropic
from datetime import datetime, timedelta

def get_slow_queries_since(hours: int = 24) -> str:
    """Query slow log from last N hours."""
    result = subprocess.run([
        "psql", "-h", "prod-db.example.com",
        "-U", "monitor", "-d", "mydb",
        "-c", f"""
        SELECT query, calls, total_exec_time, mean_exec_time
        FROM pg_stat_statements
        WHERE query_start > now() - interval '{hours} hours'
        AND mean_exec_time > 500
        ORDER BY total_exec_time DESC
        LIMIT 5
        """
    ], capture_output=True, text=True)
    return result.stdout

def send_daily_report():
    """Generate and send daily optimization report."""
    client = anthropic.Anthropic()
    slow_queries = get_slow_queries_since(24)

    message = client.messages.create(
        model="claude-opus-4-6",
        max_tokens=2000,
        messages=[{
            "role": "user",
            "content": f"""Production Slow Query Report for {datetime.now().date()}

{slow_queries}

Provide a priority-ordered list of:
1. What queries need attention (worst first)
2. One-sentence diagnosis for each
3. Estimated fix complexity (easy/medium/hard)
4. If this is a repeat issue, note it

Keep this brief for email (max 200 words)."""
        }]
    )

    # Send via email
    report = message.content[0].text
    subprocess.run(["mail", "-s", "Daily DB Query Report", "team@example.com"],
                   input=report.encode())

# Run daily
send_daily_report()