#!/usr/bin/env python3
"""
Web frontend for viewing and analyzing trending topics.
"""

import json
import sqlite3
from datetime import datetime, timedelta
from pathlib import Path
from collections import Counter

from flask import Flask, render_template, jsonify, request
from dotenv import load_dotenv

load_dotenv()

app = Flask(__name__)

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


def get_db():
    """Get database connection."""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


def get_latest_trends():
    """Get trends from the most recent collection run."""
    json_path = OUTPUT_DIR / "trends_weekly.json"
    if json_path.exists():
        with open(json_path) as f:
            return json.load(f)
    return None


def get_historical_runs(limit=30):
    """Get list of past collection runs."""
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute(
        """
        SELECT id, run_date, total_items, sources_succeeded, sources_failed
        FROM collection_runs
        ORDER BY run_date DESC
        LIMIT ?
    """,
        (limit,),
    )
    runs = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return runs


def get_run_details(run_id):
    """Get detailed trend data for a specific run."""
    conn = get_db()
    cursor = conn.cursor()

    # Get run info
    cursor.execute("SELECT * FROM collection_runs WHERE id = ?", (run_id,))
    run_row = cursor.fetchone()
    if not run_row:
        conn.close()
        return None

    run_info = dict(run_row)

    # Get trend items
    cursor.execute(
        """
        SELECT topic_name, source, original_rank, final_rank, region,
               final_score, source_count, boosted, url, metadata
        FROM trend_items
        WHERE run_id = ?
        ORDER BY final_rank
    """,
        (run_id,),
    )
    items = [dict(row) for row in cursor.fetchall()]

    conn.close()
    return {"run": run_info, "items": items}


def get_persistent_topics(days=7):
    """Find topics that have appeared in multiple runs over the past N days."""
    conn = get_db()
    cursor = conn.cursor()

    cutoff = (datetime.utcnow() - timedelta(days=days)).isoformat()

    cursor.execute(
        """
        SELECT t.topic_name, COUNT(DISTINCT r.id) as appearances,
               GROUP_CONCAT(DISTINCT t.source) as sources,
               AVG(t.final_score) as avg_score,
               MIN(r.run_date) as first_seen,
               MAX(r.run_date) as last_seen
        FROM trend_items t
        JOIN collection_runs r ON t.run_id = r.id
        WHERE r.run_date >= ?
        GROUP BY LOWER(t.topic_name)
        HAVING appearances > 1
        ORDER BY appearances DESC, avg_score DESC
        LIMIT 50
    """,
        (cutoff,),
    )

    topics = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return topics


def get_source_stats():
    """Get statistics about each source."""
    conn = get_db()
    cursor = conn.cursor()

    cursor.execute(
        """
        SELECT source, COUNT(*) as total_items,
               AVG(final_score) as avg_score,
               COUNT(DISTINCT run_id) as runs_appeared
        FROM trend_items
        GROUP BY source
    """
    )

    stats = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return stats


def get_trending_over_time(topic_name, days=30):
    """Get historical data for a specific topic."""
    conn = get_db()
    cursor = conn.cursor()

    cutoff = (datetime.utcnow() - timedelta(days=days)).isoformat()

    cursor.execute(
        """
        SELECT r.run_date, t.final_rank, t.final_score, t.source
        FROM trend_items t
        JOIN collection_runs r ON t.run_id = r.id
        WHERE LOWER(t.topic_name) LIKE LOWER(?)
        AND r.run_date >= ?
        ORDER BY r.run_date
    """,
        (f"%{topic_name}%", cutoff),
    )

    history = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return history


@app.route("/")
def index():
    """Main dashboard page."""
    latest = get_latest_trends()
    runs = get_historical_runs(10)
    persistent = get_persistent_topics(7)
    source_stats = get_source_stats()

    return render_template(
        "index.html",
        latest=latest,
        runs=runs,
        persistent=persistent,
        source_stats=source_stats,
    )


@app.route("/api/latest")
def api_latest():
    """API endpoint for latest trends."""
    return jsonify(get_latest_trends())


@app.route("/api/runs")
def api_runs():
    """API endpoint for historical runs."""
    limit = request.args.get("limit", 30, type=int)
    return jsonify(get_historical_runs(limit))


@app.route("/api/run/<int:run_id>")
def api_run_details(run_id):
    """API endpoint for specific run details."""
    details = get_run_details(run_id)
    if details:
        return jsonify(details)
    return jsonify({"error": "Run not found"}), 404


@app.route("/api/persistent")
def api_persistent():
    """API endpoint for persistent topics."""
    days = request.args.get("days", 7, type=int)
    return jsonify(get_persistent_topics(days))


@app.route("/api/topic/<path:topic_name>")
def api_topic_history(topic_name):
    """API endpoint for topic history."""
    days = request.args.get("days", 30, type=int)
    return jsonify(get_trending_over_time(topic_name, days))


@app.route("/api/collect")
def api_collect():
    """Trigger a new collection run."""
    import main

    try:
        result = main.run_pipeline()
        if result:
            return jsonify({"status": "success", "run_id": result.get("run_id")})
        return jsonify({"status": "error", "message": "No data collected"}), 500
    except Exception as e:
        return jsonify({"status": "error", "message": str(e)}), 500


if __name__ == "__main__":
    print("Starting Trending Topics Dashboard...")
    print("Open http://localhost:5000 in your browser")
    app.run(host="0.0.0.0", port=5000, debug=True)