import sqlite3
import json
import logging
import re
from datetime import datetime
from typing import List, Dict, Any, Optional
from .config import COMPANIES_CATALOG_PATH, DB_PATH

logger = logging.getLogger("ocr.database")

def get_connection() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH, timeout=60.0)
    conn.execute("PRAGMA foreign_keys = ON;")
    conn.execute("PRAGMA journal_mode = WAL;")
    conn.row_factory = sqlite3.Row
    return conn


def iso_utc(value: Optional[str]) -> Optional[str]:
    """Stored timestamps are naive UTC (datetime.utcnow().isoformat()); return them as ISO-8601 with 'Z'."""
    if not value:
        return None
    s = str(value).strip().replace(" ", "T")
    if s.endswith("Z") or "+" in s[10:] or "-" in s[10:]:
        return s
    return s + "Z"


def extraction_message(status: Optional[str], missing_cells: Optional[int]) -> Optional[str]:
    if status != "FAILED":
        return None
    return f"Missing {missing_cells or 0} cells"


# Runs that change (or deprecate) the data a merge is built from.
_MERGE_INPUT_PROCESS_TYPES = ("OCR", "EXTRACTION")


def is_merge_stale(instrument_code: str) -> bool:
    """True when an OCR or extraction run, or a merge override, came after the last merge."""
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        row = conn.execute(f"""
            SELECT
                (SELECT MAX(id) FROM pipeline_runs WHERE instrument_code = ? AND process_type = 'MERGE') AS last_merge,
                (SELECT created_at FROM pipeline_runs WHERE instrument_code = ? AND process_type = 'MERGE'
                    ORDER BY id DESC LIMIT 1) AS last_merge_at,
                (SELECT MAX(id) FROM pipeline_runs WHERE instrument_code = ?
                    AND process_type IN ({",".join("?" * len(_MERGE_INPUT_PROCESS_TYPES))})) AS last_input,
                (SELECT MAX(u) FROM (SELECT MAX(updated_at) AS u FROM merge_override
                                         WHERE instrument_code = ? AND COALESCE(source, 'user') = 'user'
                                     UNION ALL SELECT MAX(updated_at) FROM line_override WHERE instrument_code = ?)) AS last_override,
                (SELECT merge_invalidated_at FROM company_results WHERE instrument_code = ?) AS invalidated_at
        """, (code, code, code, *_MERGE_INPUT_PROCESS_TYPES, code, code, code)).fetchone()
    # A merge saved without a MERGE run has no reference point: assume fresh.
    if row["last_merge"] is None:
        return False
    # Marked invalid by hand (maintenance reset-reviews): stale until the next merge run.
    if row["invalidated_at"] and row["last_merge_at"] and row["invalidated_at"] > row["last_merge_at"]:
        return True
    # Ids increase monotonically, so they order runs more reliably than timestamps.
    if row["last_input"] is not None and row["last_input"] > row["last_merge"]:
        return True
    return bool(row["last_override"] and row["last_merge_at"] and row["last_override"] > row["last_merge_at"])

def _add_missing_columns(cursor, table: str, columns: Dict[str, str]) -> None:
    """ALTER TABLE ADD COLUMN for every listed column the table does not have yet (older databases)."""
    have = {r["name"] for r in cursor.execute(f"PRAGMA table_info({table})")}
    for name, decl in columns.items():
        if name not in have:
            cursor.execute(f"ALTER TABLE {table} ADD COLUMN {name} {decl}")


def _backfill_override_authors(cursor) -> None:
    """Fills merge_override.created_by of older user overrides from the event log (the override event, or the review decision
    that created it). Overrides with no event stay without an author."""
    todo = [r["id"] for r in cursor.execute("SELECT id FROM merge_override WHERE created_by IS NULL AND COALESCE(source, 'user') = 'user'")]
    if not todo:
        return
    who: Dict[int, str] = {}
    for r in cursor.execute("""SELECT username, action, target_type, target_id, details_json FROM event_log
                               WHERE status = 'SUCCEEDED' AND username IS NOT NULL ORDER BY id"""):
        if r["target_type"] == "merge_override" and str(r["action"]).startswith("OVERRIDE_") and str(r["target_id"]).isdigit():
            who.setdefault(int(r["target_id"]), r["username"])
        elif str(r["action"]).startswith("REVIEW_"):
            try:
                oid = json.loads(r["details_json"] or "{}").get("override_id")
            except ValueError:
                oid = None
            if isinstance(oid, int):
                who.setdefault(oid, r["username"])
    for oid in todo:
        if oid in who:
            cursor.execute("UPDATE merge_override SET created_by = ? WHERE id = ?", (who[oid], oid))


def init_db():
    """Initializes SQLite database tables and indexes with clean, definitive schemas."""
    with get_connection() as conn:
        cursor = conn.cursor()

        # 1. Companies Catalog
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS companies (
            instrument_code TEXT PRIMARY KEY,
            name TEXT,
            sector TEXT,
            created_at TEXT,
            updated_at TEXT
        )
        """)

        # 2. Documents (One per Instrument + Fiscal Year)
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS documents (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            instrument_code TEXT NOT NULL,
            fiscal_year TEXT NOT NULL,
            filename TEXT,
            filepath TEXT,
            total_pages INTEGER DEFAULT 0,
            status TEXT DEFAULT 'UPLOADED',
            created_at TEXT,
            updated_at TEXT,
            verified_by TEXT,
            verified_at TEXT,
            verify_note TEXT,
            UNIQUE(instrument_code, fiscal_year)
        )
        """)
        # Columns added after the first release: there are no migrations, so add them to an older database here.
        _add_missing_columns(cursor, "documents", {"verified_by": "TEXT", "verified_at": "TEXT", "verify_note": "TEXT"})

        # 3. Document Pages (with OCR and Extraction states)
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS pages (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            document_id INTEGER,
            instrument_code TEXT NOT NULL,
            fiscal_year TEXT NOT NULL,
            page_number INTEGER NOT NULL,
            image_path TEXT,
            is_selected INTEGER DEFAULT 0,
            category TEXT DEFAULT 'OTHER',
            title TEXT,
            ocr_markdown TEXT,
            ocr_status TEXT DEFAULT 'PENDING',
            ocr_tokens INTEGER DEFAULT 0,
            ocr_duration_sec REAL DEFAULT 0.0,
            extraction_tokens INTEGER DEFAULT 0,
            extraction_duration_sec REAL DEFAULT 0.0,
            extraction_status TEXT DEFAULT 'PENDING',
            expected_cells INTEGER DEFAULT 0,
            extracted_cells INTEGER DEFAULT 0,
            missing_cells INTEGER DEFAULT 0,
            completeness_pct REAL DEFAULT 0.0,
            verification_status TEXT DEFAULT 'PENDING',
            created_at TEXT,
            updated_at TEXT,
            UNIQUE(instrument_code, fiscal_year, page_number),
            FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
        )
        """)

        # 4. Pipeline Runs (OCR, Extraction, and Merge runtime & token tracking)
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS pipeline_runs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            instrument_code TEXT NOT NULL,
            fiscal_year TEXT NOT NULL,
            process_type TEXT NOT NULL,
            page_numbers_json TEXT,
            total_pages INTEGER DEFAULT 0,
            duration_sec REAL DEFAULT 0.0,
            prompt_tokens INTEGER DEFAULT 0,
            candidates_tokens INTEGER DEFAULT 0,
            total_tokens INTEGER DEFAULT 0,
            model TEXT,
            details_json TEXT,
            created_at TEXT
        )
        """)
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_pr_inst_fy ON pipeline_runs(instrument_code, fiscal_year)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_pr_inst ON pipeline_runs(instrument_code)")

        # 5. Financial Metrics (Canonical 6-Dimension Architecture)
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS financial_metrics (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            document_id INTEGER,
            instrument_code TEXT NOT NULL,
            fiscal_year TEXT NOT NULL,
            page_number INTEGER NOT NULL,
            
            -- 1. Concept Domain (What is it?)
            concept_name TEXT NOT NULL,
            statement_type TEXT NOT NULL,
            category TEXT,
            aggregation_level TEXT DEFAULT 'LINE_ITEM',

            -- 2. Scope Domain (Whose is it?)
            scope TEXT DEFAULT 'CONSOLIDATED',
            entity TEXT,
            segment TEXT,

            -- 3. Behavior Domain (How does it act?)
            temporal_state TEXT DEFAULT 'PERIOD_FLOW',
            action TEXT,
            metric_type TEXT DEFAULT 'CURRENCY',
            qualifier TEXT,

            -- Display & Raw
            raw_field TEXT,
            field TEXT,
            tag TEXT DEFAULT 'FINANCIAL_METRIC',
            verification_flag TEXT DEFAULT NULL,
            flag_reason TEXT DEFAULT NULL,
            created_at TEXT,
            FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
        )
        """)
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_fm_inst_fy_page ON financial_metrics(instrument_code, fiscal_year, page_number)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_fm_inst_fy_stmt ON financial_metrics(instrument_code, fiscal_year, statement_type)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_fm_concept ON financial_metrics(concept_name)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_fm_scope ON financial_metrics(scope)")

        # 6. Metric Notes
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS financial_metric_notes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            metric_id INTEGER NOT NULL,
            note TEXT NOT NULL,
            seq_order INTEGER DEFAULT 0,
            table_num INTEGER,
            table_row INTEGER,
            table_col INTEGER,
            FOREIGN KEY (metric_id) REFERENCES financial_metrics(id) ON DELETE CASCADE
        )
        """)
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_fmn_metric_id ON financial_metric_notes(metric_id)")


        # 8. Metric Data Observations
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS financial_metric_data (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            metric_id INTEGER NOT NULL,
            year INTEGER,
            date TEXT,
            restated INTEGER DEFAULT 0,
            numeric_value REAL,
            unit TEXT,
            multiplier REAL DEFAULT 1.0,
            table_num INTEGER DEFAULT 1,
            table_row INTEGER,
            table_col INTEGER,
            seq_order INTEGER DEFAULT 0,
            FOREIGN KEY (metric_id) REFERENCES financial_metrics(id) ON DELETE CASCADE
        )
        """)
        _add_missing_columns(cursor, "financial_metric_data", {"restated": "INTEGER DEFAULT 0"})
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_fmd_metric_id ON financial_metric_data(metric_id)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_fmd_year ON financial_metric_data(year)")

        # 9. Company Results (Consolidated Multi-Year Merged Reports)
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS company_results (
            instrument_code TEXT PRIMARY KEY,
            analyzed_years_json TEXT,
            raw_csv_text TEXT,
            raw_stating_csv_text TEXT,
            merged_csv_text TEXT,
            merged_stating_csv_text TEXT,
            remaining_data_json TEXT,
            merge_report_json TEXT,
            merged_rows_json TEXT,
            conflicts_json TEXT,
            updated_at TEXT
        )
        """)

        # 10. Series merge state (see services/series_merge.py). Everything is keyed on natural keys
        # (partition, report, label key), never on financial_metrics ids, so re-extraction keeps decisions.
        for ddl in _SERIES_MERGE_DDL:
            cursor.execute(ddl)
        # a user's choice to leave a merged row out of the Excel downloads (kept while the series id is kept)
        _add_missing_columns(cursor, "series", {"excluded": "INTEGER DEFAULT 0"})
        # review items: 'extraction' ones belong to a page (replaced when it is re-extracted), 'merge' ones to the last merge
        _add_missing_columns(cursor, "merge_review_item", {"origin": "TEXT NOT NULL DEFAULT 'merge'",
                                                           "fiscal_year": "TEXT", "page_number": "INTEGER"})
        # overrides: 'user' decisions, or 'llm' links Gemini made in the last merge (rewritten by every merge)
        _add_missing_columns(cursor, "merge_override", {"source": "TEXT NOT NULL DEFAULT 'user'",
                                                        "confidence": "REAL", "link_rule": "TEXT", "created_by": "TEXT"})
        try:
            _backfill_override_authors(cursor)
        except sqlite3.OperationalError:      # event_log is created further down on a new database: nothing to read yet
            pass
        _add_missing_columns(cursor, "company_results", {"merge_invalidated_at": "TEXT"})
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_mri_origin ON merge_review_item(instrument_code, origin, fiscal_year, page_number)")

        # 11. Cell compositions: which printed cells add up to a total (identified by the extraction
        # model, checked against the printed numbers). Keyed on page coordinates; replaced per page.
        for ddl in _COMPOSITION_DDL:
            cursor.execute(ddl)

        # 12. Event log: who did what (see events.py).
        for ddl in _EVENT_DDL:
            cursor.execute(ddl)

        # 13. Disclosures a user added to a merged cell, and which disclosure of a cell is the current one.
        for ddl in _CELL_USER_DDL:
            cursor.execute(ddl)

        conn.commit()
    logger.info(f"Database initialized at {DB_PATH}")
    populate_companies_catalog()


_EVENT_DDL = [
    """CREATE TABLE IF NOT EXISTS event_log (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        created_at TEXT NOT NULL,
        finished_at TEXT,
        username TEXT NOT NULL,
        action TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'STARTED',
        instrument_code TEXT,
        fiscal_year TEXT,
        target_type TEXT,
        target_id TEXT,
        job_id TEXT,
        source TEXT NOT NULL DEFAULT 'api',
        details_json TEXT
    )""",
    "CREATE INDEX IF NOT EXISTS idx_event_log_company ON event_log(instrument_code, created_at)",
    "CREATE INDEX IF NOT EXISTS idx_event_log_user ON event_log(username, created_at)",
    "CREATE INDEX IF NOT EXISTS idx_event_log_action ON event_log(action, created_at)",
]


# A user's disclosures and choices are kept apart from the extracted data and from the merge. They are keyed on the
# series' natural identity (partition, report, label of one member) with the series id as a fallback, so they find their
# series again after a re-merge.
_CELL_USER_DDL = [
    """CREATE TABLE IF NOT EXISTS cell_disclosure (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        instrument_code TEXT NOT NULL,
        partition_key TEXT NOT NULL,
        anchor_report TEXT NOT NULL,
        anchor_label TEXT NOT NULL,
        series_id INTEGER NOT NULL,
        year INTEGER NOT NULL,
        value REAL NOT NULL,
        unit TEXT,
        date TEXT,
        note TEXT NOT NULL,
        created_by TEXT NOT NULL,
        created_at TEXT NOT NULL
    )""",
    "CREATE INDEX IF NOT EXISTS idx_cd_company ON cell_disclosure(instrument_code, year)",
    """CREATE TABLE IF NOT EXISTS cell_current (
        instrument_code TEXT NOT NULL,
        partition_key TEXT NOT NULL,
        anchor_report TEXT NOT NULL,
        anchor_label TEXT NOT NULL,
        series_id INTEGER NOT NULL,
        year INTEGER NOT NULL,
        record_key TEXT NOT NULL,
        set_by TEXT NOT NULL,
        set_at TEXT NOT NULL,
        PRIMARY KEY (instrument_code, partition_key, anchor_report, anchor_label, year)
    )""",
]

_COMPOSITION_DDL = [
    """CREATE TABLE IF NOT EXISTS line_override (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        instrument_code TEXT NOT NULL,
        fiscal_year TEXT NOT NULL,
        page_number INTEGER NOT NULL,
        table_num INTEGER NOT NULL,
        table_row INTEGER NOT NULL,
        field TEXT NOT NULL,
        note TEXT,
        updated_at TEXT NOT NULL,
        UNIQUE(instrument_code, fiscal_year, page_number, table_num, table_row)
    )""",
    """CREATE TABLE IF NOT EXISTS page_note_cell (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        instrument_code TEXT NOT NULL,
        fiscal_year TEXT NOT NULL,
        page_number INTEGER NOT NULL,
        table_num INTEGER NOT NULL,
        table_row INTEGER NOT NULL,
        table_col INTEGER NOT NULL,
        notes_json TEXT NOT NULL,
        UNIQUE(instrument_code, fiscal_year, page_number, table_num, table_row, table_col)
    )""",
    "CREATE INDEX IF NOT EXISTS idx_page_note_cell ON page_note_cell(instrument_code, fiscal_year, page_number)",
    """CREATE TABLE IF NOT EXISTS cell_composition (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        instrument_code TEXT NOT NULL,
        fiscal_year TEXT NOT NULL,
        page_number INTEGER NOT NULL,
        table_num INTEGER NOT NULL,
        direction TEXT NOT NULL,
        total_row INTEGER NOT NULL,
        total_col INTEGER NOT NULL,
        total_value REAL,
        parts_json TEXT NOT NULL,
        parts_sum REAL,
        difference REAL,
        status TEXT,
        operation TEXT NOT NULL DEFAULT 'SUM',
        created_at TEXT,
        UNIQUE(instrument_code, fiscal_year, page_number, table_num, total_row, total_col)
    )""",
    "CREATE INDEX IF NOT EXISTS idx_cell_comp_page ON cell_composition(instrument_code, fiscal_year, page_number)",
]

_SERIES_MERGE_DDL = [
    """CREATE TABLE IF NOT EXISTS series (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        instrument_code TEXT NOT NULL,
        partition_key TEXT NOT NULL,
        statement_type TEXT NOT NULL,
        scope TEXT NOT NULL,
        entity_key TEXT NOT NULL DEFAULT '',
        segment_key TEXT NOT NULL DEFAULT '',
        value_kind TEXT,
        unit TEXT,
        canonical_field TEXT,
        first_report TEXT,
        last_report TEXT,
        members_count INTEGER DEFAULT 0,
        status TEXT DEFAULT 'active',
        created_at TEXT,
        updated_at TEXT
    )""",
    "CREATE INDEX IF NOT EXISTS idx_series_inst ON series(instrument_code, status)",
    """CREATE TABLE IF NOT EXISTS series_member (
        instrument_code TEXT NOT NULL,
        partition_key TEXT NOT NULL,
        node_id TEXT NOT NULL,
        report_fy TEXT NOT NULL,
        label_key TEXT NOT NULL,
        field TEXT,
        series_id INTEGER NOT NULL,
        PRIMARY KEY (instrument_code, partition_key, node_id)
    )""",
    "CREATE INDEX IF NOT EXISTS idx_sm_series ON series_member(series_id)",
    # which series each printed cell of the last merge belongs to (the merge's cell -> series map, kept for lookups)
    """CREATE TABLE IF NOT EXISTS series_cell (
        instrument_code TEXT NOT NULL,
        report_fy TEXT NOT NULL,
        page_number INTEGER NOT NULL,
        table_num INTEGER NOT NULL,
        table_row INTEGER NOT NULL,
        table_col INTEGER NOT NULL,
        series_id INTEGER NOT NULL,
        PRIMARY KEY (instrument_code, report_fy, page_number, table_num, table_row, table_col)
    )""",
    "CREATE INDEX IF NOT EXISTS idx_sc_series ON series_cell(series_id)",
    """CREATE TABLE IF NOT EXISTS series_link (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        instrument_code TEXT NOT NULL,
        partition_key TEXT NOT NULL,
        from_node TEXT NOT NULL,
        to_node TEXT NOT NULL,
        from_report TEXT NOT NULL,
        to_report TEXT NOT NULL,
        from_label TEXT NOT NULL,
        to_label TEXT NOT NULL,
        rule TEXT NOT NULL,
        source TEXT NOT NULL,
        confidence REAL NOT NULL,
        status TEXT NOT NULL DEFAULT 'active',
        evidence_json TEXT,
        decided_at TEXT,
        UNIQUE (instrument_code, partition_key, from_node, to_node)
    )""",
    "CREATE INDEX IF NOT EXISTS idx_sl_inst ON series_link(instrument_code, partition_key)",
    """CREATE TABLE IF NOT EXISTS pair_judgement (
        instrument_code TEXT NOT NULL,
        partition_key TEXT NOT NULL,
        label_a TEXT NOT NULL,
        label_b TEXT NOT NULL,
        evidence_class TEXT NOT NULL,
        verdict INTEGER NOT NULL,
        confidence REAL NOT NULL,
        type_a TEXT,
        type_b TEXT,
        model TEXT,
        decided_at TEXT,
        PRIMARY KEY (instrument_code, partition_key, label_a, label_b, evidence_class)
    )""",
    """CREATE TABLE IF NOT EXISTS merge_override (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        instrument_code TEXT NOT NULL,
        kind TEXT NOT NULL,
        statement_type TEXT NOT NULL,
        scope TEXT NOT NULL,
        entity_key TEXT NOT NULL DEFAULT '',
        segment_key TEXT NOT NULL DEFAULT '',
        a_label_key TEXT NOT NULL,
        a_report TEXT,
        b_label_key TEXT,
        b_report TEXT,
        new_label TEXT,
        note TEXT,
        active INTEGER NOT NULL DEFAULT 1,
        created_at TEXT,
        updated_at TEXT
    )""",
    "CREATE INDEX IF NOT EXISTS idx_mo_inst ON merge_override(instrument_code, active)",
    """CREATE TABLE IF NOT EXISTS merge_review_item (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        instrument_code TEXT NOT NULL,
        kind TEXT NOT NULL,
        dedupe_key TEXT NOT NULL,
        payload_json TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'open',
        resolution TEXT,
        override_id INTEGER,
        created_at TEXT,
        updated_at TEXT,
        UNIQUE (instrument_code, kind, dedupe_key)
    )""",
    "CREATE INDEX IF NOT EXISTS idx_mri_inst ON merge_review_item(instrument_code, status)",
]


def populate_companies_catalog():
    """Populates companies table from companies.json."""
    catalog_path = COMPANIES_CATALOG_PATH
    if not catalog_path.exists():
        return 0
    try:
        with open(catalog_path, "r", encoding="utf-8") as f:
            catalog = json.load(f)
        now = datetime.utcnow().isoformat()
        with get_connection() as conn:
            cursor = conn.cursor()
            for item in catalog:
                code = item.get("security_code", "").upper().strip()
                name = item.get("company_name", "").strip() or code
                sector = item.get("sector", "").strip()
                if code:
                    cursor.execute("""
                        INSERT INTO companies (instrument_code, name, sector, created_at, updated_at)
                        VALUES (?, ?, ?, ?, ?)
                        ON CONFLICT(instrument_code) DO UPDATE SET
                            name = excluded.name,
                            sector = excluded.sector,
                            updated_at = excluded.updated_at
                    """, (code, name, sector, now, now))
            conn.commit()
        logger.info(f"Populated {len(catalog)} companies from catalog into database.")
        return len(catalog)
    except Exception as e:
        logger.error(f"Error populating companies catalog: {e}")
        return 0

# ---------------------------------------------------------------------------
# Company CRUD & Status
# ---------------------------------------------------------------------------

def get_company_info(instrument_code: str) -> Dict[str, Any]:
    """Retrieve company name, sector, and ticker for dynamic context."""
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT instrument_code, name, sector FROM companies WHERE instrument_code = ?", (code,))
        row = cursor.fetchone()
        if row:
            return {"instrument_code": row["instrument_code"], "name": row["name"], "sector": row["sector"]}
        return {"instrument_code": code, "name": code, "sector": ""}

def verification_fields(doc: Optional[Dict[str, Any]]) -> Dict[str, Any]:
    """The "a person checked this report's fiscal year" state of a documents row (a row, or None for no document)."""
    doc = dict(doc) if doc else {}
    return {
        "verified": bool(doc.get("verified_by")),
        "verified_by": doc.get("verified_by"),
        "verified_at": iso_utc(doc.get("verified_at")),
        "verify_note": doc.get("verify_note"),
    }


def set_document_verification(instrument_code: str, fiscal_year: str, verified: bool,
                              username: Optional[str] = None, note: Optional[str] = None) -> Optional[Dict[str, Any]]:
    """Marks a report's fiscal year as checked by `username` (verified=True) or clears the mark. Returns the
    document row as it was BEFORE the change (so the caller can log who had verified it), or None when the report
    is not registered."""
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        before = conn.execute("""
            SELECT id, instrument_code, fiscal_year, verified_by, verified_at, verify_note
            FROM documents WHERE instrument_code = ? AND fiscal_year = ?
        """, (code, fiscal_year)).fetchone()
        if before is None:
            return None
        if verified:
            conn.execute("UPDATE documents SET verified_by = ?, verified_at = ?, verify_note = ? WHERE id = ?",
                         (username, datetime.utcnow().isoformat(), note or None, before["id"]))
        else:
            conn.execute("UPDATE documents SET verified_by = NULL, verified_at = NULL, verify_note = NULL WHERE id = ?",
                         (before["id"],))
        conn.commit()
        return dict(before)


def get_company_status(instrument_code: str) -> Dict[str, Any]:
    """Check if an instrument code already has analyzed years and retrieve current progress."""
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        cursor = conn.cursor()

        # Check existing documents
        cursor.execute("""
            SELECT fiscal_year, status, total_pages, updated_at, verified_by, verified_at, verify_note
            FROM documents
            WHERE instrument_code = ?
            ORDER BY fiscal_year ASC
        """, (code,))
        docs = cursor.fetchall()

        # Check existing extracted metrics count per year
        cursor.execute("""
            SELECT fiscal_year, COUNT(*) as count
            FROM financial_metrics
            WHERE instrument_code = ?
            GROUP BY fiscal_year
        """, (code,))
        metrics_by_year = {row["fiscal_year"]: row["count"] for row in cursor.fetchall()}

        # Check company results
        cursor.execute("""
            SELECT analyzed_years_json, updated_at,
                   LENGTH(raw_csv_text) as raw_len,
                   LENGTH(merged_csv_text) as merged_len
            FROM company_results
            WHERE instrument_code = ?
        """, (code,))
        res = cursor.fetchone()

        analyzed_years = []
        if res and res["analyzed_years_json"]:
            try:
                analyzed_years = json.loads(res["analyzed_years_json"])
            except Exception:
                pass

        if not analyzed_years:
            doc_years = [d["fiscal_year"] for d in docs if d["fiscal_year"]]
            if doc_years:
                analyzed_years = sorted(list(set(doc_years)))
            elif metrics_by_year:
                analyzed_years = sorted(list(set(metrics_by_year.keys())))

        documents_summary = []
        for d in docs:
            fy = d["fiscal_year"]
            documents_summary.append({
                "fiscal_year": fy,
                "status": d["status"],
                "total_pages": d["total_pages"],
                "metrics_count": metrics_by_year.get(fy, 0),
                "is_extracted": metrics_by_year.get(fy, 0) > 0,
                "updated_at": iso_utc(d["updated_at"]),
                **verification_fields(d)
            })

        return {
            "instrument_code": code,
            "has_data": len(docs) > 0 or len(analyzed_years) > 0,
            "analyzed_years": analyzed_years,
            "documents": documents_summary,
            "has_merged_results": bool(res and res["merged_len"]),
            "last_updated": iso_utc(res["updated_at"]) if res else None
        }

_REPORT_STAGE_RANK = {"UPLOADED": 0, "OCR_DONE": 1, "EXTRACTED": 2}


def get_coverage_data() -> Dict[str, Any]:
    """Everything the coverage dashboard needs, in a few aggregate queries (no per-company loop except the merge
    staleness of the few merged companies): {companies: [(code, name, sector)], reports: {code: [report]},
    merged: {code: stale?}}. A report is {fiscal_year, end_year, status UPLOADED|OCR_DONE|EXTRACTED, total_pages,
    pages_selected, pages_ocr, pages_extracted, verified fields}."""
    from .services.series_keys import report_end_year

    with get_connection() as conn:
        companies = [(r["instrument_code"], r["name"] or r["instrument_code"], r["sector"] or "")
                     for r in conn.execute("SELECT instrument_code, name, sector FROM companies ORDER BY instrument_code")]
        docs = {(r["instrument_code"], r["fiscal_year"]): dict(r) for r in conn.execute("""
            SELECT instrument_code, fiscal_year, total_pages, verified_by, verified_at, verify_note, updated_at FROM documents""")}
        pages = {(r["instrument_code"], r["fiscal_year"]): dict(r) for r in conn.execute("""
            SELECT instrument_code, fiscal_year, SUM(is_selected) AS selected,
                   SUM(ocr_status = 'COMPLETED') AS ocr, SUM(extraction_status = 'COMPLETED') AS extracted
            FROM pages GROUP BY instrument_code, fiscal_year""")}
        with_metrics = {(r["instrument_code"], r["fiscal_year"]) for r in conn.execute(
            "SELECT DISTINCT instrument_code, fiscal_year FROM financial_metrics")}
        merged_codes = [r["instrument_code"] for r in conn.execute(
            "SELECT instrument_code FROM company_results WHERE LENGTH(COALESCE(merged_csv_text, '')) > 0")]

    reports: Dict[str, List[Dict[str, Any]]] = {}
    for key in sorted(set(docs) | set(pages) | with_metrics):
        code, fy = key
        d, p = docs.get(key) or {}, pages.get(key) or {}
        ocr, extracted = int(p.get("ocr") or 0), int(p.get("extracted") or 0)
        status = "EXTRACTED" if (extracted > 0 or key in with_metrics) else "OCR_DONE" if ocr > 0 else "UPLOADED"
        reports.setdefault(code, []).append({
            "fiscal_year": fy, "end_year": report_end_year(fy) or None, "status": status,
            "total_pages": d.get("total_pages") or 0, "pages_selected": int(p.get("selected") or 0),
            "pages_ocr": ocr, "pages_extracted": extracted, **verification_fields(d)})
    return {"companies": companies, "reports": reports,
            "merged": {code: is_merge_stale(code) for code in merged_codes}}


def get_companies_basic() -> List[Dict[str, Any]]:
    """All known securities (catalog seeded at startup + any registered later), without status lookups."""
    with get_connection() as conn:
        rows = conn.execute("""
            SELECT instrument_code, name, sector FROM companies ORDER BY instrument_code ASC
        """).fetchall()
    return [
        {
            "security_code": r["instrument_code"],
            "company_name": r["name"] or r["instrument_code"],
            "sector": r["sector"] or "",
        }
        for r in rows
    ]

def get_all_companies() -> List[Dict[str, Any]]:
    """List all companies recorded in SQLite."""
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT instrument_code, name, sector, updated_at FROM companies ORDER BY instrument_code ASC
        """)
        companies = []
        for row in cursor.fetchall():
            status = get_company_status(row["instrument_code"])
            companies.append({
                "instrument_code": row["instrument_code"],
                "name": row["name"] or row["instrument_code"],
                "sector": row["sector"] or "",
                "analyzed_years": status["analyzed_years"],
                "document_count": len(status["documents"]),
                "has_merged_results": status["has_merged_results"],
                "last_updated": iso_utc(row["updated_at"])
            })
        return companies

# ---------------------------------------------------------------------------
# Document & Pages Management
# ---------------------------------------------------------------------------

def upsert_company(instrument_code: str, name: Optional[str] = None, sector: Optional[str] = None):
    code = instrument_code.upper().strip()
    now = datetime.utcnow().isoformat()
    
    # Auto-lookup name and sector from companies.json if not provided
    if not name or not sector:
        catalog_path = COMPANIES_CATALOG_PATH
        if catalog_path.exists():
            try:
                with open(catalog_path, "r", encoding="utf-8") as f:
                    cat = json.load(f)
                    for item in cat:
                        if item.get("security_code", "").upper().strip() == code:
                            name = name or item.get("company_name")
                            sector = sector or item.get("sector")
                            break
            except Exception:
                pass

    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            INSERT INTO companies (instrument_code, name, sector, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?)
            ON CONFLICT(instrument_code) DO UPDATE SET
                updated_at = excluded.updated_at,
                name = CASE WHEN excluded.name = excluded.instrument_code THEN COALESCE(companies.name, excluded.name)
                            ELSE excluded.name END,
                sector = COALESCE(NULLIF(excluded.sector, ''), companies.sector)
        """, (code, name or code, sector or "", now, now))      # a call without a name / sector keeps the stored ones
        conn.commit()

def upsert_document(
    instrument_code: str,
    fiscal_year: str,
    filename: str,
    filepath: str,
    total_pages: int,
    new_file: bool = False
) -> int:
    """Creates or updates a report. new_file=True (a PDF was just uploaded for it) clears its "checked" mark: the
    person checked the fiscal year of the earlier file."""
    code = instrument_code.upper().strip()
    upsert_company(code)
    now = datetime.utcnow().isoformat()
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            INSERT INTO documents (
                instrument_code, fiscal_year, filename, filepath, total_pages, status, created_at, updated_at
            ) VALUES (?, ?, ?, ?, ?, 'UPLOADED', ?, ?)
            ON CONFLICT(instrument_code, fiscal_year) DO UPDATE SET
                filename = excluded.filename,
                filepath = COALESCE(NULLIF(excluded.filepath, ''), documents.filepath),
                total_pages = excluded.total_pages,
                status = 'UPLOADED',
                updated_at = excluded.updated_at,
                verified_by = CASE WHEN ? THEN NULL ELSE documents.verified_by END,
                verified_at = CASE WHEN ? THEN NULL ELSE documents.verified_at END,
                verify_note = CASE WHEN ? THEN NULL ELSE documents.verify_note END
        """, (code, fiscal_year, filename, filepath, total_pages, now, now, int(new_file), int(new_file), int(new_file)))
        conn.commit()

        cursor.execute("""
            SELECT id FROM documents WHERE instrument_code = ? AND fiscal_year = ?
        """, (code, fiscal_year))
        return cursor.fetchone()["id"]

def get_document_by_year(instrument_code: str, fiscal_year: str) -> Optional[Dict[str, Any]]:
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT id, instrument_code, fiscal_year, filename, filepath, total_pages, status, updated_at,
                   verified_by, verified_at, verify_note
            FROM documents
            WHERE instrument_code = ? AND fiscal_year = ?
        """, (code, fiscal_year))
        row = cursor.fetchone()
        return dict(row) if row else None

def upsert_page(
    document_id: int,
    instrument_code: str,
    fiscal_year: str,
    page_number: int,
    image_path: str,
    is_selected: bool = False,
    category: str = "OTHER",
    title: str = "",
    ocr_markdown: Optional[str] = None
):
    code = instrument_code.upper().strip()
    now = datetime.utcnow().isoformat()
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            INSERT INTO pages (
                document_id, instrument_code, fiscal_year, page_number,
                image_path, is_selected, category, title, ocr_markdown,
                ocr_status, created_at, updated_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(instrument_code, fiscal_year, page_number) DO UPDATE SET
                image_path = excluded.image_path,
                is_selected = excluded.is_selected,
                category = excluded.category,
                title = excluded.title,
                ocr_markdown = COALESCE(excluded.ocr_markdown, pages.ocr_markdown),
                ocr_status = CASE WHEN COALESCE(excluded.ocr_markdown, pages.ocr_markdown) IS NOT NULL THEN 'COMPLETED' ELSE pages.ocr_status END,
                updated_at = excluded.updated_at
        """, (
            document_id, code, fiscal_year, page_number,
            image_path, int(is_selected), category, title, ocr_markdown,
            "COMPLETED" if ocr_markdown else "PENDING", now, now
        ))
        conn.commit()
    if ocr_markdown:
        save_ocr_md_to_disk(code, fiscal_year, page_number, ocr_markdown)

def save_ocr_md_to_disk(instrument_code: str, fiscal_year: str, page_number: int, ocr_markdown: str):
    """Saves extracted Markdown to Backend/storage/ocr-md-files."""
    try:
        from .config import OCR_MD_DIR
        sub_dir = OCR_MD_DIR / instrument_code / fiscal_year
        sub_dir.mkdir(parents=True, exist_ok=True)
        md_dest_sub = sub_dir / f"page_{page_number}.md"
        with open(md_dest_sub, "w", encoding="utf-8") as f:
            f.write(ocr_markdown)

        logger.info(f"Saved OCR markdown to {md_dest_sub}")
    except Exception as e:
        logger.warning(f"Could not save markdown file to disk: {e}")

def sync_all_ocr_md_to_disk():
    """Ensures all completed OCR markdown files in the database are written to OCR_MD_DIR."""
    try:
        from .config import OCR_MD_DIR
        OCR_MD_DIR.mkdir(parents=True, exist_ok=True)
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT instrument_code, fiscal_year, page_number, ocr_markdown
                FROM pages
                WHERE ocr_markdown IS NOT NULL AND length(ocr_markdown) > 0
            """)
            rows = cursor.fetchall()
            for r in rows:
                save_ocr_md_to_disk(r["instrument_code"], r["fiscal_year"], r["page_number"], r["ocr_markdown"])
            if rows:
                logger.info(f"Synchronized {len(rows)} OCR markdown files to {OCR_MD_DIR}")
    except Exception as e:
        logger.warning(f"Could not synchronize OCR markdown files: {e}")

def update_page_ocr(
    instrument_code: str,
    fiscal_year: str,
    page_number: int,
    ocr_markdown: str,
    tokens: int = 0,
    duration_sec: float = 0.0
) -> Dict[str, Any]:
    code = instrument_code.upper().strip()
    now = datetime.utcnow().isoformat()
    with get_connection() as conn:
        cursor = conn.cursor()

        # Check existing extraction state for this page
        cursor.execute("""
            SELECT extraction_status, extraction_tokens, extracted_cells
            FROM pages
            WHERE instrument_code = ? AND fiscal_year = ? AND page_number = ?
        """, (code, fiscal_year, page_number))
        existing_page = cursor.fetchone()

        has_prior_extraction = False
        if existing_page:
            st = existing_page["extraction_status"]
            toks = existing_page["extraction_tokens"] or 0
            cells = existing_page["extracted_cells"] or 0
            if st in ("COMPLETED", "VERIFIED", "FAILED", "DEPRECATED") or toks > 0 or cells > 0:
                has_prior_extraction = True

        if not has_prior_extraction:
            cursor.execute("""
                SELECT COUNT(*) FROM financial_metrics
                WHERE instrument_code = ? AND fiscal_year = ? AND page_number = ?
            """, (code, fiscal_year, page_number))
            if cursor.fetchone()[0] > 0:
                has_prior_extraction = True

        new_extraction_status = 'DEPRECATED' if has_prior_extraction else 'PENDING'
        new_verification_status = 'DEPRECATED' if has_prior_extraction else 'PENDING'

        cursor.execute("""
            UPDATE pages
            SET ocr_markdown = ?, ocr_status = 'COMPLETED',
                ocr_tokens = ?, ocr_duration_sec = ?, updated_at = ?,
                extraction_status = ?, verification_status = ?
            WHERE instrument_code = ? AND fiscal_year = ? AND page_number = ?
        """, (ocr_markdown, tokens, duration_sec, now, new_extraction_status, new_verification_status, code, fiscal_year, page_number))
        conn.commit()

    if ocr_markdown:
        save_ocr_md_to_disk(code, fiscal_year, page_number, ocr_markdown)

    return {
        "extraction_status": new_extraction_status,
        "verification_status": new_verification_status
    }

def update_page_extraction(
    instrument_code: str,
    fiscal_year: str,
    page_number: int,
    tokens: int = 0,
    duration_sec: float = 0.0,
    status: str = "COMPLETED",
    expected_cells: int = 0,
    extracted_cells: int = 0,
    missing_cells: int = 0,
    completeness_pct: float = 0.0,
    verification_status: str = "PENDING"
):
    code = instrument_code.upper().strip()
    now = datetime.utcnow().isoformat()
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            UPDATE pages
            SET extraction_tokens = ?, extraction_duration_sec = ?,
                extraction_status = ?, expected_cells = ?,
                extracted_cells = ?, missing_cells = ?,
                completeness_pct = ?, verification_status = ?,
                updated_at = ?
            WHERE instrument_code = ? AND fiscal_year = ? AND page_number = ?
        """, (
            tokens, round(duration_sec, 2), status,
            expected_cells, extracted_cells, missing_cells,
            round(completeness_pct, 1), verification_status,
            now, code, fiscal_year, page_number
        ))
        conn.commit()

def record_pipeline_run(
    instrument_code: str,
    fiscal_year: str,
    process_type: str,
    page_numbers: List[int],
    duration_sec: float,
    total_tokens: int,
    prompt_tokens: int = 0,
    candidates_tokens: int = 0,
    model: str = "",
    details: Optional[List[Dict[str, Any]]] = None,
    total_items: Optional[int] = None
) -> int:
    """Logs a completed OCR, Extraction, or Merge pipeline run to SQLite with time and token stats."""
    code = instrument_code.upper().strip()
    now = datetime.utcnow().isoformat()
    total_count = total_items if total_items is not None else len(page_numbers)
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            INSERT INTO pipeline_runs (
                instrument_code, fiscal_year, process_type,
                page_numbers_json, total_pages, duration_sec,
                prompt_tokens, candidates_tokens, total_tokens,
                model, details_json, created_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            code, fiscal_year, process_type.upper(),
            json.dumps(page_numbers), total_count, round(duration_sec, 2),
            prompt_tokens, candidates_tokens, total_tokens,
            model, json.dumps(details or []), now
        ))
        conn.commit()
        return cursor.lastrowid

def _empty_year_usage(fiscal_year: str) -> Dict[str, Any]:
    return {
        "fiscal_year": fiscal_year,
        "ocr_tokens": 0,
        "ocr_duration_sec": 0.0,
        "extraction_tokens": 0,
        "extraction_duration_sec": 0.0,
        "merge_tokens": 0,
        "merge_duration_sec": 0.0,
        "total_tokens": 0,
        "total_duration_sec": 0.0,
        "page_count": 0
    }


_FY_RE = re.compile(r"\b\d{4}(?:-\d{2})?\b")   # "2024-25" or "2024"


def _merge_run_years(label: Optional[str], details_json: Optional[str]) -> List[str]:
    """Fiscal years a MERGE run covered: details_json's analyzed_years, else parsed from the label."""
    try:
        details = json.loads(details_json or "[]")
        years = details[0].get("analyzed_years") if details and isinstance(details[0], dict) else None
        if years:
            return [str(y) for y in years]
    except (ValueError, TypeError, AttributeError):
        pass
    return _FY_RE.findall(label or "")


def get_pipeline_usage_report(
    instrument_code: str,
    fiscal_year: Optional[str] = None,
    runs_limit: Optional[int] = None,
    runs_offset: int = 0
) -> Dict[str, Any]:
    """
    Computes total time consumption and token usage across OCR and Extraction.
    Returns executive summary, breakdown by year, breakdown by page, and run history.
    """
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        cursor = conn.cursor()

        # Page-level stats including extraction verification audits
        query = """
            SELECT instrument_code, fiscal_year, page_number, title, category,
                   ocr_tokens, ocr_duration_sec, ocr_status,
                   extraction_tokens, extraction_duration_sec, extraction_status,
                   expected_cells, extracted_cells, missing_cells,
                   completeness_pct, verification_status
            FROM pages
            WHERE instrument_code = ?
        """
        params = [code]
        if fiscal_year:
            query += " AND fiscal_year = ?"
            params.append(fiscal_year)
        query += " ORDER BY fiscal_year ASC, page_number ASC"
        cursor.execute(query, params)
        page_rows = cursor.fetchall()

        # Recent pipeline runs (filtered in Python: a MERGE run covers several years)
        cursor.execute("""
            SELECT id, process_type, fiscal_year, total_pages, duration_sec,
                   prompt_tokens, candidates_tokens, total_tokens, model, created_at, details_json
            FROM pipeline_runs
            WHERE instrument_code = ?
            ORDER BY id DESC
        """, [code])
        all_run_rows = cursor.fetchall()

    merge_run_rows = [r for r in all_run_rows if r["process_type"] == "MERGE"]
    merge_run_years = {r["id"]: _merge_run_years(r["fiscal_year"], r["details_json"]) for r in merge_run_rows}

    def _run_matches_year(run) -> bool:
        if not fiscal_year:
            return True
        if run["process_type"] == "MERGE":
            return fiscal_year in merge_run_years[run["id"]]
        return run["fiscal_year"] == fiscal_year

    run_rows = [r for r in all_run_rows if _run_matches_year(r)]
    if runs_limit and runs_limit > 0:
        start = max(0, runs_offset)
        run_rows = run_rows[start:start + runs_limit]

    pages_list = []
    total_ocr_tokens = 0
    total_ocr_duration = 0.0
    total_ext_tokens = 0
    total_ext_duration = 0.0
    by_year = {}

    for r in page_rows:
        fy = r["fiscal_year"]
        p_num = r["page_number"]
        o_tok = r["ocr_tokens"] or 0
        o_dur = r["ocr_duration_sec"] or 0.0
        e_tok = r["extraction_tokens"] or 0
        e_dur = r["extraction_duration_sec"] or 0.0

        total_ocr_tokens += o_tok
        total_ocr_duration += o_dur
        total_ext_tokens += e_tok
        total_ext_duration += e_dur

        p_data = {
            "page_number": p_num,
            "fiscal_year": fy,
            "title": r["title"] or f"Page {p_num}",
            "category": r["category"] or "FINANCIAL_STATEMENT",
            "ocr_tokens": o_tok,
            "ocr_duration_sec": round(o_dur, 2),
            "ocr_status": r["ocr_status"],
            "extraction_tokens": e_tok,
            "extraction_duration_sec": round(e_dur, 2),
            "extraction_status": r["extraction_status"] or "PENDING",
            "extraction_message": extraction_message(r["extraction_status"], r["missing_cells"]),
            "expected_cells": r["expected_cells"] or 0,
            "extracted_cells": r["extracted_cells"] or 0,
            "missing_cells": r["missing_cells"] or 0,
            "completeness_pct": r["completeness_pct"] or 0.0,
            "verification_status": r["verification_status"] or "PENDING",
            "total_tokens": o_tok + e_tok,
            "total_duration_sec": round(o_dur + e_dur, 2)
        }
        pages_list.append(p_data)

        if fy not in by_year:
            by_year[fy] = _empty_year_usage(fy)
        by_year[fy]["ocr_tokens"] += o_tok
        by_year[fy]["ocr_duration_sec"] = round(by_year[fy]["ocr_duration_sec"] + o_dur, 2)
        by_year[fy]["extraction_tokens"] += e_tok
        by_year[fy]["extraction_duration_sec"] = round(by_year[fy]["extraction_duration_sec"] + e_dur, 2)
        by_year[fy]["total_tokens"] += (o_tok + e_tok)
        by_year[fy]["total_duration_sec"] = round(by_year[fy]["total_duration_sec"] + o_dur + e_dur, 2)
        by_year[fy]["page_count"] += 1

    # Merge usage: each MERGE run's totals are split evenly across the years it covered.
    # With a fiscal_year filter, the summary counts only that year's share.
    total_merge_tokens = 0
    total_merge_duration = 0.0
    total_merge_prompt_tokens = 0
    total_merge_candidates_tokens = 0
    merge_runs_count = 0
    for r in merge_run_rows:
        years = merge_run_years[r["id"]]
        tok = r["total_tokens"] or 0
        dur = r["duration_sec"] or 0.0
        for y in years:
            if y not in by_year and (not fiscal_year or y == fiscal_year):
                by_year[y] = _empty_year_usage(y)
            if y in by_year:
                share_tok = round(tok / len(years))
                share_dur = dur / len(years)
                by_year[y]["merge_tokens"] += share_tok
                by_year[y]["merge_duration_sec"] = round(by_year[y]["merge_duration_sec"] + share_dur, 2)
                by_year[y]["total_tokens"] += share_tok
                by_year[y]["total_duration_sec"] = round(by_year[y]["total_duration_sec"] + share_dur, 2)
        if fiscal_year:
            if fiscal_year not in years:
                continue
            share = 1 / len(years)
        else:
            share = 1.0
        merge_runs_count += 1
        total_merge_tokens += round(tok * share)
        total_merge_duration += dur * share
        total_merge_prompt_tokens += round((r["prompt_tokens"] or 0) * share)
        total_merge_candidates_tokens += round((r["candidates_tokens"] or 0) * share)
    total_merge_duration = round(total_merge_duration, 2)

    recent_runs = []
    for run in run_rows:
        is_merge = run["process_type"] == "MERGE"
        recent_runs.append({
            "id": run["id"],
            "process_type": run["process_type"],
            # Company-wide runs (MERGE) have no single fiscal year.
            "fiscal_year": None if is_merge else run["fiscal_year"],
            "fiscal_year_label": run["fiscal_year"],
            "fiscal_years": merge_run_years[run["id"]] if is_merge else [run["fiscal_year"]],
            "total_pages": run["total_pages"],
            "duration_sec": run["duration_sec"],
            "prompt_tokens": run["prompt_tokens"] or 0,
            "candidates_tokens": run["candidates_tokens"] or 0,
            "total_tokens": run["total_tokens"],
            "model": run["model"] or "gemini-3.5-flash-lite",
            "created_at": iso_utc(run["created_at"])
        })

    total_combined_tokens = total_ocr_tokens + total_ext_tokens + total_merge_tokens
    total_combined_duration = round(total_ocr_duration + total_ext_duration + total_merge_duration, 2)

    total_expected = sum(p["expected_cells"] for p in pages_list)
    total_extracted = sum(p["extracted_cells"] for p in pages_list)
    total_missing = sum(p["missing_cells"] for p in pages_list)
    verified_pages_cnt = sum(1 for p in pages_list if p["verification_status"] in ("VERIFIED", "NO_DATA_PAGE"))
    failed_pages_cnt = sum(1 for p in pages_list if p["verification_status"] == "FAILED")

    return {
        "instrument_code": code,
        "fiscal_year_filter": fiscal_year,
        "summary": {
            "total_time_consumption_sec": total_combined_duration,
            "total_tokens": total_combined_tokens,
            "ocr": {
                "total_time_sec": round(total_ocr_duration, 2),
                "total_tokens": total_ocr_tokens,
                "page_count": sum(1 for p in pages_list if p["ocr_tokens"] > 0 or p["ocr_status"] == 'COMPLETED')
            },
            "extraction": {
                "total_time_sec": round(total_ext_duration, 2),
                "total_tokens": total_ext_tokens,
                "page_count": sum(1 for p in pages_list if p["extraction_tokens"] > 0 or p["extraction_status"] == 'COMPLETED')
            },
            "verification": {
                "total_expected_cells": total_expected,
                "total_extracted_cells": total_extracted,
                "total_missing_cells": total_missing,
                "verified_pages": verified_pages_cnt,
                "failed_pages": failed_pages_cnt,
                "all_verified": (failed_pages_cnt == 0 and len(pages_list) > 0)
            },
            "merge": {
                "total_time_sec": total_merge_duration,
                "total_tokens": total_merge_tokens,
                "prompt_tokens": total_merge_prompt_tokens,
                "candidates_tokens": total_merge_candidates_tokens,
                "runs_count": merge_runs_count
            },
            "total_pages_recorded": len(pages_list)
        },
        "by_year": [by_year[y] for y in sorted(by_year)],
        "pages": pages_list,
        "recent_runs": recent_runs
    }

def get_pages_for_year(instrument_code: str, fiscal_year: str) -> List[Dict[str, Any]]:
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT id, document_id, page_number, image_path, is_selected,
                   category, title, ocr_markdown, ocr_status,
                   ocr_tokens, ocr_duration_sec,
                   extraction_tokens, extraction_duration_sec, extraction_status,
                   expected_cells, extracted_cells, missing_cells,
                   completeness_pct, verification_status
            FROM pages
            WHERE instrument_code = ? AND fiscal_year = ?
            ORDER BY page_number ASC
        """, (code, fiscal_year))
        return [dict(row) for row in cursor.fetchall()]

# ---------------------------------------------------------------------------
# Financial Metrics Management
# ---------------------------------------------------------------------------

def save_extracted_metrics(
    instrument_code: str,
    fiscal_year: str,
    page_number: int,
    metrics_list: List[Dict[str, Any]],
    clear_existing_page: bool = True
):
    code = instrument_code.upper().strip()
    now = datetime.utcnow().isoformat()
    with get_connection() as conn:
        cursor = conn.cursor()

        # Find document id
        cursor.execute("SELECT id FROM documents WHERE instrument_code = ? AND fiscal_year = ?", (code, fiscal_year))
        doc_row = cursor.fetchone()
        doc_id = doc_row["id"] if doc_row else None

        if clear_existing_page:
            cursor.execute("""
                SELECT id FROM financial_metrics
                WHERE instrument_code = ? AND fiscal_year = ? AND page_number = ?
            """, (code, fiscal_year, page_number))
            existing_ids = [r["id"] for r in cursor.fetchall()]
            if existing_ids:
                placeholders = ",".join("?" * len(existing_ids))
                cursor.execute(f"DELETE FROM financial_metric_notes WHERE metric_id IN ({placeholders})", existing_ids)
                cursor.execute(f"DELETE FROM financial_metric_data WHERE metric_id IN ({placeholders})", existing_ids)
                cursor.execute(f"DELETE FROM financial_metrics WHERE id IN ({placeholders})", existing_ids)

        for item in metrics_list:
            concept = item.get("concept") or {}
            scope_dict = item.get("scope") or {}
            behavior = item.get("behavior") or {}

            # 1. Concept Domain
            c_name = concept.get("name") or item.get("concept_name") or item.get("field") or "Unknown"
            raw_stmt = str(concept.get("statement") or item.get("statement_type") or "UNKNOWN").strip().upper()
            if "APPROPRIAT" in raw_stmt or "EQUITY" in raw_stmt:
                stmt = "CHANGES_IN_EQUITY"
            elif "BALANCE" in raw_stmt or "POSITION" in raw_stmt:
                stmt = "BALANCE_SHEET"
            elif "INCOME" in raw_stmt or "PROFIT" in raw_stmt or "LOSS" in raw_stmt:
                stmt = "INCOME_STATEMENT"
            elif "CASH" in raw_stmt or "FLOW" in raw_stmt:
                stmt = "CASH_FLOW"
            elif "NOTE" in raw_stmt or "FOOTNOTE" in raw_stmt:
                stmt = "NOTES_AND_FOOTNOTES"
            elif "HIGHLIGHT" in raw_stmt:
                stmt = "FINANCIAL_HIGHLIGHTS"
            else:
                stmt = raw_stmt
            cat = concept.get("category") or item.get("category") or ""
            agg = concept.get("level") or item.get("aggregation_level") or "LINE_ITEM"

            # 2. Scope Domain
            if isinstance(scope_dict, dict):
                sc_type = scope_dict.get("type") or "CONSOLIDATED"
                ent = scope_dict.get("entity") or item.get("entity")
                seg = scope_dict.get("segment") or item.get("segment")
            else:
                sc_type = str(scope_dict or "CONSOLIDATED")
                ent = item.get("entity")
                seg = item.get("segment")

            # 3. Behavior Domain
            temporal = behavior.get("temporal") or item.get("temporal_state") or "PERIOD_FLOW"
            act = behavior.get("action") or item.get("action")
            m_type = behavior.get("metric_type") or item.get("metric_type") or "CURRENCY"
            qual = behavior.get("qualifier") or item.get("qualifier")

            # Raw & Display fields
            raw_f = item.get("raw_field") or item.get("field") or c_name
            if seg and str(seg).strip():
                display_f = f"{c_name} ({str(seg).strip()})"
            else:
                display_f = c_name

            v_flag = item.get("verification_flag")
            f_reason = item.get("flag_reason")

            cursor.execute("""
                INSERT INTO financial_metrics (
                    document_id, instrument_code, fiscal_year, page_number,
                    concept_name, statement_type, category, aggregation_level,
                    scope, entity, segment,
                    temporal_state, action, metric_type, qualifier,
                    raw_field, field, tag, verification_flag, flag_reason, created_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                doc_id, code, fiscal_year, page_number,
                c_name, stmt, cat, agg,
                sc_type, ent, seg,
                temporal, act, m_type, qual,
                raw_f, display_f, item.get("tag", "FINANCIAL_METRIC"), v_flag, f_reason, now
            ))
            metric_id = cursor.lastrowid

            # Save notes
            notes = item.get("notes") or []
            nc = item.get("note_cell") or {}
            for seq, n in enumerate(notes):
                if n is not None and str(n).strip():
                    cursor.execute("""
                        INSERT INTO financial_metric_notes (metric_id, note, seq_order, table_num, table_row, table_col)
                        VALUES (?, ?, ?, ?, ?, ?)
                    """, (metric_id, str(n).strip(), seq, nc.get("table_num"), nc.get("table_row"), nc.get("table_col")))

            # Save observation data
            data_obs = item.get("data") or []
            if isinstance(data_obs, list):
                for seq, obs in enumerate(data_obs):
                    if not isinstance(obs, dict):
                        continue
                    cursor.execute("""
                        INSERT INTO financial_metric_data (
                            metric_id, year, date, restated, numeric_value,
                            unit, multiplier,
                            table_num, table_row, table_col, seq_order
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    """, (
                        metric_id,
                        obs.get("year"),
                        obs.get("date"),
                        1 if obs.get("restated") else 0,
                        obs.get("numeric_value"),
                        obs.get("unit"),
                        obs.get("multiplier", 1.0),
                        obs.get("table_num") or 1,
                        obs.get("table_row"),
                        obs.get("table_col"),
                        seq
                    ))

        _apply_line_overrides(conn, code, fiscal_year, page_number)
        replace_page_review_items(conn, code, fiscal_year, page_number)
        conn.commit()


# What the extraction itself could not settle: reviewed right after the page is extracted (origin 'extraction'),
# replaced whenever that page is extracted again. (The merge makes no item of these kinds.)
EXTRACTION_REVIEW_KINDS = ("FIELD_UNIDENTIFIED", "LABEL_EVIDENCE_FAILED")


def replace_page_review_items(conn, code: str, fiscal_year: Optional[str] = None, page_number: Optional[int] = None) -> int:
    """Rebuilds the extraction review items of one page (of one report, or of the whole company when they are None) from the
    stored metrics: the old ones (any status) are deleted, one open item is made for every flagged printed row. A line a
    user named (USER_CONFIRMED) is no longer flagged, so it makes none. Returns the number of items made."""
    where, args = "instrument_code = ? AND COALESCE(origin, 'merge') = 'extraction'", [code]
    mwhere, margs = "m.instrument_code = ? AND m.verification_flag IN (?, ?)", [code, *EXTRACTION_REVIEW_KINDS]
    if fiscal_year is not None:
        where += " AND fiscal_year = ?"
        args.append(fiscal_year)
        mwhere += " AND m.fiscal_year = ?"
        margs.append(fiscal_year)
    if page_number is not None:
        where += " AND page_number = ?"
        args.append(page_number)
        mwhere += " AND m.page_number = ?"
        margs.append(page_number)
    conn.execute(f"DELETE FROM merge_review_item WHERE {where}", args)
    now = datetime.utcnow().isoformat()
    made = set()
    for m in conn.execute(f"""SELECT m.id, m.fiscal_year, m.page_number, m.raw_field, m.field, m.verification_flag, m.flag_reason,
                                     m.statement_type
                              FROM financial_metrics m WHERE {mwhere} ORDER BY m.fiscal_year, m.page_number, m.id""", margs).fetchall():
        data = [dict(d) for d in conn.execute("""SELECT year, numeric_value, table_num, table_row, table_col FROM financial_metric_data
                                                 WHERE metric_id = ? ORDER BY seq_order, id""", (m["id"],))]
        first = data[0] if data else {}
        kind = m["verification_flag"]
        key = f"{m['fiscal_year']}#{m['page_number']}#{first.get('table_num')}#{first.get('table_row')}"
        if (kind, key) in made:
            continue
        made.add((kind, key))
        payload = {"report": m["fiscal_year"], "page_number": m["page_number"], "metric_id": m["id"],
                   "statement_type": m["statement_type"],
                   "raw_field": m["raw_field"], "table_num": first.get("table_num"), "table_row": first.get("table_row"),
                   "table_col": first.get("table_col"), "reason": m["flag_reason"],
                   "values": {str(d.get("year")): d.get("numeric_value") for d in data}}
        if kind == "LABEL_EVIDENCE_FAILED":
            payload["field"] = m["field"]
        conn.execute("""INSERT OR REPLACE INTO merge_review_item
                        (instrument_code, kind, dedupe_key, payload_json, status, origin, fiscal_year, page_number, created_at, updated_at)
                        VALUES (?, ?, ?, ?, 'open', 'extraction', ?, ?, ?, ?)""",
                     (code, kind, key, json.dumps(payload, default=str), m["fiscal_year"], m["page_number"], now, now))
    return len(made)


def rebuild_extraction_review_items(instrument_code: Optional[str] = None) -> int:
    """Makes the extraction review items of every stored page of one company (or of every company) again."""
    with get_connection() as conn:
        codes = ([instrument_code.upper().strip()] if instrument_code
                 else [r[0] for r in conn.execute("SELECT DISTINCT instrument_code FROM financial_metrics ORDER BY 1")])
        total = sum(replace_page_review_items(conn, c) for c in codes)
        conn.commit()
    return total


def _apply_line_overrides(conn, code: str, fiscal_year: str, page_number: int) -> None:
    """Gives the lines a user named (line_override) their name again; run after a page is (re-)extracted."""
    for o in conn.execute("SELECT * FROM line_override WHERE instrument_code = ? AND fiscal_year = ? AND page_number = ?",
                          (code, fiscal_year, page_number)).fetchall():
        _rename_row_metrics(conn, code, fiscal_year, page_number, o["table_num"], o["table_row"], o["field"])


def _rename_row_metrics(conn, code, fiscal_year, page_number, table_num, table_row, field) -> int:
    """Names every metric of one printed row (all its scope / segment variants); returns how many."""
    ids = [r[0] for r in conn.execute("""
        SELECT DISTINCT m.id FROM financial_metrics m JOIN financial_metric_data d ON d.metric_id = m.id
        WHERE m.instrument_code = ? AND m.fiscal_year = ? AND m.page_number = ? AND d.table_num = ? AND d.table_row = ?""",
                           (code, fiscal_year, page_number, table_num, table_row))]
    for mid in ids:
        seg = conn.execute("SELECT segment FROM financial_metrics WHERE id = ?", (mid,)).fetchone()["segment"]
        shown = f"{field} ({str(seg).strip()})" if seg and str(seg).strip() else field
        conn.execute("""UPDATE financial_metrics SET concept_name = ?, field = ?, verification_flag = 'USER_CONFIRMED',
                        flag_reason = 'Named by a user' WHERE id = ?""", (field, shown, mid))
    return len(ids)


def set_line_override(instrument_code: str, fiscal_year: str, page_number: int, table_num: int, table_row: int,
                      field: str, note: Optional[str] = None) -> Dict[str, Any]:
    """A user names (or renames) one printed row. Stored by page coordinates, so it survives re-extraction.
    Returns {id, updated_metrics}."""
    code = instrument_code.upper().strip()
    now = datetime.utcnow().isoformat()
    with get_connection() as conn:
        conn.execute("""INSERT INTO line_override (instrument_code, fiscal_year, page_number, table_num, table_row, field, note, updated_at)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                        ON CONFLICT(instrument_code, fiscal_year, page_number, table_num, table_row)
                        DO UPDATE SET field = excluded.field, note = excluded.note, updated_at = excluded.updated_at""",
                     (code, fiscal_year, page_number, table_num, table_row, field.strip(), note, now))
        oid = conn.execute("""SELECT id FROM line_override WHERE instrument_code = ? AND fiscal_year = ? AND page_number = ?
                              AND table_num = ? AND table_row = ?""",
                           (code, fiscal_year, page_number, table_num, table_row)).fetchone()["id"]
        n = _rename_row_metrics(conn, code, fiscal_year, page_number, table_num, table_row, field.strip())
        replace_page_review_items(conn, code, fiscal_year, page_number)       # the named line is no longer unidentified
        conn.commit()
    return {"id": oid, "updated_metrics": n}


def list_line_overrides(instrument_code: str, fiscal_year: Optional[str] = None) -> List[Dict[str, Any]]:
    code = instrument_code.upper().strip()
    sql, args = "SELECT * FROM line_override WHERE instrument_code = ?", [code]
    if fiscal_year:
        sql += " AND fiscal_year = ?"
        args.append(fiscal_year)
    with get_connection() as conn:
        return [dict(r) for r in conn.execute(sql + " ORDER BY fiscal_year, page_number, table_num, table_row", args)]


def delete_line_override(instrument_code: str, override_id: int) -> bool:
    """Removes the user's name; the line keeps it until the page is extracted again."""
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        n = conn.execute("DELETE FROM line_override WHERE id = ? AND instrument_code = ?", (override_id, code)).rowcount
        conn.commit()
    return bool(n)


def get_all_metrics_for_company(instrument_code: str) -> List[Dict[str, Any]]:
    """Retrieve all structured metrics for an instrument code across all fiscal years."""
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT id, fiscal_year, page_number,
                   concept_name, statement_type, category, aggregation_level,
                   scope, entity, segment,
                   temporal_state, action, metric_type, qualifier,
                   raw_field, field, tag, verification_flag, flag_reason
            FROM financial_metrics
            WHERE instrument_code = ?
            ORDER BY fiscal_year ASC, page_number ASC, id ASC
        """, (code,))
        metric_rows = cursor.fetchall()
        if not metric_rows:
            return []

        metric_ids = [r["id"] for r in metric_rows]
        notes_by_metric: Dict[int, List[str]] = {mid: [] for mid in metric_ids}
        data_by_metric: Dict[int, List[Dict[str, Any]]] = {mid: [] for mid in metric_ids}

        chunk_size = 500
        for i in range(0, len(metric_ids), chunk_size):
            chunk = metric_ids[i:i + chunk_size]
            placeholders = ",".join("?" * len(chunk))

            # Fetch notes
            cursor.execute(f"""
                SELECT metric_id, note
                FROM financial_metric_notes
                WHERE metric_id IN ({placeholders})
                ORDER BY metric_id, seq_order, id
            """, chunk)
            for r in cursor.fetchall():
                notes_by_metric[r["metric_id"]].append(r["note"])

            # Fetch data
            cursor.execute(f"""
                SELECT metric_id, year, date, restated, numeric_value, unit, multiplier,
                       table_num, table_row, table_col
                FROM financial_metric_data
                WHERE metric_id IN ({placeholders})
                ORDER BY metric_id, seq_order, id
            """, chunk)
            for r in cursor.fetchall():
                data_by_metric[r["metric_id"]].append({
                    "year": r["year"],
                    "date": r["date"],
                    "restated": bool(r["restated"]),
                    "numeric_value": r["numeric_value"],
                    "unit": r["unit"],
                    "multiplier": r["multiplier"],
                    "table_num": r["table_num"],
                    "table_row": r["table_row"],
                    "table_col": r["table_col"]
                })

        results = []
        for row in metric_rows:
            mid = row["id"]
            sc = row["scope"] or "CONSOLIDATED"
            results.append({
                "id": mid,
                "fiscal_year": row["fiscal_year"],
                "page_number": row["page_number"],
                "statement_type": row["statement_type"],
                
                # 3 Canonical Domains
                "concept": {
                    "name": row["concept_name"] or row["field"],
                    "statement": row["statement_type"],
                    "category": row["category"] or "",
                    "level": row["aggregation_level"] or "LINE_ITEM"
                },
                "behavior": {
                    "temporal": row["temporal_state"] or "PERIOD_FLOW",
                    "action": row["action"],
                    "metric_type": row["metric_type"] or "CURRENCY",
                    "qualifier": row["qualifier"]
                },

                # Normalized flat keys for downstream pipelines & UI
                "concept_name": row["concept_name"] or row["field"],
                "category": row["category"] or "",
                "aggregation_level": row["aggregation_level"] or "LINE_ITEM",
                "scope": sc,
                "entity": row["entity"],
                "segment": row["segment"],
                "temporal_state": row["temporal_state"] or "PERIOD_FLOW",
                "action": row["action"],
                "metric_type": row["metric_type"] or "CURRENCY",
                "qualifier": row["qualifier"],
                "raw_field": row["raw_field"],
                "field": row["field"] or row["concept_name"],
                "tag": row["tag"],
                "verification_flag": row["verification_flag"],
                "flag_reason": row["flag_reason"],
                "notes": notes_by_metric.get(mid, []),
                "data": data_by_metric.get(mid, [])
            })
        return results

def get_extracted_cells_for_company(
    instrument_code: str,
    fiscal_year: Optional[str] = None
) -> Dict[str, Any]:
    """
    Returns extracted cell coordinates (table_num, table_row, table_col, value, concept, raw_field)
    grouped by f"{fiscal_year}_{page_number}" for instant frontend table highlighting during human review.
    Annotates each cell with deterministic value-match verification against the OCR markdown grid.
    """
    from .services.extraction_verifier import verify_cell_values

    code = instrument_code.upper().strip()
    with get_connection() as conn:
        cursor = conn.cursor()
        query = """
            SELECT m.fiscal_year, m.page_number, m.concept_name, m.raw_field, m.statement_type,
                   m.verification_flag, m.flag_reason,
                   d.year, d.numeric_value, d.table_num, d.table_row, d.table_col, d.unit, d.multiplier
            FROM financial_metrics m
            JOIN financial_metric_data d ON d.metric_id = m.id
            WHERE m.instrument_code = ?
        """
        params = [code]
        if fiscal_year:
            query += " AND m.fiscal_year = ?"
            params.append(fiscal_year)
        query += " ORDER BY m.fiscal_year ASC, m.page_number ASC, d.table_num ASC, d.table_row ASC, d.table_col ASC"
        cursor.execute(query, params)
        rows = cursor.fetchall()

        # Fetch ocr_markdown for pages to run deterministic value verification
        page_query = "SELECT fiscal_year, page_number, ocr_markdown FROM pages WHERE instrument_code = ?"
        page_params = [code]
        if fiscal_year:
            page_query += " AND fiscal_year = ?"
            page_params.append(fiscal_year)
        cursor.execute(page_query, page_params)
        page_mds = {(p["fiscal_year"], p["page_number"]): (p["ocr_markdown"] or "") for p in cursor.fetchall()}
        sids = series_cell_map(conn, code, fiscal_year)

    raw_by_page = {}
    for r in rows:
        key = (r["fiscal_year"], r["page_number"])
        if key not in raw_by_page:
            raw_by_page[key] = []
        raw_by_page[key].append({
            "concept_name": r["concept_name"],
            "raw_field": r["raw_field"],
            "statement_type": r["statement_type"],
            "verification_flag": r["verification_flag"],
            "flag_reason": r["flag_reason"],
            "year": r["year"],
            "numeric_value": r["numeric_value"],
            "table_num": r["table_num"] or 1,
            "table_row": r["table_row"],
            "table_col": r["table_col"],
            "unit": r["unit"],
            "multiplier": r["multiplier"],
            "series_id": sids.get((r["fiscal_year"], r["page_number"], r["table_num"] or 1, r["table_row"], r["table_col"]))
        })

    by_page = {}
    for (fy_val, pg_val), cells in raw_by_page.items():
        key = f"{fy_val}_{pg_val}"
        md_text = page_mds.get((fy_val, pg_val), "")
        if md_text:
            val_res = verify_cell_values(md_text, cells)
            by_page[key] = val_res.get("annotated_cells", cells)
        else:
            by_page[key] = cells

    return by_page

def get_extracted_cell_by_position(
    instrument_code: str,
    fiscal_year: str,
    page_number: int,
    table_num: int,
    table_row: int,
    table_col: int
) -> Dict[str, Any]:
    """
    Looks up extracted LLM metric and raw markdown content for a single cell by its spatial coordinates:
    (fiscal_year, page_number, table_num, table_row, table_col).
    Returns {"found": True, "coordinates": ..., "raw_cell_content": ..., "metric": ...} or {"found": False, ...}.
    """
    from .services.extraction_verifier import extract_markdown_table_grid
    from .services.csv_service import normalize_unit

    code = instrument_code.upper().strip()
    clean_fy = re.sub(r"^FY\s*", "", str(fiscal_year), flags=re.I).strip()
    ref = f"FY {clean_fy} | P-{page_number} | T-{table_num} | R-{table_row} | C-{table_col}"

    coords = {
        "reference": ref,
        "ref": ref,
        "instrument_code": code,
        "fiscal_year": clean_fy,
        "page_number": int(page_number),
        "table_num": int(table_num),
        "table_row": int(table_row),
        "table_col": int(table_col)
    }

    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT 
                fm.id as metric_id, fm.instrument_code, fm.fiscal_year, fm.page_number,
                fm.concept_name, fm.raw_field, fm.field as display_field,
                fm.statement_type, fm.category, fm.aggregation_level,
                COALESCE(fm.scope, 'CONSOLIDATED') as scope, fm.entity, fm.segment,
                fm.temporal_state, fm.action, fm.metric_type, fm.qualifier, fm.tag,
                fm.verification_flag, fm.flag_reason,
                fmd.id as data_id, fmd.year as data_year, fmd.date as data_date, fmd.restated as data_restated,
                fmd.numeric_value, fmd.unit, fmd.multiplier,
                fmd.table_num, fmd.table_row, fmd.table_col
            FROM financial_metrics fm
            JOIN financial_metric_data fmd ON fm.id = fmd.metric_id
            WHERE fm.instrument_code = ?
              AND fm.fiscal_year = ?
              AND fm.page_number = ?
              AND fmd.table_num = ?
              AND fmd.table_row = ?
              AND fmd.table_col = ?
            LIMIT 1
        """, (code, clean_fy, int(page_number), int(table_num), int(table_row), int(table_col)))
        row = cursor.fetchone()

        # If direct lookup yields no row, check if clean_fy is a single year (e.g. '2016')
        # and matches a report fiscal year (e.g. '2015-16') for this page and cell coordinate:
        if not row and re.match(r"^\d{4}$", clean_fy):
            cursor.execute("""
                SELECT 
                    fm.id as metric_id, fm.instrument_code, fm.fiscal_year, fm.page_number,
                    fm.concept_name, fm.raw_field, fm.field as display_field,
                    fm.statement_type, fm.category, fm.aggregation_level,
                    COALESCE(fm.scope, 'CONSOLIDATED') as scope, fm.entity, fm.segment,
                    fm.temporal_state, fm.action, fm.metric_type, fm.qualifier, fm.tag,
                    fm.verification_flag, fm.flag_reason,
                    fmd.id as data_id, fmd.year as data_year, fmd.date as data_date, fmd.restated as data_restated,
                    fmd.numeric_value, fmd.unit, fmd.multiplier,
                    fmd.table_num, fmd.table_row, fmd.table_col
                FROM financial_metrics fm
                JOIN financial_metric_data fmd ON fm.id = fmd.metric_id
                WHERE fm.instrument_code = ?
                  AND (fm.fiscal_year LIKE ? OR fmd.year = ?)
                  AND fm.page_number = ?
                  AND fmd.table_num = ?
                  AND fmd.table_row = ?
                  AND fmd.table_col = ?
                ORDER BY CASE WHEN fm.fiscal_year LIKE ? THEN 0 ELSE 1 END
                LIMIT 1
            """, (code, f"%{clean_fy}%", int(clean_fy), int(page_number), int(table_num), int(table_row), int(table_col), f"%{clean_fy}"))
            row = cursor.fetchone()
            if row:
                clean_fy = row["fiscal_year"]
                coords["fiscal_year"] = clean_fy
                coords["reference"] = f"FY {clean_fy} | P-{page_number} | T-{table_num} | R-{table_row} | C-{table_col}"
                coords["ref"] = coords["reference"]

        # Fetch raw cell text from OCR markdown
        page_row = cursor.execute(
            "SELECT ocr_markdown FROM pages WHERE instrument_code = ? AND fiscal_year = ? AND page_number = ?",
            (code, clean_fy, int(page_number))
        ).fetchone()

    raw_cell_content = None
    if page_row and page_row["ocr_markdown"]:
        grid = extract_markdown_table_grid(page_row["ocr_markdown"])
        raw_cell_content = grid.get((int(table_num), int(table_row), int(table_col)))

    if not row:
        return {
            "found": False,
            "ref": coords["reference"],
            "coordinates": coords,
            "raw_cell_content": raw_cell_content,
            "message": "No numeric metric extracted for these coordinates (cell may be a row label, header, or unextracted text)."
        }

    # Fetch notes for this metric
    notes = []
    with get_connection() as conn:
        note_rows = conn.execute("""
            SELECT note FROM financial_metric_notes
            WHERE metric_id = ?
            ORDER BY seq_order ASC, id ASC
        """, (row["metric_id"],)).fetchall()
        notes = [n["note"] for n in note_rows if n["note"]]

    # Fetch arithmetic composition links if any
    composition = get_cell_composition_links(code, clean_fy, int(page_number), int(table_num), int(table_row), int(table_col))
    if not composition.get("breakdown") and not composition.get("part_of"):
        composition = None

    with get_connection() as conn:
        series_id = series_cell_map(conn, code, clean_fy).get(
            (clean_fy, int(page_number), int(table_num), int(table_row), int(table_col)))
    val = float(row["numeric_value"] or 0)
    mult = float(row["multiplier"] or 1.0)
    eff_val = val * mult
    unit = normalize_unit(row["unit"], row["concept_name"] or "")
    formula = f"{val:,.0f} × {mult:g} = {eff_val:,.0f} {unit}".strip() if unit else f"{val:,.0f} × {mult:g} = {eff_val:,.0f}"

    return {
        "found": True,
        "ref": coords["reference"],
            "coordinates": coords,
        "raw_cell_content": raw_cell_content,
        "metric": {
            "metric_id": row["metric_id"],
            "concept_name": row["concept_name"],
            "raw_field": row["raw_field"],
            "display_field": row["display_field"],
            "statement_type": row["statement_type"],
            "category": row["category"],
            "aggregation_level": row["aggregation_level"],
            "scope": row["scope"],
            "entity": row["entity"],
            "segment": row["segment"],
            "temporal_state": row["temporal_state"],
            "action": row["action"],
            "metric_type": row["metric_type"],
            "qualifier": row["qualifier"],
            "tag": row["tag"],
            "data_year": row["data_year"],
            "data_date": row["data_date"],
            "data_restated": bool(row["data_restated"]),
            "numeric_value": val,
            "multiplier": mult,
            "effective_value": eff_val,
            "unit": unit,
            "calculation_formula": formula,
            "verification_flag": row["verification_flag"],
            "flag_reason": row["flag_reason"],
            "notes": notes,
            "composition": composition,
            "series_id": series_id
        }
    }

def save_page_note_cells(instrument_code: str, fiscal_year: str, page_number: int,
                         note_cells: List[Dict[str, Any]]) -> None:
    """Replaces the page's note cells (extraction_service.decode_note_cells)."""
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        conn.execute("DELETE FROM page_note_cell WHERE instrument_code = ? AND fiscal_year = ? AND page_number = ?",
                     (code, fiscal_year, page_number))
        conn.executemany("""INSERT OR REPLACE INTO page_note_cell (instrument_code, fiscal_year, page_number, table_num,
                                table_row, table_col, notes_json) VALUES (?, ?, ?, ?, ?, ?, ?)""",
                         [(code, fiscal_year, page_number, c["table_num"], c["table_row"], c["table_col"],
                           json.dumps(c["notes"])) for c in note_cells])
        conn.commit()


def get_note_cells_for_company(instrument_code: str, fiscal_year: Optional[str] = None) -> Dict[str, Any]:
    """Every printed cell that holds a note reference (rows with or without figures), grouped by
    "{fiscal_year}_{page_number}": [{table_num, table_row, table_col, notes: [...], raw_field, concept_name,
    metric_ids: [...]}]; raw_field is the row's printed label, metric_ids the metrics of that row (empty for a
    heading row without figures)."""
    code = instrument_code.upper().strip()
    sql = "SELECT * FROM page_note_cell WHERE instrument_code = ?"
    params: list = [code]
    if fiscal_year:
        sql += " AND fiscal_year = ?"
        params.append(fiscal_year)
    sql += " ORDER BY fiscal_year, page_number, table_num, table_row"
    with get_connection() as conn:
        cells = conn.execute(sql, params).fetchall()
        mq = """SELECT m.fiscal_year, m.page_number, d.table_num, d.table_row, m.id, m.concept_name, m.raw_field
                FROM financial_metric_data d JOIN financial_metrics m ON m.id = d.metric_id WHERE m.instrument_code = ?"""
        margs: list = [code]
        if fiscal_year:
            mq += " AND m.fiscal_year = ?"
            margs.append(fiscal_year)
        by_row: Dict[tuple, Dict[str, Any]] = {}
        for r in conn.execute(mq, margs):
            e = by_row.setdefault((r["fiscal_year"], r["page_number"], r["table_num"] or 1, r["table_row"]),
                                  {"concept_name": r["concept_name"], "raw_field": r["raw_field"], "metric_ids": []})
            if r["id"] not in e["metric_ids"]:
                e["metric_ids"].append(r["id"])
        labels = {(p["fiscal_year"], p["page_number"]): p["ocr_markdown"] or "" for p in conn.execute(
            "SELECT fiscal_year, page_number, ocr_markdown FROM pages WHERE instrument_code = ?", (code,))}
    from .services.extraction_verifier import extract_markdown_table_grid
    grids: Dict[tuple, Dict[tuple, str]] = {}
    out: Dict[str, Any] = {}
    for c in cells:
        key = (c["fiscal_year"], c["page_number"])
        if key not in grids:
            grids[key] = extract_markdown_table_grid(labels.get(key, ""))
        m = by_row.get((c["fiscal_year"], c["page_number"], c["table_num"], c["table_row"]), {})
        out.setdefault(f"{c['fiscal_year']}_{c['page_number']}", []).append({
            "table_num": c["table_num"], "table_row": c["table_row"], "table_col": c["table_col"],
            "notes": json.loads(c["notes_json"]),
            "raw_field": m.get("raw_field") or grids[key].get((c["table_num"], c["table_row"], 1), "").replace("*", "").strip(),
            "concept_name": m.get("concept_name"), "metric_ids": m.get("metric_ids", [])})
    return out


def get_note_cells_for_metrics(metric_ids: List[int]) -> Dict[int, Dict[str, Any]]:
    """{metric_id: {notes: [...], note_cell: {table_num, table_row, table_col} or None}}."""
    out: Dict[int, Dict[str, Any]] = {}
    ids = list(dict.fromkeys(metric_ids))
    with get_connection() as conn:
        for i in range(0, len(ids), 500):
            chunk = ids[i:i + 500]
            for r in conn.execute(f"""SELECT metric_id, note, table_num, table_row, table_col FROM financial_metric_notes
                                      WHERE metric_id IN ({",".join("?" * len(chunk))}) ORDER BY metric_id, seq_order""", chunk):
                e = out.setdefault(r["metric_id"], {"notes": [], "note_cell": None})
                e["notes"].append(r["note"])
                if r["table_row"] is not None and e["note_cell"] is None:
                    e["note_cell"] = {"table_num": r["table_num"], "table_row": r["table_row"], "table_col": r["table_col"]}
    return out


# ---------------------------------------------------------------------------
# Results Storage (CSV & Merged Reports)
# ---------------------------------------------------------------------------

def save_company_results(
    instrument_code: str,
    analyzed_years: List[str],
    raw_csv_text: str,
    raw_stating_csv_text: str,
    merged_csv_text: str,
    merged_stating_csv_text: str,
    remaining_data_json: str,
    merge_report_json: str,
    merged_rows_json: Optional[str] = None,
    conflicts_json: Optional[str] = None
):
    code = instrument_code.upper().strip()
    now = datetime.utcnow().isoformat()
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            INSERT INTO company_results (
                instrument_code, analyzed_years_json,
                raw_csv_text, raw_stating_csv_text,
                merged_csv_text, merged_stating_csv_text,
                remaining_data_json, merge_report_json,
                merged_rows_json, conflicts_json, updated_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(instrument_code) DO UPDATE SET
                analyzed_years_json = excluded.analyzed_years_json,
                raw_csv_text = excluded.raw_csv_text,
                raw_stating_csv_text = excluded.raw_stating_csv_text,
                merged_csv_text = CASE WHEN excluded.merged_csv_text != '' THEN excluded.merged_csv_text ELSE company_results.merged_csv_text END,
                merged_stating_csv_text = CASE WHEN excluded.merged_stating_csv_text != '' THEN excluded.merged_stating_csv_text ELSE company_results.merged_stating_csv_text END,
                remaining_data_json = excluded.remaining_data_json,
                merge_report_json = excluded.merge_report_json,
                merged_rows_json = COALESCE(excluded.merged_rows_json, company_results.merged_rows_json),
                conflicts_json = COALESCE(excluded.conflicts_json, company_results.conflicts_json),
                updated_at = excluded.updated_at
        """, (
            code, json.dumps(sorted(analyzed_years)),
            raw_csv_text, raw_stating_csv_text,
            merged_csv_text, merged_stating_csv_text,
            remaining_data_json, merge_report_json,
            merged_rows_json, conflicts_json, now
        ))
        conn.commit()

def get_company_results(instrument_code: str) -> Optional[Dict[str, Any]]:
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT * FROM company_results WHERE instrument_code = ?
        """, (code,))
        row = cursor.fetchone()
        if not row:
            return None
        return dict(row)


# ---------------------------------------------------------------------------
# Deterministic relabelling (scope, value kind, unit) -- see services/series_keys.py
# ---------------------------------------------------------------------------

def save_cell_compositions(instrument_code: str, fiscal_year: str, page_number: int,
                           compositions: List[Dict[str, Any]]) -> int:
    """Replaces the page's cell compositions (extraction_service.decode_compositions). Returns rows saved."""
    code = instrument_code.upper().strip()
    now = datetime.utcnow().isoformat()
    with get_connection() as conn:
        conn.execute("DELETE FROM cell_composition WHERE instrument_code = ? AND fiscal_year = ? AND page_number = ?",
                     (code, fiscal_year, page_number))
        conn.executemany("""
            INSERT OR REPLACE INTO cell_composition (instrument_code, fiscal_year, page_number, table_num, direction,
                total_row, total_col, total_value, parts_json, parts_sum, difference, status, created_at, operation)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, [(code, fiscal_year, page_number, c["table_num"], c["direction"], c["total"]["row"], c["total"]["col"],
               c["total"].get("value"), json.dumps(c["parts"]), c.get("parts_sum"), c.get("difference"),
               c.get("status"), now, c.get("operation") or "SUM") for c in compositions])
        conn.commit()
    return len(compositions)


def _cell_metric_map(conn, code: str, fiscal_year: Optional[str] = None) -> Dict[tuple, Dict[str, Any]]:
    """{(fiscal_year, page, table, row, col): {metric_id, field, raw_field, statement_type, scope, year}}."""
    sql = """SELECT m.fiscal_year, m.page_number, d.table_num, d.table_row, d.table_col, m.id AS metric_id,
                    m.field, m.raw_field, m.statement_type, m.scope, m.entity, d.year
             FROM financial_metric_data d JOIN financial_metrics m ON m.id = d.metric_id
             WHERE m.instrument_code = ?"""
    args: list = [code]
    if fiscal_year:
        sql += " AND m.fiscal_year = ?"
        args.append(fiscal_year)
    out = {}
    for r in conn.execute(sql, args).fetchall():
        out[(r["fiscal_year"], r["page_number"], r["table_num"] or 1, r["table_row"], r["table_col"])] = {
            "metric_id": r["metric_id"], "field": r["field"], "raw_field": r["raw_field"],
            "statement_type": r["statement_type"], "scope": r["scope"], "entity": r["entity"], "year": r["year"]}
    return out


def get_cell_compositions(instrument_code: str, fiscal_year: Optional[str] = None,
                          page_number: Optional[int] = None) -> List[Dict[str, Any]]:
    """Compositions with every total and part cell resolved to its extracted metric (field, raw_field,
    metric_id, year; null when the model did not list that row as a metric)."""
    code = instrument_code.upper().strip()
    sql = "SELECT * FROM cell_composition WHERE instrument_code = ?"
    args: list = [code]
    if fiscal_year:
        sql += " AND fiscal_year = ?"
        args.append(fiscal_year)
    if page_number is not None:
        sql += " AND page_number = ?"
        args.append(page_number)
    sql += " ORDER BY fiscal_year, page_number, table_num, total_row, total_col"
    with get_connection() as conn:
        rows = conn.execute(sql, args).fetchall()
        cells = _cell_metric_map(conn, code, fiscal_year) if rows else {}
        sids = series_cell_map(conn, code, fiscal_year) if rows else {}
    out = []
    for r in rows:
        fy, pg, tn = r["fiscal_year"], r["page_number"], r["table_num"]

        def info(row, col):
            return cells.get((fy, pg, tn, row, col)) or {}

        t = info(r["total_row"], r["total_col"])
        parts = []
        for p in json.loads(r["parts_json"] or "[]"):
            pi = info(p["row"], p["col"])
            v = p.get("value")
            applied = None if v is None else (-abs(v) if p["sign"] < 0 else v)   # the amount the part adds to the total
            parts.append({**p, "applied": applied, "field": pi.get("field"), "raw_field": pi.get("raw_field"),
                          "metric_id": pi.get("metric_id"), "year": pi.get("year"),
                          "series_id": sids.get((fy, pg, tn, p["row"], p["col"]))})
        op = r["operation"] or "SUM"
        names = [p.get("field") or p.get("raw_field") or f"row {p['row']}" for p in parts]
        if op == "SUM":
            expression = " ".join((("- " if p["sign"] < 0 else "+ ") if i else ("- " if p["sign"] < 0 else "")) + n
                                  for i, (p, n) in enumerate(zip(parts, names)))
        else:
            expression = {"RATIO": " / ", "PERCENT": " / ", "PRODUCT": " x "}[op].join(names) + (" x 100" if op == "PERCENT" else "")
        out.append({
            "id": r["id"], "fiscal_year": fy, "page_number": pg, "table_num": tn, "direction": r["direction"],
            "operation": op, "expression": expression,
            "total": {"row": r["total_row"], "col": r["total_col"], "value": r["total_value"],
                      "field": t.get("field"), "raw_field": t.get("raw_field"), "metric_id": t.get("metric_id"),
                      "year": t.get("year"), "statement_type": t.get("statement_type"), "scope": t.get("scope"),
                      "series_id": sids.get((fy, pg, tn, r["total_row"], r["total_col"]))},
            "parts": parts, "parts_sum": r["parts_sum"], "difference": r["difference"], "status": r["status"],
        })
    return out


def get_cell_composition_links(instrument_code: str, fiscal_year: str, page_number: int, table_num: int,
                               row: int, col: int) -> Dict[str, Any]:
    """For one printed cell: {"breakdown": the composition whose total it is (or None),
    "part_of": compositions it is a part of}."""
    comps = get_cell_compositions(instrument_code, fiscal_year, page_number)
    breakdown = next((c for c in comps if c["table_num"] == table_num
                      and (c["total"]["row"], c["total"]["col"]) == (row, col)), None)
    part_of = [c for c in comps if c["table_num"] == table_num
               and any((p["row"], p["col"]) == (row, col) for p in c["parts"])]
    return {"breakdown": breakdown, "part_of": part_of}


def get_pages_markdown(instrument_code: str) -> Dict[tuple, str]:
    """{(fiscal_year, page_number): ocr_markdown} for every page of a company."""
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        rows = conn.execute(
            "SELECT fiscal_year, page_number, ocr_markdown FROM pages WHERE instrument_code = ?", (code,)
        ).fetchall()
    return {(r["fiscal_year"], r["page_number"]): (r["ocr_markdown"] or "") for r in rows}


def get_report_label_context(instrument_code: str, fiscal_year: str, max_previous: int = 40) -> Dict[str, Any]:
    """Context the extraction model needs to label scope and statement type consistently:
    whether the report has consolidated statements (printed titles or column headers), the titles
    printed on its pages, and how the previous report labelled its headings (only labels that passed
    the evidence check)."""
    from .services import series_keys as sk
    from .services.csv_service import extract_year_int

    code = instrument_code.upper().strip()
    pages = {p: md for (fy, p), md in get_pages_markdown(code).items() if fy == fiscal_year}
    titles, has_cons = [], False
    for p in sorted(pages):
        md = pages[p]
        head = sk.page_heading(md, max_lines=6)
        if head:
            titles.append({"page": p, "title": head[:200]})
        if sk.page_title_scope(md) == sk.CONSOLIDATED or re.search(
                r"^\s*\|.*\bconsolidated\b.*\|", md, re.I | re.M):
            has_cons = True

    previous_fy, previous = None, []
    end = extract_year_int(fiscal_year) or 0
    with get_connection() as conn:
        reports = [r[0] for r in conn.execute(
            "SELECT DISTINCT fiscal_year FROM financial_metrics WHERE instrument_code = ?", (code,))]
        earlier = sorted((r for r in reports if (extract_year_int(r) or 0) < end), key=lambda r: (extract_year_int(r) or 0, r))
        if earlier:
            previous_fy = earlier[-1]
            rows = conn.execute("""
                SELECT category, statement_type, scope, COUNT(*) n FROM financial_metrics
                WHERE instrument_code = ? AND fiscal_year = ? AND COALESCE(category, '') != ''
                  AND COALESCE(verification_flag, '') != 'LABEL_EVIDENCE_FAILED'
                GROUP BY category, statement_type, scope ORDER BY n DESC LIMIT ?
            """, (code, previous_fy, max_previous)).fetchall()
            previous = [{"heading": r["category"], "statement": r["statement_type"], "scope": r["scope"]} for r in rows]
    return {"fiscal_year": fiscal_year, "has_consolidated": has_cons, "page_titles": titles,
            "previous_fiscal_year": previous_fy, "previous_labels": previous}


def relabel_company(instrument_code: str, fiscal_year: Optional[str] = None) -> Dict[str, int]:
    """Rewrites metric_type and unit of stored metrics deterministically (no AI): metric_type is one of
    CURRENCY / PER_SHARE / COUNT / PERCENT / RATIO and every observation of the metric gets the
    matching unit (BDT, BDT/share, Nos., %, Times). Idempotent.

    Scope is NOT rewritten: the extraction model labels it from the printed headings and its evidence
    is checked at extraction time. "scope" counts metrics whose stored scope disagrees with the
    report's printed statement titles (series_keys.resolve_report_scopes), for the label audit only.
    Returns counts of changed metrics."""
    from .services import series_keys as sk

    code = instrument_code.upper().strip()
    pages_md = get_pages_markdown(code)
    changed = {"scope_disagreements": 0, "metric_type": 0, "unit": 0}
    with get_connection() as conn:
        q = """SELECT id, fiscal_year, page_number, scope, metric_type, raw_field, field
               FROM financial_metrics WHERE instrument_code = ?"""
        params: list = [code]
        if fiscal_year:
            q += " AND fiscal_year = ?"
            params.append(fiscal_year)
        metrics = [dict(r) for r in conn.execute(q, params).fetchall()]
        if not metrics:
            return changed
        data: Dict[int, list] = {}
        ids = [m["id"] for m in metrics]
        for i in range(0, len(ids), 500):
            chunk = ids[i:i + 500]
            for r in conn.execute(
                f"SELECT metric_id, unit, numeric_value FROM financial_metric_data WHERE metric_id IN ({','.join('?' * len(chunk))})",
                chunk,
            ):
                data.setdefault(r["metric_id"], []).append((r["unit"], r["numeric_value"]))

        by_report: Dict[str, list] = {}
        for m in metrics:
            by_report.setdefault(m["fiscal_year"], []).append(m)

        scope_updates, type_updates, unit_updates = [], [], []
        for fy, ms in by_report.items():
            model_scopes: Dict[int, set] = {}
            for m in ms:
                sc = "STANDALONE" if "STANDALONE" in str(m["scope"] or "").upper() else "CONSOLIDATED"
                model_scopes.setdefault(m["page_number"], set()).add(sc)
            pages = {p: md for (f, p), md in pages_md.items() if f == fy}
            page_scope = sk.resolve_report_scopes(pages, model_scopes)
            for m in ms:
                new_scope = page_scope.get(m["page_number"])
                if new_scope and new_scope != m["scope"]:
                    scope_updates.append((new_scope, m["id"]))
                obs = data.get(m["id"], [])
                kind = sk.value_kind(f"{m['raw_field'] or ''} {m['field'] or ''}", [u for u, _ in obs],
                                     m["metric_type"], [v for _, v in obs])
                if kind != m["metric_type"]:
                    type_updates.append((kind, m["id"]))
                unit = sk.unit_for_kind(kind, [u for u, _ in obs])
                if any(u != unit for u, _ in obs):
                    unit_updates.append((unit, m["id"]))

        conn.executemany("UPDATE financial_metrics SET metric_type = ? WHERE id = ?", type_updates)
        conn.executemany("UPDATE financial_metric_data SET unit = ? WHERE metric_id = ?", unit_updates)
        conn.commit()
    changed.update(scope_disagreements=len(scope_updates), metric_type=len(type_updates), unit=len(unit_updates))
    return changed


def clear_all_merges() -> int:
    """Deletes every saved merge result (merged CSVs, rows, report, conflicts) and the series merge
    state; raw CSVs, extracted metrics, pipeline runs and user overrides are kept. Returns companies cleared."""
    with get_connection() as conn:
        n = conn.execute("""
            UPDATE company_results SET merged_csv_text = '', merged_stating_csv_text = '',
                   merge_report_json = '[]', merged_rows_json = NULL, conflicts_json = NULL
        """).rowcount
        for table in ("series", "series_member", "series_cell", "series_link", "merge_review_item"):
            conn.execute(f"DELETE FROM {table}")
        conn.commit()
    return n


# ---------------------------------------------------------------------------
# Series merge state
# ---------------------------------------------------------------------------

def load_active_links(instrument_code: str) -> List[Dict[str, Any]]:
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        rows = conn.execute("SELECT * FROM series_link WHERE instrument_code = ? AND status = 'active'", (code,)).fetchall()
    return [dict(r) for r in rows]


def _insert_series_cells(conn, code: str, series: List[Dict[str, Any]], ids: List[int]) -> int:
    """series_cell rows of a merge: every printed cell of every member line, with the series it ended up in."""
    rows = {}
    for s, sid in zip(series, ids):
        for n in s["nodes"]:
            for (pg, tn, row, col) in n.get("cells") or ():
                if pg is None or row is None or col is None:
                    continue
                rows[(code, n["report"], pg, tn or 1, row, col)] = sid
    conn.executemany("""INSERT OR REPLACE INTO series_cell (instrument_code, report_fy, page_number, table_num, table_row,
                        table_col, series_id) VALUES (?, ?, ?, ?, ?, ?, ?)""", [k + (v,) for k, v in rows.items()])
    return len(rows)


def rebuild_series_cells(instrument_code: str, nodes: List[Dict[str, Any]]) -> int:
    """Fills series_cell for a company from its saved series without a new merge: `nodes` are the line items of
    series_linker.build_nodes (rebuilt from the stored metrics, the same ids as in the merge); each is matched to the
    saved series through series_member (partition and node id). Returns the number of cells stored."""
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        sid_of = {(r["partition_key"], r["node_id"]): r["series_id"]
                  for r in conn.execute("SELECT partition_key, node_id, series_id FROM series_member WHERE instrument_code = ?", (code,))}
        kept = [(n, sid_of[(n["partition"], n["id"])]) for n in nodes if (n["partition"], n["id"]) in sid_of]
        conn.execute("DELETE FROM series_cell WHERE instrument_code = ?", (code,))
        n = _insert_series_cells(conn, code, [{"nodes": [node]} for node, _ in kept], [sid for _, sid in kept])
        conn.commit()
    return n


def series_cell_map(conn, code: str, fiscal_year: Optional[str] = None) -> Dict[tuple, int]:
    """{(report_fy, page, table, row, col): series_id} of the last merge (empty before the first merge)."""
    sql, args = "SELECT * FROM series_cell WHERE instrument_code = ?", [code]
    if fiscal_year:
        sql += " AND report_fy = ?"
        args.append(fiscal_year)
    return {(r["report_fy"], r["page_number"], r["table_num"], r["table_row"], r["table_col"]): r["series_id"]
            for r in conn.execute(sql, args)}


def get_series_id_for_metric(instrument_code: str, metric_id: int) -> Optional[int]:
    """The series (merged row) a stored metric belongs to, from the last merge; None when it is in no series."""
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        row = conn.execute("""SELECT sc.series_id FROM financial_metrics m
                              JOIN financial_metric_data d ON d.metric_id = m.id
                              JOIN series_cell sc ON sc.instrument_code = m.instrument_code AND sc.report_fy = m.fiscal_year
                                   AND sc.page_number = m.page_number AND sc.table_num = COALESCE(d.table_num, 1)
                                   AND sc.table_row = d.table_row AND sc.table_col = d.table_col
                              WHERE m.id = ? AND m.instrument_code = ? LIMIT 1""", (metric_id, code)).fetchone()
    return row["series_id"] if row else None


def save_series_run(instrument_code: str, series: List[Dict[str, Any]], links: List[Dict[str, Any]],
                    review_items: List[Dict[str, Any]],
                    llm_overrides: Optional[List[Dict[str, Any]]] = None) -> List[int]:
    """Saves one merge run in a single transaction and returns the series id of each series.

    Series ids are kept stable between runs: a new series reuses the id of the old series that shared
    most of its members (at least half). Every review item of origin 'merge' is replaced by the new ones
    (extraction items are left alone); llm_overrides replace the company's overrides of source 'llm'."""
    code = instrument_code.upper().strip()
    now = datetime.utcnow().isoformat()
    with get_connection() as conn:
        old_members: Dict[int, set] = {}
        for r in conn.execute("SELECT series_id, partition_key, node_id FROM series_member WHERE instrument_code = ?", (code,)):
            old_members.setdefault(r["series_id"], set()).add((r["partition_key"], r["node_id"]))
        node_to_old = {m: sid for sid, ms in old_members.items() for m in ms}

        ids: List[int] = []
        taken: set = set()
        for s in series:
            members = {(n["partition"], n["id"]) for n in s["nodes"]}
            counts: Dict[int, int] = {}
            for m in members:
                sid = node_to_old.get(m)
                if sid is not None:
                    counts[sid] = counts.get(sid, 0) + 1
            reuse = None
            for sid, c in sorted(counts.items(), key=lambda x: -x[1]):
                if sid not in taken and c / len(members | old_members[sid]) >= 0.5:
                    reuse = sid
                    break
            first, last = s["nodes"][0], s["nodes"][-1]
            values = (code, s["partition"], last["statement_type"], last["scope"], last["entity_key"],
                      last["segment_key"], last["kind"], last["unit"], last["fields"][0] if last["fields"] else last["label"],
                      first["report"], last["report"], len(s["nodes"]), now)
            if reuse is not None:
                conn.execute("""UPDATE series SET instrument_code=?, partition_key=?, statement_type=?, scope=?, entity_key=?,
                                segment_key=?, value_kind=?, unit=?, canonical_field=?, first_report=?, last_report=?,
                                members_count=?, updated_at=?, status='active' WHERE id=?""", values + (reuse,))
                ids.append(reuse)
                taken.add(reuse)
            else:
                cur = conn.execute("""INSERT INTO series (instrument_code, partition_key, statement_type, scope, entity_key,
                                      segment_key, value_kind, unit, canonical_field, first_report, last_report,
                                      members_count, updated_at, created_at, status)
                                      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'active')""", values + (now,))
                ids.append(cur.lastrowid)
        stale_ids = [sid for sid in old_members if sid not in taken]
        if stale_ids:
            conn.execute(f"UPDATE series SET status = 'retired', updated_at = ? WHERE id IN ({','.join('?' * len(stale_ids))})",
                         [now] + stale_ids)

        conn.execute("DELETE FROM series_member WHERE instrument_code = ?", (code,))
        conn.executemany("""INSERT INTO series_member (instrument_code, partition_key, node_id, report_fy, label_key, field, series_id)
                            VALUES (?, ?, ?, ?, ?, ?, ?)""",
                         [(code, n["partition"], n["id"], n["report"], n["label"], n["fields"][0] if n["fields"] else n["label"], sid)
                          for s, sid in zip(series, ids) for n in s["nodes"]])

        conn.execute("DELETE FROM series_cell WHERE instrument_code = ?", (code,))
        _insert_series_cells(conn, code, series, ids)

        conn.execute("DELETE FROM series_link WHERE instrument_code = ?", (code,))
        conn.executemany("""INSERT INTO series_link (instrument_code, partition_key, from_node, to_node, from_report, to_report,
                            from_label, to_label, rule, source, confidence, status, evidence_json, decided_at)
                            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'active', ?, ?)""",
                         [(code, l["partition"], l["from_node"], l["to_node"], l["from_report"], l["to_report"],
                           l["from_label"], l["to_label"], l["rule"], l["source"], l["confidence"],
                           json.dumps({**l["evidence"], "gap": l.get("gap", 0)}, default=str), now) for l in links])

        sid_of_node = {(n["partition"], n["id"]): sid for s, sid in zip(series, ids) for n in s["nodes"]}
        for item in review_items:  # timeline lines (SPLIT/MERGE_DETECTED) learn their series id
            tl = item["payload"].get("timeline") if isinstance(item["payload"], dict) else None
            for line in (tl or {}).get("lines", []):
                line["series_id"] = sid_of_node.get((item["payload"].get("partition"), line.get("node_id")))

        # Merge review items are replaced as a whole by every merge (extraction ones belong to their page and stay).
        # An item a user override already decides arrives with status 'resolved' (series_merge.auto_resolve).
        conn.execute("DELETE FROM merge_review_item WHERE instrument_code = ? AND COALESCE(origin, 'merge') = 'merge'", (code,))
        seen = set()
        for item in review_items:
            key = (item["kind"], item["dedupe_key"])
            if key in seen:
                continue
            seen.add(key)
            conn.execute("""INSERT OR REPLACE INTO merge_review_item
                            (instrument_code, kind, dedupe_key, payload_json, status, resolution, override_id, origin, created_at, updated_at)
                            VALUES (?, ?, ?, ?, ?, ?, ?, 'merge', ?, ?)""",
                         (code, item["kind"], item["dedupe_key"], json.dumps(item["payload"], default=str),
                          item.get("status") or "open", item.get("resolution"), item.get("override_id"), now, now))

        # The links Gemini made in this merge are listed as overrides of type 'llm' (the previous ones are replaced).
        conn.execute("DELETE FROM merge_override WHERE instrument_code = ? AND source = 'llm'", (code,))
        for o in llm_overrides or []:
            conn.execute("""INSERT INTO merge_override (instrument_code, kind, statement_type, scope, entity_key, segment_key,
                            a_label_key, a_report, b_label_key, b_report, note, active, created_at, updated_at,
                            source, confidence, link_rule)
                            VALUES (?, 'FORCE_LINK', ?, ?, ?, ?, ?, ?, ?, ?, ?, 1, ?, ?, 'llm', ?, ?)""",
                         (code, o["statement_type"], o["scope"], o.get("entity_key") or "", o.get("segment_key") or "",
                          o["a_label_key"], o["a_report"], o["b_label_key"], o["b_report"], o.get("note"), now, now,
                          o.get("confidence"), o.get("link_rule")))
        conn.execute("UPDATE company_results SET merge_invalidated_at = NULL WHERE instrument_code = ?", (code,))
        conn.commit()
    return ids


def set_series_active(instrument_code: str, series_ids: List[int], active: bool) -> Dict[str, Any]:
    """Marks merged rows (series of the last merge) active or inactive; inactive ones are left out of the Excel
    downloads. Returns {"updated": ids changed, "unchanged": ids already in that state, "unknown": ids that are
    no active series of the company}. The flag stays with the series id, so it survives a re-merge that keeps the id."""
    code = instrument_code.upper().strip()
    ids = list(dict.fromkeys(series_ids))
    with get_connection() as conn:
        known = {r["id"]: r["excluded"] for r in conn.execute(
            f"SELECT id, COALESCE(excluded, 0) AS excluded FROM series WHERE instrument_code = ? AND status = 'active' "
            f"AND id IN ({','.join('?' * len(ids))})", [code] + ids)} if ids else {}
        want = 0 if active else 1
        changed = [i for i in ids if i in known and known[i] != want]
        if changed:
            conn.execute(f"UPDATE series SET excluded = ? WHERE id IN ({','.join('?' * len(changed))})", [want] + changed)
            conn.commit()
    return {"updated": changed, "unchanged": [i for i in ids if i in known and i not in changed],
            "unknown": [i for i in ids if i not in known]}


def get_inactive_series_ids(instrument_code: str) -> set:
    """Ids of the company's series a user marked inactive."""
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        return {r["id"] for r in conn.execute(
            "SELECT id FROM series WHERE instrument_code = ? AND status = 'active' AND excluded = 1", (code,))}


def list_series(instrument_code: str) -> List[Dict[str, Any]]:
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        rows = [dict(r) for r in conn.execute(
            "SELECT * FROM series WHERE instrument_code = ? AND status = 'active' ORDER BY statement_type, canonical_field, id",
            (code,))]
        members: Dict[int, list] = {}
        for r in conn.execute("""SELECT series_id, report_fy, label_key, field, node_id FROM series_member
                                 WHERE instrument_code = ? ORDER BY report_fy""", (code,)):
            members.setdefault(r["series_id"], []).append(
                {"report": r["report_fy"], "field": r["field"], "label_key": r["label_key"], "node_id": r["node_id"]})
    for r in rows:
        r["active"] = not r.pop("excluded", 0)
        r["members"] = members.get(r["id"], [])
        r["updated_at"] = iso_utc(r["updated_at"])
        r["created_at"] = iso_utc(r["created_at"])
    return rows


def get_series_evidence(instrument_code: str, series_id: int) -> Optional[Dict[str, Any]]:
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        s = conn.execute("SELECT * FROM series WHERE instrument_code = ? AND id = ?", (code, series_id)).fetchone()
        if not s:
            return None
        members = [dict(r) for r in conn.execute(
            "SELECT report_fy, label_key, field, node_id, partition_key FROM series_member WHERE series_id = ? ORDER BY report_fy",
            (series_id,))]
        nodes = {m["node_id"] for m in members}
        part = members[0]["partition_key"] if members else s["partition_key"]
        links = []
        for r in conn.execute("SELECT * FROM series_link WHERE instrument_code = ? AND partition_key = ?", (code, part)):
            if r["from_node"] in nodes and r["to_node"] in nodes:
                d = dict(r)
                d["evidence"] = json.loads(d.pop("evidence_json") or "{}")
                d["gap"] = d["evidence"].get("gap", 0)
                d["decided_at"] = iso_utc(d["decided_at"])
                links.append(d)
    links.sort(key=lambda l: l["to_report"])
    out = dict(s)
    out["updated_at"] = iso_utc(out["updated_at"])
    out["created_at"] = iso_utc(out["created_at"])
    out["members"] = members
    out["links"] = links
    return out


def get_series_formation(instrument_code: str, series_id: int) -> Optional[Dict[str, Any]]:
    """How a series (merged row) was formed: `{series_id, canonical_field, statement_type, scope, unit, value_kind, members: [...]}`.
    A member is one line of one report (`report`, `field`, `node_id`, `link_rule` / `link_confidence` of the link that joined it to
    the earlier line) with its `metrics`: every stored metric (`metric_id`, names, concept, type, page) holding the printed cells that
    ended up in the series, each with its `values` (`data_id`, year, date, restated, value, unit, multiplier, table coordinates).
    None when the series does not exist. Metrics are matched to a member by report and label key; a metric that fits no member of its
    report goes to `unmatched_metrics`."""
    from .services import series_keys as sk
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        s = conn.execute("SELECT * FROM series WHERE instrument_code = ? AND id = ?", (code, series_id)).fetchone()
        if not s:
            return None
        members = [dict(r) for r in conn.execute(
            "SELECT report_fy, label_key, field, node_id, partition_key FROM series_member WHERE series_id = ? ORDER BY report_fy, node_id",
            (series_id,))]
        links = {r["to_node"]: r for r in conn.execute(
            "SELECT to_node, rule, confidence FROM series_link WHERE instrument_code = ? AND partition_key = ?",
            (code, members[0]["partition_key"] if members else s["partition_key"]))}
        rows = [dict(r) for r in conn.execute("""
            SELECT fm.id AS metric_id, fm.fiscal_year AS report, fm.page_number, fm.statement_type, fm.concept_name, fm.field,
                   fm.raw_field, fm.metric_type, fm.scope, fm.aggregation_level, fm.category,
                   d.id AS data_id, d.year, d.date, d.restated, d.numeric_value, d.unit, d.multiplier,
                   d.table_num, d.table_row, d.table_col
            FROM series_cell sc
            JOIN financial_metrics fm ON fm.instrument_code = sc.instrument_code AND fm.fiscal_year = sc.report_fy
                 AND fm.page_number = sc.page_number
            JOIN financial_metric_data d ON d.metric_id = fm.id AND COALESCE(d.table_num, 1) = sc.table_num
                 AND d.table_row = sc.table_row AND d.table_col = sc.table_col
            WHERE sc.instrument_code = ? AND sc.series_id = ?
            ORDER BY fm.fiscal_year, fm.id, d.year, d.seq_order""", (code, series_id))]
    out_members = []
    for m in members:
        l = links.get(m["node_id"])
        out_members.append({"report": m["report_fy"], "field": m["field"], "label_key": m["label_key"], "node_id": m["node_id"],
                            "link_rule": l["rule"] if l else None, "link_confidence": l["confidence"] if l else None,
                            "metrics": []})
    by_metric: Dict[int, Dict[str, Any]] = {}
    for r in rows:
        met = by_metric.get(r["metric_id"])
        if met is None:
            met = by_metric[r["metric_id"]] = {
                "metric_id": r["metric_id"], "report": r["report"], "page_number": r["page_number"],
                "statement_type": r["statement_type"], "concept_name": r["concept_name"], "field": r["field"],
                "raw_field": r["raw_field"], "metric_type": r["metric_type"], "scope": r["scope"],
                "aggregation_level": r["aggregation_level"], "category": r["category"], "values": []}
        met["values"].append({"data_id": r["data_id"], "year": r["year"], "date": r["date"], "restated": bool(r["restated"]),
                              "value": r["numeric_value"], "unit": r["unit"], "multiplier": r["multiplier"],
                              "table_num": r["table_num"] or 1, "table_row": r["table_row"], "table_col": r["table_col"]})
    unmatched = []
    for met in by_metric.values():
        key = sk.label_key(met["field"])
        cands = [m for m in out_members if m["report"] == met["report"]]
        target = next((m for m in cands if m["label_key"] == key), None) or (cands[0] if len(cands) == 1 else None)
        (target["metrics"] if target else unmatched).append(met)
    return {"series_id": series_id, "canonical_field": s["canonical_field"], "statement_type": s["statement_type"],
            "scope": s["scope"], "unit": s["unit"], "value_kind": s["value_kind"], "status": s["status"],
            "active": not s["excluded"], "members": out_members, "unmatched_metrics": unmatched}


def list_review_items(instrument_code: str, status: Optional[str] = "open") -> List[Dict[str, Any]]:
    code = instrument_code.upper().strip()
    q = "SELECT * FROM merge_review_item WHERE instrument_code = ?"
    params: list = [code]
    if status:
        q += " AND status = ?"
        params.append(status)
    with get_connection() as conn:
        rows = [dict(r) for r in conn.execute(q + " ORDER BY kind, id", params)]
    from .services import series_linker as sl

    for r in rows:
        r["payload"] = json.loads(r.pop("payload_json") or "{}")
        r["created_at"] = iso_utc(r["created_at"])
        r["updated_at"] = iso_utc(r["updated_at"])
        r["attention"] = sl.review_attention(r["kind"], r["payload"])
        r["attention_rank"] = sl.ATTENTION_RANK[r["attention"]]
    rows.sort(key=lambda r: (r["attention_rank"], r["kind"], r["id"]))      # most important first
    return rows


def get_review_item(instrument_code: str, item_id: int) -> Optional[Dict[str, Any]]:
    for r in list_review_items(instrument_code, status=None):
        if r["id"] == item_id:
            return r
    return None


def set_review_item_status(item_id: int, status: str, resolution: Optional[str] = None,
                           override_id: Optional[int] = None) -> None:
    with get_connection() as conn:
        conn.execute("UPDATE merge_review_item SET status = ?, resolution = ?, override_id = ?, updated_at = ? WHERE id = ?",
                     (status, resolution, override_id, datetime.utcnow().isoformat(), item_id))
        conn.commit()


_OVERRIDE_FIELDS = ("kind", "statement_type", "scope", "entity_key", "segment_key", "a_label_key", "a_report",
                    "b_label_key", "b_report", "new_label", "note")


def add_merge_override(instrument_code: str, override: Dict[str, Any], created_by: Optional[str] = None) -> int:
    code = instrument_code.upper().strip()
    now = datetime.utcnow().isoformat()
    vals = [override.get(f) if override.get(f) is not None else ("" if f in ("entity_key", "segment_key") else None)
            for f in _OVERRIDE_FIELDS]
    with get_connection() as conn:
        cur = conn.execute(f"""INSERT INTO merge_override (instrument_code, {', '.join(_OVERRIDE_FIELDS)}, active, created_at, updated_at, created_by)
                               VALUES (?, {', '.join('?' * len(_OVERRIDE_FIELDS))}, 1, ?, ?, ?)""", [code] + vals + [now, now, created_by])
        conn.commit()
        return cur.lastrowid


def load_merge_overrides(instrument_code: str, active_only: bool = True, source: Optional[str] = None) -> List[Dict[str, Any]]:
    """source: None = every override, 'user' or 'llm' = only those decided by that source."""
    code = instrument_code.upper().strip()
    q = "SELECT * FROM merge_override WHERE instrument_code = ?" + (" AND active = 1" if active_only else "")
    params: list = [code]
    if source:
        q += " AND COALESCE(source, 'user') = ?"
        params.append(source)
    with get_connection() as conn:
        return [dict(r) for r in conn.execute(q + " ORDER BY id", params)]


def get_merge_override(instrument_code: str, override_id: int) -> Optional[Dict[str, Any]]:
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        row = conn.execute("SELECT * FROM merge_override WHERE instrument_code = ? AND id = ?", (code, override_id)).fetchone()
    return dict(row) if row else None


def make_llm_override_permanent(instrument_code: str, override_id: int, username: Optional[str] = None) -> bool:
    """An LLM override the user confirmed becomes a user override (same row; later merges keep it); `created_by` is that user."""
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        n = conn.execute("""UPDATE merge_override SET source = 'user', updated_at = ?, created_by = ?
                            WHERE instrument_code = ? AND id = ? AND active = 1 AND source = 'llm'""",
                         (datetime.utcnow().isoformat(), username, code, override_id)).rowcount
        conn.commit()
    return n > 0


def deactivate_merge_override(instrument_code: str, override_id: int) -> bool:
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        n = conn.execute("UPDATE merge_override SET active = 0, updated_at = ? WHERE instrument_code = ? AND id = ? AND active = 1",
                         (datetime.utcnow().isoformat(), code, override_id)).rowcount
        conn.commit()
    return n > 0


# ---------------------------------------------------------------------------
# Event log: who did what (written through events.py, read by GET /ocr/events)
# ---------------------------------------------------------------------------

def add_event(username: str, action: str, instrument_code: Optional[str] = None, fiscal_year: Optional[str] = None,
              target_type: Optional[str] = None, target_id: Optional[Any] = None, job_id: Optional[str] = None,
              details: Optional[Dict[str, Any]] = None, status: str = "STARTED", source: str = "api",
              created_at: Optional[str] = None) -> int:
    code = instrument_code.upper().strip() if instrument_code else None
    now = created_at or datetime.utcnow().isoformat()
    with get_connection() as conn:
        cur = conn.execute("""INSERT INTO event_log (created_at, finished_at, username, action, status, instrument_code, fiscal_year,
                                  target_type, target_id, job_id, source, details_json)
                              VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                           (now, now if status != "STARTED" else None, username, action, status, code, fiscal_year,
                            target_type, None if target_id is None else str(target_id), job_id, source,
                            json.dumps(details or {}, default=str)))
        conn.commit()
        return cur.lastrowid


def finish_event(event_id: int, status: str, details: Optional[Dict[str, Any]] = None,
                 target_type: Optional[str] = None, target_id: Optional[Any] = None,
                 job_id: Optional[str] = None) -> None:
    """Sets the outcome of an event; the given details are merged into the stored ones."""
    with get_connection() as conn:
        row = conn.execute("SELECT details_json FROM event_log WHERE id = ?", (event_id,)).fetchone()
        if row is None:
            return
        merged = json.loads(row["details_json"] or "{}")
        merged.update(details or {})
        conn.execute("""UPDATE event_log SET status = ?, finished_at = ?, details_json = ?,
                            target_type = COALESCE(?, target_type), target_id = COALESCE(?, target_id),
                            job_id = COALESCE(?, job_id) WHERE id = ?""",
                     (status, datetime.utcnow().isoformat(), json.dumps(merged, default=str), target_type,
                      None if target_id is None else str(target_id), job_id, event_id))
        conn.commit()


def set_event_job(event_id: int, job_id: str) -> None:
    with get_connection() as conn:
        conn.execute("UPDATE event_log SET job_id = ? WHERE id = ?", (job_id, event_id))
        conn.commit()


def list_events(username: Optional[str] = None, instrument_code: Optional[str] = None, action: Optional[str] = None,
                status: Optional[str] = None, since: Optional[str] = None, until: Optional[str] = None,
                limit: int = 100, offset: int = 0) -> Dict[str, Any]:
    """Newest first. since / until are ISO dates or timestamps (compared as text on created_at)."""
    where, args = [], []
    for column, value in (("username", username), ("action", action), ("status", status)):
        if value:
            where.append(f"{column} = ?")
            args.append(value)
    if instrument_code:
        where.append("instrument_code = ?")
        args.append(instrument_code.upper().strip())
    if since:
        where.append("created_at >= ?")
        args.append(since.rstrip("Z"))
    if until:
        where.append("created_at <= ?")
        args.append(until.rstrip("Z") + ("T23:59:59.999999" if len(until) == 10 else ""))
    clause = (" WHERE " + " AND ".join(where)) if where else ""
    with get_connection() as conn:
        total = conn.execute(f"SELECT COUNT(*) FROM event_log{clause}", args).fetchone()[0]
        rows = [dict(r) for r in conn.execute(
            f"SELECT * FROM event_log{clause} ORDER BY created_at DESC, id DESC LIMIT ? OFFSET ?", args + [limit, offset])]
    for r in rows:
        r["details"] = json.loads(r.pop("details_json") or "{}")
        r["created_at"] = iso_utc(r["created_at"])
        r["finished_at"] = iso_utc(r["finished_at"])
    return {"total": total, "events": rows}


_RUN_ACTIONS = {"OCR": "PAGES_OCR", "EXTRACTION": "PAGES_EXTRACT", "MERGE": "MERGE_RUN"}


def backfill_events(username: str) -> Dict[str, int]:
    """One-time: turns the history the tables already hold into event_log rows (source 'backfill') for one user.
    Does nothing when backfill rows exist. Timestamps are the stored ones."""
    counts: Dict[str, int] = {}
    with get_connection() as conn:
        if conn.execute("SELECT 1 FROM event_log WHERE source = 'backfill' LIMIT 1").fetchone():
            return counts
        events: List[tuple] = []     # (created_at, action, code, fiscal_year, target_type, target_id, details)
        for r in conn.execute("SELECT instrument_code, fiscal_year, filename, total_pages, created_at FROM documents"):
            events.append((r["created_at"], "REPORT_UPLOAD", r["instrument_code"], r["fiscal_year"], None, None,
                           {"filename": r["filename"], "total_pages": r["total_pages"]}))
        for r in conn.execute("""SELECT id, instrument_code, fiscal_year, process_type, page_numbers_json, total_pages, total_tokens,
                                        created_at FROM pipeline_runs"""):
            action = _RUN_ACTIONS.get(r["process_type"])
            if action:
                events.append((r["created_at"], action, r["instrument_code"], r["fiscal_year"], "pipeline_run", r["id"],
                               {"pages": json.loads(r["page_numbers_json"] or "[]"), "total": r["total_pages"],
                                "total_tokens": r["total_tokens"]}))
        resolved_overrides = {r[0] for r in conn.execute("SELECT override_id FROM merge_review_item WHERE override_id IS NOT NULL")}
        for r in conn.execute("SELECT * FROM merge_override"):
            if r["id"] in resolved_overrides:      # created by a review decision: that event carries it
                continue
            events.append((r["created_at"], f"OVERRIDE_{r['kind']}", r["instrument_code"], None, "merge_override", r["id"],
                           {"kind": r["kind"], "a_label": r["a_label_key"], "b_label": r["b_label_key"],
                            "new_label": r["new_label"], "note": r["note"]}))
            if not r["active"]:
                events.append((r["updated_at"], "OVERRIDE_REMOVE", r["instrument_code"], None, "merge_override", r["id"],
                               {"kind": r["kind"]}))
        for r in conn.execute("SELECT * FROM merge_review_item WHERE status != 'open'"):
            action = r["resolution"] if r["resolution"] in ("accept", "reject", "dismiss") else "dismiss"
            events.append((r["updated_at"], f"REVIEW_{action.upper()}", r["instrument_code"], None, "review_item", r["id"],
                           {"review_kind": r["kind"], "override_id": r["override_id"]}))
        for r in conn.execute("SELECT * FROM line_override"):
            events.append((r["updated_at"], "LINE_NAME_SET", r["instrument_code"], r["fiscal_year"], "line_override", r["id"],
                           {"page_number": r["page_number"], "table_num": r["table_num"], "table_row": r["table_row"],
                            "field": r["field"], "note": r["note"]}))
        for created, action, code, fy, ttype, tid, details in events:
            conn.execute("""INSERT INTO event_log (created_at, finished_at, username, action, status, instrument_code, fiscal_year,
                                target_type, target_id, source, details_json)
                            VALUES (?, ?, ?, ?, 'SUCCEEDED', ?, ?, ?, ?, 'backfill', ?)""",
                         (created or "", created, username, action, code, fy, ttype,
                          None if tid is None else str(tid), json.dumps(details, default=str)))
            counts[action] = counts.get(action, 0) + 1
        conn.commit()
    return counts


# ---------------------------------------------------------------------------
# Disclosures added by users and the "current" disclosure of a cell
# ---------------------------------------------------------------------------
# A cell of the merged table is (series, year). Its disclosures are the extracted ones (one per publication, built from
# financial_metric_data) plus the ones users added here. A disclosure is never edited: a user adds another one. Which
# disclosure is the current one (the value the merged table shows) is chosen per cell; without a choice it is the
# latest extracted one. record_key names a disclosure: "ai:{report}|{page}|{table}|{row}|{col}" for an extracted cell
# (page coordinates, so it survives a re-merge) and "user:{id}" for an added one.

def ai_record_key(report: Any, page: Any, table: Any, row: Any, col: Any) -> str:
    return f"ai:{report}|{page}|{table or 1}|{row}|{col}"


def _series_anchor(conn, code: str, series_id: int) -> Optional[Dict[str, Any]]:
    """The identity a user's data is stored under: the partition, report and label of the series' latest member."""
    row = conn.execute("""SELECT m.partition_key, m.report_fy, m.label_key FROM series_member m
                          JOIN series s ON s.id = m.series_id
                          WHERE m.instrument_code = ? AND m.series_id = ? AND s.status = 'active'
                          ORDER BY m.report_fy DESC, m.node_id DESC LIMIT 1""", (code, series_id)).fetchone()
    return dict(row) if row else None


def _anchor_series_map(conn, code: str) -> Dict[tuple, int]:
    """{(partition_key, report_fy, label_key): series_id} of the company's active series."""
    return {(r["partition_key"], r["report_fy"], r["label_key"]): r["series_id"] for r in conn.execute(
        """SELECT m.partition_key, m.report_fy, m.label_key, m.series_id FROM series_member m
           JOIN series s ON s.id = m.series_id WHERE m.instrument_code = ? AND s.status = 'active'""", (code,))}


def _resolve_series(row, anchors: Dict[tuple, int], live: set) -> Optional[int]:
    sid = anchors.get((row["partition_key"], row["anchor_report"], row["anchor_label"]))
    if sid is None and row["series_id"] in live:
        sid = row["series_id"]
    return sid


def _live_series_ids(conn, code: str) -> set:
    return {r["id"] for r in conn.execute("SELECT id FROM series WHERE instrument_code = ? AND status = 'active'", (code,))}


def enter_cell_disclosure(instrument_code: str, series_id: int, year: int, value: float, unit: Optional[str],
                          date: Optional[str], note: str, user: str) -> Optional[Dict[str, Any]]:
    """Enters the user's disclosure for (series, year): a cell has at most ONE. When the cell already has one it is replaced by
    the new figure, note, date, user and time (the row and its disclosure_id stay, so a choice of it as the current disclosure
    holds). Returns {**disclosure, "replaced": bool, "previous": the replaced disclosure or None}; None when the series is not an
    active series."""
    code = instrument_code.upper().strip()
    now = datetime.utcnow().isoformat()
    with get_connection() as conn:
        anchor = _series_anchor(conn, code, series_id)
        if anchor is None:
            return None
        anchors, live = _anchor_series_map(conn, code), _live_series_ids(conn, code)
        existing = [r for r in conn.execute("SELECT * FROM cell_disclosure WHERE instrument_code = ? AND year = ? ORDER BY id",
                                            (code, year)) if _resolve_series(r, anchors, live) == series_id]
        if existing:
            keep, previous = existing[-1], _disclosure_view(existing[-1])
            conn.execute("""UPDATE cell_disclosure SET partition_key = ?, anchor_report = ?, anchor_label = ?, series_id = ?,
                            value = ?, unit = ?, date = ?, note = ?, created_by = ?, created_at = ? WHERE id = ?""",
                         (anchor["partition_key"], anchor["report_fy"], anchor["label_key"], series_id, value, unit, date, note,
                          user, now, keep["id"]))
            for extra in existing[:-1]:                       # a cell never keeps two
                conn.execute("DELETE FROM cell_disclosure WHERE id = ?", (extra["id"],))
            disclosure_id = keep["id"]
        else:
            previous = None
            cur = conn.execute("""INSERT INTO cell_disclosure (instrument_code, partition_key, anchor_report, anchor_label, series_id,
                                  year, value, unit, date, note, created_by, created_at)
                                  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                               (code, anchor["partition_key"], anchor["report_fy"], anchor["label_key"], series_id, year, value,
                                unit, date, note, user, now))
            disclosure_id = cur.lastrowid
        conn.commit()
        row = conn.execute("SELECT * FROM cell_disclosure WHERE id = ?", (disclosure_id,)).fetchone()
    return {**_disclosure_view(row), "replaced": previous is not None, "previous": previous}


def delete_cell_disclosure(instrument_code: str, series_id: int, year: int) -> Optional[Dict[str, Any]]:
    """Deletes the user's disclosure of (series, year). When it was the cell's chosen current disclosure the choice goes with it (the
    cell is back to its automatic current disclosure). Returns the deleted disclosure with `current_reset` (true when a choice was
    removed), or None when the cell had none."""
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        anchors, live = _anchor_series_map(conn, code), _live_series_ids(conn, code)
        existing = [r for r in conn.execute("SELECT * FROM cell_disclosure WHERE instrument_code = ? AND year = ? ORDER BY id",
                                            (code, year)) if _resolve_series(r, anchors, live) == series_id]
        if not existing:
            return None
        keys = {f"user:{r['id']}" for r in existing}
        for r in existing:
            conn.execute("DELETE FROM cell_disclosure WHERE id = ?", (r["id"],))
        gone = [r["rid"] for r in conn.execute("SELECT rowid AS rid, * FROM cell_current WHERE instrument_code = ? AND year = ?", (code, year))
                if r["record_key"] in keys and _resolve_series(r, anchors, live) == series_id]
        for rid in gone:
            conn.execute("DELETE FROM cell_current WHERE rowid = ?", (rid,))
        conn.commit()
    return {**_disclosure_view(existing[-1]), "current_reset": bool(gone)}


def _disclosure_view(row) -> Dict[str, Any]:
    d = dict(row)
    return {"disclosure_id": d["id"], "record_key": f"user:{d['id']}", "year": d["year"], "value": d["value"], "unit": d["unit"],
            "date": d["date"], "note": d["note"], "created_by": d["created_by"],
            "created_at": iso_utc(d["created_at"])}


def list_cell_disclosures(instrument_code: str, series_id: Optional[int] = None,
                          year: Optional[int] = None) -> List[Dict[str, Any]]:
    """The user disclosures of the company, oldest first; with series_id (and year) only those of that series' cell, found
    again through the series' identity after a re-merge."""
    code = instrument_code.upper().strip()
    sql, args = "SELECT * FROM cell_disclosure WHERE instrument_code = ?", [code]
    if year is not None:
        sql += " AND year = ?"
        args.append(year)
    with get_connection() as conn:
        rows = conn.execute(sql + " ORDER BY id", args).fetchall()
        anchors, live = _anchor_series_map(conn, code), _live_series_ids(conn, code)
    out = []
    for r in rows:
        sid = _resolve_series(r, anchors, live)
        if sid is None or (series_id is not None and sid != series_id):
            continue
        out.append({**_disclosure_view(r), "series_id": sid})
    return out


def set_cell_current(instrument_code: str, series_id: int, year: int, record_key: str, user: str) -> bool:
    """The current disclosure of a cell (an overwrite: one choice per cell). False when the series is not active."""
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        anchor = _series_anchor(conn, code, series_id)
        if anchor is None:
            return False
        conn.execute("""INSERT OR REPLACE INTO cell_current (instrument_code, partition_key, anchor_report, anchor_label,
                        series_id, year, record_key, set_by, set_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                     (code, anchor["partition_key"], anchor["report_fy"], anchor["label_key"], series_id, year, record_key,
                      user, datetime.utcnow().isoformat()))
        conn.commit()
    return True


def reset_cell_current(instrument_code: str, series_id: int, year: int) -> bool:
    """Back to the automatic current disclosure (the latest extracted one). False when the cell had no choice."""
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        anchors, live = _anchor_series_map(conn, code), _live_series_ids(conn, code)
        hit = [r for r in conn.execute("SELECT rowid AS rid, * FROM cell_current WHERE instrument_code = ? AND year = ?", (code, year))
               if _resolve_series(r, anchors, live) == series_id]
        for r in hit:
            conn.execute("DELETE FROM cell_current WHERE rowid = ?", (r["rid"],))
        conn.commit()
    return bool(hit)


def list_cell_current(instrument_code: str, series_id: Optional[int] = None) -> List[Dict[str, Any]]:
    """The chosen current disclosures [{series_id, year, record_key, set_by, set_at}] (those of one series with series_id)."""
    code = instrument_code.upper().strip()
    with get_connection() as conn:
        rows = conn.execute("SELECT * FROM cell_current WHERE instrument_code = ? ORDER BY year", (code,)).fetchall()
        anchors, live = _anchor_series_map(conn, code), _live_series_ids(conn, code)
    out = []
    for r in rows:
        sid = _resolve_series(r, anchors, live)
        if sid is None or (series_id is not None and sid != series_id):
            continue
        out.append({"series_id": sid, "year": r["year"], "record_key": r["record_key"], "set_by": r["set_by"],
                    "set_at": iso_utc(r["set_at"])})
    return out


def get_record_value(instrument_code: str, record_key: str) -> Optional[Dict[str, Any]]:
    """The effective value of a disclosure named by record_key: {value, origin, report, disclosure_id}; None when it no longer exists
    (an extracted cell whose page was extracted again without it)."""
    code = instrument_code.upper().strip()
    if record_key.startswith("user:"):
        try:
            did = int(record_key[5:])
        except ValueError:
            return None
        with get_connection() as conn:
            row = conn.execute("SELECT * FROM cell_disclosure WHERE id = ? AND instrument_code = ?", (did, code)).fetchone()
        return None if not row else {"value": row["value"], "origin": "USER_ADDED", "report": None, "disclosure_id": did}
    if record_key.startswith("ai:"):
        parts = record_key[3:].split("|")
        if len(parts) != 5:
            return None
        report = parts[0]
        try:
            page, table, row_n, col = (int(x) for x in parts[1:])
        except ValueError:
            return None
        with get_connection() as conn:
            row = conn.execute("""SELECT d.numeric_value, d.multiplier FROM financial_metrics m
                                  JOIN financial_metric_data d ON d.metric_id = m.id
                                  WHERE m.instrument_code = ? AND m.fiscal_year = ? AND m.page_number = ? AND COALESCE(d.table_num, 1) = ?
                                    AND d.table_row = ? AND d.table_col = ? LIMIT 1""",
                               (code, report, page, table, row_n, col)).fetchone()
        if not row or row["numeric_value"] is None:
            return None
        return {"value": float(row["numeric_value"]) * float(row["multiplier"] or 1.0), "origin": "AI_EXTRACTED",
                "report": report, "disclosure_id": None}
    return None
