o
    6†j³'  ã                   @   s  d dl Z d dlZd dlZd dlmZ edd e  dd¡e  dd¡e  d	d
¡e  dd¡e  dd¡dœZdd„ Zd'dede	de
fdd„Zde	defdd„Zde	defdd„Zde	defdd„Zde	defdd „Zde	defd!d"„Zde	defd#d$„Zde	defd%d&„ZdS )(é    N)Úload_dotenvT)ÚoverrideÚDB_HOSTÚ	localhostÚDB_PORTÚ5432ÚDB_NAMEÚfyndoÚDB_USERÚpostgresÚDB_PASSWORDÚ )ÚhostÚportÚdatabaseÚuserÚpasswordc                   C   s   t jdi t¤ŽS )N© )Úpsycopg2ÚconnectÚ	DB_CONFIGr   r   r   ú,/var/www/html/fyndo/pharma/fyndo/database.pyÚget_connection   s   r   Úuploaded_filesÚchat_idÚreturnc              
   C   sz   t ƒ }| ¡ }dd„ | D ƒ}| d|t |¡t| ƒt d| i¡f¡ | ¡ d }| ¡  | ¡  | ¡  t	d|› ƒ |S )Nc                 S   s   g | ]}|  d ¡‘qS )Úfilename©Úget)Ú.0Úfr   r   r   Ú
<listcomp>   s    zsave_upload.<locals>.<listcomp>z‚
        INSERT INTO uploads (chat_id, filenames, total_files, response)
        VALUES (%s, %s, %s, %s)
        RETURNING id
    r   r   u   [DB] uploads saved â€” chat_id=)
r   ÚcursorÚexecuteÚjsonÚdumpsÚlenÚfetchoneÚcommitÚcloseÚprint)r   r   ÚconnÚcurÚ	filenamesÚrow_idr   r   r   Úsave_upload   s    üü
r/   Úresultc                 C   sÔ   t ƒ }| ¡ }| dg ¡}tdd„ |D ƒi ƒ}| d¡}| d| || d¡| d¡t |¡t | di ¡¡t | d	i ¡¡t | d
i ¡¡f¡ |rR| d|| f¡ | ¡  | ¡  | ¡  t	d| › d|› ƒ d S )NÚclassificationsc                 s   s    | ]
}|  d ¡r|V  qdS )Úbatch_idNr   )r   Úcr   r   r   Ú	<genexpr>7   s   € z"save_three_boom.<locals>.<genexpr>r2   aW  
        INSERT INTO three_boom
        (chat_id, batch_id, product_name, plant,
         classifications, readiness_summary, extraction_summary, validate)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        ON CONFLICT (chat_id) DO UPDATE SET
            batch_id = EXCLUDED.batch_id,
            product_name = EXCLUDED.product_name,
            plant = EXCLUDED.plant,
            classifications = EXCLUDED.classifications,
            readiness_summary = EXCLUDED.readiness_summary,
            extraction_summary = EXCLUDED.extraction_summary,
            validate = EXCLUDED.validate
    Úproduct_nameÚplantÚreadiness_summaryÚextraction_summaryÚvalidatez3UPDATE uploads SET batch_id = %s WHERE chat_id = %su"   [DB] three_boom saved â€” chat_id=z batch=)
r   r"   r   Únextr#   r$   r%   r(   r)   r*   )r   r0   r+   r,   r1   Úfirstr2   r   r   r   Úsave_three_boom3   s2   
øóþr<   c                 C   s    t ƒ }| ¡ }| di ¡}| d| | d¡| dd¡| dd¡| dd¡| dd¡| d	¡t |¡f¡ | ¡  | ¡  | ¡  td
| › d| dd¡› ƒ d S )NÚsummarya  
        INSERT INTO exceptions
        (chat_id, batch_id, total_exceptions, critical,
         major, minor, risk_level, response)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        ON CONFLICT (chat_id) DO UPDATE SET
            batch_id = EXCLUDED.batch_id,
            total_exceptions = EXCLUDED.total_exceptions,
            critical = EXCLUDED.critical,
            major = EXCLUDED.major,
            minor = EXCLUDED.minor,
            risk_level = EXCLUDED.risk_level,
            response = EXCLUDED.response
    r2   Útotal_exceptionsr   ÚcriticalÚmajorÚminorÚ
risk_levelu"   [DB] exceptions saved â€” chat_id=z total=©	r   r"   r   r#   r$   r%   r(   r)   r*   )r   r0   r+   r,   r=   r   r   r   Úsave_exceptionsc   s$   



øó rD   c                 C   s”   t ƒ }| ¡ }| di ¡}| di ¡}| d| | d¡| d¡| d¡| d¡dt |¡f¡ | ¡  | ¡  | ¡  td	| › d
| d¡› ƒ d S )NÚinvestigation_summaryÚ
trip_scorez¬
        INSERT INTO rca
        (chat_id, batch_id, risk_level, trip_score,
         approval_status, confirmed, response)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
    r2   rB   ÚscoreÚapproval_statusFu   [DB] rca saved â€” chat_id=z risk=rC   )r   r0   r+   r,   r=   Útripr   r   r   Úsave_rcaˆ   s$   ùûrJ   Úpayloadc              
   C   st   t ƒ }| ¡ }| dd| d¡| d¡| d¡| d¡| f¡ | ¡  | ¡  | ¡  td| › d| d¡› ƒ d S )	NzÑ
        UPDATE rca SET
            confirmed    = %s,
            confirmed_by = %s,
            confirmed_at = %s,
            cause_id     = %s,
            role         = %s
        WHERE chat_id = %s
    TÚconfirmed_byÚconfirmed_atÚcause_idÚroleu   [DB] rca confirmed â€” chat_id=ú by=©r   r"   r#   r   r(   r)   r*   ©r   rK   r+   r,   r   r   r   Úupdate_rca_confirmation¥   s   	úørS   c                 C   s„   t ƒ }| ¡ }| dg ¡}| d| | d¡| d¡| d¡t|ƒdt |¡f¡ | ¡  | ¡  | ¡  t	d| › dt|ƒ› ƒ d S )	NÚcapa_actionsz­
        INSERT INTO capa
        (chat_id, batch_id, deviation_id, severity,
         total_actions, plan_status, response)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
    r2   Údeviation_idÚseverityÚPendingu   [DB] capa saved â€” chat_id=z	 actions=)
r   r"   r   r#   r&   r$   r%   r(   r)   r*   )r   r0   r+   r,   Úactionsr   r   r   Ú	save_capaÂ   s"   ùûrY   c              
   C   sn   t ƒ }| ¡ }| dd| d¡| d¡| dd¡| f¡ | ¡  | ¡  | ¡  td| › d| d¡› ƒ d S )	NzË
        UPDATE capa SET
            plan_status        = %s,
            approved_by        = %s,
            approved_at        = %s,
            send_for_execution = %s
        WHERE chat_id = %s
    ÚApprovedÚapproved_byÚapproved_atÚsend_for_executionFu   [DB] capa approved â€” chat_id=rP   rQ   rR   r   r   r   Úupdate_capa_approvalÞ   s   
ûùr^   c                 C   sž   t ƒ }| ¡ }| di ¡}| d| | d¡| d¡| d¡| d¡| d¡| d¡| d	¡t |¡f	¡ | ¡  | ¡  | ¡  td
| › d| d¡› ƒ d S )NÚreport_metadataaz  
        INSERT INTO reports
        (chat_id, batch_id, ref_id, severity,
         generated_date, pdf_filename, download_url,
         evidence_score, response)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
        ON CONFLICT (chat_id) DO UPDATE SET
            batch_id = EXCLUDED.batch_id,
            ref_id = EXCLUDED.ref_id,
            severity = EXCLUDED.severity,
            generated_date = EXCLUDED.generated_date,
            pdf_filename = EXCLUDED.pdf_filename,
            download_url = EXCLUDED.download_url,
            evidence_score = EXCLUDED.evidence_score,
            response = EXCLUDED.response
    r2   Úref_idrV   Úgenerated_dateÚpdf_filenameÚdownload_urlÚevidence_coverage_scoreu   [DB] report saved â€” chat_id=z ref=rC   )r   r0   r+   r,   Úmetar   r   r   Úsave_reportù   s&   ÷ñrf   )N)Úosr$   r   Údotenvr   Úgetenvr   r   ÚlistÚstrÚintr/   Údictr<   rD   rJ   rS   rY   r^   rf   r   r   r   r   Ú<module>   s(    





û	0%