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

package postgres

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"time"

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

type Place struct {
	ID          string
	PublicID    string
	Code        string
	Name        string
	Region      *string
	CoverURL    *string
	Description *string
	CenterPoint json.RawMessage
	Status      string
	CreatedAt   time.Time
	UpdatedAt   time.Time
}

type MapAsset struct {
	ID                   string
	PublicID             string
	PlaceID              string
	PlacePublicID        *string
	PlaceName            *string
	LegacyMapID          *string
	LegacyMapPublicID    *string
	Code                 string
	Name                 string
	MapType              string
	CoverURL             *string
	Description          *string
	Status               string
	CurrentTileReleaseID *string
	CreatedAt            time.Time
	UpdatedAt            time.Time
}

type TileRelease struct {
	ID                  string
	PublicID            string
	MapAssetID          string
	LegacyMapVersionID  *string
	LegacyMapVersionPub *string
	VersionCode         string
	Status              string
	TileBaseURL         string
	MetaURL             string
	PublishedAssetRoot  *string
	MetadataJSON        json.RawMessage
	PublishedAt         *time.Time
	CreatedAt           time.Time
	UpdatedAt           time.Time
}

type CourseSource struct {
	ID                        string
	PublicID                  string
	LegacyPlayfieldVersionID  *string
	LegacyPlayfieldVersionPub *string
	SourceType                string
	FileURL                   string
	Checksum                  *string
	ParserVersion             *string
	ImportStatus              string
	MetadataJSON              json.RawMessage
	ImportedAt                time.Time
	CreatedAt                 time.Time
	UpdatedAt                 time.Time
}

type CourseSet struct {
	ID               string
	PublicID         string
	PlaceID          string
	MapAssetID       string
	Code             string
	Mode             string
	Name             string
	Description      *string
	Status           string
	CurrentVariantID *string
	CreatedAt        time.Time
	UpdatedAt        time.Time
}

type CourseVariant struct {
	ID             string
	PublicID       string
	CourseSetID    string
	SourceID       *string
	SourcePublicID *string
	Name           string
	RouteCode      *string
	Mode           string
	ControlCount   *int
	Difficulty     *string
	Status         string
	IsDefault      bool
	ConfigPatch    json.RawMessage
	MetadataJSON   json.RawMessage
	CreatedAt      time.Time
	UpdatedAt      time.Time
}

type MapRuntimeBinding struct {
	ID                    string
	PublicID              string
	EventID               string
	EventPublicID         string
	PlaceID               string
	PlacePublicID         string
	MapAssetID            string
	MapAssetPublicID      string
	TileReleaseID         string
	TileReleasePublicID   string
	CourseSetID           string
	CourseSetPublicID     string
	CourseVariantID       string
	CourseVariantPublicID string
	Status                string
	Notes                 *string
	CreatedAt             time.Time
	UpdatedAt             time.Time
}

type CreatePlaceParams struct {
	PublicID    string
	Code        string
	Name        string
	Region      *string
	CoverURL    *string
	Description *string
	CenterPoint map[string]any
	Status      string
}

type CreateMapAssetParams struct {
	PublicID    string
	PlaceID     string
	LegacyMapID *string
	Code        string
	Name        string
	MapType     string
	CoverURL    *string
	Description *string
	Status      string
}

type UpdateMapAssetParams struct {
	MapAssetID  string
	Code        string
	Name        string
	MapType     string
	CoverURL    *string
	Description *string
	Status      string
}

type CreateTileReleaseParams struct {
	PublicID           string
	MapAssetID         string
	LegacyMapVersionID *string
	VersionCode        string
	Status             string
	TileBaseURL        string
	MetaURL            string
	PublishedAssetRoot *string
	MetadataJSON       map[string]any
	PublishedAt        *time.Time
}

type CreateCourseSourceParams struct {
	PublicID                 string
	LegacyPlayfieldVersionID *string
	SourceType               string
	FileURL                  string
	Checksum                 *string
	ParserVersion            *string
	ImportStatus             string
	MetadataJSON             map[string]any
	ImportedAt               *time.Time
}

type CreateCourseSetParams struct {
	PublicID    string
	PlaceID     string
	MapAssetID  string
	Code        string
	Mode        string
	Name        string
	Description *string
	Status      string
}

type CreateCourseVariantParams struct {
	PublicID     string
	CourseSetID  string
	SourceID     *string
	Name         string
	RouteCode    *string
	Mode         string
	ControlCount *int
	Difficulty   *string
	Status       string
	IsDefault    bool
	ConfigPatch  map[string]any
	MetadataJSON map[string]any
}

type CreateMapRuntimeBindingParams struct {
	PublicID        string
	EventID         string
	PlaceID         string
	MapAssetID      string
	TileReleaseID   string
	CourseSetID     string
	CourseVariantID string
	Status          string
	Notes           *string
}

type MapAssetLinkedEvent struct {
	EventPublicID            string
	DisplayName              string
	Summary                  *string
	Status                   string
	IsDefaultExperience      bool
	ShowInEventList          bool
	CurrentReleasePublicID   *string
	ConfigLabel              *string
	RouteCode                *string
	CurrentPresentationID    *string
	CurrentPresentationName  *string
	CurrentContentBundleID   *string
	CurrentContentBundleName *string
}

func (s *Store) ListMapAssets(ctx context.Context, limit int) ([]MapAsset, error) {
	if limit <= 0 || limit > 200 {
		limit = 50
	}
	rows, err := s.pool.Query(ctx, `
		SELECT ma.id, ma.map_asset_public_id, ma.place_id, p.place_public_id, p.name, ma.legacy_map_id, lm.map_public_id, ma.code, ma.name, ma.map_type,
		       ma.cover_url, ma.description, ma.status, ma.current_tile_release_id, ma.created_at, ma.updated_at
		FROM map_assets ma
		JOIN places p ON p.id = ma.place_id
		LEFT JOIN maps lm ON lm.id = ma.legacy_map_id
		ORDER BY ma.created_at DESC
		LIMIT $1
	`, limit)
	if err != nil {
		return nil, fmt.Errorf("list all map assets: %w", err)
	}
	defer rows.Close()
	items := []MapAsset{}
	for rows.Next() {
		item, err := scanMapAssetFromRows(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, *item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate all map assets: %w", err)
	}
	return items, nil
}

func (s *Store) ListPlaces(ctx context.Context, limit int) ([]Place, error) {
	if limit <= 0 || limit > 200 {
		limit = 50
	}
	rows, err := s.pool.Query(ctx, `
		SELECT id, place_public_id, code, name, region, cover_url, description, center_point_jsonb::text, status, created_at, updated_at
		FROM places
		ORDER BY created_at DESC
		LIMIT $1
	`, limit)
	if err != nil {
		return nil, fmt.Errorf("list places: %w", err)
	}
	defer rows.Close()
	items := []Place{}
	for rows.Next() {
		item, err := scanPlaceFromRows(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, *item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate places: %w", err)
	}
	return items, nil
}

func (s *Store) GetPlaceByPublicID(ctx context.Context, publicID string) (*Place, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT id, place_public_id, code, name, region, cover_url, description, center_point_jsonb::text, status, created_at, updated_at
		FROM places
		WHERE place_public_id = $1
		LIMIT 1
	`, publicID)
	return scanPlace(row)
}

func (s *Store) GetPlaceByCode(ctx context.Context, code string) (*Place, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT id, place_public_id, code, name, region, cover_url, description, center_point_jsonb::text, status, created_at, updated_at
		FROM places
		WHERE code = $1
		LIMIT 1
	`, code)
	return scanPlace(row)
}

func (s *Store) CreatePlace(ctx context.Context, tx Tx, params CreatePlaceParams) (*Place, error) {
	centerPointJSON, err := marshalJSONMap(params.CenterPoint)
	if err != nil {
		return nil, fmt.Errorf("marshal place center point: %w", err)
	}
	row := tx.QueryRow(ctx, `
		INSERT INTO places (place_public_id, code, name, region, cover_url, description, center_point_jsonb, status)
		VALUES ($1, $2, $3, $4, $5, $6, $7::jsonb, $8)
		RETURNING id, place_public_id, code, name, region, cover_url, description, center_point_jsonb::text, status, created_at, updated_at
	`, params.PublicID, params.Code, params.Name, params.Region, params.CoverURL, params.Description, centerPointJSON, params.Status)
	return scanPlace(row)
}

func (s *Store) ListMapAssetsByPlaceID(ctx context.Context, placeID string) ([]MapAsset, error) {
	rows, err := s.pool.Query(ctx, `
		SELECT ma.id, ma.map_asset_public_id, ma.place_id, p.place_public_id, p.name, ma.legacy_map_id, lm.map_public_id, ma.code, ma.name, ma.map_type,
		       ma.cover_url, ma.description, ma.status, ma.current_tile_release_id, ma.created_at, ma.updated_at
		FROM map_assets ma
		JOIN places p ON p.id = ma.place_id
		LEFT JOIN maps lm ON lm.id = ma.legacy_map_id
		WHERE ma.place_id = $1
		ORDER BY ma.created_at DESC
	`, placeID)
	if err != nil {
		return nil, fmt.Errorf("list map assets: %w", err)
	}
	defer rows.Close()
	items := []MapAsset{}
	for rows.Next() {
		item, err := scanMapAssetFromRows(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, *item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate map assets: %w", err)
	}
	return items, nil
}

func (s *Store) GetMapAssetByPublicID(ctx context.Context, publicID string) (*MapAsset, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT ma.id, ma.map_asset_public_id, ma.place_id, p.place_public_id, p.name, ma.legacy_map_id, lm.map_public_id, ma.code, ma.name, ma.map_type,
		       ma.cover_url, ma.description, ma.status, ma.current_tile_release_id, ma.created_at, ma.updated_at
		FROM map_assets ma
		JOIN places p ON p.id = ma.place_id
		LEFT JOIN maps lm ON lm.id = ma.legacy_map_id
		WHERE ma.map_asset_public_id = $1
		LIMIT 1
	`, publicID)
	return scanMapAsset(row)
}

func (s *Store) GetMapAssetByCode(ctx context.Context, code string) (*MapAsset, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT ma.id, ma.map_asset_public_id, ma.place_id, p.place_public_id, p.name, ma.legacy_map_id, lm.map_public_id, ma.code, ma.name, ma.map_type,
		       ma.cover_url, ma.description, ma.status, ma.current_tile_release_id, ma.created_at, ma.updated_at
		FROM map_assets ma
		JOIN places p ON p.id = ma.place_id
		LEFT JOIN maps lm ON lm.id = ma.legacy_map_id
		WHERE ma.code = $1
		LIMIT 1
	`, code)
	return scanMapAsset(row)
}

func (s *Store) CreateMapAsset(ctx context.Context, tx Tx, params CreateMapAssetParams) (*MapAsset, error) {
	row := tx.QueryRow(ctx, `
		INSERT INTO map_assets (map_asset_public_id, place_id, legacy_map_id, code, name, map_type, cover_url, description, status)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
		RETURNING id, map_asset_public_id, place_id, NULL::text, NULL::text, legacy_map_id, NULL::text, code, name, map_type, cover_url, description, status, current_tile_release_id, created_at, updated_at
	`, params.PublicID, params.PlaceID, params.LegacyMapID, params.Code, params.Name, params.MapType, params.CoverURL, params.Description, params.Status)
	return scanMapAsset(row)
}

func (s *Store) UpdateMapAsset(ctx context.Context, tx Tx, params UpdateMapAssetParams) (*MapAsset, error) {
	row := tx.QueryRow(ctx, `
		UPDATE map_assets
		SET code = $2,
		    name = $3,
		    map_type = $4,
		    cover_url = $5,
		    description = $6,
		    status = $7,
		    updated_at = NOW()
		WHERE id = $1
		RETURNING id, map_asset_public_id, place_id, NULL::text, NULL::text, legacy_map_id, NULL::text, code, name, map_type, cover_url, description, status, current_tile_release_id, created_at, updated_at
	`, params.MapAssetID, params.Code, params.Name, params.MapType, params.CoverURL, params.Description, params.Status)
	return scanMapAsset(row)
}

func (s *Store) ListTileReleasesByMapAssetID(ctx context.Context, mapAssetID string) ([]TileRelease, error) {
	rows, err := s.pool.Query(ctx, `
		SELECT tr.id, tr.tile_release_public_id, tr.map_asset_id, tr.legacy_map_version_id, mv.version_public_id,
		       tr.version_code, tr.status, tr.tile_base_url, tr.meta_url, tr.published_asset_root,
		       tr.metadata_jsonb::text, tr.published_at, tr.created_at, tr.updated_at
		FROM tile_releases tr
		LEFT JOIN map_versions mv ON mv.id = tr.legacy_map_version_id
		WHERE tr.map_asset_id = $1
		ORDER BY tr.created_at DESC
	`, mapAssetID)
	if err != nil {
		return nil, fmt.Errorf("list tile releases: %w", err)
	}
	defer rows.Close()
	items := []TileRelease{}
	for rows.Next() {
		item, err := scanTileReleaseFromRows(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, *item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate tile releases: %w", err)
	}
	return items, nil
}

func (s *Store) GetTileReleaseByPublicID(ctx context.Context, publicID string) (*TileRelease, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT tr.id, tr.tile_release_public_id, tr.map_asset_id, tr.legacy_map_version_id, mv.version_public_id,
		       tr.version_code, tr.status, tr.tile_base_url, tr.meta_url, tr.published_asset_root,
		       tr.metadata_jsonb::text, tr.published_at, tr.created_at, tr.updated_at
		FROM tile_releases tr
		LEFT JOIN map_versions mv ON mv.id = tr.legacy_map_version_id
		WHERE tr.tile_release_public_id = $1
		LIMIT 1
	`, publicID)
	return scanTileRelease(row)
}

func (s *Store) GetTileReleaseByMapAssetIDAndVersionCode(ctx context.Context, mapAssetID, versionCode string) (*TileRelease, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT tr.id, tr.tile_release_public_id, tr.map_asset_id, tr.legacy_map_version_id, mv.version_public_id,
		       tr.version_code, tr.status, tr.tile_base_url, tr.meta_url, tr.published_asset_root,
		       tr.metadata_jsonb::text, tr.published_at, tr.created_at, tr.updated_at
		FROM tile_releases tr
		LEFT JOIN map_versions mv ON mv.id = tr.legacy_map_version_id
		WHERE tr.map_asset_id = $1 AND tr.version_code = $2
		LIMIT 1
	`, mapAssetID, versionCode)
	return scanTileRelease(row)
}

func (s *Store) CreateTileRelease(ctx context.Context, tx Tx, params CreateTileReleaseParams) (*TileRelease, error) {
	metadataJSON, err := marshalJSONMap(params.MetadataJSON)
	if err != nil {
		return nil, fmt.Errorf("marshal tile release metadata: %w", err)
	}
	row := tx.QueryRow(ctx, `
		INSERT INTO tile_releases (
			tile_release_public_id, map_asset_id, legacy_map_version_id, version_code, status,
			tile_base_url, meta_url, published_asset_root, metadata_jsonb, published_at
		)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9::jsonb, $10)
		RETURNING id, tile_release_public_id, map_asset_id, legacy_map_version_id, NULL::text, version_code, status,
		          tile_base_url, meta_url, published_asset_root, metadata_jsonb::text, published_at, created_at, updated_at
	`, params.PublicID, params.MapAssetID, params.LegacyMapVersionID, params.VersionCode, params.Status, params.TileBaseURL, params.MetaURL, params.PublishedAssetRoot, metadataJSON, params.PublishedAt)
	return scanTileRelease(row)
}

func (s *Store) SetMapAssetCurrentTileRelease(ctx context.Context, tx Tx, mapAssetID, tileReleaseID string) error {
	_, err := tx.Exec(ctx, `UPDATE map_assets SET current_tile_release_id = $2 WHERE id = $1`, mapAssetID, tileReleaseID)
	if err != nil {
		return fmt.Errorf("set map asset current tile release: %w", err)
	}
	return nil
}

func (s *Store) ListCourseSources(ctx context.Context, limit int) ([]CourseSource, error) {
	if limit <= 0 || limit > 200 {
		limit = 50
	}
	rows, err := s.pool.Query(ctx, `
		SELECT cs.id, cs.course_source_public_id, cs.legacy_playfield_version_id, pv.version_public_id, cs.source_type,
		       cs.file_url, cs.checksum, cs.parser_version, cs.import_status, cs.metadata_jsonb::text, cs.imported_at, cs.created_at, cs.updated_at
		FROM course_sources cs
		LEFT JOIN playfield_versions pv ON pv.id = cs.legacy_playfield_version_id
		ORDER BY cs.created_at DESC
		LIMIT $1
	`, limit)
	if err != nil {
		return nil, fmt.Errorf("list course sources: %w", err)
	}
	defer rows.Close()
	items := []CourseSource{}
	for rows.Next() {
		item, err := scanCourseSourceFromRows(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, *item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate course sources: %w", err)
	}
	return items, nil
}

func (s *Store) GetCourseSourceByPublicID(ctx context.Context, publicID string) (*CourseSource, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT cs.id, cs.course_source_public_id, cs.legacy_playfield_version_id, pv.version_public_id, cs.source_type,
		       cs.file_url, cs.checksum, cs.parser_version, cs.import_status, cs.metadata_jsonb::text, cs.imported_at, cs.created_at, cs.updated_at
		FROM course_sources cs
		LEFT JOIN playfield_versions pv ON pv.id = cs.legacy_playfield_version_id
		WHERE cs.course_source_public_id = $1
		LIMIT 1
	`, publicID)
	return scanCourseSource(row)
}

func (s *Store) CreateCourseSource(ctx context.Context, tx Tx, params CreateCourseSourceParams) (*CourseSource, error) {
	metadataJSON, err := marshalJSONMap(params.MetadataJSON)
	if err != nil {
		return nil, fmt.Errorf("marshal course source metadata: %w", err)
	}
	importedAt := time.Now()
	if params.ImportedAt != nil {
		importedAt = *params.ImportedAt
	}
	row := tx.QueryRow(ctx, `
		INSERT INTO course_sources (
			course_source_public_id, legacy_playfield_version_id, source_type, file_url, checksum,
			parser_version, import_status, metadata_jsonb, imported_at
		)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8::jsonb, $9)
		RETURNING id, course_source_public_id, legacy_playfield_version_id, NULL::text, source_type, file_url,
		          checksum, parser_version, import_status, metadata_jsonb::text, imported_at, created_at, updated_at
	`, params.PublicID, params.LegacyPlayfieldVersionID, params.SourceType, params.FileURL, params.Checksum, params.ParserVersion, params.ImportStatus, metadataJSON, importedAt)
	return scanCourseSource(row)
}

func (s *Store) ListCourseSets(ctx context.Context, limit int) ([]CourseSet, error) {
	if limit <= 0 || limit > 200 {
		limit = 50
	}
	rows, err := s.pool.Query(ctx, `
		SELECT id, course_set_public_id, place_id, map_asset_id, code, mode, name, description, status, current_variant_id, created_at, updated_at
		FROM course_sets
		ORDER BY created_at DESC
		LIMIT $1
	`, limit)
	if err != nil {
		return nil, fmt.Errorf("list course sets: %w", err)
	}
	defer rows.Close()
	items := []CourseSet{}
	for rows.Next() {
		item, err := scanCourseSetFromRows(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, *item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate course sets: %w", err)
	}
	return items, nil
}

func (s *Store) ListCourseSetsByMapAssetID(ctx context.Context, mapAssetID string) ([]CourseSet, error) {
	rows, err := s.pool.Query(ctx, `
		SELECT id, course_set_public_id, place_id, map_asset_id, code, mode, name, description, status, current_variant_id, created_at, updated_at
		FROM course_sets
		WHERE map_asset_id = $1
		ORDER BY created_at DESC
	`, mapAssetID)
	if err != nil {
		return nil, fmt.Errorf("list course sets by map asset: %w", err)
	}
	defer rows.Close()
	items := []CourseSet{}
	for rows.Next() {
		item, err := scanCourseSetFromRows(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, *item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate course sets by map asset: %w", err)
	}
	return items, nil
}

func (s *Store) GetCourseSetByPublicID(ctx context.Context, publicID string) (*CourseSet, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT id, course_set_public_id, place_id, map_asset_id, code, mode, name, description, status, current_variant_id, created_at, updated_at
		FROM course_sets
		WHERE course_set_public_id = $1
		LIMIT 1
	`, publicID)
	return scanCourseSet(row)
}

func (s *Store) GetCourseSetByCode(ctx context.Context, code string) (*CourseSet, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT id, course_set_public_id, place_id, map_asset_id, code, mode, name, description, status, current_variant_id, created_at, updated_at
		FROM course_sets
		WHERE code = $1
		LIMIT 1
	`, code)
	return scanCourseSet(row)
}

func (s *Store) CreateCourseSet(ctx context.Context, tx Tx, params CreateCourseSetParams) (*CourseSet, error) {
	row := tx.QueryRow(ctx, `
		INSERT INTO course_sets (course_set_public_id, place_id, map_asset_id, code, mode, name, description, status)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
		RETURNING id, course_set_public_id, place_id, map_asset_id, code, mode, name, description, status, current_variant_id, created_at, updated_at
	`, params.PublicID, params.PlaceID, params.MapAssetID, params.Code, params.Mode, params.Name, params.Description, params.Status)
	return scanCourseSet(row)
}

func (s *Store) ListCourseVariantsByCourseSetID(ctx context.Context, courseSetID string) ([]CourseVariant, error) {
	rows, err := s.pool.Query(ctx, `
		SELECT cv.id, cv.course_variant_public_id, cv.course_set_id, cv.source_id, cs.course_source_public_id, cv.name, cv.route_code,
		       cv.mode, cv.control_count, cv.difficulty, cv.status, cv.is_default,
		       cv.config_patch_jsonb::text, cv.metadata_jsonb::text, cv.created_at, cv.updated_at
		FROM course_variants cv
		LEFT JOIN course_sources cs ON cs.id = cv.source_id
		WHERE cv.course_set_id = $1
		ORDER BY cv.created_at DESC
	`, courseSetID)
	if err != nil {
		return nil, fmt.Errorf("list course variants: %w", err)
	}
	defer rows.Close()
	items := []CourseVariant{}
	for rows.Next() {
		item, err := scanCourseVariantFromRows(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, *item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate course variants: %w", err)
	}
	return items, nil
}

func (s *Store) GetCourseVariantByPublicID(ctx context.Context, publicID string) (*CourseVariant, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT cv.id, cv.course_variant_public_id, cv.course_set_id, cv.source_id, cs.course_source_public_id, cv.name, cv.route_code,
		       cv.mode, cv.control_count, cv.difficulty, cv.status, cv.is_default,
		       cv.config_patch_jsonb::text, cv.metadata_jsonb::text, cv.created_at, cv.updated_at
		FROM course_variants cv
		LEFT JOIN course_sources cs ON cs.id = cv.source_id
		WHERE cv.course_variant_public_id = $1
		LIMIT 1
	`, publicID)
	return scanCourseVariant(row)
}

func (s *Store) GetCourseVariantByCourseSetIDAndRouteCode(ctx context.Context, courseSetID, routeCode string) (*CourseVariant, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT cv.id, cv.course_variant_public_id, cv.course_set_id, cv.source_id, cs.course_source_public_id, cv.name, cv.route_code,
		       cv.mode, cv.control_count, cv.difficulty, cv.status, cv.is_default,
		       cv.config_patch_jsonb::text, cv.metadata_jsonb::text, cv.created_at, cv.updated_at
		FROM course_variants cv
		LEFT JOIN course_sources cs ON cs.id = cv.source_id
		WHERE cv.course_set_id = $1 AND cv.route_code = $2
		LIMIT 1
	`, courseSetID, routeCode)
	return scanCourseVariant(row)
}

func (s *Store) CreateCourseVariant(ctx context.Context, tx Tx, params CreateCourseVariantParams) (*CourseVariant, error) {
	configPatchJSON, err := marshalJSONMap(params.ConfigPatch)
	if err != nil {
		return nil, fmt.Errorf("marshal course variant config patch: %w", err)
	}
	metadataJSON, err := marshalJSONMap(params.MetadataJSON)
	if err != nil {
		return nil, fmt.Errorf("marshal course variant metadata: %w", err)
	}
	row := tx.QueryRow(ctx, `
		INSERT INTO course_variants (
			course_variant_public_id, course_set_id, source_id, name, route_code, mode, control_count,
			difficulty, status, is_default, config_patch_jsonb, metadata_jsonb
		)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11::jsonb, $12::jsonb)
		RETURNING id, course_variant_public_id, course_set_id, source_id, NULL::text, name, route_code, mode,
		          control_count, difficulty, status, is_default, config_patch_jsonb::text, metadata_jsonb::text, created_at, updated_at
	`, params.PublicID, params.CourseSetID, params.SourceID, params.Name, params.RouteCode, params.Mode, params.ControlCount, params.Difficulty, params.Status, params.IsDefault, configPatchJSON, metadataJSON)
	return scanCourseVariant(row)
}

func (s *Store) SetCourseSetCurrentVariant(ctx context.Context, tx Tx, courseSetID, variantID string) error {
	_, err := tx.Exec(ctx, `UPDATE course_sets SET current_variant_id = $2 WHERE id = $1`, courseSetID, variantID)
	if err != nil {
		return fmt.Errorf("set course set current variant: %w", err)
	}
	return nil
}

func (s *Store) ListMapRuntimeBindings(ctx context.Context, limit int) ([]MapRuntimeBinding, error) {
	if limit <= 0 || limit > 200 {
		limit = 50
	}
	rows, err := s.pool.Query(ctx, `
		SELECT mrb.id, mrb.runtime_binding_public_id, mrb.event_id, e.event_public_id, mrb.place_id, p.place_public_id,
		       mrb.map_asset_id, ma.map_asset_public_id, mrb.tile_release_id, tr.tile_release_public_id,
		       mrb.course_set_id, cset.course_set_public_id, mrb.course_variant_id, cv.course_variant_public_id,
		       mrb.status, mrb.notes, mrb.created_at, mrb.updated_at
		FROM map_runtime_bindings mrb
		JOIN events e ON e.id = mrb.event_id
		JOIN places p ON p.id = mrb.place_id
		JOIN map_assets ma ON ma.id = mrb.map_asset_id
		JOIN tile_releases tr ON tr.id = mrb.tile_release_id
		JOIN course_sets cset ON cset.id = mrb.course_set_id
		JOIN course_variants cv ON cv.id = mrb.course_variant_id
		ORDER BY mrb.created_at DESC
		LIMIT $1
	`, limit)
	if err != nil {
		return nil, fmt.Errorf("list runtime bindings: %w", err)
	}
	defer rows.Close()
	items := []MapRuntimeBinding{}
	for rows.Next() {
		item, err := scanMapRuntimeBindingFromRows(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, *item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate runtime bindings: %w", err)
	}
	return items, nil
}

func (s *Store) GetMapRuntimeBindingByPublicID(ctx context.Context, publicID string) (*MapRuntimeBinding, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT mrb.id, mrb.runtime_binding_public_id, mrb.event_id, e.event_public_id, mrb.place_id, p.place_public_id,
		       mrb.map_asset_id, ma.map_asset_public_id, mrb.tile_release_id, tr.tile_release_public_id,
		       mrb.course_set_id, cset.course_set_public_id, mrb.course_variant_id, cv.course_variant_public_id,
		       mrb.status, mrb.notes, mrb.created_at, mrb.updated_at
		FROM map_runtime_bindings mrb
		JOIN events e ON e.id = mrb.event_id
		JOIN places p ON p.id = mrb.place_id
		JOIN map_assets ma ON ma.id = mrb.map_asset_id
		JOIN tile_releases tr ON tr.id = mrb.tile_release_id
		JOIN course_sets cset ON cset.id = mrb.course_set_id
		JOIN course_variants cv ON cv.id = mrb.course_variant_id
		WHERE mrb.runtime_binding_public_id = $1
		LIMIT 1
	`, publicID)
	return scanMapRuntimeBinding(row)
}

func (s *Store) ListMapAssetLinkedEvents(ctx context.Context, mapAssetID string, limit int) ([]MapAssetLinkedEvent, error) {
	if limit <= 0 || limit > 200 {
		limit = 50
	}
	rows, err := s.pool.Query(ctx, `
		SELECT
			e.event_public_id,
			e.display_name,
			e.summary,
			e.status,
			COALESCE(e.is_default_experience, false),
			COALESCE(e.show_in_event_list, true),
			er.release_public_id,
			er.config_label,
			er.route_code,
			ep.presentation_public_id,
			ep.name,
			cb.content_bundle_public_id,
			cb.name
		FROM events e
		JOIN map_runtime_bindings mrb ON mrb.id = e.current_runtime_binding_id
		LEFT JOIN event_releases er ON er.id = e.current_release_id
		LEFT JOIN event_presentations ep ON ep.id = e.current_presentation_id
		LEFT JOIN content_bundles cb ON cb.id = e.current_content_bundle_id
		WHERE mrb.map_asset_id = $1
		ORDER BY COALESCE(e.is_default_experience, false) DESC, e.display_name ASC
		LIMIT $2
	`, mapAssetID, limit)
	if err != nil {
		return nil, fmt.Errorf("list map asset linked events: %w", err)
	}
	defer rows.Close()
	items := []MapAssetLinkedEvent{}
	for rows.Next() {
		var item MapAssetLinkedEvent
		if err := rows.Scan(
			&item.EventPublicID,
			&item.DisplayName,
			&item.Summary,
			&item.Status,
			&item.IsDefaultExperience,
			&item.ShowInEventList,
			&item.CurrentReleasePublicID,
			&item.ConfigLabel,
			&item.RouteCode,
			&item.CurrentPresentationID,
			&item.CurrentPresentationName,
			&item.CurrentContentBundleID,
			&item.CurrentContentBundleName,
		); err != nil {
			return nil, fmt.Errorf("scan map asset linked event: %w", err)
		}
		items = append(items, item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate map asset linked events: %w", err)
	}
	return items, nil
}

func (s *Store) CreateMapRuntimeBinding(ctx context.Context, tx Tx, params CreateMapRuntimeBindingParams) (*MapRuntimeBinding, error) {
	row := tx.QueryRow(ctx, `
		INSERT INTO map_runtime_bindings (
			runtime_binding_public_id, event_id, place_id, map_asset_id, tile_release_id, course_set_id, course_variant_id, status, notes
		)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
		RETURNING id, runtime_binding_public_id, event_id, ''::text, place_id, ''::text, map_asset_id, ''::text,
		          tile_release_id, ''::text, course_set_id, ''::text, course_variant_id, ''::text,
		          status, notes, created_at, updated_at
	`, params.PublicID, params.EventID, params.PlaceID, params.MapAssetID, params.TileReleaseID, params.CourseSetID, params.CourseVariantID, params.Status, params.Notes)
	return scanMapRuntimeBinding(row)
}

func scanPlace(row pgx.Row) (*Place, error) {
	var item Place
	var centerPoint string
	err := row.Scan(&item.ID, &item.PublicID, &item.Code, &item.Name, &item.Region, &item.CoverURL, &item.Description, &centerPoint, &item.Status, &item.CreatedAt, &item.UpdatedAt)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("scan place: %w", err)
	}
	item.CenterPoint = json.RawMessage(centerPoint)
	return &item, nil
}

func scanPlaceFromRows(rows pgx.Rows) (*Place, error) {
	var item Place
	var centerPoint string
	err := rows.Scan(&item.ID, &item.PublicID, &item.Code, &item.Name, &item.Region, &item.CoverURL, &item.Description, &centerPoint, &item.Status, &item.CreatedAt, &item.UpdatedAt)
	if err != nil {
		return nil, fmt.Errorf("scan place row: %w", err)
	}
	item.CenterPoint = json.RawMessage(centerPoint)
	return &item, nil
}

func scanMapAsset(row pgx.Row) (*MapAsset, error) {
	var item MapAsset
	err := row.Scan(&item.ID, &item.PublicID, &item.PlaceID, &item.PlacePublicID, &item.PlaceName, &item.LegacyMapID, &item.LegacyMapPublicID, &item.Code, &item.Name, &item.MapType, &item.CoverURL, &item.Description, &item.Status, &item.CurrentTileReleaseID, &item.CreatedAt, &item.UpdatedAt)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("scan map asset: %w", err)
	}
	return &item, nil
}

func scanMapAssetFromRows(rows pgx.Rows) (*MapAsset, error) {
	var item MapAsset
	err := rows.Scan(&item.ID, &item.PublicID, &item.PlaceID, &item.PlacePublicID, &item.PlaceName, &item.LegacyMapID, &item.LegacyMapPublicID, &item.Code, &item.Name, &item.MapType, &item.CoverURL, &item.Description, &item.Status, &item.CurrentTileReleaseID, &item.CreatedAt, &item.UpdatedAt)
	if err != nil {
		return nil, fmt.Errorf("scan map asset row: %w", err)
	}
	return &item, nil
}

func scanTileRelease(row pgx.Row) (*TileRelease, error) {
	var item TileRelease
	var metadataJSON string
	err := row.Scan(&item.ID, &item.PublicID, &item.MapAssetID, &item.LegacyMapVersionID, &item.LegacyMapVersionPub, &item.VersionCode, &item.Status, &item.TileBaseURL, &item.MetaURL, &item.PublishedAssetRoot, &metadataJSON, &item.PublishedAt, &item.CreatedAt, &item.UpdatedAt)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("scan tile release: %w", err)
	}
	item.MetadataJSON = json.RawMessage(metadataJSON)
	return &item, nil
}

func scanTileReleaseFromRows(rows pgx.Rows) (*TileRelease, error) {
	var item TileRelease
	var metadataJSON string
	err := rows.Scan(&item.ID, &item.PublicID, &item.MapAssetID, &item.LegacyMapVersionID, &item.LegacyMapVersionPub, &item.VersionCode, &item.Status, &item.TileBaseURL, &item.MetaURL, &item.PublishedAssetRoot, &metadataJSON, &item.PublishedAt, &item.CreatedAt, &item.UpdatedAt)
	if err != nil {
		return nil, fmt.Errorf("scan tile release row: %w", err)
	}
	item.MetadataJSON = json.RawMessage(metadataJSON)
	return &item, nil
}

func scanCourseSource(row pgx.Row) (*CourseSource, error) {
	var item CourseSource
	var metadataJSON string
	err := row.Scan(&item.ID, &item.PublicID, &item.LegacyPlayfieldVersionID, &item.LegacyPlayfieldVersionPub, &item.SourceType, &item.FileURL, &item.Checksum, &item.ParserVersion, &item.ImportStatus, &metadataJSON, &item.ImportedAt, &item.CreatedAt, &item.UpdatedAt)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("scan course source: %w", err)
	}
	item.MetadataJSON = json.RawMessage(metadataJSON)
	return &item, nil
}

func scanCourseSourceFromRows(rows pgx.Rows) (*CourseSource, error) {
	var item CourseSource
	var metadataJSON string
	err := rows.Scan(&item.ID, &item.PublicID, &item.LegacyPlayfieldVersionID, &item.LegacyPlayfieldVersionPub, &item.SourceType, &item.FileURL, &item.Checksum, &item.ParserVersion, &item.ImportStatus, &metadataJSON, &item.ImportedAt, &item.CreatedAt, &item.UpdatedAt)
	if err != nil {
		return nil, fmt.Errorf("scan course source row: %w", err)
	}
	item.MetadataJSON = json.RawMessage(metadataJSON)
	return &item, nil
}

func scanCourseSet(row pgx.Row) (*CourseSet, error) {
	var item CourseSet
	err := row.Scan(&item.ID, &item.PublicID, &item.PlaceID, &item.MapAssetID, &item.Code, &item.Mode, &item.Name, &item.Description, &item.Status, &item.CurrentVariantID, &item.CreatedAt, &item.UpdatedAt)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("scan course set: %w", err)
	}
	return &item, nil
}

func scanCourseSetFromRows(rows pgx.Rows) (*CourseSet, error) {
	var item CourseSet
	err := rows.Scan(&item.ID, &item.PublicID, &item.PlaceID, &item.MapAssetID, &item.Code, &item.Mode, &item.Name, &item.Description, &item.Status, &item.CurrentVariantID, &item.CreatedAt, &item.UpdatedAt)
	if err != nil {
		return nil, fmt.Errorf("scan course set row: %w", err)
	}
	return &item, nil
}

func scanCourseVariant(row pgx.Row) (*CourseVariant, error) {
	var item CourseVariant
	var configPatch string
	var metadataJSON string
	err := row.Scan(&item.ID, &item.PublicID, &item.CourseSetID, &item.SourceID, &item.SourcePublicID, &item.Name, &item.RouteCode, &item.Mode, &item.ControlCount, &item.Difficulty, &item.Status, &item.IsDefault, &configPatch, &metadataJSON, &item.CreatedAt, &item.UpdatedAt)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("scan course variant: %w", err)
	}
	item.ConfigPatch = json.RawMessage(configPatch)
	item.MetadataJSON = json.RawMessage(metadataJSON)
	return &item, nil
}

func scanCourseVariantFromRows(rows pgx.Rows) (*CourseVariant, error) {
	var item CourseVariant
	var configPatch string
	var metadataJSON string
	err := rows.Scan(&item.ID, &item.PublicID, &item.CourseSetID, &item.SourceID, &item.SourcePublicID, &item.Name, &item.RouteCode, &item.Mode, &item.ControlCount, &item.Difficulty, &item.Status, &item.IsDefault, &configPatch, &metadataJSON, &item.CreatedAt, &item.UpdatedAt)
	if err != nil {
		return nil, fmt.Errorf("scan course variant row: %w", err)
	}
	item.ConfigPatch = json.RawMessage(configPatch)
	item.MetadataJSON = json.RawMessage(metadataJSON)
	return &item, nil
}

func scanMapRuntimeBinding(row pgx.Row) (*MapRuntimeBinding, error) {
	var item MapRuntimeBinding
	err := row.Scan(&item.ID, &item.PublicID, &item.EventID, &item.EventPublicID, &item.PlaceID, &item.PlacePublicID, &item.MapAssetID, &item.MapAssetPublicID, &item.TileReleaseID, &item.TileReleasePublicID, &item.CourseSetID, &item.CourseSetPublicID, &item.CourseVariantID, &item.CourseVariantPublicID, &item.Status, &item.Notes, &item.CreatedAt, &item.UpdatedAt)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("scan runtime binding: %w", err)
	}
	return &item, nil
}

func scanMapRuntimeBindingFromRows(rows pgx.Rows) (*MapRuntimeBinding, error) {
	var item MapRuntimeBinding
	err := rows.Scan(&item.ID, &item.PublicID, &item.EventID, &item.EventPublicID, &item.PlaceID, &item.PlacePublicID, &item.MapAssetID, &item.MapAssetPublicID, &item.TileReleaseID, &item.TileReleasePublicID, &item.CourseSetID, &item.CourseSetPublicID, &item.CourseVariantID, &item.CourseVariantPublicID, &item.Status, &item.Notes, &item.CreatedAt, &item.UpdatedAt)
	if err != nil {
		return nil, fmt.Errorf("scan runtime binding row: %w", err)
	}
	return &item, nil
}