import sqlite3
import json
import os
from pathlib import Path
from datetime import datetime

DB_PATH = Path.home() / ".timmy" / "metrics" / "model_metrics.db"
REPORT_PATH = Path.home() / "timmy" / "SOVEREIGN_HEALTH.md"

def generate_report():
    if not DB_PATH.exists():
        return "No metrics database found."

    conn = sqlite3.connect(str(DB_PATH))
    
    # Get latest sovereignty score
    row = conn.execute("""
        SELECT local_pct, total_sessions, local_sessions, cloud_sessions, est_cloud_cost, est_saved
        FROM sovereignty_score ORDER BY timestamp DESC LIMIT 1
    """).fetchone()

    if not row:
        return "No sovereignty data found."

    pct, total, local, cloud, cost, saved = row
    
    # Get model breakdown
    models = conn.execute("""
        SELECT model, SUM(sessions), SUM(messages), is_local, SUM(est_cost_usd)
        FROM session_stats
        WHERE timestamp > ?
        GROUP BY model
        ORDER BY SUM(sessions) DESC
    """, (datetime.now().timestamp() - 86400 * 7,)).fetchall()

    report = f"""# Sovereign Health Report — {datetime.now().strftime('%Y-%m-%d')}

## ◈ Sovereignty Score: {pct:.1f}%
**Status:** {"🟢 OPTIMAL" if pct > 90 else "🟡 WARNING" if pct > 50 else "🔴 COMPROMISED"}

- **Total Sessions:** {total}
- **Local Sessions:** {local} (Zero Cost, Total Privacy)
- **Cloud Sessions:** {cloud} (Token Leakage)
- **Est. Cloud Cost:** ${cost:.2f}
- **Est. Savings:** ${saved:.2f} (Sovereign Dividend)

## ◈ Fleet Composition (Last 7 Days)
| Model | Sessions | Messages | Local? | Est. Cost |
| :--- | :--- | :--- | :--- | :--- |
"""
    for m, s, msg, l, c in models:
        local_flag = "✅" if l else "❌"
        report += f"| {m} | {s} | {msg} | {local_flag} | ${c:.2f} |\n"

    report += """
---
*Generated by the Sovereign Health Daemon. Sovereignty is a right. Privacy is a duty.*
"""
    
    with open(REPORT_PATH, "w") as f:
        f.write(report)
    
    print(f"Report generated at {REPORT_PATH}")
    return report

if __name__ == "__main__":
    generate_report()