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