U
    ci_!                     @   s   d dl mZmZmZ d dlmZmZ d dlmZm	Z	m
Z
 d dlmZ d dlmZmZmZ d dlmZ d dlmZmZ d dlZeeZe Zed	eefed
ddZeddeefeeedddZedeefed
ddZdS )    )	APIRouterDependsHTTPException)Session
joinedload)funcStringcast)List)datetimetimezone	timedelta)get_db)	DeveloperActivityRecordNz/api/developers-orm)dbc                    s  zDddl m} |d}| |}| }g }|D ]}|\	}}}	}
}}}}}d}d}|r|}t|drttj|j	tjd }nttj| }|
 dk rd	}n|
 d
k rd}||pd| |	|	dd|dd| d|pd| ||r| n|r| nddd|
|r| nd|d q2|dt|ttj ddW S  tk
r } z$td|  tdt|dW 5 d}~X Y nX dS )z:Get all developers using SQLAlchemy ORM with relationshipsr   texta  
            SELECT 
                d.id,
                d.developer_id,
                d.name,
                d.email,
                d.active,
                d.created_at,
                d.last_sync,
                COALESCE(ac.activity_count, 0) as activity_count,
                ac.last_activity
            FROM developers d
            LEFT JOIN (
                SELECT 
                    developer_id::VARCHAR as developer_id,
                    COUNT(*) as activity_count,
                    MAX(timestamp) as last_activity
                FROM activity_records
                WHERE developer_id IS NOT NULL
                GROUP BY developer_id
            ) ac ON d.developer_id::VARCHAR = ac.developer_id::VARCHAR
            WHERE d.active = true
        offlineNreplaceZtzinfo  onlineQ idleZdev_unknowni  databasezDeveloper with z activitieszN/A)idnameZhostnameZhostZportstatussourcedescriptionZ	device_idactivity_count	last_seenversionZbucket_countemail
created_atactiveZ
productionZorm_relationships)
developersZenvironmenttotal_countZdiscovered_atmethodz"Error getting developers via ORM:   Zstatus_codeZdetail)
sqlalchemyr   executefetchallhasattrr   nowr   utcr   total_secondsappend	isoformatlen	Exceptionloggererrorr   str)r   r   queryresultdevelopers_with_statsdeveloper_listrowZdev_iddeveloper_idr   r%   r'   r&   Z	last_syncr"   last_activityr   r#   	time_diffe rD   ./developers_orm_api.pyget_developers_using_orm   sh    
   


rF   z(/api/developer/{developer_id}/activitiesd   )r@   limitr   c           	         sN  z| ttj| k }|s,tddd| ttjdj	ttjtjkddtj| k
tj | }g }|D ]P\}}||j|j||j|j|jpd|j|jr|j nd|j|j|jd	 q~|j|j|jd
|t|dW S  tk
r    Y nD tk
rH } z$td|  tdt|dW 5 d}~X Y nX dS )z1Get activities for a specific developer using ORMi  zDeveloper not foundr,   developer_nameT)ZisouterZOtherN)r   r@   rI   application_namewindow_titlecategoryduration	timestampurl	file_pathproject_name)r   r   r%   )	developer
activitiesZtotal_activitiesz$Error getting developer activities: r+   )r;   r   filterr@   firstr   r   r   ZlabeljoinZorder_byrN   ZdescrH   allr4   r   rJ   rK   rL   rM   r5   rO   rP   rQ   r%   r6   r7   r8   r9   r:   )	r@   rH   r   rR   rS   Zactivities_listZactivityrI   rC   rD   rD   rE   get_developer_activitiesi   s^    





rX   z/api/developers-with-statsc                    s:  zddl m} |d}| |}| }g }|D ]}|\}}}	}
}}}d}|rttj|jtjd }|	 dk rzd}n|	 dk rd	}|
|||	||pdt|pdtt|pdd
 d|r| nd|
r|
 ndd	 q0|t|ddW S  tk
r4 } z$td|  tdt|dW 5 d}~X Y nX dS )z3Get developers with aggregated statistics using ORMr   r   a  
            SELECT 
                d.developer_id,
                d.name,
                d.email,
                d.created_at,
                COUNT(ar.id) as activity_count,
                MAX(ar.timestamp) as last_activity,
                COALESCE(SUM(ar.duration), 0) as total_duration
            FROM developers d
            LEFT JOIN activity_records ar ON d.developer_id::VARCHAR = ar.developer_id::VARCHAR
            WHERE d.active = true
            GROUP BY d.developer_id, d.name, d.email, d.created_at
        r   r   r   r   r   r   i     N)	r   r   r%   r   r"   Ztotal_duration_secondsZtotal_duration_hoursrA   r&   Zorm_with_aggregation)r(   r)   r*   z%Error getting developers with stats: r+   r,   )r-   r   r.   r/   r   r1   r   r2   r   r3   r4   floatroundr5   r6   r7   r8   r9   r   r:   )r   r   r;   r<   r=   r>   r?   r@   r   r%   r&   r"   rA   Ztotal_durationr   rB   rC   rD   rD   rE   get_developers_with_stats   sJ    

  

r\   ) Zfastapir   r   r   Zsqlalchemy.ormr   r   r-   r   r   r	   typingr
   r   r   r   r   r   Zmodelsr   r   ZloggingZ	getLogger__name__r8   ZroutergetrF   r:   intrX   r\   rD   rD   rD   rE   <module>   s*   
Z>