"""Three-way reconciliation: SEC raw API vs edgartools vs yfinance."""
import json
raw=json.load(open("data/rawapi_fcf.json"))
et =json.load(open("data/edgartools_fcf.json"))
yf =json.load(open("data/yfinance_fcf.json"))
Q=raw["quarters"]; order=raw["order"]

def mm(v): return None if v is None else round(v/1e6)

print(f"{'Company':<10} {'Quarter':<9} {'SEC OCF':>10} {'yf OCF':>10} {'diff':>9} | {'SEC capex':>10} {'yf capex':>10} {'diff':>9}")
print("-"*96)
exact=0; diff=0; miss=0; rows=[]
for c in order:
    for q in Q:
        s=raw["rows"][c][q]
        y=yf["result"][c].get(q)
        yo = mm(y["ocf"]) if y and y.get("ocf") is not None else None
        yc = mm(abs(y["capex"])) if y and y.get("capex") is not None else None
        if yo is None or yc is None:
            miss+=1; flag="MISSING"
            print(f"{c:<10} {q:<9} {s['ocf']:>10,} {'-':>10} {'-':>9} | {s['capex']:>10,} {'-':>10} {'-':>9}  <-- {flag}")
            rows.append((c,q,s['ocf'],None,s['capex'],None,"missing")); continue
        do=yo-s['ocf']; dc=yc-s['capex']
        st = "exact" if (do==0 and dc==0) else "DIFF"
        if st=="exact": exact+=1
        else: diff+=1
        print(f"{c:<10} {q:<9} {s['ocf']:>10,} {yo:>10,} {do:>+9,} | {s['capex']:>10,} {yc:>10,} {dc:>+9,}"
              + ("  <-- DIFF" if st=="DIFF" else ""))
        rows.append((c,q,s['ocf'],yo,s['capex'],yc,st))
print("-"*96)
print(f"quarters compared: {len(order)*len(Q)}   exact: {exact}   differing: {diff}   missing from yfinance: {miss}")

# and confirm SEC vs edgartools is still 84/84
m=sum(1 for c in order for q in Q
      if round(et["result"][c][q]["ocf"]/1e6)!=raw["rows"][c][q]["ocf"]
      or round(abs(et["result"][c][q]["capex"])/1e6)!=raw["rows"][c][q]["capex"])
print(f"SEC raw API vs edgartools: 84 cells, {m} mismatches")

# period label check: does yfinance report the true fiscal period end?
print("\nPERIOD LABEL CHECK (yfinance column label vs actual fiscal period end)")
truth={"Apple":{"CY2025Q4":"2025-12-27","CY2026Q1":"2026-03-28"},
       "Nvidia":{"CY2025Q4":"2026-01-25","CY2026Q1":"2026-04-26"},
       "Microsoft":{"CY2025Q4":"2025-12-31","CY2026Q1":"2026-03-31"}}
for c,d in truth.items():
    for q,real in d.items():
        y=yf["result"][c].get(q)
        lab=y["period_label"] if y else "absent"
        print(f"  {c:<10} {q}  yfinance says {lab}   filing says {real}   {'MATCH' if lab==real else 'NORMALISED'}")
json.dump(rows, open("data/three_way_rows.json","w"))