
    Pkjj\|                      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	m
Z
 ddlmZmZmZm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mZ ddlmZ  ddl!m"Z" ddl#m$Z$m%Z% dZ&dZ'dZ(dZ)h dZ*h dZ+h dZ, e
d           G d d                      Z-ddZ.dd Z/dd"Z0d#d#d$dd*Z1d#d#d$dd+Z2dd.Z3dd0Z4dd2Z5dd3Z6dd9Z7dd=Z8dd@Z9ddCZ:ddFZ;ddKZ<ddQZ=ddRZ>ddSZ?ddUZ@ddWZAddZZBd[d\dd^ZC	 dddeZDddhZEddoZFddpZGdddrZHdduZIdddvddyZJdd|ZKdd}ZLdd~ZMddZNdddddZOddZPddZQddddddddddZRdS )zDeterministic portfolio ingestion and reconciliation over existing normalized data.

No function in this module reads raw files or calls external providers. Preview and
reconciliation are pure database projections. Confirm is the only write boundary.
    )annotationsN)	dataclass)UTCdatedatetime	timedelta)DecimalInvalidOperation)
ConnectionIntegrityError)Any)record_audit_event)	stable_id)PostFinanceDocumentnormalize_labelparse_postfinance_portfolio)decimal_text) confirm_instrument_price_mapping) latest_official_postfinance_cashpostfinance_account_rolesportfolio_ingestion_v1portfolio_reconciliation_v1%portfolio_reconciliation_tolerance_v1   >   postfinance_etradinglegacy_account_valuescanonical_transactionscash_account_snapshots>   buyfeetaxselldepositdividendreversaltransfer	full_sell
correction
withdrawalcash_depositdistributionpartial_sellcash_withdrawalwithholding_taxetf_distributionexternal_depositinternal_transferexternal_withdrawal>   r   r"   r'   r,   initial_position_snapshotT)frozenc                      e Zd ZU ded<   ded<   ded<   ded<   ded<   ded<   ded	<   ded
<   ded<   ded<   ded<   ded<   ded<   dZded<   dS )SourceRecordstrsource_record_fingerprintsource_record_refrecord_kinddispositionlogical_key
str | Nonetarget_type	target_idlineage_hash
account_idaccount_refaccount_labelas_ofdict[str, Any]summaryNdict[str, Any] | Nonewrite_payload)__name__
__module____qualname____annotations__rH        -src/jarvis_finance/services/portfolio_data.pyr6   r6   3   s         """"OOOJJJ+/M//////rN   r6   returnr   c                 4    t          j        t                    S N)r   nowr   rM   rN   rO   _nowrT   E   s    <rN   valuer7   fieldc                   	 t          j        |                     dd                    }n%# t          $ r}t          | d          |d }~ww xY w|j        t          | d          |                    t                    S )NZz+00:00u(    ist kein gültiger ISO-8601-Zeitstempelu#    benötigt eine eindeutige Zeitzone)r   fromisoformatreplace
ValueErrortzinfo
astimezoner   )rU   rV   parsedexcs       rO   _instantr`   I   s    V'c8(D(DEE V V VEKKKLLRUUV}EFFFGGGS!!!s   (+ 
AAAr   c                v    	 t          j        |           S # t          $ r}t          | d          |d }~ww xY w)Nu    ist kein gültiges ISO-Datum)r   rY   r[   )rU   rV   r_   s      rO   _daterb   S   sU    K!%((( K K KE@@@AAsJKs    
838Fnon_negativepositiveobjectrd   boolre   r	   c                  t          | t                    rt          | d          	 t          t	          |                                                     }n,# t          t          f$ r}t          | d          |d }~ww xY w|                                st          | d          |r|dk    rt          | d          |r|dk     rt          | d          |S )Nz/ muss als exakter Decimal-Text angegeben werdenu    ist keine gültige Dezimalzahlz muss endlich seinr   u    muss grösser als null seinz darf nicht negativ sein)
isinstancefloatr[   r	   r7   stripr
   	is_finite)rU   rV   rd   re   resultr_   s         rO   _decimalrn   Z   s   % TERRRSSSMU))++,,j) M M MEBBBCCLM 7E555666 AFaKKE???@@@ =

E;;;<<<Ms   .A B)A<<Bc               B    t          t          |           |||          S )zVConvert a database scalar to Decimal; SQLite NUMERIC affinity may return int or float.rc   )rn   r7   )rU   rV   rd   re   s       rO   _stored_decimalrp   j   s    CJJL8TTTTrN   Decimal | Noner=   c                    | d S t          | d          }d|v r(|                    d                              d          n|S )Nf.0)formatrstrip)rU   texts     rO   _decimal_textry   o   sH    }t%D+.$;;4;;s""3'''D@rN   payloadc                    t          j        t          j        | ddt                                                                                              S )NT),:)	sort_keys
separatorsdefault)hashlibsha256jsondumpsr7   encode	hexdigest)rz   s    rO   _hashr   v   s>    >$*W]`aaahhjjkkuuwwwrN   prefixc                    |  dt          j        |                                                                          d d         z   S )N-   )r   r   r   r   )r   rU   s     rO   	_safe_refr   z   s:    <<<'.88BBDDSbSIIIrN   c                P    d}t          j        || t           j                  rdn| S )Nz}\b[A-Z]{2}\s?\d{2}(?:\s?[A-Z0-9]){11,30}\b|\b\d{10,}\b|\b0x[a-f0-9]{20,}\b|(?:^|\s)[~.]?[/\\]|\.(?:csv|xlsx|sqlite|db|json)\b)flagszKonto (redigiert))research
IGNORECASE)rU   	sensitives     rO   _safe_labelr   ~   s/     QI"$)IuBM"R"R"R]X]]rN   connr   
scope_kindrA   list[dict[str, str]]c                   |dvrt          d          |dk    r|st          d          d}d}|r|dz  }|f}|                     d| d	|                                          }|dk    r|st          d
          d |D             S )N>   account	portfolioz&Scope muss portfolio oder account seinr   u1   Für den Account-Scope ist ein Konto erforderlichrM   z,WHERE is_active=1 AND performance_included=1z AND account_id=?zFSELECT account_id, account_name, account_type, currency FROM accounts z ORDER BY account_idz$Portfolio-Konto wurde nicht gefundenc                ,    g | ]}t          |          S rM   )dict.0rows     rO   
<listcomp>z#_validate_scope.<locals>.<listcomp>   s    &&&#DII&&&rN   )r[   executefetchall)r   r   rA   paramswhererowss         rO   _validate_scoper      s    111ABBBYzLMMM!#F:E $$<<|af|||  E  F  F  O  O  Q  QDYt?@@@&&&&&&rN   requestrE   *tuple[list[dict[str, str]], str, str, str]c                   t          |                    d          pd          }|t          vrt          d          t          |                    d          pd          }t          |                    d          pd          }t	          |d          t	          |d          }}||k    rt          d          t          |                    d	          pt                                                                }t          |d
                                          }t          |                    d          pd          }t          |                    d          pd          pd }	t          | ||	          }
|
|||fS )N
source_key uE   Datenquelle ist nicht vorhanden oder nicht für Ingestion freigegebenperiod_from	period_toVonBisz,Zeitraum: 'von' darf nicht nach 'bis' liegendata_cutoffData-Cutoffr   r   rA   )	r7   getSOURCE_KEYSr[   rb   rT   	isoformatr`   r   )r   r   r   r   r   startendcutoffr   rA   accountss              rO   _validate_requestr      sU   W[[..4"55J$$`aaagkk-006B77KGKK,,233I{E**E)U,C,C3Es{{GHHH]++Atvv/?/?/A/ABBFfm,,6688FW[[..=+>>JW[[..4"55=JtZ<<H[)V33rN   r   raw_idc                ,    t          d|  d|           S )Nrecordr}   )r   )r   r   s     rO   _record_refr      s     X*77v77888rN   r@   tuple[str, str] | Nonec                    |                      d|f                                          }|r*t          |d                   t          |d                   fnd S )NzSELECT target_type, target_id FROM portfolio_ingestion_items
           WHERE lineage_hash=? AND disposition IN ('new','versioned','unchanged') AND target_id IS NOT NULL
           ORDER BY created_at DESC LIMIT 1r>   r?   )r   fetchoner7   )r   r@   r   s      rO   _existing_lineager      sb    
,,	/ 
	 
 hjj  @CLCM"##S[)9%:%:;;LrN   versionintc                <    dt          | ||g          d d         z   S )Nz
valuation-   )r   )r   r@   r   s      rO   _valuation_snapshot_idr      s%    %\7 CDDSbSIIIrN   valuation_atplanned_versions-dict[tuple[str, str], tuple[int, str | None]]'tuple[str, str | None, int, str | None]c                  t          | |          }|rd|d         dd fS ||d d         f}|                    |          }|^|                     d|                                          }	|	rt	          |	d                   dz   nd}
|	rt          |	d                   nd }n|\  }
}t          |||
          }|
dz   |f||<   |rdnd	d |
|fS )
N	unchanged   r   
   zSELECT snapshot_id,snapshot_version FROM portfolio_valuation_snapshots
               WHERE scope_kind='account' AND scope_id=? AND substr(valuation_at,1,10)=?
               ORDER BY snapshot_version DESC,captured_at DESC LIMIT 1snapshot_versionsnapshot_id	versionednew)r   r   r   r   r   r7   r   )r   r   rA   r   r@   r   existingkeyplannedr   r   
supersedesr   s                rO   _valuation_dispositionr      s    !|44H 1HQKD00|CRC(
)C""3''GllJ 	
 

 (** 	 7:@#c,-..22q03=S]+,,,

%(\7KKK$q[+6S%0KK54*LLrN   r   r   r   r   list[SourceRecord]c                   |sg S d |D             }d                     d |D                       }|                     d| dg ||||R                                           }g }t                      }	i }
|D ]Q}t	          |d                   }t          dt	          |d                             }|t	          |d	                   t	          |d
                   dt	          |d                   t	          |d                   t	          |d                   d}t          |          }t	          |d         pd                                          }g }d}	 t          |d
         dd          }n# t          $ r
 d }ddg}}Y nw xY w|d         ddg}}|dv rddg}}n	|dvrddg}}||dk    rdnd}d }d\  }}||	v rd}n3|dk    r-t          | d|t	          |d	                   ||
           \  }}}}|	                    |           d }|d!v r0|.|t          |          ddd"t	          |d	                   |||||d#}|                    t          ||d$|d%| d&|d	          d'|||t          d(|          t!          ||         d)                   t	          |d	                   d*t          |          d||d+|                     S|S ),Nc                     i | ]}|d          |S rA   rM   r   s     rO   
<dictcomp>z+_legacy_account_records.<locals>.<dictcomp>       >>>c3|$c>>>rN   r|   c              3     K   | ]}d V  dS ?NrM   r   _s     rO   	<genexpr>z*_legacy_account_records.<locals>.<genexpr>   "      55AC555555rN   zGSELECT * FROM account_value_snapshots
            WHERE account_id IN (z) AND valuation_date BETWEEN ? AND ? AND created_at <= ?
            ORDER BY valuation_date, account_id, created_at, snapshot_idrA   r   r   valuation_datetotal_value_chfCHFsource_typequality_status
created_at)r   atrU   currencysourcequalitycreatedr   complete
KontototalTrd   unavailablesource_error
updated_at>   stalepartialr   stale_snapshot>   okr   r   r   blockedr   r   N	duplicater   rA   r   r@   r   >   r   r   1rA   rU   r   base_currencyfxr   r   r   
record_refr   reason_codes	valuationaccount:r}   portfolio_valuation_snapshotr   account_nameaccount_totalkindrU   r   r   r  )joinr   r   setr7   r   r   lowerrp   r[   r   addry   appendr6   r   r   )r   r   r   r   r   account_mapplaceholdersr   recordsseenr   r   rA   refbase_payloadfingerprintr   reasonsstatusrU   r;   r?   r   r   rH   s                            rO   _legacy_account_recordsr     s     	>>X>>>K885555555L<<	L".	L 	L 	L 	7+6{6I6v66	 
 hjj 	 #%GUUDFH 6 6\*++
13s=7I3J3JKK!c*+,,./00#m,--3/0113|,--
 
 L))c*+1r2288::	>#C(9$:LW[\\\EE 	> 	> 	>E+n-=GFFF	> |(+n-=GF***'*:);GFFBBB+n-=GF#(=Fm4K4KiiQV $	%$%KKI%%:P!8Z %5!677k!1; ; ;7KGZ
 	...53D(=3G3GUZ!&c3sK[G\C]C]"*C"('	 M 	|k;;z;;C(8$9;;=[]fYy*%E%E{S^_iSjkySzG{G{$%&&$}U/C/CQVjp  CJ  K  K
 
 	 	 	 	 Ns   E33FFc                D   |sg S d |D             }d                     d |D                       }|                     d| dg ||||R                                           }g }t                      }	i }
|D ]%}t	          |d                   }t          dt	          |d                             }|t	          |d	                   t	          |d
                   t	          |d                   t	          |d                   t	          |d                   t	          |d                   t	          |d                   d}t          |          }g }	 t          |d         dd          }n# t          $ r d }dg}Y nw xY w|dnd}d }d\  }}||	v rd}n/|-t          | d|t	          |d	                   ||
          \  }}}}|	
                    |           d }|dv r0|.|t          |          dddt	          |d	                   |||d|d}|                    t          ||d|d | d!|d	          d"|||t          d#|          t          ||         d$                   t	          |d	                   d%t          |          d|dnd&|d'|                     '|S )(Nc                     i | ]}|d          |S r   rM   r   s     rO   r   z*_cash_snapshot_records.<locals>.<dictcomp>#  r   rN   r|   c              3     K   | ]}d V  dS r   rM   r   s     rO   r   z)_cash_snapshot_records.<locals>.<genexpr>$  r   rN   zFSELECT * FROM cash_account_snapshots
            WHERE account_id IN (z}) AND balance_date BETWEEN ? AND ? AND created_at <= ?
            ORDER BY balance_date, account_id, created_at, snapshot_idrA   r   r   balance_dateamount_original
amount_chfr   r   snapshot_typer   )r   r   amountr  r   r   typer   zCash-BewertungTr   r   r   r   r   r   r   >   r   r   r   r   r   r   r  r  r}   r  r   r  
cash_totalr   r  )r
  r   r   r  r7   r   r   rp   r[   r   r  ry   r  r6   r   r   )r   r   r   r   r   r  r  r   r  r  r   r   rA   r  rz   r  r  rU   r;   r?   r   r   rH   s                          rO   _cash_snapshot_recordsr#    sn     	>>X>>>K885555555L<<	J".	J 	J 	J 	7+6{6I6v66	 
 hjj 	 #%GUUDFH * *\*++
2CM8J4K4KLL!S-@)A)ASQTUfQgMhMhc,/00c#j/>R>R#h-((#c/6J2K2KX[\_`l\mXnXn
 

 Gnn	'#C$57GVZ[[[EE 	' 	' 	'E%&GGG	' $)=iie $	%$%KK:P!9j ^!455K!1; ; ;7KGZ
 	...53D(=3G3GUZ!&c3s>GZC[C["*C",g	 M 	|k;9z99C$799;Y[dYy*%E%E{S^_iSjkySzG{G{N#$$!M%,@,@euz  vGgqgq  MZ  ls  t  t
 
 	 	 	 	 Ns   E//F Fc                   |sg S d |D             }d                     d |D                       }|                     d| dg ||||R                                           }g }t                      }	|D ]}
t	          |
d                   }t          dt	          |
d                             }t	          |
d	         p	|
d
         pd                                          }t	          |
d                   ||t	          |
d         pd          t	          |
d         p|
d                   t	          |
d         pd          t	          |
d         pd          t	          |
d         pd          t	          |
d         pd          t	          |
d         pd          t	          |
d         pd          t	          |
d         pd          t	          |
d         pd          t	          |
d         p	|
d         pd          d}t	          |
d         pd          pt          |          }g }d}||	v rddg}}n)|t          vs|dv rdd g}}n|t          v r|
d         sd!d"g}}|	
                    |           |                    t          ||d#|d$| d%|||t          d&|          t          ||         d'                   t	          |
d         p|
d                   ||
d         t	          |
d                   nd |
d         |
d         t	          |
d         p|
d                   nd t	          |
d         pd          |d(d                      |S ))Nc                     i | ]}|d          |S r   rM   r   s     rO   r   z2_canonical_transaction_records.<locals>.<dictcomp>a  r   rN   r|   c              3     K   | ]}d V  dS r   rM   r   s     rO   r   z1_canonical_transaction_records.<locals>.<genexpr>b  r   rN   z<SELECT * FROM transactions
            WHERE account_id IN (z) AND trade_date BETWEEN ? AND ? AND created_at <= ?
              AND COALESCE(is_voided,0)=0 AND COALESCE(is_confirmed,1)=1
            ORDER BY trade_date, created_at, transaction_idrA   r   transaction_idactivity_kindtransaction_typer   instrument_idevent_timestamp
trade_datequantitygross_amount_originalfee_originalru   tax_originalnet_amount_originalcurrency_originalfx_rate_to_chfrow_hashsource_referenceexternal_transaction_id)transactionr	  r   
instrumentr   r-  grossr    r!   netr   r   r4  r5  r   r   duplicate_source_record>   r3   r   unsupported_activity	ambiguousambiguous_instrumentactivityz	activity:r7  r   r  )r	  r-  r   r   r  )r
  r   r   r  r7   r   r  r   SUPPORTED_ACTIVITIESPOSITION_ACTIVITY_TYPESr  r  r6   r   r   )r   r   r   r   r   r  r  r   r  r  r   rA   r  raw_kindrz   r  r  r;   s                     rO   _canonical_transaction_recordsrC  \  s     	>>X>>>K885555555L<<	?".	? 	? 	? 	7+6{6I6v66  hjj 	 #%GUUD  \*++
2C<L8M4N4NOOs?+Ls3E/FL"MMSSUUs#3455xT^c/28b99SIZE[Ep_bco_pAqAqC
O1r22SE\A]Acac=d=ds>*1c223s>?R?YVY;Z;Zs017R88c#NaJbJhfhFiFic*+1r22C
ODYWY@Z@Z #C(:$;$csC\?]$cac d d
 
 #j//R00BE'NN!$#.1J0KKK111XA^5^5^#,/E.FKK000_9M0#.1G0HK|j+7P;7P7PR_adYy*%E%E{S^_iSjkySzG{G{%&;#l*;<<3z?C^3s:+?+?+?dhZ]^sZt  [A  EH  I`  Ea  Ems345U=T9UVVV  swS!45;<<gW W 
 
 	 	 	 	 NrN   r   c                   t          |                     d          pd          }|st          d          	 t          j        |d          }n.# t
          j        t          f$ r}t          d          |d }~ww xY wt          |          S )Ncontent_base64r   PostFinance-Datei fehltT)validateu+   PostFinance-Datei ist nicht gültig kodiert)r7   r   r[   base64	b64decodebinasciiErrorr   )r   encodedrawr_   s       rO   _postfinance_documentrN    s    '++.//5266G 42333Qw666NJ' Q Q QFGGSPQ&s+++s   A A9$A44A9dict[str, str]c                    |                      d                                          }t          |          dk    rt          d          t	          |d                   S )Na  SELECT a.account_id,a.account_name,a.account_type,a.currency,p.platform_id
           FROM accounts a JOIN platforms p ON p.platform_id=a.platform_id
           WHERE a.is_active=1 AND a.performance_included=1
             AND lower(p.name || ' ' || a.account_name) LIKE '%postfinance%'
             AND a.account_type NOT IN ('cash','robo_advisor','total_value')
           ORDER BY a.account_idr   z:PostFinance E-Trading-Konto ist nicht eindeutig zugeordnetr   )r   r   lenr[   r   )r   r   s     rO   _postfinance_accountrR    sX    <<	$  hjj 	 4yyA~~UVVVQ==rN   documentdict[str, dict[str, str]]c                >   |                      d          pg }|si S i }t                      }|D ]d}d t          |                                          D             }|                     dd                                          }|                     dd                                          }|r||v st          j        d|          st          d          ||v rt          d          |                     d	          d
k    rt          d          |                     d          dk    s?|                     d          r*|                     d          r|                     d          st          d          |||<   |                    |           fd |j	        D             }	t          |          |	k    rt          d          |j	        D ]}
||
j
                                                 }|                     d          |
j        k    rt          d          |                     dd                                          |
j        k    rt          d          |                     dd                                          |
j        k    rt          d          |S )Ninstrument_mappingsc                \    i | ])\  }}|t          |pd                                           *S )r   )r7   rk   )r   r   parts      rO   r   z2_postfinance_mapping_decisions.<locals>.<dictcomp>  s4    RRRdS__**,,RRRrN   source_symbolr   isinz[A-Z]{2}[A-Z0-9]{9}[0-9]u?   PostFinance-Instrumentzuordnung ist unvollständig oder doppeltu;   PostFinance-Instrumentzuordnung enthält eine doppelte ISINsource_venueunknownz=PostFinance-Quellhandelsplatz muss ohne Beleg unknown bleibenproviderfmpinstrument_namevaluation_symbolvaluation_venueu2   PostFinance-Bewertungszuordnung ist unvollständigc                @    h | ]}|j                                         S rM   )source_labelupperr   items     rO   	<setcomp>z1_postfinance_mapping_decisions.<locals>.<setcomp>  s'    OOODd'--//OOOrN   uS   PostFinance-Instrumentzuordnungen müssen alle und nur die Quellpositionen abdeckenasset_classz=PostFinance-Instrumentzuordnung widerspricht der Anlageklassesource_currencyu>   PostFinance-Instrumentzuordnung widerspricht der Quellwährungvaluation_currencyu=   Bewertungswährung widerspricht der bestätigten Handelslinie)r   r  r   itemsrd  r   	fullmatchr[   r  	positionsrc  rh  r   )r   rS  rM  	decisions
seen_isinsrU   rf  symbolrZ  source_symbolspositiondecisions               rO   _postfinance_mapping_decisionsrt    s   
+++
,
,
2C 	+-I55J  RRd5kk>O>O>Q>QRRR/2..4466xx##))++ 	`9,,BLA\^b4c4c,^___:Z[[[88N##y00\]]]HHZ  E))88-.. *88.// * 88-.. *
 QRRR 	&tOOH<NOOON
9~~''nooo& ^ ^X288::;<<&&(*>>>\]]]<<)2..4466(:KKK]^^^<<,b117799X=NNN\]]] OrN   user)
created_byrv  c                  t          |          }t          ||          st          d          t                                                      }g }g }	 |                     d           |j        D ]}|j                                                 }|                     d|d         f          	                                }	t          |	          dk    rt          d|d                    |	rt          |	d         d                   }
nVt          d	|d                   }
|                     d
|
|j        |d         |d         |d         |j        |j        d|f	           t          | fi d|
d|d         d|d         d|d         d|d         d|d         ddddd|d         d|d         d|d         d|d         d|d         d|j        d|dd }|                    |           |                    |
           t#          | d!d"d#d$t          |          t          t%          |                    fd%t'                    D             d&d'd(|)	  	         |                                  n## t*          $ r |                                   w xY wt          |          t          t%          |                    |d*S )+zUApply the explicitly approved PostFinance identity and valuation mappings atomically.z*PostFinance mapping decisions are requiredBEGIN IMMEDIATEzDSELECT * FROM instruments WHERE upper(isin)=? ORDER BY instrument_idrZ  r   z!Duplicate canonical ISIN exists: r   r*  r8  aA  INSERT INTO instruments(
                         instrument_id,asset_class,name,ticker,isin,currency,trading_currency,is_active,
                         position_category,instrument_status,valuation_policy,notes,created_at)
                       VALUES(?,?,?,?,?,?,?,1,'postfinance_baseline','active','live_price',?,?)r_  rY  zICanonical identity from explicitly confirmed PostFinance mapping decisionr]  provider_symbolr`  provider_marketra  exchanger   rj  
confidencer   notez9Sprint 9 explicitly approved PostFinance mapping decisiontrading_currencycanonical_isinr[  ri  rv  commitFpostfinance_mapping_set'confirm_postfinance_instrument_mappingsinstrument_mapping_setzpostfinance-2026-05-14c                0    g | ]fd dD             S )c                .    i | ]}|         |         S rM   rM   )r   r   rn  rp  s     rO   r   zFconfirm_postfinance_instrument_mappings.<locals>.<listcomp>.<dictcomp>  s1       SS)F+C0   rN   )rZ  rY  r[  r`  rj  r]  rM   )r   rp  rn  s    @rO   r   z;confirm_postfinance_instrument_mappings.<locals>.<listcomp>  sT       
 	     =     rN   )mapping_countinstrument_countrn  z<Explicitly approved Sprint 9 PostFinance instrument mappingsT)r   actionentity_type	entity_id
new_valuesuser_text_note	confirmedrv  )r  r  mapping_ids)rN  rt  r[   rT   r   r   rm  rc  rd  r   rQ  r7   r   rh  r   r   r  r   r  sortedr  	Exceptionrollback)r   r   rv  rS  rS   r  instrument_idsrf  rs  r   r*  
mapping_idrn  s               @rO   r  r    s    %W--H.wAAI GEFFF
&&



CK "ND&'''& )	1 )	1D !2!8!8!:!:;H<<V&!#  hjj  4yy1}} !WXfEU!W!WXXX  #DGO$< = = ),8H I Ic #D$4h?P6QS[\kSlf%t}dm`beg   :  +m "*-- !)); < <	
 !)): ; ; ""344 ""677 3 QP "**>!?!?  (// !)): ; ; '77 &n55 !%  &:!" u#J& z***!!-0000,<0.!$[!1!1$'N(;(;$<$<   
 #)"3"3  
 
 Z!'	
 	
 	
 	
* 	    [))N 3 344"  s   H$I< < Jlabelrh  r   rs  dict[str, str] | Nonetuple[str, bool]c                &  	 |rL|d                                          }|                     d|f                                          }t          |          dk    rt	          d          |r]t          |d         d                                                   k    rt	          d          t          |d         d                   d	fS d
t          |g          d d         z   }|                     d|f                                          }|r<t          |d         pd                                           |k    rt	          d          ||d u fS t          |          		fd|                     d                                          D             }t          |          dk    rt          |d         d                   d	fS d
t          	g          d d         z   }|                     d|f                                          }||d u fS )NrZ  zUSELECT instrument_id,asset_class FROM instruments WHERE is_active=1 AND UPPER(isin)=?r   z6Mehrere kanonische Instrumente verwenden dieselbe ISINr   rh  uA   Kanonisches Instrument widerspricht der bestätigten Anlageklasser*  Fzpf-instrument-   z@SELECT instrument_id,isin FROM instruments WHERE instrument_id=?r   u0   Deterministische Instrumentidentität kollidiertc                   g | ]}t          t          |d                              k    rXt          |d                                                   k    r-t          |d                                                   k    ~|S )namerh  r   )r   r7   r  rd  )r   r   rh  r   
normalizeds     rO   r   z*_instrument_for_source.<locals>.<listcomp>B  s        3s6{++,,
::M"##))++{::J  &&((H44 	
 544rN   zQSELECT instrument_id,name,asset_class,currency FROM instruments WHERE is_active=1z;SELECT instrument_id FROM instruments WHERE instrument_id=?)
rd  r   r   rQ  r[   r7   r  r   r   r   )
r   r  rh  r   rs  rZ  matches	source_idr   r  s
     ``     @rO   _instrument_for_sourcer  )  s8     +%%'',,cG
 
 (** 	 w<<!UVVV 	;71:m,--3355DD !deeewqz/233U::$udV}}SbS'99	<< bendpqqzz|| 	QHV,23399;;tCCOPPP(d*** ''J     ||_
 

(**  G 7||q71:o.//66 5*k8)L#M#Mcrc#RRI||Y\e[ghhqqssHh$&&&rN   r  tuple[str, str | None]c                >    t          | |          }|r
d|d         fndS )Nr   r   )r   N)r   )r   r  r   s      rO   _postfinance_dispositionr  Q  s*     {33H)1DK!%%}DrN   r*  r-  enabledowner_resolutionslist[dict[str, Any]]4tuple[str | None, str | None, dict[str, Any] | None]c          	     <   |                      d||f                                          }|s|                      d||f                                          }|sdS t          |          dk    rdS |d         }	 t          t	          |d                             }	n# t
          t          f$ r Y dS w xY wt	          |d	                   d
k    ot	          |d                   dv o|d          o|d          o|d         d u ot          t	          |d         pd                    dk    o\|d          pSt	          |d                   t	          |d                   k    p't	          |d                                       d          }
|	|k    rdS |
sdS g }|D ]}|D ]}	 t          t	          |                    d          pd                    }n# t
          t          f$ r Y Iw xY wt	          |                    d          pd          t	          |d                   k    rt	          |                    d          pd          t	          |d                   k    rt	          |                    d          pd          |k    rt	          |                    d          pd          	                                t	          |d         pd          	                                k    r6||k    r0|                    d          d k    r|
                    ||f           t          |          dk    rd!S |d         \  }}	 t          t	          |                    d"          pd          d#          }n# t          $ r Y d$S w xY w|t                      k    rd%S t	          |                    d&          pd                                          }t          |          d'k     rd$S d |                                |t	          |d                   |t	          |d                   t!          |          d(}t	          |d                   d |fS |sd)S t          |          dk    rdS |d         }	 t          t	          |d                             }n# t
          t          f$ r Y dS w xY wt	          |d	                   d
k    ot	          |d                   dv o|d          o|d          o|d         d u ot          t	          |d         pd                    dk    o\|d          pSt	          |d                   t	          |d                   k    p't	          |d                                       d          }
||k    rdS |
sdS t	          |d                   d d fS )*Nai  SELECT transaction_id,transaction_type,quantity,source_type,source_id,
                  external_transaction_id,row_hash,notes,price_original,gross_amount_original
           FROM transactions
           WHERE account_id=? AND instrument_id=? AND COALESCE(is_voided,0)=0
             AND COALESCE(is_confirmed,1)=1
           ORDER BY trade_date,transaction_ida  SELECT t.transaction_id,t.account_id,t.transaction_type,t.quantity,t.source_type,t.source_id,
                      t.external_transaction_id,t.row_hash,t.price_original,t.gross_amount_original,i.isin
               FROM transactions t
               JOIN instruments i ON i.instrument_id=t.instrument_id
               WHERE t.account_id<>? AND t.instrument_id=? AND COALESCE(t.is_voided,0)=0
                 AND COALESCE(t.is_confirmed,1)=1
               ORDER BY t.account_id,t.trade_date,t.transaction_idNNNr   )N$relevant_transaction_history_presentNr   r-  )N#manual_placeholder_quantity_invalidNr)  r3   r   >   initial_snapshotmanual_dashboardvue_manual_positionr6  r4  price_originalr.  ru   r  r'  zoriginal-entry-)N$manual_placeholder_quantity_conflictN)N'evidenced_cost_basis_or_lineage_presentNexpected_quantitymanual_transaction_idr   expected_source_account_idrA   target_account_idexpected_isinrZ  reason_code3manual_placeholder_wrong_account_confirmed_by_owner)N)manual_placeholder_other_account_conflictNowner_confirmed_atu   Eigentümerbestätigung)N"owner_confirmed_resolution_invalidN)N'owner_confirmed_resolution_future_datedNowner_confirmation_noter  )r  r  r  r  r  r  r  )N-manual_position_overlap_requires_confirmationN)r   r   rQ  r	   r7   r
   	TypeError
startswithr   rd  r  r`   r[   rT   rk   r   postfinance_decimal_text)r   rA   r*  r-  r  r  r   cross_account	candidatecandidate_quantityunlineaged_placeholderr  r   
resolutionr  r  r}  rs  stored_quantitys                      rO   $_manual_placeholder_for_supersessionr  V  s    <<	1 
]#  hjj 	  J:F '	
 	
 (** 	  	$##}""EE!!$		E!(Yz-B)C)C!D!D ), 	E 	E 	EDDD	E 	,-..2MM 
Im,--1pp
788
 j))
 *+t3	

 I&=>E#FFGG1L
 k** My-..#i@P6Q2R2RRMy-..99:KLL 	 ))EE% 	IHH46  	6 	6C/ 6 6
(/JNNCV4W4W4^[^0_0_(`(`%%()4   H 
'>??E2FF#cRbNcJdJdddJNN+GHHNBOOSVWZ[gWhSiSiiiJNN+>??E2FF*TTJNN?;;ArBBHHJJcRUV\R]RcacNdNdNjNjNlNlll)X55"}559nnnNNC#45556 w<<1JJ!!*Z	D!)#jnn=Q.R.R.XVX*Y*Y[t!u!u 	D 	D 	DCCC	D&&HH:>>";<<BCCIIKKt99r>>CCP"4">">"@"@'+*-c,.?*@*@!+ V--!9(!C!C
 
 3'())499 KJJ
4yyA~~AA
q'CA!#c*o"6"677i( A A A@@@A 	C"#$$(CC 
	
M"##'ff
	
-..
	
 J
	
  !T)	
	

 C34;<<==B
	
 K   C3{#$$C0@,A(B(BBC3{#$$//0ABB  (""AA! EDDs#$%%tT11sH   :"B B21B21GG%$G%2M5 5
NN."R R&%R&c           "     |   t          |           }t          |          }t          |                    d                    }t	          |                    d          pg           }t          |d                   }t          d|          }t          t          |d                             }	t          |d                   }
d j        D             }t          d          |d	<   g }fd
j
        D             }j
        D ]%}|                    |j                                                  }t          | |j        |j        |j        |          \  }}ddg}|s|                    d           |j        |v }t%          j        |j        dg          }t+          | |          \  }}d }d }d }|dk    r |st-          | |||j        ||          \  }}}|rd}|                    |           |rd\  }}d|d d         z   }d|j        |r|                    d          nd |j        d|j        t1          |j                  |j        t1          |                    |j                            |rt          d|          nd dt3          t5          ||r|r|d         ndgng z                       d}|                    t7          |t          d|          d|d|j         d|||||	j        ||dk    rd ni d d!d"|d|d#|d|
d$|j        d%|r|                    d%          n|j        d|r|                    d          nd d&|j        d'dd(|j        d)t1          |j                  d*|j        d+t1          |                    |j                            d,|d-j        d.|||d/                     t%          j        |j        d0g          }t+          | |          \  }}d1|d d         z   }|                     d2j        ||f                                          }t	          |          }|rd3\  }}|                    d4           n||rz|d5k    rtt?          |d)         d6          |j        k    ot?          |d7         d8          |j         k    } | rd5nd9}| rt          |d:                   nd }| s|                    d;           |                    t7          |t          d|          d<|d=|j         d>|||||	j        d?|j        |r|                    d          nd |j        t1          |j                  t1          |j                   t1          |j!                  t1          |j"                  t1          |j#                  d@|j        dt3          t5          |                    dA|dk    rd n\dB|||
|t1          |j                  d d t1          |j!                  t1          |j                   d t1          |j$                  j        dC                     't%          j        dDdE j        D             g          }!t+          | |!          \  }"}#tK          dF j        D             t          dG                    }$fdHj        D             }%|                    t7          |!t          d|!          d<|"dIj         dJ|#|!|||	j        dKt1          j&                  d	|%t1          |$          t1          |$j&        z
            dLdMgdN|"dk    rd ndO|t1          j&                  j        |%dP                     dQD ]}&t%          j        |&t1          j'                  g          }'t+          | |'          \  }}|&dRk    rdSndT}(ddg})|                    t7          |'t          d|'          d<|dU|& dVj         |&dRk    rdWndX||'|||	j        |&t1          j'                  d	d|)dY|dk    rd n,|(|t1          j'                  j        t          d|'          dZ                     d[j(        v s	d\j(        v rmd[j(        v rd]nd^}*t%          j        |*g          }'|                    t7          |'t          d|'          d<d9d_|* d d |'|||	j        d`da|*gdbd                      |S )cN"allow_manual_baseline_supersession.owner_confirmed_manual_placeholder_resolutionsrA   r   r  platform_idc                (    i | ]}|j         |j        S rM   )r   r3  re  s     rO   r   z(_postfinance_records.<locals>.<dictcomp>  s    MMMtt2MMMrN   r   r   c                h    h | ]-t          fd j        D                       dk    &j        .S )c              3  8   K   | ]}|j         j         k    V  d S rR   )normalized_label)r   otherrf  s     rO   r   z1_postfinance_records.<locals>.<setcomp>.<genexpr>  s.      __5u%)>>______rN   r   )sumrm  r  r   rf  rS  s    @rO   rg  z'_postfinance_records.<locals>.<setcomp>  sS       "&____HL^_____bccc 	cccrN   baseline_onlymissing_transaction_historymissing_instrument_identifierinitial_positionr   )rA   r*  r-  r  r  T)r   Nzpf-tx-r  r3   rZ  r\  r7  r   r  manual_placeholder_supersession)r	  instrument_labelr  rY  r[  rh  r-  r   r3  supersedes_transaction_refr   r  r   r?  zpostfinance:initial_position:
write_kindpostfinance_initial_positionr'  r*  r  r_  rY  r[  rh  r-  r   r3  r4  rD   create_instrument)supersede_transaction_idsupersession_resolutionreported_positionzpf-position-zSELECT position_snapshot_id,quantity,market_value_chf FROM positions_snapshot
               WHERE snapshot_date=? AND account_id=? AND instrument_id=?)r=  Nr>  r   Mengemarket_value_chfPositionswertr   position_snapshot_idconflicting_snapshotr  zpostfinance:position:positions_snapshotposition_snapshotunverified_source_display_only)r	  r  r  rh  r-  r  market_price_originalsource_reported_average_costsource_reported_cost_totalcost_basis_semanticsr   r   r  postfinance_position_snapshot)r  r   rA   r  r*  r-  average_cost
cost_totalmarket_pricer  pnl_chf
weight_pctrD   cashc                    g | ]	}|j         
S rM   )r4  re  s     rO   r   z(_postfinance_records.<locals>.<listcomp>U  s    :c:c:cT4=:c:c:crN   c              3  $   K   | ]}|j         V  d S rR   r  re  s     rO   r   z'_postfinance_records.<locals>.<genexpr>W  s$      II4tIIIIIIrN   ru   c                >   g | ]}d |j          |j         t          |j                  t          |j                  j        t          |j                  t          |j                            t          d                              t          |j                  dS )Kontosaldo 0.01)rc  r   r  displayed_fx_rate_to_chffx_as_ofcomputed_amount_chf_unroundedcomputed_amount_chf_display#reported_currency_account_value_chf	r   r  r  r3  rD   r  quantizer	   reported_account_value_chfr  s     rO   r   z(_postfinance_records.<locals>.<listcomp>X  s     	 	 	  7t}66DM4T5IJJ%=d>Q%R%R^*B4?*S*S(@AYAYZabhZiZiAjAj(k(k0HIh0i0i	k 	k	 	 	rN   zpostfinance:cash:cash_account_snapshotcash_snapshotr   displayed_fx_rate_is_rounded)r	  rU   r   
componentscomputed_from_displayed_fx_chfdisplayed_fx_difference_chfr   r  postfinance_cash_snapshot)r  rA   rU   rD   r  )reported_totalperformance_baseliner  postfinance_account_total postfinance_performance_baselinezpostfinance:r}   account_value_snapshotr  r  )r  rA   rU   rD   r  unsupported_rowportfolio_total_mismatchr<  r   zpostfinance:review:review_requiredr   )r	  r   r  ))rR  rt  rg   r   listr7   r   r   r  r	   rm  rc  rd  r  rh  r   r  r  r   	file_hashr4  r  r  r-  r  r  r  r6   rD   r   r   rp   r  r  r  r  r  r  reported_cash_value_chfr   r  )+r   rS  r   r   rn  allow_supersessionr  rA   rB   rC   r  fx_ratesr  duplicate_labelsrf  rs  r*  r  base_reasonsr   activity_fingerprintr;   r?   r  overlap_reasonr  r'  activity_summarysnapshot_fingerprintr   existing_snapshotsnapshot_reasonssamecash_fingerprintcash_dispositioncash_targetcomputed_cash_totalcash_componentstarget_kindr  r  r  reasons+    `                                         rO   _postfinance_recordsr$    s   
 #4((G.wAAIgkk*NOOPPW[[)YZZ`^`aaW\*++JIz22KGN$; < <==Mgm,--KMMx}MMMHcllHUO"$G   *2*<   " o o==!2!8!8!:!:;;+A$#T%5t}h,
 ,
(( ()FG 	A ?@@@'+;;$h&8$-I[%\]]!9$@T!U!UY/3 %)9=%(9Pt%+*"3Q Q QM$n6M  	0G/// 	5%4"K!$8"$==/TEV6>Hhll6222D!.	+9QRVR_9`9`6x||DM7R7RSS`x  +C)MC[*\*\*\  C'"3| *82*R'660Q8 8 02(3 $4 $4 5 5
 
 	| )H6J"K"KZYd;DM;;]I *k=(.5((DD /</>NP^/j/*9=/JWYd/ #D$5/ 8Imu  KM(,,WhJiJiJi{  |M/ BV,,,d	/
  !2/
 5CI/ t// 4T]CC/ FPQUQ^/ !":8<<;V;V"W"W/ 0/ 3:8>/ $%6/ -E+B/ / /
 
 	 	 	*  %h&8$-I\%]^^!9$@T!U!UY$';CRC'@@ LLM^Z7
 
 (**	 	
  -- 	@%6"K##$:;;;; 	@;+#=#= 1* =wGG4=X u#$56H$I?[[_c_tt  *.<++9KJNX-.DEFFFTXI @ ''(>???| )H6J"K"K[Ze3DM335I9 *k=(.(d>O7?Ix||F333T ,:RSWS`:a:a!9$:O!P!P&>t?P&Q&Q-EdFW-X-X+CDO+T+T%E)#C(8$9$9::	< 	<  5((DD=k(Wd4T]CC $" 89J K K$<T=R$S$S6tGGRZR`
/ 
/
 
 	 	 	 	6 h0&:c:cU]Ub:c:c:cdee$<TCS$T$T!kII8=III7SV<<XX	 	 	 	 M	 	 	O NN<)H.>??N^,HN,,.E{*k=(. +CHDd+e+e/+CDW+X+X(@ATW_WwAw(x(x%8V7W		Y 	Y
 !E))5Z-h.NOOZbZh)0
 0
      B  X/>VW_Wo>p>pqrr!9$!L!LY4?CS4S4S00Y{
"$AB|8[99;9;9999(37G(G(G$$Mk{J]HN +CHD\+]+])WV V5((DD(
1(2JKKV^Vd'+>>/ /
 
 	 	 	 	 H1115OS[Sh5h5h+<@U+U+U''[iX/899|8[99;	*&**D$ZQ\8>&-Z`Yabb
 
 	 	 	 NrN   rG   c                    |dk    r/|t          d          t          | t          |          |          S |dk    rt          | ||||          S |dk    rt	          | ||||          S t          | ||||          S )Nr   rF  r   r   )r[   r$  rN  r  r#  rC  )r   r   r   r   r   r   r   s          rO   _recordsr&    s    +++?6777#D*?*H*H'RRR,,,&tX{IvVVV---%dHk9fUUU)$+yRXYYYrN   r  source_revisionc                R   t           | d         |                     d          pd|                     d          | d         | d         | d         ||                     d          pg t          |                     d	                    |                     d
          pg d |D             dS )Nr   r   r   rA   r   r   r   rV  r  r  c                N    g | ]"}|j         |j        |j        |j        |j        g#S rM   )r8   r:   r;   r<   r@   r   rs     rO   r   z$_preview_payload.<locals>.<listcomp>  sE      A  A  AstQ0!-PQP]_`_mn  A  A  ArN   )r   r   r   rA   r   r   r   r'  rV  r  r  r  )INGESTION_VERSIONr   rg   )r   r  r'  s      rO   _preview_payloadr-    s    $l+7;;|;T;T;cXckk,//@V[)'-:P*&{{+@AAGR.27;;?c3d3d.e.e:A++<;
 ;
 ; A  Ax  A  A  A  rN   preview_created_atconfirmation_idr/  r0  c               h
   t          |          }d t          |                    d          pd          dk    r#t          |          j        |d<   j        |d<   t          | |          \  }}}}t          |d                   t          |                    d          pd          t          |                    d          pd          pd |||d	}	t          | |	d         |||||          rj        nt          d
 D                       }
t          |	          }rY|                    d          pg |d<   t          |                    d                    |d<   |                    d          pg |d<   t          ||
          }t          |          }|rt          |d          nt                      }|t          t                    z   }fddD             }t                    |d<   d|d d         z   }|pdt!          j                    j        z   }t          ||||                                d          }i |	|||                                |                                |
|||d D             d D             d D             |d         r|d         |d         k    rdn,|d         s!|d         st)          d D                       rdndt+          d  D                       d!d" d d#         D             t                    d#k    d$}rd%d%d%d%d%d%d%t          j                  t          j                  d&	}t1          d' j        D             t3          d(                    }t1          d) j        D             t3          d(                    }fd*j        D             }|                    d+j        |d,t                    t1          d- D                       t1          d. D                       t          j                  t1          d/ D                       t1          d0 D                       d1|t7          |          t7          j                  t7          |j        z
            t7          |          t7          j                  t7          |j        z
            t7          ||z             t7          j                  t7          ||z   j        z
            t7          j        j        z             d2d3dd4d4d5d6gd7d8           |S )9Nr   r   r   r   r   r   r   rA   )r   r   rA   r   r   r   c                6    g | ]}|j         |j        |j        gS rM   )r8   r;   r<   r*  s     rO   r   z%preview_ingestion.<locals>.<listcomp>  sX      Yg  Yg  Yg  Z[Z[Zuwx  xE  GH  GT  ZU  Yg  Yg  YgrN   rV  r  r  Preview-Zeitpunktminutesc                J    i | ]t          fd D                       S )c              3  .   K   | ]}|j         k    V  d S rR   r;   )r   r+  r   s     rO   r   z/preview_ingestion.<locals>.<dictcomp>.<genexpr>  s*      ==q}+======rN   )r  )r   r   r  s    @rO   r   z%preview_ingestion.<locals>.<dictcomp>  sK      Q  Q  Q#c3====W=====  Q  Q  QrN   )r   r   r   r=  r   r   
discoveredzportfolio-preview-r   zportfolio-confirm-
preview_idr0  input_fingerprintr/  c                D    g | ]}|j         d k    |j        dv |j        S )r?  >   r   r   r:   r;   rF   r*  s     rO   r   z%preview_ingestion.<locals>.<listcomp>  sV      D  D  DQQ]j=X=X]^]j  oC  ^C  ^Cqy  ^C  ^C  ^CrN   c                D    g | ]}|j         d k    |j        dv |j        S )r  >   r   r   r>  r*  s     rO   r   z%preview_ingestion.<locals>.<listcomp>  sV      E  E  EQQ]k=Y=Y^_^k  pD  _D  _Dqy  _D  _D  _DrN   c                f    g | ].}|j         d k    s|j                            d          '|j        /S )r   r  )r;   rF   r   r*  s     rO   r   z%preview_ingestion.<locals>.<listcomp>  sE      
  
  
}++qy}}=Y/Z/Z+ I+++rN   r   r   r=  c              3  J   K   | ]}|j                             d           V  dS )r  NrF   r   r*  s     rO   r   z$preview_ingestion.<locals>.<genexpr>  sv        iY  iY  LM  jk  js  jw  jw  xF  jG  jG  iY  iY  iY  iY  iY  iYrN   r   r   c                N    h | ]"}|j                             d g           D ]}|#S r  rB  )r   r+  codes      rO   rg  z$preview_ingestion.<locals>.<setcomp>  s7    #h#h#hQaimmTbdfFgFg#h#hdD#h#h#h#hrN   )r  r  c           
     h    g | ]/}|j         |j        |j        |j        |j        |j        |j        d 0S )r9   r:   r;   rB   rC   rD   rF   rG  r*  s     rO   r   z%preview_ingestion.<locals>.<listcomp>  sX     
 
 
  #$"5amdedqMAOVWV]jkjsu u
 
 
rN      )r;  r0  r/  
expires_atr'  r<  payload_hashcountsplanned_activitiesplanned_valuationsplanned_correctionsquality_impactrk  	truncatedr   )	buyssellsdistributionsfeestaxesdepositswithdrawalsrm  cash_balancesc              3  $   K   | ]}|j         V  d S rR   r  re  s     rO   r   z$preview_ingestion.<locals>.<genexpr>  s$      SST_SSSSSSrN   ru   c              3  $   K   | ]}|j         V  d S rR   )r  re  s     rO   r   z$preview_ingestion.<locals>.<genexpr>  s%      ^^tT2^^^^^^rN   c                >   g | ]}d |j          |j         t          |j                  t          |j                  j        t          |j                  t          |j                            t          d                              t          |j                  dS )r  r  )rc  ri  source_amountr  r  computed_chf_unroundedcomputed_chf_displayr  r  )r   rf  postfinance_documents     rO   r   z%preview_ingestion.<locals>.<listcomp>  s     	
 	
 	
  ;4=::t}6t7KLL)A$BU)V)V-3'?'P'P%=do>V>VW^_eWfWf>g>g%h%h4LTMl4m4mo o	
 	
 	
rN   zPostFinance Portfolio-BewertungTc              3  B   K   | ]}|j         d k    o
|j        dk    V  dS )r  r   Nr>   r;   r*  s     rO   r   z$preview_ingestion.<locals>.<genexpr>
  s8       s sfg2F!F!a1=\aKa s s s s s srN   c              3  B   K   | ]}|j         d k    o
|j        dk    V  dS )r   r   Nra  r*  s     rO   r   z$preview_ingestion.<locals>.<genexpr>  s8      qqdeAM-DD_Z_I_qqqqqqrN   c              3  B   K   | ]}|j         d k    o
|j        dk    V  dS )r  r   Nra  r*  s     rO   r   z$preview_ingestion.<locals>.<genexpr>  sR        *G  *Gyz!-;Y*Y*t^_^kot^t  *G  *G  *G  *G  *G  *GrN   c              3  d   K   | ]+}t          |j                            d                     V  ,dS )r  N)rg   rF   r   r*  s     rO   r   z$preview_ingestion.<locals>.<genexpr>  s:      +o+obcD?[1\1\,],]+o+o+o+o+o+orN   )r  rm  r  r!  account_valuationsmanual_supersessionszDisplayed FX rates and row CHF values are rounded; source aggregate positions plus source aggregate cash equals the document total exactly. No balancing entry is created.)r  #computed_cash_from_displayed_fx_chfreported_cash_chfcash_difference_chfsum_position_rows_chfreported_positions_chfposition_display_difference_chf"calculated_from_displayed_rows_chfreported_document_total_chftotal_difference_chfsource_total_identity_chfexplanation
restrictedr  r  )history_beforetwrmwrr  )document_typer  operation_countsr  expected_changescash_reconciliationperformance_impact)r   r7   r   rN  rD   r   r&  r  r   rg   r-  r`   rT   r   PREVIEW_TTL_MINUTESrQ  uuiduuid4hexr   anyr  rm  r  r  r	   updater  r  reported_positions_value_chfr   )r   r   r/  r0  effective_requestr   r   r   r   normalized_requestr'  canonical_request	canonicalr  r   expiresrK  r;  active_confirmation_idrJ  responserw  computed_cashrow_positionsr!  r_  r  s                            @@rO   preview_ingestionr    sV    W7;
7;;|$$*++/EEE4W==+?+E-()=)C+&/@GX/Y/Y,Hk9f+L9::#N_NcNcdpNqNq  OA  vA  KB  KB+//==CDDL"6 
 t/=xV_agiz{{G8L  h*44RW  Yg  Yg  _f  Yg  Yg  Yg  Sh  ShO/00 3D3H3HI^3_3_3ece/0BF!!"FGGC
 C
>? O`NcNc<O
 O
 O 	JK !!2G_MMI	""KCUah)+>???[_[a[aG	*=>>>>G Q  Q  Q  Q  JP  Q  Q  QFw<<F<%CRC(88J,W0Dtz||GW0W 1(%//11	   L
 1%//11ARARATAT*^j D  D'  D  D  D E  E'  E  E  E 
  
& 
  
  

 (.i'8  iVL=QU[\eUf=f=fmmy  AJ  zK  mi  OU  Va  Ob  mi  fi  iY  iY  QX  iY  iY  iY  fY  fY  milulu  _i"#h#h'#h#h#hii
 

 
 TcT]
 
 

 \\C'-  H0  3Aq1!#>R>\:]:] !5!:;;
 

 SS9M9RSSSU\]`UaUabb^^?S?]^^^`ghk`l`lmm	
 	
 	
 	
 -1	
 	
 	
 	>-7 0!w<<  s skr s s sssqqipqqqqq#&';'@#A#A&)  *G  *G  F  *G  *G  *G  'G  'G(++o+ogn+o+o+o(o(o! ! .7OP]7^7^%=>R>j%k%k'?PdP|@|'}'})A-)P)P*BCWCt*u*u3KM\p  ]N  MN  4O  4O6N}_lOl6m6m/GH\Hl/m/m(@Q^A^au  bF  BF  )G  )G-E(EH\Htt. .  L$ $" #0\!02O P# #;!
 !
 !	 !	 !	D OrN   r   captured_atc                v   |j         }|t          d          t          ||j        |d                   }|                     d||d         |d         |d         |d         |d         |d         |d	         d
| ||d         |d         |d         |d         t          j        |d         d          f           |S )Nu-   Vorschau enthält keine schreibbare Bewertungr   a  INSERT INTO portfolio_valuation_snapshots(
             snapshot_id, scope_kind, scope_id, account_id, value_original, currency, base_currency,
             fx_rate_to_base, fx_direction, valuation_at, source, captured_at, snapshot_version,
             supersedes_snapshot_id, source_reference, quality_status, reason_codes_json
           ) VALUES(?, 'account', ?, ?, ?, ?, ?, ?, 'original_to_base', ?, ?, ?, ?, ?, ?, ?, ?)rA   rU   r   r   r   r   z
ingestion:r   r  r   r  Tr~   )rH   r[   r   r@   r   r   r   )r   r   r   r  rz   r   s         rO   _insert_valuationr  (  s    "GHIII(V5H'R[J\]]KLL	c
 
gl+W\-BGGDTV]^hVi		!74='.2IKdXbKdKd	gi(',*?AV	!	"DJw~/FRV$W$W$W	Y
 
 
 rN   c                	   |j         }|t          d          t          |d                   }|dk    rt          |d                   }|                    d          rP|                     d||d         |d         |d	         |                    d
          |d         |d         d|f	           |                    d          }|                    d          }|rdnd}|r|                     d|f                                          }	|	t          |	d         pd          dk    rt          d          |                     d||||f           t          | dddt          |          d|	d         |	d         dd|d         |j        ||r|d         nd|d |d!d"#
  
         |                    d$          }
|                     d%|d         d&d&|d'         ||d(         |d(         |d         |d         |
|d         d)k    rd*n|
rd+nd,|
rd-nd.d/|j        |j        |d0         ||r|nd d1|f           t          |d                   S |d2k    r|                     d3|d4         |d(         |d'         |d5         |d         |d         |d6         |d7         |d8         |d9         |d:         |d;         |f           t          |d4                   S |d<k    r|d=         D ]{}d>t          |d'         |d(         |d         |d?         g          d d@         z   }|                     dA||d'         |d(         |d         |d?         |dB         |dC         dD|f	           |dE|j
        d d@         z   }|                     dF||d'         |d(         |dG         |dG         t          |d=                    dH|f           |S |dIk    rAdJ|j
        d d@         z   }|                     dK||d'         |d(         |dG         |f           |S |dLk    r|                     dM|d'         |d(         f                                          }|rt          |dN                   dz   nd}dO|j
        d d@         z   }|                     dP||d'         |d'         |dG         |d(         |||r|d4         nd |dQ         t          j        dRdSg          f
           |S t          dT          )UNu;   PostFinance-Vorschau enthält keinen schreibbaren Datensatzr  r  r*  r  a5  INSERT INTO instruments(
                     instrument_id,asset_class,name,ticker,isin,currency,trading_currency,is_active,
                     position_category,instrument_status,valuation_policy,notes,created_at)
                   VALUES(?,?,?,?,?,?,?,1,'postfinance_baseline','active','live_price',?,?)rh  r_  rY  rZ  r   z=Canonical PostFinance baseline identity; source venue unknownr  r  zaOwner-confirmed wrong-account manual placeholder superseded by authoritative PostFinance baselinezKAuthoritative PostFinance baseline supersedes unlineaged manual placeholderz]SELECT transaction_id,is_voided,source_type,quantity FROM transactions WHERE transaction_id=?	is_voidedr   z@Manueller Platzhalter kann nicht mehr sicher supersediert werdenzUPDATE transactions
                   SET is_voided=1,voided_at=?,void_reason=?,voided_by='postfinance_confirm',updated_at=?
                   WHERE transaction_id=?portfolio_ingestion_api)postfinance_manual_placeholder_supersededr7  r   r-  )r  r   r-  r   r'  r  ,manual_placeholder_same_account_supersession)r  superseded_by_transaction_idsuperseded_by_source_record_refr#  r  owner_confirmationTru  )	r   r  r  r  
old_valuesr  r  r  rv  r3  a  INSERT INTO transactions(
                 transaction_id,transaction_type,activity_kind,account_id,instrument_id,trade_date,
                 event_timestamp,quantity,currency_original,fx_rate_to_chf,fx_source,fx_status,
                 source_type,source_id,external_transaction_id,row_hash,is_confirmed,quality_status,
                 correction_of_transaction_id,correction_reason,notes,created_at)
               VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,1,'incomplete',?,?,?,?)r3   rA   rD   r   document_identitypostfinance_statementmissing_in_sourcer   missingpostfinance_etrading_snapshotr4  zYAuthoritative initial-position baseline; purchase price and historical cost basis unknownr  a  INSERT INTO positions_snapshot(
                 position_snapshot_id,snapshot_date,account_id,platform_id,instrument_id,quantity,
                 average_cost_original,cost_basis_original,market_price_original,market_value_chf,
                 unrealized_price_pnl_chf,portfolio_weight_pct,data_quality_status,created_at)
               VALUES(?,?,?,?,?,?,?,?,?,?,?,?, 'incomplete',?)r   r  r  r  r  r  r  r  r  r  zpf-cash-component-r  r  a  INSERT INTO cash_balances(
                     cash_balance_id,account_id,balance_date,currency,amount_original,fx_rate_to_chf,
                     amount_chf,source_type,quality_status,notes,created_at)
                   VALUES(?,?,?,?,?,?,?,'postfinance_etrading','partial',?,?)r  r  zfOriginal currency retained; CHF component is calculated from the statement's displayed rounded FX ratezpf-cash-a  INSERT INTO cash_account_snapshots(
                 snapshot_id,account_id,snapshot_type,balance_date,amount_original,currency,
                 amount_chf,source,note,created_at,created_by)
               VALUES(?,?,'reconciliation',?,?,'CHF',?,'postfinance_etrading',?,?,'user')rU   zS original-currency components retained separately; aggregate CHF is source-reportedr	  zpf-account-total-zINSERT INTO account_value_snapshots(
                 snapshot_id,account_id,valuation_date,total_value_chf,currency,source_type,
                 quality_status,created_at)
               VALUES(?,?,?,?,'CHF','postfinance_etrading','partial',?)r
  zSELECT snapshot_id,snapshot_version FROM portfolio_valuation_snapshots
               WHERE scope_kind='account' AND scope_id=? AND substr(valuation_at,1,10)=?
               ORDER BY snapshot_version DESC LIMIT 1r   zpf-valuation-a  INSERT INTO portfolio_valuation_snapshots(
                 snapshot_id,scope_kind,scope_id,account_id,value_original,currency,base_currency,
                 fx_rate_to_base,fx_direction,valuation_at,source,captured_at,snapshot_version,
                 supersedes_snapshot_id,source_reference,quality_status,reason_codes_json)
               VALUES(?,'account',?,?,?,'CHF','CHF','1','original_to_base',?,
                      'ingestion:postfinance_etrading',?,?,?,?, 'partial',?)r  r  r  z(Unbekannter PostFinance-Schreibdatensatz)rH   r[   r7   r   r   r   r   r   r9   r   r@   rQ  r   r   )r   r   r  rz   r	  r*  r  r  supersession_reasonoldr   	componentcomponent_idr   r   r   s                   rO   _insert_postfinance_recordr  ;  sb   "GVWWWw|$%%D---GO455;;*++ 		LL_  6@Q8RT[\kTlV$$gj&97:;NPR]_   $+;;/I#J#J [[!:;;
 mool 	 $  	,,o)+  hjj  {c#k"2"7a88A== !cdddLL- 1;@XY	   0B)677)*3};M[^_i[jkk!"GL\D]7=7O15?s
=11Es*4   3!%   ( [[)**Q %&(CE`\"M773CWWEUW^_iWjZ "$+J$75$@$@  acF|F]F]i|&TTY(GIa%wz':<T$<F  $h	
 	
 	
" 7+,---...B
 ]#WW%5w|7LgVcNd_%wz':GN<SU\]iUj^$g.@&A79CU\"K1
	
 
	
 
	
 7=)****** . 	 	I/%%ww'7:9NPYZkPl9 3 3s3 L LLQ w|4gg6F	R\H],-y9S/T:;y	
 
 
 
 !6#6ss#;;] ',/1A77CSU\]dUeGL)**	
 	
 	
 ***)F,?,DDK ',/1A77CSU`a	
 	
 	
 111ll9 \"GG$45	
 

 (** 	 7:@#c,-..22q%(;CRC(@@P ',/1FPWHXZabiZj'#F3}#5#5$P\H]Z*GHIIK
	
 
	
 
	
 
?
@
@@rN   c                N                        d          durt          d          t                               d          pd          }t          |d          }t                               d          pd          }|st          d          fd	d
D             }t	          t                               d          pd          |t                               d          pd          |                                d          }	 |                     d           |                     d|f                                          }|r t                               d          pd          t                               d          pd          t                               d          pd          t                               d          pd          pd t                               d          pd          t                               d          pd          t          t                               d          pd          d                                          t                               d          pd          t                               d          pd          f	}|d         |d         |d         |d         |d         |d         |d         |d         |d         f	}	||	k    s%|d         |k    s                     d          |k    rt          d          |                                  |d         |d         ddS t                      |t          t                    z   k    rt          d           t          | |||!          }
t                               d          pd          |
d         k    rt          d"          t                               d          pd          |
d         k    rt          d#          t	          |
d         ||
d         |
d         d          }t                               d          pd          }||k    rt          d$          |
d         d%k    rW|                     d&|
d         f                                          }|r&|                                  |d         |d         ddS d't          j                    j        z   }t                                                      }d(}g }t!          | |
d         t#          | |
d         |
d                   |
d         |
d         |
d         |          }|
d         d%k    r(t%          d) |D                       rt          d*          |D ]}|j        }|
d         d%k    r"|j        d+k    rt+          | ||          }|d,z  }n1|j        d-k    r&|j        d.v rt/          | ||
d         |          }|d,z  }|                    ||f           t3          | d/d0d1||
d         |t4          |
d2         d3         d4dd56          }|                     d7||
d         |
d         |
d         |
d         |
d         |
d         |
d         |
d         |
d         ||t7          j        |
d8         d9          ||f           |D ]v\  }}|                     d:d;t          j                    j        z   ||j        |j        |j        |j        |j        ||j         t7          j        |j!        d9          |f           w| "                                 ||d<||d=S # tF          $ r}|                                  |                     d>|f                                          }|rK|d         |k    r?t                               d          pd          |k    r|d         |d         ddcY d }~S |rt          d          |t          d?          |d }~wtH          $ r |                                   w xY w)@NconfirmTu'   Explizite Bestätigung ist erforderlichr/  r   r3  r0  u   Bestätigungskennung fehltc                <    i | ]}|                     |          S rM   )r   )r   r   r   s     rO   r   z%confirm_ingestion.<locals>.<dictcomp>  s3       cCS))   rN   )r   r   rA   r   r   r   	file_namerE  rV  r  r  r;  r<  r:  rx  zSELECT batch_id, payload_hash, audit_id, preview_id, source_key, scope_kind, scope_id,
                      period_from, period_to, data_cutoff, source_revision, input_fingerprint
               FROM portfolio_ingestion_batches WHERE confirmation_id=?r   r   r   rA   r   r   r   r   r'  scope_idrJ  uB   Bestätigungskennung wurde mit verändertem Inhalt wiederverwendetbatch_idaudit_id)r  r  
idempotentr4  z?Die Vorschau ist abgelaufen; bitte eine neue Vorschau erstellenr.  uB   Die Vorschau ist veraltet oder die Quelldaten haben sich geändertu2   Die Ausgangsrevision der Quelle hat sich geändertu:   Bestätigungsinhalt stimmt nicht mit der Vorschau übereinr   zSELECT batch_id,audit_id FROM portfolio_ingestion_batches
                   WHERE source_key='postfinance_etrading' AND source_revision=?
                   ORDER BY confirmed_at LIMIT 1z
ingestion-r   c              3  (   K   | ]}|j         d v V  dS )>   r   r=  Nr8  )r   r   s     rO   r   z$confirm_ingestion.<locals>.<genexpr>  sD       C
 C
?EF"::C
 C
 C
 C
 C
 C
rN   uP   PostFinance-Batch enthält unklare oder ungültige Zeilen; Prüfung erforderlichr   r   r  >   r   r   r  portfolio_ingestion_confirmedportfolio_ingestion_batchrO  r  )r   written_recordsr   r  ru  )r   r  r  r  r  r  rv  a  INSERT INTO portfolio_ingestion_batches(
                 batch_id, source_key, scope_kind, scope_id, period_from, period_to, data_cutoff,
                 source_revision, input_fingerprint, preview_id, confirmation_id, payload_hash,
                 status, counts_json, audit_id, confirmed_at, confirmed_by
               ) VALUES(?,?,?,?,?,?,?,?,?,?,?,?, 'confirmed', ?,?,?, 'user')rK  r  a'  INSERT INTO portfolio_ingestion_items(
                     ingestion_item_id, batch_id, source_record_fingerprint, source_record_ref,
                     record_kind, disposition, target_type, target_id, lineage_hash, summary_json, created_at
                   ) VALUES(?,?,?,?,?,?,?,?,?,?,?)zingestion-item-F)r  r  r  r  rJ  z`SELECT batch_id, payload_hash, audit_id FROM portfolio_ingestion_batches WHERE confirmation_id=?u/   Ingestion konnte nicht atomar bestätigt werden)%r   r[   r7   r`   r   r   r   r   r  rT   r   r{  r  r|  r}  r~  r&  r   r  r?   r;   r  r:   r  r  r   r,  r   r   r8   r9   r>   r@   rF   r  r   r  )r   r   r/  r   r0  base_requestexpected_existing_hashr   request_identitystored_identitypreviewrJ  supplied_hashpriorr  confirmed_atwritable	item_rowsr  r   r?   r  r_   s    `                     rO   confirm_ingestionr    s[	   {{9T))BCCCW[[)=>>D"EE)+>??G'++&788>B??O 75666    6   L
 #'++l339r::* -@!A!A!GRHH%//11	$ $  t&'''<<K 	
 

 (** 	  	lGKK--344c'++l:S:S:YWY6Z6ZGKK--<==s7;;|C\C\Cb`b?c?c?kgkGKK..4"55s7;;{;S;S;YWY7Z7ZW[[77=2>>NNXXZZGKK 1228b993w{{K^?_?_?ece;f;f  &(>@VX`akXl'+)>@W*+X6I-JO
  ?22h~6NRh6h6hlslwlw  yG  mH  mH  Lb  mb  mb !efffMMOOO ( 4(:BVfjkkk66Gi0CDDDDDD^___#D,K]o~w{{<((.B//7<3HHHabbbw{{,--344@Q8RRRQRRR!,/.!()<!=")*>"?	
 
   GKK77=2>>L((YZZZ< $:::LL4 *+-	 
 hjj   j$)*$55CTdhiii$*,,"22vv''));=	','D',"79NOOM"GK$8'-:PR^
 

 < $:::s C
 C
IPC
 C
 C
 @
 @
: oppp 	2 	2F(I|$(>>>6CUY^C^C^6tV\RR	A#{22v7IMa7a7a-dFGL<QS_``	Afi01111%2;Z3x%l3, '(8 9. I 
 v	
 	
 	
 	P
 w|,gl.CW\EZ]#W[%97=;QSZ[lSm()7<+@/S_Z)T:::HlT
	
 
	
 
	
 "+ 		 		FILL6 #TZ\\%55xAa)6+=v?QSYSeF/FNVZ1[1[1[]ik    	$(%dl  K  L  L  	L U U U<<  !C  FU  EW  X  X  a  a  c  c 	l04JJJsSZS^S^_mSnSnStrtOuOu  zP  PP  PP ( 4(:BVfjkkkkkkkk 	labbhkkJKKQTT   s4   
I ] F] I9] 
`$B``$"`#`$c                   ddddddg dfdddddd	g d
fddddd	ddgfddddd	ddgfdddd	d	d	ddgfdddd	d	d	dgfdddd	d	d	dgfg}g }|D ]p\  }}}}}}}	|dk    r=t          |                     d|f                                          d                   }
n=t          |                     d|                                           d                   }
|                     d |f                                          }d }|dk    r(|                     d!                                          }n-|dk    r'|                     d"                                          }|                    |||
d#k    d||||	|
|r|d$         nd |r|d%         nd p|r|d$         nd |r|d&         nd p|r|d&         nd d'd(gd)           rt	          d* |D                       }|t                                                      |d+S ),Nr   zPostFinance E-Trading Portfolioportfolio_ingestion_batchesT)PositionsbestandCashFXzPerformance-Baseliner   zKanonisches TransaktionsledgertransactionsF)KaufVerkauf	Dividendeu   GebührSteuerTransferr   zBestehende Account-Totalwerteaccount_value_snapshotszAccount-Totalwertr   zBestehende Cash-SnapshotszCash-Totalwertreported_positionszGespeicherte Positionssnapshotsr  r  r  market_priceszGespeicherte MarktpreisePreisr  zGespeicherte FX-Kurser  zLSELECT COUNT(*) AS count FROM portfolio_ingestion_batches WHERE source_key=?countzSELECT COUNT(*) AS count FROM zrSELECT confirmed_at, status FROM portfolio_ingestion_batches WHERE source_key=? ORDER BY confirmed_at DESC LIMIT 1zSELECT finished_at, status FROM import_sessions WHERE import_type IN ('transactions','crypto_transactions') ORDER BY COALESCE(finished_at,started_at) DESC LIMIT 1zSELECT finished_at, status FROM import_sessions WHERE import_type='cash_balances' ORDER BY COALESCE(finished_at,started_at) DESC LIMIT 1r   r  finished_atr  z#Keine neue externe Provider-Abfragez'Keine automatische Ingestion beim Start)r   r  	available
configuredingestion_supportedactivity_supportvaluation_supportcapabilitiesrecord_countlast_successful_confirmed_atlast_attempt_atlast_attempt_status
known_gapsc                <    g | ]}|d          |d         |d         gS )r   r  r  rM   r   s     rO   r   z%list_data_sources.<locals>.<listcomp>}  s0    wwwkn#l+S-@#FdBefwwwrN   )sourcesr   r<  )r   r   r   r  r   rT   r   )r   definitionsr   r   r  table
ingestible
activities
valuationsr  r  batchattemptr  s                 rO   list_data_sourcesr  Z  s<   	!BDacgimos	C	C	C	E	!#C^UY[_af	J	J	J	L	 "AC\^bdiko  sF  rG  	H	!#>@XZ^`egkn~m  	A	@BVX]_dfknu  xG  nH  	I	4oueUZ]d\ef	,j%PTvV
K "$DOZ  KUE:z:|(((^ad`f hjj" # #EE %Me%M%MNNWWYYZabccE  R  UX  TZ  [  [  d  d  f  f***ll  $H  I  I  R  R  T  TGG,,,ll  $n  o  o  x  x  z  zGEAIUY#-:dn(%EJ,TE.,A,APT:A K 6 6tzjoQyQVWeQfQfuy9@$JGH$5$5d#schPrPUV^P_P_nr@Bkl
 
 	 	 	 	 wwrvwwwxxKDFF,<,<,>,>U`aaarN   2   )limitoffsetr  r  c               .   |dk     s|dk    s|dk     rt          d          t          |                     d                                          d                   }|                     d||f                                          }d |D             |||d	S )
Nr   d   r      Pagination ist ungültigz9SELECT COUNT(*) AS count FROM portfolio_ingestion_batchesr  zSELECT batch_id, source_key, scope_kind, scope_id, period_from, period_to, data_cutoff,
                  input_fingerprint, status, counts_json, confirmed_at
           FROM portfolio_ingestion_batches ORDER BY confirmed_at DESC LIMIT ? OFFSET ?c                <   g | ]}d  i t          |          t          dt          |d                             |d         r#t          dt          |d                             ndt          j        |d                   d                                D             S )c                &    i | ]\  }}|d k    ||S )counts_jsonrM   )r   r   rU   s      rO   r   z0ingestion_history.<locals>.<listcomp>.<dictcomp>  s6     5 5 5*#u  #m33	   433rN   r  r  r  r   Nr  )r  r  rK  )r   r   r7   r   loadsrk  r   s     rO   r   z%ingestion_history.<locals>.<listcomp>  s     F F F :=	5 5 13ii1-6wC
O@T@T-U-UNQR\o g	)SZ5I5I J J Jcg"j]);<<1 1 1 EGG	5 5 5 F F FrN   )rk  r  r  total)r[   r   r   r   r   )r   r  r  r  r   s        rO   ingestion_historyr    s    qyyECKK6A::3444XYYbbddelmnnE<<	[]bdj\k  hjj	 	F F AE	F F F
 &5  rN   diffbaseabsoluterelativec                    | dk    rdS t          |           |k    rdS ||dk    rt          | |z            |k    rdS dS )Nr   matchedwithin_tolerancemismatch)abs)r  r  r  r  s       rO   _statusr    sV    qyyy
4yyH!!DAII#dTk*:*:h*F*F!!:rN   rD   -tuple[Decimal | None, str | None, str | None]c                   |dk    rt          d          |dfS |                     d|||f                                          }|sdS t          |d         dd	          t	          |d
                   t	          |d                   fS )Nr   r   identityaK  SELECT rate, rate_date, provider FROM fx_rates
           WHERE base_currency=? AND quote_currency='CHF' AND rate_date<=?
             AND datetime(created_at)<=datetime(?)
             AND rate IS NOT NULL AND quality_status IN ('ok','fresh','complete')
           ORDER BY rate_date DESC, created_at DESC, fx_rate_id DESC LIMIT 1r  rater  T)re   	rate_dater]  )r	   r   r   rp   r7   )r   r   rD   r   r   s        rO   
_latest_fxr    s    5s||UJ..
,,	P S[\aciQj  hjj    3v;t<<<c#kBR>S>SUXY\]gYhUiUiiirN   r   r  z0.001rH  )r   rA   r   absolute_tolerancerelative_tolerancer  r  r   r   r  r  c                 TUV t          d           t          |pt                                                      d                                          }	|                                dk    rt          d          t          |dd          }
t          |dd          }|d	k     s|d
k    s|dk     rt          d          t          | |rdnd|          }d |D             }|sQd dD             }|	dddgt          t          |
          t          |          d|g g t          |	g g          g ||ddS d                    d |D                       }|                     d| dg ||	R                                           }i Ud}t                      }|D ]#}t          |d         p	|d         pd                                          }|t           v r|d	z  }|d         r
|d         dv rUt          |d                    t          |d                   f}	 t#          |d         d!d          }n# t
          $ r Y w xY w|d"v rIU                    |t'          d#                    |z   U|<   |d$         dvr|                    |           |d%v r)U                    |t'          d#                    |z
  U|<   %|                     d&| d'g ||	R                                           }d( |D             Vt+          t          U          t          V          z            }g }dx}x}}d) |D             }d* |D             }|D ]\  T}U                    T|f          } V                    T|f          }!|!r |!d         dvrt#          |!d         d+          nd }"g }#|!r.t          |!d,                   k    r|#                    d-           |!r@t          |!d.         pd                                          d/vr|#                    d0           d}$d }%| |"|#                    d1           nP| |"z
  }%|!rt          |!d,                   k    rd2}$n-t/          |%|"|
|          }$|$d3k    r|#                    d4           d }&|!r |!d5         dvrt#          |!d5         d6          nd }'|                     d7||	f                                          }(|(rt          |(d8                   nd })|(rt          |(d9                   nd }*d x}+},|!r0|(r.t          |!d,                   |)k    r|#                    d:           | |#                    d1           n|(s|#                    d;           n|)k    r|#                    d<           n|d	z  }t#          |(d=         d>d          }-t3          | t          |(d?                                                   |	          \  }.}+},|.|#                    d@           n)|+k    r|#                    dA           n|d	z  }| |-z  |.z  }&d}/d }0d }1|&}|'{|!r4d:|#v sd-|#v sd<|#v sdA|#v s|+r"|+|)k    rd2}/d:|#vr|#                    d:           n\|&|'z
  }0|'dk    r|0|'z  nd }1t/          |0|'|
|          }/|/d3k    r|#                    dB           |d	z  }n|'|#                    d1           |!r@|'>|Txx         |'z  cc<   |T                             t          |!d,                              |                     dC|f                                          }2|                    t5          dDT dE| dE           t5          dT          t7          |T         dF                   t5          dG|          |2rt          |2dH                   ndIt9          |           t9          |"          t9          |%          |$dJt9          |&          t9          |'          t9          |0          t9          |1          |/|!rt          |!d,                   nd |)|+|*|,dK
t+          t          |#                    dL           t;          |           }3t=          | |	M          }4t?          dN |4D             t'          d#                    }5tA          dO |4D             d P          }6g }7d}8|!                                D ]\  T}9|                     dQT|	f                                          }:|:rt#          |:dR         dS          nd };|:rt          |:dT                   nd }<T|3                    dU          k    r|4r|5};|6}<|                     dVT|	f                                          }=|=rt#          |=dW         dX          nd }>|=rt          |=dY                   nd }?tE          TfdZVD                       }@|@s|:r|T         |;pt'          d#          z   nd }Ag }#d}Bd }C|9d[         d\v p|=d uotE          Tfd]|D                        }Dd }E|Dr|                     d^T|	f                                          }F|Frt#          |Fd_         d`          nd }A|Frt          |Fda                   d db         nd }E|Fr|?|Ek    r|#                    d:           t          |T                   |<r|<hnt                      z  |?r|?hnt                      z  |Er|Ehnt                      z  }G|A|#                    |Dsd1nd           |>|#                    d           |A|>fdc|GD             }HtG          dd |GD                       d	k    s|Hsd:|#v r3d2}B|Hr|#                    d-           d:|#vr|#                    d:           n=|A|>z
  }Ct/          |C|>|
|          }B|Bd3k    r|#                    |;|DsdendB           |8d	z  }8|7                    t5          dT          t7          |9dF                   |D|Drd nt9          |T                   |Drd nt9          |;          t9          |A          t9          |>          t9          |C          |Bdf |GD             hk    rnd t+          t          |#                    dg           ddm}ItG          |          }J|                     dn| dog ||	R                                           }K|                     dn| dpg ||	R                                           }LtI          |Kdq         pd          }MtI          |Ldq         pd          }N |I|8tG          |                     |It?          UVfdr|D                       |J           |I|tG          |                     |I|MtG          |                     |I|NtG          |                     |I||J           |I||J           |ItG          |          |J           |I||8z   |JtG          |          z             d	}Ot+          ds |D             dt |7D             z            }Pdu |D             dv |D             z   dw |7D             z   }QtE          dx |QD                       rd3}RnW|Qr7tK          dy |QD                       rtK          dz |QD                       rd{nd|}RntE          d} |QD                       rd2}Rnd}RtL          |	t          |
          t          |          ||7|Og}S|	d|R|Pt          t          |
          t          |          d|O||||z            |7t          |S          ||tG          |          t+          d~ |D             d |D             z            dS )NStichtagr   r   uD   Reconciliation v1 unterstützt als Basiswährung ausschliesslich CHFzAbsolute ToleranzTr   zRelative Toleranzr   i  r   r  r   r   c                     i | ]}|d          |S r   rM   r   s     rO   r   z2build_portfolio_reconciliation.<locals>.<dictcomp>  r   rN   c                    i | ]	}|d d dd
S )r   Ncoveredr  ratiorM   r   r   s     rO   r   z2build_portfolio_reconciliation.<locals>.<dictcomp>  s6      [  [  [S#1q4HH  [  [  [rN   )	r   instrumentsr  opening_valuationsclosing_valuationspricesr   
cost_basisreconciliationr   missing_account_valuation)r   r  r  )rD   r   r   r  r  	tolerancecoveragedifferencesaccount_totalsr<  r  r  r  r  r|   c              3     K   | ]}d V  dS r   rM   r   s     rO   r   z1build_portfolio_reconciliation.<locals>.<genexpr>  r   rN   z0SELECT * FROM transactions WHERE account_id IN (z) AND trade_date<=?
            AND datetime(created_at)<=datetime(?)
            AND COALESCE(is_voided,0)=0 AND COALESCE(is_confirmed,1)=1
            ORDER BY trade_date, created_at, transaction_idr(  r)  r   r*  r-  )Nr   rA   r  >   r   r3   ru   r.  >   r"   r'   r,   zWITH ranked AS (
              SELECT p.*, ROW_NUMBER() OVER(PARTITION BY account_id,instrument_id ORDER BY snapshot_date DESC,created_at DESC,position_snapshot_id DESC) rn
              FROM positions_snapshot p WHERE account_id IN (zz) AND snapshot_date<=?
                AND datetime(created_at)<=datetime(?)
            ) SELECT * FROM ranked WHERE rn=1c                d    i | ]-}t          |d                    t          |d                   f|.S )rA   r*  r7   r   s     rO   r   z2build_portfolio_reconciliation.<locals>.<dictcomp>  s9    bbbSS&''S-A)B)BCSbbbrN   c                .    i | ]}|t          d           S )ru   )r	   r  s     rO   r   z2build_portfolio_reconciliation.<locals>.<dictcomp>  s     2\2\2\32\2\2\rN   c                ,    i | ]}|t                      S rM   )r  r  s     rO   r   z2build_portfolio_reconciliation.<locals>.<dictcomp>  s    2U2U2U#32U2U2UrN   zGemeldete Mengesnapshot_dater   data_quality_status>   r   freshr   r   missing_position_snapshotnot_comparabler  quantity_mismatchr  zGemeldeter Positionswerta  SELECT close,currency,price_date,provider FROM market_prices WHERE instrument_id=? AND price_date<=?
               AND datetime(created_at)<=datetime(?)
               AND quality_status IN ('ok','fresh','complete') ORDER BY price_date DESC,created_at DESC,market_price_id DESC LIMIT 1
price_dater]  cutoff_mismatchmissing_pricestale_priceclose
Marktpreisr   
missing_fxstale_fxvalue_mismatchz2SELECT name FROM instruments WHERE instrument_id=?
differencer}   r  r8  r  zInstrument (unbekannt))ledgerreportedr%  r  )
derivedr'  r%  relative_differencer  position_as_ofprice_as_ofr  price_source	fx_source)difference_refrB   rC   instrument_refr  r-  r  r  )rD   r   c              3  $   K   | ]}|j         V  d S rR   r  r   r  s     rO   r   z1build_portfolio_reconciliation.<locals>.<genexpr>F  s%      @@)	@@@@@@rN   c              3  $   K   | ]}|j         V  d S rR   )r  r1  s     rO   r   z1build_portfolio_reconciliation.<locals>.<genexpr>I  s%      CCY	 CCCCCCrN   )r   zSELECT amount_chf,balance_date,source FROM cash_account_snapshots WHERE account_id=? AND balance_date<=?
               AND datetime(created_at)<=datetime(?)
               ORDER BY balance_date DESC,created_at DESC,snapshot_id DESC LIMIT 1r  Cashbestandr  etrading_depota  SELECT total_value_chf,valuation_date,source_type FROM account_value_snapshots
               WHERE account_id=? AND valuation_date<=? AND datetime(created_at)<=datetime(?)
                 AND COALESCE(is_active,1)=1
               ORDER BY valuation_date DESC,
                        CASE source_type WHEN 'truewealth_official_import' THEN 3 WHEN 'truewealth_manual_provisional' THEN 2 ELSE 1 END DESC,
                        COALESCE(valuation_at,created_at) DESC,created_at DESC,snapshot_id DESC LIMIT 1r   r   r   c              3  0   K   | ]}|d          k    V  dS r   rM   r   r   r   s     rO   r   z1build_portfolio_reconciliation.<locals>.<genexpr>d  s,      BB#CFg-BBBBBBrN   account_type>   pension
truewealthtotal_valuerobo_advisorc              3  0   K   | ]}|d          k    V  dS r   rM   r6  s     rO   r   z1build_portfolio_reconciliation.<locals>.<genexpr>i  si        Or  Or  fi  PS  TU  PV  Za  Pa  Or  Or  Or  Or  Or  OrrN   aM  SELECT value_original,valuation_at FROM portfolio_valuation_snapshots
                   WHERE scope_kind='account' AND scope_id=? AND substr(valuation_at,1,10)<=?
                     AND datetime(captured_at)<=datetime(?)
                   ORDER BY valuation_at DESC,snapshot_version DESC,captured_at DESC,snapshot_id DESC LIMIT 1value_originalzKanonischer Totalwertr   r   c                $    h | ]}||k    
|S rM   rM   )r   rf  rD   s     rO   rg  z1build_portfolio_reconciliation.<locals>.<setcomp>|  s#    LLLDTLdemm4mmmrN   c                    h | ]}||S rM   rM   re  s     rO   rg  z1build_portfolio_reconciliation.<locals>.<setcomp>}  s    333Td3D333rN   cash_gapc                    h | ]}||S rM   rM   re  s     rO   rg  z1build_portfolio_reconciliation.<locals>.<setcomp>  s    >>>>t>>>rN   )rB   rC   total_value_onlypositions_value
cash_valuederived_totalr  r%  r  rD   r  r  r   r  rP   rE   c                j    | ||r,t          t          |           t          |          z            nd dS )Nr  )ry   r	   )r  r  s     rO   r  z0build_portfolio_reconciliation.<locals>.coverage  sN    "Uqv  >A]7SZK[K[^efk^l^lKl=m=m=m  }A  B  B  	BrN   zcSELECT COUNT(DISTINCT account_id) AS count FROM portfolio_valuation_snapshots WHERE account_id IN (zH) AND substr(valuation_at,1,10)<? AND datetime(captured_at)<=datetime(?)zH) AND substr(valuation_at,1,10)=? AND datetime(captured_at)<=datetime(?)r  c              3  ,   K   | ]}|v |v 
d V  dS )r   NrM   )r   r   
quantitiesr'  s     rO   r   z1build_portfolio_reconciliation.<locals>.<genexpr>  s6      #_#_#ST^M^M^AM^M^M^M^#_#_rN   c                (    h | ]}|d          D ]}|S rD  rM   r   rf  r#  s      rO   rg  z1build_portfolio_reconciliation.<locals>.<setcomp>  s*    \\\TtNG[\\V&\\\\rN   c                (    h | ]}|d          D ]}|S rD  rM   rJ  s      rO   rg  z1build_portfolio_reconciliation.<locals>.<setcomp>  sc      `f  `f  `fko  QU  Vd  Qe  `f  `f  GM`f  `f  `f  `f  `frN   c                *    g | ]}|d          d         S )r-  r  rM   re  s     rO   r   z2build_portfolio_reconciliation.<locals>.<listcomp>  s"    CCCtZ *CCCrN   c                *    g | ]}|d          d         S )r  r  rM   re  s     rO   r   z2build_portfolio_reconciliation.<locals>.<listcomp>  s$    F{F{F{gktKGXYaGbF{F{F{rN   c                    g | ]
}|d          S )r  rM   re  s     rO   r   z2build_portfolio_reconciliation.<locals>.<listcomp>  sH      j  j  j  SW  @D  EM  @N  j  j  jrN   c              3  "   K   | ]
}|d k    V  dS )r  NrM   r   r  s     rO   r   z1build_portfolio_reconciliation.<locals>.<genexpr>  s'      
7
7F6Z
7
7
7
7
7
7rN   c              3     K   | ]}|d v V  	dS )>   r  r  NrM   rP  s     rO   r   z1build_portfolio_reconciliation.<locals>.<genexpr>  s(      [[&$CC[[[[[[rN   c              3  "   K   | ]
}|d k    V  dS )r  NrM   rP  s     rO   r   z1build_portfolio_reconciliation.<locals>.<genexpr>  s'      "N"N66Y#6"N"N"N"N"N"NrN   r  r  c              3  "   K   | ]
}|d k    V  dS )r  NrM   rP  s     rO   r   z1build_portfolio_reconciliation.<locals>.<genexpr>  s(      ??FV''??????rN   c                `    h | ]+}|d          d         t          |d          d                   ,S )r  r,  r  re  s     rO   rg  z1build_portfolio_reconciliation.<locals>.<setcomp>  s<    d]abm]no}]~3tK0@AArN   c                `    h | ]+}|d          d         t          |d          d                   ,S )r  r-  r  re  s     rO   rg  z1build_portfolio_reconciliation.<locals>.<setcomp>  s      Cb  Cb  Cb  lp  CG  HS  CT  U`  Ca  Cb  DG  HL  MX  HY  Ze  Hf  Dg  Dg  Cb  Cb  CbrN   )rD   r   r   r  r  r  r  r  r  r<  r  r  r  r  )r  r   r  r   rP   rE   )'rb   r`   rT   r   rd  r[   rn   r   TOLERANCE_VERSIONr7   r   r
  r   r   r  r  r@  rp   r   r	   r  r  r  r  r   r  r   r   ry   r   r   r  maxrk  r  rQ  r   allRECONCILIATION_VERSION)Wr   rD   r   rA   r   r  r  r  r  r   r  r  r   r  empty_coverager  transaction_rowsactivity_supportedcost_basis_keysr   r	  r   r-  reported_rowskeysr  price_covered
fx_coveredrecon_coveredaccount_position_valuesaccount_position_datesr8  
ledger_qtyr   
source_qtyr  quantity_statusquantity_diffderived_valuereported_value	price_rowr  r,  fx_dater-  pricer   value_status
value_diffrelative_diffinstrument_rowpf_rolesofficial_pf_cashofficial_pf_cash_totalofficial_pf_cash_dater  account_coveredmetar  rD  	cash_datelegacyr  
total_datehas_positionsrE  total_statusr  rB  canonical_dater  datesstale_datesr  instrument_totalopening_rowsclosing_rowsopening_coveredclosing_coveredcoverage_payloadall_reasonsstatusesoverallfingerprint_payloadr   rH  r'  sW    `                                                                                  @@@rO   build_portfolio_reconciliationr    s   
 
%k7TVV%5%5%7%7GGQQSSF%%_```*,?dSSSH*,?dSSSHqyyECKK6A::3444t*%MYY+zZZH>>X>>>K m [  [  UZ  [  [  [vYf  zU  yV  qB  PS  T\  P]  P]  kn  ow  kx  kx  ey  ey  GU  fh  |~  UZ  \a  ci  km  [n  Uo  Uo  |~  IN  Z`  kl  m  m  	m885555555L||	?\ 	? 	? 	? A^+@]u@]V\@]@]	 
 hjj  24J,/EEO K K3'H3/A+BHbIIOOQQ'''!#?# 	s:*'D'D3|$%%s3+?'@'@A	&s:dSSSHH 	 	 	H	777(nnS'#,,??(JJsO*+:==##C(((:::(nnS'#,,??(JJsOLL	1>J	1 	1 	1 3PK2O2O2O2O  hjj  cbTabbbH#j//CMM122D(*K122M2J2\2\P[2\2\2\2U2U2U2U2U# W W^^Wj$9::
w
344OU  IZ`akZlt~Z~Z~_VJ%79JKKK  EI
 	-c&122e;;NN+,,, 	+c&!67=2>>DDFFNiiiNN>***'(,!3NN67777&3M 8#f_566%??"2")-Xx"X"X"j00NN#6777(,dj  fou  wI  pJ  R\  p\  p\0B)CE_```  bfLLH KU  W\  ^d  Je
 
 (**	 	
 6?HS<0111D
5>Hs9Z0111D"") 	.i 	.C(?$@$@J$N$NNN,---NN67777 	8NN?++++U""}----"#Ig$6SWXXXE%/c)J:O6P6P6V6V6X6XZ_ag%h%h"Bz|,,,,e##NN:....!OJ *U 2R 7$%)
(,$)C #!W,,#w.. G++(( ) ': 5 5/$G33NN#4555*^;
?MQR?R?R
^ ; ;X\&z>8XVV:--NN#3444"#NN6777 	Nn0#G,,,>,,,"7+//F?4K0L0LMMM&Z]g\ijjssuu'6W6W:6W6WPU6W6WXX$Y88;WbcjWklzW{K|K|'jAA  wE  XcWZ[ijp[qWrWrWr  Kc#0#<#<-XbJcJc  tA  BO  tP  tP  \k  l  l%2=%A%A}]kOlOl  }J  KU  }V  }V  o|  }J  oK  oK  WcLR,\C0G,H,H,HX\mw  FM  _k  zCD D"3w<<00
 
 	 	 	 	 )..H7  
 !@@/?@@@'#,,   CC2BCCCT   ,.NO$**,, B B||VX_afhnWo
 
 (**	 	
 LPY_T,%7GGGUY
15?C^,---4	hll#344449I4 0J-Ik
 nuv{  ~D  mE
 
 (** 	 V\e0A)BLQQQae6<FS 01222$
BBBBBBBBB[hzlpz/8J<V'RU,,WWvz$#/3kk  spv  C  qC  qr  LO  Or  Or  Or  Or  mq  Or  Or  Or  Lr  Lr  Hr%) 
	2q t{  }B  DJ  sK	 
 hjj  foxOI6F,GI`aaatxMDMWS>!:;;CRC@@SWN 2Z>990111*7344y8[VYV[V[\pz  aFak`l`l  AD  AF  AF  G  _m  Kx  LZ  K[  K[  sv  sx  sx  y NN>No66Toppp!NN6777$)CLLLLELLLK33U33344q88K8K\`gKgKg/ 5NN#3444$G33NN#4555$~5&t^XxPP:--NN1CL\1C::brsss1$$Y88;W[\jWkKlKl 0M]  FSTTcp  rI  JQ  rR  dS  dS"2Q$$j8Q8Qdqr  eA  eA+N;;=Y]K^K^jv>>>>>5'IIUUteklopwlxlxeyey
 
 	 	 	 	B B B B4yy<< 	Enz  	E  	E  	E  Gd  IT  Gd  V[  Gd  ]c  Gd  Gd hjj  << 	Enz  	E  	E  	E  Gd  IT  Gd  V[  Gd  ]c  Gd  Gd hjj  ,w/4155O,w/4155OH_c+.>.>??x#_#_#_#_#_#_#_#_ _ _aqrrh137G3H3HII&hK8H8HII&hK8H8HII(=*:;;hz#344hs?335EFF"(=?#BDTWZ[fWgWgDghh
 
 \\[\\\  `f  `f  tB  `f  `f  `f  f  g  gKCC{CCCF{F{ozF{F{F{{  j  j  [i  j  j  j  jH

7
7h
7
7
777  	  c[[RZ[[[[[  ""N"NX"N"N"NNNf))Tf	??h???	?	?  "15&#h--QTU]Q^Q^`km{  ~N  OvQX#>O]`ai]j]jx{  }E  yF  yF  3G  3G$[QVAV5Wky"#677%SYdghsdtdtk  Cb  Cb  t  Cb  Cb  Cb  b  c  c  s   H66
II)rP   r   )rU   r7   rV   r7   rP   r   )rU   r7   rV   r7   rP   r   )
rU   rf   rV   r7   rd   rg   re   rg   rP   r	   )rU   rq   rP   r=   )rz   rf   rP   r7   )r   r7   rU   r7   rP   r7   )rU   r7   rP   r7   )r   r   r   r7   rA   r=   rP   r   )r   r   r   rE   rP   r   )r   r7   r   r7   rP   r7   )r   r   r@   r7   rP   r   )r   r7   r@   r7   r   r   rP   r7   )r   r   r   r7   rA   r7   r   r7   r@   r7   r   r   rP   r   )r   r   r   r   r   r7   r   r7   r   r7   rP   r   )r   rE   rP   r   )r   r   rP   rO  )r   rE   rS  r   rP   rT  )r   r   r   rE   rv  r7   rP   rE   rR   )r   r   r  r7   rh  r7   r   r7   rs  r  rP   r  )r   r   r  r7   rP   r  )r   r   rA   r7   r*  r7   r-  r	   r  rg   r  r  rP   r  )r   r   rS  r   r   rE   rP   r   )r   r   r   r7   r   r   r   r7   r   r7   r   r7   r   rG   rP   r   )r   rE   r  r   r'  r7   rP   rE   )
r   r   r   rE   r/  r=   r0  r=   rP   rE   )
r   r   r   r6   r   r7   r  r7   rP   r7   )r   r   r   r6   r  r7   rP   r7   )r   r   r   rE   rP   rE   )r   r   rP   rE   )r   r   r  r   r  r   rP   rE   )
r  r	   r  rq   r  r	   r  r	   rP   r7   )
r   r   r   r7   rD   r7   r   r7   rP   r  )r   r   rD   r7   r   r=   rA   r=   r   r7   r  r7   r  r7   r  r   r  r   rP   rE   )S__doc__
__future__r   rH  rJ  r   r   r   r|  dataclassesr   r   r   r   r   decimalr	   r
   sqlite3r   r   typingr   jarvis_finance.audit.logr   jarvis_finance.imports.commonr   +jarvis_finance.imports.postfinance_etradingr   r   r   r   r  #jarvis_finance.market_data.mappingsr   -jarvis_finance.services.portfolio_aggregationr   r   r,  rY  rV  r{  r   r@  rA  r6   rT   r`   rb   rn   rp   ry   r   r   r   r   r   r   r   r   r   r  r#  rC  rN  rR  rt  r  r  r  r  r$  r&  r-  r  r  r  r  r  r  r  r  r  rM   rN   rO   <module>r     s    # " " " " "     				  ! ! ! ! ! ! 3 3 3 3 3 3 3 3 3 3 3 3 - - - - - - - - . . . . . . . .       7 7 7 7 7 7 3 3 3 3 3 3         
      Q P P P P P       
 - 6 ;  sss    dcc  $0 0 0 0 0 0 0 0"   " " " "K K K K AFX]        HM_d U U U U U U
A A A Ax x x xJ J J J^ ^ ^ ^
' ' ' ' 4 4 4 4"9 9 9 9M M M MJ J J JM M M M<G G G GT; ; ; ;|0 0 0 0f, , , ,   $ $ $ $V 	W W W W W W~ '+%' %' %' %' %'PE E E E
w2 w2 w2 w2t~ ~ ~ ~B	Z 	Z 	Z 	Z 	Z   ( &*"&x x x x x xv   &RA RA RA RAjG G G GT$b $b $b $bN 9;!      &   j j j j  @D^b&\cAJ J J J J J J JrN   