"""Fundamentals ingestion (FirstRate /api-f) with revision tracking. Params: tickers[] or universe (active stocks,
limit n). Each run: fetch quarterly + annual financials and earnings; compare with latest stored revision per
(entity, metric, period_end, period_type); unchanged → nothing; changed → new revision, restatement_flag, supersede."""
from __future__ import annotations

from datetime import UTC, datetime
from typing import Any

from findata_core.db import connection, transaction
from findata_fundamentals import FirstRateFundamentalsConnector, facts_from_financials

from findata_ingest import queue
from findata_ingest.lake import Lake


def run(job: dict[str, Any], lake: Lake, log) -> dict[str, Any]:
    p = job.get("params") or {}
    tickers = [t.upper() for t in (p.get("tickers") or [])]
    if not tickers:
        with connection() as conn:
            if p.get("all_active"):
                tickers = [r["symbol"] for r in conn.execute("SELECT symbol FROM instruments WHERE asset_class='stock' AND status='active' ORDER BY symbol").fetchall()]
            elif p.get("tracked"):
                row = conn.execute("SELECT value FROM settings_kv WHERE key='fundamentals_tracked_tickers'").fetchone()
                tickers = list(row["value"]) if row and row["value"] else [r["symbol"] for r in conn.execute("SELECT DISTINCT split_part(entity_id, ':', 2) symbol FROM fundamentals_facts WHERE entity_id LIKE 'frd:%'").fetchall()]
            else:
                n = int(p.get("limit", 50))
                tickers = [r["symbol"] for r in conn.execute("SELECT symbol FROM instruments WHERE asset_class='stock' AND status='active' ORDER BY symbol LIMIT %s", (n,)).fetchall()]
    fr = FirstRateFundamentalsConnector()
    observed = datetime.now(UTC)
    run_id = job.get("run_id")
    stats = {"tickers": 0, "facts_new": 0, "facts_unchanged": 0, "facts_restated": 0, "earnings": 0, "errors": 0, "pit_fallback": 0}
    ck = queue.get_checkpoint(f"job:{job['id']}", "state") or {}
    done = set(ck.get("done") or [])
    no_data = set(ck.get("no_data") or [])
    for tk in tickers:
        if tk in done:
            continue
        try:
            facts = []
            for period in ("quarterly", "annual"):
                payload = fr.financials_all(tk, period)
                facts.extend(facts_from_financials(payload, observed, tk))
            earnings = fr.earnings(tk)
        except Exception as e:
            stats["errors"] += 1
            log.warning("fundamentals.error", ticker=tk, error=str(e)[:200])
            continue
        with transaction() as conn:
            conn.execute("INSERT INTO entities(entity_id, name, tickers) VALUES (%s, (SELECT name FROM instruments WHERE symbol=%s AND asset_class='stock' LIMIT 1), %s) "
                         "ON CONFLICT (entity_id) DO UPDATE SET tickers=EXCLUDED.tickers, updated_at=now()", (f"frd:{tk}", tk, [tk]))
            for f in facts:
                if f.pop("pit_fallback", False):
                    stats["pit_fallback"] += 1
                cur = conn.execute("SELECT id, value, revision_id FROM fundamentals_facts WHERE entity_id=%s AND metric=%s AND fiscal_period_end=%s AND fiscal_period_type=%s AND source_id=%s AND superseded_at IS NULL",
                                   (f["entity_id"], f["metric"], f["fiscal_period_end"], f["fiscal_period_type"], f["source_id"])).fetchone()
                if cur is not None:
                    same = (cur["value"] is None and f["value"] is None) or (cur["value"] is not None and f["value"] is not None and abs(float(cur["value"]) - f["value"]) <= 1e-9 * max(1.0, abs(f["value"])))
                    if same:
                        stats["facts_unchanged"] += 1
                        continue
                    conn.execute("UPDATE fundamentals_facts SET superseded_at=%s WHERE id=%s", (observed, cur["id"]))
                    rev = int(cur["revision_id"]) + 1
                    restated = True
                    stats["facts_restated"] += 1
                else:
                    rev, restated = 1, False
                    stats["facts_new"] += 1
                conn.execute(
                    "INSERT INTO fundamentals_facts(entity_id, security_id, metric, value, unit, currency, fiscal_period_start, fiscal_period_end, fiscal_period_type, fiscal_year, fiscal_period, "
                    "filing_date, published_at, available_at, observed_at, source_document, source_id, revision_id, restatement_flag, ingest_run_id) "
                    "VALUES (%(entity_id)s,%(security_id)s,%(metric)s,%(value)s,%(unit)s,%(currency)s,%(fiscal_period_start)s,%(fiscal_period_end)s,%(fiscal_period_type)s,%(fiscal_year)s,%(fiscal_period)s,"
                    "%(filing_date)s,%(published_at)s,%(available_at)s,%(observed_at)s,%(source_document)s,%(source_id)s,%(rev)s,%(restated)s,%(run_id)s)",
                    {**f, "rev": rev, "restated": restated, "run_id": run_id})
            for e in earnings:
                conn.execute("INSERT INTO earnings_events(entity_id, event_date, eps_estimate, eps_reported, release_time, source_id) VALUES (%s,%s,%s,%s,%s,'firstrate_fundamentals') "
                             "ON CONFLICT (entity_id, event_date, source_id) DO UPDATE SET eps_estimate=EXCLUDED.eps_estimate, eps_reported=EXCLUDED.eps_reported, release_time=EXCLUDED.release_time, observed_at=now()",
                             (f"frd:{tk}", e["date"], e.get("eps_estimate"), e.get("eps_reported"), e.get("release_time")))
            stats["earnings"] += len(earnings)
        stats["tickers"] += 1
        done.add(tk)
        queue.set_checkpoint(f"job:{job['id']}", "state", {"done": sorted(done), "no_data": sorted(no_data)})
        queue.progress(job["id"], {**stats, "last": tk})
    fr.close()
    stats["no_statements_404"] = len(no_data)
    log.info("fundamentals.done", **stats)
    return stats