#!/usr/bin/env python3
"""Fail-closed reconciliation of the private laboratory matrix XLSX."""
from __future__ import annotations

import argparse
import hashlib
import html
import json
import math
import re
import sqlite3
import unicodedata
from collections import Counter
from datetime import date, datetime
from pathlib import Path
from typing import Any

from openpyxl import load_workbook

RATIO = re.compile(r"^\s*\d+\s*/\s*\d+\s*$")
NUMBER = re.compile(r"^[+-]?\d+(?:[.,]\d+)?$")
REF_RANGE = re.compile(r"^\s*(?:(?P<op>[<>])\s*)?(?P<a>[+-]?\d+(?:[.,]\d+)?)(?:\s*[-–]\s*(?P<b>[+-]?\d+(?:[.,]\d+)?))?\s*(?P<unit>.+?)?\s*$")
CONVERSIONS = {("g/l", "mg/l"): 1000.0, ("mg/l", "g/l"): 0.001, ("mg/dl", "g/l"): 0.01, ("g/l", "mg/dl"): 100.0}
CATEGORIES = (
    "exact_match", "safe_unit_conversion_match", "db_present_not_canonical", "only_in_xlsx",
    "only_in_document_or_ocr", "value_conflict", "reference_conflict", "unclear_unit_or_format",
    "missing_original_document",
)


def sha256(path: Path) -> str:
    return hashlib.sha256(path.read_bytes()).hexdigest()


def normalize_text(value: object) -> str:
    text = unicodedata.normalize("NFKD", str(value or "")).encode("ascii", "ignore").decode().casefold()
    return " ".join(re.sub(r"[^a-z0-9]+", " ", text).split())


def normalize_unit(value: object) -> str:
    text = str(value or "").strip().casefold().replace("μ", "µ").replace("/µl", "/ul")
    return re.sub(r"\s+", "", text)


def parse_reference(value: object) -> dict[str, Any]:
    raw = str(value or "").strip()
    match = REF_RANGE.match(raw)
    if not match:
        return {"raw": raw, "min": None, "max": None, "unit": "", "status": "not_documented" if not raw else "ambiguous"}
    a = float(match.group("a").replace(",", "."))
    b = float(match.group("b").replace(",", ".")) if match.group("b") else None
    op = match.group("op")
    minimum = a if b is not None or op == ">" else None
    maximum = b if b is not None else a if op == "<" else None
    return {"raw": raw, "min": minimum, "max": maximum, "unit": str(match.group("unit") or "").strip(), "status": "parsed"}


def number(value: object) -> float | None:
    if type(value) in {int, float} and math.isfinite(float(value)):
        return float(value)
    raw = str(value or "").strip()
    if not NUMBER.fullmatch(raw):
        return None
    return float(raw.replace(",", "."))


def same(a: object, b: object, tolerance: float = 1e-9) -> bool:
    try:
        return abs(float(a) - float(b)) <= tolerance
    except (TypeError, ValueError):
        return False


def db_rows(connection: sqlite3.Connection) -> list[dict[str, Any]]:
    rows = connection.execute(
        """SELECT id,parameter_name,wert,COALESCE(einheit,einheiten) AS unit,
                  reference_min,reference_max,COALESCE(abnahme_datum,befund_datum,ermittlung_datum) AS observed_date,
                  validierungsstatus,verified_against_original,canonical_document_id,dokument_id
           FROM laborwerte ORDER BY observed_date,id"""
    ).fetchall()
    return [dict(row) for row in rows]


def reconcile(database: Path, workbook_path: Path, json_report: Path, html_report: Path) -> dict[str, int]:
    workbook = load_workbook(workbook_path, read_only=False, data_only=True)
    if len(workbook.sheetnames) != 1:
        raise RuntimeError("unexpected laboratory workbook shape")
    sheet = workbook[workbook.sheetnames[0]]
    dates: dict[int, str | None] = {}
    for column in range(3, sheet.max_column + 1):
        raw = sheet.cell(1, column).value
        if isinstance(raw, datetime):
            dates[column] = raw.date().isoformat()
        elif isinstance(raw, date):
            dates[column] = raw.isoformat()
        else:
            dates[column] = None
    connection = sqlite3.connect(f"file:{database.resolve()}?mode=ro", uri=True)
    connection.row_factory = sqlite3.Row
    database_rows = db_rows(connection)
    connection.close()
    by_identity: dict[tuple[str, str], list[dict[str, Any]]] = {}
    for row in database_rows:
        day = str(row["observed_date"] or "")[:10]
        by_identity.setdefault((normalize_text(row["parameter_name"]), day), []).append(row)
    matched_ids: set[int] = set()
    review: list[dict[str, Any]] = []
    counts: Counter[str] = Counter()
    for row_number in range(2, sheet.max_row + 1):
        parameter = str(sheet.cell(row_number, 1).value or "").strip()
        reference = parse_reference(sheet.cell(row_number, 2).value)
        if not parameter:
            continue
        for column, observed_date in dates.items():
            cell = sheet.cell(row_number, column)
            raw = cell.value
            if raw in (None, ""):
                continue
            value = number(raw)
            identity = (normalize_text(parameter), observed_date or "")
            candidates = by_identity.get(identity, []) if observed_date else []
            raw_text = str(raw).strip()
            unit = normalize_unit(reference["unit"])
            category = "unclear_unit_or_format"
            reason = "format_or_date_ambiguous"
            selected: dict[str, Any] | None = None
            if observed_date and value is not None and not RATIO.fullmatch(raw_text) and unit:
                exact = [item for item in candidates if normalize_unit(item["unit"]) == unit and same(item["wert"], value)]
                converted = []
                for item in candidates:
                    factor = CONVERSIONS.get((unit, normalize_unit(item["unit"])))
                    if factor is not None and same(value * factor, item["wert"], 1e-6):
                        converted.append(item)
                if exact:
                    selected = exact[0]
                    matched_ids.add(int(selected["id"]))
                    canonical = bool(selected["verified_against_original"]) and selected["canonical_document_id"] is not None
                    ref_equal = same(reference["min"], selected["reference_min"]) and same(reference["max"], selected["reference_max"])
                    if not ref_equal and any(value is not None for value in (reference["min"], reference["max"], selected["reference_min"], selected["reference_max"])):
                        category, reason = "reference_conflict", "observation_specific_reference_differs"
                    elif bool(selected["verified_against_original"]) and selected["canonical_document_id"] is None:
                        category, reason = "missing_original_document", "verified_row_has_no_canonical_source_document"
                    elif not canonical:
                        category, reason = "db_present_not_canonical", "existing_row_not_verified_against_original"
                    else:
                        category, reason = "exact_match", "date_parameter_value_unit_and_reference_match"
                elif converted:
                    selected = converted[0]
                    matched_ids.add(int(selected["id"]))
                    category, reason = "safe_unit_conversion_match", "allowlisted_linear_unit_conversion"
                elif candidates:
                    category, reason = "value_conflict", "same_date_and_parameter_but_value_or_unit_differs"
                elif float(value).is_integer() and abs(value) >= 100:
                    category, reason = "unclear_unit_or_format", "large_integer_without_primary_source_confirmation"
                else:
                    category, reason = "only_in_xlsx", "no_database_candidate"
            counts[category] += 1
            review.append({
                "category": category, "reason": reason, "sheet": sheet.title, "cell": cell.coordinate,
                "date": observed_date, "parameter": parameter, "xlsx_value": raw_text,
                "xlsx_unit": reference["unit"], "xlsx_reference": reference["raw"],
                "database_candidate_id": int(selected["id"]) if selected else None,
                "canonical_action": "none",
            })
    for row in database_rows:
        row_id = int(row["id"])
        if row_id in matched_ids:
            continue
        category = "missing_original_document" if row["dokument_id"] is None and row["canonical_document_id"] is None else "only_in_document_or_ocr"
        counts[category] += 1
        review.append({"category": category, "reason": "database_or_document_observation_not_matched_in_xlsx", "database_candidate_id": row_id, "canonical_action": "none"})
    for category in CATEGORIES:
        counts.setdefault(category, 0)
    report = {
        "status": "completed", "source_filename": workbook_path.name, "source_sha256": sha256(workbook_path),
        "rules": ["reconciliation source only", "no database values overwritten", "ambiguous values fail closed", "canonical release requires original document agreement"],
        "counts": dict(counts), "review_items": review,
    }
    json_report.parent.mkdir(parents=True, exist_ok=True, mode=0o700)
    json_report.write_text(json.dumps(report, ensure_ascii=False, indent=2) + "\n")
    json_report.chmod(0o600)
    rows_html = []
    for item in review:
        cells = [item.get(key, "") for key in ("category", "reason", "cell", "date", "parameter", "xlsx_value", "xlsx_unit", "xlsx_reference", "database_candidate_id")]
        rows_html.append("<tr>" + "".join(f"<td>{html.escape(str(value if value is not None else ''))}</td>" for value in cells) + "</tr>")
    html_report.write_text(
        "<!doctype html><meta charset='utf-8'><title>Privater Labor-Reconciliation-Review</title>"
        "<h1>Privater Labor-Reconciliation-Review</h1><p>Keine Werte wurden überschrieben oder kanonisch freigegeben.</p>"
        "<table><thead><tr><th>Kategorie</th><th>Grund</th><th>Zelle</th><th>Datum</th><th>Parameter</th><th>XLSX-Wert</th><th>Einheit</th><th>Referenz</th><th>DB-ID</th></tr></thead><tbody>"
        + "".join(rows_html) + "</tbody></table>"
    )
    html_report.chmod(0o600)
    return dict(counts)


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("--db", required=True, type=Path)
    parser.add_argument("--xlsx", required=True, type=Path)
    parser.add_argument("--json-report", required=True, type=Path)
    parser.add_argument("--html-report", required=True, type=Path)
    args = parser.parse_args()
    counts = reconcile(args.db, args.xlsx, args.json_report, args.html_report)
    print(json.dumps(counts, sort_keys=True))
    return 0


if __name__ == "__main__":
    raise SystemExit(main())