
    JiV              
          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d ZejA                  d      dd ee      fde!dee!   dee!   defd       Z"ejA                  d      dd ee      fde!dee!   dee!   defd       Z#ejA                  d      dd ee      fdee!   dee!   defd       Z$ejA                  d       ed       ed       ee      fde!dee!   dee!   defd       Z%ejM                  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)DevelopmentIDECodeTerminalDocumentation)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_durationr6      sJ    4 E7T>b()Gw|DWBwir$q))    z0/api/developer/{developer_id}/productivity-hoursdeveloper_id
start_dateend_datedbc                   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"              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]&  \  }}t/        |      t+        t-        |      d      d( }}}|	D cg c]6  \  }}}}||xs dt+        t-        |      d      ||t         v xs |t"        v d8 }}}}}|dkD  r||z  dz  nd}|
r|t1        |
      z  nd}|j                  |j2                  d|j)                         |j)                         dt+        |d      t+        |d      t+        |d      t+        |d      t1        |
      d|
||dS c c}}w c c}}}}w # t        $ r  t4        $ r3}t6        j9                  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 
                    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_idr9   r:   productive_categoriesproductive_appsa~  
            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
        rG   r9   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)hourr2   Other)applicationcategoryr2   usage_countis_productiveidnamestartend)total_work_hourstotal_productive_hoursrP   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   filterr8   firstr   executer	   tuplePRODUCTIVE_CATEGORIESPRODUCTIVE_APPSfetchallappend	isoformatroundfloatr0   lenr[   	Exceptionloggererrorstr)r8   r9   r:   r;   r]   r^   rc   rf   rg   	app_usagedaily_statsr_   r`   row	work_daterN   rO   rQ   
activitiesrP   rS   r2   hourly_statsapp_namerV   count	app_statsoverall_productivityavg_daily_hourses                                 r5    get_developer_productivity_hoursr   !   s    U<**:+=+=c8+LMELL.1BBE(()9)9#x)HIC,,x||,C HHY'..""l2

%' 	 C8MNN  ZZ . )& #%*+@%A$_5
%0 8:1 	6 !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<+ %)>>](oB]
 3< 	 = UeghTh 69I IC OnoAL*S-==RS  ,,!
 *}}
 %**:A$>*/0F*J+01Eq+I',_a'@%(%5 #.#/ )%
 	
%2=B   <=aSABCF;;<sH   NH%M )+L5
M ;L;
BM 4N5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 databaser=   r>      r@   rB   rC   rD   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
        rJ   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   rL   N)project_namerN   days_workedrQ   activity_countfirst_activitylast_activityaverage_hours_per_dayrN   rK   rM   
percentageUnknown)projectr2   rT   )rU   rV   r2   rW   
   rY   r\   r   )rN   total_projectsmost_active_project)rc   rd   summaryprojectsdaily_distributionproject_applicationsz!Error getting project breakdown: ri   )r   rj   rk   rl   r   rm   r   rn   r   ro   r8   rp   r   rq   r	   ru   rv   rx   ry   rw   r[   rz   r{   r|   r}   r~   )r8   r9   r:   r;   r]   r^   rc   project_statsdaily_project_hoursproject_activitiesr   total_hours_all_projectsr   r   r2   rA   appsr   r   r   r   r   r   date_strproject_appsapprV   r   r   s                                r5   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              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t/        |            d}~ww xY ww)z+Get productivity summary for all developersr=   r>   r   rS   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 
                    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
        )r9   r:   rH   rI   rK   inactive)tzinfor-   activeiQ idlerL   rM   N)	r8   r[   rN   rO   rP   projects_countactivities_countr   statusc              3   &   K   | ]	  }|d      yw)rN   N .0ds     r5   	<genexpr>z:get_all_developers_productivity_summary.<locals>.<genexpr>  s     DAq/   c              3   &   K   | ]	  }|d      yw)rO   Nr   r   s     r5   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   rM   Nr   r   s     r5   r   z:get_all_developers_productivity_summary.<locals>.<genexpr>  s     Q:a89P:s   r\   )total_developersactive_developersteam_total_hoursteam_productive_hoursteam_productivity_percentageaverage_hours_per_developer)rd   team_summary
developersz3Error getting all developers productivity summary: ri   rD   )r   rj   rk   rl   r   rm   rq   r	   rr   rs   rt   ru   total_secondsrv   rx   ry   rw   sumrz   r{   r|   r}   r   r~   )r9   r:   r;   r]   r^   developer_statsr   r   rG   r[   rN   rO   r   r   r   rP   r   	time_diffr   r   team_productivityr   r   s                          r5   'get_all_developers_productivity_summaryr   h  s    ]<**:+=+=c8+LMELL.66AaPQ_`6aE(()9)9#x)HIC,,x||,C **T + &,  %*+@%A$_5	
+4 8:5 	: 
"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'.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 onlyr=   r>   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
        rJ      i  rM   rL      z, )r   files_countr   total_time_secondstotal_time_formatted
categoriesznon-workr   r\   )r   r   total_productive_timerd   z Error getting projects summary: ri   rD   N)r   rj   rk   rl   r   rm   r	   rq   ru   r6   splitrv   rz   rw   r{   r|   r}   r   r~   )r8   r9   r:   r;   r]   r^   rn   resultr   r   r   duration_secondsproject_datar   s                 r5   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 activitiesrB   rC   rD   r   F)synchronize_sessionT)successupdated_countr   r   zError updating project: ri   N)rn   r   ro   r8   rp   r   r   r
   rZ   in_updatecommitr{   rollbackr|   r}   r~   )r8   r   r   r;   rc   updatedr   s          r5   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|   routerrs   rt   r6   getr~   r   r   r   r   postr0   r   r   r7   r5   <module>r      s"   < < " ' ' ' ' 8 8  , 			8	$	 T * >? !%"&/	\<\<\< sm\< 		\< @\<~ => !%"&/	c<c<c< smc< 		c< ?c<L 67 $"&/c<c<smc< 	c< 8c<L 23 !&d#Dk&/	G<G<G< smG< 		G< 4G<T ;<
 &/	'<'<s)'< '< 		'< ='<r7   