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 }