CosmicClash/server/store/ranked_profile_sql.go

package store

import (
	"context"
	"database/sql"
	"fmt"

	"github.com/cosmic-clash/cosmic-clash/server/domain"
)

const RankedProfileSelectSQL = `SELECT rating, deviation, volatility, ranked_games, updated_at,
    COALESCE((SELECT season_id FROM seasons
              WHERE playlist = 'ranked' AND starts_at <= CURRENT_TIMESTAMP AND ends_at > CURRENT_TIMESTAMP
              ORDER BY starts_at DESC, season_id DESC LIMIT 1), ''),
    COALESCE((SELECT ends_at FROM seasons
              WHERE playlist = 'ranked' AND starts_at <= CURRENT_TIMESTAMP AND ends_at > CURRENT_TIMESTAMP
              ORDER BY starts_at DESC, season_id DESC LIMIT 1), TIMESTAMP 'epoch')
FROM ratings
WHERE player_id = $1`

// PostgresRankedProfiles reads the durable rating row api.Service's
// RankedProfileProvider needs. A missing row means "this player has no
// ranked profile yet" (never queued ranked, or their identity predates any
// result) -- that's a real, expected state, not an error, and is reported
// the same way the in-memory RankedProfiles map api.Service still falls
// back to already did: (zero value, false, nil).
//
// LastSeasonID and SeasonHistory remain zero-valued because they describe
// rollover history, while CurrentSeasonID is derived from the active ranked
// season row. Keeping those concepts separate prevents the profile endpoint
// from making a current season look already rolled over to maintenance.
type PostgresRankedProfiles struct{ DB *sql.DB }

func (p PostgresRankedProfiles) Get(ctx context.Context, playerID string) (domain.RankedProfile, bool, error) {
	if p.DB == nil || playerID == "" {
		return domain.RankedProfile{}, false, fmt.Errorf("invalid ranked profile lookup")
	}
	var profile domain.RankedProfile
	err := p.DB.QueryRowContext(ctx, RankedProfileSelectSQL, playerID).
		Scan(&profile.Value, &profile.RD, &profile.Volatility, &profile.RankedGames, &profile.LastRatedAt, &profile.CurrentSeasonID, &profile.CurrentSeasonEndsAt)
	if err == sql.ErrNoRows {
		return domain.RankedProfile{}, false, nil
	}
	if err != nil {
		return domain.RankedProfile{}, false, err
	}
	return profile, true, nil
}