o
    ¯ÞajcL  ã                   @   s  d dl Z d dlZd dlmZmZmZ e j e j e j e	¡¡d¡Z
dedefdd„Zdefdd	„ZdZdedededefdd„Zdededefdd„Zdedefdd„ZdedededB dedef
dd„ZdedededB fdd„Zdedee fdd„Zdedefdd„ZdedededB defdd „Zdedefd!d"„Zdedee fd#d$„Zdededefd%d&„Zdefd'd(„Z	)d[ded*ed+ed,edB d-edB d.efd/d0„Zdedee fd1d2„Zded3efd4d5„Z 	)d[ded*ed+ed,edB d-edB d6ed.efd7d8„Z!dedee fd9d:„Z"ded3efd;d<„Z#ded=ededefd>d?„Z$deded@efdAdB„Z%dededefdCdD„Z&dEdF„ Z'dedGedHedIedef
dJdK„Z(dedee fdLdM„Z)dedNededB fdOdP„Z*			Q	d\dedNedGedHedIedRedSedTedUefdVdW„Z+dedNefdXdY„Z,dS )]é    N)ÚdatetimeÚ	timedeltaÚdateÚdatabaseÚbot_usernameÚreturnc                 C   s&   t jtdd� t j t|  ¡ › d�¡S )NT)Úexist_okz.db)ÚosÚmakedirsÚDB_DIRÚpathÚjoinÚlower)r   © r   ú+/var/www/kodo/factory/cobots/download/db.pyÚget_db_path   s   r   c              	   Ã   s  �t | ƒ}t |¡4 I d H šq}| d¡I d H  | d¡I d H  | d¡I d H  | d¡I d H  | d¡I d H  | d¡I d H  | d¡I d H  | d¡I d H  g d	¢}|D ]\}}| d
||f¡I d H  qU| d¡I d H  | ¡ I d H  W d   ƒI d H  d S 1 I d H s…w   Y  d S )Nz‡
           CREATE TABLE IF NOT EXISTS settings (
               key   TEXT PRIMARY KEY,
               value TEXT
           )
       aR  
           CREATE TABLE IF NOT EXISTS users (
               id          INTEGER PRIMARY KEY,
               username    TEXT,
               first_name  TEXT,
               is_banned   INTEGER DEFAULT 0,
               is_blocked  INTEGER DEFAULT 0,
               last_active TEXT,
               joined_at   TEXT
           )
       zÒ
           CREATE TABLE IF NOT EXISTS admins (
               id         INTEGER PRIMARY KEY,
               username   TEXT,
               first_name TEXT,
               added_at   TEXT
           )
       aÔ  
           CREATE TABLE IF NOT EXISTS mandatory_channels (
               id               INTEGER PRIMARY KEY AUTOINCREMENT,
               channel_id       TEXT UNIQUE NOT NULL,
               channel_username TEXT,
               channel_title    TEXT NOT NULL,
               invite_link      TEXT,
               display_type     TEXT DEFAULT 'buttons',
               is_active        INTEGER DEFAULT 1,
               added_at         TEXT
           )
       a\  
           CREATE TABLE IF NOT EXISTS funded_channels (
               id               INTEGER PRIMARY KEY AUTOINCREMENT,
               channel_id       TEXT UNIQUE NOT NULL,
               channel_username TEXT,
               channel_title    TEXT NOT NULL,
               invite_link      TEXT,
               display_type     TEXT DEFAULT 'buttons',
               target_count     INTEGER NOT NULL,
               current_count    INTEGER DEFAULT 0,
               is_active        INTEGER DEFAULT 1,
               added_at         TEXT,
               completed_at     TEXT
           )
       aU  
           CREATE TABLE IF NOT EXISTS funded_subscriptions (
               id                INTEGER PRIMARY KEY AUTOINCREMENT,
               funded_channel_id INTEGER NOT NULL,
               user_id           INTEGER NOT NULL,
               subscribed_at     TEXT,
               UNIQUE(funded_channel_id, user_id)
           )
       a  
           CREATE TABLE IF NOT EXISTS downloads (
               id            INTEGER PRIMARY KEY AUTOINCREMENT,
               user_id       INTEGER NOT NULL,
               platform      TEXT NOT NULL,
               downloaded_at TEXT
           )
       z÷
           CREATE TABLE IF NOT EXISTS daily_downloads (
               user_id  INTEGER NOT NULL,
               dl_date  TEXT NOT NULL,
               count    INTEGER DEFAULT 0,
               PRIMARY KEY (user_id, dl_date)
           )
       ))Úis_openÚ1)Úclosed_messageu(   ðŸ”´ Ø§Ù„Ø¨ÙˆØª Ù…Ù‚Ù�ÙˆÙ„ Ø­Ø§Ù„ÙŠØ§Ù‹.)Únotify_new_usersr   )Únotify_blockedr   )Údaily_limitÚ	unlimited)Úmax_durationÚ10)Úwelcome_messageÚ )Únotify_funded_subr   z9INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)aV  
           CREATE TABLE IF NOT EXISTS custom_buttons (
               id          INTEGER PRIMARY KEY AUTOINCREMENT,
               btn_type    TEXT NOT NULL,
               label       TEXT NOT NULL,
               content     TEXT NOT NULL,
               position    INTEGER DEFAULT 0,
               created_at  TEXT
           )
       )r   Ú	aiosqliteÚconnectÚexecuteÚcommit)r   Údb_pathÚdbÚdefaultsÚkeyÚvaluer   r   r   Úinit_db   s(   €	
		
þ.�r'   r   r%   Údefaultc              
   Ã   s¾   �t  t| ƒ¡4 I d H šF}| d|f¡4 I d H š$}| ¡ I d H }|r&|d n|W  d   ƒI d H  W  d   ƒI d H  S 1 I d H sBw   Y  W d   ƒI d H  d S 1 I d H sXw   Y  d S )Nz(SELECT value FROM settings WHERE key = ?r   ©r   r   r   r    Úfetchone)r   r%   r(   r#   ÚcurÚrowr   r   r   Úget_setting‡   s   €þÿ.ÿr-   r&   c              	   Ã   sn   �t  t| ƒ¡4 I d H š}| d||f¡I d H  | ¡ I d H  W d   ƒI d H  d S 1 I d H s0w   Y  d S )Nz:INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)©r   r   r   r    r!   )r   r%   r&   r#   r   r   r   Úset_settingŽ   s   €
þ.ûr/   c              
   Ã   ó¸   �t  t| ƒ¡4 I d H šC}| d¡4 I d H š#}| ¡ I d H }dd„ |D ƒW  d   ƒI d H  W  d   ƒI d H  S 1 I d H s?w   Y  W d   ƒI d H  d S 1 I d H sUw   Y  d S )NzSELECT key, value FROM settingsc                 S   s   i | ]	}|d  |d “qS )r   é   r   )Ú.0r,   r   r   r   Ú
<dictcomp>›   s    z$get_all_settings.<locals>.<dictcomp>©r   r   r   r    Úfetchall©r   r#   r+   Úrowsr   r   r   Úget_all_settings—   ó   €þÿ.ÿr8   Úuser_idÚusernameÚ
first_namec              
   Ã   sN  �t  ¡  ¡ }t t| ƒ¡4 I d H šˆ}| d|f¡4 I d H š}| ¡ I d H }W d   ƒI d H  n1 I d H s6w   Y  |sf| d|||||f¡I d H  | ¡ I d H  |||dd|ddœW  d   ƒI d H  S | d||||f¡I d H  | ¡ I d H  |d |d |d |d	 |d
 |d ddœW  d   ƒI d H  S 1 I d H s w   Y  d S )Nú SELECT * FROM users WHERE id = ?z[INSERT INTO users (id, username, first_name, last_active, joined_at) VALUES (?, ?, ?, ?, ?)r   T)Úidr;   r<   Ú	is_bannedÚ
is_blockedÚ	joined_atÚis_newzKUPDATE users SET username = ?, first_name = ?, last_active = ? WHERE id = ?r1   é   é   é   é   F©	r   ÚutcnowÚ	isoformatr   r   r   r    r*   r!   )r   r:   r;   r<   Únowr#   r+   r,   r   r   r   Úget_or_create_userž   s4   €(ÿ
þÿ÷

þþ0ïrK   c              
   Ã   s  �t  t| ƒ¡4 I d H šm}| d|f¡4 I d H šK}| ¡ I d H }|s7	 W d   ƒI d H  W d   ƒI d H  d S |d |d |d |d |d |d |d d	œW  d   ƒI d H  W  d   ƒI d H  S 1 I d H siw   Y  W d   ƒI d H  d S 1 I d H sw   Y  d S )
Nr=   r   r1   rC   rD   rE   é   rF   )r>   r;   r<   r?   r@   Úlast_activerA   r)   )r   r:   r#   r+   r,   r   r   r   Úget_user·   s    €ýÿþüÿ.ÿrN   c              
   Ã   r0   )Nz;SELECT id FROM users WHERE is_blocked = 0 AND is_banned = 0c                 S   s   g | ]}d |d i‘qS )r>   r   r   ©r2   Úrr   r   r   Ú
<listcomp>Æ   s    z(get_all_active_users.<locals>.<listcomp>r4   r6   r   r   r   Úget_all_active_usersÂ   r9   rR   c              	   Ã   s‚  �t  ¡ }|tdd�  ¡ }t t| ƒ¡4 I d H š’}| d¡I d H  ¡ I d H d }| d|f¡I d H  ¡ I d H d }| d¡I d H  ¡ I d H d }| d¡I d H  ¡ I d H d }| d¡I d H  ¡ I d H d }i }	d	D ]}
| d
|
f¡I d H  ¡ I d H d }||	|
< qk| d¡I d H  ¡ I d H d }| d¡I d H  ¡ I d H d }W d   ƒI d H  n1 I d H s±w   Y  ||||||	||dœS )Né   )ÚhourszSELECT COUNT(*) FROM usersr   z1SELECT COUNT(*) FROM users WHERE last_active >= ?z.SELECT COUNT(*) FROM users WHERE is_banned = 1z/SELECT COUNT(*) FROM users WHERE is_blocked = 1zSELECT COUNT(*) FROM downloads)ÚyoutubeÚtiktokÚ	instagramÚfacebookÚsnapchatÚ	pinterestz1SELECT COUNT(*) FROM downloads WHERE platform = ?z;SELECT COUNT(*) FROM mandatory_channels WHERE is_active = 1z8SELECT COUNT(*) FROM funded_channels WHERE is_active = 1)Útotal_usersÚ
active_24hÚbanned_usersÚblocked_botÚtotal_dlÚdl_by_platformÚ
mand_countÚ
fund_count)	r   rH   r   rI   r   r   r   r    r*   )r   rJ   Úlast_24hr#   ÚtotalÚactiveÚbannedÚblockedr_   r`   ÚpÚcountra   rb   r   r   r   Úget_statisticsÉ   s*   €""
 (õürj   c              	   Ã   s~   �t  ¡  ¡ }t t| ƒ¡4 I d H š }| d||||f¡I d H  | ¡ I d H  W d   ƒI d H  d S 1 I d H s8w   Y  d S )NzVINSERT OR REPLACE INTO admins (id, username, first_name, added_at) VALUES (?, ?, ?, ?))r   rH   rI   r   r   r   r    r!   )r   r:   r;   r<   rJ   r#   r   r   r   Ú	add_adminà   s   €

þ.ûrk   c              	   Ã   ól   �t  t| ƒ¡4 I d H š}| d|f¡I d H  | ¡ I d H  W d   ƒI d H  d S 1 I d H s/w   Y  d S )NzDELETE FROM admins WHERE id = ?r.   )r   r:   r#   r   r   r   Úremove_adminê   ó
   €.þrm   c              
   Ã   r0   )NzSELECT * FROM adminsc                 S   s$   g | ]}|d  |d |d dœ‘qS )r   r1   rC   )r>   r;   r<   r   rO   r   r   r   rQ   ô   s   $ zget_admins.<locals>.<listcomp>r4   r6   r   r   r   Ú
get_adminsð   r9   ro   c              
   Ã   s²   �t  t| ƒ¡4 I d H š@}| d|f¡4 I d H š}| ¡ I d H d uW  d   ƒI d H  W  d   ƒI d H  S 1 I d H s<w   Y  W d   ƒI d H  d S 1 I d H sRw   Y  d S )Nz"SELECT id FROM admins WHERE id = ?r)   )r   r:   r#   r+   r   r   r   Úis_admin÷   s   €ÿÿ.ÿrp   c              	   Ã   sh   �t  t| ƒ¡4 I d H š}| d¡I d H  | ¡ I d H  W d   ƒI d H  d S 1 I d H s-w   Y  d S )NzDELETE FROM adminsr.   )r   r#   r   r   r   Úclear_adminsý   s
   €.þrq   ÚbuttonsÚ
channel_idÚchannel_titleÚchannel_usernameÚinvite_linkÚdisplay_typec              
   Ã   s†   �t  ¡  ¡ }t t| ƒ¡4 I d H š$}| dt|ƒ|||||f¡I d H  | ¡ I d H  W d   ƒI d H  d S 1 I d H s<w   Y  d S )NzÀINSERT OR REPLACE INTO mandatory_channels
              (channel_id, channel_username, channel_title, invite_link, display_type, is_active, added_at)
              VALUES (?, ?, ?, ?, ?, 1, ?)©	r   rH   rI   r   r   r   r    Ústrr!   )r   rs   rt   ru   rv   rw   rJ   r#   r   r   r   Úadd_mandatory_channel  s   €
ü.ùrz   c              
   Ã   r0   )Nz4SELECT * FROM mandatory_channels WHERE is_active = 1c              	   S   s6   g | ]}|d  |d |d |d |d |d dœ‘qS )r   r1   rC   rD   rE   rL   )r>   rs   ru   rt   rv   rw   r   rO   r   r   r   rQ     s
    ÿ
ÿz*get_mandatory_channels.<locals>.<listcomp>r4   r6   r   r   r   Úget_mandatory_channels  s   €ÿþÿ.ÿr{   Úchannel_db_idc              	   Ã   rl   )Nz8UPDATE mandatory_channels SET is_active = 0 WHERE id = ?r.   ©r   r|   r#   r   r   r   Úremove_mandatory_channel  rn   r~   Útarget_countc           	      Ã   sˆ   �t  ¡  ¡ }t t| ƒ¡4 I d H š%}| dt|ƒ||||||f¡I d H  | ¡ I d H  W d   ƒI d H  d S 1 I d H s=w   Y  d S )NzïINSERT OR REPLACE INTO funded_channels
              (channel_id, channel_username, channel_title, invite_link,
               display_type, target_count, current_count, is_active, added_at)
              VALUES (?, ?, ?, ?, ?, ?, 0, 1, ?)rx   )	r   rs   rt   ru   rv   r   rw   rJ   r#   r   r   r   Úadd_funded_channel  s   €
û.ør€   c              
   Ã   r0   )Nz1SELECT * FROM funded_channels WHERE is_active = 1c                 S   sB   g | ]}|d  |d |d |d |d |d |d |d dœ‘qS )	r   r1   rC   rD   rE   rL   rF   é   )r>   rs   ru   rt   rv   rw   r   Úcurrent_countr   rO   r   r   r   rQ   2  s    þ
þz'get_funded_channels.<locals>.<listcomp>r4   r6   r   r   r   Úget_funded_channels.  s   €þþÿ.ÿrƒ   c              	   Ã   rl   )Nz5UPDATE funded_channels SET is_active = 0 WHERE id = ?r.   r}   r   r   r   Úremove_funded_channel7  rn   r„   Úfunded_channel_idc              
   Ã   sŒ  �t  ¡  ¡ }t t| ƒ¡4 I d H š§}| d||f¡4 I d H š'}| ¡ I d H r<	 W d   ƒI d H  W d   ƒI d H  dS W d   ƒI d H  n1 I d H sLw   Y  | d|||f¡I d H  | d|f¡I d H  | d|f¡4 I d H š}| ¡ I d H }W d   ƒI d H  n1 I d H sŠw   Y  |o˜|d |d k}|r¦| d||f¡I d H  | ¡ I d H  |W  d   ƒI d H  S 1 I d H s¿w   Y  d S )	NzOSELECT id FROM funded_subscriptions WHERE funded_channel_id = ? AND user_id = ?Fz]INSERT INTO funded_subscriptions (funded_channel_id, user_id, subscribed_at) VALUES (?, ?, ?)zIUPDATE funded_channels SET current_count = current_count + 1 WHERE id = ?zDSELECT current_count, target_count FROM funded_channels WHERE id = ?r   r1   zGUPDATE funded_channels SET is_active = 0, completed_at = ? WHERE id = ?rG   )r   r…   r:   rJ   r#   r+   r,   Ú	completedr   r   r   Úrecord_funded_subscription=  sL   €þûÿ(ü
þ
þþ(ü
þ0år‡   Úplatformc              	   Ã   sž   �t  ¡  ¡ }t ¡  ¡ }t t| ƒ¡4 I d H š*}| d|||f¡I d H  | d||f¡I d H  | 	¡ I d H  W d   ƒI d H  d S 1 I d H sHw   Y  d S )NzIINSERT INTO downloads (user_id, platform, downloaded_at) VALUES (?, ?, ?)z’INSERT INTO daily_downloads (user_id, dl_date, count) VALUES (?, ?, 1)
              ON CONFLICT(user_id, dl_date) DO UPDATE SET count = count + 1)
r   rH   rI   r   Útodayr   r   r   r    r!   )r   r:   rˆ   rJ   r‰   r#   r   r   r   Úrecord_download]  s   €
þ
ý.örŠ   c              
   Ã   sÌ   �t  ¡  ¡ }t t| ƒ¡4 I d H šG}| d||f¡4 I d H š$}| ¡ I d H }|r-|d ndW  d   ƒI d H  W  d   ƒI d H  S 1 I d H sIw   Y  W d   ƒI d H  d S 1 I d H s_w   Y  d S )NzCSELECT count FROM daily_downloads WHERE user_id = ? AND dl_date = ?r   )r   r‰   rI   r   r   r   r    r*   )r   r:   r‰   r#   r+   r,   r   r   r   Úget_user_daily_countm  s   €þûÿ.ÿr‹   c              	   Ã   s:   �dD ]\}}z
|   |¡I d H  W q ty   Y qw d S )N))Úemojiz0ALTER TABLE custom_buttons ADD COLUMN emoji TEXT)Úcustom_emoji_idz:ALTER TABLE custom_buttons ADD COLUMN custom_emoji_id TEXT)Úcolorz0ALTER TABLE custom_buttons ADD COLUMN color TEXT)Ú
is_visiblezBALTER TABLE custom_buttons ADD COLUMN is_visible INTEGER DEFAULT 1)r    Ú	Exception)r#   ÚcolÚddlr   r   r   Ú_ensure_cbtn_columnsx  s   €ÿør“   Úbtn_typeÚlabelÚcontentc              	   Ã   s˜   �ddl m } t t| ƒ¡4 I d H š-}t|ƒI d H  | d|||| ¡  ¡ f¡I d H }| ¡ I d H  |j	W  d   ƒI d H  S 1 I d H sEw   Y  d S )Nr   )r   z¨INSERT INTO custom_buttons (btn_type, label, content, position, created_at, is_visible) VALUES (?, ?, ?, (SELECT COALESCE(MAX(position),0)+1 FROM custom_buttons), ?, 1))
r   r   r   r   r“   r    rH   rI   r!   Ú	lastrowid)r   r”   r•   r–   Ú_dtr#   r+   r   r   r   Úadd_custom_button…  s   €
ý0ør™   c              
   Ã   sÆ   �t  t| ƒ¡4 I d H šJ}t|ƒI d H  | d¡4 I d H š#}| ¡ I d H }dd„ |D ƒW  d   ƒI d H  W  d   ƒI d H  S 1 I d H sFw   Y  W d   ƒI d H  d S 1 I d H s\w   Y  d S )NzŠSELECT id, btn_type, label, content, position, emoji, custom_emoji_id, color, COALESCE(is_visible,1) FROM custom_buttons ORDER BY positionc                 S   sH   g | ] }|d  |d |d |d |d |d |d |d |d d	œ	‘qS )
r   r1   rC   rD   rE   rL   rF   r�   é   ©	r>   r”   r•   r–   ÚpositionrŒ   r�   rŽ   r�   r   rO   r   r   r   rQ   š  s    þÿÿz&get_custom_buttons.<locals>.<listcomp>)r   r   r   r“   r    r5   r6   r   r   r   Úget_custom_buttons’  s   €ÿýûþ.þr�   Úbtn_idc                 Ã   s&  �t  t| ƒ¡4 I d H šz}t|ƒI d H  | d|f¡4 I d H šQ}| ¡ I d H }|s>	 W d   ƒI d H  W d   ƒI d H  d S |d |d |d |d |d |d |d |d	 |d
 dœ	W  d   ƒI d H  W  d   ƒI d H  S 1 I d H svw   Y  W d   ƒI d H  d S 1 I d H sŒw   Y  d S )Nz…SELECT id, btn_type, label, content, position, emoji, custom_emoji_id, color, COALESCE(is_visible,1) FROM custom_buttons WHERE id = ?r   r1   rC   rD   rE   rL   rF   r�   rš   r›   )r   r   r   r“   r    r*   )r   rž   r#   r+   rP   r   r   r   Úget_custom_button¡  s(   €ýùþ
ÿøþ.þrŸ   Ú__keep__rŒ   r�   rŽ   r�   c	              	   Ã   sz  �t  t| ƒ¡4 I d H š¤}	t|	ƒI d H  g g }
}|d ur'|
 d¡ | |¡ |d ur5|
 d¡ | |¡ |d urC|
 d¡ | |¡ |dkrQ|
 d¡ | |¡ |dkr_|
 d¡ | |¡ |dkrm|
 d¡ | |¡ |d ur{|
 d¡ | |¡ |
s‰	 W d   ƒI d H  d S | |¡ |	 d	d
 |
¡› d�|¡I d H  |	 ¡ I d H  W d   ƒI d H  d S 1 I d H s¶w   Y  d S )Nzbtn_type = ?z	label = ?zcontent = ?r    z	emoji = ?zcustom_emoji_id = ?z	color = ?zis_visible = ?zUPDATE custom_buttons SET z, z WHERE id = ?)r   r   r   r“   Úappendr    r   r!   )r   rž   r”   r•   r–   rŒ   r�   rŽ   r�   r#   ÚsetsÚvalsr   r   r   Úupdate_custom_button°  s2   €
î
 .ër¤   c              	   Ã   rl   )Nz'DELETE FROM custom_buttons WHERE id = ?r.   )r   rž   r#   r   r   r   Údelete_custom_buttonÌ  rn   r¥   )r   )rr   )NNNr    r    r    N)-r	   r   r   r   r   r   r   ÚdirnameÚabspathÚ__file__r   ry   r   r'   r-   r/   Údictr8   ÚintrK   rN   ÚlistrR   rj   rk   rm   ro   Úboolrp   rq   rz   r{   r~   r€   rƒ   r„   r‡   rŠ   r‹   r“   r™   r�   rŸ   r¤   r¥   r   r   r   r   Ú<module>   sŽ   v	
ÿÿ
ÿ
þÿÿ
þþÿÿþ
þ	 ýÿÿþþý
ý