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

package postgres

import (
	"context"
	"fmt"
	"time"
)

type Card struct {
	ID                          string
	PublicID                    string
	CardType                    string
	Title                       string
	Subtitle                    *string
	CoverURL                    *string
	DisplaySlot                 string
	DisplayPriority             int
	IsDefaultExperience         bool
	StartsAt                    *time.Time
	EndsAt                      *time.Time
	EntryChannelID              *string
	EventPublicID               *string
	EventDisplayName            *string
	EventSummary                *string
	EventStatus                 *string
	EventCurrentReleasePubID    *string
	EventConfigLabel            *string
	EventRouteCode              *string
	EventReleasePayloadJSON     *string
	EventRuntimeBindingID       *string
	EventPresentationID         *string
	EventPresentationName       *string
	EventPresentationType       *string
	EventPresentationSchemaJSON *string
	EventContentBundleID        *string
	EventContentBundleName      *string
	EventContentEntryURL        *string
	EventContentAssetRootURL    *string
	EventContentMetadataJSON    *string
	HTMLURL                     *string
}

func (s *Store) ListCardsForEntry(ctx context.Context, tenantID string, entryChannelID *string, slot string, now time.Time, limit int) ([]Card, error) {
	if limit <= 0 || limit > 100 {
		limit = 20
	}
	if slot == "" {
		slot = "home_primary"
	}

	rows, err := s.pool.Query(ctx, `
		SELECT
			c.id,
			c.card_public_id,
			c.card_type,
			c.title,
			c.subtitle,
			c.cover_url,
			c.display_slot,
			c.display_priority,
			c.is_default_experience,
			c.starts_at,
			c.ends_at,
			c.entry_channel_id,
			e.event_public_id,
			e.display_name,
			e.summary,
			e.status,
			er.release_public_id,
			er.config_label,
			er.route_code,
			er.payload_jsonb::text,
			mrb.runtime_binding_public_id,
			ep.presentation_public_id,
			ep.name,
			ep.presentation_type,
			ep.schema_jsonb::text,
			cb.content_bundle_public_id,
			cb.name,
			cb.entry_url,
			cb.asset_root_url,
			cb.metadata_jsonb::text,
			c.html_url
		FROM cards c
		LEFT JOIN events e ON e.id = c.event_id
		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 event_presentations ep ON ep.id = er.presentation_id
		LEFT JOIN content_bundles cb ON cb.id = er.content_bundle_id
		WHERE c.tenant_id = $1
		  AND ($2::uuid IS NULL OR c.entry_channel_id = $2 OR c.entry_channel_id IS NULL)
		  AND c.display_slot = $3
		  AND c.status = 'active'
		  AND (c.starts_at IS NULL OR c.starts_at <= $4)
		  AND (c.ends_at IS NULL OR c.ends_at >= $4)
		ORDER BY
		  CASE WHEN $2::uuid IS NOT NULL AND c.entry_channel_id = $2 THEN 0 ELSE 1 END,
		  c.display_priority DESC,
		  c.created_at ASC
		LIMIT $5
	`, tenantID, entryChannelID, slot, now, limit)
	if err != nil {
		return nil, fmt.Errorf("list cards for entry: %w", err)
	}
	defer rows.Close()

	var cards []Card
	for rows.Next() {
		var card Card
		if err := rows.Scan(
			&card.ID,
			&card.PublicID,
			&card.CardType,
			&card.Title,
			&card.Subtitle,
			&card.CoverURL,
			&card.DisplaySlot,
			&card.DisplayPriority,
			&card.IsDefaultExperience,
			&card.StartsAt,
			&card.EndsAt,
			&card.EntryChannelID,
			&card.EventPublicID,
			&card.EventDisplayName,
			&card.EventSummary,
			&card.EventStatus,
			&card.EventCurrentReleasePubID,
			&card.EventConfigLabel,
			&card.EventRouteCode,
			&card.EventReleasePayloadJSON,
			&card.EventRuntimeBindingID,
			&card.EventPresentationID,
			&card.EventPresentationName,
			&card.EventPresentationType,
			&card.EventPresentationSchemaJSON,
			&card.EventContentBundleID,
			&card.EventContentBundleName,
			&card.EventContentEntryURL,
			&card.EventContentAssetRootURL,
			&card.EventContentMetadataJSON,
			&card.HTMLURL,
		); err != nil {
			return nil, fmt.Errorf("scan card: %w", err)
		}
		cards = append(cards, card)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate cards: %w", err)
	}
	return cards, nil
}