
    I;i"                        d dl mZmZmZ d dlmZ d dlmZmZm	Z	 d dl
m
Z
mZ d dlmZ d dlmZmZmZmZ d dlmZ d dlmZmZmZmZmZ  ed	d
g      ZdededefdZej?                  de       ee       ee       edd       edd       edd       edd      fdedede
dz  de
dz  de dz  de fd       Z!ej?                  d e       ee       ee       ed       ed      fdedede
dz  de
dz  fd!       Z"y)"    )	APIRouterDependsQuery)Session)funcand_case)datetime	timedelta)get_db)UserDeliveryNotificationTemplate)get_current_user)AnalyticsResponseAnalyticsOverviewChannelStatsTemplateStatsTimelineStatsz
/analytics	analytics)prefixtags	numeratordenominatorreturnc                 2    |dk(  ryt        | |z  dz  d      S )z4Calculate percentage rate, handling division by zeror   g        d      )round)r   r   s     +/var/www/notifications/app/api/analytics.pycalculate_rater"      s$    a)k)S0!44     )response_modelNzStart date for analytics)descriptionzEnd date for analyticsz'Filter by channel (email, telegram, vk)dayz)Timeline grouping: hour, day, week, monthcurrent_userdb	date_fromdate_tochannelgroup_byc                    |st        j                         }|s|t        d      z
  }|j                  t              j                  t              j                  t        j                  | j                  k(        }|r"|j                  t        j                  |k\        }|r"|j                  t        j                  |k        }|r"|j                  t        j                  |k(        }|j                  t        j                  t        j                        j                  d      t        j                  t        t        j                   dk(  df            j                  d      t        j                  t        t        j"                  j%                  d      df            j                  d      t        j                  t        t        j&                  j%                  d      df            j                  d	            j)                  t              j                  t              j                  t        j                  | j                  k(  |rt        j                  |k\  nd
|rt        j                  |k  nd
|rt        j                  |k(  nd
      j+                         }|j,                  xs d}|j.                  xs d}	|j0                  xs d}
|j2                  xs d}t5        ||	|
|t7        |	|      t7        |
|	      t7        ||
            }|j                  t        j                  t        j                  t        j                        j                  d      t        j                  t        t        j                   dk(  df            j                  d      t        j                  t        t        j"                  j%                  d      df            j                  d      t        j                  t        t        j&                  j%                  d      df            j                  d            j                  t              j                  t        j                  | j                  k(  |rt        j                  |k\  nd
|rt        j                  |k  nd
      j9                  t        j                        j;                         }|D cg c]  }t=        |j                  |j>                  |j@                  |jB                  |jD                  t7        |j@                  |j>                        t7        |jB                  |j@                        t7        |jD                  |jB                               }}|j                  tF        j                  tF        jH                  t        j                  t        j                        j                  d      t        j                  t        t        j                   dk(  df            j                  d      t        j                  t        t        j"                  j%                  d      df            j                  d      t        j                  t        t        j&                  j%                  d      df            j                  d            j                  t        tF        j                  t        jJ                  k(        j                  t        t        j                  t        jL                  k(        j                  tF        j                  | j                  k(  |rt        j                  |k\  nd
|rt        j                  |k  nd
|rt        j                  |k(  nd
      j9                  tF        j                  tF        jH                        jO                  t        j                  t        j                        jQ                               jS                  d      j;                         }|D cg c]  }tU        |j                  |jH                  |j>                  |j@                  |jB                  |jD                  t7        |j@                  |j>                        t7        |jB                  |j@                        t7        |jD                  |jB                        	       }}dddddjW                  |d      }|j                  t        jX                  |t        j                        j                  d      t        j                  t        j                        j                  d      t        j                  t        t        j                   dk(  df            j                  d      t        j                  t        t        j"                  j%                  d      df            j                  d      t        j                  t        t        j&                  j%                  d      df            j                  d            j                  t              j                  t        j                  | j                  k(  |rt        j                  |k\  nd
|rt        j                  |k  nd
|rt        j                  |k(  nd
      j9                  t        jX                  |t        j                              jO                  t        jX                  |t        j                              j;                         }|D cg c]`  }t[        |j\                  r|j\                  j_                         nd|j>                  |j@                  |jB                  |jD                        b }}ta        ||||      S c c}w c c}w c c}w )z4
    Get comprehensive analytics for the tenant
       days
total_sentsent   total_deliveredNtotal_openedtotal_clickedTr   r2   r5   r6   r7   delivery_rate	open_rate
click_rate	deliveredopenedclicked)r,   r3   r<   r=   r>   r9   r:   r;   
   )	idnamer3   r<   r=   r>   r9   r:   r;   hourr'   weekmonth)rB   r'   rC   rD   periodr$   )rE   r3   r<   r=   r>   )overview
by_channelby_templatetimeline)1r
   utcnowr   queryr   joinr   filter	tenant_id
created_atr,   r   countr@   labelr	   status	opened_atisnot
clicked_atselect_fromfirstr2   r5   r6   r7   r   r"   r-   allr   r3   r<   r=   r>   r   rA   template_idnotification_idorder_bydesclimitr   get
date_truncr   rE   	isoformatr   )r(   r)   r*   r+   r,   r-   
base_queryoverview_statsr2   r5   r6   r7   rF   channel_stats_rawrowrG   template_stats_rawrH   date_trunc_formattimeline_stats_rawrI   s                        r!   get_analyticsrh      sU    //#iR00	 (#((6==,"8"88J
 &&|'>'>)'KL
&&|'>'>''IJ
 &&x'7'77'BC
 XX

8;;%%l3

4F2A678>>?PQ

4++11$7;<=CCNS

4,,2248!<=>DD_U	
 k(DD.vv,"8"88099,t.57*4'.G#D	0
 eg   **/aJ$449O!..3!L"005AM '!#$_jA ?!->H 

8;;%%f-

4F2A678>>{K

4++11$7;<=CCHM

4,,2248!<=>DDYO d<,"8"88099,t.57*4 hx  . %  	KKmm::KK(A$SZZ?%ckk3::>		
J  

8;;%%f-

4F2A678>>{K

4++11$7;<=CCHM

4,,2248!<=>DDYO d<(@(@@A
d8\__(@(@@Afl444099,t.57*4'.G#D	
 hx{{HMM*htzz(++&++-.eBi	ce# > &  	vvmm::KK(A$SZZ?%ckk3::>
	
K $ 	
 
c(E  )<+B+BCII(S

8;;%%f-

4F2A678>>{K

4++11$7;<=CCHM

4,,2248!<=>DDYO d<,"8"88099,t.57*4'.G#D	
 ht0,2I2IJKht0,2I2IJK	ce . &	  	-0ZZ3::'')Rmm::KK	
	H 	 	 iDN	s   	B&m=B1nA%nz	/overviewc                 `   |st        j                         }|s|t        d      z
  }|j                  t	        j
                  t        j                        j                  d      t	        j
                  t        t        j                  dk(  df            j                  d      t	        j
                  t        t        j                  j                  d      df            j                  d      t	        j
                  t        t        j                  j                  d      df            j                  d	            j                  t              j                  t               j#                  t         j$                  | j$                  k(  t         j&                  |k\  t         j&                  |k        j)                         }|j*                  xs d
}|j,                  xs d
}|j.                  xs d
}|j0                  xs d
}t3        ||||t5        ||      t5        ||      t5        ||            S )zGet overview statistics onlyr/   r0   r2   r3   r4   r5   Nr6   r7   r   r8   )r
   rJ   r   rK   r   rP   r   r@   rQ   r	   rR   rS   rT   rU   rV   rL   r   rM   rN   rO   rW   r2   r5   r6   r7   r   r"   )	r(   r)   r*   r+   statsr2   r5   r6   r7   s	            r!   get_overviewrk      s    //#iR00	HH

8;;%%l3

4F2A678>>?PQ

4++11$7;<=CCNS

4,,2248!<=>DD_U	
 k(DD.vv,"8"889,7*0 eg 
 !!&QJ++0qO%%*L'',1M'!#$_jA ?!-> r#   )#fastapir   r   r   sqlalchemy.ormr   
sqlalchemyr   r   r	   r
   r   app.db.sessionr   app.db.modelsr   r   r   r   app.api.depsr   app.api.schemasr   r   r   r   r   routerintfloatr"   r^   strrh   rk    r#   r!   <module>rx      sn   - - " ' ' ( ! @ @ )  
,k]	;5c 5 5 5 B01 !12&/!&t9S!T$T7OP2[\%-XYbbb $b _	b
 4Zb b 2bJ K(9: !12&/!&t$T{	%%% $% _	% ;%r#   