mini-prog/backend/internal/store/postgres/ops_summary_store.go

package postgres

import (
	"context"
	"fmt"
)

type OpsOverviewCounts struct {
	ManagedAssets   int
	Places          int
	MapAssets       int
	TileReleases    int
	CourseSets      int
	CourseVariants  int
	Events          int
	DefaultEvents   int
	PublishedEvents int
	ConfigSources   int
	Releases        int
	RuntimeBindings int
	Presentations   int
	ContentBundles  int
	OpsUsers        int
}

func (s *Store) GetOpsOverviewCounts(ctx context.Context) (*OpsOverviewCounts, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT
			(SELECT COUNT(*) FROM managed_assets WHERE status <> 'archived') AS managed_assets,
			(SELECT COUNT(*) FROM places WHERE status <> 'archived') AS places,
			(SELECT COUNT(*) FROM map_assets WHERE status <> 'archived') AS map_assets,
			(SELECT COUNT(*) FROM tile_releases WHERE status <> 'archived') AS tile_releases,
			(SELECT COUNT(*) FROM course_sets WHERE status <> 'archived') AS course_sets,
			(SELECT COUNT(*) FROM course_variants WHERE status <> 'archived') AS course_variants,
			(SELECT COUNT(*) FROM events WHERE status <> 'archived') AS events,
			(SELECT COUNT(*) FROM events WHERE status <> 'archived' AND COALESCE(is_default_experience, false)) AS default_events,
			(SELECT COUNT(*) FROM events WHERE status <> 'archived' AND current_release_id IS NOT NULL) AS published_events,
			(SELECT COUNT(*) FROM event_config_sources) AS config_sources,
			(SELECT COUNT(*) FROM event_releases) AS releases,
			(SELECT COUNT(*) FROM map_runtime_bindings WHERE status <> 'archived') AS runtime_bindings,
			(SELECT COUNT(*) FROM event_presentations WHERE status <> 'archived') AS presentations,
			(SELECT COUNT(*) FROM content_bundles WHERE status <> 'archived') AS content_bundles,
			(SELECT COUNT(*) FROM ops_users WHERE status <> 'deleted') AS ops_users
	`)
	var item OpsOverviewCounts
	if err := row.Scan(
		&item.ManagedAssets,
		&item.Places,
		&item.MapAssets,
		&item.TileReleases,
		&item.CourseSets,
		&item.CourseVariants,
		&item.Events,
		&item.DefaultEvents,
		&item.PublishedEvents,
		&item.ConfigSources,
		&item.Releases,
		&item.RuntimeBindings,
		&item.Presentations,
		&item.ContentBundles,
		&item.OpsUsers,
	); err != nil {
		return nil, fmt.Errorf("get ops overview counts: %w", err)
	}
	return &item, nil
}