cms-server/data/stats.go

package data

import (
	"context"
	"time"

	"github.com/google/uuid"
)

type PermissionLevelCount struct {
	PermissionLevel uint  `json:"permission_level"`
	Count           int64 `json:"count"`
}

type EventStatDoc struct {
	EventId      uuid.UUID `json:"event_id"`
	Name         string    `json:"name"`
	JoinCount    int64     `json:"join_count"`
	CheckinCount int64     `json:"checkin_count"`
}

type GlobalStats struct{}

func (self *GlobalStats) TotalUsers(ctx context.Context) (int64, error) {
	var count int64
	err := Database.WithContext(ctx).Model(&User{}).Count(&count).Error
	return count, err
}

func (self *GlobalStats) UsersPerPermissionLevel(ctx context.Context) (*[]PermissionLevelCount, error) {
	var results []PermissionLevelCount

	err := Database.WithContext(ctx).
		Model(&User{}).
		Select("permission_level, COUNT(*) as count").
		Group("permission_level").
		Order("permission_level ASC").
		Scan(&results).Error

	if err != nil {
		return nil, err
	}

	return &results, nil
}

func (self *GlobalStats) EventJoinCheckinCounts(ctx context.Context) (*[]EventStatDoc, error) {
	var results []EventStatDoc

	zero := time.Time{}

	err := Database.WithContext(ctx).
		Table("events").
		Select(`
			events.event_id,
			events.name,
			COUNT(attendances.id) AS join_count,
			SUM(CASE WHEN attendances.checkin_at > ? THEN 1 ELSE 0 END) AS checkin_count
		`, zero).
		Joins("LEFT JOIN attendances ON attendances.event_id = events.event_id").
		Group("events.id, events.event_id, events.name").
		Order("events.id ASC").
		Scan(&results).Error

	if err != nil {
		return nil, err
	}

	return &results, nil
}