o
    H)j1                     @  s  d Z ddlmZ ddlZddlZddlZddl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mZmZmZmZ ddlmZ eeZejejejedZi d	d	d
d	ddddddddddddddddddddddddddddddi ddddddddddddd dd!dd"dd#d#d$d#d%d#d&d&d'd'd(d'd)d'ZdNd.d/ZdOd1d2Z dPd4d5Z!dQd8d9Z"dRd<d=Z#dSdBdCZ$dTdFdGZ%	dUdVdHdIZ&dWdLdMZ'dS )XzGManual lead recording uploads and CSV bulk import into {bid}_raw_calls.    )annotationsN)datetime)StringIO)AnyDictListOptionalSet)quoterecording_uploadscallidcall_idnumbercustomer_callinfophonemobilecustomer_number
start_datecall_starttime
start_timecall_start_timeend_datecall_endtimeend_timecall_end_timecall_urlfileurlfile_urlfilenamerecording_urlurlagent	agentname
agent_name
callernamecaller_name	groupname
group_namelocation	directioncall_status
dialstatusstatusvaluer   returnstrc                 C  s>   | d u rdS t |   }|dddd}tdd|S )N  _-z
[^a-z0-9_])r/   striplowerreplaceresub)r-   header r:   >/home/aiteam/pcaa-dev/dashboard-backend/lead_upload_service.py_normalize_header=   s
   r<   Optional[datetime]c              	   C  s   | d u rd S t |  }|sd S dD ]}z	t||W   S  ty&   Y qw zt|dddd W S  ty@   Y d S w )N)z%Y-%m-%d %H:%M:%Sz%Y-%m-%d %H:%Mz%Y-%m-%dT%H:%M:%Sz%Y-%m-%dT%H:%Mz%d-%m-%Y %H:%M:%Sz%d-%m-%Y %H:%Mz%d-%m-%Y %I:%M:%S %pz%d-%m-%Y %I:%M %pz%Y/%m/%d %H:%M:%Sz%Y/%m/%d %H:%Mz%Y/%m/%d %I:%M:%S %pz%Y/%m/%d %I:%M %pz%d/%m/%Y %H:%M:%Sz%d/%m/%Y %H:%Mz%d/%m/%Y %I:%M:%S %pz%d/%m/%Y %I:%M %pz%m/%d/%Y %H:%M:%Sz%m/%d/%Y %H:%Mz%m/%d/%Y %I:%M:%S %pz%m/%d/%Y %I:%M %pZz+00:00+r   )r/   r4   r   strptime
ValueErrorfromisoformatr6   split)r-   textfmtr:   r:   r;   _parse_datetimeE   s    rF   Optional[str]c                 C  s>   | d u rd S t | tr|  rtt| S t|  }|pd S N)
isinstancefloat
is_integerr/   intr4   )r-   rD   r:   r:   r;   _normalize_callidk   s   rM   
table_nameSet[str]c                 C  s(   |  d| d dd |  pg D S )NzSHOW COLUMNS FROM ``c                 S  s"   h | ]}t |d p|d qS )Fieldr   )r/   get).0rowr:   r:   r;   	<setcomp>v   s   " z!_table_columns.<locals>.<setcomp>)executefetchall)cursorrN   r:   r:   r;   _table_columnst   s   rY   configDict[str, Any]c                 C  s"   t tdp| dpddS )NPUBLIC_BASE_URLr0   /)r/   osgetenvrR   rstrip)rZ   r:   r:   r;   _public_base_urly   s   
ra   bid	file_name	raw_bytesbytesc                 C  s   t ddt|  pd}t ddtj|pdpd}|}tjt|}tj	|dd t
d}| d| }	tj||	}
t|
d	}|| W d
   n1 sTw   Y  | d|	 }t|}|ro| dt| S d| S )z;Persist audio and return a URL stored on the raw_calls row.z[^A-Za-z0-9_-]r2   unknownz[^A-Za-z0-9._-]	recordingT)exist_ok   wbNr]   z/recording-uploads/)r7   r8   r/   r4   r^   pathbasenamejoinRECORDING_UPLOAD_ROOTmakedirssecrets	token_hexopenwritera   r
   )rb   rc   rd   rZ   safe_bid	safe_namerel_dirdest_dirtoken	dest_name	dest_pathfhrel_pathbaser:   r:   r;   save_recording_bytes   s    

r~   csv_textList[Dict[str, Any]]c                 C  s  | rt |  stdtt| }|jstdi }|jD ]}t|}t	|}|r2||t |< qd|
 vr=tdg }|D ]}|rPtdd |
 D rQqAi }| D ]\}	}|	|	}
t|
t rk|
 pjd}
|
||< qWt|	d}|szqA||d< |	drt|d }||d< |	d	rt|d	 }||d	< |	dst |d< |	d	s|d |d	< |	d
}|sqAt | |d
< || qA|std|S )z<Parse bulk upload CSV into normalized raw_calls field dicts.zCSV file is emptyzCSV header row is missingr   z CSV must include a callid columnc                 s  s"    | ]}t |pd   V  qdS )r0   N)r/   r4   )rS   vr:   r:   r;   	<genexpr>        z&parse_csv_bulk_rows.<locals>.<genexpr>Nr   r   r   z=No valid rows found in CSV (need callid and call_url per row))r/   r4   rA   csv
DictReaderr   
fieldnamesr<   CSV_HEADER_ALIASESrR   valuesallitemsrI   rM   rF   r   nowappend)r   reader
header_maprawnorm	canonicalrecordsrT   recordsrc_keyvalr   dtr   r:   r:   r;   parse_csv_bulk_rows   s\   









r   c              	     s2  t |}| d}|  }| }|d|f | s%td| dt|| d}|D ]Dt|d< dd dd d	d
 dd
 dd dd |rdd v rd|d< d v rrt	
dddd< q.g d}	 fdd|	D }
g }|D ]drds|d
7 }q|fdd|
D  q|stdddd |
D }ddgt|
 }d d |
D }d!| d"| d#| d$}|rdd%d |D }|d&| 7 }d'}tdt||D ]}||||||   qW d(   n	1 sw   Y  t||d)t| d*d+S ),z3Insert or update rows from a leads bulk-upload CSV.
_raw_callsSHOW TABLES LIKE %sTable  does not existr   rb   r,   transcription_statustranscription_requested   selected_for_processingr*   ANSWERr)   inboundr&   extra_fieldsmanual_uploadT)sourcebulk_csv)rb   r   r   r   r   r   r,   r*   r)   r&   r"   r   r   r   r   c                   s   g | ]}| v r|qS r:   r:   rS   c)columnsr:   r;   
<listcomp>      z-import_raw_calls_from_csv.<locals>.<listcomp>r   r   c                   s   g | ]}  |qS r:   )rR   )rS   colr   r:   r;   r     s    zNo valid rows to import, c                 s      | ]	}d | d V  qdS rP   Nr:   r   r:   r:   r;   r         z,import_raw_calls_from_csv.<locals>.<genexpr>%sc                 S     g | ]}|d vr|qS )r   r:   r   r:   r:   r;   r     r   INSERT INTO `` (
) VALUES ()c                 s  "    | ]}d | d| dV  qdS rP   z` = VALUES(`z`)Nr:   r   r:   r:   r;   r     r    ON DUPLICATE KEY UPDATE i  Nz	Imported z= call(s) from CSV. Pipeline will process new rows (status 0).)	processedskippedmessage)r   get_connectionrX   rV   fetchonerA   rY   r/   
setdefaultjsondumpsrR   r   rm   lenrangeexecutemany)
db_handlerrb   r   r&   rowsrN   connrX   r   	col_orderpresentr   columns_sqlplaceholdersupdate_colsquery
update_sql
chunk_sizeir:   r   r   r;   import_raw_calls_from_csv   sd   


Br   
lead_phonepayloadc                   s  | d}t |p	d }|stdt |dpd }|d}t |dp*d }|rdztjt |dd	}	W n tyL }
 ztd
|
d}
~
ww |	sStdt|	dkr]tdt|||	|}|sjtdt	|dpxdt
d }t|dpt }t|d}t ||||||ddt |dpd  pd|d|dd|  }| }|d|f | std| dt|| d v rtd d!id< g d"} fd#d$|D }d%d&d' |D }d%d(gt| }d)d$ |D }d*| d+| d,| d-}|r%d%d.d' |D }|d/| 7 }||fd0d$|D  W d   n	1 s=w   Y  ||d1d2S )3z3Create or update one manual-upload call for a lead.r   r0   zLead phone is requiredr   file_data_base64rc   rg   F)validatezInvalid file_data_base64NzUploaded file is emptyi  z File size must be 200 MB or lessz-recording_url or file_data_base64 is requiredr   manual_   conversation_datetimer   r   r   r)   r   r"   r&   )rb   r   r   r   r   r   r,   r*   r)   r"   r&   r   r   r   r   r   r   )rb   r   r   r   r   r   r,   r*   r)   r"   r&   r   c                   s,   g | ]}| v r|v r| d ur|qS rH   r:   r   r   r:   r;   r   n  s
    z)insert_lead_recording.<locals>.<listcomp>r   c                 s  r   r   r:   r   r:   r:   r;   r   t  r   z(insert_lead_recording.<locals>.<genexpr>r   c                 S  r   r   r:   r   r:   r:   r;   r   v  r   r   r   r   r   c                 s  r   r   r:   r   r:   r:   r;   r   y  r   r   c                   s   g | ]} | qS r:   r:   r   r   r:   r;   r   |  s    z-Recording uploaded and queued for processing.)r   r   r   )r/   r4   rA   rR   base64	b64decode	Exceptionr   r~   rM   rp   rq   rF   r   utcnowr5   r   rX   rV   r   rY   r   r   rm   )r   rZ   rb   r   r   rN   r   file_data_b64rc   r   excr   start_dtend_dtr   rX   r   r   r   r   r   r   r   r:   r   r;   insert_lead_recording%  sz   




)r   )r-   r   r.   r/   )r-   r   r.   r=   )r-   r   r.   rG   )rN   r/   r.   rO   )rZ   r[   r.   r/   )
rb   r/   rc   r/   rd   re   rZ   r[   r.   r/   )r   r/   r.   r   rH   )rb   r/   r   r/   r&   rG   r.   r[   )
rZ   r[   rb   r/   r   r/   r   r[   r.   r[   )(__doc__
__future__r   r   r   r   loggingr^   r7   rp   r   ior   typingr   r   r   r   r	   urllib.parser
   	getLogger__name__loggerrk   rm   dirnameabspath__file__rn   r   r<   rF   rM   rY   ra   r~   r   r   r   r:   r:   r:   r;   <module>   s    
	
 !
%

&
	


=R