@@ -3510,90 +3510,61 @@ func (q *FakeQuerier) GetTemplateInsightsByTemplate(_ context.Context, arg datab
35103510q .mutex .RLock ()
35113511defer q .mutex .RUnlock ()
35123512
3513- // map time.Time x TemplateID x UserID x <usage>
3514- appUsageByTemplateAndUser := map [time.Time ]map [uuid.UUID ]map [uuid.UUID ]database.GetTemplateInsightsByTemplateRow {}
3515-
3516- // Review agent stats in terms of usage
3517- templateIDSet := make (map [uuid.UUID ]struct {})
3513+ /*
3514+ SELECT
3515+ template_id,
3516+ COUNT(DISTINCT user_id) AS active_users,
3517+ (SUM(usage_mins) * 60)::bigint AS usage_total_seconds, -- Includes app usage.
3518+ (SUM(ssh_mins) * 60)::bigint AS usage_ssh_seconds,
3519+ (SUM(sftp_mins) * 60)::bigint AS usage_sftp_seconds,
3520+ (SUM(reconnecting_pty_mins) * 60)::bigint AS usage_reconnecting_pty_seconds,
3521+ (SUM(vscode_mins) * 60)::bigint AS usage_vscode_seconds,
3522+ (SUM(jetbrains_mins) * 60)::bigint AS usage_jetbrains_seconds
3523+ FROM
3524+ template_usage_stats
3525+ WHERE
3526+ start_time >= @start_time::timestamptz
3527+ AND end_time <= @end_time::timestamptz
3528+ GROUP BY template_id;
3529+ */
35183530
3519- for _ ,s := range q .workspaceAgentStats {
3520- if s .CreatedAt .Before (arg .StartTime )|| s .CreatedAt .Equal (arg .EndTime )|| s .CreatedAt .After (arg .EndTime ) {
3521- continue
3522- }
3523- if s .ConnectionCount == 0 {
3531+ type grouped struct {
3532+ database.GetTemplateInsightsByTemplateRow
3533+ activeUserIDs map [uuid.UUID ]struct {}
3534+ }
3535+ groupedByTemplateID := make (map [uuid.UUID ]grouped )
3536+ for _ ,tus := range q .templateUsageStats {
3537+ if tus .StartTime .Before (arg .StartTime )|| tus .EndTime .After (arg .EndTime ) {
35243538continue
35253539}
3526-
3527- t := s .CreatedAt .Truncate (time .Minute )
3528- templateIDSet [s .TemplateID ]= struct {}{}
3529-
3530- if _ ,ok := appUsageByTemplateAndUser [t ];! ok {
3531- appUsageByTemplateAndUser [t ]= make (map [uuid.UUID ]map [uuid.UUID ]database.GetTemplateInsightsByTemplateRow )
3532- }
3533-
3534- if _ ,ok := appUsageByTemplateAndUser [t ][s.TemplateID ];! ok {
3535- appUsageByTemplateAndUser [t ][s.TemplateID ]= make (map [uuid.UUID ]database.GetTemplateInsightsByTemplateRow )
3536- }
3537-
3538- if _ ,ok := appUsageByTemplateAndUser [t ][s.TemplateID ][s .UserID ];! ok {
3539- appUsageByTemplateAndUser [t ][s.TemplateID ][s .UserID ]= database.GetTemplateInsightsByTemplateRow {}
3540- }
3541-
3542- u := appUsageByTemplateAndUser [t ][s.TemplateID ][s .UserID ]
3543- if s .SessionCountJetBrains > 0 {
3544- u .UsageJetbrainsSeconds = 60
3545- }
3546- if s .SessionCountVSCode > 0 {
3547- u .UsageVscodeSeconds = 60
3548- }
3549- if s .SessionCountReconnectingPTY > 0 {
3550- u .UsageReconnectingPtySeconds = 60
3551- }
3552- if s .SessionCountSSH > 0 {
3553- u .UsageSshSeconds = 60
3540+ row ,ok := groupedByTemplateID [tus .TemplateID ]
3541+ if ! ok {
3542+ row = grouped {
3543+ GetTemplateInsightsByTemplateRow : database.GetTemplateInsightsByTemplateRow {
3544+ TemplateID :tus .TemplateID ,
3545+ },
3546+ activeUserIDs :make (map [uuid.UUID ]struct {}),
3547+ }
35543548}
3555- appUsageByTemplateAndUser [t ][s.TemplateID ][s .UserID ]= u
3549+ row .activeUserIDs [tus .UserID ]= struct {}{}
3550+ row .ActiveUsers = int64 (len (row .activeUserIDs ))
3551+ row .UsageTotalSeconds += int64 (tus .UsageMins )* 60
3552+ row .UsageSshSeconds += int64 (tus .SshMins )* 60
3553+ row .UsageSftpSeconds += int64 (tus .SftpMins )* 60
3554+ row .UsageReconnectingPtySeconds += int64 (tus .ReconnectingPtyMins )* 60
3555+ row .UsageVscodeSeconds += int64 (tus .VscodeMins )* 60
3556+ row .UsageJetbrainsSeconds += int64 (tus .JetbrainsMins )* 60
3557+ groupedByTemplateID [tus .TemplateID ]= row
35563558}
35573559
3558- // Sort used templates
3559- templateIDs := make ([]uuid.UUID ,0 ,len (templateIDSet ))
3560- for templateID := range templateIDSet {
3561- templateIDs = append (templateIDs ,templateID )
3560+ var rows []database.GetTemplateInsightsByTemplateRow
3561+ for _ ,row := range groupedByTemplateID {
3562+ rows = append (rows ,row .GetTemplateInsightsByTemplateRow )
35623563}
3563- slices .SortFunc (templateIDs ,func (a ,b uuid. UUID )int {
3564- return slice .Ascending (a .String (),b .String ())
3564+ slices .SortFunc (rows ,func (a ,b database. GetTemplateInsightsByTemplateRow )int {
3565+ return slice .Ascending (a .TemplateID . String (),b . TemplateID .String ())
35653566})
3566-
3567- // Build result
3568- var result []database.GetTemplateInsightsByTemplateRow
3569- for _ ,templateID := range templateIDs {
3570- r := database.GetTemplateInsightsByTemplateRow {
3571- TemplateID :templateID ,
3572- }
3573-
3574- uniqueUsers := map [uuid.UUID ]struct {}{}
3575-
3576- for _ ,mTemplateUserUsage := range appUsageByTemplateAndUser {
3577- mUserUsage ,ok := mTemplateUserUsage [templateID ]
3578- if ! ok {
3579- continue // template was not used in this time window
3580- }
3581-
3582- for userID ,usage := range mUserUsage {
3583- uniqueUsers [userID ]= struct {}{}
3584-
3585- r .UsageJetbrainsSeconds += usage .UsageJetbrainsSeconds
3586- r .UsageVscodeSeconds += usage .UsageVscodeSeconds
3587- r .UsageReconnectingPtySeconds += usage .UsageReconnectingPtySeconds
3588- r .UsageSshSeconds += usage .UsageSshSeconds
3589- }
3590- }
3591-
3592- r .ActiveUsers = int64 (len (uniqueUsers ))
3593-
3594- result = append (result ,r )
3595- }
3596- return result ,nil
3567+ return rows ,nil
35973568}
35983569
35993570func (q * FakeQuerier )GetTemplateParameterInsights (ctx context.Context ,arg database.GetTemplateParameterInsightsParams ) ([]database.GetTemplateParameterInsightsRow ,error ) {