# ============================================================
# SPID — command line interface
#
#   python -m spid extract  [--backend llm|rules] [--tickers NVDA,AAPL]
#                           [--ciks ...] [--forms 10-K,10-Q,8-K]
#                           [--limit-sections N] [--workers 8]
#   python -m spid resolve
#   python -m spid graph
#   python -m spid embed
#   python -m spid analytics
#   python -m spid search   "data center" --type data_center --min-amount 5e8
#   python -m spid semantic "AI projects launched by banks"
#   python -m spid project  <project_id>
#   python -m spid all      [extraction flags]   # full pipeline
#   python -m spid stats
# ============================================================
from __future__ import annotations

import argparse
import json
import sys

from spid.config import get_settings
from spid.store import Store


def _fmt_usd(v) -> str:
    if not v:
        return "-"
    for unit, div in (("B", 1e9), ("M", 1e6), ("K", 1e3)):
        if v >= div:
            return f"${v/div:.2f}{unit}"
    return f"${v:.0f}"


def _tickers_to_ciks(store: Store, tickers: list[str]) -> list[str]:
    up = [t.upper() for t in tickers]
    q = ",".join("'" + t + "'" for t in up)
    rows = store.con.execute(
        f"SELECT DISTINCT cik FROM src_filings WHERE upper(ticker) IN ({q})").fetchall()
    return [r[0] for r in rows]


def _print_table(rows: list[dict], cols: list[str], money_cols=()):
    if not rows:
        print("  (no rows)"); return
    widths = {c: max(len(c), *(len(_cell(r.get(c), c, money_cols)) for r in rows)) for c in cols}
    print("  " + "  ".join(c.ljust(widths[c]) for c in cols))
    print("  " + "  ".join("-" * widths[c] for c in cols))
    for r in rows:
        print("  " + "  ".join(_cell(r.get(c), c, money_cols).ljust(widths[c]) for c in cols))


def _cell(v, col, money_cols) -> str:
    if col in money_cols:
        return _fmt_usd(v or 0)
    if isinstance(v, list):
        return ", ".join(str(x) for x in v[:3])
    if v is None:
        return "-"
    return str(v)[:48]


# ---- commands ---------------------------------------------------------
def cmd_extract(args, store):
    from spid.pipeline import run_extraction
    ciks = None
    if args.tickers:
        ciks = _tickers_to_ciks(store, args.tickers.split(","))
    elif args.ciks:
        ciks = [c.zfill(10) for c in args.ciks.split(",")]
    elif args.sample:
        rows = store.con.execute(
            f"SELECT DISTINCT cik FROM src_filings ORDER BY cik LIMIT {int(args.sample)}").fetchall()
        ciks = [r[0] for r in rows]
    forms = args.forms.split(",") if args.forms else None
    res = run_extraction(store, backend=args.backend, ciks=ciks, forms=forms,
                         limit_sections=args.limit_sections, workers=args.workers,
                         min_confidence=args.min_conf)
    print(json.dumps(res, indent=2))


def cmd_ingest(args, store):
    from spid.ingest import ingest_10k
    ciks = None
    if args.tickers:
        ciks = _tickers_to_ciks(store, args.tickers.split(","))
    elif args.ciks:
        ciks = [c.zfill(10) for c in args.ciks.split(",")]
    print(json.dumps(ingest_10k(store, ciks=ciks, limit=args.limit), indent=2))


def cmd_backfill(args, store):
    n = store.backfill_extracted(backend=args.backend or "llm")
    print(json.dumps({"extracted_sections": n}, indent=2))


def cmd_pending(args, store):
    """How many sections still need extraction (and the estimated gpt-4o-mini cost)."""
    rows = store.con.execute("""
        SELECT form_type, COUNT(*) AS n, SUM(word_count) AS words
        FROM v_sections WHERE word_count >= 30
          AND section_id NOT IN (SELECT section_id FROM extracted_sections)
        GROUP BY form_type ORDER BY form_type""").fetchall()
    total = sum(r[1] for r in rows)
    print(json.dumps({"pending_by_form": [{"form": r[0], "sections": r[1], "words": int(r[2] or 0)} for r in rows],
                      "pending_total": total,
                      "est_cost_usd_gpt4o_mini": round(total * 0.0008, 2)}, indent=2))


def cmd_resolve(args, store):
    from spid.resolve import resolve_projects
    print(json.dumps(resolve_projects(store), indent=2))


def cmd_graph(args, store):
    from spid.graph import build_graph, export
    res = build_graph(store)
    res.update(export(store, get_settings().out_dir / "exports"))
    print(json.dumps(res, indent=2, default=str))


def cmd_embed(args, store):
    from spid.embed import build_embeddings
    print(json.dumps(build_embeddings(store), indent=2))


def cmd_analytics(args, store):
    from spid import analytics
    rep = analytics.full_report(store)
    print("\n=== OVERVIEW ===")
    for k, v in rep["overview"].items():
        print(f"  {k:28s}: {_fmt_usd(v) if 'usd' in k else v}")
    print("\n=== PROJECTS BY TYPE ===");      _print_table(rep["by_type"], ["project_type","projects","total_usd","avg_conf"], ("total_usd",))
    print("\n=== BY SECTOR ===");             _print_table(rep["by_sector"][:12], ["sector","projects","total_usd"], ("total_usd",))
    print("\n=== BY TECHNOLOGY ===");         _print_table(rep["by_technology"][:12], ["technology","projects"])
    print("\n=== BY GEOGRAPHY ===");          _print_table(rep["by_geography"][:12], ["location","projects","total_usd"], ("total_usd",))
    print("\n=== CAPEX BY YEAR ===");         _print_table(rep["capex_by_year"], ["year","projects_started","announced_usd"], ("announced_usd",))
    print("\n=== AI ADOPTION (top) ===");     _print_table(rep["ai_adoption"][:12], ["ticker","sector","ai_projects","total_usd"], ("total_usd",))
    print("\n=== TOP COMPANIES ===");         _print_table(rep["top_companies"][:12], ["ticker","company_name","projects","total_usd"], ("total_usd",))
    if args.json:
        (get_settings().out_dir / "exports" / "analytics.json").write_text(json.dumps(rep, indent=2, default=str))
        print("\n(written exports/analytics.json)")


def cmd_search(args, store):
    from spid.search import search_projects
    rows = search_projects(store, keyword=args.query, project_type=args.type,
                           sector=args.sector, technology=args.tech, location=args.location,
                           ticker=args.ticker, min_amount=args.min_amount,
                           year_from=args.year_from, year_to=args.year_to,
                           status=args.status, limit=args.limit)
    print(f"{len(rows)} projects:")
    _print_table(rows, ["ticker","project_type","project_name","status","total_amount_usd","n_filings","canonical_location"], ("total_amount_usd",))


def cmd_semantic(args, store):
    from spid.embed import semantic_search
    rows = semantic_search(store, args.query, k=args.limit)
    print(f"Top {len(rows)} semantic matches for: {args.query!r}")
    _print_table(rows, ["score","ticker","project_type","project_name","status","total_amount_usd"], ("total_amount_usd",))


def cmd_project(args, store):
    from spid.search import get_project
    p = get_project(store, args.project_id)
    if not p:
        print("not found"); return
    print(json.dumps({k: v for k, v in p.items() if k != "timeline"}, indent=2, default=str))
    print("\nTIMELINE:")
    _print_table(p["timeline"], ["filing_date","form_type","status","amount_usd","confidence","snippet"], ("amount_usd",))


def cmd_stats(args, store):
    for t in ["project_mentions","projects","project_timeline","kg_nodes","kg_edges","project_embeddings"]:
        try:
            print(f"  {t:22s}: {store.count(t):,}")
        except Exception:
            print(f"  {t:22s}: (missing)")


def cmd_all(args, store):
    cmd_extract(args, store)
    cmd_resolve(args, store)
    cmd_graph(args, store)
    try:
        cmd_embed(args, store)
    except Exception as e:  # noqa: BLE001
        print(f"[embed skipped: {e}]")
    cmd_analytics(args, store)


def main(argv=None):
    ap = argparse.ArgumentParser(prog="spid", description="SEC Project Intelligence Database")
    sub = ap.add_subparsers(dest="cmd", required=True)

    def add_extract_flags(p):
        p.add_argument("--backend", choices=["rules", "llm"], default=None)
        p.add_argument("--tickers", help="comma list e.g. NVDA,AAPL")
        p.add_argument("--ciks", help="comma list of CIKs")
        p.add_argument("--sample", type=int, help="first N companies by CIK")
        p.add_argument("--forms", help="comma list e.g. 10-K,10-Q,8-K")
        p.add_argument("--limit-sections", type=int, default=None)
        p.add_argument("--workers", type=int, default=8)
        p.add_argument("--min-conf", type=float, default=None)

    pe = sub.add_parser("extract"); add_extract_flags(pe)
    pb = sub.add_parser("backfill-extracted"); pb.add_argument("--backend", default="llm")
    sub.add_parser("pending")
    pin = sub.add_parser("ingest")
    pin.add_argument("--tickers"); pin.add_argument("--ciks"); pin.add_argument("--limit", type=int, default=None)
    sub.add_parser("resolve")
    sub.add_parser("graph")
    sub.add_parser("embed")
    pa = sub.add_parser("analytics"); pa.add_argument("--json", action="store_true")
    pall = sub.add_parser("all"); add_extract_flags(pall); pall.add_argument("--json", action="store_true")

    ps = sub.add_parser("search")
    ps.add_argument("query", nargs="?", default=None)
    ps.add_argument("--type"); ps.add_argument("--sector"); ps.add_argument("--tech")
    ps.add_argument("--location"); ps.add_argument("--ticker"); ps.add_argument("--status")
    ps.add_argument("--min-amount", type=float, dest="min_amount")
    ps.add_argument("--year-from", type=int, dest="year_from")
    ps.add_argument("--year-to", type=int, dest="year_to")
    ps.add_argument("--limit", type=int, default=25)

    psem = sub.add_parser("semantic"); psem.add_argument("query"); psem.add_argument("--limit", type=int, default=10)
    pp = sub.add_parser("project"); pp.add_argument("project_id")
    sub.add_parser("stats")

    args = ap.parse_args(argv)
    store = Store()
    try:
        {
            "extract": cmd_extract, "ingest": cmd_ingest, "resolve": cmd_resolve, "graph": cmd_graph,
            "embed": cmd_embed, "analytics": cmd_analytics, "search": cmd_search,
            "semantic": cmd_semantic, "project": cmd_project, "stats": cmd_stats,
            "all": cmd_all, "backfill-extracted": cmd_backfill, "pending": cmd_pending,
        }[args.cmd](args, store)
    finally:
        store.close()


if __name__ == "__main__":
    sys.exit(main())