+
    wi4                       R t ^ RIHt ^ RIt^ RIHtHt ^ RIHt ^ RIH	t	 ^ RI
HtHt ^ RIHtHt ]P                   ! ]4      tR R	 ltR
 R ltR R ltR R ltR R ltR R ltR R ltR R ltR R ltR R ltR R ltR R ltR# ) zT
Aggregations for dashboard / reports from per-business tables on the `cluster` DB.
)annotationsN)datetime	timedelta)Any)connections)InterfaceErrorOperationalError)_q_ident_table_namesc                   V ^8  d   QhRR/# )   returnzdict[str, Any] | None )formats   "DE:\live-kit-agent\livekit_voicebot\backend\config\reporting_utils.py__annotate__r      s     * *&; *    c           
        \        V RR4      ;'       g    RP                  4       p\        V RR4      ;'       g    RP                  4       pT;'       g    TpV'       g   R# RpR F  p \        R,          P                  4       ;_uu_ 4       pVP	                  WCV.4       VP                  4       pV'       g    RRR4        R# RV^ ,          e   \        V^ ,          4      MRRV^,          R9  d   \        V^,          4      MRR	V^,          /uuRRR4       u # 	  R#   + '       g   i     K  ; i  \        \        3 dv   p\        P                  R
TTT4        \        R,          P                  4        M&  \         d    \        P                  R4        Mi ; iT^8X  d    Rp? R#  Rp?EKP  Rp?i\         d    \        P                  R4         R# i ; i)zKMatch logged-in Django user to a row in master `users` (same idea as auth).emailN usernamez
        SELECT business_id, agent_id, role
        FROM users
        WHERE (email = %s OR username = %s)
        LIMIT 1
    defaultbusiness_idagent_idrolezLreporting_utils._legacy_profile_for_user db error attempt=%s ident=%s exc=%sz5reporting_utils._legacy_profile_for_user close failedz/reporting_utils._legacy_profile_for_user failed)   r   )Nr   )getattrstripr   cursorexecutefetchoneintr   r   loggerwarningclose_if_unusable_or_obsolete	Exception	exception)	userr   r   identsqlattemptcurrowexcs	   &        r   _legacy_profile_for_userr.      s   T7D)//R668Ej$/552<<>HXEC 	Y'..00CC0lln	 10 "#a&2D3s1v;$s1vZ/GCFTCF 10 : 7 100  01 	NN^	ZI&DDF Z  !XYZ!|  	NO	ss   )#D4-D 9D4AD 
D4 D1+D41D44GF5E:9F5: FF5F
F55GGGc                    V ^8  d   QhRRRR/# )r   r   r   r   boolr   )r   s   "r   r   r   @   s     K Kc Kd Kr   c                    \        T ;'       g    R 4      P                  4       P                  RR4      pV\        0 Rm4      9   # )r    _>   adminowner
superadminsuper_admin)strlowerreplace	frozenset)r   rs   & r   _admin_roler=   @   s8    DJJB''S1A	IJJJr   c                   V ^8  d   QhRR/# )r   r   r8   r   )r   s   "r   r   r   E   s     G G# Gr   c                 t    \        \        R ,          P                  P                  R4      ;'       g    R4      # )clusterNAMEr   )r8   r   settings_dictgetr   r   r   _cluster_db_namerD   E   s+    {9%3377?EE2FFr   c                    V ^8  d   QhRRRR/# )r   tabler8   r   r0   r   )r   s   "r   r   r   I   s        r   c                Z   \        4       pV'       d	   V '       g   R #  \        R,          P                  4       ;_uu_ 4       pVP                  RW.4       VP	                  4       RJuuRRR4       #   + '       g   i     R# ; i  \
         d    \        P                  RT 4        R # i ; i)Fr@   z
                SELECT 1 FROM information_schema.tables
                WHERE table_schema = %s AND table_name = %s
                LIMIT 1
                Nz$cluster_table_exists failed table=%s)rD   r   r   r   r    r%   r"   r&   )rF   dbr+   s   &  r   cluster_table_existsrI   I   s    		BU#**,,KK
  <<>- -,,,  ?Gs/   #B %A1&
B 1B	<B B !B*)B*c                    V ^8  d   QhRRRR/# )r   r   r!   r   r8   r   )r   s   "r   r   r   ]   s        r   c                6    \        \        V 4      4      w   rpV# )N)r
   r!   )r   r3   chs   &  r   call_history_table_namerM   ]   s    s;/0KAqaIr   c                    V ^8  d   QhRRRR/# )r   legacydict[str, Any]r   ztuple[str, list[Any]]r   )r   s   "r   r   r   b   s     " " "3H "r   c                    \        V P                  R 4      4      '       d   R. 3# V P                  R4      pVf   R. 3# RV.3# )r   r   r   zagent_id = %s)r=   rC   )rO   aids   & r   _agent_sql_fragmentrS   b   sH    6::f%&&2v
**Z
 C
{2vSE!!r   c                    V ^8  d   QhRRRR/# )r   rO   rP   r   r   )r   s   "r   r   r   k   s     9 9. 9^ 9r   c                p   V P                  R 4      pV'       g   \        4       # \        \        V4      4      p\	        V4      '       g   \        4       # \        V 4      w  r4R.pV'       d   VP                  V4       RP                  V4      pR\        V4       RV R2p\        V4      .VOp \        R,          P                  4       ;_uu_ 4       p	V	P                  Wx4       V	P                  4       p
V
'       g   \        4       uuRRR4       # V
w  rrppp\        T;'       g    ^ 4      p\        T;'       g    ^ 4      p\        T;'       g    ^ 4      p\        ^ W,
          4      pV'       d'   \        \        T;'       g    ^ 4      R,          ^4      M^ pR	TR
TRTRTR\        T;'       g    ^ 4      RTR^ R^ R^ R\        T;'       g    ^ 4      R\        T;'       g    ^ 4      /uuRRR4       #   + '       g   i     R# ; i  \          d$    \"        P%                  RT4       \        4       u # i ; i)r   business_id = %s AND at  
        SELECT
            COUNT(*) AS total_calls,
            COALESCE(SUM(transferred = 1), 0) AS transferred_calls,
            COALESCE(SUM(ongoing = 1), 0) AS active_calls,
            COALESCE(AVG(call_duration_secs), 0) AS avg_secs,
            COALESCE(SUM(campaign_id IS NOT NULL AND campaign_id <> 0), 0) AS outbound_calls,
            COALESCE(SUM(campaign_id IS NULL OR campaign_id = 0), 0) AS inbound_calls,
            COALESCE(SUM(
                (call_successful = 1)
                OR LOWER(COALESCE(call_status, '')) IN ('success', 'completed', 'resolved')
            ), 0) AS resolved_calls
        FROM 
        WHERE 
    r@   Ng      N@
totalCallsbotHandledCallsresolvedCallstransferredCallsactiveCallsCountavgHandlingTimeavailMintotalMin	remainMininboundCallsoutboundCallsz+fetch_dashboard_stats failed business_id=%s)rC   _empty_statsrM   r!   rI   rS   appendjoinr	   r   r   r   r    maxroundfloatr%   r"   r&   )rO   bidrF   
agent_fragagent_paramswhere_parts	where_sqlr)   paramsr+   r,   totaltransferredactiveavg_secsoutboundinboundresolvedbot_handledavg_mins   &                   r   fetch_dashboard_statsrz   k   s   
**]
#C~#CH-E&&~26:J%&K:&[)I uo k C S1L1F#**,,KK$,,.C#~	 -,
 QTME(GX

OEk..Q/K8==q)Ha!45K?GeE(--a047;QGe!;"K"C!$4!7AAQGLLq 1X]]!3 -,,,0  FL~s`   .#H 3G3
H *G3:G33G3+G3/G3G3!G3(
H 3H	>H H +H54H5c                   V ^8  d   QhRR/# )r   r   rP   r   )r   s   "r   r   r      s      n r   c                 2    R ^ R^ R^ R^ R^ R^ R^ R^ R^ R	^ R
^ /# )rZ   r[   r\   r]   r^   r_   r`   ra   rb   rc   rd   r   r   r   r   re   re      sC    a1AA1AAQ r   c               (    V ^8  d   QhRRRRRRRR/# )r   rO   rP   	date_from
str | Nonedate_tor   zlist[dict[str, Any]]r   )r   s   "r   r   r      s0     > >>> > 	>r   c                   V P                  R 4      pV'       g   . # \        \        V4      4      p\        V4      '       g   . # V'       d	   V'       gW   \        P
                  ! 4       P                  4       pV\        ^R7      ,
          pVP                  4       pVP                  4       p\        V 4      w  rx. ROp	V'       d   V	P                  V4       RP                  V	4      p
R\        V4       RV
 R2p\        V4      W.VOp \        R,          P                  4       ;_uu_ 4       pVP                  W4       VP!                  4       p. pV Fm  w  ppppV'       g   K  TP                  R\#        V4      R\        T;'       g    ^ 4      R	\        T;'       g    ^ 4      R
\        T;'       g    ^ 4      /4       Ko  	  VuuRRR4       #   + '       g   i     R# ; i  \$         d    \&        P)                  RT4       . u # i ; i)r   )daysrW   am  
        SELECT
            DATE_FORMAT(call_start_time, '%%H:00') AS t,
            COUNT(*) AS calls,
            COALESCE(SUM(
                (call_successful = 1)
                OR LOWER(COALESCE(call_status, '')) IN ('success', 'completed', 'resolved')
            ), 0) AS resolved,
            COALESCE(SUM(transferred = 1), 0) AS transferred
        FROM rX   z+
        GROUP BY t
        ORDER BY t
    r@   timecallsrw   rr   Nz,fetch_dashboard_charts failed business_id=%s)rV   call_start_time >= %s.call_start_time < DATE_ADD(%s, INTERVAL 1 DAY))rC   rM   r!   rI   r   utcnowdater   	isoformatrS   rf   rg   r	   r   r   r   fetchallr8   r%   r"   r&   )rO   r~   r   rk   rF   endstartrl   rm   rn   ro   r)   rp   r+   rowsouttr   rw   rr   s   &&&                 r   fetch_dashboard_chartsr      s   
 **]
#C	#CH-E&&	Goo$$&iQ''OO%	--/26:JK
 :&[)I	 uo k C S9EEF#**,,KK$<<>D(*C37/5(K

AUZZa"CA$6%s;+;+;!'<	 48  -,,,   GM	sC   #G  )AGGG0G
G  G	G  G   #HHc               $    V ^8  d   QhRRRRRR/# )r   rO   rP   recent_minutesr!   r   r   )r   s   "r   r   r      s!     # #^ #S #S #r   c           	        V P                  R 4      pV'       g   ^ # \        \        V4      4      p\        V4      '       g   ^ # \	        V 4      w  rE\        ^\        \        T;'       g    ^4      R4      4      pRR.pV'       d   VP                  V4       RP                  V4      pR\        V4       RV R2p	\        V4      Wf.VOp
 \        R,          P                  4       ;_uu_ 4       pVP                  W4       VP                  4       pV'       d   \        V^ ,          ;'       g    ^ 4      M^ uuR	R	R	4       #   + '       g   i     R	# ; i  \         d    \        P!                  R
4        ^ # i ; i)r   i  rV   a  (
            (ongoing = 1 AND call_start_time >= (NOW() - INTERVAL %s MINUTE))
            OR (
              call_end_time IS NULL
              AND call_start_time IS NOT NULL
              AND call_start_time >= (NOW() - INTERVAL %s MINUTE)
            )
        )rW   z
        SELECT COUNT(*) FROM rX   rY   r@   Nzfetch_active_calls_count failed)rC   rM   r!   rI   rS   rh   minrf   rg   r	   r   r   r   r    r%   r"   r&   )rO   r   rk   rF   rl   rm   minutesrn   ro   r)   rp   r+   r,   s   &&           r   fetch_active_calls_countr      sB   
**]
#C#CH-E&&26:J!S^00q1489G	
K :&[)I&uo. /k C S7ClCF#**,,KK$,,.C'*3s1v{{# -,,,  :;s6   	#E ,>D>+D>3
E >E		E E  E65E6c               @    V ^8  d   QhRRRRRRRRRRR	RR
RRRRRRR/
# )r   rO   rP   limitr!   offset
start_dater   end_dater   customer_numbercampaign_idconversation_idr   z tuple[list[dict[str, Any]], int]r   )r   s   "r   r   r     sz     ] ]] ] 	]
 ] ] ]  ] ]  ] &]r   c          	        V P                  R 4      p	V	'       g   . ^ 3# \        \        V	4      4      p
\        V
4      '       g   . ^ 3# R.p\        V	4      .p\	        V 4      w  rV'       d#   VP                  V4       VP                  V4       V'       d#   VP                  R4       VP                  V4       V'       d#   VP                  R4       VP                  V4       V'       d2   VP                  R4       VP                  \        V4      RV R2.4       V'       d*   VP                  R4       RV R2pVP                  W.4       V'       dM   VP                  R4       TP                  \        V4      P                  4       '       d   \        V4      MT4       V'       d#   VP                  R4       VP                  V4       R	P                  V4      pR
\        V
4       RV 2pR\        V
4       RV R2p \        R,          P                  4       ;_uu_ 4       pVP                  VV4       VP                  4       pV'       d   \        V^ ,          ;'       g    ^ 4      M^ pVP                  V. VO\        V4      N\        V4      N4       VP                   Uu. uF  pV^ ,          NK  	  pp. pVP!                  4        F2  p\#        \%        VV4      4      pVP                  \'        V4      4       K4  	  VV3uuRRR4       # u upi   + '       g   i     R# ; i  \(         d    \*        P-                  R4       . ^ 3u # i ; i)r   rV   r   r   z3(CAST(agent_id AS CHAR) = %s OR agent_name LIKE %s)%z4(phone_number LIKE %s OR agent_phone_number LIKE %s)zcampaign_id = %szconversation_id = %srW   zSELECT COUNT(*) FROM z WHERE a  
        SELECT
            id,
            conversation_id,
            agent_id,
            agent_name,
            phone_number,
            agent_phone_number,
            customer_name,
            call_start_time,
            call_end_time,
            call_duration_secs,
            call_successful,
            call_status,
            ongoing,
            transferred,
            sentiment,
            summary,
            recording_url,
            conversation_data,
            campaign_id
        FROM rX   zO
        ORDER BY call_start_time DESC, id DESC
        LIMIT %s OFFSET %s
    r@   Nzfetch_call_history_page failed)rC   rM   r!   rI   rS   rf   extendr8   isdigitrg   r	   r   r   r   r    descriptionr   dictzip_serialize_call_rowr%   r"   r&   )rO   r   r   r   r   r   r   r   r   rk   rF   whererp   role_agent_fragrole_agent_paramslikero   	count_sqllist_sqlr+   	total_rowrq   ccolsconversationsr,   ds   &$$$$$$$$                  r   fetch_call_history_pager     s    **]
#C1u#CH-E&&1u*+ES
F)<V)D&O_%'(,-j!EFhJKs8}(1o67B	
 ?#1%tl#'(#k*:*B*B*D*Dc+&+V+,o&U#I''8	{KI* uo k -H6#**,,KK	6*I.7C	!))*QEKK"DF"DCJ"DF"DE"%//2/QAaDD/D224M||~T3($$%8%;< & !%' -,
 3 -,,  9:1usJ   >#L% !?L!AL"L4AL
L% LL"	L% "L% %$MMc                    V ^8  d   QhRRRR/# )r   r   rP   r   r   )r   s   "r   r   r   ~  s      > n r   c                >   / R V P                  R 4      bRV P                  R4      bRV P                  R4      bRV P                  R4      bRV P                  R4      bRV P                  R4      bRV P                  R4      bRV P                  R4      '       d    V P                  R4      P                  4       MRbR	V P                  R	4      '       d    V P                  R	4      P                  4       MRbR
V P                  R
4      bRV P                  R4      bRV P                  R4      bR\        V P                  R4      4      bRV P                  R4      bRV P                  R4      bRV P                  R4      bRV P                  R4      bRV P                  R4      /C# )idr   r   
agent_namephone_numberagent_phone_numbercustomer_namecall_start_timeNcall_end_timecall_duration_secscall_successfulcall_statusrr   	sentimentsummaryrecording_urlconversation_datar   )rC   r   r0   )r   s   &r   r   r   ~  s   aeeDk155!23 	AEE*% 	aeeL)	
 	n- 	aee$89 	/ 	55"## 55!23==? 	55!! /99; 	aee$89 	155!23  	quu]+!" 	tAEE-01#$ 	QUU;'%& 	155#'( 	/)* 	QUU#67+, 	quu]+- r   )__doc__
__future__r   loggingr   r   typingr   	django.dbr   django.db.utilsr   r   apps.cluster.dynamic_tablesr	   r
   	getLogger__name__r"   r.   r=   rD   rI   rM   rS   rz   re   r   r   r   r   r   r   r   <module>r      su    #  (  ! < >			8	$*ZK
G(
"9x >B#L]@r   