sorarebuddy/db/game.go

package db

import (
	"context"
	"time"

	"github.com/uptrace/bun"

	"git.lehouerou.net/laurent/sorarebuddy/model"
)

type GameRepository struct {
	*Repository[model.Game]
}

func NewGameRepository(db *bun.DB) *GameRepository {
	return &GameRepository{
		Repository: NewRepository[model.Game](db, []string{"id"}),
	}
}

func (r *GameRepository) GetByFixture(ctx context.Context, fixtureSlug string) ([]model.Game, error) {
	var games []model.Game
	err := r.db.NewSelect().
		Model(&games).
		Relation("HomeTeam").
		Relation("AwayTeam").
		Relation("WinnerTeam").
		Relation("Competition").
		Relation("Competition.Country").
		Relation("Competition.Zone").
		Relation("GamePlayers").
		Relation("GamePlayers.Player").
		Relation("GamePlayers.Score").
		Where("fixture_slug = ?", fixtureSlug).
		Scan(ctx)
	return games, err
}

func (r *GameRepository) GetByIds(ctx context.Context, ids []string) ([]model.Game, error) {
	var games []model.Game
	err := r.db.NewSelect().
		Model(&games).
		Relation("HomeTeam").
		Relation("AwayTeam").
		Relation("WinnerTeam").
		Relation("Competition").
		Relation("Competition.Country").
		Relation("Competition.Zone").
		Relation("GamePlayers").
		Relation("GamePlayers.Player").
		Relation("GamePlayers.Score").
		Where("id IN (?)", bun.In(ids)).
		Scan(ctx)
	return games, err
}

func (r *GameRepository) CurrentlyPlayingGames(ctx context.Context) ([]model.Game, error) {
	var games []model.Game
	if err := r.db.NewSelect().
		Model(&games).
		Relation("HomeTeam").
		Relation("AwayTeam").
		Relation("WinnerTeam").
		Relation("Competition").
		Relation("Competition.Country").
		Relation("Competition.Zone").
		Relation("GamePlayers").
		Relation("GamePlayers.Player").
		Relation("GamePlayers.Score").
		Join("INNER JOIN fixtures AS f ON f.slug = game.fixture_slug").
		Where("f.state = 'started' AND (game.status = 'playing' OR (game.status = 'played' AND game.date > now() - interval '3 hours') OR (game.status = 'scheduled' AND game.date < now() + interval '1 hour'))").
		OrderExpr("game.date ASC").
		Scan(ctx); err != nil {
		return nil, err
	}
	return games, nil
}

func (r *GameRepository) GetFutureGameIdsWithoutFormation(
	ctx context.Context,
	startingIn time.Duration,
	minPlayers int,
) ([]string, error) {
	var gameIds []string
	subQueryHome := r.db.NewSelect().
		ColumnExpr("COUNT(*)").
		Model((*model.GamePlayer)(nil)).
		Where("game_id = game.id").
		Where("team_slug = game.home_team_slug")

	subQueryAway := r.db.NewSelect().
		ColumnExpr("COUNT(*)").
		Model((*model.GamePlayer)(nil)).
		Where("game_id = game.id").
		Where("team_slug = game.away_team_slug")

	if err := r.db.NewSelect().
		Model((*model.Game)(nil)).
		Column("id").
		Where("date > ?", time.Now().Add(-3*time.Hour)).
		Where("date < ?", time.Now().Add(startingIn)).
		Where(`(?) < ? OR (?) < ?`,
			subQueryHome, minPlayers,
			subQueryAway, minPlayers).
		Scan(ctx, &gameIds); err != nil {
		return nil, err
	}
	return gameIds, nil
}