U
    [Mh&                     @   s   d Z ddlZddlmZmZmZ ddlmZ ddlm	Z	 ddl
Z
ddlmZ ddlmZmZ e	  dd	 ZdedddZedkre  ed
 dS )zI
Database Inspector - Check database values and see where data is stored
    N)create_enginetextinspect)sessionmaker)load_dotenv)SessionLocal)datetimetimezonec               
   C   s  t  } zzDtd td td td tdd}td|  |  }td|jj  td	|jj  t  td
 td t	|}|
 }|D ]}||}| td|  }td|  td|  tdt|  |D ]X}|d rdnd}	|d r$d|d  nd}
td|d  d|d  d|	 |
  qt  qtd td | tj }|r|D ]}td|j  td|j  td|j  tdt|dr|jnd   | tjtjj|jk }td!|  t  qntd" td# td$ | tjtjj  d% }|r"t!|d&D ]\}}t| d'|j  td(|j  td)|j"  td*|j#d+d,  d- td.|j$  td/|j%d0d1 td2|j  td3|j&  td4|j'  td5|j(  t  q\ntd6 td7 td | tj }| tj }td8|  td9|  |d:krz| tjtjj) * }| tjtjj * }td;|j  td<|j  | tj+| j,-tjj% pd:}|d= }td>|d?d@ tdA | tdB. }|D ]<\}}}|rT|d= nd:}tdC| d| dD|d?dE q<t  tdF td tdG t  tdH tdI tdJ tdK t  tdL tdM tdN tdO tdP t  tdQ tdR tdS tdT t  tdU td tdV tdW tdX tdY t  W nB t/k
r } z"tdZ|  d:d+l0}|1  W 5 d+}~X Y nX W 5 |   X d+S )[z!Comprehensive database inspectionu   🗄️ DATABASE INSPECTIONz<============================================================u   📡 CONNECTION INFO:z------------------------------ZDATABASE_URLz	Not foundzDatabase URL: zDatabase Type: zDatabase Driver: u   📊 TABLES OVERVIEW:zSELECT COUNT(*) FROM zTable: z  Rows: z  Columns: nullableZNULLzNOT NULLdefaultz	 DEFAULT  u       • name: type u   👥 USERS TABLE:zID: z  Username: z	  Email: z  Created: 
created_atzN/Az  Total Activities: zNo users found!u(   📋 ACTIVITY RECORDS SAMPLE (Latest 5):z2--------------------------------------------------      z. ID: z   User ID: z   App: z
   Title: N2   z...z   Category: z   Duration: .1fsz   Timestamp: z   URL: z   File Path: z   Detailed Activity: zNo activity records found!u   📈 DATABASE STATISTICS:zTotal Users: zTotal Activity Records: r   zFirst Activity: zLast Activity:   zTotal Time Tracked: .2f hoursz
Categories:a  
                SELECT category, 
                       COUNT(*) as count,
                       SUM(duration) as total_duration
                FROM activity_records 
                GROUP BY category 
                ORDER BY total_duration DESC
                 • z
 records, hu   🔍 HOW TO QUERY YOUR DATA:zYou can query your data using:z1. Direct SQL queries:z   SELECT * FROM users;z4   SELECT * FROM activity_records WHERE user_id = 2;zJ   SELECT category, SUM(duration) FROM activity_records GROUP BY category;z"2. Python scripts (like this one):z$   from database import SessionLocalz   import modelsz   db = SessionLocal()z5   activities = db.query(models.ActivityRecord).all()z3. Database tools:z&   - DataGrip (you already have this!)z   - pgAdmin for PostgreSQLz   - DBeaver (free)u   📁 FILE LOCATIONS:z"Database Models: backend/models.pyz$Database Config: backend/database.pyz.Environment File: .env (contains DATABASE_URL)z CRUD Operations: backend/crud.py   ❌ Error: )2r   closeprintosgetenvZget_bindZdialectr   Zdriverr   Zget_table_namesZget_columnsZexecuter   ZscalarlenquerymodelsUserallidusernameemailhasattrr   ActivityRecordfilteruser_idcountorder_by	timestampdesclimit	enumerateapplication_namewindow_titlecategorydurationurl	file_pathZdetailed_activityZascfirstZwith_entitiesfuncsumZfetchall	Exception	traceback	print_exc)dbZdatabase_urlZengineZ	inspectorZtablesZ
table_namecolumnsZ	row_countcolr
   r   ZusersuserZactivity_countZrecent_activitiesiactivityZtotal_activitiesZtotal_usersZfirst_activityZlast_activityZtotal_durationZtotal_hoursZcategory_statscatr-   r6   hourser=    rH   ./inspect_database.pyinspect_database   s    
( 




	"
rJ      )r,   c              
   C   s  t  }zԐztd|   td |tjtjj| k }|sbtd|  d W W dS td|j	  td|j
  t  |tjtjj| ktjj d }td	 td
 t|dD ]\}}t|dd|jd  td|j  td|j  td|j  td|jdd |jrTtd|j  |jrltd|j  t  qttjjddddd}|jdddd}|tjtjj| ktjj|ktjj|k }|rtdd |D }	td td td t |  td!|	d" d#d$ i }
|D ]0}|j}||
krDd|
|< |
|  |j7  < q(td% t!|
" d&d' d(d)D ]*\}}|d" }td*| d+|d#d, qxW n2 t#k
r } ztd-|  W 5 d}~X Y nX W 5 |  X dS ).z&Show detailed data for a specific useru!   
👤 DETAILED DATA FOR USER ID: z2==================================================u   ❌ User with ID z not found!Nz
Username: zEmail: 
   u!   📋 RECENT ACTIVITIES (Last 10):z(----------------------------------------r   Z2dz. z%Y-%m-%d %H:%M:%Sz	    App: z    Title: z    Category: z    Duration: r   r   z	    URL: z
    File: r   )hourminutesecondZmicrosecond   ;   )rM   rN   rO   c                 s   s   | ]}|j V  qd S )N)r6   ).0arH   rH   rI   	<genexpr>   s     z*show_specific_user_data.<locals>.<genexpr>u   📅 TODAY'S SUMMARY:z--------------------zActivities: zTotal Time: r   r   r   zCategories:c                 S   s   | d S )Nr   rH   )xrH   rH   rI   <lambda>       z)show_specific_user_data.<locals>.<lambda>T)keyreverser   r   r   r   )$r   r   r   r"   r#   r$   r+   r&   r9   r'   r(   r*   r,   r.   r/   r0   r1   r%   r2   strftimer3   r4   r5   r6   r7   r8   r   Znowr	   Zutcreplacer;   r!   sorteditemsr<   )r,   r?   rB   Z
activitiesrC   rD   ZtodayZtomorrowZtoday_activitiesZ
total_time
categoriesrE   r6   rF   rG   rH   rH   rI   show_specific_user_data   sr    





 $r_   __main__)rK   )__doc__r   Z
sqlalchemyr   r   r   Zsqlalchemy.ormr   Zdotenvr   r#   Zdatabaser   r   r	   rJ   intr_   __name__rH   rH   rH   rI   <module>   s    1I