o
    Uj                  
   @   sx  d dl mZmZmZmZmZ d dlmZ d dlm	Z	 d dl
Z
d dlZd dlZd dlmZ d dlm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 d dlmZ d dlm Z m!Z! d dl"m#Z#m$Z$m%Z%m&Z& d dl'm(Z( d dl)m*Z*m+Z+m,Z,m-Z-m.Z.m+Z+m,Z,m-Z-m.Z. zd dl/m0Z0m1Z1m2Z2m3Z3m4Z4m5Z5m6Z6m7Z7 dZ8e9d W n e:y Z; zdZ8e9de;  W Y dZ;[;ndZ;[;ww eddZ<e<j=edgdgdgd edZ>edZ?e>j@dd e?j@dd deAdeAfd d!ZBe<Cd"d#d$ ZDe<Ed%d&eAfd'd(ZFe<Ed)d&eAfd*d+ZGe<Cd,d-efd.d/ZHe<Ed0ed1fd-ed2ee fd3d4ZIe<Cd5d6d7 ZJe<Kd5d8d9 ZLe<Ed:d-ed&eAfd;d<ZMe<Ed=d-ed&eAfd>d?ZNe<Cd@dAeOfdBdCZPe<CdDd-efdEdFZQe<EdGd-efdHdIZRe<EdJd-efdKdLZSe<EdMd-efdNdOZTe<EdPd-efdQdRZUe<EdSd-efdTdUZVe<EdVd-efdWdXZWe<CdYdZeOd-efd[d\ZXe<Cd]dZeOd-efd^d_ZYe<Cd`dZeOd-efdadbZZe<CdcdZeOd-efdddeZ[e<CdfdZeOd-efdgdhZ\e<CdidZeOd-efdjdkZ]e<Cdld-efdmdnZ^e<Cdodpdq Z_e<KdrdAeOfdsdtZ`dS )u    )FastAPI
UploadFileFileHTTPExceptionRequest)CORSMiddleware)FileResponseN)Path)List)datetimeclassify_documentextract_document)validate_extraction)detect_exceptions)perform_rca)generate_capa)generate_full_reportget_base_url)save_sessionload_sessionupdate_sessionclear_session)run_investigation_agent)	get_user_from_tokenregister_userget_user_by_emailverify_passwordcreate_tokenr   r   r   r   )save_uploadsave_three_boomsave_exceptionssave_rcaupdate_rca_confirmation	save_capaupdate_capa_approvalsave_reportTz[DB] Database connectedFz[DB] Database not connected: zFyndo Pharma API)title*)allow_originsallow_methodsallow_headersuploadsreports)exist_okextractionsreturnc                 C   s   |  dg }tdd |D dtdd |D dtdd |D ddd |D d	d |D d
d |D dd |D dd |D tdd |D i d	}tdt|d  dt|d  dt|d  dt|d  dt|d  dt|d   |S )zj
    Converts nested files[] structure into flat arrays.
    GPT reads ALL files instead of just BMR.
    filesc                 s   $    | ]}| d r| d V  qdS )batch_idNget.0e r:   (/var/www/html/fyndo/pharma/fyndo/main.py	<genexpr><      " z&flatten_extractions.<locals>.<genexpr>Nc                 s   r3   )product_nameNr5   r7   r:   r:   r;   r<   =   r=   c                 s   r3   )plantNr5   r7   r:   r:   r;   r<   >   r=   c                 S   "   g | ]}| d g D ]}|q
qS )
parametersr5   )r8   r9   pr:   r:   r;   
<listcomp>?      " z'flatten_extractions.<locals>.<listcomp>c                 S   r@   )lims_resultsr5   )r8   r9   lr:   r:   r;   rC   @   rD   c                 S   r@   )equipment_readingsr5   )r8   r9   eqr:   r:   r;   rC   A   rD   c                 S   r@   )maintenance_recordsr5   )r8   r9   mr:   r:   r;   rC   B   rD   c                 S   r@   )
sop_limitsr5   )r8   r9   sr:   r:   r;   rC   C   rD   c                 s   s,    | ]}| d i  dr| d V  qdS )deviation_detailsdeviation_idNr5   r7   r:   r:   r;   r<   E   s    )	r4   r>   r?   rA   rE   rG   rI   rK   rM   z[FLATTEN] params:rA    lims:rE    equipment:rG    maintenance:rI   z sop:rK   z deviation:rM   )r6   nextprintlenbool)r0   r2   flatr:   r:   r;   flatten_extractions5   s0    



rW   /c                   C   s   ddiS )NstatuszFyndo API runningr:   r:   r:   r:   r;   rootO   s   rZ   z/auth/registerpayloadc                    s   |  dd }|  dd  }|  dd}|s!tddd|s)tddd|s1tdd	dt|d
k r=tdddt|||}d|d |d |d |d ddS )z?
    Register new user
    Body: { name, email, password }
    name emailpassword  zName is required.status_codedetailEmail is required.Password is required.   z'Password must be at least 6 characters.z?Registration successful. Please login to get your access token.id
created_atrg   r\   r^   rh   )messageuser)r6   striplowerr   rT   r   )r[   r\   r^   r_   rk   r:   r:   r;   registerX   s(   rn   z/auth/loginc                    s   |  dd  }|  dd}|stddd|s!tdddt|}|s-tdd	dt||d
 s:tdd	dt|d |d |d }d|d |d |d |d d|ddS )za
    Login with email + password
    Body: { email, password }
    Returns: JWT access token
    r^   r]   r_   r`   rd   ra   re   i  zInvalid email or password.password_hashrg   r\   zLogin successful.rh   ri   Bearer)rj   rk   access_token
token_type)r6   rl   rm   r   r   r   r   )r[   r^   r_   rk   tokenr:   r:   r;   loginx   s,   rt   z/auth/merequestc                    s   t | }|ddS )zS
    Get current logged in user info
    Header: Authorization: Bearer <token>
    zToken valid)user_idrj   )r   )ru   rv   r:   r:   r;   get_me   s
   rw   z/upload.r2   c           	   
      s  t | }td|  t  g }|D ]1}t|j }t|d}t|j| W d    n1 s1w   Y  |	|jt
j|dd qt|d d d d d d d d tt }td| trzt||d W n ty } ztd|  W Y d }~nd }~ww ||t| d	d
S )Nz[UPLOAD] user_id=wbuploaded)filenamesizerY   )uploaded_filesclassificationsr0   validate
exceptionsrcacapareportchat_idr   z[DB] upload save error: z& files saved. Call /classify-all next.)ry   r   rj   )r   rS   r   
UPLOAD_DIRrz   openshutilcopyfileobjfileappendospathgetsizer   struuiduuid4r   
DB_ENABLEDr    	ExceptionrT   )	ru   r2   rv   ry   r   	file_pathfr   r9   r:   r:   r;   upload_files   sL   



r   z/sessionc                  C   s   t  } | s
dddS d| dt| dg t| dt| dt| dt| d	t| d
t| dt| dt| dddS )NemptyzNo files uploaded yet)rY   rj   activer   r|   r}   r0   r~   r   r   r   r   )uploadclassifyextractr~   r   r   r   r   )rY   r   r|   steps_completed)r   r6   rT   rU   )sessionr:   r:   r;   get_session   s    
r   c                   C   s   t   ddiS )NrY   cleared)r   r:   r:   r:   r;   delete_session   s   r   z/rca-confirmc              
      s   t | }t }|stddd|d}|stdddd|dt d|d	|d
d|d< td| trvz |d}|r[t	||d|d d |d	|d
d W n t
yu } ztd|  W Y d }~nd }~ww d|d dS )Nr`   No session found.ra   r   z!No RCA found. Run /rca-all first.Tconfirmed_by%Y-%m-%d %H:%M:%Scause_idrole)	confirmedr   confirmed_atr   r   human_confirmationr   r   )r   r   r   r   z[DB] rca confirm update error: r   )rY   r   )r   r   r   r6   r   nowstrftimer   r   r$   r   rS   )ru   r[   rv   r   r   r   r9   r:   r:   r;   rca_confirm   s>   




r   z/capa-approvec              
      s   t | }t }|stddd|d}|stdddd|dt d|d	d
d|d< td| trpz|d}|rUt	||d|d d |d	d
d W n t
yo } ztd|  W Y d }~nd }~ww d|d dS )Nr`   r   ra   r   z#No CAPA found. Run /capa-all first.Approvedapproved_byr   send_for_executionF)rY   r   approved_atr   plan_approvalr   r   )r   r   r   z [DB] capa approve update error: approved)rY   r   )r   r   r   r6   r   r   r   r   r   r&   r   rS   )ru   r[   rv   r   r   r   r9   r:   r:   r;   capa_approve!  s:   






r   z/reports/download/{filename}rz   c                    s<   t |  }| stdddtt|d| dd|  idS )N  zReport not foundra   zapplication/pdfzContent-Dispositionzattachment; filename=)r   
media_typerz   headers)REPORTS_DIRexistsr   r   r   rz   r   r:   r:   r;   download_reportF  s   r   z/reports/listc                 C   s\   t | }g }t D ]}| r)|jdkr)||jtj	|| d|j d q
d|iS )Nz.pdfz/reports/download/)rz   r{   download_urlr.   )
r   r   iterdiris_filesuffixr   r\   r   r   r   )ru   base_urlr.   r   r:   r:   r;   list_reportsS  s   
r   z/agent-investigatec           	   
      s  t | }t }|stddd|dg }|stdddt| }t||I d H }td|d td|d td|d td	|d	 td
|d
 trzt|d}|sttt	
 }td| t||d td|  |r|di dog i i |di d}t|g i i |di d t||di  t||di  t||d	i  t||d
i  td|  W |S W |S  ty } ztd|  W Y d }~|S d }~ww |S )Nr`   &No files uploaded. Call /upload first.ra   r|   zNo files found in session.r~   r   r   r   r   r   r   u'   [DB] Created upload record — chat_id=r4   r}   readiness_summaryextraction_summaryr~   u)   [DB] Agent all results saved — chat_id=z[DB] Agent save error: )r   r   r   r6   r   r   r   r   r   r   r   r    rS   r!   r"   r#   r%   r'   r   )	ru   rv   r   r|   r   resultr   three_boom_datar9   r:   r:   r;   agent_investigatef  sb   



r   z/agent/validatec                    s  t | }td|  	 t }|stddd|dg }dd |D }tdt| d	 d
d |D }dd |D }ttj| tj| I d H \}}t|}t|}|t|d}	tdt| d |D ]}
td|
d d|
d d|
d d|
d  qitdt| d |D ]0}td|d dt|dg  dt|dg  dt|dg  dt|dg  
 qt	|	}t
d| t
d |	 t
d!| g d"}td#d$ |D   fd%d&|D }t fd'd$|D }t|| }td(d$ |D }t|t| d) t|| d*t| |||d+kr%d,nd-|d.}|d/|d0|dp9g |dp@g |dpGg |dpNg |d1pUg |d2p\i d3}td4 t|I d H }t
d5| td6d$ |d7g D }td8|  ttd9d$ |dg D }ttd:d$ |dg D }td;d$ |d7g D }g }|D ]I}|dd<d d= }|dr|d>| d? |dr|d@| d? |dr|dA| d? |dr|dB| d? qt|}| d*| t|dg t|dg t|dg t|dg t|d1g ||||d dC dD
}trfz|dE}|rJt|||||dF W n tye } ztdG|  W Y d }~nd }~ww d5dH||||dIS )JNz[AGENT/VALIDATE] user_id=r`   r   ra   r|   c                 S   s   g | ]}t |d   qS )rz   )r   r8   r   r:   r:   r;   rC     s    z'agent_step_validate.<locals>.<listcomp>z1[AGENT/VALIDATE] Starting classify + extract for z	 files...c                 S      g | ]}t |qS r:   r   r8   fpr:   r:   r;   rC         c                 S   r   r:   r   r   r:   r:   r;   rC     r   )r2   total_filesz[AGENT/VALIDATE] Classified z filesz  -> rz   z | document_type
confidencez% | verification_statusz[AGENT/VALIDATE] Extracted z
 | params:rA   rO   rE   rP   rG   rQ   rI   r}   r0   rV   )BMRSOPEQPLIMSMAINTDEVc                 s   s    | ]}| d V  qdS )document_type_codeNr5   r8   cr:   r:   r;   r<     s    z&agent_step_validate.<locals>.<genexpr>c                    s   i | ]}|| v rd ndqS )FOUNDMISSINGr:   r8   codefound_codesr:   r;   
<dictcomp>  s    z'agent_step_validate.<locals>.<dictcomp>c                 3   s    | ]	}| v rd V  qdS )   Nr:   r   r   r:   r;   r<         c                 s   s$    | ]}| d ddk rdV  qdS )r   r   K   r   Nr5   r   r:   r:   r;   r<     r=   d   rX   r   READYPARTIAL)progress_to_next_phasefiles_uploadedrequired_types_foundrequired_files_missinglow_confidence_filesready_to_process	checklistr4   r>   rK   rM   )r4   r>   rA   rE   rG   rI   rK   rM   z)[AGENT/VALIDATE] Validating parameters...r~   c                 s   "    | ]}| d dkrdV  qdS )validation_status	DEVIATIONr   Nr5   r8   rr:   r:   r;   r<         validation_rowsu,   [AGENT/VALIDATE] Done — deviations found: c                 s   r3   )operator_idNr5   r8   rH   r:   r:   r;   r<          
c                 s   r3   )equipment_idNr5   r   r:   r:   r;   r<     r   c                 s   s"    | ]}| d dv rdV  qdS )r   )r   zNeeds Reviewr   Nr5   r   r:   r:   r;   r<     r   r]      zChecking calibration logs for z...z Cross-referencing LIMS data for z+Awaiting equipment maintenance records for zMatching batch parameters for    )
files_processedparameters_extractedlims_results_extractedequipment_readings_extractedmaintenance_records_extractedsop_limits_extractedoperators_foundequipment_foundvalidation_issues_foundactivity_logr   r   z [DB] agent/validate save error: /agent/exceptions)step	next_stepr}   r   r   r~   )r   rS   r   r   r6   rT   asynciogatherlistrW   r   setsumintr   r   r   r!   r   )ru   rv   r   r|   
file_pathsclassify_tasksextract_tasksr}   r0   combinedr   r9   rV   requiredr   types_foundmissinglow_confr   	safe_flatvalidate_result
deviationsr   r   issuesr  fnamenr   r   r:   r   r;   agent_step_validate  s   :^











r  r  c              
      st  t | }	 t }|stddd|d}|s.|d}|s%tdddt|}td| |d}t|}|r`dd	 |d
g D }||d< |d|d< |d|d< tdt| d t	|I d H }i }	|dg D ]}
t
|
ddd d }|
d}|r|r||	|< qo|dg D ]&}|dst
|ddd d }|	|p|	rt|	 d nd |d< qi }|dg D ]0}t
|dd   t
|dd }t
|dd } rd|v sd|v rd| < q|dg D ]*}t
|dd  t fd d!| D d }|r!||d"< |dkr!d#|d$< q|d%i }|d&|}|d'p8|d"}|dkrV|d(rJd|d( d)< |d*rVd|d* d+< td,d! |dg D }td-d! |dg D }|d(r||d( d< ||d( d.< td| trz|d/}|rt|| W n ty } ztd0|  W Y d }~nd }~ww dd1|d2S )3Nr`   z%No session found. Call /upload first.ra   rV   r0   .No extracted data. Call /agent/validate first.r~   c                 S      g | ]}| d dkr|qS r   r   r5   r   r:   r:   r;   rC   W      z)agent_step_exceptions.<locals>.<listcomp>r   validated_deviation_rowscompliance_scorealcoa_checklistz[AGENT/EXCEPTIONS] Passing z, validated deviation rows from previous steprG   	timestampr]      r   r   operatorr   rK   parameter_namecriticalityaction_if_exceededcriticalzstop immediatelyCritical	parameterc                 3   s(    | ]\}}| v s |v r|V  qd S )Nr:   )r8   kvparamr:   r;   r<   x     & z(agent_step_exceptions.<locals>.<genexpr>severityTis_critical_compliancerM   deviation_recordSeveritysummary
risk_level
batch_risklevelc                 s   r   )r0  r)  r   Nr5   r7   r:   r:   r;   r<          c                 s   r   )r0  Majorr   Nr5   r7   r:   r:   r;   r<     r8  majorr   z"[DB] agent/exceptions save error: 
/agent/rca)r  r  r   )r   r   r   r6   rW   r   dictrS   rT   r   r   r  valuesrm   rl   rR   itemsr
  r   r"   r   )ru   rv   r   rV   r0   r  enriched_flatdeviation_rowsr   op_maprH   tsopexsop_criticalitysopr&  actionmatched	deviation
dev_recordreal_severityr(  r:  r   r9   r:   r-  r;   agent_step_exceptions=  s   





&




rL  r;  c                     s  t | }	 t }|stddd|d}|s.|d}|s%tdddt|}td| |d}|d}|s@tdd	d|d
g }dd |D }dd |dg D }	||||	d d |rg|dnd tdd |poi dg D |r}|dnd dd}
t|
I d H }i }|dg D ]}t|ddd d }|d}|r|r|||< q|di dg }dd |D }t	dd |D d}t	dd |D d}t	dd |D d}t	d d |D d}t	d!d |D d}|d"i d#g D ]>}|ds$t|ddd d }||p!|r t
| d$ nd |d< t|d%p,dd&r9|p6||d%< q|d'g D ]H}|dsit|ddd d }||pf|ret
| d$ nd |d< t|d(d}|r|d&s|d)r|p||d(< q@|d d* D ]:}d+d |d'g D }|d,|vr|d'g |pd-|d,d|d.dt|d/d|d0dd1 q|d2rd3d |||||fD |d2 d4< |d5i }|d6|}|d7d8ks|d9d8krd8|d2 d:< |d;d u ri |d;< t||d; d<< td=| trHz|d>}|r,t|| W n tyG } ztd?|  W Y d }~nd }~ww d=d@|dAS )BNr`   r   ra   rV   r0   r  r~   r   z2No exceptions found. Call /agent/exceptions first.rI   c                 S   sX   g | ](}d t |dd v s(dt |dd v s(dt |dd v r|qS )overduerY   r]   driftobservationurgent)r   r6   rm   r8   rJ   r:   r:   r;   rC     s    z"agent_step_rca.<locals>.<listcomp>c                 S   s(   g | ]}t |d d dv r|qS )rY   r]   )ALERTr   )r   r6   upperr7   r:   r:   r;   rC     s    rG      r   c                 S   r  r  r5   r   r:   r:   r;   rC     r  r   r!  )r   deviation_countr!  )extracted_datar   key_maintenance_recordsalert_equipment_readingsvalidation_summaryr"  r]   r#  r   r2   c                 S   s"   g | ]}| d r| d dqS )rz   r]   r5   r   r:   r:   r;   rC     rD   c                 s       | ]	}d |v r|V  qdS )	EquipmentNr:   r   r:   r:   r;   r<     r   z!agent_step_rca.<locals>.<genexpr>c                 s   rZ  )MaintenanceNr:   r   r:   r:   r;   r<     r   c                 s   rZ  )BatchNr:   r   r:   r:   r;   r<     r   c                 s   rZ  )r   Nr:   r   r:   r:   r;   r<     r   c                 s   rZ  )	DeviationNr:   r   r:   r:   r;   r<     r   cluster_contextvertical_timeliner   source_filefile_supporting_evidencerz   temperature   c                 S   s   g | ]}| d qS )	referencer5   r7   r:   r:   r;   rC     s    work_order_idzMaintenance_Log.csvrO  maintenance_dateperformed_by)rz   rf  rc   r"  r   investigation_summaryc                 S   s   g | ]}|r|qS r:   r:   r   r:   r:   r;   rC     s
    supporting_evidence_refsrM   r2  r3  r)  r0  r5  analysis_metadatadata_sources_countr   r   z[DB] agent/rca save error: /agent/capa)r  r  r   )r   r   r   r6   rW   r   rT   r   r   rR   r  r=  
startswith
setdefaultr   r   r#   r   rS   ) ru   rv   r   rV   r0   r  r   maintenancekey_maintenancealert_readings	rca_inputr   rA  rH   rB  rC  
real_filesreal_filenames
equip_file
maint_fileprimary_file	lims_filedev_fileitemevr  rJ   existingrI  rJ  r   r9   r:   r:   r;   agent_step_rca  s   






**


 


r  rn  c              
      sx  t | }	 t }|stddd|d}|s)|d}|s%tdddt|}|d}|d}|r7|s=tdd	d|||d
}t|I d H }|di }	|	d|	}
|
dp_|
d}|
dpi|
d}|rp||d< |r||d< |dr||d d< d|d d< td| trz|d}|rt|| W n t	y } zt
d|  W Y d }~nd }~ww dd|dS )Nr`   r   ra   rV   r0   r  r   r   z+Run /agent/exceptions and /agent/rca first.)rV  r   r   rM   r2  Deviation_IDrN   r3  r0  impact_assessmentpre_capa_riskLowresidual_riskr   r   z[DB] agent/capa save error: /agent/report)r  r  r   )r   r   r   r6   rW   r   r   r   r%   r   rS   )ru   rv   r   rV   r0   r   r   
capa_inputr   rI  rJ  real_dev_idrK  r   r9   r:   r:   r;   agent_step_capa  s^   







r  r  c           -   
      s  t | }	 t }|stddd|d}|s)|d}|s%tdddt|}|d}|d}|d	}|r>|r>|sDtdd
dt| }||||d}	t|	|I d H }
|di }|d|}|dpl|d}|rz|
drz||
d d< i }|dg D ]}t|ddd d }|d}|r|r|||< q|rt|	 d nd }|r|
di dr|
d d }dD ]}|
|d| }q||
d d< dd l}|dg }tdd |D d }|r|dd}t|d d}|d!|}|rd"|d# d$nd }t|d%i d&d}|d'||j}|r'|d# d(nd }|
di d)r|
d d) }t|d*d} d+D ]	}!| 
|!|} qC|rW|d,|| } |r||d-d.| | } t|d/d}"d0D ]	}!|"
|!|}"qn|"|d/< | |d*< |
di d1rd0D ]}!|
d d1 
|!||
d d1< q|d2i }#|#d3d}$|$r|
drd4D ]F}%|
d |%}&t|&tr|& D ]\}'}(t|(trd5|(v r|d6|$|(|&|'< qȐqt|&trd5|&v r|d6|$|&|
d |%< q|
di d7r|
d d7 D ]})|)d8d9v rd:|)d8< q|
d;r6|
d; D ]}*|*d8d<kr4d=|*d8< q&td>|
 trhz|d?}+|+rLt|+|
 W n tyg }, ztd@|,  W Y d },~,nd },~,ww d>d |
|
dA|
dBdCdDS )ENr`   r   ra   rV   r0   r  r   r   r   z#Run all previous agent steps first.)rV  r   r   r   rM   r2  r3  r0  report_metadatarG   r"  r]   r#  r   r   sectionsdeviation_description)zoperator ID was not recordedz The operator ID was not recordedzoperator not recordedzoperator ID: rI   c                 s   s(    | ]}d t |ddv r|V  qdS )0401rg  r]   N)r   r6   rQ  r:   r:   r;   r<     r/  z$agent_step_report.<locals>.<genexpr>rg  zWO-2024-0401rO  z[+](\d+\.?\d+|\d+)+r   u   °CEquipment_DetailsCalibration_Statusz(\d+)\s*days?\s*past\s*duez daysroot_cause_analysisprobable_cause)WO-2024-0456WO-2024-045zWO-2024-046u   [+]\d+\.?\d*°Czoverdue by \d+ dayszoverdue by 
conclusion)r  r  capa_summaryLinked_RecordsOOS_Reference)r  r  executive_summaryr  OOSz0OOS[-\s]?\d{4}[-\s]?\d{4}|OOS[-\s]?\d{4}|OOS-\d+regulatory_compliancerY   )zNon-CompliantfailzUnder Reviewcompliance_checklistr  warnr   r   z[DB] agent/report save error: r   pdf_filename	completed)r  r  r   r   r  rY   )r   r   r   r6   rW   r   r   r   r  r=  replacererR   searchgroup
IGNORECASEsub
isinstancer<  r>  r   r   r'   r   rS   )-ru   rv   r   rV   r0   r   r   r   r   report_inputr   rI  rJ  rK  rA  rH   rB  rC  first_opdescphraser  rI   	breakdownwo_idobsdrift_match
real_drift
cal_statusoverdue_matchreal_overduerca_secprobablewrong_wor  linkedreal_oossection_keysecr+  r,  regr|  r   r9   r:   r:   r;   agent_step_reportZ  s   





 


r  z/agent/validate/{chat_id}r   c           
   
      s   t |}tstdddzFdd l}dd l}dd l}||d}| }|	d| f |
 }|  |  |sAtddd| d	d
|d |d |d |d dW S  ty[     tyn }	 ztdt|	dd }	~	ww )N  Database not connected.ra   r   DATABASE_URLzjSELECT validate, classifications, readiness_summary, extraction_summary FROM three_boom WHERE chat_id = %sr   z(No validate data found for this chat_id.r~   r  r   re     )r   r  r  r~   r}   r   r     )r   r   r   psycopg2jsonr   connectgetenvcursorexecutefetchonecloser   r   )
r   ru   rv   r  r  r   conncurrowr9   r:   r:   r;   get_agent_validate  s>   	r  z/agent/exceptions/{chat_id}c           	   
         t |}tstdddz8dd l}dd l}||d}| }|d| f |	 }|
  |
  |s=tddd| d	|d d
W S  tyM     ty` } ztdt|dd }~ww )Nr  r  ra   r   r  2SELECT response FROM exceptions WHERE chat_id = %sr   z*No exceptions data found for this chat_id.r   )r   r  r   r  r   r   r   r  r   r  r  r  r  r  r  r   r   	r   ru   rv   r  r   r  r  r  r9   r:   r:   r;   get_agent_exceptions  *   r  z/agent/rca/{chat_id}c           	   
      r  )Nr  r  ra   r   r  +SELECT response FROM rca WHERE chat_id = %sr   z#No RCA data found for this chat_id.r   )r   r  r   r  r  r  r:   r:   r;   get_agent_rca  r  r  z/agent/capa/{chat_id}c           	   
      r  )Nr  r  ra   r   r  ,SELECT response FROM capa WHERE chat_id = %sr   z$No CAPA data found for this chat_id.r   )r   r  r   r  r  r  r:   r:   r;   get_agent_capa0  r  r  z/agent/report/{chat_id}c           	   
      s   t |}tstdddz>dd l}dd l}||d}| }|d| f |	 }|
  |
  |s=tddd| d	|d |d
 |d dW S  tyS     tyf } ztdt|dd }~ww )Nr  r  ra   r   r  KSELECT response, pdf_filename, download_url FROM reports WHERE chat_id = %sr   z!No report found for this chat_id.r   r   re  )r   r  r   r  r   r  r  r  r:   r:   r;   get_agent_reportF  s4   r  z/agent/all/{chat_id}c              
      s  t |}tstdddzddl}ddl}||d}| }|d| f |	 }|r3|d nd}|d| f |	 }|rF|d nd}	|d	| f |	 }|rY|d nd}
|d
| f |	 }|rl|d nd}|d| f |	 }|r|d nd}|r|d nd}|r|d nd}|
  |
  t||	|
||gstddd| ||	|
||||dW S  ty     ty } ztdt|dd}~ww )zj
    Get all steps for a chat_id in one call
    Returns: validate + exceptions + rca + capa + report
    r  r  ra   r   Nr  z2SELECT validate FROM three_boom WHERE chat_id = %sr  r  r  r  r   re  r   zNo data found for this chat_id.)r   r~   r   r   r   r   r  r   r  )r   r   r   r  r   r  r  r  r  r  r  anyr   r   )r   ru   rv   r  r   r  r  r  r~   r   r   r   r   r  r   r9   r:   r:   r;   get_agent_allb  sX   
r  z/investigationsc              
      s   t | }tstdddz2ddl}ddl}||d}| }|d |	 }|
  |
  t|dd	 |D d
W S  tyS } ztdt|dd}~ww )z\
    Get list of all investigations
    Returns: chat_id, batch_id, created_at for each
    r  r  ra   r   Nr  an  
            SELECT u.chat_id, u.batch_id, u.total_files, u.created_at,
                   e.risk_level, e.total_exceptions,
                   r.pdf_filename, r.download_url
            FROM uploads u
            LEFT JOIN exceptions e ON e.chat_id = u.chat_id
            LEFT JOIN reports r ON r.chat_id = u.chat_id
            ORDER BY u.created_at DESC
        c                 S   sF   g | ]}|d  |d |d t |d |d |d |d |d dqS )	r   r   re  r     rT  rf      )r   r4   r   rh   r5  total_exceptionsr  r   )r   )r8   r  r:   r:   r;   rC     s    
z*get_all_investigations.<locals>.<listcomp>)totalinvestigationsr  )r   r   r   r  r   r  r  r  r  fetchallr  rT   r   r   )ru   rv   r  r   r  r  rowsr9   r:   r:   r;   get_all_investigations  s*   
	r  z/filesc                  C   sD   g } t  D ]}| r| |jtj||j	 d qd| iS )N)rz   r{   	extensionr2   )
r   r   r   r   r\   r   r   r   r   rm   )r2   r   r:   r:   r;   
list_files  s   
r  z/files/{filename}c                 C   s.   t |  }| stdddt| d| iS )Nr   zFile not foundra   deleted)r   r   r   r   remover   r:   r:   r;   delete_file  s
   
r  )afastapir   r   r   r   r   fastapi.middleware.corsr   fastapi.responsesr   r   r   r  pathlibr	   typingr
   r   r   r   r   r   r   r~   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   agentr   authr   r   r   r   r   databaser    r!   r"   r#   r$   r%   r&   r'   r   rS   r   r9   appadd_middlewarer   r   mkdirr<  rW   r6   rZ   postrn   rt   rw   r   r   deleter   r   r   r   r   r   r   r  rL  r  r  r  r  r  r  r  r  r  r  r  r  r:   r:   r:   r;   <module>   s    ,(

# ,

"$B b{= 	 >0
