
    i>Z              
          d dl mZ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mZmZ d dlmZmZmZmZ d dlmZ d dlmZmZ d dlZ ej2                  e      Z e       Zg d	Zg d
Zg dZd Z ejC                  d      dd ee      fde"dee"   dee"   defd       Z#ejC                  d      dd ee      fde"dee"   dee"   defd       Z$ejC                  d      dd ee      fdee"   dee"   defd       Z%ejC                  d       ed       ed       ee      fde"dee"   dee"   defd       Z&ejO                  d       ee      fde"dee(   de"defd       Z)y)    )	APIRouterDependsHTTPExceptionQuery)Session)functextand_)ListDictOptional)datetime	timedeltatimezonedate)get_db)	DeveloperActivityRecordN)
productiveserverDevelopmentIDECodeTerminalDocumentation)non-workentertainmentzsocial-mediagaming)zVisual Studio CodezIntelliJ IDEAPyCharmWebStormzAndroid StudiozSublime TextAtomEclipseNetBeansVimEmacsr   zCommand Prompt
PowerShellzGit BashChromeFirefoxEdgeSafariPostmanInsomniazDocker DesktopzMicrosoft TeamsSlackZoomzMicrosoft WordExcel
PowerPointzGoogle Docsc                 r    t        | dz        }t        | dz  dz        }t        | dz        }| d| d| dS )z1Convert seconds to hours, minutes, seconds format  <   zh zm s)int)secondshoursminutessecss       6E:\timesheet\timesheet_new\backend\productivity_api.pyformat_durationr;      sJ    4 E7T>b()Gw|DWBwir$q))    z0/api/developer/{developer_id}/productivity-hoursdeveloper_id
start_dateend_datedbc                 J  K   	 |r&t        j                  |j                  dd            }n0t        j                  t        j
                        t        d      z
  }|r&t        j                  |j                  dd            }n#t        j                  t        j
                        }|j                  t              j                  t        j                  | k(        j                         }|st        dd      |j                  t        d      | ||t        t               t        t"              t        t$              d	      j'                         }|j                  t        d
      | ||d      j'                         }|j                  t        d      | ||d      j'                         }	g }
d}d}|D ]}  }|\  }}}}}|dkD  r||z  dz  nd}|
j)                  |r|j+                         ndt-        t/        |      d      t-        t/        |      d      t-        |d      ||d       ||z  }||z  } |D cg c]&  \  }}t1        |      t-        t/        |      d      d( }}}|	D cg c]@  \  }}}}||xs dt-        t/        |      d      ||t         v xs |t"        v xr |t$        vdB }}}}}|dkD  r||z  dz  nd}|
r|t3        |
      z  nd}|j                  |j4                  d|j+                         |j+                         dt-        |d      t-        |d      t-        |d      t-        |d      t3        |
      d|
||dS c c}}w c c}}}}w # t        $ r  t6        $ r3}t8        j;                  d|        t        dt=        |            d}~ww xY ww)z:Calculate productivity hours for a developer from databaseZ+00:00   days  Developer not foundstatus_codedetaila  
            SELECT 
                DATE(timestamp) as work_date,
                SUM(duration) / 3600.0 as total_hours,
                SUM(CASE 
                    WHEN (category IN :productive_categories 
                        OR application_name IN :productive_apps)
                        AND category NOT IN :non_productive_categories
                        AND window_title NOT LIKE '%youtube%' 
                        AND window_title NOT LIKE '%gmail%'
                        AND window_title NOT LIKE '%mail.google%'
                    THEN duration 
                    ELSE 0 
                END) / 3600.0 as productive_hours,
                COUNT(DISTINCT application_name) as apps_used,
                COUNT(*) as total_activities
            FROM activity_records
            WHERE developer_id = :dev_id
            AND timestamp >= :start_date
            AND timestamp <= :end_date
            GROUP BY DATE(timestamp)
            ORDER BY work_date DESC
        )dev_idr>   r?   productive_categoriesproductive_appsnon_productive_categoriesa~  
            SELECT 
                EXTRACT(HOUR FROM timestamp) as hour_of_day,
                SUM(duration) / 3600.0 as total_hours
            FROM activity_records
            WHERE developer_id = :dev_id
            AND timestamp >= :start_date
            AND timestamp <= :end_date
            GROUP BY EXTRACT(HOUR FROM timestamp)
            ORDER BY hour_of_day
        rL   r>   r?   a  
            SELECT 
                application_name,
                category,
                SUM(duration) / 3600.0 as total_hours,
                COUNT(*) as usage_count
            FROM activity_records
            WHERE developer_id = :dev_id
            AND timestamp >= :start_date
            AND timestamp <= :end_date
            GROUP BY application_name, category
            ORDER BY total_hours DESC
            LIMIT 20
        r   d   N      )r   total_hoursproductive_hoursproductivity_percentage	apps_usedtotal_activities)hourr7   Other)applicationcategoryr7   usage_countis_productiveidnamestartend)total_work_hourstotal_productive_hoursrV   average_daily_hourstotal_days_worked)	developer
date_rangeoverall_statsdaily_productivityhourly_distributiontop_applicationsz&Error calculating productivity hours:   )r   fromisoformatreplacenowr   utcr   queryr   filterr=   firstr   executer	   tuplePRODUCTIVE_CATEGORIESPRODUCTIVE_APPSNON_PRODUCTIVE_CATEGORIESfetchallappend	isoformatroundfloatr5   lenra   	Exceptionloggererrorstr)r=   r>   r?   r@   rc   rd   ri   rl   rm   	app_usagedaily_statsre   rf   row	work_daterT   rU   rW   
activitiesrV   rY   r7   hourly_statsapp_namer\   count	app_statsoverall_productivityavg_daily_hourses                                 r:    get_developer_productivity_hoursr   $   s    Z<**:+=+=c8+LMELL.1BBE(()9)9#x)HIC,,x||,C HHY'..""l2

%' 	 C8MNN  ZZ . ). #%*+@%A$_5)./H)I
-: 8:; 	@ !jj 
/ 
* #
 8: 	" JJt %   #
" 8:# 	( !"%CNQKI{$4iP[^_P_'7+'E'Kef#1:	++-$U;%7;$)%0@*A1$E+01H!+L&$.   +"&66" &( 12 1kdE I5<+
 1 	 2 3<= 3<.h% $ +G5<+ &*??^8C^  Ndl  uN  eN
 3< 	 = UeghTh 69I IC OnoAL*S-==RS  ,,!
 *}}
 %**:A$>*/0F*J+01Eq+I',_a'@%(%5 #.#/ )%
 	
%2=B   <=aSABCF;;<sI   N#H3M 7+M"
M ,AM
1BM N#M N -.NN  N#z//api/developer/{developer_id}/project-breakdownc                   K   	 |r&t        j                  |j                  dd            }n0t        j                  t        j
                        t        d      z
  }|r&t        j                  |j                  dd            }n#t        j                  t        j
                        }|j                  t              j                  t        j                  | k(        j                         }|st        dd      |j                  t        d      | ||d	      j                         }|j                  t        d
      | ||d	      j                         }|j                  t        d      | ||d	      j                         }	g }
d}|D ]  }|\  }}}}}}}||z  }|
j!                  |t#        t%        |      d      ||||r|j'                         nd|r|j'                         nd|dkD  rt#        t%        |      |z  d      ndd        |
D ]!  }t#        |dkD  r|d   |z  dz  ndd      |d<   # i }|D ]M  \  }}}|r|j'                         nd}||vrg ||<   ||   j!                  |t#        t%        |      d      d       O i }|	D ]A  \  }}}}}||vrg ||<   ||   j!                  ||xs dt#        t%        |      d      |d       C |D ]  }||   dd ||<    |j                  |j(                  d|j'                         |j'                         dt#        |d      t+        |
      |
r|
d   d   ndd|
||dS # t        $ r  t,        $ r3}t.        j1                  d|        t        dt3        |            d}~ww xY ww) z8Get project-wise breakdown for a developer from databaserB   rC      rE   rG   rH   rI   a  
            SELECT 
                COALESCE(project_name, 'Unassigned') as project,
                SUM(duration) / 3600.0 as total_hours,
                COUNT(DISTINCT DATE(timestamp)) as days_worked,
                COUNT(DISTINCT application_name) as apps_used,
                COUNT(*) as activity_count,
                MIN(timestamp) as first_activity,
                MAX(timestamp) as last_activity
            FROM activity_records
            WHERE developer_id = :dev_id
            AND timestamp >= :start_date
            AND timestamp <= :end_date
            GROUP BY project_name
            ORDER BY total_hours DESC
        rP   a  
            SELECT 
                DATE(timestamp) as work_date,
                COALESCE(project_name, 'Unassigned') as project,
                SUM(duration) / 3600.0 as hours
            FROM activity_records
            WHERE developer_id = :dev_id
            AND timestamp >= :start_date
            AND timestamp <= :end_date
            GROUP BY DATE(timestamp), project_name
            ORDER BY work_date DESC, hours DESC
        a  
            SELECT 
                COALESCE(project_name, 'Unassigned') as project,
                application_name,
                category,
                SUM(duration) / 3600.0 as hours,
                COUNT(*) as count
            FROM activity_records
            WHERE developer_id = :dev_id
            AND timestamp >= :start_date
            AND timestamp <= :end_date
            GROUP BY project_name, application_name, category
            ORDER BY project_name, hours DESC
        r   rR   N)project_namerT   days_workedrW   activity_countfirst_activitylast_activityaverage_hours_per_dayrT   rQ   rS   
percentageUnknown)projectr7   rZ   )r[   r\   r7   r]   
   r_   rb   r   )rT   total_projectsmost_active_project)ri   rj   summaryprojectsdaily_distributionproject_applicationsz!Error getting project breakdown: ro   )r   rp   rq   rr   r   rs   r   rt   r   ru   r=   rv   r   rw   r	   r|   r}   r   r   r~   ra   r   r   r   r   r   )r=   r>   r?   r@   rc   rd   ri   project_statsdaily_project_hoursproject_activitiesr   total_hours_all_projectsr   r   r7   rF   appsr   r   r   r   r   r   date_strproject_appsappr\   r   r   s                                r:   get_developer_project_breakdownr      s    \<**:+=+=c8+LMELL.1CCE(()9)9#x)HIC,,x||,C HHY'..""l2

%' 	 C8MNN 

4 ) $  #
& 8:' 	, !jj / * #
 8: 	$  ZZ . ) #
" 8:# 	( #$  CY\VL%tZ$-$OO ,$U5\15#!",@N.":":"<TX>K!8!8!:QUJNQR(uU|d/BA)FXY	 		 !   G$)+a/ '*BBSH56%GL!    )<%Iw09y**,yH11/1"8,x(//"uU|Q/1 	 *= 4F0GS(E5l*(*W%!(("$/uU|Q/$	*  5G $G$0$9#2$>L! $
  ,,!
 *}}
  %%=qA"%h-FNx{>'BTX
 !"4$0!
 	
&   <8<=CF;;<s)   NL7L< ;N<N.M<<NNz(/api/all-developers/productivity-summaryc                   K   	 | r&t        j                  | j                  dd            }n6t        j                  t        j
                        j                  dddd      }|r&t        j                  |j                  dd            }n#t        j                  t        j
                        }|j                  t        d      ||t        t              t        t              t        t              d      j                         }g }|D ]  }|\  }}	}
}}}}|
dkD  r||
z  dz  nd}d}|rt|
dkD  rot        j                  t        j
                        |j                  t        j
                  	      z
  }|j                         d
k  rd}n|j                         dk  rd}|j                  ||	t        t!        |
      d      t        t!        |      d      t        |d      |||r|j#                         nd|d	        t%        d |D              }t%        d |D              }|dkD  r||z  dz  nd}t%        d |D              }|j#                         |j#                         dt'        |      |t        |d      t        |d      t        |d      |rt        |t'        |      z  d      ndd|dS # t(        $ r3}t*        j-                  d|        t/        dt1        |            d}~ww xY ww)z+Get productivity summary for all developersrB   rC   r   rY   minutesecondmicroseconda  
            SELECT 
                d.developer_id,
                d.name,
                COALESCE(SUM(ar.duration) / 3600.0, 0) as total_hours,
                COALESCE(SUM(CASE 
                    WHEN (ar.category IN :productive_categories 
                        OR ar.application_name IN :productive_apps)
                        AND ar.category NOT IN :non_productive_categories
                        AND ar.window_title NOT LIKE '%youtube%' 
                        AND ar.window_title NOT LIKE '%gmail%'
                        AND ar.window_title NOT LIKE '%mail.google%'
                    THEN ar.duration 
                    ELSE 0 
                END) / 3600.0, 0) as productive_hours,
                COUNT(DISTINCT ar.project_name) as projects_worked,
                COUNT(ar.id) as total_activities,
                MAX(ar.timestamp) as last_activity
            FROM developers d
            LEFT JOIN activity_records ar ON d.developer_id = ar.developer_id
                AND ar.timestamp >= :start_date
                AND ar.timestamp <= :end_date
            WHERE d.active = true
            GROUP BY d.developer_id, d.name
            ORDER BY total_hours DESC
        )r>   r?   rM   rN   rO   rQ   inactive)tzinfor2   activeiQ idlerR   rS   N)	r=   ra   rT   rU   rV   projects_countactivities_countr   statusc              3   &   K   | ]	  }|d      yw)rT   N .0ds     r:   	<genexpr>z:get_all_developers_productivity_summary.<locals>.<genexpr>  s     DAq/   c              3   &   K   | ]	  }|d      yw)rU   Nr   r   s     r:   r   z:get_all_developers_productivity_summary.<locals>.<genexpr>  s     #N:aA&8$9:r   c              3   2   K   | ]  }|d    dk(  sd  yw)r   r   rS   Nr   r   s     r:   r   z:get_all_developers_productivity_summary.<locals>.<genexpr>  s     Q:a89P:s   rb   )total_developersactive_developersteam_total_hoursteam_productive_hoursteam_productivity_percentageaverage_hours_per_developer)rj   team_summary
developersz3Error getting all developers productivity summary: ro   rI   )r   rp   rq   rr   r   rs   rw   r	   rx   ry   rz   r{   r|   total_secondsr}   r   r   r~   sumr   r   r   r   r   r   )r>   r?   r@   rc   rd   developer_statsr   r   rL   ra   rT   rU   r   r   r   rV   r   	time_diffr   r   team_productivityr   r   s                          r:   'get_all_developers_productivity_summaryr   p  s    b<**:+=+=c8+LMELL.66AaPQ_`6aE(()9)9#x)HIC,,x||,C **T + &4  %*+@%A$_5)./H)I
3> 8:? 	D 
"C_b\FD+'7:}P[^_P_'7+'E'Kef#  Fq$LL69N9NV^VbVb9N9cc	**,t3%F,,.6#F &$U;%7;$)%0@*A1$E+01H!+L"*$.>K!8!8!:QU 
 
 #6 DDD ##N:#N NP`cdPd25EEKjkQ:QQ *}}
 %(
O%6$)*:A$>)./Da)H056G0K_iu5EJ5WYZ/[op %
 	
   <J1#NOCF;;<s)   K+J'J, +K+,	K(5.K##K((K+z$/api/projects-summary/{developer_id}c           	      |  K   	 |r&t        j                  |j                  dd            }n6t        j                  t        j
                        j                  dddd      }|r&t        j                  |j                  dd            }n#t        j                  t        j
                        }t        d      }|j                  || ||d      j                         }g }d}	|D ]`  }
|
d   xs ddz  }|
d   |
d	   |
d
   |t        |      |
d   r|
d   j                  d      ng d}|j                  |       d|d   vs\|	|z  }	b |t        |      t        |	      |j                         |j                         ddS # t        $ r3}t        j!                  d|        t#        dt%        |            d}~ww xY ww)z+Get activities grouped by project name onlyrB   rC   r   r   au  
            SELECT 
                COALESCE(project_name, 'Uncategorized') as project,
                COUNT(DISTINCT file_path) as files_worked,
                COUNT(*) as total_activities,
                SUM(duration) as total_duration_ms,
                STRING_AGG(DISTINCT category, ', ') as categories
            FROM activity_records
            WHERE developer_id = :dev_id
            AND timestamp >= :start_date
            AND timestamp <= :end_date
            AND project_name IS NOT NULL
            AND project_name != ''
            GROUP BY project_name
            ORDER BY total_duration_ms DESC
        rP      i  rS   rR      z, )r   files_countr   total_time_secondstotal_time_formatted
categoriesr   r   rb   )r   r   total_productive_timerj   z Error getting projects summary: ro   rI   N)r   rp   rq   rr   r   rs   r	   rw   r|   r;   splitr}   r   r~   r   r   r   r   r   )r=   r>   r?   r@   rc   rd   rt   resultr   r   r   duration_secondsproject_datar   s                 r:   get_projects_summaryr     s    @<**:+=+=c8+LMELL.66AaPQ_`6aE(()9)9#x)HIC,,x||,C   " E"$
  8:	 	  !C #A!t3 !$A"1v$'F&6(78H(I47Fc!fll40L OOL) l!;;%)99%! & !!(m%45J%K$)OO$5cmmoN	
 	
  <7s;<CF;;<s/   F<D9E= >>E= <F<=	F9.F44F99F<z,/api/developer/{developer_id}/update-projectactivity_idsr   c                 Z  K   	 |j                  t              j                  t        j                  | k(        j	                         }|st        dd      |j                  t              j                  t        t        j                  | k(  t        j                  j                  |                  j                  d|id      }|j                          d|||dS # t        $ rC}|j                          t        j                  d	|        t        d
t!        |            d}~ww xY ww)z+Update project name for specific activitiesrG   rH   rI   r   F)synchronize_sessionT)successupdated_countr   r   zError updating project: ro   N)rt   r   ru   r=   rv   r   r   r
   r`   in_updatecommitr   rollbackr   r   r   )r=   r   r   r@   ri   updatedr   s          r:   update_activity_projectr   '  s     <HHY'..""l2

%' 	 C8MNN ((>*11++|;!!%%l3

 &\* %  
 	 			 $((	
 	
  <
/s34CF;;<s)   D+CC D+	D(%>D##D((D+)*fastapir   r   r   r   sqlalchemy.ormr   
sqlalchemyr   r	   r
   typingr   r   r   r   r   r   r   databaser   modelsr   r   logging	getLogger__name__r   routerry   r{   rz   r;   getr   r   r   r   r   postr5   r   r   r<   r:   <module>r      s(   < < " ' ' ' ' 8 8  , 			8	$	 l S * >? !%"&/	a<a<a< sma< 		a< @a<H => !%"&/	c<c<c< smc< 		c< ?c<L 67 $"&/h<h<smh< 	h< 8h<V 23 !&d#Dk&/	G<G<G< smG< 		G< 4G<T ;<
 &/	'<'<s)'< '< 		'< ='<r<   