
    4ʜh%                     l   d dl Z d dlmZ d dlmZ d dlmZmZmZmZm	Z	 d dl
m
Z
mZ d dlmZmZmZmZmZ d dlZd dlmZmZ d dlmZ d d	lmZ  ej4                  e      Z	 	 dd
ede
de
deeef   fdZ	 	 dd
ede
de
deeeef      fdZ 	 	 dd
ede
de
deeeef      fdZ!	 	 dd
ede
de
deeef   fdZ"y)    N)extract)Session)funcdistinctcastr   text)datetime	timedelta)DictAnyOptionalListTuple)TransferResultCallsLog)convert_to_local_time)Advisordb
start_dateend_datereturnc                 h   	 | j                  t        j                  t        j                  t        j
                        j                  d            j                  t        j                  |k\  t        j                  |k        }|"|j                  t        j                  |k(        }|j                  t        j                        }|j                         }d}d}|D ]  }	|	j                  dk(  r|dz  }|dz  } ||dS # t        $ r'}
t        j                  dt!        |
               d}
~
ww xY w)zM
    Get counts of unique and repetitive calls for the given date range.
    
call_countNr      unique
repetitivez0Error retrieving unique/repetitive call counts: )queryr   caller_numberr   countidlabelfiltercall_date_timetransferred_togroup_byallr   	Exceptionloggererrorstr)r   r   r   user
advisor_idr   calls_in_rangeunique_callsrepetitive_callscalles              P/var/www/html/DP/alpha_backend/app/advisor_service/services/dashboard_service.pyget_unique_repetitive_countsr4      s   """JJx{{#)),7
 &##z1##x/
 	 !LL!8!8J!FGE
 x556"D!#! A% 	 # #*
 	

  GAxPQs   C>D 	D1
"D,,D1c                 `   	 i }|}||k  r.|j                  d      }ddd||<   |t        d      z  }||k  r.| j                  t        j                  dt
        j                        j                  d      t
        j                  t        j                  t
        j                        j                  d            j                  t
        j                  |k\  t
        j                  |k        }|r"|j                  t
        j                  |k(        }|j                  t        j                  dt
        j                        t
        j                        j                         }	|	D ]M  }
|
j                  j                  d      }|
j                   dk(  r||   d	xx   dz  cc<   >||   d
xx   dz  cc<   O g }|j#                         D ]   \  }}|j%                  ||d	   |d
   d       " |j'                  d        |S # t(        $ r'}t*        j-                  dt/        |               d}~ww xY w)z
    Get daily counts of unique and repetitive calls for the given date range.
    Returns a list of dictionaries with date, unique and repetitive call counts.
    %b-%dr   r   r   )daysday	call_dater   r   r   )dater   r   c                 4    t        j                  | d   d      S )Nr:   r6   )r	   strptimexs    r3   <lambda>z'get_daily_call_counts.<locals>.<lambda>s   s    ("3"3AfIw"G    keyz$Error retrieving daily call counts: N)strftimer
   r   r   
date_truncr   r$   r"   r   r    r!   r#   r%   r&   r'   r9   r   itemsappendsortr(   r)   r*   r+   )r   r   r   r,   r-   daily_countscurrent_datedate_strdaily_caller_countsr   recordresultcountsr2   s                 r3   get_daily_call_countsrO   <   s
   2!h&#,,W5H01%CL"I1--L h&
 !hhOOE8#:#:;AA+N""JJx{{#)),7
 &##z1##x/
	 	 "5"<"<X=T=TXb=b"c
 $,,OOE8#:#:;""
 #% 	
 F''009H  A%X&x0A50X&|494   , 2 2 4HfMM  *$\2  !5 	GH ;CF8DEs   6G= GG= =	H-"H((H-c                     	 | j                  t        dt        j                        j	                  d      t        j                  t        j                        j	                  d            j                  t        j                  |k\  t        j                  |k        }|"|j                  t        j                  |k(        }|j                  t        dt        j                              }|j                         }||z
  j                  dz   }g }|D ]  }	t        |	j                        }
t        j                   |j#                         t        j$                  j'                         j)                  |
            }t+        | |      }|j                  }|dd}|	j,                  }|j/                  ||d        |j1                  d	 
       |S # t2        $ r'}t4        j7                  dt9        |               d}~ww xY w)z
    Get average call volume by hour of day for the given date range.
    Returns a list of dictionaries with time and calls count.
    hourr   Nr   )rQ   02dz:00)timecallsc                 B    t        | d   j                  d      d         S )NrS   :r   )intsplitr=   s    r3   r?   z(get_hourly_call_volume.<locals>.<lambda>   s    #aiooc&:1&=">r@   rA   z%Error retrieving hourly call volume: )r   r   r   r$   r"   r   r    r!   r#   r%   r&   r'   r7   rW   rQ   r	   combiner:   minrS   replacer   r   rF   rG   r(   r)   r*   r+   )r   r   r   r,   r-   r   hourly_countsdays_in_rangerM   rL   utc_hoursample_date
local_date
local_hourtime_strtotal_callsr2   s                    r3   get_hourly_call_volumerd   {   s   'FH334::6BJJx{{#)),7
 &##z1##x/
 	 !LL!8!8J!FGE FH334
 		!J.44q8#F6;;'H"**:??+<hll>O>O>Q>Y>Y_g>Y>hiK.r;?J#J$S)-H ++KMM8kBC $ 	>? <SVHEFs   G
G 	G="G88G=c           	      6	   	 | j                  t        j                  t        j                              j                  t        j                  |k\  t        j                  |k        }| j                  t        j                  t        j                              j                  t        t        j                  t        j                  k(        j                  t        j                  |k\  t        j                  |k  t        j                  dk(        }| j                  t        j                  t        d                  j                  t        j                  |k\  t        j                  |k  t        j                  j                  d            }|rY| j                  t              j                  t        j                  |k(        j!                         }|r|j"                  r|j                  t        j$                  |j"                  k(  t        j                  |k\  t        j                  |k        }| j                  t        j                  t        d                  j'                  t              j                  t        t        j                  t        j                  k(        j                  t        j                  dk(  t        j$                  |j"                  k(  t        j                  |k\  t        j                  |k  t        j                  j                  d            }| j                  t        j                  t        j                              j                  t        t        j                  t        j                  k(        j                  t        j$                  |j"                  k(  t        j                  |k\  t        j                  |k  t        j                  dk(        }|j)                         xs d}	|j)                         xs d}
|j)                         xs d}t+        |dz        }t+        |dz        }||d}|	|
|dS # t,        $ rD}t.        j1                  d	t3        |              t5        t7        j8                                 d}~ww xY w)
z
    Get summary statistics for the given date range.
    
    Args:
        db: Database session
        start_date: Start date for filtering
        end_date: End date for filtering
        advisor_id: Optional advisor ID to filter calls by advisor
    Fz3EXTRACT(EPOCH FROM CAST(call_duration AS INTERVAL))NTr   <   )minutesseconds)rc   total_missed_callsaverage_call_timez%Error retrieving summary statistics: )r   r   r    r   r!   r#   r$   r   jointwilio_call_idstatusavgr   call_durationisnotAdvisorModelfirstphone_numberr%   select_fromscalarrW   r(   r)   r*   r+   print	traceback
format_exc)r   r   r   r,   r-   total_calls_querymissed_calls_queryavg_duration_queryadvisorrc   ri   avg_duration_secondsrg   rh   avg_call_timer2   s                   r3   get_summary_statisticsr      s    UHHTZZ%<=DD##z1##x/

  XXdjj1B1B&CDII++~/L/LL &++z9++x7"))U2 	  XXHHJK
 &##z1##x/""((.
	 	 hh|,33LOOz4QRXXZG7//$5$<$<++w/C/CC++z9++x7%! &(XXHHRS& +h'""11X5L5LL) &"))T1++w/C/CC++z9++x7**006 # &(XXdjj9J9J.K%L%Q%Q  //>3P3PP& f //73G3GG //:= //8;&--6	 # (..05A/668=A188:?a*b01*R/0
 '"4!.
 	
  <SVHEFi""$%s   QQ 	R?RR)NN)#rw   sqlalchemy.sqlr   sqlalchemy.ormr   
sqlalchemyr   r   r   r   r	   r
   typingr   r   r   r   r   loggingapp.common.modelsr   r   )app.advisor_service.services.time_servicer   r   rq   	getLogger__name__r)   r+   rW   r4   rO   rd   r    r@   r3   <module>r      s1    " " : : ( 3 3  6 K 5			8	$ ,,, , 
#s(^,d === = 
$sCx.=F 222 2 
$sCx.2p eee e 
#s(^er@   