
    \njE                       d dl mZ d dlZd dlZd dlmZ d dlmZ d dl	m
Z
mZmZmZmZmZmZmZmZmZmZmZmZmZmZ d dlmZmZmZ d dlmZ d dlm Z m!Z! d6dZ"d6dZ#d Z$ddddddddZ%d Z&dddddddddZ'd7d!Z(d7d"Z)d7d#Z*d7d$Z+d7d%Z,d7d&Z-d7d'Z.d7d(Z/d8d)Z0d7d*Z1d8d+Z2d7d,Z3d8d-Z4d8d.Z5d8d/Z6d8d0Z7d8d1Z8d8d2Z9d8d3Z:d7d4Z;d8d5Z<dS )9    )annotationsN)Path)data)!assert_productive_import_disabledassert_write_enabled#confirm_review_item_account_mapping&confirm_review_item_instrument_mapping!confirm_review_item_snapshot_dateconfirm_review_item_mapping create_broker_import_review_itemget_broker_review_itemsget_dry_run_sessionsignore_review_itemmark_review_item_resolvedrun_broker_import_dry_runupdate_dry_run_session_statusupdate_review_item_isin"update_review_item_ticker_exchange)parse_postfinance_docxparse_raiffeisen_xlsxparse_true_wealth_docx)connect_memory)apply_migrationsutc_nowpathr   rowslist[list[str]]returnNonec                   ddd                     fd|D                       }d| d}t          j        | d	          5 }|                    d
d           |                    d|           d d d            d S # 1 swxY w Y   d S )Ntextstrr   c                    d|  dS )Nz<w:tc><w:p><w:r><w:t>z</w:t></w:r></w:p></w:tc> )r!   s    1tests/unit/test_broker_parser_dry_run_pipeline.pytcz_docx.<locals>.tc"   s    FtFFFF     c              3  f   K   | ]+}d d                     fd|D                       z   dz   V  ,dS )z<w:tr>r(   c              3  .   K   | ]} |          V  d S Nr$   ).0cr&   s     r%   	<genexpr>z"_docx.<locals>.<genexpr>.<genexpr>$   s+      $8$8qRRUU$8$8$8$8$8$8r'   z</w:tr>N)join)r,   rowr&   s     r%   r.   z_docx.<locals>.<genexpr>$   sN      UU(RWW$8$8$8$8C$8$8$88889DUUUUUUr'   z<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<w:document xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"><w:body><w:tbl>z</w:tbl></w:body></w:document>w[Content_Types].xmlzword/document.xml)r!   r"   r   r"   )r/   zipfileZipFilewritestr)r   r   trsxmlzfr&   s        @r%   _docxr9   !   s    G G G G
''UUUUPTUUU
U
UCIcfI I IC	s	#	# .r
)2...
'---. . . . . . . . . . . . . . . . . .s   -A<<B B c           	     *   g }i }g }t          |d          D ]\  }}g }t          |          D ]\  }}	|	|vr't          |          ||	<   |                    |	           t          t	          d          |z             t          |          z   }
|                    d|
 d||	          d           |                    d| dd	                    |          z   d
z              dd	                    d |D                       z   dz   }dd	                    |          z   dz   }t          j        | d          5 }|	                    d|           |	                    d|           |	                    dd	           d d d            d S # 1 swxY w Y   d S )N   )startAz<c r="z" t="s"><v>z</v></c>z<row r="z">r(   z</row>zG<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">c              3  "   K   | ]
}d | dV  dS )z<si><t>z	</t></si>Nr$   )r,   ss     r%   r.   z_xlsx.<locals>.<genexpr>8   sT        eM  eM  ABe{ope{e{e{  eM  eM  eM  eM  eM  eMr'   z</sst>zX<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData>z</sheetData></worksheet>r1   zxl/sharedStrings.xmlzxl/worksheets/sheet1.xmlr2   )
	enumeratelenappendchrordr"   r/   r3   r4   r5   )r   r   sharedindex
sheet_rowsr_idxr0   cellsc_idxvalueref
shared_xml	sheet_xmlr8   s                 r%   _xlsxrO   ,   sB   FEJA... L L
s%cNN 	J 	JLE5E!!"6{{eFMM%,@,@,@c#hh&''#e**4CLLH#HH%,HHHIIII.U...?(JKKKKZ]_]d]d  eM  eM  FL  eM  eM  eM  ^M  ^M  M  PX  XJjmomtmtu  nA  nA  A  D^  ^I	s	#	# /r
*J777
.	:::
)2.../ / / / / / / / / / / / / / / / / /s   8AFFFc                 @    t                      } t          |            | S r+   )r   r   r-   s    r%   connrR   @   s     AQHr'   inst-1etfCH0000000001METFSIXCHF)instrument_idasset_classisintickerexchangecurrencyc               Z    |                      d||d||||t                      f           d S )NzINSERT INTO instruments(instrument_id, asset_class, name, ticker, isin, exchange, currency, created_at) VALUES (?,?,?,?,?,?,?,?)
Mapped ETFexecuter   )r-   rY   rZ   r[   r\   r]   r^   s          r%   seed_instrumentrc   F   sz    II  Q  Ta  cn  p|  ~D  FJ  LT  V^  `g  `i  `i  Sj  k  k  k  k  kr'   c                    |                      dt                      f           |                      dt                      f           |                      dt                      f           d S )NzjINSERT INTO platforms(platform_id, name, platform_type, created_at) VALUES ('pf','PostFinance','broker',?)zINSERT INTO accounts(account_id, platform_id, account_name, account_type, currency, created_at) VALUES ('acct-broker','pf','Brokerage','brokerage','CHF',?)zINSERT INTO accounts(account_id, platform_id, account_name, account_type, currency, created_at) VALUES ('acct-cash','pf','Cash','cash','CHF',?)ra   rQ   s    r%   seed_accountsre   J   s    IIz  ~E  ~G  ~G  }I  J  J  JII  l  ov  ox  ox  nz  {  {  {II  `  cj  cl  cl  bn  o  o  o  o  or'   PostFinanceTopen)sourcerZ   r^   flagsquantitymarket_valuestatusc                   dt          j                     }|                     d|||t                      f           t	          | ||ddd|||||pg d|          S )Nzdry-aP  INSERT INTO broker_import_dry_runs(dry_run_id, source_name, source_platform, source_file_label, source_file_type, source_hash, source_filename_hash, detected_sections_json, rows_total, quality_flags_json, summary_json, created_at, snapshot_date_status) VALUES (?, ?, ?, '[redacted]', 'docx', 'h', 'h', '[]', 1, '{}', '{}', ?, 'missing')docxzrow-1zSynthetic Label)missing_isinmissing_tickersnapshot_onlycost_basis_uncertain)
dry_run_idsource_platformsource_file_typesource_row_refsource_labeldetected_asset_classdetected_currencydetected_quantity_presentdetected_market_value_presentquality_flagsreview_status)uuiduuid4rb   r   r   )	r-   rh   rZ   r^   ri   rj   rk   rl   dry_ids	            r%   seed_review_itemr   P   s    "DJLL""FII  a  dj  lr  tz  |C  |E  |E  cF  G  G  G+A&RXkq  CJ  Yj  AL  `h  DL  lx  HM  H\  Q\  Q\  Q\  lr  s  s  s  sr'   tmp_pathc                   | dz  }t          |g dg dg dg           t          |          }|j        dk    sJ |j        dk    sJ |j        dk    sJ |j        }|d         dk    sJ |d         dk    sJ |d	         dk    sJ d S )
Nsynthetic_postfinance.docx)ProduktMenge   Währung	MarktwertStichtag 31.12.2025)zSynthetic ETFMenge vorhandenrX   Marktwert vorhandenzEinstand legacy)
Cash Kontor(   rX   r(   r(   rf   r;   ro   rp   market_value_legacy)r9   r   rt   candidate_positionscandidate_cash_rowsquality_flags_summary)r   r   parsedri   s       r%   Itest_synthetic_postfinance_parser_recognizes_candidates_and_quality_flagsr   V   s    22D	$UUU  Xm  Xm  Xm  oP  oP  oP  Q  R  R  R#D))F!]2222%****%****(E A%%%%!"a''''&'1,,,,,,r'   c                    | dz  }t          |g dg dg dg           t          |          }|j        dk    sJ |j        dk    sJ |j        d         dk    sJ |j        d         dk    sJ d S )	Nsynthetic_true_wealth.docx)PositionAnteiler   Wertr   )
Global ETFzAnteile vorhandenUSDWert vorhanden	Portfolio)zCash USDr(   r   r   r(   r;   
missing_fx   rr   )r9   r   r   r   r   r   r   r   s      r%   Ftest_synthetic_true_wealth_parser_recognizes_candidates_and_missing_fxr   c   s    22D	$SSS  V_  V_  V_  aN  aN  aN  O  P  P  P#D))F%****%****'5::::'(>?1DDDDDDr'   c                   | dz  }t          |g dg dg dg           t          |          }|j        dk    sJ |j        dk    sJ |j        d         dk    sJ |j        d         dk    sJ |j        d         dk    sJ d S )	Nsynthetic_raiffeisen.xlsx)LabelTypr   SaldozDatum 31.12.2025)PrivatkontoKontorX   zSaldo vorhandenr(   )Depot Anlagen gesamtAnlagerX   r   r(   r;   cash_snapshot_candidateaggregate_onlymissing_instrument_details)rO   r   r   r   r   r   s      r%   Otest_synthetic_raiffeisen_parser_recognizes_cash_and_aggregate_control_snapshotr   m   s    11D	$JJJ  MC  MC  MC  ED  ED  ED  E  F  F  F"4((F%****%****'(ABaGGGG'(89Q>>>>'(DEJJJJJJr'   c                ,   | dz  }t          |g dg dg dg           t                      }t          |t          |          |j                  }|d         dk    sJ |d         d	k    sJ |d
         dk    sJ |                    d                                          d         dk    sJ |                    d                                          d         d	k    sJ |                    d                                          d         dk    sJ d S )Nr   )r   r   r   r   )Name Only ETFr   rX   r   )r   r(   rX   r(   source_filename
rows_total   review_items_createdr   blocked_positionsr;   z0SELECT COUNT(*) AS n FROM broker_import_dry_runsnz4SELECT COUNT(*) AS n FROM broker_import_review_items,SELECT COUNT(*) AS n FROM positions_snapshotr   r9   rR   r   r   namerb   fetchone)r   r   r-   summarys       r%   Rtest_dry_run_pipeline_writes_summary_and_review_items_without_productive_positionsr   x   sd   22D	$>>>  AC  AC  AC  Eb  Eb  Eb  c  d  d  dA'+A$+G+GY]YbcccG< A%%%%)*a////&'1,,,,99GHHQQSSTWX\]]]]]99KLLUUWWX[\`aaaaa99CDDMMOOPSTXYYYYYYYr'   c                "   | dz  }t          |g dg dg           t                      }t          |t          |          |j                   |                    d                                          }|d         dk    sJ d|d	         v sJ d S )
Nr   r   r   r   )r   r   rX   r   z(SELECT * FROM broker_import_review_itemsr}   rg   ro   quality_flags_jsonr   )r   r   r-   items       r%   @test_name_without_isin_stays_needs_manual_review_via_review_itemr      s    22D	$1113^3^3^_```Aa!7!=!=tyYYYY99?@@IIKKD F****T"67777777r'   c                   | dz  }t          |g dg dg           t                      }t          |t          |          |j                   |                    d                                          }|d         dk    sJ d|d	         v sJ |                    d
                                          d         dk    sJ d S )Nr   r   r   r   r   r   rX   r   zHSELECT review_status, quality_flags_json FROM broker_import_review_itemsr}   blockedr   r   r   r   r   )rO   rR   r   r   r   rb   r   )r   r   r-   r0   s       r%   Atest_raiffeisen_aggregate_only_is_blocked_not_productive_positionr      s    11D	$---/X/X/XYZZZAa!6t!<!<diXXXX
))^
_
_
h
h
j
jC9,,,,s#78888899CDDMMOOPSTXYYYYYYYr'   c                6   | dz  }t          |ddgddgg           t                      }t          |t          |          |j                   t          |          sJ d t          j        |          D             }|d         d	k    sJ |d
         d	k    sJ d S )Nr   r   r   r   r   r   c                ,    i | ]}|d          |d         S checkcountr$   r,   rs     r%   
<dictcomp>zTtest_manual_review_queue_and_data_quality_aggregate_review_flags.<locals>.<dictcomp>   "    	M	M	MQ!G*aj	M	M	Mr'   zReview Flag: missing_isin1zReview Flag: missing_fx)r9   rR   r   r   r   r   dashboard_dataget_data_qualityr   r   r-   dqs       r%   @test_manual_review_queue_and_data_quality_aggregate_review_flagsr      s    22D	$*j)L%+@ABBBAa!7!=!=tyYYYY"1%%%%%	M	M.*I!*L*L	M	M	MB)*c1111'(C//////r'   c                   | dz  }t          |g dg dg           t                      }t          |t          |          |j                   d t          j        |          D             }|d         dk    sJ |d         dk    sJ d S )	Nr   r   r   r   c                ,    i | ]}|d          |d         S r   r$   r   s     r%   r   zRtest_data_quality_includes_blocked_aggregate_only_review_flags.<locals>.<dictcomp>   r   r'   zReview Flag: aggregate_onlyr   z'Review Flag: missing_instrument_details)rO   rR   r   r   r   r   r   r   s       r%   >test_data_quality_includes_blocked_aggregate_only_review_flagsr      s    11D	$---/X/X/XYZZZAa!6t!<!<diXXXX	M	M.*I!*L*L	M	M	MB+,333378C??????r'   c                     t                      } t          j        |           }|d         dk    sJ 	 t          d           t	          d          # t
          $ r Y d S w xY w)N
write_modez	Read-onlyF)write_enabledz$read-only commit guard did not block)rR   r   get_safe_mode_statusr   AssertionErrorPermissionError)r-   rl   s     r%   4test_safe_mode_default_read_only_and_commit_disabledr      s}    A033F,;....E51111 CDDD    s   A 
AAc                   | dz  }t          |g dg dg           t                      }t          |           t          |t	          |          |j                   |                    d                                          d         }t          ||dd	          }|sJ |                    d
|f                                          d         dk    sJ d S )Nr   r   )r`   r   rX   r   z5SELECT review_item_id FROM broker_import_review_itemsreview_item_idrS   zSynthetic B2 confirmationr   rY   note5SELECT COUNT(*) AS n FROM audit_log WHERE entity_id=?r   r;   )	r9   rR   rc   r   r   r   rb   r   r   )r   r   r-   r   audit_ids        r%   2test_review_item_mapping_confirmation_writes_auditr      s    22D	$1113[3[3[\]]]A"""a!7!=!=tyYYYYYYVWW``bbcstN*1^[c  kF  G  G  GHOO899L~N_``iikkloptuuuuuuur'   c                 r    t          d          } d |                     d          D             }|g k    sJ d S )Nz(/home/agent/.hermes/repos/FinanceManagerc                ~    g | ]:}|j                                         d v d|j        v&d|j        v/d|j        v8|;S )>   .pdf.docx.xlsxtestsexamplesdocs)suffixlowerparts)r,   ps     r%   
<listcomp>z5test_no_real_broker_files_in_repo.<locals>.<listcomp>   s      r  r  rqqx~~/?/?C]/]/]biqrqxbxbx  ~H  PQ  PW  ~W  ~W  \b  jk  jq  \q  \q  \q  \q  \qr'   *)r   rglob)repo	forbiddens     r%   !test_no_real_broker_files_in_repor      sM    :;;D r  rDJJsOO  r  r  rI??????r'   c                   t                      }| dz  }t          |g dg dg           | dz  }t          |ddgddgg           t          |t          |          |j        	          }t          |t          |          |j        	          }t          t          |d
                    dk    sJ t          t          ||d                             dk    sJ t          t          |d                    dk    sJ d S )Npf.docxr   zETF Ar   rX   ztw.docxr   r   zETF Br   r   rf   )rt   r;   rs   )rs   r   )quality_flag)rR   r9   r   r   r   r   rA   r   )r   r-   p1p2s1s2s         r%   ?test_b4_review_queue_filters_by_source_dry_run_and_quality_flagr      s!   A	I	BuR*J*J*JLeLeLe)fggg	I	BuR:z*BWeDT)UVVV	"1&<R&@&@RTRY	Z	Z	ZB	"1&<R&@&@RTRY	Z	Z	ZB&q-HHHIIQNNNN&qR5EFFFGG1LLLL&q|DDDEEJJJJJJr'   c                    t                      } t          |           }t          | |dd           t          | |dd           |                     d|f                                          }|d         dk    sJ |d	         dk    sJ d
|d         vsJ |d         dk    sJ |                     d|f                                          d         dk    sJ d S )NrU   zsynthetic isinr   r[   r   rV   zsynthetic tickerr   r\   r   z?SELECT * FROM broker_import_review_items WHERE review_item_id=?r[   r\   ro   r   r}   rg   r   r   r   )rR   r   r   r   rb   r   r-   r   r0   s      r%   Itest_b4_isin_ticker_exchange_updates_write_audit_and_name_only_not_mappedr     s    A'**AdN^____&qfSeffff
))UX\W^
_
_
h
h
j
jCv;.((((x=F""""%9!:::::6))))99LtgVV__aabefjkkkkkkkr'   c                     t                      } t          |           }t          | |dd           |                     d|f                                          }|d         dv sJ d|d         v sJ d S )	NrV   zticker onlyr  ziSELECT import_readiness_status, quality_flags_json FROM broker_import_review_items WHERE review_item_id=?import_readiness_status>   	not_readyreview_neededticker_without_exchanger   )rR   r   r   rb   r   r  s      r%   3test_b4_ticker_without_exchange_stays_review_neededr     s    A'**&qfS`aaaa
))  CG  BI  J  J  S  S  U  UC()-KKKKK$,@(AAAAAAAr'   c                    t                      } t          | d           t          |            t          | d          }t	          | |dd           t          | |dd           t          | |d	
           t          | |dd           |                     d|f          	                                }|d         dk    sJ d S )NequityrZ   rU   r[   r  acct-brokeraccountr   
account_idr   zsnapshot confirmedr   r   rS   
instrumentr   USELECT import_readiness_status FROM broker_import_review_items WHERE review_item_id=?r  ready_for_import
rR   rc   re   r   r   r   r
   r	   rb   r   r  s      r%   Gtest_b4_instrument_mapping_with_isin_can_be_ready_for_import_for_equityr     s    Ax8888-:J:J:JScdes{S|S|S|DAdfUUUU'$=_hiiii%aCWXXXX*1TQY`lmmmm
))knrmt
u
u
~
~  A  AC()-???????r'   c                    t                      } t          |            t          |            t          | d          }t	          | |dd           t          | |dd          }t          | |d	
           t          | |dd           |sJ |                     d|f          	                                d         dk    sJ d S )NrT   r  rU   r[   r  r  r  r  snapshotr  rS   r  r   r  r  r  r  )r-   r   audits      r%   :test_b4_ready_for_import_for_etf_and_account_mapping_auditr     s    A"""M!$4$4$4=Ma]b=c=c=cdAdfUUUU/$S`gpqqqE%a:NNNN*1TQY`lmmmmLL599losnuvv  B  B  C\  ]  as  s  s  s  s  s  sr'   c                    t                      } t          |            t          | ddgdd          }t          | |dd           t	          | |d	
           |                     d|f           ddlm}  || |          dk    sJ d S )Ncashr   FT)rZ   ri   rj   rk   z	acct-cashzcash accountr  r  r  zQUPDATE broker_import_review_items SET reviewer_confirmed=1 WHERE review_item_id=?r   refresh_review_item_readinessr  )rR   re   r   r   r
   rb   %jarvis_finance.imports.broker_mappingr   )r-   r   r   s      r%   !test_b4_ready_for_import_for_cashr"     s    Aa   )9!XqWr  ~C  RV  *W  *W  *W$'$;]kllll%a:NNNNIIadhcjkkkSSSSSS((D115GGGGGGGr'   c                 T   t                      } t          |           }	 t          | |d           t          d          # t          $ r Y nw xY wt          | |d          }|sJ t          | d          }	 t          | |d           t          d          # t          $ r Y d S w xY w)	Nr(   r  zignore without note acceptedzsynthetic ignorezTrue Wealth)rh   z	too earlyzresolved before ready accepted)rR   r   r   r   
ValueErrorr   )r-   r   ignored_audititem2s       r%   =test_b4_ignore_requires_note_and_mark_resolved_requires_readyr'  	  s    A'**=1T;;;; ;<<<     'qDVWWWM=Q}555E?!!ELLLL =>>>    s#   A   
AA8B 
B'&B'c                     t                      } t          | ddddgd          }|                     d|f                                          }dd	lm}  || |          dk    sJ d S )
N
Raiffeisenotherr   r   r   )rh   rZ   ri   rl   zdSELECT import_readiness_status, review_status FROM broker_import_review_items WHERE review_item_id=?r   r  )rR   r   rb   r   r!  r   )r-   r   r0   r   s       r%   <test_b4_aggregate_only_remains_blocked_for_instrument_importr+    s    A',G\l  oK  \L  U^  _  _  _
))z  ~B  }D  E  E  N  N  P  PCSSSSSS((D11Y>>>>>>r'   c                   t                      }| dz  }t          |g dg dg           t          |t          |          |j                  }t          ||d         dd           t          ||d         d	d	           t          |          }|d
         d         dk    sJ |                    d|d         f                                          d         dk    sJ d S )Nr   r   r   r   rs   mark_currentcurrent)rs   actionr   archiver   session_statusarchivedr   r   r   )	rR   r9   r   r   r   r   r   rb   r   )r   r-   r   r   sessionss        r%   <test_b4_dry_run_session_archive_discard_current_writes_auditr4  #  s    A9AeA(H(H(HJcJcJc'deee'+A!+D+DVWV\]]]G!!0Enclmmmm!!0Ei^ghhhh#A&&HA;'(J666699LwWcOdNfggpprrsvw{|||||||r'   c                    t                      } t          |           }d t          j        |           D             }d|v sJ t	                      du sJ |                     d                                          d         dk    sJ d S )Nc                ,    i | ]}|d          |d         S r   r$   r   s     r%   r   zXtest_b4_data_quality_readiness_counts_and_productive_import_disabled.<locals>.<dictcomp>0  s"    OOOqAgJ'
OOOr'   zReview Readiness: not_readyTr   r   r   )rR   r   r   r   r   rb   r   )r-   r   r   s      r%   Dtest_b4_data_quality_readiness_counts_and_productive_import_disabledr7  .  s    A'**OON,KA,N,NOOOD(D0000,..$666699CDDMMOOPSTXYYYYYYYr'   )r   r   r   r   r   r   )r   r   r   r   )r   r   )=
__future__r   r3   r~   pathlibr   jarvis_finance.dashboardr   r   r!  r   r   r   r	   r
   r   r   r   r   r   r   r   r   r   r   %jarvis_finance.imports.broker_parsersr   r   r   jarvis_finance.storage.databaser   !jarvis_finance.storage.migrationsr   r   r9   rO   rR   rc   re   r   r   r   r   r   r   r   r   r   r   r   r   r   r  r  r  r  r"  r'  r+  r4  r7  r$   r'   r%   <module>r>     s   " " " " " "
         ; ; ; ; ; ;                                 " x w w w w w w w w w : : : : : : G G G G G G G G. . . ./ / / /(   )1e.agrw  CH k k k k ko o o #0UUZ^im  }A  JP s s s s s
- 
- 
- 
-E E E EK K K K
Z 
Z 
Z 
Z8 8 8 8Z Z Z Z0 0 0 0@ @ @ @	E 	E 	E 	Ev v v v   K K K K	l 	l 	l 	lB B B B@ @ @ @s s s sH H H H? ? ? ?&? ? ? ?} } } }Z Z Z Z Z Zr'   