"""N10 - universe artefacts: hashes, reconstructible counts, missing files, 'missing' filings, annual_panel CIKs (cluster H, I). Read-only."""
from __future__ import annotations
import json, sys, subprocess
from pathlib import Path
sys.path.insert(0, str(Path(__file__).resolve().parent))
from common import *

r = Run("universe")
META = RDB / "data/metadata"
U = {}
files = ["universe.json", "russell3000_constituents.json", "sp500_constituents.json", "cik_mapping.json", "shards.json"]
U["sha256"] = {f: {"sha256": sha256_file(META / f), "bytes": (META / f).stat().st_size, "mtime": dt.datetime.fromtimestamp((META / f).stat().st_mtime).isoformat()} for f in files if (META / f).exists()}
with open(r.out / "SHA256SUMS", "w") as fh:
    for f, v in U["sha256"].items():
        fh.write(f"{v['sha256']}  {f}\n")
# not archived?
found = subprocess.run(["find", str(RDB), "-maxdepth", "6", "(", "-iname", "*holding*", "-o", "-iname", "company_tickers*", "-o", "-iname", "*IWV*", ")", "-not", "-path", "*/.venv/*"], capture_output=True, text=True).stdout.strip().splitlines()
U["holdings_or_company_tickers_files_found"] = found
uni = json.load(open(META / "universe.json")); ru = json.load(open(META / "russell3000_constituents.json")); sp = json.load(open(META / "sp500_constituents.json")); cm = json.load(open(META / "cik_mapping.json"))
U["universe_keys"] = sorted(uni.keys()); U["russell_keys"] = sorted(ru.keys()); U["sp500_keys"] = sorted(sp.keys())
U["cik_mapping_type"] = type(cm).__name__; U["cik_mapping_size"] = len(cm)
U["cik_mapping_scalars"] = {k: v for k, v in cm.items() if not isinstance(v, (list, dict))} if isinstance(cm, dict) else None
U["cik_mapping_dict_sizes"] = {k: len(v) for k, v in cm.items() if isinstance(v, (list, dict))} if isinstance(cm, dict) else None
U["universe_scalars"] = {k: v for k, v in uni.items() if not isinstance(v, (list, dict))}
U["russell_scalars"] = {k: v for k, v in ru.items() if not isinstance(v, (list, dict))}
U["sp500_scalars"] = {k: v for k, v in sp.items() if not isinstance(v, (list, dict))}
cons = ru.get("constituents", [])
U["russell_constituent_lines"] = len(cons)
U["russell_distinct_ciks"] = len({c.get("cik") for c in cons})
U["russell_constituent_keys"] = sorted(cons[0].keys()) if cons else []
U["russell_unmatched"] = ru.get("unmatched")
dup = {}
for c in cons: dup.setdefault(c.get("cik"), []).append(c.get("ticker"))
U["russell_ciks_with_multiple_lines"] = {k: v for k, v in dup.items() if len(v) > 1}
# mapper view of the unmatched tickers
T2C = cm.get("ticker_to_cik", {}) if isinstance(cm, dict) else {}
def lookup(tkr):
    out = {"in_ticker_to_cik": T2C.get(tkr) or T2C.get(tkr.upper()) or T2C.get(tkr.replace(".", "-")) or T2C.get(tkr.replace("-", "."))}
    for k, v in (cm.items() if isinstance(cm, dict) else []):
        if isinstance(v, dict) and k != "ticker_to_cik" and (tkr in v or tkr.upper() in v):
            out[k] = v.get(tkr, v.get(tkr.upper()))
        if isinstance(v, list) and any(str(x).upper().startswith(tkr.upper()) for x in v[:100000] if isinstance(x, str)):
            out[k] = [x for x in v if isinstance(x, str) and str(x).upper().startswith(tkr.upper())][:3]
    return out
U["unmatched_mapper_view"] = {str(u): lookup(str(u).split(" ")[0]) for u in (ru.get("unmatched") or [])}
# is the unmatched ticker's company present in the universe under another ticker/CIK (e.g. HOLX)?
U["unmatched_in_universe_by_name"] = {str(u): [ (c.get("ticker"), c.get("cik"), c.get("company_name") or c.get("name")) for c in ucons0 if str(u).split("(")[-1].rstrip(")").split(" ")[0].upper() in str(c.get("company_name") or c.get("name") or "").upper()][:3] for u in (ru.get("unmatched") or [])} if (ucons0 := uni.get("constituents", [])) is not None else None
U["unmatched_in_edgar_companies"] = {}
ucons = uni.get("constituents", [])
U["universe_counts"] = {"n": len(ucons), "in_sp500": sum(1 for c in ucons if c.get("in_sp500")), "in_russell3000": sum(1 for c in ucons if c.get("in_russell3000")),
                        "both": sum(1 for c in ucons if c.get("in_sp500") and c.get("in_russell3000")), "sp_only": sum(1 for c in ucons if c.get("in_sp500") and not c.get("in_russell3000")),
                        "ru_only": sum(1 for c in ucons if c.get("in_russell3000") and not c.get("in_sp500")), "keys": sorted(ucons[0].keys()) if ucons else []}
U["sp500_rows"] = len(sp.get("constituents", [])); U["sp500_distinct_ciks"] = len({c.get("cik") for c in sp.get("constituents", [])})
U["reconciliation"] = {"iwv_equity_lines_reported_in_paper": 2587, "reconstructible": False, "reason": "latest-holdings.csv / company_tickers.json not archived; universe.py drops non-company lines with a bare continue",
                       "lines_mapped": ru.get("count"), "distinct_ciks": U["russell_distinct_ciks"], "unmatched": len(ru.get("unmatched") or []), "universe_n": uni.get("count")}
# 'missing' filings and annual_panel CIKs
con = connect(SPID_DB); attach_edgar(con)
U["download_parse_status"] = con.execute("select form_type, download_status, parse_status, count(*) from edgar.filings group by 1,2,3 order by 1,2,3").fetchall()
r.dump("filings_status_missing.csv", con, "select accession_number, cik, ticker, form_type, filing_date, download_status, parse_status, local_path, file_size_bytes from edgar.filings where parse_status in ('missing','error') or download_status <> 'complete' order by parse_status, 1")
U["filings_missing_on_disk"] = con.execute("select form_type, count(*) from edgar.filings where parse_status='missing' group by 1").fetchall()
U["filings_parse_error"] = con.execute("select form_type, count(*) from edgar.filings where parse_status='error' group by 1").fetchall()
U["sections_for_error_filings"] = con.execute("select count(distinct accession_number) from edgar.filing_sections where accession_number in (select accession_number from edgar.filings where parse_status in ('error','missing'))").fetchone()[0]
U["paper_annual_panel_firms"] = 2501
U["annual_panel_distinct_ciks"] = con.execute("select count(distinct cik) from edgar.annual_panel").fetchone()[0]
U["filings_distinct_ciks"] = con.execute("select count(distinct cik) from edgar.filings").fetchone()[0]
U["companies_rows"] = con.execute("select count(*) from edgar.companies").fetchone()[0]
U["annual_panel_ciks_not_in_filings"] = con.execute("select list(distinct cik) from edgar.annual_panel where cik not in (select cik from edgar.filings)").fetchone()[0]
U["filings_ciks_not_in_annual_panel"] = con.execute("select list(distinct cik) from edgar.filings where cik not in (select cik from edgar.annual_panel)").fetchone()[0]
U["annual_panel_ciks_not_in_companies"] = con.execute("select list(distinct cik) from edgar.annual_panel where cik not in (select cik from edgar.companies)").fetchone()[0]
U["annual_panel_rows"] = con.execute("select count(*), min(fiscal_year), max(fiscal_year) from edgar.annual_panel").fetchone()
U["quarterly_panel_distinct_ciks"] = con.execute("select count(distinct cik) from edgar.quarterly_panel").fetchone()[0]
for u in (ru.get("unmatched") or []):
    tk = str(u).split(" ")[0]; nm = str(u).split("(")[-1].rstrip(")")
    U["unmatched_in_edgar_companies"][str(u)] = con.execute("select ticker, cik, company_name, in_sp500, in_russell3000 from edgar.companies where upper(ticker)=? or upper(company_name) like ? limit 3", [tk.upper(), "%" + nm.split(" ")[0].upper() + "%"]).fetchall()
U["companies_in_sp500_in_russell"] = con.execute("select count(*) filter (where in_sp500), count(*) filter (where in_russell3000), count(*) filter (where in_sp500 and in_russell3000), count(*) from edgar.companies").fetchone()
r.write_json("universe_artefacts.json", U)
r.finish()