from rich import box as rbox
from rich.console import Console
from rich.table import Table

from .db import get_conn
from .journal import compute_clv

console = Console()

VALID_GROUP_BY = ("market", "sport", "stat_type", "direction", "book")


def show_report(
    since: str | None = None,
    sport: str | None = None,
    group_by: str = "market",
):
    q = "SELECT * FROM bets WHERE status IN ('won','lost','push')"
    params: list = []
    if since:
        q += " AND game_date >= ?"
        params.append(since)
    if sport:
        q += " AND sport = ?"
        params.append(sport)

    with get_conn() as conn:
        rows = [dict(r) for r in conn.execute(q, params).fetchall()]

    if not rows:
        console.print("[yellow]No settled bets.[/yellow]")
        return

    total_stake = sum(r["stake"] for r in rows)
    total_return = sum(r["payout"] or 0 for r in rows)
    pnl = total_return - total_stake
    roi = pnl / total_stake * 100
    wins = sum(1 for r in rows if r["status"] == "won")
    hit_rate = wins / len(rows) * 100

    clv_rows = [r for r in rows if r.get("odds_close")]
    avg_clv = (
        sum(compute_clv(r["odds_taken"], r["odds_close"]) for r in clv_rows) / len(clv_rows)
        if clv_rows else None
    )

    pnl_color = "green" if pnl >= 0 else "red"
    roi_color = "green" if roi >= 0 else "red"

    console.print(f"\n[bold]Summary[/bold]  ({len(rows)} bets)")
    console.print(f"  Stake    {total_stake:.1f} RON")
    console.print(f"  P&L      [{pnl_color}]{pnl:+.1f} RON[/{pnl_color}]")
    console.print(f"  ROI      [{roi_color}]{roi:+.1f}%[/{roi_color}]")
    console.print(f"  Hit rate {hit_rate:.1f}%  ({wins}/{len(rows)})")
    if avg_clv is not None:
        clv_color = "green" if avg_clv >= 0 else "red"
        console.print(
            f"  Avg CLV  [{clv_color}]{avg_clv:+.2f}%[/{clv_color}]"
            f"  ({len(clv_rows)} bets with close)"
        )

    if group_by not in VALID_GROUP_BY:
        return

    groups: dict[str, list] = {}
    for r in rows:
        key = r.get(group_by) or "—"
        groups.setdefault(key, []).append(r)

    t = Table(box=rbox.SIMPLE, header_style="bold", show_header=True)
    t.add_column(group_by.replace("_", " ").title())
    t.add_column("N", justify="right")
    t.add_column("Stake", justify="right")
    t.add_column("P&L", justify="right")
    t.add_column("ROI%", justify="right")
    t.add_column("Hit%", justify="right")

    for key in sorted(groups):
        g = groups[key]
        s = sum(b["stake"] for b in g)
        ret = sum(b["payout"] or 0 for b in g)
        g_pnl = ret - s
        g_roi = g_pnl / s * 100 if s else 0
        g_wins = sum(1 for b in g if b["status"] == "won")
        g_hr = g_wins / len(g) * 100
        pc = "green" if g_pnl >= 0 else "red"
        rc = "green" if g_roi >= 0 else "red"
        t.add_row(
            key, str(len(g)), f"{s:.1f}",
            f"[{pc}]{g_pnl:+.1f}[/{pc}]",
            f"[{rc}]{g_roi:+.1f}%[/{rc}]",
            f"{g_hr:.0f}%",
        )
    console.print(t)


def show_status():
    with get_conn() as conn:
        open_bets = [
            dict(r) for r in conn.execute(
                "SELECT * FROM bets WHERE status='open' ORDER BY game_date, id"
            ).fetchall()
        ]
        settled = conn.execute(
            "SELECT payout, stake FROM bets WHERE status IN ('won','lost','push')"
        ).fetchall()

    pnl = sum((r["payout"] or 0) - r["stake"] for r in settled)
    exposed = sum(b["stake"] for b in open_bets)
    pnl_color = "green" if pnl >= 0 else "red"

    console.print(f"\n[bold]Bankroll[/bold]")
    console.print(f"  Settled P&L  [{pnl_color}]{pnl:+.1f} RON[/{pnl_color}]")
    console.print(f"  Open bets    {len(open_bets)}  ({exposed:.1f} RON exposed)")

    if open_bets:
        t = Table(box=rbox.SIMPLE, header_style="bold")
        t.add_column("ID", justify="right")
        t.add_column("Date")
        t.add_column("Selection", max_width=38)
        t.add_column("Odds", justify="right")
        t.add_column("Stake", justify="right")
        for b in open_bets:
            t.add_row(
                str(b["id"]), b["game_date"],
                b["selection"], f"{b['odds_taken']:.2f}", f"{b['stake']:.1f}",
            )
        console.print(t)