§
    éƒtj€(  ã                  óÂ   — d Z ddlmZ ddlZddlmZ ddlmZ ddlmZ ddl	m
Z
 ddlmZmZ e G d	„ d
¦  «        ¦   «         Zd d„Zd!d„Zddœd"d„Zddœd#d„Zddœd"d„ZdS )$z8Multi-document compare, reconcile, timeline, duplicates.é    )ÚannotationsN)Ú	dataclass)Údatetime)ÚAny)Úget_connection)ÚComputedResultÚ_docs_for_agentc                  ó8   — e Zd ZU ded<   ded<   ded<   ded<   dS )Ú	MatchPairÚstrÚleft_idÚright_idÚfloatÚscoreÚreasonN)Ú__name__Ú
__module__Ú__qualname__Ú__annotations__© ó    ú,/var/www/html/CA-Chatbot/app/fdi/multidoc.pyr   r      s4   € € € € € € à€L€L�LØ€M€M�MØ€L€L�LØ€K€K�K€K€Kr   r   Úvalueú
str | NoneÚreturnc                óx  — | sd S |                       ¦   «         }dD ]a}	 t          j        |                     dd¦  «        |¦  «                             ¦   «                              ¦   «         c S # t          $ r Y Œ^w xY wt          j        d|¦  «        }|r¦	 | 	                    d¦  «        › d| 	                    d¦  «        › d| 	                    d¦  «        › �}d	D ]M}	 t          j        ||¦  «                             ¦   «                              ¦   «         c S # t          $ r Y ŒJw xY wn# t          $ r Y d S w xY wd S )
N)ú%d %b %yú%d %b %Yz%d-%m-%Yz%d/%m/%Yz%Y-%m-%dú%d %B %Yú'Ú z)(\d{1,2})\s+([A-Za-z]{3,9})\s*'?(\d{2,4})é   Ú é   é   )r   r   z%d %B %yr   )Ústripr   ÚstrptimeÚreplaceÚdateÚ	isoformatÚ
ValueErrorÚreÚsearchÚgroupÚ	Exception)r   ÚvÚfmtÚmÚraws        r   Ú_parse_loose_dater4      sz  € Øð ØˆtØ�Š‰Œ€AØWð ð ˆð	ÝÔ$ Q§Y¢Y¨s°BÑ%7Ô%7¸Ñ=Ô=×BÒBÑDÔD×NÒNÑPÔPÐPÐPÐPøÝð 	ð 	ð 	ØˆHð	øøøå
Œ	Ð>ÀÑBÔB€AØð 	ð	Ø—W’W˜Q‘Z”ZÐ;Ð; !§'¢'¨!¡*¤*Ð;Ð;¨q¯wªw°q©z¬zÐ;Ð;ˆCØGð ð �ðÝ#Ô,¨S°#Ñ6Ô6×;Ò;Ñ=Ô=×GÒGÑIÔIÐIÐIÐIøÝ!ð ð ð Ø�Hðøøøðøõ
 ð 	ð 	ð 	Ø�4�4ð	øøøàˆ4sI   žAA-Á-
A:Á9A:ÂAD) Ã8DÄD) Ä
D%Ä"D) Ä$D%Ä%D) Ä)
D7Ä6D7Úorg_idr   Úagent_idr   c                ó   — t          ¦   «         5 }|                     d| |f¦  «                             ¦   «         }d„ |D ¦   «         }t          |¦  «        dk     rt	          dddd¬¦  «        cd d d ¦  «         S t	          d	dt          |¦  «        |d
„ |D ¦   «         ddt          |¦  «        › d�d¬¦  «        cd d d ¦  «         S # 1 swxY w Y   d S )Na‹  
            SELECT d.id, d.filename, d.doc_type, d.holder_name, d.period_start, d.period_end,
                   d.parse_confidence,
                   (SELECT value_numeric FROM fdi_assertions a
                    WHERE a.document_id=d.id AND a.kind IN ('total_spend','invoice_total','net_pay','portfolio_value')
                    ORDER BY a.confidence DESC LIMIT 1) AS primary_metric,
                   (SELECT kind FROM fdi_assertions a
                    WHERE a.document_id=d.id AND a.kind IN ('total_spend','invoice_total','net_pay','portfolio_value')
                    ORDER BY a.confidence DESC LIMIT 1) AS primary_kind,
                   (SELECT COUNT(*) FROM fdi_line_items l WHERE l.document_id=d.id) AS line_count
            FROM fdi_documents d
            WHERE d.org_id=? AND d.agent_id=? AND d.status='ready'
            ORDER BY d.indexed_at DESC
            LIMIT 20
            c                ó,   — g | ]}t          |¦  «        ‘ŒS r   ©Údict©Ú.0Úrs     r   ú
<listcomp>z%compare_documents.<locals>.<listcomp>A   ó   € Ð'Ð'Ð'˜Q•�a‘”Ð'Ð'Ð'r   r$   FÚcomparez/Need at least two indexed documents to compare.çš™™™™™é?©ÚmessageÚ
confidenceTc                ó8   — g | ]}t          |d          ¦  «        ‘ŒS ©Úid©r   ©r<   Úxs     r   r>   z%compare_documents.<locals>.<listcomp>N   ó"   € Ð6Ð6Ð6¨1�#˜a œg™,œ,Ð6Ð6Ð6r   Úcompare_docszComparison across z
 documentsg333333ë?©Ú	row_countÚ
group_rowsÚevidence_idsÚsql_fingerprintrC   rD   )r   ÚexecuteÚfetchallÚlenr   )r5   r6   ÚconnÚrowsÚitemss        r   Úcompare_documentsrX   -   sZ  € Ý	Ñ	Ô	ð $
˜TØ�|Š|ðð �XÐñ!
ô 
÷" Š(‰*Œ*ð# 	ð$ (Ð' $Ð'Ñ'Ô'ˆÝˆu‰:Œ:˜Š>ˆ>Ý!ØØØIØð	ñ ô ð+$
ð $
ð $
ð $
ñ $
ô $
ð $
ð $
õ6 ØØÝ˜%‘j”jØØ6Ð6°Ð6Ñ6Ô6Ø*Ø?­¨U©¬Ð?Ð?Ð?Øð	
ñ 	
ô 	
ð7$
ð $
ð $
ð $
ñ $
ô $
ð $
ð $
ð $
ð $
ð $
ð $
øøøð $
ð $
ð $
ð $
ð $
ð $
s   �ACÁ8>CÃCÃ
Cg      ð?)Úamount_tolerancerY   r   c                ó²  — t          ¦   «         5 }t          || |¦  «        }|st          ddd¬¦  «        cddd¦  «         S d                     d„ |D ¦   «         ¦  «        }|                     d|› d	�| g|¢R ¦  «                             ¦   «         }|                     d
|› d�| g|¢R ¦  «                             ¦   «         }d„ |D ¦   «         }d„ |D ¦   «         }	|st          dddd¬¦  «        cddd¦  «         S |	st          dddd¬¦  «        cddd¦  «         S t          ¦   «         }
g }g }|D �]7}t          |d         ¦  «        }d}d}|	D ]W}t          |d         ¦  «        }||
v rŒt          |d         pd¦  «        }t          ||z
  ¦  «        }||k    r|�||k     r|}|}ŒX|r“|
 
                    t          |d         ¦  «        ¦  «         |                     |                     d¦  «        ||                     d¦  «        t          |d         ¦  «        |                     d¦  «        |ddœ¦  «         �Œ|                     |                     d¦  «        |ddœ¦  «         �Œ9||z   }t          ddt          |¦  «        |ddt          |¦  «        › d t          |¦  «        › d!t          |¦  «        › d"�d¬#¦  «        cddd¦  «         S # 1 swxY w Y   dS )$zLMatch invoice/receipt totals to bank/UPI outflows by amount (+/- tolerance).FÚ	reconcileúNo indexed documents.©rC   NÚ,c              3  ó   K  — | ]}d V — ŒdS ©Ú?Nr   ©r<   Ú_s     r   ú	<genexpr>z$reconcile_amounts.<locals>.<genexpr>`   ó"   è è € Ð+Ð+˜a�cÐ+Ð+Ð+Ð+Ð+Ð+r   zì
            SELECT a.id, a.value_numeric, a.label, d.filename, d.doc_type, d.id AS document_id
            FROM fdi_assertions a
            JOIN fdi_documents d ON d.id=a.document_id
            WHERE a.org_id=? AND a.document_id IN (z’)
              AND a.kind IN ('invoice_total','receipt_total','amount_due','po_total')
              AND a.value_numeric IS NOT NULL
            a  
            SELECT l.id, COALESCE(l.debit, l.amount) AS amt, l.event_date, l.description_norm,
                   d.filename, d.doc_type
            FROM fdi_line_items l
            JOIN fdi_documents d ON d.id=l.document_id
            WHERE l.org_id=? AND l.document_id IN (zý)
              AND l.direction='out'
              AND COALESCE(l.debit, l.amount, 0) > 0
              AND COALESCE(l.debit, l.amount, 0) < 5000000
              AND d.doc_type IN ('bank_statement','upi_statement','credit_card_statement')
            c                ó,   — g | ]}t          |¦  «        ‘ŒS r   r9   r;   s     r   r>   z%reconcile_amounts.<locals>.<listcomp>z   ó   € Ð)Ð)Ð)˜1�t�A‰wŒwÐ)Ð)Ð)r   c                ó,   — g | ]}t          |¦  «        ‘ŒS r   r9   r;   s     r   r>   z%reconcile_amounts.<locals>.<listcomp>{   rg   r   zDNo invoice/receipt totals found to reconcile. Upload invoices first.rA   rB   z5No bank/UPI outflows found to match against invoices.Úvalue_numericrG   Úamtr   ÚfilenameÚdescription_normÚ
event_dateÚmatched)ÚinvoiceÚinvoice_amountÚpaymentÚpayment_amountÚpayment_dateÚdeltaÚstatusÚ	unmatched)ro   rp   ru   TÚreconcile_amountszReconciled ú/z& invoice totals to bank/UPI outflows (z unmatched).©rN   rO   rQ   rC   rD   )r   r	   r   ÚjoinrR   rS   Úsetr   r   ÚabsÚaddÚappendÚgetrT   )r5   r6   rY   rU   Údoc_idsÚphÚinvoicesÚpaymentsÚinvÚpayÚused_payrn   Úunmatched_invro   ÚtargetÚbestÚ
best_deltaÚpÚpidrj   rt   rV   s                         r   rw   rw   U   ss  € õ 
Ñ	Ô	ð a
˜TÝ! $¨°Ñ9Ô9ˆØð 	WÝ! %¨Ð>UÐVÑVÔVða
ð a
ð a
ð a
ñ a
ô a
ð a
ð a
ð �XŠXÐ+Ð+ 7Ð+Ñ+Ô+Ñ+Ô+ˆØ—<’<ðð 57ð	ð ð ð Ð�gÐÐñ

ô 

÷ Š(‰*Œ*ð 	ð —<’<ð
ð
 57ð
ð 
ð 
ð Ð�gÐÐñ
ô 
÷ Š(‰*Œ*ð 	ð *Ð) Ð)Ñ)Ô)ˆØ)Ð) Ð)Ñ)Ô)ˆØð 	Ý!ØØØ^Øð	ñ ô ðCa
ð a
ð a
ð a
ñ a
ô a
ð a
ð a
ðN ð 	Ý!ØØØOØð	ñ ô ðQa
ð a
ð a
ð a
ñ a
ô a
ð a
ð a
õ^ !™UœUˆØ(*ˆØ.0ˆØð !	ñ !	ˆGÝ˜7 ?Ô3Ñ4Ô4ˆFØˆDØˆJØð 'ð '�Ý˜!˜Dœ'‘l”l�Ø˜(�?�?ØÝ˜A˜eœH˜M¨Ñ*Ô*�Ý˜C &™LÑ)Ô)�ØÐ,Ò,Ð,°*Ð2DÈÐPZÒHZÐHZØ�DØ!&�JøØð Ø—’�S  d¤™_œ_Ñ-Ô-Ð-Ø—’à#*§;¢;¨zÑ#:Ô#:Ø*0Ø#'§8¢8Ð,>Ñ#?Ô#?Ý*/°°U´Ñ*<Ô*<Ø(,¯ª°Ñ(>Ô(>Ø!+Ø"+ðð ñ
ô 
ð 
ñ 
ð ×$Ò$à#*§;¢;¨zÑ#:Ô#:Ø*0Ø"-ðð ñô ð ñ ð ˜Ñ&ˆÝØØÝ˜$‘i”iØØ/ð5�c '™lœlð 5ð 5­S°©X¬Xð 5ð 5Ý˜Ñ&Ô&ð5ð 5ð 5ð ð
ñ 
ô 
ðma
ð a
ð a
ð a
ñ a
ô a
ð a
ð a
ð a
ð a
ð a
ð a
øøøð a
ð a
ð a
ð a
ð a
ð a
s%   �%KÁB+KÃ9KÄF%KËKËKé   )ÚlimitrŽ   Úintc                ó–  — t          ¦   «         5 }t          || |¦  «        }|st          ddd¬¦  «        cd d d ¦  «         S d                     d„ |D ¦   «         ¦  «        }|                     d|› d�| g|¢|‘R ¦  «                             ¦   «         }d	„ |D ¦   «         }|D ]6}t          t          |                     d
¦  «        pd¦  «        ¦  «        |d<   Œ7| 	                    d„ d¬¦  «         t          ddt          |¦  «        |d„ |D ¦   «         ddt          |¦  «        › d�|rdnd¬¦  «        cd d d ¦  «         S # 1 swxY w Y   d S )NFÚtimeliner\   r]   r^   c              3  ó   K  — | ]}d V — ŒdS r`   r   rb   s     r   rd   z$timeline_activity.<locals>.<genexpr>Ê   re   r   a-  
            SELECT l.id, l.event_date, l.description_norm, l.direction,
                   COALESCE(l.debit, l.credit, l.amount) AS amt, d.filename, d.doc_type
            FROM fdi_line_items l
            JOIN fdi_documents d ON d.id=l.document_id
            WHERE l.org_id=? AND l.document_id IN (zø)
              AND l.event_date IS NOT NULL
              AND COALESCE(l.debit, l.credit, l.amount, 0) > 0
              AND COALESCE(l.debit, l.credit, l.amount, 0) < 5000000
            ORDER BY l.event_date DESC
            LIMIT ?
            c                ó,   — g | ]}t          |¦  «        ‘ŒS r   r9   r;   s     r   r>   z%timeline_activity.<locals>.<listcomp>Ú   r?   r   rm   r!   Úiso_datec                ó0   — |                       d¦  «        pdS )Nr”   r!   )r   ©rJ   s    r   ú<lambda>z#timeline_activity.<locals>.<lambda>Þ   s   €  §¢ zÑ!2Ô!2Ð!8°b€ r   T)ÚkeyÚreversec                ó8   — g | ]}t          |d          ¦  «        ‘ŒS rF   rH   rI   s     r   r>   z%timeline_activity.<locals>.<listcomp>ä   rK   r   zTimeline of z dated line itemsrA   gš™™™™™Ù?rM   )r   r	   r   rz   rR   rS   r4   r   r   ÚsortrT   )	r5   r6   rŽ   rU   r€   r�   rV   rW   Úits	            r   Útimeline_activityr�   À   sû  € õ 
Ñ	Ô	ð "
˜TÝ! $¨°Ñ9Ô9ˆØð 	VÝ! %¨Ð=TÐUÑUÔUð"
ð "
ð "
ð "
ñ "
ô "
ð "
ð "
ð �XŠXÐ+Ð+ 7Ð+Ñ+Ô+Ñ+Ô+ˆØ�|Š|ðð
 57ðð ð ð Ð%�gÐ%˜uÐ%Ð%ñ
ô 
÷ Š(‰*Œ*ð 	ð (Ð' $Ð'Ñ'Ô'ˆàð 	Pð 	PˆBÝ.­s°2·6²6¸,Ñ3GÔ3GÐ3MÈ2Ñ/NÔ/NÑOÔOˆBˆz‰NˆNØ�
Š
Ð8Ð8À$ˆ
ÑGÔGÐGÝØØÝ˜%‘j”jØØ6Ð6°Ð6Ñ6Ô6Ø&Ø@¥3 u¡:¤:Ð@Ð@Ð@Ø#Ð,�s�s¨ð	
ñ 	
ô 	
ð3"
ð "
ð "
ð "
ñ "
ô "
ð "
ð "
ð "
ð "
ð "
ð "
øøøð "
ð "
ð "
ð "
ð "
ð "
s   �%D>ÁC0D>Ä>EÅEg{®Gáz„?c                ó¾  — t          ¦   «         5 }t          || |¦  «        }|st          ddd¬¦  «        cd d d ¦  «         S d                     d„ |D ¦   «         ¦  «        }|                     d|› d�| g|¢R ¦  «                             ¦   «         }i }|D ]¼}t          |¦  «        }	t          t          |	 	                    d	¦  «        pd
¦  «        d¦  «        }
t          |	 	                    d¦  «        pd¦  «        |
d›f}|                     ||	 	                    d¦  «        |
d
|	 	                    d¦  «        dœ¦  «        }|dxx         dz  cc<   Œ½d„ |                     ¦   «         D ¦   «         }|                     d„ ¬¦  «         t          ddt          |¦  «        |d d…         ddt          |¦  «        › d�|rdnd¬¦  «        cd d d ¦  «         S # 1 swxY w Y   d S )NFÚ
duplicatesr\   r]   r^   c              3  ó   K  — | ]}d V — ŒdS r`   r   rb   s     r   rd   z"find_duplicates.<locals>.<genexpr>õ   re   r   zÞ
            SELECT l.event_date,
                   COALESCE(l.debit, l.credit, l.amount) AS amt,
                   l.description_norm
            FROM fdi_line_items l
            WHERE l.org_id=? AND l.document_id IN (z’)
              AND COALESCE(l.debit, l.credit, l.amount, 0) > 0
              AND COALESCE(l.debit, l.credit, l.amount, 0) < 5000000
            rj   r   r$   rm   r!   z.2frl   )rm   rj   ÚnÚsample_descr¡   r"   c                ó*   — g | ]}|d          dk    ¯|‘ŒS )r¡   r"   r   )r<   r0   s     r   r>   z#find_duplicates.<locals>.<listcomp>  s!   € Ð;Ð;Ð;�q°°#´¸²
°
�°
°
°
r   c                óZ   — t          | d         ¦  «         t          | d         ¦  «         fS )Nr¡   rj   )r�   r   r–   s    r   r—   z!find_duplicates.<locals>.<lambda>  s$   € ¥3 q¨¤v¡;¤; ,µ°q¸´x±´Ð0@Ð!A€ r   )r˜   Tr�   zFound z& possible duplicate amount/date groupsg      è?gffffffæ?ry   )r   r	   r   rz   rR   rS   r:   Úroundr   r   r   Ú
setdefaultÚvaluesr›   rT   )r5   r6   rY   rU   r€   r�   rV   Úbucketsr=   Údrj   r˜   ÚslotrW   s                 r   Úfind_duplicatesr«   ë   s€  € õ 
Ñ	Ô	ð %
˜TÝ! $¨°Ñ9Ô9ˆØð 	XÝ! %¨Ð?VÐWÑWÔWð%
ð %
ð %
ð %
ñ %
ô %
ð %
ð %
ð �XŠXÐ+Ð+ 7Ð+Ñ+Ô+Ñ+Ô+ˆØ�|Š|ðð
 57ðð ð ð Ð�gÐÐñ
ô 
÷ Š(‰*Œ*ð 	ð :<ˆØð 	ð 	ˆAÝ�Q‘”ˆAÝ�˜aŸeše E™lœlÐ/¨aÑ0Ô0°!Ñ4Ô4ˆCÝ�q—u’u˜\Ñ*Ô*Ð0¨bÑ1Ô1°c°<°<Ð@ˆCØ×%Ò%ØØ Ÿušu \Ñ2Ô2¸3ÀQÐWX×W\ÒW\Ð]oÑWpÔWpÐqÐqñô ˆDð �ˆIˆIŒI˜‰NˆIˆI‰IˆIØ;Ð;˜GŸNšNÑ,Ô,Ð;Ñ;Ô;ˆØ�
Š
ÐAÐAˆ
ÑBÔBÐBÝØØÝ˜%‘j”jØ˜S˜b˜S”zØ(ØO�S ™ZœZÐOÐOÐOØ$Ð-�t�t¨#ð
ñ 
ô 
ð;%
ð %
ð %
ð %
ñ %
ô %
ð %
ð %
ð %
ð %
ð %
ð %
øøøð %
ð %
ð %
ð %
ð %
ð %
s   �%GÁFGÇGÇG)r   r   r   r   )r5   r   r6   r   r   r   )r5   r   r6   r   rY   r   r   r   )r5   r   r6   r   rŽ   r�   r   r   )Ú__doc__Ú
__future__r   r,   Údataclassesr   r   Útypingr   Úapp.databaser   Úapp.fdi.computer   r	   r   r4   rX   rw   r�   r«   r   r   r   ú<module>r²      s^  ðØ >Ð >à "Ð "Ð "Ð "Ð "Ð "à 	€	€	€	Ø !Ð !Ð !Ð !Ð !Ð !Ø Ð Ð Ð Ð Ð Ø Ð Ð Ð Ð Ð à 'Ð 'Ð 'Ð 'Ð 'Ð 'Ø ;Ð ;Ð ;Ð ;Ð ;Ð ;Ð ;Ð ;ð ðð ð ð ð ñ ô ñ „ððð ð ð ð.%
ð %
ð %
ð %
ðX "ð	h
ð h
ð h
ð h
ð h
ð h
ð^ ð	(
ð (
ð (
ð (
ð (
ð (
ð^ #ð	+
ð +
ð +
ð +
ð +
ð +
ð +
ð +
r   