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
	EntryChannelID   *string
	EventPublicID    *string
	EventDisplayName *string
	EventSummary     *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.entry_channel_id,
			e.event_public_id,
			e.display_name,
			e.summary,
			c.html_url
		FROM cards c
		LEFT JOIN events e ON e.id = c.event_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.EntryChannelID,
			&card.EventPublicID,
			&card.EventDisplayName,
			&card.EventSummary,
			&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
}