"""Read-only controlled laboratory candidate review projection.

This module deliberately does not write or medically validate data.  It projects
existing document reconciliation and canonical verified-laboratory contracts into
a bounded review model.  Confirmation remains an Action-Queue concern.
"""
from __future__ import annotations

import hashlib
import json
import re
import sqlite3
from collections import Counter, defaultdict
from datetime import date
from typing import Any, Callable

from dashboard_v5.lab_registry import LAB_ALLOWLIST
from dashboard_v5.metric_catalog_v2 import LAB_CATALOG_SPECS

CONTRACT_VERSION = "health.lab-review.v1"
MAX_CANDIDATES = 500
REVIEW_STATES = frozenset(
    {
        "extracted",
        "suggestion_available",
        "needs_review",
        "conflict",
        "confirmed",
        "deferred",
        "possible_duplicate",
        "rejected",
        "incomplete",
    }
)
QUALITATIVE = frozenset(
    {
        "positiv",
        "negativ",
        "reaktiv",
        "nicht reaktiv",
        "nachweisbar",
        "nicht nachweisbar",
    }
)
UNREADABLE = frozenset({"nicht lesbar", "unleserlich", "unklar", "nicht erkennbar"})
NUMERIC = re.compile(r"^\s*([<>≤≥]?)[ ]*([+-]?\d+(?:[.,]\d+)?)\s*$")


def _normal(value: Any) -> str:
    return " ".join(str(value or "").strip().casefold().replace("_", " ").split())


def _slug(value: Any) -> str:
    normalized = (
        str(value or "")
        .casefold()
        .replace("µ", "u")
        .replace("μ", "u")
        .replace("%", " percent ")
    )
    return re.sub(r"[^a-z0-9]+", "_", normalized).strip("_")


def _catalog() -> tuple[dict[tuple[str, str], tuple[str, str]], list[dict[str, str]]]:
    by_key: dict[tuple[str, str], tuple[str, str]] = {}
    alternatives: dict[str, dict[str, str]] = {}
    for (raw_parameter, raw_unit), (parameter, unit) in LAB_ALLOWLIST.items():
        metric_id = LAB_CATALOG_SPECS[parameter].metric_id
        by_key[(raw_parameter, raw_unit)] = (metric_id, parameter)
        alternatives[metric_id] = {"metric_id": metric_id, "parameter": parameter, "unit": unit}
    return by_key, sorted(alternatives.values(), key=lambda item: item["parameter"].casefold())


def candidate_catalog_match(value_text: object, unit: object, normalized_parameter: object = None, normalized_unit: object = None) -> tuple[str | None, str | None]:
    by_key, _ = _catalog()
    raw_label, _raw_value = _split_value(value_text)
    match = by_key.get((_slug(normalized_parameter), _slug(normalized_unit or unit))) if normalized_parameter else None
    match = match or by_key.get((_slug(raw_label), _slug(unit)))
    if not match and not str(unit or "").strip():
        parameter_key = _slug(normalized_parameter or raw_label)
        candidates = {value for (raw_parameter, _raw_unit), value in by_key.items() if raw_parameter == parameter_key}
        if len(candidates) == 1:
            match = next(iter(candidates))
    return match if match else (None, None)


def _group(_parameter: str, _metric_id: str | None = None) -> str:
    # The current catalog has no reviewed thematic-group field. Keep all
    # parameters visible and unclassified instead of inferring medical groups.
    return "Weitere / nicht klassifiziert"


def _split_value(raw: Any) -> tuple[str, str]:
    text = " ".join(str(raw or "").split())
    if ":" not in text:
        return text[:120], ""
    label, value = text.split(":", 1)
    return label.strip()[:120], value.strip()[:120]


def _value_contract(raw: str) -> dict[str, str | None]:
    compact = " ".join(raw.split())
    match = NUMERIC.fullmatch(compact)
    if match:
        operator = match.group(1) or "="
        normalized = match.group(2).replace(",", ".")
        return {
            "raw": compact,
            "kind": "numeric",
            "operator": operator,
            "normalized": normalized,
        }
    lowered = compact.casefold()
    if lowered in QUALITATIVE:
        return {"raw": compact, "kind": "qualitative", "operator": None, "normalized": None}
    if not compact or lowered in UNREADABLE:
        return {"raw": compact, "kind": "unreadable", "operator": None, "normalized": None}
    return {"raw": compact, "kind": "unrecognized", "operator": None, "normalized": None}


def _review_state(row: sqlite3.Row, value: dict[str, str | None], proposed: str | None, observation_date: str | None) -> str:
    candidate_status = _normal(row["candidate_status"])
    match_status = _normal(row["match_status"])
    staging_status = _normal(row["staging_status"])
    latest_decision = _normal(row["latest_decision"])
    if str(row["latest_corrected_value"] or "") == "__incomplete__":
        return "incomplete"
    if staging_status == "transferred":
        return "confirmed"
    if staging_status in {"reviewed pending preview", "ready for transfer"}:
        return "needs_review"
    if candidate_status in {"rejected", "dismissed"}:
        return "rejected"
    if latest_decision == "defer" or candidate_status in {"deferred", "zuruckgestellt", "zurückgestellt"}:
        return "deferred"
    if candidate_status == "already present" or match_status in {"exact match", "format variation", "repeated candidate"}:
        return "possible_duplicate"
    if candidate_status == "conflicting" or match_status in {"value conflict", "ambiguous"}:
        if value["kind"] in {"unreadable", "unrecognized"} or (value["kind"] == "numeric" and not row["unit"]) or not observation_date:
            return "incomplete"
        return "conflict"
    if value["kind"] in {"unreadable", "unrecognized"} or (value["kind"] == "numeric" and not row["unit"]) or not observation_date:
        return "incomplete"
    if proposed and match_status == "not present":
        return "suggestion_available"
    if proposed:
        return "needs_review"
    return "extracted"


def _date_or_none(value: Any) -> str | None:
    text = str(value or "")
    try:
        return date.fromisoformat(text[:10]).isoformat()
    except ValueError:
        return None


def _reference(value: Any) -> str | None:
    text = " ".join(str(value or "").split())
    return text[:120] if text else None


def public_engine_label(value: Any) -> str:
    key = _normal(value)
    if "ocr" in key:
        return "OCR-Extraktion"
    if any(token in key for token in ("xlsx", "excel", "spreadsheet", "table")):
        return "Tabellenimport"
    if "text" in key or "pdf" in key:
        return "Dokumentextraktion"
    return "Automatische Extraktion"


_ISO_DATE = re.compile(r"\b(20\d{2}-\d{2}-\d{2})\b")
_LOCAL_DATE = re.compile(r"\b([0-3]?\d)[.\-/]([01]?\d)[.\-/](20\d{2})\b")
_OBSERVATION_DATE_LABEL = re.compile(r"(?:befund|untersuchung|abnahme)(?:sdatum|datum|stag)?", re.I)


def explicit_candidate_date(value_text: object, context_text: object) -> str | None:
    """Return an explicitly candidate-bound date; never use document date."""
    value = str(value_text or "")
    context = str(context_text or "")
    snippets = [value]
    for match in _OBSERVATION_DATE_LABEL.finditer(context):
        snippets.append(context[match.start():match.end() + 48])
    for snippet in snippets:
        match = _ISO_DATE.search(snippet)
        if match and _date_or_none(match.group(1)):
            return match.group(1)
        local = _LOCAL_DATE.search(snippet)
        if local:
            candidate = f"{local.group(3)}-{int(local.group(2)):02d}-{int(local.group(1)):02d}"
            if _date_or_none(candidate):
                return candidate
    return None


def preview_revision_digest(*, candidate_id: str, candidate_revision: int, comparison_digest: str, target_parameter: str | None, metric_id: str | None, raw_value: str | None, unit: str | None, observation_date: str | None, reference_range: str | None, source_original_revision: str | None, source_page_revision: str | None) -> str:
    material = {
        "candidate_id": candidate_id,
        "candidate_revision": candidate_revision,
        "comparison_digest": comparison_digest,
        "target_parameter": target_parameter,
        "metric_id": metric_id,
        "value": raw_value,
        "unit": unit,
        "observation_date": observation_date,
        "reference_range": reference_range,
        "source_original_revision": source_original_revision,
        "source_page_revision": source_page_revision,
    }
    return hashlib.sha256(json.dumps(material, ensure_ascii=False, sort_keys=True, separators=(",", ":")).encode()).hexdigest()


def _coverage(verified_rows: list[dict[str, Any]], candidates: list[dict[str, Any]]) -> dict[str, Any]:
    by_parameter: dict[str, list[dict[str, Any]]] = defaultdict(list)
    sources: set[str] = set()
    days: list[str] = []
    groups: Counter[str] = Counter()
    for row in verified_rows:
        parameter = str(row.get("parameter") or "Nicht klassifiziert")
        by_parameter[parameter].append(row)
        source = str((row.get("document") or {}).get("id") or row.get("document_link_status") or "")
        if source:
            sources.add(source)
        day = _date_or_none(row.get("date"))
        if day:
            days.append(day)
    for parameter in by_parameter:
        groups[_group(parameter)] += 1
    open_count = sum(
        item["review_status"] not in {"confirmed", "rejected", "possible_duplicate"}
        for item in candidates
    )
    return {
        "title": "Bestätigte Laborabdeckung",
        "confirmed_parameters": len(by_parameter),
        "confirmed_values": len(verified_rows),
        "earliest_date": min(days) if days else None,
        "latest_date": max(days) if days else None,
        "source_count": len(sources),
        "parameters_multiple_dates": sum(len(items) > 1 for items in by_parameter.values()),
        "parameters_single_date": sum(len(items) == 1 for items in by_parameter.values()),
        "unclassified_parameters": groups.get("Weitere / nicht klassifiziert", 0),
        "open_candidates": open_count,
        "groups": dict(sorted(groups.items())),
        "medical_completeness_claim": False,
    }


def build_lab_review(
    connection: sqlite3.Connection,
    params: dict[str, str],
    *,
    verified_rows: list[dict[str, Any]],
    document_id_factory: Callable[[Any, str | None, str, str], str],
) -> dict[str, Any]:
    """Build a bounded projection from existing tables; never mutate the connection."""
    _by_catalog_key, alternatives = _catalog()
    required = {"document_candidates", "document_candidate_matches", "dokumente"}
    present = {
        str(row[0])
        for row in connection.execute(
            "SELECT name FROM sqlite_master WHERE type='table' AND name IN (?,?,?)",
            tuple(sorted(required)),
        )
    }
    raw_rows: list[sqlite3.Row] = []
    if present == required:
        raw_rows = list(
            connection.execute(
                """SELECT c.id,c.document_id,c.value_text,c.unit,c.page_number,c.section_number,
                          c.context_text,c.engine,c.confidence,c.status AS candidate_status,
                          c.created_at,c.candidate_revision,m.match_status,m.normalized_parameter,
                          m.normalized_date AS observation_date,m.normalized_value,m.normalized_unit,
                          m.reference_text,m.database_match_count,m.workbook_match_count,
                          m.comparison_digest,d.document_date,d.institution,d.kategorie,
                          p.intake_id,p.original_reviewed_at,s.status AS staging_status,s.operation AS staging_operation,
                          s.old_value_json,s.new_value_json,
                          (SELECT pg.reviewed_at FROM document_pages pg
                            WHERE pg.document_id=c.document_id AND pg.text_version=c.source_text_version
                              AND pg.page_number=c.page_number AND pg.reviewed_at IS NOT NULL LIMIT 1) AS source_page_reviewed_at,
                          (SELECT e.decision FROM document_candidate_review_events e
                            WHERE e.candidate_id=c.id ORDER BY e.processed_at DESC,e.action_id DESC LIMIT 1) AS latest_decision,
                          (SELECT e.corrected_value FROM document_candidate_review_events e
                            WHERE e.candidate_id=c.id ORDER BY e.processed_at DESC,e.action_id DESC LIMIT 1) AS latest_corrected_value
                     FROM document_candidates c
                     JOIN document_candidate_matches m ON m.candidate_id=c.id
                     JOIN dokumente d ON d.id=c.document_id
                     JOIN document_processing p ON p.document_id=c.document_id
                     LEFT JOIN document_transfer_staging s ON s.candidate_id=c.id
                    WHERE c.candidate_type='laboratory_value'
                    ORDER BY COALESCE(m.normalized_date,d.document_date) DESC,c.id
                    LIMIT ?""",
                (MAX_CANDIDATES + 1,),
            )
        )
    if len(raw_rows) > MAX_CANDIDATES:
        raise ValueError("labor_candidate_limit_exceeded")
    candidates: list[dict[str, Any]] = []
    for row in raw_rows:
        raw_label, raw_value = _split_value(row["value_text"])
        unit = " ".join(str(row["unit"] or row["normalized_unit"] or "").split()) or None
        metric_id, proposed = candidate_catalog_match(
            row["value_text"], unit, row["normalized_parameter"], row["normalized_unit"]
        )
        value = _value_contract(raw_value)
        observation_date = explicit_candidate_date(row["value_text"], row["context_text"])
        state = _review_state(row, value, proposed, observation_date)
        source_id = document_id_factory(
            row["document_id"],
            _date_or_none(row["document_date"]),
            str(row["kategorie"] or "other"),
            str(row["institution"] or ""),
        )
        reference = _reference(row["reference_text"])
        revision = preview_revision_digest(
            candidate_id=str(row["id"]), candidate_revision=int(row["candidate_revision"] or 1),
            comparison_digest=str(row["comparison_digest"] or ""), target_parameter=proposed,
            metric_id=metric_id, raw_value=raw_value, unit=unit,
            observation_date=observation_date, reference_range=reference,
            source_original_revision=str(row["original_reviewed_at"] or "") or None,
            source_page_revision=str(row["source_page_reviewed_at"] or "") or None,
        )
        item = {
            "id": str(row["id"]),
            "revision": revision,
            "revision_number": int(row["candidate_revision"] or 1),
            "raw_label": raw_label,
            "proposed_parameter": proposed,
            "metric_id": metric_id,
            "value": value,
            "unit": unit,
            "observation_date": observation_date,
            "reference_range": reference,
            "source": {
                "document_id": source_id,
                "review_id": str(row["intake_id"]),
                "label": "Quelldokument" + (f" vom {_date_or_none(row['document_date'])}" if _date_or_none(row["document_date"]) else ""),
                "review_ready": bool(row["original_reviewed_at"] and row["source_page_reviewed_at"]),
            },
            "match_confidence": round(float(row["confidence"]), 3) if row["confidence"] is not None else None,
            "match_status": str(row["match_status"] or "not_compared"),
            "review_status": state,
            "details": {
                "excerpt": " ".join(str(row["context_text"] or "").split())[:500],
                "page": int(row["page_number"] or 0) or None,
                "section": int(row["section_number"] or 0) or None,
                "imported_at": str(row["created_at"] or "")[:25] or None,
                "provenance": {"engine": public_engine_label(row["engine"])},
                "alternative_matches": alternatives,
            },
            "preview": {
                "target_parameter": proposed,
                "metric_id": metric_id,
                "value": raw_value,
                "unit": unit,
                "observation_date": observation_date,
                "reference_range": reference,
                "source": "Quelldokument",
                "existing_comparisons": int(row["database_match_count"] or 0),
                "target_count": 1,
                "candidate_revision": int(row["candidate_revision"] or 1),
                "revision": revision,
            },
        }
        if row["staging_status"]:
            try:
                raw_old = json.loads(str(row["old_value_json"] or "{}"))
                raw_new = json.loads(str(row["new_value_json"] or "{}"))
            except json.JSONDecodeError:
                raw_old, raw_new = {}, {}
            staged_old = {key: raw_old.get(key) for key in ("database_matches", "workbook_matches", "existing_value") if key in raw_old}
            staged_new = {key: raw_new.get(key) for key in ("parameter", "value", "unit", "date", "reference") if key in raw_new}
            item["transfer_preview"] = {
                "status": str(row["staging_status"]),
                "operation": str(row["staging_operation"] or "create"),
                "target": "laboratory",
                "old": staged_old,
                "new": staged_new,
                "source": {"page": int(row["page_number"] or 0) or None},
            }
        candidates.append(item)
    all_candidates = candidates
    requested_status = params.get("status", "open") or "open"
    if requested_status != "all":
        if requested_status == "open":
            candidates = [item for item in candidates if item["review_status"] not in {"confirmed", "rejected", "possible_duplicate"}]
        else:
            candidates = [item for item in candidates if item["review_status"] == requested_status]
    query = _normal(params.get("q"))
    if query:
        candidates = [item for item in candidates if query in _normal(item["raw_label"]) or query in _normal(item["proposed_parameter"])]
    start = _date_or_none(params.get("from")) if params.get("from") else None
    end = _date_or_none(params.get("to")) if params.get("to") else None
    if start:
        candidates = [item for item in candidates if item["observation_date"] and item["observation_date"] >= start]
    if end:
        candidates = [item for item in candidates if item["observation_date"] and item["observation_date"] <= end]
    source = params.get("source")
    if source:
        candidates = [item for item in candidates if item["source"]["document_id"] == source]
    counts = Counter(item["review_status"] for item in all_candidates)
    return {
        "version": 1,
        "contract_version": CONTRACT_VERSION,
        "candidates": candidates,
        "candidate_count": len(candidates),
        "summary": {
            "open": sum(item["review_status"] not in {"confirmed", "rejected", "possible_duplicate"} for item in all_candidates),
            "conflicts": counts["conflict"],
            "possible_duplicates": counts["possible_duplicate"],
            "incomplete": counts["incomplete"],
            "confirmed_values": len(verified_rows),
        },
        "coverage": _coverage(verified_rows, all_candidates),
        "catalog": alternatives,
        "sources": sorted(
            {
                (item["source"]["document_id"], item["source"]["label"])
                for item in all_candidates
            }
        ),
        "review_states": sorted(REVIEW_STATES),
        "medical_statement": "Kontrollierte Zuordnung dokumentierter Werte; keine automatische medizinische Verifizierung oder Interpretation.",
    }