
    bkj                    B   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mZm	Z	 d dl
mZmZ d dlmZ d dlmZ d dlmZ d dlmZmZ d dlmc mZ d d	lmZ d d
lmZ d dlmZm Z  d dl!m"Z"m#Z# d dl$m%Z% h dZ&h dZ' ej(        d          Z) ej(        dej*                  Z+ ej(        d          Z, ej(        d          Z- ed           G d d                      Z. ed           G d d                      Z/ ed           G d d                      Z0dvdZ1dwd!Z2dxd#Z3dyd&Z4dzd(Z5d{d+Z6d|d,Z7d}d/Z8d|d0Z9d1d2d~d6Z:dvd7Z;d1ddd8d9ddAZ<dBdCddGZ=ddOZ>dBdBdPddTZ?dBdCddUZ@ddXZAdddddYdd`ZBddaZCdbdcdddZDddhZEddjZFddnZGddddBdodpdqdduZHdS )    )annotationsN)	dataclassfield)DecimalInvalidOperation)Path)
Connection)Sequence)requesterror)record_audit_event) add_manual_position_from_catalog)	stable_idutc_now)CatalogEntryInputupsert_catalog_entry)create_alert>   probablerejected	confirmedexact_isin_matchneeds_manual_review>   manual
qwen_localdeterministic_parserz\b[A-Z]{2}[A-Z0-9]{9}[0-9]\bz!\b(CHF|USD|EUR|GBP|JPY|CAD|AUD)\bz(\b[A-Z0-9]{1,6}(?:[.:-][A-Z0-9]{1,4})?\bz[(?<![A-Z0-9])[-+]?\d{1,3}(?:[ '\u00a0]\d{3})*(?:[.,]\d+)?|(?<![A-Z0-9])[-+]?\d+(?:[.,]\d+)?T)frozenc                  <   e Zd ZU ded<   ded<   dZded<   dZded<   dZded<   dZded<   dZded	<   dZ	ded
<   dZ
ded<   dZded<   dZded<   dZded<   dZded<   dZded<   dZded<   dZded<   dZded<   dZded<   dZded<   dZded<   dZded<   dZded<   dS )CandidateInputstrsource_fileplatform accountsource_row_numberraw_name
raw_tickerraw_isinraw_currencyraw_quantityraw_asset_classr   extracted_bylowextraction_confidenceproposed_nameproposed_tickerproposed_isinproposed_exchangeproposed_currencyproposed_asset_classproviderprovider_symbolr   mapping_statusreview_noteN)__name__
__module____qualname____annotations__r#   r$   r%   r&   r'   r(   r)   r*   r+   r-   r.   r/   r0   r1   r2   r3   r4   r5   r6   r7        3src/jarvis_finance/imports/instrument_candidates.pyr   r      s        MMMGHJHLLO.L....!&&&&&MOM """""HO/N////Kr=   r   c                  6   e Zd ZU dZded<   dZded<   dZded<   dZded<   dZded<   dZ	ded<   dZ
ded	<   dZded
<   dZded<   dZded<   dZded<   dZded<   dZded<   dZded<   dZded<   dZded<   dZded<   dZded<   dZded<   dZded<   dZded<   dS )CandidateSummaryr   intcandidates_total	with_isinwithout_isinwith_tickerwith_currencyr   r   r   r   r   importable_confirmedblockedFbool	qwen_usedparser_qwen_agreeparser_qwen_disagreetransactions_writtenaudit_logs_writtenwarningserrorsdry_runqwen_availableN)r8   r9   r:   rB   r;   rC   rD   rE   rF   r   r   r   r   r   rG   rH   rJ   rK   rL   rM   rN   rO   rP   rQ   rR   r<   r=   r>   r@   r@   8   s{        ILKMH     IH !!!!!GI !!!!! !!!!!HFOOOOG N      r=   r@   c                      e Zd ZU dZded<   dZded<   dZded<   dZd	ed
<   dZd	ed<   dZ	d	ed<   dZ
d	ed<   dZd	ed<   dZd	ed<   dZd	ed<   dZd	ed<   dZd	ed<   dZd	ed<   dZd	ed<   dZd	ed<   dZded<   dS )QwenResolveSummaryFrI   rR   rJ   r"   r   modelr   rA   rB   exact_beforeexact_afterprobable_beforeprobable_afterneeds_review_beforeneeds_review_afterconfirmed_afterimprovedmanual_review_remainingrO   rP   rQ   N)r8   r9   r:   rR   r;   rJ   rU   rB   rV   rW   rX   rY   rZ   r[   r\   r]   r^   rO   rP   rQ   r<   r=   r>   rT   rT   Q   s         N    IEOOOOLKON     OH#$$$$$HFOOOOGr=   rT   textr   returnc                p    t          j        ddt          | pd                                                    S )Nz\s+ r"   )resubr   stripr_   s    r>   _norm_spacerg   d   s,    6&#s4:2//55777r=   r    c                    t          |           }t          j        t          |j                                      d                                                    d d         S )Nutf-8   )r   hashlibsha256r   nameencode	hexdigest)r    ps     r>   _source_labelrq   h   sJ    [A>#af++,,W5566@@BB3B3GGr=   inpc                    t          dt          | j                  | j        | j        | j        | j        | j        | j        | j	        | j
        
  
        S )Ninstrument_candidate)r   rq   r    r!   r#   r$   r%   r'   r&   r(   r)   )rr   s    r>   _candidate_idru   m   sH    co&&ck3CXclCNC4DcFV  r=   path
str | Pathc                *    t          |           j        S N)r   rm   )rv   s    r>   _file_labelrz   u   s    ::?r=   	list[str]c           
        t          j        |           5 }|                    d          }ddd           n# 1 swxY w Y   t          j        |          }ddi}g }|                    d|          D ]Y}t          d                    d |                    d|          D                                 }|r|                    |           Z|S )	z_Return sanitized paragraph/table text lines from a DOCX without storing broker content in repo.zword/document.xmlNwz<http://schemas.openxmlformats.org/wordprocessingml/2006/mainz.//w:pr"   c              3  (   K   | ]}|j         pd V  dS )r"   Nrf   ).0nodes     r>   	<genexpr>z$extract_docx_text.<locals>.<genexpr>   s(      "Z"Zt49?"Z"Z"Z"Z"Z"Zr=   z.//w:t)	zipfileZipFilereadET
fromstringfindallrg   joinappend)rv   zfxmlrootnslinesparar_   s           r>   extract_docx_textr   y   s	   			 +"gg)**+ + + + + + + + + + + + + + +=D
M	NBEXr**  277"Z"Zt||HVX?Y?Y"Z"Z"ZZZ[[ 	LLLs   7;;linerI   c                   |                                  t          fddD                       rdS t          t                                        pt
                                                  }t          t                              |                     dk    }t          t          j
        dd|                     dk    }|o|o|S )Nc              3      K   | ]}|v V  	d S ry   r<   )r   stopups     r>   r   z'_looks_position_line.<locals>.<genexpr>   s'      
n
n$42:
n
n
n
n
n
nr=   )TOTALSUMMECASHLIQUIDITKONTO	MARKTWERTPERFORMANCEF   z	[^A-Za-z]r"      )upperanyrI   ISIN_REsearchCURRENCY_RElen
DECIMAL_REr   rc   rd   )r   has_identifierhas_quantity_likehas_namer   s       @r>   _looks_position_liner      s    	B

n
n
n
n"m
n
n
nnn u'..,,F0B0B20F0FGGNJ..t4455:26,D1122a7H</<H<r=   c                J    |                                  }d|v sd|v sd|v rdS dS )NETFFUNDFONDSetfequityr   )r   r   s     r>   _asset_classr      s2    	B{{fllgmmu8r=   isincurrencyc           	         d t                               |                                           D             }||dddddddh	}|D ]1}||vr+|                                st	          |          d	k    r|c S 2d
S )Nc                \    g | ])}|                     d                                           *S )r   )groupr   )r   ms     r>   
<listcomp>z#_extract_ticker.<locals>.<listcomp>   s.    JJJAQWWQZZJJJr=   CHFUSDEURGBPr   r   ISIN   r"   )	TICKER_REfinditerr   isdigitr   )r   r   r   wordsignoredr}   s         r>   _extract_tickerr      s    JJ););DJJLL)I)IJJJEXueUE5&&QG  GAIIKKCFFaKKHHH2r=   c                    t                               |           }|r=t          |d                                       dd                              dd          ndS )Nr   'r"       rb   )r   r   rg   replace)r   valss     r>   _extract_quantityr      sR    d##DKOW;tAw''R00883GGGUWWr=   r"   )r#   r!   r#   list[CandidateInput]c                  t          |           }g t                      d:fd}t          |d          D ]\  }}t          |          }t                              |                                          }|sD|                    d          }	d	 |t          d|d
z
            |dz
           D             }
t          |
r|
d         n|	                    |	d                    d d         }d
                    ||t          t          |          |dz                                }t                              |                                          }|r'|                    d                                          nd}t          ||	|          } |t          d;i dt!          |           d|d|dt#          |          d|d|d|	d|dt%          |          dt'          |          ddddd|d|d |	d!|d"t'          |          d#d$d%d&           t          |d          D ]	\  }}t)          |          st          |          }t                              |                                          }|r|                    d          nd}	t                              |                                          }|r'|                    d                                          nd}t          ||	|          }t*                              d|          }|	r|	                    |	d          }|r8t/          j        d't/          j        |           d'd|t.          j        (          }t          |          d d         }|	rd$nd)}|	rdn|r|rd*nd+} |t          d;i dt!          |           d|d|dt#          |          d|p	|d d         d|d|	d|dt%          |          dt'          |          ddd|d|p	|d d         d|d |	d!|d"t'          |          d#|d%d,           d-}d.}h d/}t          |d          D ]\  }}t          |                                          }|d0v r	d1|v rd2nd-}4|d3k    rd4}=|r||v s|                    d5          rYt/          j        d6|          sod
                    ||t          t          |          |d7z                                }t                              |                                          }|r'|                    d                                          nd}t%          d
                    |||d8z                                }|s2 |t          d;i dt!          |           d|d|dt#          |          d|d|ddd|d|d|ddd|rd*nd+d|d|d!|d"|d#d)d%d9           S )<Nrr   r   r`   Nonec                    | j         | j        p| j        | j        p| j        f}|vr,                    |                               |            d S d S ry   )r$   r'   r0   r&   r/   addr   )rr   keyoutseens     r>   r   z%deterministic_parse_docx.<locals>.add   sY    $cl&Gc6GIn[^[nod??HHSMMMJJsOOOOO ?r=   r   )startr   c                J    g | ]t          fd dD                        S )c              3  D   K   | ]}|                                 v V  d S ry   r   )r   xls     r>   r   z6deterministic_parse_docx.<locals>.<listcomp>.<genexpr>   sW        P[  P[cdPQUVU\U\U^U^P^  P[  P[  P[  P[  P[  P[r=   )POSITIONSWERTr   zCHF zUSD zEUR )r   )r   r   s    @r>   r   z,deterministic_parse_docx.<locals>.<listcomp>   sy      \  \  \C  P[  P[  P[  P[  iZ  P[  P[  P[  M[  M[  \1  \  \  \r=      r"      rb      r    r!   r#   r$   r%   r&   r'   r(   r)   r*   r+   r   r-   highr.   r/   r0   r2   r3   r6   r   r7   zLdeterministic DOCX staging candidate with source ISIN; no productive bookingz\bflagsr   mediumr,   z;deterministic DOCX staging candidate; no productive bookingr   F>	      GELDKURS WÄHRUNGG&V CHFDIFF. VORTAGPOSITIONEN %TOTALWERT CHFANZAHLPRODUKT	TOTALWERTEINSTANDSKURS>   r   ETFSAKTIENr   r   r   TzPAGE z[A-Z][A-Z0-9.]{0,7}	      zGticker-only split-table candidate; manual ISIN/exchange review required)rr   r   r`   r   r<   )r   set	enumeraterg   r   r   r   r   maxr   r   minr   r   r   r   rz   r   r   r   r   r   rd   rc   escapeI
startswith	fullmatch)rv   r!   r#   r   r   idxr   raw
isin_matchr   prev_candidatesrm   window
curr_matchr   tickerstatus
confidencecurrent_asset_classin_positionsheadersr   qtyr   r   s                          @@r>   deterministic_parse_docxr     s@   d##E "C&)eeD       uA...  	T$^^CIIKK00
 	"" \  \eC37OOC!G,C&D  \  \  \/\?2..s{{SWY[G\G\]]^b_b^bc%CE

C!G$<$< <=>> ''77
2<D:##A&&,,..." tX66N 
 
 
#D)))
4<H
FMg
adehaiaiai
T
&,f
7;t
JR(
 +6222
 EQQUDVDVDV
 f|e{
 #)&	
 9=	
 OUf	

 $

 3;(

 R^^bQcQcQc
 .-
 <J  <J
 	 	 	 	 uA...  	T#D)) 	$^^CIIKK00
&08z"""b ''		44
2<D:##A&&,,..." dH55~~c3'' 	+<<c**D 	P67	( 3 3777d"$OOOD4  #&'+F##1F#UVVf*T*T((u
N 
 
 
#D)))
4<H
FMg
adehaiaiai
&S#Y
396
DHD
W_W_
 +3///
 BNcARARAR
 bxaw
 #-*	
 =A<MCI	
 `f_e	

 $

 3;(

 R^^aQbQbQb
 "6
 0m/l
 	 	 	 	 #L S  S  SGuA...  	T$$&&***+0B;;%%H??L 	rW}}g0F0F}|2B77 	%CE

C!G$<$< <=>> ''77
2<D:##A&&,,..."s37{); < <== 	N 
 
 
#D)))
4<H
FMg
adehaiaiai
R
$&B
13
BJ(
Y\Y\
 0/
 ?U>T
 yA  mLltlt  GL
 "	
 /1b	
 EMH	
 dwcv	

 10

 ?H  ?H
 	 	 	 	 Jr=   c                    t                               dt          |                     }t          j        dd|t          j                  }|d d         S )Nz<number_redacted>zF\b(?:IBAN|KONTO|ACCOUNT|DEPOT|CLIENT|KUNDE|KUNDENNR)[:# ]*[A-Z0-9-]+\bz<identifier_redacted>r     )r   rd   rg   rc   r   )r_   	sanitizeds     r>   _sanitize_for_qwenr    s`    2K4E4EFFI`by  |E  MO  MQ  R  R  RITcT?r=      )r#   endpointrU   timeout
text_linesr	  
str | NonerU   r
  rA   !tuple[list[CandidateInput], bool]c               	   |p)t          j        d          pt          j        d          pd}|pt          j        dd          }|dddd	d
d                    d t          | dd                   D                       d	gd}t	          j        |                    d           dt          j        |          	                    d          ddi          }	 t	          j
        ||          5 }	t          j        |	                                                    d                    }
ddd           n# 1 swxY w Y   n1# t          t          j        t"          t          j        f$ r g dfcY S w xY w|
                    di g          d                             di                               dd          }t)          j        d|t(          j                  }|sg dfS 	 t          j        |                    d                    }n# t          j        $ r g dfcY S w xY wg }|                    dg           D ]}t1          |                    d          pd                                                                          }|rt6                              |          sd}t1          |                    d           pd                                                                          }t1          |                    d!          pd                                                                          }|rd"nd#}|                    t=          dAi d$t?          |          d%|d&|d't1          |                    d(          pd          d)tA          t1          |                    d*          pd                    dd+         d,|d-|d.|d/dd0t1          |                    d1          pd          !                                pd2d3d4d5t1          |                    d6          pd7          d8tA          t1          |                    d*          pd                    dd+         d9|d:|d;|d<t1          |                    d1          pd          !                                pd2d=|d>tA          t1          |                    d?          pd@                               |dfS )BzYUse local OpenAI-compatible Qwen as assistive parser. It returns staging candidates only.QWEN_OPENAI_BASE_URLLOCAL_QWEN_BASE_URLhttp://100.101.173.25:11435/v1
QWEN_MODELqwen-3.6-agentr   systemzExtract broker equity/ETF position candidates as JSON only from sanitized text. Do not invent ISIN, history, prices, quantities, account data, or IDs. Output {"positions":[{name,ticker,isin,currency,asset_class,row_number,confidence,note}]}rolecontentuser
c              3  J   K   | ]\  }}|d z    dt          |           V  dS )r   z: N)r  )r   ir   s      r>   r   z*qwen_extract_candidates.<locals>.<genexpr>  s\        2C  2C[b[\^bQqS2V2V<Nt<T<T2V2V  2C  2C  2C  2C  2C  2Cr=   Ni,  )rU   temperaturemessages//chat/completionsri   Content-Typeapplication/jsondatar  r
  Fchoicesmessager  r"   \{.*\}T	positionsr   r   r   r   r   r    r!   r#   r$   
row_numberr%   rm   r   r&   r'   r(   r)   r*   asset_classr   r+   r   r-   r   r,   r.   r/   r0   r2   r3   r6   r7   notez*qwen assistive extraction; review requiredr<   )"osgetenvr   r   r   Requestrstripjsondumpsrn   urlopenloadsr   decodeOSErrorr   URLErrorTimeoutErrorJSONDecodeErrorgetrc   r   Sr   r   re   r   r   r   r   r   rz   rg   lower)r  r    r!   r#   r	  rU   r
  payloadreqrespr#  r  matchparsedr   posr   r   r   r   s                       r>   qwen_extract_candidatesrB  	  s     E29%;<<  E	J_@`@`  E  eEH>RY|-=>>E  +_  `  `		  2C  2Cfopz{|{  qA  gB  gB  2C  2C  2C  )C  )C  D  D
 G /X__S11DDD4:V]K^K^KeKefmKnKn  zH  J\  y]  ^  ^  ^C_S'222 	;d:diikk0099::D	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	;U^\43GH   5yhhy2$''*..y"==AA)RPPGIi"$//E 4xEKKNN++   4x "Czz+r**  3776??(b))//117799 	))$// 	DSWWX&&,"--3355;;==swwz**0b117799??AA'+F##1F

> 	
 	
 	
#K000	
;C8	
MTW	
!#'',"7"7"=2>>>	
ITUXY\Y`Y`agYhYhYnlnUoUoIpIpquruquIvIv	
 v	
 )-	
 <D8	
 SURT	
   6 6 <"==CCEEQ		
 am`l		

 #&cggl&;&;&Du"E"E"E	

 Vaadehelelmsetetezxza{a{U|U|  ~B  B  ~B  VC  VC	
 #F	
 37$	
 KS(	
 "%SWW]%;%;%Ar!B!B!H!H!J!J!Vh	
 hngm	
 $C(g;g$h$hiii	
 		 		 		 		 9sH   E /:D5)E 5D99E <D9=E +E/.E/'H HHFrQ   connr	   rQ   c               ~   |j         t          vrt          d          |j        t          vrt          d          t          |          }|r|S t                      }|                     d||j        |j	        |j
        |j        |j        |j        |j        |j        |j        |j        |j         |j        |j        |j        |j        |j        |j        |j        |j        |j        |j        |j        ||f           |j        dk    r9t9          | ddd|dd	|j	        t;          |j        p|j                  d
d	  	         |S )Nzinvalid extractorzinvalid mapping statusa  
        INSERT INTO instrument_import_candidates(
            candidate_id, source_file, platform, account, source_row_number, raw_name, raw_ticker,
            raw_isin, raw_currency, raw_quantity, raw_asset_class, extracted_by, extraction_confidence,
            proposed_name, proposed_ticker, proposed_isin, proposed_exchange, proposed_currency,
            proposed_asset_class, provider, provider_symbol, mapping_status, review_note, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(candidate_id) DO UPDATE SET
            raw_name=excluded.raw_name, raw_ticker=excluded.raw_ticker, raw_isin=excluded.raw_isin,
            raw_currency=excluded.raw_currency, raw_quantity=excluded.raw_quantity, raw_asset_class=excluded.raw_asset_class,
            extraction_confidence=excluded.extraction_confidence, proposed_name=excluded.proposed_name,
            proposed_ticker=excluded.proposed_ticker, proposed_isin=excluded.proposed_isin,
            proposed_exchange=excluded.proposed_exchange, proposed_currency=excluded.proposed_currency,
            proposed_asset_class=excluded.proposed_asset_class, provider=excluded.provider,
            provider_symbol=excluded.provider_symbol,
            mapping_status=CASE WHEN instrument_import_candidates.mapping_status='confirmed' THEN 'confirmed' ELSE excluded.mapping_status END,
            review_note=excluded.review_note, updated_at=excluded.updated_at
        r   warnungr   instrument_import_candidate$instrument_candidate_review_requiredzBInstrument import candidate requires manual review before booking.)r!   has_isin)prioritycategoryentity_type	entity_idrule_idr&  evidencefingerprint)r+   VALID_EXTRACTORS
ValueErrorr6   VALID_MAPPING_STATUSru   r   executer    r!   r#   r$   r%   r&   r'   r(   r)   r*   r-   r.   r/   r0   r1   r2   r3   r4   r5   r7   r   rI   )rD  rr   rQ   cidnows        r>   upsert_candidaterW  8  s   
///,---
!5551222


C 

))CLL	& #,S=RTWT`bebpL#*C,<c>QSVScehe~s2C4EsG\^a^s$clC4GI[]`]lnqsv		
'  4 [((TINkwz  Ek  uy  QT  Q]  ko  ps  p|  pQ  @C  @Q  kR  kR  DS  DS  aG  	H  	H  	H  	HJr=   itemsrJ   rR   agreedisagreerO   rP   c          	        t          di dt          |           dt          d | D                       dt          d | D                       dt          d | D                       dt          d	 | D                       d
t          d | D                       dt          d | D                       dt          d | D                       dt          d | D                       dt          d | D                       dt          d | D                       dt          d | D                       d|d|d|d|d|d|d|S ) NrB   rC   c              3  6   K   | ]}|j         s|j        d V  dS r   Nr0   r'   r   cs     r>   r   z'_summary_from_inputs.<locals>.<genexpr>c  s0      HHA!/HQZHaHHHHHHr=   rD   c              3  6   K   | ]}|j         	|j        d V  dS r]  r^  r_  s     r>   r   z'_summary_from_inputs.<locals>.<genexpr>d  s0      QQq!/QQZQQQQQQQr=   rE   c              3  6   K   | ]}|j         s|j        d V  dS r]  )r/   r&   r_  s     r>   r   z'_summary_from_inputs.<locals>.<genexpr>e  s1      NNaA,=NNNNNNNNr=   rF   c              3  6   K   | ]}|j         s|j        d V  dS r]  )r2   r(   r_  s     r>   r   z'_summary_from_inputs.<locals>.<genexpr>f  s1      TTa.ATQ^T!TTTTTTr=   r   c              3  0   K   | ]}|j         d k    dV  dS )r   r   Nr6   r_  s     r>   r   z'_summary_from_inputs.<locals>.<genexpr>g  s0      XX11AEW1W1WQ1W1W1W1WXXr=   r   c              3  0   K   | ]}|j         d k    dV  dS )r   r   Nre  r_  s     r>   r   z'_summary_from_inputs.<locals>.<genexpr>h  /      HH1)9Z)G)GQ)G)G)G)GHHr=   r   c              3  0   K   | ]}|j         d k    dV  dS )r   r   Nre  r_  s     r>   r   z'_summary_from_inputs.<locals>.<genexpr>i  s0      ^^aA4DH]4]4]4]4]4]4]^^r=   r   c              3  0   K   | ]}|j         d k    dV  dS r   r   Nre  r_  s     r>   r   z'_summary_from_inputs.<locals>.<genexpr>j  s/      JJA!*:k*I*Ia*I*I*I*IJJr=   r   c              3  0   K   | ]}|j         d k    dV  dS )r   r   Nre  r_  s     r>   r   z'_summary_from_inputs.<locals>.<genexpr>k  rg  r=   rG   c              3  Z   K   | ]&}|j         d k    |j        |j        s|j        "dV  'dS rj  )r6   r)   r2   r(   r_  s     r>   r   z'_summary_from_inputs.<locals>.<genexpr>l  sb        !U  !UqQ5E5T5TYZYg5Tmn  nA5T  EF  ES5T5T5T5T5T  !U  !Ur=   rH   c              3  0   K   | ]}|j         d k    dV  dS rj  re  r_  s     r>   r   z'_summary_from_inputs.<locals>.<genexpr>m  s/      HH!(8K(G(GA(G(G(G(GHHr=   rJ   rR   rK   rL   rO   rP   rQ   r<   )r@   r   sum)rX  rJ   rR   rY  rZ  rO   rP   rQ   s           r>   _summary_from_inputsro  `  s      UHHHHHHHH QQEQQQQQQ NN5NNNNNN	
 TTUTTTTTT XXXXXXXX HHHHHHHH  ^^5^^^^^^ JJJJJJJJ HHHHHHHH !  !U  !UE  !U  !U  !U  U  U  U HHuHHHHHH ) &~  %  &X!" #$ v%& ' r=   )use_qwenrQ   filesSequence[str | Path]rp  c               \   d}d}t                      }t                      }g }d}	d}
|D ]}t          |          }d|j                                        v rdnd|j                                        v rdnd}	 t	          ||          }|D ]}|                    |           |                    |j        p|j        	                                |j
        p|j        	                                |j        p|j        	                                f           t          | ||	           |rt          |          }t!          ||j        |
          \  }}|p|}|pt#          |          }|D ]}|                    |           |                    |j        p|j        	                                |j
        p|j        	                                |j        p|j        	                                f           t          | ||	           # t$          $ r	 |
dz  }
Y w xY w|s|                                  t)          | |	          }t+          |dhz
  |dhz
  z            }|rt+          ||z  dhz
            nd}|rt-          ||||||	|
d          S t/          di i |j        |||||	|
|dS )NFr   truetrue_wealthpostpostfinanceunknown)r!   rC  )r    r!   r   )r"   r"   r"   T)rJ   rR   rY  rZ  rO   rP   rQ   )rJ   rR   rK   rL   rO   rP   rQ   r<   )r   r   rm   r;  r  r   r   r0   r'   r   r/   r&   r2   r(   rW  r   rB  rI   	Exceptioncommitsummarize_candidatesr   ro  r@   __dict__)rD  rq  rp  rQ   rJ   rR   parser_signaturesqwen_signaturesstaged_itemsrO   rP   rv   rp   r!   parser_candidatesr`  r   q_candidates	availablesummaryrY  rZ  s                         r>   stage_docx_candidatesr  x  s   IN365514O)+LHF  JJ$*afllnn$<$<==TZ^_^d^j^j^l^lTlTl==r{	 8X N N N& ; ;##A&&&!%%(E1:'L'L'N'NQRQbQrfgfrPyPyP{P{~  R  d  VW  Vd  ~k  ~k  ~m  ~m  'n  o  o  o q'::::: ?)!,,*A%UVU[fn*o*o*o'i!/!<9%;l););	% ? ?A ''***#''!/*GQZ)N)N)P)PSTSdSthihtR{R{R}R}  AB  AT  Af  XY  Xf  @m  @m  @o  @o  )p  q  q  q$T1g>>>>> 	 	 	aKFFF	 "4999G"l^3<.8XYZZEN]ds%7L>IJJJcdH v#LIVdlq  }E  PX  ag  qu  v  v  v  	v  h  h  g!1  g	]k  CH  bj  x@  LR  _f  g  g  g  h  h  hs   8FHHHc                    dd fd}t           |             |d           |d           |d	           |d
           |d           |d           |d           |d           |d           |d           |d          |          S )N1=1wherer   r`   rA   c                    t                              d|                                            d         pd          S )Nz=SELECT COUNT(*) AS c FROM instrument_import_candidates WHERE r`  r   )rA   rT  fetchone)r  rD  s    r>   countz#summarize_candidates.<locals>.count  s?    4<< g`e g ghhqqsstwx}|}~~~r=   z)COALESCE(proposed_isin, raw_isin, '')<>''z(COALESCE(proposed_isin, raw_isin, '')=''z-COALESCE(proposed_ticker, raw_ticker, '')<>''z1COALESCE(proposed_currency, raw_currency, '')<>''z!mapping_status='exact_isin_match'zmapping_status='probable'z$mapping_status='needs_manual_review'zmapping_status='confirmed'zmapping_status='rejected'zpmapping_status='confirmed' AND COALESCE(raw_quantity,'')<>'' AND COALESCE(proposed_currency,raw_currency,'')<>''zmapping_status!='confirmed')rB   rC   rD   rE   rF   r   r   r   r   r   rG   rH   rQ   )r  )r  r   r`   rA   )r@   )rD  rQ   r  s   `  r>   r{  r{    s          %CDDUEFFEIJJeOPPBCC233!E"HII%455233"U  $V  W  W344   r=   candidate_iddict[str, str]c                   |                      d|f                                          }|t          d          t          |          }|                    d          p|                    d          pd                                }|                    d          p|                    d          pd                                }|                    d          pd                                }|                    d	          p|                    d
          pd                                }d}|rI|                      d|f                                          d         }	t          |	pd          dk    rdnd}nP|rN|rL|rJ|                      d|||f                                          d         }	t          |	pd          dk    rdnd}|                      d|t                      |f           |                                  ||dS )N?SELECT * FROM instrument_import_candidates WHERE candidate_id=?candidate not foundr0   r'   r"   r/   r&   r1   r2   r(   r   zASELECT COUNT(*) AS c FROM instrument_catalog_entries WHERE isin=?r`  r   r   r   zSELECT COUNT(*) AS c FROM instrument_catalog_entries WHERE upper(ticker)=? AND upper(exchange)=? AND upper(COALESCE(trading_currency,instrument_currency,''))=?r   z{UPDATE instrument_import_candidates SET mapping_status=?, updated_at=? WHERE candidate_id=? AND mapping_status!='confirmed')r  r6   )	rT  r  rR  dictr9  r   rA   r   rz  )
rD  r  rowitemr   r   exchanger   r   matchess
             r>   resolve_candidater    sX   
,,X[gZi
j
j
s
s
u
uC
{.///99DHH_%%C*)=)=CJJLLDhh())ITXXl-C-CIrPPRRF,--3::<<H,--O.1I1IORVVXXH"F Q,,beidklluuwwx{|'*7<a'8'8A'='=##CX	 QH Q Q,,   A  DJ  LT  V^  C_  `  `  i  i  k  k  lo  p"7<a00A55;PLL  O  RX  Za  Zc  Zc  eq  Qr  s  s  sKKMMM(FCCCr=   )r0   r/   r1   r2   r+  r0   r/   r1   r2   r   c               r   t          |          }|st          d          |                     d|f                                          }|t          d          ||n|d         p	|d         pd                                                                }||n|d         p	|d         pd                                                                }	||n	|d	         pd                                                                }
||n|d
         p	|d         pd                                                                }|s|	r|
r|st          d          |                     d||	|
||t                      |f           t          | ddd|dt          |          d|dd	  	         | 	                                 d S )Nreview note is requiredr  r  r0   r'   r"   r/   r&   r1   r2   r(   z=confirmed candidate requires ISIN or ticker+exchange+currencyzUPDATE instrument_import_candidates SET proposed_isin=?, proposed_ticker=?, proposed_exchange=?, proposed_currency=?, mapping_status='confirmed', review_note=?, updated_at=? WHERE candidate_id=?instrument_import_reviewconfirm_candidaterG  r   )r6   rI  T	dashboardsourceactionrL  rM  
new_valuesuser_text_noter   
created_by)
rg   rR  rT  r  re   r   r   r   rI   rz  )rD  r  r+  r0   r/   r1   r2   r  r   r   r  r   s               r>   r  r    s]   tD 42333
,,X[gZi
j
j
s
s
u
uC
{.///*6MMC<P<iTWXbTc<igipprrxxzzD!0!<oo#FWBXBs\_`l\mBsqszz||  C  C  E  EF%6%B!!L_H`HfdfmmoouuwwH%6%B!!L_H`H}dghvdwH}{}  E  E  G  G  M  M  O  OH Z ZH Z ZXYYYLL 	M	vx4LI   t$>GZ  iF  R^  ~I  W[  \`  Wa  Wa  kb  kb  sw  CG  T_  `  `  `  `KKMMMMMr=   c                   t          |          }|st          d          |                     d|t                      |f           t	          | ddd|ddi|dd	
	  	         |                                  d S )Nr  zsUPDATE instrument_import_candidates SET mapping_status='rejected', review_note=?, updated_at=? WHERE candidate_id=?r  reject_candidaterG  r6   r   Tr  r  )rg   rR  rT  r   r   rz  )rD  r  r+  s      r>   r  r    s    tD 42333LL  G  JN  PW  PY  PY  [g  Ih  i  i  it$>GY  hE  Q]  k{  }G  jH  Y]  im  zE  F  F  F  FKKMMMMMr=   later reviewr+  c               B   t          |          pd}|                     d|f                                          }|t          d          |                     d|t	                      |f           t          | ddd|dd	i|d
d	  	         |                                  d S )Nr  zJSELECT candidate_id FROM instrument_import_candidates WHERE candidate_id=?r  zUPDATE instrument_import_candidates SET mapping_status='needs_manual_review', review_note=?, updated_at=? WHERE candidate_id=? AND mapping_status!='confirmed'r  defer_candidaterG  r6   r   Tr  r  )rg   rT  r  rR  r   r   rz  )rD  r  r+  r  s       r>   r  r    s   t.D
,,cfret
u
u
~
~  A  AC
{.///LL  r  uy  {B  {D  {D  FR  tS  T  T  Tt$>GX  gD  P\  jz  |Q  iR  cg  sw  DO  P  P  P  PKKMMMMMr=   
account_idsnapshot_dateconfirmc                  |st          d          |                     d|f                                          }|t          d          |d         dk    rt          d          |d         p	|d         pd	                                                                }|d
         p	|d         pd	                                                                }|d         pd	                                                                }	|d         p	|d         pd                                                                }
t          |d         p	|d         pd          }|s|r|	r|
st          d          	 t          t          |d                                       dd	                              dd	                              dd                     n)# t          t           f$ r}t          d          |d }~ww xY wt          | t          |d         dk    rdnd||||	|
|
|d         pd|d         pd d |rd!nd"#          |$          }t          | ||d%t          |d                                       dd	                              dd	                              dd          ||
d |
dk    rd&nd'|d()          }|                     d*||j        f           t          | d+d,d-|j        |d(d.|d(d/0	  	         |                                  |j        S )1Nzexplicit confirm is requiredr  r  r6   r   z)only confirmed candidates can be importedr0   r'   r"   r/   r&   r1   r2   r(   r   r.   r%   zReviewed instrumentz:confirmed import requires ISIN or ticker+exchange+currencyr)   r   rb   ,.z'candidate quantity must be Decimal textr3   r   r   r4   r   r5   broker_reviewr   r   )r*  rm   r   r   r  trading_currencyinstrument_currencyr4   r5   r  source_confidencer  initial_snapshot
not_neededmissingT)
catalog_entry_idr  position_typequantity_text
trade_dater   cost_basis_original_text	fx_statusr+  r  ziUPDATE transactions SET source_type='broker_import_reviewed_snapshot', source_id=? WHERE transaction_id=?r  #import_confirmed_candidate_snapshottransaction)r  cost_basis_uncertainr  r  )rR  rT  r  re   r   rg   r   r   r   r   r   r   r   transaction_idr   rz  )rD  r  r  r  r+  r  r  r   r   r  r   rm   excrU  results                  r>   r  r    s    97888
,,X[gZi
j
j
s
s
u
uC
{.///
++DEEE 9C
O9r@@BBHHJJD#$?L(9?RFFHHNNPPF'(.B5577==??H'(HC,?H5OOQQWWYYHs?+Ws:WBWXXD W WH W WUVVVMC'((00b99AA#rJJRRSVX[\\]]]]j) M M MBCCLM
t%6CPfLgkpLpLp55v~  FJ  QU  ^d  ow  JR  hp  {~  I  {J  {V  NV  hk  l}  h~  hF  BF  O^  |@  rN  rx  rx  FN  &O  &O  &O  VZ  [  [  [C-dSU_  pB  RU  VY  Zh  Vi  Rj  Rj  Rr  Rr  sv  xz  R{  R{  RC  RC  DG  IK  RL  RL  RT  RT  UX  Z]  R^  R^  kx  CK  fj  FN  RW  FW  FW  vB  vB  ]f  mq  {  @  @  @FLL|  @L  NT  Nc  d  e  e  et$>Gl  {H  TZ  Ti  GS  mq  vr  vr  CG  SW  do  p  p  p  pKKMMM  s   .AG G3G..G3dict[str, int]c                2   |                      d                                          }t          |d         pd          t          |d         pd          t          |d         pd          t          |d         pd          t          |d         pd          dS )	Na  
        SELECT
            COUNT(*) AS total,
            SUM(CASE WHEN mapping_status='exact_isin_match' THEN 1 ELSE 0 END) AS exact,
            SUM(CASE WHEN mapping_status='probable' THEN 1 ELSE 0 END) AS probable,
            SUM(CASE WHEN mapping_status='needs_manual_review' THEN 1 ELSE 0 END) AS needs_review,
            SUM(CASE WHEN mapping_status='confirmed' THEN 1 ELSE 0 END) AS confirmed
        FROM instrument_import_candidates
        totalr   exactr   needs_reviewr   )r  r  r   r  r   )rT  r  rA   )rD  r  s     r>   _status_countsr    s    
,,	
 
 hjj  W*++c#g,:K!6L6LZ]^abl^m^rqrZsZs  FI  JM  N\  J]  Jb  ab  Fc  Fc  ru  vy  zE  vF  vK  JK  rL  rL  M  M  Mr=   r<  dict[str, object]%tuple[dict[str, object] | None, bool]c               T   t          j        |                    d           dt          j        |                               d          ddi          }	 t          j        ||          5 }t          j        |                                	                    d                    }d d d            n# 1 swxY w Y   n.# t          t          j        t          t          j        f$ r Y dS w xY w|                    d	i g          d
                             di                               dd          }t!          j        dt%          |          t           j                  }|si dfS 	 t          j        |                    d
                    }n# t          j        $ r i dfcY S w xY w|dfS )Nr  r  ri   r   r!  r"  r$  )NFr%  r   r&  r  r"   r'  T)r   r.  r/  r0  r1  rn   r2  r3  r   r4  r5  r   r6  r7  r8  r9  rc   r   r   r:  r   )	r<  r	  r
  r=  r>  r#  r  r?  r@  s	            r>   _qwen_chat_jsonr    s   
/X__S11DDD4:V]K^K^KeKefmKnKn  zH  J\  y]  ^  ^  ^C_S'222 	;d:diikk0099::D	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	;U^\43GH   {{hhy2$''*..y"==AA)RPPGIiWrt44E 4xEKKNN++   4x4<sH   B> ,:B2&B> 2B66B> 9B6:B> >'C)(C)%'F F#"F#-   r   )r	  rU   limitrQ   r
  
batch_sizer  
int | Noner  c                  |p)t          j        d          pt          j        d          pd}|pt          j        dd          }t          |           }|                     d                                          }|
|d|         }g }	|D ]^}
t          |
          }|	                    |d         |                    d	          pd
|                    d          pd
|                    d          p|                    d          pd
|                    d          p|                    d          pd
|                    d          p|                    d          pd
|                    d          p|                    d          pd
|                    d          p|                    d          pd
|                    d          pd
|                    d          pdd
           `g }d}t          d|          }t          dt          |	          |          D ]}|	|||z            }|ddddd d!d"t          j        d#|d$d%          d!gd&}t          |||'          \  }}|sd} n`t          |t
                    rJ|                    d(g           }t          |t                    r|                    d) |D                        |sbt          |           }t#          dd||d*         |d+         |d+         |d,         |d,         |d-         |d-         |d.         |d-         d|/          S d(|i}d0 |D             }d}d}t          |t
                    r|pi                     d(g           ng D ]}t%          |                    d          pd
          }||vr|dz  }1||         }|                    d          p|                    d          pd
                                                                }t%          |                    d          pd
                                                                          }|r||k    r|}|dz  }t%          |                    d          p+|                    d          p|                    d          pd
                                                                          }t%          |                    d          p|                    d          pd
                                                                          }t%          |                    d          p+|                    d          p|                    d          pd
                                                                          }t+          t%          |                    d          p+|                    d          p|                    d          pd
                    dd1         } t+          t%          |                    d          p+|                    d          p|                    d          pd2                                                    }!t%          |                    d          pd                                          }"|rd3}#n|r|r|r	|"d,k    rd,}#nd}#t+          t%          |                    d4          pd5                    dd6         }$|#|                    d          k    r|dz  }|s,|                     d7| |||||!|#|$t/                      |f
           |s|                                  t          |           }t#          dHi d8dd9dd:|d;|d*         d<|d+         d=|d+         d>|d,         d?|d,         d@|d-         dA|d-         dB|d.         dC|dD|d-         dE|dFddG|S )IzgRun local Qwen over sanitized staged candidates only. No quantities, values, accounts or ledger writes.r  r  r  r  r  a  
        SELECT candidate_id, platform, source_row_number, raw_name, raw_ticker, raw_isin, raw_currency,
               raw_asset_class, proposed_name, proposed_ticker, proposed_isin, proposed_exchange,
               proposed_currency, proposed_asset_class, mapping_status, review_note
        FROM instrument_import_candidates
        WHERE mapping_status NOT IN ('confirmed','rejected')
        ORDER BY platform, source_row_number
        Nr  r!   r"   r$   r.   r%   r/   r&   r0   r'   r2   r(   r3   r*   r1   r6   r   )
r  r!   source_row_idproduct_namer   r   r   r*  r  current_statusTr   r   i   Fr  a>  You are a conservative equity/ETF import review assistant. Return JSON only. Never invent ISINs, quantities, cost basis, transaction history, account numbers or valuations. If ISIN is empty, do not add one. Ticker alone is not enough for confirmation. Use statuses exact_isin_match, probable, needs_manual_review only.r  r  aF  Classify sanitized instrument import candidates for human review. Do not confirm anything. Return {updates:[{candidate_id, proposed_name, proposed_ticker, proposed_isin, proposed_exchange, proposed_currency, proposed_asset_class, mapping_status, review_note}]}. If an ISIN is not present in input, proposed_isin must be empty.)task
candidates)ensure_ascii)rU   r  
max_tokensstreamr  )r	  r
  updatesc                <    g | ]}t          |t                    |S r<   )
isinstancer  )r   us     r>   r   z2qwen_resolve_staged_candidates.<locals>.<listcomp>c  s'    #N#N#N!*Q:M:M#NA#N#N#Nr=   r  r  r   r  r   )rR   rJ   rU   rB   rV   rW   rX   rY   rZ   r[   r\   r^   rP   rQ   c                T    i | ]%}t          |d                    t          |          &S )r  )r   r  )r   rs     r>   
<dictcomp>z2qwen_resolve_staged_candidates.<locals>.<dictcomp>h  s-    ???QQ~&''a???r=   r   r   r   r7   z7Qwen review helper: manual review required; no booking.r  aj  
                UPDATE instrument_import_candidates
                SET proposed_name=?, proposed_ticker=?, proposed_isin=?, proposed_exchange=?, proposed_currency=?,
                    proposed_asset_class=?, mapping_status=?, review_note=?, updated_at=?
                WHERE candidate_id=? AND mapping_status NOT IN ('confirmed','rejected')
                rR   rJ   rU   rB   rV   rW   rX   rY   rZ   r[   r\   r]   r^   rO   rP   rQ   r<   )r,  r-  r  rT  fetchallr  r   r9  r   ranger   r0  r1  r  r  listextendrT   r   re   r   rg   r;  r   rz  )%rD  r	  rU   r  rQ   r
  r  beforerowsr  r  dall_updatesr  	start_idxbatchr<  r@  batch_availabler  after	row_by_idr]   rO   updrU  currentexisting_isinq_isinq_ticker
q_exchange
q_currencyq_nameq_asset_classrequested_statusr   r+  s%                                        r>   qwen_resolve_staged_candidatesr  -  sk
     E29%;<<  E	J_@`@`  E  eEH>RY|-=>>ED!!F<<		 	 hjj 	 FUF|I  GGn-j))/RUU#677=2EE/22MaeeJ6G6GM2ee-..K!%%2E2EKEE/**EaeeJ.?.?E2122QaeeN6K6KQr55!788ZAEEBS<T<TZXZ1228bee$455N9N
 
 	 	 	 	 ,.KIQ
##J1c)nnj99 P P	)I
$::;!  /o  p  pDJ  AI  Y^  8_  8_  ns  -t  -t  -t  u  u	
 	
 #2'HV]"^"^"^ 	IEfd## 	PjjB//G'4(( P""#N#Nw#N#N#NOOO |t$$!%ugmnugv  FL  MT  FU  ch  ip  cq  CI  JT  CU  fk  lv  fw  MS  Tb  Mc  x}  ~L  xM  _d  ep  _q  KP  Q_  K`  ij  t{  |  |  |  	|%F??$???IHH4>vt4L4LT"!!)R000RT $ $#''.))/R00iMHC. _55VZ9P9PVTV]]__eeggSWW_--344::<<BBDD 	f--"FMHsww011vW[[AR5S5SvW^WbWbcoWpWpvtvww}}  F  F  H  H!455_EX9Y9Y_]_``ffhhnnpp
!455~EX9Y9Y~]d]h]hiw]x]x~|~  F  F  H  H  N  N  P  P
S!9!9!zW[[=Y=Y!z]d]h]his]t]t!zxz{{||  ~B  B  ~B  C#C0F(G(G  )]7;;WmKnKn  )]ryr}r}  P  sQ  sQ  )]  U]  %^  %^  _  _  e  e  g  gsww'788Q<QRRXXZZ 	+'FF 	+* 	+ 	+8HJ8V8VFF*F3sww}55r9rssttuyvyuyzW[[!12222MH 		LL 6:z=RXZ^`g`i`ikno    4  E  [  [  [TT  [TT  [  [afgnaoao  [  E  FM  N  N  [  \a  bi  \j  \j  [  |B  CM  |N  |N  [  _d  eo  _p  _p  [  FL  M[  F\  F\  [  qv  wE  qF  qF  [  X]  ^i  Xj  Xj  [  u}  u}  [  W\  ]k  Wl  Wl  [  w  w  [  HI  HI  [  SZ  SZ  [  [r=   )r_   r   r`   r   )r    r   r`   r   )rr   r   r`   r   )rv   rw   r`   r   )rv   rw   r`   r{   )r   r   r`   rI   )r   r   r`   r   )r   r   r   r   r   r   r`   r   )rv   rw   r!   r   r#   r   r`   r   )r  r{   r    r   r!   r   r#   r   r	  r  rU   r  r
  rA   r`   r  )rD  r	   rr   r   rQ   rI   r`   r   )rX  r   rJ   rI   rR   rI   rY  rA   rZ  rA   rO   rA   rP   rA   rQ   rI   r`   r@   )
rD  r	   rq  rr  rp  rI   rQ   rI   r`   r@   )rD  r	   rQ   rI   r`   r@   )rD  r	   r  r   r`   r  )rD  r	   r  r   r+  r   r0   r  r/   r  r1   r  r2   r  r`   r   )rD  r	   r  r   r+  r   r`   r   )rD  r	   r  r   r  r   r  r   r+  r   r  rI   r`   r   )rD  r	   r`   r  )r<  r  r	  r   r
  rA   r`   r  )rD  r	   r	  r  rU   r  r  r  rQ   rI   r
  rA   r  rA   r`   rT   )I
__future__r   rk   r0  r,  rc   r   dataclassesr   r   decimalr   r   pathlibr   sqlite3r	   typingr
   urllibr   r   xml.etree.ElementTreeetreeElementTreer   jarvis_finance.audit.logr   jarvis_finance.equity.manager   jarvis_finance.imports.commonr   r   "jarvis_finance.market_data.catalogr   r   jarvis_finance.quality.alertsr   rS  rQ  compiler   r   r   r   r   r   r@   rT   rg   rq   ru   rz   r   r   r   r   r   r  r  rB  rW  ro  r  r{  r  r  r  r  r  r  r  r  r<   r=   r>   <module>r     sZ   " " " " " "   				 				  ( ( ( ( ( ( ( ( - - - - - - - -                   ! ! ! ! ! ! ! ! " " " " " " " " " 7 7 7 7 7 7 I I I I I I < < < < < < < < V V V V V V V V 6 6 6 6 6 6ggg CCC 
"*4
5
5bj=rtDDBJBCC	RZvww
 $       2 $! ! ! ! ! ! ! !0 $       $8 8 8 8H H H H
         = = = =      X X X X QS Y Y Y Y Y Yx    gi  BF  \`  qs , , , , , ,^ PU % % % % % %P   0 ^cty #h #h #h #h #h #hL ?D      (D D D D* fj  JN  pt  VZ      *    IW      ! ! ! !8M M M M   $ PTim  DH  Z_  pr  FG f[ f[ f[ f[ f[ f[ f[ f[r=   