#!/usr/bin/env python3
"""
Quick status report for the trending topics pipeline.
Usage: python report.py
"""

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

OUTPUT_DIR = Path(__file__).parent / "output"
DB_PATH = OUTPUT_DIR / "trends_history.db"
ANALYSIS_PATH = OUTPUT_DIR / "analysis_opportunities.json"


def get_db_stats():
    """Get database statistics."""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    # Total items
    cursor.execute("SELECT COUNT(*) FROM trend_items")
    total_items = cursor.fetchone()[0]

    # Items per source
    cursor.execute("SELECT source, COUNT(*) FROM trend_items GROUP BY source ORDER BY COUNT(*) DESC")
    by_source = cursor.fetchall()

    # Last collection time
    cursor.execute("SELECT run_date FROM collection_runs ORDER BY id DESC LIMIT 1")
    last_collection = cursor.fetchone()

    # Total collection runs
    cursor.execute("SELECT COUNT(*) FROM collection_runs")
    total_runs = cursor.fetchone()[0]

    # Items in last run
    cursor.execute("SELECT MAX(id) FROM collection_runs")
    last_run_id = cursor.fetchone()[0]

    cursor.execute("SELECT COUNT(*) FROM trend_items WHERE run_id = ?", (last_run_id,))
    items_last_run = cursor.fetchone()[0]

    conn.close()

    return {
        "total_items": total_items,
        "by_source": dict(by_source),
        "last_collection": last_collection[0] if last_collection else None,
        "total_runs": total_runs,
        "last_run_id": last_run_id,
        "items_last_run": items_last_run,
    }


def get_analysis_stats():
    """Get analysis statistics."""
    if not ANALYSIS_PATH.exists():
        return None

    with open(ANALYSIS_PATH, "r") as f:
        data = json.load(f)

    # Get analysis timestamp (check multiple possible keys)
    analysis_time = data.get("analysis_timestamp") or data.get("timestamp") or data.get("generated_at")

    # Get opportunities
    opportunities = data.get("opportunities", [])

    # Sort by weighted_score
    opportunities.sort(key=lambda x: x.get("weighted_score", 0), reverse=True)

    # Count verdicts
    verdicts = {}
    for opp in opportunities:
        v = opp.get("verdict", "Unknown")
        verdicts[v] = verdicts.get(v, 0) + 1

    return {
        "analysis_time": analysis_time,
        "total_opportunities": len(opportunities),
        "verdicts": verdicts,
        "top_10": opportunities[:10],
    }


def count_items_since_analysis(analysis_time: str, last_run_id: int) -> int:
    """Count items collected since the last analysis."""
    if not analysis_time:
        return 0

    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    # Parse analysis time and normalize to ISO format without timezone
    try:
        # Handle timezone-aware timestamps
        analysis_dt = datetime.fromisoformat(analysis_time.replace("Z", "+00:00"))
        # Convert to naive datetime string in same format as collected_at
        analysis_str = analysis_dt.replace(tzinfo=None).strftime("%Y-%m-%dT%H:%M:%S")
    except:
        analysis_str = analysis_time

    # Count items collected after analysis
    cursor.execute(
        "SELECT COUNT(*) FROM trend_items WHERE collected_at > ?",
        (analysis_str,)
    )
    count = cursor.fetchone()[0]
    conn.close()

    return count


def main():
    print("=" * 60)
    print("TRENDING TOPICS PIPELINE - STATUS REPORT")
    print("=" * 60)

    # Database stats
    db_stats = get_db_stats()

    print("\n[DATABASE STATS]")
    print(f"  Total items collected: {db_stats['total_items']:,}")
    print(f"  Collection runs: {db_stats['total_runs']}")
    print(f"  Last collection: {db_stats['last_collection']}")
    print(f"  Items in last run: {db_stats['items_last_run']:,}")

    print("\n  Items by source:")
    for source, count in db_stats['by_source'].items():
        print(f"    {source}: {count:,}")

    # Analysis stats
    analysis_stats = get_analysis_stats()

    if analysis_stats:
        print("\n[ANALYSIS STATS]")
        print(f"  Last analysis: {analysis_stats['analysis_time']}")
        print(f"  Total opportunities: {analysis_stats['total_opportunities']}")

        print("\n  Verdicts:")
        for verdict, count in sorted(analysis_stats['verdicts'].items()):
            print(f"    {verdict}: {count}")

        # Items since analysis
        new_items = count_items_since_analysis(
            analysis_stats['analysis_time'],
            db_stats['last_run_id']
        )
        print(f"\n  New items since last analysis: {new_items:,}")

        # Top 10
        print("\n[TOP 10 OPPORTUNITIES]")
        for i, opp in enumerate(analysis_stats['top_10'], 1):
            verdict = opp.get("verdict", "?")[:13]
            score = opp.get("weighted_score", 0)
            title = opp.get("suggested_title", opp.get("topic", "Unknown"))[:42]
            print(f"  {i:2}. {verdict:13} | {score:4.2f} | {title}")
    else:
        print("\n[ANALYSIS STATS]")
        print("  No analysis results found. Run: python analyze.py")

    print("\n" + "=" * 60)


if __name__ == "__main__":
    main()