package db import ( "context" "time" "git.lehouerou.net/laurent/sorare/types" "github.com/pkg/errors" "github.com/samber/lo" "github.com/shopspring/decimal" "github.com/uptrace/bun" "git.lehouerou.net/laurent/sorarebuddy/model" ) type PlayerRepository struct { *Repository[model.Player] } func NewPlayerRepository(db *bun.DB) *PlayerRepository { return &PlayerRepository{ Repository: NewRepository[model.Player](db, []string{"slug"}), } } func (r *PlayerRepository) SearchByDisplayName( ctx context.Context, displayName string, limit int, ) ([]model.Player, error) { var players []model.Player err := r.db.NewSelect(). Model(&players). Relation("Team"). Where("f_unaccent(player.display_name) ILIKE ?", "%"+displayName+"%"). Limit(limit). Scan(ctx) return players, err } func (r *PlayerRepository) GetOne(ctx context.Context, slug string) (model.Player, error) { var player model.Player err := r.db.NewSelect(). Model(&player). Where("player.slug = ?", slug). Relation("CardSupply"). Relation("ClubMembership"). Relation("ClubMembership.Team"). Relation("ClubMembership.Team.Country"). Relation("ClubMembership.Team.DomesticLeague"). Relation("ClubMembership.Team.DomesticLeague.Zone"). Relation("Country"). Relation("Team"). Relation("DomesticLeague"). Relation("DomesticLeague.Zone"). Scan(ctx) return player, err } func (r *PlayerRepository) GetMany(ctx context.Context, slugs ...string) ([]model.Player, error) { var players []model.Player err := r.db.NewSelect(). Model(&players). Where("player.slug IN (?)", bun.In(slugs)). Relation("CardSupply"). Relation("ClubMembership"). Relation("Country"). Relation("Team"). Relation("DomesticLeague"). Relation("DomesticLeague.Zone"). Scan(ctx) return players, err } func (r *PlayerRepository) GetAll(ctx context.Context) ([]model.Player, error) { var players []model.Player err := r.db.NewSelect(). Model(&players). Scan(ctx) return players, err } func (r *PlayerRepository) GetPlayerSlugsNotInDb(ctx context.Context, playerSlugs []string) ([]string, error) { var players []model.Player err := r.db.NewSelect(). Model(&players). Where("slug IN (?)", bun.In(playerSlugs)). Scan(ctx) if err != nil { return nil, errors.Wrap(err, "getting players not in db") } diff, _ := lo.Difference(playerSlugs, lo.Map(players, func(p model.Player, index int) string { return p.Slug })) return diff, nil } type SingleRankingsOptions struct { StartDate time.Time `json:"startDate"` Position types.Position `json:"position"` Competitions []string `json:"competitions"` Zones []int `json:"zones"` OnlyClubGames bool `json:"onlyClubGames"` OnlyStarting bool `json:"onlyStarting"` MinGameCount int `json:"minGameCount"` MinTeamGamesPlayedPercentage int `json:"minTeamGamesPlayedPercentage"` Order string `json:"order"` Limit int `json:"limit"` Rarity types.Rarity `json:"rarity"` U23 bool `json:"u23"` MinTotalMinutes int `json:"minTotalMinutes"` MinAge int `json:"minAge"` MaxAge int `json:"maxAge"` HasGameInNextGw bool `json:"hasGameInNextGw"` } type SingleRanking struct { PlayerSlug string `bun:"player_slug" json:"playerSlug"` TeamSlug string `bun:"team_slug" json:"teamSlug"` AvgTeamGoalsFor decimal.Decimal `bun:"avg_team_goals_for" json:"avgTeamGoalsFor"` AvgTeamGoalsAgainst decimal.Decimal `bun:"avg_team_goals_against" json:"avgTeamGoalsAgainst"` TotalAvgScore decimal.Decimal `bun:"total_avg_score" json:"totalAvgScore"` DecisiveAvgScore decimal.Decimal `bun:"decisive_avg_score" json:"decisiveAvgScore"` AllAroundAvgScore decimal.Decimal `bun:"all_around_avg_score" json:"allAroundAvgScore"` MinScore decimal.Decimal `bun:"min_score" json:"minScore"` MaxScore decimal.Decimal `bun:"max_score" json:"maxScore"` TotalStddevScore decimal.Decimal `bun:"total_stddev_score" json:"totalStddevScore"` GameUsedInStatsCount int `bun:"game_used_in_stats_count" json:"gameUsedInStatsCount"` GameStartedCount int `bun:"game_started_count" json:"gameStartedCount"` GameBenchedPlayedCount int `bun:"game_benched_played_count" json:"gameBenchedPlayedCount"` GameBenchedUnplayedCount int `bun:"game_benched_unplayed_count" json:"gameBenchedUnplayedCount"` TeamsGameCount int `bun:"teams_game_count" json:"teamsGameCount"` TotalPossibleMinutes int `bun:"total_possible_minutes" json:"totalPossibleMinutes"` PercentageMinutesPlayed decimal.Decimal `bun:"percentage_minutes_played" json:"percentageMinutesPlayed"` StackPlayPercentage decimal.Decimal `bun:"stack_play_percentage" json:"stackPlayPercentage"` Floor decimal.Decimal `bun:"floor" json:"floor"` MoreThan80Score int `bun:"more_than_80_score" json:"moreThan80Score"` MoreThan70Score int `bun:"more_than_70_score" json:"moreThan70Score"` MoreThan60Score int `bun:"more_than_60_score" json:"moreThan60Score"` MoreThan50Score int `bun:"more_than_50_score" json:"moreThan50Score"` DecisiveCount int `bun:"decisive_count" json:"decisiveCount"` MoreThan40AA int `bun:"more_than_40_aa" json:"moreThan40AA"` MoreThan30AA int `bun:"more_than_30_aa" json:"moreThan30AA"` MoreThan20AA int `bun:"more_than_20_aa" json:"moreThan20AA"` MoreThan10AA int `bun:"more_than_10_aa" json:"moreThan10AA"` MoreThan80ScorePercentage decimal.Decimal `bun:"more_than_80_score_percentage" json:"moreThan80ScorePercentage"` MoreThan70ScorePercentage decimal.Decimal `bun:"more_than_70_score_percentage" json:"moreThan70ScorePercentage"` MoreThan60ScorePercentage decimal.Decimal `bun:"more_than_60_score_percentage" json:"moreThan60ScorePercentage"` MoreThan50ScorePercentage decimal.Decimal `bun:"more_than_50_score_percentage" json:"moreThan50ScorePercentage"` DecisiveCountPercentage decimal.Decimal `bun:"decisive_percentage" json:"decisivePercentage"` MoreThan40AAPercentage decimal.Decimal `bun:"more_than_40_aa_percentage" json:"moreThan40AAPercentage"` MoreThan30AAPercentage decimal.Decimal `bun:"more_than_30_aa_percentage" json:"moreThan30AAPercentage"` MoreThan20AAPercentage decimal.Decimal `bun:"more_than_20_aa_percentage" json:"moreThan20AAPercentage"` MoreThan10AAPercentage decimal.Decimal `bun:"more_than_10_aa_percentage" json:"moreThan10AAPercentage"` TotalMinutes decimal.Decimal `bun:"total_minutes" json:"totalMinutes"` TotalAA decimal.Decimal `bun:"total_aa" json:"totalAA"` AAPerMin decimal.Decimal `bun:"aa_per_min" json:"aaPerMin"` DuelWonPer90Min decimal.Decimal `bun:"duel_won_per_90_min" json:"duelWonPer90Min"` EffectiveClearancePer90Min decimal.Decimal `bun:"effective_clearance_per_90_min" json:"effectiveClearancePer90Min"` ShotOnTargetPer90Min decimal.Decimal `bun:"shot_on_target_per_90_min" json:"shotOnTargetPer90Min"` AccuratePassPer90Min decimal.Decimal `bun:"accurate_pass_per_90_min" json:"accuratePassPer90Min"` WonContestPer90Min decimal.Decimal `bun:"won_contest_per_90_min" json:"wonContestPer90Min"` Scores []*decimal.Decimal `bun:"scores,array" json:"scores"` Player model.Player `bun:"-" json:"player"` } func CalculateCutoffBirthdate(age int) time.Time { // Get the current time. now := time.Now() // Subtract the age from the current year. // Also subtract 6 months to reverse the original SQL operation. birthYear := now.Year() - age if now.Month() < 7 { // If before July, subtract an additional year. birthYear-- } // Construct the birth date using the calculated birth year. // The month and day are set to the same as the current date for simplicity. // Adjust these values as needed for your specific requirements. birthDate := time.Date(birthYear, now.Month(), now.Day(), 0, 0, 0, 0, time.UTC) return birthDate } func (r *PlayerRepository) SingleRankings(ctx context.Context, opts SingleRankingsOptions) ([]SingleRanking, error) { minAgeBirthdate := CalculateCutoffBirthdate(opts.MinAge) maxAgeBirthdate := CalculateCutoffBirthdate(opts.MaxAge) // Return the list of team slugs for teams that have games in the currently opened game week. NextGWGameCountRequest := r.db.NewSelect(). ColumnExpr("t.slug"). TableExpr("teams AS t"). Join("JOIN games AS g ON g.away_team_slug = t.slug OR g.home_team_slug = t.slug"). Join("JOIN fixtures AS f ON f.slug = g.fixture_slug"). Where("f.state = 'opened'"). Group("t.slug") FilteredGames := r.db.NewSelect(). ColumnExpr("g.*"). TableExpr("games AS g"). Where("g.date >= ?", opts.StartDate) if opts.OnlyClubGames { FilteredGames.Join("JOIN competitions AS c ON c.slug = g.competition_slug") FilteredGames.Where("c.competition_type = 'CLUB'") } // Return for each player the sum of supplies over all seasons PlayersSupplies := r.db.NewSelect(). ColumnExpr("cs.player_slug"). ColumnExpr("SUM(cs.limited) AS limited"). ColumnExpr("SUM(cs.rare) AS rare"). ColumnExpr("SUM(cs.super_rare) AS super_rare"). ColumnExpr("SUM(cs.unique) AS unique"). TableExpr("card_supplies AS cs"). Group("player_slug") FilteredPlayers := r.db.NewSelect(). ColumnExpr("p.*"). TableExpr("players AS p"). Join("JOIN \"Supplies\" AS s ON s.player_slug = p.slug"). Join("JOIN competitions AS dc ON dc.slug = p.domestic_league_slug"). Where("p.field_position = ?", opts.Position). Where("p.birth_date >= ?", maxAgeBirthdate). Where("p.birth_date <= ?", minAgeBirthdate). Where("dc.zone_id IS NOT NULL") if opts.U23 { FilteredPlayers.Where("p.birth_date >= ?", CalculateCutoffBirthdate(23)) } if len(opts.Competitions) > 0 { FilteredPlayers.Where("dc.slug IN (?)", bun.In(opts.Competitions)) } if len(opts.Zones) > 0 { FilteredPlayers.Where("dc.zone_id IN (?)", bun.In(opts.Zones)) } if opts.HasGameInNextGw { FilteredPlayers.Join("JOIN \"NextGWGameCount\" AS ngc ON ngc.slug = p.team_slug") } PlayerPossibleGameCount := r.db.NewSelect(). ColumnExpr("p.slug"). ColumnExpr("COUNT(DISTINCT g.id) AS total_game_count"). ColumnExpr("SUM(g.minutes) AS total_minutes"). ColumnExpr("ARRAY_AGG(COALESCE(fs.score, -1) ORDER BY g.date DESC) AS scores"). ColumnExpr("COUNT(DISTINCT CASE WHEN gp.status = 'starting' THEN gp.game_id ELSE NULL END) AS game_started_count"). ColumnExpr("COUNT(DISTINCT CASE WHEN gp.status = 'bench' AND fs.minutes_played > 0 THEN gp.game_id ELSE NULL END) AS game_benched_played_count"). ColumnExpr("COUNT(DISTINCT CASE WHEN gp.status = 'bench' AND fs.minutes_played = 0 THEN gp.game_id ELSE NULL END) AS game_benched_unplayed_count"). ColumnExpr("SUM(fs.minutes_played) * 100 / SUM(g.minutes) AS percentage_minutes_played"). TableExpr("\"FilteredPlayers\" AS p"). Join("JOIN memberships AS m ON m.player_slug = p.slug AND m.start_date <= now() AND (m.end_date IS NULL OR m.end_date >= ?)", opts.StartDate). Join("JOIN games AS g ON (g.away_team_slug = m.team_slug OR g.home_team_slug = m.team_slug) AND g.date >= GREATEST(m.start_date, ?) AND (g.date <= m.end_date OR m.end_date IS NULL) AND g.date < DATE(now())", opts.StartDate). Join("LEFT JOIN game_players AS gp ON gp.player_slug = p.slug AND gp.game_id = g.id"). Join("LEFT JOIN game_player_scores AS fs ON fs.player_slug = p.slug AND fs.game_id = g.id"). Group("p.slug") if opts.OnlyClubGames { PlayerPossibleGameCount.Where("m.membership_type = 'club'") } // Return the list of games played by players filtered out with params gamePlayersRequest := r.db.NewSelect(). ColumnExpr("gp.player_slug"). ColumnExpr("gp.game_id"). ColumnExpr("gp.team_slug"). ColumnExpr("CASE WHEN gp.team_slug = g.home_team_slug THEN g.home_goals ELSE g.away_goals END as team_goals_for"). ColumnExpr("CASE WHEN gp.team_slug = g.home_team_slug THEN g.away_goals ELSE g.home_goals END as team_goals_against"). ColumnExpr("CASE WHEN gp.team_slug = g.home_team_slug THEN 'H' ELSE 'A' END AS home_away"). TableExpr("\"FilteredGames\" AS g"). Join("JOIN game_players AS gp ON g.id = gp.game_id"). Join("JOIN \"FilteredPlayers\" AS p ON p.slug = gp.player_slug") if opts.OnlyStarting { gamePlayersRequest.Where("gp.status = 'starting'") } scoresRequest := r.db.NewSelect(). ColumnExpr("gp.player_slug"). ColumnExpr("round(AVG(gp.team_goals_for), 2) AS avg_team_goals_for"). ColumnExpr("round(AVG(gp.team_goals_against), 2) AS avg_team_goals_against"). ColumnExpr("round(AVG(fs.score), 2) AS total_avg_score"). ColumnExpr("round(AVG(fs.decisive_score), 2) AS decisive_avg_score"). ColumnExpr("round(AVG(fs.all_around_score), 2) AS all_around_avg_score"). ColumnExpr("min(fs.score) as min_score"). ColumnExpr("max(fs.score) as max_score"). ColumnExpr("COALESCE(round(stddev(fs.score),2), 0) AS total_stddev_score"). ColumnExpr("COUNT(*) AS game_used_in_stats_count"). ColumnExpr("COUNT(CASE WHEN fs.score > 80 THEN 1 ELSE NULL END) AS more_than_80_score"). ColumnExpr("COUNT(CASE WHEN fs.score > 70 THEN 1 ELSE NULL END) AS more_than_70_score"). ColumnExpr("COUNT(CASE WHEN fs.score > 60 THEN 1 ELSE NULL END) AS more_than_60_score"). ColumnExpr("COUNT(CASE WHEN fs.score > 50 THEN 1 ELSE NULL END) AS more_than_50_score"). ColumnExpr("COUNT(CASE WHEN fs.decisive_score > 60 THEN 1 ELSE NULL END) AS decisive_count"). ColumnExpr("COUNT(CASE WHEN fs.all_around_score > 40 THEN 1 ELSE NULL END) AS more_than_40_aa"). ColumnExpr("COUNT(CASE WHEN fs.all_around_score > 30 THEN 1 ELSE NULL END) AS more_than_30_aa"). ColumnExpr("COUNT(CASE WHEN fs.all_around_score > 20 THEN 1 ELSE NULL END) AS more_than_20_aa"). ColumnExpr("COUNT(CASE WHEN fs.all_around_score > 10 THEN 1 ELSE NULL END) AS more_than_10_aa"). ColumnExpr("SUM(fs.minutes_played) as total_minutes"). ColumnExpr("SUM(fs.all_around_score) as total_aa"). ColumnExpr("COALESCE(ROUND(SUM(fs.all_around_score) / NULLIF(SUM(fs.minutes_played), 0), 3), 0) AS aa_per_min"). ColumnExpr("COALESCE(ROUND((SUM(fs.duel_won)::NUMERIC / NULLIF(SUM(fs.minutes_played), 0)) * 90, 2), 0) AS duel_won_per_90_min"). ColumnExpr("COALESCE(ROUND((SUM(fs.effective_clearance)::NUMERIC / NULLIF(SUM(fs.minutes_played), 0)) * 90, 2), 0) AS effective_clearance_per_90_min"). ColumnExpr("COALESCE(ROUND((SUM(fs.shot_on_target)::NUMERIC / NULLIF(SUM(fs.minutes_played), 0)) * 90, 2), 0) AS shot_on_target_per_90_min"). ColumnExpr("COALESCE(ROUND((SUM(fs.accurate_pass)::NUMERIC / NULLIF(SUM(fs.minutes_played), 0)) * 90, 2), 0) AS accurate_pass_per_90_min"). ColumnExpr("COALESCE(ROUND((SUM(fs.won_contest)::NUMERIC / NULLIF(SUM(fs.minutes_played), 0)) * 90, 2), 0) AS won_contest_per_90_min"). TableExpr("\"FilteredGamePlayers\" AS gp"). Join("JOIN game_player_scores AS fs ON gp.player_slug = fs.player_slug AND gp.game_id = fs.game_id"). Group("gp.player_slug") filteredScoresRequest := r.db.NewSelect(). ColumnExpr("s.*"). ColumnExpr("ROUND(((s.game_used_in_stats_count::float / ppgc.total_game_count::float) * 100)::numeric, 2) AS stack_play_percentage"). ColumnExpr("ROUND(((s.more_than_80_score::float / s.game_used_in_stats_count::float) * 100)::numeric, 2) AS more_than_80_score_percentage"). ColumnExpr("ROUND(((s.more_than_70_score::float / s.game_used_in_stats_count::float) * 100)::numeric, 2) AS more_than_70_score_percentage"). ColumnExpr("ROUND(((s.more_than_60_score::float / s.game_used_in_stats_count::float) * 100)::numeric, 2) AS more_than_60_score_percentage"). ColumnExpr("ROUND(((s.more_than_50_score::float / s.game_used_in_stats_count::float) * 100)::numeric, 2) AS more_than_50_score_percentage"). ColumnExpr("ROUND(((s.decisive_count::float / s.game_used_in_stats_count::float) * 100)::numeric, 2) AS decisive_percentage"). ColumnExpr("ROUND(((s.more_than_40_aa::float / s.game_used_in_stats_count::float) * 100)::numeric, 2) AS more_than_40_aa_percentage"). ColumnExpr("ROUND(((s.more_than_30_aa::float / s.game_used_in_stats_count::float) * 100)::numeric, 2) AS more_than_30_aa_percentage"). ColumnExpr("ROUND(((s.more_than_20_aa::float / s.game_used_in_stats_count::float) * 100)::numeric, 2) AS more_than_20_aa_percentage"). ColumnExpr("ROUND(((s.more_than_10_aa::float / s.game_used_in_stats_count::float) * 100)::numeric, 2) AS more_than_10_aa_percentage"). ColumnExpr("ppgc.game_started_count"). ColumnExpr("ppgc.game_benched_played_count"). ColumnExpr("ppgc.game_benched_unplayed_count"). ColumnExpr("ppgc.total_game_count AS teams_game_count"). ColumnExpr("ppgc.scores"). ColumnExpr("ppgc.total_minutes AS total_possible_minutes"). ColumnExpr("ppgc.percentage_minutes_played"). TableExpr("\"Scores\" AS s"). Join("JOIN \"PlayerPossibleGameCount\" AS ppgc ON ppgc.slug = s.player_slug"). Where("s.game_used_in_stats_count >= ?", opts.MinGameCount). Where("s.total_minutes >= ?", opts.MinTotalMinutes) mainRequest := r.db.NewSelect(). With("Supplies", PlayersSupplies). With("NextGWGameCount", NextGWGameCountRequest). With("FilteredGames", FilteredGames). With("FilteredPlayers", FilteredPlayers). With("PlayerPossibleGameCount", PlayerPossibleGameCount). With("FilteredGamePlayers", gamePlayersRequest). With("Scores", scoresRequest). With("FilteredScores", filteredScoresRequest). ColumnExpr("fs.*"). TableExpr("\"FilteredScores\" AS fs"). Where("fs.stack_play_percentage >= ?", opts.MinTeamGamesPlayedPercentage). Order(opts.Order). Limit(opts.Limit) var res []SingleRanking err := mainRequest.Scan(ctx, &res) if err != nil { return nil, errors.Wrap(err, "selecting single rankings") } playerSlugs := lo.Map(res, func(r SingleRanking, _ int) string { return r.PlayerSlug }) if len(playerSlugs) == 0 { return res, nil } players, err := r.GetMany(ctx, playerSlugs...) if err != nil { return nil, errors.Wrap(err, "selecting players") } for i, re := range res { for _, p := range players { if re.PlayerSlug == p.Slug { res[i].Player = p break } } } return res, nil }