Ë
    ã*ôj¾1  ã                  ó  — U d Z ddlmZ ddlZddlZddlZddlmZmZ ddlm	Z	 dddd	d
ddddddœ
Z
ded<   e	D � ci c]  } | j                  «       | “Œ c} ZddddddddddddœZ ej                  dej                   «      Z ej                  d«      Z ej                  dej                   «      Zd1d„Zd2d„Zd3d„Zd4d „Zd5d!„Zd6d"„Zd7d#„Zd8d$„Zd9d%„Zd:d&„Zd;d'„Zd<d(„Zd)d*œ	 	 	 	 	 	 	 d=d+„Z d>d,„Z!d?d-„Z"d@d.„Z#dAd/„Z$dBd0„Z%yc c} w )Cuþ   Parse lead CSV / XLSX uploads into normalized row dicts.

Matches the Esferasoft â€œLeads Pipelineâ€� sheet format:
Lead ID | Date Added | Contact Name | Email | (Our Email) | Pipeline Stage |
FU 1â€“7 | Notes | optional Website / Store | Deal Value ($)
é    )ÚannotationsN)ÚdateÚdatetime)ÚPIPELINE_STAGES)Úcontact_nameúcontact nameÚcontactÚnamez	full namez	lead name)Úclient_emailzclient emailzclient's emailzclients emailz
lead emailzcustomer emailÚemail)Ú	our_emailz	our emailzour mailzsender emailz
from email)	Úwebsitezwebsite / storezwebsite/storeÚstorez	store urlÚsiteÚurlÚwebzcompany website)Ú
date_addedz
date addedÚaddedÚcreatedr   )Ústagezpipeline stageÚpipeline)ÚnotesÚnoteÚcommentsÚcomment)Ú
deal_valuez
deal valuezdeal value ($)ÚvalueÚamountÚpriceÚdeal)Úcall_scheduledzcall scheduledzscheduled call)Ú	lead_codeú	lead codeúlead idÚid)
r   r   r   r   r   r   r   r   r!   r"   zdict[str, tuple[str, ...]]ÚHEADER_ALIASESúNew LeadzPotential leadz	Warm leadzPositive leadÚWonúCall Scheduled)Únewznew leadz	cold leadz	warm leadzhot leadÚ	convertedu   replied â€“ warmzreplied - warmu   closed â€“ wonzclosed - wonzcall bookedz_\[?\s*Website\s*:\s*(https?://[^\s\]\)]+|www\.[^\s\]\)]+|[^\s\]\)]+\.[a-z]{2,}[^\s\]\)]*)\s*\]?z^[^@\s]+@[^@\s]+\.[^@\s]+$z^fu\s*(\d+)c                ó´   — | xs dj                  dd«      j                  dd«      }t        j                  dd|j                  «       «      j	                  «       S )NÚ Ú
Ú Úz[\s_\-]+)ÚreplaceÚreÚsubÚstripÚcasefold)r   Útexts     ú;/var/www/html/marketing_pipleline/backend/app/csv_import.pyÚ_norm_headerr8   \   sG   € ØŠK�R× Ñ   sÓ+×3Ñ3°D¸#Ó>€DÜ�6‰6�+˜s D§J¡J£LÓ1×:Ñ:Ó<Ð<ó    c                ó¢   — | D �ch c]  }|sŒt        |«      ’Œ }}d|v xs d|v }d|v xs d|v }t        d„ |D «       «      }|xr |xs |S c c}w )Nr$   r#   r   r
   c              3  ó$   K  — | ]  }d |v –— Œ
 y­w)r   N© )Ú.0Úns     r7   Ú	<genexpr>z!_is_header_row.<locals>.<genexpr>e   s   è ø€ Ð0©% Q�G˜q”L©%ùs   ‚)r8   Úany)ÚcellsÚcÚnormsÚhas_leadÚhas_contactÚ	has_emails         r7   Ú_is_header_rowrG   a   sh   € Ù&+Ó1¡e ªqŒ\˜!�_ e€EÐ1Ø˜EÐ!Ò9 [°EÐ%9€HØ  EÐ)Ò<¨V°u¨_€KÜÑ0©%Ó0Ó0€IØÒ2˜Ò1¨	Ð2ùò	 2s
   …A�Ac                óN   — t        | d d «      D ]  \  }}t        |«      sŒ|c S  y)Né   r   )Ú	enumeraterG   )ÚmatrixÚiÚrows      r7   Ú_find_header_indexrN   i   s-   € Ü˜F 3 B˜KÖ(‰ˆˆ3Ü˜#ÕØŠHð )ð r9   c           	     ó€  — i }| s|S t        t        j                  «       «      }t        «       }|D ]U  }t        |   }|D ]E  }t	        |«      }| D ]-  }||v rŒt	        |«      |k(  sŒ|||<   |j                  |«        n ||v sŒE ŒU ŒW d|vr¨d|v r¤	 | j                  |d   «      }	d|	cxk  rt        | «      dz
  k  rxn nu| |	dz      }
|
xs dj                  «       rt	        |
«      j                  d«      r|
|d<   |j                  |
«       n(t	        |
«      dk(  r|
|vr|
|d<   |j                  |
«       | D ]s  }t        j                  t	        |«      j                  d	d«      «      }|st        j                  t	        |«      «      }|sŒT||d
t        |j                  d«      «      › �<   Œu |S # t        $ r d}	Y �Œw xY w)Nr   r   éÿÿÿÿr   é   r-   zcolumn r   r/   Úfu_)Úlistr&   ÚkeysÚsetr8   ÚaddÚindexÚ
ValueErrorÚlenr4   Ú
startswithÚFU_HEADER_REÚmatchr1   ÚintÚgroup)Ú
fieldnamesÚmappingÚordered_fieldsÚused_headersÚfieldÚaliasesÚaliasÚkeyÚhÚidxÚnxtÚms               r7   Ú_map_headersrk   p   sÔ  € Ø €GÙØˆô œ.×-Ñ-Ó/Ó0€NÜ ›U€LãˆÜ  Ñ'ˆÛˆEÜ˜uÓ%ˆCÛ�Ø˜Ñ$ØÜ “? cÓ)Ø%&�G˜E‘NØ ×$Ñ$ QÔ'Ùð  ð ˜ÒÙñ ð  ð ˜'Ñ! n¸Ñ&?ð	Ø×"Ñ" 7¨>Ñ#:Ó;ˆCð �Ô)”c˜*“o¨Ñ)Õ)Ø˜S 1™WÑ%ˆCØ’I˜2×$Ñ$Ô&¬,°sÓ*;×*FÑ*FÀyÔ*QØ'*�˜Ñ$Ø× Ñ  Õ%Ü˜cÓ" gÒ-°#¸\Ñ2Ià'*�˜Ñ$Ø× Ñ  Ô%ó ˆÜ×Ñœ|¨A›×6Ñ6°s¸BÓ?Ó@ˆÙÜ×"Ñ"¤<°£?Ó3ˆAÚØ/0ˆG�cœ#˜aŸg™g a›j›/Ð*Ð+Ò,ð ð €Nøô) ò 	Ø‹Cð	ús   ÂF. Æ.F=Æ<F=c                ó  — | xs dj                  «       }|st        j                  «       S |j                  d«      d   }dD ](  }	 t	        j
                  ||«      j                  «       c S  t        j                  «       S # t        $ r Y ŒJw xY w)Nr-   r/   r   )z%Y-%m-%dz%m/%d/%Yz%d/%m/%Yz%m-%d-%Yz%d-%m-%Yz%Y/%m/%d)r4   r   ÚtodayÚsplitr   ÚstrptimerX   )Úrawr6   Úfmts      r7   Ú_parse_daterr   ¢   sƒ   € ØŠI�2×ÑÓ€DÙÜ�z‰z‹|Ðà�:‰:�c‹?˜1Ñ€DÛWˆð	Ü×$Ñ$ T¨3Ó/×4Ñ4Ó6Ò6ð Xô
 �:‰:‹<Ðøô ò 	Ùð	ús   Á#B Â 	BÂBc                óN   — | xs dj                  «       j                  «       }|dv S )Nr-   >   Ú1ÚyÚonÚyesÚtrue©r4   r5   ©rp   r6   s     r7   Ú_parse_boolr{   °   s)   € ØŠI�2×ÑÓ×'Ñ'Ó)€DØÐ2Ð2Ð2r9   c                ó”   — | xs dj                  «       }|sy t        j                  dd|«      }	 t        |«      S # t        $ r Y y w xY w)Nr-   u   [,$â‚¬Â£\s])r4   r2   r3   ÚfloatrX   )rp   r6   Úcleaneds      r7   Ú_parse_deal_valuer   µ   sM   € ØŠI�2×ÑÓ€DÙØÜ�f‰f�^ R¨Ó.€GðÜ�W‹~ÐøÜò Ùðús   °
; »	AÁAc                ó˜   — | xs dj                  «       }|sy|j                  «       }|t        v r	t        |   S |t        v r	t        |   S |S )Nr-   r'   )r4   r5   ÚSTAGE_LOOKUPÚSTAGE_ALIASES)rp   r6   Úfoldeds      r7   Ú_normalize_stager„   À   sP   € ØŠI�2×ÑÓ€DÙØØ�]‰]‹_€FØ”ÑÜ˜FÑ#Ð#Ø”ÑÜ˜VÑ$Ð$Ø€Kr9   c                ól   — | xs dj                  «       j                  «       }|sy |dv ry|dv ry|dv ryy )Nr-   >   õ   âœ“õ   âœ”rw   ÚdoneÚcompleteÚ	completedrˆ   >   ún/aÚnaÚskipÚskippedrŽ   >   úto doÚopenÚtodoÚpendingr’   ry   rz   s     r7   Ú_parse_fu_statusr“   Ì   sJ   € ØŠI�2×ÑÓ×'Ñ'Ó)€DÙØØÐEÑEØØÐ/Ñ/ØØÐ3Ñ3ØØr9   c                óH  — | sd | fS t         j                  | «      }|sd | fS |j                  d«      j                  d«      }| d |j	                  «        | |j                  «       d  z   j                  «       }t        j                  dd|«      j                  «       }||xs | fS )NrQ   z.,;z[ \t]+\nr.   )	ÚWEBSITE_IN_NOTESÚsearchr^   ÚrstripÚstartÚendr4   r2   r3   )r   r\   r   r~   s       r7   Ú_extract_website_from_notesrš   Ù   sœ   € ÙØ�Uˆ{ÐÜ×#Ñ# EÓ*€EÙØ�Uˆ{ÐØ�k‰k˜!‹n×#Ñ# EÓ*€GØ�_�u—{‘{“}Ð%¨¨e¯i©i«k¨mÐ(<Ñ<×CÑCÓE€GÜ�f‰f�[ $¨Ó0×6Ñ6Ó8€GØ�GÒ$˜uÐ$Ð$r9   c                óÖ   — | €yt        | t        «      r| j                  «       j                  «       S t        | t        «      r| j                  «       S t	        | «      j                  «       S ©Nr-   )Ú
isinstancer   r   Ú	isoformatÚstrr4   ©r   s    r7   Ú_cell_to_strr¡   å   sS   € Ø€}ØÜ�%œÔ"Ø�z‰z‹|×%Ñ%Ó'Ð'Ü�%œÔØ�‰Ó Ð Üˆu‹:×ÑÓÐr9   c                ób   — t        t        j                  | xs dj                  «       «      «      S rœ   )ÚboolÚEMAIL_REr\   r4   r    s    r7   Ú_looks_like_emailr¥   ï   s#   € Ü”—‘ ¢¨×2Ñ2Ó4Ó5Ó6Ð6r9   rQ   ©Úheader_row_numberc               óÆ  ‡— t        | «      Šd‰vrd‰vrt        d«      ‚g }g }t        |«      D �]¬  \  }}|dz   |z   }|fdˆfd„} |d«      }	 |d«      }
 |d«      }|
r|st        |
«      s	 |
s|rt        |«      r|d}}
|rt        |«      sd}|	s|
sŒf|	r%d|	j	                  «       v rd	|	j	                  «       v rŒ� |d
«      xs d } |d«      xs d }|s|rt        |«      \  }}|}t         |d«      «      }t         |d«      «      xs |dk(  }i }t        dd«      D ]  }t         |d|› �«      «      }|sŒ|||<   Œ  |	s|j                  d|› d�«       |
s|j                  d|› d�«       n#t        |
«      s|j                  d|› d|
› d�«       |j                  | |d«      xs d |	|
|xs d |t         |d«      «      |t         |d«      «      |||dœ«       �Œ¯ ||fS )Nr   r   zOFile must include Contact Name and Email columns (Leads Pipeline sheet format).rQ   c                ó`   •— ‰j                  | «      }|€yt        |j                  |«      «      S rœ   )Úgetr¡   )rc   Ú_rawÚcolÚ
header_maps      €r7   rª   z_parse_mapped_rows.<locals>.get  s,   ø€ Ø—.‘. Ó'ˆCØˆ{ØÜ §¡¨£Ó.Ð.r9   r   r-   r   zemail marketingr   r   r   r!   r)   é   rR   zRow z: missing contact namez: missing client emailu   : invalid client email â€œu   â€�r"   r   r   )Ú
row_numberr"   r   r   r   r   r   r   r   r   r!   Ú
follow_ups)rc   rŸ   ÚreturnrŸ   )rk   rX   rJ   r¥   r5   rš   r„   r{   Úranger“   Úappendrr   r   )r_   Úraw_rowsr§   ÚrowsÚerrorsÚoffsetrp   Ú	excel_rowrª   r   r   r   r   r   Ú	extractedr   r!   r°   r>   Ústatusr­   s                       @r7   Ú_parse_mapped_rowsr»   ó   sB  ø€ ô ˜jÓ)€JØ˜ZÑ'¨NÀ*Ñ,LÜð-ó
ð 	
ð
 €DØ€Fä  ×*‰ˆ�Ø%¨Ñ)¨FÑ2ˆ	à!$ö 	/ñ ˜>Ó*ˆÙ˜>Ó*ˆÙ˜Ó$ˆ	ñ ¡	Ô2CÀLÔ2QØÙ¡	Ô.?À	Ô.JØ&/°˜)ˆLñ Ô.¨yÔ9ØˆIá¡LØá˜J¨,×*?Ñ*?Ó*AÑAÐFWÐ[g×[pÑ[pÓ[rÑFrØá�G“Ò$ ˆÙ�i“.Ò( DˆÙ™5Ü:¸5ÓAÑˆI�uØˆGä ¡ W£Ó.ˆÜ$¡SÐ)9Ó%:Ó;ÒX¸uÐHXÑ?Xˆà%'ˆ
Ü�q˜!–ˆAÜ%¡c¨C°¨s¨)£nÓ5ˆFÚØ &�
˜1’ð ñ Ø�M‰M˜D  Ð+AÐBÔCÙØ�M‰M˜D  Ð+AÐBÕCÜ" <Ô0Ø�M‰M˜D  Ð+EÀlÀ^ÐSVÐWÔXà�‰à'Ù  Ó-Ò5°Ø ,Ø ,Ø&Ò.¨$Ø"Ü)©#¨lÓ*;Ó<ØÜ/±°LÓ0AÓBØØ"0Ø(ñö	
ði +ðJ �ˆ<Ðr9   c           
     óº   — t        j                  t        j                  | «      «      }|D ��cg c]  }|D �cg c]  }t	        |«      ‘Œ c}‘Œ c}}S c c}w c c}}w ©N)ÚcsvÚreaderÚioÚStringIOr¡   )r6   r¿   rM   rB   s       r7   Ú_matrix_from_csvrÂ   K  sG   € Ü�Z‰ZœŸ™ DÓ)Ó*€FÙ6<Ô=±f¨s¡cÓ*¡c Œ\˜!�_ cÓ*°fÒ=Ð=ùÒ*ùÓ=s   ®	A·AÁ	AÁAc           	     óx  — | r=t        d„ | d   D «       «      r(| j                  «        | rt        d„ | d   D «       «      rŒ(| st        d«      ‚t        | «      }| |   }t	        t        |«      gd„ | |d  D «       ¢d‘­Ž }g }t        |«      D ]2  }|t        |«      k  r||   nd}|j                  |r|nd|dz   › �«       Œ4 g }| |dz   d  D ]b  }t        |«      dg|t        |«      z
  z  z   }	t        d	„ |	D «       «      rŒ4|j                  t        |«      D �ci c]  }||   |	|   “Œ c}«       Œd |||dz   fS c c}w )
Nc              3  ó"   K  — | ]  }| –— Œ	 y ­wr½   r<   ©r=   rB   s     r7   r?   z$_rows_from_matrix.<locals>.<genexpr>Q  s   è ø€ Ð3©
 1˜Qœ©
ùó   ‚rP   zSpreadsheet is emptyc              3  ó2   K  — | ]  }t        |«      –— Œ y ­wr½   )rY   )r=   Úrs     r7   r?   z$_rows_from_matrix.<locals>.<genexpr>Y  s   è ø€ Ð#HÑ4G¨q¤C¨§FÑ4Gùs   ‚r   r-   zColumn rQ   c              3  ó"   K  — | ]  }| –— Œ	 y ­wr½   r<   rÅ   s     r7   r?   z$_rows_from_matrix.<locals>.<genexpr>b  s   è ø€ Ð%™f˜�1Œu™fùrÆ   )	ÚallÚpoprX   rN   ÚmaxrY   r²   r³   rS   )
rK   Ú
header_idxÚraw_headersÚwidthÚheadersrL   rg   r´   rA   Úpaddeds
             r7   Ú_rows_from_matrixrÒ   P  sT  € Ù
”SÑ3¨¨rª
Ó3Ô3Ø�
‰
Œñ ”SÑ3¨¨rª
Ó3Õ3áÜÐ/Ó0Ð0ä# FÓ+€JØ˜Ñ$€Kä”�KÓ ÐLÑ#H°F¸:¸;Ñ4GÓ#HÐLÈ!ÒL€EØ€GÜ�5Ž\ˆØ¤# kÓ"2Ò2ˆK˜ŠN¸ˆØ�‰™A‘q W¨Q°©U¨GÐ#4Õ5ð ð €HØ˜
 Q™Ð(Ó)ˆÜ�e“ ˜t u¬s°5«zÑ'9Ñ:Ñ:ˆÜÑ%™fÓ%Ô%ØØ�‰¼¸e¼ÓE¹°1˜ ™ V¨A¡YÑ.¸ÑEÕFð	 *ð �H˜j¨1™nÐ,Ð,ùò Fs   ÄD7
c                óš   — t        | t        «      r| j                  dd¬«      }n| }t        t	        |«      «      \  }}}t        |||¬«      S )Nz	utf-8-sigr1   )r¶   r¦   )r�   ÚbytesÚdecoderÒ   rÂ   r»   )Úcontentr6   rÐ   r´   Ú
header_rows        r7   Úparse_leads_csvrØ   i  sI   € Ü�'œ5Ô!Ø�~‰~˜k°)ˆ~Ó<‰àˆÜ$5Ô6FÀtÓ6LÓ$MÑ!€GˆX�zÜ˜g xÀ:ÔNÐNr9   c           
     óì  — ddl m}  |t        j                  | «      dd¬«      }	 d }|j                  D ]'  }|j                  «       j                  «       dk(  sŒ%|} n |r||   n|j                  }|j                  d¬«      D ��cg c]  }|D �cg c]  }t        |«      ‘Œ c}‘Œ }}}|j                  «        t        |«      \  }	}
}t        |	|
|¬«      S c c}w c c}}w # |j                  «        w xY w)Nr   )Úload_workbookT)ÚfilenameÚ	read_onlyÚ	data_onlyzleads pipeline)Úvalues_onlyr¦   )ÚopenpyxlrÚ   rÀ   ÚBytesIOÚ
sheetnamesr4   r5   ÚactiveÚ	iter_rowsr¡   ÚcloserÒ   r»   )rÖ   rÚ   ÚwbÚ	preferredr
   ÚwsrM   rB   rK   rÐ   r´   r×   s               r7   Úparse_leads_xlsxrè   r  sØ   € Ý&á	¤§
¡
¨7Ó 3¸tÈtÔ	T€Bð	Øˆ	Ø—M”MˆDØ�z‰z‹|×$Ñ$Ó&Ð*:Ó:Ø �	Ùð "ñ (ˆR�	Š]¨R¯Y©YˆØ<>¿L¹LÐUY¸LÔ<ZÔ[Ñ<Z°S©CÓ0©C q”< •?¨CÓ0Ð<ZˆÑ[à
�‰Œ
ä$5°fÓ$=Ñ!€GˆX�zÜ˜g xÀ:ÔNÐNùò 1ùÓ[øà
�‰�
ús5   ¦2C! Á-C! Â	CÂCÂ!CÂ&C! ÃCÃC! Ã!C3c                ó°   — | xs dj                  «       }|j                  d«      rt        |«      S |j                  d«      rt        d«      ‚t	        |«      S )Nr-   z.xlsxz.xlsz;Legacy .xls is not supported. Please save as .xlsx or .csv.)ÚlowerÚendswithrè   rX   rØ   )rÛ   rÖ   r
   s      r7   Úparse_leads_uploadrì   …  sN   € ØŠN˜×!Ñ!Ó#€DØ‡}�}�WÔÜ Ó(Ð(Ø‡}�}�VÔÜÐVÓWÐWÜ˜7Ó#Ð#r9   )r   rŸ   r±   rŸ   )rA   z	list[str]r±   r£   )rK   úlist[list[str]]r±   r]   )r_   úlist[str] | Noner±   zdict[str, str])rp   ú
str | Noner±   r   )rp   rï   r±   r£   )rp   rï   r±   zfloat | None)rp   rï   r±   rŸ   )rp   rï   r±   rï   )r   rï   r±   ztuple[str | None, str | None])r±   rŸ   )r   rŸ   r±   r£   )r_   rî   r´   z
list[dict]r§   r]   r±   útuple[list[dict], list[str]])r6   rŸ   r±   rí   )rK   rí   r±   z!tuple[list[str], list[dict], int])rÖ   zbytes | strr±   rð   )rÖ   rÔ   r±   rð   )rÛ   rŸ   rÖ   rÔ   r±   rð   )&Ú__doc__Ú
__future__r   r¾   rÀ   r2   r   r   Ú
app.configr   r&   Ú__annotations__r5   r�   r‚   ÚcompileÚ
IGNORECASEr•   r¤   r[   r8   rG   rN   rk   rr   r{   r   r„   r“   rš   r¡   r¥   r»   rÂ   rÒ   rØ   rè   rì   )Úss   0r7   Ú<module>rø      s}  ðòõ #ã 
Û 	Û 	ß #å &ðððð
ð KØ4Ø5ðð MØ<ñc2.€Ð*ó 2ñh *9Ó9© A�—
‘
“˜a‘¨Ñ9€àØØ!ØØØØ#Ø!ØØØ#ñ€ð �2—:‘:ØfØ‡M�MóÐ ð ˆ2�:‰:Ð3Ó4€Øˆr�z‰z˜.¨"¯-©-Ó8€ó=ó
3óó/ódó3ó
ó	ó
ó	%óó7ð ñ	UØ ðUàðUð ð	Uð
 "óUóp>ó
-ó2OóOô&$ùòA
 :s   ºD