import numpy as np
import pandas as pd
from typing import Optional, Dict, Any, Tuple
from config.loader import CONFIG
from database.repository import get_db_connection

WEEK_SECONDS = 604800.0


def _normalize_series(series: pd.Series) -> pd.Series:
    """Min-max normalize a series to [0, 1]. Returns zeros if range is zero."""
    min_val = series.min()
    max_val = series.max()
    if max_val == min_val:
        return pd.Series(0.0, index=series.index)
    return (series - min_val) / (max_val - min_val)


def compute_baseline_velocity(db_path: Optional[str] = None) -> Dict[str, float]:
    """
    (#5) Compute each tag's historical velocity baseline from leaderboard_history.
    Returns {tag: median_weekly_velocity} across all recorded leaderboard snapshots.
    This lets us compare a tag's current velocity against its own history.
    """
    with get_db_connection(db_path) as conn:
        hist = pd.read_sql_query(
            "SELECT tag, weekly_velocity FROM leaderboard_history WHERE weekly_velocity IS NOT NULL",
            conn
        )
    if hist.empty:
        return {}
    # Median velocity per tag (robust to outliers)
    baseline = hist.groupby("tag")["weekly_velocity"].median().to_dict()
    return baseline


def compute_trend_score(
    leaderboard: pd.DataFrame,
    db_path: Optional[str] = None
) -> pd.DataFrame:
    """
    (#6) Multi-signal trend score combining weekly_velocity, acceleration,
    and growth_rate_pct into a single weighted score.

    Weights are configurable via config.analytics.trend_score_weights.
    Default: velocity=0.4, acceleration=0.3, growth_rate=0.3
    """
    if leaderboard.empty:
        return leaderboard

    weights = CONFIG.get("analytics", {}).get("trend_score_weights", {
        "velocity": 0.4,
        "acceleration": 0.3,
        "growth_rate": 0.3
    })

    lb = leaderboard.copy()

    # Normalize each signal to [0, 1]
    lb["norm_velocity"] = _normalize_series(lb["weekly_velocity"].fillna(0))
    lb["norm_acceleration"] = _normalize_series(
        lb["acceleration"].fillna(lb["acceleration"].median() if not lb["acceleration"].isna().all() else 1.0)
    )
    lb["norm_growth_rate"] = _normalize_series(lb["growth_rate_pct"].fillna(0))

    # Weighted combination
    lb["trend_score"] = (
        weights.get("velocity", 0.4) * lb["norm_velocity"] +
        weights.get("acceleration", 0.3) * lb["norm_acceleration"] +
        weights.get("growth_rate", 0.3) * lb["norm_growth_rate"]
    )

    # (#5) Add baseline comparison
    baseline = compute_baseline_velocity(db_path)
    if baseline:
        lb["baseline_velocity"] = lb["hashtag"].str.lstrip("#").map(baseline)
        lb["baseline_ratio"] = lb.apply(
            lambda r: round(r["weekly_velocity"] / r["baseline_velocity"], 2)
            if r.get("baseline_velocity") and r["baseline_velocity"] > 0
            else None,
            axis=1
        )
    else:
        lb["baseline_velocity"] = None
        lb["baseline_ratio"] = None

    return lb


def detect_stagnant_tags(
    min_days: int = 14,
    stagnant_threshold: float = 100.0,
    db_path: Optional[str] = None
) -> list:
    """
    (#7) Detect tags with very low velocity for an extended period.
    Returns list of tag names that should be considered for pruning.
    """
    with get_db_connection(db_path) as conn:
        df = pd.read_sql_query(
            "SELECT tag, hashtag_id, media_count, cycle_bucket FROM hashtag_snapshots ORDER BY cycle_bucket ASC",
            conn
        )
    if df.empty:
        return []

    df["cycle_bucket"] = pd.to_datetime(df["cycle_bucket"], utc=True)
    cutoff = pd.Timestamp.now(tz="UTC") - pd.Timedelta(days=min_days)

    stagnant = []
    for tag, group in df.groupby("tag"):
        recent = group[group["cycle_bucket"] >= cutoff]
        if recent.empty:
            stagnant.append(tag)
            continue
        if len(recent) < 3:
            continue
        x = (recent["cycle_bucket"] - recent["cycle_bucket"].iloc[0]).dt.total_seconds().to_numpy()
        y = recent["media_count"].to_numpy(dtype=np.float64)
        if len(x) < 2 or x[-1] == 0:
            continue
        slope = np.polyfit(x, y, 1)[0]
        weekly_velocity = slope * WEEK_SECONDS
        if weekly_velocity < stagnant_threshold:
            stagnant.append(tag)

    return stagnant


def detect_rising_tags(
    min_acceleration: float = 1.5,
    min_snapshots: int = 5,
    db_path: Optional[str] = None
) -> list:
    """
    (#7) Detect tags with high acceleration (speeding up).
    Returns list of tag names that should be considered for promotion.
    """
    with get_db_connection(db_path) as conn:
        df = pd.read_sql_query(
            "SELECT tag, media_count, cycle_bucket FROM hashtag_snapshots ORDER BY cycle_bucket ASC",
            conn
        )
    if df.empty:
        return []

    df["cycle_bucket"] = pd.to_datetime(df["cycle_bucket"], utc=True)
    rising = []

    for tag, group in df.groupby("tag"):
        if len(group) < min_snapshots:
            continue
        y = group["media_count"].to_numpy(dtype=np.float64)
        x = (group["cycle_bucket"] - group["cycle_bucket"].iloc[0]).dt.total_seconds().to_numpy()
        slope = np.polyfit(x, y, 1)[0]
        ols_daily = slope * 86400.0
        if ols_daily <= 0:
            continue
        # Recent velocity from last two snapshots
        prev, latest = y[-2], y[-1]
        gap_h = (group["cycle_bucket"].iloc[-1] - group["cycle_bucket"].iloc[-2]).total_seconds() / 3600.0
        if gap_h <= 0 or gap_h > 48:
            continue
        recent_daily = (latest - prev) / gap_h * 24.0
        acceleration = recent_daily / ols_daily
        if acceleration >= min_acceleration:
            rising.append((tag, round(acceleration, 2)))

    return rising


def calculate_top_velocity_regression(
    min_days: Optional[float] = None,
    top_k: Optional[int] = None,
    sort_by: str = "trend_score",
    db_path: Optional[str] = None
) -> pd.DataFrame:
    """
    Full trend metrics per hashtag. Four lenses:

      weekly_velocity : OLS posts/week               -> "which tags add most volume?"
      growth_rate_pct : weekly_velocity / count *100 -> "which tags grow fastest RELATIVE to size?"
      acceleration    : recent daily velocity / OLS daily velocity
                                                     -> "which tags are speeding up RIGHT NOW?"
      trend_score     : weighted multi-signal score   -> "which tags are trending overall?"
      baseline_ratio  : current velocity / own historical median
                                                     -> "which tags are above their own baseline?"

    Data-quality per tag (snapshots, drop_events, r_squared) so rankings
    never rest on noisy or purged counters.
    """
    if min_days is None:
        min_days = 0.0 if CONFIG.get("develop_mode") else CONFIG["analytics"]["default_min_days"]
    if top_k is None:
        top_k = CONFIG["analytics"].get("default_top_k", 10)
    min_snapshots = CONFIG["analytics"].get("min_snapshots_for_regression", 3)

    query = """
        SELECT tag, hashtag_id, media_count, cycle_bucket, recorded_at
        FROM hashtag_snapshots
        ORDER BY cycle_bucket ASC
    """
    with get_db_connection(db_path) as conn:
        df = pd.read_sql_query(query, conn)
    if df.empty:
        return pd.DataFrame()

    df["cycle_bucket"] = pd.to_datetime(df["cycle_bucket"], utc=True)
    results = []

    for tag, group in df.groupby("tag"):
        if len(group) < min_snapshots:
            continue

        first_time = group["cycle_bucket"].iloc[0]
        last_time = group["cycle_bucket"].iloc[-1]
        days_tracked = (last_time - first_time).total_seconds() / 86400.0
        if days_tracked < min_days:
            continue

        x = (group["cycle_bucket"] - first_time).dt.total_seconds().to_numpy()
        y = group["media_count"].to_numpy(dtype=np.float64)

        slope, intercept = np.polyfit(x, y, 1)
        residuals = y - (slope * x + intercept)
        ss_res = np.sum(residuals**2)
        ss_tot = np.sum((y - np.mean(y))**2)
        r_squared = 1.0 - (ss_res / ss_tot) if ss_tot > 0 else 0.0

        current_count = int(y[-1])
        weekly_velocity = slope * WEEK_SECONDS
        ols_daily = slope * 86400.0
        growth_rate_pct = (weekly_velocity / current_count * 100.0) if current_count > 0 else 0.0

        # Recent velocity: last two snapshots within the last 48h
        recent_daily = np.nan
        if len(group) >= 2:
            prev, latest = y[-2], y[-1]
            gap_h = (group["cycle_bucket"].iloc[-1] - group["cycle_bucket"].iloc[-2]).total_seconds() / 3600.0
            if 0 < gap_h <= 48:
                recent_daily = (latest - prev) / gap_h * 24.0
        acceleration = (recent_daily / ols_daily) if (ols_daily > 0 and not np.isnan(recent_daily)) else np.nan

        # Data quality: counter drops (purges) and staleness
        diffs = np.diff(y)
        drop_events = int(np.sum(diffs < 0))
        stale = (pd.Timestamp.now(tz="UTC") - last_time).total_seconds() / 3600.0

        results.append({
            "hashtag": f"#{tag}",
            "hashtag_id": group["hashtag_id"].iloc[-1],
            "snapshots": len(group),
            "current_count": current_count,
            "days_tracked": round(days_tracked, 2),
            "weekly_velocity": round(weekly_velocity, 1),
            "growth_rate_pct": round(growth_rate_pct, 3),
            "recent_daily": round(recent_daily, 1) if not np.isnan(recent_daily) else None,
            "acceleration": round(acceleration, 2) if not np.isnan(acceleration) else None,
            "drop_events": drop_events,
            "hours_since_snapshot": round(stale, 1),
            "r_squared": round(r_squared, 3),
        })

    if not results:
        return pd.DataFrame()
    res = pd.DataFrame(results)

    # (#6) Compute multi-signal trend score + baseline comparison
    res = compute_trend_score(res, db_path)

    # Sort by trend_score (default) or specified column
    sort_key = sort_by if sort_by in res.columns else "trend_score"
    res = res.sort_values(by=sort_key, ascending=False, na_position="last")
    return res.head(top_k).reset_index(drop=True)