Ë
    {v…jîR  ã                   óà  — 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Zd dlm	Z	m
Z
mZmZmZmZmZmZ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 d d	lmZ d d
lm Z  d dl!Z!d dl"Z"d dl#m$Z$  e%e jL                  jO                  dd«      «      Z(e jL                  jO                  dd«      jS                  d«      Z*e jL                  jO                  d«      Z+e jL                  jO                  dd«      Z,e jL                  jO                  dd«      Z-dZ.e jL                  jO                  dd«      j_                  «       dk(  Z0 ee¬«      Z1 e	d¬«      Z2e1e2jf                  _1        e2ji                  e e«       e2jk                  ee*ddgdg¬«        ed¬«       edd ¬!«      fde6dz  d"e6dz  fd#„Z7 ee7«      fd$„Z8 G d%„ d&e«      Z9 G d'„ d(e«      Z: G d)„ d*e«      Z; G d+„ d,e«      Z< G d-„ d.e«      Z= G d/„ d0e«      Z>d1ed2e6fd3„Z?e2jO                  d4«      d5„ «       Z@e2jƒ                  d6«      e1j…                  e-«      d7ed1ed8e9fd9„«       «       ZCe2jƒ                  d:«      e1j…                  e-«      d7ed1ed8e:fd;„«       «       ZDe2jƒ                  d<«       ed¬«      fd1ede6dz  fd=„«       ZEe2jO                  d>«       ee7«      fd?„«       ZFe2jƒ                  d@«       ee7«      fd8e;fdA„«       ZGe2jO                  dB«       ee8«      fdC„«       ZHe2jƒ                  dB«       ee8«      fd8e<fdD„«       ZIe2j•                  dE«       ee8«      fdFe%fdG„«       ZKe2j™                  dH«       ee8«      fdFe%d8e=fdI„«       ZMe2jƒ                  dJ«       ee8«      fdFe%d8e>fdK„«       ZN e%e jL                  jO                  dLdM«      «      ZO eeOdN¬O«      ZPdPe6dQeQfdR„ZRe2jƒ                  dS ee7«      g¬T«      e1j…                  e,«       edU«       edVdW¬X«       ed«       edY«       ee7«      fd7edZeSe
   d[e6d\eTd]eTf
d^„«       «       ZUe2jO                  d_ ee7«      g¬T«       ed`dadb¬c«      fdde%fde„«       ZVe2jO                  df ee7«      g¬T«      dPe6fdg„«       ZWdPe6dheQfdi„ZXdPe6dje6dhefdk„ZYdlej´                  dmedheSe6   fdn„Z[e2jO                  do ee7«      g¬T«       edp«       ed«       ed«       edqdr¬X«       edada¬s«       ed`dadt¬c«      fdPe6dje6due6dz  dve6dz  dwe6dxe%dye%fdz„«       Z\dmedhe6dz  fd{„Z]e2jO                  d| ee7«      g¬T«       edp«      fdPe6dje6fd}„«       Z^d~Z_dˆdlej´                  dmedeSe6   dz  dhe6fd€„Z`e2jO                  d� ee7«      g¬T«       edp«       ed«       edqdr¬X«       ed«      dfdPe6dje6due6dz  dwe6deSe6   dz  d‚efdƒ„«       Zae2j•                  d„ ee7«      g¬T«      dPe6fd…„«       Zbe2jO                  d†«      d‡„ «       Zcy)‰é    N)ÚThreadPoolExecutor)ÚPath)ÚFastAPIÚ
UploadFileÚFileÚHTTPExceptionÚQueryÚHeaderÚCookieÚBackgroundTasksÚDependsÚRequestÚResponse)ÚCORSMiddleware)ÚFileResponse)Ú	BaseModel)ÚLimiterÚ_rate_limit_exceeded_handler)Úget_remote_address)ÚRateLimitExceeded)Úclean_leadsÚMAX_UPLOAD_MBÚ1024ÚALLOWED_ORIGINSzhttp://localhost:5173Ú,ÚAPI_KEYÚUPLOAD_RATE_LIMITz	10/minuteÚLOGIN_RATE_LIMITÚsessionÚCOOKIE_SECUREÚfalseÚtrue)Úkey_funczLead Cleaner API)ÚtitleTÚ*)Úallow_originsÚallow_credentialsÚallow_methodsÚallow_headers)Údefaultz	X-API-Key)r*   ÚaliasÚ	x_api_keyc                 ó†   — t         r|t         k(  rdddddœS t        j                  | «      }|ri |¥ddi¥S t        dd	¬
«      ‚)zˆAccepts EITHER a valid logged-in session cookie OR the API key (for
    scripts/automation that shouldn't have to log in interactively).úapi-keyÚadminNT)ÚemailÚroleÚidÚvia_api_keyr3   Fé‘  zNot authenticated©Ústatus_codeÚdetail)r   ÚauthÚget_session_userr   )r   r,   Úusers      ú*/var/www/html/lead-cleaner/backend/main.pyÚrequire_authr<   .   sQ   € õ �9¤Ò'Ø"¨G¸4ÐPTÑUÐUÜ× Ñ  Ó)€DÙØ-�$Ð-˜ uÑ-Ð-Ü
 CÐ0CÔ
DÐDó    c                 ó0   — | d   dk7  rt        dd¬«      ‚| S )Nr1   r/   é“  zAdmin access requiredr5   )r   ©r:   s    r;   Úrequire_adminrA   <   s!   € ØˆF�|�wÒÜ¨Ð4KÔLÐLØ€Kr=   c                   ó"   — e Zd ZU eed<   eed<   y)Ú	SetupBodyr0   ÚpasswordN©Ú__name__Ú
__module__Ú__qualname__ÚstrÚ__annotations__© r=   r;   rC   rC   F   ó   … ØƒJØ„Mr=   rC   c                   ó"   — e Zd ZU eed<   eed<   y)Ú	LoginBodyr0   rD   NrE   rK   r=   r;   rN   rN   K   rL   r=   rN   c                   ó"   — e Zd ZU eed<   eed<   y)ÚChangePasswordBodyÚcurrent_passwordÚnew_passwordNrE   rK   r=   r;   rP   rP   P   s   … ØÓØÔr=   rP   c                   ó,   — e Zd ZU eed<   eed<   eed<   y)ÚCreateUserBodyr0   rD   r1   NrE   rK   r=   r;   rT   rT   U   s   … ØƒJØƒMØ
„Ir=   rT   c                   ó   — e Zd ZU eed<   y)ÚUpdateRoleBodyr1   NrE   rK   r=   r;   rV   rV   [   s   … Ø
„Ir=   rV   c                   ó   — e Zd ZU eed<   y)ÚAdminResetPasswordBodyrR   NrE   rK   r=   r;   rX   rX   _   s   … ØÔr=   rX   ÚresponseÚtokenc           	      óf   — | j                  t        |dt        dt        j                  dz  d¬«       y )NTÚlaxi  Ú/)ÚkeyÚvalueÚhttponlyÚsecureÚsamesiteÚmax_ageÚpath)Ú
set_cookieÚCOOKIE_NAMEr    r8   ÚSESSION_TTL_HOURS)rY   rZ   s     r;   Ú_set_session_cookierh   c   s5   € Ø×ÑÜØØÜØÜ×&Ñ&¨Ñ-Øð õ r=   z/api/auth/statusc                  ó.   — dt        j                  «       iS )NÚsetup_complete)r8   Úis_setup_completerK   r=   r;   Úauth_statusrl   o   s   € àœd×4Ñ4Ó6Ð7Ð7r=   z/api/auth/setupÚrequestÚbodyc                 ó¶  — t        j                  «       rt        dd¬«      ‚t        j                  |j                  «      st        dd¬«      ‚t        j
                  |j                  «      }|rt        d|¬«      ‚t        j                  |j                  |j                  d¬«      }t        j                  |d   «      }t        ||«       |d	   |d   |d
   dœS )Nr?   z&Setup already complete. Please log in.r5   é�  ú"Please enter a valid email addressr/   )r1   r0   r2   r1   ©r2   r0   r1   )
r8   rk   r   Úvalidate_emailr0   Úvalidate_password_strengthrD   Úcreate_userÚcreate_sessionrh   )rm   rY   rn   Úpw_errorr:   rZ   s         r;   Ú
auth_setuprx   t   sµ   € ô ×ÑÔÜ¨Ð4\Ô]Ð]Ü×Ñ˜tŸz™zÔ*Ü¨Ð4XÔYÐYÜ×.Ñ.¨t¯}©}Ó=€HÙÜ¨°HÔ=Ð=ä×Ñ˜DŸJ™J¨¯©¸GÔD€DÜ×Ñ  W¡Ó.€EÜ˜ %Ô(Ø�t‘* t¨G¡}¸dÀ6¹lÑKÐKr=   z/api/auth/loginc                 óÚ   — t        j                  |j                  |j                  «      }|st	        dd¬«      ‚t        j
                  |d   «      }t        ||«       |d   |d   |d   dœS )Nr4   zIncorrect email or passwordr5   r0   r2   r1   rr   )r8   Úauthenticater0   rD   r   rv   rh   )rm   rY   rn   r:   rZ   s        r;   Ú
auth_loginr{   ˆ   sf   € ô ×Ñ˜TŸZ™Z¨¯©Ó7€DÙÜ¨Ð4QÔRÐRÜ×Ñ  W¡Ó.€EÜ˜ %Ô(Ø�t‘* t¨G¡}¸dÀ6¹lÑKÐKr=   z/api/auth/logoutc                 ób   — t        j                  |«       | j                  t        d¬«       ddiS )Nr]   )rd   ÚokT)r8   Údelete_sessionÚdelete_cookierf   )rY   r   s     r;   Úauth_logoutr€   “   s-   € ä×Ñ˜Ô Ø×Ñœ;¨SÐÔ1Ø�$ˆ<Ðr=   z/api/auth/mec                 ó8   — | j                  d«      | d   | d   dœS )Nr2   r0   r1   rr   ©Úgetr@   s    r;   Úauth_mer„   š   s   € à—(‘(˜4“.¨4°©=À$ÀvÁ,ÑOÐOr=   z/api/auth/change-passwordc                 ó  — |j                  d«      rt        dd¬«      ‚t        j                  | j                  «      }|rt        d|¬«      ‚t        j
                  |d   | j                  | j                  «      st        dd¬«      ‚d	d
iS )Nr3   r?   z/Log in with your account to change the passwordr5   rp   r0   r4   zCurrent password is incorrectr}   T)rƒ   r   r8   rt   rR   Úchange_own_passwordrQ   )rn   r:   rw   s      r;   Úauth_change_passwordr‡   Ÿ   s|   € à‡x�x�ÔÜ¨Ð4eÔfÐfÜ×.Ñ.¨t×/@Ñ/@ÓA€HÙÜ¨°HÔ=Ð=Ü×#Ñ# D¨¡M°4×3HÑ3HÈ$×J[ÑJ[Ô\Ü¨Ð4SÔTÐTØ�$ˆ<Ðr=   z
/api/usersc                 ó.   — dt        j                  «       iS )NÚusers)r8   Ú
list_users)r/   s    r;   rŠ   rŠ   ¯   s   € à”T—_‘_Ó&Ð'Ð'r=   c                 óÆ  — t        j                  | j                  «      st        dd¬«      ‚t        j                  | j
                  «      }|rt        d|¬«      ‚| j                  t         j                  vrt        dd¬«      ‚	 t        j                  | j                  | j
                  | j                  «      }|S # t        $ r}t        dt        |«      ¬«      ‚d }~ww xY w)Nrp   rq   r5   ú role must be 'admin' or 'member'é™  )r8   rs   r0   r   rt   rD   r1   ÚVALID_ROLESru   Ú
ValueErrorrI   )rn   r/   rw   r:   Úes        r;   ru   ru   ´   sµ   € ä×Ñ˜tŸz™zÔ*Ü¨Ð4XÔYÐYÜ×.Ñ.¨t¯}©}Ó=€HÙÜ¨°HÔ=Ð=Ø‡y�yœ×(Ñ(Ñ(Ü¨Ð4VÔWÐWð<Ü×Ñ §
¡
¨D¯M©M¸4¿9¹9ÓEˆð €Køô ò <Ü¨´C¸³FÔ;Ð;ûð<ús   Â5B< Â<	C ÃCÃC z/api/users/{user_id}Úuser_idc                 ó  — t        j                  | «      }|st        dd¬«      ‚|j                  d«      | k(  rt        dd¬«      ‚|d   dk(  r$t        j                  «       d	k  rt        dd
¬«      ‚t        j
                  | «       ddiS )Né”  úUser not foundr5   r2   rp   z7You can't delete your own account while logged in as itr1   r/   é   z%Can't delete the last remaining adminÚdeletedT)r8   Úget_user_by_idr   rƒ   Úcount_adminsÚdelete_user)r‘   r/   Útargets      r;   r™   r™   Ä   s„   € ä× Ñ  Ó)€FÙÜ¨Ð4DÔEÐEØ‡y�y�ƒ˜'Ò!Ü¨Ð4mÔnÐnØˆf�~˜Ò ¤T×%6Ñ%6Ó%8¸AÒ%=Ü¨Ð4[Ô\Ð\Ü×Ñ�WÔØ�tÐÐr=   z/api/users/{user_id}/rolec                 óZ  — t        j                  | «      }|st        dd¬«      ‚|j                  t         j                  vrt        dd¬«      ‚|d   dk(  r3|j                  dk(  r$t        j
                  «       d	k  rt        dd
¬«      ‚t        j                  | |j                  «       ddiS )Nr“   r”   r5   rp   rŒ   r1   r/   Úmemberr•   z%Can't demote the last remaining adminr}   T)r8   r—   r   r1   rŽ   r˜   Úupdate_user_role)r‘   rn   r/   rš   s       r;   r�   r�   Ñ   s—   € ä× Ñ  Ó)€FÙÜ¨Ð4DÔEÐEØ‡y�yœ×(Ñ(Ñ(Ü¨Ð4VÔWÐWØˆf�~˜Ò  T§Y¡Y°(Ò%:¼t×?PÑ?PÓ?RÐVWÒ?WÜ¨Ð4[Ô\Ð\Ü×Ñ˜' 4§9¡9Ô-Ø�$ˆ<Ðr=   z#/api/users/{user_id}/reset-passwordc                 óî   — t        j                  | «      }|st        dd¬«      ‚t        j                  |j                  «      }|rt        d|¬«      ‚t        j
                  | |j                  «       ddiS )Nr“   r”   r5   rp   r}   T)r8   r—   r   rt   rR   Úadmin_reset_password)r‘   rn   r/   rš   rw   s        r;   rŸ   rŸ   Þ   si   € ä× Ñ  Ó)€FÙÜ¨Ð4DÔEÐEÜ×.Ñ.¨t×/@Ñ/@ÓA€HÙÜ¨°HÔ=Ð=Ü×Ñ˜g t×'8Ñ'8Ô9Ø�$ˆ<Ðr=   ÚMAX_CONCURRENT_JOBSÚ3z	clean-job)Úmax_workersÚthread_name_prefixÚjob_idÚparamsc                 óØ  — t        j                  | «      }t        j                  | «      }	 t        j                  | d¬«       t	        |j                  d«      «      }|st        d«      ‚t        |fi |¤Ž}|j                  j                  |dz  «       |j                  j                  |dz  «       t        j                  | d|j                  |j                  ¬«       |j                  «       rt!        j"                  |d¬«       y y # t        $ r+}t        j                  | d	t        |«      ¬
«       Y d }~ŒXd }~ww xY w# |j                  «       rt!        j"                  |d¬«       w w xY w)NÚ
processing)Ústatusz*.csvz!No input files found for this jobzclean.parquetzrejected.parquetÚdone)r¨   ÚstatsÚcategory_columnÚerror)r¨   r¬   T)Úignore_errors)ÚjobsÚjob_dirÚ
inputs_dirÚ
set_statusÚsortedÚglobr�   r   Úclean_dfÚwrite_parquetÚrejected_dfrª   r«   Ú	ExceptionrI   ÚexistsÚshutilÚrmtree)r¤   r¥   Údr°   Úinput_pathsÚresultr�   s          r;   Ú_run_cleaning_jobr¾   ø   s!  € Ü�‰�VÓ€AÜ—‘ Ó(€Jð:Ü�‰˜ |Õ4Ü˜ZŸ_™_¨WÓ5Ó6ˆÙÜÐ@ÓAÐAÜ˜[Ñ3¨FÑ3ˆØ�‰×%Ñ% a¨/Ñ&9Ô:Ø×Ñ×(Ñ(¨Ð-?Ñ)?Ô@Ü�‰ØØØ—,‘,Ø"×2Ñ2õ		
ð ×ÑÔÜ�M‰M˜*°DÖ9ð øô	 ò >Ü�‰˜ w´c¸!³f×=Ò=ûð>ûð ×ÑÔÜ�M‰M˜*°DÖ9ð ús*   ¬B3D Ä	D<Ä!D7Ä2D? Ä7D<Ä<D? Ä?*E)z/api/upload)Údependencies.r0   z^(email|domain|row)$)ÚpatternFÚfilesÚ
dedupe_keyÚexplode_phonesÚexclude_placeholder_storesc              ƒ   ór  K  — |st        dd¬«      ‚|D ]F  }|j                  j                  «       j                  d«      rŒ-t        dd|j                  › d�¬«      ‚ t	        j
                  |D �cg c]  }|j                  ‘Œ c}|j                  d«      s|j                  d«      nd	¬
«      }t	        j                  |«      }t        dz  dz  }	d}
	 t        |«      D ]ä  \  }}||d›d�z  }t        |d«      5 }|j                  d«      ƒ d {  –—† x}rU|
t        |«      z  }
|
|	kD  rt        ddt        › d�¬«      ‚|j                  |«       |j                  d«      ƒ d {  –—† x}rŒUd d d «       t        |d«      5 }|j                  d«      }|d d dv s|d d dk(  rt        dd|j                  › d�¬«      ‚	 d d d «       Œæ 	 t        |||¬«      }t         j#                  t$        ||«       d|iS c c}w 7 Œé7 Œ—# 1 sw Y   Œ“xY w# 1 sw Y   �Œ4xY w# t         $ r t	        j                  |«       ‚ w xY w­w)Nrp   zNo files providedr5   ú.csvÚ'z' is not a .csv filer3   r0   r.   )Ú
created_byi   r   Ú04dÚwbi   i�  zCombined upload exceeds zMB limitÚrbé   é   )s   PKs   MZs   ELFz ' does not look like a valid CSV)rÂ   rÃ   rÄ   r¤   )r   ÚfilenameÚlowerÚendswithr®   Ú
create_jobrƒ   r°   r   Ú	enumerateÚopenÚreadÚlenÚwriteÚ
delete_jobÚdictÚjob_executorÚsubmitr¾   )rm   rÁ   rÂ   rÃ   rÄ   r:   Úfr¤   r°   Ú	max_bytesÚ
total_sizeÚiÚdestÚoutÚchunkÚcheckÚheadr¥   s                     r;   Ú
upload_csvrä     s;  è ø€ ñ Ü¨Ð4GÔHÐHÛˆØ�z‰z×ÑÓ!×*Ñ*¨6Õ2Ü¨C¸!¸A¿J¹J¸<ÐG[Ð8\Ô]Ð]ð ô �_‰_Ù"Ó#™U˜ˆ�‹˜UÑ#Ø,0¯H©H°]Ô,C�4—8‘8˜GÔ$Èô€Fô —‘ Ó(€Jä Ñ$ tÑ+€IØ€JðÜ˜eÖ$‰DˆAˆqØ 1 S '¨ Ñ.ˆDÜ�d˜DÔ! SØ%&§V¡V¨KÓ%8×8Ð8�eÐ8Ø¤# e£*Ñ,�JØ! IÒ-Ü+Ø(+Ø%=¼m¸_ÈHÐ#Uôð ð —I‘I˜eÔ$ð &'§V¡V¨KÓ%8×8Ð8�eÑ8÷ "ô �d˜DÔ! UØ—z‘z !“}�Ø˜˜�8˜~Ñ-°°b°q°¸ZÒ1GÜ'°CÀ!ÀAÇJÁJÀ<ÐOoÐ@pÔqÐqð 2H÷ "Ð!ñ %ô* ØØ%Ø#=ô€Fô
 ×ÑÔ)¨6°6Ô:à�fÐÐùòI 	$ð  9øÐ8ø÷ "Ð!ú÷ "Ñ!ûô ò Ü�‰˜ÔØðüs…   ‚>H7Á/H7Á0G2ÂAH7Ã&H Ã8G;ÄG7ÄAG;Å!G9Å"G;Å)H Å=<HÆ:
H Ç3H7Ç7G;Ç9G;Ç;H	È H ÈH	ÈH È H4È4H7z	/api/jobsé2   r•   éÈ   )ÚgeÚleÚlimitc                 óÌ   — t        j                  «       }g }|D ]/  }t        j                  |«      }|sŒ|j                  d|i|¥«       Œ1 |j	                  d„ d¬«       d|d |  iS )Nr¤   c                 ó&   — | j                  dd«      S )NÚ
updated_atÚ r‚   )r�   s    r;   Ú<lambda>zlist_jobs.<locals>.<lambda>Y  s   € ˜qŸu™u \°2Ô6r=   T)r^   Úreverser®   )r®   Ú	list_jobsÚ
get_statusÚappendÚsort)ré   Úall_idsÚentriesr¤   Ústs        r;   rð   rð   Q  sh   € ä�n‰nÓ€GØ€GÛˆÜ�_‰_˜VÓ$ˆÚØ�N‰N˜H fÐ3°Ð3Õ4ð ð ‡L�LÑ6À€LÔEØ�G˜F˜U�OÐ$Ð$r=   z/api/jobs/{job_id}/statusc                 óN   — t        j                  | «      }|€t        dd¬«      ‚|S )Nr“   úJob not foundr5   )r®   rñ   r   ©r¤   rö   s     r;   Ú
job_statusrú   \  s'   € ä	�‰˜Ó	 €BØ	€zÜ¨°OÔDÐDØ€Ir=   Úreturnc                 ó¶   — t        j                  | «      }|€t        dd¬«      ‚|j                  d«      dk7  r t        dd|j                  d«      › d�¬«      ‚|S )	Nr“   rø   r5   r¨   r©   r�   zJob status is 'z', not ready)r®   rñ   r   rƒ   rù   s     r;   Ú_require_done_jobrý   h  sY   € Ü	�‰˜Ó	 €BØ	€zÜ¨°OÔDÐDØ	‡v�vˆhÓ˜6Ò!Ü¨°oÀbÇfÁfÈXÓFVÐEWÐWcÐ4dÔeÐeØ€Ir=   Údatasetc                 ó˜   — |dvrt        dd¬«      ‚t        j                  | «      |› d�z  }|j                  «       st        dd¬«      ‚|S )N)ÚcleanÚrejectedrp   z%dataset must be 'clean' or 'rejected'r5   z.parquetr“   zDataset not found for this job)r   r®   r¯   r¸   )r¤   rþ   Úps      r;   Ú_parquet_pathr  q  sO   € ØÐ+Ñ+Ü¨Ð4[Ô\Ð\Ü�‰�VÓ ' ¨(Ð3Ñ3€AØ�8‰8Œ:Ü¨Ð4TÔUÐUØ€Hr=   ÚconÚparquet_pathc                 óÀ   — | j                  d|j                  «       › d�«      j                  «       }|D �cg c]  }d|d   j                  «       v sŒ|d   ‘Œ c}S c c}w )Nú%DESCRIBE SELECT * FROM read_parquet('ú')ÚVARCHARr•   r   )ÚexecuteÚas_posixÚfetchallÚupper)r  r  ÚdescÚrows       r;   Ú_string_columnsr  z  s[   € Ø�;‰;Ð>¸|×?TÑ?TÓ?VÐ>WÐWYÐZÓ[×dÑdÓf€DÙ"ÓB™d�s i°3°q±6·<±<³>Ò&AˆC�‹F˜dÑBÐBùÒBs   ¶AÁAz/api/jobs/{job_id}/datar   Úallz^(all|has_email|no_email)$)rç   iè  ÚcategoryÚsearchÚemail_filterÚpageÚ	page_sizec           
      óÜ  — t        | «      }t        | |«      }|j                  d«      }	t        j                  «       }
g }g }|r(|	r&|j                  d|	› d�«       |j                  |«       |dk7  r>t        |
|«      }|r0|dk(  r|j                  d|› d�«       n|j                  d|› d�«       |r]t        |
|«      }|rOdj                  d	„ |D «       «      }|j                  d
|› d�«       |j                  d|› d�gt        |«      z  «       |rddj                  |«      › �nd}|
j                  d|j                  «       › d|› �|«      j                  «       d   }|dz
  |z  }|
j                  d|j                  «       › d|› d|› d|› �|«      }|j                  D �cg c]  }|d   ‘Œ	 }}|j                  «       D �cg c]  }t!        t#        ||«      «      ‘Œ }}|||||dœS c c}w c c}w )Nr«   Ú"ú" = ?r  Ú	has_emailú" IS NOT NULLú	" IS NULLz OR c              3   ó(   K  — | ]
  }d |› d�–— Œ y­w)r  z	" ILIKE ?NrK   )Ú.0Úcs     r;   Ú	<genexpr>zjob_data.<locals>.<genexpr>   s   è ø€ Ð&JÁ¸A¨¨1¨#¨YÔ'7Áùs   ‚Ú(Ú)Ú%úWHERE ú AND rí   z#SELECT COUNT(*) FROM read_parquet('ú') r   r•   zSELECT * FROM read_parquet('z LIMIT z OFFSET )ÚcolumnsÚrowsÚtotalr  r  )rý   r  rƒ   ÚduckdbÚconnectrò   Ú_guess_email_colr  ÚjoinÚextendrÕ   r
  r  ÚfetchoneÚdescriptionr  rØ   Úzip)r¤   rþ   r  r  r  r  r  rö   r  Úcat_colr  Úwhere_clausesr¥   Ú	email_colÚstr_colsÚlike_clausesÚ	where_sqlr)  ÚoffsetÚrows_resultr»   r'  Úrr(  s                           r;   Újob_datar;    s   € ô 
˜6Ó	"€BÜ  ¨Ó1€LØ�f‰fÐ&Ó'€Gä
�.‰.Ó
€CØ€MØ€Fá‘GØ×Ñ˜q  	¨Ð/Ô0Ø�‰�hÔà�uÒÜ$ S¨,Ó7ˆ	ÙØ˜{Ò*Ø×$Ñ$ q¨¨°=Ð%AÕBà×$Ñ$ q¨¨°9Ð%=Ô>áÜ" 3¨Ó5ˆÙØ!Ÿ;™;Ñ&JÁÓ&JÓJˆLØ× Ñ  1 \ N°!Ð!4Ô5Ø�M‰M˜Q˜v˜h a˜=˜/¬C°«MÑ9Ô:á:G�&˜Ÿ™ mÓ4Ð5Ñ6ÈR€Ià�K‰KØ
-¨l×.CÑ.CÓ.EÐ-FÀcÈ)ÈÐUÐW]óç�hƒj�ñ€Eð �Q‰h˜)Ñ#€FØ—+‘+Ø
& |×'<Ñ'<Ó'>Ð&?¸sÀ9À+ð NØ�˜8 F 8ð	-àó€Kð
 )×4Ò4Ó5Ñ4˜ˆq�‹tÐ4€GÐ5Ø+6×+?Ñ+?Ô+AÓBÑ+A aŒD”�W˜a“Õ!Ð+A€DÐBð ØØØØñð ùò 6ùÚBs   ÆG$Æ>G)c                 óÊ   — | j                  d|j                  «       › d�«      j                  «       D �cg c]  }|d   ‘Œ	 }}|D ]  }d|j                  «       v sŒ|c S  y c c}w )Nr  r  r   r0   )r
  r  r  rÏ   )r  r  r  Úcolsr  s        r;   r,  r,  ¼  sl   € Ø!Ÿk™kÐ,QÐR^×RgÑRgÓRiÐQjÐjlÐ*mÓn×wÑwÔyÓzÑy�sˆC�‹FÐy€DÐzÛˆØ�a—g‘g“iÒØŠHð ð ùò	 {s   ´A z/api/jobs/{job_id}/categoriesc                 ó2  — t        | «      }|j                  d«      }|sd g dœS t        | |«      }t        j                  «       }|j                  d|› d|j                  «       › d�«      j                  «       }||D �cg c]  }|d   |d   dœ‘Œ c}dœS c c}w )	Nr«   )r«   Ú
categorieszSELECT COALESCE("z?", '(uncategorized)') AS cat, COUNT(*) AS n FROM read_parquet('z') GROUP BY 1 ORDER BY n DESCr   r•   )ÚnameÚcount)rý   rƒ   r  r*  r+  r
  r  r  )r¤   rþ   rö   r2  r  r  r(  r:  s           r;   Újob_categoriesrB  Ä  s°   € ä	˜6Ó	"€BØ�f‰fÐ&Ó'€GÙØ#'°rÑ:Ð:Ü  ¨Ó1€LÜ
�.‰.Ó
€CØ�;‰;Ø
˜G˜9ð %Ø*×3Ñ3Ó5Ð6Ð6Sð	Uó÷ �hƒjð 	ð  'Ñ^bÓ6cÑ^bÐYZÀÀ!ÁÈqÐQRÉtÓ7TÐ^bÑ6cÑdÐdùÒ6cs   Á=B)Ú=Ú+Ú-Ú@r'  c                 óÜ  — | j                  d|j                  «       › d�«      j                  «       }|D �cg c]  }|d   |d   f‘Œ }}|r3t        |«      }|D �cg c]  }|d   |v sŒ|‘Œ }}|st	        dd¬«      ‚|}g }	|D ]N  \  }
}d|j                  «       v r"|	j                  d	|
› d
|
› d|
› d|
› d|
› d�«       Œ:|	j                  d|
› d�«       ŒP dj                  |	«      S c c}w c c}w )Nr  r  r   r•   rp   z3None of the requested columns exist in this datasetr5   r	  zCASE WHEN "z"" IS NOT NULL AND regexp_matches("z0", '^[=+\-@]')
                    THEN '	' || "z" ELSE "z
" END AS "r  z, )r
  r  r  Úsetr   r  rò   r-  )r  r  r'  r  r:  Úall_colsÚwantedr  ÚfilteredÚexprsr@  Údtypes               r;   Ú_sanitized_selectrN  Ú  s  € Ø�;‰;Ð>¸|×?TÑ?TÓ?VÐ>WÐWYÐZÓ[×dÑdÓf€DÙ&*Ó+¡d ��1‘�q˜‘t’ d€HÐ+ÙÜ�W“ˆÙ'Ó:™x˜!¨1¨Q©4°6ª>’A˜xˆÐ:ÙÜ¨CÐ8mÔnÐnØˆØ€EÛ‰ˆˆeØ˜Ÿ™›Ñ%ð �L‰LØ ˜vÐ%GÈÀvð N#Ø#' &¨°°°jÀÀÀbðLõð
 �L‰L˜1˜T˜F !˜Õ%ð  ð �9‰9�UÓÐùò' ,ùò ;s   ¶C$ÁC)Á'C)z/api/jobs/{job_id}/downloadÚbackground_tasksc           	      ó  ‡— t        | «      }t        | |«      }|j                  d«      }t        j                  «       }	g }
g }|r(|r&|
j                  d|› d�«       |j                  |«       |dk7  r>t        |	|«      }|r0|dk(  r|
j                  d|› d�«       n|
j                  d|› d�«       |
rdd	j                  |
«      › �nd
}t        |	||¬«      }t        j                  dd¬«      }|j                  «        |j                  Šd|› d|j                  «       › d|› d‰› d�	}|r|	j                  ||«       n|	j                  |«       d|› �}|r|d|› �z  }|dz  }ˆfd„}|�|j                  |«       t!        ‰d||¬«      S )Nr«   r  r  r  r  r  r  r$  r%  rí   )r'  rÆ   F)ÚsuffixÚdeletezCOPY (SELECT z FROM read_parquet('r&  z) TO 'z' (HEADER, DELIMITER ',')Úleads_Ú_c                  óP   •— 	 t        j                  ‰ «       y # t        $ r Y y w xY w©N)ÚosÚunlinkÚOSError)Útmp_paths   €r;   Úcleanupzdownload.<locals>.cleanup"  s&   ø€ ð	Ü�I‰I�hÕøÜò 	Ùð	ús   ƒ ™	%¤%ztext/csv)Ú
media_typerÎ   Ú
background)rý   r  rƒ   r*  r+  rò   r,  r-  rN  ÚtempfileÚNamedTemporaryFileÚcloser@  r  r
  Úadd_taskr   )r¤   rþ   r  r  r'  rO  rö   r  r2  r  r3  r¥   r4  r7  Úselect_exprÚtmpÚqueryrÎ   r[  rZ  s                      @r;   Údownloadre  ò  s±  ø€ ô 
˜6Ó	"€BÜ  ¨Ó1€LØ�f‰fÐ&Ó'€Gä
�.‰.Ó
€CØ€MØ€FÙ‘GØ×Ñ˜q  	¨Ð/Ô0Ø�‰�hÔØ�uÒÜ$ S¨,Ó7ˆ	ÙØ˜{Ò*Ø×$Ñ$ q¨¨°=Ð%AÕBà×$Ñ$ q¨¨°9Ð%=Ô>Ù:G�&˜Ÿ™ mÓ4Ð5Ñ6ÈR€Iä# C¨¸wÔG€Kä
×
%Ñ
%¨V¸EÔ
B€CØ‡I�I„KØ�x‰x€Hð ˜�}Ð$8¸×9NÑ9NÓ9PÐ8QÐQTÐU^ÐT_ð `ØˆjÐ1ð	3ð 
ñ Ø�‰�E˜6Õ"à�‰�EÔà˜�yÐ!€HÙØ�a˜�z�NÑ"ˆØ�Ñ€Hôð Ð#Ø×!Ñ! 'Ô*ä˜¨ZÀ(ÐWgÔhÐhr=   z/api/jobs/{job_id}c                 óx   — t        j                  | «      st        dd¬«      ‚t        j                  | «       ddiS )Nr“   rø   r5   r–   T)r®   Ú
job_existsr   r×   )r¤   s    r;   r×   r×   2  s2   € ä�?‰?˜6Ô"Ü¨°OÔDÐDÜ‡O�O�FÔØ�tÐÐr=   z/api/healthc                  ó
   — ddiS )Nr}   TrK   rK   r=   r;   Úhealthri  :  s   € à�$ˆ<Ðr=   rV  )drW  r¹   r^  Úconcurrent.futuresr   Úpathlibr   r*  Úfastapir   r   r   r   r	   r
   r   r   r   r   r   Úfastapi.middleware.corsr   Úfastapi.responsesr   Úpydanticr   Úslowapir   r   Úslowapi.utilr   Úslowapi.errorsr   r®   r8   Úcleaningr   ÚintÚenvironrƒ   r   Úsplitr   r   r   r   rf   rÏ   r    ÚlimiterÚappÚstateÚadd_exception_handlerÚadd_middlewarerI   r<   rA   rC   rN   rP   rT   rV   rX   rh   rl   Úpostré   rx   r{   r€   r„   r‡   rŠ   ru   rR  r™   Úpatchr�   rŸ   r    rÙ   rØ   r¾   ÚlistÚboolrä   rð   rú   rý   r  ÚDuckDBPyConnectionr  r;  r,  rB  ÚDANGEROUS_PREFIXESrN  re  r×   ri  rK   r=   r;   Ú<module>r‚     sØ  ðÛ 	Û Û Ý 1Ý ã ÷ A÷  A÷  Añ  AÝ 2Ý *Ý ß 9Ý +Ý ,ã Û Ý  ñ �B—J‘J—N‘N ?°FÓ;Ó<€Ø—*‘*—.‘.Ð!2Ð4KÓL×RÑRÐSVÓW€Ø
�*‰*�.‰.˜Ó
#€Ø—J‘J—N‘NÐ#6¸ÓDÐ Ø—:‘:—>‘>Ð"4°kÓBÐ Ø€Ø—
‘
—‘˜°Ó8×>Ñ>Ó@ÀFÑJ€á
Ð-Ô
.€ÙÐ&Ô'€Ø€‡	�	Ô Ø × Ñ Ð+Ð-IÔ Jà × Ñ ØØ!ØØ�%Ø�%ð ô ñ !¨Ô.Ù"¨4°{ÔCñEØ�4‰ZðEà�T‰zóEñ ˜|Ó,ó ô�	ô ô
�	ô ô
˜ô ô
�Yô ô�Yô ô˜Yô ð	 (ð 	°3ó 	ð ‡�Ð	Óñ8ó ð8ð ‡�Ð
ÓØ‡�ÐÓ ðL˜ð L¨8ð L¸9ò Ló !ó ðLð$ ‡�Ð
ÓØ‡�ÐÓ ðL˜ð L¨8ð L¸9ò Ló !ó ðLð ‡�Ð
ÓÙ:@ÈÔ:Nñ ˜(ð ¨S°4©Zò ó ðð ‡�ˆÓÙ˜Ó&ò Pó ðPð ‡�Ð
%Ó&Ù8?ÀÓ8Mñ Ð1ò ó 'ðð ‡�ˆÓÙ˜]Ó+ò (ó ð(ð ‡�ˆ,ÓÙ,3°MÓ,Bñ �nò ó ðð ‡�Ð"Ó#Ù$+¨MÓ$:ñ 	˜ò 	ó $ð	ð ‡�Ð&Ó'Ù?FÀ}Ó?Uñ 	˜cð 	¨ò 	ó (ð	ð ‡�Ð
/Ó0ÙKRÐS`ÓKañ  #ð Ð-Cò ó 1ðñ* ˜"Ÿ*™*Ÿ.™.Ð)>ÀÓDÓEÐ Ù!Ð.AÐVaÔb€ð:˜cð :¨4ó :ð: ‡�ˆ-¡w¨|Ó'<Ð&=€Ó>Ø‡�Ð Ó!ñ # 3›iÙ˜GÐ-CÔDÙ  ›;Ù',¨U£|Ù	�Ó	ñ3Øð3à�
Ñð3ð ð3ð ð	3ð
 !%ò3ó "ó ?ð3ðt ‡�ˆ¡G¨LÓ$9Ð#:€Ó;Ù  ¨¨cÔ2ñ %�Sò %ó <ð%ð ‡�Ð	$±G¸LÓ4IÐ3J€ÓKð�sò ó Lðð˜cð  dó ð˜#ð ¨ð °ó ðC˜×2Ñ2ð CÀ$ð CÈ4ÐPSÉ9ó Cð
 ‡�Ð	"±'¸,Ó2GÐ1H€ÓIñ ˜“>Ù  ›;Ù˜t›Ù˜eÐ-IÔJÙ�a˜A”Ù˜2 !¨Ô-ñ9Øð9àð9ð �D‰jð9ð �$‰Jð	9ð
 ð9ð ð9ð ò9ó Jð9ðx¨ð °°t±ó ð ‡�Ð	(¹ÀÓ8MÐ7N€ÓOÙ/4°W«~ñ e˜3ð e¨ò eó Pðeð$ *Ð ñ˜6×4Ñ4ð ÀDð ÐSWÐX[ÑS\Ð_cÑScð Ðoró ð0 ‡�Ð	&±g¸lÓ6KÐ5L€ÓMñ ˜“>Ù  ›;Ù˜eÐ-IÔJÙ % d£Ø(,ñ8iØð8iàð8ið �D‰jð8ið ð	8ið
 �#‰Y˜Ñð8ið &ò8ió Nð8ið~ ‡�Ð ±¸Ó0EÐ/F€ÓGð�sò ó Hðð ‡�ˆÓñó ñr=   