#!/usr/bin/env python3
"""JARVIS Health Dashboard v3.1.

Robust Chart.js dashboard:
- laboratory trend charts with reference ranges and clinical-event markers
- Apple Health Auto Export modules (sleep/HRV/pulse/activity/SpO2/respiration)
- nutrition adherence
- doctor-report PDF link / Drive upload metadata
"""
from __future__ import annotations

import html
import json
import os
import re
import sqlite3
from collections import defaultdict
from datetime import datetime
from pathlib import Path
from statistics import mean
from typing import Any

BASE = Path.home() / ".hermes" / "assets" / "Gesundheit"
DB = BASE / "health_data.db"
REPORTS = BASE / "reports"

import sys
sys.path.insert(0, str(BASE / "scripts"))
from health_pipeline import best_reference_xlsx, parse_reference_xlsx, get_lab_matrix, split_reference, db_counts  # noqa: E402
from apple_health_analytics import daily_series as canonical_apple_daily, unit_for as canonical_apple_unit  # noqa: E402

APPLE_CHARTS = [
    ("step_count", "Schritte", "sum"),
    ("walking_running_distance", "Geh-/Laufdistanz", "sum"),
    ("resting_heart_rate", "Ruhepuls", "avg"),
    ("heart_rate", "Herzfrequenz", "avg"),
    ("heart_rate_variability", "HRV", "avg"),
    ("sleep_analysis", "Schlafdauer", "sum"),
    ("blood_oxygen_saturation", "SpO₂", "avg"),
    ("respiratory_rate", "Atemfrequenz", "avg"),
    ("active_energy", "Aktive Energie", "sum"),
    ("physical_effort", "Physical Effort", "avg"),
    ("weight_body_mass", "Gewicht", "avg"),
]


def conn() -> sqlite3.Connection:
    c = sqlite3.connect(DB)
    c.row_factory = sqlite3.Row
    return c


def resolve_doc_path(*values: Any) -> Path | None:
    for value in values:
        if not value:
            continue
        raw = str(value)
        for p in [Path(os.path.expanduser(raw)), BASE / raw, Path("/home/agent") / raw, Path(raw.replace("/home/agent/Gesundheit", str(BASE)))]:
            try:
                if p.exists():
                    return p.resolve()
            except OSError:
                pass
    return None


def doc_url(row: sqlite3.Row) -> str | None:
    """Return browser-openable URL for dashboard documents.

    iPhone/Safari cannot open local file:// links from the Tailscale dashboard,
    so local HealthManager docs are exposed via the dashboard's restricted
    /health-doc/<id> route. Drive remains only a fallback.
    """
    keys = row.keys()
    vals = []
    if "local_original_path" in keys: vals.append(row["local_original_path"])
    if "dateipfad" in keys: vals.append(row["dateipfad"])
    p = resolve_doc_path(*vals)
    if p and "id" in keys:
        return f"/health-doc/{int(row['id'])}"
    if "drive_web_url" in keys and row["drive_web_url"]:
        return str(row["drive_web_url"])
    return None


def link_doc(row: sqlite3.Row) -> str:
    name = html.escape(str(row["datei_name"]))
    url = doc_url(row)
    return f"<a href='{html.escape(url)}' target='_blank'>{name}</a>" if url else name


def num(x: Any) -> float | None:
    try:
        return float(str(x).replace(",", ".").replace("<", "").replace(">", "").strip())
    except Exception:
        return None


def row_value(row: Any, key: str, default: Any = None) -> Any:
    try:
        value = row[key]
    except (KeyError, IndexError, TypeError):
        return default
    return default if value is None else value


def parse_comparison_value(raw: Any) -> tuple[str, float] | None:
    match = re.fullmatch(r"\s*(<=|>=|<|>|=)?\s*([-+]?\d+(?:[.,]\d+)?)\s*", str(raw or ""))
    if not match:
        return None
    return match.group(1) or "=", float(match.group(2).replace(",", "."))


def evaluate_lab_warning(row: Any) -> tuple[str, str, str] | None:
    """Return a conservative reference-range warning for verified lab data only."""
    if str(row_value(row, "validierungsstatus", "")).lower() != "validiert":
        return None
    if int(row_value(row, "verified_against_original", 0) or 0) != 1:
        return None
    if str(row_value(row, "reference_range_source", "")).lower() != "scanned_original":
        return None
    unit = str(row_value(row, "einheit", "")).strip()
    parsed = parse_comparison_value(row_value(row, "wert"))
    lower = num(row_value(row, "reference_min"))
    upper = num(row_value(row, "reference_max"))
    if not unit or parsed is None or (lower is None and upper is None):
        return None
    operator, value = parsed
    direction = None
    if operator == "=" and upper is not None and value > upper:
        direction = "über"
    elif operator == "=" and lower is not None and value < lower:
        direction = "unter"
    elif operator == ">" and upper is not None and value >= upper:
        direction = "über"
    elif operator == ">=" and upper is not None and value > upper:
        direction = "über"
    elif operator == "<" and lower is not None and value <= lower:
        direction = "unter"
    elif operator == "<=" and lower is not None and value < lower:
        direction = "unter"
    if direction is None:
        return None
    name = str(row_value(row, "parameter_name", "Laborwert"))
    date = str(row_value(row, "abnahme_datum", row_value(row, "befund_datum", "")))
    reference = f"{lower if lower is not None else '–'} bis {upper if upper is not None else '–'} {unit}"
    display_value = f"{operator if operator != '=' else ''}{value:g} {unit}".strip()
    return (
        "orange",
        f"Validierten Laborwert ärztlich prüfen: {name}",
        f"{display_value} liegt sicher {direction} dem Referenzbereich ({reference}); Messdatum {date}.",
    )


def ref_nums(ref: str | None) -> tuple[float | None, float | None]:
    rmin, rmax, _ = split_reference(ref)
    return num(rmin), num(rmax)


def nutrition_score(text: str) -> dict[str, Any]:
    t = (text or "").lower()
    negatives = {
        "Schweinefleisch": ["schwein", "bratwurst", "salami", "speck", "schinken"],
        "Weizen/Gluten": ["hörnli", "hoernli", "weizen", "pasta", "brot", "pizza", "teigwaren"],
        "Industriezucker": ["torte", "kuchen", "süss", "suess", "zucker", "dessert", "glace"],
        "Histamin": ["alter käse", "kaese", "rotwein", "salami", "ferment", "bier", "wein"],
        "Nachtschatten": ["tomate", "aubergine", "kartoffel", "paprika", "peperoni"],
        "Gesättigte Fette": ["butter", "bratwurst", "kokos", "rahm", "sahne", "speck"],
        "Alkohol": ["alkohol", "bier", "wein", "panaché", "panache"],
    }
    positives = {
        "Fisch/Omega-3": ["lachs", "sardine", "makrele", "forelle", "fisch", "algenöl", "algenoel"],
        "Ballaststoffe": ["hafer", "linsen", "bohnen", "gemüse", "gemuese", "salat", "beeren"],
        "Protein gut": ["skyr", "quark", "huhn", "poulet", "ei", "tofu", "fisch"],
    }
    neg_hits = sorted({label for label, words in negatives.items() if any(w in t for w in words)})
    pos_hits = sorted({label for label, words in positives.items() if any(w in t for w in words)})
    score = max(0, min(100, 85 + 5 * len(pos_hits) - 16 * len(neg_hits)))
    return {"score": score, "neg_hits": neg_hits, "pos_hits": pos_hits, "histamine": 100 if "Histamin" in neg_hits else 0, "satfat": 100 if "Gesättigte Fette" in neg_hits else 0, "sugar": 100 if "Industriezucker" in neg_hits else 0, "gluten": int("Weizen/Gluten" in neg_hits), "pork": int("Schweinefleisch" in neg_hits), "nightshade": int("Nachtschatten" in neg_hits), "alcohol": int("Alkohol" in neg_hits), "fiber": 70 if "Ballaststoffe" in pos_hits else 20, "protein": 70 if "Protein gut" in pos_hits else 30, "omega3": 80 if "Fisch/Omega-3" in pos_hits else 10}


def compute_nutrition_features(c: sqlite3.Connection) -> list[sqlite3.Row]:
    by_day: dict[str, list[str]] = defaultdict(list)
    for r in c.execute("SELECT datum,beschreibung,wirkung,notizen FROM ernaehrung ORDER BY datum"):
        by_day[str(r["datum"])[:10]].append(" ".join(str(r[k] or "") for k in r.keys() if k != "datum"))
    for r in c.execute("SELECT datum,kategori,titel,inhalt,wirkung,notizen FROM tagebuch WHERE lower(kategori) LIKE 'ern%' ORDER BY datum"):
        by_day[str(r["datum"])[:10]].append(" ".join(str(r[k] or "") for k in r.keys() if k != "datum"))
    for day, texts in by_day.items():
        s = nutrition_score(" ".join(texts))
        c.execute("""
            INSERT OR REPLACE INTO nutrition_daily_features
            (datum, plan_adherence_score, histamine_score, saturated_fat_score, sugar_score, gluten_flag, pork_flag, nightshade_flag, alcohol_flag, fiber_proxy, protein_proxy, omega3_proxy, positive_hits, negative_hits, source, computed_at)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP)
        """, (day, s["score"], s["histamine"], s["satfat"], s["sugar"], s["gluten"], s["pork"], s["nightshade"], s["alcohol"], s["fiber"], s["protein"], s["omega3"], ", ".join(s["pos_hits"]), ", ".join(s["neg_hits"]), "dashboard_v31_keyword_heuristic"))
    c.commit()
    return list(c.execute("SELECT * FROM nutrition_daily_features ORDER BY datum"))


def apple_series(c: sqlite3.Connection, metric: str, mode: str) -> dict[str, Any] | None:
    # Use canonical aggregation: de-duplicates overlapping exports and converts
    # weekly/yearly sum exports to daily-equivalent values for comparable trends.
    series = canonical_apple_daily(metric, mode)
    if not series:
        return None
    labels = sorted(series)
    data = [series[d] for d in labels]
    unit = canonical_apple_unit(metric)
    return {"labels": labels, "data": data, "unit": unit, "count": len(data), "first": labels[0], "last": labels[-1], "latest": data[-1], "avg": round(mean(data), 2), "min": round(min(data), 2), "max": round(max(data), 2)}


def load_doctor_report_meta() -> dict[str, Any] | None:
    p = REPORTS / "arztbericht_drive_link.json"
    if not p.exists():
        return None
    try:
        return json.loads(p.read_text(encoding="utf-8"))
    except Exception:
        return None


def main() -> None:
    REPORTS.mkdir(parents=True, exist_ok=True)
    counts = db_counts()
    labs = parse_reference_xlsx(best_reference_xlsx()) if best_reference_xlsx().exists() else []
    dates, _params, matrix, _cats, refs = get_lab_matrix(labs) if labs else ([], [], {}, {}, {})

    c = conn()
    try:
        docs = list(c.execute("SELECT id,datei_name,dateipfad,kategorie,status,review_status,upload_datum,drive_web_url,local_original_path,length(COALESCE(extrahierte_inhalte,'')) text_len FROM dokumente ORDER BY id DESC LIMIT 25"))
        lab_docs = list(c.execute("SELECT id,datei_name,dateipfad,kategorie,status,drive_web_url,local_original_path FROM dokumente WHERE upper(COALESCE(kategorie,'')) LIKE '%LABOR%' ORDER BY id DESC LIMIT 40"))
        events = list(c.execute("""
            SELECT date,category,parameter,value,notes,source FROM health_events
            WHERE date IS NOT NULL AND date(date) IS NOT NULL AND upper(COALESCE(category,'')) NOT IN ('LABOR','PROFIL','BEFUNDE')
            ORDER BY date
        """))
        symptoms = list(c.execute("SELECT datum,kategori,symptom,schwergrad,notizen FROM symptome WHERE date(datum) IS NOT NULL ORDER BY datum"))
        periods = list(c.execute("SELECT start_date,end_date,event_type,label,severity,location,notes FROM health_event_periods ORDER BY start_date DESC LIMIT 50"))
        nutrition_features = compute_nutrition_features(c)
        nutrition_v2_daily = list(c.execute("SELECT * FROM nutrition_daily_summary_v2 ORDER BY datum"))
        nutrition_v2_meals = list(c.execute("SELECT * FROM nutrition_meal_summary ORDER BY datum, CASE meal WHEN 'breakfast' THEN 1 WHEN 'lunch' THEN 2 WHEN 'dinner' THEN 3 WHEN 'snack' THEN 4 ELSE 9 END"))
        nutrition_v2_items = list(c.execute("""
            SELECT i.datum,i.meal,i.name,i.amount,i.kcal,i.protein_g,i.carb_g,i.fat_g,
                   h.traffic_light,h.canonical_food,h.sighi_score,h.tags,h.confidence
            FROM nutrition_items i LEFT JOIN nutrition_histamine_scores h ON h.item_id=i.id
            WHERE i.source='yazio_api'
            ORDER BY i.datum, CASE i.meal WHEN 'breakfast' THEN 1 WHEN 'lunch' THEN 2 WHEN 'dinner' THEN 3 WHEN 'snack' THEN 4 ELSE 9 END, i.id
        """))
        medication_admins = list(c.execute("SELECT datum,medication_name,dose,route,event_type,scheduled_next_date,notes FROM medication_administrations ORDER BY datum DESC LIMIT 50"))
        nutrition_review = list(c.execute("SELECT example_name,occurrence_count,first_seen,last_seen,reason FROM nutrition_review_queue WHERE status='open' ORDER BY occurrence_count DESC,last_seen DESC LIMIT 25"))
        nutrition_correlations = list(c.execute("SELECT metric,lag_days,n,correlation,interpretation FROM nutrition_correlation_results WHERE target='symptom_score' ORDER BY metric,lag_days"))
        nutrition_food_insights = list(c.execute("SELECT canonical_food,insight_type,days_seen,avg_next_day_symptom,avg_followup_symptom,histamine_avg,confidence,notes FROM nutrition_food_insights ORDER BY CASE insight_type WHEN 'trigger_candidate' THEN 1 WHEN 'safe_candidate' THEN 2 ELSE 3 END, days_seen DESC, avg_followup_symptom DESC LIMIT 30"))
        personal_tolerances = list(c.execute("SELECT canonical_food,personal_status,evidence_level,notes,updated_at FROM personal_food_tolerance ORDER BY CASE personal_status WHEN 'problematic' THEN 1 WHEN 'unclear' THEN 2 WHEN 'safe' THEN 3 ELSE 4 END, canonical_food"))
        nutrition_phases = list(c.execute("SELECT phase,start_date,end_date,nutrition_days,avg_histamine_score,avg_symptom_score,avg_kcal,avg_protein_g,notes FROM nutrition_treatment_phase_summary ORDER BY CASE phase WHEN 'baseline_pre_hyrimoz' THEN 1 WHEN 'early_hyrimoz_phase' THEN 2 WHEN 'stable_hyrimoz_phase' THEN 3 ELSE 9 END"))
        nutrition_recommendations = list(c.execute("SELECT priority,title,rationale,next_step FROM nutrition_action_recommendations WHERE status='open' ORDER BY priority LIMIT 12"))
        apple = {metric: apple_series(c, metric, mode) for metric, _label, mode in APPLE_CHARTS}
    finally:
        c.close()

    key_params = [
        "C-Reaktives Protein (CRP)", "Blutsenkungsreaktion miniiSED", "Leukozyten", "Neutrophile", "Lymphozyten", "Monozythen", "Thrombozyten", "Ferritin", "Fibrinogen",
        "D-Dimer", "Faktor VIII", "Cholesterin gesamt", "LDL-Cholesterin", "HDL-Cholesterin", "Triglyceride", "Homocystein",
        "Kreatinin", "eGFR (Niere)", "Albumin/Kreatinin", "Protein", "Blut", "Erythrozyten",
        "ALAT (GPT)", "ASAT (GOT)", "GGT", "Vitamin D (25-OH)", "Vitamin B12 (Active)", "Folsäure", "HLA-B51"
    ]
    event_by_date: dict[str, list[str]] = defaultdict(list)
    for r in events:
        event_by_date[str(r["date"])[:10]].append(f"{r['category']}: {r['parameter']} {r['value'] or ''}".strip())
    for r in symptoms:
        event_by_date[str(r["datum"])[:10]].append(f"Symptom: {r['symptom']} ({r['schwergrad'] or 'n/a'})")

    charts = []
    for p in key_params:
        vals = []
        for d in dates:
            v = matrix.get((p, d))
            if v and num(v.value) is not None:
                vals.append((d, num(v.value)))
        if not vals:
            continue
        labels = [d for d, _ in vals]
        data = [v for _, v in vals]
        event_points = [{"x": d, "label": "; ".join(event_by_date[d])[:180]} for d in labels if d in event_by_date]
        rmin, rmax = ref_nums(refs.get(p))
        cid = re.sub(r"\W+", "_", p)
        charts.append(f"""
<section class='chart-card'><h3>{html.escape(p)}</h3><div class='muted'>Referenz: {html.escape(refs.get(p, 'n/a'))}</div><canvas id='{cid}' height='120'></canvas></section>
<script>makeLabChart('{cid}', {json.dumps(p)}, {json.dumps(labels)}, {json.dumps(data)}, {json.dumps(rmin)}, {json.dumps(rmax)}, {json.dumps(event_points, ensure_ascii=False)});</script>
""")

    # Full lab matrix grouped by physician-style categories; includes every parsed value from the reference XLSX.
    category_order = [
        "Entzündung", "Hämatologie", "Blutstatus Leuk", "Blutbild automatisch absolut",
        "Gerinnung", "Spezielle Gerinnung", "Immunologie / Infektion", "Auto-Antikörper gegen",
        "Virale Hepatitiden A,B,C,D,E", "Blutfette & Stoffwechsel", "Leber & Niere",
        "Elektrolyte & Vitamine", "Proteine", "Schilddrüse & Hormone", "Urin", "Sonstiges"
    ]
    params_by_cat: dict[str, list[str]] = defaultdict(list)
    for p_name in _params:
        params_by_cat[_cats.get(p_name, "Sonstiges")].append(p_name)
    lab_category_sections = []
    for cat in category_order + sorted(set(params_by_cat) - set(category_order)):
        plist = params_by_cat.get(cat, [])
        if not plist:
            continue
        rows = []
        for p_name in plist:
            vals = []
            latest = ""
            for d in dates:
                v = matrix.get((p_name, d))
                cell = html.escape(v.value) if v else ""
                vals.append(f"<td>{cell}</td>")
                if v:
                    latest = f"{html.escape(v.value)} <span class='muted'>{html.escape(d)}</span>"
            rows.append(f"<tr><td><b>{html.escape(p_name)}</b><div class='muted'>Latest: {latest or 'n/a'}</div></td><td>{html.escape(refs.get(p_name, ''))}</td>{''.join(vals)}</tr>")
        lab_category_sections.append(f"""
        <details class='section-block lab-category search-section' data-search='{html.escape(cat + ' ' + ' '.join(plist))}'>
          <summary>{html.escape(cat)} <span class='muted'>({len(plist)} Parameter)</span></summary>
          <div class='section-body'><table class='lab-matrix'><tr><th>Parameter</th><th>Referenz</th>{''.join(f'<th>{html.escape(d)}</th>' for d in dates)}</tr>{''.join(rows)}</table></div>
        </details>
        """)
    all_lab_category_html = "".join(lab_category_sections) or "<p>Keine Laborwerte aus XLSX verfügbar.</p>"
    lab_param_count = len(_params)
    lab_value_count = len(matrix)

    apple_charts = []
    apple_rows = []
    for metric, label, mode in APPLE_CHARTS:
        s = apple.get(metric)
        if not s:
            continue
        cid = "apple_" + re.sub(r"\W+", "_", metric)
        apple_charts.append(f"""
<section class='chart-card'><h3>{html.escape(label)}</h3><div class='muted'>Einheit: {html.escape(s['unit'])}; Zeitraum: {s['first']} bis {s['last']}; Aggregation: {'Summe' if mode == 'sum' else 'Durchschnitt'} pro Tag/Woche</div><canvas id='{cid}' height='110'></canvas></section>
<script>makeLineChart('{cid}', {json.dumps(label)}, {json.dumps(s['labels'])}, {json.dumps(s['data'])}, {json.dumps(s['unit'])});</script>
""")
        apple_rows.append(f"<tr><td>{html.escape(label)}</td><td>{s['first']} bis {s['last']}</td><td>{s['count']}</td><td>{s['latest']} {html.escape(s['unit'])}</td><td>{s['avg']}</td><td>{s['min']}</td><td>{s['max']}</td></tr>")

    cards = "".join(f"<div class='card'><div class='num'>{html.escape(str(v))}</div><div>{html.escape(k)}</div></div>" for k, v in counts.items())
    doc_rows = "".join(f"<tr><td>{r['id']}</td><td>{link_doc(r)}</td><td>{html.escape(str(r['kategorie']))}</td><td>{html.escape(str(r['status']))}</td><td>{html.escape(str(r['review_status'] or ''))}</td><td>{r['text_len']}</td></tr>" for r in docs)
    lab_doc_rows = "".join(f"<tr><td>{r['id']}</td><td>{link_doc(r)}</td><td>{html.escape(str(r['status']))}</td></tr>" for r in lab_docs)
    event_rows = "".join(f"<tr><td>{html.escape(str(r['date']))}</td><td>{html.escape(str(r['category']))}</td><td>{html.escape(str(r['parameter']))}</td><td>{html.escape(str(r['value'] or r['notes'] or ''))}</td></tr>" for r in events[-30:])
    symptom_rows = "".join(f"<tr><td>{html.escape(str(r['datum']))}</td><td>Symptom</td><td>{html.escape(str(r['symptom']))}</td><td>{html.escape(str(r['schwergrad'] or '') + ' ' + str(r['notizen'] or ''))}</td></tr>" for r in symptoms[-30:])
    period_rows = "".join(f"<tr><td>{html.escape(str(r['start_date']))}</td><td>{html.escape(str(r['end_date'] or 'offen'))}</td><td>{html.escape(str(r['event_type']))}</td><td>{html.escape(str(r['label'] or ''))}</td><td>{html.escape(str(r['severity'] or ''))}</td><td>{html.escape(str(r['notes'] or ''))}</td></tr>" for r in periods) or "<tr><td colspan='6'>Noch keine Zeitraum-Events erfasst. Beispiel: Aphte von Start- bis Enddatum.</td></tr>"

    nf_labels = [r["datum"] for r in nutrition_features]
    nf_scores = [r["plan_adherence_score"] for r in nutrition_features]
    nf_hits = [r["negative_hits"] or "konform/unklar" for r in nutrition_features]
    nutrition_rows = "".join(f"<tr><td>{r['datum']}</td><td>{r['plan_adherence_score']:.0f}</td><td>{html.escape(str(r['negative_hits'] or ''))}</td><td>{html.escape(str(r['positive_hits'] or ''))}</td></tr>" for r in nutrition_features[-30:])

    light_icon = {"green":"🟢", "yellow":"🟡", "orange":"🟠", "red":"🔴", "unknown":"⚫"}
    meal_name = {"breakfast":"Frühstück", "lunch":"Mittagessen", "dinner":"Nachtessen", "snack":"Snacks", "unknown":"Unbekannt"}
    nutrient_labels = {
        "nutrient.sugar": "Zucker", "nutrient.fiber": "Ballaststoffe", "nutrient.salt": "Salz",
        "nutrient.sodium": "Natrium", "nutrient.saturated": "gesättigte Fettsäuren",
        "nutrient.protein": "Protein", "nutrient.carb": "Kohlenhydrate", "nutrient.fat": "Fett", "energy.energy": "Energie"
    }
    nutrition_v2_labels = [r["datum"] for r in nutrition_v2_daily]
    nutrition_v2_histamine = [r["histamine_score"] for r in nutrition_v2_daily]
    nutrition_v2_symptom = []
    severity_map = {"keine":0,"none":0,"leicht":1,"mild":1,"mittel":2,"moderat":2,"schwer":3,"stark":3,"hoch":3}
    symptom_by_day: dict[str, float] = defaultdict(float)
    for r in symptoms:
        txt = str(r["schwergrad"] or "").lower()
        sev = 1
        for k, v in severity_map.items():
            if k in txt:
                sev = v; break
        symptom_by_day[str(r["datum"])[:10]] += sev
    nutrition_v2_symptom = [symptom_by_day.get(d, 0) for d in nutrition_v2_labels]

    by_day_meal: dict[tuple[str, str], list[sqlite3.Row]] = defaultdict(list)
    for r in nutrition_v2_items:
        by_day_meal[(str(r["datum"]), str(r["meal"]))].append(r)
    meal_summary_by_key = {(str(r["datum"]), str(r["meal"])): r for r in nutrition_v2_meals}
    recent_days = [r["datum"] for r in nutrition_v2_daily[-30:]][::-1]
    overview_cards = []
    for idx, day in enumerate(recent_days):
        day_sum = next((r for r in nutrition_v2_daily if r["datum"] == day), None)
        if not day_sum:
            continue
        label = str(day_sum['histamine_label'] or 'unknown')
        summary_line = (
            f"<summary class='day-summary'>"
            f"<span class='day-date'>{html.escape(day)}</span>"
            f"<span class='pill {html.escape(label)}'>{light_icon.get(label,'⚫')} Histamin {html.escape(label)}</span>"
            f"<span class='day-kcal'>{float(day_sum['kcal'] or 0):.0f} kcal</span>"
            f"<span class='day-load'>Load {float(day_sum['histamine_score'] or 0):.1f}</span>"
            f"<span class='day-items'>{int(day_sum['item_count'] or 0)} Items</span>"
            f"</summary>"
        )
        day_macro = (
            f"<div class='day-macro'>Protein {float(day_sum['protein_g'] or 0):.1f} g · "
            f"KH {float(day_sum['carb_g'] or 0):.1f} g · Fett {float(day_sum['fat_g'] or 0):.1f} g · "
            f"Unbekannt {int(day_sum['histamine_unknown_count'] or 0)}</div>"
        )
        meals_html = []
        for m in ["breakfast", "lunch", "dinner", "snack", "unknown"]:
            items_for = by_day_meal.get((day, m), [])
            if not items_for:
                continue
            ms = meal_summary_by_key.get((day, m))
            meal_label = str(ms['histamine_label'] if ms else 'unknown')
            top_nutrients = []
            if ms and ms["nutrient_json"]:
                try:
                    nj = json.loads(ms["nutrient_json"])
                    for k in ["nutrient.sugar", "nutrient.fiber", "nutrient.salt", "nutrient.saturated"]:
                        if k in nj and nj[k]:
                            top_nutrients.append(f"{nutrient_labels.get(k,k)} {float(nj[k]):.1f} g")
                except Exception:
                    pass
            item_cards = "".join(
                f"<div class='food-row {html.escape(str(r['traffic_light'] or 'unknown'))}'>"
                f"<span class='food-h'>{light_icon.get(r['traffic_light'] or 'unknown','⚫')}</span>"
                f"<span class='food-name'>{html.escape(str(r['name']))}<small>{html.escape(str(r['canonical_food'] or 'unbekannt'))}</small></span>"
                f"<span class='food-amount'>{float(r['amount'] or 0):.0f} g</span>"
                f"<span class='food-kcal'>{float(r['kcal'] or 0):.0f} kcal</span>"
                f"<span class='food-macro'>P {float(r['protein_g'] or 0):.1f} · KH {float(r['carb_g'] or 0):.1f} · F {float(r['fat_g'] or 0):.1f}</span>"
                f"</div>"
                for r in items_for
            )
            meals_html.append(f"""
            <section class='meal-card'><div class='meal-head'><h4>{meal_name.get(m,m)}</h4><span class='pill {html.escape(meal_label)}'>{light_icon.get(meal_label,'⚫')} {html.escape(meal_label)}</span></div>
            <div class='macroline'><b>{float(ms['kcal'] if ms else 0):.0f} kcal</b> · Protein {float(ms['protein_g'] if ms else 0):.1f} g · KH {float(ms['carb_g'] if ms else 0):.1f} g · Fett {float(ms['fat_g'] if ms else 0):.1f} g</div>
            <div class='muted'>{html.escape(' · '.join(top_nutrients) if top_nutrients else 'Details abhängig von YAZIO-Produktdaten')}</div>
            <div class='food-list'>{item_cards}</div></section>
            """)
        open_attr = ""
        overview_cards.append(f"<details class='day-card'{open_attr}>{summary_line}<div class='day-detail'>{day_macro}{''.join(meals_html)}</div></details>")
    meal_explorer_html = "".join(overview_cards) or "<section class='card'>Noch keine Mahlzeiten importiert.</section>"

    medication_rows = "".join(f"<tr><td>{html.escape(str(r['datum']))}</td><td>{html.escape(str(r['medication_name']))}</td><td>{html.escape(str(r['event_type'] or ''))}</td><td>{html.escape(str(r['dose'] or ''))}</td><td>{html.escape(str(r['scheduled_next_date'] or ''))}</td><td>{html.escape(str(r['notes'] or ''))}</td></tr>" for r in medication_admins) or "<tr><td colspan='6'>Noch keine Medikamentengaben erfasst.</td></tr>"

    corr_label = {"histamine_load":"Histamin-Load", "kcal":"kcal", "protein_g":"Protein", "sugar_g":"Zucker", "fiber_g":"Ballaststoffe", "saturated_fat_g":"gesättigte Fettsäuren"}
    corr_parts = []
    for r in nutrition_correlations:
        corr_val = '' if r['correlation'] is None else f"{float(r['correlation']):.2f}"
        corr_parts.append(f"<tr><td>{html.escape(corr_label.get(r['metric'], str(r['metric'])))}</td><td>+{r['lag_days']} Tage</td><td>{r['n']}</td><td>{corr_val}</td><td>{html.escape(str(r['interpretation'] or ''))}</td></tr>")
    correlation_rows = "".join(corr_parts) or "<tr><td colspan='5'>Noch keine Korrelationsdaten berechnet.</td></tr>"
    insight_badge = {"trigger_candidate":"🔴 Trigger-Kandidat", "safe_candidate":"🟢 Safe-Kandidat", "insufficient_data":"⚪ unklar"}
    food_insight_rows = "".join(
        f"<tr><td>{html.escape(str(r['canonical_food']))}</td><td>{insight_badge.get(r['insight_type'], html.escape(str(r['insight_type'])))}</td><td>{r['days_seen']}</td><td>{float(r['avg_next_day_symptom'] or 0):.2f}</td><td>{float(r['avg_followup_symptom'] or 0):.2f}</td><td>{float(r['histamine_avg'] or 0):.2f}</td><td>{html.escape(str(r['confidence'] or ''))}</td><td>{html.escape(str(r['notes'] or ''))}</td></tr>"
        for r in nutrition_food_insights
    ) or "<tr><td colspan='8'>Noch keine Food-Insights berechnet.</td></tr>"
    review_rows = "".join(
        f"<tr><td>{html.escape(str(r['example_name']))}</td><td>{r['occurrence_count']}</td><td>{html.escape(str(r['first_seen']))}</td><td>{html.escape(str(r['last_seen']))}</td><td>{html.escape(str(r['reason'] or ''))}</td></tr>"
        for r in nutrition_review
    ) or "<tr><td colspan='5'>Keine offenen unbekannten Produkte. Beunruhigend effizient.</td></tr>"

    status_icon = {'safe':'🟢 safe', 'problematic':'🔴 problematisch', 'unclear':'🟡 unklar', 'unknown':'⚫ unbekannt'}
    tolerance_rows = "".join(
        f"<tr><td>{html.escape(str(r['canonical_food']))}</td><td>{status_icon.get(r['personal_status'], html.escape(str(r['personal_status'])))}</td><td>{html.escape(str(r['evidence_level'] or ''))}</td><td>{html.escape(str(r['notes'] or ''))}</td><td>{html.escape(str(r['updated_at'] or ''))}</td></tr>"
        for r in personal_tolerances
    ) or "<tr><td colspan='5'>Noch keine persönliche Verträglichkeit gesetzt. Das System kennt SIGHi, aber nicht deinen Magen. Noch.</td></tr>"
    phase_label = {'baseline_pre_hyrimoz':'Baseline vor Hyrimoz', 'early_hyrimoz_phase':'Frühe Hyrimoz-Phase', 'stable_hyrimoz_phase':'Stabile Hyrimoz-Phase', 'unknown':'Unbekannt'}
    phase_rows = "".join(
        f"<tr><td>{html.escape(phase_label.get(r['phase'], str(r['phase'])))}</td><td>{html.escape(str(r['start_date']))} bis {html.escape(str(r['end_date']))}</td><td>{r['nutrition_days']}</td><td>{float(r['avg_histamine_score'] or 0):.2f}</td><td>{float(r['avg_symptom_score'] or 0):.2f}</td><td>{float(r['avg_kcal'] or 0):.0f}</td><td>{float(r['avg_protein_g'] or 0):.1f} g</td><td>{html.escape(str(r['notes'] or ''))}</td></tr>"
        for r in nutrition_phases
    ) or "<tr><td colspan='8'>Noch keine Hyrimoz-Phasen berechnet.</td></tr>"
    recommendation_rows = "".join(
        f"<tr><td>{r['priority']}</td><td>{html.escape(str(r['title']))}</td><td>{html.escape(str(r['rationale'] or ''))}</td><td>{html.escape(str(r['next_step'] or ''))}</td></tr>"
        for r in nutrition_recommendations
    ) or "<tr><td colspan='4'>Keine offenen Empfehlungen.</td></tr>"

    # --- Extended operational cockpit sections ---
    c_ext = conn()
    doc_view_rows = "".join(
        f"<tr><td>{r['id']}</td><td>{link_doc(r)}</td><td>{html.escape(str(r['document_date'] or r['upload_datum'] or ''))}</td><td>{html.escape(str(r['kategorie'] or ''))}</td><td>{html.escape(str(r['institution'] or ''))}</td><td>{html.escape(str(r['review_status'] or 'nicht_geprueft'))}</td></tr>"
        for r in list(c_ext.execute("SELECT id,datei_name,dateipfad,kategorie,status,review_status,upload_datum,drive_web_url,local_original_path,document_date,institution FROM dokumente ORDER BY COALESCE(document_date,upload_datum) DESC, id DESC LIMIT 30"))
    ) or "<tr><td colspan='6'>Keine Dokumente.</td></tr>"
    doc_counts = list(c_ext.execute("SELECT COALESCE(review_status,'nicht_geprueft') review_status, COALESCE(kategorie,'unkategorisiert') kategorie, COUNT(*) c FROM dokumente GROUP BY review_status,kategorie ORDER BY c DESC LIMIT 20"))
    doc_review_rows = "".join(f"<tr><td>{html.escape(str(r['review_status']))}</td><td>{html.escape(str(r['kategorie']))}</td><td>{r['c']}</td></tr>" for r in doc_counts) or "<tr><td colspan='3'>Keine Dokumente.</td></tr>"

    key_lab_names = ["C-Reaktives Protein (CRP)", "D-Dimer", "Thrombozyten", "Leukozyten", "Ferritin", "Vitamin D (25-OH)"]
    lab_latest = []
    for name in key_lab_names:
        row = c_ext.execute("""SELECT parameter_name,wert,einheit,reference_min,reference_max,
                                      abnahme_datum,befund_datum,validierungsstatus,
                                      verified_against_original,reference_range_source
                               FROM laborwerte WHERE parameter_name=?
                               ORDER BY COALESCE(abnahme_datum,befund_datum,ermittlung_datum) DESC LIMIT 1""", (name,)).fetchone()
        if row: lab_latest.append(row)
    lab_latest_cards = "".join(
        f"<div class='info-row'><b>{html.escape(str(r['parameter_name']))}</b><span>{html.escape(str(r['wert']))} {html.escape(str(r['einheit'] or ''))}</span><small>{html.escape(str(r['abnahme_datum'] or r['befund_datum'] or ''))} · {html.escape(str(r['validierungsstatus'] or ''))}</small></div>"
        for r in lab_latest
    ) or "<div class='info-row'>Keine Schlüssellabore gefunden.</div>"

    appointment_docs = list(c_ext.execute("SELECT id,datei_name,dateipfad,kategorie,status,review_status,upload_datum,drive_web_url,local_original_path,document_date,institution FROM dokumente WHERE review_status IN ('arzttermin','relevant') ORDER BY COALESCE(document_date,upload_datum) DESC, id DESC LIMIT 12"))
    appointment_doc_rows = "".join(f"<li>{link_doc(r)} <span class='muted'>({html.escape(str(r['kategorie'] or ''))}, {html.escape(str(r['document_date'] or r['upload_datum'] or ''))})</span></li>" for r in appointment_docs) or "<li>Noch keine Dokumente als relevant/Arzttermin markiert.</li>"
    medication_brief_cards = "".join(
        f"<div class='info-row'><b>{html.escape(str(r['medication_name']))}</b><span>{html.escape(str(r['dose'] or ''))}</span><small>{html.escape(str(r['datum']))} · nächste: {html.escape(str(r['scheduled_next_date'] or 'n/a'))}</small></div>"
        for r in medication_admins[:8]
    ) or "<div class='info-row'>Keine Medikation erfasst.</div>"
    recent_sym_data = list(c_ext.execute("SELECT datum,symptom,schwergrad,notizen FROM symptom_log ORDER BY datum DESC, id DESC LIMIT 12"))
    recent_sym_cards = "".join(
        f"<div class='info-row'><b>{html.escape(str(r['symptom']))}</b><span>{html.escape(str(r['schwergrad'] or ''))}</span><small>{html.escape(str(r['datum']))} · {html.escape(str(r['notizen'] or ''))}</small></div>"
        for r in recent_sym_data[:8]
    ) or "<div class='info-row'>Noch wenig Quick-Symptomdaten.</div>"

    timeline = []
    for r in medication_admins[:20]: timeline.append((str(r['datum'])[:10], '💉 Medikation', f"{r['medication_name']} {r['dose'] or ''} {r['event_type'] or ''}".strip()))
    for r in symptoms[-40:]: timeline.append((str(r['datum'])[:10], '🩺 Symptom', f"{r['symptom']} ({r['schwergrad'] or 'n/a'})"))
    for r in events[-60:]: timeline.append((str(r['date'])[:10], str(r['category'] or 'Event'), f"{r['parameter'] or ''} {r['value'] or r['notes'] or ''}".strip()))
    for r in nutrition_v2_daily[-20:]: timeline.append((str(r['datum']), '🍽️ Ernährung', f"{float(r['kcal'] or 0):.0f} kcal · Histamin {r['histamine_label']} · Load {float(r['histamine_score'] or 0):.1f}"))
    for r in lab_latest: timeline.append((str(r['abnahme_datum'] or r['befund_datum'] or '')[:10], '🧪 Labor', f"{r['parameter_name']}: {r['wert']} {r['einheit'] or ''}"))
    timeline = sorted([x for x in timeline if x[0]], key=lambda x: x[0], reverse=True)[:80]
    timeline_rows = "".join(f"<tr><td>{html.escape(d)}</td><td>{html.escape(cat)}</td><td>{html.escape(txt)}</td></tr>" for d,cat,txt in timeline) or "<tr><td colspan='3'>Keine Timeline-Daten.</td></tr>"

    symptom_days = sorted({str(r['datum'])[:10] for r in c_ext.execute("SELECT datum FROM symptom_log")})[-30:]
    symptom_dims = ['Aphthen/Mundulzera','GI/Darm','Müdigkeit/Fatigue','Haut','Augen','Gelenke','Vaskulär/Thrombose-Warnzeichen']
    def sev_num(txt: str) -> int:
        txt = (txt or '').lower()
        if 'schwer' in txt or '(3)' in txt: return 3
        if 'mittel' in txt or '(2)' in txt: return 2
        if 'leicht' in txt or '(1)' in txt: return 1
        return 0
    symptom_map = defaultdict(dict)
    for r in c_ext.execute("SELECT datum,symptom,schwergrad FROM symptom_log"):
        symptom_map[str(r['datum'])[:10]][str(r['symptom'])] = sev_num(str(r['schwergrad'] or ''))
    symptom_total = [sum(symptom_map[d].get(dim,0) for dim in symptom_dims) for d in symptom_days]
    symptom_datasets = [{"label":"Total","data":symptom_total,"borderColor":"#1f4e78","backgroundColor":"rgba(31,78,120,.08)","tension":.25}]
    colors = ['#ef4444','#f97316','#7c3aed','#16a34a','#0ea5e9','#a16207','#991b1b']
    for dim, col in zip(symptom_dims, colors):
        symptom_datasets.append({"label": dim.split('/')[0], "data": [symptom_map[d].get(dim,0) for d in symptom_days], "borderColor": col, "backgroundColor": col, "tension": .2})
    symptom_chart = f"<section class='chart-card'><h3>Symptom-Score Verlauf</h3><div class='muted'>0..3 je Dimension; Total ist Summe. Mehr Daten = weniger Kaffeesatz.</div><canvas id='symptom_score_chart' height='130'></canvas></section><script>makeMultiLineChart('symptom_score_chart', {json.dumps(symptom_days)}, {json.dumps(symptom_datasets, ensure_ascii=False)});</script>" if symptom_days else "<section class='chart-card'><h3>Symptom-Score Verlauf</h3><p>Noch keine Quick-Symptomdaten.</p></section>"

    c_ext.execute("""CREATE TABLE IF NOT EXISTS low_histamine_experiments (
        id INTEGER PRIMARY KEY AUTOINCREMENT, start_date TEXT NOT NULL, end_date TEXT, phase TEXT NOT NULL DEFAULT 'baseline', challenge_food TEXT, hypothesis TEXT, notes TEXT, status TEXT DEFAULT 'active', created_at TEXT DEFAULT CURRENT_TIMESTAMP, updated_at TEXT DEFAULT CURRENT_TIMESTAMP)""")
    exp_rows = list(c_ext.execute("SELECT id,start_date,end_date,phase,challenge_food,hypothesis,notes,status FROM low_histamine_experiments ORDER BY id DESC LIMIT 20"))
    experiment_rows = "".join(f"<tr><td>{r['id']}</td><td>{html.escape(str(r['start_date']))}</td><td>{html.escape(str(r['end_date'] or 'offen'))}</td><td>{html.escape(str(r['phase']))}</td><td>{html.escape(str(r['challenge_food'] or ''))}</td><td>{html.escape(str(r['status']))}</td><td>{html.escape(str(r['hypothesis'] or r['notes'] or ''))}</td></tr>" for r in exp_rows) or "<tr><td colspan='7'>Noch kein Low-Histamine-Experiment gestartet. CLI: <code>low_histamine_experiment.py start</code></td></tr>"

    warnings = []
    for r in lab_latest:
        lab_warning = evaluate_lab_warning(r)
        if lab_warning:
            warnings.append(lab_warning)
    unreviewed = c_ext.execute("SELECT COUNT(*) FROM dokumente WHERE COALESCE(review_status,'nicht_geprueft')='nicht_geprueft'").fetchone()[0]
    if unreviewed: warnings.append(('yellow','Dokumente ungeprüft', f'{unreviewed} Dokumente haben noch keinen Review-Status.'))
    if len(symptom_days) < 7: warnings.append(('yellow','Symptomdaten dünn', f'Nur {len(symptom_days)} Tage Quick-Symptomdaten; Korrelationen bleiben schwach.'))
    red_recent = c_ext.execute("SELECT COUNT(*) FROM nutrition_daily_summary_v2 WHERE histamine_label='red' AND date(datum) >= date('now','-30 day')").fetchone()[0]
    if red_recent: warnings.append(('orange','Hohe Histamin-Tage', f'{red_recent} rote Tage in den letzten 30 Tagen.'))
    warning_rows = "".join(f"<tr><td><span class='pill {cls}'>{html.escape(cls)}</span></td><td>{html.escape(title)}</td><td>{html.escape(text)}</td></tr>" for cls,title,text in warnings) or "<tr><td colspan='3'>Keine automatischen Hinweise erkannt – dies schließt medizinische Risiken nicht aus.</td></tr>"
    warning_cards = "".join(f"<div class='mini-card'><span class='pill {cls}'>{html.escape(cls)}</span><b>{html.escape(title)}</b><small>{html.escape(text)}</small></div>" for cls,title,text in warnings[:4]) or "<div class='mini-card'><span class='pill'>Info</span><b>Keine automatischen Hinweise erkannt</b><small>Dies schließt medizinische Risiken nicht aus.</small></div>"
    latest_nutrition = nutrition_v2_daily[-1] if nutrition_v2_daily else None
    latest_nutrition_card = (
        f"<div class='mini-card'><b>Ernährung zuletzt</b><span>{latest_nutrition['datum']}: {float(latest_nutrition['kcal'] or 0):.0f} kcal</span><span class='pill {html.escape(str(latest_nutrition['histamine_label']))}'>{light_icon.get(latest_nutrition['histamine_label'],'⚫')} Load {float(latest_nutrition['histamine_score'] or 0):.1f}</span></div>"
        if latest_nutrition else "<div class='mini-card'><b>Ernährung</b><span>Keine Daten</span></div>"
    )
    next_med = next((r for r in medication_admins if r['scheduled_next_date']), None)
    next_med_card = (
        f"<div class='mini-card'><b>Nächste Medikation</b><span>{html.escape(str(next_med['scheduled_next_date']))}</span><small>{html.escape(str(next_med['medication_name']))} {html.escape(str(next_med['dose'] or ''))}</small></div>"
        if next_med else "<div class='mini-card'><b>Medikation</b><span>Kein nächster Termin erfasst</span></div>"
    )
    top_kpi_cards = (
        f"<div class='mini-card'><b>Warnhinweise</b><span class='big'>{len(warnings)}</span><small>einfache Regeln</small></div>"
        f"<div class='mini-card'><b>Dokumente Review</b><span class='big'>{unreviewed}</span><small>nicht geprüft</small></div>"
        f"<div class='mini-card'><b>Symptomtage</b><span class='big'>{len(symptom_days)}</span><small>Quick-Logs</small></div>"
        f"<div class='mini-card'><b>Laborwerte</b><span class='big'>{lab_param_count}</span><small>{lab_value_count} Einzelwerte</small></div>"
        + latest_nutrition_card + next_med_card
    )
    c_ext.close()

    meta = load_doctor_report_meta()
    pdf_local = REPORTS / "arztbericht_aktuell.pdf"
    pdf_link = "<a class='button' href='/health-report/arztbericht_aktuell.pdf' target='_blank'>PDF öffnen</a>" if pdf_local.exists() else "<span class='muted'>Noch kein PDF generiert.</span>"
    drive_link = ""
    if meta:
        up = meta.get('drive_upload') or {}
        url = up.get('webViewLink') or up.get('file', {}).get('webViewLink') or up.get('webViewUrl')
        if url:
            drive_link = f" <a class='button' href='{html.escape(url)}' target='_blank'>PDF in Google Drive öffnen</a>"

    now = datetime.now().strftime("%Y-%m-%d %H:%M")
    out = REPORTS / "health_dashboard.html"
    html_text = f"""<!doctype html><html lang='de'><head><meta charset='utf-8'><meta name='viewport' content='width=device-width, initial-scale=1, viewport-fit=cover'><title>JARVIS Health Dashboard v3.1</title>
<script src='https://cdn.jsdelivr.net/npm/chart.js@4.5.1/dist/chart.umd.min.js'></script>
<script>
function makeLabChart(id, label, labels, data, rmin, rmax, events) {{
  const clean = data.filter(v => v !== null); const maxData = Math.max(...clean); const minData = Math.min(...clean);
  const datasets = [{{label: label, data: data, borderColor:'#1f4e78', backgroundColor:'#1f4e78', pointRadius:4, tension:.25}}];
  if (rmin !== null && rmax !== null) {{ datasets.push({{label:'Ref min', data: labels.map(_=>rmin), borderColor:'rgba(34,197,94,.25)', pointRadius:0, borderWidth:1}}); datasets.push({{label:'Referenzbereich', data: labels.map(_=>rmax), borderColor:'rgba(34,197,94,.7)', backgroundColor:'rgba(34,197,94,.12)', pointRadius:0, borderWidth:1, fill:'-1'}}); }}
  else if (rmax !== null) {{ datasets.push({{label:'Ref max '+rmax, data:labels.map(_=>rmax), borderColor:'#22c55e', borderDash:[6,4], pointRadius:0, borderWidth:2}}); }}
  else if (rmin !== null) {{ datasets.push({{label:'Ref min '+rmin, data:labels.map(_=>rmin), borderColor:'#22c55e', borderDash:[6,4], pointRadius:0, borderWidth:2}}); }}
  if (events.length) {{ const eventData = labels.map(d => events.find(e => e.x === d) ? maxData * 1.08 : null); datasets.push({{label:'Health Event', data:eventData, borderColor:'rgba(239,68,68,.0)', backgroundColor:'#ef4444', pointStyle:'triangle', pointRadius:7, showLine:false}}); }}
  new Chart(document.getElementById(id), {{type:'line', data:{{labels:labels, datasets:datasets}}, options:{{responsive:true, maintainAspectRatio:false, interaction:{{mode:'index',intersect:false}}, plugins:{{tooltip:{{callbacks:{{afterBody:(items)=>{{ const lab=items[0]?.label; const ev=events.find(e=>e.x===lab); return ev ? ['Event: '+ev.label] : []; }}}}}}}}, scales:{{y:{{suggestedMin: Math.min(minData, rmin ?? minData)*0.9, suggestedMax: Math.max(maxData, rmax ?? maxData)*1.15}}, x:{{ticks:{{maxRotation:45,minRotation:45, autoSkip:true, maxTicksLimit:18}}}}}} }} }});
}}
function makeLineChart(id, label, labels, data, unit) {{ new Chart(document.getElementById(id), {{type:'line', data:{{labels:labels, datasets:[{{label:label+' ('+unit+')', data:data, borderColor:'#7c3aed', backgroundColor:'rgba(124,58,237,.12)', fill:true, pointRadius:2, tension:.25}}]}}, options:{{responsive:true, maintainAspectRatio:false, interaction:{{mode:'index',intersect:false}}, plugins:{{legend:{{display:true}}}}, scales:{{x:{{ticks:{{maxRotation:45,minRotation:45, autoSkip:true, maxTicksLimit:18}}}}}}}} }}); }}
function makeMultiLineChart(id, labels, datasets) {{ new Chart(document.getElementById(id), {{type:'line', data:{{labels:labels, datasets:datasets}}, options:{{responsive:true, maintainAspectRatio:false, interaction:{{mode:'index',intersect:false}}, scales:{{x:{{ticks:{{autoSkip:true,maxTicksLimit:14}}}}, y:{{beginAtZero:true,suggestedMax:3}}}}}} }}); }}
function makeBarChart(id, labels, data, hits) {{ new Chart(document.getElementById(id), {{type:'bar', data:{{labels:labels, datasets:[{{label:'Plan-Treue %', data:data, backgroundColor:data.map(v=>v>=80?'#22c55e':v>=60?'#f59e0b':'#ef4444')}}]}}, options:{{responsive:true, maintainAspectRatio:false, plugins:{{tooltip:{{callbacks:{{afterLabel:(ctx)=>'Hinweise: '+hits[ctx.dataIndex]}}}}}}, scales:{{y:{{min:0,max:100}},x:{{ticks:{{autoSkip:true,maxTicksLimit:18}}}}}}}} }}); }}
function makeNutritionCorrelationChart(id, labels, histamine, symptoms) {{ new Chart(document.getElementById(id), {{type:'line', data:{{labels:labels, datasets:[{{label:'Histamin-Load', data:histamine, borderColor:'#ef4444', backgroundColor:'rgba(239,68,68,.08)', yAxisID:'y', tension:.25}},{{label:'Symptomscore', data:symptoms, borderColor:'#7c3aed', backgroundColor:'rgba(124,58,237,.08)', yAxisID:'y1', tension:.25}}]}}, options:{{responsive:true, maintainAspectRatio:false, interaction:{{mode:'index',intersect:false}}, scales:{{y:{{type:'linear',position:'left'}},y1:{{type:'linear',position:'right',grid:{{drawOnChartArea:false}}}},x:{{ticks:{{autoSkip:true,maxTicksLimit:20}}}}}}}} }}); }}
function filterDashboard(q) {{
  q=(q||'').toLowerCase().trim();
  document.querySelectorAll('.search-section').forEach(el=>{{
    const text=(el.getAttribute('data-search')||el.innerText||'').toLowerCase();
    const show=!q || text.includes(q);
    el.style.display=show?'':'none';
    if(q && show) {{ let cur=el; while(cur) {{ if(cur.tagName && cur.tagName.toLowerCase()==='details') cur.open=true; cur=cur.parentElement; }} }}
  }});
  document.querySelectorAll('table').forEach(tbl=>{{
    let any=false;
    tbl.querySelectorAll('tr').forEach((tr,idx)=>{{
      const isHead=idx===0 || tr.querySelector('th');
      const match=!q || (tr.innerText||'').toLowerCase().includes(q) || isHead;
      tr.style.display=match?'':'none';
      if(match && !isHead) any=true;
    }});
    if(q && any) {{ let cur=tbl.parentElement; while(cur) {{ if(cur.tagName && cur.tagName.toLowerCase()==='details') cur.open=true; cur=cur.parentElement; }} }}
  }});
}}
function openSection(id) {{ const el=document.getElementById(id); if(!el) return; if(el.tagName.toLowerCase()==='details') el.open=true; el.scrollIntoView({{behavior:'smooth',block:'start'}}); }}
</script>
<style>
:root{{--blue:#1f4e78;--bg:#f6f8fb;--text:#1f2937;--muted:#6b7280;--line:#e5e7eb}}
*{{box-sizing:border-box}}body{{font-family:-apple-system,BlinkMacSystemFont,'Segoe UI',Arial,sans-serif;margin:18px;background:var(--bg);color:var(--text);font-size:15px;line-height:1.35}}a{{color:var(--blue)}}h1{{font-size:1.55rem;margin:.2rem 0}}h2{{font-size:1.25rem;margin:1.25rem 0 .6rem}}.grid{{display:grid;grid-template-columns:repeat(auto-fit,minmax(150px,1fr));gap:10px}}.card,.chart-card,.day-card{{background:white;border-radius:14px;padding:14px;box-shadow:0 2px 8px #0001;margin-bottom:14px}}.num{{font-size:26px;font-weight:700;color:var(--blue)}}table{{border-collapse:collapse;width:100%;background:white;margin-bottom:14px;display:block;overflow-x:auto;-webkit-overflow-scrolling:touch}}td,th{{border:1px solid #ddd;padding:6px;vertical-align:top;white-space:nowrap}}code{{white-space:pre-wrap;overflow-wrap:anywhere;word-break:break-word}}th{{background:var(--blue);color:white}}canvas{{background:white;border-radius:12px;margin-bottom:12px;width:100%!important;max-height:330px}}.muted{{color:var(--muted);font-size:.9em;margin-bottom:8px}}.warn{{background:#fff7ed;border-left:4px solid #f97316;padding:10px;border-radius:8px}}.button{{display:inline-block;background:var(--blue);color:white!important;text-decoration:none;padding:10px 14px;border-radius:8px;margin:4px 8px 4px 0}}.pill{{display:inline-flex;align-items:center;gap:3px;border-radius:999px;padding:3px 8px;font-size:.82em;font-weight:700;white-space:nowrap}}.green{{background:#dcfce7;color:#166534}}.yellow{{background:#fef9c3;color:#854d0e}}.orange{{background:#ffedd5;color:#9a3412}}.red{{background:#fee2e2;color:#991b1b}}.unknown{{background:#e5e7eb;color:#374151}}.day-list{{display:grid;grid-template-columns:1fr;gap:10px}}.day-card{{padding:0;overflow:hidden}}.day-summary{{display:grid;grid-template-columns:1.25fr auto auto auto auto;gap:8px;align-items:center;cursor:pointer;padding:14px}}.day-summary::-webkit-details-marker{{display:none}}.day-summary:before{{content:'▸';font-size:14px;color:var(--muted)}}.day-card[open]>.day-summary:before{{content:'▾'}}.day-date{{font-weight:800}}.day-kcal{{font-weight:800;color:var(--blue)}}.day-load,.day-items{{color:var(--muted);font-size:.9em}}.day-detail{{border-top:1px solid var(--line);padding:12px 14px}}.day-macro{{font-weight:600;margin-bottom:10px;color:#374151}}.meal-card{{border:1px solid var(--line);border-radius:12px;padding:12px;margin:10px 0;background:#fbfdff}}.meal-head{{display:flex;justify-content:space-between;align-items:center;gap:8px}}.meal-head h4{{margin:0;font-size:1rem}}.macroline{{margin:6px 0 8px;color:#374151}}.food-list{{display:grid;gap:6px}}.food-row{{display:grid;grid-template-columns:24px minmax(0,1fr) 52px 60px;grid-template-areas:'h name amount kcal' '. name macro macro';gap:2px 8px;align-items:center;padding:8px;border:1px solid var(--line);border-radius:10px;background:white}}.food-h{{grid-area:h}}.food-name{{grid-area:name;font-weight:650;min-width:0;overflow-wrap:anywhere}}.food-name small{{display:block;color:var(--muted);font-weight:400}}.food-amount{{grid-area:amount;text-align:right;color:#374151}}.food-kcal{{grid-area:kcal;text-align:right;font-weight:700}}.food-macro{{grid-area:macro;color:var(--muted);font-size:.86em;text-align:right}}.chart-card table,.card table{{font-size:.92em}}.topbar{{position:sticky;top:0;z-index:20;background:rgba(246,248,251,.96);backdrop-filter:blur(8px);padding:8px 0 10px;border-bottom:1px solid var(--line);margin-bottom:12px}}.quicklinks{{display:flex;gap:8px;overflow-x:auto;-webkit-overflow-scrolling:touch;padding:4px 0}}.quicklinks a,.quicklinks button{{white-space:nowrap;border:1px solid var(--line);background:white;color:var(--blue);padding:8px 10px;border-radius:999px;text-decoration:none;font-weight:700}}.searchbox{{width:100%;border:1px solid var(--line);border-radius:12px;padding:11px 12px;font-size:16px;background:white}}.kpi-grid{{display:grid;grid-template-columns:repeat(auto-fit,minmax(160px,1fr));gap:10px}}.mini-card{{background:white;border:1px solid var(--line);border-radius:14px;padding:12px;display:flex;flex-direction:column;gap:5px;min-height:88px}}.mini-card b{{font-size:.95rem}}.mini-card .big{{font-size:1.8rem;font-weight:800;color:var(--blue)}}.mini-card small{{color:var(--muted)}}.section-block{{background:white;border-radius:14px;box-shadow:0 2px 8px #0001;margin:14px 0;overflow:hidden}}.section-block>summary{{cursor:pointer;list-style:none;padding:16px;font-size:1.12rem;font-weight:800;color:var(--blue);border-bottom:1px solid var(--line)}}.section-block>summary::-webkit-details-marker{{display:none}}.section-block>summary:before{{content:'▸ ';color:var(--muted)}}.section-block[open]>summary:before{{content:'▾ '}}.section-body{{padding:14px}}.priority{{border-left:5px solid var(--blue)}}.legacy-note{{font-size:.85em;color:var(--muted)}}.info-list{{display:grid;gap:8px}}.info-row{{border:1px solid var(--line);border-radius:12px;padding:10px;background:#fff;display:grid;gap:3px}}.info-row b{{overflow-wrap:anywhere}}.info-row span{{font-weight:800;color:var(--blue)}}.info-row small{{color:var(--muted);overflow-wrap:anywhere}}
@media (max-width: 700px){{body{{margin:8px;padding-bottom:24px;font-size:14px}}h1{{font-size:1.3rem}}h2{{font-size:1.1rem}}.card,.chart-card,.day-card{{border-radius:12px;margin-bottom:10px}}.grid{{grid-template-columns:repeat(2,minmax(0,1fr));gap:8px}}.num{{font-size:22px}}.day-summary{{grid-template-columns:18px 1fr auto;grid-template-areas:'arrow date kcal' 'arrow pill load' 'arrow items items';gap:4px 8px;padding:12px}}.day-summary:before{{grid-area:arrow}}.day-date{{grid-area:date}}.day-summary .pill{{grid-area:pill;justify-self:start}}.day-kcal{{grid-area:kcal}}.day-load{{grid-area:load}}.day-items{{grid-area:items}}.day-detail{{padding:10px}}.meal-card{{padding:10px}}.food-row{{grid-template-columns:22px minmax(0,1fr) 54px;grid-template-areas:'h name kcal' '. name amount' '. macro macro';gap:2px 6px}}.food-amount,.food-kcal,.food-macro{{text-align:right}}.chart-card{{overflow:hidden}}table{{font-size:12px;max-width:100%}}td,th{{padding:5px}}canvas{{max-height:260px}}.button{{width:100%;text-align:center}}
</style></head><body>
<h1>JARVIS Health Dashboard</h1><p class='muted'>Generiert: {now}</p>
<div class='topbar'>
  <input id='dashSearch' class='searchbox' type='search' placeholder='Suche: Labor, PDF, Hyrimoz, CRP, Symptom, Lebensmittel…' oninput='filterDashboard(this.value)'>
  <nav class='quicklinks'>
    <a href='#overview'>Übersicht</a><a href='#arzt'>Arzttermin</a><a href='#warnung'>Warnung</a><a href='#ernaehrung'>Ernährung</a><a href='#dokumente'>Dokumente</a><a href='#symptome'>Symptome</a><a href='#timeline'>Timeline</a><a href='#trends'>Trends</a><a href='#system'>System</a>
  </nav>
</div>

<section id='overview' class='search-section priority' data-search='übersicht status warnung dokumente symptome ernährung medikation arzttermin'>
  <h2>Relevante Übersicht</h2>
  <div class='kpi-grid'>{top_kpi_cards}</div>
  <div class='chart-card'><h3>Aktuelle Hinweise</h3><div class='kpi-grid'>{warning_cards}</div></div>
</section>

<details id='arzt' class='section-block search-section' open data-search='arzttermin konsulation labor medikation dokumente symptome fragen pdf'>
  <summary>Arzttermin-Modus</summary>
  <div class='section-body'>
    <div class='grid'>
      <div class='card'><h3>Schlüssellabore</h3><div class='info-list'>{lab_latest_cards}</div></div>
      <div class='card'><h3>Medikation</h3><div class='info-list'>{medication_brief_cards}</div></div>
    </div>
    <div class='grid'>
      <div class='card'><h3>Relevante Dokumente</h3><ul>{appointment_doc_rows}</ul></div>
      <div class='card'><h3>Letzte Symptome</h3><div class='info-list'>{recent_sym_cards}</div></div>
    </div>
    <div class='card'><h3>Arztbericht PDF</h3><p>{pdf_link}{drive_link}</p><p class='muted'>Aktualisieren: <code>python3 {BASE}/scripts/generate_doctor_report.py && python3 {BASE}/scripts/health_pipeline.py dashboard</code></p></div>
  </div>
</details>

<details id='warnung' class='section-block search-section' open data-search='warnlogik warnung crp d-dimer dokumente symptome risiko'>
  <summary>Warnlogik & To-dos</summary>
  <div class='section-body'>
    <section class='chart-card'><div class='muted'>Automatische Hinweise unterstützen Datenprüfung und Arztvorbereitung. Keine erkannten Hinweise schließen medizinische Risiken nicht aus und sind keine Diagnose.</div><table><tr><th>Level</th><th>Hinweis</th><th>Details</th></tr>{warning_rows}</table></section>
    <section class='chart-card'><h3>Nächste sinnvolle Aktionen</h3><table><tr><th>Prio</th><th>Empfehlung</th><th>Warum</th><th>Nächster Schritt</th></tr>{recommendation_rows}</table></section>
  </div>
</details>

<details id='ernaehrung' class='section-block search-section' open data-search='ernährung yazio histamin lebensmittel kcal protein kohlenhydrate fett low histamine'>
  <summary>Ernährung & Histamin</summary>
  <div class='section-body'>
    <div class='warn'><b>Hinweis:</b> Histamin-Ampeln nutzen lokale SIGHi-inspirierte Regeln plus Alias-Mapping; rot nur bei hoher Gesamtlast.</div>
    <div class='day-list'>{meal_explorer_html}</div>
    <details class='section-block search-section' data-search='histamin chart symptomscore korrelation'><summary>Charts & Korrelationen</summary><div class='section-body'><section class='chart-card'><h3>Histamin-Load vs. Symptomscore</h3><div class='muted'>Explorativ, keine Kausalitätsbehauptung.</div><canvas id='nutrition_correlation' height='120'></canvas></section><script>makeNutritionCorrelationChart('nutrition_correlation', {json.dumps(nutrition_v2_labels)}, {json.dumps(nutrition_v2_histamine)}, {json.dumps(nutrition_v2_symptom)});</script><section class='chart-card'><h3>Lag-Korrelationen Ernährung → Symptome</h3><table><tr><th>Metrik</th><th>Lag</th><th>n</th><th>r</th><th>Interpretation</th></tr>{correlation_rows}</table></section></div></details>
    <details class='section-block search-section' data-search='safe trigger personal tolerance lebensmittel'><summary>Safe-/Trigger & Personal Tolerance</summary><div class='section-body'><section class='chart-card'><h3>Safe-/Trigger-Kandidaten</h3><table><tr><th>Lebensmittelgruppe</th><th>Typ</th><th>Tage</th><th>Symptom +1</th><th>Symptom +1..+3</th><th>Histamin Ø</th><th>Confidence</th><th>Notiz</th></tr>{food_insight_rows}</table></section><section class='chart-card'><h3>Personal Tolerance Layer</h3><table><tr><th>Lebensmittelgruppe</th><th>Status</th><th>Evidenz</th><th>Notiz</th><th>Aktualisiert</th></tr>{tolerance_rows}</table></section></div></details>
    <details class='section-block search-section' data-search='low histamine experiment baseline challenge'><summary>Low-Histamine Experiment Tracker</summary><div class='section-body'><table><tr><th>ID</th><th>Start</th><th>Ende</th><th>Phase</th><th>Challenge</th><th>Status</th><th>Hypothese/Notiz</th></tr>{experiment_rows}</table><div class='muted'>CLI: <code>python3 {BASE}/scripts/low_histamine_experiment.py start</code> oder <code>challenge --food "..."</code></div></div></details>
    <details class='section-block search-section' data-search='mapping review queue unbekannt'><summary>Mapping Review Queue</summary><div class='section-body'><table><tr><th>Beispielprodukt</th><th>Vorkommen</th><th>Erstmals</th><th>Zuletzt</th><th>Grund</th></tr>{review_rows}</table></div></details>
  </div>
</details>

<details id='dokumente' class='section-block search-section' data-search='dokumente pdf berichte labor arztbrief inbox review'>
  <summary>Dokumente & PDFs</summary>
  <div class='section-body'>
    <section class='chart-card'><h3>Aktuelle Dokumente</h3><div class='muted'>Links öffnen über Tailscale-Route.</div><table><tr><th>ID</th><th>Dokument</th><th>Datum</th><th>Kategorie</th><th>Institution</th><th>Review</th></tr>{doc_view_rows}</table></section>
    <section class='chart-card'><h3>Dokumenten-Inbox Review</h3><table><tr><th>Review</th><th>Kategorie</th><th>Anzahl</th></tr>{doc_review_rows}</table><div class='muted'>CLI: <code>python3 {BASE}/scripts/health_doc_review.py ID --status arzttermin|relevant|archiviert</code></div></section>
  </div>
</details>

<details id='symptome' class='section-block search-section' data-search='symptome aphthen gi fatigue haut augen gelenke quick log'>
  <summary>Symptome</summary>
  <div class='section-body'>{symptom_chart}<section class='chart-card'><h3>Daily Symptom Quick Log</h3><div class='muted'>Format: <code>SYM YYYY-MM-DD aphthen=0 gi=1 fatigue=2 skin=0 eyes=0 joints=0 vascular=0 notes=...</code></div><table><tr><th>Dimension</th><th>Skala</th><th>Warum</th></tr><tr><td>Aphthen, GI, Fatigue, Haut, Augen, Gelenke, vaskulär</td><td>0 keine · 1 leicht · 2 mittel · 3 schwer</td><td>Verbessert Lag-Korrelationen.</td></tr></table></section></div>
</details>

<details id='timeline' class='section-block search-section' data-search='timeline ereignisse medikation labor symptome ernährung'>
  <summary>Health Timeline</summary>
  <div class='section-body'><table><tr><th>Datum</th><th>Typ</th><th>Eintrag</th></tr>{timeline_rows}</table></div>
</details>

<details id='trends' class='section-block search-section' data-search='trends apple health labor crp d-dimer hrv schlaf puls spo2'>
  <summary>Trends, Labor & Apple Health</summary>
  <div class='section-body'>
    <details class='section-block'><summary>Hyrimoz-Phasenvergleich</summary><div class='section-body'><table><tr><th>Phase</th><th>Zeitraum</th><th>Tage</th><th>Histamin Ø</th><th>Symptome Ø</th><th>kcal Ø</th><th>Protein Ø</th><th>Notiz</th></tr>{phase_rows}</table></div></details>
    <details class='section-block'><summary>Apple Health Trends</summary><div class='section-body'><table><tr><th>Metrik</th><th>Zeitraum</th><th>Records</th><th>letzter Wert</th><th>Ø</th><th>Min</th><th>Max</th></tr>{''.join(apple_rows)}</table>{''.join(apple_charts) or '<p>Keine Apple-Health-Daten verfügbar.</p>'}</div></details>
    <details class='section-block' open><summary>Alle Laborwerte nach Thema</summary><div class='section-body'><div class='warn'><b>Provenienz-Regel:</b> Kanonische Referenz ist der eingescannte Original-Laborbericht des jeweiligen Untersuches — auch für Referenzbereiche. Die XLSX ist nur sekundäre Arbeitsübersicht und kann Fehler enthalten.</div><p class='muted'>{lab_param_count} Parameter · {lab_value_count} Einzelwerte aus der XLSX-Arbeitsmatrix. Kategorien sind aufklappbar und suchbar; klinische Verifikation immer gegen Originalscan.</p>{all_lab_category_html}</div></details>
    <details class='section-block'><summary>Labortrend-Charts Auswahl</summary><div class='section-body'>{''.join(charts) or '<p>Keine Labortrends verfügbar.</p>'}</div></details>
  </div>
</details>

<details id='system' class='section-block search-section' data-search='system status archiv legacy dokumente labor events'>
  <summary>System, Archiv & Legacy-Listen</summary>
  <div class='section-body'>
    <p class='legacy-note'>Technische/alte Listen — nützlich für Debugging, aber nicht primär für die tägliche Ansicht.</p>
    <details class='section-block'><summary>Systemstatus</summary><div class='section-body'><div class='grid'>{cards}</div></div></details>
    <details class='section-block'><summary>Medikations-Timeline Rohdaten</summary><div class='section-body'><table><tr><th>Datum</th><th>Medikament</th><th>Ereignis</th><th>Dosis</th><th>Nächste geplant</th><th>Notiz</th></tr>{medication_rows}</table></div></details>
    <details class='section-block'><summary>Ernährungsplan-Konsequenz Legacy</summary><div class='section-body'><section class='chart-card'><canvas id='nutrition_adherence' height='110'></canvas></section><script>makeBarChart('nutrition_adherence', {json.dumps(nf_labels)}, {json.dumps(nf_scores)}, {json.dumps(nf_hits, ensure_ascii=False)});</script><table><tr><th>Datum</th><th>Score</th><th>erkannte Risiken</th><th>positive Treffer</th></tr>{nutrition_rows}</table></div></details>
    <details class='section-block'><summary>Health Event Periods</summary><div class='section-body'><table><tr><th>Start</th><th>Ende</th><th>Typ</th><th>Label</th><th>Schweregrad</th><th>Notizen</th></tr>{period_rows}</table></div></details>
    <details class='section-block'><summary>Laborberichte / Original-PDFs</summary><div class='section-body'><table><tr><th>ID</th><th>Original</th><th>Status</th></tr>{lab_doc_rows}</table></div></details>
    <details class='section-block'><summary>Klinische Events, Medikamente & Symptome</summary><div class='section-body'><table><tr><th>Datum</th><th>Kategorie</th><th>Parameter/Symptom</th><th>Wert/Notiz</th></tr>{event_rows}{symptom_rows}</table></div></details>
    <details class='section-block'><summary>Letzte Dokumente Legacy</summary><div class='section-body'><table><tr><th>ID</th><th>Datei</th><th>Kategorie</th><th>Status</th><th>Review</th><th>Volltext-Zeichen</th></tr>{doc_rows}</table></div></details>
  </div>
</details>
</body></html>"""
    out.write_text(html_text, encoding="utf-8")
    c = conn(); c.execute("INSERT INTO report_runs(report_type, output_path, summary) VALUES(?,?,?)", ("dashboard_v31", str(out), "Dashboard v3.1 mit Apple Health generiert")); c.commit(); c.close()
    print(out)

if __name__ == "__main__":
    main()