spb/uqo-research-db

1# ============================================================2# SPID — raw 10-K ingester3#4# The pre-parsed corpus only sectioned 10-Q & 8-K filings; the5# 10-K HTML was left raw.  This module cleans the raw 10-K HTML6# and extracts the project-dense Items (1 Business, 1A Risk,7# 7 MD&A, 2 Properties) into a SPID-owned `spid_sections` table8# that `v_sections` unions in — so 10-Ks flow through the same9# extraction pipeline.10# ============================================================11from __future__ import annotations1213import glob14import re15import uuid16from pathlib import Path1718from spid.config import get_logger, get_settings19from spid.store import Store2021log = get_logger("ingest")2223RAW_SEC = get_settings().source_data / "raw" / "sec"2425# 10-K items we keep (where projects/capex/strategy concentrate)26_KEEP_ITEMS = {27    "1": "business", "1A": "risk_factors", "2": "properties",28    "7": "md_and_a", "7A": "quantitative_disclosures",29}30_ITEM_RE = re.compile(r"(?:^|\n)\s*ITEM\s+(\d+[AB]?)\s*[.\-:—–]", re.IGNORECASE)31_TAG_RE = re.compile(r"<[^>]+>")32_WS_RE = re.compile(r"[ \t]+")33_NL_RE = re.compile(r"\n{3,}")343536def _clean_html(path: Path) -> str:37    raw = path.read_text(errors="ignore")38    try:39        from bs4 import BeautifulSoup40        soup = BeautifulSoup(raw, "lxml")41        for t in soup(["script", "style", "table"]):42            t.decompose()43        text = soup.get_text("\n")44    except Exception:  # noqa: BLE00145        text = _TAG_RE.sub(" ", raw)46    import html as _html47    text = _html.unescape(text)48    text = _WS_RE.sub(" ", text)49    text = _NL_RE.sub("\n\n", text)50    return text515253def _extract_items(text: str) -> list[tuple[str, str]]:54    matches = list(_ITEM_RE.finditer(text))55    if not matches:56        return [("full_body", text)]57    out: list[tuple[str, str]] = []58    for i, m in enumerate(matches):59        item = m.group(1).upper()60        if item not in _KEEP_ITEMS:61            continue62        start = m.start()63        end = matches[i + 1].start() if i + 1 < len(matches) else len(text)64        body = text[start:end].strip()65        if len(body) > 400:  # skip the table-of-contents stubs66            out.append((_KEEP_ITEMS[item], body[:600_000]))67    # keep the longest instance of each section (TOC vs real body)68    best: dict[str, str] = {}69    for name, body in out:70        if name not in best or len(body) > len(best[name]):71            best[name] = body72    return list(best.items())737475def _find_primary(acc: str, cik: str) -> Path | None:76    d = RAW_SEC / cik / "10-K" / acc.replace("-", "")77    if not d.is_dir():78        return None79    cands = glob.glob(str(d / "*.htm")) + glob.glob(str(d / "*.html")) + glob.glob(str(d / "*.txt"))80    cands = [c for c in cands if "R" + "" not in Path(c).name or True]81    if not cands:82        return None83    return Path(max(cands, key=lambda p: Path(p).stat().st_size))848586_SPID_SECTIONS_DDL = """87CREATE TABLE IF NOT EXISTS spid_sections (88    section_id VARCHAR PRIMARY KEY,89    accession_number VARCHAR,90    section_name VARCHAR,91    item_number VARCHAR,92    text VARCHAR,93    word_count INTEGER94);95"""969798def ingest_10k(store: Store, *, ciks: list[str] | None = None, limit: int | None = None) -> dict:99    con = store.con100    con.execute(_SPID_SECTIONS_DDL)101    where = "form_type='10-K'"102    if ciks:103        q = ",".join("'" + c + "'" for c in ciks)104        where += f" AND cik IN ({q})"105    sql = f"SELECT accession_number, cik FROM src_filings WHERE {where} ORDER BY filing_date DESC"106    if limit:107        sql += f" LIMIT {int(limit)}"108    filings = con.execute(sql).fetchall()109110    rows, done, ok = [], 0, 0111    for acc, cik in filings:112        done += 1113        # skip if already ingested114        if con.execute("SELECT 1 FROM spid_sections WHERE accession_number=? LIMIT 1", [acc]).fetchone():115            ok += 1116            continue117        path = _find_primary(acc, cik)118        if not path:119            continue120        try:121            text = _clean_html(path)122            for name, body in _extract_items(text):123                rows.append((str(uuid.uuid4()), acc, name, "", body, len(body.split())))124            ok += 1125        except Exception as e:  # noqa: BLE001126            log.warning("ingest failed %s: %s", acc, e)127        if len(rows) >= 400:128            con.executemany("INSERT OR REPLACE INTO spid_sections VALUES (?,?,?,?,?,?)", rows); rows = []129        if done % 50 == 0:130            log.info("  ... ingested %d/%d 10-Ks", done, len(filings))131    if rows:132        con.executemany("INSERT OR REPLACE INTO spid_sections VALUES (?,?,?,?,?,?)", rows)133    n = store.count("spid_sections")134    log.info("Ingested 10-K sections: %d filings processed, %d section rows total.", ok, n)135    return {"filings_processed": ok, "section_rows": n}136