"""Shared helpers for the dashboard views: DB access, live Alpaca overview, date listing,
OCC symbol parsing."""

from __future__ import annotations

import re
import sqlite3

import pandas as pd
import streamlit as st

from config.settings import get_settings

S = get_settings()

_OCC_RE = re.compile(r"^([A-Z]+)(\d{6})([CP])(\d{8})$")


def _con() -> sqlite3.Connection | None:
    if not S.db_path.exists():
        return None
    con = sqlite3.connect(str(S.db_path))
    con.row_factory = sqlite3.Row
    return con


def query_df(sql: str, params: tuple = ()) -> pd.DataFrame:
    con = _con()
    if con is None:
        return pd.DataFrame()
    try:
        return pd.read_sql_query(sql, con, params=params)
    except Exception:
        return pd.DataFrame()
    finally:
        con.close()


def trade_dates() -> list[str]:
    if not S.output_dir.exists():
        return []
    return sorted((p.name for p in S.output_dir.iterdir() if p.is_dir()), reverse=True)


@st.cache_data(ttl=60, show_spinner=False)
def live_overview() -> tuple[pd.DataFrame, pd.DataFrame]:
    """Live per-persona account + position data straight from Alpaca (60s cache).

    Returns (accounts_df, positions_df) shaped like the DB tables (account = persona id).
    Personas without reachable creds are skipped; duplicate underlying accounts (shared-key
    fallback) are deduped by Alpaca account number. Empty frames when fully offline —
    callers fall back to the last DB snapshot.
    """
    from config.agent_registry import load_personas
    from execution.broker import for_persona

    acc_rows, pos_rows, seen = [], [], set()
    for p in load_personas():
        try:
            b = for_persona(p.id)
            acct = b.get_account()
            if acct.account_number in seen:
                continue
            seen.add(acct.account_number)
            acc_rows.append(
                {
                    "account": p.id,
                    "equity": float(acct.equity),
                    "cash": float(acct.cash),
                    "buying_power": float(acct.buying_power),
                }
            )
            for pos in b.get_positions():
                pos_rows.append(
                    {
                        "account": p.id,
                        "symbol": pos.symbol,
                        "qty": float(pos.qty),
                        "avg_entry": float(pos.avg_entry_price or 0),
                        "market_value": float(pos.market_value or 0),
                        "unrealized_pl": float(pos.unrealized_pl or 0),
                    }
                )
        except Exception:
            continue
    return pd.DataFrame(acc_rows), pd.DataFrame(pos_rows)


def describe_symbol(symbol: str) -> str:
    """Human-readable label for a position/order symbol. OCC option symbols become
    'AAPL $295 call 2026-07-24'; plain tickers pass through unchanged."""
    m = _OCC_RE.match(str(symbol).strip().upper())
    if not m:
        return symbol
    underlying, yymmdd, right, strike = m.groups()
    return (
        f"{underlying} ${int(strike) / 1000:g} {'call' if right == 'C' else 'put'} "
        f"20{yymmdd[:2]}-{yymmdd[2:4]}-{yymmdd[4:]}"
    )


def is_option_symbol(symbol: str) -> bool:
    return bool(_OCC_RE.match(str(symbol).strip().upper()))