U
    [Mh!                     @   s   d Z ddlmZ ddlZddlmZmZ ddlmZmZm	Z	 dd Z
ded	d
dZedkre
  ed ed ed ed dS )z:
Simple Database Query Tool - Easy way to check your data
    )SessionLocalN)textfunc)datetimetimezone	timedeltac               
   C   s&  t  } zztd td td td | tj }| tj }td|  td|  |dkr| t	tjj
 pd}|d }td	|d
d | tjtjj  }| tjtjj  }td|j   td|j   t  td td ttjjddddd}|tdd }| tjtjj|ktjj|k  }	|	r6t	dd |	D d }
tdt|	  td|
d
d i }|	D ]4}|j}||krd||< ||  |j
d 7  < qtd t| dd ddD ]"\}}td| d|d
d qntd t  td td ttjtd d }| tjtjj|k }|rt	d!d |D d }td"t|  td#|d
d td$|d  d
d t  td% td& | td'  }t!|dD ]F\}\}}}|r,|d nd}t|d(d)| d|d
d*| d+ qt  td, td& | tjtjjd-ktjj"#d.tjj $d/ }|rLt!|dD ]\}}ttj|jjtjd0 }|j%dkr|j% d1}n.|j&dkr|j&d  d2}n|j&d3  d4}t|d(d)|j" d5| d6 qntd7 t  td8 td9 td: td; td< td= t  td> td? td@ tdA t  tdB tdC tdD W nB t'k
r } z"tdE|  dd.l(}|)  W 5 d.}~X Y nX W 5 |   X d.S )Fz!Simple queries to check your datau   🔍 SIMPLE DATABASE QUERIESz2==================================================u   📊 QUICK STATS:z--------------------zUsers: zTotal Activities: r     zTotal Hours Tracked: z.2fhzFirst Activity: zLast Activity: u   📅 TODAY'S ACTIVITIES:z-------------------------hourZminutesecondZmicrosecond   daysc                 s   s   | ]}|j V  qd S Nduration.0a r   ./query_database.py	<genexpr>7   s     z!query_database.<locals>.<genexpr>zActivities Today: zHours Today: zCategories Today:c                 S   s   | d S )Nr   r   )xr   r   r   <lambda>D       z query_database.<locals>.<lambda>T)keyreverseu     • z: zNo activities today yet.u   📈 LAST 7 DAYS:   c                 s   s   | ]}|j V  qd S r   r   r   r   r   r   r   V   s     zActivities This Week: zHours This Week: zAverage Per Day: u!   🏆 TOP APPLICATIONS (All Time):#-----------------------------------a  
            SELECT application_name, 
                   COUNT(*) as activity_count,
                   SUM(duration) as total_duration
            FROM activity_records 
            GROUP BY application_name 
            ORDER BY total_duration DESC 
            LIMIT 10
        2dz. zh (z activities)u    📁 RECENT FILES (Development):ZdevelopmentN
   )Ztzinfozd agozh ago<   zm agoz ()z!No development files tracked yet.u(   🛠️ HOW TO ACCESS DATABASE DIRECTLY:z(----------------------------------------z+1. Using DataGrip (you already have this!):zH   - Connect to: postgresql://postgres:asdf1234@localhost:5432/timesheetz!   - Main table: activity_recordsz   - Users table: usersz2. Using psql command line:z-   psql -h localhost -U postgres -d timesheetz   \dt  (list tables)z+   SELECT * FROM activity_records LIMIT 10;z3. Using this Python script:z   python inspect_database.pyz   python query_database.py   ❌ Error: )*r   closeprintquerymodelsZUsercountActivityRecordr   sumr   Zscalarorder_by	timestampascfirstZdescdater   nowr   utcreplacer   filteralllencategorysorteditemsexecuter   fetchall	enumerate	file_pathZisnotlimitr   Zseconds	Exception	traceback	print_exc)dbZtotal_usersZtotal_activitiesZtotal_durationZtotal_hoursZfirst_activityZlast_activitytodaytomorrowZtoday_activitiesZtotal_today
categoriesactivitycathoursZweek_agoZweek_activitiesZ
total_weekZ	app_statsiZappr)   r   Zrecent_filesZtime_agoZtime_strer@   r   r   r   query_database
   s    





(

$rK   rC   )
query_typec              
   C   s
  t  }zz| dkrRtd td ttjjddddd}|tdd }i }|	t
jt
jj|kt
jj|k t
jj  }|D ]d}|jj}||krddt d||< || d	  |j7  < || d
  d7  < || d |j qt| D ]N}|| }|d	 d }	d|d }
t|dd|	dd|d
  d|
  q np| dkrtd td |td }|D ]>\}}}}|d }t|dd|dd|dd|   qW n2 tk
r } ztd|  W 5 d}~X Y nX W 5 |  X dS ) zRun custom queriesrC   u    📅 TODAY'S DETAILED BREAKDOWN:r   r   r
   r   r   )r   
activitiesrE   r   rM   rE   r"   z, r    z:00 - z5.1fzmin (z activities) - filesu   📁 FILES YOU'VE WORKED ON:z------------------------------a  
                SELECT file_path, 
                       COUNT(*) as sessions,
                       SUM(duration) as total_time,
                       MAX(timestamp) as last_worked
                FROM activity_records 
                WHERE file_path IS NOT NULL 
                  AND category = 'development'
                GROUP BY file_path 
                ORDER BY total_time DESC 
                LIMIT 15
            r   Z40z | z5.2fzh | Z3dz sessions | r$   N)r   r%   r&   r   r1   r   r2   r3   r   r'   r(   r*   r4   r-   r,   r.   r5   r   setr   addr7   r8   keysjoinr:   r   r;   r0   r?   )rL   rB   rC   rD   Zhourly_datarM   rF   r   dataZduration_minrE   Z
file_statsr=   ZsessionsZ
total_timeZlast_workedrH   rJ   r   r   r   custom_query   sH    



,
0$rT   __main__z3
==================================================rN   )rC   )__doc__Zdatabaser   r(   Z
sqlalchemyr   r   r   r   r   rK   strrT   __name__r&   r   r   r   r   <module>   s    =