"""Financial OCR & extraction routes, mounted under /ocr.

Every route on ``router`` requires the X-API-Key header (see app.security); ``public_router``
holds the unauthenticated GET /ocr/health. Every data-changing route also requires the X-Username header
(``Username``, see events.py) and records what was done in the event log.
"""

import asyncio
import concurrent.futures
import json
import logging
import os
import re
import threading
from pathlib import Path
from typing import Annotated, Any, Dict, List, Literal, Optional, Tuple

import pypdfium2 as pdfium
from fastapi import APIRouter, Depends, File, Form, HTTPException, Query, Request, UploadFile, WebSocket, WebSocketDisconnect
from fastapi import Path as PathParam
from fastapi.concurrency import run_in_threadpool
from fastapi.responses import FileResponse, JSONResponse, Response, StreamingResponse
from pydantic import BaseModel, Field

from app.security import _safe_equals, api_key_configured, require_auth

from . import config, database, events, jobs
from .events import Username
from .services import (
    csv_service,
    extraction_service,
    extraction_verifier,
    merge_service,
    ocr_service,
    series_keys,
    series_merge,
    xlsx_export,
)

logger = logging.getLogger("ocr.router")

router = APIRouter(prefix="/ocr", tags=["ocr"], dependencies=[Depends(require_auth)])
public_router = APIRouter(prefix="/ocr", tags=["ocr"])

# Values that end up in file-system paths: no dots or slashes allowed.
INSTRUMENT_CODE_PATTERN = r"^[A-Za-z0-9_&()\-]{1,64}$"
# A report's fiscal year is "2024-25" (split year) or "2024" (calendar-style year); the "FY " prefix is only display.
FISCAL_YEAR_PATTERN = r"^\d{4}(-\d{2})?$"
_FISCAL_YEAR_RE = re.compile(FISCAL_YEAR_PATTERN)

CodeParam = Annotated[str, PathParam(pattern=INSTRUMENT_CODE_PATTERN)]
FyParam = Annotated[str, PathParam(pattern=FISCAL_YEAR_PATTERN)]
CodeForm = Annotated[str, Form(pattern=INSTRUMENT_CODE_PATTERN)]
FyForm = Annotated[str, Form(pattern=FISCAL_YEAR_PATTERN)]

_PDF_MAGIC = (b"%PDF-",)
_IMAGE_MAGIC = (b"\x89PNG\r\n\x1a\n", b"\xff\xd8\xff")
_OCR_ERROR_MD = "# Page {num}\n\n*Error during live OCR inference (see server logs).*"
# Downloads only (so Excel detects UTF-8); csv_text in JSON responses has no BOM.
_UTF8_BOM = "﻿"


def _validate_fiscal_year(fy) -> str:
    if not isinstance(fy, str) or not _FISCAL_YEAR_RE.fullmatch(fy):
        raise HTTPException(status_code=422, detail="fiscal_year must look like 2024-25 or 2024")
    return fy


async def _read_upload(upload: UploadFile, magics: tuple, kind: str) -> bytes:
    """Read an upload, enforcing MAX_UPLOAD_MB and the expected file signature."""
    content = await upload.read(config.MAX_UPLOAD_BYTES + 1)
    if len(content) > config.MAX_UPLOAD_BYTES:
        raise HTTPException(
            status_code=413,
            detail=f"Uploaded file is too large (limit {config.MAX_UPLOAD_MB:g} MB per file)",
        )
    if not content.startswith(magics):
        raise HTTPException(status_code=415, detail=f"Uploaded file is not a valid {kind}")
    return content


def _pdf_page_count(pdf_path: Path) -> int:
    with ocr_service.PDF_LOCK:  # PDFium is not thread-safe
        pdf = pdfium.PdfDocument(str(pdf_path))
        try:
            return len(pdf)
        finally:
            pdf.close()


def _page_verification_fields(p: dict) -> dict:
    """Extraction/verification state of a page row (defaults for pages not in the DB)."""
    return {
        "extraction_status": p.get("extraction_status") or "PENDING",
        "extraction_message": database.extraction_message(p.get("extraction_status"), p.get("missing_cells")),
        "expected_cells": p.get("expected_cells") or 0,
        "extracted_cells": p.get("extracted_cells") or 0,
        "missing_cells": p.get("missing_cells") or 0,
        "completeness_pct": p.get("completeness_pct") or 0.0,
        "verification_status": p.get("verification_status") or "PENDING",
    }


def _run_per_page(fn, page_numbers: List[int]) -> list:
    """Runs fn(page_number) for each unique page (request order kept), up to config.PAGE_PARALLELISM at a time."""
    unique_pages = list(dict.fromkeys(page_numbers))
    if len(unique_pages) <= 1:
        return [fn(num) for num in unique_pages]
    with concurrent.futures.ThreadPoolExecutor(max_workers=min(config.PAGE_PARALLELISM, len(unique_pages))) as executor:
        return list(executor.map(fn, unique_pages))


# ---------------------------------------------------------------------------
# Request Models
# ---------------------------------------------------------------------------

class OcrRequest(BaseModel):
    instrument_code: str = Field(pattern=INSTRUMENT_CODE_PATTERN)
    fiscal_year: str = Field(pattern=FISCAL_YEAR_PATTERN)
    page_numbers: List[Annotated[int, Field(ge=1, le=10000)]] = Field(max_length=2000)

class ExtractionRequest(BaseModel):
    instrument_code: str = Field(pattern=INSTRUMENT_CODE_PATTERN)
    fiscal_year: str = Field(pattern=FISCAL_YEAR_PATTERN)
    page_numbers: List[Annotated[int, Field(ge=1, le=10000)]] = Field(max_length=2000)

class MergeRequest(BaseModel):
    instrument_code: str = Field(pattern=INSTRUMENT_CODE_PATTERN)

class PageJobRequest(BaseModel):
    """One page of one report: the unit of an OCR or extraction job."""
    instrument_code: str = Field(pattern=INSTRUMENT_CODE_PATTERN)
    fiscal_year: str = Field(pattern=FISCAL_YEAR_PATTERN)
    page_number: int = Field(ge=1, le=10000)


def _result_summary(result: Dict[str, Any]) -> Dict[str, Any]:
    """The few numbers of an OCR / extraction response worth keeping in the event log."""
    out: Dict[str, Any] = {}
    usage = result.get("usage_report") or {}
    tokens = (usage.get("current_run") or usage).get("total_tokens")
    if tokens is not None:
        out["total_tokens"] = tokens
    if result.get("token_usage"):
        out["total_tokens"] = result["token_usage"].get("total_tokens", 0)
    for key in ("completed_count", "items_extracted_count", "merged_rows_count", "merge_count", "conflicts_count",
                "total_pairs"):
        if key in result:
            out[key] = result[key]
    if result.get("verification_results") is not None:
        out["failed_pages"] = [r.get("page_number") for r in result["verification_results"]
                               if r.get("extraction_status") == "FAILED"]
    return out


def _run_logged(user: str, action: str, code: str, fy: str, fn, *args,
                details: Optional[Dict[str, Any]] = None, event_id: Optional[int] = None) -> Dict[str, Any]:
    """Runs fn(*args) (a pipeline function) inside an event; the response is returned unchanged."""
    with events.record(user, action, code, fy, details=details, event_id=event_id) as ev:
        result = fn(*args)
        ev.details.update(_result_summary(result))
        return result


def _job_worker(user: str, action: str, code: str, fy: Optional[str], ids: Dict[str, Any], fn, *args,
                job: Optional[jobs.JobContext] = None) -> Dict[str, Any]:
    """Runs a queued job's work inside its event (made when the job was queued)."""
    try:
        return _run_logged(user, action, code, fy, fn, *args, *((job,) if job is not None else ()),
                           event_id=ids["event_id"])
    except merge_service.MergeCancelled:
        raise jobs.JobCancelled()


def _submit_logged_job(kind: str, stage: str, user: str, action: str, code: str, fy: Optional[str],
                       page: Optional[int], details: Dict[str, Any], fn, *args, takes_job: bool = False,
                       event_id: Optional[int] = None) -> Dict[str, Any]:
    """Queues fn(*args) as one job (fn(*args, job) when takes_job). A new job records its event at once (STARTED,
    with the job id); the worker finishes it, and cancelling the job before it starts closes it as CANCELLED.
    event_id: an event made before (it gets the job id). When the same work is already queued, that job is
    returned and no new event is written (a given event_id is closed as a duplicate)."""
    code = code.upper().strip()
    ids: Dict[str, Any] = {"event_id": event_id}

    def queued(job_id: str) -> None:
        if ids["event_id"] is None:
            ids["event_id"] = events.start(user, action, code, fy, details={**details, "job": kind})
        database.set_event_job(ids["event_id"], job_id)

    def cancelled() -> None:
        database.finish_event(ids["event_id"], "CANCELLED", {"cancelled_before_start": True})

    job = jobs.submit(kind, stage, _job_worker, user, action, code, fy, ids, fn, *args,
                      instrument_code=code, fiscal_year=fy, page_number=page, takes_job=takes_job,
                      on_queued=queued, on_cancel=cancelled)
    if job.get("deduplicated") and event_id is not None:
        database.finish_event(event_id, "SUCCEEDED", {"duplicate_of_job": job["job_id"]}, job_id=job["job_id"])
    return job

# ---------------------------------------------------------------------------
# Endpoints
# ---------------------------------------------------------------------------

@public_router.get("/health")
def health_check():
    """Public health check (RFC draft "application/health+json"), no authentication.

    ``fail`` (503) when the database is unreachable, ``warn`` (200) when one of OCR_API_KEY,
    EXTRACT_API_KEY, MERGE_API_KEY is missing (reads work, that step does not), otherwise ``pass`` (200).
    """
    try:
        conn = database.get_connection()
        try:
            conn.execute("SELECT 1")
        finally:
            conn.close()
    except Exception:
        logger.exception("Health check: database unreachable")
        status, code = "fail", 503
    else:
        keys_ok = all(config.gemini_api_key(stage) for stage in config.GEMINI_STAGES)
        status, code = ("pass", 200) if keys_ok else ("warn", 200)
    return JSONResponse({"status": status}, status_code=code, media_type="application/health+json")

@router.get("/companies")
def list_companies(fields: Optional[str] = Query(None, pattern=r"^(basic|full)$")):
    """Returns all companies and their status in SQLite.

    fields=basic: a light list ``[{security_code, company_name, sector}]`` from the catalog
    (data/companies.json) plus any company recorded in SQLite, without per-company status.
    """
    if fields == "basic":
        return database.get_companies_basic()
    return database.get_all_companies()

def _company_state(reports: List[Dict[str, Any]], merged: Optional[bool]) -> str:
    """NOT_STARTED (no report), COMPLETE (every report extracted and checked by a person, and the company merged with a
    merge that is not stale) or IN_PROGRESS."""
    if not reports:
        return "NOT_STARTED"
    done = all(r["status"] == "EXTRACTED" and r["verified"] for r in reports)
    return "COMPLETE" if done and merged is False else "IN_PROGRESS"


@router.get("/coverage")
def get_coverage(
    sector: Optional[str] = Query(None, max_length=100),
    search: Optional[str] = Query(None, max_length=100),
    state: Optional[Literal["complete", "pending", "in_progress", "not_started"]] = Query(None),
    from_year: Optional[int] = Query(None, ge=1990, le=2100),
    to_year: Optional[int] = Query(None, ge=1990, le=2100),
):
    """Sector-wise coverage matrix: which companies have which years, and how far each report got.

    `years` are the columns (the end year of each report: `2022-23` and `2023` are both 2023), from the earliest to the
    latest year that any company has, narrowed by `from_year` / `to_year`. Each company has `state` (NOT_STARTED,
    IN_PROGRESS, COMPLETE), `years: {"2023": cell}` with a cell only for years it has a report for (a missing key is a
    missing year; `missing_years` lists them), `merged`, `merged_stale` and `jobs: {queued, running}`. A cell:
    `{fiscal_year, status: UPLOADED|OCR_DONE|EXTRACTED, total_pages, pages_selected, pages_ocr, pages_extracted, verified,
    verified_by, verified_at, verify_note, jobs}`. `sectors` counts every company per sector and state, whatever the
    filters, so the tabs do not change while filtering. Filters: `sector` (exact, case-insensitive), `search` (code or
    name), `state` (`pending` = not complete)."""
    data = database.get_coverage_data()
    active = jobs.active_by_target()
    zero = {"queued": 0, "running": 0}

    all_end_years = [r["end_year"] for rs in data["reports"].values() for r in rs if r["end_year"]]
    lo = max(from_year or 0, min(all_end_years)) if all_end_years else None
    hi = min(to_year or 9999, max(all_end_years)) if all_end_years else None
    years = list(range(lo, hi + 1)) if lo is not None and lo <= hi else []

    sectors: Dict[str, Dict[str, int]] = {}
    out_companies = []
    totals = {"companies": 0, "complete": 0, "in_progress": 0, "not_started": 0}
    for code, name, sec in data["companies"]:
        sec_name = sec or "Unclassified"
        reports = data["reports"].get(code, [])
        st = _company_state(reports, data["merged"].get(code))
        counts = sectors.setdefault(sec_name, {"companies": 0, "complete": 0, "in_progress": 0, "not_started": 0})
        for bucket in (counts, totals):
            bucket["companies"] += 1
            bucket[st.lower()] += 1

        if sector and sec_name.lower() != sector.strip().lower():
            continue
        if search and search.strip().lower() not in f"{code} {name}".lower():
            continue
        if state and not (st.lower() == state or (state == "pending" and st != "COMPLETE")):
            continue

        cells: Dict[str, Dict[str, Any]] = {}
        for r in sorted(reports, key=lambda r: (r["end_year"] or 0, database._REPORT_STAGE_RANK[r["status"]], r["fiscal_year"])):
            if not r["end_year"] or not (lo is not None and lo <= r["end_year"] <= hi):
                continue
            cell = {k: v for k, v in r.items() if k != "end_year"}
            cell["jobs"] = active.get((code, r["fiscal_year"]), zero)
            key = str(r["end_year"])
            if key in cells:      # two reports of one end year (2022-23 and 2023): the one that got further is shown
                cell["other_fiscal_years"] = [cells[key]["fiscal_year"], *cells[key].get("other_fiscal_years", [])]
            cells[key] = cell
        merge_jobs = active.get((code, None), zero)
        company_jobs = {k: merge_jobs[k] + sum(v[k] for (c, fy), v in active.items() if c == code and fy is not None)
                        for k in zero}
        out_companies.append({
            "instrument_code": code, "name": name, "sector": sec_name, "state": st,
            "reports": len(reports), "years": cells,
            "missing_years": [y for y in years if str(y) not in cells],
            "merged": code in data["merged"], "merged_stale": bool(data["merged"].get(code)),
            "jobs": company_jobs,
        })

    out_companies.sort(key=lambda c: (c["sector"].lower(), c["instrument_code"]))
    return {
        "years": years,
        "totals": totals,
        "sectors": [{"sector": s, **c} for s, c in sorted(sectors.items(), key=lambda kv: kv[0].lower())],
        "companies": out_companies,
    }


@router.get("/companies/catalog")
def get_companies_catalog():
    """Returns master list of companies from data/companies.json."""
    catalog_path = config.COMPANIES_CATALOG_PATH
    if catalog_path.exists():
        with open(catalog_path, "r", encoding="utf-8") as f:
            return json.load(f)
    return []

@router.get("/company/{code}/status")
def get_company_status(code: CodeParam):
    """Checks whether an instrument code and years are already analyzed in SQLite."""
    return database.get_company_status(code)

@router.post("/upload")
async def upload_documents(
    user: Username,
    instrument_code: CodeForm,
    fiscal_years_json: str = Form(..., max_length=100_000),
    files: List[UploadFile] = File(...)
):
    """
    Upload one or multiple PDF annual reports.
    Extracts pages to PNGs, registers documents in SQLite, and returns cataloged pages.
    Needs the X-Username header; recorded as REPORT_UPLOAD.
    """
    with events.record(user, "REPORT_UPLOAD", instrument_code) as ev:
        out = await _upload_documents(instrument_code, fiscal_years_json, files)
        ev.details["documents"] = [{"filename": d["filename"], "fiscal_year": d["fiscal_year"], "total_pages": d["total_pages"],
                                    **({"verification_cleared": True} if d["verification_cleared"] else {})}
                                   for d in out["uploaded_documents"]]
        return out


async def _upload_documents(instrument_code: str, fiscal_years_json: str, files: List[UploadFile]):
    code = instrument_code.upper().strip()
    try:
        fy_mapping = json.loads(fiscal_years_json)
    except ValueError:
        raise HTTPException(status_code=422, detail="fiscal_years_json must be a JSON object")
    if not isinstance(fy_mapping, dict):
        raise HTTPException(status_code=422, detail="fiscal_years_json must be a JSON object")

    # Validate every file before writing anything to disk.
    planned = []
    for upload_file in files:
        filename = upload_file.filename
        fy = _validate_fiscal_year(fy_mapping.get(filename, "2024-25"))
        content = await _read_upload(upload_file, _PDF_MAGIC, "PDF")
        planned.append((filename, fy, content))

    results = []

    for filename, fy, content in planned:
        # Save PDF to disk using {instrument_code}_{fiscal_year}.pdf convention
        dest_filename = f"{code}_{fy}.pdf"
        dest_pdf = config.UPLOADS_DIR / dest_filename
        with open(dest_pdf, "wb") as f:
            f.write(content)

        # Render PDF pages to PNG
        try:
            rendered_pages = ocr_service.render_pdf_to_images(
                pdf_path=str(dest_pdf),
                instrument_code=code,
                fiscal_year=fy
            )
        except Exception:
            logger.exception("Failed to render PDF %s", dest_filename)
            rendered_pages = []

        # Upsert document in SQLite (a new PDF clears the report's "checked" mark)
        cleared = bool((database.get_document_by_year(code, fy) or {}).get("verified_by"))
        doc_id = database.upsert_document(
            instrument_code=code,
            fiscal_year=fy,
            filename=dest_filename,
            filepath=str(dest_pdf),
            total_pages=len(rendered_pages),
            new_file=True
        )

        # Upsert pages in SQLite
        for p in rendered_pages:
            database.upsert_page(
                document_id=doc_id,
                instrument_code=code,
                fiscal_year=fy,
                page_number=p["page_number"],
                image_path=p["image_path"],
                is_selected=False,
                category="OTHER",
                title=f"Page {p['page_number']}"
            )

        results.append({
            "document_id": doc_id,
            "filename": filename,
            "fiscal_year": fy,
            "total_pages": len(rendered_pages),
            "verification_cleared": cleared
        })

    return {
        "instrument_code": code,
        "uploaded_documents": results,
        "status": database.get_company_status(code)
    }

def resolve_pdf_path(code: str, fy: str) -> Optional[Path]:
    """Finds the PDF file on disk in Backend storage/uploads, self-healing DB if needed."""
    code_clean = code.upper().strip()
    doc = database.get_document_by_year(code_clean, fy)
    if doc and doc.get("filepath") and Path(doc["filepath"]).exists():
        return Path(doc["filepath"])

    # Check storage/uploads
    upload_pdf = config.UPLOADS_DIR / f"{code_clean}_{fy}.pdf"
    if upload_pdf.exists():
        if doc and doc.get("id"):
            database.upsert_document(
                instrument_code=code_clean,
                fiscal_year=fy,
                filename=upload_pdf.name,
                filepath=str(upload_pdf),
                total_pages=doc.get("total_pages") or 0
            )
        return upload_pdf

    return None

def _page_image_url(p: dict) -> Optional[str]:
    """URL of a page's rendered PNG (served by /ocr/files), or None when it was never rendered."""
    img = p.get("image_path")
    if not img or not Path(img).exists():
        return None
    try:
        rel = Path(img).resolve().relative_to(config.STORAGE_DIR.resolve())
    except ValueError:
        return None
    return f"/ocr/files/{rel.as_posix()}"


@router.get("/company/{code}/year/{fy}/pages")
def get_document_pages(
    code: CodeParam,
    fy: FyParam,
    include_markdown: bool = Query(True, description="false: ocr_markdown is null (use the per-page markdown endpoint)"),
):
    """Retrieves list of all pages for a specific company and fiscal year."""
    out = _list_document_pages(code, fy)
    for page in out["pages"]:
        if not include_markdown:
            page["ocr_markdown"] = None
    return out


@router.get("/company/{code}/year/{fy}/page/{page_number}/markdown")
def get_page_markdown(code: CodeParam, fy: FyParam, page_number: int = PathParam(..., ge=1, le=10000)):
    """OCR markdown of a single page ("" when the page has not been OCR'd yet)."""
    code_clean = code.upper().strip()
    page = next((p for p in database.get_pages_for_year(code_clean, fy) if p["page_number"] == page_number), None)
    if page is None:
        raise HTTPException(status_code=404, detail=f"Page {page_number} not found for {code_clean} {fy}")
    return {"page": page_number, "ocr_markdown": page.get("ocr_markdown") or ""}


def _list_document_pages(code: str, fy: str) -> dict:
    code_clean = code.upper().strip()
    doc = database.get_document_by_year(code_clean, fy)
    pdf_path = resolve_pdf_path(code_clean, fy)

    # If no doc record in DB, auto-register if PDF exists on disk
    if not doc and pdf_path and pdf_path.exists():
        try:
            t_pages = _pdf_page_count(pdf_path)
            database.upsert_document(code_clean, fy, pdf_path.name, str(pdf_path), t_pages)
            doc = database.get_document_by_year(code_clean, fy)
        except Exception as e:
            logger.warning(f"Auto-register PDF check failed: {e}")

    total_pages = doc.get("total_pages") if doc else 0
    if not total_pages and pdf_path and pdf_path.exists():
        try:
            total_pages = _pdf_page_count(pdf_path)
        except Exception:
            logger.warning("Could not count pages of %s", pdf_path.name)

    pages = database.get_pages_for_year(code_clean, fy)
    db_page_map = {p["page_number"]: p for p in pages}
    has_pdf = bool(pdf_path and pdf_path.exists())

    # If document has total_pages, return ALL pages 1..total_pages for the whole PDF!
    if total_pages and total_pages > 0:
        out_pages = []
        for num in range(1, total_pages + 1):
            if num in db_page_map:
                p = db_page_map[num]
                out_pages.append({
                    "id": f"page_{num}",
                    "page": num,
                    "fiscal_year": fy,
                    "category": p["category"] or "Financial Statement",
                    "title": p["title"] or f"Page {num}",
                    "is_selected": bool(p["is_selected"]),
                    "ocr_status": p["ocr_status"],
                    "has_markdown": bool(p["ocr_markdown"]),
                    "ocr_markdown": p.get("ocr_markdown") or "",
                    **_page_verification_fields(p),
                    "image_url": _page_image_url(p)
                })
            else:
                out_pages.append({
                    "id": f"page_{num}",
                    "page": num,
                    "fiscal_year": fy,
                    "category": "Annual Report",
                    "title": f"Page {num}",
                    "is_selected": False,
                    "ocr_status": "PENDING",
                    "has_markdown": False,
                    "ocr_markdown": "",
                    **_page_verification_fields({}),
                    "image_url": None
                })
        return {
            "instrument_code": code_clean,
            "fiscal_year": fy,
            "total_pages": total_pages,
            "has_pdf": has_pdf,
            **database.verification_fields(doc),
            "pages": out_pages
        }

    # Fallback to whatever pages were stored in database
    out_pages = []
    for p in pages:
        out_pages.append({
            "id": f"page_{p['page_number']}",
            "page": p["page_number"],
            "fiscal_year": fy,
            "category": p["category"] or "Financial Statement",
            "title": p["title"] or f"Page {p['page_number']}",
            "is_selected": bool(p["is_selected"]),
            "ocr_status": p["ocr_status"],
            "has_markdown": bool(p["ocr_markdown"]),
            "ocr_markdown": p.get("ocr_markdown") or "",
            **_page_verification_fields(p),
            "image_url": _page_image_url(p)
        })

    return {
        "instrument_code": code_clean,
        "fiscal_year": fy,
        "total_pages": len(out_pages),
        "has_pdf": has_pdf,
        **database.verification_fields(doc),
        "pages": out_pages
    }


class VerifyRequest(BaseModel):
    verified: bool
    note: Optional[str] = Field(None, max_length=500)


@router.post("/company/{code}/year/{fy}/verify")
def verify_report(code: CodeParam, fy: FyParam, req: VerifyRequest, user: Username):
    """Marks a report's fiscal year as checked by a person (`verified: true`, optional `note`) or clears the mark
    (`verified: false`). Verifying an already verified report replaces who / when / note; clearing an unverified one
    changes nothing. Every call is recorded: REPORT_VERIFY or REPORT_UNVERIFY, with the note and who had verified
    it before. `404` when the report is not registered. Needs the X-Username header.
    Returns the report's state: {instrument_code, fiscal_year, verified, verified_by, verified_at, verify_note}."""
    code_clean = code.upper().strip()
    action = "REPORT_VERIFY" if req.verified else "REPORT_UNVERIFY"
    with events.record(user, action, code_clean, fy, details={"note": req.note} if req.note else None) as ev:
        before = database.set_document_verification(code_clean, fy, req.verified, user, req.note)
        if before is None:
            raise HTTPException(status_code=404, detail="Report not found: upload or register it first")
        ev.target("document", before["id"])
        ev.details.update(was_verified=bool(before["verified_by"]), previous_verified_by=before["verified_by"],
                          previous_verified_at=database.iso_utc(before["verified_at"]))
        return {"instrument_code": code_clean, "fiscal_year": fy,
                **database.verification_fields(database.get_document_by_year(code_clean, fy))}


@router.get("/company/{code}/year/{fy}/pdf")
def get_company_year_pdf(code: CodeParam, fy: FyParam):
    """Returns raw PDF file for the given company and fiscal year if stored on disk."""
    code_clean = code.upper().strip()
    pdf_path = resolve_pdf_path(code_clean, fy)
    if not pdf_path or not pdf_path.exists():
        raise HTTPException(status_code=404, detail="PDF not found for specified company and year")
    return FileResponse(
        str(pdf_path),
        media_type="application/pdf",
        filename=pdf_path.name
    )

@router.post("/register-document")
async def register_document_fast(
    user: Username,
    instrument_code: CodeForm,
    fiscal_year: FyForm,
    filename: str = Form(..., max_length=255),
    total_pages: int = Form(..., ge=0, le=10000),
    file: Optional[UploadFile] = File(None)
):
    """
    Instantly registers a document without rendering pages upfront.
    Saves raw PDF in milliseconds, registers document in SQLite.
    Registering again for the same (instrument_code, fiscal_year) replaces the stored PDF and
    page count ("replaced": true); existing pages, OCR and extracted metrics are kept. Sending a new
    file clears the report's "checked" mark (see POST .../year/{fy}/verify).
    Registration is optional: OCR endpoints work without it.
    Needs the X-Username header; recorded as REPORT_REGISTER.
    """
    with events.record(user, "REPORT_REGISTER", instrument_code, fiscal_year,
                       details={"filename": filename, "total_pages": total_pages}) as ev:
        out = await _register_document(instrument_code, fiscal_year, total_pages, file)
        ev.details["replaced"] = out["replaced"]
        if out["verification_cleared"]:
            ev.details["verification_cleared"] = True
        return out


async def _register_document(instrument_code: str, fiscal_year: str, total_pages: int, file: Optional[UploadFile]):
    code = instrument_code.upper().strip()
    existing = database.get_document_by_year(code, fiscal_year)
    replaced = existing is not None
    cleared = bool(file and existing and existing.get("verified_by"))
    dest_filename = f"{code}_{fiscal_year}.pdf"
    dest_pdf_path = ""
    if file:
        content = await _read_upload(file, _PDF_MAGIC, "PDF")
        dest_pdf = config.UPLOADS_DIR / dest_filename
        with open(dest_pdf, "wb") as f:
            f.write(content)
        dest_pdf_path = str(dest_pdf)
    elif (config.UPLOADS_DIR / dest_filename).exists():
        dest_pdf_path = str(config.UPLOADS_DIR / dest_filename)

    doc_id = database.upsert_document(
        instrument_code=code,
        fiscal_year=fiscal_year,
        filename=dest_filename,
        filepath=dest_pdf_path,
        total_pages=total_pages,
        new_file=bool(file)
    )

    return {
        "document_id": doc_id,
        "instrument_code": code,
        "fiscal_year": fiscal_year,
        "filename": dest_filename,
        "total_pages": total_pages,
        "replaced": replaced,
        "verification_cleared": cleared,
        "status": database.get_company_status(code)
    }

@router.post("/pipeline/ocr-client-pages")
async def run_ocr_client_pages(
    user: Username,
    instrument_code: CodeForm,
    fiscal_year: FyForm,
    page_numbers_json: str = Form(..., max_length=100_000),
    files: List[UploadFile] = File(...)
):
    """
    Receives high-resolution PNG page images rendered on the client for ONLY the selected pages.
    Saves only those pages, and runs Gemini 3.5 Flash-Lite OCR directly.
    The response always has one "pages" entry per uploaded image (an error markdown when OCR failed).
    Needs the X-Username header; recorded as PAGES_OCR.
    """
    with events.record(user, "PAGES_OCR", instrument_code, fiscal_year, details={"client_pages": True}) as ev:
        code, fy, saved_images = await _save_client_pages(instrument_code, fiscal_year, page_numbers_json, files)
        ev.details["pages"] = sorted(saved_images)
        # Gemini calls block: run them off the event loop so other requests keep being served.
        result = await run_in_threadpool(_ocr_saved_client_pages, code, fy, saved_images)
        ev.details.update(_result_summary(result))
        return result


async def _save_client_pages(instrument_code: str, fiscal_year: str, page_numbers_json: str, files: List[UploadFile]):
    """Validates the uploaded page images and saves them as page rows. Returns (code, fy, {page: image path})."""
    code = instrument_code.upper().strip()
    fy = fiscal_year
    try:
        page_numbers = json.loads(page_numbers_json)
    except ValueError:
        raise HTTPException(status_code=422, detail="page_numbers_json must be a JSON list of integers")
    if not isinstance(page_numbers, list) or not all(
        isinstance(n, int) and not isinstance(n, bool) and n >= 1 for n in page_numbers
    ):
        raise HTTPException(status_code=422, detail="page_numbers_json must be a JSON list of integers")

    images = [await _read_upload(f, _IMAGE_MAGIC, "PNG/JPEG image") for f in files]

    out_dir = config.PAGES_DIR / code / fy
    out_dir.mkdir(parents=True, exist_ok=True)

    saved_images = {}
    doc = database.get_document_by_year(code, fy)
    doc_id = doc["id"] if doc else None  # pages.document_id is a foreign key, so never 0

    for idx, (upload_file, content) in enumerate(zip(files, images)):
        fname = upload_file.filename or ""
        p_num = None
        for part in fname.replace(".png", "").replace(".jpg", "").split("_"):
            if part.isdigit():
                p_num = int(part)
                break
        if p_num is None and idx < len(page_numbers):
            p_num = page_numbers[idx]
        elif p_num is None:
            p_num = idx + 1

        dest_img = out_dir / f"page_{p_num}.png"
        with open(dest_img, "wb") as f:
            f.write(content)

        database.upsert_page(
            document_id=doc_id,
            instrument_code=code,
            fiscal_year=fy,
            page_number=p_num,
            image_path=str(dest_img),
            is_selected=True,
            category="FINANCIAL_STATEMENT",
            title=f"Page {p_num}"
        )
        saved_images[p_num] = str(dest_img)

    if not saved_images:
        raise HTTPException(status_code=422, detail="At least one page image is required")
    return code, fy, saved_images


def _ocr_saved_client_pages(code: str, fy: str, saved_images: dict) -> dict:
    completed_pages = []
    total_tokens = 0
    total_duration = 0.0
    prompt_tokens = 0
    candidates_tokens = 0
    page_details = []

    for num in sorted(list(saved_images.keys())):
        img_path = saved_images[num]
        try:
            res = ocr_service.run_gemini_ocr_on_image(img_path, model_name=config.DEFAULT_MODEL,
                                                context=_ocr_context(code, fy, num))
            md_content = res["markdown"]
            tokens = res["tokens"]
            duration = res["duration_sec"]
            p_tok = res.get("prompt_tokens", 0)
            c_tok = res.get("candidates_tokens", 0)
        except Exception:
            logger.exception("Live client OCR error on %s %s P.%s", code, fy, num)
            md_content = _OCR_ERROR_MD.format(num=num)
            tokens = 0
            duration = 0.0
            p_tok = 0
            c_tok = 0

        ocr_info = database.update_page_ocr(
            instrument_code=code,
            fiscal_year=fy,
            page_number=num,
            ocr_markdown=md_content,
            tokens=tokens,
            duration_sec=duration
        )

        total_tokens += tokens
        total_duration += duration
        prompt_tokens += p_tok
        candidates_tokens += c_tok
        page_details.append({
            "page_number": num,
            "tokens": tokens,
            "duration_sec": duration
        })

        completed_pages.append({
            "page_number": num,
            "page_id": f"page_{num}",
            "image_url": f"/ocr/files/pages/{code}/{fy}/page_{num}.png",
            "ocr_markdown": md_content,
            "tokens": tokens,
            "duration_sec": duration,
            "extraction_status": ocr_info.get("extraction_status", "PENDING"),
            "verification_status": ocr_info.get("verification_status", "PENDING")
        })

    # Log OCR pipeline execution to database
    database.record_pipeline_run(
        instrument_code=code,
        fiscal_year=fy,
        process_type="OCR",
        page_numbers=list(saved_images.keys()),
        duration_sec=total_duration,
        total_tokens=total_tokens,
        prompt_tokens=prompt_tokens,
        candidates_tokens=candidates_tokens,
        model=config.DEFAULT_MODEL,
        details=page_details
    )

    return {
        "instrument_code": code,
        "fiscal_year": fy,
        "completed_count": len(completed_pages),
        "pages": completed_pages,
        "usage_report": {
            "process_type": "OCR",
            "total_time_consumption_sec": round(total_duration, 2),
            "total_tokens": total_tokens,
            "prompt_tokens": prompt_tokens,
            "candidates_tokens": candidates_tokens,
            "page_count": len(completed_pages)
        }
    }

@router.post("/pipeline/ocr")
def run_ocr_pipeline(req: OcrRequest, user: Username):
    """
    Executes Vision OCR on selected pages (up to config.PAGE_PARALLELISM pages in parallel).
    Uses Gemini 3.5 Flash-Lite. Needs the X-Username header; recorded as PAGES_OCR.
    """
    return _run_logged(user, "PAGES_OCR", req.instrument_code, req.fiscal_year, _ocr_pipeline, req,
                       details={"pages": req.page_numbers})


def _ocr_pipeline(req: OcrRequest):
    code = req.instrument_code.upper().strip()
    fy = req.fiscal_year
    pages = database.get_pages_for_year(code, fy)
    page_map = {p["page_number"]: p for p in pages}

    unrenderable = [
        num for num in dict.fromkeys(req.page_numbers)
        if not (page_map.get(num, {}).get("image_path") and Path(page_map[num]["image_path"]).exists())
    ]
    if unrenderable and not resolve_pdf_path(code, fy):
        raise HTTPException(
            status_code=404,
            detail=f"Document not registered and no page image supplied for page(s) {unrenderable[:20]}",
        )

    def _process_single_page_ocr(num):
        page_rec = page_map.get(num)
        img_path = page_rec.get("image_path") if page_rec else None

        # On-demand single page rendering if not already rendered
        if not img_path or not Path(img_path).exists():
            doc = database.get_document_by_year(code, fy)
            pdf_path = Path(doc["filepath"]) if (doc and doc.get("filepath")) else resolve_pdf_path(code, fy)
            if not doc and pdf_path and pdf_path.exists():
                try:
                    t_pages = _pdf_page_count(pdf_path)
                    database.upsert_document(code, fy, pdf_path.name, str(pdf_path), t_pages)
                    doc = database.get_document_by_year(code, fy)
                except Exception as e:
                    logger.warning(f"Could not auto-register document {code} {fy}: {e}")

            if pdf_path and pdf_path.exists():
                try:
                    p_info = ocr_service.render_single_pdf_page(str(pdf_path), code, fy, num)
                    img_path = p_info["image_path"]
                    database.upsert_page(
                        document_id=doc["id"] if doc else None,
                        instrument_code=code,
                        fiscal_year=fy,
                        page_number=num,
                        image_path=img_path,
                        is_selected=True,
                        title=f"Page {num}"
                    )
                except Exception as e:
                    logger.warning(f"Could not render single page {num} on-demand: {e}")

        ocr_failed = False
        try:
            if not img_path or not Path(img_path).exists():
                raise FileNotFoundError(f"No rendered image for page {num}")
            res = ocr_service.run_gemini_ocr_on_image(img_path, model_name=config.DEFAULT_MODEL,
                                                context=_ocr_context(code, fy, num))
            md_content = res["markdown"]
            tokens = res["tokens"]
            duration = res["duration_sec"]
            p_tok = res.get("prompt_tokens", 0)
            c_tok = res.get("candidates_tokens", 0)
        except Exception:
            logger.exception("Live OCR error on %s %s P.%s", code, fy, num)
            md_content = _OCR_ERROR_MD.format(num=num)
            tokens = 0
            duration = 0.0
            p_tok = 0
            c_tok = 0
            ocr_failed = True

        # Save OCR markdown to SQLite (resets/deprecates any earlier extraction of this page).
        # A failed OCR never replaces the page's stored OCR or its extraction.
        ocr_info = {} if ocr_failed else database.update_page_ocr(
            instrument_code=code,
            fiscal_year=fy,
            page_number=num,
            ocr_markdown=md_content,
            tokens=tokens,
            duration_sec=duration
        )

        return {
            "page_number": num,
            "page_id": f"page_{num}",
            "ocr_markdown": md_content,
            "tokens": tokens,
            "duration_sec": duration,
            "prompt_tokens": p_tok,
            "candidates_tokens": c_tok,
            "extraction_status": ocr_info.get("extraction_status", "PENDING"),
            "verification_status": ocr_info.get("verification_status", "PENDING"),
            "ocr_failed": ocr_failed,
        }

    page_results = _run_per_page(_process_single_page_ocr, req.page_numbers)

    completed_pages = []
    total_tokens = 0
    total_duration = 0.0
    prompt_tokens = 0
    candidates_tokens = 0
    page_details = []

    for pr in page_results:
        total_tokens += pr["tokens"]
        total_duration = max(total_duration, pr["duration_sec"])  # pages run in parallel
        prompt_tokens += pr["prompt_tokens"]
        candidates_tokens += pr["candidates_tokens"]
        page_details.append({
            "page_number": pr["page_number"],
            "tokens": pr["tokens"],
            "duration_sec": pr["duration_sec"]
        })
        completed_pages.append({
            "page_number": pr["page_number"],
            "page_id": pr["page_id"],
            "ocr_markdown": pr["ocr_markdown"],
            "tokens": pr["tokens"],
            "duration_sec": pr["duration_sec"],
            "extraction_status": pr["extraction_status"],
            "verification_status": pr["verification_status"],
            "ocr_failed": pr["ocr_failed"],
        })

    # Log OCR pipeline execution to database
    database.record_pipeline_run(
        instrument_code=code,
        fiscal_year=fy,
        process_type="OCR",
        page_numbers=[pr["page_number"] for pr in page_results],
        duration_sec=total_duration,
        total_tokens=total_tokens,
        prompt_tokens=prompt_tokens,
        candidates_tokens=candidates_tokens,
        model=config.DEFAULT_MODEL,
        details=page_details
    )

    return {
        "instrument_code": code,
        "fiscal_year": fy,
        "completed_count": len(completed_pages),
        "pages": completed_pages,
        "usage_report": {
            "process_type": "OCR",
            "total_time_consumption_sec": round(total_duration, 2),
            "total_tokens": total_tokens,
            "prompt_tokens": prompt_tokens,
            "candidates_tokens": candidates_tokens,
            "page_count": len(completed_pages)
        }
    }

@router.post("/pipeline/extract")
def run_extraction_pipeline(req: ExtractionRequest, user: Username):
    """
    Extracts structured financial JSON metrics from OCR markdown (up to config.PAGE_PARALLELISM pages in parallel),
    verifies each page's extraction against the markdown value cells, and retries once with a
    corrective prompt when nothing was extracted from a table with values.
    Stores extracted metrics in SQLite and synthesizes multi-year raw CSV across all completed years!
    Needs the X-Username header; recorded as PAGES_EXTRACT.
    """
    return _run_logged(user, "PAGES_EXTRACT", req.instrument_code, req.fiscal_year, _extraction_pipeline, req,
                       details={"pages": req.page_numbers})


def _extraction_pipeline(req: ExtractionRequest):
    code = req.instrument_code.upper().strip()
    fy = req.fiscal_year
    pages = database.get_pages_for_year(code, fy)
    page_map = {p["page_number"]: p for p in pages}
    company_name = database.get_company_info(code).get("name", code)
    # Report-level facts for the model's scope / statement labels (checked against the printed page).
    report_context = database.get_report_label_context(code, fy)

    def _extract_single_page(num):
        page_rec = page_map.get(num)
        md_content = page_rec.get("ocr_markdown") if page_rec else ""

        if not md_content:
            disk_md_path = config.OCR_MD_DIR / code / fy / f"page_{num}.md"
            if disk_md_path.exists():
                try:
                    md_content = disk_md_path.read_text(encoding="utf-8")
                except Exception as e:
                    logger.warning(f"Could not read disk md file {disk_md_path}: {e}")

        if not md_content:
            img_path = page_rec.get("image_path") if page_rec else None
            if not img_path or not Path(img_path).exists():
                doc = database.get_document_by_year(code, fy)
                if doc and doc.get("filepath") and Path(doc["filepath"]).exists():
                    try:
                        p_info = ocr_service.render_single_pdf_page(doc["filepath"], code, fy, num)
                        img_path = p_info["image_path"]
                    except Exception as e:
                        logger.warning(f"Could not render page {num} for extraction: {e}")
            if img_path and Path(img_path).exists():
                try:
                    ocr_res = ocr_service.run_gemini_ocr_on_image(img_path, model_name=config.DEFAULT_MODEL,
                                                context=_ocr_context(code, fy, num))
                    md_content = ocr_res["markdown"]
                    database.update_page_ocr(code, fy, num, md_content, ocr_res["tokens"], ocr_res["duration_sec"])
                except Exception as e:
                    logger.error(f"Live OCR fallback error on page {num}: {e}")

        # Live Extraction using Gemini 3.5 Flash-Lite with dynamic entity context
        ext_res = extraction_service.extract_financial_data_from_markdown(
            md_content,
            company_name=company_name,
            security_code=code,
            model_name=config.DEFAULT_MODEL,
            fiscal_year=fy,
            report_context=report_context
        )
        items = ext_res.get("items", [])
        label_failures = ext_res.get("label_failures") or {}
        compositions = ext_res.get("compositions") or []
        note_cells = ext_res.get("note_cells") or []
        p_tokens = ext_res.get("tokens", 0)
        p_dur = ext_res.get("duration_sec", 0.0)
        p_tok = ext_res.get("prompt_tokens", 0)
        c_tok = ext_res.get("candidates_tokens", 0)

        # Verify: compare items/cells in the JSON against the numeric cells in the OCR markdown
        audit = extraction_verifier.verify_extraction(md_content, items)

        # Automated recovery: nothing extracted from a table with values -> retry once with a corrective prompt
        if (not audit["is_success"]) and audit["expected_cells"] > 0 and audit["failure_type"] == "ZERO_EXTRACTION":
            logger.warning(
                f"Page {num} ({fy}) failed extraction verification with 0 items for {audit['expected_cells']} cells. "
                "Executing automated recovery retry with corrective prompt..."
            )
            retry_res = extraction_service.extract_financial_data_from_markdown(
                md_content,
                company_name=company_name,
                security_code=code,
                model_name=config.DEFAULT_MODEL,
                fiscal_year=fy,
                report_context=report_context,
                corrective_hint=(
                    f"CRITICAL: Previous extraction attempt returned no metric rows. "
                    f"The markdown tables on this page have {audit['expected_cells']} numeric value cells across {audit['expected_rows']} rows. "
                    f"You MUST list every table that holds values in \"t\", map every value column in \"y\", "
                    f"and list every row that has a value in \"r\"."
                )
            )
            retry_items = retry_res.get("items", [])
            retry_audit = extraction_verifier.verify_extraction(md_content, retry_items)
            if retry_audit["is_success"] or len(retry_items) > len(items):
                logger.info(f"Automated recovery succeeded for Page {num} ({fy}): captured {retry_audit['extracted_cells']}/{retry_audit['expected_cells']} cells")
                items = retry_items
                audit = retry_audit
                label_failures = retry_res.get("label_failures") or {}
                compositions = retry_res.get("compositions") or []
                note_cells = retry_res.get("note_cells") or []
            # The retry costs tokens whether or not its result is kept
            p_tokens += retry_res.get("tokens", 0)
            p_dur += retry_res.get("duration_sec", 0.0)
            p_tok += retry_res.get("prompt_tokens", 0)
            c_tok += retry_res.get("candidates_tokens", 0)

        ext_status = "COMPLETED" if audit["is_success"] else "FAILED"

        database.update_page_extraction(
            instrument_code=code,
            fiscal_year=fy,
            page_number=num,
            tokens=p_tokens,
            duration_sec=p_dur,
            status=ext_status,
            expected_cells=audit["expected_cells"],
            extracted_cells=audit["extracted_cells"],
            missing_cells=audit["missing_cells"],
            completeness_pct=audit["completeness_pct"],
            verification_status=audit["status"]
        )
        database.save_extracted_metrics(code, fy, num, items)
        # Which printed cells add up to a total (the model's breakdowns, checked against the numbers)
        database.save_cell_compositions(code, fy, num, compositions)
        database.save_page_note_cells(code, fy, num, note_cells)
        # Metric type and unit follow deterministic rules; scope stays as the model labelled it.
        database.relabel_company(code, fiscal_year=fy)

        return {
            "page_number": num,
            "tokens": p_tokens,
            "duration_sec": p_dur,
            "prompt_tokens": p_tok,
            "candidates_tokens": c_tok,
            "items_count": len(items),
            "expected_cells": audit["expected_cells"],
            "extracted_cells": audit["extracted_cells"],
            "missing_cells": audit["missing_cells"],
            "completeness_pct": audit["completeness_pct"],
            "extraction_status": ext_status,
            "verification_status": audit["status"],
            "is_success": audit["is_success"],
            "message": audit["message"],
            # tables whose scope / statement label is not backed by the printed page (after one retry)
            "label_evidence_failures": {str(k): v for k, v in sorted(label_failures.items())},
            "compositions_count": len(compositions),
            "composition_mismatches": sum(1 for c in compositions if c["status"] != "MATCH"),
            # lines whose page does not show what they are: extracted but kept out of the CSV and the merge
            "unidentified_lines": sum(1 for it in items if it.get("verification_flag") == "FIELD_UNIDENTIFIED"),
        }

    def _extract_single_page_safe(num):
        # One failing page must not drop the others' results: report it as a failed entry.
        try:
            return _extract_single_page(num)
        except Exception:
            logger.exception("Extraction error on %s %s P.%s", code, fy, num)
            return {
                "page_number": num,
                "tokens": 0,
                "duration_sec": 0.0,
                "prompt_tokens": 0,
                "candidates_tokens": 0,
                "items_count": 0,
                "expected_cells": 0,
                "extracted_cells": 0,
                "missing_cells": 0,
                "completeness_pct": 0.0,
                "extraction_status": "FAILED",
                "verification_status": "FAILED",
                "is_success": False,
                "message": "Extraction failed (see server logs)",
                "label_evidence_failures": {},
                "compositions_count": 0,
                "composition_mismatches": 0,
                "unidentified_lines": 0,
            }

    ext_results = _run_per_page(_extract_single_page_safe, req.page_numbers)

    total_extracted_items = 0
    total_ext_tokens = 0
    total_ext_duration = 0.0
    ext_prompt_tokens = 0
    ext_candidates_tokens = 0
    ext_page_details = []

    for er in ext_results:
        total_extracted_items += er["items_count"]
        total_ext_tokens += er["tokens"]
        total_ext_duration = max(total_ext_duration, er["duration_sec"])  # pages run in parallel
        ext_prompt_tokens += er["prompt_tokens"]
        ext_candidates_tokens += er["candidates_tokens"]
        ext_page_details.append({
            "page_number": er["page_number"],
            "tokens": er["tokens"],
            "duration_sec": er["duration_sec"],
            "items_count": er["items_count"]
        })

    # Log Extraction pipeline execution to database
    database.record_pipeline_run(
        instrument_code=code,
        fiscal_year=fy,
        process_type="EXTRACTION",
        page_numbers=[er["page_number"] for er in ext_results],
        duration_sec=total_ext_duration,
        total_tokens=total_ext_tokens,
        prompt_tokens=ext_prompt_tokens,
        candidates_tokens=ext_candidates_tokens,
        model=config.DEFAULT_MODEL,
        details=ext_page_details
    )

    # Consolidate raw CSV across ALL analyzed years in SQLite for this company!
    all_metrics = database.get_all_metrics_for_company(code)
    raw_csv_res = csv_service.build_raw_csv_from_metrics(all_metrics)

    # Save to SQLite
    analyzed_years_set = set(m["fiscal_year"] for m in all_metrics)
    database.save_company_results(
        instrument_code=code,
        analyzed_years=list(analyzed_years_set),
        raw_csv_text=raw_csv_res["csv_text"],
        raw_stating_csv_text=raw_csv_res["stating_csv_text"],
        merged_csv_text="",  # "" keeps the last saved merge; /results/raw and /results/merged then report merged_stale: true
        merged_stating_csv_text="",
        remaining_data_json=json.dumps(raw_csv_res["remaining_data"]),
        merge_report_json="[]"
    )

    # Fetch cumulative usage report from database for this company
    cumulative_usage = database.get_pipeline_usage_report(code, fy)

    return {
        "instrument_code": code,
        "fiscal_year": fy,
        "items_extracted_count": total_extracted_items,
        "total_company_metrics_count": len(all_metrics),
        "analyzed_years": sorted(list(analyzed_years_set)),
        "raw_csv_data": raw_csv_res,
        "verification_results": ext_results,
        "usage_report": {
            "current_run": {
                "process_type": "EXTRACTION",
                "total_time_consumption_sec": round(total_ext_duration, 2),
                "total_tokens": total_ext_tokens,
                "prompt_tokens": ext_prompt_tokens,
                "candidates_tokens": ext_candidates_tokens,
                "page_count": len(ext_results)
            },
            "cumulative": cumulative_usage["summary"]
        }
    }

@router.get("/company/{code}/usage-report")
def get_company_usage_report(
    code: CodeParam,
    fiscal_year: Optional[str] = Query(None),
    runs_limit: Optional[int] = Query(None),
    runs_offset: int = Query(0)
):
    """Returns total time consumption and token usage report for OCR + Extraction from SQLite."""
    return database.get_pipeline_usage_report(code, fiscal_year, runs_limit, runs_offset)

@router.get("/company/{code}/year/{fy}/usage-report")
def get_company_year_usage_report(
    code: CodeParam,
    fy: FyParam,
    runs_limit: Optional[int] = Query(None),
    runs_offset: int = Query(0)
):
    """Returns token usage and time consumption report for a company and specific fiscal year from SQLite."""
    return database.get_pipeline_usage_report(code, fy, runs_limit, runs_offset)

@router.get("/company/{code}/verify-extraction")
def verify_company_extractions_endpoint(
    code: CodeParam,
    fiscal_year: Optional[str] = Query(None, pattern=FISCAL_YEAR_PATTERN)
):
    """
    Deterministically audits extraction completeness against the OCR markdown value cells.
    Returns status, expected cells, extracted cells, missing count, and failure flags per page.
    """
    return extraction_verifier.audit_company_extractions(code, fiscal_year, db_path=str(config.DB_PATH))

@router.get("/company/{code}/extracted-cells")
def get_company_extracted_cells_endpoint(
    code: CodeParam,
    fiscal_year: Optional[str] = Query(None, pattern=FISCAL_YEAR_PATTERN)
):
    """
    Returns extracted cell coordinates (table_num, table_row, table_col, value, concept, raw_field)
    grouped by "{fiscal_year}_{page_number}", for highlighting cells on the page during review.
    """
    return database.get_extracted_cells_for_company(code, fiscal_year)


@router.get("/company/{code}/unidentified-lines")
def get_company_unidentified_lines_endpoint(
    code: CodeParam,
    fiscal_year: Optional[str] = Query(None, pattern=FISCAL_YEAR_PATTERN)
):
    """
    Lines whose figures were extracted but whose page does not show what the line is (flag
    FIELD_UNIDENTIFIED): they are left out of the raw/merged CSV and the merge. Sorted by report and page:
    [{metric_id, fiscal_year, page_number, table_num, table_row, raw_field, reason, values: {year: number}}].
    Re-extract the page to resolve them.
    """
    out = []
    for m in database.get_all_metrics_for_company(code):
        if m.get("verification_flag") != "FIELD_UNIDENTIFIED" or (fiscal_year and m["fiscal_year"] != fiscal_year):
            continue
        first = (m.get("data") or [{}])[0]
        out.append({"metric_id": m["id"], "fiscal_year": m["fiscal_year"], "page_number": m["page_number"],
                    "table_num": first.get("table_num"), "table_row": first.get("table_row"),
                    "raw_field": m.get("raw_field"), "suggested_field": m.get("field"), "reason": m.get("flag_reason"),
                    "values": {str(o.get("year")): o.get("numeric_value") for o in m.get("data") or []}})
    return sorted(out, key=lambda r: (r["fiscal_year"], r["page_number"], r["table_num"] or 0, r["table_row"] or 0))


class LineOverrideRequest(BaseModel):
    fiscal_year: str = Field(pattern=FISCAL_YEAR_PATTERN)
    page_number: int = Field(ge=1)
    table_num: int = Field(1, ge=1)
    table_row: int = Field(ge=1)
    field: str = Field(min_length=1, max_length=300)
    note: Optional[str] = Field(None, max_length=1000)


@router.post("/company/{code}/line-overrides")
def set_company_line_override(code: CodeParam, req: LineOverrideRequest, user: Username):
    """
    A user names one printed row (a line from /unidentified-lines, or any line the model named wrongly): the row's
    metrics get that name and the flag USER_CONFIRMED, so they enter the CSV and the merge. The name is stored by page
    coordinates and applied again whenever the page is extracted again. Returns {id, updated_metrics, merged_stale};
    404 when no extracted line sits at those coordinates.
    Needs the X-Username header; recorded as LINE_NAME_SET.
    """
    with events.record(user, "LINE_NAME_SET", code, req.fiscal_year,
                       details={"page_number": req.page_number, "table_num": req.table_num,
                                "table_row": req.table_row, "field": req.field, "note": req.note}) as ev:
        res = database.set_line_override(code, req.fiscal_year, req.page_number, req.table_num, req.table_row,
                                         req.field, req.note)
        if not res["updated_metrics"]:
            database.delete_line_override(code, res["id"])
            raise HTTPException(status_code=404, detail="No extracted line at these page coordinates")
        ev.target("line_override", res["id"])
        ev.details["updated_metrics"] = res["updated_metrics"]
        return {**res, "merged_stale": database.is_merge_stale(code)}


@router.get("/company/{code}/line-overrides")
def list_company_line_overrides(code: CodeParam, fiscal_year: Optional[str] = Query(None, pattern=FISCAL_YEAR_PATTERN)):
    """The names users gave to rows: [{id, fiscal_year, page_number, table_num, table_row, field, note, updated_at}]."""
    return database.list_line_overrides(code, fiscal_year)


@router.delete("/company/{code}/line-overrides/{override_id}")
def delete_company_line_override(code: CodeParam, user: Username, override_id: int = PathParam(..., ge=1)):
    """Forgets a user's name; the lines keep it until their page is extracted again (then the model's name returns).
    Needs the X-Username header; recorded as LINE_NAME_REMOVE."""
    with events.record(user, "LINE_NAME_REMOVE", code, target_type="line_override", target_id=override_id):
        if not database.delete_line_override(code, override_id):
            raise HTTPException(status_code=404, detail="Unknown override")
        return {"deleted": True}


@router.get("/company/{code}/note-cells")
def get_company_note_cells_endpoint(
    code: CodeParam,
    fiscal_year: Optional[str] = Query(None, pattern=FISCAL_YEAR_PATTERN)
):
    """
    Printed cells that hold a note reference (the Note column, or a "(note 12.a)" inside a label), grouped by
    "{fiscal_year}_{page_number}", for highlighting them on the page: [{table_num, table_row, table_col,
    notes, concept_name, raw_field, metric_ids}].
    """
    return database.get_note_cells_for_company(code, fiscal_year)

# One merge at a time per company. The app runs as a single process (see Procfile),
# so in-memory locks are enough.
_merge_locks: dict = {}
_merge_locks_guard = threading.Lock()

# The stream reports an error if the merge thread is silent for this long.
MERGE_STREAM_IDLE_TIMEOUT_SEC = 900


def _acquire_merge_lock(code: str) -> threading.Lock:
    """Returns the company's merge lock, already acquired; raises 409 if a merge is running."""
    with _merge_locks_guard:
        lock = _merge_locks.setdefault(code, threading.Lock())
    if not lock.acquire(blocking=False):
        raise HTTPException(status_code=409, detail="A merge is already running for this company")
    return lock


def _merge_fy_label(analyzed_years_list: list) -> str:
    if len(analyzed_years_list) > 1:
        return f"{analyzed_years_list[0]}-{analyzed_years_list[-1]}"
    return analyzed_years_list[0] if analyzed_years_list else "MULTI_YEAR"


def _execute_merge(code: str, all_metrics: list, progress_callback=None, should_cancel=None) -> dict:
    """Runs the series merge (services/series_merge.py), saves the results and records the MERGE run.
    Returns the merge result and its token usage. Raises merge_service.MergeCancelled (merged results
    not saved) when cancelled. all_metrics is reloaded after the deterministic relabel."""
    run = series_merge.run_series_merge(code, progress_callback=progress_callback, should_cancel=should_cancel)
    merged_res, all_metrics = run["merged_res"], run["metrics"]
    if should_cancel is not None and should_cancel():
        raise merge_service.MergeCancelled()
    raw_csv_res = csv_service.build_raw_csv_from_metrics(all_metrics)

    analyzed_years_set = set(m["fiscal_year"] for m in all_metrics)

    database.save_company_results(
        instrument_code=code,
        analyzed_years=list(analyzed_years_set),
        raw_csv_text=raw_csv_res["csv_text"],
        raw_stating_csv_text=raw_csv_res["stating_csv_text"],
        merged_csv_text=merged_res["csv_text"],
        merged_stating_csv_text=merged_res["stating_csv_text"],
        remaining_data_json=json.dumps(raw_csv_res["remaining_data"]),
        merge_report_json=json.dumps(merged_res["merge_report"]),
        merged_rows_json=json.dumps(merged_res.get("rows", [])),
        conflicts_json=json.dumps(merged_res.get("value_mismatch_conflicts", []))
    )

    t_usage = merged_res.get("token_usage", {})
    analyzed_years_list = sorted(list(analyzed_years_set))

    database.record_pipeline_run(
        instrument_code=code,
        fiscal_year=_merge_fy_label(analyzed_years_list),
        process_type="MERGE",
        page_numbers=[],
        total_items=merged_res.get("total_pairs", 0),
        duration_sec=t_usage.get("duration_sec", 0.0),
        total_tokens=t_usage.get("total_tokens", 0),
        prompt_tokens=t_usage.get("prompt_tokens", 0),
        candidates_tokens=t_usage.get("candidates_tokens", 0),
        model=config.DEFAULT_MODEL,
        details=[{
            "total_pairs": merged_res.get("total_pairs", 0),
            "evaluated_pairs": merged_res.get("evaluated_pairs", 0),
            "matched_pairs": merged_res.get("matched_pairs", 0),
            "conflicts_count": len(merged_res.get("conflicts", [])),
            "batches_count": t_usage.get("batches_count", 0),
            "analyzed_years": analyzed_years_list
        }]
    )
    return {"merged_res": merged_res, "token_usage": t_usage}


def _merge_event_details(merged_res: dict, token_usage: dict) -> Dict[str, Any]:
    return {"merged_rows_count": len(merged_res.get("rows", [])), "merge_count": len(merged_res.get("merge_report", [])),
            "conflicts_count": len(merged_res.get("conflicts", [])), "total_pairs": merged_res.get("total_pairs", 0),
            "total_tokens": (token_usage or {}).get("total_tokens", 0),
            **({"merge_stats": merged_res["merge_stats"]} if merged_res.get("merge_stats") else {})}


@router.post("/pipeline/merge")
def run_merge_pipeline(req: MergeRequest, user: Username):
    """
    Links every line item across all analyzed reports into series (deterministic name and
    comparative-value matching; Gemini only for ambiguous links) and saves the merged CSV, rows and
    series state to SQLite. Returns 409 while another merge for the same company is running.
    Needs the X-Username header; recorded as MERGE_RUN.
    """
    code = req.instrument_code.upper().strip()
    lock = _acquire_merge_lock(code)
    try:
        all_metrics = database.get_all_metrics_for_company(code)

        if not all_metrics:
            # Fall back to existing company results if there are no metrics
            existing = database.get_company_results(code)
            if existing and existing.get("merged_csv_text"):
                return {
                    "instrument_code": code,
                    "merged_csv_text": existing["merged_csv_text"],
                    "merge_report": json.loads(existing.get("merge_report_json") or "[]")
                }

        with events.record(user, "MERGE_RUN", code) as ev:
            out = _execute_merge(code, all_metrics)
            ev.details.update(_merge_event_details(out["merged_res"], out["token_usage"]))
    finally:
        lock.release()

    merged_res = out["merged_res"]
    return {
        "instrument_code": code,
        "merged_rows_count": len(merged_res["rows"]),
        "merge_count": len(merged_res["merge_report"]),
        "conflicts_count": len(merged_res["conflicts"]),
        "total_pairs": merged_res.get("total_pairs", 0),
        "evaluated_pairs": merged_res.get("evaluated_pairs", 0),
        "matched_pairs": merged_res.get("matched_pairs", 0),
        "merge_stats": merged_res.get("merge_stats"),
        "token_usage": out["token_usage"],
        "merged_csv_data": merged_res
    }


@router.post("/pipeline/merge/stream")
async def run_merge_pipeline_stream(req: MergeRequest, request: Request, user: Username):
    """
    The series merge of /pipeline/merge with real-time NDJSON progress updates
    (init, one progress event per Gemini batch, finalize, then complete or error).
    Needs the X-Username header; recorded as MERGE_RUN (CANCELLED when the client disconnects or the stream times out).

    The stream always ends with exactly one terminal event, "complete" or "error". When the client
    disconnects, the merge stops before its next Gemini batch and nothing is saved.
    Returns 409 while another merge for the same company is running.
    """
    code = req.instrument_code.upper().strip()
    lock = _acquire_merge_lock(code)
    try:
        all_metrics = await run_in_threadpool(database.get_all_metrics_for_company, code)
        if not all_metrics:
            existing = database.get_company_results(code)
            if existing and existing.get("merged_csv_text"):
                merged_rows = csv_service.parse_csv_text_to_rows(existing["merged_csv_text"])
                event = {
                    "type": "complete",
                    "percent": 100,
                    "total_pairs": 0,
                    "evaluated_pairs": 0,
                    "matched_pairs": 0,
                    "conflicts_count": 0,
                    "merged_rows_count": len(merged_rows),
                    "merge_count": len(json.loads(existing.get("merge_report_json") or "[]")),
                    "merged_csv_data": {
                        "csv_text": existing["merged_csv_text"],
                        "rows": merged_rows
                    },
                    "subtitle": "Loaded existing merged results"
                }

                async def single_event():
                    yield json.dumps(event) + "\n"
                lock.release()
                return StreamingResponse(single_event(), media_type="application/x-ndjson")
            raise HTTPException(status_code=404, detail=f"No financial metrics found in SQLite for '{code}'")
    except BaseException:
        lock.release()
        raise

    queue = asyncio.Queue()
    loop = asyncio.get_running_loop()
    cancel_event = threading.Event()
    last_percent = [0]

    def emit(data: dict):
        # Progress never goes backwards, even though different merge stages report on their own scale.
        if "percent" in data:
            data = {**data, "percent": max(last_percent[0], data["percent"])}
            last_percent[0] = data["percent"]
        loop.call_soon_threadsafe(queue.put_nowait, data)

    def run_sync_merge():
        try:
            with events.record(user, "MERGE_RUN", code, details={"stream": True}) as ev:
                out = _execute_merge(code, all_metrics, progress_callback=emit, should_cancel=cancel_event.is_set)
                ev.details.update(_merge_event_details(out["merged_res"], out["token_usage"]))
            merged_res = out["merged_res"]
            emit({
                "type": "complete",
                "percent": 100,
                "total_pairs": merged_res.get("total_pairs", 0),
                "evaluated_pairs": merged_res.get("evaluated_pairs", 0),
                "matched_pairs": merged_res.get("matched_pairs", len(merged_res.get("merge_report", []))),
                "conflicts_count": len(merged_res.get("conflicts", [])),
                "value_mismatch_count": merged_res.get("value_mismatch_count", 0),
                "value_mismatch_conflicts": merged_res.get("value_mismatch_conflicts", []),
                "merged_rows_count": len(merged_res["rows"]),
                "merge_count": len(merged_res["merge_report"]),
                "stage": "complete",
                "gemini_calls_done": out["token_usage"].get("batches_count", 0),
                "gemini_calls_total": out["token_usage"].get("batches_count", 0),
                "estimate_exact": True,
                "merge_stats": merged_res.get("merge_stats"),
                "token_usage": out["token_usage"],
                "merged_csv_data": merged_res,
                "subtitle": f"Merge Complete! {len(merged_res['merge_report'])} clusters consolidated across all years."
            })
        except merge_service.MergeCancelled:
            logger.info("Streaming merge for %s cancelled; nothing saved", code)
            emit({"type": "error", "error": "Merge cancelled", "subtitle": "Merge cancelled"})
        except Exception as exc:
            logger.exception("Error in streaming merge for %s", code)
            emit({
                "type": "error",
                "error": f"Merge failed ({type(exc).__name__})",
                "subtitle": "Merge failed (see server logs)"
            })
        finally:
            lock.release()

    worker = threading.Thread(target=run_sync_merge, daemon=True)
    worker.start()

    async def stream_generator():
        idle_sec = 0.0
        try:
            while True:
                try:
                    item = await asyncio.wait_for(queue.get(), timeout=1.0)
                except asyncio.TimeoutError:
                    if await request.is_disconnected():
                        cancel_event.set()
                        return
                    idle_sec += 1.0
                    if not worker.is_alive() and queue.empty():
                        msg = "Merge stopped unexpectedly"
                    elif idle_sec >= MERGE_STREAM_IDLE_TIMEOUT_SEC:
                        cancel_event.set()
                        msg = "Merge timed out"
                    else:
                        continue
                    yield json.dumps({"type": "error", "error": msg, "subtitle": msg}) + "\n"
                    return
                idle_sec = 0.0
                yield json.dumps(item) + "\n"
                if item.get("type") in ("complete", "error"):
                    return
        finally:
            # Generator closed early (client gone): stop the merge before its next Gemini batch.
            if worker.is_alive():
                cancel_event.set()

    return StreamingResponse(stream_generator(), media_type="application/x-ndjson")

_RAW_RESULT_KEYS = ("raw_columns", "raw_csv_text", "raw_rows")
_MERGED_RESULT_KEYS = ("merged_csv_text", "merged_rows", "value_mismatch_conflicts", "value_mismatch_count", "merge_report", "inactive_series_count")


RAW_PAGE_ROWS = 100      # rows per chunk of GET /results/raw


@router.get("/company/{code}/results/raw")
def get_company_raw_results_endpoint(
    code: CodeParam,
    statement_type: Optional[str] = Query(None, pattern=r"^[A-Za-z_]{3,40}$"),
    offset: int = Query(0, ge=0),
    view: Literal["slim", "full"] = Query("slim", description="slim (default): only what the table needs; full: the former, much larger rows"),
):
    """One chunk of the raw table: always at most 100 rows (`raw_rows`), plus `years`, `raw_columns` and the status fields
    (`analyzed_years`, `has_raw_csv`, `has_merged_csv`, `merged_stale`, `updated_at`). Fast: no merge enrichment. `404` only
    when the company has neither results nor metrics.

    Rows are in a stable order, grouped by statement. `statement_type` keeps one statement's rows; `offset` is where the chunk
    starts (the first call has none). The response says where it stands: `total_rows` (rows matching `statement_type`),
    `offset`, `limit` (100), `has_more` and `next_offset` (the `offset` of the next chunk, null on the last one), and
    `statement_types` (`[{statement_type, rows}]` for the whole table, in table order, for tabs). There is no `raw_csv_text`:
    use GET /download/raw for the CSV.

    `view=slim` (default): every row is `{field, raw_field, statement_type, scope, unit, metric_type, cells}` with
    `cells[column.key] = {value, page_number, table_num, table_row, table_col}` (the cell's figure and where it is printed; the report is
    the column's `report_fiscal_year`). `view=full` gives the former rows, with the concept, behaviour, restatement and provenance fields,
    `values` and the whole cell objects (about 3 times the size)."""
    out = _company_results(code, include_merged=False)
    rows = out["raw_rows"]
    counts: Dict[str, int] = {}
    for r in rows:
        counts[r.get("statement_type") or ""] = counts.get(r.get("statement_type") or "", 0) + 1
    out["statement_types"] = [{"statement_type": k, "rows": v} for k, v in counts.items()]
    if statement_type is not None:
        rows = [r for r in rows if (r.get("statement_type") or "") == statement_type.upper()]
    page = rows[offset:offset + RAW_PAGE_ROWS]
    if view == "slim":
        page = [_slim_raw_row(r) for r in page]
    end = offset + len(page)
    out.pop("raw_csv_text")
    out.update(raw_rows=page, total_rows=len(rows), limit=RAW_PAGE_ROWS, offset=offset, has_more=end < len(rows),
               next_offset=end if end < len(rows) else None)
    return out


@router.get("/company/{code}/results/merged")
def get_company_merged_results_endpoint(
    code: CodeParam,
    statement_type: Optional[str] = Query(None, pattern=r"^[A-Za-z_]{3,40}$"),
    view: Literal["slim", "full"] = Query("slim", description="slim (default): only what the table needs; full: the former, much larger rows"),
):
    """One statement of the merged table: all its `merged_rows` (consolidated and standalone together) and the same status fields as
    the raw table. `404` only when the company has neither results nor metrics.

    `view=slim` (default): every row is `{series_id, field, scope, statement_type, unit, metric_type, level, active, values, flags,
    breakdown, breakdown_of, merged_from_count, mismatch_years}` and there are no `merge_report` or `value_mismatch_conflicts` (counts:
    `value_conflicts_count`, `value_merge_count`; the lists: `GET /results/merged/conflicts` and `/results/merged/report`; one row in full:
    `GET /results/merged/row/{series_id}`). `view=full` gives the former rows, with provenance, restatement dots, constituents, and the
    lists.

    Without `statement_type` the first statement that has rows is returned (statements in table order: BALANCE_SHEET,
    INCOME_STATEMENT, CASH_FLOW, CHANGES_IN_EQUITY, NOTES_AND_FOOTNOTES, ...); a statement with no rows is never offered, so no
    call is wasted. The response says what it holds and what comes next: `statement_type` (this chunk), `statement_types`
    (`[{statement_type, rows}]` of the statements that exist, in order), `has_more` and `next_statement_type` (null on the
    last one). Asking for a statement that has no rows gives `merged_rows: []` and the first existing one as
    `next_statement_type`. There is no `merged_csv_text`: use GET /download/merged."""
    out = _company_results(code, include_raw=False, chunk_merged=True, merged_statement=statement_type)
    out.pop("merged_csv_text")
    if view == "slim":
        out["merged_rows"] = [_slim_merged_row(r) for r in out["merged_rows"]]
        out.pop("merge_report", None)
        out.pop("value_mismatch_conflicts", None)
        res = database.get_company_results(code.upper().strip()) or {}
        try:
            out["value_conflicts_count"] = len(json.loads(res.get("conflicts_json") or "[]"))
            out["value_merge_count"] = len(json.loads(res.get("merge_report_json") or "[]"))
        except (TypeError, ValueError):
            out["value_conflicts_count"] = out["value_merge_count"] = 0
    return out


def _slim_merged_row(mr: Dict[str, Any]) -> Dict[str, Any]:
    """A merged row with only what the table needs. Everything else of a row is in `GET /results/merged/row/{series_id}`."""
    bd = mr.get("breakdown")
    return {
        "series_id": mr.get("series_id"),
        "field": mr.get("field"),
        "scope": mr.get("scope"),
        "statement_type": mr.get("statement_type"),
        "unit": mr.get("unit"),
        "metric_type": mr.get("metric_type"),
        "level": mr.get("aggregation_level"),
        "active": mr.get("active", True),
        "values": {y: v for y, v in (mr.get("values") or {}).items() if v not in (None, "")},
        "flags": mr.get("flags") or {},
        "breakdown": None if not bd else {
            "operation": bd.get("operation"), "status": bd.get("status"),
            "components": [{"series_id": c.get("series_id"), "sign": c.get("sign")} for c in bd.get("components") or []]},
        "breakdown_of": mr.get("breakdown_of") or [],
        "merged_from_count": len(mr.get("merged_from") or []),
        "mismatch_years": mr.get("mismatch_years") or [],
    }


def _slim_raw_row(r: Dict[str, Any]) -> Dict[str, Any]:
    """A raw row with only what the table needs: its name, statement, scope, unit and number type, and for every column that has a
    figure: the value and where it is printed (page, table, row, column), enough to open the cell with GET /cell-by-position."""
    return {
        "field": r.get("field"),
        "raw_field": r.get("raw_field"),
        "statement_type": r.get("statement_type"),
        "scope": r.get("scope"),
        "unit": r.get("unit"),
        "metric_type": r.get("metric_type"),
        "cells": {k: {"value": c.get("value"), "page_number": c.get("page_number"), "table_num": c.get("table_num"),
                      "table_row": c.get("table_row"), "table_col": c.get("table_col")}
                  for k, c in (r.get("cells") or {}).items()},
    }


@router.get("/company/{code}/results/merged/row/{series_id}")
def get_company_merged_row_endpoint(code: CodeParam, series_id: int = PathParam(..., ge=1)):
    """One merged row with everything (the former `merged_rows` item): provenance, `merged_from`, `constituent_details`, `link_summary`,
    `value_mismatches`, `micro_variances`, `restatement_variances`, `breakdown` with names, the restatement `flags` and the legacy `restatement_dots`.
    For the modals of a row; the table itself uses `GET /results/merged`. `404` for a series that is not a row of the saved merge."""
    clean = code.upper().strip()
    res = database.get_company_results(clean) or {}
    try:
        stored = json.loads(res.get("merged_rows_json") or "[]")
    except (TypeError, ValueError):
        stored = []
    stmt = next((r.get("statement_type") for r in stored if r.get("series_id") == series_id), None)
    if stmt is None:
        raise HTTPException(status_code=404, detail=f"Series {series_id} is not a merged row of '{clean}'")
    out = _company_results(clean, include_raw=False, chunk_merged=True, merged_statement=stmt)
    row = next((r for r in out["merged_rows"] if r.get("series_id") == series_id), None)
    if row is None:
        raise HTTPException(status_code=404, detail=f"Series {series_id} is not a merged row of '{clean}'")
    return row


@router.get("/company/{code}/results/merged/conflicts")
def get_company_value_conflicts_endpoint(code: CodeParam, statement_type: Optional[str] = Query(None, pattern=r"^[A-Za-z_]{3,40}$")):
    """The value conflicts of the merge (linked lines whose values disagree: `value_mismatch_conflicts`, each with both lines' figures
    and coordinates), all of them or one statement's. `{statement_type, count, value_mismatch_conflicts}`."""
    out = _company_results(code, include_raw=False)
    items = out["value_mismatch_conflicts"]
    if statement_type:
        items = [c for c in items if (c.get("statement_type") or "") == statement_type.upper()]
    return {"instrument_code": out["instrument_code"], "statement_type": statement_type.upper() if statement_type else None,
            "count": len(items), "value_mismatch_conflicts": items}


@router.get("/company/{code}/results/merged/report")
def get_company_merge_report_endpoint(code: CodeParam, statement_type: Optional[str] = Query(None, pattern=r"^[A-Za-z_]{3,40}$")):
    """The merge report (which names were merged into one line, with the reasons: `merge_report`), all of it or one statement's.
    `{statement_type, count, merge_report}`."""
    clean = code.upper().strip()
    res = database.get_company_results(clean) or {}
    try:
        items = json.loads(res.get("merge_report_json") or "[]")
    except (TypeError, ValueError):
        items = []
    if statement_type:
        items = [m for m in items if m.get("statement_type") in (statement_type.upper(), None)]
    return {"instrument_code": clean, "statement_type": statement_type.upper() if statement_type else None,
            "count": len(items), "merge_report": items}


def _merged_chunk(rows: List[Dict[str, Any]], conflicts: List[Dict[str, Any]], statement: Optional[str]):
    """One statement's merged rows and conflicts, and what the response says about the chunk (see the endpoint)."""
    counts: Dict[str, int] = {}
    for r in rows:
        counts[r.get("statement_type") or ""] = counts.get(r.get("statement_type") or "", 0) + 1
    order = list(counts)
    chosen = statement.upper() if statement else (order[0] if order else None)
    later = [k for k in order if chosen in order and order.index(k) > order.index(chosen)] if chosen in order else order
    nxt = later[0] if later else None
    info = {"statement_type": chosen, "statement_types": [{"statement_type": k, "rows": v} for k, v in counts.items()],
            "has_more": nxt is not None, "next_statement_type": nxt}
    kept = [r for r in rows if (r.get("statement_type") or "") == chosen]
    kept_conflicts = [c for c in conflicts if c.get("statement_type") in (chosen, None)]
    return kept, kept_conflicts, info


def _company_results(code: str, include_raw: bool = True, include_merged: bool = True,
                     chunk_merged: bool = False, merged_statement: Optional[str] = None) -> Dict[str, Any]:
    """The body of the two results endpoints (raw: include_merged=False; merged: include_raw=False). Without include_merged the merged rows are not loaded or enriched (the slow
    part); without include_raw the raw fields are left out of the response (they are still needed to enrich the merged rows).
    chunk_merged keeps one statement's merged rows (merged_statement, else the first that has rows) BEFORE the rows are enriched, so
    the dots are only computed for the rows that are sent."""
    chunk_info: Dict[str, Any] = {}
    clean_code = code.upper().strip()
    res = database.get_company_results(clean_code)
    all_metrics = database.get_all_metrics_for_company(clean_code)
    if not res and not all_metrics:
        raise HTTPException(status_code=404, detail=f"No results found for company '{clean_code}'")

    if not res:
        res = {}

    analyzed_years = []
    if res.get("analyzed_years_json"):
        try:
            analyzed_years = json.loads(res["analyzed_years_json"])
        except Exception:
            pass
    if not analyzed_years and all_metrics:
        analyzed_years = sorted(list(set(m["fiscal_year"] for m in all_metrics)))

    raw_rows = []
    merged_rows = []
    years = []
    raw_columns = []
    value_mismatch_conflicts = []

    if all_metrics:
        raw_res = csv_service.build_raw_csv_from_metrics(all_metrics)
        raw_rows = raw_res.get("rows", [])
        raw_columns = raw_res.get("raw_columns", [])
        years = raw_res.get("years", [])

        # 1. Load cached merged rows if available
        if include_merged and res.get("merged_rows_json"):
            try:
                merged_rows = json.loads(res["merged_rows_json"])
            except Exception:
                pass

        # 2. Fast fallback: parse saved CSV text without invoking the neural model
        if include_merged and not merged_rows and res.get("merged_csv_text"):
            merged_rows = csv_service.parse_csv_text_to_rows(res["merged_csv_text"])

        # 3. Load cached value mismatch conflicts
        if include_merged and res.get("conflicts_json"):
            try:
                value_mismatch_conflicts = json.loads(res["conflicts_json"])
            except Exception:
                pass
        if include_merged and chunk_merged:
            merged_rows, value_mismatch_conflicts, chunk_info = _merged_chunk(merged_rows, value_mismatch_conflicts, merged_statement)

        # 4. Enrich merged_rows & value_mismatch_conflicts with provenance from raw_rows
        raw_map = {}
        for r in raw_rows:
            stmt = r.get("statement_type", "")
            f_norm = r.get("field", "").strip().lower()
            rep_yr = str(r.get("report_year") or "").strip()
            pg = r.get("page_number")
            if stmt and f_norm:
                raw_map[(stmt, f_norm)] = r
                if rep_yr:
                    raw_map[(stmt, f_norm, rep_yr)] = r
                if pg is not None:
                    raw_map[(stmt, f_norm, str(pg))] = r
                    raw_map[(stmt, f_norm, pg)] = r
            if f_norm and f_norm not in raw_map:
                raw_map[f_norm] = r

        for mr in merged_rows:
            m_stmt = mr.get("statement_type", "")
            m_f = mr.get("field", "").strip().lower()
            match = raw_map.get((m_stmt, m_f)) or raw_map.get(m_f)
            if match:
                if not mr.get("page_number"):
                    mr["page_number"] = match.get("page_number")
                if not mr.get("report_year"):
                    mr["report_year"] = match.get("report_year")
                if not mr.get("table_num"):
                    mr["table_num"] = match.get("table_num")
                if not mr.get("table_row"):
                    mr["table_row"] = match.get("table_row")
                if not mr.get("table_col"):
                    mr["table_col"] = match.get("table_col")
                if not mr.get("raw_field"):
                    mr["raw_field"] = match.get("raw_field")
                if not mr.get("provenance"):
                    mr["provenance"] = match.get("provenance")
                if not mr.get("scope"):
                    mr["scope"] = match.get("scope", "CONSOLIDATED")

            # Enrich restated variance years and variances from match and constituent fields
            var_years = set(mr.get("restated_variance_years") or [])
            var_dict = dict(mr.get("restatement_variances") or {})
            if match:
                for vy in match.get("restated_variance_years", []):
                    var_years.add(str(vy))
                for vy, info in match.get("restatement_variances", {}).items():
                    var_dict[str(vy)] = info

            for cd in mr.get("constituent_details", []):
                cd_f = cd.get("field", "").strip().lower()
                c_match = raw_map.get((m_stmt, cd_f)) or raw_map.get(cd_f)
                if c_match:
                    if not cd.get("page_number"):
                        cd["page_number"] = c_match.get("page_number")
                    if not cd.get("report_year"):
                        cd["report_year"] = c_match.get("report_year")
                    if not cd.get("table_num"):
                        cd["table_num"] = c_match.get("table_num")
                    if not cd.get("table_row"):
                        cd["table_row"] = c_match.get("table_row")
                    if not cd.get("table_col"):
                        cd["table_col"] = c_match.get("table_col")
                    if not cd.get("raw_field"):
                        cd["raw_field"] = c_match.get("raw_field")
                    if not cd.get("provenance"):
                        cd["provenance"] = c_match.get("provenance")
                    for vy in c_match.get("restated_variance_years", []):
                        var_years.add(str(vy))
                    for vy, info in c_match.get("restatement_variances", {}).items():
                        var_dict[str(vy)] = info

            for constituent in (mr.get("merged_from") or []):
                c_norm = constituent.strip().lower()
                c_m = raw_map.get((m_stmt, c_norm)) or raw_map.get(c_norm)
                if c_m:
                    for vy in c_m.get("restated_variance_years", []):
                        var_years.add(str(vy))
                    for vy, info in c_m.get("restatement_variances", {}).items():
                        var_dict[str(vy)] = info

            mr["restated_variance_years"] = sorted(list(var_years))
            mr["restatement_variances"] = var_dict

        # 5. Restatement dots: the same records and rule as cell-details (_row_records, _restatement_dots)
        cands_by_stmt: Dict[str, List[Dict[str, Any]]] = {}
        for r in (_load_cell_candidates(clean_code) if merged_rows else []):
            cands_by_stmt.setdefault((r["statement_type"] or "").strip().upper(), []).append(r)
        parent_cands = _company_parent_candidates(clean_code) if merged_rows else None

        def _row_dots(names, stmt_type, scope, members=None, category=None, kind=None) -> tuple:
            """({year: dots}, [years a single report prints twice], {year: restatement entries})."""
            rows, _ = _row_records(cands_by_stmt.get((stmt_type or "").strip().upper(), []), names, scope,
                                   parent_cands, members, category)
            by_year: Dict[int, List[Dict[str, Any]]] = {}
            for r in rows:
                by_year.setdefault(r["fiscal_year"], []).append(r)
            dots = {str(y): d for y, recs in by_year.items() if (d := _restatement_dots(recs))}
            entries = {str(y): e for y, recs in by_year.items() if (e := _restatement_entries(recs, kind))}
            return dots, sorted(str(y) for y, recs in by_year.items() if _duplicate_in_report(recs)), entries

        for mr in merged_rows:
            # a merged row is its series: exactly the lines listed in constituent_details (as cell-details with series_id)
            members = {(str(cd.get("report_year") or ""), series_keys.label_key(cd.get("field") or ""))
                       for cd in mr.get("constituent_details") or []} or None
            all_dots, dup_years, all_entries = _row_dots(mr.get("merged_from") or [mr.get("field", "")], mr.get("statement_type"),
                                                         mr.get("scope"), members, mr.get("category"), mr.get("metric_type"))
            mr["duplicate_in_report_years"] = dup_years
            mr["flags"] = {y: e for y, e in all_entries.items() if mr.get("values", {}).get(y) not in (None, "")}
            mr_dots = {}
            mr_mvs = mr.get("micro_variances") or {}
            for y_str, cell_val in mr.get("values", {}).items():
                if cell_val is None or cell_val == "":
                    continue
                d_list = list(all_dots.get(str(y_str), []))
                if str(y_str) in mr_mvs and "purple" not in d_list:
                    d_list.append("purple")
                if d_list:
                    mr_dots[y_str] = d_list
            mr["restatement_dots"] = mr_dots
            mr["restated_variance_years"] = [y for y, d_list in mr_dots.items() if "yellow" in d_list]
            if mr_mvs:
                mr["has_micro_variance"] = True
                mr["micro_variance_years"] = sorted(list(mr_mvs.keys()))
            else:
                mr["has_micro_variance"] = False
                mr["micro_variance_years"] = []

        for r in raw_rows:
            r["duplicate_in_report_years"] = []
            r["restatement_dots"] = {}
            r["restated_variance_years"] = []

        for mr in merged_rows:
            m_stmt = mr.get("statement_type", "")
            for cd in mr.get("constituent_details", []):
                cd_f = cd.get("field", "").strip().lower()
                cd_rep = str(cd.get("report_year") or "").strip()
                cd_pg = cd.get("page_number")
                c_match = (
                    raw_map.get((m_stmt, cd_f, cd_rep)) or
                    raw_map.get((m_stmt, cd_f, str(cd_pg))) or
                    raw_map.get((m_stmt, cd_f, cd_pg)) or
                    raw_map.get((m_stmt, cd_f)) or
                    raw_map.get(cd_f)
                )
                if c_match:
                    cd["restatement_dots"] = c_match.get("restatement_dots", {})

        for c in value_mismatch_conflicts:
            stmt = c.get("statement_type", "")
            fa = c.get("field_a", "").strip().lower()
            fb = c.get("field_b", "").strip().lower()
            ma = raw_map.get((stmt, fa)) or raw_map.get(fa)
            mb = raw_map.get((stmt, fb)) or raw_map.get(fb)
            if ma:
                c["field_a_page"] = ma.get("page_number")
                c["field_a_year"] = ma.get("report_year")
                c["field_a_table_num"] = ma.get("table_num")
                c["field_a_table_row"] = ma.get("table_row")
                c["field_a_table_col"] = ma.get("table_col")
                c["field_a_raw_field"] = ma.get("raw_field")
                c["field_a_provenance"] = ma.get("provenance")
                c["field_a_restatement_dots"] = ma.get("restatement_dots", {})
            if mb:
                c["field_b_page"] = mb.get("page_number")
                c["field_b_year"] = mb.get("report_year")
                c["field_b_table_num"] = mb.get("table_num")
                c["field_b_table_row"] = mb.get("table_row")
                c["field_b_table_col"] = mb.get("table_col")
                c["field_b_raw_field"] = mb.get("raw_field")
                c["field_b_provenance"] = mb.get("provenance")
                c["field_b_restatement_dots"] = mb.get("restatement_dots", {})

    elif include_merged and res.get("merged_csv_text"):
        # No metrics left: still return the saved merge as rows
        merged_rows = csv_service.parse_csv_text_to_rows(res["merged_csv_text"])
        if chunk_merged:
            merged_rows, value_mismatch_conflicts, chunk_info = _merged_chunk(merged_rows, value_mismatch_conflicts, merged_statement)

    inactive = database.get_inactive_series_ids(clean_code) if include_merged else set()
    if include_merged:
        _apply_cell_choices(clean_code, merged_rows)
    for mr in merged_rows:
        mr["active"] = mr.get("series_id") not in inactive
    has_merged = bool(res.get("merged_csv_text"))
    out = {
        "instrument_code": clean_code,
        "analyzed_years": analyzed_years,
        "years": years,
        "raw_columns": raw_columns,
        "has_raw_csv": bool(res.get("raw_csv_text") or raw_rows),
        "has_merged_csv": has_merged,
        "merged_stale": has_merged and database.is_merge_stale(clean_code),
        "raw_csv_text": (raw_res.get("csv_text", "") if all_metrics else "") or res.get("raw_csv_text") or "",
        "merged_csv_text": res.get("merged_csv_text") or "",
        "raw_rows": raw_rows,
        "merged_rows": merged_rows,
        "value_mismatch_conflicts": value_mismatch_conflicts,
        "value_mismatch_count": len(value_mismatch_conflicts),
        "merge_report": json.loads(res.get("merge_report_json") or "[]") if include_merged else [],
        "updated_at": database.iso_utc(res.get("updated_at"))
    }
    out["inactive_series_count"] = len(inactive)       # dropped from the raw part with the other merged keys
    if chunk_merged and include_merged:
        if not chunk_info:           # nothing merged at all
            chunk_info = {"statement_type": None, "statement_types": [], "has_more": False, "next_statement_type": None}
        chosen = chunk_info["statement_type"]
        out["merge_report"] = [m for m in out["merge_report"] if m.get("statement_type") in (chosen, None)]
        out["value_mismatch_count"] = len(out["value_mismatch_conflicts"])
        out.update(chunk_info)
    for keys, wanted in ((_RAW_RESULT_KEYS, include_raw), (_MERGED_RESULT_KEYS, include_merged)):
        if not wanted:
            for k in keys:
                out.pop(k)
    return out

@router.get("/company/{code}/download/raw")
def download_raw_csv(code: CodeParam):
    clean_code = code.upper().strip()
    res = database.get_company_results(clean_code)
    csv_text = res.get("raw_csv_text") if res else None
    if not csv_text:
        all_metrics = database.get_all_metrics_for_company(clean_code)
        if all_metrics:
            raw_res = csv_service.build_raw_csv_from_metrics(all_metrics)
            csv_text = raw_res.get("csv_text")
    if not csv_text:
        raise HTTPException(status_code=404, detail="Raw CSV not yet generated")
    return Response(
        content=_UTF8_BOM + csv_text,
        media_type="text/csv",
        headers={"Content-Disposition": f"attachment; filename={clean_code}_financial_data.csv"}
    )

def _without_inactive_raw_rows(code: str, raw_rows: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
    """The raw rows minus those whose cells all belong to inactive series (a row none of whose cells is in a series, or
    one with any cell in an active series, stays)."""
    inactive = database.get_inactive_series_ids(code)
    if not inactive:
        return raw_rows
    with database.get_connection() as conn:
        sids = database.series_cell_map(conn, code)
    kept = []
    for r in raw_rows:
        mapped = {sids.get((c.get("report_fiscal_year"), c.get("page_number"), c.get("table_num") or 1,
                            c.get("table_row"), c.get("table_col"))) for c in (r.get("cells") or {}).values()}
        mapped.discard(None)
        if mapped and mapped <= inactive:
            continue
        kept.append(r)
    return kept


@router.get("/company/{code}/download/raw.xlsx")
def download_raw_xlsx(code: CodeParam):
    """The raw table as an Excel workbook: one sheet per statement (Balance Sheet, Income Statement, Cash Flow,
    Changes in Equity, Notes & Others), a header row with the report ("FY 2022-23") over its columns and one with each
    column's year (a restated column: its printed date), numbers as numbers. Built from the stored metrics like the
    raw CSV; `404` when there is nothing to export."""
    clean_code = code.upper().strip()
    out = _company_results(clean_code, include_merged=False)
    if not out.get("raw_rows"):
        raise HTTPException(status_code=404, detail="Raw table not yet generated")
    raw_rows = _without_inactive_raw_rows(clean_code, out["raw_rows"])
    return Response(
        content=xlsx_export.build_raw_workbook(raw_rows, out.get("raw_columns") or []),
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": f"attachment; filename={clean_code}_financial_data.xlsx"}
    )

@router.get("/company/{code}/download/merged")
def download_merged_csv(code: CodeParam):
    code = code.upper().strip()
    res = database.get_company_results(code)
    if not res or not res.get("merged_csv_text"):
        raise HTTPException(status_code=403, detail="Merged CSV is locked. Perform Merge first.")
    return Response(
        content=_UTF8_BOM + res["merged_csv_text"],
        media_type="text/csv",
        headers={"Content-Disposition": f"attachment; filename={code}_financial_data_merged.csv"}
    )

def _export_rows(code: str) -> Tuple[List[Dict[str, Any]], Dict[str, Any]]:
    """(merged rows for an export or the Standard tab, the company's saved results): the saved merge without the rows a user switched
    off, with the users' chosen disclosures applied. 403 until a merge has run."""
    res = database.get_company_results(code)
    if not res or not res.get("merged_rows_json"):
        raise HTTPException(status_code=403, detail="Merged table is locked. Perform Merge first.")
    try:
        rows = json.loads(res["merged_rows_json"])
    except (TypeError, ValueError):
        raise HTTPException(status_code=403, detail="Merged table is locked. Perform Merge first.")
    inactive = database.get_inactive_series_ids(code)
    rows = [r for r in rows if r.get("series_id") not in inactive]
    _apply_cell_choices(code, rows)
    return rows, res


@router.get("/company/{code}/download/merged.xlsx")
def download_merged_xlsx(code: CodeParam):
    """The merged table as an Excel workbook: one sheet per statement (Balance Sheet, Income Statement, Cash Flow,
    Changes in Equity, Notes & Others), the line names indented as in the results tree, numbers as numbers and
    restated cells highlighted. 403 until a merge has run."""
    code = code.upper().strip()
    rows, _ = _export_rows(code)
    return Response(
        content=xlsx_export.build_merged_workbook(rows),
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": f"attachment; filename={code}_financial_data_merged.xlsx"}
    )

@router.get("/company/{code}/download/standard.xlsx")
def download_standard_xlsx(code: CodeParam):
    """The "Standard Excel": the merged table in the standard layout, a sheet per statement (Balance Sheet, Income Statement, Cash
    Flow, Changes in Equity, Notes & Others; one per scope when a statement has both). Totals show the figure the reports print;
    an "As calculated" row (Excel formulas over their lines) and a "Check (calculated - reported)" row sit under each; the Balance Sheet has banners, group headers and an assets-minus-equity-and-
    liabilities check, the other statements read as printed (lines, then their total). Blue figures for lines, bold for totals,
    restated cells in yellow. Inactive rows are left out. `{CODE}_financial_data_standard.xlsx`. `/download/merged.xlsx` is a different export and is unchanged.
    403 until a merge has run."""
    code = code.upper().strip()
    rows, _ = _export_rows(code)
    return Response(
        content=xlsx_export.build_standard_workbook(rows),
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": f"attachment; filename={code}_financial_data_standard.xlsx"}
    )

@router.get("/company/{code}/results/standard")
def get_company_standard_results(code: CodeParam):
    """The Standard tab: the merged table in the standard layout as data, the same layout and figures as `/download/standard.xlsx`.
    `{instrument_code, years, merged_stale, updated_at, sheets, checks}`. `sheets` are `{name, statement, scope, style, rows}` (a sheet per
    statement, per scope when a statement has both). A total shows the figure the reports print and is followed by a `calculated` row ("As calculated": the sum of its parts, the union of
    the breakdowns across reports) and a `check` row (calculated minus reported, with `ok` per year); the Balance Sheet also ends with a `balance_check`. Row kinds and fields: `xlsx_export._layout_rows`. `checks` counts the
    check cells (`total`) and those off by more than rounding (`off`). Inactive rows are left out, a user's chosen disclosure is used.
    403 until a merge has run."""
    clean = code.upper().strip()
    rows, res = _export_rows(clean)
    layout = xlsx_export.standard_layout(rows)
    return {"instrument_code": clean, "years": layout["years"], "merged_stale": database.is_merge_stale(clean),
            "updated_at": database.iso_utc(res.get("updated_at")), "sheets": layout["sheets"],
            "checks": xlsx_export.layout_checks(layout)}


@router.get("/sync-ocr-md", operation_id="sync_ocr_md_get")
@router.post("/sync-ocr-md", operation_id="sync_ocr_md_post")
def sync_ocr_markdown_endpoint(user: Username):
    """Forces synchronization of all database OCR Markdown files to storage/ocr-md-files.
    Needs the X-Username header; recorded as OCR_MD_SYNC."""
    with events.record(user, "OCR_MD_SYNC"):
        database.sync_all_ocr_md_to_disk()
    files_list = []
    if config.OCR_MD_DIR.exists():
        for p in config.OCR_MD_DIR.glob("**/*.md"):
            files_list.append(str(p.relative_to(config.OCR_MD_DIR).as_posix()))
    return {
        "status": "success",
        "storage_path": "storage/ocr-md-files",
        "total_files": len(files_list),
        "files": sorted(files_list)
    }

@router.get("/ocr-md-files")
def list_ocr_markdown_files():
    """Lists all extracted Markdown files present on disk in storage/ocr-md-files."""
    files_list = []
    if config.OCR_MD_DIR.exists():
        for p in config.OCR_MD_DIR.glob("**/*.md"):
            files_list.append({
                "relative_path": str(p.relative_to(config.OCR_MD_DIR).as_posix()),
                "filename": p.name,
                "size_bytes": p.stat().st_size
            })
    return {
        "storage_path": "storage/ocr-md-files",
        "total_files": len(files_list),
        "files": files_list
    }

def _complete_or_none(record, numeric_keys) -> Optional[dict]:
    """The record with every numeric_keys value as a float, or None if any is missing or not numeric."""
    if not isinstance(record, dict):
        return None
    out = dict(record)
    for k in numeric_keys:
        v = record.get(k)
        if isinstance(v, bool) or v is None:
            return None
        try:
            out[k] = float(v)
        except (TypeError, ValueError):
            return None
    return out


@router.get("/company/{code}/cell-details")
def get_cell_details_endpoint(
    code: CodeParam,
    year: int = Query(..., ge=1900, le=2200),
    series_id: Optional[int] = Query(None, ge=1, description="the merged row's series_id (`merged_rows[].series_id`)"),
    metric_id: Optional[int] = Query(None, ge=1, description="a stored metric's id (`metric_id` of a breakdown part or "
                                     "total, a record or a printed cell): its series is used; ignored when series_id is given"),
):
    """
    Returns complete provenance, backhistory, stating vs restating records,
    and multi-year timeline for one cell of the merged table: the line is the series (`series_id`), the
    column is `year`. The records are exactly the series' members (the lines the merge linked across
    reports); field, statement and scope come from the series. Give `series_id`, or a `metric_id` whose series
    (from the last merge) is used. `404` for an unknown series, or a metric that is in no series (not merged yet);
    `422` when neither is given. For a cell of the raw table use GET /cell-by-position.
    """
    clean_code = code.upper().strip()
    if series_id is None:
        if metric_id is None:
            raise HTTPException(status_code=422, detail="Give series_id or metric_id")
        series_id = database.get_series_id_for_metric(clean_code, metric_id)
        if series_id is None:
            raise HTTPException(status_code=404, detail=f"Metric {metric_id} is in no series of '{clean_code}' (merge first)")

    s_ev = database.get_series_evidence(clean_code, series_id)
    if s_ev is None:
        raise HTTPException(status_code=404, detail=f"Series {series_id} not found for '{clean_code}'")
    series_members = {(m["report_fy"], m["label_key"]) for m in s_ev["members"]}
    series_category = _series_category(clean_code, series_id)
    member_fields = list(dict.fromkeys(m["field"] for m in s_ev["members"] if m["field"]))
    field = s_ev["canonical_field"] or (member_fields[0] if member_fields else "")
    statement_type = s_ev["statement_type"]
    scope = s_ev["scope"]
    value_kind = s_ev.get("value_kind") or series_keys.CURRENCY
    fields_list = [field] + [f for f in member_fields if f != field]
    parent_cands = _company_parent_candidates(clean_code)
    names = [f.strip() for f in fields_list if isinstance(f, str) and f.strip()][:200]

    # The same record selection and dot rule as /results/merged (_row_records, _restatement_dots)
    candidates = _load_cell_candidates(clean_code, statement_type)
    all_rows, _ = _row_records(candidates, names, scope, parent_cands, series_members, series_category)
    for r in all_rows:
        r.pop("_key", None)
        r["unit"] = csv_service.normalize_unit(r["unit"], r.get("field") or "")
        val = float(r["numeric_value"] or 0)
        r["calculation_formula"] = f"{val:,.0f} × {r['multiplier']:g} = {r['effective_value']:,.0f} {r['unit']}".strip()

    note_info = database.get_note_cells_for_metrics([r["metric_id"] for r in all_rows])
    for r in all_rows:
        info = note_info.get(r["metric_id"]) or {}
        r["notes"] = info.get("notes", [])
        r["note_cell"] = info.get("note_cell")

    # Disclosures of the requested year: exactly one record per publication (report, page, table): the
    # cell itself. Other printed cells are never shown as part of it (their sums are the breakdowns).
    target_all = [r for r in all_rows if r["fiscal_year"] == int(year)]
    reps = _publications(target_all)
    statuses = _publication_statuses(reps)
    orig_val = reps[0]["effective_value"] if reps else 0.0
    for r, st in zip(reps, statuses):
        diff = r["effective_value"] - orig_val
        r["status"] = st
        r["is_original"] = st == "ORIGINAL_STATED"
        r["is_restatement"] = st == "RESTATED"
        r["needs_review"] = st == DUPLICATE_IN_REPORT     # the same report prints the line twice
        r["diff_from_original"] = 0.0 if st == "ORIGINAL_STATED" else diff
        r["has_variance"] = st != "ORIGINAL_STATED" and abs(diff) > 1e-4
        r["restatement_type"] = (series_keys.restatement_type(orig_val, r["effective_value"], value_kind,
                                                              reps[0].get("multiplier") or 1.0, r.get("multiplier") or 1.0)[0]
                                 if st == "RESTATED" else None)
    for r in reps:
        _mark_extracted(r)
    all_cell_records = reps
    user_all = database.list_cell_disclosures(clean_code, series_id)
    chosen = {c["year"]: c for c in database.list_cell_current(clean_code, series_id)}
    series_unit = s_ev.get("unit") or "BDT"
    restated = [r for r, st in zip(reps, statuses) if st == "RESTATED"]

    restatement_variance = None
    if restated:
        orig_rep_v, latest_rep_v = reps[0]["effective_value"], restated[-1]["effective_value"]
        diff = latest_rep_v - orig_rep_v
        restatement_variance = {
            "original_value": orig_rep_v,
            "restated_value": latest_rep_v,
            "difference": diff,
            "percent_change": round(diff / orig_rep_v * 100, 2) if orig_rep_v != 0 else None     # None = n/a (zero base)
        }

    # Check if this cell had a micro-tolerance variance recorded during merge
    micro_variance_record = None
    c_res = database.get_company_results(clean_code)
    if c_res and c_res.get("merged_rows_json"):
        try:
            merged_rows_db = json.loads(c_res["merged_rows_json"])
            target_f_clean = field.strip().lower()
            for mr_item in merged_rows_db:
                m_fields = [mr_item.get("field", "").strip().lower()] + [str(f).strip().lower() for f in (mr_item.get("merged_from") or [])]
                matches = (mr_item.get("series_id") == series_id) if series_id is not None else (target_f_clean in m_fields)
                if matches:
                    mvs = mr_item.get("micro_variances", {})
                    if str(year) in mvs:
                        micro_variance_record = _complete_or_none(
                            mvs[str(year)], ("val_a", "val_b", "diff", "percent_change")
                        )
                        break
        except Exception as e:
            logger.warning(f"Error checking micro_variance in cell_details: {e}")

    # Breakdowns: the cells that add up to each record's cell, and the totals it is a part of
    comp_cache: Dict[tuple, List[Dict[str, Any]]] = {}
    for r in all_cell_records:
        fy_r, pg_r = str(r.get("report_year") or r.get("fiscal_year") or "").strip(), r.get("page_number")
        if not fy_r or pg_r is None:
            r["breakdown"], r["part_of"] = None, []
            continue
        if (fy_r, pg_r) not in comp_cache:
            comp_cache[(fy_r, pg_r)] = database.get_cell_compositions(clean_code, fy_r, pg_r)
        cell = (r.get("table_num") or 1, r.get("table_row"), r.get("table_col"))
        comps = comp_cache[(fy_r, pg_r)]
        bd = next((c for c in comps if (c["table_num"], c["total"]["row"], c["total"]["col"]) == cell), None)
        r["breakdown"] = _composition_view(bd) if bd else None
        r["part_of"] = [_composition_view(c) for c in comps if c["table_num"] == cell[0]
                        and any((p["row"], p["col"]) == cell[1:] for p in c["parts"])]

    user_year = [u for u in user_all if u["year"] == int(year)]
    current = _current_choice(chosen.get(int(year)), reps, user_year)
    orig_for_user = reps[0]["effective_value"] if reps else None
    all_cell_records = list(reps) + [_user_record(u, field, statement_type, scope, series_unit, orig_for_user) for u in user_year]
    for r in all_cell_records:
        r["is_current"] = r["record_key"] == current["record_key"]
    flags = _restatement_entries(target_all, value_kind) + (
        [_user_defined_flag(current["record_key"], current["value"], *_origin_of(current["record_key"], reps, user_year),
                            current["selection"]["set_by"], current["selection"]["set_at"])] if current["is_override"] else [])
    return {
        "instrument_code": clean_code,
        "canonical_field": field,
        "statement_type": statement_type,
        "target_year": int(year),
        "series_id": series_id,
        "merged_from": fields_list if len(fields_list) > 1 else [],
        "current_record_key": current["record_key"],
        "current_is_override": current["is_override"],
        "current_selection": current["selection"],
        "user_added_count": len(user_year),
        "cell_records": all_cell_records,
        "restatement_variance": restatement_variance,
        "flags": flags,
        "duplicate_in_report": DUPLICATE_IN_REPORT in statuses,
        "micro_variance": micro_variance_record,
        "has_micro_variance": bool(micro_variance_record),
        "total_disclosures": len(all_rows)
    }


ORIGIN_AI = "AI_EXTRACTED"
ORIGIN_USER = "USER_ADDED"


def _mark_extracted(r: Dict[str, Any]) -> None:
    """A disclosure the extraction model read from a printed cell: its origin and the key a choice of it is stored under."""
    r["origin"] = ORIGIN_AI
    r["disclosure_id"] = None
    r["created_by"] = None
    r["record_key"] = database.ai_record_key(r.get("report_year"), r.get("page_number"), r.get("table_num"),
                                             r.get("table_row"), r.get("table_col"))


def _user_record(u: Dict[str, Any], field: str, statement_type: Optional[str], scope: Optional[str], series_unit: str,
                 original_value: Optional[float]) -> Dict[str, Any]:
    """A disclosure a user added, in the shape of an extracted record (the printed-cell fields are null)."""
    unit = u.get("unit") or series_unit
    diff = None if original_value is None else u["value"] - original_value
    return {
        "origin": ORIGIN_USER, "disclosure_id": u["disclosure_id"], "record_key": u["record_key"],
        "created_by": u["created_by"], "created_at": u["created_at"], "user_note": u["note"],
        "metric_id": None, "report_year": None, "fiscal_year": u["year"], "page_number": None, "table_num": None,
        "table_row": None, "table_col": None, "date": u["date"], "restated": False,
        "field": field, "raw_field": None, "concept_name": field, "statement_type": statement_type,
        "scope": scope, "unit": unit, "numeric_value": u["value"], "multiplier": 1.0, "effective_value": u["value"],
        "calculation_formula": f"{u['value']:,.0f} (added by {u['created_by']}) {unit}".strip(),
        "status": "USER_ADDED", "is_original": False, "is_restatement": False, "needs_review": False,
        "diff_from_original": diff, "has_variance": bool(diff and abs(diff) > 1e-4), "restatement_type": None,
        "notes": [], "note_cell": None, "breakdown": None, "part_of": [],
    }


def _origin_of(record_key: str, ai_records: List[Dict[str, Any]], user_records: List[Dict[str, Any]]) -> tuple:
    """(origin, disclosure_id, report) of the disclosure a record_key names, for a USER_DEFINED flag."""
    for u in user_records:
        if u["record_key"] == record_key:
            return ORIGIN_USER, u["disclosure_id"], None
    for r in ai_records:
        if r["record_key"] == record_key:
            return ORIGIN_AI, None, r.get("report_year")
    return ORIGIN_AI, None, None


def _current_choice(selection: Optional[Dict[str, Any]], ai_records: List[Dict[str, Any]],
                    user_records: List[Dict[str, Any]]) -> Dict[str, Any]:
    """Which disclosure of one cell is current: the user's choice when it still names a disclosure of the cell, else the latest
    extracted one. {record_key, value, is_override, selection}; selection is {record_key, set_by, set_at, missing} or None.
    A choice whose disclosure is gone (its page was extracted again without that cell) is reported with missing=true and ignored."""
    values = {r["record_key"]: r["effective_value"] for r in ai_records}
    values.update({u["record_key"]: u["value"] for u in user_records})
    default_key = ai_records[-1]["record_key"] if ai_records else None
    if selection and selection["record_key"] in values:
        key = selection["record_key"]
        return {"record_key": key, "value": values[key], "is_override": True,
                "selection": {"record_key": key, "set_by": selection["set_by"], "set_at": selection["set_at"], "missing": False}}
    return {"record_key": default_key, "value": values.get(default_key), "is_override": False,
            "selection": None if not selection else {"record_key": selection["record_key"], "set_by": selection["set_by"],
                                                     "set_at": selection["set_at"], "missing": True}}


def _user_defined_flag(record_key: str, value: float, origin: str, disclosure_id: Optional[int], report: Optional[str],
                       set_by: str, set_at: Optional[str]) -> Dict[str, Any]:
    """The flag of a cell whose current disclosure a user chose (the same entry on merged rows and in cell-details)."""
    return {"type": series_keys.USER_DEFINED, "report": report, "date": None, "value": value, "origin": origin,
            "disclosure_id": disclosure_id, "record_key": record_key, "set_by": set_by, "set_at": set_at}


def _apply_cell_choices(code: str, rows: List[Dict[str, Any]]) -> None:
    """Puts the users' current-disclosure choices on merged rows, in place (rows come from the saved merge, so a re-merge keeps
    the choices): the year's value becomes the chosen disclosure's value, `stating_years[year]` its report ("USER" for an added
    one), and `overrides[year]` says what was chosen. A choice whose disclosure no longer exists is ignored."""
    chosen = database.list_cell_current(code)
    if not chosen:
        return
    by_series = {r.get("series_id"): r for r in rows if r.get("series_id") is not None}
    for c in chosen:
        row = by_series.get(c["series_id"])
        found = database.get_record_value(code, c["record_key"]) if row else None
        if not found:
            continue
        y = str(c["year"])
        row.setdefault("values", {})[y] = found["value"]
        row.setdefault("stating_years", {})[y] = found["report"] or "USER"
        row.setdefault("overrides", {})[y] = {"record_key": c["record_key"], "origin": found["origin"],
                                              "disclosure_id": found["disclosure_id"], "set_by": c["set_by"], "set_at": c["set_at"]}
        row.setdefault("flags", {}).setdefault(y, []).append(_user_defined_flag(
            c["record_key"], found["value"], found["origin"], found["disclosure_id"], found["report"], c["set_by"], c["set_at"]))


class DisclosureRequest(BaseModel):
    series_id: int = Field(ge=1)
    year: int = Field(ge=1900, le=2200)
    value: float = Field(allow_inf_nan=False, description="the amount in the row's unit, already scaled (476000, not 476 thousand)")
    note: str = Field(min_length=1, max_length=500, description="why the user adds it")
    unit: Optional[str] = Field(None, max_length=20, description="default: the row's unit")
    date: Optional[str] = Field(None, pattern=r"^\d{4}-\d{2}-\d{2}$", description="the date the figure is as at, YYYY-MM-DD")


class CurrentDisclosureRequest(BaseModel):
    series_id: int = Field(ge=1)
    year: int = Field(ge=1900, le=2200)
    record_key: str = Field(min_length=4, max_length=200)


def _cell_record_keys(code: str, series_id: int, year: int) -> set:
    """The record keys of every disclosure of one cell: the extracted ones (one per publication) and the user's."""
    body = get_cell_details_endpoint(code, year=year, series_id=series_id, metric_id=None)
    return {r["record_key"] for r in body["cell_records"]}


@router.post("/company/{code}/enter-disclosure")
def enter_disclosure(code: CodeParam, req: DisclosureRequest, user: Username):
    """Enters the user's disclosure for one cell of the merged table (a row = `series_id`, a column = `year`), for example to give an
    empty cell a value or to put a corrected figure next to the extracted ones. A cell has at most ONE user disclosure: when it already
    has one, this request REPLACES it (new figure, note, date, user and time; the `disclosure_id` stays), so one endpoint adds and edits.
    The extracted disclosures are never touched. Entering does NOT change what the merged table shows; to use it, make it the
    current disclosure (`PUT /cell-current`); if it already is the current one, the new figure is shown at once. It appears in
    `cell-details` with `origin: "USER_ADDED"`.
    Returns {disclosure_id, record_key, series_id, year, value, unit, date, note, created_by, created_at, replaced}; `replaced` is true
    when it replaced an earlier one. `404` for a series that is not in the last merge. Needs the X-Username header; recorded as
    DISCLOSURE_ENTER (with the replaced figure in its details)."""
    clean = code.upper().strip()
    with events.record(user, "DISCLOSURE_ENTER", clean, target_type="series", target_id=req.series_id,
                       details={"year": req.year, "value": req.value, "note": req.note}) as ev:
        s = database.get_series_evidence(clean, req.series_id)
        if s is None or s.get("status") != "active":
            raise HTTPException(status_code=404, detail=f"Series {req.series_id} not found for '{clean}'")
        made = database.enter_cell_disclosure(clean, req.series_id, req.year, req.value, req.unit or s.get("unit"), req.date,
                                              req.note.strip(), user)
        if made is None:
            raise HTTPException(status_code=404, detail=f"Series {req.series_id} not found for '{clean}'")
        previous = made.pop("previous")
        ev.details["disclosure_id"] = made["disclosure_id"]
        ev.details["replaced"] = made["replaced"]
        if previous:
            ev.details["previous"] = {k: previous[k] for k in ("value", "unit", "date", "note", "created_by", "created_at")}
        return {**made, "series_id": req.series_id}


@router.delete("/company/{code}/enter-disclosure")
def delete_disclosure(code: CodeParam, user: Username, series_id: int = Query(..., ge=1), year: int = Query(..., ge=1900, le=2200)):
    """Deletes the user's disclosure of one cell (`series_id` + `year`). Only the user's own entry can be deleted; the extracted
    disclosures cannot. When it was the cell's current disclosure the choice goes with it and the cell is back to its automatic
    current disclosure (the latest extracted one). `{series_id, year, deleted, current_reset}`: `deleted` is false when the cell had no
    user disclosure; `current_reset` is true when a choice of it was removed. `404` for a series that is not in the last merge. Needs the
    X-Username header; recorded as DISCLOSURE_DELETE (with the deleted figure in its details)."""
    clean = code.upper().strip()
    with events.record(user, "DISCLOSURE_DELETE", clean, target_type="series", target_id=series_id, details={"year": year}) as ev:
        if database.get_series_evidence(clean, series_id) is None:
            raise HTTPException(status_code=404, detail=f"Series {series_id} not found for '{clean}'")
        gone = database.delete_cell_disclosure(clean, series_id, year)
        ev.details["deleted"] = gone is not None
        if gone is None:
            return {"series_id": series_id, "year": year, "deleted": False, "current_reset": False}
        ev.details["disclosure_id"] = gone["disclosure_id"]
        ev.details["previous"] = {k: gone[k] for k in ("value", "unit", "date", "note", "created_by", "created_at")}
        ev.details["current_reset"] = gone["current_reset"]
        return {"series_id": series_id, "year": year, "deleted": True, "current_reset": gone["current_reset"]}


@router.put("/company/{code}/cell-current")
def set_cell_current(code: CodeParam, req: CurrentDisclosureRequest, user: Username):
    """Chooses the current disclosure of one cell (`series_id` + `year`): the one marked CURRENT, and the value the merged table
    and the Excel download show. This is an overwrite: one choice per cell, replaced by the next. `record_key` is the
    `record_key` of one of the cell's `cell_records` (an extracted or a user-added one); others give `422`. `404` for an unknown
    series. Needs no new merge and survives a re-merge. Needs the X-Username header; recorded as CELL_CURRENT_SET.
    Returns {series_id, year, current_record_key, current_is_override: true}."""
    clean = code.upper().strip()
    with events.record(user, "CELL_CURRENT_SET", clean, target_type="series", target_id=req.series_id,
                       details={"year": req.year, "record_key": req.record_key}) as ev:
        if database.get_series_evidence(clean, req.series_id) is None:
            raise HTTPException(status_code=404, detail=f"Series {req.series_id} not found for '{clean}'")
        if req.record_key not in _cell_record_keys(clean, req.series_id, req.year):
            raise HTTPException(status_code=422, detail="record_key is not a disclosure of this cell")
        if not database.set_cell_current(clean, req.series_id, req.year, req.record_key, user):
            raise HTTPException(status_code=404, detail=f"Series {req.series_id} not found for '{clean}'")
        return {"series_id": req.series_id, "year": req.year, "current_record_key": req.record_key, "current_is_override": True}


@router.delete("/company/{code}/cell-current")
def reset_cell_current(code: CodeParam, user: Username, series_id: int = Query(..., ge=1),
                       year: int = Query(..., ge=1900, le=2200)):
    """Gives a cell back its automatic current disclosure (the latest extracted one): removes the user's choice.
    `{series_id, year, reset}`; `reset` is false when the cell had no choice. Needs the X-Username header; recorded as
    CELL_CURRENT_RESET."""
    clean = code.upper().strip()
    with events.record(user, "CELL_CURRENT_RESET", clean, target_type="series", target_id=series_id, details={"year": year}) as ev:
        reset = database.reset_cell_current(clean, series_id, year)
        ev.details["reset"] = reset
        return {"series_id": series_id, "year": year, "reset": reset}


def parse_coordinate_ref(ref_str: Optional[str]) -> Optional[Dict[str, Any]]:
    """
    Parses coordinate reference strings into discrete components.
    Supported patterns:
      'FY 2002-03 | P-59 | T-2 | R-9 | C-3'
      '2016-17 | P-35 | T-1 | R-16 | C-4'
      'FY 2016-17 | P-35 | R-16 | C-4' (table_num defaults to 1)
      'FY 2016 | P-36 | T-2 | R-1 | C-2'
    """
    if not ref_str or not isinstance(ref_str, str):
        return None
    s = ref_str.strip()
    if not s:
        return None

    p_m = re.search(r"\bP-(\d+)\b", s, re.I)
    r_m = re.search(r"\bR-(\d+)\b", s, re.I)
    c_m = re.search(r"\bC-(\d+)\b", s, re.I)
    if not (p_m and r_m and c_m):
        return None

    t_m = re.search(r"\bT-(\d+)\b", s, re.I)
    table_num = int(t_m.group(1)) if t_m else 1

    parts = [p.strip() for p in s.split("|")]
    first_part = parts[0]
    fy_m = re.search(r"(?:FY\s*)?(\d{4}(?:-\d{2,4})?)", first_part, re.I)
    if not fy_m:
        fy_m = re.search(r"(?:FY\s*)?(\d{4}(?:-\d{2,4})?)", s, re.I)
    if not fy_m:
        return None

    return {
        "fiscal_year": fy_m.group(1),
        "page_number": int(p_m.group(1)),
        "table_num": table_num,
        "table_row": int(r_m.group(1)),
        "table_col": int(c_m.group(1)),
    }


@router.get("/company/{code}/cell-by-position")
def get_cell_by_position_endpoint(
    code: CodeParam,
    ref: Optional[str] = Query(None, description="Full coordinate reference string e.g. 'FY 2002-03 | P-59 | T-2 | R-9 | C-3'"),
    fiscal_year: Optional[str] = Query(None, description="Report fiscal year"),
    page_number: Optional[int] = Query(None, ge=1, description="Page number"),
    table_num: Optional[int] = Query(None, ge=1, description="Table number on page (default 1)"),
    table_row: Optional[int] = Query(None, ge=1, description="Table row number"),
    table_col: Optional[int] = Query(None, ge=1, description="Table column number"),
):
    """Looks up extracted LLM metric and raw markdown content for a single cell by its spatial coordinates."""
    clean_code = code.upper().strip()
    parsed = parse_coordinate_ref(ref) if ref else None

    # Explicit query parameters take precedence over parsed reference tokens:
    fy = fiscal_year or (parsed["fiscal_year"] if parsed else None)
    pg = page_number if page_number is not None else (parsed["page_number"] if parsed else None)
    t_num = table_num if table_num is not None else (parsed["table_num"] if parsed else 1)
    r_num = table_row if table_row is not None else (parsed["table_row"] if parsed else None)
    c_num = table_col if table_col is not None else (parsed["table_col"] if parsed else None)

    if not fy or pg is None or r_num is None or c_num is None:
        raise HTTPException(
            status_code=422,
            detail="Missing coordinate parameters: provide 'ref' or all four of ('fiscal_year', 'page_number', 'table_row', 'table_col').",
        )

    res = database.get_extracted_cell_by_position(
        instrument_code=clean_code,
        fiscal_year=fy,
        page_number=pg,
        table_num=t_num or 1,
        table_row=r_num,
        table_col=c_num,
    )
    return res


_TOTAL_LABEL_RE = re.compile(r"\[total\b|total\s+consolidated|\btotal\b")


def _publication_key(r: Dict[str, Any]) -> tuple:
    # a column the report marks restated is its own publication of the cell (the same line printed again for that year)
    return (str(r.get("report_year") or "").strip(), r.get("page_number") or 1, r.get("table_num") or 1,
            1 if r.get("restated") else 0)


def _representative(siblings: List[Dict[str, Any]]) -> Dict[str, Any]:
    """The record standing for one publication when a table prints several matching rows: a total row,
    else the first non-zero one."""
    chosen = siblings[0]
    for s in siblings:
        if _TOTAL_LABEL_RE.search(f"{s.get('raw_field') or ''} {s.get('field') or ''}".lower()):
            return s
        if chosen.get("effective_value") == 0.0 and s.get("effective_value") != 0.0:
            chosen = s
    return chosen


def _publications(records: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
    """One representative record per publication (report, page, table, restated column or not) of a cell, oldest
    report first; inside a report the restated print comes after the one printed as it was."""
    groups: Dict[tuple, List[Dict[str, Any]]] = {}
    for r in records:
        groups.setdefault(_publication_key(r), []).append(r)
    keys = sorted(groups, key=lambda k: (csv_service.extract_year_int(k[0]) or 0, k[1], k[2], k[3]))
    return [_representative(groups[k]) for k in keys]


DUPLICATE_IN_REPORT = "DUPLICATE_IN_REPORT"


REPEATED_IN_REPORT = "REPEATED_IN_REPORT"


def _publication_statuses(reps: List[Dict[str, Any]]) -> List[str]:
    """Status of each publication of one cell (one statement and scope: the records come from one row), oldest
    report first as _publications orders them. Only a LATER report can restate a figure:
    - the first report's first publication: ORIGINAL_STATED;
    - the first publication of each later report: RESTATED;
    - a column the same report marks restated ("1st July 2022 (Re-Stated)" next to "30 June 2022"): RESTATED (the
      year's restated figure; it counts for the dots even though it is in the same report);
    - another publication inside the same report (another table / page of the same statement): REPEATED_IN_REPORT
      when it prints exactly the same figure (fine, e.g. the number of shares printed three times), else
      DUPLICATE_IN_REPORT (one report giving two figures for the line: needs review). Neither is a restatement."""
    first_value: Dict[str, float] = {}
    out = []
    for r in reps:
        rep = str(r.get("report_year") or "").strip()
        if rep in first_value and r.get("restated"):
            out.append("RESTATED")
        elif rep in first_value:
            same = abs(r["effective_value"] - first_value[rep]) <= 1e-4
            out.append(REPEATED_IN_REPORT if same else DUPLICATE_IN_REPORT)
        else:
            out.append("RESTATED" if first_value else "ORIGINAL_STATED")
            first_value[rep] = r["effective_value"]
    return out


def _restatement_dots(records: List[Dict[str, Any]]) -> List[str]:
    """The cell's restatement dots, used by /results/merged and cell-details alike: one dot per LATER report that
    printed the cell (the original disclosure gets none, and a second printing in the same report is not a
    restatement), yellow when its value differs from the original, green when equal."""
    reps = _publications(records)
    statuses = _publication_statuses(reps)
    if len(reps) < 2:
        return []
    orig = reps[0]["effective_value"]
    return ["yellow" if abs(r["effective_value"] - orig) > 1e-4 else "green"
            for r, st in zip(reps, statuses) if st == "RESTATED"]


def _restatement_entries(records: List[Dict[str, Any]], kind: Optional[str]) -> List[Dict[str, Any]]:
    """What is behind a cell's restatement dots, as values: one entry per LATER printing of the cell (the same ones that get a dot),
    {type, report, date, value, original_value, original_report, difference, percent_change}. `type` is how the printing differs from
    the FIRST one printed: RESTATED_IDENTICAL (same figure), RESTATED_MICRO (rounding), RESTATED_LOW (up to 25 %), RESTATED_MID (up to
    100 %) or RESTATED_HIGH (more than 100 %); see series_keys.restatement_type."""
    reps = _publications(records)
    if len(reps) < 2:
        return []
    first = reps[0]
    out = []
    for r, st in zip(reps, _publication_statuses(reps)):
        if st != "RESTATED":
            continue
        typ, diff, pct = series_keys.restatement_type(first["effective_value"], r["effective_value"], kind or series_keys.CURRENCY,
                                                      first.get("multiplier") or 1.0, r.get("multiplier") or 1.0)
        out.append({"type": typ, "report": r.get("report_year"), "date": r.get("date"), "value": r["effective_value"],
                    "original_value": first["effective_value"], "original_report": first.get("report_year"),
                    "difference": diff, "percent_change": None if pct is None else round(pct, 2)})
    return out


def _duplicate_in_report(records: List[Dict[str, Any]]) -> bool:
    """True when one report prints the cell twice with DIFFERENT figures (needs review)."""
    return DUPLICATE_IN_REPORT in _publication_statuses(_publications(records))


def _prepare_records(records: List[Dict[str, Any]]) -> None:
    for r in records:
        mult = float(r.get("multiplier") or 1.0)
        val = float(r.get("numeric_value") or 0)
        r["multiplier"] = mult
        r["effective_value"] = val * mult
        r["scope"] = r.get("scope") or "CONSOLIDATED"
        r["restated"] = bool(r.get("restated"))
        r["_key"] = csv_service.normalize_field_name(r.get("field") or "")


def _narrow_by_category(rows: List[Dict[str, Any]], category: Optional[str]) -> List[Dict[str, Any]]:
    """When ONE page prints the row's name under different headings (the "Total" of note 45 and of note 46 on the
    same page), only the records under the heading closest to `category` stay on that page, so another note's line
    is never compared with this one. Pages where the name sits under a single heading, and the same name on other
    pages (a second printing of a statement elsewhere in the report), are untouched."""
    target = set(csv_service.normalize_field_name(category or "").split())
    if not target:
        return rows
    by_page: Dict[tuple, Dict[str, List[Dict[str, Any]]]] = {}
    for r in rows:
        by_page.setdefault((str(r.get("report_year") or ""), r.get("page_number")), {}).setdefault(
            csv_service.normalize_field_name(r.get("category") or ""), []).append(r)

    def score(cat: str) -> float:
        toks = set(cat.split())
        return len(toks & target) / len(toks | target) if toks | target else 0.0
    keep: List[Dict[str, Any]] = []
    for groups in by_page.values():
        keep.extend(next(iter(groups.values())) if len(groups) == 1 else groups[max(groups, key=score)])
    return keep


def _row_records(candidates: List[Dict[str, Any]], names: List[str], scope: Optional[str], parent_cands: set,
                 members: Optional[set] = None, category: Optional[str] = None) -> tuple:
    """The disclosures of one row, used by /results/merged and cell-details alike. Only the row's scope;
    with `members` (a series: {(report, label_key)}) exactly the series' lines, else the full label
    (normalised like the raw CSV) or, when nothing carries it, a trailing "(Entity)" read as that
    subsidiary; subsidiary breakdown records are left out unless the row is that subsidiary.
    Returns (records, target_entity)."""
    row_scope = "STANDALONE" if (scope or "").strip().upper() == "STANDALONE" else "CONSOLIDATED"
    pool = [r for r in candidates if r["scope"] == row_scope]
    target_entity = None
    if members is not None:
        rows = [r for r in pool if (r["report_year"], series_keys.label_key(r.get("field") or "")) in members]
    else:
        full_keys = {csv_service.normalize_field_name(n) for n in names}
        rows = [r for r in pool if r["_key"] in full_keys]
        if not rows:
            for n in names:
                m = re.search(r"^(.*\S)\s*\(([^()]+)\)$", n)
                if not m:
                    continue
                base, ent = csv_service.normalize_field_name(m.group(1)), csv_service.normalize_entity_name(m.group(2))
                rows = [r for r in pool if r["_key"] == base and r.get("entity")
                        and csv_service.normalize_entity_name(r["entity"]) == ent]
                if rows:
                    target_entity = m.group(2).strip()
                    break
                seg = csv_service.normalize_field_name(m.group(2))
                rows = [r for r in pool if r["_key"] == base and r.get("segment")
                        and csv_service.normalize_field_name(r["segment"]) == seg]
                if rows:
                    break
    if target_entity is None:
        own = [r for r in rows if not csv_service.is_distinct_subsidiary_entity(r.get("entity"), parent_cands)]
        rows = own or rows
    if members is None and any(not r.get("segment") for r in rows):
        # a row matched by its full label is the line without a segment: segment columns are their own rows
        rows = [r for r in rows if not r.get("segment")]
    return _narrow_by_category(rows, category), target_entity


def _company_parent_candidates(code: str) -> set:
    cands = {code}
    with database.get_connection() as conn:
        row = conn.execute("SELECT name FROM companies WHERE instrument_code = ?", (code,)).fetchone()
    if row and row["name"]:
        cands.add(row["name"])
    return cands


def _series_category(code: str, series_id: int) -> Optional[str]:
    """Category (heading) of a series' merged row in the saved merge, or None."""
    res = database.get_company_results(code)
    try:
        for mr in json.loads((res or {}).get("merged_rows_json") or "[]"):
            if mr.get("series_id") == series_id:
                return mr.get("category")
    except Exception:
        pass
    return None


_CELL_RECORD_SQL = """
    SELECT fm.id as metric_id, fm.fiscal_year as report_year, fm.page_number, fm.statement_type,
           fm.concept_name, 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.raw_field, fm.field, fm.tag,
           fmd.id as data_id, fmd.year as fiscal_year, fmd.date, fmd.restated, fmd.numeric_value, fmd.unit, fmd.multiplier,
           fmd.table_num, fmd.table_row, fmd.table_col, fmd.seq_order
    FROM financial_metrics fm
    JOIN financial_metric_data fmd ON fm.id = fmd.metric_id
    WHERE fm.instrument_code = ? {extra}
    ORDER BY fmd.year ASC, fm.fiscal_year ASC, fm.page_number ASC, fmd.seq_order ASC
"""


def _load_cell_candidates(code: str, statement_type: Optional[str] = None) -> List[Dict[str, Any]]:
    """Every disclosure of a company (optionally one statement), ready for _row_records."""
    extra, params = "", [code]
    if statement_type and statement_type != "ALL":
        extra, params = "AND fm.statement_type = ?", [code, statement_type]
    with database.get_connection() as conn:
        rows = [dict(r) for r in conn.execute(_CELL_RECORD_SQL.format(extra=extra), params).fetchall()
                if r["fiscal_year"] is not None]
    _prepare_records(rows)
    return rows


def _ocr_context(code: str, fy: str, page_number: int) -> Dict[str, Any]:
    """What the OCR model is told about the page (written by it as the page's `Source:` line)."""
    return {"company_name": (database.get_company_info(code) or {}).get("name") or "", "security_code": code,
            "fiscal_year": fy, "page_number": page_number}


def _composition_view(c: Dict[str, Any], parts: bool = True) -> Dict[str, Any]:
    out = {"composition_id": c["id"], "direction": c["direction"], "operation": c["operation"],
           "expression": c["expression"], "status": c["status"],
           "difference": c["difference"], "parts_sum": c["parts_sum"], "total": c["total"]}
    if parts:
        out["parts"] = c["parts"]
    return out


@router.get("/company/{code}/year/{fy}/compositions")
def list_cell_compositions(code: CodeParam, fy: FyParam, page_number: Optional[int] = Query(None, ge=1)):
    """Breakdowns the extraction model identified (which printed cells add up to a total), with the
    arithmetic check (MATCH / MISMATCH) and the extracted metric of every cell."""
    clean = code.upper().strip()
    comps = database.get_cell_compositions(clean, fy, page_number)
    return {"instrument_code": clean, "fiscal_year": fy, "page_number": page_number, "count": len(comps),
            "mismatch_count": sum(1 for c in comps if c["status"] != "MATCH"), "compositions": comps}


# ---------------------------------------------------------------------------
# Series merge: series, link evidence, review queue and user overrides
# ---------------------------------------------------------------------------
LabelParam = Annotated[str, Field(min_length=1, max_length=300)]


class MergeOverrideRequest(BaseModel):
    kind: Literal["FORCE_LINK", "FORCE_SPLIT", "RENAME", "DETACH"]
    statement_type: str = Field(pattern=r"^[A-Z_]{3,40}$")
    scope: Literal["CONSOLIDATED", "STANDALONE"]
    entity_key: str = Field("", max_length=200)
    segment_key: str = Field("", max_length=200)
    a_label: LabelParam
    a_report: Optional[str] = Field(None, pattern=FISCAL_YEAR_PATTERN)
    b_label: Optional[str] = Field(None, min_length=1, max_length=300)
    b_report: Optional[str] = Field(None, pattern=FISCAL_YEAR_PATTERN)
    new_label: Optional[str] = Field(None, min_length=1, max_length=300)
    note: Optional[str] = Field(None, max_length=1000)


class ReviewResolveRequest(BaseModel):
    action: Literal["accept", "reject", "dismiss"]
    note: Optional[str] = Field(None, max_length=1000)


def _create_override(code: str, req: MergeOverrideRequest, username: Optional[str] = None) -> int:
    if req.kind in ("FORCE_LINK", "FORCE_SPLIT") and not req.b_label:
        raise HTTPException(status_code=422, detail=f"{req.kind} needs b_label")
    if req.kind == "RENAME" and not req.new_label:
        raise HTTPException(status_code=422, detail="RENAME needs new_label")
    if req.kind in ("FORCE_LINK", "FORCE_SPLIT") and bool(req.a_report) != bool(req.b_report):
        raise HTTPException(status_code=422, detail="Give both a_report and b_report, or neither (applies to every report)")
    if req.kind == "FORCE_LINK" and req.a_report and req.a_report == req.b_report:
        raise HTTPException(status_code=409, detail="Two lines of the same report cannot be the same series")
    return database.add_merge_override(code, {
        "kind": req.kind, "statement_type": req.statement_type, "scope": req.scope,
        "entity_key": req.entity_key, "segment_key": req.segment_key,
        "a_label_key": req.a_label, "a_report": req.a_report, "b_label_key": req.b_label, "b_report": req.b_report,
        "new_label": req.new_label, "note": req.note,
    }, created_by=username)


@router.get("/company/{code}/series")
def list_company_series(code: CodeParam):
    """Every series of the last merge: id, canonical field, statement, scope, value kind, unit and members."""
    clean = code.upper().strip()
    return {"instrument_code": clean, "series": database.list_series(clean)}


class SeriesActiveRequest(BaseModel):
    active: bool


class SeriesActiveBulkRequest(BaseModel):
    series_ids: List[int] = Field(min_length=1, max_length=2000)
    active: bool


def _set_series_active(user: str, code: str, series_ids: List[int], active: bool, single: bool) -> Dict[str, Any]:
    clean = code.upper().strip()
    with events.record(user, "SERIES_ACTIVATE" if active else "SERIES_DEACTIVATE", clean,
                       target_type="series" if single else None, target_id=series_ids[0] if single else None,
                       details={"series_ids": series_ids[:100], "count": len(series_ids)}) as ev:
        res = database.set_series_active(clean, series_ids, active)
        if single and res["unknown"]:
            raise HTTPException(status_code=404, detail=f"Series {series_ids[0]} not found for '{clean}'")
        ev.details.update(updated=len(res["updated"]), unknown=len(res["unknown"]))
        return {"active": active, **res, "inactive_series_count": len(database.get_inactive_series_ids(clean))}


@router.put("/company/{code}/series/{series_id}/active")
def set_company_series_active(code: CodeParam, req: SeriesActiveRequest, user: Username,
                              series_id: int = PathParam(..., ge=1)):
    """Marks one merged row (series) active or inactive. An inactive series is not in the Excel downloads
    (`/download/merged.xlsx`, `/download/raw.xlsx`) but stays in the results (`merged_rows[].active = false`), so a
    checkbox can turn it back on. Needs no new merge. `404` for an unknown series. Needs the X-Username header;
    recorded as SERIES_ACTIVATE / SERIES_DEACTIVATE. Returns {active, updated, unchanged, unknown, inactive_series_count}."""
    return _set_series_active(user, code, [series_id], req.active, single=True)


@router.put("/company/{code}/series-active")
def set_company_series_active_bulk(code: CodeParam, req: SeriesActiveBulkRequest, user: Username):
    """Marks many merged rows (up to 2000 series ids) active or inactive in one call: "select all", "clear all", or a
    statement's rows. Ids that are no series of the company are returned in `unknown` and ignored. Same effect, header and
    event as the single call. Returns {active, updated, unchanged, unknown, inactive_series_count}."""
    return _set_series_active(user, code, req.series_ids, req.active, single=False)


@router.get("/company/{code}/series/{series_id}/evidence")
def get_company_series_evidence(code: CodeParam, series_id: int = PathParam(..., ge=1)):
    """A series with every link between its members and the evidence behind each link."""
    clean = code.upper().strip()
    out = database.get_series_evidence(clean, series_id)
    if out is None:
        raise HTTPException(status_code=404, detail=f"Series {series_id} not found for '{clean}'")
    return out


@router.get("/company/{code}/series-formation")
def get_company_series_formation(code: CodeParam, series_id: int = Query(..., ge=1)):
    """How one merged row (series) was formed, for the "Consolidated Disclosures" page: `{series_id, canonical_field, statement_type,
    scope, unit, value_kind, active, merged_values, members, unmatched_metrics}`. `merged_values` is the row's `{year: value}` in the
    saved merge. Each member (one line of one report, oldest first, with `link_rule` / `link_confidence` of the link that joined it to
    the earlier line) lists its `metrics`: every stored metric (`metric_id`, `field`, `raw_field`, `concept_name`, `metric_type`, page)
    with its `values` `[{data_id, year, date, restated, value, unit, multiplier, table_num, table_row, table_col}]`, only the cells
    that are part of the series. `404` for a series that does not exist."""
    clean = code.upper().strip()
    out = database.get_series_formation(clean, series_id)
    if out is None:
        raise HTTPException(status_code=404, detail=f"Series {series_id} not found for '{clean}'")
    res = database.get_company_results(clean) or {}
    try:
        stored = json.loads(res.get("merged_rows_json") or "[]")
    except (TypeError, ValueError):
        stored = []
    row = next((r for r in stored if r.get("series_id") == series_id), None)
    out["merged_values"] = (row or {}).get("values") or {}
    return out


@router.get("/company/{code}/merge/review")
def list_merge_review(code: CodeParam, status: str = Query("open", pattern=r"^(open|resolved|dismissed|all)$")):
    """Review queue of the last merge (ambiguous links, value conflicts, splits, stale links ...)."""
    clean = code.upper().strip()
    items = database.list_review_items(clean, None if status == "all" else status)
    return {"instrument_code": clean, "status": status, "count": len(items), "items": items}


_LINK_REVIEW_KINDS = ("AMBIGUOUS_LINK", "VALUE_CONFLICT", "STALE_LINK", "LONG_GAP", "GAP_BLOCKED", "MAGNITUDE_JUMP", "RESTATEMENT_CHECK")


@router.post("/company/{code}/merge/review/{item_id}/resolve")
def resolve_merge_review(code: CodeParam, req: ReviewResolveRequest, user: Username, item_id: int = PathParam(..., ge=1)):
    """accept / reject a proposed link (saved as a FORCE_LINK / FORCE_SPLIT override), or dismiss the item.
    Other item kinds can only be dismissed (accept is treated as an acknowledgement).
    Needs the X-Username header; recorded as REVIEW_ACCEPT / REVIEW_REJECT / REVIEW_DISMISS."""
    with events.record(user, f"REVIEW_{req.action.upper()}", code, target_type="review_item", target_id=item_id,
                       details={"note": req.note}) as ev:
        out = _resolve_merge_review(code, req, item_id, ev, user)
        ev.details.update(override_id=out["override_id"], status=out["status"])
        return out


def _resolve_merge_review(code: str, req: ReviewResolveRequest, item_id: int, ev: "events.Event", username: Optional[str] = None):
    clean = code.upper().strip()
    item = database.get_review_item(clean, item_id)
    if item is None:
        raise HTTPException(status_code=404, detail=f"Review item {item_id} not found for '{clean}'")
    ev.details["review_kind"] = item["kind"]
    override_id = None
    if req.action == "dismiss" or item["kind"] not in _LINK_REVIEW_KINDS:
        if req.action == "reject" and item["kind"] not in _LINK_REVIEW_KINDS:
            raise HTTPException(status_code=422, detail=f"{item['kind']} items can only be accepted or dismissed")
        status = "dismissed" if req.action == "dismiss" else "resolved"
    else:
        p = item["payload"]
        stmt, scope_, ent, seg = (p.get("partition") or "|||").split("|")[:4]
        override_id = _create_override(clean, MergeOverrideRequest(
            kind="FORCE_LINK" if req.action == "accept" else "FORCE_SPLIT",
            statement_type=p.get("statement_type") or stmt, scope=p.get("scope") or scope_,
            entity_key=p.get("entity_key", ent) or "", segment_key=p.get("segment_key", seg) or "",
            a_label=p["a_label_key"], a_report=p.get("a_report"), b_label=p["b_label_key"], b_report=p.get("b_report"),
            note=req.note or f"review item {item_id}",
        ), username)
        status = "resolved"
    database.set_review_item_status(item_id, status, resolution=req.action, override_id=override_id)
    return {"id": item_id, "status": status, "resolution": req.action, "override_id": override_id,
            "merged_stale": override_id is not None}


@router.get("/company/{code}/merge/overrides")
def list_merge_overrides(code: CodeParam, include_inactive: bool = Query(False),
                         source: Optional[Literal["user", "llm"]] = Query(None)):
    """Every override of the company. `source` is "user" (decided by a person) or "llm" (a link Gemini made in the last merge;
    rewritten by every merge unless a user made it permanent or broke it, see POST .../override/{id}/decide)."""
    clean = code.upper().strip()
    rows = database.load_merge_overrides(clean, active_only=not include_inactive, source=source)
    for r in rows:
        r["created_at"] = database.iso_utc(r["created_at"])
        r["updated_at"] = database.iso_utc(r["updated_at"])
        r["active"] = bool(r["active"])
    return {"instrument_code": clean, "overrides": rows}


@router.post("/company/{code}/merge/override", status_code=201)
def create_merge_override(code: CodeParam, req: MergeOverrideRequest, user: Username):
    """Saves a user decision. It applies from the next merge (the saved merge is then reported stale).
    Needs the X-Username header; recorded as OVERRIDE_FORCE_LINK / OVERRIDE_FORCE_SPLIT / OVERRIDE_RENAME / OVERRIDE_DETACH."""
    clean = code.upper().strip()
    with events.record(user, f"OVERRIDE_{req.kind}", clean, details=req.model_dump(exclude_none=True)) as ev:
        oid = _create_override(clean, req, user)
        ev.target("merge_override", oid)
        return {"id": oid, "instrument_code": clean, "merged_stale": True}


class OverrideDecisionRequest(BaseModel):
    action: Literal["make_permanent", "break_link"]
    note: Optional[str] = Field(None, max_length=1000)


@router.post("/company/{code}/merge/override/{override_id}/decide")
def decide_llm_override(code: CodeParam, req: OverrideDecisionRequest, user: Username, override_id: int = PathParam(..., ge=1)):
    """A user's decision on a link Gemini made (an override of source "llm"): `make_permanent` keeps the link as a user override,
    `break_link` splits the two lines instead (the LLM override is removed and a user FORCE_SPLIT is saved). Either way the
    override becomes a user decision that later merges keep. Applies from the next merge (the merge is reported stale).
    Needs the X-Username header; recorded as OVERRIDE_MAKE_PERMANENT / OVERRIDE_BREAK_LINK."""
    clean = code.upper().strip()
    with events.record(user, f"OVERRIDE_{req.action.upper()}", clean, target_type="merge_override", target_id=override_id,
                       details={"note": req.note}) as ev:
        o = database.get_merge_override(clean, override_id)
        if o is None:
            raise HTTPException(status_code=404, detail=f"Override {override_id} not found for '{clean}'")
        if o.get("source") != "llm" or not o["active"]:
            raise HTTPException(status_code=422, detail=f"Override {override_id} is not an active LLM override")
        new_id = override_id
        if req.action == "make_permanent":
            database.make_llm_override_permanent(clean, override_id, user)
        else:
            database.deactivate_merge_override(clean, override_id)
            new_id = _create_override(clean, MergeOverrideRequest(
                kind="FORCE_SPLIT", statement_type=o["statement_type"], scope=o["scope"], entity_key=o["entity_key"] or "",
                segment_key=o["segment_key"] or "", a_label=o["a_label_key"], a_report=o["a_report"],
                b_label=o["b_label_key"], b_report=o["b_report"], note=req.note or f"broke Fintra AI override {override_id}"), user)
            ev.details["split_override_id"] = new_id
        row = database.get_merge_override(clean, new_id)
        row["created_at"], row["updated_at"] = database.iso_utc(row["created_at"]), database.iso_utc(row["updated_at"])
        row["active"] = bool(row["active"])
        return {"override": row, "merged_stale": True}


@router.delete("/company/{code}/merge/override/{override_id}")
def delete_merge_override(code: CodeParam, user: Username, override_id: int = PathParam(..., ge=1)):
    """Needs the X-Username header; recorded as OVERRIDE_REMOVE."""
    clean = code.upper().strip()
    with events.record(user, "OVERRIDE_REMOVE", clean, target_type="merge_override", target_id=override_id):
        if not database.deactivate_merge_override(clean, override_id):
            raise HTTPException(status_code=404, detail=f"Override {override_id} not found for '{clean}'")
        return {"id": override_id, "active": False, "merged_stale": True}


# ---------------------------------------------------------------------------
# Job queue (jobs.py): one job per page (OCR, extraction) or per company (merge), polled via GET /jobs/{job_id}
# ---------------------------------------------------------------------------

def _page_request(req: PageJobRequest, model):
    return model(instrument_code=req.instrument_code, fiscal_year=req.fiscal_year, page_numbers=[req.page_number])


@router.post("/jobs/ocr", status_code=202)
def submit_ocr_job(req: PageJobRequest, user: Username):
    """Queues the OCR of ONE page (the page image rendered from the stored PDF); the job result is POST /pipeline/ocr's
    response for that page. Send one request per page, all at once: the backend queues them and runs them in order
    within Gemini's rate limit. Needs the X-Username header; recorded as PAGES_OCR when the job is queued."""
    return _submit_logged_job("ocr", "ocr", user, "PAGES_OCR", req.instrument_code, req.fiscal_year, req.page_number,
                              {"pages": [req.page_number]}, _ocr_pipeline, _page_request(req, OcrRequest))


@router.post("/jobs/ocr-client-pages", status_code=202)
async def submit_ocr_client_pages_job(
    user: Username,
    instrument_code: CodeForm,
    fiscal_year: FyForm,
    page_number: int = Form(..., ge=1, le=10000),
    file: UploadFile = File(...)
):
    """Saves ONE page image rendered by the client now and queues its OCR; the job result is
    POST /pipeline/ocr-client-pages's response for that page. Needs the X-Username header; recorded as PAGES_OCR."""
    details = {"client_pages": True, "pages": [page_number], "job": "ocr-client-pages"}
    event_id = events.start(user, "PAGES_OCR", instrument_code, fiscal_year, details=details)
    # a failure while saving the image closes the event; on success the worker finishes it
    with events.record(user, "PAGES_OCR", event_id=event_id, keep_open=True):
        code, fy, saved_images = await _save_client_pages(instrument_code, fiscal_year, json.dumps([page_number]), [file])
    page = next(iter(saved_images))
    return _submit_logged_job("ocr-client-pages", "ocr", user, "PAGES_OCR", code, fy, page, details,
                              _ocr_saved_client_pages, code, fy, saved_images, event_id=event_id)


@router.post("/jobs/extract", status_code=202)
def submit_extract_job(req: PageJobRequest, user: Username):
    """Queues the extraction of ONE page; the job result is POST /pipeline/extract's response for that page. It runs
    after any earlier job on the same page (its OCR). Needs the X-Username header; recorded as PAGES_EXTRACT."""
    return _submit_logged_job("extract", "extract", user, "PAGES_EXTRACT", req.instrument_code, req.fiscal_year,
                              req.page_number, {"pages": [req.page_number]}, _extraction_pipeline,
                              _page_request(req, ExtractionRequest))


def _merge_job(code: str, job: jobs.JobContext) -> Dict[str, Any]:
    """The merge of POST /pipeline/merge as a job: waits (instead of 409) while another merge of the company runs,
    reports its progress on the job, and stops at the next Gemini batch when the job is cancelled."""
    with _merge_locks_guard:
        lock = _merge_locks.setdefault(code, threading.Lock())
    lock.acquire()
    try:
        all_metrics = database.get_all_metrics_for_company(code)
        if not all_metrics:
            raise HTTPException(status_code=404, detail=f"No financial metrics found in SQLite for '{code}'")
        last_percent = [0]

        def progress(data: dict) -> None:
            if "percent" in data:        # never backwards, as on the stream
                last_percent[0] = max(last_percent[0], data["percent"])
                data = {**data, "percent": last_percent[0]}
            job.progress({k: v for k, v in data.items() if k != "merged_csv_data"})

        out = _execute_merge(code, all_metrics, progress_callback=progress, should_cancel=job.cancelled)
    finally:
        lock.release()
    merged_res, usage = out["merged_res"], out["token_usage"]
    job.progress({"type": "complete", "stage": "complete", "percent": 100,
                  "gemini_calls_done": usage.get("batches_count", 0), "gemini_calls_total": usage.get("batches_count", 0),
                  "estimate_exact": True})
    return {
        "instrument_code": code,
        "merged_rows_count": len(merged_res["rows"]),
        "merge_count": len(merged_res["merge_report"]),
        "conflicts_count": len(merged_res["conflicts"]),
        "value_mismatch_count": merged_res.get("value_mismatch_count", 0),
        "total_pairs": merged_res.get("total_pairs", 0),
        "evaluated_pairs": merged_res.get("evaluated_pairs", 0),
        "matched_pairs": merged_res.get("matched_pairs", 0),
        "merge_stats": merged_res.get("merge_stats"),
        "token_usage": usage,
    }


@router.post("/jobs/merge", status_code=202)
def submit_merge_job(req: MergeRequest, user: Username):
    """Queues the merge of one company. It starts after every earlier job of that company (its OCR and extraction
    jobs) has finished, and page jobs sent after it wait for it. While it runs, the job's "progress" holds the
    stream's latest progress event (percent, stage, gemini_calls_done / total, eta_sec...). The result is a summary;
    read the table with GET /company/{code}/results/merged. Cancel with DELETE /jobs/{job_id}.
    Needs the X-Username header; recorded as MERGE_RUN."""
    code = req.instrument_code.upper().strip()
    return _submit_logged_job("merge", "merge", user, "MERGE_RUN", code, None, None, {}, _merge_job, code,
                              takes_job=True)


def _merge_socket_end(job: Dict[str, Any]) -> Dict[str, Any]:
    """The last message of a merge socket: the finished job as {type: "complete" | "error", ...}."""
    if job["status"] == "done":
        return {"type": "complete", "stage": "complete", "percent": 100, "job_id": job["job_id"], "status": "done",
                **(job.get("result") or {}),
                "subtitle": f"Merge complete. {(job.get('result') or {}).get('merge_count', 0)} lines consolidated across all years."}
    msg = "Merge cancelled" if job["status"] == "cancelled" else (job.get("error") or "Merge failed")
    return {"type": "error", "job_id": job["job_id"], "status": job["status"], "error": msg, "subtitle": msg,
            "status_code": job.get("status_code")}


# Registered only when config.MERGE_SOCKET_ENABLED (MERGE_SOCKET_ENABLED=1): the hosted server does not pass sockets through, so
# the frontend polls GET /jobs/{job_id}/events (the same messages). Kept so the socket can be switched back on.
async def merge_socket(websocket: WebSocket, code: str):
    """Runs the merge of one company and feeds its status over a socket. Connect to
    `ws://<host>/ocr/ws/merge/{code}?username=<name>&api_key=<key>` (a browser cannot send the X-Username / X-API-Key headers on a
    socket, so they are query parameters; `api_key` is needed when the server has API_KEY set). Connecting starts the merge as a
    job (the same queue as POST /jobs/merge: it waits for earlier jobs of the company, recorded as MERGE_RUN). Every message is JSON:

    - `{"type": "queued", "job_id", "position", "waiting_for_earlier_job"}` first, then `{"type": "started"}` when the job runs;
    - a progress event for every step, in particular two or more per Fintra AI call (`type` `init`, `progress` with `stage`
      `gemini_call_started` / `gemini_retry` / `gemini_call_done`, `finalize`): `percent`, `subtitle`, `gemini_calls_done` /
      `gemini_calls_total`, `eta_sec`, ... (as the NDJSON stream), each with `merge_stats` (reports / lines done and remaining, links by
      system / user / Fintra AI, Fintra AI questions, review items made); a `report_linked` progress after every report;
    - one last message, `{"type": "complete", ...summary of the merge...}` or `{"type": "error", "error", "status_code"}`, and
      the server closes the socket (code 1000; 1008 when the username, key or company is refused). Read the merged table with
      GET /company/{code}/results/merged afterwards.

    The client can send `{"action": "cancel"}`; closing the socket cancels the merge (it stops before its next Fintra AI call and
    nothing is saved)."""
    await websocket.accept()
    clean = code.upper().strip()
    q = websocket.query_params
    name = (q.get("username") or "").strip()

    async def refuse(message: str, status_code: int) -> None:
        await websocket.send_json({"type": "error", "error": message, "subtitle": message, "status_code": status_code})
        await websocket.close(code=1008)

    if api_key_configured() and not _safe_equals(q.get("api_key") or "", os.getenv("API_KEY", "")):
        return await refuse("Not authenticated", 401)
    if not name or len(name) > events.USERNAME_MAX_LENGTH or not name.isprintable():
        return await refuse("username is required (1-64 printable characters)", 400)
    if not re.match(INSTRUMENT_CODE_PATTERN, clean):
        return await refuse("Invalid instrument code", 422)
    if not await run_in_threadpool(database.get_all_metrics_for_company, clean):
        return await refuse(f"No financial metrics found in SQLite for '{clean}'", 404)

    loop = asyncio.get_running_loop()
    inbox: asyncio.Queue = asyncio.Queue()

    def on_job_event(payload: Dict[str, Any]) -> None:       # runs in the merge worker thread
        loop.call_soon_threadsafe(inbox.put_nowait, payload)

    try:
        job = await run_in_threadpool(lambda: _submit_logged_job(
            "merge", "merge", name, "MERGE_RUN", clean, None, None, {"socket": True}, _merge_job, clean, takes_job=True))
    except HTTPException as exc:
        return await refuse(str(exc.detail), exc.status_code)
    job_id = job["job_id"]
    jobs.subscribe(job_id, on_job_event)

    async def listen() -> None:             # the client: a cancel request, or the socket closing (which cancels the merge)
        try:
            while True:
                msg = await websocket.receive_json()
                if isinstance(msg, dict) and msg.get("action") == "cancel":
                    await run_in_threadpool(_cancel_quietly, job_id)
        except (WebSocketDisconnect, RuntimeError, ValueError):
            await run_in_threadpool(_cancel_quietly, job_id)
            inbox.put_nowait({"event": "closed"})

    listener = asyncio.create_task(listen())
    try:
        await websocket.send_json({"type": "queued", "job_id": job_id, "position": job.get("position"),
                                   "waiting_for_earlier_job": job.get("waiting_for_earlier_job", False),
                                   "deduplicated": bool(job.get("deduplicated"))})
        started = False
        while True:
            now = await run_in_threadpool(jobs.get, job_id)
            if now is None:
                await websocket.send_json({"type": "error", "error": "Job not found or expired"})
                break
            if now["status"] == "running" and not started:
                started = True
                await websocket.send_json({"type": "started", "job_id": job_id})
            if now["status"] in ("done", "error", "cancelled"):
                while not inbox.empty():                  # progress reported just before the end still goes out first
                    item = inbox.get_nowait()
                    data = item.get("progress") or {}
                    if item.get("event") == "progress" and data.get("type") != "complete":
                        await websocket.send_json({k: v for k, v in data.items() if k != "merged_csv_data"})
                await websocket.send_json(_merge_socket_end(now))
                break
            try:
                item = await asyncio.wait_for(inbox.get(), timeout=1.0)
            except asyncio.TimeoutError:
                continue
            if item.get("event") == "closed":
                break
            if item.get("event") == "progress":
                data = item["progress"]
                if data.get("type") == "complete":       # the final message below carries it
                    continue
                if not started:
                    started = True
                    await websocket.send_json({"type": "started", "job_id": job_id})
                await websocket.send_json({k: v for k, v in data.items() if k != "merged_csv_data"})
    except (WebSocketDisconnect, RuntimeError):
        await run_in_threadpool(_cancel_quietly, job_id)
    finally:
        jobs.unsubscribe(job_id, on_job_event)
        listener.cancel()
        try:
            await websocket.close(code=1000)
        except Exception:
            pass


if config.MERGE_SOCKET_ENABLED:
    public_router.add_api_websocket_route("/ws/merge/{code}", merge_socket)


def _merge_poll_messages(snap: Dict[str, Any], after: int) -> Tuple[List[Dict[str, Any]], int]:
    """The messages of a job after the first `after`, in the order and shape the merge socket sends them, and the count to
    ask from next time. The full sequence is: "queued", "started" (once the job runs), every progress event (without
    `merged_csv_data`; the stream's own "complete" event is left out), then one "complete" or "error". `snap` is
    jobs.events_since(job_id, max(0, after - prefix)); the numbering only grows, so a poll never repeats or misses one."""
    job = snap["job"]
    started = job["started_at"] is not None
    prefix = 1 + (1 if started else 0)
    finished = job["status"] in ("done", "error", "cancelled")
    events_total = snap["total"]                    # progress events reported so far (not the cursor)
    seq: List[Optional[Dict[str, Any]]] = []
    if after < 1:
        seq.append({"type": "queued", "job_id": job["job_id"], "position": job.get("position"),
                    "waiting_for_earlier_job": job.get("waiting_for_earlier_job", False)})
    if started and after < 2:
        seq.append({"type": "started", "job_id": job["job_id"]})
    for data in snap["events"]:
        seq.append(None if data.get("type") == "complete" else {k: v for k, v in data.items() if k != "merged_csv_data"})
    total = prefix + events_total
    if finished:
        if after <= total:
            seq.append(_merge_socket_end(job))
        total += 1
    return [m for m in seq if m is not None], max(total, after)


@router.get("/jobs/{job_id}/events")
def get_job_events(job_id: str = PathParam(..., pattern=r"^[0-9a-f]{32}$"), after: int = Query(0, ge=0)):
    """The merge's progress for clients that poll instead of using the socket: the same messages as the merge socket, in
    the same order. Start a merge with POST /jobs/merge, then call this with `after` = the `next` of the previous answer
    (0 the first time) every second or two, until `done` is true.

    Returns {job_id, status, done, next, messages}. `messages` are the new ones since `after`: `{"type": "queued"}` first,
    `{"type": "started"}`, the progress events (`init`, `progress` with `stage`, `finalize`; each with `percent`, `subtitle`,
    `gemini_calls_done` / `gemini_calls_total`, `eta_sec`, `merge_stats`, ...), and last one `{"type": "complete", ...}`
    (the summary with `merge_stats`) or `{"type": "error", "error", "status_code"}`. 404 when the job is unknown or expired.
    Cancel with DELETE /jobs/{job_id}. Reading needs no X-Username."""
    snap = jobs.events_since(job_id, 0)
    if snap is None:
        raise HTTPException(status_code=404, detail="Job not found or expired")
    prefix = 1 + (1 if snap["job"]["started_at"] is not None else 0)
    snap = jobs.events_since(job_id, max(0, after - prefix)) or snap
    messages, nxt = _merge_poll_messages(snap, after)
    job = snap["job"]
    return {"job_id": job_id, "status": job["status"], "done": job["status"] in ("done", "error", "cancelled"),
            "next": nxt, "messages": messages}


def _cancel_quietly(job_id: str) -> None:
    """Cancels a merge job; a job that already ended (or cannot be stopped) is left alone."""
    try:
        jobs.cancel(job_id)
    except HTTPException:
        pass


@router.get("/jobs")
def list_jobs_endpoint(
    instrument_code: Optional[str] = Query(None, pattern=INSTRUMENT_CODE_PATTERN),
    status: Optional[Literal["queued", "running", "done", "error", "cancelled"]] = Query(None),
    kind: Optional[Literal["ocr", "ocr-client-pages", "extract", "merge"]] = Query(None),
):
    """The job queue: {queues: {ocr|extract|merge: {queued, running}}, limits, jobs: [...]} with the jobs in the order
    they were sent (without "result"; GET /jobs/{job_id} has it). Jobs live in memory: a restart empties the list."""
    return {
        "queues": jobs.counts(),
        "limits": {"rate_limit_rpm": config.RATE_LIMIT_RPM, "workers": config.JOB_WORKERS,
                   "queue_max": config.JOB_QUEUE_MAX},
        "jobs": jobs.list_jobs(instrument_code, status, kind),
    }


@router.delete("/jobs/{job_id}")
def cancel_job(user: Username, job_id: str = PathParam(..., pattern=r"^[0-9a-f]{32}$")):
    """Cancels a job: a queued one at once (its event becomes CANCELLED), a running merge before its next Gemini batch
    (status stays "running" with cancel_requested until it stops). 409 for a running page job (one Gemini call) or a
    finished job. Needs the X-Username header; recorded as JOB_CANCEL."""
    job = jobs.get(job_id)
    if job is None:
        raise HTTPException(status_code=404, detail="Job not found or expired")
    with events.record(user, "JOB_CANCEL", job["instrument_code"], job["fiscal_year"], target_type="job",
                       target_id=job_id, details={"kind": job["kind"], "page_number": job["page_number"],
                                                  "status_before": job["status"]}):
        out = jobs.cancel(job_id)
    if out is None:
        raise HTTPException(status_code=404, detail="Job not found or expired")
    return out


@router.get("/events")
def list_events(
    username: Optional[str] = Query(None, max_length=64),
    instrument_code: Optional[str] = Query(None, pattern=INSTRUMENT_CODE_PATTERN),
    action: Optional[str] = Query(None, pattern=r"^[A-Z_]{3,40}$"),
    status: Optional[Literal["STARTED", "SUCCEEDED", "FAILED", "CANCELLED"]] = Query(None),
    since: Optional[str] = Query(None, pattern=r"^\d{4}-\d{2}-\d{2}(T[\d:.]+Z?)?$"),
    until: Optional[str] = Query(None, pattern=r"^\d{4}-\d{2}-\d{2}(T[\d:.]+Z?)?$"),
    limit: int = Query(100, ge=1, le=500),
    offset: int = Query(0, ge=0),
):
    """The event log (who did what), newest first: {total, events: [{id, created_at, finished_at, username, action, status,
    instrument_code, fiscal_year, target_type, target_id, job_id, source, details}]}. since / until are ISO dates or
    timestamps (an until date includes that whole day). Reading needs no X-Username."""
    return database.list_events(username, instrument_code, action, status, since, until, limit, offset)


@router.get("/jobs/{job_id}")
def get_job(job_id: str = PathParam(..., pattern=r"^[0-9a-f]{32}$")):
    """One job: status queued | running | done (result set) | error (error, status_code set) | cancelled, with
    kind, stage, instrument_code, fiscal_year, page_number, position (1-based place in its step's queue while queued),
    waiting_for_earlier_job, progress (merge), cancel_requested and the created / started / finished times."""
    job = jobs.get(job_id)
    if job is None:
        raise HTTPException(status_code=404, detail="Job not found or expired")
    return job


@router.get("/files/{file_path:path}")
def get_storage_file(file_path: str):
    """Serves files from app/ocr/storage (page images, uploads, OCR markdown); replaces the old /storage mount."""
    root = config.STORAGE_DIR.resolve()
    target = (root / file_path).resolve()
    if not target.is_relative_to(root) or not target.is_file():
        raise HTTPException(status_code=404, detail="File not found")
    return FileResponse(str(target))
