o
    ÕÞajƒH  ã                   @   s6  d dl Z d dlZd dl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d\d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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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	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]de	d0e	d1e	d2e	dB d3e	dB d4e	fd5d6„Zde	d7efd8d9„Z de	dee fd:d;„Z!de	d0e	d1e	d2e	dB d3e	dB d4e	d<efd=d>„Z"de	d7efd?d@„Z#de	d7ededefdAdB„Z$de	d7ede%eef fdCdD„Z&de	d7efdEdF„Z'dGdH„ Z(de	dIe	dJe	dKe	def
dLdM„Z)de	dee fdNdO„Z*de	dPededB fdQdR„Z+			S	d^de	dPedIe	dJe	dKe	dTe	dUe	dVe	dWefdXdY„Z,de	dPefdZd[„Z-dS )_é    N©ÚdatetimeÚ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/transcoding/db.pyÚget_db_path   s   r   c              	   Ã   sô   �t  t| ƒ¡4 I d H ša}| 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  qK| ¡ I d H  W d   ƒI d H  d S 1 I d H ssw   Y  d S )
NzŒ
            CREATE TABLE IF NOT EXISTS settings (
                key   TEXT PRIMARY KEY,
                value TEXT
            )
        a\  
            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
            )
        av  
            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 DEFAULT 100,
                current_count    INTEGER DEFAULT 0,
                is_active        INTEGER DEFAULT 1,
                added_at         TEXT,
                completed_at     TEXT
            )
        aZ  
            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 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
            )
        ))Úis_openÚ1)Úclosed_messageu(   ðŸ”´ Ø§Ù„Ø¨ÙˆØª Ù…Ù‚Ù�ÙˆÙ„ Ø­Ø§Ù„ÙŠØ§Ù‹.)Úwelcome_messageÚ )Únotify_new_usersr   )Únotify_blockedr   )Úfund_notifyr   z9INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)©Ú	aiosqliteÚconnectr   ÚexecuteÚcommit)r   Ú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_settingk   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   Úset_settingr   s   €
ÿ.ür)   c              
   Ã   ó´   �t  t| ƒ¡4 I d H šA}| d¡4 I d H š!}dd„ | ¡ I d H 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 sSw   Y  d S )NzSELECT key, value FROM settingsc                 S   s   i | ]	}|d  |d “qS )r   é   r   ©Ú.0Úrr   r   r   Ú
<dictcomp>}   s    z$get_all_settings.<locals>.<dictcomp>©r   r   r   r   Úfetchall©r   r   r&   r   r   r   Úget_all_settingsz   s   €ÿÿ.ÿr3   Úuser_idÚusernameÚ
first_namec              
   Ã   sF  �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  |se| 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œW  d   ƒI d H  S 1 I d H sœw   Y  d S )NúSELECT * FROM users WHERE id=?zRINSERT INTO users (id,username,first_name,last_active,joined_at) VALUES(?,?,?,?,?)r   T)Úidr5   r6   Ú	is_bannedÚ
is_blockedÚis_newzAUPDATE users SET username=?,first_name=?,last_active=? WHERE id=?r+   é   é   é   F©	r   ÚutcnowÚ	isoformatr   r   r   r   r%   r   )r   r4   r5   r6   Únowr   r&   r'   r   r   r   Úget_or_create_user€   s2   €(ÿ
þÿ÷

þÿ0ðrC   c              
   Ã   s   �t  t| ƒ¡4 I d H šg}| d|f¡4 I d H šE}| ¡ I d H }|s7	 W d   ƒI d H  W d   ƒI d H  d S |d |d |d |d |d dœW  d   ƒI d H  W  d   ƒI d H  S 1 I d H scw   Y  W d   ƒI d H  d S 1 I d H syw   Y  d S )Nr7   r   r+   r<   r=   r>   )r8   r5   r6   r9   r:   r$   )r   r4   r   r&   r'   r   r   r   Úget_user—   s   €ýÿÿüÿ.ÿrD   c              
   Ã   r*   )NzKSELECT id,username,first_name FROM users WHERE is_banned=0 AND is_blocked=0c                 S   ó$   g | ]}|d  |d |d dœ‘qS ©r   r+   r<   )r8   r5   r6   r   r,   r   r   r   Ú
<listcomp>¦   ó    ÿz(get_all_active_users.<locals>.<listcomp>r0   r2   r   r   r   Úget_all_active_users¡   ó   €ÿÿýÿ.ÿrI   c              
   Ã   sè   �t  t| ƒ¡4 I d H š[}| d|f¡4 I d H š'}| ¡ I d H s5	 W d   ƒI d H  W d   ƒI d H  dS W d   ƒI d H  n1 I d H sEw   Y  | d|f¡I d H  | ¡ I d H  	 W d   ƒI d H  dS 1 I d H smw   Y  d S )NzSELECT id FROM users WHERE id=?Fz'UPDATE users SET is_banned=1 WHERE id=?T)r   r   r   r   r%   r   ©r   r4   r   r&   r   r   r   Úban_userª   s   €þÿ(ÿ0úrL   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'UPDATE users SET is_banned=0 WHERE id=?r   ©r   r4   r   r   r   r   Ú
unban_user´   ó
   €.þrO   c              
   Ã   r*   )Nz:SELECT id,username,first_name FROM users WHERE is_banned=1c                 S   rE   rF   r   r,   r   r   r   rG   ¿   rH   z$get_banned_users.<locals>.<listcomp>r0   r2   r   r   r   Úget_banned_usersº   rJ   rQ   c              
   Ã   s’  �t  t| ƒ¡4 I d H š«}| d¡4 I d H š}| ¡ I d H d }W d   ƒI d H  n1 I d H s0w   Y  | d¡4 I d H š}| ¡ I d H d }W d   ƒI d H  n1 I d H sXw   Y  | d¡4 I d H š}| ¡ I d H d }W d   ƒI d H  n1 I d H s€w   Y  | d¡4 I d H š}| ¡ I d H d }W d   ƒI d H  n1 I d H s¨w   Y  W d   ƒI d H  n1 I d H s½w   Y  ||||dœS )NzSELECT COUNT(*) FROM usersr   z,SELECT COUNT(*) FROM users WHERE is_banned=1z-SELECT COUNT(*) FROM users WHERE is_blocked=1z=SELECT COUNT(*) FROM users WHERE is_banned=0 AND is_blocked=0)ÚtotalÚbannedÚblockedÚactiver$   )r   r   r&   rR   rS   rT   rU   r   r   r   Úget_statisticsÃ   s&   €(ÿ(ÿ(ÿÿ*ý(ùrV   c              
   Ã   r*   )Nz)SELECT id,username,first_name FROM adminsc                 S   rE   rF   r   r,   r   r   r   rG   Õ   rH   zget_admins.<locals>.<listcomp>r0   r2   r   r   r   Ú
get_adminsÒ   s   €ÿÿÿ.ÿrW   c              
   Ã   s²   �t  t| ƒ¡4 I d H š@}| d|f¡4 I d H š}t| ¡ I d H ƒ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   Úboolr%   rK   r   r   r   Úis_adminÙ   s   €ÿÿ.ÿrY   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 )NzOINSERT OR REPLACE INTO admins (id,username,first_name,added_at) VALUES(?,?,?,?)©r   r@   rA   r   r   r   r   r   )r   r4   r5   r6   rB   r   r   r   r   Ú	add_adminß   s   €

þ.ûr[   c              	   Ã   rM   )NzDELETE FROM admins WHERE id=?r   rN   r   r   r   Úremove_adminê   rP   r\   c              
   Ã   r*   )NzvSELECT id,channel_id,channel_username,channel_title,invite_link,display_type FROM mandatory_channels WHERE is_active=1c              	   S   s6   g | ]}|d  |d |d |d |d |d dœ‘qS )r   r+   r<   r=   r>   é   )r8   Ú
channel_idÚchannel_usernameÚchannel_titleÚinvite_linkÚdisplay_typer   r,   r   r   r   rG   ö   s
    þ
ÿz*get_mandatory_channels.<locals>.<listcomp>r0   r2   r   r   r   Úget_mandatory_channelsð   s   €ÿþüÿ.ÿrc   Úbuttonsr^   r`   r_   ra   rb   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 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,?)rZ   )r   r^   r`   r_   ra   rb   rB   r   r   r   r   Úadd_mandatory_channelû   s   €
ü.ùre   Úchannel_db_idc              	   Ã   rM   )Nz4UPDATE mandatory_channels SET is_active=0 WHERE id=?r   ©r   rf   r   r   r   r   Úremove_mandatory_channel	  ó   €
ÿ.ürh   c              
   Ã   r*   )NzŽSELECT id,channel_id,channel_username,channel_title,invite_link,display_type,target_count,current_count FROM funded_channels WHERE is_active=1c                 S   sB   g | ]}|d  |d |d |d |d |d |d |d dœ‘qS )	r   r+   r<   r=   r>   r]   é   é   )r8   r^   r_   r`   ra   rb   Útarget_countÚcurrent_countr   r,   r   r   r   rG     s    ý
þz'get_funded_channels.<locals>.<listcomp>r0   r2   r   r   r   Úget_funded_channels  s   €ÿýûÿ.ÿrn   rl   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 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,?)rZ   )	r   r^   r`   r_   ra   rb   rl   rB   r   r   r   r   Úadd_funded_channel  s   €ÿ
ü.øro   c              	   Ã   rM   )Nz1UPDATE funded_channels SET is_active=0 WHERE id=?r   rg   r   r   r   Úremove_funded_channel-  ri   rp   c              
   Ã   s  �t  ¡  ¡ }t t| ƒ¡4 I d H š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  | ¡ I d H  	 W d   ƒI d H  dS 1 I d H s€w   Y  d S )NzKSELECT id FROM funded_subscriptions WHERE funded_channel_id=? AND user_id=?FzXINSERT INTO funded_subscriptions (funded_channel_id,user_id,subscribed_at) VALUES(?,?,?)zCUPDATE funded_channels SET current_count=current_count+1 WHERE id=?Tr?   )r   rf   r4   rB   r   r&   r   r   r   Úrecord_funded_sub5  s2   €þûÿ(ü
ý
þ0ïrq   c              
   Ã   sÆ   �t  t| ƒ¡4 I d H šJ}| d|f¡4 I d H š(}| ¡ I d H }|r*|d |d fn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 )NzASELECT current_count,target_count FROM funded_channels WHERE id=?r   r+   )r   r   r$   )r   rf   r   r&   r'   r   r   r   Úget_funded_channel_countL  s   €þûÿ.ÿrr   c              	   Ã   sz   �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 s6w   Y  d S )Nz@UPDATE funded_channels SET is_active=0,completed_at=? WHERE id=?rZ   )r   rf   rB   r   r   r   r   Úmark_funded_completeW  s   €
þ.ûrs   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_columns`  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   r@   rA   r   Ú	lastrowid)r   r|   r}   r~   Ú_dtr   r&   r   r   r   Úadd_custom_buttonm  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   r+   r<   r=   r>   r]   rj   rk   é   ©	r8   r|   r}   r~   Úpositionrt   ru   rv   rw   r   r,   r   r   r   rG   ‚  s    þÿÿz&get_custom_buttons.<locals>.<listcomp>)r   r   r   r{   r   r1   )r   r   r&   Úrowsr   r   r   Úget_custom_buttonsz  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   r+   r<   r=   r>   r]   rj   rk   r‚   rƒ   )r   r   r   r{   r   r%   )r   r‡   r   r&   r.   r   r   r   Úget_custom_button‰  s(   €ýùþ
ÿøþ.þrˆ   Ú__keep__rt   ru   rv   rw   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~   rt   ru   rv   rw   r   ÚsetsÚvalsr   r   r   Úupdate_custom_button˜  s2   €
î
 .ër�   c              	   Ã   rM   )Nz'DELETE FROM custom_buttons WHERE id = ?r   )r   r‡   r   r   r   r   Údelete_custom_button´  rP   rŽ   )r   )rd   )NNNr‰   r‰   r‰   N).r   r   r   r   r   ÚdirnameÚabspathÚ__file__r
   Ústrr   r"   r(   r)   Údictr3   ÚintrC   rD   ÚlistrI   rX   rL   rO   rQ   rV   rW   rY   r[   r\   rc   re   rh   rn   ro   rp   rq   Útuplerr   rs   r{   r�   r†   rˆ   r�   rŽ   r   r   r   r   Ú<module>   s¨   Z
ÿÿ
ÿ
	
	
ÿ
ÿþÿÿ
þÿÿþ
þ
ÿ
ÿÿ

ÿ	ýÿÿþþý
ý