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

package postgres

import (
	"context"
	"errors"
	"fmt"
	"time"

	"github.com/jackc/pgx/v5"
)

type Event struct {
	ID                  string
	PublicID            string
	Slug                string
	DisplayName         string
	Summary             *string
	Status              string
	CurrentReleaseID    *string
	CurrentReleasePubID *string
	ConfigLabel         *string
	ManifestURL         *string
	ManifestChecksum    *string
	RouteCode           *string
	ReleasePayloadJSON  *string
	RuntimeBindingID    *string
	PlacePublicID       *string
	PlaceName           *string
	MapAssetPublicID    *string
	MapAssetName        *string
	TileReleasePublicID *string
	CourseSetPublicID   *string
	CourseVariantID     *string
	CourseVariantName   *string
	RuntimeRouteCode    *string
	PresentationID      *string
	PresentationName    *string
	PresentationType    *string
	ContentBundleID     *string
	ContentBundleName   *string
	ContentEntryURL     *string
	ContentAssetRootURL *string
}

type EventRelease struct {
	ID                string
	PublicID          string
	EventID           string
	ReleaseNo         int
	ConfigLabel       string
	ManifestURL       string
	ManifestChecksum  *string
	RouteCode         *string
	BuildID           *string
	Status            string
	PublishedAt       time.Time
	RuntimeBindingID  *string
	PlacePublicID     *string
	PlaceName         *string
	MapAssetPublicID  *string
	MapAssetName      *string
	TileReleaseID     *string
	CourseSetID       *string
	CourseVariantID   *string
	CourseVariantName *string
	RuntimeRouteCode  *string
	PresentationID    *string
	PresentationName  *string
	PresentationType  *string
	ContentBundleID   *string
	ContentBundleName *string
	ContentEntryURL   *string
	ContentAssetURL   *string
}

type CreateGameSessionParams struct {
	SessionPublicID       string
	UserID                string
	EventID               string
	EventReleaseID        string
	DeviceKey             string
	ClientType            string
	AssignmentMode        *string
	VariantID             *string
	VariantName           *string
	RouteCode             *string
	SessionTokenHash      string
	SessionTokenExpiresAt time.Time
}

type GameSession struct {
	ID                    string
	SessionPublicID       string
	UserID                string
	EventID               string
	EventReleaseID        string
	DeviceKey             string
	ClientType            string
	AssignmentMode        *string
	VariantID             *string
	VariantName           *string
	RouteCode             *string
	Status                string
	SessionTokenExpiresAt time.Time
}

func (s *Store) GetEventByPublicID(ctx context.Context, eventPublicID string) (*Event, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT
			e.id,
			e.event_public_id,
			e.slug,
			e.display_name,
			e.summary,
			e.status,
			e.current_release_id,
			er.release_public_id,
			er.config_label,
			er.manifest_url,
			er.manifest_checksum_sha256,
			er.route_code,
			er.payload_jsonb::text,
			mrb.runtime_binding_public_id,
			p.place_public_id,
			p.name,
			ma.map_asset_public_id,
			ma.name,
			tr.tile_release_public_id,
			cset.course_set_public_id,
			cv.course_variant_public_id,
			cv.name,
			cv.route_code,
			ep.presentation_public_id,
			ep.name,
			ep.presentation_type,
			cb.content_bundle_public_id,
			cb.name,
			cb.entry_url,
			cb.asset_root_url
		FROM events e
		LEFT JOIN event_releases er ON er.id = e.current_release_id
		LEFT JOIN map_runtime_bindings mrb ON mrb.id = er.runtime_binding_id
		LEFT JOIN places p ON p.id = mrb.place_id
		LEFT JOIN map_assets ma ON ma.id = mrb.map_asset_id
		LEFT JOIN tile_releases tr ON tr.id = mrb.tile_release_id
		LEFT JOIN course_sets cset ON cset.id = mrb.course_set_id
		LEFT JOIN course_variants cv ON cv.id = mrb.course_variant_id
		LEFT JOIN event_presentations ep ON ep.id = er.presentation_id
		LEFT JOIN content_bundles cb ON cb.id = er.content_bundle_id
		WHERE e.event_public_id = $1
		LIMIT 1
	`, eventPublicID)

	var event Event
	err := row.Scan(
		&event.ID,
		&event.PublicID,
		&event.Slug,
		&event.DisplayName,
		&event.Summary,
		&event.Status,
		&event.CurrentReleaseID,
		&event.CurrentReleasePubID,
		&event.ConfigLabel,
		&event.ManifestURL,
		&event.ManifestChecksum,
		&event.RouteCode,
		&event.ReleasePayloadJSON,
		&event.RuntimeBindingID,
		&event.PlacePublicID,
		&event.PlaceName,
		&event.MapAssetPublicID,
		&event.MapAssetName,
		&event.TileReleasePublicID,
		&event.CourseSetPublicID,
		&event.CourseVariantID,
		&event.CourseVariantName,
		&event.RuntimeRouteCode,
		&event.PresentationID,
		&event.PresentationName,
		&event.PresentationType,
		&event.ContentBundleID,
		&event.ContentBundleName,
		&event.ContentEntryURL,
		&event.ContentAssetRootURL,
	)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("get event by public id: %w", err)
	}
	return &event, nil
}

func (s *Store) GetEventByID(ctx context.Context, eventID string) (*Event, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT
			e.id,
			e.event_public_id,
			e.slug,
			e.display_name,
			e.summary,
			e.status,
			e.current_release_id,
			er.release_public_id,
			er.config_label,
			er.manifest_url,
			er.manifest_checksum_sha256,
			er.route_code,
			er.payload_jsonb::text,
			mrb.runtime_binding_public_id,
			p.place_public_id,
			p.name,
			ma.map_asset_public_id,
			ma.name,
			tr.tile_release_public_id,
			cset.course_set_public_id,
			cv.course_variant_public_id,
			cv.name,
			cv.route_code,
			ep.presentation_public_id,
			ep.name,
			ep.presentation_type,
			cb.content_bundle_public_id,
			cb.name,
			cb.entry_url,
			cb.asset_root_url
		FROM events e
		LEFT JOIN event_releases er ON er.id = e.current_release_id
		LEFT JOIN map_runtime_bindings mrb ON mrb.id = er.runtime_binding_id
		LEFT JOIN places p ON p.id = mrb.place_id
		LEFT JOIN map_assets ma ON ma.id = mrb.map_asset_id
		LEFT JOIN tile_releases tr ON tr.id = mrb.tile_release_id
		LEFT JOIN course_sets cset ON cset.id = mrb.course_set_id
		LEFT JOIN course_variants cv ON cv.id = mrb.course_variant_id
		LEFT JOIN event_presentations ep ON ep.id = er.presentation_id
		LEFT JOIN content_bundles cb ON cb.id = er.content_bundle_id
		WHERE e.id = $1
		LIMIT 1
	`, eventID)

	var event Event
	err := row.Scan(
		&event.ID,
		&event.PublicID,
		&event.Slug,
		&event.DisplayName,
		&event.Summary,
		&event.Status,
		&event.CurrentReleaseID,
		&event.CurrentReleasePubID,
		&event.ConfigLabel,
		&event.ManifestURL,
		&event.ManifestChecksum,
		&event.RouteCode,
		&event.ReleasePayloadJSON,
		&event.RuntimeBindingID,
		&event.PlacePublicID,
		&event.PlaceName,
		&event.MapAssetPublicID,
		&event.MapAssetName,
		&event.TileReleasePublicID,
		&event.CourseSetPublicID,
		&event.CourseVariantID,
		&event.CourseVariantName,
		&event.RuntimeRouteCode,
		&event.PresentationID,
		&event.PresentationName,
		&event.PresentationType,
		&event.ContentBundleID,
		&event.ContentBundleName,
		&event.ContentEntryURL,
		&event.ContentAssetRootURL,
	)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("get event by id: %w", err)
	}
	return &event, nil
}

func (s *Store) NextEventReleaseNo(ctx context.Context, eventID string) (int, error) {
	var next int
	if err := s.pool.QueryRow(ctx, `
		SELECT COALESCE(MAX(release_no), 0) + 1
		FROM event_releases er
		WHERE event_id = $1
	`, eventID).Scan(&next); err != nil {
		return 0, fmt.Errorf("next event release no: %w", err)
	}
	return next, nil
}

type CreateEventReleaseParams struct {
	PublicID         string
	EventID          string
	ReleaseNo        int
	ConfigLabel      string
	ManifestURL      string
	ManifestChecksum *string
	RouteCode        *string
	BuildID          *string
	RuntimeBindingID *string
	PresentationID   *string
	ContentBundleID  *string
	Status           string
	PayloadJSON      string
}

func (s *Store) CreateEventRelease(ctx context.Context, tx Tx, params CreateEventReleaseParams) (*EventRelease, error) {
	row := tx.QueryRow(ctx, `
		INSERT INTO event_releases (
			release_public_id,
			event_id,
			release_no,
			config_label,
			manifest_url,
			manifest_checksum_sha256,
			route_code,
			build_id,
			runtime_binding_id,
			presentation_id,
			content_bundle_id,
			status,
			payload_jsonb
		)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13::jsonb)
		RETURNING id, release_public_id, event_id, release_no, config_label, manifest_url, manifest_checksum_sha256, route_code, build_id, status, published_at
	`, params.PublicID, params.EventID, params.ReleaseNo, params.ConfigLabel, params.ManifestURL, params.ManifestChecksum, params.RouteCode, params.BuildID, params.RuntimeBindingID, params.PresentationID, params.ContentBundleID, params.Status, params.PayloadJSON)

	var item EventRelease
	if err := row.Scan(
		&item.ID,
		&item.PublicID,
		&item.EventID,
		&item.ReleaseNo,
		&item.ConfigLabel,
		&item.ManifestURL,
		&item.ManifestChecksum,
		&item.RouteCode,
		&item.BuildID,
		&item.Status,
		&item.PublishedAt,
	); err != nil {
		return nil, fmt.Errorf("create event release: %w", err)
	}
	return &item, nil
}

func (s *Store) SetCurrentEventRelease(ctx context.Context, tx Tx, eventID, releaseID string) error {
	if _, err := tx.Exec(ctx, `
		UPDATE events
		SET current_release_id = $2
		WHERE id = $1
	`, eventID, releaseID); err != nil {
		return fmt.Errorf("set current event release: %w", err)
	}
	return nil
}

func (s *Store) CreateGameSession(ctx context.Context, tx Tx, params CreateGameSessionParams) (*GameSession, error) {
	row := tx.QueryRow(ctx, `
		INSERT INTO game_sessions (
			session_public_id,
			user_id,
			event_id,
			event_release_id,
			device_key,
			client_type,
			assignment_mode,
			variant_id,
			variant_name,
			route_code,
			session_token_hash,
			session_token_expires_at
		)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
		RETURNING id, session_public_id, user_id, event_id, event_release_id, device_key, client_type, assignment_mode, variant_id, variant_name, route_code, status, session_token_expires_at
	`, params.SessionPublicID, params.UserID, params.EventID, params.EventReleaseID, params.DeviceKey, params.ClientType, params.AssignmentMode, params.VariantID, params.VariantName, params.RouteCode, params.SessionTokenHash, params.SessionTokenExpiresAt)

	var session GameSession
	err := row.Scan(
		&session.ID,
		&session.SessionPublicID,
		&session.UserID,
		&session.EventID,
		&session.EventReleaseID,
		&session.DeviceKey,
		&session.ClientType,
		&session.AssignmentMode,
		&session.VariantID,
		&session.VariantName,
		&session.RouteCode,
		&session.Status,
		&session.SessionTokenExpiresAt,
	)
	if err != nil {
		return nil, fmt.Errorf("create game session: %w", err)
	}
	return &session, nil
}

func (s *Store) ListEventReleasesByEventID(ctx context.Context, eventID string, limit int) ([]EventRelease, error) {
	if limit <= 0 || limit > 100 {
		limit = 20
	}
	rows, err := s.pool.Query(ctx, `
		SELECT
			er.id,
			er.release_public_id,
			er.event_id,
			er.release_no,
			er.config_label,
			er.manifest_url,
			er.manifest_checksum_sha256,
			er.route_code,
			er.build_id,
			er.status,
			er.published_at,
			mrb.runtime_binding_public_id,
			p.place_public_id,
			p.name,
			ma.map_asset_public_id,
			ma.name,
			tr.tile_release_public_id,
			cset.course_set_public_id,
			cv.course_variant_public_id,
			cv.name,
			cv.route_code,
			ep.presentation_public_id,
			ep.name,
			ep.presentation_type,
			cb.content_bundle_public_id,
			cb.name,
			cb.entry_url,
			cb.asset_root_url
		FROM event_releases er
		LEFT JOIN map_runtime_bindings mrb ON mrb.id = er.runtime_binding_id
		LEFT JOIN places p ON p.id = mrb.place_id
		LEFT JOIN map_assets ma ON ma.id = mrb.map_asset_id
		LEFT JOIN tile_releases tr ON tr.id = mrb.tile_release_id
		LEFT JOIN course_sets cset ON cset.id = mrb.course_set_id
		LEFT JOIN course_variants cv ON cv.id = mrb.course_variant_id
		LEFT JOIN event_presentations ep ON ep.id = er.presentation_id
		LEFT JOIN content_bundles cb ON cb.id = er.content_bundle_id
		WHERE er.event_id = $1
		ORDER BY er.release_no DESC
		LIMIT $2
	`, eventID, limit)
	if err != nil {
		return nil, fmt.Errorf("list event releases by event id: %w", err)
	}
	defer rows.Close()

	items := []EventRelease{}
	for rows.Next() {
		item, err := scanEventReleaseFromRows(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, *item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate event releases by event id: %w", err)
	}
	return items, nil
}

func (s *Store) GetEventReleaseByPublicID(ctx context.Context, releasePublicID string) (*EventRelease, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT
			er.id,
			er.release_public_id,
			er.event_id,
			er.release_no,
			er.config_label,
			er.manifest_url,
			er.manifest_checksum_sha256,
			er.route_code,
			er.build_id,
			er.status,
			er.published_at,
			mrb.runtime_binding_public_id,
			p.place_public_id,
			p.name,
			ma.map_asset_public_id,
			ma.name,
			tr.tile_release_public_id,
			cset.course_set_public_id,
			cv.course_variant_public_id,
			cv.name,
			cv.route_code,
			ep.presentation_public_id,
			ep.name,
			ep.presentation_type,
			cb.content_bundle_public_id,
			cb.name,
			cb.entry_url,
			cb.asset_root_url
		FROM event_releases er
		LEFT JOIN map_runtime_bindings mrb ON mrb.id = er.runtime_binding_id
		LEFT JOIN places p ON p.id = mrb.place_id
		LEFT JOIN map_assets ma ON ma.id = mrb.map_asset_id
		LEFT JOIN tile_releases tr ON tr.id = mrb.tile_release_id
		LEFT JOIN course_sets cset ON cset.id = mrb.course_set_id
		LEFT JOIN course_variants cv ON cv.id = mrb.course_variant_id
		LEFT JOIN event_presentations ep ON ep.id = er.presentation_id
		LEFT JOIN content_bundles cb ON cb.id = er.content_bundle_id
		WHERE er.release_public_id = $1
		LIMIT 1
	`, releasePublicID)

	var item EventRelease
	err := row.Scan(
		&item.ID,
		&item.PublicID,
		&item.EventID,
		&item.ReleaseNo,
		&item.ConfigLabel,
		&item.ManifestURL,
		&item.ManifestChecksum,
		&item.RouteCode,
		&item.BuildID,
		&item.Status,
		&item.PublishedAt,
		&item.RuntimeBindingID,
		&item.PlacePublicID,
		&item.PlaceName,
		&item.MapAssetPublicID,
		&item.MapAssetName,
		&item.TileReleaseID,
		&item.CourseSetID,
		&item.CourseVariantID,
		&item.CourseVariantName,
		&item.RuntimeRouteCode,
		&item.PresentationID,
		&item.PresentationName,
		&item.PresentationType,
		&item.ContentBundleID,
		&item.ContentBundleName,
		&item.ContentEntryURL,
		&item.ContentAssetURL,
	)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("get event release by public id: %w", err)
	}
	return &item, nil
}

func scanEventReleaseFromRows(rows pgx.Rows) (*EventRelease, error) {
	var item EventRelease
	err := rows.Scan(
		&item.ID,
		&item.PublicID,
		&item.EventID,
		&item.ReleaseNo,
		&item.ConfigLabel,
		&item.ManifestURL,
		&item.ManifestChecksum,
		&item.RouteCode,
		&item.BuildID,
		&item.Status,
		&item.PublishedAt,
		&item.RuntimeBindingID,
		&item.PlacePublicID,
		&item.PlaceName,
		&item.MapAssetPublicID,
		&item.MapAssetName,
		&item.TileReleaseID,
		&item.CourseSetID,
		&item.CourseVariantID,
		&item.CourseVariantName,
		&item.RuntimeRouteCode,
		&item.PresentationID,
		&item.PresentationName,
		&item.PresentationType,
		&item.ContentBundleID,
		&item.ContentBundleName,
		&item.ContentEntryURL,
		&item.ContentAssetURL,
	)
	if err != nil {
		return nil, fmt.Errorf("scan event release row: %w", err)
	}
	return &item, nil
}

func (s *Store) SetEventReleaseRuntimeBinding(ctx context.Context, tx Tx, releaseID string, runtimeBindingID *string) error {
	if _, err := tx.Exec(ctx, `
		UPDATE event_releases
		SET runtime_binding_id = $2
		WHERE id = $1
	`, releaseID, runtimeBindingID); err != nil {
		return fmt.Errorf("set event release runtime binding: %w", err)
	}
	return nil
}