§
    éƒtjr.  ã                  óÄ   — d Z ddlmZ ddlZddlmZmZ ddlmZ ddl	m
Z
 e G d„ d¦  «        ¦   «         Zd'd„Zddœd(d„Zd)d„Zddœd*d„Zdddœd+d"„Zd,d#„Zd,d$„Zd-d&„ZdS ).uH   Deterministic computation engine â€” LLMs must not invent these numbers.é    )ÚannotationsN)Ú	dataclassÚfield)ÚAny)Úget_connectionc                  óÊ   — e Zd ZU ded<   ded<   dZded<   dZded	<   dZded
<   dZded<    ee	¬¦  «        Z
ded<    ee	¬¦  «        Zded<   dZded<   dZded<   dZded<   dS )ÚComputedResultÚboolÚokÚstrÚmetricNzfloat | NoneÚvalueú
str | NoneÚunitÚcurrencyr   ÚintÚ	row_count)Údefault_factoryzlist[dict[str, Any]]Ú
group_rowsú	list[str]Úevidence_idsÚ Úsql_fingerprintÚmessageg        ÚfloatÚ
confidence)Ú__name__Ú
__module__Ú__qualname__Ú__annotations__r   r   r   r   r   Úlistr   r   r   r   r   © ó    ú+/var/www/html/CA-Chatbot/app/fdi/compute.pyr	   r	      s×   € € € € € € à€H€H�HØ€K€K�KØ€EÐÐÐÑØ€DÐÐÐÑØ€HÐÐÐÑØ€IÐÐÐÑØ', u¸TÐ'BÑ'BÔ'B€JÐBÐBÐBÑBØ#˜e°DÐ9Ñ9Ô9€LÐ9Ð9Ð9Ñ9Ø€OÐÐÐÑØ€GÐÐÐÑØ€JÐÐÐÑÐÐr#   r	   Úconnr   Úorg_idr   Úagent_idÚreturnr   c                ón   — |                       d||f¦  «                             ¦   «         }d„ |D ¦   «         S )Nzm
        SELECT id FROM fdi_documents
        WHERE org_id = ? AND agent_id = ? AND status = 'ready'
        c                ór   — g | ]4}t          t          |t          ¦  «        r|d          n|d         ¦  «        ‘Œ5S )Úidr   )r   Ú
isinstanceÚdict©Ú.0Úrs     r$   ú
<listcomp>z#_docs_for_agent.<locals>.<listcomp>#   s;   € ÐHÐHÐH¸a�C�: a­Ñ.Ô.Ð8��$”�°A°a´DÑ9Ô9ÐHÐHÐHr#   )ÚexecuteÚfetchall)r%   r&   r'   Úrowss       r$   Ú_docs_for_agentr5      sI   € Ø�<Š<ð	ð 
�Ðñô ÷ ‚h�j„jð 	ð IÐHÀ4ÐHÑHÔHÐHr#   r   )Ú
value_normÚid_typer6   c                ó  — t          ¦   «         5 }t          || |¦  «        }|st          ddd¬¦  «        cd d d ¦  «         S d                     d„ |D ¦   «         ¦  «        }|r3|                     d|› d�| ||g|¢R ¦  «                             ¦   «         }n1|                     d	|› d�| |g|¢R ¦  «                             ¦   «         }d
„ |D ¦   «         }|s#t          ddd|› d�d¬¦  «        cd d d ¦  «         S t          ddd t          |¦  «        |d„ |D ¦   «         d|› d|pd› �dt          |¦  «        › d|› d�t          |d                              d¦  «        pd¦  «        ¬¦	  «	        cd d d ¦  «         S # 1 swxY w Y   d S )NFÚ
identifierúNo indexed financial documents.©r   Ú,c              3  ó   K  — | ]}d V — ŒdS ©Ú?Nr"   ©r/   Ú_s     r$   ú	<genexpr>z$lookup_identifier.<locals>.<genexpr>1   ó"   è è € Ð5Ð5¨ Ð5Ð5Ð5Ð5Ð5Ð5r#   a?  
                SELECT i.id, i.id_type, i.value_raw, i.value_norm, i.confidence, d.filename, d.doc_type
                FROM fdi_identifiers i
                JOIN fdi_documents d ON d.id = i.document_id
                WHERE i.org_id = ? AND i.id_type = ? AND i.value_norm = ?
                  AND i.document_id IN (zV)
                ORDER BY i.confidence DESC
                LIMIT 20
                a*  
                SELECT i.id, i.id_type, i.value_raw, i.value_norm, i.confidence, d.filename, d.doc_type
                FROM fdi_identifiers i
                JOIN fdi_documents d ON d.id = i.document_id
                WHERE i.org_id = ? AND i.id_type = ?
                  AND i.document_id IN (c                ó,   — g | ]}t          |¦  «        ‘ŒS r"   ©r-   r.   s     r$   r1   z%lookup_identifier.<locals>.<listcomp>L   ó   € Ð'Ð'Ð'˜Q•�a‘”Ð'Ð'Ð'r#   zNo z found in the knowledge base.gÍÌÌÌÌÌì?)r   r   Tc                ó8   — g | ]}t          |d          ¦  «        ‘ŒS ©r+   ©r   ©r/   Úxs     r$   r1   z%lookup_identifier.<locals>.<listcomp>Z   ó"   € Ð6Ð6Ð6¨1�#˜a œg™,œ,Ð6Ð6Ð6r#   zidentifiers:ú:Ú*zFound Ú z
 value(s).r   r   g333333ë?)r   r   r   r   r   r   r   )	r   r5   r	   Újoinr2   r3   Úlenr   Úget)	r&   r'   r7   r6   r%   Údoc_idsÚplaceholdersr4   Úitemss	            r$   Úlookup_identifierrV   &   s�  € õ 
Ñ	Ô	ð 1
˜TÝ! $¨°Ñ9Ô9ˆØð 	bÝ! %¨Ð?`ÐaÑaÔað1
ð 1
ð 1
ð 1
ñ 1
ô 1
ð 1
ð 1
ð —x’xÐ5Ð5¨WÐ5Ñ5Ô5Ñ5Ô5ˆØð 	Ø—<’<ðð
 *6ðð ð ð ˜ *Ð7¨wÐ7Ð7ñô ÷ Šh‰jŒjð ˆDð —<’<ðð
 *6ðð ð ð ˜Ð+ 7Ð+Ð+ñô ÷ Šh‰jŒjð ð (Ð' $Ð'Ñ'Ô'ˆØð 	Ý!ØØØD˜gÐDÐDÐDØð	ñ ô ðC1
ð 1
ð 1
ð 1
ñ 1
ô 1
ð 1
ð 1
õN ØØØÝ˜%‘j”jØØ6Ð6°Ð6Ñ6Ô6ØH¨7ÐHÐH°ZÐ5FÀ3ÐHÐHØ=�S ™ZœZÐ=Ð=¨'Ð=Ð=Ð=Ý˜U 1œXŸ\š\¨,Ñ7Ô7Ð?¸4Ñ@Ô@ð

ñ 

ô 

ðO1
ð 1
ð 1
ð 1
ñ 1
ô 1
ð 1
ð 1
ð 1
ð 1
ð 1
ð 1
øøøð 1
ð 1
ð 1
ð 1
ð 1
ð 1
s   �%E6ÁB)E6Ã7A2E6Å6E:Å=E:Úkindc                óä  — t          ¦   «         5 }t          || |¦  «        }|st          d|d¬¦  «        cd d d ¦  «         S d                     d„ |D ¦   «         ¦  «        }|                     d|› d�| |g|¢R ¦  «                             ¦   «         }d„ |D ¦   «         }|s"t          d|d	|› d
�¬¦  «        cd d d ¦  «         S |d         }t          d||                     d¦  «        |                     d¦  «        t          |¦  «        |d„ |D ¦   «         d|› �|                     d¦  «        p|t          |                     d¦  «        pd¦  «        ¬¦
  «
        cd d d ¦  «         S # 1 swxY w Y   d S )NFr:   r;   r<   c              3  ó   K  — | ]}d V — ŒdS r>   r"   r@   s     r$   rB   z get_assertion.<locals>.<genexpr>k   rC   r#   a8  
            SELECT a.id, a.kind, a.label, a.value_numeric, a.currency, a.source, a.confidence,
                   d.filename, d.doc_type, d.holder_name
            FROM fdi_assertions a
            JOIN fdi_documents d ON d.id = a.document_id
            WHERE a.org_id = ? AND a.kind = ? AND a.document_id IN (zy)
            ORDER BY a.confidence DESC, CASE a.source WHEN 'header' THEN 0 ELSE 1 END
            LIMIT 10
            c                ó,   — g | ]}t          |¦  «        ‘ŒS r"   rE   r.   s     r$   r1   z!get_assertion.<locals>.<listcomp>x   rF   r#   zNo 'z' metric on indexed documents.r   TÚvalue_numericr   c                ó8   — g | ]}t          |d          ¦  «        ‘ŒS rH   rI   rJ   s     r$   r1   z!get_assertion.<locals>.<listcomp>ƒ   rL   r#   z
assertion:Úlabelr   çš™™™™™é?)r   r   r   r   r   r   r   r   )	r   r5   r	   rP   r2   r3   rR   rQ   r   )	r&   r'   rW   r%   rS   rT   r4   rU   Úbests	            r$   Úget_assertionr`   a   s3  € õ 
Ñ	Ô	ð  
˜TÝ! $¨°Ñ9Ô9ˆØð 	ZÝ! %¨Ð7XÐYÑYÔYð 
ð  
ð  
ð  
ñ  
ô  
ð  
ð  
ð —x’xÐ5Ð5¨WÐ5Ñ5Ô5Ñ5Ô5ˆØ�|Š|ðð
 FRðð ð ð �TÐ$˜GÐ$Ð$ñ
ô 
÷ Š(‰*Œ*ð 	ð (Ð' $Ð'Ñ'Ô'ˆØð 	dÝ! %¨Ð7b¸dÐ7bÐ7bÐ7bÐcÑcÔcð' 
ð  
ð  
ð  
ñ  
ô  
ð  
ð  
ð( �QŒxˆÝØØØ—(’(˜?Ñ+Ô+Ø—X’X˜jÑ)Ô)Ý˜%‘j”jØØ6Ð6°Ð6Ñ6Ô6Ø/¨Ð/Ð/Ø—H’H˜WÑ%Ô%Ð-¨Ý˜TŸXšX lÑ3Ô3Ð:°sÑ;Ô;ð
ñ 
ô 
ð+ 
ð  
ð  
ð  
ñ  
ô  
ð  
ð  
ð  
ð  
ð  
ð  
øøøð  
ð  
ð  
ð  
ð  
ð  
s   �%E%ÁA3E%ÃBE%Å%E)Å,E))Úcounterparty_likeÚ	directionra   r   c                ó2  — t          ¦   «         5 }t          || |¦  «        }|s!t          dd|› �d¬¦  «        cd d d ¦  «         S d                     d„ |D ¦   «         ¦  «        }| |g|¢}d}|r1d}d	|                     ¦   «         › d	�}	|                     |	|	g¦  «         |                     d
|› d|› d�t          |¦  «        ¦  «                             ¦   «         }
t          t          |
t          ¦  «        r|
d         n|
d         ¦  «        }t          t          |
t          ¦  «        r|
d         n|
d         ¦  «        }t          dd|› �t          |d¦  «        d|d|› d|pd› �d|› d�|rd|› �ndz   |rdnd¬¦  «        cd d d ¦  «         S # 1 swxY w Y   d S )NFÚsum_r:   r;   r<   c              3  ó   K  — | ]}d V — ŒdS r>   r"   r@   s     r$   rB   z sum_direction.<locals>.<genexpr>•   rC   r#   r   z; AND (l.description_norm LIKE ? OR e.canonical_name LIKE ?)ú%a  
            SELECT COALESCE(SUM(
                     COALESCE(
                       CASE WHEN COALESCE(l.debit, 0) > 0 AND COALESCE(l.debit, 0) < 5000000
                            THEN l.debit END,
                       CASE WHEN COALESCE(l.credit, 0) > 0 AND COALESCE(l.credit, 0) < 5000000
                            THEN l.credit END,
                       CASE WHEN COALESCE(l.amount, 0) > 0 AND COALESCE(l.amount, 0) < 5000000
                            THEN l.amount END,
                       0
                     )
                   ), 0) AS total,
                   COUNT(*) AS n
            FROM fdi_line_items l
            LEFT JOIN fdi_entities e ON e.id = l.counterparty_entity_id
            WHERE l.org_id = ? AND l.direction = ? AND l.document_id IN (z)
            z
            Útotalr   Úné   Té   ÚINRzsum:rM   rN   zSum of z flowsz for r^   çš™™™™™Ù?)r   r   r   r   r   r   )r   r5   r	   rP   ÚlowerÚextendr2   ÚtupleÚfetchoner   r,   r-   r   Úround)r&   r'   rb   ra   r%   rS   rT   ÚparamsÚwhere_extraÚlikeÚrowrg   rh   s                r$   Úsum_directionrv   Š   sm  € õ 
Ñ	Ô	ð +
˜TÝ! $¨°Ñ9Ô9ˆØð 	hÝ! %Ð);°	Ð);Ð);ÐEfÐgÑgÔgð+
ð +
ð +
ð +
ñ +
ô +
ð +
ð +
ð —x’xÐ5Ð5¨WÐ5Ñ5Ô5Ñ5Ô5ˆØ# YÐ9°Ð9ˆØˆØð 	(ØWˆKØ3Ð(×.Ò.Ñ0Ô0Ð3Ð3Ð3ˆDØ�MŠM˜4 ˜,Ñ'Ô'Ð'Ø�lŠlðð KWðð ð  ð!ð ð õ$ �&‰MŒMñ'
ô 
÷( Š(‰*Œ*ð) 	õ* ¥j°µdÑ&;Ô&;ÐG�c˜'”l�lÀÀQÄÑHÔHˆÝ�J s­DÑ1Ô1Ð=��C”�°s¸1´vÑ>Ô>ˆÝØØ�9ÐÐÝ˜˜q‘/”/ØØØI 9ÐIÐIÐ/@Ð/GÀCÐIÐIØ/˜iÐ/Ð/Ð/ÐRcÐ3kÐ3NÐ;LÐ3NÐ3NÐ3NÐikÑlØÐ(�s�s Sð	
ñ 	
ô 	
ðE+
ð +
ð +
ð +
ñ +
ô +
ð +
ð +
ð +
ð +
ð +
ð +
øøøð +
ð +
ð +
ð +
ð +
ð +
s   �(FÁD;FÆFÆFÚouté
   )rb   Úlimitry   r   c           
     óÊ  — t          ¦   «         5 }t          || |¦  «        }|st          ddd¬¦  «        cd d d ¦  «         S d                     d„ |D ¦   «         ¦  «        }|                     d|› d�| |g|¢|‘R ¦  «                             ¦   «         }d	„ |D ¦   «         }t          d
dt          |¦  «        |d|› �d|› �|rdnd¬¦  «        cd d d ¦  «         S # 1 swxY w Y   d S )NFÚgroup_counterpartyr:   r;   r<   c              3  ó   K  — | ]}d V — ŒdS r>   r"   r@   s     r$   rB   z(group_by_counterparty.<locals>.<genexpr>Ê   rC   r#   aX  
            SELECT COALESCE(e.canonical_name, l.description_norm, 'Unknown') AS name,
                   SUM(
                     COALESCE(
                       CASE WHEN COALESCE(l.debit, 0) > 0 AND COALESCE(l.debit, 0) < 5000000
                            THEN l.debit END,
                       CASE WHEN COALESCE(l.credit, 0) > 0 AND COALESCE(l.credit, 0) < 5000000
                            THEN l.credit END,
                       CASE WHEN COALESCE(l.amount, 0) > 0 AND COALESCE(l.amount, 0) < 5000000
                            THEN l.amount END,
                       0
                     )
                   ) AS total,
                   COUNT(*) AS n
            FROM fdi_line_items l
            LEFT JOIN fdi_entities e ON e.id = l.counterparty_entity_id
            WHERE l.org_id = ? AND l.direction = ? AND l.document_id IN (a  )
              AND LOWER(COALESCE(e.canonical_name, l.description_norm, '')) NOT LIKE 'upi ref%'
            GROUP BY COALESCE(e.canonical_name, l.description_norm, 'Unknown')
            HAVING SUM(
                     COALESCE(
                       CASE WHEN COALESCE(l.debit, 0) > 0 AND COALESCE(l.debit, 0) < 5000000
                            THEN l.debit END,
                       CASE WHEN COALESCE(l.credit, 0) > 0 AND COALESCE(l.credit, 0) < 5000000
                            THEN l.credit END,
                       CASE WHEN COALESCE(l.amount, 0) > 0 AND COALESCE(l.amount, 0) < 5000000
                            THEN l.amount END,
                       0
                     )
                   ) > 0
            ORDER BY total DESC
            LIMIT ?
            c                ó,   — g | ]}t          |¦  «        ‘ŒS r"   rE   r.   s     r$   r1   z)group_by_counterparty.<locals>.<listcomp>ï   rF   r#   Tzgroup:zTop counterparties by r^   rl   ©r   r   r   r   r   )r   r5   r	   rP   r2   r3   rQ   )	r&   r'   rb   ry   r%   rS   rT   r4   rU   s	            r$   Úgroup_by_counterpartyr   ¿   s�  € õ 
Ñ	Ô	ð 2
˜TÝ! $¨°Ñ9Ô9ˆØð 	jÝ! %Ð)=ÐGhÐiÑiÔið2
ð 2
ð 2
ð 2
ñ 2
ô 2
ð 2
ð 2
ð —x’xÐ5Ð5¨WÐ5Ñ5Ô5Ñ5Ô5ˆØ�|Š|ð ð  KWð! ð  ð  ðB �YÐ0 Ð0¨%Ð0Ð0ñE#
ô #
÷F Š(‰*Œ*ðG 	ðH (Ð' $Ð'Ñ'Ô'ˆÝØØ Ý˜%‘j”jØØ0 YÐ0Ð0Ø8¨YÐ8Ð8Ø#Ð,�s�s¨ð
ñ 
ô 
ðU2
ð 2
ð 2
ð 2
ñ 2
ô 2
ð 2
ð 2
ð 2
ð 2
ð 2
ð 2
øøøð 2
ð 2
ð 2
ð 2
ð 2
ð 2
s   �%CÁB
CÃCÃCc                ó$  — t          ¦   «         5 }|                     d| |f¦  «                             ¦   «         }d„ |D ¦   «         }t          ddt	          |¦  «        |dt	          |¦  «        › d�d¬¦  «        cd d d ¦  «         S # 1 swxY w Y   d S )Na3  
            SELECT id, filename, doc_type, doc_type_confidence, page_count,
                   holder_name, period_start, period_end, parse_confidence, status
            FROM fdi_documents
            WHERE org_id = ? AND agent_id = ? AND status = 'ready'
            ORDER BY indexed_at DESC
            c                ó,   — g | ]}t          |¦  «        ‘ŒS r"   rE   r.   s     r$   r1   z&document_inventory.<locals>.<listcomp>  rF   r#   TÚ	inventoryz ready document(s)g      ð?r~   )r   r2   r3   r	   rQ   ©r&   r'   r%   r4   rU   s        r$   Údocument_inventoryr„   û   sð   € Ý	Ñ	Ô	ð 
˜TØ�|Š|ðð �XÐñ	
ô 	
÷ Š(‰*Œ*ð 	ð (Ð' $Ð'Ñ'Ô'ˆÝØØÝ˜%‘j”jØØ'Ý˜5‘z”zÐ5Ð5Ð5Øð
ñ 
ô 
ð
ð 
ð 
ð 
ñ 
ô 
ð 
ð 
ð 
ð 
ð 
ð 
øøøð 
ð 
ð 
ð 
ð 
ð 
s   �A)BÂB	ÂB	c                óª  — t          ¦   «         5 }|                     d| |f¦  «                             ¦   «         }d„ |D ¦   «         }|st          ddd¬¦  «        cd d d ¦  «         S t          ddt	          |¦  «        |d„ |D ¦   «         dd	t          |d
                              d¦  «        pd¦  «        ¬¦  «        cd d d ¦  «         S # 1 swxY w Y   d S )Na>  
            SELECT id, filename, doc_type, holder_name, issuer_name,
                   period_start, period_end, parse_confidence, currency_primary
            FROM fdi_documents
            WHERE org_id = ? AND agent_id = ? AND status = 'ready'
            ORDER BY indexed_at DESC
            LIMIT 20
            c                ó,   — g | ]}t          |¦  «        ‘ŒS r"   rE   r.   s     r$   r1   z$document_profile.<locals>.<listcomp>   rF   r#   FÚprofiler:   r;   Tc                ó8   — g | ]}t          |d          ¦  «        ‘ŒS rH   rI   rJ   s     r$   r1   z$document_profile.<locals>.<listcomp>(  rL   r#   zDocument profiler   Úparse_confidencegffffffæ?)r   r   r   r   r   r   )r   r2   r3   r	   rQ   r   rR   rƒ   s        r$   Údocument_profilerŠ     sV  € Ý	Ñ	Ô	ð 
˜TØ�|Š|ðð �XÐñ

ô 

÷ Š(‰*Œ*ð 	ð (Ð' $Ð'Ñ'Ô'ˆØð 	_Ý! %¨Ð<]Ð^Ñ^Ô^ð
ð 
ð 
ð 
ñ 
ô 
ð 
ð 
õ ØØÝ˜%‘j”jØØ6Ð6°Ð6Ñ6Ô6Ø%Ø&Ý˜U 1œXŸ\š\Ð*<Ñ=Ô=ÐDÀÑEÔEð	
ñ 	
ô 	
ð
ð 
ð 
ð 
ñ 
ô 
ð 
ð 
ð 
ð 
ð 
ð 
øøøð 
ð 
ð 
ð 
ð 
ð 
s   �A
CÁ&ACÃCÃCÚquestionc                óZ  — | pd                      ¦   «         }t          j        d|¦  «        }|r?|                     d¦  «                              ¦   «         }|                     ¦   «         dvr|S t          j        d|¦  «        }|r'|                     d¦  «                              ¦   «         S d S )Nr   zB(?i)(?:on|at|to|for)\s+([A-Za-z][A-Za-z0-9 &.'\-]{1,40})\s*[?.!]*$ri   >   ÚthatÚthisrg   ÚoverallÚ
everythingzR(?i)\b(?:merchant|vendor|paid\s+to|spent\s+on)\s+([A-Za-z][A-Za-z0-9 &.'\-]{1,40}))ÚstripÚreÚsearchÚgrouprm   )r‹   ÚqÚmÚnames       r$   Údetect_merchant_in_questionr˜   /  s®   € Ø	ˆ�R×ÒÑ Ô €AÝ
Œ	ØMØ	ñ	ô 	€Að 	ð Ø�wŠw�q‰zŒz×ÒÑ!Ô!ˆØ�:Š:‰<Œ<ÐQÐQÐQØˆKÝ
Œ	Ø]Ø	ñ	ô 	€Að 	ð "Ø�wŠw�q‰zŒz×ÒÑ!Ô!Ð!Øˆ4r#   )r%   r   r&   r   r'   r   r(   r   )
r&   r   r'   r   r7   r   r6   r   r(   r	   )r&   r   r'   r   rW   r   r(   r	   )
r&   r   r'   r   rb   r   ra   r   r(   r	   )
r&   r   r'   r   rb   r   ry   r   r(   r	   )r&   r   r'   r   r(   r	   )r‹   r   r(   r   )Ú__doc__Ú
__future__r   r’   Údataclassesr   r   Útypingr   Úapp.databaser   r	   r5   rV   r`   rv   r   r„   rŠ   r˜   r"   r#   r$   ú<module>rž      sx  ðØ NÐ Nà "Ð "Ð "Ð "Ð "Ð "à 	€	€	€	Ø (Ð (Ð (Ð (Ð (Ð (Ð (Ð (Ø Ð Ð Ð Ð Ð à 'Ð 'Ð 'Ð 'Ð 'Ð 'ð ðð ð ð ð ñ ô ñ „ððIð Ið Ið Ið  ð8
ð 8
ð 8
ð 8
ð 8
ð 8
ðv&
ð &
ð &
ð &
ð\ %)ð2
ð 2
ð 2
ð 2
ð 2
ð 2
ðr Øð9
ð 9
ð 9
ð 9
ð 9
ð 9
ðx
ð 
ð 
ð 
ð0
ð 
ð 
ð 
ð8ð ð ð ð ð r#   