"""
report.py — Analysis layer: what characterizes trending hashtags?

Answers questions like:
  - What domains (finance, crypto, forex) are over-represented?
  - What hashtags co-occur most frequently?
  - What's the baseline comparison for each trending tag?
  - How do trend scores decompose across signals?

Run: python report.py
     python report.py --domain crypto
     python report.py --min-velocity 5000
"""

import argparse
import os
import sqlite3
import sys
from collections import Counter
from datetime import datetime, timezone

import pandas as pd

sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))

from config.loader import CONFIG
from database.repository import get_db_connection
from analytics.engine import calculate_top_velocity_regression


DOMAIN_KEYWORDS = {
    "finance": ["finance", "financial", "wealth", "money", "invest", "investing", "dividend", "portfolio"],
    "crypto": ["crypto", "bitcoin", "ethereum", "btc", "eth", "blockchain", "defi", "altcoin", "token"],
    "forex": ["forex", "fx", "currency", "usd", "eur", "gbp", "pip"],
    "stocks": ["stock", "stocks", "equity", "equities", "nasdaq", "sp500", "dow", "nyse"],
    "trading": ["trading", "trade", "trader", "daytrading", "swingtrading", "scalping"],
    "psychology": ["psychology", "mindset", "discipline", "emotion", "fomo", "fear", "greed"],
    "risk": ["risk", "riskmanagement", "drawdown", "stoploss", "hedge"],
    "technical": ["technical", "analysis", "chart", "candlestick", "support", "resistance", "pattern"],
}


def classify_domain(tag):
    tag_lower = tag.lower().strip("#")
    for domain, keywords in DOMAIN_KEYWORDS.items():
        if any(kw in tag_lower for kw in keywords):
            return domain
    return "other"


def load_sibling_viral_posts():
    viral_db = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))),
                            "2_viral-posts", "data", "viral_tracker.db")
    if not os.path.exists(viral_db):
        return pd.DataFrame()
    try:
        conn = sqlite3.connect(viral_db)
        df = pd.read_sql_query(
            "SELECT username, caption_text, like_count FROM tracked_posts WHERE caption_text IS NOT NULL", conn)
        conn.close()
        return df
    except Exception:
        return pd.DataFrame()


def extract_hashtags_from_caption(caption):
    if not caption:
        return []
    return [t.strip("#").lower() for t in caption.split() if t.startswith("#") and len(t) > 2]


def analyze_domain_distribution(leaderboard):
    lb = leaderboard.copy()
    lb["domain"] = lb["hashtag"].apply(classify_domain)
    domain_stats = lb.groupby("domain").agg(
        count=("hashtag", "count"),
        avg_velocity=("weekly_velocity", "mean"),
        avg_trend_score=("trend_score", "mean") if "trend_score" in lb.columns else ("weekly_velocity", "mean"),
        avg_baseline_ratio=("baseline_ratio", "mean") if "baseline_ratio" in lb.columns else ("weekly_velocity", "mean"),
    ).reset_index()
    total = domain_stats["count"].sum()
    domain_stats["pct"] = (domain_stats["count"] / total * 100).round(1) if total > 0 else 0
    n_domains = len(domain_stats)
    expected_pct = 100.0 / n_domains if n_domains > 0 else 0
    domain_stats["lift"] = (domain_stats["pct"] / expected_pct).round(2) if expected_pct > 0 else 0
    return domain_stats.sort_values("avg_velocity", ascending=False)


def analyze_cooccurrence(db_path=None):
    with get_db_connection(db_path) as conn:
        df = pd.read_sql_query("SELECT tag, cycle_bucket FROM hashtag_snapshots ORDER BY cycle_bucket ASC", conn)
    if df.empty:
        return pd.DataFrame()
    bucket_tags = df.groupby("cycle_bucket")["tag"].apply(list).reset_index()
    cooccurrence = Counter()
    for tags in bucket_tags["tag"]:
        for i, t1 in enumerate(tags):
            for t2 in tags[i+1:]:
                if t1 != t2:
                    pair = tuple(sorted([t1, t2]))
                    cooccurrence[pair] += 1
    if not cooccurrence:
        return pd.DataFrame()
    cooc_df = pd.DataFrame(
        [{"tag1": p[0], "tag2": p[1], "cooccurrence": c} for p, c in cooccurrence.items()])
    return cooc_df.sort_values("cooccurrence", ascending=False).head(20)


def analyze_sibling_cooccurrence(leaderboard):
    viral_df = load_sibling_viral_posts()
    if viral_df.empty or leaderboard.empty:
        return pd.DataFrame()
    trending_tags = set(leaderboard["hashtag"].str.lstrip("#").str.lower())
    cooc = Counter()
    for _, row in viral_df.iterrows():
        caption_tags = set(extract_hashtags_from_caption(row.get("caption_text", "")))
        matching = caption_tags & trending_tags
        for t in matching:
            cooc[t] += 1
    if not cooc:
        return pd.DataFrame()
    return pd.DataFrame([{"hashtag": f"#{t}", "viral_post_mentions": c} for t, c in cooc.most_common(20)])


def print_report(min_velocity=0, domain_filter=None):
    print("=" * 80)
    print(f"HASHTAG TREND ANALYSIS REPORT — {datetime.now(timezone.utc).strftime('%Y-%m-%d %H:%M UTC')}")
    print("=" * 80)
    print()

    min_days = 0.0 if CONFIG.get("develop_mode") else CONFIG["analytics"]["default_min_days"]
    leaderboard = calculate_top_velocity_regression(min_days=min_days, top_k=50)

    if leaderboard.empty:
        print("No trending hashtags found. Collect more data (need >= 3 snapshots per tag).")
        return

    if min_velocity > 0:
        leaderboard = leaderboard[leaderboard["weekly_velocity"] >= min_velocity]
    if domain_filter:
        leaderboard = leaderboard[leaderboard["hashtag"].str.lower().str.contains(domain_filter, na=False)]
    if leaderboard.empty:
        print("No hashtags match the filters.")
        return

    print(f"Analyzing {len(leaderboard)} trending hashtag(s)...\n")

    # 1. Domain Distribution
    print("-" * 80)
    print("1. DOMAIN DISTRIBUTION")
    print("-" * 80)
    domain_stats = analyze_domain_distribution(leaderboard)
    if not domain_stats.empty:
        print(domain_stats.to_string(index=False))
        over_rep = domain_stats[domain_stats.get("lift", pd.Series([0]*len(domain_stats))) > 1.2]
        if not over_rep.empty:
            print(f"\n   → Over-represented: {', '.join(over_rep['domain'].tolist())}")
    print()

    # 2. Baseline Comparison
    print("-" * 80)
    print("2. BASELINE COMPARISON")
    print("-" * 80)
    if "baseline_ratio" in leaderboard.columns and leaderboard["baseline_ratio"].notna().any():
        bl = leaderboard[["hashtag", "weekly_velocity", "baseline_velocity", "baseline_ratio"]].dropna(subset=["baseline_ratio"])
        if not bl.empty:
            bl = bl.sort_values("baseline_ratio", ascending=False)
            print(bl.to_string(index=False))
            high_bl = bl[bl["baseline_ratio"] > 1.5]
            if not high_bl.empty:
                print(f"\n   → Running >1.5× own history: {', '.join(high_bl['hashtag'].tolist())}")
        else:
            print("   Insufficient history.")
    else:
        print("   No baseline data yet. Run for a few days.")
    print()

    # 3. Trend Score Breakdown
    print("-" * 80)
    print("3. MULTI-SIGNAL TREND SCORE")
    print("-" * 80)
    score_cols = ["hashtag", "weekly_velocity", "acceleration", "growth_rate_pct", "trend_score"]
    score_cols = [c for c in score_cols if c in leaderboard.columns]
    if "trend_score" in leaderboard.columns:
        print(leaderboard[score_cols].head(15).to_string(index=False))
        print(f"\n   → Weights: {CONFIG.get('analytics', {}).get('trend_score_weights', 'default')}")
    print()

    # 4. Co-occurrence
    print("-" * 80)
    print("4. CO-OCCURRENCE ANALYSIS")
    print("-" * 80)
    cooc = analyze_cooccurrence()
    if not cooc.empty:
        print(cooc.head(15).to_string(index=False))
    else:
        print("   Not enough data yet.")
    print()

    # 5. Cross-project synergy
    print("-" * 80)
    print("5. CROSS-PROJECT SYNERGY (trending hashtags in viral posts)")
    print("-" * 80)
    sibling_cooc = analyze_sibling_cooccurrence(leaderboard)
    if not sibling_cooc.empty:
        print(sibling_cooc.to_string(index=False))
    else:
        print("   No overlap found (or viral-posts DB unavailable).")
    print()

    # 6. Summary
    print("-" * 80)
    print("6. SUMMARY INSIGHTS")
    print("-" * 80)
    if "trend_score" in leaderboard.columns:
        top = leaderboard.iloc[0]
        print(f"   Top trending: {top['hashtag']} (trend_score={top.get('trend_score', 0):.3f})")
    if "acceleration" in leaderboard.columns:
        accel_tags = leaderboard.dropna(subset=["acceleration"]).sort_values("acceleration", ascending=False)
        if not accel_tags.empty:
            print(f"   Fastest accelerating: {accel_tags.iloc[0]['hashtag']} ({accel_tags.iloc[0]['acceleration']}×)")
    if "baseline_ratio" in leaderboard.columns and leaderboard["baseline_ratio"].notna().any():
        top_bl = leaderboard.dropna(subset=["baseline_ratio"]).sort_values("baseline_ratio", ascending=False).iloc[0]
        print(f"   Highest above baseline: {top_bl['hashtag']} ({top_bl['baseline_ratio']}×)")
    print()
    print("=" * 80)
    print("Rerun weekly as data accumulates.")
    print("=" * 80)


if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Hashtag trend analysis report")
    parser.add_argument("--domain", type=str, default=None, help="Filter by domain keyword")
    parser.add_argument("--min-velocity", type=float, default=0, help="Minimum weekly velocity")
    args = parser.parse_args()
    print_report(min_velocity=args.min_velocity, domain_filter=args.domain)