o
    1kj%c  ã                   @   s  d dl mZmZmZmZmZ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mZmZmZmZmZ d dlZd dlZd dlZd dl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$ d dl%m&Z&m'Z'm(Z(m)Z)m*Z*m+Z+m,Z,m-Z-m.Z.m/Z/ d dl0m1Z1m2Z2 d dl3m4Z4 d dl5m6Z6m7Z7m8Z8 dZ9e :dd¡ ;¡ dkZ<e :dd¡ ;¡ dkZ=e :dd¡ ;¡ dkZ>e1ƒ Z?e-e9 @dd¡e<dd�ZAe2eAd�ZBdd„ ZCeƒ ZDeD Ee¡ eDjEeedd� e eFƒjGZHeHd ZIeHd ZJeHd  ZKeeLeIƒd!�ZMe N¡ ZOd"ddd#œZPd$d%„ ZQd~d&eLd'eLd(eLfd)d*„ZRd+eSfd,d-„ZTd.eUd/eLfd0d1„ZVd.eUd/eLd2eLd+eSfd3d4„ZWd5e d(eLfd6d7„ZXG d8d9„ d9e?ƒZYd:eLd+eeL fd;d<„ZZeDj[d=ed>�			dd?ed@eeL dAeeL dBeeL fdCdD„ƒZ\eD ]d=¡edEƒedEƒedƒfd?edBeLdFeLd@eeL fdGdH„ƒZ^eD ]dI¡d?efdJdK„ƒZ_eDj[dLed>�		d€d?ed'eeL dMeeL fdNdO„ƒZ`eD ]dL¡edEƒedEƒedEƒfd?edPeLdQeLdReLfdSdT„ƒZaeDj[dUed>�d?efdVdW„ƒZbh dX£ZceDj[dYed>�	Z			d�d?ed[eUd\eeL d]eeL d^eeL f
d_d`„ƒZdeDj[daed>�d?efdbdc„ƒZeeD [dd¡dedf„ ƒZfeD ]dg¡dhdi„ ƒZgeD ]dj¡edEƒfdkedlefdmdn„ƒZheD ]do¡edEƒedƒedƒfdked.eUd/eeL d2eeL fdpdq„ƒZieDj[dred>�d?ed.eUfdsdt„ƒZje=�rbe?jkjleAd� duamdvdw„ Zne>�roenƒ  ndameodxk�r‹d dlpZpepjqdydzd{deLeHƒgd|gd}� dS dS )‚é    )ÚFastAPIÚRequestÚFormÚFileÚ
UploadFileÚBackgroundTasks)ÚHTMLResponseÚRedirectResponseÚJSONResponse)ÚJinja2Templates)ÚSessionMiddleware)ÚAdminAuthMiddlewareÚSESSION_SECRETÚchange_admin_passwordÚcheck_admin_loginÚis_authenticatedÚsafe_next_pathÚverify_adminN)ÚListÚDictÚOptional)ÚPath)Ú	urlencode)Úuuid4)
ÚColumnÚIntegerÚStringÚTextÚBooleanÚFloatÚDateTimeÚcreate_engineÚtextÚinspect)Údeclarative_baseÚsessionmaker)Úfunc)Úclassify_new_imap_emailsÚlearn_from_feedbackÚprocess_uploaded_spam_csvzjpostgresql+asyncpg://postgres:p4python@postgres.cqf3qroocfwm.us-east-1.rds.amazonaws.com:5432/email_filterÚSQL_ECHOÚfalseÚtrueÚAUTO_CREATE_TABLESÚENSURE_FEEDBACK_COLUMNz+asyncpgÚ T)ÚechoÚpool_pre_ping)Úbindc               
   C   sŒ   t ƒ } z=z|  t¡ tj d ¡¡ tj ¡ ¡ 	¡ }|pg W W |  
¡  S  ty@ } ztd|ƒ g W  Y d }~W |  
¡  S d }~ww |  
¡  w )NzDB error while fetching emails:)ÚSessionLocalÚqueryÚ	EmailSpamÚfilterÚimap_uidÚisnotÚorder_byÚreceived_atÚdescÚallÚcloseÚ	ExceptionÚprint)ÚdbÚemailsÚe© rC   ú/home/esfera/manjet/main.pyÚget_all_emails_from_db+   s    ü

ü

€ü
rE   Úmanjet_session)Ú
secret_keyÚsession_cookieÚ	templatesÚuploadsÚmodels)Ú	directoryÚidle©ÚstateÚmessageÚfilenamec                  C   sT   t � t ¡ } W d   ƒ n1 sw   Y  d|  d¡pdv r(|  ddddœ¡ | S )NÚ	RefreshedrP   r/   rM   rN   )ÚTRAINING_STATUS_LOCKÚTRAINING_STATUSÚcopyÚgetÚupdate)ÚstatusrC   rC   rD   Úget_training_statusP   s   
ÿrY   rO   rP   rQ   c                 C   s<   t � t | ||dœ¡ W d   ƒ d S 1 sw   Y  d S )NrN   )rS   rT   rW   rN   rC   rC   rD   Úset_training_statusX   s   
ý"ÿrZ   Úreturnc                  C   s2   t ƒ } z|  tdƒ¡ ¡ d uW |  ¡  S |  ¡  w )NzÀ
                SELECT 1
                FROM email_filter
                WHERE imap_uid IS NOT NULL
                  AND model_accuracy IS NOT NULL
                LIMIT 1
                )r3   Úexecuter"   Úfirstr=   )r@   rC   rC   rD   Úfilter_results_exista   s   ÿÿ
ör^   Úemail_idÚclassificationc              
   C   sV   zt | |ttd� td| ƒ W d S  ty* } ztd| d|ƒ W Y d }~d S d }~ww )N)r_   Úfeedback_labelÚsession_factoryÚ	model_dirz)Updated model from feedback for email_id:z-Background model learning failed for email_idú:)r(   r3   Ú	MODEL_DIRr?   r>   )r_   r`   rB   rC   rC   rD   Ú#run_feedback_learning_in_backgrounds   s   ü€ÿrf   Úfeedbackc                 C   s>  t ƒ }z–z(| tdƒ||| dœ¡ ¡ }|s!| ¡  W W | ¡  dS | ¡  W W | ¡  dS  ty™ } za| ¡  td|ƒ z1| tdƒ|| dœ¡ ¡ }|s^| ¡  W W Y d }~W | ¡  dS | ¡  W W Y d }~W | ¡  dS  ty” } z| ¡  td|ƒ W Y d }~W Y d }~W | ¡  dS d }~ww d }~ww | ¡  w )	Na`  
                UPDATE email_filter
                SET predicted_label = :classification,
                    feedback_label = :classification,
                    feedback = :feedback,
                    is_reviewed = TRUE
                WHERE id = :email_id
                  AND imap_uid IS NOT NULL
                RETURNING id
                )r`   rg   r_   FTz1Full feedback save failed, trying minimal update:a  
                    UPDATE email_filter
                    SET predicted_label = :classification,
                        is_reviewed = TRUE
                    WHERE id = :email_id
                      AND imap_uid IS NOT NULL
                    RETURNING id
                    )r`   r_   zMinimal feedback save failed:)	r3   r\   r"   r]   Úrollbackr=   Úcommitr>   r?   )r_   r`   rg   r@   ÚrowrB   Úfallback_errorrC   rC   rD   Úsave_classification_feedback€   sf   ÿýóî!àâ
ÿþõñùû
€û€ç
rl   Úcsv_pathc                 C   sÀ   z?t dd|› d�|ƒ t| ttd�}d|› d|j› d|j› d|j› d	|jd
 d›d|j› d|j	› d|j
› d�}t d||ƒ W d S  ty_ } ztd|ƒ t dt|ƒ|ƒ W Y d }~d S d }~ww )NÚrunningúTraining model from ú...)rm   rb   rc   zTraining complete for z. Trained on z usable rows from z CSV rows and reclassified z existing emails. Accuracy: éd   z.1fz	%. Spam: z, Ham: z, Undecided: Ú.Ú	completedzCSV upload/training failed:Úerror)rZ   r)   r3   re   Útraining_rowsÚ
total_rowsÚreclassified_rowsÚaccuracyÚ
spam_countÚ	ham_countÚundecided_countr>   r?   Ústr)rm   rQ   ÚresultrP   rB   rC   rC   rD   Ú$train_uploaded_dataset_in_backgroundÀ   s6   ýÿÿ
þýýýÿ
€þr~   c                   @   s¸   e Zd ZdZeeddd�Zeedd�Zeedd�Z	ee
dd�Zeedd�Zeedd�Zeedd�Zee
dd�Zeedd�Zee
dd�Zeedd�Zeedd�Zeedd�e ¡ d�Zd	S )
r5   Úemail_filterT)Úprimary_keyÚindexF)Únullable)Údefault)Útimezone)Úserver_defaultN)Ú__name__Ú
__module__Ú__qualname__Ú__tablename__r   r   Úidr   Úsender_emailÚsubjectr   Úbodyr   Úspam_probabilityÚpredicted_labelra   rg   r   Úis_reviewedÚemail_summaryÚmodel_accuracyr7   r    r&   Únowr:   rC   rC   rC   rD   r5   Ô   s    r5   rŒ   c                 C   sD   g }|   ¡ }g d¢}|D ]}t ||tj¡r| d|› d�¡ q|S )z*Check email subject for spam-like patterns)zF\b(win|winner|won|prize|free|urgent|limited time|act now|click here)\bz\$[\d,]+z!!!+z"\b(viagra|cialis|pharmacy|pills)\bz(\b(guaranteed|risk-free|no obligation)\bz>\b(click|download|claim|verify)\b.*\b(now|immediately|today)\bz&Subject contains suspicious pattern: 'ú')ÚlowerÚreÚsearchÚ
IGNORECASEÚappend)rŒ   Úspam_patternsÚsubject_lowerÚspam_keywordsÚpatternrC   rC   rD   Úanalyze_email_subjectê   s   	€rž   z/admin/login)Úresponse_classÚrequestÚnextrt   Úusernamec              
   Ã   s8   �t | ƒrtt|ƒdd�S t d| |||pddd dœ¡S )Né/  ©Ústatus_codeúadmin_login.htmlr/   )r    rt   r¡   r¢   ÚpasswordÚerror_field)r   r	   r   rI   ÚTemplateResponse)r    r¡   rt   r¢   rC   rC   rD   Úadmin_login_page  s   €úþrª   .r§   c              	   Ã   s^   �t ||ƒ\}}|rd| jd< tt|ƒdd�S |dkrd}nd}tjd| |||||d	œd
d�S )NTÚis_adminr£   r¤   Úwrong_usernamez>Wrong username. Please check your email address and try again.z!Wrong password. Please try again.r¦   )r    rt   r¨   r¡   r¢   r§   i‘  )r   Úsessionr	   r   rI   r©   )r    r¢   r§   r¡   ÚsuccessÚ
error_codeÚerror_messagerC   rC   rD   Úadmin_login_submit  s&   €
úör±   z/admin/logoutc                 Ã   s   �| j  ¡  tddd�S )Nú/r£   r¤   )r­   Úclearr	   ©r    rC   rC   rD   Úadmin_logout7  s   €
rµ   z/admin/change-passwordÚmessage_typec                 Ã   s   �t  d| ||p	ddœ¡S )Núchange_password.htmlrt   ©r    rP   r¶   ©rI   r©   r¸   rC   rC   rD   Úchange_password_page=  s   €ýþrº   Úcurrent_passwordÚnew_passwordÚconfirm_passwordc                 Ã   sB   �t |||ƒ\}}|rdnd}tjd| ||dœ|rdd�S dd�S )Nr®   rt   r·   r¸   éÈ   i�  r¤   )r   rI   r©   )r    r»   r¼   r½   ÚokÚresult_messager¶   rC   rC   rD   Úchange_password_submitM  s"   €ýýùùrÁ   r²   c                 Ã   s&   �t | ƒrtddd�S t dd| i¡S )Nú/dashboard/spamr£   )Úurlr¥   zlanding.htmlr    )r   r	   rI   r©   r´   rC   rC   rD   Úlanding_pageh  s   €þrÄ   >   ÚhamÚspamÚ	undecidedrÂ   é   Úpager6   Úupload_statusÚupload_messagec                 ƒ   s–  �|rd|v rd }d }d}|d | }dd„ ‰zt ttƒ W n ty5 } ztd|ƒ W Y d }~nd }~ww tƒ }t|ƒ}	tƒ }
z"|
 tj	¡ 
tj d ¡¡ 
tj	 d ¡¡ tj ¡ ¡ ¡ }W |
 ¡  n|
 ¡  w |rut|j	d dƒ}d}nd }d	}|r­‡fd
d„|D ƒ}tdd„ |D ƒƒ}tdd„ |D ƒƒ}tdd„ |D ƒƒ}tdd„ |D ƒƒ}|	| }nd}d}d}d}|p¸d ¡  ¡ ‰ ˆ tvrÃd ‰ ˆ rÉ|sÉd ‰ |}ˆ r×‡ ‡fdd„|D ƒ}|||| … }g }|D ]:}|rìˆ|jƒnd }|ró|jnd }|d u�rt|d dƒnd }| |j|j|j|j|�r|nd||j�pddœ¡ qãt|ƒ}|�r-|| d | nd}|	||||dœ}t d| ||||||||t ƒ ˆ |dœ¡S )NrR   é   rÈ   c                 S   s   | r|   ¡  ¡ S d S ©N)Ústripr•   )ÚlabelrC   rC   rD   Únormalize_label‡  s   z'spam_dashboard.<locals>.normalize_labelz(Could not classify newly fetched emails:rq   TFc                    s   g | ]}ˆ |j ƒ‘qS rC   ©r�   )Ú.0rB   )rÐ   rC   rD   Ú
<listcomp>¨  ó    z"spam_dashboard.<locals>.<listcomp>c                 s   ó   � | ]	}|d krdV  qdS )rÆ   rÈ   NrC   ©rÒ   ÚlrC   rC   rD   Ú	<genexpr>©  ó   € z!spam_dashboard.<locals>.<genexpr>c                 s   rÕ   )rÅ   rÈ   NrC   rÖ   rC   rC   rD   rØ   ª  rÙ   c                 s   rÕ   )rÇ   rÈ   NrC   rÖ   rC   rC   rD   rØ   «  rÙ   c                 s   s   � | ]	}|d u rdV  qd S )NrÈ   rC   rÖ   rC   rC   rD   rØ   ¬  rÙ   r   r/   c                    s   g | ]}ˆ|j ƒˆ kr|‘qS rC   rÑ   )rÒ   Úemail©Úactive_filterrÐ   rC   rD   rÓ   ¼  s
    þÚ
unfiltered)r_   ÚsenderrŒ   ÚsummaryrÏ   Úml_scorerg   )ÚtotalrÆ   rÅ   rÇ   Ú
classifiedzspam_dashboard.html)r    rA   ÚstatsrÉ   Útotal_pagesr’   Úfilter_results_availablerÊ   rË   Útraining_statusrÜ   Úfiltered_total)!r'   r3   re   r>   r?   rE   Úlenr4   r5   r’   r6   r7   r8   r9   rŠ   r;   r]   r=   ÚroundÚsumrÎ   r•   ÚLABEL_FILTERSr�   rŽ   r™   r‹   rŒ   r�   rg   rI   r©   rY   )r    rÉ   r6   rÊ   rË   ÚPER_PAGEÚoffsetrB   Ú
all_emailsÚtotal_emailsr@   Úlatest_accuracy_rowÚmodel_accuracy_percentrå   Ú
all_labelsry   rz   r{   Únot_classified_countÚclassified_countÚdisplay_emailsÚ	db_emailsÚenhanced_emailsrÚ   Údisplay_labelÚ	spam_probrà   rç   rä   rã   rC   rÛ   rD   Úspam_dashboardx  s²   €€ÿ
û
þ

ùûôþrú   z/dashboard/spam/addc                 Ã   s   �t  dd| i¡S )Nzspam_add.htmlr    r¹   r´   rC   rC   rD   Úspam_add_page÷  s
   €þrû   z/dashboard/spam/upload-statusc                   Ã   s   �t tƒ ƒS rÍ   )r
   rY   rC   rC   rC   rD   Úspam_upload_statusÿ  s   €
rü   z/dashboard/spam/clear-analysisc                  Ã   sR  �t ƒ } |  d¡dkrtdddœƒ}td|› �dd�S tƒ }zDz| td	ƒ¡ | ¡  W n0 ty\ } z$| 	¡  t
d
|ƒ tdddœƒ}td|› �dd�W  Y d }~W | ¡  S d }~ww W | ¡  n| ¡  w td td fD ]"}z|jdd� W qp ty’ } zt
d||ƒ W Y d }~qpd }~ww tdddƒ tdddœƒ}td|› �dd�S )NrO   rn   rt   z@Training is still running. Please wait before clearing analysis.©rÊ   rË   ú/dashboard/spam?r£   r¤   zã
                UPDATE email_filter
                SET spam_probability = NULL,
                    predicted_label = NULL,
                    model_accuracy = NULL
                WHERE imap_uid IS NOT NULL
                zCould not clear analysis:z+Could not clear analysis. Please try again.zfinal_model.pklzfinal_vectorizer.pklT)Ú
missing_okzCould not remove model file:rM   r/   r®   zQAnalysis cleared. Emails are still saved; upload a dataset to analyze them again.)rY   rV   r   r	   r3   r\   r"   ri   r>   rh   r?   r=   re   ÚunlinkrZ   )Úcurrent_statusr4   r@   rB   Ú
model_filerC   rC   rD   Úclear_spam_analysis  sT   €þÿÿ
þ
€÷ÿ
€ÿþr  z/dashboard/spam/uploadÚbackground_tasksÚdatasetc           	   
   Ã   s¸  �t ƒ }| d¡dkrtdddœƒ}td|› �dd�S tƒ r.tdd	dœƒ}td|› �dd�S |jp2d
}| ¡  d¡sJtdddœƒ}td|› �dd�S tj	ddd� t
 ddt|ƒj¡}ttƒ j› d|› � }zfz6| d¡�}t |j|¡ W d   ƒ n1 sw   Y  tdd|› d�|ƒ |  t||¡ tdddœƒ}W n( tyÅ } ztd|ƒ tdt|ƒ|ƒ tdt|ƒdœƒ}W Y d }~nd }~ww W |j ¡  n|j ¡  w td|› �dd�S )NrO   rn   rt   z<A dataset is already training. Please wait for it to finish.rý   rþ   r£   r¤   zMAnalysis already exists. Clear analysis before training with another dataset.r/   z.csvzPlease upload a CSV file.T)ÚparentsÚexist_okz[^a-zA-Z0-9_.-]Ú_Úwbro   rp   ÚinfozXUpload received. Training is running in the background and existing emails will be kept.zCSV upload failed:)rY   rV   r   r	   r^   rQ   r•   ÚendswithÚUPLOADS_DIRÚmkdirr–   Úsubr   Únamer   ÚhexÚopenÚshutilÚcopyfileobjÚfilerZ   Úadd_taskr~   r>   r?   r|   r=   )	r  r  r  r4   rQ   Ú	safe_nameÚupload_pathÚbufferrB   rC   rC   rD   Úupload_spam_dataset5  s\   €þþ
þÿþ
þ€ý€r  z/dashboard/spam/classifyc              
   Ã   s  �zf|pd  ¡  ¡ }|pd  ¡ }|dvrtd|› d�dd�W S |s,td|› d�dd�W S td||t|ƒdœƒ t|||ƒsMtd	|ƒ td|› d
�dd�W S td|ƒ |dv r]|  t||¡ td|› d�dd�W S  tyˆ } ztd|ƒ td|› d
�dd�W  Y d }~S d }~ww )Nr/   )rÆ   rÅ   rÇ   z/dashboard/spam/email/z?form_error=1r£   r¤   zPOST /dashboard/spam/classify)r_   r`   Úfeedback_lenz;No row found or could not save classification for email_id:z?db_error=1z+Saved classification/feedback for email_id:>   rÅ   rÆ   z?feedback_saved=1z"Unexpected classify handler error:)	rÎ   r•   r	   r?   rè   rl   r  rf   r>   )r  r_   r`   rg   rB   rC   rC   rD   Úclassify_email�  s>   €þ

ý
þ
 €þr  z /dashboard/spam/email/{email_id}c                 Ã   s  �t ƒ }z| t¡ tj|k¡ tj d ¡¡ ¡ }W | ¡  n| ¡  w |s,t	ddd�S |j
d u}|jp5d ¡  ¡ }|jp>d}tt d|tj¡ƒ}|rN|jnd }|d ur[t|d dƒnd }	|j|jpcd|j||||	|rm|nd	|jprd||j||d
kt|jƒdœ}
t d| |
dœ¡S )NrÂ   r£   r¤   r/   z<[a-z][\s\S]*>rq   rÈ   z
No SubjectrÝ   rÇ   )rŠ   rŒ   rÞ   r�   rß   Úis_htmlrà   rÏ   rg   rŽ   r:   rå   Úshow_classify_formr�   zemail_details.html)r    rÚ   )r3   r4   r5   r6   rŠ   r7   r8   r]   r=   r	   r’   r�   rÎ   r•   r�   Úboolr–   r—   r˜   rŽ   ré   rŒ   r‹   rg   r:   r�   rI   r©   )r    r_   r@   rÚ   rå   r�   r�   r  rù   rà   Úenhanced_emailrC   rC   rD   Úemail_details»  sL   €ü


òþþr   Fc               
   C   sÞ   t ƒ } z6z|  tdƒ¡ |  tdƒ¡ |  ¡  W n ty3 } z|  ¡  td|ƒ W Y d }~nd }~ww W |  ¡  n|  ¡  w ztt	ƒ}dd„ | 
d¡D ƒ}d|v aW d S  tyn } ztd|ƒ d	aW Y d }~d S d }~ww )
NzSET LOCAL lock_timeout = '5s'z?ALTER TABLE email_filter ADD COLUMN IF NOT EXISTS feedback TEXTz!Could not ensure feedback column:c                 S   s   g | ]}|  d ¡‘qS )r  )rV   )rÒ   ÚcrC   rC   rD   rÓ     rÔ   z*ensure_feedback_column.<locals>.<listcomp>r   rg   z+Could not verify feedback column existence:F)r3   r\   r"   ri   r>   rh   r?   r=   r#   ÚengineÚget_columnsÚFEEDBACK_COLUMN_EXISTS)r@   rB   ÚinspÚcolsrC   rC   rD   Úensure_feedback_columnõ  s*   €þ€
€þr'  Ú__main__zmain:appz0.0.0.0i:  Úvenv)ÚhostÚportÚreloadÚreload_dirsÚreload_excludes)r/   r/   )NNN)NN)rÈ   NNN)rÚfastapir   r   r   r   r   r   Úfastapi.responsesr   r	   r
   Úfastapi.templatingr   Ústarlette.middleware.sessionsr   Úauthr   r   r   r   r   r   r   r–   Úosr  Ú	threadingÚtypingr   r   r   Úpathlibr   Úurllib.parser   Úuuidr   Ú
sqlalchemyr   r   r   r   r   r   r    r!   r"   r#   Úsqlalchemy.ormr$   r%   Úsqlalchemy.sqlr&   Úspam_trainingr'   r(   r)   ÚDATABASE_URLÚgetenvr•   r*   r-   r.   ÚBaseÚreplacer"  r3   rE   ÚappÚadd_middlewareÚ__file__ÚparentÚBASE_DIRÚTEMPLATES_DIRr  re   r|   rI   ÚLockrS   rT   rY   rZ   r  r^   Úintrf   rl   r~   r5   rž   rV   rª   Úpostr±   rµ   rº   rÁ   rÄ   rë   rú   rû   rü   r  r  r  r   ÚmetadataÚ
create_allr$  r'  r†   ÚuvicornÚrunrC   rC   rC   rD   Ú<module>   s\    $	0
ý


ý	ÿþý
ü@üÿþýüüÿþýüýÿþýüÿþýüûÿþýüû~

0þÿþYüÿþýü+4

úý