
    h              
          d dl m Z mZ d dlmZ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 d dlmZ d dlZd dlZd dlZd dlmZmZ d dlZd dlmZ d dlZ	 d dlZ ej:                  ej<                  j>                         d	Z! ee"d
      Z# ee#jH                  dd      e#_$        dZ% ejL                  de%      Z'	  ejP                  e'd	       ejT                  j[                  e'd      Z.de. e#j^                  d<   de#j^                  d<    ejL                  dd      Z0	  ejP                  ejT                  jc                  e0      d	       ejT                  je                  e0      s e3e0d      ji                           ejj                         Z6e6jo                  ejp                          e9e6jt                        D ]  Z;e6jy                  e;         ee0      Z=e=jo                  ejp                         e=j}                   ej~                  d              ej                         ZAeAjo                  ej                         eAj}                   ej~                  d             e6j                  e=       e6j                  eA       g e#j                  _:        d	e#j                  _E        e#j                  jo                  ejp                          e	e#      ZF G d deFj                        ZH G d d eFj                        ZI G d! d"eFj                        ZJe#j                         5   eeFj                        ZMd#eMj                         v rmeMj                  d#      D  cg c]  } | d$   	 c} ZPd%D ]F  ZQeQePvs	 eFj                  j                         5 ZSeSj                   ed&eQ d'             ddd       H d*eMj                         v raeMj                  d*      D  cg c]  } | d$   	 c} ZWd+eWvr;	 eFj                  j                         5 ZSeSj                   ed,             ddd       eFj                          ddd       d.eYd/eYfd0ZZd1eYd/eYfd2Z[d+eYd/eYfd3Z\d4 Z]dcd5Z^d6 Z_d7e`fd8Zad9 Zbd7e`fd:Zcd;eYfd<Zdd=e`fd>Zed? Zfe#j                  d@dAgB      dC        Zhe#j                  dDdAgB      dE        ZidF ZjdG ZkdH ZldI ZmdJ ZndK ZodddLZpdM Zqe#j                  dN      dO        ZrdedPZsdQ ZtdR Zu ev ejL                  dSdT            Zw ev ejL                  dUdV            Zxe#j                  dW      dX        Zye#j                  dY      dZ        Zze#j                  d[      d\        Z{e#j                  d]      d^        Z|e"d_k(  re#j                  d`dadb       yy# e $ r Y !w xY w# e)$ r5 ejT                  jW                  d      Z, ejP                  e,d	       e,Z'Y w xY w# e)$ rR ejT                  j[                  e'd      Z0ejT                  je                  e0      s e3e0d      ji                          Y w xY wc c} w # 1 sw Y   xY w# e $ r,ZUe#j                  j                  d(eQ d)eU        Y dZU[UdZU[Uww xY wc c} w # 1 sw Y   gxY w# e $ r)ZUe#j                  j                  d-eU        Y dZU[UdZU[Uww xY w# 1 sw Y   xY w)f    )datetime	timedelta)Flaskrequestjsonifyrender_templatemake_response)
SQLAlchemy)inspectfunctextcasedesc)ProxyFixN)Counterdefaultdict)WatchedFileHandlerTz/pexip-sink/static)static_url_path   )x_protox_hostz/var/lib/pexip-event-sinkPEXIP_DATA_DIR)exist_okz~/.pexip-event-sinkzpexip_events.dbz
sqlite:///SQLALCHEMY_DATABASE_URIFSQLALCHEMY_TRACK_MODIFICATIONSPEXIP_LOG_PATHz/var/log/pexip-event-sink.logazpexip-event-sink.logz/%(asctime)s %(levelname)s %(name)s: %(message)sz%(levelname)s %(message)sc                   f   e Zd Zej	                  ej
                  d      Zej	                  ej                  d            Zej	                  ej                  d            Z	ej	                  ej                  d            Z
ej	                  ej                  d            Zej	                  ej                  d            Zej	                  ej                        Zej	                  ej
                        Zej	                  ej
                        Zej	                  ej                  d            Zej	                  ej$                        Zej	                  ej(                  d      Zej	                  ej                  d            Zej	                  ej.                  ej2                        Zy)	EventTprimary_key      d   2   )defaultN)__name__
__module____qualname__dbColumnIntegeridStringsession_keyconference_namecall_idparticipant_idvendorFloatconnect_timerx_bandwidthtx_bandwidthcall_quality_nowTextpacket_loss_detailsBooleanactivestatusDateTimer   utcnow	timestamp     /var/www/html/pexip-sink/app.pyr   r   U   s,   	2::4	0B))BIIcN+Kii		#/Oii		#'GYYryy~.NYYryy~&F99RXX&L99RZZ(L99RZZ(Lyy2/))BGG,YYrzz4Y0FYYryy~&F		"++x	?IrB   r   c                      e Zd Zej	                  ej
                  d      Zej	                  ej                  d      d      Zej	                  ej                  d      d      Z	ej	                  ej                  d      d      Z
ej	                  ej                        Zej	                  ej                        Zej	                  ej                  ej                   d      Zy)
FinalStatsTr    r$   )indexr#   )r&   rF   N)r'   r(   r)   r*   r+   r,   r-   r.   r2   r0   r1   r9   audio_statsvideo_statsr>   r   r?   r@   rA   rB   rC   rE   rE   e   s    	2::4	0BYYryy~TY:Nii		#di;Oii		#di3G))BGG$K))BGG$K		"++xd	KIrB   rE   c                   `   e Zd ZdZej                  ej                  d      Zej                  ej                  d      dd      Z	ej                  ej                  d      d      Z
ej                  ej                  ddej                  	      Zej                  ej                  d      Zy
)WorkerVMLoadworker_vm_loadTr    @   F)rF   nullable   )rM   )rF   rM   r&   N)r'   r(   r)   __tablename__r*   r+   r,   r-   r.   node_id	node_namer>   r   r?   r@   r4   
media_loadrA   rB   rC   rJ   rJ   o   s    $M	2::4	0Bii		"TEiBG		"))C.5	9I		"++TE8??	[I288e4JrB   rJ   eventname)r1   r2   r/   zALTER TABLE event ADD COLUMN z TEXTzCould not add column 'z': final_statsr1   z/ALTER TABLE final_stats ADD COLUMN call_id TEXTz+Could not add column 'FinalStats.call_id': uareturnc                 `    t        j                  d| xs d      }|r|j                  d      S dS )Nz#(Chrome|Firefox|Safari|Edge|PexRTC) r   unknown)researchgroup)rV   vendor_matchs     rC   _safe_vendorr_      s/    99CRX2NL$0<a ?i?rB   sc                 $   | syt        j                  dd| xs d      } | j                  d      d   j                  d      d   } | j                  d      d   } t        j                  dd	|       j                         j	                         } | xs dS )
NrZ   z	(?i)^sip:rY   ;r   ?@z\s+ )r[   subsplitstriplowerr`   s    rC   _canonical_aliasrk      s~    
|Rb)A	Qc"1%A	QA
vsA$$&,,.A>	rB   c                 0   t         j                  j                  t         j                  | k(  t         j                  d k7        j                  t         j                  j                               j                         }|r|j                  r|j                  S dS )NrZ   )	r   queryfilterr1   r0   order_byr@   r   first)r1   rows     rC   _latest_alias_for_callrr      si    	(%*?*?4*G	H	%//&&(	)		  $'3+>+>CNINrB   c                 $    | d k7  | dk7  z  | dk7  z  S NrY   rZ   rA   cols    rC   _valid_call_idrw      s    4KC2I&#*:;;rB   c                    t         j                  j                  t        j                  t        j                  t        j                              j                  t        j                  dk(  t        t        j                              }| r"|j                  t        j                  | k\        }|j                  t        j                        j                         }|D ch c]  }|d   	 c}S c c}w )Nconference_endedr   )r*   sessionrm   r   r1   r   maxr@   rn   r=   rw   group_byall)since_dtqrowsrs       rC   _ended_call_ids_sincer      s    


(AB	LL..5==)

  HHU__01::emm$((*DQAaDs   C,c           	      B   | sg S g }| D ]  }t         j                  j                  t         j                  |k(  t         j                  dk(  t         j
                  d k7  t         j
                  dk7  t         j
                  dk7        j                  t         j                  j                               j                         }|j                  |r|j
                  nd        t        |      }t        |j                         d       D cg c]
  \  }}||d c}}S c c}}w )NFrY   ry   Unknownc                     | d    | d   fS )Nr   r   rA   xs    rC   <lambda>z._per_call_disconnect_reasons.<locals>.<lambda>   s    YZ[\Y]X]_`ab_cWd rB   key)reasoncount)r   rm   rn   r1   r<   r=   ro   r@   r   rp   appendr   sorteditems)call_idsreasonscidlatest_disccountsr   cs          rC   _per_call_disconnect_reasonsr      s    	G IKKV$%$" 22 Xeoo**,-UW 	 	[{))iHI WF28Md2ef$!Qq1%fffs   Dinfoc                     dD ]F  }| j                  |      }t        |t              s%|j                         s6|j                         c S  y )N)r8   quality_nowquality)get
isinstancestrrh   )r   kvs      rC   _extract_qualityr      sA    ; HHQKa!'')779 rB   c                     t        | t              r| S t        | t              r| gS t        | t        t        t
        f      rd| igS g S )Nvalue)r   listdictintfloatr   r   s    rC   _normalize_packet_loss_to_arrayr      sD    !T!Ts
!c5#&'!~IrB   c                    d| v r$	 t        | d         }t        j                  |      S d| v r$	 t        | d         }t        j                  |      S | j	                  d      xs | j	                  d      }|t        j                  t        |            S y# t        $ r t        j                  g       cY S w xY w# t        $ r t        j                  g       cY S w xY w)Npacket_loss_historypacket_losspacket_loss_mspacket_loss_nowrY   )r   jsondumps	Exceptionr   )r   arrsingles      rC   _extract_packet_lossr      s    $	"1$7L2MNC::c?" 	"1$}2EFC::c?" XX&'F4884E+FFzz9&ABB  	"::b>!	"  	"::b>!	"s"   "B "B: B76B7:CCr2   c                 T   t         j                  j                  t         j                  | k(        j	                  t         j
                  j                               j                         }|r|j                  dvr|j                  nd}|r|j                  dvr|j                  nd}||fS )zLReturn (alias, call_id) from the most recent Event row for this participant.rt   N)
r   rm   rn   r/   ro   r@   r   rp   r0   r1   )r2   ealiasr   s       rC   '_resolve_recent_context_for_participantr      s     		!!^3	4	%//&&(	)		  #$(9(9AV(VQ\`E!"qyy?T'TQYYZ^C#:rB   tc                 J    t        | t              r| j                  dd        | S )N
jitter_avg)r   r   pop)r   s    rC   _strip_jitter_totalsr      s    !T	lD!HrB   c                    d}t         j                  j                  t         j                  | k(        }t	        |      r#|j                  t         j
                  |k(        }n)|r'|dk7  r"|j                  t         j                  |k(        }|j                  t        t         j                              }|j                         }|rt        ||j                  z
  j                               |k  r	 |j                  rt        j                  |j                        ng }		 |j"                  rt        j                  |j"                        ng }
|r|	j%                  |       |r|
j%                  |       t        j&                  |	      |_        t        j&                  |
      |_        ||_	        t(        j*                  j-                  |       yt(        j*                  j-                  t        | |t	        |      r|ndt        j&                  |xs g       t        j&                  |xs g       |             y# t         $ r g }	Y 'w xY w# t         $ r g }
Y w xY w)z
    For streams_destroyed arriving as separate audio/video events, merge them into one FinalStats
    row by 'upserting' within a short window for the same participant/call.
       rZ   N)r2   r0   r1   rG   rH   r@   )rE   rm   rn   r2   rw   r1   r0   ro   r   r@   rp   abstotal_secondsrG   r   loadsr   rH   extendr   r*   rz   add)r2   	conf_namer1   
audio_list
video_listts
WINDOW_SECr   fsexisting_audioexisting_videos              rC   _merge_or_create_finalstatsr     s   
 J
 9 9^ KLAgHHZ''723 i/33y@AA	

4
,,-.A	
B	c2$3356*D	 ;=>>TZZ7rN	 ;=>>TZZ7rN !!*-!!*-N3N3


r 	

z)%-g6GD

:#34

:#34
 	!  	 N	   	 N	 s$   /-H5 -I 5IIIIz/pexip-sink/event_sinkPOST)methodsc                     t        j                  d      } t        j                  j	                  d|         | st        ddi      dfS | j                  dd      }| j                  d	i       xs i }|j                  d
      xs; |j                  d      xs( |j                  d      xs |j                  d      xs d}t        |      }|j                  d      xs |j                  d      xs d}|j                  d      }t        |t              r|r|d   n|j                  dd      }t        |j                  dd            }| }	t        j                         }
	 |dv r|j                  dd      }|j                  dd      }|j                  dd      }|dk(  r*|j                  d      xs t        |      }t        |      }nt        |      }t        |      }t        j                   j#                  |	      j%                         }|r5|j&                  du r't        j                  j	                  d| d|	        n|r|xs |j(                  |_        |r|dk7  r||_        |xs |j,                  |_        |r||_        t        |t0        t2        f      r|dkD  rt1        |      |_        t        |t0        t2        f      r|dkD  rt1        |      |_        |r||_        |r||_        d|_        |
|_        nt>        j@                  jC                  t        |	|||||t        |t0        t2        f      rt1        |      nd t        |t0        t2        f      rt1        |      nd |xs d|d|
             n|d k(  r|j                  d!d"      }t        j                   j#                  |	      j%                         }|rDd|_        ||_"        |
|_        |r|j*                  d#v r||_        |rF|j(                  d#v r8||_        n0t>        j@                  jC                  t        |	||||d||
$             tF        s}t        |j                  d%      t              r^|d%   D cg c]  }|j                  d&      d'k(  s| }}|d%   D cg c]  }|j                  d&      d(k(  s| }}tI        ||||||
       |rE|dk7  r?t>        j@                  j!                  t              jK                  t        j&                  dk(  t        j*                  d k(  t        j*                  d)k(  z  t        j*                  dk(  z  z  t        j(                  |k(  z        jM                  t        j*                  |id*       n|d+k(  r|j                  d      |j                  d      |j                  d
      |j                  d      fD cg c]  }|rt        |       }}|j                  d      |j                  d      fD cg c]  }|s|	 }}t        j                   jK                  t        j&                  dk(        }|rQ|rO|jK                  t        j(                  jO                  |      t        j*                  jO                  |      z        }na|r/|jK                  t        j(                  jO                  |            }n0|r.|jK                  t        j*                  jO                  |            }|jM                  dd+|
d,d*       n|d-k(  r|dk(  stQ        |      stS        |      \  }}|r|}|r|}g g }}|j                  d%g       D ]N  }|j                  d&      d'k(  r|jU                  |       )|j                  d&      d(k(  s>|jU                  |       P tI        ||||||
       	 t>        j@                  j!                  t              jK                  t        jV                  |	k(        jM                  dd+|
d,d*       t>        j@                  j]                          t        d2d3i      d4fS c c}w c c}w c c}w c c}w # tX        $ r/}t        j                  j[                  d.|	 d/|        Y d }~td }~ww xY w# tX        $ rL}t        j                  j_                  d0| d1       t>        j@                  ja                          Y d }~d }~ww xY w)5NTsilentzReceived event: errorzInvalid datai  rS   rZ   datadestination_alias
conferencesource_aliasrT   conversation_idr1   related_uuidsr   uuidr3   )participant_connectedparticipant_updatedparticipant_media_stream_windowconference_updatedr6   r7   r5   r   r8   )r/   FzIgnoring late z for disconnected session 	0_unknown)r/   r0   r1   r2   r3   r5   r6   r7   r8   r:   r<   r@   participant_disconnecteddisconnect_reasonr   rt   )r/   r0   r1   r2   r3   r<   r=   r@   media_streamsstream_typeaudiovideorY   synchronize_sessionry   r<   r=   r@   #participant_media_streams_destroyedz%Could not mark participant ended for z: zError handling event: exc_infor=   okr"   )1r   get_jsonapploggerr   r   r   rk   r   r   r_   r   r?   r   r   r   rm   	filter_byrp   r<   r0   r1   r3   r5   r   r   r6   r7   r8   r:   r@   r*   rz   r   r=   (STRICT_FINALSTATS_FROM_STREAMS_DESTROYEDr   rn   updatein_rw   r   r   r/   r   warningcommitr   rollback)r   
event_typer   conf_name_rawr   r1   r   r2   r3   r/   r@   r6   r7   r5   r   r   existingr   r`   r   r   r   name_candidatesr   call_candidatesr   
back_aliasback_cidstreamr   s                                 rC   
event_sinkr   6  s   4(DJJOO&tf-.01366'9-J88FB%2D 	$% 	88L!	88N#	 88F	   !/Ihh()MTXXi-@MIGHH_-M mT*} 	aXXfi(  $((8Y78F#$K!IM  C  C88NA6L88NA6L88NA6L>>"hh'9:T>Nt>T248.t4248{{,,,EKKMHHOOu4

.<VWbVc de/8/TH<T<TH,7i#7+2(&,&?HO#0<-!,e=,QRBR03L0A-!,e=,QRBR03L0A-"4?1"7B4&*HO)2H&JJNN5$/(1 ''5%%1:D\TWY^S_:`S%6fj:D\TWY^S_:`S%6fj*5*D,7#"+$  55XX19=F{{,,,EKKMH"'"(%." 0 04I I'.H$(":":>S"S/8H,

u +$-##1! !'	  	 =*TXXVeMfhlBm$($9]qQUU==QU\=\]]$($9]qQUU==QU\=\]]+NIwPUW\^gh 7i/

  '..\\T)mmt+0CDYbHbce**i79 &%--1u&M-- &,-(	> 
  !1 3 O  ,0884E+FQZH[*\bQ`aqbOb""5<<4#78A?HHe3377HU]]M^M^_nMopq HHU2266GH HHU]]..?@HH ,>YW$)  
 @@Y&w0G'N~'^$
H:y8w r5E((?B7 )::m,7LL(ZZ.'9LL(	) (	7ESXZcd_

  '..u/@/@K/OPWW$0BQZ[(- X  	


 Hd#$c))C ^] cL  _

""%J;-WYZ[Y\#]^^_  

1!5E


s    La? $`0>`0	a? `5%`5)Da? `:&a? >`?`?
E<a? #a? +Aa a? 0a? 	a<%a72a? 7a<<a? ?	cAccz!/pexip-sink/admin/cleanup-activesc                     t        j                  d      xs i } t        | j                  dd            }t	        | j                  dd            }t	        | j                  dd            }t        j                         t        |      z
  }t        j                  j                  t              j                  t        j                  dk(  t        j                  |k        }|rJ|j                  t        j                  d	k(  t        j                  d
k(  z  t        j                  dk(  z        }	 |ri|j!                  ddt        j                         dd      }t        j                  j#                          t%        d||j'                         dz   d      dfS |j)                         }t+        |      }|D ]!  }	t        j                  j-                  |	       # t        j                  j#                          t%        d||j'                         dz   d      dfS # t.        $ rc}	t        j                  j1                          t2        j4                  j7                  d|	d       t%        dt9        |	      d      dfcY d	}	~	S d	}	~	ww xY w)a"  
    Body (all optional):
      {
        "older_than_minutes": 60,   # mark/delete actives older than this
        "only_unknown": false,      # limit to unknown alias rows
        "mark_ended": true          # true = set active=False/status=stale_cleanup; false = delete rows
      }
    Tr   older_than_minutes<   only_unknownF
mark_endedminutesNrY   rZ   stale_cleanupr   r   Z)r   marked_inactivecutoffr"   )r   deletedr  z!/admin/cleanup-actives failed: %sr   )r   r     )r   r   r   r   boolr   r?   r   r*   rz   rm   r   rn   r<   r@   r0   r   r   r   	isoformatr}   lendeleter   r   r   r   r   r   )
bodyr   r  r  r  r   updatedr   r	  r   s
             rC   admin_cleanup_activesr    s    4(.BDTXX&:B?@78Ldhh|T23J__3E!FFF


&&u||t';U__v=UVAHH""d*u/D/D/JKuOdOdhqOqr
<hh O(//J[\$)  G JJ$7fN^N^N`adNdefhkkk557D$iG %

!!!$%JJ$7fFVFVFXY\F\]^`ccc <




<a$Oec!f56;;<s'   /A*H B H 	J$AJ<JJc                 f    |D ]  }|| v s| |   	 t        | |         c S  y # t        $ r Y ,w xY wNr   r   )samplekeysr   s      rC   
_pluck_numr    sN     ;6!90VAY''   s   $	00c           
          t        | g d      t        | ddg      t        | ddg      t        | g d      t        | g d      t        | g d      d	S )
N)rx_packets_sentrx_sent
rx_packetsrx_packets_recvrx_receivedrx_packets_lostrx_lost)rx_jitter_ms	rx_jitter	jitter_rxjitter_ms_rx)rx_widthrx_video_widthrx_w)	rx_heightrx_video_heightrx_hsentrecvlostjitterwhr  rj   s    rC   _rx_stats_from_sampler2    X    1HI10?@10;<Q WX?@AB rB   c           
          t        | g d      t        | ddg      t        | ddg      t        | g d      t        | g d      t        | g d      d	S )
N)tx_packets_senttx_sent
tx_packetstx_packets_recvtx_receivedtx_packets_losttx_lost)tx_jitter_ms	tx_jitter	jitter_txjitter_ms_tx)tx_widthtx_video_widthtx_w)	tx_heighttx_video_heighttx_hr*  r1  rj   s    rC   _tx_stats_from_samplerF  )  r3  rB   c                 ^   ddddddddddd d d}| D ]B  }t        |t              s|D ](  }t        |t              st        |      }t	        |      }|d   |dxx   |d   z  cc<   |d   |dxx   |d   z  cc<   |d   |d	xx   |d   z  cc<   |d
   |dxx   |d
   z  cc<   |dxx   dz  cc<   |d   |dxx   |d   z  cc<   |d   |dxx   |d   z  cc<   |d   |dxx   |d   z  cc<   |d
   |dxx   |d
   z  cc<   |dxx   dz  cc<   |d   xs |d   }|d   xs |d   }|s	|st        |      |d<   t        |      |d<   + E |d   dkD  rt        |d	   |d   z  dz        nd }|d   dkD  rt        |d   |d   z  dz        nd }	|d   rt        |d   |d   z  d      nd }
|d   rt        |d   |d   z  d      nd }t        |d         t        |d         t        |d	         ||
t        |d         t        |d         t        |d         |	||d   |d   dS )N        r   )r  rx_recvr  rx_jitter_sumrx_jitter_nr6  tx_recvr;  tx_jitter_sumtx_jitter_nlast_wlast_hr+  r  r,  rI  r-  r  r.  rJ  rK  r   r6  rL  r;  rM  rN  r/  r0  rO  rP  r$      )r  rI  r  rx_loss_pctrx_jitter_avgr6  rL  r;  tx_loss_pcttx_jitter_avgfinal_wfinal_h)r   r   r   r2  rF  r   round)packet_arraystotalsr   r`   rxtxr/  r0  rR  rT  rS  rU  s               rC   _aggregate_totalsr]  3  s   SXYSXYF
  *#t$ 	*Aa&&q)B&q)B&z%vi'8BvJ'F'8&z%vi'8BvJ'F'8&z%vi'8BvJ'F'8(|')@BxL)P)@RXYfRgklRlRg&z%vi'8BvJ'F'8&z%vi'8BvJ'F'8&z%vi'8BvJ'F'8(|')@BxL)P)@RXYfRgklRlRg3"2c7A3(:2c7AQ#&q6x #&q6x !	**( KQQZJ[^_J_%	*VI->>#EFeiKJPQZJ[^_J_%	*VI->>#EFeiKQWXeQfE&1F=4II1MlpMQWXeQfE&1F=4II1MlpMvi()vi()vi()"&vi()vi()vi()"&(#(# rB   c                     | rd| vry	 | j                         j                  dd      \  }}t        t        |            t        t        |            fS # t        $ r Y yw xY w)Nr   NNr   )ri   rg   r   r   r   )r`   r/  r0  s      rC   _parse_res_xyr`  b  sZ    1wwysA&158}c%(m++ s   A
A 	A A c                 z   dx}x}x}}d xd x}}fd}| xs g D ]  }	 ||	       |t        |	j                  dd      xs d      z  }|t        |	j                  dd      xs d      z  }|t        |	j                  dd      xs d      z  }|t        |	j                  dd      xs d      z  }|	j                  d      xs |}|	j                  d	      xs |} |xs g D ]  }	 ||	       |t        |	j                  dd      xs d      z  }|t        |	j                  dd      xs d      z  }|t        |	j                  dd      xs d      z  }|t        |	j                  dd      xs d      z  }|	j                  d      xs |}|	j                  d	      xs |} |dkD  rt        ||z  d
z        nd }
|dkD  rt        ||z  d
z        nd }d\  }}||fD ]  }t        |      \  }}|s|s n t	        ||z         t	        |      t	        |      |
d dt	        |      d t	        |      |d d||ddS )NrH  c                     | j                  d      }| j                  d      }t        |t        t        f      r|nt	        |      t        |t        t        f      r|nt        |      y y )N
start_timeend_time)r   r   r   r   minr{   )r`   stetend_tsstart_tss      rC   upd_tsz&_agg_from_finalstreams.<locals>.upd_tsp  sd    UU< quuZ'8"b3,'(:JBPST\^`Pab3,'"CPVXZO'rB   rx_packets_receivedr   r  r5  r:  rx_resolutiontx_resolutionr$   r_  r+  r,  r-  loss_pctr   r/  r0  )	rx_totals	tx_totalsfinal_resolutionrc  rd  )r   r   rX  r`  r   )r   r   rI  r  r6  r;  last_rx_reslast_tx_resrj  r`   rR  rT  r/  r0  r   rh  ri  s                  @@rC   _agg_from_finalstreamsrv  k  s   ,//G/g/'Hv $$K+\ B <q	54a8=A>>50!49::50!49::50!49::eeO,;eeO,;< B <q	54a8=A>>50!49::50!49::50!49::eeO,;eeO,;< 7>k%7*c12tK6=k%7*c12tKDAq;' Q1 "'G"34c'lTWX_T`ny  JN  O!'lD#g,\gw{|"#!, rB   c                 \   t         j                  j                  t         j                  | k(        }t	        |      r#|j                  t         j
                  |k(        }n$|r"|j                  t         j                  |k(        }|j                  t        t         j                              }|j                  d      j                         }|sg g fS |d   j                  }d}g g }}|D ]  }	t        ||	j                  z
  j                               |k  s-	 |	j                  r.|j                  t!        j"                  |	j                               	 |	j&                  r.|j                  t!        j"                  |	j&                                ||fS # t$        $ r Y Lw xY w# t$        $ r Y w xY w)z
    Return combined audio/video lists by merging FinalStats rows for the participant
    within a small time window around the most recent row. This handles the case where
    audio and video arrived in separate 'streams_destroyed' events.
       r   r   )rE   rm   rn   r2   rw   r1   r0   ro   r   r@   limitr}   r   r   rG   r   r   r   r   rH   )
r2   r1   r   r   r   	newest_tsr   	audio_all	video_allr   s
             rC   '_fetch_merge_finalstats_for_participantr}    si    	
 9 9^ KLAgHHZ''723	HHZ//589	

4
,,-.A771:>>D2vQ!!IJryI 	BLL(779:jH>>$$TZZ%?@>>$$TZZ%?@ i  
  s$   :F:F	FF	F+*F+c                    g }i }| D ]a  }|j                   xs d}|j                  r|j                  dk7  r|j                  nd}|xs |}	 |j                  rt        j                  |j                        ng }t        |t              sg }|j                  xs d|j                  r|j                  dk7  r|j                  nd|j                  r|j                  dkD  r|j                  nd|j                  r|j                  dkD  r|j                  nd||j                  |j                  ndt        |j                  j                               d}|j                  |j                   ||j                  xs d|j"                  xs ddd|       ||vrJ|||j                  xs d|j"                  xs dg dt        |j                  j                               d||<   ||   }	t%        |	d	   t        |j                  j                                     |	d	<   |j"                  r|	d
   dk(  r|j"                  |	d
<   |d   |	d   D 
cg c]  }
|
d   	 c}
vsN|	d   j                  |       d g }|j'                         D ]0  }	|	d   }|st)        d |D              }|r |j                  |	       2 |j+                  d        ||fS # t        $ r g }Y Lw xY wc c}
w )zJGroup active events primarily by call_id (if present), otherwise by alias.rZ   Nr   r   )r2   r   r6   r7   r   r5   _tsr<   )r/   r   r1   r3   r=   )r]   r   r1   r3   participantsr=   r  r  r3   r2   r  c              3   `   K   | ]&  }|d    dk(  xr |d    xr |d    xr |d   dk(   ( yw)r2   rZ   r6   r7   r   r   NrA   ).0pps     rC   	<genexpr>z&_fold_active_events.<locals>.<genexpr>  s\      
   !Y. 0N##0,.~,>(>0;.0
s   ,.c                     | d   S )Nr  rA   r   s    rC   r   z%_fold_active_events.<locals>.<lambda>  s
    AeH rB   r   )r0   r1   r:   r   r   r   r   r   r2   r8   r6   r7   r5   r   r@   r   r/   r3   r{   valuesr}   sort)eventsflatgroupedr   r   call_key	group_keyplpgr   grouped_listpartsall_unknowns                 rC   _fold_active_eventsr    s   DG .(!!.Y !		aii9.D199$%		676K6KA112QSBb$'
  ..;)121C1CHZHZ^iHi1--oz./nnRSASANNY]./nnRSASANNY]./nn.HANNdq{{,,./
 	==yy-Ihh+)
 
 	 G#"#991	((/i ""1;;0023"GI IqxQ[[%:%:%<!=>%88(y0((AhKAnDU&Vqq)9':&VVn$$Q'].(` L^^ .! 
 	
 
 A ,-o  	B	H 'Ws   ?KKKKz/pexip-sink/chart-datac                  n   t        j                         } t        j                  j	                  t        j
                  dk(        j                  t        j                  j                               j                         }t        |      \  }}| t        d      z
  }t        j                  j                  t        j                        j	                  t        t        j                        t        j                  |k\        j!                         j                         D cg c]  }|d   	 }}t        j                  j                  t        j                        j	                  t        t        j                        t        j
                  dk(        j!                         j                         D ch c]  }|d   	 }}|D cg c]	  }||vs| }	}i }
|	D ]  }t        j                  j                  t#        j$                  t        j                              j	                  t        j                  |k(        j'                         }|st|d   szt)        |d   j                               |
|<    t+        |
       d|
rt%        |
j-                               nd }t)        t/        j0                  dd            }t3        |
j5                         d d	      d | D cg c]  \  }}|	 }}}g }|D ]  }t7        |      }t        j                  j	                  t        j                  |k(        j                  t        j                  j9                               j                         }|
j;                  |      }t=        t>              }|D ]&  }|j@                  xs d
}||   jC                  |       ( |j5                         D ]  \  }}tE        |||      \  }}d|g fdg |fg}d}|D ]  \  }}}|s|stG        ||      } tI        | d         }!tI        | d         }"| d   d   }#| d   d   }$| d   }%d }&d }'|D ]>  }|'xs |jJ                  }'|jL                  s |jL                  dk7  s0|&r3|jL                  }&@ |jC                  |r|d   jN                  nd|||'xs d
||&xs d|!|"|#|$d|%|%nd |d||xsA |r&t)        |d   j                  j                               nt)        | j                               d       d} |rCtI        d d d d d d      }!tI        d d d d d d      }"|jC                  |r|d   jN                  nd|||r|d   jJ                  r|d   jJ                  nd
|d|!|"d d dd |dd
|xsA |r&t)        |d   j                  j                               nt)        | j                               d         tP        j                  j	                  tP        j                  | t        d      z
  k\        j                  t9        tP        j                              jS                  |dz        j                         }(|D )ch c]  })|)d   |)d   f }*})|(D ]  }+|+j@                  xs d
}|+j                  xs d
}||f|*v r+	 |+jT                  rtW        jX                  |+jT                        ng }	 |+j\                  rtW        jX                  |+j\                        ng }|+j^                  xs d
},|,d
k(  rt        j                  j	                  t        j@                  |k(        j                  t        j                  j9                               j'                         }-|-r|-j^                  d vr|-j^                  },d }.t        j                  j	                  t        j@                  |k(        j                  t        j                  j9                               j'                         }/|/r|/jJ                  r|/jJ                  }.d}0d|g fdg |ffD ]  \  }}}|s|stG        ||      } tI        | d         }!tI        | d         }"|jC                  ||,||.xs d
|d|!|"| d   d   | d   d   d| d   | d   nd t)        |+j                  j                               d|t)        |+j                  j                               d       d}0 |0sm|*ja                  ||f        t        j                  j                  t        j                        j	                  t        j                  | t        d!"      z
  k\  t        t        j                              j!                         jc                         }1t        j                  j                  t        j                        j	                  t        j                  | t        d#      z
  k\  t        t        j                              j!                         jc                         }2te        tg        |
ji                                     }3|D ]>  })tI        |)j;                  di             |)d<   tI        |)j;                  di             |)d<   @ tk        |||g |1|2|3|d$      S c c}w c c}w c c}w c c}}w c c})w # tZ        $ r g }Y w xY w# tZ        $ r g }Y w xY w)%NT   hoursr   -PEXIP_ENDED_LIMIT20c                     | d   S )Nr   rA   )kvs    rC   r   zchart_data.<locals>.<lambda>  s    bQRe rB   )r   reverserZ   )r1   r   r   r   Frq  rr  rs  r/  r0  rc  r   rY   rp  ry   )r/   r   r1   r3   r2   r   rq  rr  rs  r5   ended_atr=   r   r  rn  r   r     r2   r1   rt      daysr   )r  participants_groupedended_participantswrapupstotal_callsrecent_consultsdisconnect_reasonsended_signature)6r   r?   r   rm   rn   r<   ro   r@   ascr}   r  r   r*   rz   r1   rw   distinctr   r{   rp   r   r  r  osgetenvr   r   rr   r   r   r   r   r2   r   r}  rv  r   r3   r8   r/   rE   ry  rG   r   r   r   rH   r0   r   r   r   setr  r   )4nowactive_eventsflat_activegrouped_activesincer   recent_callsactive_call_idsr   ended_candidatescall_end_tsrq   r  ENDED_LIMIT_	top_callsr  r1   display_aliasr   rh  by_pidr   pidplistr   r   
per_streamany_emittedstypea_listv_listrx_txrx_tottx_totrV  rW  rc  quality_finalr3   fs_rowsep	seen_keysr   r   evvendev2emittedr  r  r  s4                                                       rC   
chart_datar    s
   
//
C KK&&u||t';<EEeooFYFYF[\``bM"5m"DK )"%%E"$**"2"25=="A"H"HX]XeXeIfhmhwhw  |A  iA  #B  #K  #K  #M  #Q  #Q  #S  TQAaD  TL  T%'ZZ%5%5emm%D%K%KN[`[h[hLikpkwkw{k  &A  &J  &J  &L  &P  &P  &R  Sqt  SO  S'3Rs/7QRRK 7jjtxx89@@RUAUV\\^3q6"3q6#3#3#56K7
 [)*!C0B0B0D,EZ[+\]Obii 3T:;K#)+*;*;*=CS]a#bcodo#pqaqIq  K.w7{{!!%--7":;DDU__EYEYE[\``b)T" 	"A""/iC3Kq!	" !,,. A	JCB3PW_lmLE5 %$"e$J
  K)3 "#%vvf.vv>-eK.@A-eK.@A 23C8 23C8"<0
 $ ;A#/qxxF))a.@.@K.OXe()(:(:;
 #));@58#7#7b"/&$1	&)#0#?K!'!'/6W(F2<2HJd &0#(!mUc%(*<*<*F*F*H&IX[\_\i\i\kXl+   #E"#H -tTSWeiy}.~-tTSWeiy}.~"));@58#7#7b"/&27E!HOOuQxQZ&)#.!'!'/3$(@$( &0#,!mUc%(*<*<*F*F*H&IX[\_\i\i\kXl+ eA	K^ 		
$$i.C(CC	D	$z++,	-	{Q		  BTT2"%&96TIT =&,9jj%I:"	24..DJJr~~.bE	24..DJJr~~.bE ""/iI,,34%//..01	  b((0EE**KKVE((C/0Xeoo**,-UW	 	 3::::D'.r&:Wb%<P%Q 	!E66&*66:E)%*<=F)%*<=F%%"#+)"%*##*/0B*CC*HuUgOhilOm$n7<\7J7Vl 3\` 6 6 89,$2<<1134'   G-	0 MM3*%{=&@ 	

'	OOsYA%6665==)

 
	  	

'	OOsYQ%7775==)

 
	  6c+:J:J:L6MN ! H.rvvk2/FG;.rvvk2/FG;H # .0"*0*	 	 	 T SR rr U  	E	  	E	sH   i9/i>	jjjj-j;-j%j"!j"%j43j4c                 l    t        t        |       |      }d|j                  d<   d|j                  d<   |S )Nz.no-store, no-cache, must-revalidate, max-age=0zCache-Controlzno-cachePragma)r	   r   headers)payloadr=   resps      rC   _no_cache_jsonr    s4    )62D$TDLL!'DLLKrB   c                 N    | j                   dz  dz  }| j                  |dd      S )N   r   )minutesecondmicrosecond)r  replace)dtms     rC   
_floor_15mr    s)    	bBA::Qqa:88rB   c                 j    | j                   dk(  rt        | j                        S d| j                   dS )Nr   .02d)r  r   hourr  s    rC   _decimal_quarter_labelr    s.    	yyA~277|ryyorB   PEXIP_VMLOAD_RETENTION_HOURS4PEXIP_VMLOAD_BUCKET_MINUTES15z/pexip-sink/worker-vm/statusc                     t        j                  dd      } t        j                  dd      }t        j                  dd      }	 t        j                  | ||fdd	      }|j	                          |j                         }|}t        |t              r|j                  d      nd t        |t              r|j                  d      nd t        |t              r|j                  d      nd t        |t              r|j                  d      nd g}t        d |D        d       }|t        |t              r|}|xs g }d }	d }
t        j                         }|t        t              z
  }	 t         j"                  j%                  t&              j)                  t&        j*                  |k        j-                          |D ]  }t         j"                  j/                  t'        t1        |j                  dd            |j                  dd      xs t1        |j                  dd            | |	|j                  d                          t         j"                  j3                          t7        |      }t7        |      }t        t8              }g }g }|}||k  rE|j;                  |j=                  d             |j;                  t?        |             ||z  }||k  rEt         j"                  j%                  t&        j@                  t&        jB                  t&        j*                  t&        jD                        j)                  t&        j*                  |k\        jG                  t&        j@                  t&        j*                        jI                         }d }i }i }|D ]  \  }}}}|jK                  ||g d      d   j;                  |jM                         d z   tO        tQ        |      d!      d"        ||      }|jK                  ||i d#      d$   jK                  |g       j;                  tQ        |              g }|D ]>  }t1        |j                  dd            }|j                  dd      xs |}|j                  |dg i      d   }|r|d%   d&   n |	|j                  d            }|j                  |d$i i      d$   } g }!|D ]L  }| j                  |      }"|"rtS        |"      tU        |"      z  nd'}#|!j;                  |tO        |#d!      d(       N |j;                  |||j                  d)d      |j                  d*d      |j                  d+d       |
|d,d-d.       |
|d/d0d1       |
|d2d3d4      |||!d5       A tS        d6 |D              tS        d7 |D              tS        d8 |D              d9}$tW        |jM                         d z   |jM                         d z   ||||$d:      S # t        $ r}t        d
d| i      dfcY d }~S d }~ww xY w# t        $ r:}t         j"                  j5                          t        d
d| i      dfcY d }~S d }~ww xY w);NPEXIP_MGMT_URLzIhttps://cklab-pexmgr.ck-collab-engtest.com/api/admin/status/v1/worker_vm/PEXIP_MGMT_USERadminPEXIP_MGMT_PASSrY   
   Fauthtimeoutverifyr   zMgmt fetch failed:   resultsobjectsworkersr   c              3   B   K   | ]  }t        |t              s|  y wr  )r   r   )r  r   s     rC   r  z#worker_vm_status.<locals>.<genexpr>  s     ?:a+>!?s   c                 x    	 t        | xs d      }|dk\  r|dz  }|dk  rd}|dkD  rd}|S # t        $ r d}Y 'w xY w)NrH  g      ?g      Y@r  )r   r   s     rC   normzworker_vm_status.<locals>.norm
  sW    	ah3A 8E	As7As7A  	A	s   + 99c                 x    |D ]&  }|| v s| |   	 t        t        | |               c S  y# t        $ r Y 5w xY w)Nr   )r   r   r   )dr  r   s      rC   getintz worker_vm_status.<locals>.getint  sR     	AAv!A$*uQqT{++	  ! s   -	99r  r-   rT   rR   )rP   rQ   r@   rR   zDB write failed: r
  r  %Y-%m-%d %H:%M:00c                 6    t        |       j                  d      S )Nr  )r  strftimer  s    rC   bucket_labelz&worker_vm_status.<locals>.bucket_labelH  s    B!8!89L!MMrB   )rT   pointsr  r  r  )r   r   )rT   bucketsr  r   rH  )r  	avg_mediasystem_locationversion	node_typemax_full_hd_callsmax_full_hdmax_full_hd_capacitymax_sd_callsmax_sdmax_sd_capacitymax_audio_calls	max_audiomax_audio_capacity)r-   rT   r  r  r  r  r  r  media_load_nowr  media_load_seriesc              3   &   K   | ]	  }|d      yw)r  NrA   r  ns     rC   r  z#worker_vm_status.<locals>.<genexpr>p  s     F!q,-F   c              3   &   K   | ]	  }|d      yw)r  NrA   r  s     rC   r  z#worker_vm_status.<locals>.<genexpr>q  s     F!q(Fr  c              3   &   K   | ]	  }|d      yw)r  NrA   r  s     rC   r  z#worker_vm_status.<locals>.<genexpr>r  s     F!q*+Fr  )full_hdsdr   )generated_at	polled_athour_labelshour_labels_displaynodesrZ  ),r  r  requestsr   raise_for_statusr   r   r   r   r   nextr   r   r?   r   RETENTION_HOURSr*   rz   rm   rJ   rn   r@   r  r   r   r   r   r  BUCKET_MINUTESr   r  r  rP   rQ   rR   ro   r}   
setdefaultr  rX  r   sumr  r  )%mgmt_url	mgmt_user	mgmt_passr   r  r   raw
candidatesr  r  r  now_utcr  r  
start_tickend_tickstephour_labels_isor  r   r   r   by_node_pointsby_node_bucketsrP   rQ   r   medialblresponse_nodespts	media_nowbmapseriesvalsavgrZ  s%                                        rC   worker_vm_statusr<    s   		*,wxH		+W5I		+R0IBLLI(>SXY	&&( C(d3	(d3	(d3	(d3	J ?Z?FE}C.KRE	 ooG	88F@


&--l.D.Dv.MNUUW 	AJJNN<AEE$rN+55+Bs155b>/B!l 34	 	 	

 F#JG$H>2DOA
x-qzz*=>?""#9!#<=	T	 x- 	

  ,"8"8""L$;$;	
 
&&&0	1	,&&(>(>	?	 	 NNO)- 6%B!!'I+LMhWV",,.,5ua3HIJ2""7Y2,NOPYZZR e!56 N d2'EE&$/	  8R.9(C$'CGCLT!%%2E-F	""7YO<YG" 	DC88C=D+/3t9SY&SCMM3U3q\BC	D
 	 uu%6r:uuYr*{2.!'+>}Mc!d!'>(CT!U!'+<[I]!^'!'
 	8 F~FFF~FFF~FFF ))+C/&&(,&2  u  B#6qc!:;<cAABb  @


#4QC!89:C??@s=   ;W& &C=X &	X/X XX	Y/YYYz/pexip-sink/licensing/statusc                     t        j                  dd      } t        j                  dd      }t        j                  dd      }	 t        j                  | ||fdd	      }|j	                          |j                         }d }t        |t              r-t        |j                  d      t              r|d   r	|d   d   }nt        |t              r|r|d   }|xs i }g }|j                         D ]  \  }}	|j                  d      s|d d }
|
 d}t        |j                  |d      xs d      }t        |j                  |d      xs d      }|j                  |
|
j                  dd      j!                         ||d        t        t#        j$                         j'                         dz   |d      S # t        $ r}t        d
d| i      dfcY d }~S d }~ww xY w)NPEXIP_MGMT_LICENSE_URLzIhttps://cklab-pexmgr.ck-collab-engtest.com/api/admin/status/v1/licensing/r  r  r  rY   r  Fr  r   zLicensing fetch failed: r  r  r   _counti_totalr  re   )r   labelr   totalr  )r  r   )r  r  r   r   r!  r   r   r   r   r   r   r   endswithr   r   r  upperr   r?   r  )lic_urlr(  r)  r   r   r   objr   r   r   base	total_keyr   rB  s                 rC   licensing_statusrI    s   yy SG 		+W5I		+R0IGLL	9'=rRWX	vvx C$*TXXi-@$"GDQZO9oa 	D$	D1g
)CE		 1zz(#"vfFO	CGGAqM&Q'CGGIq).Q/\\#s+113	
 	  )335;  3  G#;A3!?@A3FFGs   ;F4 4	G=GGGz/pexip-sink/healthc                  h    t        dt        j                         j                         dz   d      dfS )NTr  )r   timer"   )r   r   r?   r  rA   rB   rC   healthrL    s,    $(9(C(C(E(KLMsRRrB   z/pexip-sink/c                     t        j                         } | t        d      z
  }| t        d      z
  }t        j                  j                  t        j                        j                  t        j                  |k\  t        t        j                              j                         j                         }t        j                  j                  t        j                        j                  t        j                  |k\  t        t        j                              j                         j                         }t        d       }t        |      }t        d|||D cg c]  }|d   |d   f c}      S c c}w )	Nr  r  r   r  zdashboard.htmlr   r   )r  consultations_last_hourr  )r   r?   r   r*   rz   rm   r   r1   rn   r@   rw   r  r   r   r   r   )r  week_agohour_agor  rN  ended_call_idsdisconnectsr  s           rC   	dashboardrS    s   
//
CYA&&HYQ''H 	

'	OOx'5==)

 
	  	

'	OOx'5==)

 
	  +40N.~>K 7?JK!Qx[!G*5K	  Ls   E0__main__z	127.0.0.1i  )hostportdebugr  r_  )r"   )~r   r   flaskr   r   r   r   r	   flask_sqlalchemyr
   
sqlalchemyr   r   r   r   r   werkzeug.middleware.proxy_fixr   r  r   r[   collectionsr   r   logginglogging.handlersr   r   urllib3disable_warnings
exceptionsInsecureRequestWarningr   r   r'   r   wsgi_appDEFAULT_DATA_DIRr  DATA_DIRmakedirsPermissionErrorpath
expanduserhome_fallbackjoinDB_PATHconfigLOG_PATHdirnameexistsopenclose	getLoggerrootsetLevelDEBUGr   handlersr0  removeHandlerfile_hsetFormatter	FormatterStreamHandler	console_hINFO
addHandlerr   	propagater*   Modelr   rE   rJ   app_contextengine	inspectorget_table_namesget_columnscolsrv   connectconnexecuter   r   fcols
create_allr   r_   rk   rr   rw   r   r   r   r   r   r   r   r   r   router   r  r  r2  rF  r]  r`  rv  r}  r  r  r  r  r  r   r#  r$  r<  rI  rL  rS  runru   s   0rC   <module>r     s   ( I I ' 6 6 2 	  	 ,  / 	GW//FFG ,0 ( H&:;a: / 299%'78BKK4( '',,x!2
3*4WI(>

$ %/4

+ , 299%'FG$BKK)D977>>(#Xs!!# w gmm  
dmm	 Aq 
H	%     %G%%&WX Y!G!!#	 	  7<<   	  (w(()DE F   	  

 

  

  GMM " _@BHH @ L L5288 5 	__ 		"I)++--'0'<'<W'EFFF? 	MC$M**, WT,I#e*T%UVW	M 	1133(1(=(=m(LMVME!VYY&&( ZDLL&W!XYZ
 MMO-4@S @S @  OC OC O< g.4 t $
C 
D -^ #fX6o* 7o*f .A'< B'<V+^,\  HER #$Q %Qj9
 yryy!?EFyryy!>FG)*J +J\ )*( +(X  S !S >   H zGG4uG5 I&  		   GG&&'<=MBKK-H	"  $ww||H&<=H77>>(#Xs!!#	$R GW W  MJJ&&)?uCs'KLLM NZ Z V

""%PQRPS#TUUV' s   *V :V# AW  #8[X:'[5YX?+Y3'[Z &[.ZZ Z([V V #6WW AX76X7:[?Y	YY=!Y82[8Y==[Z	
Z[ Z;5[;[  [[