"""Sanitized read-only source and runtime status contract for Dashboard v5."""
from __future__ import annotations

import hashlib
import html
import json
import re
import sqlite3
from datetime import datetime, timedelta, timezone
from pathlib import Path
from typing import Any
from zoneinfo import ZoneInfo

from dashboard_v5.nutrition_mapping_review import build_review as build_mapping_review

CONTRACT_VERSION = "health.source_status.v1"
LOCAL_TZ = ZoneInfo("Europe/Zurich")
AVAILABILITY = frozenset({"available", "unavailable", "unknown"})
FRESHNESS = frozenset({"current", "delayed", "unknown", "not_expected", "not_configured"})
IMPORT = frozenset({"success", "failed", "unknown", "never_run"})
REVIEW = frozenset({"none", "review_open", "conflict"})
REGENERATION = frozenset({"current", "pending", "failed", "unknown"})
COMMIT_RE = re.compile(r"^[0-9a-f]{40}$")
PRIVATE_TEXT = re.compile(r"(?:/home/|/tmp/|\.hermes|file:|https?://|\\|\.pdf\b|\.json\b|\.db\b)", re.I)


def _scalar(connection: sqlite3.Connection, sql: str, default: Any = None) -> Any:
    try:
        row = connection.execute(sql).fetchone()
    except sqlite3.Error:
        return default
    return default if row is None or row[0] is None else row[0]


def _table(connection: sqlite3.Connection, name: str) -> bool:
    return bool(_scalar(connection, "SELECT 1 FROM sqlite_master WHERE type='table' AND name=" + repr(name), False))


def _iso(value: Any) -> str | None:
    if value is None:
        return None
    text = str(value).strip()
    if not text:
        return None
    candidate = text.replace("Z", "+00:00")
    try:
        parsed = datetime.fromisoformat(candidate)
    except ValueError:
        return None
    if parsed.tzinfo is None:
        parsed = parsed.replace(tzinfo=LOCAL_TZ)
    return parsed.astimezone(timezone.utc).isoformat().replace("+00:00", "Z")


def _day(value: Any) -> str | None:
    if value is None:
        return None
    text = str(value).strip()
    if re.fullmatch(r"\d{4}-\d{2}-\d{2}", text):
        return text
    parsed = _dt(text)
    return parsed.astimezone(LOCAL_TZ).date().isoformat() if parsed else None


def _dt(value: Any) -> datetime | None:
    stamp = _iso(value)
    return datetime.fromisoformat(stamp.replace("Z", "+00:00")) if stamp else None


def classify_freshness(*, configured: bool, cadence_kind: str, last_success: Any, now: datetime, max_delay_days: int | None = None) -> str:
    if not configured:
        return "not_configured"
    if cadence_kind == "event_based":
        return "not_expected"
    if cadence_kind != "daily" or max_delay_days is None:
        return "unknown"
    success = _dt(last_success)
    if success is None:
        return "unknown"
    return "delayed" if now.astimezone(timezone.utc) - success > timedelta(days=max_delay_days) else "current"


def derive_regeneration(last_change: Any, generated_at: Any, *, artifact_available: bool) -> str:
    if not artifact_available:
        return "failed"
    change, build = _dt(last_change), _dt(generated_at)
    if change is None or build is None:
        return "unknown"
    return "pending" if change > build else "current"


def _latest_import_state(last_attempt: Any, last_success: Any, last_failure: Any) -> str:
    attempt, success, failure = _dt(last_attempt), _dt(last_success), _dt(last_failure)
    if attempt is None and success is None and failure is None:
        return "never_run"
    if failure is not None and (success is None or failure > success):
        return "failed"
    if success is not None:
        return "success"
    return "unknown"


def _artifact_metadata(path: Path | None) -> dict[str, Any]:
    result = {"available": False, "generated_at": None, "build_id": None, "commit": None, "sha256": None}
    if path is None:
        return result
    try:
        raw = path.read_bytes()
    except OSError:
        return result
    if len(raw) > 16 * 1024 * 1024:
        return result
    result["available"] = True
    result["sha256"] = hashlib.sha256(raw).hexdigest()
    match = re.search(rb"<script type=['\"]application/json['\"] id=['\"]health-dashboard-bundle['\"]>(.*?)</script>", raw, re.S)
    if not match:
        return result
    try:
        bundle = json.loads(html.unescape(match.group(1).decode("utf-8")))
    except (UnicodeError, json.JSONDecodeError):
        return result
    result["generated_at"] = _iso(bundle.get("generated_at"))
    commit_match = re.search(rb"<meta name=['\"]health-runtime-commit['\"] content=['\"]([0-9a-f]{40})['\"]>", raw)
    build_match = re.search(rb"<meta name=['\"]health-runtime-build-id['\"] content=['\"](v5-[0-9a-f]{12})['\"]>", raw)
    commit = commit_match.group(1).decode("ascii") if commit_match else None
    build_id = build_match.group(1).decode("ascii") if build_match else None
    result["commit"] = commit if commit and COMMIT_RE.fullmatch(commit) else None
    result["build_id"] = build_id if build_id and re.fullmatch(r"v5-[0-9a-f]{12}", build_id) else None
    return result


def _cadence(kind: str, label: str, max_delay_days: int | None = None) -> dict[str, Any]:
    return {"kind": kind, "label": label, "max_delay_days": max_delay_days}


def _source(*, key: str, name: str, icon: str, configured: bool, actual_day: Any, attempt: Any, success: Any,
            failure: Any, cadence: dict[str, Any], availability: str, review: str = "none", review_count: int = 0,
            last_change: Any = None, artifact: dict[str, Any], explanation: str, action: dict[str, str] | None = None,
            import_state: str | None = None) -> dict[str, Any]:
    freshness = classify_freshness(configured=configured, cadence_kind=cadence["kind"], last_success=success,
                                   now=artifact["now"], max_delay_days=cadence.get("max_delay_days"))
    regeneration = derive_regeneration(last_change, artifact.get("generated_at"), artifact_available=bool(artifact.get("available"))) if configured else "unknown"
    item = {
        "source_key": key, "display_name": name, "icon": icon, "enabled": configured, "configured": configured,
        "actual_data_through": _day(actual_day), "last_import_attempt_at": _iso(attempt),
        "last_import_success_at": _iso(success), "last_import_failure_at": _iso(failure),
        "expected_cadence": cadence, "availability": availability, "freshness": freshness,
        "import_state": import_state or _latest_import_state(attempt, success, failure),
        "review_state": review, "open_review_count": max(0, int(review_count or 0)),
        "regeneration_state": regeneration, "explanation": explanation,
        "user_action_required": action is not None, "action": action,
        "last_relevant_data_change_at": _iso(last_change), "calculated_at": artifact["calculated_at"],
    }
    return item


def build_source_status(connection: sqlite3.Connection, *, artifact_path: Path | None = None,
                        now: datetime | None = None) -> dict[str, Any]:
    now = now or datetime.now(LOCAL_TZ)
    if now.tzinfo is None:
        now = now.replace(tzinfo=LOCAL_TZ)
    calculated_at = now.astimezone(timezone.utc).isoformat().replace("+00:00", "Z")
    artifact = _artifact_metadata(artifact_path)
    artifact.update({"now": now, "calculated_at": calculated_at})

    apple_records = int(_scalar(connection, "SELECT COUNT(*) FROM apple_health_records", 0)) if _table(connection, "apple_health_records") else 0
    apple_day = _scalar(connection, "SELECT MAX(end_date) FROM apple_health_records") if apple_records else None
    apple_change = _scalar(connection, "SELECT MAX(imported_at) FROM apple_health_records") if apple_records else None
    apple_attempt = apple_success = apple_failure = None
    if _table(connection, "apple_health_import_files"):
        apple_attempt = _scalar(connection, "SELECT MAX(COALESCE(last_attempt_at, imported_at)) FROM apple_health_import_files")
        apple_success = _scalar(connection, "SELECT MAX(imported_at) FROM apple_health_import_files WHERE status='imported'")
        apple_failure = _scalar(connection, "SELECT MAX(COALESCE(last_attempt_at, imported_at)) FROM apple_health_import_files WHERE status NOT IN ('imported','skipped')")

    nutrition_rows = int(_scalar(connection, "SELECT COUNT(*) FROM nutrition_items", 0)) if _table(connection, "nutrition_items") else 0
    nutrition_day = _scalar(connection, "SELECT MAX(datum) FROM nutrition_items") if nutrition_rows else None
    nutrition_change = _scalar(connection, "SELECT MAX(COALESCE(updated_at, imported_at)) FROM nutrition_items") if nutrition_rows else None
    nutrition_run = _scalar(connection, "SELECT MAX(updated_at) FROM sync_state WHERE key LIKE 'yazio:%:v2:fingerprint'") if _table(connection, "sync_state") else None
    if nutrition_rows and _table(connection, "nutrition_histamine_scores"):
        nutrition_reviews = int(
            build_mapping_review(connection, status="all")["summary"]["open_entries"]
        )
    else:
        # A legacy/minimal database without score rows is entirely unreviewed.
        nutrition_reviews = nutrition_rows

    lab_rows = int(_scalar(connection, "SELECT COUNT(*) FROM laborwerte", 0)) if _table(connection, "laborwerte") else 0
    lab_day = _scalar(connection, "SELECT MAX(COALESCE(abnahme_datum, befund_datum)) FROM laborwerte") if lab_rows else None
    lab_success = _scalar(connection, "SELECT MAX(ermittlung_datum) FROM laborwerte") if lab_rows else None
    lab_reviews = int(_scalar(connection, "SELECT COUNT(*) FROM laborwerte_staging WHERE status NOT IN ('approved','rejected','imported','reviewed','resolved')", 0)) if _table(connection, "laborwerte_staging") else 0

    document_rows = int(_scalar(connection, "SELECT COUNT(*) FROM dokumente", 0)) if _table(connection, "dokumente") else 0
    document_day = _scalar(connection, "SELECT MAX(document_date) FROM dokumente") if document_rows else None
    document_change = _scalar(connection, "SELECT MAX(updated_at) FROM document_processing") if _table(connection, "document_processing") else None
    doc_attempt = _scalar(connection, "SELECT MAX(started_at) FROM health_processing_runs WHERE run_type='full_document_reprocess'") if _table(connection, "health_processing_runs") else None
    doc_success = _scalar(connection, "SELECT MAX(finished_at) FROM health_processing_runs WHERE run_type='full_document_reprocess' AND status='completed'") if _table(connection, "health_processing_runs") else None
    doc_failure = _scalar(connection, "SELECT MAX(finished_at) FROM health_processing_runs WHERE run_type='full_document_reprocess' AND status NOT IN ('completed','success','ok')") if _table(connection, "health_processing_runs") else None
    doc_open = int(_scalar(connection, "SELECT COUNT(*) FROM document_candidates WHERE status='open'", 0)) if _table(connection, "document_candidates") else 0
    doc_conflicts = int(_scalar(connection, "SELECT COALESCE(SUM(conflict_count),0) FROM document_reconciliation", 0)) if _table(connection, "document_reconciliation") else 0

    symptom_rows = int(_scalar(connection, "SELECT COUNT(*) FROM symptom_log WHERE lower(COALESCE(kontext,'')) LIKE '%telegram%'", 0)) if _table(connection, "symptom_log") else 0
    symptom_day = _scalar(connection, "SELECT MAX(COALESCE(occurred_at, datum)) FROM symptom_log WHERE lower(COALESCE(kontext,'')) LIKE '%telegram%'") if symptom_rows else None
    symptom_success = _scalar(connection, "SELECT MAX(created_at) FROM symptom_log WHERE lower(COALESCE(kontext,'')) LIKE '%telegram%'") if symptom_rows else None
    telegram_rows = int(_scalar(connection, "SELECT COUNT(*) FROM health_events WHERE lower(source) LIKE 'telegram%' AND lower(category) <> 'symptom'", 0)) if _table(connection, "health_events") else 0
    telegram_day = _scalar(connection, "SELECT MAX(COALESCE(occurred_at, date)) FROM health_events WHERE lower(source) LIKE 'telegram%' AND lower(category) <> 'symptom'") if telegram_rows else None
    telegram_success = _scalar(connection, "SELECT MAX(created_at) FROM health_events WHERE lower(source) LIKE 'telegram%' AND lower(category) <> 'symptom'") if telegram_rows else None

    availability = lambda rows, table: "available" if rows else ("unavailable" if table else "unknown")
    sources = [
        _source(key="apple_health_metrics", name="Apple-Health-Metriken", icon="♥", configured=True, actual_day=apple_day,
                attempt=apple_attempt, success=apple_success, failure=apple_failure, cadence=_cadence("daily", "Täglich nach nächtlichem Export", 2),
                availability=availability(apple_records, _table(connection, "apple_health_records")), last_change=apple_change, artifact=artifact,
                explanation="Messzeitpunkt, Importversuch und erfolgreicher Import werden getrennt ausgewiesen."),
        _source(key="apple_health_workouts", name="Apple-Health-Workouts", icon="↗", configured=False, actual_day=None,
                attempt=None, success=None, failure=None, cadence=_cadence("none", "Kein Rhythmus konfiguriert"), availability="unknown",
                last_change=None, artifact=artifact, import_state="never_run",
                explanation="Nicht konfiguriert oder nicht im Export enthalten. Das sagt nicht aus, ob auf dem iPhone Workouts vorhanden sind.",
                action={"label": "Workout-Export auf dem iPhone prüfen", "target": "workout-export-help"}),
        _source(key="nutrition_yazio", name="Ernährung · YAZIO", icon="◒", configured=True, actual_day=nutrition_day,
                attempt=nutrition_run, success=nutrition_run, failure=None, cadence=_cadence("daily", "Täglich", 2),
                availability=availability(nutrition_rows, _table(connection, "nutrition_items")),
                review="review_open" if nutrition_reviews else "none", review_count=nutrition_reviews, last_change=nutrition_change,
                artifact=artifact, explanation="Ein erfolgreicher Abgleich kann auch dann vorliegen, wenn kein neuer Ernährungseintrag erwartet oder geliefert wurde.",
                action={"label": "Lebensmittel-Zuordnungen prüfen", "target": "nutrition-mapping"} if nutrition_reviews else None),
        _source(key="laboratory", name="Laborimporte", icon="◇", configured=True, actual_day=lab_day,
                attempt=None, success=lab_success, failure=None, cadence=_cadence("event_based", "Bei neuem Laborbericht"),
                availability=availability(lab_rows, _table(connection, "laborwerte")), review="review_open" if lab_reviews else "none",
                review_count=lab_reviews, last_change=lab_success, artifact=artifact,
                explanation="Ereignisbasiert: Ohne neuen Laborbericht wird keine tägliche Aktualisierung erwartet.",
                action={"label": "Laborimport fachlich prüfen", "target": "document-review"} if lab_reviews else None),
        _source(key="documents_ocr", name="Dokumente & OCR", icon="▤", configured=True, actual_day=document_day,
                attempt=doc_attempt, success=doc_success, failure=doc_failure, cadence=_cadence("event_based", "Bei neuem Dokument"),
                availability=availability(document_rows, _table(connection, "dokumente")),
                review="conflict" if doc_conflicts else ("review_open" if doc_open else "none"),
                review_count=doc_conflicts or doc_open, last_change=document_change, artifact=artifact,
                explanation="Dokumentdatum, technische Verarbeitung und fachliche Prüfung bleiben getrennt.",
                action={"label": "Dokumente fachlich prüfen", "target": "document-review"} if doc_conflicts or doc_open else None),
        _source(key="telegram_symptoms", name="Telegram-Symptome", icon="●", configured=True, actual_day=symptom_day,
                attempt=symptom_success, success=symptom_success, failure=None, cadence=_cadence("event_based", "Bei neuer Meldung"),
                availability=availability(symptom_rows, _table(connection, "symptom_log")), last_change=symptom_success, artifact=artifact,
                explanation="Ereignisbasiert: Ohne neue Meldung besteht keine Verspätung."),
        _source(key="telegram_other", name="Weitere Telegram-Erfassungen", icon="＋", configured=True, actual_day=telegram_day,
                attempt=telegram_success, success=telegram_success, failure=None, cadence=_cadence("event_based", "Bei neuer Erfassung"),
                availability=availability(telegram_rows, _table(connection, "health_events")), last_change=telegram_success, artifact=artifact,
                explanation="Ereignisbasiert: Es wird kein täglicher Eintrag erwartet."),
    ]
    relevant_changes = [_dt(item["last_relevant_data_change_at"]) for item in sources if item["configured"]]
    latest_change = max((value for value in relevant_changes if value is not None), default=None)
    dashboard_regeneration = derive_regeneration(latest_change, artifact.get("generated_at"), artifact_available=bool(artifact.get("available")))
    dashboard = _source(key="dashboard_v5", name="Private Dashboard V5", icon="◆", configured=True, actual_day=None,
                        attempt=artifact.get("generated_at"), success=artifact.get("generated_at"), failure=None,
                        cadence=_cadence("event_based", "Nach relevantem Datenwechsel"),
                        availability="available" if artifact.get("available") else "unavailable", last_change=latest_change,
                        artifact=artifact, import_state="unknown",
                        explanation="Datenänderung, Artefaktgenerierung und ausgelieferter Build werden getrennt geprüft.")
    dashboard["regeneration_state"] = dashboard_regeneration
    dashboard["runtime"] = {"generated_at": artifact.get("generated_at"), "build_id": artifact.get("build_id"),
                            "commit": artifact.get("commit"), "artifact_sha256": artifact.get("sha256")}
    sources.append(dashboard)

    technical_attention = [item for item in sources if item["source_key"] != "apple_health_workouts" and (
        item["freshness"] == "delayed" or item["import_state"] == "failed" or item["regeneration_state"] in {"pending", "failed"})]
    review_total = sum(item["open_review_count"] for item in sources)
    configured_attention = [item for item in sources if item["configured"] and (
        item["freshness"] == "delayed" or item["import_state"] == "failed" or item["review_state"] != "none" or item["regeneration_state"] in {"pending", "failed"})]
    payload = {
        "contract_version": CONTRACT_VERSION, "calculated_at": calculated_at, "sources": sources,
        "summary": {
            "headline": "Bei einigen Quellen besteht Handlungsbedarf" if configured_attention else "Alle konfigurierten Quellen aktuell",
            "attention_required": bool(configured_attention), "open_review_count": review_total,
            "today_attention_reasons": [
                "Ein Import ist verspätet oder fehlgeschlagen." if any(x["freshness"] == "delayed" or x["import_state"] == "failed" for x in technical_attention) else None,
                "Der ausgelieferte Datenstand wird noch aktualisiert." if any(x["regeneration_state"] in {"pending", "failed"} for x in technical_attention) else None,
            ],
        },
    }
    payload["summary"]["today_attention_reasons"] = [x for x in payload["summary"]["today_attention_reasons"] if x][:2]
    validate_public_payload(payload)
    return payload


def validate_public_payload(payload: dict[str, Any]) -> None:
    for item in payload.get("sources", []):
        if item.get("availability") not in AVAILABILITY or item.get("freshness") not in FRESHNESS:
            raise ValueError("invalid source availability or freshness")
        if item.get("import_state") not in IMPORT or item.get("review_state") not in REVIEW or item.get("regeneration_state") not in REGENERATION:
            raise ValueError("invalid source status dimension")
    def walk(value: Any) -> None:
        if isinstance(value, dict):
            for key, child in value.items():
                if key in {"source_key", "target", "contract_version"}:
                    continue
                walk(child)
        elif isinstance(value, list):
            for child in value:
                walk(child)
        elif isinstance(value, str) and PRIVATE_TEXT.search(value):
            raise ValueError("private status detail rejected")
    walk(payload)