
    EQdj'                    @   S r SSKJr  SSKrSSKJr  SSKJr  SSKJ	r	  SSS	.r
S
SS	.rSr " S S\5      rSS jr\SS j5       rSS jrS S jr S!       S"S jjrS#S jr      S$S jrS%S&S j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g)-u_   Хранилище meta-puller: свои посты Instagram и Threads с метриками.    )annotationsN)contextmanager)date)Pathig_poststh_posts)	instagramthreads)post_idcaption
media_typemedia_product_type	permalink	timestamplikescomments)r   textr   is_quote_postr   r   a  
CREATE TABLE IF NOT EXISTS ig_posts (
    post_id TEXT PRIMARY KEY,
    caption TEXT,
    media_type TEXT,
    media_product_type TEXT,
    permalink TEXT,
    timestamp TEXT,
    likes INTEGER,
    comments INTEGER,
    views INTEGER,
    reach INTEGER,
    saved INTEGER,
    shares INTEGER,
    total_interactions INTEGER,
    follows INTEGER,
    profile_visits INTEGER,
    stats_updated_at TEXT,
    is_deleted INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS th_posts (
    post_id TEXT PRIMARY KEY,
    text TEXT,
    media_type TEXT,
    is_quote_post INTEGER,
    permalink TEXT,
    timestamp TEXT,
    views INTEGER,
    likes INTEGER,
    replies INTEGER,
    reposts INTEGER,
    quotes INTEGER,
    shares INTEGER,
    stats_updated_at TEXT,
    is_deleted INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS account_snapshots (
    platform TEXT NOT NULL,
    date TEXT NOT NULL,
    followers INTEGER,
    media_count INTEGER,
    username TEXT,
    PRIMARY KEY (platform, date)
);

CREATE TABLE IF NOT EXISTS runs (
    started_at TEXT NOT NULL,
    finished_at TEXT,
    status TEXT,
    error_summary TEXT
);

CREATE TABLE IF NOT EXISTS tokens (
    platform TEXT PRIMARY KEY,
    token TEXT NOT NULL,
    issued_at TEXT NOT NULL,
    expires_at TEXT
);

CREATE INDEX IF NOT EXISTS idx_ig_timestamp ON ig_posts(timestamp);
CREATE INDEX IF NOT EXISTS idx_th_timestamp ON th_posts(timestamp);
c                      \ rS rSrSrSrg)UnknownPlatformErrorX   u2   Платформа не instagram и не threads. N)__name__
__module____qualname____firstlineno____doc____static_attributes__r       /home/rasp/meta-puller/db.pyr   r   X   s    <r   r   c                     [         U    $ ! [         a9    [        SR                  U SR	                  [        [         5      5      5      5      S ef = f)NuE   неизвестная платформа {!r}, ожидались {}u    или )TABLESKeyErrorr   formatjoinsorted)platforms    r    _tabler(   \   sS    h "SZZ*//&.9;
 	s
    AAc              #     #    [         R                  " U 5      n[         R                  Ul         Uv   UR	                  5         UR                  5         g ! UR                  5         f = f7fN)sqlite3connectRowrow_factorycommitclosedb_pathconns     r    r,   r,   f   sD     ??7#D{{D




s   ,A)A A)A&&A)c                    [        U 5      R                  R                  SSS9  [        U 5       nUR	                  [
        5        S S S 5        g ! , (       d  f       g = f)NT)parentsexist_ok)r   parentmkdirr,   executescriptSCHEMAr1   s     r    init_dbr;   q   sC    Mtd;		T6" 
		s   A
Ac                    [        U 5       nUR                  SR                  U5      5      R                  5        Vs1 s H  o3S   iM	     snsS S S 5        $ s  snf ! , (       d  f       g = f)NzPRAGMA table_info({})name)r,   executer$   fetchall)r2   tabler3   rs       r    _columnsrB   w   sc    		T#'<<#**51$33;8:$> ? $>a&	 $> ? 
	? 
	s   1AAAA
A*c           	     x    [        U 5       nUR                  SXX4U45        S S S 5        g ! , (       d  f       g = f)Na  INSERT INTO account_snapshots (platform, date, followers, media_count, username) VALUES (?, ?, ?, ?, ?) ON CONFLICT(platform, date) DO UPDATE SET followers = excluded.followers, media_count = excluded.media_count, username = COALESCE(excluded.username, account_snapshots.username)r,   r>   )r2   r'   r   	followersmedia_countusernamer3   s          r    save_account_snapshotrH   }   s8     
	TQ YX>	
 
		s   +
9c                   [        U 5       nUR                  SU45      R                  5       nUc  SSSSSS.sSSS5        $ UR                  SXU45      R                  5       nSSS5        WS   US   US   SSS.nWbT  US   bN  US   US   -
  US'   [        R                  " US	   5      [        R                  " US	   5      -
  R
                  US
'   U$ ! , (       d  f       Nx= f)u  Подписчики сейчас и прирост относительно ближайшего снапшота 6+ дней назад.

Окно взято как у ТГ-архива: история бывает редкой, поэтому берём ближайший
подходящий снапшот в пределах месяца и честно показываем его давность.
zrSELECT date, followers, media_count, username FROM account_snapshots WHERE platform = ? ORDER BY date DESC LIMIT 1N)rE   rF   rG   growthgrowth_dayszSELECT date, followers FROM account_snapshots WHERE platform = ? AND date <= date(?, '-6 days') AND date >= date(?, '-31 days') AND followers IS NOT NULL ORDER BY date DESC LIMIT 1rE   rF   rG   rJ   r   rK   )r,   r>   fetchoner   fromisoformatdays)r2   r'   todayr3   currentearlierstates          r    account_staterS      s    
	T,,<K
 (*	 	
 ?!%d"49 
	 ,,) e$
 (* 	 
" "+.w}?U ,TSEw{3?!+.1EEh $ 2 276? C"&"4"4WV_"E!FGKt 	mL/ 
	s   ,C"C
C$c                v    [        U 5       nUR                  SXX445        S S S 5        g ! , (       d  f       g = f)NzUINSERT INTO runs (started_at, finished_at, status, error_summary) VALUES (?, ?, ?, ?)rD   )r2   
started_atfinished_atstatuserror_summaryr3   s         r    
record_runrY      s1    		T"f<	
 
		   *
8c                    [        U 5       nUR                  SU45      R                  5       nSSS5        [        W5      U:H  =(       a    [	        S U 5       5      $ ! , (       d  f       N5= f)u   True, если последние `streak` прогонов подряд упали.

Одиночный сбой сети — обычное дело, будить из-за него не за чем.
z3SELECT status FROM runs ORDER BY rowid DESC LIMIT ?Nc              3  0   #    U  H  oS    S:H  v   M     g7f)rW   failedNr   ).0rA   s     r    	<genexpr>should_alert.<locals>.<genexpr>   s     &M1{h'>s   )r,   r>   r?   lenall)r2   streakr3   rowss       r    should_alertre      s\    
 
	T||AF9

(* 	 
 t9M3&M&M#MM	 
	s   "A
A+c                v    [        U 5       nUR                  SXX445        S S S 5        g ! , (       d  f       g = f)NzINSERT INTO tokens (platform, token, issued_at, expires_at) VALUES (?, ?, ?, ?) ON CONFLICT(platform) DO UPDATE SET token = excluded.token, issued_at = excluded.issued_at, expires_at = excluded.expires_atrD   )r2   r'   token	issued_at
expires_atr3   s         r    
save_tokenrj      s3    		T/ i4	
 
		rZ   c                    [        U 5       nUR                  SU45      R                  5       sS S S 5        $ ! , (       d  f       g = f)Nz'SELECT * FROM tokens WHERE platform = ?)r,   r>   rL   )r2   r'   r3   s      r    
load_tokenrl      s3    		T||5{DDLHJ 
		s	   !7
Ac           	     l   [        U5      nU Vs/ s H  of[        X5      ;   d  M  UPM     nnU(       d  gSR                  S U 5       5      n[        U 5       n	U	R	                  SR                  XX5      U Vs/ s H  ocU   PM	     snXB/-   5        SSS5        gs  snf s  snf ! , (       d  f       g= f)um   Записывает метрики из insights и отметку времени их обновления.N, c              3  D   #    U  H  nS R                  U5      v   M     g7f)z{} = ?Nr$   r^   fs     r    r_   update_stats.<locals>.<genexpr>   s     ?1HOOA..s    z8UPDATE {} SET {}, stats_updated_at = ? WHERE post_id = ?)r(   rB   r%   r,   r>   r$   )
r2   r'   r   metricsnowr@   rr   fieldsassignmentsr3   s
             r    update_statsrx      s     8E !BA')A$AaFB))???K		TFMM$!'(AQZ(C>9	
 
		 C )	 
	s(   BB B%9B B% B%%
B3c                    [        U5      n[        U 5       nUR                  SR                  U5      U45      R	                  5       sSSS5        $ ! , (       d  f       g= f)uQ   Посты не старше `since` (ISO-строка), новые сверху.z=SELECT * FROM {} WHERE timestamp >= ? ORDER BY timestamp DESCN)r(   r,   r>   r$   r?   )r2   r'   sincer@   r3   s        r    posts_in_windowr{      sK    8E		T||KRRSXYH
 (*	 
		s   0A
Ac                    [        U5      n[        U 5       nUR                  SR                  U5      U45        S S S 5        g ! , (       d  f       g = f)Nz.UPDATE {} SET is_deleted = 1 WHERE post_id = ?)r(   r,   r>   r$   )r2   r'   r   r@   r3   s        r    mark_deletedr}      sA    8E		T<CCEJJ	
 
		s   #A
Ac           
         [        U5      n[        U    Vs/ s H  oDU;   d  M
  UPM     nnSR                  S U 5       5      nSR                  S U 5       5      n[        U 5       nUR	                  SR                  U5      US   45      R                  5       SLn	UR	                  SR                  USR                  U5      XgS9U Vs/ s H  oBU   PM	     sn5        SSS5        U	(       + $ s  snf s  snf ! , (       d  f       W	(       + $ = f)	uQ  Пишет пост. Возвращает True, если пост появился в базе впервые.

Повторный вызов обновляет поля поста, не создавая дубля: у постов растут
лайки и комментарии, и свежие цифры важнее первых.
rn   c              3  &   #    U  H  nS v   M	     g7f)?Nr   )r^   _s     r    r_   upsert_post.<locals>.<genexpr>   s     1&QS&s   c              3  R   #    U  H  oS :w  d  M
  SR                  U5      v   M     g7f)r   z{0} = excluded.{0}Nrp   rq   s     r    r_   r      s*      J061y.&##A&&s   	''z"SELECT 1 FROM {} WHERE post_id = ?r   NzYINSERT INTO {table} ({cols}) VALUES ({vals}) ON CONFLICT(post_id) DO UPDATE SET {updates})r@   colsvalsupdates)r(   POST_FIELDSr%   r,   r>   r$   rL   )
r2   r'   postr@   rr   rv   placeholdersr   r3   existeds
             r    upsert_postr      s    8E$X.<.At)a.F<991&11Lii J06J JG		T,,077>)_
 (*D! 	;;A6$))F"3! <B <4 %%f!Wf%	
 
 ;! = & 
	 ;s)   	C/C/'A$C9C4C94C99
D)r'   strreturnr   )r2   r   )r2   r   r   None)r2   r   r@   r   r   setr*   )r2   r   r'   r   r   r   rE   intrF   z
int | NonerG   
str | Noner   r   )r2   r   r'   r   rO   r   r   dict)r2   r   rU   r   rV   r   rW   r   rX   r   r   r   )   )r2   r   rc   r   r   bool)r2   r   r'   r   rg   r   rh   r   ri   r   r   r   )r2   r   r'   r   )r2   r   r'   r   r   r   rt   r   ru   r   r   r   )r2   r   r'   r   rz   r   r   list)r2   r   r'   r   r   r   r   r   )r2   r   r'   r   r   r   r   r   )r   
__future__r   r+   
contextlibr   datetimer   pathlibr   r"   r   r:   
ValueErrorr   r(   r,   r;   rB   rH   rS   rY   re   rj   rl   rx   r{   r}   r   r   r   r    <module>r      s   e "  %  
 "j	9A*?
D=: =  #? 26

%(

7A

$.

:>

@

+.
37
	N	
	
+5	
:>	
O

%(
-1
$
r   