o
    ˃ig                    @   st   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mZ d dlm	Z	 d dl
mZ eeZG dd dZdS )    N)
DictCursor)datetime)contextmanager)Fernetc                   @   sl  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dddZdd ZdddZd d! Zd"d# Z									dd$d%Zd&d' Zd(d) Z							d d*d+Zd,d- Zd.d/ Zd0d1 Zd2d3 Zd4d5 Zd6d7 Zd8d9 Zd:d; Z d<d= Z!d>d? Z"d@dA Z#dBdC Z$dDdE Z%dFdG Z&dHdI Z'ddJdKZ(ddLdMZ)d!dPdQZ*dRdS Z+d"dTdUZ,ddVdWZ-d#dYdZZ.d$d\d]Z/dd^d_Z0d"d`daZ1ddbdcZ2ddde Z3dfdg Z4d%didjZ5d#dkdlZ6dmdn Z7dodp Z8dqdr Z9dsdt Z:dudv Z;dwdx Z<dydz Z=d{d| Z>d}d~ Z?d%ddZ@dddZAdddZBdddZCdddZDd&ddZEdddZFdd ZGdd ZHdd ZIdeJfddZKdeLfddZMdddZNdd ZOdddZPdddZQdddZRdd ZSdd ZTdd ZUdddZVdd ZWd'ddZXdeYdeJdB fddZZde[fddZ\deYdeJddfddZ]d'ddZ^deYde[fddZ_deYdeJde`fddZadeYdeYdeLfddZbdeYddfddZcdeYdeJddfddĄZddeYdeYdeYddfddȄZedeYdeYdeYdeYddf
dd̄ZfdeYdeYddfdd΄ZgdeYdeYdeJddfddфZh	Od(deYde`de`de[fddքZid)deYde`de[fdd؄Zj							d*deYde`de`deYdB deYdB deYdB deYdB deYdB deJfddZkdeYdeYdeJdB fddZldd Zmdd Znd+ddZodd Zpd,ddZqdd Zrdd ZsdeYdeYfddZtdeYdeYfddZudeYdeYdeJfddZvd-deYdeYde`fddZwdeYfddZxdeYdeYd eJfddZydeYdeYfddZzdd Z{dd Z|dd	d
Z}dd Z~dd Zdd Zd+ddZdd Zdd Zdd Zdd Zdd ZdS (.  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_PORTi  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   '/var/www/html/pca-backend/db_handler.py__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   r   base64urlsafe_b64encodehashlibsha256encodedigestr   )r   secretkeyr   r   r   _get_fernet   s   zDatabaseHandler._get_fernetc                 C   s&   |sd S |   t|ddS )Nr   )r)   encryptr    r%   decoder   valuer   r   r   _encrypt_text#   s   zDatabaseHandler._encrypt_textc                 C   sH   |sd S z|   t|ddW S  ty#   td Y d S w )Nr   z8Failed to decrypt CRM secret; data may use legacy format)r)   decryptr    r%   r+   	Exceptionloggerwarningr,   r   r   r   _decrypt_text(   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       | ]	}|  r|V  qd S Nisdigit.0chr   r   r   	<genexpr>2       z<DatabaseHandler._normalize_phone_variants.<locals>.<genexpr>
   i+91z+910c                 S   s   g | ]}|r|qS r   r   r:   vr   r   r   
<listcomp>9       z=DatabaseHandler._normalize_phone_variants.<locals>.<listcomp>)joinr    lenupdate)r   phonedigitscore10variantsr   r   r   _normalize_phone_variants1   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connectr   Errorr1   errorclose)r   conner   r   r   get_connection;   s    

zDatabaseHandler.get_connectionc                 C   s   | d|f | d uS )NSHOW TABLES LIKE %sexecutefetchone)r   cursor
table_namer   r   r   _table_existsI   s   zDatabaseHandler._table_existsc                 C   s   | d||f | d uS )NzySELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s LIMIT 1rW   )r   rZ   r[   column_namer   r   r   _column_existsM   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
                rU   rZ   rX   r   rS   rZ   r   r   r   ensure_crm_integrations_tableU      
"z-DatabaseHandler.ensure_crm_integrations_tablec                 C   r_   )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
                r`   ra   r   r   r   ensure_crm_leads_cache_tablel   rc   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)
re   rf   rg   rh   ri   r   rj   rk   rl   has_credentials)
rb   rU   rZ   rX   r    lowerrY   r   bool_parse_json_fieldr   rf   rg   rS   rZ   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 = %srf   rg   r4   rm   rn   rh   ri   r   )rf   rg   
access_key
secret_keyrh   ri   r   )rb   appendr    rp   rU   rZ   rX   fetchallr   r3   rq   rr   )	r   rg   queryparamsrS   rZ   rowsresultrt   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
                rm   rn   rh   ri   )rv   rw   rh   ri   )
rb   rU   rZ   rX   r    rp   rY   r3   r   rq   rs   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   )	rb   r    rp   r.   jsondumpsrU   rZ   rX   )r   rf   rg   rv   rw   rh   ri   r   rm   rn   config_jsonrS   rZ   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   )rb   rU   rZ   rX   r    rp   rowcountr   rf   rg   rS   rZ   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
                )rb   rU   rZ   rX   r    rp   r   r   r   r   mark_crm_integration_tested  s   
"z+DatabaseHandler.mark_crm_integration_testedc                 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rt|	 nd |
d urt|
 nd ||f W d    dS 1 sw   Y  dS )Nr4   Fc                 S   s    h | ]}t | rt |qS r   r    striprB   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
                )rd   r    rp   r   rM   r   r   sortedlistr   utcnowrU   rZ   rX   )r   rf   rg   external_lead_id	lead_name
owner_nameemailphone_primaryphone_variantslead_statusnext_task_due_datelead_payloadlast_synced_atsafe_lead_idrL   variants_jsonpayload_jsonsync_dtrS   rZ   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 sw   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
                    re   rf   rg   r   r   r   r   r   r   r   r   r   r   )re   rf   rg   r   r   r   r   r   r   r   r   r   r   )
rd   r    rp   rM   rU   rZ   rX   rY   r   rr   )	r   rf   rg   rI   rL   rS   rZ   variantrt   r   r   r   get_cached_crm_lead_by_phoneP  sF   


&&z,DatabaseHandler.get_cached_crm_lead_by_phonec                 C   r_   )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
                r`   ra   r   r   r    ensure_crm_lead_activities_table  rc   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 )Nr4   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   r    rp   r   rU   rZ   rX   r   r   )r   rf   rg   activity_idlead_id
event_code
event_nameactivity_created_onactivity_modified_onactivity_dataactivity_fieldssafe_activity_idrS   rZ   r   r   r   upsert_crm_lead_activity  s2   

!!z(DatabaseHandler.upsert_crm_lead_activityc                 C   r_   )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
                r`   ra   r   r   r   ensure_sync_watermarks_table  rc   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   rU   rZ   rX   r    rY   )r   rf   	sync_typerS   rZ   rt   r   r   r   get_sync_watermark  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   rU   rZ   rX   r    )r   rf   r   r   rS   rZ   r   r   r   set_sync_watermark  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   r4   r   )rd   r    rp   rU   rZ   rX   ry   setr   r   addrr   )r   rf   rg   rS   rZ   r|   	phone_setrt   primaryrL   rC   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   r4   r   )
r    rp   rU   rZ   rX   ry   r   r   rr   rM   )r   rf   rg   phonesrS   rZ   r|   variant_maprt   infor   rC   r}   rI   r   r   r   r   get_crm_enrichment_for_phones  sR   


	z-DatabaseHandler.get_crm_enrichment_for_phonesc                 C   r_   )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
                r`   ra   r   r   r   ensure_call_sync_cache_tableQ  rc   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   rU   rZ   rX   r    rY   rr   r   )r   	cache_keyrS   rZ   rt   r   r   r   get_call_sync_cachef  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   maxintr   r   rU   rZ   rX   r    )
r   r   rf   sourcer   ttl_secondssafe_ttlr   rS   rZ   r   r   r   upsert_call_sync_cachey  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   rU   rZ   rX   r   r   ra   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)
isinstancer    r   loadsJSONDecodeErrorr,   r   r   r   rr     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	reasoningr4   )r   r   r   r   FreasonzNot applicable)r   r   r   r   )rr   r   r   dictitems)	r   rt   psrawr   rebuilt
param_namedatar   r   r   r   _resolve_parameter_scores  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)rr   )r   recordjson_fieldsfieldr   r   r   _format_call_record  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   _callsr4   SELECT COUNT(*) as count FROM ``count	Business )rf   name
totalCalls
callsTableN)	rU   rZ   rX   ry   r   valuesreplacerY   rx   )
r   rS   rZ   tables
businessestabler[   rf   count_resultr   r   r   r   get_all_businesses  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 businessr   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`
            r   )rf   r   
statisticsrU   rZ   rX   rY   )r   rf   rS   rZ   r[   rz   statsr   r   r   get_business_info  s"   



$z!DatabaseHandler.get_business_infoc                 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
            	groupnamer   )r   r   N)rU   rZ   rX   ry   rx   )r   rf   rS   rZ   rz   results
groupnamesrt   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   s$   g | ]}t | rt | qS r   r   rB   r   r   r   rD   @  s   $ zADatabaseHandler.get_group_agent_customer_rows.<locals>.<listcomp>
_raw_callsN, %szr.customer_callinfo IN ()zr.groupname IS NOT NULLzTRIM(r.groupname) != ''zr.agentname IS NOT NULLzTRIM(r.agentname) != ''r.groupname = %s AND 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
            )	rU   rZ   r\   rF   rG   r   rx   rX   ry   )r   rf   customer_numbersr   normalized_numbersrS   rZ   r[   placeholdersr{   where_clauses	where_sqlrz   r   r   r   get_group_agent_customer_rows;  s>   





$z-DatabaseHandler.get_group_agent_customer_rowsc              
   C   s  |   v}| }d}g }|r|d7 }|| d| d| d| d| d	}||| | }|si dd	d
d	dd	dd	dd	dd	dd	dd	dd	dd	dd	dd	dd	dd	dd	dd	dd	i dd	dd	dd	dd	dd	dd	d d	d!d	d"d	d#d	d$d	d%d	d&d	d'd	d(d	d)d	d*d	d	d	d+W  d,   S t|d pd	}	t|d pd	}
t|d- pd	}td.d/|d0  }|	| }|
| }|| }|}|| }|d	krt|| d1 }d1| }| d2| }nd	}d	}d3}t|d pd	}t|d pd	}|d	krt|| d1 d4nd	}t|d$ p$d	}t|d& p-d	}|d	kr=t|| d1 d4nd	}i dt|d pHd	d
t|d
 pRd	dt|d p\d	dt|d pfd	dt|d ppd	dt|d pzd	dt|d pd	dt|d pd	dt|d pd	dt|d pd	dt|d pd	dt|d pd	dt|d pd	d|d|d5|dt|d pd	d6i d|d|d|dt|d pd	dt|d pd	dt|d pd	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$|d%t|d% p;d	d&|d'|d(t|d( pKd	d)t|d) pUd	t|d* p_d	t|d7 pgd	t|d8 pod	d9W  d,   S 1 sw   Y  d,S ):z@Get call statistics for specified business filtered by groupnamez	WHERE 1=1 AND r.groupname = %sa"  
                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 2 minutes
                    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) >= 120
                            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) >= 120
                            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,
                    AVG(CASE WHEN a.talk_listen_ratio IS NOT NULL THEN a.dead_air_percentage ELSE NULL END) as avg_dead_air_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,

                    -- 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_callsr   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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_outboundNavg_dead_air_percentageg        g      ?g      Y@d   z : N/Ar   talk_listen_ratio   r/  r0  )r.  r/  r0  )	rU   rZ   rx   rX   rY   floatr   roundr   )r   rf   r   rS   rZ   where_clauser{   rz   r}   	raw_agentraw_customerraw_dead_airspoken_fractionagent_of_callcustomer_of_calltalklistentotal_parts	agent_pctcustomer_pctr4  r  r  r  r(  r*  r+  r   r   r   get_location_statsh  s  
 /  0  1  2  5	
 !"#  z



	
 !"#  &z"DatabaseHandler.get_location_statsr2  r   c              
   C   s   |   d}| }g }	g }
|r|	d |
| |r%|	d |
| |r1|	d |
| |	r:dd|	 nd}d| d| d	| d
| d	}|
| |
| |||
 | }|W  d   S 1 skw   Y  dS )zBGet filtered raw calls from {bid}_raw_calls table with pagination.r  zLOWER(r.direction) = LOWER(%s)zr.call_status = %s WHERE r  r4   a  
                SELECT
                    r.callid,
                    r.agentname,
                    r.agent_callinfo,
                    r.call_starttime,
                    r.call_endtime,
                    r.direction,
                    r.call_status,
                    r.groupname,
                    COALESCE(r.duration_seconds, TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime)) as duration_seconds,
                    ca.quality_score,
                    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,
                    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)rU   rZ   rx   rF   rX   ry   )r   rf   r   	directioncall_statuslimitoffsetrS   rZ   r  r{   r  rz   callsr   r   r   get_filtered_raw_calls  s:   








$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}_sarvamresponsea9  
                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,
                    COALESCE(r.duration_seconds, 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transcriptsr5  r   status)rU   rZ   rX   rY   r   )r   rf   callidrS   rZ   rz   call	call_dictr   r   r   get_raw_call_details  s    
%$z$DatabaseHandler.get_raw_call_detailsc                    s
  | d}  ||sg S | d}| d}  ||}	  ||}
g }d}d}d}|	r:|d| d d	}d
}|
rN|d| d d}|	rLd}nd}g }g }|rd|v rd|d ||d  d|v rt|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 |rdd| nd }d!|}d"| d#| d$| d%| d&| d'| d(}|||g ||| | } fd)d*|D S )+Nr   _sarvamresponse_callanalyticsrA   zNULL AS quality_scorez7TIMESTAMPDIFF(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_scorezXCASE 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	date_fromr.call_starttime >= %sdate_tor.call_starttime <= %srM     a.callid IS NOT NULL0=1r5  s.callid IS NOT NULLa.callid IS NULLr   r.status = 1s.callid IS NULL"(r.status = 0 OR r.status IS NULL)rE  r  r4    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   r   r:   rO  r   r   r   rD   z      z7DatabaseHandler._get_raw_calls_list.<locals>.<listcomp>)r\   rx   r   rF   extendrX   ry   )r   rZ   rf   filtersrH  rI  	raw_tablesarvam_tableanalytics_table
has_sarvamhas_analyticsjoinsstatus_casequality_score_selectduration_seconds_selectr  r{   status_filterr  join_sqlrz   rJ  r   rh  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 |rd|v rQ|
d	 ||d  d
|v ra|
d ||d
  |d}|d ur|dkr||rv|
d n=|
d n7|dkr|r|
d |r|
d n$|dkr|
d |r|
d n|r|
d |r|
d |
d |
rdd|
 nd}d|	}d| d| d| d}||| | }|r|d S dS ) Nr   r   rR  rS  rT  rU  rV  rW  rX  rY  rZ  rM  r[  r\  r]  r5  r^  r_  r   r`  ra  rb  rE  r  r4   rc  z8
            SELECT COUNT(*) as count
            FROM `rd  r  z	
        r   )r\   rx   r   rF   rX   rY   )r   rZ   rf   rk  rl  rm  rn  ro  rp  rq  r  r{   ru  r  rv  rz   r}   r   r   r   _get_raw_calls_count|  sn   













z$DatabaseHandler._get_raw_calls_count2   c                    s^   | d}  ||sg S d| d}|d| d|||||f | } fdd|D S )Nr   %a9  
            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,
                COALESCE(r.duration_seconds, TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime)) as duration_seconds
            FROM `a  ` r
            WHERE r.callid LIKE %s
               OR r.agentname LIKE %s
               OR r.customer_callinfo LIKE %s
               OR r.agent_callinfo LIKE %s
            ORDER BY r.call_starttime DESC, r.call_endtime DESC
            LIMIT %s
        c                    re  r   rf  rg  rh  r   r   rD     ri  z5DatabaseHandler._search_raw_calls.<locals>.<listcomp>)r\   rX   ry   )r   rZ   rf   rz   rH  rl  search_termrJ  r   rh  r   _search_raw_calls  s   
z!DatabaseHandler._search_raw_callsFc           "      C   sR  |   }| }| 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| }|rd|
 dnd}|rd| dnd}|rdnd}|rdnd}d| d|	 d| d| d| d }||| | pd!di}t|	d!pd}d"| d#| d$| d%|	 d&| d'| d'| d(| d)}|||||g  |
 }g }|D ]G}||	d*t|	d+pd|	d,|	d-pd.t|	d/pdt|	d0pdt|	d1p(dt|	d2p1ddd.d.d3 qd4d5 |D }|r| |d6|}|D ]D}|	d*d} |	| i }!|!	d7pbd|d7< |!	d8rq|!d8 |d8< |!	d9r}|!d9 |d9< |d- d.kr|!	d:r|!d: |d-< qM||dW  d   S 1 sw   Y  dS );zGet customer-level lead aggregates from raw calls.

        For inbound calls the customer phone is callfrom (agent_callinfo).
        For outbound calls the customer phone is callto (customer_callinfo).
        r   rR  rS  r   )leadstotalNCASE WHEN LOWER(r.direction) = 'inbound' THEN CASE WHEN r.agent_callinfo = LEFT(r.callid, 10) THEN r.agent_callinfo ELSE LEFT(r.callid, 10) END ELSE r.customer_callinfo END(z) IS NOT NULLzTRIM((z)) != '')inboundoutboundzLOWER(r.direction) = %sr  r^  z&COALESCE(TRIM(s.transcript), '') != ''rE  r  rT  rU  r4   rV  ROUND(AVG(a.quality_score), 2)NULLz`SUM(CASE WHEN s.callid IS NOT NULL AND COALESCE(TRIM(s.transcript), '') != '' THEN 1 ELSE 0 END)rA   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,
                    a   AS avg_quality_score,
                    SUM(
                        CASE
                            WHEN r.call_starttime IS NOT NULL AND r.call_endtime IS NOT NULL
                            THEN GREATEST(COALESCE(r.duration_seconds, 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
            
lead_phoneconversationslast_conversationr   -r  total_duration_secondsanswered_callstranscript_calls)r  r  r  r   r  r  r  r  r   r   r   c                 S   s   g | ]}| d r|d  qS )r  r   )r:   leadr   r   r   rD   [      z2DatabaseHandler.get_leads_list.<locals>.<listcomp>leadsquaredr   r   r   r   )rU   rZ   r\   rp   rx   rF   rX   rY   r   r   ry   r6  r   )"r   rf   r   rH  rI  transcripts_onlyrF  rS   rZ   rl  rm  rn  ro  rp  lead_phone_exprr  r{   r  join_sarvamjoin_analyticsquality_exprtranscript_exprcount_query	total_rowr~  
data_queryr|   r}  rt   r   crm_mapr  rI   
enrichmentr   r   r   get_leads_list  s   













 &zDatabaseHandler.get_leads_listc           2         s     <}| }| d}| d}| d} ||s(	 W d   dS  ||}	 ||}
dg}|g}|rF|d || dd| }|	rUd	| d
nd}|
r_d	| dnd}|
rednd}|
rkdnd}|
rqdnd}d}|
rydnd}|
rdnd}|
rdnd}|
rdnd}|
o ||d}|
o ||d}|
o ||d}|
o ||d}|rdnd}|rdnd}|rdnd}|rdnd}| d}  || }!|!rd	|  dnd}"|!rd nd}#|!rd!nd}$|	rd"nd}%|	rd#nd}&|	rd$nd}'|	rd%nd}(d&| d'| d(| d)| d*| d+| d,| d-})||)| | }*|*r.|*d.s7	 W d   dS dg d/|% d0|& d1|' d2|( d3| d4| d5| d6| d7| d8| d9| d:| d;|# d<|$ d=| d+| d,| d,|" d,| d>}+||+| |	 },t
|*d?pd@}-t
|*dApd@}. |dB|g}/|/|i }0|*dCpdD}1||0dEpd|0dFpdD|0dGpdD|1dDkr|1n|0dHpdDt
|*d.pd@|*dI|*dJt|*dKpd@|-s|.r|- dL|. ndMt
|*dNp%d@t
|*dOp.d@ fdPdQ|,D dRW  d   S 1 sEw   Y  dS )SzHGet customer-level details and call timeline for a given customer phone.r   rR  rS  NzCASE WHEN LOWER(r.direction) = 'inbound' THEN CASE WHEN r.agent_callinfo = LEFT(r.callid, 10) THEN r.agent_callinfo ELSE LEFT(r.callid, 10) END ELSE r.customer_callinfo END = %sr  rE  r  rT  rU  r4   rV  r  r  z'ROUND(AVG(a.agent_speak_percentage), 0)rA   z*ROUND(AVG(a.customer_speak_percentage), 0)za.quality_score	a.summarya.call_purposea.objections_concernsr   parameter_detectionsr   rich_summaryza.parameter_scoresza.parameter_detectionsza.raw_responsea.rich_summary_bantH` b ON CONVERT(r.callid USING utf8mb4) = CONVERT(b.callid USING utf8mb4)b.profile_jsonzb.profile_summaryz[CASE WHEN s.callid IS NOT NULL AND COALESCE(TRIM(s.transcript), '') != '' THEN 1 ELSE 0 ENDs.transcript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,
                    z+ AS avg_quality_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(COALESCE(r.duration_seconds, 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,
                    COALESCE(r.duration_seconds, TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime)) AS duration_seconds,
                    z( AS has_transcript,
                    $ AS transcript,
                    * AS speaker_segments,
                    z- AS transcript_duration,
                    z' AS quality_score,
                    ! AS summary,
                    & AS rich_summary,
                    z* AS parameter_scores,
                    z. AS parameter_detections,
                    z& AS call_purpose,
                    - AS objections_concerns,
                    z& AS raw_response,
                    + AS bant_profile_json,
                    z' AS bant_summary
                FROM `z<
                ORDER BY r.call_starttime DESC
            avg_talk_pctr   avg_listen_pctr  r   r  r   r   r   r   first_conversationr  r  :r3  avg_intent_scorer  c                    sp  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pdd | dd | | d| dpd| dpd | d| dpddqS )rN  call_starttimecall_endtimerG  rF  	agentnamer  r   duration_secondsr   has_transcriptfile_url
transcriptr4   speaker_segmentstranscript_durationquality_scoreNsummaryr  r   r  call_purposeobjections_concernsbant_profile_jsonbant_summary)r  r  r  bant_profiler  )r   r   rq   rr   r6  r   r:   rt   rh  r   r   rD     sX    




	

$
z4DatabaseHandler.get_lead_details.<locals>.<listcomp>)r  r   r   r   r   r  r  r  r  r4  intent_scorer  rJ  )rU   rZ   r\   rx   rF   r^   rX   rY   r   ry   r   r   r6  )2r   rf   r  r   rS   rZ   rl  rm  rn  ro  rp  r  r{   r  r  r  summary_quality_expr	talk_exprlisten_exprintent_exprcall_quality_exprcall_summary_exprcall_purpose_exprcall_objections_exprhas_param_scores_colhas_param_detections_colhas_raw_response_colhas_rich_summary_colcall_parameter_scores_exprcall_parameter_detections_exprcall_raw_response_exprcall_rich_summary_expr
bant_tablehas_bant	join_bantbant_profile_exprbant_summary_exprcall_has_transcript_exprcall_transcript_exprcall_speaker_segments_exprcall_transcript_duration_exprsummary_queryr  calls_queryrJ  talk_pct
listen_pctcrm_enrichmentcrm_info
call_ownerr   rh  r   get_lead_detailsl  s  








^ 
 &z DatabaseHandler.get_lead_detailsc                    s^     }| }| 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|
 d}|	||g |||	 | } fdd|D W  d   S 1 sw   Y  dS )z!Get calls with optional filteringr   NrM  status = %ssales_intentsales_intent = %srW  call_starttime >= %srY  call_starttime <= %srE  r  r4    
                SELECT * FROM `z`
                zp
                ORDER BY call_starttime DESC, call_endtime DESC
                LIMIT %s OFFSET %s
            c                    re  r   rf  rg  rh  r   r   rD   O  ri  z-DatabaseHandler.get_calls.<locals>.<listcomp>)	rU   rZ   r\   rw  rx   rF   rj  rX   ry   )r   rf   rk  rH  rI  rS   rZ   r[   r  r{   r  rz   rJ  r   rh  r   	get_calls$  s@   





$zDatabaseHandler.get_callsc                 C   sH  |   }| }| d}| ||s"| |||W  d   S g }g }|rnd|v r>|d dur>|d ||d  d|v rN|d ||d  d|v r^|d ||d  d	|v rn|d
 ||d	  |rwdd| nd}d| d| }	||	| | }
|
r|
d ndW  d   S 1 sw   Y  dS )z)Get total count of calls matching filtersr   NrM  r  r  r  rW  r  rY  r  rE  r  r4   r   ` r   r   )rU   rZ   r\   rx  rx   rF   rX   rY   )r   rf   rk  rS   rZ   r[   r  r{   r  rz   r}   r   r   r   get_calls_countQ  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 IDr   NSELECT * FROM `` WHERE callid = %s LIMIT 1)rU   rZ   r\   rQ  rX   rY   r   )r   rf   rN  rS   rZ   r[   rz   rO  r   r   r   get_call_by_idv  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 tablerR  r   r   Nz{
                SELECT transcript, language, raw_response, speaker_segments, num_speakers, duration
                FROM `zq`
                WHERE callid = %s
                ORDER BY created_at DESC
                LIMIT 1
            r   )r   rf   rN  rS   rZ   r[   rz   r   r   r   get_call_transcript  s   


$z#DatabaseHandler.get_call_transcriptr>   c                 C   s   | j |d|ddS )zGet most recent callsNr   )rk  rH  rI  )r  )r   rf   rH  r   r   r   get_recent_calls  s   z DatabaseHandler.get_recent_callsc           
   
      s      K}| }| d} ||s# ||||W  d   S d| d}d| d}||||||||f | }	 fdd|	D W  d   S 1 sRw   Y  dS )zSearch calls by query stringr   Nr  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
            rz  c                    re  r   rf  rg  rh  r   r   rD     ri  z0DatabaseHandler.search_calls.<locals>.<listcomp>)rU   rZ   r\   r|  rX   ry   )
r   rf   rz   rH  rS   rZ   r[   search_queryr{  rJ  r   rh  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 recordr   r   Nz = %sFzupdated_at = %s
                UPDATE `z`
                SET r   z/
                WHERE callid = %s
            r   )rU   rZ   r   r   r   rx   r   nowrF   rX   r   )r   rf   rN  r   rS   rZ   r[   set_clausesr{   r(   r-   rz   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_historyr   r   Table  does not existNF
                INSERT INTO `zy`
                (business_id, callid, transfer_reason, created_at)
                VALUES (%s, %s, %s, %s)
            r   )rU   rZ   rX   rY   r1   r2   r   r   r   r  r   )r   rf   rN  transfer_reasonrS   rZ   r[   rz   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}|rzddl}t	|t
rr||n|}|rzt|nd}W n%   |	rtdd |	dD nd}Y n|	rt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 tablerR  r   r   r  r  NaT  
                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,
                    COALESCE(c.duration_seconds, 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_transcriptr4   num_speakersr5  r   r  c                 S      g | ]}|  r|qS r   r   r:   liner   r   r   rD   =      z3DatabaseHandler.get_transcripts.<locals>.<listcomp>
c                 S   r  r   r  r  r   r   r   rD   @  r  customer_callinfounknownCall_rN  _rc  z.txttranscript_idlanguagedurationr  rk   rl   stored_in_vectordbr  )r  rN  filenamer  r  r  num_segmentsr  rk   rl   r  customer_name
agent_namezError fetching transcripts: )rU   rZ   rX   rY   r1   r2   ry   r   r   r   r    r   rG   splitr   rx   	isoformatrq   r0   rQ   )r   rf   rS   rZ   rm  rz   rL  r}   ttranscript_textr  r  r   segments_datar  customer_infor  rT   r   r   r   get_transcripts  sr   



$ &
WWzDatabaseHandler.get_transcriptsc           	      C   s<  |   }| }d| d}||||dd|d|d|d|dd	|d
|d|dd|drCt|dnd|d|d|d|d|d|d|d|d|d|d|df |  |j}|dg }|r| ||||| |W  d   S 1 sw   Y  dS )z0Save call analytics to {bid}_callanalytics tabler  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)
                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
            rf   7987r  r  r  objection_typeNoner  	sentimentanalysis_modelzaws-novar   Nr   r  total_possible_scoreparameters_not_applicabler4  agent_talk_timecustomer_talk_timedead_air_percentageagent_speak_percentagecustomer_speak_percentagetalk_listen_assessmentclassified_objections)	rU   rZ   rX   r   r   r   commit	lastrowid_save_classified_objections)	r   rf   rN  analytics_datarS   rZ   rz   analytics_idr*  r   r   r   save_call_analytics\  sF   
 


$z#DatabaseHandler.save_call_analyticsc                 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rU   rZ   rX   r+  )r   rf   rS   rZ   rz   r   r   r   ensure_bant_table  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.Nr  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
            )r3  r   r   r0   rU   rZ   rX   r+  )	r   rf   rN  profiler  profile_jsonrS   rZ   rz   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
            r5  profile_summaryr4   )r4  r  )r3  r0   rU   rZ   rX   rY   rr   r   )r   rf   rN  rS   rZ   rz   r}   r4  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)rX   r   r+  r1   r   rG   r0   rQ   )
r   rf   rN  r*  rZ   rS   delete_queryinsert_query	objectionrT   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 callr  zV_callanalytics`
                WHERE callid = %s
                LIMIT 1
            r   N)rU   rZ   rX   rY   r   rr   )r   rf   rN  rS   rZ   rz   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)rU   rZ   rX   ry   r   rr   )r   rf   rH  rS   rZ   rz   r   r}   r   r   r   get_calls_for_analysis%	  s"   

$z&DatabaseHandler.get_calls_for_analysisc           	      C   s   |   5}| }d}g }|rd}|| d| d| d| d}||| | }|r0|ni W  d   S 1 s<w   Y  dS )zGet overall analytics summaryr4   WHERE r.groupname = %sa  
                SELECT
                    COUNT(DISTINCT a.callid) as total_analyzed_calls,
                    AVG(a.quality_score) as avg_quality_score,
                    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)rU   rZ   rx   rX   rY   )	r   rf   r   rS   rZ   r8  r{   rz   r}   r   r   r   get_analytics_overviewK	  s$   

	

$z&DatabaseHandler.get_analytics_overviewc                 C   z   |   /}| }d}g }|rd}|| d| d| d| d}||| | W  d   S 1 s6w   Y  dS )z&Get sentiment distribution by locationr4   AND r.groupname = %sz
                SELECT
                    r.groupname as location,
                    a.sentiment,
                    COUNT(*) as count
                FROM `rC  zS_raw_calls` r ON a.callid = r.callid
                WHERE a.sentiment IS NOT NULL zq
                GROUP BY r.groupname, a.sentiment
                ORDER BY r.groupname, a.sentiment
            NrU   rZ   rx   rX   ry   r   rf   r   rS   rZ   r8  r{   rz   r   r   r   get_sentiment_by_locationg	  s"   

$z)DatabaseHandler.get_sentiment_by_locationc                 C   rF  )z%Get average quality score by locationr4   rB  aC  
                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 `rC  rD  zb
                GROUP BY r.groupname
                ORDER BY avg_quality_score DESC
            NrH  rI  r   r   r   get_quality_by_location	  s"   

	$z'DatabaseHandler.get_quality_by_locationc                 C   rF  )z"Get average quality score by agentr4   rB  a  
                SELECT
                    COALESCE(NULLIF(r.agent_callinfo, ''), NULLIF(r.agentname, ''), 'Unknown') as agent,
                    COALESCE(NULLIF(MAX(r.agentname), ''), MAX(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 `rC  rD  z\
                GROUP BY agent
                ORDER BY avg_quality_score DESC
            NrH  rI  r   r   r   get_quality_by_agent	  s"   

	
$z$DatabaseHandler.get_quality_by_agentc                 C   sx  dg}g }|r| d | | |r| d | | |r)| d | | dd| }d| d| d	| d
}|  }	|	 }
|
|| |
 pPg }W d   n1 s[w   Y  g }t|dD ]R\}}|d}| ||d t|d p|dt|d pdt|d pdt	|d pdt	|d pdt	|d pdt	|d pdt
|dr| nt|pdd
 qg|S )zHReturn per-agent scoring leaderboard with full stats and date filtering.za.quality_score IS NOT NULLr  rX  rZ  zWHERE r  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_callr  	avg_scorer   	min_scorer   r  
high_calls	mid_calls	low_callsr  r4   )
rankr  rN  rO  r   r  rP  rQ  rR  rM  )rx   rF   rU   rZ   rX   ry   	enumerater   r6  r   hasattrr  r    )r   rf   r   rW  rY  
conditionsr{   whererz   rS   rZ   r|   r}   rS  rt   rM  r   r   r   get_agent_leaderboard	  sP   








z%DatabaseHandler.get_agent_leaderboardc                 C   rF  )z(Get frequency of different call purposesr4   rG  zx
                SELECT
                    a.call_purpose,
                    COUNT(*) as count
                FROM `rC  z_raw_calls` r ON a.callid = r.callid
                WHERE a.call_purpose IS NOT NULL
                  AND a.call_purpose != '' zr
                GROUP BY a.call_purpose
                ORDER BY count DESC
                LIMIT 20
            NrH  rI  r   r   r   get_call_purpose_frequency	  s"   

$z*DatabaseHandler.get_call_purpose_frequencyc                 C   r_   )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
            r`   ra   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)rZ  rU   rZ   rX   ry   ra   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)rZ  rU   rZ   rX   rY   r   rf   rS   rZ   r   r   r   get_stt_pipeline_bid_config,
  s   
$z+DatabaseHandler.get_stt_pipeline_bid_configr   c           	   	      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   )r:   krC   allowedr   r   
<dictcomp>7
  r  zBDatabaseHandler.upsert_stt_pipeline_bid_config.<locals>.<dictcomp>r   c                 s       | ]	}d | d V  qdS r   Nr   r:   rd  r   r   r   r<   :
  r=   zADatabaseHandler.upsert_stt_pipeline_bid_config.<locals>.<genexpr>r   c                 s   "    | ]}d | d| dV  qdS r   ` = VALUES(``)Nr   rj  r   r   r   r<   <
  s     z*INSERT INTO stt_pipeline_bid_config (bid, z) VALUES (%s, ) ON DUPLICATE KEY UPDATE )	rZ  r   rF   rG   rU   rZ   rX   r   r   )	r   rf   r   fieldscol_listph_listdup_setrS   rZ   r   re  r   upsert_stt_pipeline_bid_config3
  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   )rZ  rU   rZ   rX   )r   rf   r_  rS   rZ   r   r   r   toggle_stt_pipeline_bidF
  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 )rM  cntr   r:   rr   r   r   rg  ]
  s    z5DatabaseHandler.get_stt_job_stats.<locals>.<dictcomp>zget_stt_job_stats failed: %s)rU   rZ   rX   ry   r0   r1   r2   )r   rf   rS   rZ   r|   excr   r   r   get_stt_job_statsP
  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_NAMEr   r4   )r   rw  r   r   r   rD   l
  s    z>DatabaseHandler.discover_stt_raw_call_bids.<locals>.<listcomp>rU   rZ   rX   ry   r   rS   rZ   r|   r   r   r   discover_stt_raw_call_bidsb
  s   
z*DatabaseHandler.discover_stt_raw_call_bidsc                 C   rF  )z.Get frequency of different concerns/objectionsr4   rG  z
                SELECT
                    a.objections_concerns,
                    a.objection_type,
                    COUNT(*) as count
                FROM `rC  a  _raw_calls` r ON a.callid = r.callid
                WHERE a.objections_concerns IS NOT NULL
                  AND a.objections_concerns != ''
                  AND a.objections_concerns != 'None identified'
                  AND a.objections_concerns != 'None'
                  z
                GROUP BY a.objections_concerns, a.objection_type
                ORDER BY count DESC
                LIMIT 20
            NrH  rI  r   r   r   get_concerns_frequencyn
  s"   

$z&DatabaseHandler.get_concerns_frequencyc                 C   rF  )z$Get busiest locations by call volumer4   rB  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
            NrH  rI  r   r   r   get_busy_locations
  s"   

$z"DatabaseHandler.get_busy_locationsc           	      C   s   |   6}| }d}|||||g}|r|d7 }|| d| d| d| d}||| | W  d   S 1 s=w   Y  dS )z/Get all calls with a specific objection/concerna  
                WHERE (
                    LOWER(TRIM(COALESCE(a.objection_type, ''))) = LOWER(TRIM(%s))
                    OR LOWER(TRIM(COALESCE(oc.category_name, ''))) = LOWER(TRIM(%s))
                    OR a.objections_concerns = %s
                )
            r	  a  
                SELECT DISTINCT
                    a.callid,
                    r.groupname as location,
                    r.agentname,
                    r.call_starttime,
                    r.call_endtime,
                    r.call_status,
                    r.direction,
                    COALESCE(r.duration_seconds, TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime)) as duration_seconds,
                    a.call_purpose,
                    a.sentiment,
                    a.quality_score,
                    a.objections_concerns,
                    a.objection_type,
                    a.created_at
                FROM `rC  z_raw_calls` r ON a.callid = r.callid
                LEFT JOIN call_objections co ON co.bid = %s AND co.callid = a.callid
                LEFT JOIN objection_classifications oc ON oc.bid = %s AND oc.id = co.classification_id
                zn
                ORDER BY COALESCE(r.call_starttime, a.created_at) DESC
                LIMIT 100
            NrH  )	r   rf   r?  r   rS   rZ   r8  r{   rz   r   r   r   get_calls_by_objection
  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rU   rZ   rX   r+  r   r   rf   rN  rS   rZ   rz   r   r   r   delete_transcript
     
$z!DatabaseHandler.delete_transcriptc                 C   r  )z2Reset transcription_status to 0 in raw_calls tableUPDATE `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 sw   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: r   textr  zP_sarvamresponse` SET speaker_segments = %s, updated_at = NOW() WHERE callid = %szUpdated segment z
 for call NT)rU   rZ   rX   rY   
ValueErrorrr   r   r   rG   r   r   r+  r1   r   )r   rf   rN  segment_indexnew_textrS   rZ   rz   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   rD   "  rE   z3DatabaseHandler.get_agent_names.<locals>.<listcomp>Nr|  )r   rf   r   rS   rZ   rz   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 |
st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 ]\}}||}|sq|||}||v r|||< qd| vrt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 |! st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 uploadsr  r4   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 )Nr4   rc  r  r  z
[^a-z0-9_])r    r   rp   r   sub)r-   header)rer   r   normalize_headerI  s   zEDatabaseHandler.import_raw_calls_from_excel.<locals>.normalize_header)rf   rN  fileurlrM  r  r   r  r  rG  agent_callinfor
  rF  transcription_requestedtranscription_statusselected_for_processingcall_idrN  r  r  	audio_urlrecording_urlrecordingurl	file_namer  r  
group_namer   	starttimer  
start_timecall_start_timeendtimer  end_timecall_end_time
dialstatusrG  r  r
  )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 r6   )r   r6  
is_integerr    r   r   )r-   r   r   r   normalize_callid  s
   zEDatabaseHandler.import_raw_calls_from_excel.<locals>.normalize_callidc                 s   s(    | ]}|d u pt | dkV  qd S )Nr4   r   )r:   cellr   r   r   r<     s   & z>DatabaseHandler.import_raw_calls_from_excel.<locals>.<genexpr>rf   r   zNo valid rows found to importc                    s   g | ]}| v r|qS r   r   r:   col)present_columnsr   r   rD     r  z?DatabaseHandler.import_raw_calls_from_excel.<locals>.<listcomp>c                    s   g | ]  fd dD qS )c                    re  r   r  r  r   r   r   rD     ri  zJDatabaseHandler.import_raw_calls_from_excel.<locals>.<listcomp>.<listcomp>r   )r:   )columnsr  r   rD     r  r   rV   r  r  r   c                 s   rh  ri  r   r  r   r   r   r<     r=   r   c                 S   s   g | ]}|d vr|qS ))rN  r   r  r   r   r   rD     r  c                 s   rk  rl  r   r  r   r   r   r<     s    
INSERT INTO `` (
) VALUES (r   z ON DUPLICATE KEY UPDATE i  )	processedskippedr  )%openpyxlImportErrorr  getattrr  rp   endswithr   	TypeErrorload_workbookr0   active	iter_rowsnextStopIterationr  rT  r   r   allr   rG   r   r    r   rx   r   rH   keysinsertrU   rZ   rX   rY   rF   rangeexecutemany)%r   rf   file_storager  rT   r  	bid_valueworkbook	worksheet	rows_iterheadersr  allowed_columnsaliases
header_mapidx
raw_header
normalizedcolumnr  recordsr  rt   r   r-   rN  r   r[   rS   rZ   columns_sqlr  update_cols
update_sqlrz   
chunk_sizeir   )r  r  r  r   import_raw_calls_from_excel%  s"  



	







z+DatabaseHandler.import_raw_calls_from_excelreturnc                 C   @   |   }| }|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
            Nr`   ra   r   r   r   %ensure_business_pipeline_config_table     
"z5DatabaseHandler.ensure_business_pipeline_config_tablerf   c                 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)rU   rZ   rX   r    rY   r\  r   r   r   get_pipeline_config  s   
$z#DatabaseHandler.get_pipeline_configc                 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  )rf   r   rw  r   r   r   rD     rE   z=DatabaseHandler.get_enabled_pipeline_bids.<locals>.<listcomp>Nr|  r}  r   r   r   get_enabled_pipeline_bids  s   
$z)DatabaseHandler.get_enabled_pipeline_bidsc                    s  t | t| 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|	 }
|  }|	 }|
|
 fdd|D  W d   dS 1 s|w   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``).
        rf   )stt_api_keysource_db_password_encre   rk   rl   Nr   r   c                 s   rh  ri  r   r:   cr   r   r   r<     r=   z7DatabaseHandler.save_pipeline_config.<locals>.<genexpr>c                 s   s*    | ]}|d krd| d| dV  qdS )rf   r   rm  rn  Nr   r  r   r   r   r<          z&INSERT INTO business_pipeline_config (r  ro  c                       g | ]} | qS r   r   r  rt   r   r   rD   (  rE   z8DatabaseHandler.save_pipeline_config.<locals>.<listcomp>)r   r    r.   popr   r  rF   rG   rU   rZ   rX   )r   rf   r   	plain_keyenc_keyfcolsr  cols_sqlr  sqlrS   rZ   r   r  r   save_pipeline_config  s.   	

"z$DatabaseHandler.save_pipeline_configc                 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
            Nr`   ra   r   r   r   "ensure_business_agent_config_table.  r  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)rU   rZ   rX   r    ry   r\  r   r   r   get_agent_configsE  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.rf   r  Nr   r   c                 s   rh  ri  r   r  r   r   r   r<   X  r=   z4DatabaseHandler.save_agent_config.<locals>.<genexpr>c                 s   s*    | ]}|d vrd| d| dV  qdS ))rf   r  r   rm  rn  Nr   r  r   r   r   r<   Y  r  z#INSERT INTO business_agent_config (r  ro  c                    r  r   r   r  r  r   r   rD   b  rE   z5DatabaseHandler.save_agent_config.<locals>.<listcomp>)r   r    r  r   r  rF   rG   rU   rZ   rX   r,  )r   rf   r   r  r  r  r  r  r  rS   rZ   r   r  r   save_agent_configO  s&   

$z!DatabaseHandler.save_agent_configr  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)rU   rZ   rX   r    r   )r   rf   r  rS   rZ   r   r   r   delete_agent_confige  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_recordsr1  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
            Nr`   )r   rf   r   rS   rZ   r   r   r   ensure_call_records_tables  s   

"z)DatabaseHandler.ensure_call_records_tabler   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 sw   Y  dS )z>Insert or update a call record.  ``callid`` is the unique key.r  rf   r  analysisNr  r   r   c                 s   rh  ri  r   r  r   r   r   r<     r=   z5DatabaseHandler.upsert_call_record.<locals>.<genexpr>)rN  rf   )r  r  r  r  r  r   z` = IF(VALUES(`z`) IS NOT NULL, VALUES(`z`), `rn  rm  r  r  r  ro  c                    r  r   r   r  r  r   r   rD     rE   z6DatabaseHandler.upsert_call_record.<locals>.<listcomp>)r   
setdefaultr    r   r   r   r  r   r  rF   rG   rx   rU   rZ   rX   )r   rf   r   r   r  r  r  r  r  update_partsr  r  r  rS   rZ   r   r  r   upsert_call_record  s<   
"

"z"DatabaseHandler.upsert_call_recordrN  rM  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 = %sNr`   )r   rf   rN  rM  r   rS   rZ   r   r   r   set_call_status  s   


"zDatabaseHandler.set_call_statusstager   c                 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 = %sNi  r`   )r   rf   rN  r  r   r   rS   rZ   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   rU   rZ   rX   rg   r  r  )	r   rf   rN  
stt_resultr  r   	segs_jsonrS   rZ   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)r   r   r   rU   rZ   rX   )
r   rf   rN  r  r   r  r  analysis_jsonrS   rZ   r   r   r   save_call_analysis  s   






"z"DatabaseHandler.save_call_analysisr[  batchmin_duration_sc                 C   s|   | d}|rdt | nd}|   }| }|d| d| d|f | p,g W  d   S 1 s7w   Y  dS )z?Return call records with status='pending' that have a file URL.r  zAND call_duration_s >= r4   zkSELECT 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)r   rU   rZ   rX   ry   )r   rf   r  r  r   duration_clauserS   rZ   r   r   r   get_calls_to_transcribe  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  zSELECT 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|  )r   rf   r  r   rS   rZ   r   r   r   get_calls_to_analyze'  s   



$z$DatabaseHandler.get_calls_to_analyzer      page	page_sizesearchrW  rY  c	                 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  | pg }W d   n1 sw   Y  |D ]}dD ]}t	||t
r||  ||< qq|||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)rz  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_endrk   rl   )r~  r  r  pagesr  )rx   rF   r   rU   rZ   rX   rY   r   ry   r   r   r  )r   rf   r  r  rM  r  r  rW  rY  r   rV  r{   likerW  rI  rS   rZ   r~  r|   rt   r  r   r   r   get_call_records_list8  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  r  z` WHERE callid = %sNr  r  )rU   rZ   rX   rY   r   r   r    r   r   r0   r   r  )	r   rf   rN  r   rS   rZ   rt   r  valr   r   r   get_call_record_detail  s2   





z&DatabaseHandler.get_call_record_detailc                 C   sH   |   }| }|d |  W d    d S 1 sw   Y  d S )Na  
                CREATE TABLE IF NOT EXISTS business_telephony_config (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    bid VARCHAR(20) NOT NULL,
                    provider VARCHAR(50) NOT NULL,
                    source_bid VARCHAR(20),
                    config_json TEXT,
                    is_active TINYINT(1) NOT NULL DEFAULT 1,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    UNIQUE KEY uq_bid_provider (bid, provider)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            r2  ra   r   r   r   &ensure_business_telephony_config_table  s
   


"z6DatabaseHandler.ensure_business_telephony_config_tablec              
   C   s   |    |  ]}| }|dt|f | pg }g }|D ];}t|}|dr:t|d dr:|d 	 |d< zt
|dpCd|d< W n tyU   i |d< Y nw || q |W  d    S 1 shw   Y  d S )NzmSELECT provider, source_bid, config_json, is_active, created_at FROM business_telephony_config WHERE bid = %srk   r  r   z{}r   )r  rU   rZ   rX   r    ry   r   r   rU  r  r   r   r0   rx   )r   rf   rS   rZ   r|   r}   rx  rt   r   r   r   get_telephony_integrations  s*   
$z*DatabaseHandler.get_telephony_integrationsc              
   C   sp   |    t|p	i }|  }| }|dt||||||f |  W d    d S 1 s1w   Y  d S )Na'  
                INSERT INTO business_telephony_config (bid, provider, source_bid, config_json, is_active)
                VALUES (%s, %s, %s, %s, 1)
                ON DUPLICATE KEY UPDATE source_bid=%s, config_json=%s, is_active=1,
                    updated_at=CURRENT_TIMESTAMP
            )r  r   r   rU   rZ   rX   r    r+  )r   rf   rg   
source_bidr   
config_strrS   rZ   r   r   r   save_telephony_integration  s   

"z*DatabaseHandler.save_telephony_integrationc                 C   sZ   |    |  }| }|dt||f |  W d    d S 1 s&w   Y  d S )NzBDELETE FROM business_telephony_config WHERE bid=%s AND provider=%s)r  rU   rZ   rX   r    r+  r   r   r   r   delete_telephony_integration  s   


"z,DatabaseHandler.delete_telephony_integration   c                 C   s   t |}| d}|  `}| }d| d}g }	|r)|r)|d7 }|	||g |d7 }|	t| |||	 | p?g }
g }|
D ]}t|}|	 D ]\}}t
|dr]| ||< qN|| qD|W  d   S 1 spw   Y  dS )z2Preview raw_calls for Mcube Classic (local table).r   r  z` WHERE 1=1z+ AND DATE(call_starttime) BETWEEN %s AND %sz& ORDER BY call_starttime DESC LIMIT %sr  N)r    rU   rZ   rj  rx   r   rX   ry   r   r   rU  r  )r   rf   rW  rY  rH  r   rS   rZ   rz   r{   r|   r}   rx  rt   rd  rC   r   r   r   get_raw_calls_preview  s.   


$z%DatabaseHandler.get_raw_calls_previewc                 C   s  t |}| d}d}d}|  }| }|D ]}t |dp!d }	|	s,|d7 }qz\|d| d|	||dpA|d	pAd|d
pGd|dpMd|dpX|dpXd|dpc|dpcd|dpn|dpnd|dptd|dpzd|dpdf |d7 }W q ty }
 ztd|	 d|
  |d7 }W Y d}
~
qd}
~
ww |	  W d   n1 sw   Y  ||dS )z:Insert/upsert telephony call records into {bid}_raw_calls.r   r   rN  r4   r   z&
                        INSERT INTO `a  `
                            (callid, bid, fileurl, status, agentname, groupname,
                             call_starttime, call_endtime, call_status,
                             customer_callinfo, direction, agent_callinfo)
                        VALUES (%s, %s, %s, 0, %s, %s, %s, %s, %s, %s, %s, %s)
                        ON DUPLICATE KEY UPDATE
                            fileurl          = VALUES(fileurl),
                            agentname        = VALUES(agentname),
                            groupname        = VALUES(groupname),
                            call_starttime   = VALUES(call_starttime),
                            call_endtime     = VALUES(call_endtime),
                            call_status      = VALUES(call_status),
                            customer_callinfo= VALUES(customer_callinfo),
                            direction        = VALUES(direction),
                            agent_callinfo   = VALUES(agent_callinfo)
                    r  r  r  r   r  r  Nr  r  r  rG  r
  rF  r  zSkipped record z: )insertedr  )
r    rU   rZ   r   r   rX   r0   r1   r2   r+  )r   rf   r  r   r$  r  rS   rZ   recrN  rT   r   r   r   insert_raw_calls_from_telephony  sJ   



*z/DatabaseHandler.insert_raw_calls_from_telephonyc              
   C   s   |  | |  0}| }dD ]\}}| || d|s,|d| d| d|  q|  W d   dS 1 s<w   Y  dS )z@Add lead insights columns to {bid}_bant if they don't exist yet.))r  zVARCHAR(50))lead_insights_jsonLONGTEXT)lead_insights_atDATETIMEr  ALTER TABLE `z_bant` ADD COLUMN `r  N)r3  rU   rZ   r^   rX   r+  )r   rf   rS   rZ   r  
definitionr   r   r   _ensure_bant_lead_columns,  s   


"z)DatabaseHandler._ensure_bant_lead_columnsr  c                 C   s"   d dd t|D }d| S )zASynthetic callid used to store lead-level insights in bant table.r4   c                 s   r5   r6   r7   r9   r   r   r   r<   >  r=   z8DatabaseHandler._lead_insights_callid.<locals>.<genexpr>LEAD_)rF   r    )r   r  rJ   r   r   r   _lead_insights_callid<  s   
z%DatabaseHandler._lead_insights_callidc                 C   s   z|  | W n
 ty   Y dS w | |}|  }| }|d| d|f | }W d   n1 s9w   Y  |rE|dsGdS | |d }|t	|dpVddS )z)Return cached lead insights dict or None.Nz2SELECT lead_insights_json, lead_insights_at FROM `z _bant` WHERE callid = %s LIMIT 1r'  r)  r4   )insightsgenerated_at)
r-  r0   r/  rU   rZ   rX   rY   r   rr   r    )r   rf   r  synthetic_idrS   rZ   rt   parsedr   r   r   get_lead_insightsA  s(   



z!DatabaseHandler.get_lead_insightsr0  c                 C   s|   |  | | |}tj|dd}|  }| }|d| d||||f |  W d   dS 1 s7w   Y  dS )zCPersist lead insights into the bant table using a synthetic callid.Fr   r  ai  _bant`
                    (callid, bid, lead_phone, lead_insights_json, lead_insights_at)
                VALUES (%s, %s, %s, %s, NOW())
                ON DUPLICATE KEY UPDATE
                    lead_phone = VALUES(lead_phone),
                    lead_insights_json = VALUES(lead_insights_json),
                    lead_insights_at = NOW()
                N)r-  r/  r   r   rU   rZ   rX   r+  )r   rf   r  r0  r2  insights_jsonrS   rZ   r   r   r   save_lead_insightsW  s   



	
"z"DatabaseHandler.save_lead_insightsmin_duration_secondsc                 C   st  |   }| }| d}| d}| d}| d}	| ||s,g W  d   S | ||}
| ||}| ||	}|oF| ||d}|
rKdnd}|
rQd	nd}|rWd
nd}|r]dnd}|rcdnd}|ridnd}|rodnd}|
ryd| dnd}|rd| dnd}|rd|	 dnd}d}|d| d| d| d| d| d| d| d| d| d| d| d| d |
r|nd! d"||f | }W d   n1 sw   Y  g }|D ][}||d# t|d$pd|d%pd|d&pdt	|d'pd(|d)pd| 
|d*pg |d+pd| 
|d| 
|d,|d-p,d|d.p3dd/ q|S )0zSReturn all calls > min_duration_seconds for a lead with transcript + analysis data.r   rR  rS  r  Nr  r  r  r  r  r  r  r  r  rT  rU  r4   rV  r  r  a1  
                SELECT
                    r.callid,
                    r.call_starttime,
                    r.agentname,
                    r.direction,
                    COALESCE(r.duration_seconds, TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime)) AS duration_seconds,
                    r  r  r  r  r  r  z' AS call_purpose
                FROM `r  r  z
                WHERE z~ = %s
                  AND TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime) >= %s
                  AND COALESCE(TRIM(z''zL ), '') != ''
                ORDER BY r.call_starttime ASC
                rN  r  r  rF  r  r   r  r  r  r  r  r  )rN  r  r  rF  r  r  r  r  r  r  r  r  )rU   rZ   r\   r^   rX   ry   rx   r    r   r   rr   )r   rf   r  r7  rS   rZ   rl  rm  rn  r  ro  rp  r  has_rich_summaryr  speaker_exprsummary_exprrich_summary_expr	bant_exprobjections_exprpurpose_exprr  r  r  
phone_exprr|   r}   rt   r   r   r   get_lead_calls_for_insightsl  s   





	


=
z+DatabaseHandler.get_lead_calls_for_insightsc                 C   s   | d}|   7}| }| ||r0| ||ds8|d| d |  W d   dS W d   dS W d   dS 1 sCw   Y  dS )zCAdd rich_summary column to {bid}_callanalytics if it doesn't exist.rS  r  r+  z$` ADD COLUMN `rich_summary` LONGTEXTN)rU   rZ   r\   r^   rX   r+  )r   rf   rn  rS   rZ   r   r   r   ensure_rich_summary_column  s   



"z*DatabaseHandler.ensure_rich_summary_columnr  c                 C   sx   | d}|  | tj|dd}|  }| }|d| d||f |  W d   dS 1 s5w   Y  dS )zPSave rich_summary JSON to {bid}_callanalytics without touching existing columns.rS  Fr   r  zZ`
                SET rich_summary = %s
                WHERE callid = %s
                N)rA  r   r   rU   rZ   rX   r+  )r   rf   rN  r  rn  	rich_jsonrS   rZ   r   r   r   save_rich_summary  s   



"z!DatabaseHandler.save_rich_summaryc                 C   s   | d}zF|   ,}| }| ||ds 	 W d   W dS |d| d|f | }W d   n1 s9w   Y  |sCW dS | |dW S  tyU   Y dS w )z,Fetch rich_summary JSON for a call, or None.rS  r  NzSELECT rich_summary FROM `r  )rU   rZ   r^   rX   rY   rr   r   r0   )r   rf   rN  rn  rS   rZ   rt   r   r   r   get_rich_summary  s&   



	z DatabaseHandler.get_rich_summaryc                 C      | d d S )Na$  
            CREATE TABLE IF NOT EXISTS `lead_notes` (
                id INT AUTO_INCREMENT PRIMARY KEY,
                bid VARCHAR(50) NOT NULL,
                lead_phone VARCHAR(50) NOT NULL,
                content TEXT NOT NULL,
                created_by VARCHAR(255),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_lead_notes (bid, lead_phone)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        rX   r   rZ   r   r   r   _ensure_lead_notes_table     z(DatabaseHandler._ensure_lead_notes_tablec              	   C   s   |   }| }| | |d||f | }W d    n1 s$w   Y  g }|D ]'}||d |d |d |d rD|d  nd |d rO|d  nd d q-|S )NzSELECT id, content, created_by, created_at, updated_at FROM `lead_notes` WHERE bid=%s AND lead_phone=%s ORDER BY created_at DESCre   content
created_byrk   rl   )re   rJ  rK  rk   rl   )rU   rZ   rH  rX   ry   rx   r  r   rf   r  rS   rZ   r|   r}   rx  r   r   r   get_lead_notes  s&   


	
zDatabaseHandler.get_lead_notesc                 C   s`   |   "}| }| | |d||||f |  |jW  d    S 1 s)w   Y  d S )NzWINSERT INTO `lead_notes` (bid, lead_phone, content, created_by) VALUES (%s, %s, %s, %s))rU   rZ   rH  rX   r+  r,  )r   rf   r  rJ  rK  rS   rZ   r   r   r   add_lead_note  s   


$zDatabaseHandler.add_lead_notec                 C   V   |   }| }|d||f |  |jdkW  d    S 1 s$w   Y  d S )Nz/DELETE FROM `lead_notes` WHERE id=%s AND bid=%sr   r  )r   note_idrf   rS   rZ   r   r   r   delete_lead_note"     
$z DatabaseHandler.delete_lead_notec                 C   rE  )Nat  
            CREATE TABLE IF NOT EXISTS `lead_tasks` (
                id INT AUTO_INCREMENT PRIMARY KEY,
                bid VARCHAR(50) NOT NULL,
                lead_phone VARCHAR(50) NOT NULL,
                title VARCHAR(500) NOT NULL,
                due_date DATE,
                done TINYINT(1) DEFAULT 0,
                created_by VARCHAR(255),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_lead_tasks (bid, lead_phone)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        rF  rG  r   r   r   _ensure_lead_tasks_table+  rI  z(DatabaseHandler._ensure_lead_tasks_tablec              
   C   s   |   }| }| | |d||f | }W d    n1 s$w   Y  g }|D ],}||d |d |d rA|d  nd t|d |d |d rT|d  nd d q-|S )	NzSELECT id, title, due_date, done, created_by, created_at FROM `lead_tasks` WHERE bid=%s AND lead_phone=%s ORDER BY created_at DESCre   titledue_datedonerK  rk   )re   rT  rU  rV  rK  rk   )rU   rZ   rS  rX   ry   rx   r  rq   rL  r   r   r   get_lead_tasks;  s(   


	

zDatabaseHandler.get_lead_tasksc              	   C   sf   |   %}| }| | |d||||pd |f |  |jW  d    S 1 s,w   Y  d S )NzcINSERT INTO `lead_tasks` (bid, lead_phone, title, due_date, created_by) VALUES (%s, %s, %s, %s, %s))rU   rZ   rS  rX   r+  r,  )r   rf   r  rT  rU  rK  rS   rZ   r   r   r   add_lead_taskQ  s   

$zDatabaseHandler.add_lead_taskc                 C   s`   |   "}| }|d|rdnd||f |  |jdkW  d    S 1 s)w   Y  d S )Nz6UPDATE `lead_tasks` SET done=%s WHERE id=%s AND bid=%sr   r   r  )r   task_idrf   rV  rS   rZ   r   r   r   update_lead_task\  s   
$z DatabaseHandler.update_lead_taskc                 C   rO  )Nz/DELETE FROM `lead_tasks` WHERE id=%s AND bid=%sr   r  )r   rY  rf   rS   rZ   r   r   r   delete_lead_taskf  rR  z DatabaseHandler.delete_lead_taskc                 C   sH   |   }| }|d |  W d   dS 1 sw   Y  dS )zNGlobal table keyed by business bid for custom lead-insights data capture keys.a  
                CREATE TABLE IF NOT EXISTS data_capture_fields (
                  id INT AUTO_INCREMENT PRIMARY KEY,
                  bid VARCHAR(64) NOT NULL,
                  field_key VARCHAR(128) NOT NULL,
                  display_name VARCHAR(512) 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 DATETIME DEFAULT CURRENT_TIMESTAMP,
                  UNIQUE KEY uk_bid_field (bid, field_key),
                  KEY idx_bid_sort (bid, sort_order)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
                Nr2  ra   r   r   r    ensure_data_capture_fields_tableq  s   

"z0DatabaseHandler.ensure_data_capture_fields_tablec                 C   s   |    t|}|  }| }|d|f | pg }W d   n1 s(w   Y  g }|D ]}||d |d |d |d |d t|d |d	 d
 q1|S )zFOrdered field definitions for a business (used by lead insights + UI).z
                SELECT id, bid, field_key, display_name, field_type, required_flag, sort_order
                FROM data_capture_fields
                WHERE bid = %s
                ORDER BY sort_order ASC, id ASC
                Nre   rf   	field_keydisplay_name
field_typerequired_flag
sort_order)re   rf   r]  r^  r_  requiredra  )r\  r    rU   rZ   rX   ry   rx   rq   )r   rf   rS   rZ   r|   outrx  r   r   r   get_data_capture_fields  s.   
	
z'DatabaseHandler.get_data_capture_fieldsc                 C   sn  ddl }|   t|}|  }| }|d|f t }t|p#g D ]{\}}t|t	s/q%|
dp:|
dp:d }	|	s@q%|dd|	 dpPd	| }
|
dd
 }
|
}d}||v rn|
 d| }|d7 }||v s_|| |
dp~|
dp~d  }|dvrd}t|
d}|d|||	||rdnd|f q%|  W d   dS 1 sw   Y  dS )z
        Replace all field definitions for a business.
        Each item: name (or display_name), type (or field_type), required (bool).
        r   Nz.DELETE FROM data_capture_fields WHERE bid = %sr   r^  r4   z
[^a-z0-9]+r  field_r2  r5  r   typer_  r  )r  rI   r   numbertextareadaterb  z
                    INSERT INTO data_capture_fields
                        (bid, field_key, display_name, field_type, required_flag, sort_order)
                    VALUES (%s, %s, %s, %s, %s, %s)
                    )r  r\  r    rU   rZ   rX   r   rT  r   r   r   r   r  rp   r   rq   r+  )r   rf   rp  r  rS   rZ   	seen_keysr  rt   displaybaser(   nftypereqr   r   r   replace_data_capture_fields  s@   

"
 
"z+DatabaseHandler.replace_data_capture_fieldsr6   )NTN)	NNNNNNNNN)NNNNNNN)NNNr2  r   )Nr2  r   )ry  )Nr2  r   FN)r>   )NNN)r  N)r[  r   )r[  )r   r  NNNNN)NN)NNr"  )r  )__name__
__module____qualname____doc__r   r)   r.   r3   rM   r   rU   r\   r^   rb   rd   ru   r~   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   rr   r   r   r   r   r   r  rD  rK  rQ  rw  rx  r|  r  r  r  r  r  r  r  r  r  r  r  r0  r3  r6  r8  r-  r@  rA  rE  rJ  rK  rL  rX  rY  rZ  r[  r]  r   rt  rq   ru  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  r  r   r!  r#  r&  r-  r/  r4  r6  r@  rA  rC  rD  rH  rM  rN  rQ  rS  rW  rX  rZ  r[  r\  rd  rp  r   r   r   r   r      sn   	

 
@1!7:"!#-  H:.fE!  9-%$ ]G "&:
.		! 2

(
1&

	

H
6U	
r   )rN   pymysql.cursorsr   loggingr   r!   r#   r   
contextlibr   cryptography.fernetr   	getLoggerrq  r1   r   r   r   r   r   <module>   s    
