OOKNET                             [ /  search the index  ]  
──────────────────────────────────────────────────────────────────────────────────────
[ KB ]    [ GIT ]    [ PROJECTS ]    [ ABOUT ]                                      EN
══════════════════════════════════════════════════════════════════════════════════════
OOKNET   [ /  search  ]  
────────────────────────────────────────────────
[ KB ] [ GIT ] [ PROJECTS ] [ ABOUT ]
════════════════════════════════════════════════
 
 
master @ 384 LINES
 
[ HISTORY ]  [ UP ]
 

package loader

import (
	"database/sql"
	"fmt"
	"strings"
)

// BestRunData represents a player's best run for a specific dungeon in a specific
 season
type BestRunData struct {
	DungeonID               int64
	DungeonName             string
	DungeonSlug             string
	RunID                   int64
	Duration                int64
	CompletedTimestamp      int64
	SeasonID                int
	GlobalRankingFiltered   sql.NullInt64
	RegionalRankingFiltered sql.NullInt64
	RealmRankingFiltered    sql.NullInt64
	GlobalBracket           string
	RegionalBracket         string
	RealmBracket            string
}

// TeamMemberData represents a member of a run team
type TeamMemberData struct {
	RunID     int64
	Name      string
	SpecID    sql.NullInt64
	Region    string
	RealmSlug string
}

// LoadAllBestRuns loads best runs for a set of players
// Returns: map[playerID][]BestRunData, all unique run IDs, error
func LoadAllBestRuns(db *sql.DB, playerIDs []int64) (map[int64][]BestRunData, []in
t64, error) {
	if len(playerIDs) == 0 {
		return make(map[int64][]BestRunData), []int64{}, nil
	}

	// Build IN clause
	placeholders := make([]string, len(playerIDs))
	args := make([]any, len(playerIDs))
	for i, id := range playerIDs {
		placeholders[i] = "?"
		args[i] = id
	}

	query := fmt.Sprintf(`
        WITH realm_rankings AS (
            SELECT cr.id as run_id, cr.dungeon_id, cr.realm_id,
                   ROW_NUMBER() OVER (PARTITION BY cr.dungeon_id, cr.realm_id ORDE
R BY cr.duration ASC, cr.completed_timestamp ASC, cr.id ASC) as realm_ranking,
                   COUNT(*) OVER (PARTITION BY cr.dungeon_id, cr.realm_id) as tota
l_in_realm_dungeon
            FROM challenge_runs cr
        )
        SELECT pbr.player_id, pbr.dungeon_id, d.name, d.slug, pbr.run_id, pbr.dura
tion, pbr.completed_timestamp,
               pbr.season_id,
               rr_global_filtered.ranking as global_ranking_filtered,
               rr_regional_filtered.ranking as regional_ranking_filtered,
               realm_rankings.realm_ranking as realm_ranking_filtered,
               COALESCE(rr_global_filtered.percentile_bracket, '') as global_perce
ntile_bracket,
               COALESCE(rr_regional_filtered.percentile_bracket, '') as regional_p
ercentile_bracket,
               CASE
                   WHEN realm_rankings.realm_ranking = 1 THEN 'artifact'
                   ELSE
                       CASE
                           WHEN (CAST(realm_rankings.realm_ranking AS REAL) / CAST
(realm_rankings.total_in_realm_dungeon AS REAL) * 100) <= 1.0 THEN 'excellent'
                           WHEN (CAST(realm_rankings.realm_ranking AS REAL) / CAST
(realm_rankings.total_in_realm_dungeon AS REAL) * 100) <= 5.0 THEN 'legendary'
                           WHEN (CAST(realm_rankings.realm_ranking AS REAL) / CAST
(realm_rankings.total_in_realm_dungeon AS REAL) * 100) <= 20.0 THEN 'epic'
                           WHEN (CAST(realm_rankings.realm_ranking AS REAL) / CAST
(realm_rankings.total_in_realm_dungeon AS REAL) * 100) <= 40.0 THEN 'rare'
                           WHEN (CAST(realm_rankings.realm_ranking AS REAL) / CAST
(realm_rankings.total_in_realm_dungeon AS REAL) * 100) <= 60.0 THEN 'uncommon'
                           ELSE 'common'
                       END
               END as realm_percentile_bracket
        FROM player_best_runs pbr
        JOIN dungeons d ON pbr.dungeon_id = d.id
        JOIN players p ON pbr.player_id = p.id
        JOIN realms r ON p.realm_id = r.id
        LEFT JOIN run_rankings rr_global_filtered ON pbr.run_id = rr_global_filter
ed.run_id
            AND rr_global_filtered.ranking_type = 'global' AND rr_global_filtered.
ranking_scope = 'filtered'
            AND rr_global_filtered.season_id = pbr.season_id
        LEFT JOIN run_rankings rr_regional_filtered ON pbr.run_id = rr_regional_fi
ltered.run_id
            AND rr_regional_filtered.ranking_type = 'regional' AND rr_regional_fil
tered.ranking_scope = r.region || '_filtered'
            AND rr_regional_filtered.season_id = pbr.season_id
        LEFT JOIN realm_rankings ON pbr.run_id = realm_rankings.run_id
            AND pbr.dungeon_id = realm_rankings.dungeon_id AND r.id = realm_rankin
gs.realm_id
        WHERE pbr.player_id IN (%s)
        ORDER BY pbr.player_id, pbr.season_id, d.name
    `, strings.Join(placeholders, ","))

	rows, err := db.Query(query, args...)
	if err != nil {
		return nil, nil, err
	}
	defer rows.Close()

	bestRunsMap := make(map[int64][]BestRunData)
	runIDSet := make(map[int64]bool)
	var allRunIDs []int64

	for rows.Next() {
		var playerID int64
		var run BestRunData
		if err := rows.Scan(
			&playerID, &run.DungeonID, &run.DungeonName, &run.DungeonSlug, &run.RunID, &run
.Duration, &run.CompletedTimestamp,
			&run.SeasonID,
			&run.GlobalRankingFiltered, &run.RegionalRankingFiltered, &run.RealmRankingFilt
ered,
			&run.GlobalBracket, &run.RegionalBracket, &run.RealmBracket); err != nil {
			return nil, nil, fmt.Errorf("scan best run: %w", err)
		}
		bestRunsMap[playerID] = append(bestRunsMap[playerID], run)

		// Deduplicate run IDs
		if !runIDSet[run.RunID] {
			runIDSet[run.RunID] = true
			allRunIDs = append(allRunIDs, run.RunID)
		}
	}
	return bestRunsMap, allRunIDs, nil
}

// LoadAllTeamMembers loads team members for a set of runs
// Returns: map[runID][]TeamMemberData
func LoadAllTeamMembers(db *sql.DB, runIDs []int64) (map[int64][]TeamMemberData, e
rror) {
	if len(runIDs) == 0 {
		return make(map[int64][]TeamMemberData), nil
	}

	teamMembersMap := make(map[int64][]TeamMemberData)

	// Process in batches to avoid SQL limits (max ~32k parameters)
	const batchSize = 10000
	for i := 0; i < len(runIDs); i += batchSize {
		end := i + batchSize
		if end > len(runIDs) {
			end = len(runIDs)
		}

		batch := runIDs[i:end]

		// Build IN clause for this batch
		placeholders := make([]string, len(batch))
		args := make([]any, len(batch))
		for j, id := range batch {
			placeholders[j] = "?"
			args[j] = id
		}

		query := fmt.Sprintf(`
            SELECT rm.run_id, p.name, rm.spec_id, r.region, r.slug
            FROM run_members rm
            JOIN players p ON rm.player_id = p.id
            JOIN realms r ON p.realm_id = r.id
            WHERE rm.run_id IN (%s)
            ORDER BY rm.run_id, p.name
        `, strings.Join(placeholders, ","))

		rows, err := db.Query(query, args...)
		if err != nil {
			return nil, fmt.Errorf("batch %d: %w", i/batchSize+1, err)
		}

		for rows.Next() {
			var member TeamMemberData
			if err := rows.Scan(
				&member.RunID, &member.Name, &member.SpecID, &member.Region, &member.RealmSlug
); err != nil {
				rows.Close()
				return nil, fmt.Errorf("scan team member: %w", err)
			}
			teamMembersMap[member.RunID] = append(teamMembersMap[member.RunID], member)
		}
		rows.Close()
	}

	return teamMembersMap, nil
}

// LeaderboardMember represents a member in a leaderboard row
type LeaderboardMember struct {
	Name      string
	SpecID    *int
	Region    string
	RealmSlug string
}

// LeaderboardRow represents a canonical run for leaderboards
type LeaderboardRow struct {
	ID                 int64
	Duration           int64
	CompletedTimestamp int64
	KeystoneLevel      int
	DungeonName        string
	RealmName          string
	Region             string
	RankingPercentile  string // percentile bracket based on scope
	Members            []LeaderboardMember
}

// one canonical run per team_signature, ordered, with members; caller paginates i
n memory
func LoadAllCanonicalRuns(db *sql.DB, dungeonID int, region string, realmSlug stri
ng, seasonID int) ([]LeaderboardRow, error) {
	where := "WHERE cr.dungeon_id = ?"
	args := []any{dungeonID}
	if region != "" {
		where += " AND r.region = ?"
		args = append(args, region)
	}
	if realmSlug != "" {
		where += " AND r.slug = ?"
		args = append(args, realmSlug)
	}
	where += " AND cr.season_id = ?"
	args = append(args, seasonID)

	q := fmt.Sprintf(`
      WITH ranked AS (
        SELECT cr.id, cr.duration, cr.completed_timestamp,
               ROW_NUMBER() OVER (PARTITION BY cr.team_signature ORDER BY cr.durat
ion ASC, cr.completed_timestamp ASC, cr.id ASC) AS rn
        FROM challenge_runs cr
        JOIN realms r ON cr.realm_id = r.id
        %s
      )
      SELECT id FROM ranked WHERE rn = 1
      ORDER BY duration ASC, completed_timestamp ASC, id ASC
    `, where)

	idRows, err := db.Query(q, args...)
	if err != nil {
		return nil, err
	}
	ids := []int64{}
	for idRows.Next() {
		var id int64
		if err := idRows.Scan(&id); err != nil {
			idRows.Close()
			return nil, err
		}
		ids = append(ids, id)
	}
	idRows.Close()
	if len(ids) == 0 {
		return []LeaderboardRow{}, nil
	}

	// Determine ranking scope for percentile bracket
	var rankingType, rankingScope string
	if region == "" {
		// Global leaderboard
		rankingType = "global"
		rankingScope = "filtered"
	} else if realmSlug == "" {
		// Regional leaderboard
		rankingType = "regional"
		rankingScope = region + "_filtered"
	} else {
		// Realm leaderboard - compute on the fly
		rankingType = "realm"
		rankingScope = ""
	}

	// Load rows
	placeholders := make([]string, len(ids))
	iargs := make([]any, len(ids))
	for i, id := range ids {
		placeholders[i] = "?"
		iargs[i] = id
	}

	var rQuery string
	if rankingType == "realm" {
		// For realm scope, compute percentile on the fly
		// We need to rank ALL runs for the realm/dungeon, not just the page subset
		rQuery = fmt.Sprintf(`
      WITH realm_rankings AS (
        SELECT cr.id as run_id,
               cr.dungeon_id,
               cr.realm_id,
               ROW_NUMBER() OVER (PARTITION BY cr.dungeon_id, cr.realm_id ORDER BY
 cr.duration ASC, cr.completed_timestamp ASC, cr.id ASC) as realm_ranking,
               COUNT(*) OVER (PARTITION BY cr.dungeon_id, cr.realm_id) as total_in
_realm_dungeon
        FROM challenge_runs cr
        WHERE cr.season_id = ?
      )
      SELECT cr.id, cr.duration, cr.completed_timestamp, cr.keystone_level,
             d.name, rr.name, rr.region,
             CASE
               WHEN realm_rankings.realm_ranking = 1 THEN 'artifact'
               ELSE
                 CASE
                   WHEN (CAST(realm_rankings.realm_ranking AS REAL) / CAST(realm_r
ankings.total_in_realm_dungeon AS REAL) * 100) <= 1.0 THEN 'excellent'
                   WHEN (CAST(realm_rankings.realm_ranking AS REAL) / CAST(realm_r
ankings.total_in_realm_dungeon AS REAL) * 100) <= 5.0 THEN 'legendary'
                   WHEN (CAST(realm_rankings.realm_ranking AS REAL) / CAST(realm_r
ankings.total_in_realm_dungeon AS REAL) * 100) <= 20.0 THEN 'epic'
                   WHEN (CAST(realm_rankings.realm_ranking AS REAL) / CAST(realm_r
ankings.total_in_realm_dungeon AS REAL) * 100) <= 40.0 THEN 'rare'
                   WHEN (CAST(realm_rankings.realm_ranking AS REAL) / CAST(realm_r
ankings.total_in_realm_dungeon AS REAL) * 100) <= 60.0 THEN 'uncommon'
                   ELSE 'common'
                 END
             END as percentile_bracket
      FROM challenge_runs cr
      JOIN dungeons d ON cr.dungeon_id = d.id
      JOIN realms rr ON cr.realm_id = rr.id
      LEFT JOIN realm_rankings ON cr.id = realm_rankings.run_id
        AND cr.dungeon_id = realm_rankings.dungeon_id
        AND cr.realm_id = realm_rankings.realm_id
      WHERE cr.id IN (%s)
    `, strings.Join(placeholders, ","))
		// Prepend seasonID for the CTE
		iargs = append([]any{seasonID}, iargs...)
	} else {
		// For global/regional, use pre-computed rankings
		rQuery = fmt.Sprintf(`
      SELECT cr.id, cr.duration, cr.completed_timestamp, cr.keystone_level,
             d.name, rr.name, rr.region,
             COALESCE(run_rankings.percentile_bracket, '') as percentile_bracket
      FROM challenge_runs cr
      JOIN dungeons d ON cr.dungeon_id = d.id
      JOIN realms rr ON cr.realm_id = rr.id
      LEFT JOIN run_rankings ON cr.id = run_rankings.run_id
        AND run_rankings.ranking_type = ?
        AND run_rankings.ranking_scope = ?
        AND run_rankings.season_id = ?
      WHERE cr.id IN (%s)
    `, strings.Join(placeholders, ","))
		// Prepend ranking parameters
		iargs = append([]any{rankingType, rankingScope, seasonID}, iargs...)
	}

	rrows, err := db.Query(rQuery, iargs...)
	if err != nil {
		return nil, err
	}
	byID := map[int64]LeaderboardRow{}
	for rrows.Next() {
		var row LeaderboardRow
		if err := rrows.Scan(&row.ID, &row.Duration, &row.CompletedTimestamp, &row.Keyst
oneLevel, &row.DungeonName, &row.RealmName, &row.Region, &row.RankingPercentile); 
err != nil {
			rrows.Close()
			return nil, err
		}
		byID[row.ID] = row
	}
	rrows.Close()

	// Members - use original ids array, not modified iargs
	mQuery := fmt.Sprintf(`
      SELECT rm.run_id, p.name, rm.spec_id, rr.region, rr.slug
      FROM run_members rm
      JOIN players p ON rm.player_id = p.id
      JOIN realms rr ON p.realm_id = rr.id
      WHERE rm.run_id IN (%s)
      ORDER BY rm.run_id, p.name
    `, strings.Join(placeholders, ","))
	mArgs := make([]any, len(ids))
	for i, id := range ids {
		mArgs[i] = id
	}
	mrows, err := db.Query(mQuery, mArgs...)
	if err != nil {
		return nil, err
	}
	for mrows.Next() {
		var runID int64
		var name, region, rslug string
		var spec sql.NullInt64
		if err := mrows.Scan(&runID, &name, &spec, &region, &rslug); err != nil {
			mrows.Close()
			return nil, err
		}
		row := byID[runID]
		var specPtr *int
		if spec.Valid {
			v := int(spec.Int64)
			specPtr = &v
		}
		row.Members = append(row.Members, LeaderboardMember{Name: name, SpecID: specPtr,
 Region: region, RealmSlug: rslug})
		byID[runID] = row
	}
	mrows.Close()

	// Order back as ids
	out := make([]LeaderboardRow, 0, len(ids))
	for _, id := range ids {
		out = append(out, byID[id])
	}
	return out, nil
}


package loader

import (
	"database/sql"
	"fmt"
	"strings"
)

// BestRunData represents a player's best ru
n for a specific dungeon in a specific seaso
n
type BestRunData struct {
	DungeonID               int64
	DungeonName             string
	DungeonSlug             string
	RunID                   int64
	Duration                int64
	CompletedTimestamp      int64
	SeasonID                int
	GlobalRankingFiltered   sql.NullInt64
	RegionalRankingFiltered sql.NullInt64
	RealmRankingFiltered    sql.NullInt64
	GlobalBracket           string
	RegionalBracket         string
	RealmBracket            string
}

// TeamMemberData represents a member of a r
un team
type TeamMemberData struct {
	RunID     int64
	Name      string
	SpecID    sql.NullInt64
	Region    string
	RealmSlug string
}

// LoadAllBestRuns loads best runs for a set
 of players
// Returns: map[playerID][]BestRunData, all 
unique run IDs, error
func LoadAllBestRuns(db *sql.DB, playerIDs [
]int64) (map[int64][]BestRunData, []int64, e
rror) {
	if len(playerIDs) == 0 {
		return make(map[int64][]BestRunData), []in
t64{}, nil
	}

	// Build IN clause
	placeholders := make([]string, len(playerID
s))
	args := make([]any, len(playerIDs))
	for i, id := range playerIDs {
		placeholders[i] = "?"
		args[i] = id
	}

	query := fmt.Sprintf(`
        WITH realm_rankings AS (
            SELECT cr.id as run_id, cr.dunge
on_id, cr.realm_id,
                   ROW_NUMBER() OVER (PARTIT
ION BY cr.dungeon_id, cr.realm_id ORDER BY c
r.duration ASC, cr.completed_timestamp ASC, 
cr.id ASC) as realm_ranking,
                   COUNT(*) OVER (PARTITION 
BY cr.dungeon_id, cr.realm_id) as total_in_r
ealm_dungeon
            FROM challenge_runs cr
        )
        SELECT pbr.player_id, pbr.dungeon_id
, d.name, d.slug, pbr.run_id, pbr.duration, 
pbr.completed_timestamp,
               pbr.season_id,
               rr_global_filtered.ranking as
 global_ranking_filtered,
               rr_regional_filtered.ranking 
as regional_ranking_filtered,
               realm_rankings.realm_ranking 
as realm_ranking_filtered,
               COALESCE(rr_global_filtered.p
ercentile_bracket, '') as global_percentile_
bracket,
               COALESCE(rr_regional_filtered
.percentile_bracket, '') as regional_percent
ile_bracket,
               CASE
                   WHEN realm_rankings.realm
_ranking = 1 THEN 'artifact'
                   ELSE
                       CASE
                           WHEN (CAST(realm_
rankings.realm_ranking AS REAL) / CAST(realm
_rankings.total_in_realm_dungeon AS REAL) * 
100) <= 1.0 THEN 'excellent'
                           WHEN (CAST(realm_
rankings.realm_ranking AS REAL) / CAST(realm
_rankings.total_in_realm_dungeon AS REAL) * 
100) <= 5.0 THEN 'legendary'
                           WHEN (CAST(realm_
rankings.realm_ranking AS REAL) / CAST(realm
_rankings.total_in_realm_dungeon AS REAL) * 
100) <= 20.0 THEN 'epic'
                           WHEN (CAST(realm_
rankings.realm_ranking AS REAL) / CAST(realm
_rankings.total_in_realm_dungeon AS REAL) * 
100) <= 40.0 THEN 'rare'
                           WHEN (CAST(realm_
rankings.realm_ranking AS REAL) / CAST(realm
_rankings.total_in_realm_dungeon AS REAL) * 
100) <= 60.0 THEN 'uncommon'
                           ELSE 'common'
                       END
               END as realm_percentile_brack
et
        FROM player_best_runs pbr
        JOIN dungeons d ON pbr.dungeon_id = 
d.id
        JOIN players p ON pbr.player_id = p.
id
        JOIN realms r ON p.realm_id = r.id
        LEFT JOIN run_rankings rr_global_fil
tered ON pbr.run_id = rr_global_filtered.run
_id
            AND rr_global_filtered.ranking_t
ype = 'global' AND rr_global_filtered.rankin
g_scope = 'filtered'
            AND rr_global_filtered.season_id
 = pbr.season_id
        LEFT JOIN run_rankings rr_regional_f
iltered ON pbr.run_id = rr_regional_filtered
.run_id
            AND rr_regional_filtered.ranking
_type = 'regional' AND rr_regional_filtered.
ranking_scope = r.region || '_filtered'
            AND rr_regional_filtered.season_
id = pbr.season_id
        LEFT JOIN realm_rankings ON pbr.run_
id = realm_rankings.run_id
            AND pbr.dungeon_id = realm_ranki
ngs.dungeon_id AND r.id = realm_rankings.rea
lm_id
        WHERE pbr.player_id IN (%s)
        ORDER BY pbr.player_id, pbr.season_i
d, d.name
    `, strings.Join(placeholders, ","))

	rows, err := db.Query(query, args...)
	if err != nil {
		return nil, nil, err
	}
	defer rows.Close()

	bestRunsMap := make(map[int64][]BestRunData
)
	runIDSet := make(map[int64]bool)
	var allRunIDs []int64

	for rows.Next() {
		var playerID int64
		var run BestRunData
		if err := rows.Scan(
			&playerID, &run.DungeonID, &run.DungeonNa
me, &run.DungeonSlug, &run.RunID, &run.Durat
ion, &run.CompletedTimestamp,
			&run.SeasonID,
			&run.GlobalRankingFiltered, &run.Regional
RankingFiltered, &run.RealmRankingFiltered,
			&run.GlobalBracket, &run.RegionalBracket,
 &run.RealmBracket); err != nil {
			return nil, nil, fmt.Errorf("scan best ru
n: %w", err)
		}
		bestRunsMap[playerID] = append(bestRunsMap
[playerID], run)

		// Deduplicate run IDs
		if !runIDSet[run.RunID] {
			runIDSet[run.RunID] = true
			allRunIDs = append(allRunIDs, run.RunID)
		}
	}
	return bestRunsMap, allRunIDs, nil
}

// LoadAllTeamMembers loads team members for
 a set of runs
// Returns: map[runID][]TeamMemberData
func LoadAllTeamMembers(db *sql.DB, runIDs [
]int64) (map[int64][]TeamMemberData, error) 
{
	if len(runIDs) == 0 {
		return make(map[int64][]TeamMemberData), n
il
	}

	teamMembersMap := make(map[int64][]TeamMemb
erData)

	// Process in batches to avoid SQL limits (
max ~32k parameters)
	const batchSize = 10000
	for i := 0; i < len(runIDs); i += batchSize
 {
		end := i + batchSize
		if end > len(runIDs) {
			end = len(runIDs)
		}

		batch := runIDs[i:end]

		// Build IN clause for this batch
		placeholders := make([]string, len(batch))
		args := make([]any, len(batch))
		for j, id := range batch {
			placeholders[j] = "?"
			args[j] = id
		}

		query := fmt.Sprintf(`
            SELECT rm.run_id, p.name, rm.spe
c_id, r.region, r.slug
            FROM run_members rm
            JOIN players p ON rm.player_id =
 p.id
            JOIN realms r ON p.realm_id = r.
id
            WHERE rm.run_id IN (%s)
            ORDER BY rm.run_id, p.name
        `, strings.Join(placeholders, ","))

		rows, err := db.Query(query, args...)
		if err != nil {
			return nil, fmt.Errorf("batch %d: %w", i/
batchSize+1, err)
		}

		for rows.Next() {
			var member TeamMemberData
			if err := rows.Scan(
				&member.RunID, &member.Name, &member.Spe
cID, &member.Region, &member.RealmSlug); err
 != nil {
				rows.Close()
				return nil, fmt.Errorf("scan team member
: %w", err)
			}
			teamMembersMap[member.RunID] = append(tea
mMembersMap[member.RunID], member)
		}
		rows.Close()
	}

	return teamMembersMap, nil
}

// LeaderboardMember represents a member in 
a leaderboard row
type LeaderboardMember struct {
	Name      string
	SpecID    *int
	Region    string
	RealmSlug string
}

// LeaderboardRow represents a canonical run
 for leaderboards
type LeaderboardRow struct {
	ID                 int64
	Duration           int64
	CompletedTimestamp int64
	KeystoneLevel      int
	DungeonName        string
	RealmName          string
	Region             string
	RankingPercentile  string // percentile bra
cket based on scope
	Members            []LeaderboardMember
}

// one canonical run per team_signature, ord
ered, with members; caller paginates in memo
ry
func LoadAllCanonicalRuns(db *sql.DB, dungeo
nID int, region string, realmSlug string, se
asonID int) ([]LeaderboardRow, error) {
	where := "WHERE cr.dungeon_id = ?"
	args := []any{dungeonID}
	if region != "" {
		where += " AND r.region = ?"
		args = append(args, region)
	}
	if realmSlug != "" {
		where += " AND r.slug = ?"
		args = append(args, realmSlug)
	}
	where += " AND cr.season_id = ?"
	args = append(args, seasonID)

	q := fmt.Sprintf(`
      WITH ranked AS (
        SELECT cr.id, cr.duration, cr.comple
ted_timestamp,
               ROW_NUMBER() OVER (PARTITION 
BY cr.team_signature ORDER BY cr.duration AS
C, cr.completed_timestamp ASC, cr.id ASC) AS
 rn
        FROM challenge_runs cr
        JOIN realms r ON cr.realm_id = r.id
        %s
      )
      SELECT id FROM ranked WHERE rn = 1
      ORDER BY duration ASC, completed_times
tamp ASC, id ASC
    `, where)

	idRows, err := db.Query(q, args...)
	if err != nil {
		return nil, err
	}
	ids := []int64{}
	for idRows.Next() {
		var id int64
		if err := idRows.Scan(&id); err != nil {
			idRows.Close()
			return nil, err
		}
		ids = append(ids, id)
	}
	idRows.Close()
	if len(ids) == 0 {
		return []LeaderboardRow{}, nil
	}

	// Determine ranking scope for percentile b
racket
	var rankingType, rankingScope string
	if region == "" {
		// Global leaderboard
		rankingType = "global"
		rankingScope = "filtered"
	} else if realmSlug == "" {
		// Regional leaderboard
		rankingType = "regional"
		rankingScope = region + "_filtered"
	} else {
		// Realm leaderboard - compute on the fly
		rankingType = "realm"
		rankingScope = ""
	}

	// Load rows
	placeholders := make([]string, len(ids))
	iargs := make([]any, len(ids))
	for i, id := range ids {
		placeholders[i] = "?"
		iargs[i] = id
	}

	var rQuery string
	if rankingType == "realm" {
		// For realm scope, compute percentile on 
the fly
		// We need to rank ALL runs for the realm/
dungeon, not just the page subset
		rQuery = fmt.Sprintf(`
      WITH realm_rankings AS (
        SELECT cr.id as run_id,
               cr.dungeon_id,
               cr.realm_id,
               ROW_NUMBER() OVER (PARTITION 
BY cr.dungeon_id, cr.realm_id ORDER BY cr.du
ration ASC, cr.completed_timestamp ASC, cr.i
d ASC) as realm_ranking,
               COUNT(*) OVER (PARTITION BY c
r.dungeon_id, cr.realm_id) as total_in_realm
_dungeon
        FROM challenge_runs cr
        WHERE cr.season_id = ?
      )
      SELECT cr.id, cr.duration, cr.complete
d_timestamp, cr.keystone_level,
             d.name, rr.name, rr.region,
             CASE
               WHEN realm_rankings.realm_ran
king = 1 THEN 'artifact'
               ELSE
                 CASE
                   WHEN (CAST(realm_rankings
.realm_ranking AS REAL) / CAST(realm_ranking
s.total_in_realm_dungeon AS REAL) * 100) <= 
1.0 THEN 'excellent'
                   WHEN (CAST(realm_rankings
.realm_ranking AS REAL) / CAST(realm_ranking
s.total_in_realm_dungeon AS REAL) * 100) <= 
5.0 THEN 'legendary'
                   WHEN (CAST(realm_rankings
.realm_ranking AS REAL) / CAST(realm_ranking
s.total_in_realm_dungeon AS REAL) * 100) <= 
20.0 THEN 'epic'
                   WHEN (CAST(realm_rankings
.realm_ranking AS REAL) / CAST(realm_ranking
s.total_in_realm_dungeon AS REAL) * 100) <= 
40.0 THEN 'rare'
                   WHEN (CAST(realm_rankings
.realm_ranking AS REAL) / CAST(realm_ranking
s.total_in_realm_dungeon AS REAL) * 100) <= 
60.0 THEN 'uncommon'
                   ELSE 'common'
                 END
             END as percentile_bracket
      FROM challenge_runs cr
      JOIN dungeons d ON cr.dungeon_id = d.i
d
      JOIN realms rr ON cr.realm_id = rr.id
      LEFT JOIN realm_rankings ON cr.id = re
alm_rankings.run_id
        AND cr.dungeon_id = realm_rankings.d
ungeon_id
        AND cr.realm_id = realm_rankings.rea
lm_id
      WHERE cr.id IN (%s)
    `, strings.Join(placeholders, ","))
		// Prepend seasonID for the CTE
		iargs = append([]any{seasonID}, iargs...)
	} else {
		// For global/regional, use pre-computed r
ankings
		rQuery = fmt.Sprintf(`
      SELECT cr.id, cr.duration, cr.complete
d_timestamp, cr.keystone_level,
             d.name, rr.name, rr.region,
             COALESCE(run_rankings.percentil
e_bracket, '') as percentile_bracket
      FROM challenge_runs cr
      JOIN dungeons d ON cr.dungeon_id = d.i
d
      JOIN realms rr ON cr.realm_id = rr.id
      LEFT JOIN run_rankings ON cr.id = run_
rankings.run_id
        AND run_rankings.ranking_type = ?
        AND run_rankings.ranking_scope = ?
        AND run_rankings.season_id = ?
      WHERE cr.id IN (%s)
    `, strings.Join(placeholders, ","))
		// Prepend ranking parameters
		iargs = append([]any{rankingType, rankingS
cope, seasonID}, iargs...)
	}

	rrows, err := db.Query(rQuery, iargs...)
	if err != nil {
		return nil, err
	}
	byID := map[int64]LeaderboardRow{}
	for rrows.Next() {
		var row LeaderboardRow
		if err := rrows.Scan(&row.ID, &row.Duratio
n, &row.CompletedTimestamp, &row.KeystoneLev
el, &row.DungeonName, &row.RealmName, &row.R
egion, &row.RankingPercentile); err != nil {
			rrows.Close()
			return nil, err
		}
		byID[row.ID] = row
	}
	rrows.Close()

	// Members - use original ids array, not mo
dified iargs
	mQuery := fmt.Sprintf(`
      SELECT rm.run_id, p.name, rm.spec_id, 
rr.region, rr.slug
      FROM run_members rm
      JOIN players p ON rm.player_id = p.id
      JOIN realms rr ON p.realm_id = rr.id
      WHERE rm.run_id IN (%s)
      ORDER BY rm.run_id, p.name
    `, strings.Join(placeholders, ","))
	mArgs := make([]any, len(ids))
	for i, id := range ids {
		mArgs[i] = id
	}
	mrows, err := db.Query(mQuery, mArgs...)
	if err != nil {
		return nil, err
	}
	for mrows.Next() {
		var runID int64
		var name, region, rslug string
		var spec sql.NullInt64
		if err := mrows.Scan(&runID, &name, &spec,
 &region, &rslug); err != nil {
			mrows.Close()
			return nil, err
		}
		row := byID[runID]
		var specPtr *int
		if spec.Valid {
			v := int(spec.Int64)
			specPtr = &v
		}
		row.Members = append(row.Members, Leaderbo
ardMember{Name: name, SpecID: specPtr, Regio
n: region, RealmSlug: rslug})
		byID[runID] = row
	}
	mrows.Close()

	// Order back as ids
	out := make([]LeaderboardRow, 0, len(ids))
	for _, id := range ids {
		out = append(out, byID[id])
	}
	return out, nil
}

 
──────────────────────────────────────────────────────────────────────────────────────
OOKNET
────────────────────────────────────────────────
OOKNET