o
    RiE                     @   sB   d dl Z d dlZd dlmZmZmZ d dlmZ G dd dZdS )    N)datetimetimezone	timedelta)CONFIGc                   @   sR   e Zd Zdd Zdd Zdd Zddd	ZdddZdd ZdddZ	dd Z
dS )DBLoggerc                 C   s    t d | _t | _|   d S )Ndb_path)r   r   	threadingLock_lock_init_dbself r   $/var/www/html/bot-teams/db_logger.py__init__   s   

zDBLogger.__init__c                 C   sX   t | j}|d |d |d |  W d    d S 1 s%w   Y  d S )Nad  CREATE TABLE IF NOT EXISTS request_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL,
                agent_name TEXT NOT NULL, agent_id TEXT, client_name TEXT, cid TEXT,
                email TEXT, amount INTEGER NOT NULL, products TEXT, provider TEXT NOT NULL,
                link_type TEXT NOT NULL, url TEXT NOT NULL)z@CREATE INDEX IF NOT EXISTS idx_ts ON request_log(timestamp DESC)z6CREATE INDEX IF NOT EXISTS idx_cid ON request_log(cid))sqlite3connectr   executecommitr   connr   r   r   r      s   



"zDBLogger._init_dbc                 K   s   | j V t| j7}|dttj	 |d |d |d |d |d |d |d |d	 |d
 |d f |
  W d    n1 sDw   Y  W d    d S W d    d S 1 s\w   Y  d S )NzINSERT INTO request_log
                    (timestamp,agent_name,agent_id,client_name,cid,email,amount,products,provider,link_type,url)
                    VALUES (?,?,?,?,?,?,?,?,?,?,?)
agent_nameagent_idclient_namecidemailamountproductsprovider	link_typeurl)r
   r   r   r   r   r   nowr   utc	isoformatr   )r   kwr   r   r   r   log_request   s   
"zDBLogger.log_request   c                 C   s   t tjt|d  }t| j@}tj	|_
|d||f }|rKt |d }tt tj|  d d}|d |d |dW  d    S W d    d S 1 sVw   Y  d S )	NhourszNSELECT * FROM request_log WHERE cid=? AND timestamp>? ORDER BY id DESC LIMIT 1	timestampi     r   r   )r   r   	hours_ago)r   r!   r   r"   r   r#   r   r   r   Rowrow_factoryr   fetchonefromisoformatroundtotal_seconds)r   r   r(   cutoffr   rowtsr+   r   r   r   check_duplicate_cid!   s&   
		zDBLogger.check_duplicate_ciddayc                 C   s"  t tdd}t|}|dkr#|jddddd}d|d d}nN|d	krG|t| d
 jddddd}d|d d|d d}n*|dkr_|jdddddd}d|d d}n|jddddd}d|d d}|t j	 }t
| j}t
j|_|d|f }t|}	tdd |D }
ttdd |D }i }i }i }g }|D ]@}||d dd ||d < ||d dd ||d < ||d d|d  ||d < ||d |d |d |d d q||d|	|
|||||d	W  d    S 1 s
w   Y  d S )N   r'   r6   r   )hourminutesecondmicrosecondzToday (z%Y-%m-%d)week)dayszThis Week (z%m/%dz - monthr*   )r6   r8   r9   r:   r;   zThis Month (z%B %Yz,SELECT * FROM request_log WHERE timestamp>=?c                 s   s    | ]}|d  V  qdS )r   Nr   .0rr   r   r   	<genexpr>C   s    z&DBLogger.get_report.<locals>.<genexpr>c                 s   s     | ]}|d  r|d  V  qdS )r   Nr   r@   r   r   r   rC   D   s    r   r   r   r   r   r   )namer   r   r   )	labeldatetotaltotal_amountunique_clientsby_providerby_agentby_agent_amountclients)r   r   r   r!   replacestrftimeweekday
astimezoner"   r#   r   r   r   r,   r-   r   fetchalllensumsetgetappend)r   periodeetr!   startrE   	start_utcr   rowsrG   rH   rI   rJ   rK   rL   rM   rB   r   r   r   
get_report.   sP   
   &&zDBLogger.get_reportc                 C   s
   |  dS )Nr6   )r]   r   r   r   r   get_daily_reportZ   s   
zDBLogger.get_daily_report2   c                 C   sT   t | j}t j|_dd |d|f D W  d    S 1 s#w   Y  d S )Nc                 S   s   g | ]}t |qS r   )dictr@   r   r   r   
<listcomp>`   s    z'DBLogger.get_recent.<locals>.<listcomp>z2SELECT * FROM request_log ORDER BY id DESC LIMIT ?)r   r   r   r,   r-   r   rR   )r   limitr   r   r   r   
get_recent]   s   $zDBLogger.get_recentc                 C   sB   t | j}|d d W  d    S 1 sw   Y  d S )Nz SELECT COUNT(*) FROM request_logr   )r   r   r   r   r.   r   r   r   r   get_total_countb   s   $zDBLogger.get_total_countN)r&   )r6   )r_   )__name__
__module____qualname__r   r   r%   r5   r]   r^   rc   rd   r   r   r   r   r      s    

,
r   )r   r   r   r   r   configr   r   r   r   r   r   <module>   s    