U
    [MhQ                     @   sB   e d dddeeeefee ee eej	e
dddZdS )z/top-window-titlesN2   )
start_dateend_datelimitcurrent_userdbc                 C   s  zjddl m} | r(t| dd}nttjjddddd}|rZt|dd}nttj}|d}|j}	|	|||||	d
 }
g }|
D ]}|jp|j}|rt| dkr| }n|j}|jp|j}t|dkr|d	d
 d }||j|jt|j|j|jt|jd dd|jr6|jdnd|j||dd q|t|| | ddW S  tk
r } z(td|  tddt| dW 5 d	}~X Y nX d	S )z/Get top window titles by duration from database    )textZz+00:00)ZhourZminutesecondZmicroseconda	  
            SELECT 
                ar.application_name,
                ar.window_title,
                ar.developer_id,
                ar.category,
                SUM(ar.duration) as total_duration,
                COUNT(*) as activity_count,
                MAX(ar.timestamp) as last_seen,
                -- Extract project information
                CASE 
                    WHEN ar.window_title ILIKE '%localhost:%' THEN 'Web Development'
                    WHEN ar.application_name ILIKE '%cursor%' OR ar.application_name ILIKE '%vscode%' THEN 'Development'
                    WHEN ar.application_name ILIKE '%chrome%' OR ar.application_name ILIKE '%firefox%' THEN 'Browser'
                    WHEN ar.application_name ILIKE '%filezilla%' OR ar.application_name ILIKE '%winscp%' THEN 'Server Management'
                    WHEN ar.application_name ILIKE '%datagrip%' OR ar.application_name ILIKE '%pgadmin%' THEN 'Database'
                    ELSE 'General'
                END as project_type,
                -- Extract project name
                CASE 
                    WHEN ar.window_title ILIKE '%localhost:%' THEN 
                        SUBSTRING(ar.window_title FROM 'localhost:([0-9]+)')
                    WHEN ar.application_name ILIKE '%cursor%' OR ar.application_name ILIKE '%vscode%' THEN
                        SPLIT_PART(SPLIT_PART(ar.window_title, ' - ', 2), ' - ', 1)
                    WHEN ar.application_name ILIKE '%chrome%' OR ar.application_name ILIKE '%firefox%' THEN
                        SPLIT_PART(ar.window_title, ' - ', 1)
                    ELSE ar.application_name
                END as project_name,
                -- Extract file name
                CASE 
                    WHEN ar.window_title LIKE '%.%' THEN 
                        SPLIT_PART(ar.window_title, ' - ', 1)
                    ELSE ar.window_title
                END as file_name
            FROM activity_records ar
            WHERE ar.timestamp >= :start_date 
            AND ar.timestamp <= :end_date
            AND ar.duration >= 5  -- Filter out very short activities
            AND ar.window_title IS NOT NULL
            AND ar.window_title != ''
            AND ar.developer_id = :developer_id  -- Filter by current user's developer ID
            GROUP BY ar.application_name, ar.window_title, ar.developer_id, ar.category
            ORDER BY total_duration DESC
            LIMIT :limit
        )r   r   r   developer_idd   Na   z...i  z.2fhz%Y-%m-%d %H:%M:%S )project_typeproject_name	file_name)application_namewindow_titletotal_durationactivity_count	last_seenZduration_formattedZlast_seen_formattedZproject_info)startend)Ztop_window_titlesZtotal_titlesZ
date_rangezError in top-window-titles: i  z"Error fetching top window titles: )Zstatus_codeZdetail)Z
sqlalchemyr   ZdatetimeZfromisoformatreplaceZnowtimezoneZutcZusernameZexecuteZfetchallr   r   lenstripr   r   appendfloatr   r   r   strftimer   Z	isoformat	ExceptionprintZHTTPExceptionstr)r   r   r   r   r   r   r   r   Zqueryr   resultZ
top_titlesrowr   r   e r'   ./fixed_top_window_titles.pyget_top_window_titles   sZ    	0

r)   )ZappgetZDependsZget_current_userZget_dbZOptionalr#   intZmodelsZUserZSessionr)   r'   r'   r'   r(   <module>   s   