o
    GY)j:8 ã                   @   s´   d dl 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mZm	Z	m
Z
 d dlmZ d dlmZmZmZmZ d dlmZ e e¡Ze	e
ddd	ƒZd
d„ ZG dd„ dƒZdS )é    N)Ú
DictCursor)ÚdatetimeÚtimezoneÚ	timedelta)Úcontextmanager)ÚAnyÚDictÚListÚOptional)ÚFerneté   é   )ÚhoursÚminutesc                 C   sF   | du rdS t | dƒr!| jdu r| jtdn|  t¡}|jddS | S )zKSerialize naive MySQL datetimes as IST so clients do not treat them as UTC.NÚreplace)ÚtzinfoÚseconds)Útimespec)Úhasattrr   r   Ú_ISTÚ
astimezoneÚ	isoformat)ÚvalueÚaware© r   ú5/home/aiteam/pcaa-dev/dashboard-backend/db_handler.pyÚ_serialize_db_datetime   s   
 r   c                   @   s	  e Zd ZdZdd„ Zdd„ Zdd„ Zdd	„ Zd
d„ Ze	dd„ ƒZ
dd„ Zdd„ Zdd„ Zdd„ Zdd„ ZdUdd„Zdd„ ZdVdd„Zd d!„ Zd"d#„ Zd$d%„ Z								dWd&d'„ZdXd+d,„Zd-d.„ Zd/d0„ ZdYd1d2„Zd3d4„ Zd5d6„ Zed7ed8efd9d:„ƒZd;d<„ Z d=ed8e!e"ee#f  fd>d?„Z$d=ed@e!e"ee#f  d8e!e"ee#f  fdAdB„Z%									dZdCdD„Z&dEdF„ Z'dGdH„ Z(							d[dIdJ„Z)dKdL„ Z*dMdN„ Z+dOdP„ Z,dQdR„ Z-dSdT„ Z.dUdV„ Z/dWdX„ Z0dYdZ„ Z1d[d\„ Z2d]d^„ Z3d_d`„ Z4dadb„ Z5dcdd„ Z6dedf„ Z7d\dgdh„Z8d]djdk„Z9dldm„ Z:dUdndo„Z;						p			d^dqdr„Z<				)	*				d_dsdt„Z=dudv„ Z>d=edwe?dxe?dye@dze@d8dfd{d|„ZAd`d}d~„ZBdadd€„ZCdbd‚dƒ„ZDdcd„d…„ZEdad†d‡„ZFd`dˆd‰„ZGdadŠd‹„ZHdŒd„ ZIdŽd„ ZJddd‘d’„ZKdbd“d”„ZLd•d–„ ZMd—d˜„ ZNd™dš„ ZOd›dœ„ ZPdedžed8e?fdŸd „ZQd=ed8dfd¡d¢„ZRd=ed£ed¤eSd8e?fd¥d¦„ZTd=ed8efd§d¨„ZUd©dª„ ZVd«d¬„ ZWd­d®„ ZXd¯d°„ ZYd±d²„ ZZded³d´„Z[dfdµd¶„Z\dfd·d¸„Z]dfd¹dº„Z^dfd»d¼„Z_dfd½d¾„Z`dfd¿dÀ„ZadÁdÂ„ ZbdÃdÄ„ ZcdÅdÆ„ Zdd¤eSfdÇdÈ„ZedÉe?fdÊdË„ZfdUdÌdÍ„ZgdÎdÏ„ ZhdfdÐdÑ„ZidfdÒdÓ„ZjdadÔdÕ„ZkdÖd×„ ZldØdÙ„ ZmdÚdÛ„ ZndUdÜdÝ„ZodÞdß„ Zpdgdàdá„Zqdgdâdã„Zrd=ed8eSdB fdädå„Zsd=efdædç„Ztd=ed8e?fdèdé„Zued8e@fdêdë„ƒZvdìdí„ Zwdîdï„ Zxd8e@fdðdñ„Zyed8eSfdòdó„ƒZzd=ed¤eSd8dfdôdõ„Z{dUd=edöed8dfd÷dø„Z|dgdùdú„Z}d=ed£e~e dûedüeSdýedþe~e dÿe?d e?d8dfdd„Zdddddddddd*dœ
d=e~e dýe~e de~e d e~e? d£e~e de~e de~e de~e€ de€d	e€d8e"ee#f fd
d„Zdgdd„Z‚d=ed8e@fdd„Zƒd=ed¤eSd8e€fdd„Z„d=eded8e?fdd„Z…d=ed8dfdd„Z†d=edeSd8dfdd„Z‡d=ed£eded8dfdd„Zˆd=ed£ededþed8df
dd„Z‰d=ed£ed8dfd d!„ZŠd=ed£ed"eSd8dfd#d$„Z‹	%	*	dhd=ed&e€d'e€d8e@fd(d)„ZŒdid=ed&e€d8e@fd*d+„Z	,	-					djd=ed.e€d/e€dedB dedB d0edB dedB dedB d8eSfd1d2„ZŽd=ed£ed8eSdB fd3d4„Zdgd5d6„Zed8eSfd7d8„ƒZ‘ed9d:„ ƒZ’did;œd<eSd=e?d8eSfd>d?„Z“did;œd=ed=e?d8eSdB fd@dA„Z”d=edüeSd8eSfdBdC„Z•ddddddddiddDœ	d=ed£edB dEedB dFedB dGedB dHedB dIedB düeSdB dJe€dB dKedB dLe?dMedB d8dfdNdO„Z–dkd=ede€d8e@fdQdR„Z—d=ed£ed8e?fdSdT„Z˜dS (l  ÚDatabaseHandlerz0Handle all database operations for the dashboardc              	   C   sJ   || _ | dd¡| dd¡| dd¡| dd¡| d	d
¡dtddœ| _d S )NÚDB_HOSTz	127.0.0.1ÚDB_PORTéê  ÚDB_USERÚadminÚDB_PASSWORDzmcube@admin123ÚDB_NAMEÚvoicebot_clusterÚutf8mb4T)ÚhostÚportÚuserÚpasswordÚdatabaseÚcharsetÚcursorclassÚ
autocommit)ÚconfigÚgetr   Ú	db_config)Úselfr/   r   r   r   Ú__init__   s   




øzDatabaseHandler.__init__c                 C   s4   t | j dd¡ƒ}t t | d¡¡ ¡ ¡}t	|ƒS )NÚ
SECRET_KEYz#dev-secret-key-change-in-productionúutf-8)
Ústrr/   r0   Úbase64Úurlsafe_b64encodeÚhashlibÚsha256ÚencodeÚdigestr   )r2   ÚsecretÚkeyr   r   r   Ú_get_fernet,   s   zDatabaseHandler._get_fernetc                 C   s&   |sd S |   ¡  t|ƒ d¡¡ d¡S )Nr5   )r?   Úencryptr6   r;   Údecode©r2   r   r   r   r   Ú_encrypt_text1   s   zDatabaseHandler._encrypt_textc                 C   sH   |sd S z|   ¡  t|ƒ d¡¡ d¡W S  ty#   t d¡ Y d S w )Nr5   z8Failed to decrypt CRM secret; data may use legacy format)r?   Údecryptr6   r;   rA   Ú	ExceptionÚloggerÚwarningrB   r   r   r   Ú_decrypt_text6   s    
þzDatabaseHandler._decrypt_textc                 C   sŒ   d  dd„ t|p	dƒD ƒ¡}|sg S t|ƒdkr|dd … n|}|d|› |h}t|ƒdkr?| d|› d|› d	|› h¡ d
d„ |D ƒS )NÚ c                 s   s    | ]	}|  ¡ r|V  qd S ©N)Úisdigit)Ú.0Úchr   r   r   Ú	<genexpr>@   ó   € z<DatabaseHandler._normalize_phone_variants.<locals>.<genexpr>é
   iöÿÿÿú+Ú91z+91Ú0c                 S   s   g | ]}|r|‘qS r   r   ©rL   Úvr   r   r   Ú
<listcomp>G   ó    z=DatabaseHandler._normalize_phone_variants.<locals>.<listcomp>)Újoinr6   ÚlenÚupdate)r2   ÚphoneÚdigitsÚcore10Úvariantsr   r   r   Ú_normalize_phone_variants?   s   "z)DatabaseHandler._normalize_phone_variantsc              
   c   sx    d}z0zt jdi | j¤Ž}|V  W n t jy( } z	t d|› ¡ ‚ d}~ww W |r2| ¡  dS dS |r;| ¡  w w )z(Context manager for database connectionsNzDatabase connection error: r   )ÚpymysqlÚconnectr1   ÚErrorrF   ÚerrorÚclose)r2   ÚconnÚer   r   r   Úget_connectionI   s    €
€þÿÿ
ÿzDatabaseHandler.get_connectionc                 C   s   |  d|f¡ | ¡ d uS )NúSHOW TABLES LIKE %s©ÚexecuteÚfetchone)r2   ÚcursorÚ
table_namer   r   r   Ú_table_existsW   s   zDatabaseHandler._table_existsc                 C   ó   |  d||f¡ | ¡ d uS )NzySELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s LIMIT 1ri   ©r2   rl   rm   Úcolumn_namer   r   r   Ú_column_exists[   s
   ýzDatabaseHandler._column_existsc                 C   ó@   |   ¡ }| ¡ }| d¡ W d   ƒ d S 1 sw   Y  d S )Na•  
                CREATE TABLE IF NOT EXISTS business_crm_integrations (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY,
                    bid VARCHAR(50) NOT NULL,
                    provider VARCHAR(50) NOT NULL,
                    access_key_enc LONGTEXT NOT NULL,
                    secret_key_enc LONGTEXT NOT NULL,
                    api_host VARCHAR(255) DEFAULT NULL,
                    is_active BOOLEAN DEFAULT TRUE,
                    config JSON DEFAULT NULL,
                    last_tested_at DATETIME DEFAULT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    UNIQUE KEY uniq_bid_provider (bid, provider),
                    INDEX idx_provider_active (provider, is_active)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
                ©rg   rl   rj   ©r2   re   rl   r   r   r   Úensure_crm_integrations_tablec   ó   
ÿ"þz-DatabaseHandler.ensure_crm_integrations_tablec                 C   rs   )Na+  
                CREATE TABLE IF NOT EXISTS crm_leads_cache (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY,
                    bid VARCHAR(50) NOT NULL,
                    provider VARCHAR(50) NOT NULL,
                    external_lead_id VARCHAR(128) DEFAULT NULL,
                    lead_name VARCHAR(255) DEFAULT NULL,
                    owner_name VARCHAR(255) DEFAULT NULL,
                    email VARCHAR(255) DEFAULT NULL,
                    phone_primary VARCHAR(32) DEFAULT NULL,
                    phone_variants JSON DEFAULT NULL,
                    lead_status VARCHAR(255) DEFAULT NULL,
                    next_task_due_date VARCHAR(255) DEFAULT NULL,
                    lead_payload JSON DEFAULT NULL,
                    last_synced_at DATETIME NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    UNIQUE KEY uniq_bid_provider_external (bid, provider, external_lead_id),
                    INDEX idx_bid_provider (bid, provider),
                    INDEX idx_bid_provider_phone (bid, provider, phone_primary),
                    INDEX idx_last_synced (last_synced_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
                rt   ru   r   r   r   Úensure_crm_leads_cache_tablez   rw   z,DatabaseHandler.ensure_crm_leads_cache_tablec                 C   sÞ   |   ¡  |  ¡ ]}| ¡ }| dt|ƒt|ƒ ¡ f¡ | ¡ }|s*	 W d   ƒ d S |d |d |d | d¡t| d¡ƒ|  	| d¡¡| d¡| d	¡| d
¡t| d¡oZ| d¡ƒdœ
W  d   ƒ S 1 shw   Y  d S )Na  
                SELECT id, bid, provider, access_key_enc, secret_key_enc, api_host, is_active, config, last_tested_at, created_at, updated_at
                FROM business_crm_integrations
                WHERE bid = %s AND provider = %s
                LIMIT 1
                ÚidÚbidÚproviderÚapi_hostÚ	is_activer/   Úlast_tested_atÚ
created_atÚ
updated_atÚaccess_key_encÚsecret_key_enc)
ry   rz   r{   r|   r}   r/   r~   r   r€   Úhas_credentials)
rv   rg   rl   rj   r6   Úlowerrk   r0   ÚboolÚ_parse_json_field©r2   rz   r{   re   rl   Úrowr   r   r   Úget_crm_integration—   s.   
ù	óö$òz#DatabaseHandler.get_crm_integrationNc           	      C   sú   |   ¡  d}g }|r|d7 }| t|ƒ ¡ ¡ |  ¡ X}| ¡ }| ||¡ | ¡ p+g }g }|D ]9}| t| d¡ƒt| d¡pAdƒ ¡ |  	| d¡¡|  	| d¡¡| d¡t
| d	¡ƒ|  | d
¡¡pei dœ¡ q0|W  d   ƒ S 1 svw   Y  d S )Nz²
            SELECT bid, provider, access_key_enc, secret_key_enc, api_host, is_active, config
            FROM business_crm_integrations
            WHERE is_active = 1
        z AND provider = %srz   r{   rI   r   r‚   r|   r}   r/   )rz   r{   Ú
access_keyÚ
secret_keyr|   r}   r/   )rv   Úappendr6   r„   rg   rl   rj   Úfetchallr0   rH   r…   r†   )	r2   r{   ÚqueryÚparamsre   rl   ÚrowsÚresultrˆ   r   r   r   Úget_active_crm_integrations´   s.   

ù	$ñz+DatabaseHandler.get_active_crm_integrationsc                 C   s¬   |   ¡  |  ¡ D}| ¡ }| dt|ƒt|ƒ ¡ f¡ | ¡ }|s*	 W d   ƒ d S |  | d¡¡|  | d¡¡| d¡t	| d¡ƒdœW  d   ƒ S 1 sOw   Y  d S )NzÔ
                SELECT access_key_enc, secret_key_enc, api_host, is_active
                FROM business_crm_integrations
                WHERE bid = %s AND provider = %s
                LIMIT 1
                r   r‚   r|   r}   )rŠ   r‹   r|   r}   )
rv   rg   rl   rj   r6   r„   rk   rH   r0   r…   r‡   r   r   r   Úget_crm_credentialsÐ   s"   
ù	óü$òz#DatabaseHandler.get_crm_credentialsTc                 C   sš   |   ¡  t|ƒ ¡ }|  |¡}|  |¡}	tj|pi dd}
|  ¡ "}| ¡ }| dt|ƒ|||	|p2d |r6dnd|
f¡ W d   ƒ d S 1 sFw   Y  d S )NT©Úensure_asciiaD  
                INSERT INTO business_crm_integrations (
                    bid, provider, access_key_enc, secret_key_enc, api_host, is_active, config
                ) VALUES (%s, %s, %s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE
                    access_key_enc = VALUES(access_key_enc),
                    secret_key_enc = VALUES(secret_key_enc),
                    api_host = VALUES(api_host),
                    is_active = VALUES(is_active),
                    config = VALUES(config),
                    updated_at = CURRENT_TIMESTAMP
                é   r   )	rv   r6   r„   rC   ÚjsonÚdumpsrg   rl   rj   )r2   rz   r{   rŠ   r‹   r|   r}   r/   r   r‚   Úconfig_jsonre   rl   r   r   r   Úupsert_crm_integrationç   s&   



ùó"þz&DatabaseHandler.upsert_crm_integrationc                 C   sb   |   ¡  |  ¡ }| ¡ }| dt|ƒt|ƒ ¡ f¡ |jdkW  d   ƒ S 1 s*w   Y  d S )NzFDELETE FROM business_crm_integrations WHERE bid = %s AND provider = %sr   )rv   rg   rl   rj   r6   r„   Úrowcount©r2   rz   r{   re   rl   r   r   r   Údelete_crm_integration  s   
þ$úz&DatabaseHandler.delete_crm_integrationc                 C   sZ   |   ¡  |  ¡ }| ¡ }| dt|ƒt|ƒ ¡ f¡ W d   ƒ d S 1 s&w   Y  d S )Nzž
                UPDATE business_crm_integrations
                SET last_tested_at = NOW()
                WHERE bid = %s AND provider = %s
                )rv   rg   rl   rj   r6   r„   rœ   r   r   r   Úmark_crm_integration_tested  s   
ú"þz+DatabaseHandler.mark_crm_integration_testedc                 C   rs   )Na  
                CREATE TABLE IF NOT EXISTS crm_push_logs (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY,
                    bid VARCHAR(50) NOT NULL,
                    provider VARCHAR(50) NOT NULL,
                    callid VARCHAR(128) DEFAULT NULL,
                    lead_phone VARCHAR(64) DEFAULT NULL,
                    crm_lead_id VARCHAR(128) DEFAULT NULL,
                    activity_key VARCHAR(64) DEFAULT NULL,
                    activity_event VARCHAR(64) DEFAULT NULL,
                    status VARCHAR(32) NOT NULL,
                    message TEXT,
                    payload_preview JSON DEFAULT NULL,
                    response_preview JSON DEFAULT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_bid_provider_created (bid, provider, created_at),
                    INDEX idx_bid_callid (bid, callid),
                    INDEX idx_status (bid, provider, status)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
                rt   ru   r   r   r   Úensure_crm_push_logs_table  rw   z*DatabaseHandler.ensure_crm_push_logs_tablec                 C   s¨   |   ¡  |
d urtj|
ddnd }|d urtj|ddnd }|  ¡ (}| ¡ }| dt|ƒt|ƒ ¡ |||||t|ƒ|	||f¡ |jW  d   ƒ S 1 sMw   Y  d S )NTr”   a@  
                INSERT INTO crm_push_logs (
                    bid, provider, callid, lead_phone, crm_lead_id,
                    activity_key, activity_event, status, message,
                    payload_preview, response_preview
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                )	rŸ   r—   r˜   rg   rl   rj   r6   r„   Ú	lastrowid)r2   rz   r{   ÚstatusÚcallidÚ
lead_phoneÚcrm_lead_idÚactivity_keyÚactivity_eventÚmessageÚpayload_previewÚresponse_previewÚpayload_jsonÚresponse_jsonre   rl   r   r   r   Úlog_crm_push8  s,   

õø$èzDatabaseHandler.log_crm_pushÚleadsquaredéd   r   c                 C   s   |   ¡  tdtt|pdƒdƒƒ}tdt|pdƒƒ}ddg}t|ƒt|ƒ ¡ g}|r5| d¡ | t|ƒ¡ d |¡}|  ¡ ˆ}	|	 	¡ }
|
 
d	|› |¡ |
 ¡ pQi }t| d
¡pYdƒ}|
 
d|› d|||g ¡ |
 ¡ png }g }|D ]D}| | d¡| d¡| d¡| d¡| d¡| d¡| d¡| d¡| d¡| d¡|  | d¡¡|  | d¡¡t| d¡ƒdœ¡ qs||||dœW  d   ƒ S 1 sÉw   Y  d S )Nr–   r®   éô  r   úbid = %szprovider = %sústatus = %sú AND z2SELECT COUNT(*) AS total FROM crm_push_logs WHERE Útotala  
                SELECT id, bid, provider, callid, lead_phone, crm_lead_id,
                       activity_key, activity_event, status, message,
                       payload_preview, response_preview, created_at
                FROM crm_push_logs
                WHERE zf
                ORDER BY created_at DESC, id DESC
                LIMIT %s OFFSET %s
                ry   rz   r{   r¢   r£   r¤   r¥   r¦   r¡   r§   r¨   r©   r   )ry   rz   r{   r¢   r£   r¤   r¥   r¦   r¡   r§   r¨   r©   r   ©Úlogsr³   ÚlimitÚoffset)rŸ   ÚmaxÚminÚintr6   r„   rŒ   rX   rg   rl   rj   rk   r0   r   r†   r   )r2   rz   r{   r¶   r·   r¡   Úwherer   Ú	where_sqlre   rl   Ú	total_rowr³   r   rµ   rˆ   r   r   r   Úget_crm_push_logsc  s^   


þû
	ö
óü$Úz!DatabaseHandler.get_crm_push_logsc                 C   rs   )Na5  
                CREATE TABLE IF NOT EXISTS business_telephony_integrations (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY,
                    bid VARCHAR(50) NOT NULL,
                    provider VARCHAR(50) NOT NULL,
                    source_bid VARCHAR(50) NOT NULL,
                    host VARCHAR(255) DEFAULT NULL,
                    port INT DEFAULT 3306,
                    db_user VARCHAR(255) DEFAULT NULL,
                    db_password_enc LONGTEXT DEFAULT NULL,
                    db_name VARCHAR(255) DEFAULT NULL,
                    config JSON DEFAULT NULL,
                    is_active BOOLEAN DEFAULT TRUE,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    UNIQUE KEY uniq_bid_provider (bid, provider),
                    INDEX idx_bid_active (bid, is_active),
                    INDEX idx_provider_active (provider, is_active)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
                rt   ru   r   r   r   Ú#ensure_telephony_integrations_tablež  rw   z3DatabaseHandler.ensure_telephony_integrations_tablec                 C   s’  |   ¡  |  ¡ ·}| ¡ }| dt|ƒf¡ | ¡ pg }g }|D ]•}|  | d¡¡p+i }| d¡p5| d¡}t| d¡pB| d¡pBdƒ}	| d¡pR| d¡pR| d¡}
| d¡pa| d	¡pa| d	¡}| d
¡pn|  	| d¡¡}||	|
||dœ}| 
t| d¡p€dƒ ¡ t| d¡pŠdƒtj|pi ddt| d¡ƒrœdndt| d¡dƒr¬| d¡ ¡ n| d¡|dœ¡ q |W  d   ƒ S 1 sÂw   Y  d S )Na  
                SELECT bid, provider, source_bid, host, port, db_user, db_password_enc, db_name, config, is_active, created_at
                FROM business_telephony_integrations
                WHERE bid = %s
                ORDER BY provider
                r/   r'   r(   r    r)   Údb_userr+   Údb_namer*   Údb_password_enc)r'   r(   r)   r*   r+   r{   rI   Ú
source_bidTr”   r}   r–   r   r   r   )r{   rÃ   r™   r}   r   r/   )r¿   rg   rl   rj   r6   r   r†   r0   rº   rH   rŒ   r„   r—   r˜   r…   r   r   )r2   rz   re   rl   r   Úintegrationsrˆ   r/   Údb_hostÚdb_portrÀ   rÁ   r*   Úeffective_configr   r   r   Úlist_telephony_integrations¸  sB   
ù	û&úÿ
$Úz+DatabaseHandler.list_telephony_integrationsc                 C   s  |   ¡  t|ƒ ¡ }t|ƒ ¡ }|stdƒ‚t|pi ƒ}| d¡}| d¡d ur1t| d¡p/dƒnd}| d¡p<| d¡}	| d¡}
| d¡pK| d	¡}|
rS|  |
¡nd }t	j
|pZi d
d}|  ¡ #}| ¡ }| dt|ƒ|||||	||||rydndf
¡ W d   ƒ d S 1 sˆw   Y  d S )Nzsource_bid is requiredr'   r(   r    r)   rÀ   r*   r+   rÁ   Tr”   aû  
                INSERT INTO business_telephony_integrations (
                    bid, provider, source_bid, host, port, db_user, db_password_enc, db_name, config, is_active
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE
                    source_bid = VALUES(source_bid),
                    host = VALUES(host),
                    port = VALUES(port),
                    db_user = VALUES(db_user),
                    db_password_enc = COALESCE(VALUES(db_password_enc), db_password_enc),
                    db_name = VALUES(db_name),
                    config = VALUES(config),
                    is_active = VALUES(is_active),
                    updated_at = CURRENT_TIMESTAMP
                r–   r   )r¿   r6   r„   ÚstripÚ
ValueErrorÚdictr0   rº   rC   r—   r˜   rg   rl   rj   )r2   rz   r{   rÃ   r/   r}   Úcfgr'   r(   rÀ   Údb_passwordrÁ   rÂ   r™   re   rl   r   r   r   Úupsert_telephony_integrationâ  s<   
$


öð"þz,DatabaseHandler.upsert_telephony_integrationc                 C   sf   |   ¡  t|ƒ ¡ }|  ¡ }| ¡ }| dt|ƒ|f¡ |jdkW  d   ƒ S 1 s,w   Y  d S )NzLDELETE FROM business_telephony_integrations WHERE bid = %s AND provider = %sr   ©r¿   r6   r„   rg   rl   rj   r›   rœ   r   r   r   Údelete_telephony_integration  s   

þ$úz,DatabaseHandler.delete_telephony_integrationc                 C   sx   |   ¡  t|ƒ ¡ }|  ¡ $}| ¡ }| dt|ƒf¡ | dt|ƒ|f¡ |jdkW  d  ƒ S 1 s5w   Y  dS )z5Ensure only one active telephony integration per bid.zGUPDATE business_telephony_integrations SET is_active = 0 WHERE bid = %szYUPDATE business_telephony_integrations SET is_active = 1 WHERE bid = %s AND provider = %sr   NrÏ   rœ   r   r   r   Úset_active_telephony_provider  s   
þ
þ$öz-DatabaseHandler.set_active_telephony_providerÚdisplay_nameÚreturnc                 C   s0   t  ddt| pdƒ ¡  ¡ ¡}| d¡}|pdS )Nz
[^a-z0-9]+Ú_rI   Úfield)ÚreÚsubr6   rÉ   r„   )rÒ   r>   r   r   r   Ú_slug_data_capture_field_key.  s   
z,DatabaseHandler._slug_data_capture_field_keyc                 C   rs   )NaD  
                CREATE TABLE IF NOT EXISTS business_data_capture_fields (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY,
                    bid VARCHAR(50) NOT NULL,
                    field_key VARCHAR(80) NOT NULL,
                    display_name VARCHAR(255) NOT NULL,
                    field_type VARCHAR(32) NOT NULL DEFAULT 'text',
                    required_flag TINYINT(1) NOT NULL DEFAULT 0,
                    sort_order INT NOT NULL DEFAULT 0,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    UNIQUE KEY uniq_bid_field_key (bid, field_key),
                    INDEX idx_bid_sort (bid, sort_order)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
                rt   ru   r   r   r   Ú ensure_data_capture_fields_table4  rw   z0DatabaseHandler.ensure_data_capture_fields_tablerz   c                 C   sh   |   ¡  |  ¡ "}| ¡ }| dt|ƒf¡ | ¡ pg }dd„ |D ƒW  d   ƒ S 1 s-w   Y  d S )Nzì
                SELECT id, field_key, display_name, field_type, required_flag, sort_order
                FROM business_data_capture_fields
                WHERE bid = %s
                ORDER BY sort_order ASC, id ASC
                c                 S   sR   g | ]%}|  d ¡|  d¡|  d¡|  d¡pdt|  d¡ƒt|  d¡p#dƒdœ‘qS )	ry   Ú	field_keyrÒ   Ú
field_typeÚtextÚrequired_flagÚ
sort_orderr   )ry   rÚ   rÒ   rÛ   ÚrequiredrÞ   )r0   r…   rº   ©rL   rˆ   r   r   r   rV   W  s    	ùúÿz<DatabaseHandler.list_data_capture_fields.<locals>.<listcomp>)rÙ   rg   rl   rj   r6   r   )r2   rz   re   rl   r   r   r   r   Úlist_data_capture_fieldsI  s   
ù		÷$ôz(DatabaseHandler.list_data_capture_fieldsÚfieldsc                 C   s°  |   ¡  t|ƒ ¡ }g }tƒ }t|pg ƒD ]{\}}t|pi  d¡p)|p$i  d¡p)dƒ ¡ }|s0qt|p4i  d¡p@|p;i  d¡p@dƒ ¡  ¡ }|dvrLd}t|pPi  d¡ƒ}	t|pYi  d	¡p^dƒ ¡ }
|
si|  |¡}
|
}d
}|
|v r€|› d|› }
|d7 }|
|v sq| 	|
¡ | 
|
|||	|dœ¡ q|  ¡ 6}| ¡ }| d|f¡ |D ]}| d||d	 |d |d |d r¸dnd|d f¡ q£| ¡  W d   ƒ n1 sÎw   Y  |  |¡S )NÚnamerÒ   rI   ÚtyperÛ   rÜ   >   ÚdaterÜ   Úemailr[   ÚnumberÚtextarearß   rÚ   é   rÔ   r–   )rÚ   rÒ   rÛ   rß   rÞ   z7DELETE FROM business_data_capture_fields WHERE bid = %szæ
                    INSERT INTO business_data_capture_fields (
                        bid, field_key, display_name, field_type, required_flag, sort_order
                    ) VALUES (%s, %s, %s, %s, %s, %s)
                    r   rÞ   )rÙ   r6   rÉ   ÚsetÚ	enumerater0   r„   r…   rØ   ÚaddrŒ   rg   rl   rj   Úcommitrá   )r2   rz   râ   Ú
normalizedÚ	used_keysÚindexÚrawrÒ   rÛ   rß   rÚ   Úbase_keyÚsuffixre   rl   rˆ   r   r   r   Úreplace_data_capture_fieldsc  s^   (,
þ
ûÿ

úú
í
z+DatabaseHandler.replace_data_capture_fieldsc                 C   sN  |   ¡  t|ƒ ¡ }t|pdƒ ¡ }|sdS |p|  |¡}tjttdd„ |D ƒƒƒdd}tj|p2i dd}|p;t	 
¡ }|  ¡ ]}| ¡ }| dt|ƒ|||d urWt|ƒ ¡ nd |d urbt|ƒ ¡ nd |d urmt|ƒ ¡ nd |d urxt|ƒ ¡ nd ||	d ur„t|	ƒ ¡ nd |
d urt|
ƒ ¡ nd ||f¡ W d   ƒ dS 1 s w   Y  dS )NrI   Fc                 S   s    h | ]}t |ƒ ¡ rt |ƒ’qS r   ©r6   rÉ   rT   r   r   r   Ú	<setcomp>±  s     z8DatabaseHandler.upsert_crm_lead_cache.<locals>.<setcomp>Tr”   a¹  
                INSERT INTO crm_leads_cache (
                    bid, provider, external_lead_id, lead_name, owner_name, email,
                    phone_primary, phone_variants, lead_status, next_task_due_date,
                    lead_payload, last_synced_at
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE
                    lead_name = VALUES(lead_name),
                    owner_name = VALUES(owner_name),
                    email = VALUES(email),
                    phone_primary = VALUES(phone_primary),
                    phone_variants = VALUES(phone_variants),
                    lead_status = VALUES(lead_status),
                    next_task_due_date = VALUES(next_task_due_date),
                    lead_payload = VALUES(lead_payload),
                    last_synced_at = VALUES(last_synced_at),
                    updated_at = CURRENT_TIMESTAMP
                )rx   r6   r„   rÉ   r_   r—   r˜   ÚsortedÚlistr   Úutcnowrg   rl   rj   )r2   rz   r{   Úexternal_lead_idÚ	lead_nameÚ
owner_nameræ   Úphone_primaryÚphone_variantsÚlead_statusÚnext_task_due_dateÚlead_payloadÚlast_synced_atÚsafe_lead_idr^   Úvariants_jsonrª   Úsync_dtre   rl   r   r   r   Úupsert_crm_lead_cache›  s>    
ôí
þ$Ü$z%DatabaseHandler.upsert_crm_lead_cachec           	      C   s&  |   ¡  t|ƒ ¡ }|  |¡}|sd S |  ¡ r}| ¡ }|D ]b}| dt|ƒ|t|ƒt|ƒf¡ | ¡ }|r€| d¡| d¡| d¡| d¡| d¡| d¡| d¡| d	¡|  	| d
¡¡p_g | d¡| d¡|  	| d¡¡ppi | d¡dœ  W  d   ƒ S qW d   ƒ d S 1 sŒw   Y  d S )Na–  
                    SELECT
                        id, bid, provider, external_lead_id, lead_name, owner_name, email,
                        phone_primary, phone_variants, lead_status, next_task_due_date,
                        lead_payload, last_synced_at
                    FROM crm_leads_cache
                    WHERE bid = %s
                      AND provider = %s
                      AND (
                          phone_primary = %s
                          OR JSON_CONTAINS(phone_variants, JSON_QUOTE(%s))
                      )
                    ORDER BY last_synced_at DESC, updated_at DESC
                    LIMIT 1
                    ry   rz   r{   rú   rû   rü   ræ   rý   rþ   rÿ   r   r  r  )ry   rz   r{   rú   rû   rü   ræ   rý   rþ   rÿ   r   r  r  )
rx   r6   r„   r_   rg   rl   rj   rk   r0   r†   )	r2   rz   r{   r[   r^   re   rl   Úvariantrˆ   r   r   r   Úget_cached_crm_lead_by_phoneÛ  sF   

ðóéì
þ&Ú&z,DatabaseHandler.get_cached_crm_lead_by_phonec                 C   rs   )Naí  
                CREATE TABLE IF NOT EXISTS crm_lead_activities (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY,
                    bid VARCHAR(50) NOT NULL,
                    provider VARCHAR(50) NOT NULL,
                    activity_id VARCHAR(128) NOT NULL,
                    lead_id VARCHAR(128) DEFAULT NULL,
                    event_code VARCHAR(50) DEFAULT NULL,
                    event_name VARCHAR(255) DEFAULT NULL,
                    activity_created_on DATETIME DEFAULT NULL,
                    activity_modified_on DATETIME DEFAULT NULL,
                    activity_data JSON DEFAULT NULL,
                    activity_fields JSON DEFAULT NULL,
                    last_synced_at DATETIME NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    UNIQUE KEY uniq_bid_provider_activity (bid, provider, activity_id),
                    INDEX idx_bid_provider (bid, provider),
                    INDEX idx_lead_id (bid, provider, lead_id),
                    INDEX idx_created_on (bid, provider, activity_created_on)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
                rt   ru   r   r   r   Ú ensure_crm_lead_activities_table  rw   z0DatabaseHandler.ensure_crm_lead_activities_tablec                 C   sÖ   |   ¡  t|ƒ ¡ }t|pdƒ ¡ }|sdS |  ¡ G}| ¡ }| dt|ƒ|||r-t|ƒnd |r4t|ƒnd |r;t|ƒnd |||	d urItj|	ddnd |
d urUtj|
ddnd f
¡ W d   ƒ dS 1 sdw   Y  dS )NrI   Fa‰  
                INSERT INTO crm_lead_activities (
                    bid, provider, activity_id, lead_id, event_code, event_name,
                    activity_created_on, activity_modified_on,
                    activity_data, activity_fields, last_synced_at
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
                ON DUPLICATE KEY UPDATE
                    lead_id = VALUES(lead_id),
                    event_code = VALUES(event_code),
                    event_name = VALUES(event_name),
                    activity_created_on = VALUES(activity_created_on),
                    activity_modified_on = VALUES(activity_modified_on),
                    activity_data = VALUES(activity_data),
                    activity_fields = VALUES(activity_fields),
                    last_synced_at = NOW(),
                    updated_at = CURRENT_TIMESTAMP
                Tr”   )	r	  r6   r„   rÉ   rg   rl   rj   r—   r˜   )r2   rz   r{   Úactivity_idÚlead_idÚ
event_codeÚ
event_nameÚactivity_created_onÚactivity_modified_onÚactivity_dataÚactivity_fieldsÚsafe_activity_idre   rl   r   r   r   Úupsert_crm_lead_activity(  s2   
öî
þ!ß!z(DatabaseHandler.upsert_crm_lead_activityc                 C   rs   )Naò  
                CREATE TABLE IF NOT EXISTS sync_watermarks (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY,
                    bid VARCHAR(50) NOT NULL,
                    sync_type VARCHAR(100) NOT NULL,
                    watermark DATETIME NOT NULL,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    UNIQUE KEY uniq_bid_type (bid, sync_type)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
                rt   ru   r   r   r   Úensure_sync_watermarks_table_  rw   z,DatabaseHandler.ensure_sync_watermarks_tablec                 C   sl   |   ¡  |  ¡ $}| ¡ }| dt|ƒt|ƒf¡ | ¡ }|r#|d nd W  d   ƒ S 1 s/w   Y  d S )NzOSELECT watermark FROM sync_watermarks WHERE bid = %s AND sync_type = %s LIMIT 1Ú	watermark)r  rg   rl   rj   r6   rk   )r2   rz   Ú	sync_typere   rl   rˆ   r   r   r   Úget_sync_watermarko  s   
þ$ùz"DatabaseHandler.get_sync_watermarkc                 C   sX   |   ¡  |  ¡ }| ¡ }| dt|ƒt|ƒ|f¡ W d   ƒ d S 1 s%w   Y  d S )Na  
                INSERT INTO sync_watermarks (bid, sync_type, watermark)
                VALUES (%s, %s, %s)
                ON DUPLICATE KEY UPDATE
                    watermark = VALUES(watermark),
                    updated_at = CURRENT_TIMESTAMP
                )r  rg   rl   rj   r6   )r2   rz   r  r  re   rl   r   r   r   Úset_sync_watermarkz  s   
ø"þz"DatabaseHandler.set_sync_watermarkc                 C   sÔ   |   ¡  t|ƒ ¡ }|  ¡ }| ¡ }| dt|ƒ|f¡ | ¡ p"g }W d  ƒ n1 s-w   Y  tƒ }|D ]0}t| d¡p@dƒ 	¡ }|rK| 
|¡ |  | d¡¡pTg }	|	D ]}
t|
ƒ 	¡ }
|
rf| 
|
¡ qWq7|S )zWReturn a flat set of all phone variant strings for a bid/provider from crm_leads_cache.zZSELECT phone_primary, phone_variants FROM crm_leads_cache WHERE bid = %s AND provider = %sNrý   rI   rþ   )rx   r6   r„   rg   rl   rj   r   rê   r0   rÉ   rì   r†   )r2   rz   r{   re   rl   r   Ú	phone_setrˆ   Úprimaryr^   rU   r   r   r   Úget_lead_phone_set‹  s.   

þú

€ýz"DatabaseHandler.get_lead_phone_setc                 C   sB  |si S t |ƒ ¡ }|  ¡ }| ¡ }| dt |ƒ|f¡ | ¡ p"g }W d  ƒ n1 s-w   Y  i }|D ]?}| d¡| d¡| d¡| d¡dœ}	t | d¡pRd	ƒ ¡ }
|
r\|	||
< |  | d
¡¡peg D ]}t |ƒ ¡ }|rt|	||< qfq6i }|D ]$}|sqz||v rŠ|| ||< qz|  	|¡D ]}||v r|| ||<  nqqz|S )u¢  Batch-fetch CRM lead info for a list of phone strings.

        Returns a dict mapping each input phone â†’ {lead_name, lead_status,
        next_task_due_date, crm_owner_name} by matching against phone_primary
        and phone_variants in crm_leads_cache.  Phones that have no CRM match
        are absent from the returned dict.

        Works for any number of customers (bid-scoped) and any CRM provider.
        zë
                SELECT phone_primary, phone_variants, lead_name, owner_name,
                       lead_status, next_task_due_date
                FROM crm_leads_cache
                WHERE bid = %s AND provider = %s
                Nrû   rÿ   r   rü   )rû   rÿ   r   Úcrm_owner_namerý   rI   rþ   )
r6   r„   rg   rl   rj   r   r0   rÉ   r†   r_   )r2   rz   r{   Úphonesre   rl   r   Úvariant_maprˆ   Úinfor  rU   r‘   r[   r  r   r   r   Úget_crm_enrichment_for_phones¢  sR   


ù	õü€ýþ€z-DatabaseHandler.get_crm_enrichment_for_phonesc                 C   rs   )Naþ  
                CREATE TABLE IF NOT EXISTS call_sync_cache (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY,
                    cache_key VARCHAR(255) NOT NULL,
                    bid VARCHAR(50) NOT NULL,
                    source VARCHAR(50) NOT NULL,
                    payload JSON NOT NULL,
                    expires_at DATETIME NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    UNIQUE KEY uniq_cache_key (cache_key),
                    INDEX idx_bid_source (bid, source),
                    INDEX idx_expires_at (expires_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
                rt   ru   r   r   r   Úensure_call_sync_cache_tableÜ  rw   z,DatabaseHandler.ensure_call_sync_cache_tablec                 C   s€   |   ¡  |  ¡ .}| ¡ }| dt|ƒf¡ | ¡ }|s%	 W d   ƒ d S |  | d¡¡p.d W  d   ƒ S 1 s9w   Y  d S )Nz»
                SELECT payload
                FROM call_sync_cache
                WHERE cache_key = %s
                  AND expires_at > NOW()
                LIMIT 1
                Úpayload)r!  rg   rl   rj   r6   rk   r†   r0   )r2   Ú	cache_keyre   rl   rˆ   r   r   r   Úget_call_sync_cacheñ  s   
ø
ò$ñz#DatabaseHandler.get_call_sync_cachec           
   	   C   s€   |   ¡  tdt|ƒƒ}tj|pi dd}|  ¡ }| ¡ }	|	 dt|ƒt|ƒt|ƒ||f¡ W d   ƒ d S 1 s9w   Y  d S )Nr–   Tr”   a  
                INSERT INTO call_sync_cache (
                    cache_key, bid, source, payload, expires_at
                ) VALUES (
                    %s, %s, %s, %s, DATE_ADD(NOW(), INTERVAL %s SECOND)
                )
                ON DUPLICATE KEY UPDATE
                    bid = VALUES(bid),
                    source = VALUES(source),
                    payload = VALUES(payload),
                    expires_at = VALUES(expires_at),
                    updated_at = CURRENT_TIMESTAMP
                )	r!  r¸   rº   r—   r˜   rg   rl   rj   r6   )
r2   r#  rz   Úsourcer"  Úttl_secondsÚsafe_ttlrª   re   rl   r   r   r   Úupsert_call_sync_cache  s   
ûò"þz&DatabaseHandler.upsert_call_sync_cachec                 C   sT   |   ¡  |  ¡ }| ¡ }| d¡ t|jpdƒW  d   ƒ S 1 s#w   Y  d S )Nz5DELETE FROM call_sync_cache WHERE expires_at <= NOW()r   )r!  rg   rl   rj   rº   r›   ru   r   r   r   Úprune_expired_call_sync_cache!  s   

$ýz-DatabaseHandler.prune_expired_call_sync_cachec                 C   s<   |sdS t |tƒrzt |¡W S  tjy   | Y S w |S )zParse JSON string fieldsN)Ú
isinstancer6   r—   ÚloadsÚJSONDecodeErrorrB   r   r   r   r†   (  s   
ÿz!DatabaseHandler._parse_json_fieldc           	      C   sö   |   | d¡¡}|r|S |   | d¡¡}|rt|tƒsdS | d¡}t|tƒs)dS i }| ¡ D ]E\}}t|tƒs9q/| dd¡}|r[| dd¡| d	| d
d¡¡d| dd¡dœ||< q/d| d	| d
d¡¡d| d| dd¡¡dœ||< q/|ry|S dS )z\Return parameter_scores from the column, or rebuild from raw_response if the column is NULL.Úparameter_scoresÚraw_responseNÚ
parametersÚ
applicableTÚscorer   Ú	max_scoreÚmaxScoreÚ	reasoningrI   )r1  r2  r0  r4  FÚreasonzNot applicable)r1  r2  r0  r5  )r†   r0   r*  rË   Úitems)	r2   rˆ   Úpsrñ   r/  ÚrebuiltÚ
param_nameÚdatar0  r   r   r   Ú_resolve_parameter_scores3  s4   




üüz)DatabaseHandler._resolve_parameter_scoresc                 C   s@   |sdS g d¢}|D ]}||v r|| r|   || ¡||< q
|S )z+Format call record with proper JSON parsingN©ÚkeywordsÚ
sentimentsÚemotionsÚcustomer_details)r†   )r2   ÚrecordÚjson_fieldsrÕ   r   r   r   Ú_format_call_recordU  s   €z#DatabaseHandler._format_call_recordc           
   	   C   s¸   |   ¡ N}| ¡ }| d¡ | ¡ }g }|D ]2}t| ¡ ƒd }| dd¡}| d|› d¡ | ¡ }|r9|d nd}	| |d|› |	|d	œ¡ q|W  d
  ƒ S 1 sUw   Y  d
S )z1Get list of all businesses with their call countszSHOW TABLES LIKE '%_calls'r   Ú_callsrI   úSELECT COUNT(*) as count FROM `ú`Úcountú	Business )rz   rã   Ú
totalCallsÚ
callsTableN)	rg   rl   rj   r   rø   Úvaluesr   rk   rŒ   )
r2   re   rl   ÚtablesÚ
businessesÚtablerm   rz   Úcount_resultrG  r   r   r   Úget_all_businessesf  s&   


ü$æz"DatabaseHandler.get_all_businessesc                 C   sš   |   ¡ ?}| ¡ }|› d}| d|› d¡ | ¡ s$	 W d  ƒ dS d|› d}| |¡ | ¡ }|d|› |dœW  d  ƒ S 1 sFw   Y  dS )	z0Get detailed information for a specific businessrD  úSHOW TABLES LIKE 'ú'Na8  
                SELECT
                    COUNT(*) as total_calls,
                    COUNT(CASE WHEN status = 0 THEN 1 END) as unprocessed,
                    COUNT(CASE WHEN status = 1 THEN 1 END) as transcribed,
                    COUNT(CASE WHEN status = 2 THEN 1 END) as analyzed,
                    COUNT(CASE WHEN status = 3 THEN 1 END) as message_sent,
                    AVG(duration) as avg_duration,
                    MIN(call_starttime) as first_call_starttime,
                    MAX(call_starttime) as last_call_starttime
                FROM `z`
            rH  )rz   rã   Ú
statistics©rg   rl   rj   rk   )r2   rz   re   rl   rm   rŽ   Ústatsr   r   r   Úget_business_info‡  s"   

ø
ö
ý$åz!DatabaseHandler.get_business_infoc                 C   s0   |r|  d|› d¡ | t|pg ƒ¡ d S d S )Nú(ú))rŒ   Úextendrø   )r2   Úclausesr   Úscope_whereÚscope_paramsr   r   r   Ú_append_scope_filterª  s   þz$DatabaseHandler._append_scope_filterFc           
      C   s–   t |pg ƒ}g }	|r| d¡ |	 |¡ |r$| |rdnd¡ |	 |¡ |r4| |r,dnd¡ |	 |¡ |  ||	||¡ |rGdd |¡ |	fS d|	fS )	Núr.groupname = %súr.call_starttime >= %súDATE(r.call_starttime) >= %súr.call_starttime <= %súDATE(r.call_starttime) <= %súWHERE r²   rI   )rø   rŒ   r]  rX   )
r2   Ú	groupnameÚ	date_fromÚdate_tor[  r\  Úbase_conditionsÚdate_on_datetimeÚ
conditionsr   r   r   r   Ú_analytics_where¯  s   



z DatabaseHandler._analytics_wherec                 C   s|   |   ¡ 0}| ¡ }d|› d}| |¡ | ¡ }g }|D ]}| |d |d dœ¡ q|W  d  ƒ S 1 s7w   Y  dS )zHGet list of all groupnames for specified business with their call countszx
                SELECT
                    groupname,
                    COUNT(*) as totalCalls
                FROM `z¤_raw_calls`
                WHERE groupname IS NOT NULL AND groupname != ''
                GROUP BY groupname
                ORDER BY totalCalls DESC
            rd  rI  )rd  rI  N)rg   rl   rj   r   rŒ   )r2   rz   re   rl   rŽ   ÚresultsÚ
groupnamesrˆ   r   r   r   Úget_all_groupnamesÀ  s   
ü


þ$èz"DatabaseHandler.get_all_groupnamesc                 C   sô   |sg S dd„ |D ƒ}|sg S |   ¡ ]}| ¡ }|› d}|  ||¡s,g W  d  ƒ S d dgt|ƒ ¡}t|ƒ}	d|› dd	d
ddg}
|rQ|
 d¡ |	 |¡ d |
¡}d|› d|› d}| ||	¡ | ¡ W  d  ƒ S 1 ssw   Y  dS )z4Get grouped rows for customer->group->agent mapping.c                 S   ó$   g | ]}t |ƒ ¡ rt |ƒ ¡ ‘qS r   rõ   rT   r   r   r   rV   á  ó   $ zADatabaseHandler.get_group_agent_customer_rows.<locals>.<listcomp>Ú
_raw_callsNú, ú%szr.customer_callinfo IN (rX  zr.groupname IS NOT NULLzTRIM(r.groupname) != ''zr.agentname IS NOT NULLzTRIM(r.agentname) != ''r^  r²   zý
                SELECT
                    r.groupname,
                    r.agentname,
                    r.customer_callinfo,
                    COUNT(*) AS total_calls,
                    MAX(r.call_starttime) AS last_call
                FROM `z` r
                WHERE zŽ
                GROUP BY r.groupname, r.agentname, r.customer_callinfo
                ORDER BY total_calls DESC, last_call DESC
            )	rg   rl   rn   rX   rY   rø   rŒ   rj   r   )r2   rz   Úcustomer_numbersrd  Únormalized_numbersre   rl   rm   Úplaceholdersr   Úwhere_clausesr¼   rŽ   r   r   r   Úget_group_agent_customer_rowsÜ  s>   

ü
û


ùø$Þz-DatabaseHandler.get_group_agent_customer_rowséx   c                 C   s  zt dt|pdƒƒ}W n ttfy   d}Y nw g }|r)dd„ t|ƒ d¡D ƒ}|  ¡ Ô}| ¡ }d}g }|rB|d7 }| |¡ |rM|d7 }| |¡ |rX|d	7 }| |¡ |rc|d
7 }| |¡ |r|d 	dgt
|ƒ ¡}|d|› d7 }| |¡ |	r|d|	› d7 }| t|
pŒg ƒ¡ t|p“dƒ ¡  ¡ }|dkr¥|d|› 7 }n|dkr±|d|› d7 }|  ||¡}d|› d|› d|› d|› d|› d|› d|› d}| ||¡ | ¡ }|sSi 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“d0d“d1d“d2d“d3d“d4d“d5d“d6d“d7d“d8d“d9d“d:d“d;d“d<d“d=d“d>d“¥dddd?œ¥W  d@  ƒ S t|d) pZdƒ}t|d* pcdƒ}|dkso|dkrv|› dA|› ndB}t|d- pdƒ}t|d/ pˆdƒ}|dkr˜t|| dC dDƒnd}t|d9 p¡dƒ}t|d; pªdƒ}|dkrºt|| dC dDƒnd}i dt|d pÅdƒ“dt|d pÏdƒ“dt|d pÙdƒ“d t|d  pãdƒ“d!t|d! pídƒ“d"t|d" p÷dƒ“d#t|d# pdƒ“d$t|d$ pdƒ“d%t|d% pdƒ“d&t|d& pdƒ“d't|d' p)dƒ“d(t|d( p3dƒ“d.t|d. p=dƒ“d)|“d*|“dE|“d+t|d+ pPddFƒ“i d,t| d,¡p]ddFƒ“d-|“d/|“d0|“d1t|d1 pqdƒ“d2t|d2 p{dƒ“d3t|d3 p…dƒ“d4t|d4 pdƒ“d5t|d5 p™dƒ“d6t|d6 p£dƒ“d7t|d7 p­dƒ“d8t|d8 p·dƒ“d9|“d:t|d: pÄdƒ“d;|“d<|“d=t|d= pÔdƒ“¥t|d> pÞdƒt|dG pædƒt|dH pîdƒt|dI pödƒdJœ¥W  d@  ƒ S 1 sw   Y  d@S )KzAGet call statistics for specified business filtered by groupname.r   rx  c                 S   ó   g | ]
}|  ¡ r|  ¡ ‘qS r   ©rÉ   )rL   Úar   r   r   rV     ó    z6DatabaseHandler.get_location_stats.<locals>.<listcomp>ú,z	WHERE 1=1z AND r.groupname = %sz! AND DATE(r.call_starttime) >= %sz! AND DATE(r.call_starttime) <= %sz# AND LOWER(r.direction) = LOWER(%s)rq  rr  z AND r.agentname IN (rX  ú AND (rI   Úyesz AND r.call_status = 'ANSWER' AND r.call_starttime IS NOT NULL AND r.call_endtime IS NOT NULL AND TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime) >= Únoz“ AND (r.call_status != 'ANSWER' OR r.call_starttime IS NULL OR r.call_endtime IS NULL OR TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime) < a»  
                SELECT
                    COUNT(*) as total_calls,

                    -- Inbound statistics
                    SUM(CASE WHEN LOWER(r.direction) = 'inbound' THEN 1 ELSE 0 END) as inbound_total,
                    SUM(CASE WHEN LOWER(r.direction) = 'inbound' AND r.call_status = 'ANSWER' THEN 1 ELSE 0 END) as inbound_answered,
                    SUM(CASE WHEN LOWER(r.direction) = 'inbound' AND r.call_status = 'BUSY' THEN 1 ELSE 0 END) as inbound_busy,
                    SUM(CASE WHEN LOWER(r.direction) = 'inbound' AND r.call_status = 'CANCEL' THEN 1 ELSE 0 END) as inbound_cancel,
                    SUM(CASE WHEN LOWER(r.direction) = 'inbound' AND r.call_status = 'NOANSWER' THEN 1 ELSE 0 END) as inbound_not_answered,

                    -- Outbound statistics
                    SUM(CASE WHEN LOWER(r.direction) = 'outbound' THEN 1 ELSE 0 END) as outbound_total,
                    SUM(CASE WHEN LOWER(r.direction) = 'outbound' AND r.call_status = 'ANSWER' THEN 1 ELSE 0 END) as outbound_answered,
                    SUM(CASE WHEN LOWER(r.direction) = 'outbound' AND r.call_status = 'BUSY' THEN 1 ELSE 0 END) as outbound_busy,
                    SUM(CASE WHEN LOWER(r.direction) = 'outbound' AND r.call_status = 'CANCEL' THEN 1 ELSE 0 END) as outbound_cancel,
                    SUM(CASE WHEN LOWER(r.direction) = 'outbound' AND r.call_status = 'NOANSWER' THEN 1 ELSE 0 END) as outbound_not_answered,

                    -- Total answered calls
                    SUM(CASE WHEN r.call_status = 'ANSWER' THEN 1 ELSE 0 END) as answered_total,

                    -- Average duration for answered calls (in seconds)
                    AVG(
                        CASE
                            WHEN r.call_status = 'ANSWER'
                                AND r.call_starttime IS NOT NULL
                                AND r.call_endtime IS NOT NULL
                            THEN TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime)
                            ELSE NULL
                        END
                    ) as avg_answered_duration,

                    -- Total duration for answered calls (seconds)
                    SUM(
                        CASE
                            WHEN r.call_status = 'ANSWER'
                                AND r.call_starttime IS NOT NULL
                                AND r.call_endtime IS NOT NULL
                            THEN TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime)
                            ELSE 0
                        END
                    ) as total_answered_duration_seconds,

                    -- Detailed calls over threshold
                    SUM(
                        CASE
                            WHEN r.call_status = 'ANSWER'
                                AND r.call_starttime IS NOT NULL
                                AND r.call_endtime IS NOT NULL
                                AND TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime) >= aS  
                            THEN 1
                            ELSE 0
                        END
                    ) as detailed_calls_over_2min,

                    -- Analyzed (processed) breakdowns
                    SUM(
                        CASE
                            WHEN a.callid IS NOT NULL AND r.direction = 'inbound' AND r.call_status = 'ANSWER' THEN 1
                            ELSE 0
                        END
                    ) as analyzed_inbound_answered,
                    SUM(
                        CASE
                            WHEN a.callid IS NOT NULL AND r.direction = 'inbound' AND r.call_status = 'BUSY' THEN 1
                            ELSE 0
                        END
                    ) as analyzed_inbound_busy,
                    SUM(
                        CASE
                            WHEN a.callid IS NOT NULL AND r.direction = 'inbound' AND r.call_status = 'CANCEL' THEN 1
                            ELSE 0
                        END
                    ) as analyzed_inbound_cancel,
                    SUM(
                        CASE
                            WHEN a.callid IS NOT NULL AND r.direction = 'inbound' AND r.call_status = 'NOANSWER' THEN 1
                            ELSE 0
                        END
                    ) as analyzed_inbound_not_answered,
                    SUM(
                        CASE
                            WHEN a.callid IS NOT NULL AND r.direction = 'outbound' AND r.call_status = 'ANSWER' THEN 1
                            ELSE 0
                        END
                    ) as analyzed_outbound_answered,
                    SUM(
                        CASE
                            WHEN a.callid IS NOT NULL AND r.direction = 'outbound' AND r.call_status = 'BUSY' THEN 1
                            ELSE 0
                        END
                    ) as analyzed_outbound_busy,
                    SUM(
                        CASE
                            WHEN a.callid IS NOT NULL AND r.direction = 'outbound' AND r.call_status = 'CANCEL' THEN 1
                            ELSE 0
                        END
                    ) as analyzed_outbound_cancel,
                    SUM(
                        CASE
                            WHEN a.callid IS NOT NULL AND r.direction = 'outbound' AND r.call_status = 'NOANSWER' THEN 1
                            ELSE 0
                        END
                    ) as analyzed_outbound_not_answered,

                    SUM(
                        CASE
                            WHEN a.callid IS NOT NULL AND r.call_status = 'ANSWER' THEN 1
                            ELSE 0
                        END
                    ) as analyzed_answered_total,

                    AVG(
                        CASE
                            WHEN a.callid IS NOT NULL
                                AND r.call_status = 'ANSWER'
                                AND r.call_starttime IS NOT NULL
                                AND r.call_endtime IS NOT NULL
                            THEN TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime)
                            ELSE NULL
                        END
                    ) as analyzed_avg_answered_duration,

                    SUM(
                        CASE
                            WHEN a.callid IS NOT NULL
                                AND r.call_status = 'ANSWER'
                                AND r.call_starttime IS NOT NULL
                                AND r.call_endtime IS NOT NULL
                                AND TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime) >= aÈ  
                            THEN 1
                            ELSE 0
                        END
                    ) as analyzed_detailed_calls_over_2min,

                    -- Average talk-listen ratio from analytics
                    AVG(CASE WHEN a.talk_listen_ratio IS NOT NULL THEN a.agent_speak_percentage ELSE NULL END) as avg_agent_talk_percentage,
                    AVG(CASE WHEN a.talk_listen_ratio IS NOT NULL THEN a.customer_speak_percentage ELSE NULL END) as avg_customer_talk_percentage,

                    -- Average quality score from analytics
                    AVG(CASE WHEN a.quality_score IS NOT NULL THEN a.quality_score ELSE NULL END) as avg_quality_score
                    au  ,

                    -- Total processed durations (seconds)
                    SUM(
                        CASE
                            WHEN s.transcript IS NOT NULL AND s.transcript != ''
                            THEN COALESCE(s.duration, TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime))
                            ELSE 0
                        END
                    ) as transcribed_duration_seconds,
                    SUM(
                        CASE
                            WHEN a.callid IS NOT NULL
                            THEN COALESCE(s.duration, TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime))
                            ELSE 0
                        END
                    ) as analyzed_duration_seconds,

                    -- Processed calls (analyzed)
                    COUNT(DISTINCT a.callid) as analyzed_total,
                    SUM(
                        CASE
                            WHEN a.callid IS NOT NULL AND r.direction = 'inbound' THEN 1
                            ELSE 0
                        END
                    ) as analyzed_inbound,
                    SUM(
                        CASE
                            WHEN a.callid IS NOT NULL AND r.direction = 'outbound' THEN 1
                            ELSE 0
                        END
                    ) as analyzed_outbound

                FROM `ú)_raw_calls` r
                LEFT JOIN `zD_callanalytics` a ON r.callid = a.callid
                LEFT JOIN `ú:_sarvamresponse` s ON r.callid = s.callid
                ú
            Útotal_callsÚinbound_totalÚinbound_answeredÚinbound_busyÚinbound_cancelÚinbound_not_answeredÚoutbound_totalÚoutbound_answeredÚoutbound_busyÚoutbound_cancelÚoutbound_not_answeredÚavg_answered_durationÚavg_agent_talk_percentageÚavg_customer_talk_percentageÚavg_quality_scoreÚavg_propensity_scoreÚanswered_totalÚtotal_answered_duration_secondsÚdetailed_calls_over_2minÚ detailed_calls_over_2min_percentÚanalyzed_inbound_answeredÚanalyzed_inbound_busyÚanalyzed_inbound_cancelÚanalyzed_inbound_not_answeredÚanalyzed_outbound_answeredÚanalyzed_outbound_busyÚanalyzed_outbound_cancelÚanalyzed_outbound_not_answeredÚanalyzed_answered_totalÚanalyzed_avg_answered_durationÚ!analyzed_detailed_calls_over_2minÚ)analyzed_detailed_calls_over_2min_percentÚtranscribed_duration_secondsÚanalyzed_duration_seconds)Úanalyzed_totalÚanalyzed_inboundÚanalyzed_outboundNz : úN/Ar®   r–   Útalk_listen_ratioré   r¦  r§  r¨  )r¥  r¦  r§  r¨  )r¸   rº   Ú	TypeErrorrÊ   r6   Úsplitrg   rl   rŒ   rX   rY   rY  rø   rÉ   r„   Ú_avg_propensity_sqlrj   rk   Úroundr0   Úfloat)r2   rz   rd  re  rf  Úagent_namesÚdetailed_callsÚdetailed_threshold_secondsÚ	directionr[  r\  Ú	thresholdÚ
agent_listre   rl   Úwhere_clauser   ru  Údetailed_filterÚpropensity_avg_sqlrŽ   r‘   Ú	agent_pctÚcustomer_pctrª  r”  r–  r—  r   r¢  r£  r   r   r   Úget_location_stats	  sö  ÿ




ýÿýÿ1Ï  þ  ò / Ñ 0 Ð 1 Ï 2 Î 5ÿþýüûúùø	÷
öõôóòñðïîíìëêéèçæåäãâá à!ß"Þ#Û •  &
ý
þý
ý
þýÿþýüûúùø	÷
öõôóòñðïîíìëêéèçæåäãâá à!ß"Þ#Ú  &Ùz"DatabaseHandler.get_location_statsc                 C   sh  |   ¡ ¦}| ¡ }g }g }|r| d¡ | |¡ |r%| d¡ | |¡ |r1| d¡ | |¡ |r=| d¡ | |¡ |rI| d¡ | |¡ |	r]| d|	› d¡ | t|
pZg ƒ¡ |rfdd	 |¡ nd
}d
}|› d}|  ||¡r~|  ||d¡r~d}d|› d|› d|› d|› d|› d}| ||g¡ | ||¡ | 	¡ }|W  d  ƒ S 1 s­w   Y  dS )z0Get filtered raw calls from 7987_raw_calls tabler^  zLOWER(r.direction) = LOWER(%s)zr.call_status = %sr`  rb  rW  rX  ú WHERE r²   rI   Ú_callanalyticsÚpropensity_scorez), ca.propensity_score, ca.propensity_bandaï  
                SELECT
                    r.callid,
                    r.agentname,
                    r.agent_callinfo,
                    r.customer_callinfo,
                    r.call_starttime,
                    r.call_endtime,
                    r.direction,
                    r.call_status,
                    r.groupname,
                    TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime) as duration_seconds,
                    ca.quality_score
                    a5  ,
                    CASE
                        WHEN ca.callid IS NOT NULL THEN 1
                        ELSE 0
                    END as is_analyzed,
                    r.transcription_status,
                    CASE WHEN s.callid IS NOT NULL THEN 1 ELSE 0 END as has_transcript
                FROM `r  zF_callanalytics` ca ON r.callid = ca.callid
                LEFT JOIN `r‚  z_
                ORDER BY r.call_starttime DESC
                LIMIT %s OFFSET %s
            N)
rg   rl   rŒ   rY  rø   rX   rn   Ú_table_has_columnrj   r   )r2   rz   rd  r³  Úcall_statusr¶   r·   re  rf  r[  r\  re   rl   rv  r   r¼   Úpropensity_colsÚanalytics_tablerŽ   Úcallsr   r   r   Úget_filtered_raw_callsn  sZ   











ÿóìëêé$¸z&DatabaseHandler.get_filtered_raw_callsc                 C   s’   |   ¡ ;}| ¡ }d|› d|› d}| ||f¡ | ¡ }|s(	 W d  ƒ dS t|ƒ}|d r2dnd|d< |W  d  ƒ S 1 sBw   Y  dS )	zFGet call details from {bid}_raw_calls joined with {bid}_sarvamresponsea  
                SELECT
                    r.callid,
                    r.bid,
                    r.fileurl as fileUrl,
                    r.agentname,
                    r.groupname,
                    r.call_starttime,
                    r.call_endtime,
                    r.call_status,
                    r.agent_callinfo,
                    r.customer_callinfo,
                    r.direction,
                    r.transcription_status,
                    TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime) as duration_seconds,
                    s.transcript as transcripts,
                    s.speaker_segments,
                    s.num_speakers,
                    s.duration,
                    s.language,
                    s.request_id
                FROM `r  zr_sarvamresponse` s ON r.callid = s.callid
                WHERE r.callid = %s
                LIMIT 1
            NÚtranscriptsré   r   r¡   )rg   rl   rj   rk   rË   )r2   rz   r¢   re   rl   rŽ   ÚcallÚ	call_dictr   r   r   Úget_raw_call_detailsÆ  s    
ëêÞ%$Öz$DatabaseHandler.get_raw_call_detailsÚhas_analyticsÚ
has_sarvamrv  r   c                C   s¸   |r|  d¡durdS |r| d¡ n| d¡ | d¡ |  |¡p#i }tdt|  d¡p-dƒƒ}|dkr6dS |  d¡p?|  |¡}	|	sDdS |rHd	nd
}
| d|
› d¡ | |	|g¡ dS )zHDefault dashboard view: analyzed calls only, excluding sub-min duration.r¡   Núa.callid IS NOT NULLú0=1z9(COALESCE(r.transcription_status, '') != 'skipped_short')r   Úmin_call_duration_sÚmin_call_duration_effective_atzaCOALESCE(s.duration, r.duration_seconds, TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime))zUCOALESCE(r.duration_seconds, TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime))z(r.call_starttime < %s OR (z) >= %s))r0   rŒ   Úget_pipeline_configr¸   rº   Ú ensure_min_duration_effective_atrY  )r2   rz   rÉ  rÊ  Úfiltersrv  r   rÌ   Úmin_sÚeffective_atÚduration_exprr   r   r   Ú!_append_default_call_list_filtersô  s&   

þüz1DatabaseHandler._append_default_call_list_filtersc                    sL  |› d}ˆ   ||¡sg S |› d}	|› d}
ˆ   ||	¡}ˆ   ||
¡}g }d}d}d}d}|r<| d|	› d	¡ d
}d}|rY| d|
› d¡ d}ˆ  ||
d¡rRd}|rWd}nd}g }g }ˆ j||||||d |rÜd|v rz| d¡ | |d ¡ d|v rŠ| d¡ | |d ¡ | d¡}|d urÜ|dkr¥|rŸ| d¡ n=| d¡ n7|dkr¸|r°| d¡ |r·| d¡ n$|dkrÉ| d¡ |rÈ| d ¡ n|rÐ| d ¡ |r×| d¡ | d!¡ ˆ  ||||¡ |ríd"d# |¡ nd$}d% |¡}d&|› d'|› d(|› d(|› d)|› d*|› d+|› d,}| ||g¡ | ||¡ | 	¡ }‡ fd-d.„|D ƒS )/Nrp  Ú_sarvamresponser½  rS   zNULL AS quality_scorez1NULL AS propensity_score, NULL AS propensity_bandz7TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime)úLEFT JOIN `ú` s ON r.callid = s.callidz0CASE WHEN s.callid IS NOT NULL THEN 1 ELSE 0 ENDzMCOALESCE(s.duration, TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime))ú` a ON r.callid = a.callidz a.quality_score AS quality_scorer¾  zLa.propensity_score AS propensity_score, a.propensity_band AS propensity_bandzXCASE WHEN a.callid IS NOT NULL THEN 3 WHEN s.callid IS NOT NULL THEN 2 ELSE r.status ENDz7CASE WHEN a.callid IS NOT NULL THEN 3 ELSE r.status END©rÉ  rÊ  rÑ  rv  r   re  r_  rf  ra  r¡   é   rË  rÌ  ré   ús.callid IS NOT NULLúa.callid IS NULLr–   úr.status = 1ús.callid IS NULLú"(r.status = 0 OR r.status IS NULL)r¼  r²   rI   ú a½  
            SELECT
                r.callid,
                r.bid,
                r.fileurl as fileUrl,
                r.agentname,
                r.groupname,
                r.call_starttime,
                r.call_endtime,
                r.call_status,
                r.agent_callinfo,
                r.customer_callinfo,
                r.direction,
                CAST(TIME(r.call_starttime) AS CHAR) as call_time,
                z& as duration_seconds,
                z,
                z as status
            FROM `ú` r
            rƒ  zh
            ORDER BY r.call_starttime DESC, r.call_endtime DESC
            LIMIT %s OFFSET %s
        c                    ó   g | ]}ˆ   |¡‘qS r   ©rC  ©rL   rÆ  ©r2   r   r   rV     ó    z7DatabaseHandler._get_raw_calls_list.<locals>.<listcomp>)
rn   rŒ   r¿  rÕ  r0   r]  rX   rY  rj   r   )r2   rl   rz   rÑ  r¶   r·   r[  r\  Ú	raw_tableÚsarvam_tablerÂ  rÊ  rÉ  ÚjoinsÚstatus_caseÚquality_score_selectÚpropensity_score_selectÚduration_seconds_selectrv  r   Ústatus_filterr¼   Újoin_sqlrŽ   rÃ  r   ræ  r   Ú_get_raw_calls_list  s®   


ÿÿú	




€

€



òñðïîíìz#DatabaseHandler._get_raw_calls_listc                 C   sè  |› d}|   ||¡sdS |› d}|› d}|   ||¡}	|   ||¡}
g }g }g }|	r4| d|› d¡ |
r?| d|› d¡ | j||
|	|||d |r¾d	|v r\| d
¡ | |d	 ¡ d|v rl| d¡ | |d ¡ | d¡}|d ur¾|dkr‡|
r| d¡ n=| d¡ n7|dkrš|	r’| d¡ |
r™| d¡ n$|dkr«| d¡ |	rª| d¡ n|	r²| d¡ |
r¹| d¡ | d¡ |  ||||¡ |rÏdd |¡ nd}d |¡}d|› d|› d|› d}| ||¡ | ¡ }|rò|d  S dS )!Nrp  r   rÖ  r½  r×  rØ  rÙ  rÚ  re  r_  rf  ra  r¡   rÛ  rË  rÌ  ré   rÜ  rÝ  r–   rÞ  rß  rà  r¼  r²   rI   rá  z8
            SELECT COUNT(*) as count
            FROM `râ  rƒ  z	
        rG  )rn   rŒ   rÕ  r0   r]  rX   rj   rk   )r2   rl   rz   rÑ  r[  r\  rè  ré  rÂ  rÊ  rÉ  rê  rv  r   rï  r¼   rð  rŽ   r‘   r   r   r   Ú_get_raw_calls_count’  s€   


ú	




€

€



þýüz$DatabaseHandler._get_raw_calls_counté2   c                    s–   |› d}ˆ   ||¡sg S d|› d}||||g}	d}
|r.|
d|› d7 }
|	 t|p+g ƒ¡ | d|› d|
› d|	|g ¡ | ¡ }‡ fd	d
„|D ƒS )Nrp  ú%zk WHERE (r.callid LIKE %s OR r.agentname LIKE %s OR r.customer_callinfo LIKE %s OR r.agent_callinfo LIKE %s)r~  rX  a  
            SELECT
                r.callid,
                r.bid,
                r.fileurl as fileUrl,
                r.agentname,
                r.groupname,
                r.call_starttime,
                r.call_endtime,
                r.call_status,
                r.agent_callinfo,
                r.customer_callinfo,
                r.direction,
                CAST(TIME(r.call_starttime) AS CHAR) as call_time,
                TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime) as duration_seconds
            FROM `râ  z^
            ORDER BY r.call_starttime DESC, r.call_endtime DESC
            LIMIT %s
        c                    rã  r   rä  rå  ræ  r   r   rV     rç  z5DatabaseHandler._search_raw_calls.<locals>.<listcomp>)rn   rY  rø   rj   r   )r2   rl   rz   rŽ   r¶   r[  r\  rè  Úsearch_termr   r¼   rÃ  r   ræ  r   Ú_search_raw_callsâ  s&   
ÿñðíz!DatabaseHandler._search_raw_callsc	           '      C   s¬  |   ¡ Æ}	|	 ¡ }
|› d}|› d}|› d}|  |
|¡s+g ddœW  d  ƒ S |  |
|¡}|  |
|¡}d}d|› d	d
|› dg}g }|r[| ¡ dv r[| d¡ | | ¡ ¡ |rg| d¡ | |¡ |ru|ru| d¡ | d¡ |  ||||¡ dd |¡ }|rŒd|› dnd}|r–d|› dnd}|rœdnd}|o¦|  |
|d¡}|r«dnd}|r±dnd}d|› d|› d|› d |› d!|› d"}|
 ||¡ |
 	¡ pÒd#di}t
| d#¡pÚdƒ}d$|› d%|› d&|› d'|› d(|› d)|› d*|› d*|› d+|› d,}|
 ||||g ¡ |
 ¡ }g }|D ]Z} |  d-¡}!| |  d.¡t
|  d/¡p!dƒ|  d0¡|  d1¡p-d2t|  d3¡p5dƒ|!durBtt|!ƒd4ƒndt
|  d5¡pKdƒt
|  d6¡pTdƒt
|  d7¡p]dƒdd2d2d8œ¡ qd9d:„ |D ƒ}"|"r¿|  |d;|"¡}#|D ]D}$|$ d.d¡}%|# |%i ¡}&|& d<¡pd|$d<< |& d=¡rž|&d= |$d=< |& d>¡rª|&d> |$d>< |$d1 d2kr½|& d?¡r½|&d? |$d1< qz||dœW  d  ƒ S 1 sÏw   Y  dS )@zÄGet customer-level lead aggregates from raw calls.

        Prefer the stored customer phone. Some integrations store the agent
        extension in agent_callinfo even for inbound calls.
        rp  rÖ  r½  r   )Úleadsr³   Ná{  CASE WHEN NULLIF(TRIM(CAST(r.customer_callinfo AS CHAR)), '') REGEXP '^[+]?[0-9]{6,}$' THEN TRIM(CAST(r.customer_callinfo AS CHAR)) WHEN NULLIF(TRIM(CAST(r.agent_callinfo AS CHAR)), '') REGEXP '^[+]?[0-9]{6,}$' THEN TRIM(CAST(r.agent_callinfo AS CHAR)) WHEN LOWER(r.direction) = 'inbound' THEN TRIM(CAST(r.agent_callinfo AS CHAR)) ELSE TRIM(CAST(r.customer_callinfo AS CHAR)) ENDrW  z) IS NOT NULLzTRIM((z)) != '')ÚinboundÚoutboundzLOWER(r.direction) = %sr^  rÜ  z&COALESCE(TRIM(s.transcript), '') != ''r¼  r²   r×  rØ  rI   rÙ  úROUND(AVG(a.quality_score), 2)ÚNULLr¾  ú]ROUND(AVG(CASE WHEN a.propensity_score IS NOT NULL THEN a.propensity_score ELSE NULL END), 2)z`SUM(CASE WHEN s.callid IS NOT NULL AND COALESCE(TRIM(s.transcript), '') != '' THEN 1 ELSE 0 END)rS   z\
                SELECT COUNT(*) AS total
                FROM (
                    SELECT z
                    FROM `z` r
                    z
                    z
                    GROUP BY z!
                ) t
            r³   z,
                SELECT
                    a…   AS lead_phone,
                    COUNT(*) AS conversations,
                    MAX(r.call_starttime) AS last_conversation,
                    SUBSTRING_INDEX(
                        GROUP_CONCAT(COALESCE(r.agentname, '-') ORDER BY r.call_starttime DESC SEPARATOR '||'),
                        '||',
                        1
                    ) AS owner_name,
                    ú+ AS avg_quality_score,
                    aø   AS avg_propensity_score,
                    SUM(
                        CASE
                            WHEN r.call_starttime IS NOT NULL AND r.call_endtime IS NOT NULL
                            THEN GREATEST(TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime), 0)
                            ELSE 0
                        END
                    ) AS total_duration_seconds,
                    SUM(CASE WHEN r.call_status = 'ANSWER' THEN 1 ELSE 0 END) AS answered_calls,
                    z+ AS transcript_calls
                FROM `ú` r
                ú
                z
                GROUP BY z`
                ORDER BY last_conversation DESC
                LIMIT %s OFFSET %s
            r“  r£   ÚconversationsÚlast_conversationrü   ú-r’  ré   Útotal_duration_secondsÚanswered_callsÚtranscript_calls)r£   r  r  rü   r’  r“  r  r  r  rû   rÿ   r   c                 S   s   g | ]}|  d ¡r|d  ‘qS )r£   ©r0   )rL   Úleadr   r   r   rV     ó    z2DatabaseHandler.get_leads_list.<locals>.<listcomp>r­   rû   rÿ   r   r  )rg   rl   rn   r„   rŒ   r]  rX   r¿  rj   rk   rº   r0   r   r¯  r®  r   )'r2   rz   rd  r¶   r·   Útranscripts_onlyr³  r[  r\  re   rl   rè  ré  rÂ  rÊ  rÉ  Úlead_phone_exprrv  r   r¼   Újoin_sarvamÚjoin_analyticsÚquality_exprÚhas_propensity_colÚpropensity_exprÚtranscript_exprÚcount_queryr½   r³   Ú
data_queryr   r÷  rˆ   Úavg_propensityr  Úcrm_mapr  r[   Ú
enrichmentr   r   r   Úget_leads_list  sà   


ø
ÿ

þ




þÿþÿýýüûúù
þ
öõìëêéèç

ÿñ€ &îzDatabaseHandler.get_leads_listc           5         sò  ˆ   ¡ i}| ¡ }|› d}|› d}	|› d}
ˆ  ||¡s(	 W d  ƒ dS ˆ  ||	¡}ˆ  ||
¡}d}|› dg}|g}|rK| d¡ | |¡ ˆ  ||||¡ dd	 |¡ }|rbd
|	› dnd}|rld
|
› dnd}|rrdnd}|o|ˆ  ||
d¡}|o…ˆ  ||
d¡}|rŠdnd}|rdnd}|r–dnd}d}|rždnd}|r¤dnd}|rªdnd}|r°dnd}|r¶dnd}|r¼dnd}|rÂdnd} |rÏˆ  ||
d¡rÏdnd}!|oÙˆ  ||
d¡}"|oâˆ  ||
d ¡}#|oëˆ  ||
d!¡}$|"rðd"nd}%|#röd#nd}&|$rüd$nd}'|rd%nd}(|r
d&nd})|rd'nd}*|rd(nd}+d)|› d*|› d+|› d,|› d-|› d.|› d/|› d0|› d1},| |,|¡ | 	¡ }-|-rH|- 
d2¡sQ	 W d  ƒ dS d g d3‘|(› ‘d4‘|)› ‘d5‘|*› ‘d6‘|+› ‘d7‘|› ‘d8‘|› ‘d9‘|› ‘d:‘|› ‘d;‘|%› ‘d<‘|&› ‘d=‘|› ‘d>‘|› ‘d?‘| › ‘d@‘|!› ‘dA‘|'› ‘dB‘|› ‘d/‘|› ‘d0‘|› ‘d0‘|› ‘dC‘¡}.| |.|¡ | ¡ }/t|- 
dD¡pÉdEƒ}0t|- 
dF¡pÓdEƒ}1ˆ  |dG|g¡}2|2 
|i ¡}3|- 
dH¡pêdI}4||3 
dJ¡pód|3 
dK¡púdI|3 
dL¡pdI|4dIkr|4n|3 
dM¡pdIt|- 
d2¡pdEƒ|- 
dN¡|- 
dO¡t|- 
dP¡p(dEƒ|- 
dQ¡dur;tt|- 
dQ¡ƒdRƒnd|0sB|1rI|0› dS|1› ndTt|- 
dU¡pRdEƒt|- 
dV¡p[dEƒ‡ fdWdX„|/D ƒdYœW  d  ƒ S 1 srw   Y  dS )ZzHGet customer-level details and call timeline for a given customer phone.rp  rÖ  r½  Nrø  ú = %sr^  r¼  r²   r×  rØ  rI   rÙ  rû  rü  r¾  Úpropensity_bandrý  z'ROUND(AVG(a.agent_speak_percentage), 0)rS   z*ROUND(AVG(a.customer_speak_percentage), 0)za.quality_scoreza.propensity_scoreza.propensity_bandz	a.summaryza.call_purposeza.objections_concernsza.sentimentÚobjection_typeza.objection_typer-  Úparameter_detectionsr.  za.parameter_scoresza.parameter_detectionsza.raw_responsez[CASE WHEN s.callid IS NOT NULL AND COALESCE(TRIM(s.transcript), '') != '' THEN 1 ELSE 0 ENDzs.transcriptzs.speaker_segmentsz
s.durationaÔ  
                SELECT
                    COUNT(*) AS total_conversations,
                    MAX(r.call_starttime) AS last_conversation,
                    MIN(r.call_starttime) AS first_conversation,
                    SUBSTRING_INDEX(
                        GROUP_CONCAT(COALESCE(r.agentname, '-') ORDER BY r.call_starttime DESC SEPARATOR '||'),
                        '||',
                        1
                    ) AS owner_name,
                    rþ  z. AS avg_propensity_score,
                    z& AS avg_talk_pct,
                    z( AS avg_listen_pct,
                    a”   AS avg_intent_score,
                    SUM(
                        CASE
                            WHEN r.call_starttime IS NOT NULL AND r.call_endtime IS NOT NULL
                            THEN GREATEST(TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime), 0)
                            ELSE 0
                        END
                    ) AS total_duration_seconds
                FROM `rÿ  r   rƒ  Útotal_conversationsa¦  
                SELECT
                    r.callid,
                    r.fileurl AS file_url,
                    r.call_starttime,
                    r.call_endtime,
                    r.call_status,
                    r.direction,
                    r.agentname,
                    r.groupname,
                    TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime) AS duration_seconds,
                    z( AS has_transcript,
                    z$ AS transcript,
                    z* AS speaker_segments,
                    z- AS transcript_duration,
                    z' AS quality_score,
                    z* AS propensity_score,
                    z) AS propensity_band,
                    z! AS summary,
                    z* AS parameter_scores,
                    z. AS parameter_detections,
                    z& AS call_purpose,
                    z- AS objections_concerns,
                    z# AS sentiment,
                    z( AS objection_type,
                    z' AS raw_response
                FROM `z<
                ORDER BY r.call_starttime DESC
            Úavg_talk_pctr   Úavg_listen_pctr­   rü   r  rû   rÿ   r   r  Úfirst_conversationr  r’  r“  ré   ú:r©  Úavg_intent_scorer  c                    s˜  g | ]È}i d |  d ¡“d|  d¡“d|  d¡“d|  d¡“d|  d¡“d|  d¡p*d“d|  d¡p2d“dt|  d¡p;d	ƒ“d
t|  d
¡ƒ“d|  d¡“d|  d¡pRd“dˆ  |  d¡¡p]g “dt|  d¡pfd	ƒ“d|  d¡duryt|  d¡pwd	ƒnd“d|  d¡durŠt|  d¡ƒnd“d|  d¡p“d“d|  d¡p›d“ˆ  |¡ˆ  |  d¡¡|  d¡p­d|  d¡p³d|  d¡p¹d|  d¡p¿dˆ  |  d¡¡dœ¥‘qS )r¢   Úcall_starttimeÚcall_endtimerÀ  r³  Ú	agentnamer  rd  Úduration_secondsr   Úhas_transcriptÚfile_urlÚ
transcriptrI   Úspeaker_segmentsÚtranscript_durationÚquality_scoreNr¾  r  Úsummaryr  Úcall_purposeÚobjections_concernsÚ	sentimentr  r.  )r-  r  r-  r.  r/  r  r.  )r0   rº   r…   r†   r¯  r;  rà   ræ  r   r   rV   J	  sb    ä
ÿ
þ
ý
ü
ûúùø	÷

öõôó$òÿïíìåÿz4DatabaseHandler.get_lead_details.<locals>.<listcomp>)r£   rû   rÿ   r   rü   r  r  r  r’  r“  rª  Úintent_scorer  rÃ  )rg   rl   rn   rŒ   r]  rX   r¿  rr   rj   rk   r0   r   rº   r   r¯  r®  )5r2   rz   r£   rd  r[  r\  re   rl   rè  ré  rÂ  rÊ  rÉ  r  rv  r   r¼   r  r  Úsummary_quality_exprr  Úhas_propensity_band_colÚsummary_propensity_exprÚ	talk_exprÚlisten_exprÚintent_exprÚcall_quality_exprÚcall_propensity_exprÚcall_propensity_band_exprÚcall_summary_exprÚcall_purpose_exprÚcall_objections_exprÚcall_sentiment_exprÚcall_objection_type_exprÚhas_param_scores_colÚhas_param_detections_colÚhas_raw_response_colÚcall_parameter_scores_exprÚcall_parameter_detections_exprÚcall_raw_response_exprÚcall_has_transcript_exprÚcall_transcript_exprÚcall_speaker_segments_exprÚcall_transcript_duration_exprÚsummary_queryr,  Úcalls_queryrÃ  Útalk_pctÚ
listen_pctÚcrm_enrichmentÚcrm_infoÚ
call_ownerr   ræ  r   Úget_lead_details¡  s$  


ø
ÿÿ

þþÿþÿý
öõôóòêéè—
kõôóòñðïîíìëêéèçæåäã ÿ
ãï &éz DatabaseHandler.get_lead_detailsc              
      sb  ˆ   ¡ £}| ¡ }|› d}	ˆ  ||	¡s&ˆ  |||||||¡W  d  ƒ S g }
g }|rrd|v rB|d durB|
 d¡ | |d ¡ d|v rR|
 d¡ | |d ¡ d|v rb|
 d¡ | |d ¡ d	|v rr|
 d
¡ | |d	 ¡ |
r{dd |
¡ nd}d|	› d|› d}| ||g¡ | ||¡ | ¡ }‡ fdd„|D ƒW  d  ƒ S 1 sªw   Y  dS )z!Get calls with optional filteringrD  Nr¡   r±   Úsales_intentúsales_intent = %sre  úcall_starttime >= %srf  úcall_starttime <= %sr¼  r²   rI   ú 
                SELECT * FROM `z`
                zp
                ORDER BY call_starttime DESC, call_endtime DESC
                LIMIT %s OFFSET %s
            c                    rã  r   rä  rå  ræ  r   r   rV   š	  rç  z-DatabaseHandler.get_calls.<locals>.<listcomp>)	rg   rl   rn   rñ  rŒ   rX   rY  rj   r   )r2   rz   rÑ  r¶   r·   r[  r\  re   rl   rm   rv  r   r¼   rŽ   rÃ  r   ræ  r   Ú	get_callso	  s@   

û



ÿþ$×zDatabaseHandler.get_callsc                 C   sL  |   ¡ ˜}| ¡ }|› d}|  ||¡s$|  |||||¡W  d  ƒ S g }g }	|rpd|v r@|d dur@| d¡ |	 |d ¡ d|v rP| d¡ |	 |d ¡ d|v r`| d¡ |	 |d ¡ d	|v rp| d
¡ |	 |d	 ¡ |rydd |¡ nd}
d|› d|
› }| ||	¡ | ¡ }|r“|d ndW  d  ƒ S 1 sŸw   Y  dS )z)Get total count of calls matching filtersrD  Nr¡   r±   rQ  rR  re  rS  rf  rT  r¼  r²   rI   rE  ú` rG  r   )rg   rl   rn   rò  rŒ   rX   rj   rk   )r2   rz   rÑ  r[  r\  re   rl   rm   rv  r   r¼   rŽ   r‘   r   r   r   Úget_calls_countœ	  s6   

û



$ßzDatabaseHandler.get_calls_countc                 C   s   |   ¡ :}| ¡ }|› d}|  ||¡s!|  ||¡W  d  ƒ S d|› d}| ||f¡ | ¡ }|  |¡W  d  ƒ S 1 sAw   Y  dS )zGet specific call by IDrD  NúSELECT * FROM `z` WHERE callid = %s LIMIT 1)rg   rl   rn   rÈ  rj   rk   rC  )r2   rz   r¢   re   rl   rm   rŽ   rÆ  r   r   r   Úget_call_by_idÁ	  s   


û$õzDatabaseHandler.get_call_by_idc                 C   sŒ   |   ¡ 8}| ¡ }|› d}| d|› d¡ | ¡ s$	 W d  ƒ dS d|› d}| ||f¡ | ¡ W  d  ƒ S 1 s?w   Y  dS )z(Get transcript from sarvamresponse tablerÖ  rQ  rR  Nz{
                SELECT transcript, language, raw_response, speaker_segments, num_speakers, duration
                FROM `zq`
                WHERE callid = %s
                ORDER BY created_at DESC
                LIMIT 1
            rT  )r2   rz   r¢   re   rl   rm   rŽ   r   r   r   Úget_call_transcriptÐ	  s   

ø
þ$îz#DatabaseHandler.get_call_transcriptrP   c                 C   s   | j |d|d||dS )zGet most recent callsNr   )rÑ  r¶   r·   r[  r\  )rV  )r2   rz   r¶   r[  r\  r   r   r   Úget_recent_callsæ	  s   z DatabaseHandler.get_recent_callsc              
      s¶   ˆ   ¡ M}| ¡ }|› d}ˆ  ||¡s%ˆ  ||||||¡W  d  ƒ S d|› d}	d|› d}
| |	|
|
|
|
|
|f¡ | ¡ }‡ fdd„|D ƒW  d  ƒ S 1 sTw   Y  dS )zSearch calls by query stringrD  NrU  aI  `
                WHERE
                    callid LIKE %s OR
                    customer_name LIKE %s OR
                    agent_name LIKE %s OR
                    summary LIKE %s OR
                    transcripts LIKE %s
                ORDER BY call_starttime DESC, call_endtime DESC
                LIMIT %s
            rô  c                    rã  r   rä  rå  ræ  r   r   rV   
  rç  z0DatabaseHandler.search_calls.<locals>.<listcomp>)rg   rl   rn   rö  rj   r   )r2   rz   rŽ   r¶   r[  r\  re   rl   rm   Úsearch_queryrõ  rÃ  r   ræ  r   Úsearch_callsê	  s    

ûÿÿ$èzDatabaseHandler.search_callsc                 C   sò   |   ¡ k}| ¡ }|› d}g }g }| ¡ D ]\}	}
|	dv r'|
r%t |
¡nd}
| |	› d¡ | |
¡ q|s@	 W d  ƒ dS | d¡ | t ¡ ¡ | |¡ d|› dd	 |¡› d
}| 	||¡ |j
dkW  d  ƒ S 1 srw   Y  dS )zUpdate call recordrD  r<  Nr  Fzupdated_at = %sú
                UPDATE `z`
                SET rq  z/
                WHERE callid = %s
            r   )rg   rl   r6  r—   r˜   rŒ   r   ÚnowrX   rj   r›   )r2   rz   r¢   r:  re   rl   rm   Úset_clausesr   r>   r   rŽ   r   r   r   Úupdate_call
  s0   

î

ÿþ$àzDatabaseHandler.update_callc              	   C   s¸   |   ¡ N}| ¡ }|› d}| d|› d¡ | ¡ s-t d|› d¡ 	 W d  ƒ dS d|› d	}| ||||r?t |¡ndt 	¡ f¡ |j
d
kW  d  ƒ S 1 sUw   Y  dS )z/Save conversation summary to call_history tableÚ_call_historyrQ  rR  úTable ú does not existNFú
                INSERT INTO `zy`
                (business_id, callid, transfer_reason, created_at)
                VALUES (%s, %s, %s, %s)
            r   )rg   rl   rj   rk   rF   rG   r—   r˜   r   r`  r›   )r2   rz   r¢   Útransfer_reasonre   rl   rm   rŽ   r   r   r   Úsave_conversation_summary*
  s&   

÷ÿü$èz)DatabaseHandler.save_conversation_summaryc                 C   sv  |   ¡ +}| ¡ }|› d}| d|› d¡ | ¡ s.t d|› d¡ g W  d  ƒ S d|› d|› d	}zÔ| |¡ | ¡ }g }|D ]¼}| d
d¡}	| d¡}
|
du r\|	rZdnd}
| d¡}|r“zddl}t	|t
ƒrr| |¡n|}|rzt|ƒnd}W n%   |	rtdd„ |	 d¡D ƒƒnd}Y n|	r¡tdd„ |	 d¡D ƒƒnd}| dd¡}d| dd¡› dt
|ƒ dd¡› d}| | d¡| d¡||	| d¡|
|| d¡pØ| dd¡| d¡rä| d¡ ¡ nd| d ¡rñ| d ¡ ¡ ndt| d!¡ƒ|| d"¡d#œ¡ qE|W W  d  ƒ S  ty/ } zt d$|› ¡ g W  Y d}~W  d  ƒ S d}~ww 1 s4w   Y  dS )%z;Get all transcripts with metadata from sarvamresponse tablerÖ  rQ  rR  rd  re  Na6  
                SELECT
                    s.callid as transcript_id,
                    s.callid,
                    s.transcript as full_transcript,
                    s.speaker_segments,
                    s.num_speakers,
                    s.duration,
                    s.language,
                    s.created_at,
                    s.updated_at,
                    c.call_starttime,
                    c.call_endtime,
                    c.customer_callinfo,
                    c.agentname,
                    TIMESTAMPDIFF(SECOND, c.call_starttime, c.call_endtime) as duration_seconds,
                    CASE
                        WHEN s.transcript IS NOT NULL AND s.transcript != '' THEN TRUE
                        ELSE FALSE
                    END as stored_in_vectordb
                FROM `z` s
                LEFT JOIN `zX_calls` c ON s.callid = c.callid
                ORDER BY s.created_at DESC
            Úfull_transcriptrI   Únum_speakersré   r   r)  c                 S   ó   g | ]}|  ¡ r|‘qS r   rz  ©rL   Úliner   r   r   rV   ˆ
  ó    z3DatabaseHandler.get_transcripts.<locals>.<listcomp>Ú
c                 S   rk  r   rz  rl  r   r   r   rV   ‹
  rn  Úcustomer_callinfoÚunknownÚCall_r¢   rÔ   rá  z.txtÚtranscript_idÚlanguageÚdurationr%  r   r€   Ústored_in_vectordbr$  )rs  r¢   Úfilenameri  rt  rj  Únum_segmentsru  r   r€   rv  Úcustomer_nameÚ
agent_namezError fetching transcripts: )rg   rl   rj   rk   rF   rG   r   r0   r—   r*  r6   r+  rY   r¬  r   rŒ   r   r…   rE   rc   )r2   rz   re   rl   ré  rŽ   rÅ  r‘   ÚtÚtranscript_textrj  r)  r—   Úsegments_datarx  Úcustomer_inforw  rf   r   r   r   Úget_transcriptsJ
  sr   
øìë


$ &
ó«W§€W©zDatabaseHandler.get_transcriptsc                 C   s   |   ¡ Â}| ¡ }| d|› df¡ | ¡ du}|r%d|› d|› d}nd|› d}|| dd	¡| d
¡| d¡| d¡| dd¡| d¡| d¡| dd¡| d¡r\t | d¡¡nd| d¡| d¡| d¡| d¡| d¡| d¡| d¡| d¡| d¡| d¡| d¡g}|r’| |¡ | |t|ƒ¡ | d|› d |f¡ | 	¡  |j
}	| d!g ¡}
|
r½|  |||
||¡ |	W  d  ƒ S 1 sÉw   Y  dS )"z0Save call analytics to {bid}_callanalytics tablezý
                SELECT 1
                FROM information_schema.columns
                WHERE table_schema = DATABASE()
                  AND table_name = %s
                  AND column_name = 'call_starttime'
                LIMIT 1
                r½  Nz"
                    INSERT INTO `aÛ  _callanalytics` 
                    (callid, bid, summary, call_purpose, objections_concerns, objection_type,
                     quality_score, sentiment, analysis_model, raw_response,
                     parameter_scores, parameter_detections, total_possible_score,
                     parameters_not_applicable, talk_listen_ratio, agent_talk_time,
                     customer_talk_time, dead_air_percentage, agent_speak_percentage,
                     customer_speak_percentage, talk_listen_assessment, call_starttime)
                    SELECT
                        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 
                        call_starttime 
                    FROM `aÎ  _raw_calls` WHERE callid = %s
                    ON DUPLICATE KEY UPDATE
                        summary = VALUES(summary),
                        call_purpose = VALUES(call_purpose),
                        objections_concerns = VALUES(objections_concerns),
                        objection_type = VALUES(objection_type),
                        quality_score = VALUES(quality_score),
                        sentiment = VALUES(sentiment),
                        analysis_model = VALUES(analysis_model),
                        raw_response = VALUES(raw_response),
                        parameter_scores = VALUES(parameter_scores),
                        parameter_detections = VALUES(parameter_detections),
                        total_possible_score = VALUES(total_possible_score),
                        parameters_not_applicable = VALUES(parameters_not_applicable),
                        talk_listen_ratio = VALUES(talk_listen_ratio),
                        agent_talk_time = VALUES(agent_talk_time),
                        customer_talk_time = VALUES(customer_talk_time),
                        dead_air_percentage = VALUES(dead_air_percentage),
                        agent_speak_percentage = VALUES(agent_speak_percentage),
                        customer_speak_percentage = VALUES(customer_speak_percentage),
                        talk_listen_assessment = VALUES(talk_listen_assessment),
                        updated_at = CURRENT_TIMESTAMP
                a5  _callanalytics` 
                    (callid, bid, summary, call_purpose, objections_concerns, objection_type,
                     quality_score, sentiment, analysis_model, raw_response,
                     parameter_scores, parameter_detections, total_possible_score,
                     parameters_not_applicable, talk_listen_ratio, agent_talk_time,
                     customer_talk_time, dead_air_percentage, agent_speak_percentage,
                     customer_speak_percentage, talk_listen_assessment)
                    VALUES
                    (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                    ON DUPLICATE KEY UPDATE
                        summary = VALUES(summary),
                        call_purpose = VALUES(call_purpose),
                        objections_concerns = VALUES(objections_concerns),
                        objection_type = VALUES(objection_type),
                        quality_score = VALUES(quality_score),
                        sentiment = VALUES(sentiment),
                        analysis_model = VALUES(analysis_model),
                        raw_response = VALUES(raw_response),
                        parameter_scores = VALUES(parameter_scores),
                        parameter_detections = VALUES(parameter_detections),
                        total_possible_score = VALUES(total_possible_score),
                        parameters_not_applicable = VALUES(parameters_not_applicable),
                        talk_listen_ratio = VALUES(talk_listen_ratio),
                        agent_talk_time = VALUES(agent_talk_time),
                        customer_talk_time = VALUES(customer_talk_time),
                        dead_air_percentage = VALUES(dead_air_percentage),
                        agent_speak_percentage = VALUES(agent_speak_percentage),
                        customer_speak_percentage = VALUES(customer_speak_percentage),
                        talk_listen_assessment = VALUES(talk_listen_assessment),
                        updated_at = CURRENT_TIMESTAMP
                rz   Ú7987r,  r-  r.  r  ÚNoner+  r/  Úanalysis_modelzaws-novar.  r-  r  Útotal_possible_scoreÚparameters_not_applicablerª  Úagent_talk_timeÚcustomer_talk_timeÚdead_air_percentageÚagent_speak_percentageÚcustomer_speak_percentageÚtalk_listen_assessmentúUPDATE `z,_raw_calls` SET status = 3 WHERE callid = %sÚclassified_objections)rg   rl   rj   rk   r0   r—   r˜   rŒ   Útuplerí   r    Ú_save_classified_objections)r2   rz   r¢   Úanalytics_datare   rl   Úhas_call_starttime_colrŽ   Úquery_paramsÚanalytics_idrŒ  r   r   r   Úsave_call_analytics§
  sh   

÷ÿ
õ#ÿ"


ë

þ$…z#DatabaseHandler.save_call_analyticsrm   rq   c                 C   ro   )NzÓ
            SELECT 1
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = %s
              AND column_name = %s
            LIMIT 1
            ri   rp   r   r   r   r¿  &  s
   ÷z!DatabaseHandler._table_has_columnc              
   C   s”   |› d}ddddddœ}|   ¡ /}| ¡ }| ¡ D ]\}}|  |||¡s3| d|› d|› d	|› ¡ q| ¡  W d
  ƒ d
S 1 sCw   Y  d
S )zLAdd propensity columns to {bid}_callanalytics when missing (lazy migration).r½  zINT DEFAULT NULLzJSON DEFAULT NULLzVARCHAR(16) DEFAULT NULLzVARCHAR(64) DEFAULT NULL)r¾  Úpropensity_parameter_scoresÚpropensity_parameter_detectionsr  Úpropensity_modelzALTER TABLE `z` ADD COLUMN `rW  N)rg   rl   r6  r¿  rj   rí   )r2   rz   rN  Úcolumnsre   rl   ÚcolÚcol_typer   r   r   Ú#ensure_propensity_analytics_columns4  s   
û
€
"ûz3DatabaseHandler.ensure_propensity_analytics_columnsr¢   r:  c                 C   s–   |sdS |   |¡ |  ¡ 4}| ¡ }| d|› d| d¡| d¡| d¡| d¡| d¡|f¡ | ¡  |jd	kW  d
  ƒ S 1 sDw   Y  d
S )zVUpdate propensity fields on an existing analytics row (does not touch quality fields).Fr_  ae  _callanalytics`
                SET propensity_score = %s,
                    propensity_parameter_scores = %s,
                    propensity_parameter_detections = %s,
                    propensity_band = %s,
                    propensity_model = %s,
                    updated_at = CURRENT_TIMESTAMP
                WHERE callid = %s
                r¾  r”  r•  r  r–  r   N)rš  rg   rl   rj   r0   rí   r›   )r2   rz   r¢   r:  re   rl   r   r   r   Úsave_propensity_analyticsE  s(   

ÿúõ$éz)DatabaseHandler.save_propensity_analyticsc                 C   s"   |› d}|   ||d¡r	 dS dS )Nr½  r¾  zm, AVG(CASE WHEN a.propensity_score IS NOT NULL THEN a.propensity_score ELSE NULL END) as avg_propensity_scorez, NULL as avg_propensity_score)r¿  )r2   rl   rz   rN  r   r   r   r­  c  s
   
ÿz#DatabaseHandler._avg_propensity_sqlc                 C   sT   |   ¡ }| ¡ }d|› d}| |¡ | ¡  W d  ƒ dS 1 s#w   Y  dS )z1Create the {bid}_bant table if it does not exist.ú-
                CREATE TABLE IF NOT EXISTS `aõ  _bant` (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    callid VARCHAR(255) UNIQUE,
                    bid VARCHAR(50),
                    profile_json LONGTEXT,
                    profile_summary TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    INDEX (callid)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            N©rg   rl   rj   rí   )r2   rz   re   rl   rŽ   r   r   r   Úensure_bant_tablel  s   
ÿ

"ñz!DatabaseHandler.ensure_bant_tablec           	      C   s¢   |s|sdS |   |¡ d}|r"zt |¡}W n ty!   d}Y nw |  ¡ !}| ¡ }d|› d}| |||||f¡ | ¡  W d  ƒ dS 1 sJw   Y  dS )z&Save BANT profile to {bid}_bant table.Nrf  a;  _bant` (callid, bid, profile_json, profile_summary)
                VALUES (%s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE
                    profile_json = VALUES(profile_json),
                    profile_summary = VALUES(profile_summary),
                    updated_at = CURRENT_TIMESTAMP
            )rž  r—   r˜   rE   rg   rl   rj   rí   )	r2   rz   r¢   Úprofiler,  Úprofile_jsonre   rl   rŽ   r   r   r   Úsave_bant_analysis  s.   
ÿ
ÿü
"ðz"DatabaseHandler.save_bant_analysisc                 C   s´   z|   |¡ W n
 ty   Y dS w |  ¡ :}| ¡ }d|› d}| ||f¡ | ¡ }|s7	 W d  ƒ dS |  | d¡¡}|| d¡pFddœW  d  ƒ S 1 sSw   Y  dS )zFetch BANT profile for a call.NzL
                SELECT profile_json, profile_summary
                FROM `zM_bant`
                WHERE callid = %s
                LIMIT 1
            r   Úprofile_summaryrI   )rŸ  r,  )rž  rE   rg   rl   rj   rk   r†   r0   )r2   rz   r¢   re   rl   rŽ   r‘   rŸ  r   r   r   Úget_bant_analysisŸ  s(   ÿ
þõþ$òz!DatabaseHandler.get_bant_analysisc           
      C   s¶   z?d}|  |||f¡ |r=d}|D ]}|  |||| d¡| d¡| dd¡f¡ q| ¡  t dt|ƒ› d|› ¡ W d
S W d
S  tyZ }	 zt d	|	› ¡ W Y d
}	~	d
S d
}	~	ww )z3Save classified objections to call_objections tablezh
                DELETE FROM call_objections
                WHERE bid = %s AND callid = %s
            zÕ
                    INSERT INTO call_objections
                    (bid, callid, classification_id, objection_text, confidence, created_at)
                    VALUES (%s, %s, %s, %s, %s, NOW())
                Úclassification_idÚobjection_textÚ
confidenceÚmediumzSaved z  classified objections for call z$Error saving classified objections: N)rj   r0   rí   rF   r  rY   rE   rc   )
r2   rz   r¢   rŒ  rl   re   Údelete_queryÚinsert_queryÚ	objectionrf   r   r   r   rŽ  ¹  s(   
û ï€ÿz+DatabaseHandler._save_classified_objectionsc                 C   sz   |   ¡ /}| ¡ }d|› d}| ||f¡ | ¡ }|r*| d¡r*|  |d ¡|d< |W  d  ƒ S 1 s6w   Y  dS )z!Get analytics for a specific callrU  zV_callanalytics`
                WHERE callid = %s
                LIMIT 1
            r.  N)rg   rl   rj   rk   r0   r†   )r2   rz   r¢   re   rl   rŽ   r‘   r   r   r   Úget_call_analyticsÛ  s   
ÿ$ñz"DatabaseHandler.get_call_analyticsc                 C   sŒ   |   ¡ 8}| ¡ }d|› d|› d|› d}| ||f¡ | ¡ }|D ]}| d¡r2|  |d ¡|d< q"|W  d  ƒ S 1 s?w   Y  dS )z4Get calls that have transcripts but no analytics yeta€  
                SELECT
                    r.callid,
                    r.bid,
                    r.agentname,
                    r.groupname,
                    r.call_starttime,
                    r.call_endtime,
                    s.transcript,
                    s.speaker_segments,
                    s.num_speakers,
                    s.duration
                FROM `z*_raw_calls` r
                INNER JOIN `zE_sarvamresponse` s ON r.callid = s.callid
                LEFT JOIN `a+  _callanalytics` a ON r.callid = a.callid
                WHERE r.call_status = 'ANSWER'
                  AND s.transcript IS NOT NULL
                  AND s.transcript != ''
                  AND a.callid IS NULL
                ORDER BY r.call_starttime DESC
                LIMIT %s
            r)  N)rg   rl   rj   r   r0   r†   )r2   rz   r¶   re   rl   rŽ   rk  r‘   r   r   r   Úget_calls_for_analysisî  s"   
ôóò
€$Þz&DatabaseHandler.get_calls_for_analysisc              
   C   s”   |   ¡ <}| ¡ }|  |||||¡\}	}
|  ||¡}d|› d|› d|› d|	› d	}| ||
¡ | ¡ }|r7|ni W  d  ƒ S 1 sCw   Y  dS )zGet overall analytics summaryz°
                SELECT
                    COUNT(DISTINCT a.callid) as total_analyzed_calls,
                    AVG(a.quality_score) as avg_quality_score
                    a|  ,
                    SUM(CASE WHEN a.sentiment = 'positive' THEN 1 ELSE 0 END) as positive_calls,
                    SUM(CASE WHEN a.sentiment = 'neutral' THEN 1 ELSE 0 END) as neutral_calls,
                    SUM(CASE WHEN a.sentiment = 'negative' THEN 1 ELSE 0 END) as negative_calls,
                    COUNT(DISTINCT r.groupname) as total_locations
                FROM `ú._callanalytics` a
                INNER JOIN `ú5_raw_calls` r ON a.callid = r.callid
                rƒ  N)rg   rl   rj  r­  rj   rk   )r2   rz   rd  re  rf  r[  r\  re   rl   r¶  r   r¸  rŽ   r‘   r   r   r   Úget_analytics_overview  s"   
ü	÷
öõ
$éz&DatabaseHandler.get_analytics_overviewc              	   C   s|   |   ¡ 0}| ¡ }| j|||||dgd\}	}
d|› d|› d|	› d}| ||
¡ | ¡ W  d  ƒ S 1 s7w   Y  dS )z&Get sentiment distribution by locationza.sentiment IS NOT NULL©rg  z¢
                SELECT
                    r.groupname as location,
                    a.sentiment,
                    COUNT(*) as count
                FROM `r­  r®  zq
                GROUP BY r.groupname, a.sentiment
                ORDER BY r.groupname, a.sentiment
            N©rg   rl   rj  rj   r   ©r2   rz   rd  re  rf  r[  r\  re   rl   r¶  r   rŽ   r   r   r   Úget_sentiment_by_location/  s   

ÿûúù$ìz)DatabaseHandler.get_sentiment_by_locationc                 C   óv   |   ¡ -}| ¡ }|  |||||¡\}	}
d|› d|› d|	› d}| ||
¡ | ¡ W  d  ƒ S 1 s4w   Y  dS )z%Get average quality score by locationaC  
                SELECT
                    r.groupname as location,
                    AVG(a.quality_score) as avg_quality_score,
                    MIN(a.quality_score) as min_quality_score,
                    MAX(a.quality_score) as max_quality_score,
                    COUNT(*) as call_count
                FROM `r­  r®  zb
                GROUP BY r.groupname
                ORDER BY avg_quality_score DESC
            Nr±  r²  r   r   r   Úget_quality_by_locationG  s   
ùø	÷$ìz'DatabaseHandler.get_quality_by_locationc                 C   r´  )z"Get average quality score by agenta  
                SELECT
                    COALESCE(NULLIF(TRIM(r.agentname), ''), NULLIF(TRIM(r.agent_callinfo), ''), 'Unknown') as agent,
                    COALESCE(NULLIF(TRIM(r.agentname), ''), NULLIF(TRIM(r.agent_callinfo), ''), 'Unknown') as agent_display,
                    AVG(a.quality_score) as avg_quality_score,
                    MIN(a.quality_score) as min_quality_score,
                    MAX(a.quality_score) as max_quality_score,
                    COUNT(*) as call_count
                FROM `r­  r®  zƒ
                GROUP BY agent, agent_display
                ORDER BY avg_quality_score DESC
                LIMIT 6
            Nr±  r²  r   r   r   Úget_quality_by_agent_  s   
ø	÷
ö$êz$DatabaseHandler.get_quality_by_agentc                 C   s6  | j |||||dgdd\}}d|› d|› d|› d}	|  ¡ }
|
 ¡ }| |	|¡ | ¡ p/g }W d  ƒ n1 s:w   Y  g }t|d	ƒD ]R\}}| d
¡}| ||d t|d p[dƒt|d pbdƒt|d pidƒt	|d ppdƒt	|d pwdƒt	|d p~dƒt	|d p…dƒt
|dƒr| ¡ nt|p“dƒdœ
¡ qF|S )zHReturn per-agent scoring leaderboard with full stats and date filtering.za.quality_score IS NOT NULLT)rg  rh  aß  
            SELECT
                COALESCE(NULLIF(r.agentname, ''), 'Unknown') AS agent_name,
                ROUND(AVG(a.quality_score), 1)  AS avg_score,
                ROUND(MIN(a.quality_score), 1)  AS min_score,
                ROUND(MAX(a.quality_score), 1)  AS max_score,
                COUNT(*)                         AS total_calls,
                SUM(CASE WHEN a.quality_score >= 85 THEN 1 ELSE 0 END) AS high_calls,
                SUM(CASE WHEN a.quality_score >= 60
                          AND a.quality_score < 85 THEN 1 ELSE 0 END)  AS mid_calls,
                SUM(CASE WHEN a.quality_score < 60  THEN 1 ELSE 0 END) AS low_calls,
                MAX(r.call_starttime)            AS last_call
            FROM `z*_callanalytics` a
            INNER JOIN `z1_raw_calls` r ON a.callid = r.callid
            zM
            GROUP BY agent_name
            ORDER BY avg_score DESC
        Nr–   Ú	last_callrz  Ú	avg_scorer   Ú	min_scorer2  r„  Ú
high_callsÚ	mid_callsÚ	low_callsr   rI   )
Úrankrz  r¸  r¹  r2  r„  rº  r»  r¼  r·  )rj  rg   rl   rj   r   rë   r0   rŒ   r¯  rº   r   r   r6   )r2   rz   rd  re  rf  r[  r\  r»   r   rŽ   re   rl   r   r‘   r½  rˆ   r·  r   r   r   Úget_agent_leaderboardy  sJ   
ù
ôóò
ý

öz%DatabaseHandler.get_agent_leaderboardc              	   C   s~   |   ¡ 1}| ¡ }| j|||||ddgd\}	}
d|› d|› d|	› d}| ||
¡ | ¡ W  d  ƒ S 1 s8w   Y  dS )	z(Get frequency of different call purposesza.call_purpose IS NOT NULLza.call_purpose != ''r°  zx
                SELECT
                    a.call_purpose,
                    COUNT(*) as count
                FROM `r­  r®  zr
                GROUP BY a.call_purpose
                ORDER BY count DESC
                LIMIT 20
            Nr±  r²  r   r   r   Úget_call_purpose_frequency®  s(   

ú	üûú$çz*DatabaseHandler.get_call_purpose_frequencyc                 C   rs   )Na|  
                CREATE TABLE IF NOT EXISTS `stt_pipeline_bid_config` (
                    `bid`               VARCHAR(100) NOT NULL,
                    `enabled`           TINYINT(1)   NOT NULL DEFAULT 0,
                    `raw_calls_id_col`  VARCHAR(100) NOT NULL DEFAULT 'id',
                    `raw_calls_url_col` VARCHAR(100) NOT NULL DEFAULT 'recording_url',
                    `batch_size`        INT          NOT NULL DEFAULT 10,
                    `poll_interval_s`   INT          NOT NULL DEFAULT 30,
                    `notes`             TEXT         NULL,
                    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    `updated_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    PRIMARY KEY (`bid`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            rt   ru   r   r   r   Ú$ensure_stt_pipeline_bid_config_tableÏ  s   
"þz4DatabaseHandler.ensure_stt_pipeline_bid_config_tablec                 C   sR   |   ¡  |  ¡ }| ¡ }| d¡ | ¡ pg W  d   ƒ S 1 s"w   Y  d S )Nz2SELECT * FROM stt_pipeline_bid_config ORDER BY bid)rÀ  rg   rl   rj   r   ru   r   r   r   Úget_stt_pipeline_bid_configsá  s   


$ýz,DatabaseHandler.get_stt_pipeline_bid_configsc                 C   sR   |   ¡  |  ¡ }| ¡ }| d|f¡ | ¡ W  d   ƒ S 1 s"w   Y  d S )Nz4SELECT * FROM stt_pipeline_bid_config WHERE bid = %s)rÀ  rg   rl   rj   rk   ©r2   rz   re   rl   r   r   r   Úget_stt_pipeline_bid_configè  s   
$ýz+DatabaseHandler.get_stt_pipeline_bid_configc           	   	      sÌ   |   ¡  h d£‰ ‡ fdd„| ¡ D ƒ}|sd S d dd„ |D ƒ¡}d dgt|ƒ ¡}d dd„ |D ƒ¡}|  ¡ #}| ¡ }| d	|› d
|› d|› |gt| ¡ ƒ ¡ W d   ƒ d S 1 s_w   Y  d S )N>   ÚnotesÚenabledÚ
batch_sizeÚpoll_interval_sÚraw_calls_id_colÚraw_calls_url_colc                    s   i | ]\}}|ˆ v r||“qS r   r   ©rL   ÚkrU   ©Úallowedr   r   Ú
<dictcomp>ó  r	  zBDatabaseHandler.upsert_stt_pipeline_bid_config.<locals>.<dictcomp>rq  c                 s   ó    | ]	}d |› d V  qdS ©rF  Nr   ©rL   rË  r   r   r   rN   ö  rO   zADatabaseHandler.upsert_stt_pipeline_bid_config.<locals>.<genexpr>rr  c                 s   ó"    | ]}d |› d|› dV  qdS ©rF  ú` = VALUES(`ú`)Nr   rÑ  r   r   r   rN   ø  s   €  z*INSERT INTO stt_pipeline_bid_config (bid, z) VALUES (%s, ú) ON DUPLICATE KEY UPDATE )	rÀ  r6  rX   rY   rg   rl   rj   rø   rK  )	r2   rz   r:  râ   Úcol_listÚph_listÚdup_setre   rl   r   rÌ  r   Úupsert_stt_pipeline_bid_configï  s&   
ÿþü"þz.DatabaseHandler.upsert_stt_pipeline_bid_configrÅ  c                 C   sV   |   ¡  |  ¡ }| ¡ }| d||rdndf¡ W d   ƒ d S 1 s$w   Y  d S )NztINSERT INTO stt_pipeline_bid_config (bid, enabled) VALUES (%s, %s) ON DUPLICATE KEY UPDATE enabled = VALUES(enabled)r–   r   )rÀ  rg   rl   rj   )r2   rz   rÅ  re   rl   r   r   r   Útoggle_stt_pipeline_bid  s   
ý"þz'DatabaseHandler.toggle_stt_pipeline_bidc              
   C   s    z5|   ¡ !}| ¡ }|r| d|f¡ n| d¡ | ¡ pg }W d  ƒ n1 s)w   Y  dd„ |D ƒW S  tyO } zt d|¡ i W  Y d}~S d}~ww )zMReturn job counts by status from stt_jobs table. Returns {} if table missing.zKSELECT status, COUNT(*) as cnt FROM stt_jobs WHERE bid = %s GROUP BY statusz<SELECT status, COUNT(*) as cnt FROM stt_jobs GROUP BY statusNc                 S   s   i | ]	}|d  |d “qS )r¡   Úcntr   ©rL   Úrr   r   r   rÎ    s    z5DatabaseHandler.get_stt_job_stats.<locals>.<dictcomp>zget_stt_job_stats failed: %s)rg   rl   rj   r   rE   rF   rG   )r2   rz   re   rl   r   Úexcr   r   r   Úget_stt_job_stats  s"   
þ
÷
€þz!DatabaseHandler.get_stt_job_statsc                 C   sV   |   ¡ }| ¡ }| dd¡ | ¡ pg }W d  ƒ n1 sw   Y  dd„ |D ƒS )z9Return all bids that have a *_raw_calls table in this DB.zgSELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE %s)z%_raw_callsNc                 S   s   g | ]
}|d    dd¡‘qS )Ú
TABLE_NAMErp  rI   )r   rÝ  r   r   r   rV   (  r|  z>DatabaseHandler.discover_stt_raw_call_bids.<locals>.<listcomp>©rg   rl   rj   r   ©r2   re   rl   r   r   r   r   Údiscover_stt_raw_call_bids  s   
ýùz*DatabaseHandler.discover_stt_raw_call_bidsc                 C   sÒ   |   ¡ [}| ¡ }g d¢}	g }
|r|	 d¡ |
 |¡ |r'|	 d¡ |
 |¡ |r3|	 d¡ |
 |¡ |  |	|
||¡ dd |	¡ }d|› d|› d	|› d
}| ||
¡ | ¡ W  d  ƒ S 1 sbw   Y  dS )z.Get frequency of different concerns/objections)z!a.objections_concerns IS NOT NULLza.objections_concerns != ''z*a.objections_concerns != 'None identified'za.objections_concerns != 'None'r^  r`  rb  rc  r²   z¥
                SELECT
                    a.objections_concerns,
                    a.objection_type,
                    COUNT(*) as count
                FROM `r­  r®  z‹
                GROUP BY a.objections_concerns, a.objection_type
                ORDER BY count DESC
                LIMIT 20
            N©rg   rl   rŒ   r]  rX   rj   r   ©r2   rz   rd  re  rf  r[  r\  re   rl   ri  r   r¶  rŽ   r   r   r   Úget_concerns_frequency*  s2   






ûúù$Üz&DatabaseHandler.get_concerns_frequencyc                 C   sÖ   |   ¡ ]}| ¡ }g }	g }
|r|	 d¡ |
 |¡ |r%|	 d¡ |
 |¡ |r1|	 d¡ |
 |¡ |  |	|
||¡ |	rBdd |	¡ nd}d|› d|› d	|› d
}| ||
¡ | ¡ W  d  ƒ S 1 sdw   Y  dS )z$Get busiest locations by call volumer^  r`  rb  rc  r²   rI   aI  
                SELECT
                    r.groupname as location,
                    COUNT(DISTINCT a.callid) as analyzed_calls,
                    COUNT(DISTINCT r.callid) as total_calls,
                    ROUND(COUNT(DISTINCT a.callid) * 100.0 / COUNT(DISTINCT r.callid), 2) as analysis_percentage
                FROM `r  z9_callanalytics` a ON r.callid = a.callid
                zx
                GROUP BY r.groupname
                ORDER BY analyzed_calls DESC
                LIMIT 20
            Nrå  ræ  r   r   r   Úget_busy_locationsR  s2   






úùø$àz"DatabaseHandler.get_busy_locationsc                 C   s¢   |   ¡ C}| ¡ }dg}|g}	|r| d¡ |	 |¡ |  ||	||¡ dd |¡ }
d|› d|› d|
› d}| ||	¡ | ¡ W  d	  ƒ S 1 sJw   Y  d	S )
z/Get all calls with a specific objection/concernza.objections_concerns = %sr^  rc  r²   a/  
                SELECT
                    a.callid,
                    r.groupname as location,
                    a.call_purpose,
                    a.sentiment,
                    a.quality_score,
                    a.objections_concerns,
                    a.created_at
                FROM `r­  r®  zR
                ORDER BY a.created_at DESC
                LIMIT 100
            Nrå  )r2   rz   rª  rd  r[  r\  re   rl   ri  r   r¶  rŽ   r   r   r   Úget_calls_by_objectionv  s&   


	÷
öõ$âz&DatabaseHandler.get_calls_by_objectionc                 C   ó\   |   ¡  }| ¡ }d|› d}| ||f¡ | ¡  |jW  d  ƒ S 1 s'w   Y  dS )z+Delete transcript from sarvamresponse tablezDELETE FROM `ú"_sarvamresponse` WHERE callid = %sN©rg   rl   rj   rí   r›   ©r2   rz   r¢   re   rl   rŽ   r   r   r   Údelete_transcript˜  ó   
$ûz!DatabaseHandler.delete_transcriptc                 C   rê  )z2Reset transcription_status to 0 in raw_calls tabler‹  z:_raw_calls` SET transcription_status = 0 WHERE callid = %sNrì  rí  r   r   r   Úreset_transcription_status¡  rï  z*DatabaseHandler.reset_transcription_statusc                 C   s  |   ¡ ~}| ¡ }d|› d}| ||f¡ | ¡ }|r |d s'td|› ƒ‚|  |d ¡}	|	r5t|	tƒs<td|› ƒ‚|dk sF|t|	ƒkrStd|› dt|	ƒ› d	ƒ‚||	| d
< d|› d}
| |
t	 
|	¡|f¡ | ¡  t d|› d|› ¡ 	 W d  ƒ dS 1 s…w   Y  dS )z-Update the text of a specific speaker segmentzSELECT speaker_segments FROM `rë  r)  z#No speaker segments found for call z)Invalid speaker_segments format for call r   zInvalid segment index z (total segments: rX  rÜ   r‹  zP_sarvamresponse` SET speaker_segments = %s, updated_at = NOW() WHERE callid = %szUpdated segment z
 for call NT)rg   rl   rj   rk   rÊ   r†   r*  rø   rY   r—   r˜   rí   rF   r  )r2   rz   r¢   Úsegment_indexÚnew_textre   rl   rŽ   r‘   ÚsegmentsÚupdate_queryr   r   r   Úupdate_speaker_segment_textª  s&   
$ãz+DatabaseHandler.update_speaker_segment_textc                 C   s€   |   ¡ 2}| ¡ }|rd|› d}| ||f¡ nd|› d}| |¡ dd„ | ¡ D ƒ}|W  d  ƒ S 1 s9w   Y  dS )z@Get list of unique agent names, optionally filtered by groupnamezI
                    SELECT DISTINCT agentname
                    FROM `zÂ_raw_calls`
                    WHERE agentname IS NOT NULL
                    AND agentname != ''
                    AND groupname = %s
                    ORDER BY agentname
                z›_raw_calls`
                    WHERE agentname IS NOT NULL
                    AND agentname != ''
                    ORDER BY agentname
                c                 S   ó   g | ]}|d  ‘qS )r$  r   rà   r   r   r   rV   ã  rW   z3DatabaseHandler.get_agent_names.<locals>.<listcomp>Nrâ  )r2   rz   rd  re   rl   rŽ   Úagentsr   r   r   Úget_agent_namesË  s   
þþ
$ézDatabaseHandler.get_agent_namesc           %   
      sd  zddl }W n ty } ztdƒ|‚d}~ww |r t|ddƒs$tdƒ‚|j ¡ }| d¡s2tdƒ‚zt|ƒ}W n ttfyK } ztd	ƒ|‚d}~ww z	|j	|d
d}W n t
yi } ztd|› ƒ|‚d}~ww |j}|jd
d}	zt|	ƒ}
W n ty„   tdƒ‚w |
s‹tdƒ‚ddl‰‡fdd„}g 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$“d'd(“d)d)d*d+œ¥}i }t|
ƒD ]\}}||ƒ}|såqÚ| ||¡}||v ró|||< qÚd| ¡ vrþtd,ƒ‚d-d.„ }g }d}|	D ][}|rtd/d0„ |D ƒƒrqd1|i}| ¡ D ]'\}}|t|ƒkr.q!|| }t|tƒrC| ¡ }|dkrCd}|||< q!|| d¡ƒ}|sY|d27 }q||d< | |¡ q|sktd3ƒ‚tƒ ‰|D ]
}ˆ | ¡ ¡ qp‡fd4d5„|D ƒ‰ d1ˆ vrˆ  dd1¡ dˆ vršˆ  d2d¡ ‡ fd6d5„|D ƒ}|› d7}|  ¡ u}| ¡ }|  d8|f¡ | !¡ sÅtd9|› d:ƒ‚d; "d<d0„ ˆ D ƒ¡}d; "d=gtˆ ƒ ¡}d>d5„ ˆ D ƒ} d; "d?d0„ | D ƒ¡}!d@|› dA|› dB|› dC}"| r |"dD|!› 7 }"dE}#t#dt|ƒ|#ƒD ]}$| $|"||$|$|# … ¡ q
W d  ƒ n	1 s%w   Y  t|ƒ|ˆ dFœS )GzAImport raw calls from an Excel (.xlsx) file into {bid}_raw_calls.r   Nz&openpyxl is required for Excel uploadsrw  rI   zNo file provided for uploadz.xlsxzOnly .xlsx files are supportedzBusiness ID must be numericT)Ú	data_onlyzFailed to read Excel file: )Úvalues_onlyzExcel file is emptyz Excel file header row is missingc                    sB   | d u rdS t | ƒ ¡  ¡ }| dd¡ dd¡}ˆ  dd|¡}|S )NrI   rá  rÔ   r  z
[^a-z0-9_])r6   rÉ   r„   r   r×   )r   Úheader)rÖ   r   r   Únormalize_header
  s   zEDatabaseHandler.import_raw_calls_from_excel.<locals>.normalize_header)rz   r¢   Úfileurlr¡   r$  rd  r"  r#  rÀ  Úagent_callinforp  r³  Útranscription_requestedÚtranscription_statusÚselected_for_processingÚcall_idr¢   r'  rý  Ú	audio_urlÚrecording_urlÚrecordingurlÚ	file_namerz  r$  Ú
group_namerd  Ú	starttimer"  Ú
start_timeÚcall_start_timeÚendtimer#  Úend_timeÚcall_end_timeÚ
dialstatusrÀ  rþ  rp  )Úagent_phoneÚ	emp_phoneÚclicktocalldidz$Excel must include a 'callid' columnc                 S   s6   | d u rd S t | tƒr|  ¡ rtt| ƒƒS t| ƒ ¡ S rJ   )r*  r¯  Ú
is_integerr6   rº   rÉ   )r   r   r   r   Únormalize_callidG  s
   zEDatabaseHandler.import_raw_calls_from_excel.<locals>.normalize_callidc                 s   s(    | ]}|d u pt |ƒ ¡ dkV  qd S ©NrI   rõ   )rL   Úcellr   r   r   rN   Q  s   €& z>DatabaseHandler.import_raw_calls_from_excel.<locals>.<genexpr>rz   r–   zNo valid rows found to importc                    s   g | ]}|ˆ v r|‘qS r   r   ©rL   r˜  )Úpresent_columnsr   r   rV   m  rn  z?DatabaseHandler.import_raw_calls_from_excel.<locals>.<listcomp>c                    s   g | ]‰ ‡ fd d„ˆD ƒ‘qS )c                    rã  r   r  r  ©rA  r   r   rV   s  rç  zJDatabaseHandler.import_raw_calls_from_excel.<locals>.<listcomp>.<listcomp>r   )rL   )r—  r  r   rV   s  r	  rp  rh   rd  re  rq  c                 s   rÏ  rÐ  r   r  r   r   r   rN   |  rO   rr  c                 S   s   g | ]}|d vr|‘qS ))r¢   r   r  r   r   r   rV   ~  rn  c                 s   rÒ  rÓ  r   r  r   r   r   rN     s   € 
ÿúINSERT INTO `ú` (ú
) VALUES (rX  z ON DUPLICATE KEY UPDATE r¯   )Ú	processedÚskippedr—  )%ÚopenpyxlÚImportErrorrÊ   Úgetattrrw  r„   Úendswithrº   r«  Úload_workbookrE   ÚactiveÚ	iter_rowsÚnextÚStopIterationrÖ   rë   r0   rK  Úallr6  rY   r*  r6   rÉ   rŒ   rê   rZ   ÚkeysÚinsertrg   rl   rj   rk   rX   ÚrangeÚexecutemany)%r2   rz   Úfile_storager  rf   rw  Ú	bid_valueÚworkbookÚ	worksheetÚ	rows_iterÚheadersrü  Úallowed_columnsÚaliasesÚ
header_mapÚidxÚ
raw_headerrî   Úcolumnr  Úrecordsr  rˆ   rA  r   r¢   rK  rm   re   rl   Úcolumns_sqlru  Úupdate_colsÚ
update_sqlrŽ   Ú
chunk_sizeÚir   )r—  r  rÖ   r   Úimport_raw_calls_from_excelæ  s"  
€ÿ


€ÿ€ÿÿÿþýüûúùø	÷
öõôóòñðïì€






ÿÿÿÿìýz+DatabaseHandler.import_raw_calls_from_excelc                 C   sJ   |   ¡ }| ¡ }| d¡ |  |¡ W d  ƒ dS 1 sw   Y  dS )z4Create business_pipeline_config if it doesn't exist.au  
                CREATE TABLE IF NOT EXISTS business_pipeline_config (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    bid VARCHAR(20) NOT NULL UNIQUE,
                    pipeline_enabled TINYINT(1) NOT NULL DEFAULT 1,
                    source_db_host VARCHAR(255) NOT NULL DEFAULT '',
                    source_db_port INT NOT NULL DEFAULT 3306,
                    source_db_user VARCHAR(255) NOT NULL DEFAULT '',
                    source_db_password_enc TEXT,
                    source_db_name VARCHAR(255) NOT NULL DEFAULT '',
                    stt_provider VARCHAR(50) NOT NULL DEFAULT 'sarvam',
                    stt_api_key_enc TEXT,
                    min_call_duration_s INT NOT NULL DEFAULT 120,
                    sync_batch INT NOT NULL DEFAULT 500,
                    transcribe_batch INT NOT NULL DEFAULT 3,
                    sync_interval_s INT NOT NULL DEFAULT 120,
                    lead_filter_enabled TINYINT(1) NOT NULL DEFAULT 1,
                    crm_provider VARCHAR(50) DEFAULT 'leadsquared',
                    lookback_days INT NOT NULL DEFAULT 90,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            N)rg   rl   rj   Ú(_ensure_business_pipeline_config_columnsru   r   r   r   Ú%ensure_business_pipeline_config_table—  s
   

"çz5DatabaseHandler.ensure_business_pipeline_config_tablec                 C   s¢   |  d¡ dd„ | ¡ pg D ƒ}dddddd	d
ddddddddœ}| ¡ D ]\}}||vr2|  |¡ q%ddddœ}| ¡ D ]\}}||v sI||v rN|  |¡ q=d S )Nz¾
            SELECT column_name
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = 'business_pipeline_config'
            c                 S   s,   h | ]}t | d ¡p| d¡pdƒ ¡ ’qS )rq   ÚCOLUMN_NAMErI   )r6   r0   r„   rà   r   r   r   rö   ½  s    ÿÿzKDatabaseHandler._ensure_business_pipeline_config_columns.<locals>.<setcomp>zTALTER TABLE business_pipeline_config ADD COLUMN source_type VARCHAR(50) DEFAULT NULLzSALTER TABLE business_pipeline_config ADD COLUMN source_bid VARCHAR(50) DEFAULT NULLz]ALTER TABLE business_pipeline_config ADD COLUMN source_table VARCHAR(255) NOT NULL DEFAULT ''zdALTER TABLE business_pipeline_config ADD COLUMN analysis_mode VARCHAR(50) NOT NULL DEFAULT 'default'z_ALTER TABLE business_pipeline_config ADD COLUMN analytics_enabled TINYINT(1) NOT NULL DEFAULT 1zfALTER TABLE business_pipeline_config ADD COLUMN processing_mode VARCHAR(50) NOT NULL DEFAULT 'default'zbALTER TABLE business_pipeline_config ADD COLUMN group_filter_enabled TINYINT(1) NOT NULL DEFAULT 0zGALTER TABLE business_pipeline_config ADD COLUMN allowed_groupnames TEXTzdALTER TABLE business_pipeline_config ADD COLUMN min_call_duration_effective_at DATETIME DEFAULT NULLz`ALTER TABLE business_pipeline_config ADD COLUMN propensity_enabled TINYINT(1) NOT NULL DEFAULT 0zcALTER TABLE business_pipeline_config ADD COLUMN summary_mode VARCHAR(50) NOT NULL DEFAULT 'default'zVALTER TABLE business_pipeline_config ADD COLUMN summary_instructions TEXT DEFAULT NULLzdALTER TABLE business_pipeline_config ADD COLUMN webhook_ingest_enabled TINYINT(1) NOT NULL DEFAULT 0z^ALTER TABLE business_pipeline_config ADD COLUMN ingest_secret VARCHAR(255) NOT NULL DEFAULT '')Úsource_typerÃ   Úsource_tableÚanalysis_modeÚanalytics_enabledÚprocessing_modeÚgroup_filter_enabledÚallowed_groupnamesrÎ  Úpropensity_enabledÚsummary_modeÚsummary_instructionsÚwebhook_ingest_enabledÚingest_secretz`ALTER TABLE business_pipeline_config MODIFY COLUMN source_table VARCHAR(255) NOT NULL DEFAULT ''zWALTER TABLE business_pipeline_config MODIFY COLUMN source_type VARCHAR(50) DEFAULT NULLziALTER TABLE business_pipeline_config MODIFY COLUMN processing_mode VARCHAR(50) NOT NULL DEFAULT 'default')rC  rB  rF  )rj   r   r6  )r2   rl   Úexistingr—  rã   ÚddlÚrelax_columnsr   r   r   r?  ´  sD   ÿ
þò
€ý
€þz8DatabaseHandler._ensure_business_pipeline_config_columnsc                 C   sN   |   ¡ }| ¡ }| dt|ƒf¡ | ¡ W  d  ƒ S 1 s w   Y  dS )z@Return the pipeline config dict for *bid*, or None if not found.z5SELECT * FROM business_pipeline_config WHERE bid = %sN)rg   rl   rj   r6   rk   rÂ  r   r   r   rÏ  à  s   
þ$úz#DatabaseHandler.get_pipeline_configc                 C   s¬   |   |¡pi }tdt| d¡pdƒƒ}|dkr| d¡S | d¡}|r%|S t ¡ jdd}|  ¡ }| ¡ }| 	d|t
|ƒf¡ | ¡  W d  ƒ |S 1 sOw   Y  |S )zç
        Ensure min duration has a forward-only effective timestamp.

        When ``min_call_duration_s`` is set but ``min_call_duration_effective_at``
        is missing, stamp it to now so older calls are grandfathered.
        r   rÍ  rÎ  ©Úmicrosecondz˜
                UPDATE business_pipeline_config
                SET min_call_duration_effective_at = %s
                WHERE bid = %s
                N)rÏ  r¸   rº   r0   r   r`  r   rg   rl   rj   r6   rí   )r2   rz   rÌ   rÒ  rN  r`  re   rl   r   r   r   rÐ  ê  s&   



ú

öõz0DatabaseHandler.ensure_min_duration_effective_atc                 C   s<   z|   |¡pi }tt| d¡pdƒƒW S  ty   Y dS w )zETrue only when Master Panel webhook ingest toggle is on for this BID.rL  r   F)rÏ  r…   rº   r0   rE   )r2   rz   rÌ   r   r   r   Úis_webhook_ingest_enabled  s   ÿz)DatabaseHandler.is_webhook_ingest_enabledc                 C   s~   t | tƒrdd„ | D ƒS t | tƒr=|  ¡ r=zt | ¡}t |tƒr(dd„ |D ƒW S W n	 ty2   Y nw dd„ |  d¡D ƒS g S )Nc                 S   rn  r   rõ   rT   r   r   r   rV     ro  z>DatabaseHandler._decode_allowed_groupnames.<locals>.<listcomp>c                 S   rn  r   rõ   rT   r   r   r   rV     ro  c                 S   ry  r   rz  )rL   Úpartr   r   r   rV     r|  r}  )r*  rø   r6   rÉ   r—   r+  rE   r¬  )r   Úparsedr   r   r   Ú_decode_allowed_groupnames  s   


ÿÿz*DatabaseHandler._decode_allowed_groupnamesc              	   C   s€   zt |pdƒ}t |pdƒ}W n ttfy   Y dS w |dkr$td|ƒS d|  kr.dkr8n n|dkr8|| }tdt||ƒƒS )uS   Clamp parameter score to [0, max_score]; accept 0â€“1 fractions when max_score > 1.r   ç        r–   )r¯  r«  rÊ   r¸   r¹   )r2   r1  r2  Ú	score_valÚmax_valr   r   r   Únormalize_parameter_score  s   ÿ
 z)DatabaseHandler.normalize_parameter_scorec                 C   sœ   t |tƒr|s	dS d}d}| ¡ D ]+}t |tƒsqt| d¡p dƒ}| dd¡s-||7 }q|t| d¡p5dƒ7 }||7 }q|dkrCdS t|| d d	ƒ|fS )
zQReturn (quality_score_percent, total_possible_points) from parameter_scores dict.©NNrW  r2  r   r0  Tr1  r®   ré   )r*  rË   rK  r¯  r0   r®  )r2   r-  r³   ÚpossibleÚdetailrY  r   r   r   Ú+compute_quality_score_from_parameter_scores)  s    

z;DatabaseHandler.compute_quality_score_from_parameter_scoresc                 C   sX   |   ¡ }| ¡ }| d¡ | ¡ pg }dd„ |D ƒW  d  ƒ S 1 s%w   Y  dS )z6Return list of bid strings where pipeline_enabled = 1.zPSELECT bid FROM business_pipeline_config WHERE pipeline_enabled = 1 ORDER BY bidc                 S   rö  ©rz   r   rÝ  r   r   r   rV   D  rW   z=DatabaseHandler.get_enabled_pipeline_bids.<locals>.<listcomp>Nrâ  rã  r   r   r   Úget_enabled_pipeline_bids<  s   
ÿ$úz)DatabaseHandler.get_enabled_pipeline_bidsc                   C   sx   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“dddddœ¥S )zYDefaults for first-time INSERT when only a subset of fields is saved (e.g. summary_mode).Úpipeline_enabledr–   Úsource_db_hostrI   Úsource_db_portr    Úsource_db_userÚsource_db_nameÚstt_providerÚsarvamrÍ  rx  Ú
sync_batchr¯   Útranscribe_batchrÛ  Úsync_interval_sÚlead_filter_enabledÚcrm_providerr­   Úlookback_dayséZ   rC  rD  ÚdefaultrE  rF  r   N)rG  rI  rJ  rK  r   r   r   r   r   Ú _pipeline_config_insert_defaultsF  sP   ÿþýüûúùø	÷
öõôóòñðïëz0DatabaseHandler._pipeline_config_insert_defaultsc              	      sx  |   ¡  |  |¡du}t|ƒ‰ t|ƒˆ d< |s"|  ¡ }| ˆ ¡ |‰ dˆ v r6tˆ d tƒr6t 	ˆ d ¡ˆ d< dˆ v r|  |¡p@i }zt
dtˆ d ƒƒ}W n ttfyY   d}Y nw | d¡}z|durkt
dt|ƒƒnd}W n ttfy{   d}Y nw ||ks…| d¡st ¡ jddˆ d< dD ]}	|	d	 }
|	ˆ v r¥|  ˆ  |	¡¡ˆ |
< q‘d
D ]}ˆ  |d¡ q¨tˆ  ¡ ƒ}d dgt|ƒ ¡}d dd„ |D ƒ¡}d dd„ |D ƒ¡}d|› d|› d|› }|  ¡ M}| ¡ }|rdd„ ˆ  ¡ D ƒ}|rd dd„ |D ƒ¡}t| ¡ ƒt|ƒg }| d|› d|¡ n| |‡ fdd„|D ƒ¡ | ¡  W d  ƒ dS 1 s5w   Y  dS )u  Upsert a pipeline config row for *bid*.

        *data* may contain any subset of the table columns (except ``id``,
        ``bid``, ``created_at``, ``updated_at``).  Sensitive fields
        (``stt_api_key``, ``source_db_password``) are encrypted on-the-fly
        if passed as plain text â€” callers should pass the plain value under
        the key without the ``_enc`` suffix (e.g. ``stt_api_key``).
        Nrz   rH  rÍ  r   rÎ  rQ  )Ústt_api_keyÚsource_db_passwordÚ_enc©ry   r   r€   rq  rr  c                 s   rÏ  rÐ  r   ©rL   Úcr   r   r   rN     rO   z7DatabaseHandler.save_pipeline_config.<locals>.<genexpr>c                 s   ó*    | ]}|d krd|› d|› dV  qdS ©rz   rF  rÔ  rÕ  Nr   ru  r   r   r   rN   ‘  ó   €  ÿú&INSERT INTO business_pipeline_config (r  rÖ  c                 S   s   i | ]\}}|d kr||“qS r_  r   rÊ  r   r   r   rÎ  ›  r	  z8DatabaseHandler.save_pipeline_config.<locals>.<dictcomp>c                 s   s    | ]	}d |› dV  qdS )rF  z` = %sNr   rÑ  r   r   r   rN     rO   z$UPDATE business_pipeline_config SET z WHERE bid = %sc                    ó   g | ]}ˆ | ‘qS r   r   ru  ©rˆ   r   r   rV   ¤  rW   z8DatabaseHandler.save_pipeline_config.<locals>.<listcomp>)r@  rÏ  rË   r6   rp  rZ   r*  rø   r—   r˜   r¸   rº   r«  rÊ   r0   r   r`  r   rC   Úpopr(  rX   rY   rg   rl   r6  rK  rj   rí   )r2   rz   r:  Ú
row_existsÚmergedrN  Únew_minÚold_min_rawÚold_minÚ	plain_keyÚenc_keyÚfÚcolsru  Úcols_sqlr;  Úsqlre   rl   Ú
update_rowÚset_sqlr   r   r|  r   Úsave_pipeline_configa  sr   	
ÿ
ÿ€
ÿÿÿ

þ€
$óz$DatabaseHandler.save_pipeline_configrJ  c                    s$  ddl m} |  ¡  ||ƒ}|pd ¡ pd}|dkr |s tdƒ‚|  ¡ d}| ¡ }|  |¡}	|	r<| d||t	|ƒf¡ n@|  
¡ ‰ t	|ƒˆ d< |ˆ d	< |ˆ d
< tˆ  ¡ ƒ}
d dgt|
ƒ ¡}d dd„ |
D ƒ¡}| d|› d|› d‡ fdd„|
D ƒ¡ | ¡  W d  ƒ dS 1 s‹w   Y  dS )zCPersist only call summary settings for a BID (safe partial update).r   )Únormalize_summary_moderI   NÚcustomz<summary_instructions is required when summary_mode is customz±
                    UPDATE business_pipeline_config
                    SET summary_mode = %s, summary_instructions = %s
                    WHERE bid = %s
                    rz   rJ  rK  rq  rr  c                 s   rÏ  rÐ  r   ru  r   r   r   rN   Ä  rO   z6DatabaseHandler.save_summary_config.<locals>.<genexpr>rz  r  rX  c                    r{  r   r   ru  r|  r   r   rV   Ç  rW   z7DatabaseHandler.save_summary_config.<locals>.<listcomp>)Úsummary_configrŒ  r@  rÉ   rÊ   rg   rl   rÏ  rj   r6   rp  rø   r(  rX   rY   rí   )r2   rz   rJ  rK  rŒ  ÚmodeÚinstructionsre   rl   rN  r†  ru  r‡  r   r|  r   Úsave_summary_config§  s6   

ú	þ
"èz#DatabaseHandler.save_summary_configc                 C   ó@   |   ¡ }| ¡ }| d¡ W d  ƒ dS 1 sw   Y  dS )z3Audit log for universal call ingest webhook events.aE  
                CREATE TABLE IF NOT EXISTS pcaa_ingest_log (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY,
                    bid VARCHAR(20) NOT NULL,
                    callid VARCHAR(64) DEFAULT NULL,
                    source VARCHAR(50) DEFAULT NULL,
                    payload JSON DEFAULT NULL,
                    action VARCHAR(50) NOT NULL,
                    skip_reason VARCHAR(255) DEFAULT NULL,
                    queued TINYINT(1) NOT NULL DEFAULT 0,
                    signature_valid TINYINT(1) NOT NULL DEFAULT 0,
                    received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_bid_received (bid, received_at),
                    INDEX idx_callid (callid)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                Nrt   ru   r   r   r   Úensure_pcaa_ingest_log_tableË  s   
ÿ"þz,DatabaseHandler.ensure_pcaa_ingest_log_tabler%  r"  Úactionr5  ÚqueuedÚsignature_validc                C   sˆ   |   ¡  |  ¡ 2}	|	 ¡ }
|
 dt|ƒ|pd |pd t |pi ¡|||r%dnd|r*dndf¡ |	 ¡  W d   ƒ d S 1 s=w   Y  d S )NzÒ
                INSERT INTO pcaa_ingest_log
                (bid, callid, source, payload, action, skip_reason, queued, signature_valid)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                r–   r   )r“  rg   rl   rj   r6   r—   r˜   rí   )r2   rz   r¢   r%  r"  r”  r5  r•  r–  re   rl   r   r   r   Úlog_call_ingest_eventâ  s"   


øú
"íz%DatabaseHandler.log_call_ingest_event)
rz   r”  Úskip_reasonr–  r¢   re  rf  r   r¶   r·   r˜  re  rf  r   r¶   r·   c       
         C   sb  |   ¡  tdtt|	pdƒdƒƒ}	tdt|
pdƒƒ}
g }g }|r3t|ƒ ¡ r3| d¡ | t|ƒ ¡ ¡ |rIt|ƒ ¡ rI| d¡ | t|ƒ ¡ ¡ |rft|ƒ ¡ rft|ƒ ¡ }| d¡ | ||› dg¡ |d	urx| d
¡ | |rudnd¡ |r’t|ƒ ¡ r’| d¡ | dt|ƒ ¡ › d¡ |r«t|ƒ ¡ r«| d¡ | t|ƒ ¡ › d¡ |rÄt|ƒ ¡ rÄ| d¡ | t|ƒ ¡ › d¡ |d	urÞt|ƒdkrÞ|sÞ|sÞ| d¡ | t|ƒ¡ |rèdd |¡› nd}|  	¡ 9}| 
¡ }| d|› |¡ t| ¡ pi  d¡p	dƒ}| d|› d||	|
g ¡ | ¡ pg }W d	  ƒ n	1 s+w   Y  g }|D ]u}| d¡}t|tƒrUzt |¡}W n tyT   |}Y nw |}| | d¡| d¡| d¡| d¡| d¡| d¡tt| d¡pzdƒƒtt| d ¡p…dƒƒt| d!¡d"ƒrš| d!¡jd#d$d%n	t| d!¡p¢dƒ|d&œ
¡ q4|||	|
d'œS )(z8Paginated webhook ingest audit log from pcaa_ingest_log.r–   ró  r¯   r   r°   zaction = %sz)(skip_reason = %s OR skip_reason LIKE %s)rô  Nzsignature_valid = %szcallid LIKE %szreceived_at >= %sz	 00:00:00zreceived_at <= %sz	 23:59:59z0received_at >= DATE_SUB(NOW(), INTERVAL %s HOUR)r¼  r²   rI   z+SELECT COUNT(*) AS cnt FROM pcaa_ingest_logrÜ  zÀ
                SELECT id, bid, callid, source, action, skip_reason, queued,
                       signature_valid, received_at, payload
                FROM pcaa_ingest_log
                zg
                ORDER BY received_at DESC, id DESC
                LIMIT %s OFFSET %s
                r"  ry   rz   r¢   r%  r”  r˜  r•  r–  Úreceived_atr   rá  r   )Úsepr   )
ry   rz   r¢   r%  r”  r˜  r•  r–  r™  r"  r´   )r“  r¸   r¹   rº   r6   rÉ   rŒ   rY  rX   rg   rl   rj   rk   r0   r   r*  r—   r+  rE   r…   r   r   )r2   rz   r”  r˜  r–  r¢   re  rf  r   r¶   r·   rZ  r   Úsrr¼   re   rl   r³   r   rµ   rˆ   Úpayload_rawÚpayload_objr   r   r   Úlist_webhook_ingest_logs  s   








þü
÷î
ÿÿòÿz(DatabaseHandler.list_webhook_ingest_logsc                 C   r’  )z1Create business_agent_config if it doesn't exist.a  
                CREATE TABLE IF NOT EXISTS business_agent_config (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    bid VARCHAR(20) NOT NULL,
                    agent_name VARCHAR(100) NOT NULL,
                    agent_enabled TINYINT(1) NOT NULL DEFAULT 1,
                    model_provider VARCHAR(50) NOT NULL DEFAULT 'bedrock',
                    model_id VARCHAR(200) NOT NULL DEFAULT 'amazon.nova-lite-v1:0',
                    system_prompt TEXT,
                    user_prompt_template TEXT,
                    output_schema TEXT,
                    temperature FLOAT NOT NULL DEFAULT 0.1,
                    max_tokens INT NOT NULL DEFAULT 4096,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    UNIQUE KEY uq_bid_agent (bid, agent_name)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            Nrt   ru   r   r   r   Ú"ensure_business_agent_config_tablel  s   
"þz2DatabaseHandler.ensure_business_agent_config_tablec                 C   sR   |   ¡ }| ¡ }| dt|ƒf¡ | ¡ pg W  d  ƒ S 1 s"w   Y  dS )zGReturn all agent config rows for *bid* (enabled or not), ordered by id.z>SELECT * FROM business_agent_config WHERE bid = %s ORDER BY idN)rg   rl   rj   r6   r   rÂ  r   r   r   Úget_agent_configsƒ  s   
þ
$úz!DatabaseHandler.get_agent_configsc                    sÜ   t |ƒ‰ t|ƒˆ d< dD ]}ˆ  |d¡ qtˆ  ¡ ƒ}d dgt|ƒ ¡}d dd„ |D ƒ¡}d dd„ |D ƒ¡}d	|› d
|› d|› }|  ¡ }	|	 ¡ }
|
 	|‡ fdd„|D ƒ¡ |
j
W  d  ƒ S 1 sgw   Y  dS )z+Upsert an agent config; returns the row id.rz   rt  Nrq  rr  c                 s   rÏ  rÐ  r   ru  r   r   r   rN   –  rO   z4DatabaseHandler.save_agent_config.<locals>.<genexpr>c                 s   s*    | ]}|d vrd|› d|› dV  qdS ))rz   rz  rF  rÔ  rÕ  Nr   ru  r   r   r   rN   —  ry  z#INSERT INTO business_agent_config (r  rÖ  c                    r{  r   r   ru  r|  r   r   rV      rW   z5DatabaseHandler.save_agent_config.<locals>.<listcomp>)rË   r6   r}  rø   r(  rX   rY   rg   rl   rj   r    )r2   rz   r:  r…  r†  ru  r‡  r;  rˆ  re   rl   r   r|  r   Úsave_agent_config  s&   
ÿÿÿ
$ýz!DatabaseHandler.save_agent_configrz  c                 C   sR   |   ¡ }| ¡ }| dt|ƒ|f¡ |jdkW  d  ƒ S 1 s"w   Y  dS )z@Delete a single agent config; returns True if a row was deleted.zDDELETE FROM business_agent_config WHERE bid = %s AND agent_name = %sr   N)rg   rl   rj   r6   r›   )r2   rz   rz  re   rl   r   r   r   Údelete_agent_config£  s   

þ$úz#DatabaseHandler.delete_agent_configc                 C   sR   |› d}|   ¡ }| ¡ }| d|› d¡ W d  ƒ dS 1 s"w   Y  dS )z.Create {bid}_call_records if it doesn't exist.Ú_call_recordsrœ  aý  ` (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY,
                    bid VARCHAR(20) NOT NULL,
                    callid VARCHAR(255) NOT NULL UNIQUE,
                    file_url TEXT,
                    status ENUM(
                        'pending','transcribing','transcribed',
                        'analyzing','done','failed'
                    ) NOT NULL DEFAULT 'pending',
                    fail_stage VARCHAR(50),
                    fail_reason TEXT,
                    agent_name VARCHAR(255),
                    group_name VARCHAR(255),
                    direction VARCHAR(20),
                    call_start DATETIME,
                    call_end DATETIME,
                    call_duration_s INT,
                    call_status VARCHAR(50),
                    agent_phone VARCHAR(100),
                    customer_phone VARCHAR(100),
                    stt_provider VARCHAR(50),
                    transcript MEDIUMTEXT,
                    speaker_segments JSON,
                    duration_s FLOAT,
                    analysis JSON,
                    quality_score FLOAT,
                    summary TEXT,
                    crm_provider VARCHAR(50),
                    crm_lead_id VARCHAR(100),
                    crm_lead_name VARCHAR(255),
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    INDEX idx_status (status),
                    INDEX idx_customer_phone (customer_phone),
                    INDEX idx_call_start (call_start),
                    INDEX idx_agent_name (agent_name)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            Nrt   )r2   rz   rN  re   rl   r   r   r   Úensure_call_records_table±  s   

ÿ"þz)DatabaseHandler.ensure_call_records_tablerA  c                    s~  |› d}t |ƒ‰ ˆ  dt|ƒ¡ dD ]}|ˆ v r/tˆ | tƒs/ˆ | dur/t ˆ | ¡ˆ |< qdD ]}ˆ  |d¡ q2tˆ  ¡ ƒ}d 	dgt
|ƒ ¡}d 	dd	„ |D ƒ¡}g }	|D ]*}
|
d
v r`qY|
dv rw|	 d|
› d|
› d|
› d|
› d	¡ qY|	 d|
› d|
› d¡ qYd 	|	¡}d|› d|› d|› d|› }|  ¡ }| ¡ }| |‡ fdd„|D ƒ¡ W d  ƒ dS 1 s¸w   Y  dS )z>Insert or update a call record.  ``callid`` is the unique key.r£  rz   ©r)  ÚanalysisNrt  rq  rr  c                 s   rÏ  rÐ  r   ru  r   r   r   rN   ò  rO   z5DatabaseHandler.upsert_call_record.<locals>.<genexpr>)r¢   rz   )r(  r)  r¦  r+  r,  rF  z` = IF(VALUES(`z`) IS NOT NULL, VALUES(`z`), `rÕ  rÔ  r  r  r  rÖ  c                    r{  r   r   ru  r|  r   r   rV     rW   z6DatabaseHandler.upsert_call_record.<locals>.<listcomp>)rË   Ú
setdefaultr6   r*  r—   r˜   r}  rø   r(  rX   rY   rŒ   rg   rl   rj   )r2   rz   rA  rN  r˜  r…  r†  ru  r‡  Úupdate_partsrv  r;  rˆ  re   rl   r   r|  r   Úupsert_call_recordâ  s<   
"€ÿ
ÿÿ
"þz"DatabaseHandler.upsert_call_recordr¡   c                 C   sX   |› d}|   ¡ }| ¡ }| d|› d||f¡ W d  ƒ dS 1 s%w   Y  dS )z0Update only the status column for a call record.r£  r‹  z#` SET status = %s WHERE callid = %sNrt   )r2   rz   r¢   r¡   rN  re   rl   r   r   r   Úset_call_status  s   


þ"þzDatabaseHandler.set_call_statusÚstagec                 C   sb   |› d}|   ¡ }| ¡ }| d|› d||dd… |f¡ W d  ƒ dS 1 s*w   Y  dS )z,Mark a call as failed with stage and reason.r£  r‹  zL` SET status = 'failed', fail_stage = %s, fail_reason = %s WHERE callid = %sNéè  rt   )r2   rz   r¢   r«  r5  rN  re   rl   r   r   r   Ú	fail_call  s   


ý"þzDatabaseHandler.fail_callc           	   	   C   s†   ddl m} |› d}|jrt |j¡nd}|  ¡ }| ¡ }| d|› d|j|j	||j
|f¡ W d  ƒ dS 1 s<w   Y  dS )zLPersist STT result into the call record and advance status to 'transcribed'.r   )Ú	STTResultr£  Nr‹  aY  `
                    SET stt_provider = %s,
                        transcript = %s,
                        speaker_segments = %s,
                        duration_s = %s,
                        status = 'transcribed',
                        fail_stage = NULL,
                        fail_reason = NULL
                    WHERE callid = %s)Ústtr®  r)  r—   r˜   rg   rl   rj   r{   r(  ru  )	r2   rz   r¢   Ú
stt_resultr®  rN  Ú	segs_jsonre   rl   r   r   r   Úsave_call_transcription  s   



ûö"þz'DatabaseHandler.save_call_transcriptionr¦  c           
      C   sz   |› d}|  d¡}|  d¡}t |¡}|  ¡ }| ¡ }	|	 d|› d||||f¡ W d  ƒ dS 1 s6w   Y  dS )zIPersist agent analysis into the call record and advance status to 'done'.r£  r+  r,  r‹  a  `
                    SET analysis = %s,
                        quality_score = %s,
                        summary = %s,
                        status = 'done',
                        fail_stage = NULL,
                        fail_reason = NULL
                    WHERE callid = %sN)r0   r—   r˜   rg   rl   rj   )
r2   rz   r¢   r¦  rN  r+  r,  Úanalysis_jsonre   rl   r   r   r   Úsave_call_analysis7  s   






÷"þz"DatabaseHandler.save_call_analysisrÛ  ÚbatchÚmin_duration_sc           
      C   s°   |› d}d}g }|dkr$|rd}|  |t|ƒg¡ n	d}| t|ƒ¡ | |¡ |  ¡ !}| ¡ }	|	 d|› d|› dt|ƒ¡ |	 ¡ pFg W  d	  ƒ S 1 sQw   Y  d	S )
z?Return call records with status='pending' that have a file URL.r£  rI   r   z;AND (call_start < %s OR COALESCE(call_duration_s, 0) >= %s)zAND call_duration_s >= %szkSELECT callid, file_url, call_start, call_duration_s, agent_name, customer_phone
                    FROM `z‡`
                    WHERE status = 'pending'
                      AND file_url IS NOT NULL AND file_url != ''
                      zI
                    ORDER BY call_start ASC
                    LIMIT %sN)rY  rº   rŒ   rg   rl   rj   r  r   )
r2   rz   rµ  r¶  rÓ  rN  Úduration_clauser   re   rl   r   r   r   Úget_calls_to_transcribeO  s.   
ÿ

ÿüø

$ôz'DatabaseHandler.get_calls_to_transcribec                 C   s`   |› d}|   ¡ }| ¡ }| d|› d|f¡ | ¡ pg W  d  ƒ S 1 s)w   Y  dS )zGReturn call records with status='transcribed' ready for agent analysis.r£  zšSELECT callid, transcript, speaker_segments,
                           agent_name, customer_phone, call_start, call_duration_s
                    FROM `zÁ`
                    WHERE status = 'transcribed'
                      AND transcript IS NOT NULL AND transcript != ''
                    ORDER BY call_start ASC
                    LIMIT %sNrâ  )r2   rz   rµ  rN  re   rl   r   r   r   Úget_calls_to_analyzer  s   

þø

$ôz$DatabaseHandler.get_calls_to_analyzer–   é   ÚpageÚ	page_sizeÚsearchc	                 C   s”  |› d}	dg}
g }|r|
  d¡ |  |¡ |r"|
  d¡ |  |¡ |r6|
  d¡ d|› d}||||g7 }|rB|
  d¡ |  |¡ |rN|
  d¡ |  |¡ d	 |
¡}t|d
ƒd
 | }|  ¡ 9}| ¡ }| d|	› d|› |¡ | ¡ pvi  dd¡}| d|	› d|› d|||g ¡ | ¡ p‘g }W d  ƒ n1 sœw   Y  |D ]}dD ]}t	| |¡t
ƒr¹||  ¡ ||< q§q£|||td
| |  ƒ|dœS )z*Return paginated call records for the API.r£  z1=1r±   zagent_name = %szC(customer_phone LIKE %s OR callid LIKE %s OR crm_lead_name LIKE %s)rô  zcall_start >= %szcall_start <= %sr²   r–   zSELECT COUNT(*) AS total FROM `z` WHERE r³   r   aê  SELECT id, callid, bid, status, fail_stage,
                           agent_name, group_name, direction,
                           call_start, call_end, call_duration_s, call_status,
                           agent_phone, customer_phone,
                           stt_provider, duration_s,
                           quality_score, summary,
                           crm_provider, crm_lead_id, crm_lead_name,
                           created_at, updated_at
                    FROM `z`
                    WHERE zT
                    ORDER BY call_start DESC
                    LIMIT %s OFFSET %sN©Ú
call_startÚcall_endr   r€   )r³   r»  r¼  Úpagesr8  )rŒ   rX   r¸   rg   rl   rj   rk   r0   r   r*  r   r   )r2   rz   r»  r¼  r¡   rz  r½  re  rf  rN  ri  r   Úliker»   r·   re   rl   r³   r   rˆ   r˜  r   r   r   Úget_call_records_listƒ  s^   











ø	÷
óì€þûz%DatabaseHandler.get_call_records_listc           	   	   C   sÒ   |› d}|   ¡ }| ¡ }| d|› d|f¡ | ¡ }W d  ƒ n1 s'w   Y  |s0dS dD ]}| |¡}t|tƒrQz	t |¡||< W q2 t	yP   Y q2w q2dD ]}t| |¡t
ƒrf||  ¡ ||< qT|S )z>Return a single call record with full transcript and analysis.r£  rY  z` WHERE callid = %sNr¥  r¾  )rg   rl   rj   rk   r0   r*  r6   r—   r+  rE   r   r   )	r2   rz   r¢   rN  re   rl   rˆ   r˜  Úvalr   r   r   Úget_call_record_detailË  s2   


þ
ú

ÿý€z&DatabaseHandler.get_call_record_detailc                 C   sR   |   ¡ }| ¡ }| d¡ | d¡ | ¡  W d   ƒ d S 1 s"w   Y  d S )Nai  
                CREATE TABLE IF NOT EXISTS business_api_push_config (
                    bid VARCHAR(50) NOT NULL PRIMARY KEY,
                    is_enabled TINYINT(1) NOT NULL DEFAULT 0,
                    endpoint_url TEXT,
                    http_method VARCHAR(10) NOT NULL DEFAULT 'POST',
                    timeout_seconds INT NOT NULL DEFAULT 8,
                    auth_type VARCHAR(20) NOT NULL DEFAULT 'none',
                    auth_token_enc TEXT,
                    api_key_name VARCHAR(100) DEFAULT NULL,
                    api_key_value_enc TEXT,
                    custom_headers JSON DEFAULT NULL,
                    mapping_key VARCHAR(20) NOT NULL DEFAULT 'phone',
                    mapping_location VARCHAR(20) NOT NULL DEFAULT 'query',
                    mapping_name VARCHAR(100) DEFAULT NULL,
                    selected_fields JSON DEFAULT NULL,
                    field_mappings JSON DEFAULT NULL,
                    include_empty_fields TINYINT(1) NOT NULL DEFAULT 0,
                    payload_format VARCHAR(30) NOT NULL DEFAULT 'flat_json',
                    payload_wrapper_key VARCHAR(100) NOT NULL DEFAULT 'data',
                    payload_template MEDIUMTEXT,
                    content_type VARCHAR(100) NOT NULL DEFAULT 'application/json',
                    api_push_effective_at DATETIME DEFAULT NULL,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                a"  
                CREATE TABLE IF NOT EXISTS api_push_log (
                    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    bid VARCHAR(50) NOT NULL,
                    callid VARCHAR(100) DEFAULT NULL,
                    trigger_event VARCHAR(50) DEFAULT NULL,
                    mapping_key VARCHAR(50) DEFAULT NULL,
                    mapping_value VARCHAR(255) DEFAULT NULL,
                    endpoint_url TEXT,
                    http_method VARCHAR(10) DEFAULT NULL,
                    payload JSON DEFAULT NULL,
                    response_status INT DEFAULT NULL,
                    response_body TEXT,
                    success TINYINT(1) NOT NULL DEFAULT 0,
                    error_message TEXT,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    KEY idx_api_push_log_bid_created (bid, created_at),
                    KEY idx_api_push_log_call (bid, callid, success)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                r  ru   r   r   r   Úensure_api_push_tablesì  s   
ÿÿ
"Ëz&DatabaseHandler.ensure_api_push_tablesc                   C   sj   i dd“dd“dd“dd“d	d
“dd“dd“dd“ddg“dddi“di “dd“dd“dd“dd“dd“S )NÚ
is_enabledr   Úendpoint_urlrI   Úhttp_methodÚPOSTÚtimeout_secondsé   Ú	auth_typeÚnoneÚmapping_keyr[   Úmapping_locationrŽ   Úmapping_nameÚselected_fieldsr,  Úfield_mappingsÚcustom_headersÚinclude_empty_fieldsÚpayload_formatÚ	flat_jsonÚpayload_wrapper_keyr:  Úpayload_templateÚcontent_typeúapplication/jsonr   r   r   r   r   Ú_api_push_config_defaults$  sB   ÿþýüûúùø	÷
öõôóòñðz)DatabaseHandler._api_push_config_defaultsc                 C   sJ   | d u s| dkr
|S t | ttfƒr| S zt | ¡W S  ty$   | Y S w r  )r*  rË   rø   r—   r+  rE   )r   Úfallbackr   r   r   Ú_decode_json_field9  s   ÿz"DatabaseHandler._decode_json_field©Úinclude_secretsrˆ   rà  c                C   s¼  |si |   ¡ ¥dd i¥S i d| d¡“dtt| d¡pdƒƒ“d| d¡p&d“d| d¡p.d“dt| d¡p7d	ƒ“d
| d
¡p@d“d| d¡pHd“d| d¡pPd“d| d¡pXd“d| d¡p`d“d|  | d¡dg¡“d|  | d¡ddi¡“d|  | d¡i ¡“dtt| d¡p‹dƒƒ“d| d¡p•d“d| d¡pd“d| d¡p¥d“| d¡p¬dt| d¡ƒt| d¡ƒt| d ¡ƒd!œ¥}|rÜ|  | d¡¡pÍd|d"< |  | d ¡¡pÙd|d#< |S )$Nrz   rÇ  r   rÈ  rI   rÉ  rÊ  rË  rÌ  rÍ  rÎ  Úapi_key_namerÏ  r[   rÐ  rŽ   rÑ  rÒ  r,  rÓ  rÔ  rÕ  rÖ  r×  rØ  r:  rÙ  rÚ  rÛ  Úapi_push_effective_atÚauth_token_encÚapi_key_value_enc)rÚ  râ  Úhas_auth_tokenÚhas_api_key_valueÚ
auth_tokenÚapi_key_value)rÜ  r0   r…   rº   rÞ  r   rH   )r2   rˆ   rà  Úoutr   r   r   Ú_serialize_api_push_configD  s\   
ÿþýüûúùø	÷
öõôóòñðïëz*DatabaseHandler._serialize_api_push_configc                C   sh   |   ¡  |  ¡ }| ¡ }| dt|ƒf¡ | ¡ }W d   ƒ n1 s$w   Y  |s-d S | j||dS )Nú5SELECT * FROM business_api_push_config WHERE bid = %srß  )rÆ  rg   rl   rj   r6   rk   rê  )r2   rz   rà  re   rl   rˆ   r   r   r   Úget_api_push_configc  s   
þ
úz#DatabaseHandler.get_api_push_configc              	   C   sÒ  |   ¡  t|ƒ ¡ }d }|  ¡ }| ¡ }| d|f¡ | ¡ }W d   ƒ n1 s*w   Y  |r8| j|ddni }|  ¡ }i |¥|pDi ¥}t	| 
d¡ƒ}	t	| 
d¡ƒ}
t ¡ jdd}|p_i  
d¡}|	rk|
ri|sk|}|pni  
d¡}d	|pvi v rŒt| 
d	¡pd
ƒ ¡ }|rŠ|  |¡nd }|pi  
d¡}d|p—i v r­t| 
d¡p d
ƒ ¡ }|r«|  |¡nd }i d|“d|	r¶dnd“dt| 
d¡pÀd
ƒ ¡ “dt| 
d¡pÌdƒ ¡ “dtdtt| 
d¡pÛdƒdƒƒ“dt| 
d¡pèdƒ“d|“dt| 
d¡põd
ƒ ¡ púd “d|“dt | 
d¡pi ¡“dt| 
d¡pdƒ“dt| 
d¡pdƒ“dt| 
d¡p)d
ƒ ¡ p/d “dt | 
d¡p;dg¡“d t | 
d ¡pIddi¡“d!| 
d!¡rTdnd“d"t| 
d"¡p_d#ƒ“t| 
d$¡pid%ƒ ¡ pod%t| 
d&¡pwd
ƒt| 
d'¡p€d(ƒ ¡ p†d(|d)œ¥}t| ¡ ƒ}d* d+gt|ƒ ¡}d* d,d-„ |D ƒ¡}d* d.d-„ |D ƒ¡}d/|› d0|› d1|› }|  ¡ }| ¡ }| |t| ¡ ƒ¡ | ¡  W d   ƒ n	1 sÜw   Y  |  |¡pèi S )2Nrë  Trß  rÇ  r   rQ  râ  rã  rç  rI   rä  rè  rz   r–   rÈ  rÉ  rÊ  rË  rÌ  r   rÍ  rÎ  rá  rÔ  rÏ  r[   rÐ  rŽ   rÑ  rÒ  r,  rÓ  rÕ  rÖ  r×  rØ  r:  rÙ  rÚ  rÛ  )rØ  rÙ  rÚ  râ  rq  rr  c                 s   rÏ  rÐ  r   ru  r   r   r   rN   «  rO   z7DatabaseHandler.save_api_push_config.<locals>.<genexpr>c                 s   rw  rx  r   ru  r   r   r   rN   ¬  s   €( z&INSERT INTO business_api_push_config (r  rÖ  )rÆ  r6   rÉ   rg   rl   rj   rk   rê  rÜ  r…   r0   r   r`  r   rC   Úupperr¸   r¹   rº   r—   r˜   rø   r(  rX   rY   rK  rí   rì  )r2   rz   r"  Úexisting_rowre   rl   rN  Údefaultsr  Únew_enabledÚold_enabledr`  rÓ  rã  Útokenrä  Úkey_valrˆ   r†  ru  r‡  r;  rˆ  r   r   r   Úsave_api_push_configp  s¤   
þ
úÿþýüûúùø	÷
öõôóòñðïëÿÿ

ýz$DatabaseHandler.save_api_push_config)	rÏ  Úmapping_valuerÈ  rÉ  r"  Úresponse_statusÚresponse_bodyÚsuccessÚerror_messageÚtrigger_eventrÏ  rõ  rÈ  rÉ  rö  r÷  rø  rù  c                C   s¤   |   ¡  |  ¡ @}| ¡ }| dt|ƒ||||||tj|pi td|	|
p%dd d… p+d |r/dnd|p3dd d… p9d f¡ | ¡  W d   ƒ d S 1 sKw   Y  d S )Na8  
                INSERT INTO api_push_log
                (bid, callid, trigger_event, mapping_key, mapping_value, endpoint_url,
                 http_method, payload, response_status, response_body, success, error_message)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                )ro  rI   iˆ  r–   r   r¬  )rÆ  rg   rl   rj   r6   r—   r˜   rí   )r2   rz   r¢   rú  rÏ  rõ  rÈ  rÉ  r"  rö  r÷  rø  rù  re   rl   r   r   r   Úlog_api_push_attempt·  s*   

ôù
"èz$DatabaseHandler.log_api_push_attempté   c              	   C   sò   |   ¡  tdtt|pdƒdƒƒ}|  ¡ }| ¡ }| dt|ƒ|f¡ | ¡ p(g }W d   ƒ n1 s3w   Y  |D ]<}t	t| 
d¡pDdƒƒ|d< t| 
d¡tƒrYt|d ƒ|d< | 
d¡}t|tƒrvz	t |¡|d< W q: tyu   Y q:w q:|S )	Nr–   rü  éÈ   at  
                SELECT id, bid, callid, trigger_event, mapping_key, mapping_value,
                       endpoint_url, http_method, payload, response_status, response_body,
                       success, error_message, created_at
                FROM api_push_log
                WHERE bid = %s
                ORDER BY id DESC
                LIMIT %s
                rø  r   r   r"  )rÆ  r¸   r¹   rº   rg   rl   rj   r6   r   r…   r0   r*  r   r   r—   r+  rE   )r2   rz   r¶   re   rl   r   rˆ   r"  r   r   r   Úget_api_push_logsâ  s.   

	öò

ÿýz!DatabaseHandler.get_api_push_logsc                 C   s`   |   ¡  |  ¡ }| ¡ }| dt|ƒt|ƒf¡ | ¡ d uW  d   ƒ S 1 s)w   Y  d S )Nz“
                SELECT 1 FROM api_push_log
                WHERE bid = %s AND callid = %s AND success = 1
                LIMIT 1
                )rÆ  rg   rl   rj   r6   rk   )r2   rz   r¢   re   rl   r   r   r   Úapi_push_already_succeeded   s   
ú
$öz*DatabaseHandler.api_push_already_succeededrJ   )NTN)NNNNNNNN)r­   r®   r   N)NT)	NNNNNNNNN)NNNNNNNr[  )NNNNNNF)	NNNNNrx  NNN)	NNNr®   r   NNNN)Nr®   r   NN)NNN)ró  NN)Nr®   r   FNNN)rP   NN)rP   )NNNNN)rÓ   N)rÛ  r   N)rÛ  )r–   rº  NNNNN)rü  )™Ú__name__Ú
__module__Ú__qualname__Ú__doc__r3   r?   rC   rH   r_   r   rg   rn   rr   rv   rx   r‰   r’   r“   rš   r   rž   rŸ   r¬   r¾   r¿   rÈ   rÎ   rÐ   rÑ   Ústaticmethodr6   rØ   rÙ   r	   r   r   rá   rô   r  r  r	  r  r  r  r  r  r   r!  r$  r(  r)  r†   r;  rC  rP  rV  r]  rj  rm  rw  r»  rÄ  rÈ  r…   rø   rÕ  rñ  rò  rö  r  rP  rV  rX  rZ  r[  r\  r^  rb  rh  r  r“  r¿  rš  rË   r›  r­  rž  r¡  r£  rŽ  r«  r¬  r¯  r³  rµ  r¶  r¾  r¿  rÀ  rÁ  rÃ  rÚ  rÛ  rà  rä  rç  rè  ré  rî  rð  rõ  rø  r>  r@  r?  rÏ  rÐ  rS  rV  rZ  r^  r`  rp  r‹  r‘  r“  r
   r—  rº   rž  rŸ  r   r¡  r¢  r¤  r©  rª  r­  r²  r´  r¸  r¹  rÃ  rÅ  rÆ  rÜ  rÞ  rê  rì  rô  rû  rþ  rÿ  r   r   r   r   r      sŒ   	

 
ô+;*1.=ó@1!õ7:"!#0õ  jõX.þüûùø	
÷'wP&  O-%$ ]	 "&5!
($"		! 2,

F$ýüûúùø	÷
öõ%ôýüûúùø	÷
öõô
óh
1 &$
 ûþýüú #÷þýüûúùø	÷
öH!8&
(Mòýüûúùø	÷
öõôóòñ +r   )r`   Úpymysql.cursorsr   Úloggingr—   r7   r9   rÖ   r   r   r   Ú
contextlibr   Útypingr   r   r	   r
   Úcryptography.fernetr   Ú	getLoggerr   rF   r   r   r   r   r   r   r   Ú<module>   s    

