o
    Ji0                     @   sz   d 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 Zdd Z	d	d
 Z
dd Zdd Zdd Zedkr;e  dS dS )a0  
Backfill raw calls up to a given date from source call tables into {bid}_raw_calls.

Customer mapping rules:
- Prefer source `customer_callinfo` when present.
- Outbound: use `callto`.
- Inbound: use `callfrom`.
- Fallback: `clicktocalldid`, then `callto`.
- Never fallback to `emp_phone` for customer.
    N)load_dotenv)
DictCursorc                 C   s   | d ur
t |  S dS )N )strstrip)value r   sync_calls_upto.pynz   s   r
   c                 C   s   |  d|f |  d uS )NzSHOW TABLES LIKE %s)executefetchonecursor
table_namer   r   r	   table_exists   s   r   c                 C   s$   |  d| d dd |  D S )NzSHOW COLUMNS FROM ``c                 S   s   h | ]}|d  qS )Fieldr   ).0rowr   r   r	   	<setcomp>   s    z table_columns.<locals>.<setcomp>)r   fetchallr   r   r   r	   table_columns   s   r   c                 C   s   t | d}|r|S t | d }t | d}t | d}t | d}|dkr1|r1|S |dkr9|r9|S |r=|S |rA|S dS )	Ncustomer_callinfo	directioncalltocallfromclicktocalldidoutboundinboundr   )r
   getlower)r   explicitr   r   r   click_to_callr   r   r	   pick_customer"   s    r#   c                 C   s$   t | d}|r|S t | dS )N	emp_phoner   )r
   r   )r   r$   r   r   r	   
pick_agent7   s   r%   c                     s^  t jdd} | jdddd | jdddd |  }t  td	td
dttdtddtdtddtdtddtdtdddtd}td
dttddtddtddtdddtd}t	j
d@i |}t	j
d@i |}z#| | }|j d|j d|j d|j dg}fdd|D }|std  W |  |  d S td!|  d"|j d#}	d$}
d$}d$}|D ]}t| g d%}g d&} fd'd|| D }d(d)| d*| d+}||jf  pg }|
t|7 }
t| d,t| d- |D ]f}t|d. p'd/}||	t|d0p5|jt|d1t|d2d$t|d3t|d4|d5|d6t|d7t|t||d$d8d$f |jd9kru|d97 }q|jd:kr|d97 }q|  qtd;|
 d<| d=| d>|j d?|j 
 W |  |  d S |  |  w )ANz'Sync calls up to a date into raw_calls.)descriptionz--bidTzBusiness ID, for example 6004)requiredhelpz	--date-toz Inclusive upper date, YYYY-MM-DDSYNC_SOURCE_DB_HOSTDB_HOSTz	127.0.0.1SYNC_SOURCE_DB_PORTDB_PORT3306SYNC_SOURCE_DB_USERDB_USERadminSYNC_SOURCE_DB_PASSWORDDB_PASSWORDr   SYNC_SOURCE_DB_NAMEDB_NAMEvoicebot_clusterutf8mb4)hostportuserpassworddatabasecharsetcursorclass_callhistory_call_history_callarchive_call_archivec                    s   g | ]	}t  |r|qS r   )r   )r   t)source_cursorr   r	   
<listcomp>e   s    zmain.<locals>.<listcomp>zNo source call tables found.zSource tables: z
            INSERT INTO `av  _raw_calls`
            (
                bid, callid, fileurl, status, agentname, groupname, call_starttime, call_endtime,
                call_status, agent_callinfo, customer_callinfo, direction,
                transcription_requested, transcription_status, selected_for_processing
            )
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE
                fileurl = VALUES(fileurl),
                agentname = VALUES(agentname),
                groupname = VALUES(groupname),
                call_starttime = VALUES(call_starttime),
                call_endtime = VALUES(call_endtime),
                call_status = VALUES(call_status),
                agent_callinfo = CASE
                    WHEN VALUES(agent_callinfo) != '' THEN VALUES(agent_callinfo)
                    ELSE agent_callinfo
                END,
                customer_callinfo = CASE
                    WHEN VALUES(customer_callinfo) != '' THEN VALUES(customer_callinfo)
                    ELSE customer_callinfo
                END,
                direction = VALUES(direction)
        r   )	callidbid	agentname	groupname	starttimeendtime
dialstatusr   filename)r$   r   r   r   r   c                    s   g | ]}| v r|qS r   r   )r   col)colsr   r	   rD      s    zSELECT z, z FROM `zO` WHERE starttime IS NOT NULL AND DATE(starttime) <= %s ORDER BY starttime DESCz: z rowsr   r   rF   rE   rL   rG   rH   rI   rJ   rK   not_requested      zDone. source_rows=z
 inserted=z	 updated=z	 date_to=z bid=r   )argparseArgumentParseradd_argument
parse_argsr   osgetenvintr   pymysqlconnectr   rF   printcloser   joinr   date_tor   lenr
   r   r    r%   r#   rowcountcommit)parserargssource_configdest_configsource_conn	dest_conndest_cursorsource_table_candidatessource_tablesinsert_querytotal_sourceinsertedupdatedr   select_colsoptionalfieldsqueryrowsr   r   r   )rN   rC   r	   main>   s   









_


rt   __main__)__doc__rR   rV   rY   dotenvr   pymysql.cursorsr   r
   r   r   r#   r%   rt   __name__r   r   r   r	   <module>   s    
 
