"""llm-table-extraction-vs-xbrl.py — pull one company's income-statement concepts from XBRL company facts, deduplicate restated periods, and show the traps a model-based extractor would also face.

What it does: reads https://data.sec.gov/api/xbrl/companyfacts/CIK##########.json for the CIK given, keeps 10-K
              fiscal-year values of a few us-gaap concepts, and prints (1) the deduplicated fiscal-year table
              with the accession that first reported each period, (2) how many duplicate reports of the same
              period exist (each 10-K repeats two prior years), (3) which concept names the company used for
              revenue over time, because tag names change and a query for one tag can silently return nothing.
Inputs      : --cik (default 320193, Apple Inc.); --fetch to query live, otherwise reads
              datasets/llm-table-extraction-vs-xbrl.csv written on 2026-09-05.
Needs       : Python 3.13 standard library; pandas 3.0.2.
"""
import argparse
import datetime as dt
import json
import os
import urllib.request

import pandas as pd

CSV = "datasets/llm-table-extraction-vs-xbrl.csv"
CONCEPTS = ["Revenues", "RevenueFromContractWithCustomerExcludingAssessedTax", "SalesRevenueNet",
            "CostOfGoodsAndServicesSold", "GrossProfit", "OperatingIncomeLoss", "NetIncomeLoss", "EarningsPerShareDiluted"]
UA = os.environ.get("EDGAR_UA", "prism-data-lab/1.0 (research; contact via site)")


def fetch(cik: str) -> pd.DataFrame:
    url = f"https://data.sec.gov/api/xbrl/companyfacts/CIK{str(int(cik)).zfill(10)}.json"
    req = urllib.request.Request(url, headers={"User-Agent": UA, "Accept-Encoding": "identity"})
    with urllib.request.urlopen(req, timeout=60) as r:
        j = json.loads(r.read().decode())
    rows = []
    for c in CONCEPTS:
        f = j["facts"]["us-gaap"].get(c)
        if not f:
            continue
        for unit, vals in f["units"].items():
            for v in vals:
                if v.get("form") == "10-K" and v.get("fp") == "FY" and "start" in v:
                    rows.append({"entity": j["entityName"], "cik": int(cik), "concept": c, "unit": unit, "start": v["start"],
                                 "end": v["end"], "value": v["val"], "fy": v["fy"], "accession": v["accn"], "filed": v["filed"],
                                 "retrieved": dt.date.today().isoformat()})
    return pd.DataFrame(rows).sort_values(["concept", "end", "filed"]).reset_index(drop=True)


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--cik", default="320193")
    ap.add_argument("--fetch", action="store_true")
    a = ap.parse_args()
    if a.fetch:
        fetch(a.cik).to_csv(CSV, index=False)
    df = pd.read_csv(CSV, parse_dates=["start", "end", "filed"])
    df = df[(df["end"] - df["start"]).dt.days > 300]          # full fiscal years only (drops quarterly slices)
    print(f"{df['entity'].iloc[0]} (CIK {df['cik'].iloc[0]}), retrieved {df['retrieved'].iloc[0]}: {len(df)} fiscal-year facts across {df['concept'].nunique()} concepts")
    print("\n| concept | fiscal years reported | first period end | last period end | duplicate reports of the same period |")
    print("|---|---|---|---|---|")
    for c, g in df.groupby("concept", sort=False):
        dups = g.duplicated(subset=["start", "end"]).sum()
        print(f"| {c} | {g.drop_duplicates(['start', 'end']).shape[0]} | {g['end'].min():%Y-%m-%d} | {g['end'].max():%Y-%m-%d} | {int(dups)} |")
    first = df.sort_values("filed").drop_duplicates(["concept", "start", "end"], keep="first")
    rev = first[first["concept"].isin(["Revenues", "RevenueFromContractWithCustomerExcludingAssessedTax", "SalesRevenueNet"])]
    ni = first[first["concept"] == "NetIncomeLoss"].set_index("end")
    print("\n| fiscal year end | revenue concept used | revenue (USD bn) | net income (USD bn) | first reported in | filed |")
    print("|---|---|---|---|---|---|")
    for _, r in rev.sort_values("end").iterrows():
        n = ni["value"].get(r["end"])
        n_s = f"{n / 1e9:.3f}" if n is not None and not pd.isna(n) else "-"
        print(f"| {r['end']:%Y-%m-%d} | {r['concept']} | {r['value'] / 1e9:.3f} | {n_s} | {r['accession']} | {r['filed']:%Y-%m-%d} |")
    # same period reported in several filings: are the values identical?
    chk = df[df["concept"] == "NetIncomeLoss"].groupby(["start", "end"])["value"].nunique()
    print(f"\nNetIncomeLoss periods reported in more than one 10-K: {int((df[df['concept'] == 'NetIncomeLoss'].groupby(['start', 'end']).size() > 1).sum())}; "
          f"periods where the restated value differs from the first report: {int((chk > 1).sum())}")


if __name__ == "__main__":
    main()