"""
Agente Data Coverage de Leonex.

Audita la FRESCURA y la COMPLETITUD de los datos de mercado. Para cada uno de
los ultimos N dias y cada timeframe (1d, 4h, 1h, 30m, 15m, 5m) cuenta cuantos
tickers DISTINTOS tienen datos ese dia. Sirve para ver de un vistazo los dias
en los que la descarga no corrio, o corrio a medias.

POR QUE IMPORTA:
    Una estrategia montada sobre datos incompletos no es fiable. Si el 22/05
    solo cargaron 120 de 506 activos en 15m, cualquier backtest de ese tramo
    esta sesgado y las estrategias que salgan de ahi son humo. Este agente
    pone esos huecos en evidencia para que se puedan re-descargar.

Salida: dashboard/data/data_coverage_report.json

Uso:
    python agents/agente_data_coverage.py
    python agents/agente_data_coverage.py --days 30
    python agents/agente_data_coverage.py --db /ruta/a/Leonex.sqlite
"""

from __future__ import annotations

import argparse
import json
import logging
import sqlite3
import sys
from datetime import datetime
try:
    from datetime import UTC
except ImportError:                       # Python < 3.11
    from datetime import timezone
    UTC = timezone.utc
from pathlib import Path

PROJECT_ROOT = Path(__file__).resolve().parent.parent
DB_PATH = PROJECT_ROOT / "data" / "Leonex.sqlite"
DASHBOARD_DATA_DIR = PROJECT_ROOT / "dashboard" / "data"
LOGS_DIR = PROJECT_ROOT / "logs"
REPORT_PATH = DASHBOARD_DATA_DIR / "data_coverage_report.json"

# Orden de presentacion: de mas grueso a mas fino.
TIMEFRAMES = ["1d", "4h", "1h", "30m", "15m", "5m"]
DEFAULT_DAYS = 21
FULL_RATIO = 0.98          # >= 98% del maximo reciente cuenta como "completo"
_WEEKDAYS = ["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]


def _table_exists(conn: sqlite3.Connection, name: str) -> bool:
    return conn.execute(
        "SELECT name FROM sqlite_master WHERE type='table' AND name=?",
        (name,)).fetchone() is not None


def _columns(conn: sqlite3.Connection, table: str) -> list[str]:
    return [r[1] for r in conn.execute(f"PRAGMA table_info({table})")]


def _pick(cols: list[str], candidates: list[str]) -> str | None:
    """Devuelve el nombre real de la primera columna que coincida (sin
    distinguir mayusculas)."""
    low = {c.lower(): c for c in cols}
    for cand in candidates:
        if cand in low:
            return low[cand]
    return None


def daily_coverage(conn: sqlite3.Connection, days: int) -> dict[str, int]:
    """{fecha: n_tickers} de la tabla `prices` (datos diarios)."""
    if not _table_exists(conn, "prices"):
        return {}
    cols = _columns(conn, "prices")
    dcol = _pick(cols, ["date", "dt", "day"])
    tcol = _pick(cols, ["ticker", "symbol"])
    if not dcol or not tcol:
        return {}
    rows = conn.execute(
        f"SELECT substr({dcol}, 1, 10) AS d, COUNT(DISTINCT {tcol}) "
        f"FROM prices GROUP BY d ORDER BY d DESC LIMIT ?", (days,)).fetchall()
    return {str(d)[:10]: int(c) for d, c in rows if d}


def intraday_coverage(conn: sqlite3.Connection,
                      days: int) -> dict[str, dict[str, int]]:
    """{timeframe: {fecha: n_tickers}} de la tabla `prices_intraday`."""
    out: dict[str, dict[str, int]] = {}
    if not _table_exists(conn, "prices_intraday"):
        return out
    cols = _columns(conn, "prices_intraday")
    tscol = _pick(cols, ["ts", "datetime", "timestamp", "date", "dt"])
    tcol = _pick(cols, ["ticker", "symbol"])
    tfcol = _pick(cols, ["timeframe", "tf", "interval"])
    if not tscol or not tcol or not tfcol:
        return out
    tfs = [r[0] for r in conn.execute(
        f"SELECT DISTINCT {tfcol} FROM prices_intraday") if r[0]]
    for tf in tfs:
        rows = conn.execute(
            f"SELECT substr({tscol}, 1, 10) AS d, COUNT(DISTINCT {tcol}) "
            f"FROM prices_intraday WHERE {tfcol} = ? "
            f"GROUP BY d ORDER BY d DESC LIMIT ?", (tf, days)).fetchall()
        out[str(tf)] = {str(d)[:10]: int(c) for d, c in rows if d}
    return out


def universe_size(conn: sqlite3.Connection) -> int:
    """Mejor estimacion del tamano del universo (para referencia)."""
    for tbl in ("active_universe", "prices"):
        if not _table_exists(conn, tbl):
            continue
        tcol = _pick(_columns(conn, tbl), ["ticker", "symbol"])
        if not tcol:
            continue
        try:
            return int(conn.execute(
                f"SELECT COUNT(DISTINCT {tcol}) FROM {tbl}").fetchone()[0])
        except sqlite3.Error:
            continue
    return 0


def build_report(db_path: Path, days: int, log: logging.Logger) -> dict:
    """Construye el payload de cobertura. Si la BD no existe / no se puede
    leer, devuelve un payload vacio pero valido (el dashboard no rompe)."""
    empty = {
        "generated_at": datetime.now(UTC).isoformat(),
        "n_days": 0, "timeframes": TIMEFRAMES, "expected": {},
        "universe_size": 0, "latest_full": {}, "n_issues": 0, "days": [],
        "summary_note": "No se pudo leer la base de datos.",
    }
    if not db_path.exists():
        log.error("No existe la base de datos: %s", db_path)
        empty["summary_note"] = f"No existe la base de datos: {db_path}"
        return empty

    try:
        conn = sqlite3.connect(f"file:{db_path}?mode=ro", uri=True)
    except sqlite3.Error as exc:
        log.error("No se pudo abrir la BD: %s", exc)
        empty["summary_note"] = f"No se pudo abrir la BD: {exc}"
        return empty

    try:
        daily = daily_coverage(conn, days)
        intra = intraday_coverage(conn, days)
    finally:
        conn.close()

    per_tf: dict[str, dict[str, int]] = {"1d": daily}
    per_tf.update(intra)

    all_dates: set[str] = set()
    for m in per_tf.values():
        all_dates |= set(m.keys())
    recent = sorted(all_dates, reverse=True)[:days]

    # "Esperado" por timeframe = maximo reciente con datos (dia mas completo).
    expected: dict[str, int] = {}
    for tf in TIMEFRAMES:
        m = per_tf.get(tf, {})
        nz = [m.get(d, 0) for d in recent if m.get(d, 0) > 0]
        expected[tf] = max(nz) if nz else 0

    # Universo = tickers REALMENTE cargados en las tablas de precios (lo que
    # mide esta tabla), no la tabla active_universe (~36 del universo de
    # trading). Asi el texto del resumen coincide con las celdas mostradas.
    uni = max(expected.values(), default=0)

    days_out: list[dict] = []
    for d in recent:
        try:
            wd = datetime.strptime(d, "%Y-%m-%d").weekday()
        except ValueError:
            wd = 0
        days_out.append({
            "date": d,
            "weekday": _WEEKDAYS[wd],
            "is_weekend": wd >= 5,
            "cov": {tf: int(per_tf.get(tf, {}).get(d, 0)) for tf in TIMEFRAMES},
        })

    # Fecha mas reciente "completa" por timeframe.
    latest_full: dict[str, str | None] = {}
    for tf in TIMEFRAMES:
        exp = expected[tf]
        latest_full[tf] = None
        if exp > 0:
            for d in days_out:                       # ya en orden descendente
                if d["cov"][tf] >= FULL_RATIO * exp:
                    latest_full[tf] = d["date"]
                    break

    # Incidencias: casillas timeframe-dia LABORABLE por debajo del umbral.
    issues = 0
    for d in days_out:
        if d["is_weekend"]:
            continue
        for tf in TIMEFRAMES:
            exp = expected[tf]
            if exp > 0 and d["cov"][tf] < FULL_RATIO * exp:
                issues += 1

    summary = (
        f"Cobertura de los ultimos {len(days_out)} dias x {len(TIMEFRAMES)} "
        f"timeframes (universo ~{uni} tickers). {issues} casilla(s) "
        f"timeframe-dia laborable por debajo del {int(FULL_RATIO * 100)}% de "
        f"cobertura — esos son los tramos a re-descargar. Una estrategia "
        f"sobre datos incompletos no es fiable."
    )

    return {
        "generated_at": datetime.now(UTC).isoformat(),
        "n_days": len(days_out),
        "timeframes": TIMEFRAMES,
        "expected": expected,
        "universe_size": uni,
        "latest_full": latest_full,
        "n_issues": issues,
        "days": days_out,
        "summary_note": summary,
    }


def main() -> int:
    parser = argparse.ArgumentParser(description="Agente Data Coverage de Leonex")
    parser.add_argument("--days", type=int, default=DEFAULT_DAYS,
                        help="Cuantos dias auditar hacia atras (default 21).")
    parser.add_argument("--db", type=str, default=str(DB_PATH),
                        help="Ruta a la base de datos SQLite.")
    args = parser.parse_args()

    LOGS_DIR.mkdir(parents=True, exist_ok=True)
    DASHBOARD_DATA_DIR.mkdir(parents=True, exist_ok=True)
    logging.basicConfig(
        level=logging.INFO,
        format="%(asctime)s | %(levelname)s | %(name)s | %(message)s",
        handlers=[
            logging.FileHandler(LOGS_DIR / "agente_data_coverage.log",
                                encoding="utf-8"),
            logging.StreamHandler(),
        ],
    )
    log = logging.getLogger("agente_data_coverage")

    payload = build_report(Path(args.db), max(args.days, 1), log)
    REPORT_PATH.write_text(
        json.dumps(payload, indent=2, ensure_ascii=False),
        encoding="utf-8",
    )
    log.info("Reporte exportado -> %s | %d dias, %d incidencias",
             REPORT_PATH, payload["n_days"], payload["n_issues"])

    sep = "=" * 78
    print(f"\n{sep}")
    print("Leonex -- Data Coverage (frescura y completitud de datos)")
    print(sep)
    print(payload["summary_note"])
    for d in payload["days"][:8]:
        cells = "  ".join(
            f"{tf}:{d['cov'][tf]}" for tf in payload["timeframes"])
        print(f"  {d['date']} {d['weekday']}  {cells}")
    print(sep)
    return 0


if __name__ == "__main__":
    raise SystemExit(main())