#!/usr/bin/env python3
"""Link validated lab values to likely original lab documents and generate QA report.

Conservative: links only if the document text/filename/date strongly matches the
lab date and parameter. Ambiguous values remain unlinked instead of pretending.
"""
from __future__ import annotations
import json, re, sqlite3
from collections import defaultdict
from datetime import datetime
from pathlib import Path

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

SYN = {
    "C-Reaktives Protein (CRP)": ["crp", "c-reaktives protein", "c-reaktives"],
    "D-Dimer": ["d-dimer", "ddimer", "d dimer"],
    "Faktor VIII": ["faktor viii", "faktor 8"],
    "LDL-Cholesterin": ["ldl", "ldl-cholesterin"],
    "HDL-Cholesterin": ["hdl", "hdl-cholesterin"],
    "Gesamt-Cholesterin": ["cholesterin", "gesamt-cholesterin"],
    "Triglyceride": ["triglyceride"],
    "Leukozyten": ["leukozyten"],
    "Thrombozyten": ["thrombozyten"],
    "Hämoglobin": ["hämoglobin", "haemoglobin", "hemoglobin"],
    "Kreatinin": ["kreatinin"],
    "Ferritin": ["ferritin"],
    "Vitamin D (25-OH)": ["vitamin d", "25-oh", "25oh"],
    "ALAT (GPT)": ["alat", "gpt"],
    "ASAT (GOT)": ["asat", "got"],
    "GGT": ["ggt"],
    "Glukose": ["glukose", "glucose"],
}

def norm(s: str) -> str:
    return re.sub(r"\s+", " ", (s or "").lower())

def date_variants(d: str) -> list[str]:
    y,m,dd = d.split('-')
    return [d, f"{dd}.{m}.{y}", f"{dd}.{int(m)}.{y}", f"{int(dd)}.{int(m)}.{y}", f"{y}{m}{dd}", f"{y[2:]}{m}{dd}"]

def param_terms(p: str) -> list[str]:
    return SYN.get(p, [p.lower(), p.replace('-', ' ').lower()])

def score_doc(doc, lab) -> int:
    hay = norm((doc['datei_name'] or '') + '\n' + (doc['extrahierte_inhalte'] or '') + '\n' + (doc['document_date'] or ''))
    score = 0
    for dv in date_variants(lab['abnahme_datum']):
        if dv.lower() in hay:
            score += 8
            break
    if (doc['datei_name'] or '')[:6] in ''.join(date_variants(lab['abnahme_datum'])):
        score += 3
    for term in param_terms(lab['parameter_name']):
        if norm(term) in hay:
            score += 5
            break
    if 'labor' in norm(doc['kategorie'] or '') or 'labor' in norm(doc['datei_name'] or ''):
        score += 2
    return score

def main():
    con=sqlite3.connect(DB); con.row_factory=sqlite3.Row
    # Reset only validated links from earlier automated runs; keep unvalidated/OCR data untouched.
    con.execute("UPDATE laborwerte SET dokument_id=NULL WHERE validierungsstatus='validiert'")
    docs=[dict(r) for r in con.execute("""
        SELECT id,datei_name,kategorie,document_date,extrahierte_inhalte,drive_web_url,local_original_path
        FROM dokumente
        WHERE length(coalesce(extrahierte_inhalte,''))>100
          AND coalesce(review_status,'') != 'duplicate_candidate'
          AND lower(coalesce(datei_name,'')) LIKE '%.pdf'
          AND (upper(coalesce(kategorie,'')) LIKE '%LABOR%' OR lower(datei_name) LIKE '%labor%' OR lower(extrahierte_inhalte) LIKE '%crp%' OR lower(extrahierte_inhalte) LIKE '%faktor viii%')
    """)]
    labs=[dict(r) for r in con.execute("SELECT id,parameter_name,abnahme_datum,validierungsstatus FROM laborwerte WHERE validierungsstatus='validiert' AND abnahme_datum IS NOT NULL")]
    linked=0; ambiguous=0; unlinked=[]; examples=[]
    for lab in labs:
        scores=sorted([(score_doc(d, lab), d) for d in docs], key=lambda x:x[0], reverse=True)
        best_score,best=scores[0] if scores else (0,None)
        second=scores[1][0] if len(scores)>1 else 0
        if best and best_score>=13 and best_score-second>=2:
            con.execute("UPDATE laborwerte SET dokument_id=?, quelle=COALESCE(quelle,?) WHERE id=?", (best['id'], 'validated_xlsx_linked_to_original_pdf', lab['id']))
            linked += 1
            if len(examples)<20: examples.append({'lab_id':lab['id'],'date':lab['abnahme_datum'],'param':lab['parameter_name'],'doc_id':best['id'],'doc':best['datei_name'],'score':best_score,'second':second})
        elif best and best_score>=13:
            ambiguous += 1
            unlinked.append({'lab_id':lab['id'],'date':lab['abnahme_datum'],'param':lab['parameter_name'],'reason':'ambiguous','best':best['datei_name'],'score':best_score,'second':second})
        else:
            unlinked.append({'lab_id':lab['id'],'date':lab['abnahme_datum'],'param':lab['parameter_name'],'reason':'no_strong_match','score':best_score})
    con.commit()
    by_date=[dict(r) for r in con.execute("SELECT abnahme_datum,count(*) n,count(dokument_id) linked FROM laborwerte WHERE validierungsstatus='validiert' GROUP BY abnahme_datum ORDER BY abnahme_datum")]
    summary={'validated_labs':len(labs),'linked_now':linked,'ambiguous':ambiguous,'still_unlinked':len(unlinked),'by_date':by_date,'examples':examples,'unlinked_sample':unlinked[:50]}
    out=REPORTS/f"lab_document_linkage_{datetime.now():%Y%m%d_%H%M%S}.json"
    out.write_text(json.dumps(summary,ensure_ascii=False,indent=2),encoding='utf-8')
    md=REPORTS/'lab_document_linkage_summary.md'
    md.write_text('# Laborwerte ↔ Originaldokumente Linkage\n\n```json\n'+json.dumps(summary,ensure_ascii=False,indent=2)+'\n```\n',encoding='utf-8')
    print(json.dumps(summary,ensure_ascii=False))
    print(out)
if __name__=='__main__': main()