o
    #F5j<  ã                   @   sn   d dl Z d dl mZ d dlmZ d dlmZmZmZ d dlZd dlZej	ej
d� e e¡ZG dd„ dƒZdS )é    N)ÚError)Údatetime)ÚOptionalÚDictÚList)Úlevelc                	   @   s–   e Zd Zdefdd„Zdd„ Zdd„ Zdd	„ Zd
ede	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d„ ZdS )ÚDatabaseÚconfigc                 C   s    || _ d | _|  ¡  |  ¡  d S )N)r	   Ú
connectionÚconnectÚinit_tables)Úselfr	   © r   ú</var/www/www-root/data/www/avtoreputaciya.ru/vk_parser/db.pyÚ__init__   s   zDatabase.__init__c              
   C   sd   z| j r| j jrW dS tjdi | j¤Ž| _ t d¡ W dS  ty1 } z	t d|› �¡ ‚ d}~ww )u   ÐŸÐ¾Ð´ÐºÐ»ÑŽÑ‡ÐµÐ½Ð¸Ðµ Ðº Ð‘Ð”Nu9   âœ… ÐŸÐ¾Ð´ÐºÐ»ÑŽÑ‡ÐµÐ½Ð¸Ðµ Ðº Ð‘Ð” ÑƒÑ�Ñ‚Ð°Ð½Ð¾Ð²Ð»ÐµÐ½Ð¾u1   â�Œ ÐžÑˆÐ¸Ð±ÐºÐ° Ð¿Ð¾Ð´ÐºÐ»ÑŽÑ‡ÐµÐ½Ð¸Ñ� Ðº Ð‘Ð”: r   )	r
   ÚopenÚpymysqlr   r	   ÚloggerÚinfor   Úerror)r   Úer   r   r   r      s   €þzDatabase.connectc                 C   s@   z| j r| j js|  ¡  W dS W dS  ty   |  ¡  Y dS w )uJ   ÐŸÑ€Ð¾Ð²ÐµÑ€Ð¸Ñ‚ÑŒ Ð¸ Ð¿ÐµÑ€ÐµÐ¿Ð¾Ð´ÐºÐ»ÑŽÑ‡Ð¸Ñ‚ÑŒÑ�Ñ� ÐµÑ�Ð»Ð¸ Ð½ÑƒÐ¶Ð½Ð¾N)r
   r   r   r   ©r   r   r   r   Úensure_connection!   s   ÿÿzDatabase.ensure_connectionc                 C   sF   |   ¡  | j ¡ }| d¡ | d¡ | j ¡  | ¡  t d¡ dS )u   Ð¡Ð¾Ð·Ð´Ð°Ð½Ð¸Ðµ Ñ‚Ð°Ð±Ð»Ð¸Ñ†a(  
            CREATE TABLE IF NOT EXISTS groups_tracking (
                group_id VARCHAR(50) PRIMARY KEY,
                group_name VARCHAR(255),
                last_post_id VARCHAR(50),
                last_post_date DATETIME,
                last_parsed_at DATETIME,
                total_posts_parsed INT DEFAULT 0,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_last_post (last_post_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        a×  
            CREATE TABLE IF NOT EXISTS posts (
                id INT AUTO_INCREMENT PRIMARY KEY,
                group_id VARCHAR(50) NOT NULL,
                post_id VARCHAR(50) NOT NULL,
                post_url VARCHAR(255),
                author_id VARCHAR(50),
                author_name VARCHAR(255),
                text LONGTEXT,
                text_clean LONGTEXT,
                date DATETIME,
                likes INT DEFAULT 0,
                reposts INT DEFAULT 0,
                views INT DEFAULT 0,
                comments INT DEFAULT 0,
                attachments JSON,
                raw_html LONGTEXT,
                parsed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY unique_post (group_id, post_id),
                INDEX idx_group_date (group_id, date),
                INDEX idx_date (date),
                FULLTEXT INDEX idx_text (text)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        u3   âœ… Ð¢Ð°Ð±Ð»Ð¸Ñ†Ñ‹ Ð¸Ð½Ð¸Ñ†Ð¸Ð°Ð»Ð¸Ð·Ð¸Ñ€Ð¾Ð²Ð°Ð½Ñ‹N)r   r
   ÚcursorÚexecuteÚcommitÚcloser   r   )r   r   r   r   r   r   )   s   



zDatabase.init_tablesÚgroup_idÚreturnc                 C   s@   |   ¡  | j ¡ }| d|f¡ | ¡ }| ¡  |r|d S dS )uL   ÐŸÐ¾Ð»ÑƒÑ‡Ð¸Ñ‚ÑŒ ID Ð¿Ð¾Ñ�Ð»ÐµÐ´Ð½ÐµÐ³Ð¾ Ñ�Ð¾Ñ…Ñ€Ð°Ð½ÐµÐ½Ð½Ð¾Ð³Ð¾ Ð¿Ð¾Ñ�Ñ‚Ð°z<SELECT last_post_id FROM groups_tracking WHERE group_id = %sr   N©r   r
   r   r   Úfetchoner   ©r   r   r   Úresultr   r   r   Úget_last_post_idZ   ó   
þzDatabase.get_last_post_idé   Úlast_post_idÚlast_post_dateÚtotal_parsedc                 C   s>   |   ¡  | j ¡ }| d|||||f¡ | j ¡  | ¡  dS )u5   ÐžÐ±Ð½Ð¾Ð²Ð¸Ñ‚ÑŒ Ð¸Ð½Ñ„Ð¾Ñ€Ð¼Ð°Ñ†Ð¸ÑŽ Ð¾ Ð³Ñ€ÑƒÐ¿Ð¿Ðµa®  
            INSERT INTO groups_tracking 
            (group_id, last_post_id, last_post_date, last_parsed_at, total_posts_parsed)
            VALUES (%s, %s, %s, NOW(), %s)
            ON DUPLICATE KEY UPDATE 
                last_post_id = VALUES(last_post_id),
                last_post_date = VALUES(last_post_date),
                last_parsed_at = NOW(),
                total_posts_parsed = total_posts_parsed + %s
        N)r   r
   r   r   r   r   )r   r   r&   r'   r(   r   r   r   r   Úupdate_group_trackingf   s   
	÷

zDatabase.update_group_trackingÚ	post_datac                 C   s   |   ¡  | j ¡ }z�zU| d|| d¡| d¡| d¡| d¡| d¡| d¡| d¡| d	d
¡| dd
¡| dd
¡| dd
¡tj| dg ¡dd�| d¡f¡ | j ¡  |jd
kW W | 	¡  S  t
yŠ } zt d| d¡› d|› �¡ | j ¡  W Y d}~W | 	¡  dS d}~ww | 	¡  w )u'   Ð¡Ð¾Ñ…Ñ€Ð°Ð½Ð¸Ñ‚ÑŒ Ð¿Ð¾Ñ�Ñ‚ Ð² Ð±Ð°Ð·ÑƒaJ  
                INSERT IGNORE INTO posts (
                    group_id, post_id, post_url, author_id, author_name,
                    text, text_clean, date, likes, reposts, views, comments,
                    attachments, raw_html
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            Úpost_idÚpost_urlÚ	author_idÚauthor_nameÚtextÚ
text_cleanÚdateÚlikesr   ÚrepostsÚviewsÚcommentsÚattachmentsF)Úensure_asciiÚraw_htmlu-   ÐžÑˆÐ¸Ð±ÐºÐ° Ñ�Ð¾Ñ…Ñ€Ð°Ð½ÐµÐ½Ð¸Ñ� Ð¿Ð¾Ñ�Ñ‚Ð° z: N)r   r
   r   r   ÚgetÚjsonÚdumpsr   Úrowcountr   r   r   r   Úrollback)r   r   r*   r   r   r   r   r   Ú	save_postx   s>   




òú

û
€û
zDatabase.save_postc                 C   s@   |   ¡  | j ¡ }| d|f¡ | ¡ }| ¡  |r|d S dS )u]   ÐŸÐ¾Ð»ÑƒÑ‡Ð¸Ñ‚ÑŒ ÐºÐ¾Ð»Ð¸Ñ‡ÐµÑ�Ñ‚Ð²Ð¾ Ñ�Ð¾Ñ…Ñ€Ð°Ð½ÐµÐ½Ð½Ñ‹Ñ… Ð¿Ð¾Ñ�Ñ‚Ð¾Ð² Ð´Ð»Ñ� Ð³Ñ€ÑƒÐ¿Ð¿Ñ‹z.SELECT COUNT(*) FROM posts WHERE group_id = %sr   r   r!   r   r   r   Úget_posts_countŸ   r$   zDatabase.get_posts_countc                 C   s.   | j r| j jr| j  ¡  t d¡ dS dS dS )u#   Ð—Ð°ÐºÑ€Ñ‹Ñ‚ÑŒ Ñ�Ð¾ÐµÐ´Ð¸Ð½ÐµÐ½Ð¸Ðµu+   Ð¡Ð¾ÐµÐ´Ð¸Ð½ÐµÐ½Ð¸Ðµ Ñ� Ð‘Ð” Ð·Ð°ÐºÑ€Ñ‹Ñ‚Ð¾N)r
   r   r   r   r   r   r   r   r   r   «   s   
þzDatabase.closeN)r%   )Ú__name__Ú
__module__Ú__qualname__Údictr   r   r   r   Ústrr   r#   r   Úintr)   r   Úboolr>   r?   r   r   r   r   r   r      s    1ÿÿ
ÿ'r   )r   r   r   Útypingr   r   r   r:   ÚloggingÚbasicConfigÚINFOÚ	getLoggerr@   r   r   r   r   r   r   Ú<module>   s   
