
    Gj@                    2   d Z ddlmZ ddlZddlZddlZddlZddlZddlZddl	Z	ddl
Z
ddlZddlmZ ddlmZ ddlmZ ddlmZ  ej                    dz  d	z  d
z  Zedz  Zedz  Zedz  Zedz  Ze ej                    d
z   ed          gZ ej        d          Z ej        dej                  Z ej        dej                  ZdBdZdCdZ dDdZ!dEd"Z"dFd$Z#dGdHd(Z$dFd)Z%dId,Z&dJd/Z'dKd1Z(dLd7Z)dMdNd=Z*dOd?Z+dPd@Z,e-dAk    r e,             dS dS )Qa  Batch reprocess all health documents into the JARVIS health DB.

Design:
- Never deletes documents.
- Makes a DB backup before writing.
- Resolves stale paths between /home/agent/Gesundheit and ~/.hermes/assets/Gesundheit.
- Extracts searchable full text from PDFs via PyMuPDF, with optional Docling fallback for weak extraction.
- Extracts XLSX sheet text via openpyxl.
- Stores full text in dokumente.extrahierte_inhalte and lightweight insights in document_insights.
- Marks duplicate candidates conservatively via review_status/processing_quality only.
- Does NOT auto-promote OCR/PDF lab values to confirmed laborwerte. Those belong in staging/4-eyes review.
    )annotationsN)defaultdict)datetime)Path)Iterablez.hermesassets
Gesundheitzhealth_data.dbreportsbackupsextracted_text/home/agent/GesundheitzX(?:(20\d{2})[-./](\d{1,2})[-./](\d{1,2})|(?<!\d)(\d{1,2})[.](\d{1,2})[.](20\d{2})(?!\d))uv   \b(Diagnose(?:n)?|Beurteilung|Befund|Anamnese|Therapie|Medikation|Labor|CRP|D-Dimer|Faktor\s*VIII|Behçet|Thrombose)\bu   \b(CRP|C-Reaktives Protein|D-?Dimer|Faktor\s*VIII|Leukozyten|Thrombozyten|Hämoglobin|Haemoglobin|Ferritin|Vitamin\s*D|LDL|HDL|Cholesterin|Triglyceride|Kreatinin|ALAT|ASAT|GGT|Glukose)\breturnsqlite3.Connectionc                 Z    t          j        t                    } t           j        | _        | S )N)sqlite3connectDBRowrow_factory)cons    M/home/agent/.hermes/assets/Gesundheit/scripts/process_all_health_documents.pyr   r   .   s    
/"

CkCOJ    pathr   strc                   t          j                    }|                     d          5 t          fdd          D ]}|                    |           	 d d d            n# 1 swxY w Y   |                                S )Nrbc                 .                          d          S )Ni   )read)fs   r   <lambda>zsha256.<locals>.<lambda>7   s    !&&"5"5 r   r   )hashlibsha256openiterupdate	hexdigest)r   hchunkr   s      @r   r"   r"   4   s    A	4 A5555s;; 	 	EHHUOOOO	               ;;==s   ,A##A'*A'raw
str | NonenameIterable[Path]c              #    K   t                      }| |g}|D ]}|st          j                            t	          |                    }t          |          g}t          |                                          s4|t          |z  t          j                    |z  t          d          |z  gz  }|	                    t          |
                    dt	          t          j                    dz                                           |	                    t          |
                    dd                               |	                    t          |
                    dd                               |D ].}t	          |          }||vr|                    |           |V  /d t          D             }|D ].}		 |	                    |          E d {V  # t          $ r Y +w xY wt          j        d|pd	          }
t          j        d
d	t          |          j                                                  }|D ]&}		 g }|
ru||
                    d           d|
                    d          d d          |
                    d          dd          |
                    d          dd           dgz  }|D ]2}|	                    |          D ]}|                                r|V  3|ra|	                    d          D ]K}|                                r5t          j        d
d	|j                                                  |k    r|V  L# t          $ r Y $w xY wd S )Nz/home/agentr   r	   zGesundheit/Archivz/home/agent/Gesundheit/ArchivzGesundheit/archivc                :    g | ]}|                                 |S  )exists.0rs     r   
<listcomp>z#path_candidates.<locals>.<listcomp>O   s%    3331

3Q333r   z(\d{6}) 
[^a-z0-9]+   *      )setosr   
expanduserr   r   is_absoluteBASEhomeappendreplaceaddSEARCH_ROOTSrglob	Exceptionrematchsubstemlowergroupis_file)r)   r+   seenvalsv
candidatespkeyrootsrootdate_prefix	norm_namepatternspatcands                  r   path_candidatesr[   <   s     UUD;D ' ' 	Gs1vv&&1ggY
Aww""$$ 	O4!8TY[[1_d=6I6IA6MNNJ$qyy)A3ty{{UaGaCbCbccddeee$qyy)<>]^^__```$qyy)<>]^^__``` 	' 	'Aa&&C$Qwww	'
 43333E  	zz$'''''''''' 	 	 	D	 (:tzr22K}b$t**/*?*?*A*ABBI  	H N 1 1! 4 4777K<M<Ma<P<PQSRSQS<T  :MVaVgVghiVjVjklmnknVo  :Mq|  rC  rC  DE  rF  rF  GH  GI  GI  rJ  :M  :M  :M  N  N # # JJsOO # #D||~~ #"


#  # JJsOO # #D||~~ #"&DIOODUDU*V*VZc*c*c"


 	 	 	D	 s%   :G
G#"G#=DM
MMrowsqlite3.RowPath | Nonec                    t          | d         | d                   D ]Q}	 |                                r*|                                r|                                c S B# t          $ r Y Nw xY wd S )N	dateipfad
datei_name)r[   r0   rM   resolveOSError)r\   rR   s     r   resolve_pathrd   j   s    S-s</@AA  	xxzz #aiikk #yy{{""" 	 	 	H	4s   ;A
A,+A,tuple[str, dict]c           	        dd l }g }ddd}|                    |           5 }|j        |d<   t          |d          D ]I\  }}|                    d          pd}|                    d	| d
|                                            J	 d d d            n# 1 swxY w Y   d                    |                                          |fS )Nr   pymupdf)methodpagesri   r7   starttextr5   z

--- PAGE z ---

)fitzr#   
page_count	enumerateget_textrA   stripjoin)r   rn   partsmetadocipagetxts           r   extract_pdf_pymupdfrz   t   s+   KKKE!,,D	4 ACW A... 	A 	AGAt--''-2CLL???#))++??@@@@	AA A A A A A A A A A A A A A A
 99U!!##T))s   A&BBB   	timeout_sintc                   ddl m}m} dd	} |            } |||t          |           f
          }|                                 |                    |           |                                r0|                                 |                    d           ddddfS 	 |                                \  }}n# t          $ r
 ddddfcY S w xY w|pdd|dfS )Nr   )ProcessQueueqr   rR   r   r   Nonec                &   	 ddl m}  |            }|                    |          }|                     |j                                        d f           d S # t          $ r/}|                     dt          |          f           Y d }~d S d }~ww xY w)Nr   )DocumentConverterr5   )docling.document_converterr   convertputdocumentexport_to_markdownrF   repr)r   rR   r   convresultes         r   workerz#extract_pdf_docling.<locals>.worker   s    	!DDDDDD$$&&D\\!__FEE6?5577>????? 	! 	! 	!EE2tAww-         	!s   AA 
B!$BB)targetargs
   r5   doclingtimeoutrh   error	no_result)r   r   rR   r   r   r   )
multiprocessingr   r   r   rk   rs   is_alive	terminate
get_nowaitrF   )	r   r|   r   r   r   r   procrl   errs	            r   extract_pdf_doclingr      s   ........! ! ! ! uwwA7&3t99~666DJJLLL$))I&&&}} =$))B---i)<<<<?LLNN	cc ? ? ?i+>>>>>>?:2)c::::s   !B9 9CCc                   dd l }|                    | dd          }g }d}|j        D ]}|                    d|j         d           t          |j        pdd          }t          |j        pdd          }|                    d||d	          D ]J}d
 |D             }	|	r:|t          |	          z  }|                    d
                    |	                     Kd
                    |                                          d|j        |dfS )Nr   T)	read_only	data_onlyz

--- SHEET z ---  P   r7   )min_rowmax_rowmax_colvalues_onlyc                    g | ]J}|t          |                                          dk    )t          |                                          KS )Nr5   )r   rr   )r2   rP   s     r   r4   z extract_xlsx.<locals>.<listcomp>   sC    ZZZq1=SVV\\^^WYEYEYCFFLLNNEYEYEYr   	rm   openpyxl)rh   sheetscells)r   load_workbook
worksheetsrA   titleminr   
max_column	iter_rowslenrs   rr   
sheetnames)
r   r   wbrt   r   wsmax_rmax_cr\   rO   s
             r   extract_xlsxr      s.   OOO					E	EBEEm . .4bh444555BJO!S))BM&Q++<<5%UY<ZZ 	. 	.CZZCZZZD .T"TYYt__---		.
 99U!!##
bm^c%d%dddr   rH   re.Matchc                   |                      d          r@|                      d          |                      d          |                      d          }}}n?|                      d          |                      d          |                      d          }}}	 t          |          ddt          |          d	dt          |          d	S # t          $ r Y d
S w xY w)Nr7   r9      r:         04d-02dr5   )rL   r}   rF   )rH   ymds       r   normalize_dater      s    {{1~~ A++a..%++a..%++a..a1++a..%++a..%++a..a1a&&888s1vv888CFF8888   rrs   3C
 

CCrl   dictc                   t          d t                              |           D                       d d         }t          d t                              |           D                       d d         }t          d t                              |           D                       d d         }g }t                              |           D ]}t          d|                                dz
            }t          t          |           |	                                dz             }t          j        d	d
| ||                                                   }|r||vr|                    |           t          |          dk    r n||||dS )Nc                J    h | ] }t          |          t          |          !S r/   )r   r2   r   s     r   	<setcomp>z#extract_insights.<locals>.<setcomp>   s.    [[[!XYIZIZ[N1%%[[[r      c                8    h | ]}|                     d           S r   rL   r   s     r   r   z#extract_insights.<locals>.<setcomp>   s"    IIIIIIr      c                8    h | ]}|                     d           S r   r   r   s     r   r   z#extract_insights.<locals>.<setcomp>   s"    BBBaqwwqzzBBBr   r   x      z\s+    )dates
lab_paramskeywordssnippets)sortedDATE_REfinditerLAB_PARAM_REDIAG_REmaxrk   r   r   endrG   rI   rr   rA   )	rl   r   r   r   r   r   rk   r   snips	            r   extract_insightsr      st   [[w/?/?/E/E[[[\\]`^`]`aEII\-B-B4-H-HIIIJJ3B3OJBB7+;+;D+A+ABBBCCCRCHHHd##  Aqwwyy3''s3t99aeeggm/L/Lvfc4c	?3399;; 	"D((OOD!!!x==AE *(X`aaar   ru   c                    t          | pd          }|dk    rdS |dk    rdS |                    d          dk    r|                    d          rd	S d
S )Nr5   i  goodi,  partialrh   r   r   failedweak)r   get)rl   ru   ns      r   qualityr      s_    DJBADyyvCxxyxxY&&488G+<+<&x6r   r   doc_idinsightr   r   c           
        g }|d         r9|                     dd                    |d         d d                   z              |d         r9|                     dd                    |d         d d                   z              |d         r9|                     d	d                    |d         d d                   z              |d
         r9|                     dd                    |d
         d d                   z              d}d                    |          dz   t          j        |d          z   }|d         r|d         d         nd }|dv rdnd}|                     d|d||||df           d S )Nr   zDatums: z, r   r   zLaborparameter:    r   z
Keywords: r   z
Snippets: z | r   zVolltext-Reprocessingrm   z

JSON:
Fensure_asciir   )r   r   infowarningz
        INSERT INTO document_insights(dokument_id, insight_type, datum, titel, inhalt, severity, source, created_at)
        VALUES(?,?,?,?,?,?,?,CURRENT_TIMESTAMP)
        fulltext_reprocessingzprocess_all_health_documents.py)rA   rs   jsondumpsexecute)	r   r   r   r   summaryr   contentdatumseveritys	            r   upsert_insightr      s   Gw EzDIIgg.>rr.B$C$CCDDD| S)DIIgl6KCRC6P,Q,QQRRRz K|dii
0CCRC0H&I&IIJJJz K|ejj1DRaR1H&I&IIJJJ#Eii  =04:gTY3Z3Z3ZZG#*7#3=GGQE111vvyHKK	 
(%Klm    r   FTforcebooluse_doclingc                   |d         }|d         }t          |          }|||rt          |          nd dddd d d}|s|                     ddd	|f           |S |j                                        }d
}	i }
	 |dk    rft          |          \  }	}
|rPt          |	          dk     r=t          |          \  }}t          |          t          |	          k    r||}
}	n3d|d}
n,|dv rt          |          \  }	}
n|dv r|	                    |j
        dz             |                    d          g}t          d |D             d           }|r*|                    dd          }	dt          |          d}
n	 dd l}ddlm} |                    |                    |          d          }	ddi}
n^# t&          $ r}d
}	dt)          |          d}
Y d }~n;d }~ww xY w	 |                    dd          }	dd i}
n# t&          $ r	 d
}	dd!i}
Y nw xY wt+          |	|
          }t-          |          }t.                              d"#           t.          d$| d%t3          j        d&d%|           dz                      |	d'           t9          |	          }|d(         r|d(         d         n|d)         }d }d*D ]4}|                                |	d+z   |z                                   v r|} n5|                     d,|	|||t          |          t          |          |||f	           t;          | |||           |                    d"|t          |	          |
                    d          |d(         d d-         |d.         d d/         0           nB# t&          $ r5}t)          |          |d1<   |                     dd2d3|f           Y d }~nd }~ww xY w|S )4Nidra   Fmissingr   )r   r+   r   okr   charsrh   r   z]UPDATE dokumente SET processing_quality=?, review_status=COALESCE(review_status,?) WHERE id=?missing_fileneeds_path_resolutionr5   z.pdfr   rg   )rh   docling_fallback)z.xlsxz.xlsm)z.jpgz.jpegz.pngz.tifz.tiffz_ocr.txtz.txtc              3  |   K   | ]7}|                                 |                                j        d k    3|V  8dS )r   N)r0   statst_size)r2   ss     r   	<genexpr>zprocess_one.<locals>.<genexpr>   sE      WW!188::W!&&((BRUVBVBVABVBVBVBVWWr   utf-8ignore)encodingerrorssidecar_ocr)rh   sidecar)Imagezdeu+eng)langrh   	tesseractimager   rl   unknownT)exist_ok	document__z[^A-Za-z0-9_.-]+r  r   document_date)	USZKSBViollierRothenVistaNFPraxiszDermatologische KlinikzKlinische ChemieImmunologier   a  
            UPDATE dokumente
               SET extrahierte_inhalte=?, verarbeite_datum=CURRENT_TIMESTAMP, status='eingearbeitet',
                   processing_quality=?, review_status=CASE WHEN ? IN ('good','partial') THEN COALESCE(review_status,'auto_extracted') ELSE COALESCE(review_status,'needs_review') END,
                   datei_hash=?, local_original_path=?, dateipfad=?, document_date=COALESCE(document_date,?), institution=COALESCE(institution,?)
             WHERE id=?
            r   r   r   )r   r   r  rh   r   r   r   extract_errorneeds_review) rd   r   r   suffixrK   rz   r   r   r   	with_namerJ   with_suffixnext	read_textpytesseractPILr  image_to_stringr#   rF   r   r   r"   EXTRACT_DIRmkdirrG   rI   
write_textr   r   r%   r   )r   r\   r   r   r   r+   rR   r   extrl   ru   dtextdmetasidecarsr  r(  r  r   r   r'   r   docdateinstitutioninsts                           r   process_oner5     s   YF|DSADA2G#a&&&4uajuv  CG  RV  W  WF s  wE  G^  `f  vg  	h  	h  	h
(..

CDbt9`&==,Q//JD$ Ls4yy32155uu::D		))!&$DD&/UKKD&&&%aJD$$>>>AFZ$788!--:O:OPHWWxWWWY]^^G A(('((KK"/CLLIIA&&&&))))))&66uzz!}}96UUD$k2DD  A A AD&-Q@@DDDDDDA8{{GH{EE &) 8 8 88Y"74448D$1II4(((	V6VVBF3Fs4,P,PVVV	Vbbcgrybzzz"4(()0)9S''"1%%s??S K 	* 	*Dzz||s
T 188::::"EE ; 1aCFFCFFG[&I		
 		
 		
 	sFGQ///qD		$((8BTBT\cdk\lmonomo\p  ~E  FR  ~S  TV  UV  TV  ~W  	X  	X  	X  	X ` ` `q''ws  wF  HV  X^  v_  	`  	`  	`  	`  	`  	`  	`  	`` Msh   7D N 88F1 0N 1
G;GN GN G9 8N 9H	N HE?N 
O+OO
list[dict]c           	        t          |                     d                    }t          t                     }t          t                     }|D ]}|d         r!||d                                      |           t	          j        ddt          |d                   j                                                  }||                             |           g }|	                                D ]\  }}t          |          dk    rst          d |D                       }	|D ]+}
|
d         |	k    r|                     d	|
d         f           ,|                    d
|d d         d |D             |	d           |	                                D ]E\  }}|r>t          |          dk    r+|                    d|d d         d |D             d           F|S )NzLSELECT id,datei_name,datei_hash,local_original_path,dateipfad FROM dokumente
datei_hashr6   r5   ra   r7   c              3  &   K   | ]}|d          V  dS )r   Nr/   r2   gs     r   r	  z"mark_duplicates.<locals>.<genexpr>:  s&      ..1qw......r   r   zUPDATE dokumente SET review_status='duplicate_candidate', processing_quality=COALESCE(processing_quality,'duplicate_hash') WHERE id=?hashr   c                    g | ]
}|d          S r   r/   r:  s     r   r4   z#mark_duplicates.<locals>.<listcomp>>      AYAYAYa!D'AYAYAYr   )typerS   idskeepr+   (   c                    g | ]
}|d          S r>  r/   r:  s     r   r4   z#mark_duplicates.<locals>.<listcomp>A  r?  r   )r@  rS   rA  )listr   r   rA   rG   rI   r   rJ   rK   itemsr   r   )r   rowsby_hashby_normr3   normdupsrS   rL   rB  r;  s              r   mark_duplicatesrL  .  s   jkkllD$G$G    \? 	/AlO$++A...vmRao)>)>)C)I)I)K)KLLQDmmoo j j
Uu::>>.......D u uT7d??KK  !h  kl  mq  kr  jt  u  u  uKKCRCAYAYSXAYAYAYcghhiiimmoo \ \
U 	\3u::>>KKCRCAYAYSXAYAYAYZZ[[[Kr   c                 
   t          j                    } |                     dd           |                     dd           |                     dt          d           |                                 }t
                              dd	           t                              dd	           t
          d
t          j	                    ddz  }t          j        t          |           t                      }t          |                    d                                          d         |                    d                                          d                   }t#          |                    d|j        rdndf                    }|j        r|d |j                 }g }t)          j                    }dt          j	                    d}|                    d|ddt+          j        |          f           |                    d                                          d         }	|                                 t1          |d          D ]\  }
}t3          |||j        |j                   }|                    |           |                                 t9          t+          j        d|
 dt;          |           i|d          d           t=          |          }t          |                    d                                          d         |                    d                                          d         |                    d                                          d         |                    d                                          d         |                    d                                           d         !          }t?          |          ||t;          |          tA          d" |D                       |d d#         tC          t)          j                    |z
  d          d$}|                    d%d&t+          j        |d          |	f           |                                 |"                                 t          d't          j	                    dd(z  }|#                    t+          j        ||d)d*d+          d,-           t9          d.t+          j        |d          d           t9          |           d S )/Nz--force
store_true)actionz--no-doclingz--limitr   )r@  defaultT)parentsr  "health_data_before_full_reprocess_z%Y%m%d_%H%M%Sz.dbzSELECT count(*) FROM dokumentezQSELECT count(*) FROM dokumente WHERE length(COALESCE(extrahierte_inhalte,''))<100)totalwithout_textzSELECT * FROM dokumente WHERE ? OR length(COALESCE(extrahierte_inhalte,''))<100 ORDER BY CASE status WHEN 'neu' THEN 0 WHEN 'eingearbeitet' THEN 1 ELSE 2 END, id DESCr7   full_reprocess_zqINSERT INTO health_processing_runs(batch_id,run_type,status,started_at,summary) VALUES(?,?,?,CURRENT_TIMESTAMP,?)full_document_reprocessrunningzSELECT last_insert_rowid()rj   )r   r   progress/Fr   )flushz>SELECT count(*) FROM dokumente WHERE processing_quality='good'zASELECT count(*) FROM dokumente WHERE processing_quality='partial'zFSELECT count(*) FROM dokumente WHERE processing_quality='missing_file')rS  rT  r   r   r   c              3  *   K   | ]}|d          
dV  dS )r   r7   Nr/   r1   s     r   r	  zmain.<locals>.<genexpr>`  s`        mL  mLst  DE  FJ  DK  mLmn  mL  mL  mL  mL  mL  mLr   2   )backupbeforeafter	processedr   
duplicates	elapsed_sz_UPDATE health_processing_runs SET status=?, finished_at=CURRENT_TIMESTAMP, summary=? WHERE id=?	completedfull_document_reprocess_z.json)r   resultsr9   )indentr   r
  r  SUMMARY)$argparseArgumentParseradd_argumentr}   
parse_argsBACKUPSr,  REPORTSr   nowshutilcopy2r   r   r   r   fetchonerE  r   limittimer   r   commitrp   r5  
no_doclingrA   printr   rL  r   sumroundcloser-  )apr   r]  r   r^  rG  re  t0batch_idrun_idrw   r\   resrK  r_  r   outs                    r   mainr  E  s[   		 	"	"BOOIlO333OON<O888OOICO333==??DMM$M...dUY0Z0Z0Z]HLNN]]]]]F
LV
))C$DEENNPPQRSbebmbm  oB  cC  cC  cL  cL  cN  cN  OP  cQ  R  R  RF  E  MQ  MW  H^  HI  HI  ]^  G`  a  a  b  bDz !KTZK G	B????HKK  D  GO  Qj  lu  w{  wA  BH  wI  wI  FJ  K  K  K[[566??AA!DFJJLLLD*** c c3#s$*doBUVVVs

dj*&8&8SYY&8&8@C@uUUU]abbbbb3Ds{{#CDDMMOOPQRadalal  nA  bB  bB  bK  bK  bM  bM  NO  bP  WZ  Wb  Wb  cc  Wd  Wd  Wm  Wm  Wo  Wo  pq  Wr  |  |G  |G  HK  |L  |L  |U  |U  |W  |W  XY  |Z  dg  do  do  px  dy  dy  dB  dB  dD  dD  EF  dG  H  H  HEVUXY`UaUail  mL  mLx  mL  mL  mL  jL  jL  \`  ad  bd  ad  \e  ty  z~  zC  zE  zE  FH  zH  JK  tL  tL  M  MGKKqt  BF  BL  MT  ch  Bi  Bi  Bi  kq  tr  s  s  sJJLLL#))+++
Rx|~~RRRR
RCNN4:'gFFq_deeepwNxxx	)TZe<<<DIIII	#JJJJJr   __main__)r   r   )r   r   r   r   )r)   r*   r+   r   r   r,   )r\   r]   r   r^   )r   r   r   re   )r{   )r   r   r|   r}   r   re   )rH   r   r   r   )rl   r   r   r   )rl   r   ru   r   r   r   )
r   r   r   r}   r   r   r   r   r   r   )FT)
r   r   r\   r]   r   r   r   r   r   r   )r   r   r   r6  )r   r   ).__doc__
__future__r   rh  r!   r   r<   rG   ro  r   sysrs  collectionsr   r   pathlibr   typingr   r@   r?   r   rm  rl  r+  rD   compiler   Ir   r   r   r"   r[   rd   rz   r   r   r   r   r   r   r5  rL  r  __name__r/   r   r   <module>r     s    # " " " " "    				 				   



  # # # # # #                  ty{{Y)L8	



%%DIKK,D	!"" "*p
q
q
"*  O  QS  QU  V  Vrz  X  Z\  Z^  _  _      + + + +\   	* 	* 	* 	*; ; ; ; ;4e e e e"   b b b b      .D D D D DN   .! ! ! !F zDFFFFF r   