
    `j"                        S SK Jr  S SKrS SKJrJr  SS jrSS jrSS jrSS jrSS jr	SS	 jr
SS
 jrSS jrSS jrSS jr            SS jr              SS jrSS jrSS jrS S jrS!S"S jjrg)#    )annotationsN)datetimetimezonec                  #    U R                  S5      I S h  vN   U R                  S5      I S h  vN   U R                  S5      I S h  vN   U R                  S5      I S h  vN   U R                  S5      I S h  vN   U R                  S5      I S h  vN   U R                  5       I S h  vN   g  N N Nl NU N> N' N7f)Na	  
        CREATE TABLE IF NOT EXISTS users (
            user_id       INTEGER NOT NULL,
            conference_id TEXT NOT NULL,
            started_at    TEXT NOT NULL,
            subscribed_at TEXT,
            PRIMARY KEY (user_id, conference_id)
        )
    z^
        CREATE INDEX IF NOT EXISTS idx_users_conference
        ON users (conference_id)
    z
        CREATE TABLE IF NOT EXISTS blocked_users (
            user_id    INTEGER PRIMARY KEY,
            blocked_at TEXT NOT NULL
        )
    aP  
        CREATE TABLE IF NOT EXISTS material_events (
            id            INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id       INTEGER NOT NULL,
            conference_id TEXT NOT NULL,
            material_id   TEXT NOT NULL,
            action        TEXT NOT NULL,
            created_at    TEXT NOT NULL
        )
    zl
        CREATE INDEX IF NOT EXISTS idx_material_events_conf
        ON material_events (conference_id)
    a  
        CREATE TABLE IF NOT EXISTS broadcasts (
            id            INTEGER PRIMARY KEY AUTOINCREMENT,
            conference_id TEXT,
            kind          TEXT NOT NULL,
            audience      INTEGER NOT NULL,
            delivered     INTEGER NOT NULL,
            skipped       INTEGER NOT NULL,
            errors        INTEGER NOT NULL,
            created_at    TEXT NOT NULL
        )
    )executecommit)dbs    /home/rasp/conference-bot/db.pyinit_dbr      s     
**  	 	 	 **  	 	 	 **  	 	 	 ** 	 		 		 		 **  	 	 	 **  	 	 	 ))+[							 sy   C B2C B4C 	B6
C "B8#C ;B:<C B<C ,B>-C 4C 6C 8C :C <C >C c                 f    [         R                  " [        R                  5      R	                  5       $ )N)r   nowr   utc	isoformat     r
   _nowr   8   s    <<%//11r   c                   #    U R                  SX[        5       45      I S h  vN   U R                  SU45      I S h  vN   U R                  5       I S h  vN   g  N8 N N	7f)NzQINSERT OR IGNORE INTO users (user_id, conference_id, started_at) VALUES (?, ?, ?)z)DELETE FROM blocked_users WHERE user_id=?r   r   r   )r	   user_idconference_ids      r
   record_startr   <   s^     
**[	(  
 **@7*
MMM
))+
 Ns1    A!AA!AA!AA!A!A!c                (  #    U R                  S[        5       X45       IS h  vN nUR                  S:X  a  [        SU SU 35      eS S S 5      IS h  vN   U R	                  5       I S h  vN   g  NN N! , IS h  vN  (       d  f       N4= f N#7f)NzDUPDATE users SET subscribed_at=? WHERE user_id=? AND conference_id=?r   z%record_subscription: no row for user=z conf=)r   r   rowcount
ValueErrorr   )r	   r   r   curs       r
   record_subscriptionr   F   s     zzN	(  
<<1DWIVTaSbcdd  ))+    sP   !BA2B"A6	BA4B,B-B4B6B<A?=B	Bc                  #    U R                  SU45       IS h  vN nUR                  5       I S h  vN nS S S 5      IS h  vN   W Vs/ s H  oDS   PM	     sn$  NA N+ N! , IS h  vN  (       d  f       N2= fs  snf 7f)Nz
        SELECT u.user_id FROM users u
        WHERE u.conference_id = ?
          AND u.subscribed_at IS NOT NULL
          AND u.user_id NOT IN (SELECT user_id FROM blocked_users)
    r   r   fetchallr	   r   r   rowsrows        r
   get_verified_usersr#   P   |     zz 
 	 
 "\\^#  ##dsFd## $    $_   BABA"AA"BA BA<BA" B"A9(A+)A95Bc                  #    U R                  SU45       IS h  vN nUR                  5       I S h  vN nS S S 5      IS h  vN   W Vs/ s H  oDS   PM	     sn$  NA N+ N! , IS h  vN  (       d  f       N2= fs  snf 7f)Nz
        SELECT user_id FROM users
        WHERE conference_id = ?
          AND subscribed_at IS NULL
          AND user_id NOT IN (SELECT user_id FROM blocked_users)
    r   r   r    s        r
   get_unsubscribed_usersr'   [   r$   r%   c                |  #    Sn/ nU(       a2  SR                  S U 5       5      nUSU S3-  nUR                  U5        U R                  X#5       ISh  vN nUR                  5       I Sh  vN nSSS5      ISh  vN   W Vs/ s H  owS   PM	     sn$  NA N+ N! , ISh  vN  (       d  f       N2= fs  snf 7f)zAll distinct user_ids minus blocked and `exclude_user_ids`. The IN-clause for
excludes is skipped when the set is empty (SQLite rejects an empty IN-list).zq
        SELECT DISTINCT user_id FROM users
        WHERE user_id NOT IN (SELECT user_id FROM blocked_users)
    z, c              3  &   #    U  H  nS v   M	     g7f)?Nr   ).0_s     r
   	<genexpr> get_all_users.<locals>.<genexpr>o   s      ?.>.>s   z AND user_id NOT IN ()Nr   )joinextendr   r   )r	   exclude_user_idssqlparamsplaceholdersr   r!   r"   s           r
   get_all_usersr6   f   s     C Fyy ?.> ??&|nA66&'zz#&&#\\^# '&"#dsFd## '# '&&&#sf   AB<BB<B-B.B2B<=B>B<B7B<BB<B4#B&$B40B<c                   #    U R                  SU[        5       45      I S h  vN   U R                  5       I S h  vN   g  N N7f)NzHINSERT OR REPLACE INTO blocked_users (user_id, blocked_at) VALUES (?, ?)r   )r	   r   s     r
   mark_blockedr8   w   sC     
**R	$&   ))+	 s    AA AAAAc                   #    U R                  S5       ISh  vN nUR                  5       I Sh  vN S   sSSS5      ISh  vN   $  N0 N N	! , ISh  vN  (       d  f       g= f7f)z<Count of distinct user_ids across all events, minus blocked.z|
        SELECT COUNT(DISTINCT user_id) FROM users
        WHERE user_id NOT IN (SELECT user_id FROM blocked_users)
        Nr   r   fetchone)r	   r   s     r
   get_total_usersr<      sY     zz	 
 
lln$a(   %   sS   A)A	A)AAAA)AA)AA)A&AA&"A)c                |  #    U R                  SU45       IS h  vN nUR                  5       I S h  vN S   nS S S 5      IS h  vN   U R                  SU45       IS h  vN nUR                  5       I S h  vN S   nS S S 5      IS h  vN   U R                  S5       IS h  vN nUR                  5       I S h  vN S   nS S S 5      IS h  vN   WWWS.$  N N N! , IS h  vN  (       d  f       N= f N N Ns! , IS h  vN  (       d  f       N= f Nu N_ NN! , IS h  vN  (       d  f       Nc= f7f)Nz0SELECT COUNT(*) FROM users WHERE conference_id=?r   zNSELECT COUNT(*) FROM users WHERE conference_id=? AND subscribed_at IS NOT NULLz"SELECT COUNT(*) FROM blocked_users)started
subscribedblockedr:   )r	   r   r   r>   r?   r@   s         r
   	get_statsrA      s    zz:]<L 	'+  zzX	  
LLN*A.
	 
 zz>??3'+ @?jWMM (    +	   
 @' @???s   D<CD<C"CC"D<C D<!C<"D<%D9C>:DD<D D<&D'D<*D">D?D"D<D D<C" D<"C9(C+)C95D<>D D<DD	DD<D" D<"D9(D+)D95D<c           	        #    U R                  SXX4[        5       45      I Sh  vN   U R                  5       I Sh  vN   g N N7f)uM   Append a material interaction. action ∈ {'click','gate_shown','delivered'}.zlINSERT INTO material_events (user_id, conference_id, material_id, action, created_at) VALUES (?, ?, ?, ?, ?)Nr   )r	   r   r   material_idactions        r
   record_material_eventrE      sI      **	!	df=  
 ))+
 s   !AAAAAAc                   #    X4-   U-   nU R                  SXXcXE[        5       45      I Sh  vN   U R                  5       I Sh  vN   g N N7f)zULog a finished broadcast. audience is the total attempted (delivered+skipped+errors).z{INSERT INTO broadcasts (conference_id, kind, audience, delivered, skipped, errors, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)Nr   )r	   r   kind	deliveredskippederrorsaudiences          r
   record_broadcastrL      sX      "V+H
**	'	h7DFK  
 ))+
 s!   )AA	AAAAc                  #    U R                  SU45       ISh  vN nUR                  5       I Sh  vN nSSS5      ISh  vN   W Vs/ s H  oDS   US   US   S.PM     sn$  NK N5 N'! , ISh  vN  (       d  f       N<= fs  snf 7f)zWPer material_id (with any 'click'): total clicks + unique clickers, most-clicked first.zSELECT material_id, COUNT(*), COUNT(DISTINCT user_id) FROM material_events WHERE conference_id=? AND action='click' GROUP BY material_id ORDER BY COUNT(*) DESC, material_idNr         )rC   clicksuniquesr   )r	   r   r   r!   rs        r
   get_material_click_statsrS      s     zz	C 
	 
 
\\^#  MQQDqaDAaDQqTBDQQ $    Rs_   BA&BA,A(A,BA*BB#B(A,*B,B2A53B?Bc                  #    U R                  SU45       ISh  vN nUR                  5       I Sh  vN S   nSSS5      ISh  vN   U R                  SX45       ISh  vN nUR                  5       I Sh  vN S   nSSS5      ISh  vN   WWS.$  N{ Ne NT! , ISh  vN  (       d  f       Ni= f NT N> N-! , ISh  vN  (       d  f       NB= f7f)u   delivered — distinct users who got a material; stuck — distinct users who saw the gate
but never got through (gate_shown and NOT in the delivered set).z`SELECT COUNT(DISTINCT user_id) FROM material_events WHERE conference_id=? AND action='delivered'Nr   zSELECT COUNT(DISTINCT user_id) FROM material_events WHERE conference_id=? AND action='gate_shown' AND user_id NOT IN (SELECT user_id FROM material_events WHERE conference_id=? AND action='delivered'))rH   stuckr:   )r	   r   r   rH   rU   s        r
   get_gate_statsrV      s      zz	7	  
<<>)1-	  zz	8 
&  
||~%q)  #U33
 *    &   s   CBCBBBCBC!B6"C%B<9B8:B<CB:
CBCB3"B%#B3/C8B<:C<CCCCc                   #    U R                  SX45       ISh  vN nUR                  5       I Sh  vN S   sSSS5      ISh  vN   $  N0 N N	! , ISh  vN  (       d  f       g= f7f)zMCount of starts for this event with started_at >= since_iso (rolling window).zDSELECT COUNT(*) FROM users WHERE conference_id=? AND started_at >= ?Nr   r:   )r	   r   	since_isor   s       r
   get_started_sincerY      s]     zzN	"  
lln$a(	   %	   sS   A+AA+AAAA+AA+AA+A(AA($A+c           	     ,  #    U R                  SU45       ISh  vN nUR                  5       I Sh  vN nSSS5      ISh  vN   W Vs/ s H  nUS   US   US   US   US   S.PM     sn$  NT N> N0! , ISh  vN  (       d  f       NE= fs  snf 7f)	zBMost recent broadcasts first (id breaks ties on equal timestamps).zmSELECT kind, delivered, skipped, errors, created_at FROM broadcasts ORDER BY created_at DESC, id DESC LIMIT ?Nr   rN   rO         )rG   rH   rI   rJ   
created_atr   )r	   limitr   r!   rR   s        r
   get_recent_broadcastsr_      s     zz	D	  
\\^#  A 1AaDQqTQqTYZ[\Y]^ 
 $   s_   BA/BA5A1A5BA3B!B,B1A53B5B;A><BB)r	   aiosqlite.ConnectionreturnNone)ra   str)r	   r`   r   intr   rc   ra   rb   )r	   r`   r   rc   ra   	list[int])r	   r`   r2   zset[int]ra   re   )r	   r`   r   rd   ra   rb   )r	   r`   ra   rd   )r	   r`   r   rc   ra   zdict[str, int])r	   r`   r   rd   r   rc   rC   rc   rD   rc   ra   rb   )r	   r`   r   z'str | None'rG   rc   rH   rd   rI   rd   rJ   rd   ra   rb   )r	   r`   r   rc   ra   'list[dict]')r	   r`   r   rc   ra   z'dict[str, int]')r	   r`   r   rc   rX   rc   ra   rd   )   )r	   r`   r^   rd   ra   rf   )
__future__r   	aiosqliter   r   r   r   r   r   r#   r'   r6   r8   r<   rA   rE   rL   rS   rV   rY   r_   r   r   r
   <module>rj      s    "  '.b2$$$")N  	
  
   	
   
$	R4()r   