"""Deterministic, local-only Sprint 6I-C document reconciliation.

Technical preparation may classify and link records, but never marks medical content as
original-reviewed and never writes canonical medical tables.
"""
from __future__ import annotations
import difflib,hashlib,json,re,sqlite3,unicodedata
from collections import Counter
from datetime import datetime
from decimal import Decimal,InvalidOperation
from typing import Any
from zoneinfo import ZoneInfo

from .sprint6i_c_schema import apply_schema

def normalize_parameter(value: Any) -> str:
    text=unicodedata.normalize("NFKD"," ".join(str(value or "").strip().split()).casefold())
    return re.sub(r"[^a-z0-9]+","_","".join(char for char in text if not unicodedata.combining(char))).strip("_")

def normalize_unit(value: Any) -> str|None:
    text=" ".join(str(value or "").strip().split()).replace("μ","µ").replace(" ","").casefold()
    return text or None

def normalize_value(value: Any) -> str|None:
    text=" ".join(str(value or "").strip().split()).replace("'","").replace(" ","").replace(",",".")
    match=re.fullmatch(r"([<>]=?|=)?([-+]?\d+(?:\.\d+)?)",text)
    if not match:return None
    try:number=Decimal(match.group(2)).normalize()
    except InvalidOperation:return None
    return f"{match.group(1) or '='}{format(number,'f')}"
try:
    from .lab_registry import LAB_ALLOWLIST
except ImportError:
    from lab_registry import LAB_ALLOWLIST

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


def canonical_lab_pair(parameter: Any, unit: Any) -> tuple[str, str]:
    """Return an allowlisted public pair, or a conservative normalized fallback."""
    public = LAB_ALLOWLIST.get(_lab_key(parameter, unit))
    if public:
        return public
    return normalize_parameter(parameter), normalize_unit(unit) or ""

TZ=ZoneInfo("Europe/Zurich")
LAB_VALUE_RE=re.compile(r"^\s*(?P<name>[^:]{1,100})\s*:\s*(?P<value>[<>≤≥]?\s*[-+]?\d+(?:[.,]\d+)?)\s*$")
EXPLICIT_ADMIN_RE=re.compile(r"\b(verabreicht|eingenommen|appliziert|gegeben)\b",re.I)


def _now() -> str:
    return datetime.now(TZ).isoformat(timespec="seconds")


def _digest(value: Any) -> str:
    return hashlib.sha256(json.dumps(value,ensure_ascii=False,sort_keys=True,separators=(",",":"),default=str).encode()).hexdigest()


def _canonical_parameter(name: str,unit: str|None) -> str:
    public=LAB_ALLOWLIST.get(_lab_key(name,unit))
    return normalize_parameter(public[0] if public else name)


def _lab_candidate(value_text: str,unit: Any) -> tuple[str,str|None,str|None]|None:
    match=LAB_VALUE_RE.match(value_text)
    if not match:return None
    value=normalize_value(match.group("value").replace("≤","<=").replace("≥",">="))
    if value is None:return None
    return _canonical_parameter(match.group("name"),unit),value,normalize_unit(unit)


def _lab_equivalence(record:dict[str,Any],value:str|None,unit:str|None) -> str|None:
    if record["value"]==value and record["unit"]==unit:return "exact"
    factors={("g/l","mg/dl"):Decimal("100"),("mg/dl","g/l"):Decimal("0.01")}
    factor=factors.get((str(unit),str(record["unit"])))
    if factor is None or not value or not record["value"] or not value.startswith("=") or not str(record["value"]).startswith("="):return None
    try:
        converted=(Decimal(value[1:])*factor).normalize();existing=Decimal(str(record["value"])[1:]).normalize()
    except InvalidOperation:return None
    return "format" if converted==existing else None


def _database_labs(connection: sqlite3.Connection) -> list[dict[str,Any]]:
    if connection.execute("SELECT 1 FROM sqlite_master WHERE type='table' AND name='laborwerte'").fetchone() is None:return []
    rows=[]
    for row in connection.execute("SELECT id,parameter_name,wert,einheit,abnahme_datum,befund_datum,ermittlung_datum FROM laborwerte"):
        rows.append({"id":int(row[0]),"parameter":_canonical_parameter(str(row[1] or ""),row[3]),"value":normalize_value(row[2]),"unit":normalize_unit(row[3]),"date":str(row[4] or row[5] or row[6] or "")[:10] or None})
    return rows


def _workbook_labs(records: list[dict[str,Any]]|None) -> list[dict[str,Any]]:
    result=[]
    for row in records or []:
        result.append({"parameter":normalize_parameter(row.get("parameter")),"value":normalize_value(row.get("value")),"unit":normalize_unit(row.get("unit")),"date":str(row.get("date") or "")[:10] or None})
    return result


def _candidate_key(row: sqlite3.Row) -> tuple[str,str,str]:
    return str(row["candidate_type"])," ".join(str(row["value_text"]).casefold().split()),normalize_unit(row["unit"]) or ""


def changed_fragments(left: str,right: str,limit: int=12) -> list[dict[str,str]]:
    """Return bounded changed token spans only; identical context is omitted."""
    old=left.split();new=right.split();result=[]
    for tag,i1,i2,j1,j2 in difflib.SequenceMatcher(a=old,b=new,autojunk=False).get_opcodes():
        if tag=="equal":continue
        result.append({"change":tag,"previous":" ".join(old[i1:i2])[:240],"current":" ".join(new[j1:j2])[:240]})
        if len(result)>=limit:break
    return result


def _prepare_sections(connection: sqlite3.Connection,now: str) -> tuple[int,dict[int,set[int]]]:
    connection.execute("DELETE FROM document_section_relations")
    pages=connection.execute("""SELECT p.document_id,p.page_number,p.normalized_text,p.section_hash
      FROM document_pages p WHERE p.text_version=(SELECT MAX(version) FROM document_text_versions WHERE document_id=p.document_id)
      ORDER BY p.document_id,p.page_number""").fetchall()
    prior=[];identical=0;repeated_pages:dict[int,set[int]]={}
    for page in pages:
        document_id,page_no,text,section_hash=int(page[0]),int(page[1]),str(page[2]),str(page[3])
        exact=next((old for old in reversed(prior) if old[3]==section_hash),None)
        best=None;score=0.0
        if exact is not None:best=exact;score=1.0
        else:
            current=set(text.casefold().split())
            for old in prior:
                previous=set(str(old[2]).casefold().split())
                similarity=len(current&previous)/len(current|previous) if current and previous else 0.0
                if similarity>score:score,best=similarity,old
        if best is not None and score>=.92:
            relation="identical" if exact is not None else "near_match"
            identity="section_relation_"+hashlib.sha256(f"{document_id}|{page_no}|{best[0]}|{best[1]}".encode()).hexdigest()[:24]
            connection.execute("INSERT INTO document_section_relations(id,document_id,page_number,compared_document_id,compared_page_number,relation,similarity,section_hash,created_at) VALUES(?,?,?,?,?,?,?,?,?)",(identity,document_id,page_no,int(best[0]),int(best[1]),relation,score,section_hash,now))
            connection.execute("UPDATE document_pages SET repetition_status=?,compared_document_id=? WHERE document_id=? AND page_number=? AND text_version=(SELECT MAX(version) FROM document_text_versions WHERE document_id=?)",(relation,int(best[0]),document_id,page_no,document_id))
            if relation=="identical":identical+=1;repeated_pages.setdefault(document_id,set()).add(page_no)
        prior.append((document_id,page_no,text,section_hash))
    return identical,repeated_pages


def _reference_by_lab(candidates:list[sqlite3.Row]) -> tuple[dict[str,str],set[str]]:
    references=[row for row in candidates if row["candidate_type"]=="reference_range"]
    linked={};support=set()
    for lab in (row for row in candidates if row["candidate_type"]=="laboratory_value"):
        same=[row for row in references if int(row["page_number"])==int(lab["page_number"])]
        if not same:continue
        nearest=min(same,key=lambda row:abs(int(row["section_number"])-int(lab["section_number"])))
        linked[str(lab["id"])]=str(nearest["value_text"])+(f" {nearest['unit']}" if nearest["unit"] else "")
        support.add(str(nearest["id"]))
    return linked,support


def reconcile_documents(connection:sqlite3.Connection,workbook_records:list[dict[str,Any]]|None=None,*,now:str|None=None,document_ids:list[int]|None=None,ensure_schema:bool=True) -> dict[str,int]:
    """Additively prepare documents and return aggregate technical counters only."""
    if ensure_schema:apply_schema(connection)
    connection.row_factory=sqlite3.Row;stamp=now or _now()
    for candidate in connection.execute("SELECT id,candidate_type,value_text,unit,page_number,section_number,context_text FROM document_candidates WHERE candidate_fingerprint IS NULL OR candidate_fingerprint='' ").fetchall():
        fingerprint=hashlib.sha256(f"{candidate['candidate_type']}|{candidate['value_text']}|{candidate['unit']}|{candidate['page_number']}|{candidate['section_number']}|{' '.join(str(candidate['context_text']).split())}".encode()).hexdigest()
        connection.execute("UPDATE document_candidates SET candidate_fingerprint=? WHERE id=?",(fingerprint,str(candidate["id"])))
    db_labs=_database_labs(connection);workbook=_workbook_labs(workbook_records)
    identical,repeated_page_map=_prepare_sections(connection,stamp)
    if document_ids:
        placeholders=','.join('?' for _ in document_ids)
        identical=int(connection.execute(f"SELECT COUNT(*) FROM document_section_relations WHERE relation='identical' AND document_id IN ({placeholders})",document_ids).fetchone()[0])
    document_sql="""SELECT d.id,d.document_date,d.review_status,p.original_status,p.extraction_status,p.content_status,p.sha256,p.duplicate_document_id
        FROM dokumente d JOIN document_processing p ON p.document_id=d.id"""
    document_parameters:list[Any]=[]
    if document_ids:
        document_sql+=f" WHERE d.id IN ({','.join('?' for _ in document_ids)})";document_parameters=[int(item) for item in document_ids]
    document_sql+=" ORDER BY d.id"
    documents=connection.execute(document_sql,document_parameters).fetchall()
    seen_sha:dict[str,int]={};counts=Counter();total_decisions=0
    for document in documents:
        document_id=int(document["id"]);sha=str(document["sha256"] or "");duplicate_of=(int(document["duplicate_document_id"]) if document["duplicate_document_id"] is not None else seen_sha.get(sha) if sha else None)
        if sha and duplicate_of is None:seen_sha[sha]=document_id
        if duplicate_of is not None:
            connection.execute("UPDATE document_processing SET duplicate_document_id=? WHERE document_id=?",(duplicate_of,document_id));counts["byte_duplicates"]+=1
        candidates=connection.execute("SELECT * FROM document_candidates WHERE document_id=? AND source_text_version=(SELECT MAX(version) FROM document_text_versions WHERE document_id=?) ORDER BY page_number,section_number,id",(document_id,document_id)).fetchall()
        references,supporting=_reference_by_lab(candidates);exact_count=0;conflict_count=0
        previous_keys={_candidate_key(row) for row in connection.execute("SELECT c.candidate_type,c.value_text,c.unit FROM document_candidates c WHERE c.document_id<? AND c.source_text_version=(SELECT MAX(v.version) FROM document_text_versions v WHERE v.document_id=c.document_id)",(document_id,))}
        for candidate in candidates:
            candidate_id=str(candidate["id"]);kind=str(candidate["candidate_type"]);status="non_transferable";area="document_information";parameter=value=unit=None;day=str(document["document_date"] or "")[:10] or None;db_matches=[];wb_matches=[]
            key=_candidate_key(candidate)
            if candidate_id in supporting:
                status="supporting_reference";area="laboratory"
            elif int(candidate["page_number"]) in repeated_page_map.get(document_id,set()) or key in previous_keys and kind in {"diagnosis","symptom","medication","appointment","important_event"}:
                status="repeated_exact";area="document_information";exact_count+=1
            elif kind=="laboratory_value":
                area="laboratory";parsed=_lab_candidate(str(candidate["value_text"]),candidate["unit"])
                if parsed is None:status="ambiguous"
                else:
                    parameter,value,unit=parsed
                    db_matches=[row for row in db_labs if row["parameter"]==parameter and row["date"]==day]
                    wb_matches=[row for row in workbook if row["parameter"]==parameter and row["date"]==day]
                    db_equivalence=[(row,_lab_equivalence(row,value,unit)) for row in db_matches];wb_equivalence=[(row,_lab_equivalence(row,value,unit)) for row in wb_matches]
                    db_exact=[row for row,equivalence in db_equivalence if equivalence=="exact"]
                    db_format=[row for row,equivalence in db_equivalence if equivalence=="format"]
                    wb_exact=[row for row,equivalence in wb_equivalence if equivalence=="exact"]
                    wb_format=[row for row,equivalence in wb_equivalence if equivalence=="format"]
                    any_conflict=any(equivalence is None for _,equivalence in [*db_equivalence,*wb_equivalence])
                    if db_exact:status="exact_match";exact_count+=1
                    elif db_format:status="format_unit_match";exact_count+=1
                    elif any_conflict:status="value_conflict";conflict_count+=1
                    elif wb_exact or wb_format:status="not_present"
                    elif db_matches or wb_matches:status="ambiguous";conflict_count+=1
                    else:status="not_present"
            elif kind in {"document_date","institution"}:
                area="metadata";status="exact_match";exact_count+=1
            elif kind=="appointment":area="appointment";status="not_present"
            elif kind=="medication":area="medication";status="not_present" if EXPLICIT_ADMIN_RE.search(str(candidate["context_text"])) else "non_transferable"
            digest=_digest({"candidate":candidate_id,"status":status,"parameter":parameter,"date":day,"value":value,"unit":unit,"db":[row["id"] for row in db_matches],"workbook":len(wb_matches),"reference":references.get(candidate_id)})
            connection.execute("""INSERT INTO document_candidate_matches(candidate_id,match_status,target_area,normalized_parameter,normalized_date,normalized_value,normalized_unit,reference_text,database_match_count,workbook_match_count,existing_database_value,existing_workbook_value,comparison_digest,compared_at)
              VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?) ON CONFLICT(candidate_id) DO UPDATE SET match_status=excluded.match_status,target_area=excluded.target_area,normalized_parameter=excluded.normalized_parameter,normalized_date=excluded.normalized_date,normalized_value=excluded.normalized_value,normalized_unit=excluded.normalized_unit,reference_text=excluded.reference_text,database_match_count=excluded.database_match_count,workbook_match_count=excluded.workbook_match_count,existing_database_value=excluded.existing_database_value,existing_workbook_value=excluded.existing_workbook_value,comparison_digest=excluded.comparison_digest,compared_at=excluded.compared_at""",(candidate_id,status,area,parameter,day,value,unit,references.get(candidate_id),len(db_matches),len(wb_matches),db_matches[0]["value"] if db_matches else None,wb_matches[0]["value"] if wb_matches else None,digest,stamp))
            if status in {"exact_match","format_unit_match","repeated_exact","supporting_reference"} and candidate["status"] in {"open","conflicting"}:
                connection.execute("UPDATE document_candidates SET status='already_present',reviewed_at=?,candidate_revision=candidate_revision+1 WHERE id=?",(stamp,candidate_id))
            elif status in {"value_conflict","ambiguous"} and candidate["status"]=="open":
                connection.execute("UPDATE document_candidates SET status='conflicting',candidate_revision=candidate_revision+1 WHERE id=?",(candidate_id,))
        unresolved=int(connection.execute("SELECT COUNT(*) FROM document_candidates WHERE document_id=? AND source_text_version=(SELECT MAX(version) FROM document_text_versions WHERE document_id=?) AND status IN ('open','conflicting')",(document_id,document_id)).fetchone()[0])
        staging=int(connection.execute("SELECT COUNT(*) FROM document_transfer_staging WHERE document_id=? AND status IN ('reviewed_pending_preview','ready_for_transfer')",(document_id,)).fetchone()[0])
        content_decision=1 if document["content_status"] not in {"reviewed","discarded"} else 0
        decisions=unresolved+staging+content_decision
        original=str(document["original_status"]);extraction=str(document["extraction_status"])
        if duplicate_of is not None:bucket,reason,priority,decisions="no_action","byte_duplicate",0,0
        elif original=="missing":bucket,reason,priority,decisions="original_missing","original_missing",10,0
        elif original in {"blocked","unsupported"} or extraction in {"failed"}:bucket,reason,priority,decisions="technical_blocked","technical_failure",90,0
        elif extraction=="not_started":bucket,reason,priority,decisions="automatically_prepared","processing_pending",20,0
        elif document["content_status"] in {"reviewed","discarded"} and not staging:bucket,reason,priority,decisions="no_action","completed",0,0
        else:
            bucket,reason="now_reviewable","conflict" if conflict_count else "decision_required"
            priority=100 if conflict_count else 80 if any(row["candidate_type"]=="laboratory_value" for row in candidates) else 60
        connection.execute("""INSERT INTO document_reconciliation(document_id,queue_bucket,reason_code,priority,open_decisions,byte_duplicate_of,exact_match_count,conflict_count,prepared_at)
          VALUES(?,?,?,?,?,?,?,?,?) ON CONFLICT(document_id) DO UPDATE SET queue_bucket=excluded.queue_bucket,reason_code=excluded.reason_code,priority=excluded.priority,open_decisions=excluded.open_decisions,byte_duplicate_of=excluded.byte_duplicate_of,exact_match_count=excluded.exact_match_count,conflict_count=excluded.conflict_count,prepared_at=excluded.prepared_at""",(document_id,bucket,reason,priority,decisions,duplicate_of,exact_count,conflict_count,stamp))
        counts[bucket]+=1
        if duplicate_of is not None:counts["byte_duplicates"]+=1
        counts["exact_matches"]+=exact_count;counts["conflicting_candidates"]+=conflict_count;total_decisions+=decisions
    counts["prepared_documents"]=len(documents);counts["identical_repetitions"]=identical;counts["user_decisions"]=total_decisions
    aggregate={key:int(counts[key]) for key in ("prepared_documents","now_reviewable","original_missing","technical_blocked","byte_duplicates","identical_repetitions","conflicting_candidates","exact_matches","user_decisions")}
    if not document_ids:
        run_id="reconcile_"+hashlib.sha256((stamp+_digest(aggregate)).encode()).hexdigest()[:24]
        connection.execute("INSERT OR IGNORE INTO document_reconciliation_runs(run_id,prepared_documents,now_reviewable,original_missing,technical_blocked,byte_duplicates,identical_repetitions,conflicting_candidates,exact_matches,user_decisions,aggregate_digest,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)",(run_id,*[aggregate[k] for k in ("prepared_documents","now_reviewable","original_missing","technical_blocked","byte_duplicates","identical_repetitions","conflicting_candidates","exact_matches","user_decisions")],_digest(aggregate),stamp))
    return aggregate


def transfer_preview(connection:sqlite3.Connection,candidate_id:str) -> dict[str,Any]:
    row=connection.execute("""SELECT c.id,c.document_id,c.candidate_type,c.value_text,c.unit,c.page_number,c.context_text,c.corrected_value_text,c.corrected_unit,c.status,c.source_text_version,c.candidate_revision,
      m.match_status,m.target_area,m.normalized_parameter,m.normalized_date,m.normalized_value,m.normalized_unit,m.reference_text,m.existing_database_value,m.existing_workbook_value,p.reconciliation_revision
      FROM document_candidates c JOIN document_candidate_matches m ON m.candidate_id=c.id JOIN document_processing p ON p.document_id=c.document_id WHERE c.id=?""",(candidate_id,)).fetchone()
    if row is None:raise ValueError("candidate_not_reconciled")
    value=str(row["corrected_value_text"] or row["value_text"]);unit=str(row["corrected_unit"] or row["normalized_unit"] or row["unit"] or "")
    target=str(row["target_area"])
    if target not in {"laboratory","medication","appointment"}:raise ValueError("candidate_not_transferable")
    if target=="medication" and not EXPLICIT_ADMIN_RE.search(str(row["context_text"])):raise ValueError("medication_not_documented_as_administered")
    new={"value":value,"unit":unit,"date":row["normalized_date"],"reference":row["reference_text"],"parameter":row["normalized_parameter"],"document_id":int(row["document_id"]),"page":int(row["page_number"])}
    old={"database":row["existing_database_value"],"workbook":row["existing_workbook_value"]}
    operation="link_only" if row["match_status"]=="exact_match" else "create"
    return {"candidate_id":str(row["id"]),"document_id":int(row["document_id"]),"target_area":target,"operation":operation,"old":old,"new":new,"source_page":int(row["page_number"]),"expected_text_version":int(row["source_text_version"]),"expected_candidate_revision":int(row["candidate_revision"]),"expected_reconciliation_revision":int(row["reconciliation_revision"]),"idempotency_key":_digest({"candidate":str(row["id"]),"target":target,"new":new,"text_version":int(row["source_text_version"]),"candidate_revision":int(row["candidate_revision"]),"reconciliation_revision":int(row["reconciliation_revision"])})}