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

package postgres

import (
	"context"
	"errors"
	"fmt"

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

type EventPresentation struct {
	ID               string
	PublicID         string
	EventID          string
	EventPublicID    string
	Code             string
	Name             string
	PresentationType string
	Status           string
	IsDefault        bool
	SchemaJSON       string
	CreatedAt        string
	UpdatedAt        string
}

type ContentBundle struct {
	ID            string
	PublicID      string
	EventID       string
	EventPublicID string
	Code          string
	Name          string
	Status        string
	IsDefault     bool
	EntryURL      *string
	AssetRootURL  *string
	MetadataJSON  string
	CreatedAt     string
	UpdatedAt     string
}

type CreateEventPresentationParams struct {
	PublicID         string
	EventID          string
	Code             string
	Name             string
	PresentationType string
	Status           string
	IsDefault        bool
	SchemaJSON       string
}

type CreateContentBundleParams struct {
	PublicID     string
	EventID      string
	Code         string
	Name         string
	Status       string
	IsDefault    bool
	EntryURL     *string
	AssetRootURL *string
	MetadataJSON string
}

type EventDefaultBindings struct {
	EventID                string
	EventPublicID          string
	PresentationID         *string
	PresentationPublicID   *string
	PresentationName       *string
	PresentationType       *string
	ContentBundleID        *string
	ContentBundlePublicID  *string
	ContentBundleName      *string
	ContentEntryURL        *string
	ContentAssetRootURL    *string
	RuntimeBindingID       *string
	RuntimeBindingPublicID *string
	PlacePublicID          *string
	PlaceName              *string
	MapAssetPublicID       *string
	MapAssetName           *string
	TileReleasePublicID    *string
	CourseSetPublicID      *string
	CourseVariantPublicID  *string
	CourseVariantName      *string
	RuntimeRouteCode       *string
}

type SetEventDefaultBindingsParams struct {
	EventID            string
	PresentationID     *string
	ContentBundleID    *string
	RuntimeBindingID   *string
	UpdatePresentation bool
	UpdateContent      bool
	UpdateRuntime      bool
}

func (s *Store) ListEventPresentationsByEventID(ctx context.Context, eventID string, limit int) ([]EventPresentation, error) {
	if limit <= 0 || limit > 200 {
		limit = 50
	}
	rows, err := s.pool.Query(ctx, `
		SELECT
			ep.id,
			ep.presentation_public_id,
			ep.event_id,
			e.event_public_id,
			ep.code,
			ep.name,
			ep.presentation_type,
			ep.status,
			ep.is_default,
			ep.schema_jsonb::text,
			ep.created_at::text,
			ep.updated_at::text
		FROM event_presentations ep
		JOIN events e ON e.id = ep.event_id
		WHERE ep.event_id = $1
		ORDER BY ep.is_default DESC, ep.created_at DESC
		LIMIT $2
	`, eventID, limit)
	if err != nil {
		return nil, fmt.Errorf("list event presentations: %w", err)
	}
	defer rows.Close()

	items := []EventPresentation{}
	for rows.Next() {
		item, err := scanEventPresentationFromRows(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, *item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate event presentations: %w", err)
	}
	return items, nil
}

func (s *Store) GetEventPresentationByPublicID(ctx context.Context, publicID string) (*EventPresentation, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT
			ep.id,
			ep.presentation_public_id,
			ep.event_id,
			e.event_public_id,
			ep.code,
			ep.name,
			ep.presentation_type,
			ep.status,
			ep.is_default,
			ep.schema_jsonb::text,
			ep.created_at::text,
			ep.updated_at::text
		FROM event_presentations ep
		JOIN events e ON e.id = ep.event_id
		WHERE ep.presentation_public_id = $1
		LIMIT 1
	`, publicID)
	return scanEventPresentation(row)
}

func (s *Store) GetDefaultEventPresentationByEventID(ctx context.Context, eventID string) (*EventPresentation, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT
			ep.id,
			ep.presentation_public_id,
			ep.event_id,
			e.event_public_id,
			ep.code,
			ep.name,
			ep.presentation_type,
			ep.status,
			ep.is_default,
			ep.schema_jsonb::text,
			ep.created_at::text,
			ep.updated_at::text
		FROM event_presentations ep
		JOIN events e ON e.id = ep.event_id
		WHERE ep.event_id = $1
		  AND ep.status = 'active'
		ORDER BY ep.is_default DESC, ep.updated_at DESC, ep.created_at DESC
		LIMIT 1
	`, eventID)
	return scanEventPresentation(row)
}

func (s *Store) CreateEventPresentation(ctx context.Context, tx Tx, params CreateEventPresentationParams) (*EventPresentation, error) {
	row := tx.QueryRow(ctx, `
		INSERT INTO event_presentations (
			presentation_public_id,
			event_id,
			code,
			name,
			presentation_type,
			status,
			is_default,
			schema_jsonb
		)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8::jsonb)
		RETURNING
			id,
			presentation_public_id,
			event_id,
			code,
			name,
			presentation_type,
			status,
			is_default,
			schema_jsonb::text,
			created_at::text,
			updated_at::text
	`, params.PublicID, params.EventID, params.Code, params.Name, params.PresentationType, params.Status, params.IsDefault, params.SchemaJSON)

	var item EventPresentation
	if err := row.Scan(
		&item.ID,
		&item.PublicID,
		&item.EventID,
		&item.Code,
		&item.Name,
		&item.PresentationType,
		&item.Status,
		&item.IsDefault,
		&item.SchemaJSON,
		&item.CreatedAt,
		&item.UpdatedAt,
	); err != nil {
		return nil, fmt.Errorf("create event presentation: %w", err)
	}
	return &item, nil
}

func (s *Store) GetEventDefaultBindingsByEventID(ctx context.Context, eventID string) (*EventDefaultBindings, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT
			e.id,
			e.event_public_id,
			e.current_presentation_id,
			ep.presentation_public_id,
			ep.name,
			ep.presentation_type,
			e.current_content_bundle_id,
			cb.content_bundle_public_id,
			cb.name,
			cb.entry_url,
			cb.asset_root_url,
			e.current_runtime_binding_id,
			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
		FROM events e
		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
		LEFT JOIN map_runtime_bindings mrb ON mrb.id = e.current_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
		WHERE e.id = $1
		LIMIT 1
	`, eventID)
	return scanEventDefaultBindings(row)
}

func (s *Store) SetEventDefaultBindings(ctx context.Context, tx Tx, params SetEventDefaultBindingsParams) error {
	if _, err := tx.Exec(ctx, `
		UPDATE events
		SET current_presentation_id = CASE WHEN $5 THEN $2 ELSE current_presentation_id END,
		    current_content_bundle_id = CASE WHEN $6 THEN $3 ELSE current_content_bundle_id END,
		    current_runtime_binding_id = CASE WHEN $7 THEN $4 ELSE current_runtime_binding_id END
		WHERE id = $1
	`, params.EventID, params.PresentationID, params.ContentBundleID, params.RuntimeBindingID, params.UpdatePresentation, params.UpdateContent, params.UpdateRuntime); err != nil {
		return fmt.Errorf("set event default bindings: %w", err)
	}
	return nil
}

func (s *Store) ListContentBundlesByEventID(ctx context.Context, eventID string, limit int) ([]ContentBundle, error) {
	if limit <= 0 || limit > 200 {
		limit = 50
	}
	rows, err := s.pool.Query(ctx, `
		SELECT
			cb.id,
			cb.content_bundle_public_id,
			cb.event_id,
			e.event_public_id,
			cb.code,
			cb.name,
			cb.status,
			cb.is_default,
			cb.entry_url,
			cb.asset_root_url,
			cb.metadata_jsonb::text,
			cb.created_at::text,
			cb.updated_at::text
		FROM content_bundles cb
		JOIN events e ON e.id = cb.event_id
		WHERE cb.event_id = $1
		ORDER BY cb.is_default DESC, cb.created_at DESC
		LIMIT $2
	`, eventID, limit)
	if err != nil {
		return nil, fmt.Errorf("list content bundles: %w", err)
	}
	defer rows.Close()

	items := []ContentBundle{}
	for rows.Next() {
		item, err := scanContentBundleFromRows(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, *item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate content bundles: %w", err)
	}
	return items, nil
}

func (s *Store) GetContentBundleByPublicID(ctx context.Context, publicID string) (*ContentBundle, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT
			cb.id,
			cb.content_bundle_public_id,
			cb.event_id,
			e.event_public_id,
			cb.code,
			cb.name,
			cb.status,
			cb.is_default,
			cb.entry_url,
			cb.asset_root_url,
			cb.metadata_jsonb::text,
			cb.created_at::text,
			cb.updated_at::text
		FROM content_bundles cb
		JOIN events e ON e.id = cb.event_id
		WHERE cb.content_bundle_public_id = $1
		LIMIT 1
	`, publicID)
	return scanContentBundle(row)
}

func (s *Store) GetDefaultContentBundleByEventID(ctx context.Context, eventID string) (*ContentBundle, error) {
	row := s.pool.QueryRow(ctx, `
		SELECT
			cb.id,
			cb.content_bundle_public_id,
			cb.event_id,
			e.event_public_id,
			cb.code,
			cb.name,
			cb.status,
			cb.is_default,
			cb.entry_url,
			cb.asset_root_url,
			cb.metadata_jsonb::text,
			cb.created_at::text,
			cb.updated_at::text
		FROM content_bundles cb
		JOIN events e ON e.id = cb.event_id
		WHERE cb.event_id = $1
		  AND cb.status = 'active'
		ORDER BY cb.is_default DESC, cb.updated_at DESC, cb.created_at DESC
		LIMIT 1
	`, eventID)
	return scanContentBundle(row)
}

func (s *Store) CreateContentBundle(ctx context.Context, tx Tx, params CreateContentBundleParams) (*ContentBundle, error) {
	row := tx.QueryRow(ctx, `
		INSERT INTO content_bundles (
			content_bundle_public_id,
			event_id,
			code,
			name,
			status,
			is_default,
			entry_url,
			asset_root_url,
			metadata_jsonb
		)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9::jsonb)
		RETURNING
			id,
			content_bundle_public_id,
			event_id,
			code,
			name,
			status,
			is_default,
			entry_url,
			asset_root_url,
			metadata_jsonb::text,
			created_at::text,
			updated_at::text
	`, params.PublicID, params.EventID, params.Code, params.Name, params.Status, params.IsDefault, params.EntryURL, params.AssetRootURL, params.MetadataJSON)

	var item ContentBundle
	if err := row.Scan(
		&item.ID,
		&item.PublicID,
		&item.EventID,
		&item.Code,
		&item.Name,
		&item.Status,
		&item.IsDefault,
		&item.EntryURL,
		&item.AssetRootURL,
		&item.MetadataJSON,
		&item.CreatedAt,
		&item.UpdatedAt,
	); err != nil {
		return nil, fmt.Errorf("create content bundle: %w", err)
	}
	return &item, nil
}

func scanEventPresentation(row pgx.Row) (*EventPresentation, error) {
	var item EventPresentation
	err := row.Scan(
		&item.ID,
		&item.PublicID,
		&item.EventID,
		&item.EventPublicID,
		&item.Code,
		&item.Name,
		&item.PresentationType,
		&item.Status,
		&item.IsDefault,
		&item.SchemaJSON,
		&item.CreatedAt,
		&item.UpdatedAt,
	)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("scan event presentation: %w", err)
	}
	return &item, nil
}

func scanEventPresentationFromRows(rows pgx.Rows) (*EventPresentation, error) {
	var item EventPresentation
	if err := rows.Scan(
		&item.ID,
		&item.PublicID,
		&item.EventID,
		&item.EventPublicID,
		&item.Code,
		&item.Name,
		&item.PresentationType,
		&item.Status,
		&item.IsDefault,
		&item.SchemaJSON,
		&item.CreatedAt,
		&item.UpdatedAt,
	); err != nil {
		return nil, fmt.Errorf("scan event presentation row: %w", err)
	}
	return &item, nil
}

func scanContentBundle(row pgx.Row) (*ContentBundle, error) {
	var item ContentBundle
	err := row.Scan(
		&item.ID,
		&item.PublicID,
		&item.EventID,
		&item.EventPublicID,
		&item.Code,
		&item.Name,
		&item.Status,
		&item.IsDefault,
		&item.EntryURL,
		&item.AssetRootURL,
		&item.MetadataJSON,
		&item.CreatedAt,
		&item.UpdatedAt,
	)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("scan content bundle: %w", err)
	}
	return &item, nil
}

func scanContentBundleFromRows(rows pgx.Rows) (*ContentBundle, error) {
	var item ContentBundle
	if err := rows.Scan(
		&item.ID,
		&item.PublicID,
		&item.EventID,
		&item.EventPublicID,
		&item.Code,
		&item.Name,
		&item.Status,
		&item.IsDefault,
		&item.EntryURL,
		&item.AssetRootURL,
		&item.MetadataJSON,
		&item.CreatedAt,
		&item.UpdatedAt,
	); err != nil {
		return nil, fmt.Errorf("scan content bundle row: %w", err)
	}
	return &item, nil
}

func scanEventDefaultBindings(row pgx.Row) (*EventDefaultBindings, error) {
	var item EventDefaultBindings
	err := row.Scan(
		&item.EventID,
		&item.EventPublicID,
		&item.PresentationID,
		&item.PresentationPublicID,
		&item.PresentationName,
		&item.PresentationType,
		&item.ContentBundleID,
		&item.ContentBundlePublicID,
		&item.ContentBundleName,
		&item.ContentEntryURL,
		&item.ContentAssetRootURL,
		&item.RuntimeBindingID,
		&item.RuntimeBindingPublicID,
		&item.PlacePublicID,
		&item.PlaceName,
		&item.MapAssetPublicID,
		&item.MapAssetName,
		&item.TileReleasePublicID,
		&item.CourseSetPublicID,
		&item.CourseVariantPublicID,
		&item.CourseVariantName,
		&item.RuntimeRouteCode,
	)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("scan event default bindings: %w", err)
	}
	return &item, nil
}