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

package generator

import (
	"database/sql"
	"fmt"
	"path/filepath"
	"time"

	"ookstats/internal/wow"
	"ookstats/internal/writer"
)

// StatsJSON is the top-level shape for web/public/api/stats.json.
// Three scopes are emitted: cross-season "all_time" plus one entry per season num
ber.
type StatsJSON struct {
	GeneratedAt int64 `json:"generated_at"`
	// Scopes is keyed by region (global/us/eu/kr/tw), then by season key
	// (all_time/season_1/season_2). Region "global" includes all regions.
	Scopes map[string]map[string]StatsScope `json:"scopes"`
}

// StatsScope holds aggregated stats for a single scope (all-time or one season).
type StatsScope struct {
	TotalRuns         int64                      `json:"total_runs"`
	TotalPlayers      int64                      `json:"total_players"`
	NineOfNinePlayers int64                      `json:"nine_of_nine_players"`
	CompletionTiers   CompletionTiers            `json:"completion_tiers"`
	SpecCounts        map[string]SpecCountBucket `json:"spec_counts"`
	WeeklyActivity    []WeeklyActivityEntry      `json:"weekly_activity"`
}

// TotalRuns is the distinct-run denominator so the chart can switch between
// "total slot count" and "% of runs containing the spec" without recomputing
type SpecCountBucket struct {
	TotalRuns int64                     `json:"total_runs"`
	Entries   []SpecCountEntry          `json:"entries"`
	ByDungeon map[int]DungeonSpecBucket `json:"by_dungeon"`
}

type DungeonSpecBucket struct {
	TotalRuns int64            `json:"total_runs"`
	Entries   []SpecCountEntry `json:"entries"`
}

type CompletionTiers struct {
	NineOfNineGold     CompletionTier `json:"9_of_9_gold"`
	NineOfNinePlatinum CompletionTier `json:"9_of_9_platinum"`
	NineOfNineTitle    CompletionTier `json:"9_of_9_title"`
}

// percentile denominators:
// of_all_players       = scoped pool
// of_completed_players = finishers (players_with_9_dungeon_bests)
// of_all_time_players  = stable cross-scope denominator
type CompletionTier struct {
	Count                        int64   `json:"count"`
	PercentileOfAllPlayers       float64 `json:"percentile_of_all_players"`
	PercentileOfCompletedPlayers float64 `json:"percentile_of_completed_players"`
	PercentileOfAllTimePlayers   float64 `json:"percentile_of_all_time_players"`
}

// `count` is total run_member spec slots (a run with two of the same spec
// contributes 2); `runs_with_spec` is distinct runs containing the spec
// (same run with two of the spec contributes 1, capped at total_runs)
type SpecCountEntry struct {
	SpecID       int    `json:"spec_id"`
	ClassName    string `json:"class_name"`
	SpecName     string `json:"spec_name"`
	Count        int64  `json:"count"`
	RunsWithSpec int64  `json:"runs_with_spec"`
}

type WeeklyActivityEntry struct {
	WeekStart string `json:"week_start"` // YYYY-MM-DD
	RunCount  int64  `json:"run_count"`
}

// gold/platinum/title timer thresholds in ms; gold is the achievement par
// time, platinum and title are tighter community-defined cutoffs
type dungeonThresholds struct {
	GoldMs     int64
	PlatinumMs int64
	TitleMs    int64
}

var dungeonTimerThresholds = map[int]dungeonThresholds{
	2:  {GoldMs: 900000, PlatinumMs: 615000, TitleMs: 510000},  // Temple of the Jade
 Serpent (15:00 / 10:15 / 8:30)
	56: {GoldMs: 720000, PlatinumMs: 495000, TitleMs: 390000},  // Stormstout Brewery
        (12:00 / 8:15 / 6:30)
	57: {GoldMs: 780000, PlatinumMs: 480000, TitleMs: 330000},  // Gate of the Settin
g Sun   (13:00 / 8:00 / 5:30)
	58: {GoldMs: 1260000, PlatinumMs: 840000, TitleMs: 630000}, // Shado-Pan Monaster
y       (21:00 / 14:00 / 10:30)
	59: {GoldMs: 1050000, PlatinumMs: 735000, TitleMs: 615000}, // Siege of Niuzao Te
mple    (17:30 / 12:15 / 10:15)
	60: {GoldMs: 720000, PlatinumMs: 495000, TitleMs: 405000},  // Mogu'shan Palace  
        (12:00 / 8:15 / 6:45)
	76: {GoldMs: 1140000, PlatinumMs: 615000, TitleMs: 435000}, // Scholomance       
        (19:00 / 10:15 / 7:15)
	77: {GoldMs: 780000, PlatinumMs: 480000, TitleMs: 255000},  // Scarlet Halls     
        (13:00 / 8:00 / 4:15)
	78: {GoldMs: 780000, PlatinumMs: 540000, TitleMs: 330000},  // Scarlet Monastery 
        (13:00 / 9:00 / 5:30)
}

// must clear all of them to qualify for 9/9
var totalDungeons = len(dungeonTimerThresholds)

// in-game cutoffs are lenient: a 5:30.800 run beats a "5:30" title cutoff
// because the timer reads "5:30" until it ticks to 5:31, so pass condition
// is `duration < threshold + leniencyMs`
const leniencyMs int64 = 1000

func beatsTier(duration, thresholdMs int64) bool {
	return duration < thresholdMs+leniencyMs
}

// order matters for JSON readability
type statsScopeDef struct {
	Key       string
	SeasonNum int // 0 = all-time
}

// statsRegions are the region scopes emitted; "global" means no region filter.
var statsRegions = []string{"global", "us", "eu", "kr", "tw"}

// GenerateStats writes outDir/api/stats.json with aggregated stats per (region, s
eason) scope.
func GenerateStats(db *sql.DB, outDir string) error {
	statsScopes, err := loadStatsScopes(db)
	if err != nil {
		return fmt.Errorf("load stats scopes: %w", err)
	}

	out := StatsJSON{
		GeneratedAt: time.Now().UnixMilli(),
		Scopes:      make(map[string]map[string]StatsScope, len(statsRegions)),
	}

	for _, region := range statsRegions {
		regionScopes := make(map[string]StatsScope, len(statsScopes))
		// Compute all-time first per region so its total_players is the stable
		// denominator for per-season scopes within that region.
		var allTimeTotalPlayers int64
		for _, sc := range statsScopes {
			ss, err := buildStatsScope(db, sc.SeasonNum, region, allTimeTotalPlayers)
			if err != nil {
				return fmt.Errorf("scope %s/%s: %w", region, sc.Key, err)
			}
			if sc.SeasonNum == 0 {
				allTimeTotalPlayers = ss.TotalPlayers
			}
			regionScopes[sc.Key] = ss
		}
		out.Scopes[region] = regionScopes
	}

	outPath := filepath.Join(outDir, "api", "stats.json")
	if err := writer.WriteJSONFileCompact(outPath, out); err != nil {
		return fmt.Errorf("write stats.json: %w", err)
	}
	return nil
}

// loadStatsScopes returns all-time plus one scope per season in the DB
func loadStatsScopes(db *sql.DB) ([]statsScopeDef, error) {
	rows, err := db.Query(`SELECT DISTINCT season_number FROM seasons ORDER BY season
_number ASC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	scopes := []statsScopeDef{{Key: "all_time", SeasonNum: 0}}
	for rows.Next() {
		var n int
		if err := rows.Scan(&n); err != nil {
			return nil, err
		}
		scopes = append(scopes, statsScopeDef{Key: fmt.Sprintf("season_%d", n), SeasonNu
m: n})
	}
	return scopes, rows.Err()
}

// buildStatsScope assembles a single StatsScope.
// seasonNum == 0 means cross-season (all-time). region == "global" means no regio
n filter.
// For all-time, pass allTimeTotalPlayers=0; for per-season builds, pass the all-t
ime scope's
// TotalPlayers (within the same region) so the `percentile_of_all_time_players` f
ield
// uses a stable denominator.
func buildStatsScope(db *sql.DB, seasonNum int, region string, allTimeTotalPlayers
 int64) (StatsScope, error) {
	var s StatsScope
	var err error

	if s.TotalRuns, err = countTotalRuns(db, seasonNum, region); err != nil {
		return s, fmt.Errorf("total runs: %w", err)
	}

	if s.TotalPlayers, err = countTotalPlayers(db, seasonNum, region); err != nil {
		return s, fmt.Errorf("total players: %w", err)
	}

	// On all-time, the stable denominator is just this scope's own TotalPlayers.
	allTimeDen := allTimeTotalPlayers
	if seasonNum == 0 {
		allTimeDen = s.TotalPlayers
	}

	tiers, completedCount, err := computeCompletionTiers(db, seasonNum, region)
	if err != nil {
		return s, fmt.Errorf("completion tiers: %w", err)
	}
	s.CompletionTiers = tiers
	s.NineOfNinePlayers = completedCount
	// fill percentile fields now that we know all three denominators
	s.CompletionTiers.NineOfNineGold = withPercentiles(s.CompletionTiers.NineOfNineGo
ld, s.TotalPlayers, completedCount, allTimeDen)
	s.CompletionTiers.NineOfNinePlatinum = withPercentiles(s.CompletionTiers.NineOfNi
nePlatinum, s.TotalPlayers, completedCount, allTimeDen)
	s.CompletionTiers.NineOfNineTitle = withPercentiles(s.CompletionTiers.NineOfNineT
itle, s.TotalPlayers, completedCount, allTimeDen)

	if s.SpecCounts, err = computeSpecCounts(db, seasonNum, region); err != nil {
		return s, fmt.Errorf("spec counts: %w", err)
	}

	if s.WeeklyActivity, err = queryWeeklyActivity(db, seasonNum, region); err != nil
 {
		return s, fmt.Errorf("weekly activity: %w", err)
	}

	return s, nil
}

// seasonClauseAndArg returns the SQL fragment and arg slice for filtering by chal
lenge_runs.season_id.
// Empty string for all-time (seasonNum == 0).
func seasonClauseAndArg(seasonNum int, alias string) (string, []any) {
	if seasonNum == 0 {
		return "", nil
	}
	if alias == "" {
		alias = "challenge_runs"
	}
	return fmt.Sprintf(" AND %s.season_id = ?", alias), []any{seasonNum}
}

// regionClauseAndArg filters by realm region. region == "global" returns empty (n
o filter).
// `realmAlias` is the SQL alias of the realms table reference in the FROM/JOIN cl
ause.
func regionClauseAndArg(region, realmAlias string) (string, []any) {
	if region == "" || region == "global" {
		return "", nil
	}
	if realmAlias == "" {
		realmAlias = "r"
	}
	return fmt.Sprintf(" AND %s.region = ?", realmAlias), []any{region}
}

func countTotalRuns(db *sql.DB, seasonNum int, region string) (int64, error) {
	// Region filter requires joining realms; only join when needed to keep the globa
l
	// case as a fast plain COUNT(*) on challenge_runs.
	if region == "" || region == "global" {
		q := "SELECT COUNT(*) FROM challenge_runs cr WHERE 1=1"
		clause, args := seasonClauseAndArg(seasonNum, "cr")
		var n int64
		if err := db.QueryRow(q+clause, args...).Scan(&n); err != nil {
			return 0, err
		}
		return n, nil
	}
	q := "SELECT COUNT(*) FROM challenge_runs cr JOIN realms r ON r.id = cr.realm_id 
WHERE 1=1"
	sClause, sArgs := seasonClauseAndArg(seasonNum, "cr")
	rClause, rArgs := regionClauseAndArg(region, "r")
	args := append(sArgs, rArgs...)
	var n int64
	if err := db.QueryRow(q+sClause+rClause, args...).Scan(&n); err != nil {
		return 0, err
	}
	return n, nil
}

// countTotalPlayers counts distinct players who appear in run_members within scop
e.
// "Within scope" = any player who participated in a run during the given season+r
egion.
func countTotalPlayers(db *sql.DB, seasonNum int, region string) (int64, error) {
	// Always join through challenge_runs so we can filter by season and/or region.
	q := `SELECT COUNT(DISTINCT rm.player_id)
	      FROM run_members rm
	      JOIN challenge_runs cr ON rm.run_id = cr.id`
	if region != "" && region != "global" {
		q += " JOIN realms r ON r.id = cr.realm_id"
	}
	q += " WHERE 1=1"
	sClause, sArgs := seasonClauseAndArg(seasonNum, "cr")
	rClause, rArgs := regionClauseAndArg(region, "r")
	args := append(sArgs, rArgs...)
	var n int64
	if err := db.QueryRow(q+sClause+rClause, args...).Scan(&n); err != nil {
		return 0, err
	}
	return n, nil
}

// tiers are achievements earned within a single season; for all-time scope
// we take the union of per-season qualifiers (hit 9/9 of a tier in any one
// season counts), not combine best times across seasons. returns tier
// counts plus the "completed" denominator (any-season 9-dungeon finishers).
func computeCompletionTiers(db *sql.DB, seasonNum int, region string) (CompletionT
iers, int64, error) {
	q := `
		SELECT rm.player_id, cr.season_id, cr.dungeon_id, MIN(cr.duration) AS best_durat
ion
		FROM run_members rm
		JOIN challenge_runs cr ON rm.run_id = cr.id
		%s
		WHERE 1=1 %s %s
		GROUP BY rm.player_id, cr.season_id, cr.dungeon_id
	`
	regionJoin := ""
	if region != "" && region != "global" {
		regionJoin = "JOIN realms r ON r.id = cr.realm_id"
	}
	sClause, sArgs := seasonClauseAndArg(seasonNum, "cr")
	rClause, rArgs := regionClauseAndArg(region, "r")
	args := append(sArgs, rArgs...)
	rows, err := db.Query(fmt.Sprintf(q, regionJoin, sClause, rClause), args...)
	if err != nil {
		return CompletionTiers{}, 0, err
	}
	defer rows.Close()

	// (player_id, season_id) -> per-tier dungeon-cleared count
	type key struct {
		playerID int64
		seasonID int
	}
	type counts struct{ gold, plat, title, anyDungeon int }
	per := make(map[key]*counts)
	for rows.Next() {
		var playerID int64
		var seasonID, dungeonID int
		var bestDuration int64
		if err := rows.Scan(&playerID, &seasonID, &dungeonID, &bestDuration); err != nil
 {
			return CompletionTiers{}, 0, err
		}
		threshold, ok := dungeonTimerThresholds[dungeonID]
		if !ok {
			continue // unknown dungeon - skip silently
		}
		k := key{playerID, seasonID}
		c := per[k]
		if c == nil {
			c = &counts{}
			per[k] = c
		}
		c.anyDungeon++
		if beatsTier(bestDuration, threshold.GoldMs) {
			c.gold++
		}
		if beatsTier(bestDuration, threshold.PlatinumMs) {
			c.plat++
		}
		if beatsTier(bestDuration, threshold.TitleMs) {
			c.title++
		}
	}
	if err := rows.Err(); err != nil {
		return CompletionTiers{}, 0, err
	}

	// Union per player: did they qualify in *any* season?
	type qualifiers struct{ gold, plat, title, completed bool }
	playerQual := make(map[int64]*qualifiers)
	for k, c := range per {
		q := playerQual[k.playerID]
		if q == nil {
			q = &qualifiers{}
			playerQual[k.playerID] = q
		}
		if c.anyDungeon == totalDungeons {
			q.completed = true
		}
		if c.gold == totalDungeons {
			q.gold = true
		}
		if c.plat == totalDungeons {
			q.plat = true
		}
		if c.title == totalDungeons {
			q.title = true
		}
	}

	var tiers CompletionTiers
	var completedCount int64
	for _, q := range playerQual {
		if q.completed {
			completedCount++
		}
		if q.gold {
			tiers.NineOfNineGold.Count++
		}
		if q.plat {
			tiers.NineOfNinePlatinum.Count++
		}
		if q.title {
			tiers.NineOfNineTitle.Count++
		}
	}
	return tiers, completedCount, nil
}

func withPercentiles(t CompletionTier, totalPlayers, completedPlayers, allTimePlay
ers int64) CompletionTier {
	if totalPlayers > 0 {
		t.PercentileOfAllPlayers = round2(float64(t.Count) * 100.0 / float64(totalPlayer
s))
	}
	if completedPlayers > 0 {
		t.PercentileOfCompletedPlayers = round2(float64(t.Count) * 100.0 / float64(compl
etedPlayers))
	}
	if allTimePlayers > 0 {
		t.PercentileOfAllTimePlayers = round2(float64(t.Count) * 100.0 / float64(allTime
Players))
	}
	return t
}

// computeSpecCounts returns the spec-distribution charts:
//
//	all_runs:      every run_member row in scope
//	gold_runs:     run_members where the run beat the gold threshold for its dungeo
n
//	platinum_runs: same for platinum
//	title_runs:    same for title
//	top_50_runs:   run_members where the run was top-50 in scope (globally team-fil
tered for
//	               region=="global", regionally team-filtered for a specific region
)
//
// Each bucket carries both `count` (slot occurrences, includes spec stacking)
// and `runs_with_spec` (distinct run count, capped at the bucket's total_runs).
func computeSpecCounts(db *sql.DB, seasonNum int, region string) (map[string]SpecC
ountBucket, error) {
	out := map[string]SpecCountBucket{}

	allRuns, err := querySpecCountsForRuns(db, seasonNum, region)
	if err != nil {
		return nil, fmt.Errorf("all_runs: %w", err)
	}
	out["all_runs"] = allRuns

	for _, tier := range []struct {
		key        string
		thresholdF func(dungeonThresholds) int64
	}{
		{"gold_runs", func(t dungeonThresholds) int64 { return t.GoldMs }},
		{"platinum_runs", func(t dungeonThresholds) int64 { return t.PlatinumMs }},
		{"title_runs", func(t dungeonThresholds) int64 { return t.TitleMs }},
	} {
		tierCounts, err := querySpecCountsForTier(db, seasonNum, region, tier.thresholdF
)
		if err != nil {
			return nil, fmt.Errorf("%s: %w", tier.key, err)
		}
		out[tier.key] = tierCounts
	}

	top50, err := querySpecCountsForTop50(db, seasonNum, region)
	if err != nil {
		return nil, fmt.Errorf("top_50_runs: %w", err)
	}
	out["top_50_runs"] = top50

	return out, nil
}

// querySpecCountsForRuns counts run_member spec_ids across all runs in scope.
// Returns slot count + distinct-run count per spec, plus the bucket's total
// distinct run count, plus the same broken out per dungeon.
func querySpecCountsForRuns(db *sql.DB, seasonNum int, region string) (SpecCountBu
cket, error) {
	regionJoin := ""
	if region != "" && region != "global" {
		regionJoin = "JOIN realms r ON r.id = cr.realm_id"
	}
	sClause, sArgs := seasonClauseAndArg(seasonNum, "cr")
	rClause, rArgs := regionClauseAndArg(region, "r")
	args := append(sArgs, rArgs...)

	// each challenge_run has a single dungeon_id, so one (dungeon, spec)
	// grouped query gives us both the per-dungeon and overall totals
	q := `
		SELECT cr.dungeon_id, rm.spec_id,
		       COUNT(*) AS slot_count,
		       COUNT(DISTINCT rm.run_id) AS run_count
		FROM run_members rm
		JOIN challenge_runs cr ON rm.run_id = cr.id
		%s
		WHERE rm.spec_id IS NOT NULL %s %s
		GROUP BY cr.dungeon_id, rm.spec_id
	`
	rows, err := db.Query(fmt.Sprintf(q, regionJoin, sClause, rClause), args...)
	if err != nil {
		return SpecCountBucket{}, err
	}
	defer rows.Close()

	// dungeonId -> specId -> {count, distinctRuns}
	perDungeon := make(map[int]map[int]*specRunCount)
	overall := make(map[int]*specRunCount)
	for rows.Next() {
		var dungeonID, specID int
		var slot, runs int64
		if err := rows.Scan(&dungeonID, &specID, &slot, &runs); err != nil {
			return SpecCountBucket{}, err
		}
		dm, ok := perDungeon[dungeonID]
		if !ok {
			dm = make(map[int]*specRunCount)
			perDungeon[dungeonID] = dm
		}
		dm[specID] = &specRunCount{count: slot, distinctRuns: runs}
		o := overall[specID]
		if o == nil {
			o = &specRunCount{}
			overall[specID] = o
		}
		o.count += slot
		// Each run lives in one dungeon, so distinct runs sum cleanly across dungeons.
		o.distinctRuns += runs
	}
	if err := rows.Err(); err != nil {
		return SpecCountBucket{}, err
	}

	// per-dungeon and overall in one query
	perDungeonTotals, overallTotal, err := queryRunCountsForRuns(db, seasonNum, regio
n)
	if err != nil {
		return SpecCountBucket{}, err
	}

	bucket := SpecCountBucket{
		TotalRuns: overallTotal,
		Entries:   entriesFromCounts(overall),
		ByDungeon: make(map[int]DungeonSpecBucket, len(perDungeonTotals)),
	}
	for dungeonID, total := range perDungeonTotals {
		bucket.ByDungeon[dungeonID] = DungeonSpecBucket{
			TotalRuns: total,
			Entries:   entriesFromCounts(perDungeon[dungeonID]),
		}
	}
	return bucket, nil
}

// queryRunCountsForRuns returns distinct-run counts per dungeon (and the overall
// sum) within scope, used as the denominator for the spec-distribution chart's
// "Runs" metric and for the dungeon-distribution chart.
func queryRunCountsForRuns(db *sql.DB, seasonNum int, region string) (map[int]int6
4, int64, error) {
	regionJoin := ""
	if region != "" && region != "global" {
		regionJoin = "JOIN realms r ON r.id = cr.realm_id"
	}
	sClause, sArgs := seasonClauseAndArg(seasonNum, "cr")
	rClause, rArgs := regionClauseAndArg(region, "r")
	args := append(sArgs, rArgs...)
	q := fmt.Sprintf(`
		SELECT cr.dungeon_id, COUNT(DISTINCT cr.id)
		FROM challenge_runs cr
		%s
		WHERE 1=1 %s %s
		GROUP BY cr.dungeon_id
	`, regionJoin, sClause, rClause)
	rows, err := db.Query(q, args...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()
	perDungeon := make(map[int]int64)
	var total int64
	for rows.Next() {
		var dungeonID int
		var count int64
		if err := rows.Scan(&dungeonID, &count); err != nil {
			return nil, 0, err
		}
		perDungeon[dungeonID] = count
		total += count
	}
	return perDungeon, total, rows.Err()
}

// specRunCount aggregates slot count + distinct-run count for a single spec.
type specRunCount struct {
	count, distinctRuns int64
}

// entriesFromCounts converts a (specId -> {count, distinctRuns}) map into the
// sorted SpecCountEntry slice used by the chart, attaching class/spec metadata.
// Sorted descending by count for stable order; the chart re-orders by canonical
// class+spec layout but tooltips/iteration work nicer on a deterministic input.
func entriesFromCounts(m map[int]*specRunCount) []SpecCountEntry {
	if m == nil {
		return []SpecCountEntry{}
	}
	out := make([]SpecCountEntry, 0, len(m))
	for specID, c := range m {
		entry := SpecCountEntry{SpecID: specID, Count: c.count, RunsWithSpec: c.distinct
Runs}
		if cls, spec, ok := wow.GetClassAndSpec(specID); ok {
			entry.ClassName = cls
			entry.SpecName = spec
		}
		out = append(out, entry)
	}
	for i := 1; i < len(out); i++ {
		for j := i; j > 0 && out[j].Count > out[j-1].Count; j-- {
			out[j], out[j-1] = out[j-1], out[j]
		}
	}
	return out
}

// querySpecCountsForTier counts run_member spec_ids across runs that beat a per-d
ungeon threshold.
// We can't easily express the per-dungeon threshold in a single SQL query, so ins
tead we load
// (run_id, dungeon_id, duration) rows in scope, decide qualification in Go, then 
count specs by
// the qualifying run_id set. We track both slot occurrences and distinct-run-with
-spec counts,
// and a per-dungeon breakdown for the spec-distribution chart's dungeon filter.
func querySpecCountsForTier(db *sql.DB, seasonNum int, region string, thresholdF f
unc(dungeonThresholds) int64) (SpecCountBucket, error) {
	regionJoin := ""
	if region != "" && region != "global" {
		regionJoin = "JOIN realms r ON r.id = cr.realm_id"
	}
	sClause, sArgs := seasonClauseAndArg(seasonNum, "cr")
	rClause, rArgs := regionClauseAndArg(region, "r")
	args := append(sArgs, rArgs...)
	rows, err := db.Query(fmt.Sprintf(`
		SELECT cr.id, cr.dungeon_id, cr.duration
		FROM challenge_runs cr
		%s
		WHERE 1=1 %s %s
	`, regionJoin, sClause, rClause), args...)
	if err != nil {
		return SpecCountBucket{}, err
	}
	// runId -> dungeonId for runs that beat the threshold. Per-dungeon counts
	// fall out of the same map.
	qualifyingDungeon := make(map[int64]int)
	dungeonRunCounts := make(map[int]int64)
	for rows.Next() {
		var id int64
		var dungeonID int
		var duration int64
		if err := rows.Scan(&id, &dungeonID, &duration); err != nil {
			rows.Close()
			return SpecCountBucket{}, err
		}
		threshold, ok := dungeonTimerThresholds[dungeonID]
		if !ok {
			continue
		}
		if beatsTier(duration, thresholdF(threshold)) {
			qualifyingDungeon[id] = dungeonID
			dungeonRunCounts[dungeonID]++
		}
	}
	rows.Close()
	if len(qualifyingDungeon) == 0 {
		return SpecCountBucket{TotalRuns: 0, Entries: []SpecCountEntry{}, ByDungeon: map
[int]DungeonSpecBucket{}}, nil
	}

	// For qualifying runs, count slot occurrences AND distinct runs per spec,
	// both overall and per-dungeon. Per-dungeon distinct-run counting reuses
	// the qualifying map (one run -> one dungeon, no double-count).
	overall := make(map[int]*specAgg)
	perDungeon := make(map[int]map[int]*specAgg)
	memberRows, err := db.Query(`
		SELECT run_id, spec_id
		FROM run_members
		WHERE spec_id IS NOT NULL
	`)
	if err != nil {
		return SpecCountBucket{}, err
	}
	defer memberRows.Close()
	for memberRows.Next() {
		var runID int64
		var specID int
		if err := memberRows.Scan(&runID, &specID); err != nil {
			return SpecCountBucket{}, err
		}
		dungeonID, ok := qualifyingDungeon[runID]
		if !ok {
			continue
		}
		o := overall[specID]
		if o == nil {
			o = &specAgg{runs: make(map[int64]struct{})}
			overall[specID] = o
		}
		o.count++
		o.runs[runID] = struct{}{}

		dm, ok := perDungeon[dungeonID]
		if !ok {
			dm = make(map[int]*specAgg)
			perDungeon[dungeonID] = dm
		}
		da := dm[specID]
		if da == nil {
			da = &specAgg{runs: make(map[int64]struct{})}
			dm[specID] = da
		}
		da.count++
		da.runs[runID] = struct{}{}
	}

	bucket := SpecCountBucket{
		TotalRuns: int64(len(qualifyingDungeon)),
		Entries:   specAggsToSlice(overall),
		ByDungeon: make(map[int]DungeonSpecBucket, len(perDungeon)),
	}
	for dungeonID, total := range dungeonRunCounts {
		bucket.ByDungeon[dungeonID] = DungeonSpecBucket{
			TotalRuns: total,
			Entries:   specAggsToSlice(perDungeon[dungeonID]),
		}
	}
	return bucket, nil
}

// querySpecCountsForTop50 counts spec_ids in top-50 runs.
// region "global" ? top 50 globally (filtered); region "us"/"eu"/etc ? top 50 wit
hin that region.
// Per-dungeon breakdown is also returned (each dungeon's top 50 contributes 50 ru
ns to its bucket).
func querySpecCountsForTop50(db *sql.DB, seasonNum int, region string) (SpecCountB
ucket, error) {
	rankingType := "global"
	rankingScope := "filtered"
	if region != "" && region != "global" {
		rankingType = "regional"
		rankingScope = region + "_filtered"
	}
	// Group by (dungeon, spec) so we get both per-dungeon and overall totals
	// from a single result set. cr.dungeon_id comes from joining challenge_runs.
	q := `
		SELECT cr.dungeon_id, rm.spec_id,
		       COUNT(*) AS slot_count,
		       COUNT(DISTINCT rm.run_id) AS run_count
		FROM run_members rm
		JOIN run_rankings rr ON rm.run_id = rr.run_id
		JOIN challenge_runs cr ON cr.id = rm.run_id
		WHERE rm.spec_id IS NOT NULL
		  AND rr.ranking_type = ?
		  AND rr.ranking_scope = ?
		  AND rr.ranking <= 50
	`
	args := []any{rankingType, rankingScope}
	if seasonNum != 0 {
		q += " AND rr.season_id = ?"
		args = append(args, seasonNum)
	}
	q += " GROUP BY cr.dungeon_id, rm.spec_id"

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

	perDungeon := make(map[int]map[int]*specRunCount)
	overall := make(map[int]*specRunCount)
	for rows.Next() {
		var dungeonID, specID int
		var slot, runs int64
		if err := rows.Scan(&dungeonID, &specID, &slot, &runs); err != nil {
			return SpecCountBucket{}, err
		}
		dm, ok := perDungeon[dungeonID]
		if !ok {
			dm = make(map[int]*specRunCount)
			perDungeon[dungeonID] = dm
		}
		dm[specID] = &specRunCount{count: slot, distinctRuns: runs}
		o := overall[specID]
		if o == nil {
			o = &specRunCount{}
			overall[specID] = o
		}
		o.count += slot
		o.distinctRuns += runs
	}
	if err := rows.Err(); err != nil {
		return SpecCountBucket{}, err
	}

	// Per-dungeon distinct top-50 run counts (and total).
	totalsQ := `
		SELECT cr.dungeon_id, COUNT(DISTINCT rr.run_id)
		FROM run_rankings rr
		JOIN challenge_runs cr ON cr.id = rr.run_id
		WHERE rr.ranking_type = ?
		  AND rr.ranking_scope = ?
		  AND rr.ranking <= 50
	`
	totalsArgs := []any{rankingType, rankingScope}
	if seasonNum != 0 {
		totalsQ += " AND rr.season_id = ?"
		totalsArgs = append(totalsArgs, seasonNum)
	}
	totalsQ += " GROUP BY cr.dungeon_id"
	totalRows, err := db.Query(totalsQ, totalsArgs...)
	if err != nil {
		return SpecCountBucket{}, err
	}
	defer totalRows.Close()
	dungeonTotals := make(map[int]int64)
	var grandTotal int64
	for totalRows.Next() {
		var dungeonID int
		var count int64
		if err := totalRows.Scan(&dungeonID, &count); err != nil {
			return SpecCountBucket{}, err
		}
		dungeonTotals[dungeonID] = count
		grandTotal += count
	}

	bucket := SpecCountBucket{
		TotalRuns: grandTotal,
		Entries:   entriesFromCounts(overall),
		ByDungeon: make(map[int]DungeonSpecBucket, len(dungeonTotals)),
	}
	for dungeonID, total := range dungeonTotals {
		bucket.ByDungeon[dungeonID] = DungeonSpecBucket{
			TotalRuns: total,
			Entries:   entriesFromCounts(perDungeon[dungeonID]),
		}
	}
	return bucket, nil
}

// in-memory aggregation when SQL counting is awkward (see querySpecCountsForTier)
type specAgg struct {
	count int64
	runs  map[int64]struct{}
}

func specAggsToSlice(aggs map[int]*specAgg) []SpecCountEntry {
	out := make([]SpecCountEntry, 0, len(aggs))
	for specID, a := range aggs {
		entry := SpecCountEntry{SpecID: specID, Count: a.count, RunsWithSpec: int64(len(
a.runs))}
		if cls, spec, ok := wow.GetClassAndSpec(specID); ok {
			entry.ClassName = cls
			entry.SpecName = spec
		}
		out = append(out, entry)
	}
	// simple insertion sort by count desc (small N: ~33 specs)
	for i := 1; i < len(out); i++ {
		for j := i; j > 0 && out[j].Count > out[j-1].Count; j-- {
			out[j], out[j-1] = out[j-1], out[j]
		}
	}
	return out
}

// queryWeeklyActivity returns runs grouped by Monday-start week.
// Timestamps are stored in milliseconds; SQLite date functions need seconds.
func queryWeeklyActivity(db *sql.DB, seasonNum int, region string) ([]WeeklyActivi
tyEntry, error) {
	regionJoin := ""
	if region != "" && region != "global" {
		regionJoin = "JOIN realms r ON r.id = cr.realm_id"
	}
	sClause, sArgs := seasonClauseAndArg(seasonNum, "cr")
	rClause, rArgs := regionClauseAndArg(region, "r")
	args := append(sArgs, rArgs...)
	// 'weekday 1' advances to next Monday; '-7 days' rolls back to start of current 
week.
	q := fmt.Sprintf(`
		SELECT
			date(cr.completed_timestamp / 1000, 'unixepoch', 'weekday 1', '-7 days') AS wee
k_start,
			COUNT(*) AS run_count
		FROM challenge_runs cr
		%s
		WHERE cr.completed_timestamp IS NOT NULL %s %s
		GROUP BY week_start
		ORDER BY week_start
	`, regionJoin, sClause, rClause)
	rows, err := db.Query(q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []WeeklyActivityEntry
	for rows.Next() {
		var e WeeklyActivityEntry
		if err := rows.Scan(&e.WeekStart, &e.RunCount); err != nil {
			return nil, err
		}
		out = append(out, e)
	}
	return out, rows.Err()
}

func round2(f float64) float64 {
	return float64(int64(f*100+0.5)) / 100
}


package generator

import (
	"database/sql"
	"fmt"
	"path/filepath"
	"time"

	"ookstats/internal/wow"
	"ookstats/internal/writer"
)

// StatsJSON is the top-level shape for web/
public/api/stats.json.
// Three scopes are emitted: cross-season "a
ll_time" plus one entry per season number.
type StatsJSON struct {
	GeneratedAt int64 `json:"generated_at"`
	// Scopes is keyed by region (global/us/eu/
kr/tw), then by season key
	// (all_time/season_1/season_2). Region "gl
obal" includes all regions.
	Scopes map[string]map[string]StatsScope `js
on:"scopes"`
}

// StatsScope holds aggregated stats for a s
ingle scope (all-time or one season).
type StatsScope struct {
	TotalRuns         int64                    
  `json:"total_runs"`
	TotalPlayers      int64                    
  `json:"total_players"`
	NineOfNinePlayers int64                    
  `json:"nine_of_nine_players"`
	CompletionTiers   CompletionTiers          
  `json:"completion_tiers"`
	SpecCounts        map[string]SpecCountBucke
t `json:"spec_counts"`
	WeeklyActivity    []WeeklyActivityEntry    
  `json:"weekly_activity"`
}

// TotalRuns is the distinct-run denominator
 so the chart can switch between
// "total slot count" and "% of runs contain
ing the spec" without recomputing
type SpecCountBucket struct {
	TotalRuns int64                     `json:"
total_runs"`
	Entries   []SpecCountEntry          `json:"
entries"`
	ByDungeon map[int]DungeonSpecBucket `json:"
by_dungeon"`
}

type DungeonSpecBucket struct {
	TotalRuns int64            `json:"total_run
s"`
	Entries   []SpecCountEntry `json:"entries"`
}

type CompletionTiers struct {
	NineOfNineGold     CompletionTier `json:"9_
of_9_gold"`
	NineOfNinePlatinum CompletionTier `json:"9_
of_9_platinum"`
	NineOfNineTitle    CompletionTier `json:"9_
of_9_title"`
}

// percentile denominators:
// of_all_players       = scoped pool
// of_completed_players = finishers (players
_with_9_dungeon_bests)
// of_all_time_players  = stable cross-scope
 denominator
type CompletionTier struct {
	Count                        int64   `json:
"count"`
	PercentileOfAllPlayers       float64 `json:
"percentile_of_all_players"`
	PercentileOfCompletedPlayers float64 `json:
"percentile_of_completed_players"`
	PercentileOfAllTimePlayers   float64 `json:
"percentile_of_all_time_players"`
}

// `count` is total run_member spec slots (a
 run with two of the same spec
// contributes 2); `runs_with_spec` is disti
nct runs containing the spec
// (same run with two of the spec contribute
s 1, capped at total_runs)
type SpecCountEntry struct {
	SpecID       int    `json:"spec_id"`
	ClassName    string `json:"class_name"`
	SpecName     string `json:"spec_name"`
	Count        int64  `json:"count"`
	RunsWithSpec int64  `json:"runs_with_spec"`
}

type WeeklyActivityEntry struct {
	WeekStart string `json:"week_start"` // YYY
Y-MM-DD
	RunCount  int64  `json:"run_count"`
}

// gold/platinum/title timer thresholds in m
s; gold is the achievement par
// time, platinum and title are tighter comm
unity-defined cutoffs
type dungeonThresholds struct {
	GoldMs     int64
	PlatinumMs int64
	TitleMs    int64
}

var dungeonTimerThresholds = map[int]dungeon
Thresholds{
	2:  {GoldMs: 900000, PlatinumMs: 615000, Ti
tleMs: 510000},  // Temple of the Jade Serpe
nt (15:00 / 10:15 / 8:30)
	56: {GoldMs: 720000, PlatinumMs: 495000, Ti
tleMs: 390000},  // Stormstout Brewery      
  (12:00 / 8:15 / 6:30)
	57: {GoldMs: 780000, PlatinumMs: 480000, Ti
tleMs: 330000},  // Gate of the Setting Sun 
  (13:00 / 8:00 / 5:30)
	58: {GoldMs: 1260000, PlatinumMs: 840000, T
itleMs: 630000}, // Shado-Pan Monastery     
  (21:00 / 14:00 / 10:30)
	59: {GoldMs: 1050000, PlatinumMs: 735000, T
itleMs: 615000}, // Siege of Niuzao Temple  
  (17:30 / 12:15 / 10:15)
	60: {GoldMs: 720000, PlatinumMs: 495000, Ti
tleMs: 405000},  // Mogu'shan Palace        
  (12:00 / 8:15 / 6:45)
	76: {GoldMs: 1140000, PlatinumMs: 615000, T
itleMs: 435000}, // Scholomance             
  (19:00 / 10:15 / 7:15)
	77: {GoldMs: 780000, PlatinumMs: 480000, Ti
tleMs: 255000},  // Scarlet Halls           
  (13:00 / 8:00 / 4:15)
	78: {GoldMs: 780000, PlatinumMs: 540000, Ti
tleMs: 330000},  // Scarlet Monastery       
  (13:00 / 9:00 / 5:30)
}

// must clear all of them to qualify for 9/9
var totalDungeons = len(dungeonTimerThreshol
ds)

// in-game cutoffs are lenient: a 5:30.800 r
un beats a "5:30" title cutoff
// because the timer reads "5:30" until it t
icks to 5:31, so pass condition
// is `duration < threshold + leniencyMs`
const leniencyMs int64 = 1000

func beatsTier(duration, thresholdMs int64) 
bool {
	return duration < thresholdMs+leniencyMs
}

// order matters for JSON readability
type statsScopeDef struct {
	Key       string
	SeasonNum int // 0 = all-time
}

// statsRegions are the region scopes emitte
d; "global" means no region filter.
var statsRegions = []string{"global", "us", 
"eu", "kr", "tw"}

// GenerateStats writes outDir/api/stats.jso
n with aggregated stats per (region, season)
 scope.
func GenerateStats(db *sql.DB, outDir string
) error {
	statsScopes, err := loadStatsScopes(db)
	if err != nil {
		return fmt.Errorf("load stats scopes: %w",
 err)
	}

	out := StatsJSON{
		GeneratedAt: time.Now().UnixMilli(),
		Scopes:      make(map[string]map[string]St
atsScope, len(statsRegions)),
	}

	for _, region := range statsRegions {
		regionScopes := make(map[string]StatsScope
, len(statsScopes))
		// Compute all-time first per region so it
s total_players is the stable
		// denominator for per-season scopes withi
n that region.
		var allTimeTotalPlayers int64
		for _, sc := range statsScopes {
			ss, err := buildStatsScope(db, sc.SeasonN
um, region, allTimeTotalPlayers)
			if err != nil {
				return fmt.Errorf("scope %s/%s: %w", reg
ion, sc.Key, err)
			}
			if sc.SeasonNum == 0 {
				allTimeTotalPlayers = ss.TotalPlayers
			}
			regionScopes[sc.Key] = ss
		}
		out.Scopes[region] = regionScopes
	}

	outPath := filepath.Join(outDir, "api", "st
ats.json")
	if err := writer.WriteJSONFileCompact(outPa
th, out); err != nil {
		return fmt.Errorf("write stats.json: %w", 
err)
	}
	return nil
}

// loadStatsScopes returns all-time plus one
 scope per season in the DB
func loadStatsScopes(db *sql.DB) ([]statsSco
peDef, error) {
	rows, err := db.Query(`SELECT DISTINCT seas
on_number FROM seasons ORDER BY season_numbe
r ASC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	scopes := []statsScopeDef{{Key: "all_time",
 SeasonNum: 0}}
	for rows.Next() {
		var n int
		if err := rows.Scan(&n); err != nil {
			return nil, err
		}
		scopes = append(scopes, statsScopeDef{Key:
 fmt.Sprintf("season_%d", n), SeasonNum: n})
	}
	return scopes, rows.Err()
}

// buildStatsScope assembles a single StatsS
cope.
// seasonNum == 0 means cross-season (all-ti
me). region == "global" means no region filt
er.
// For all-time, pass allTimeTotalPlayers=0;
 for per-season builds, pass the all-time sc
ope's
// TotalPlayers (within the same region) so 
the `percentile_of_all_time_players` field
// uses a stable denominator.
func buildStatsScope(db *sql.DB, seasonNum i
nt, region string, allTimeTotalPlayers int64
) (StatsScope, error) {
	var s StatsScope
	var err error

	if s.TotalRuns, err = countTotalRuns(db, se
asonNum, region); err != nil {
		return s, fmt.Errorf("total runs: %w", err
)
	}

	if s.TotalPlayers, err = countTotalPlayers(
db, seasonNum, region); err != nil {
		return s, fmt.Errorf("total players: %w", 
err)
	}

	// On all-time, the stable denominator is j
ust this scope's own TotalPlayers.
	allTimeDen := allTimeTotalPlayers
	if seasonNum == 0 {
		allTimeDen = s.TotalPlayers
	}

	tiers, completedCount, err := computeComple
tionTiers(db, seasonNum, region)
	if err != nil {
		return s, fmt.Errorf("completion tiers: %w
", err)
	}
	s.CompletionTiers = tiers
	s.NineOfNinePlayers = completedCount
	// fill percentile fields now that we know 
all three denominators
	s.CompletionTiers.NineOfNineGold = withPerc
entiles(s.CompletionTiers.NineOfNineGold, s.
TotalPlayers, completedCount, allTimeDen)
	s.CompletionTiers.NineOfNinePlatinum = with
Percentiles(s.CompletionTiers.NineOfNinePlat
inum, s.TotalPlayers, completedCount, allTim
eDen)
	s.CompletionTiers.NineOfNineTitle = withPer
centiles(s.CompletionTiers.NineOfNineTitle, 
s.TotalPlayers, completedCount, allTimeDen)

	if s.SpecCounts, err = computeSpecCounts(db
, seasonNum, region); err != nil {
		return s, fmt.Errorf("spec counts: %w", er
r)
	}

	if s.WeeklyActivity, err = queryWeeklyActiv
ity(db, seasonNum, region); err != nil {
		return s, fmt.Errorf("weekly activity: %w"
, err)
	}

	return s, nil
}

// seasonClauseAndArg returns the SQL fragme
nt and arg slice for filtering by challenge_
runs.season_id.
// Empty string for all-time (seasonNum == 0
).
func seasonClauseAndArg(seasonNum int, alias
 string) (string, []any) {
	if seasonNum == 0 {
		return "", nil
	}
	if alias == "" {
		alias = "challenge_runs"
	}
	return fmt.Sprintf(" AND %s.season_id = ?",
 alias), []any{seasonNum}
}

// regionClauseAndArg filters by realm regio
n. region == "global" returns empty (no filt
er).
// `realmAlias` is the SQL alias of the real
ms table reference in the FROM/JOIN clause.
func regionClauseAndArg(region, realmAlias s
tring) (string, []any) {
	if region == "" || region == "global" {
		return "", nil
	}
	if realmAlias == "" {
		realmAlias = "r"
	}
	return fmt.Sprintf(" AND %s.region = ?", re
almAlias), []any{region}
}

func countTotalRuns(db *sql.DB, seasonNum in
t, region string) (int64, error) {
	// Region filter requires joining realms; o
nly join when needed to keep the global
	// case as a fast plain COUNT(*) on challen
ge_runs.
	if region == "" || region == "global" {
		q := "SELECT COUNT(*) FROM challenge_runs 
cr WHERE 1=1"
		clause, args := seasonClauseAndArg(seasonN
um, "cr")
		var n int64
		if err := db.QueryRow(q+clause, args...).S
can(&n); err != nil {
			return 0, err
		}
		return n, nil
	}
	q := "SELECT COUNT(*) FROM challenge_runs c
r JOIN realms r ON r.id = cr.realm_id WHERE 
1=1"
	sClause, sArgs := seasonClauseAndArg(season
Num, "cr")
	rClause, rArgs := regionClauseAndArg(region
, "r")
	args := append(sArgs, rArgs...)
	var n int64
	if err := db.QueryRow(q+sClause+rClause, ar
gs...).Scan(&n); err != nil {
		return 0, err
	}
	return n, nil
}

// countTotalPlayers counts distinct players
 who appear in run_members within scope.
// "Within scope" = any player who participa
ted in a run during the given season+region.
func countTotalPlayers(db *sql.DB, seasonNum
 int, region string) (int64, error) {
	// Always join through challenge_runs so we
 can filter by season and/or region.
	q := `SELECT COUNT(DISTINCT rm.player_id)
	      FROM run_members rm
	      JOIN challenge_runs cr ON rm.run_id =
 cr.id`
	if region != "" && region != "global" {
		q += " JOIN realms r ON r.id = cr.realm_id
"
	}
	q += " WHERE 1=1"
	sClause, sArgs := seasonClauseAndArg(season
Num, "cr")
	rClause, rArgs := regionClauseAndArg(region
, "r")
	args := append(sArgs, rArgs...)
	var n int64
	if err := db.QueryRow(q+sClause+rClause, ar
gs...).Scan(&n); err != nil {
		return 0, err
	}
	return n, nil
}

// tiers are achievements earned within a si
ngle season; for all-time scope
// we take the union of per-season qualifier
s (hit 9/9 of a tier in any one
// season counts), not combine best times ac
ross seasons. returns tier
// counts plus the "completed" denominator (
any-season 9-dungeon finishers).
func computeCompletionTiers(db *sql.DB, seas
onNum int, region string) (CompletionTiers, 
int64, error) {
	q := `
		SELECT rm.player_id, cr.season_id, cr.dung
eon_id, MIN(cr.duration) AS best_duration
		FROM run_members rm
		JOIN challenge_runs cr ON rm.run_id = cr.i
d
		%s
		WHERE 1=1 %s %s
		GROUP BY rm.player_id, cr.season_id, cr.du
ngeon_id
	`
	regionJoin := ""
	if region != "" && region != "global" {
		regionJoin = "JOIN realms r ON r.id = cr.r
ealm_id"
	}
	sClause, sArgs := seasonClauseAndArg(season
Num, "cr")
	rClause, rArgs := regionClauseAndArg(region
, "r")
	args := append(sArgs, rArgs...)
	rows, err := db.Query(fmt.Sprintf(q, region
Join, sClause, rClause), args...)
	if err != nil {
		return CompletionTiers{}, 0, err
	}
	defer rows.Close()

	// (player_id, season_id) -> per-tier dunge
on-cleared count
	type key struct {
		playerID int64
		seasonID int
	}
	type counts struct{ gold, plat, title, anyD
ungeon int }
	per := make(map[key]*counts)
	for rows.Next() {
		var playerID int64
		var seasonID, dungeonID int
		var bestDuration int64
		if err := rows.Scan(&playerID, &seasonID, 
&dungeonID, &bestDuration); err != nil {
			return CompletionTiers{}, 0, err
		}
		threshold, ok := dungeonTimerThresholds[du
ngeonID]
		if !ok {
			continue // unknown dungeon - skip silent
ly
		}
		k := key{playerID, seasonID}
		c := per[k]
		if c == nil {
			c = &counts{}
			per[k] = c
		}
		c.anyDungeon++
		if beatsTier(bestDuration, threshold.GoldM
s) {
			c.gold++
		}
		if beatsTier(bestDuration, threshold.Plati
numMs) {
			c.plat++
		}
		if beatsTier(bestDuration, threshold.Title
Ms) {
			c.title++
		}
	}
	if err := rows.Err(); err != nil {
		return CompletionTiers{}, 0, err
	}

	// Union per player: did they qualify in *a
ny* season?
	type qualifiers struct{ gold, plat, title, 
completed bool }
	playerQual := make(map[int64]*qualifiers)
	for k, c := range per {
		q := playerQual[k.playerID]
		if q == nil {
			q = &qualifiers{}
			playerQual[k.playerID] = q
		}
		if c.anyDungeon == totalDungeons {
			q.completed = true
		}
		if c.gold == totalDungeons {
			q.gold = true
		}
		if c.plat == totalDungeons {
			q.plat = true
		}
		if c.title == totalDungeons {
			q.title = true
		}
	}

	var tiers CompletionTiers
	var completedCount int64
	for _, q := range playerQual {
		if q.completed {
			completedCount++
		}
		if q.gold {
			tiers.NineOfNineGold.Count++
		}
		if q.plat {
			tiers.NineOfNinePlatinum.Count++
		}
		if q.title {
			tiers.NineOfNineTitle.Count++
		}
	}
	return tiers, completedCount, nil
}

func withPercentiles(t CompletionTier, total
Players, completedPlayers, allTimePlayers in
t64) CompletionTier {
	if totalPlayers > 0 {
		t.PercentileOfAllPlayers = round2(float64(
t.Count) * 100.0 / float64(totalPlayers))
	}
	if completedPlayers > 0 {
		t.PercentileOfCompletedPlayers = round2(fl
oat64(t.Count) * 100.0 / float64(completedPl
ayers))
	}
	if allTimePlayers > 0 {
		t.PercentileOfAllTimePlayers = round2(floa
t64(t.Count) * 100.0 / float64(allTimePlayer
s))
	}
	return t
}

// computeSpecCounts returns the spec-distri
bution charts:
//
//	all_runs:      every run_member row in sc
ope
//	gold_runs:     run_members where the run 
beat the gold threshold for its dungeon
//	platinum_runs: same for platinum
//	title_runs:    same for title
//	top_50_runs:   run_members where the run 
was top-50 in scope (globally team-filtered 
for
//	               region=="global", regional
ly team-filtered for a specific region)
//
// Each bucket carries both `count` (slot oc
currences, includes spec stacking)
// and `runs_with_spec` (distinct run count,
 capped at the bucket's total_runs).
func computeSpecCounts(db *sql.DB, seasonNum
 int, region string) (map[string]SpecCountBu
cket, error) {
	out := map[string]SpecCountBucket{}

	allRuns, err := querySpecCountsForRuns(db, 
seasonNum, region)
	if err != nil {
		return nil, fmt.Errorf("all_runs: %w", err
)
	}
	out["all_runs"] = allRuns

	for _, tier := range []struct {
		key        string
		thresholdF func(dungeonThresholds) int64
	}{
		{"gold_runs", func(t dungeonThresholds) in
t64 { return t.GoldMs }},
		{"platinum_runs", func(t dungeonThresholds
) int64 { return t.PlatinumMs }},
		{"title_runs", func(t dungeonThresholds) i
nt64 { return t.TitleMs }},
	} {
		tierCounts, err := querySpecCountsForTier(
db, seasonNum, region, tier.thresholdF)
		if err != nil {
			return nil, fmt.Errorf("%s: %w", tier.key
, err)
		}
		out[tier.key] = tierCounts
	}

	top50, err := querySpecCountsForTop50(db, s
easonNum, region)
	if err != nil {
		return nil, fmt.Errorf("top_50_runs: %w", 
err)
	}
	out["top_50_runs"] = top50

	return out, nil
}

// querySpecCountsForRuns counts run_member 
spec_ids across all runs in scope.
// Returns slot count + distinct-run count p
er spec, plus the bucket's total
// distinct run count, plus the same broken 
out per dungeon.
func querySpecCountsForRuns(db *sql.DB, seas
onNum int, region string) (SpecCountBucket, 
error) {
	regionJoin := ""
	if region != "" && region != "global" {
		regionJoin = "JOIN realms r ON r.id = cr.r
ealm_id"
	}
	sClause, sArgs := seasonClauseAndArg(season
Num, "cr")
	rClause, rArgs := regionClauseAndArg(region
, "r")
	args := append(sArgs, rArgs...)

	// each challenge_run has a single dungeon_
id, so one (dungeon, spec)
	// grouped query gives us both the per-dung
eon and overall totals
	q := `
		SELECT cr.dungeon_id, rm.spec_id,
		       COUNT(*) AS slot_count,
		       COUNT(DISTINCT rm.run_id) AS run_co
unt
		FROM run_members rm
		JOIN challenge_runs cr ON rm.run_id = cr.i
d
		%s
		WHERE rm.spec_id IS NOT NULL %s %s
		GROUP BY cr.dungeon_id, rm.spec_id
	`
	rows, err := db.Query(fmt.Sprintf(q, region
Join, sClause, rClause), args...)
	if err != nil {
		return SpecCountBucket{}, err
	}
	defer rows.Close()

	// dungeonId -> specId -> {count, distinctR
uns}
	perDungeon := make(map[int]map[int]*specRun
Count)
	overall := make(map[int]*specRunCount)
	for rows.Next() {
		var dungeonID, specID int
		var slot, runs int64
		if err := rows.Scan(&dungeonID, &specID, &
slot, &runs); err != nil {
			return SpecCountBucket{}, err
		}
		dm, ok := perDungeon[dungeonID]
		if !ok {
			dm = make(map[int]*specRunCount)
			perDungeon[dungeonID] = dm
		}
		dm[specID] = &specRunCount{count: slot, di
stinctRuns: runs}
		o := overall[specID]
		if o == nil {
			o = &specRunCount{}
			overall[specID] = o
		}
		o.count += slot
		// Each run lives in one dungeon, so disti
nct runs sum cleanly across dungeons.
		o.distinctRuns += runs
	}
	if err := rows.Err(); err != nil {
		return SpecCountBucket{}, err
	}

	// per-dungeon and overall in one query
	perDungeonTotals, overallTotal, err := quer
yRunCountsForRuns(db, seasonNum, region)
	if err != nil {
		return SpecCountBucket{}, err
	}

	bucket := SpecCountBucket{
		TotalRuns: overallTotal,
		Entries:   entriesFromCounts(overall),
		ByDungeon: make(map[int]DungeonSpecBucket,
 len(perDungeonTotals)),
	}
	for dungeonID, total := range perDungeonTot
als {
		bucket.ByDungeon[dungeonID] = DungeonSpecB
ucket{
			TotalRuns: total,
			Entries:   entriesFromCounts(perDungeon[d
ungeonID]),
		}
	}
	return bucket, nil
}

// queryRunCountsForRuns returns distinct-ru
n counts per dungeon (and the overall
// sum) within scope, used as the denominato
r for the spec-distribution chart's
// "Runs" metric and for the dungeon-distrib
ution chart.
func queryRunCountsForRuns(db *sql.DB, seaso
nNum int, region string) (map[int]int64, int
64, error) {
	regionJoin := ""
	if region != "" && region != "global" {
		regionJoin = "JOIN realms r ON r.id = cr.r
ealm_id"
	}
	sClause, sArgs := seasonClauseAndArg(season
Num, "cr")
	rClause, rArgs := regionClauseAndArg(region
, "r")
	args := append(sArgs, rArgs...)
	q := fmt.Sprintf(`
		SELECT cr.dungeon_id, COUNT(DISTINCT cr.id
)
		FROM challenge_runs cr
		%s
		WHERE 1=1 %s %s
		GROUP BY cr.dungeon_id
	`, regionJoin, sClause, rClause)
	rows, err := db.Query(q, args...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()
	perDungeon := make(map[int]int64)
	var total int64
	for rows.Next() {
		var dungeonID int
		var count int64
		if err := rows.Scan(&dungeonID, &count); e
rr != nil {
			return nil, 0, err
		}
		perDungeon[dungeonID] = count
		total += count
	}
	return perDungeon, total, rows.Err()
}

// specRunCount aggregates slot count + dist
inct-run count for a single spec.
type specRunCount struct {
	count, distinctRuns int64
}

// entriesFromCounts converts a (specId -> {
count, distinctRuns}) map into the
// sorted SpecCountEntry slice used by the c
hart, attaching class/spec metadata.
// Sorted descending by count for stable ord
er; the chart re-orders by canonical
// class+spec layout but tooltips/iteration 
work nicer on a deterministic input.
func entriesFromCounts(m map[int]*specRunCou
nt) []SpecCountEntry {
	if m == nil {
		return []SpecCountEntry{}
	}
	out := make([]SpecCountEntry, 0, len(m))
	for specID, c := range m {
		entry := SpecCountEntry{SpecID: specID, Co
unt: c.count, RunsWithSpec: c.distinctRuns}
		if cls, spec, ok := wow.GetClassAndSpec(sp
ecID); ok {
			entry.ClassName = cls
			entry.SpecName = spec
		}
		out = append(out, entry)
	}
	for i := 1; i < len(out); i++ {
		for j := i; j > 0 && out[j].Count > out[j-
1].Count; j-- {
			out[j], out[j-1] = out[j-1], out[j]
		}
	}
	return out
}

// querySpecCountsForTier counts run_member 
spec_ids across runs that beat a per-dungeon
 threshold.
// We can't easily express the per-dungeon t
hreshold in a single SQL query, so instead w
e load
// (run_id, dungeon_id, duration) rows in sc
ope, decide qualification in Go, then count 
specs by
// the qualifying run_id set. We track both 
slot occurrences and distinct-run-with-spec 
counts,
// and a per-dungeon breakdown for the spec-
distribution chart's dungeon filter.
func querySpecCountsForTier(db *sql.DB, seas
onNum int, region string, thresholdF func(du
ngeonThresholds) int64) (SpecCountBucket, er
ror) {
	regionJoin := ""
	if region != "" && region != "global" {
		regionJoin = "JOIN realms r ON r.id = cr.r
ealm_id"
	}
	sClause, sArgs := seasonClauseAndArg(season
Num, "cr")
	rClause, rArgs := regionClauseAndArg(region
, "r")
	args := append(sArgs, rArgs...)
	rows, err := db.Query(fmt.Sprintf(`
		SELECT cr.id, cr.dungeon_id, cr.duration
		FROM challenge_runs cr
		%s
		WHERE 1=1 %s %s
	`, regionJoin, sClause, rClause), args...)
	if err != nil {
		return SpecCountBucket{}, err
	}
	// runId -> dungeonId for runs that beat th
e threshold. Per-dungeon counts
	// fall out of the same map.
	qualifyingDungeon := make(map[int64]int)
	dungeonRunCounts := make(map[int]int64)
	for rows.Next() {
		var id int64
		var dungeonID int
		var duration int64
		if err := rows.Scan(&id, &dungeonID, &dura
tion); err != nil {
			rows.Close()
			return SpecCountBucket{}, err
		}
		threshold, ok := dungeonTimerThresholds[du
ngeonID]
		if !ok {
			continue
		}
		if beatsTier(duration, thresholdF(threshol
d)) {
			qualifyingDungeon[id] = dungeonID
			dungeonRunCounts[dungeonID]++
		}
	}
	rows.Close()
	if len(qualifyingDungeon) == 0 {
		return SpecCountBucket{TotalRuns: 0, Entri
es: []SpecCountEntry{}, ByDungeon: map[int]D
ungeonSpecBucket{}}, nil
	}

	// For qualifying runs, count slot occurren
ces AND distinct runs per spec,
	// both overall and per-dungeon. Per-dungeo
n distinct-run counting reuses
	// the qualifying map (one run -> one dunge
on, no double-count).
	overall := make(map[int]*specAgg)
	perDungeon := make(map[int]map[int]*specAgg
)
	memberRows, err := db.Query(`
		SELECT run_id, spec_id
		FROM run_members
		WHERE spec_id IS NOT NULL
	`)
	if err != nil {
		return SpecCountBucket{}, err
	}
	defer memberRows.Close()
	for memberRows.Next() {
		var runID int64
		var specID int
		if err := memberRows.Scan(&runID, &specID)
; err != nil {
			return SpecCountBucket{}, err
		}
		dungeonID, ok := qualifyingDungeon[runID]
		if !ok {
			continue
		}
		o := overall[specID]
		if o == nil {
			o = &specAgg{runs: make(map[int64]struct{
})}
			overall[specID] = o
		}
		o.count++
		o.runs[runID] = struct{}{}

		dm, ok := perDungeon[dungeonID]
		if !ok {
			dm = make(map[int]*specAgg)
			perDungeon[dungeonID] = dm
		}
		da := dm[specID]
		if da == nil {
			da = &specAgg{runs: make(map[int64]struct
{})}
			dm[specID] = da
		}
		da.count++
		da.runs[runID] = struct{}{}
	}

	bucket := SpecCountBucket{
		TotalRuns: int64(len(qualifyingDungeon)),
		Entries:   specAggsToSlice(overall),
		ByDungeon: make(map[int]DungeonSpecBucket,
 len(perDungeon)),
	}
	for dungeonID, total := range dungeonRunCou
nts {
		bucket.ByDungeon[dungeonID] = DungeonSpecB
ucket{
			TotalRuns: total,
			Entries:   specAggsToSlice(perDungeon[dun
geonID]),
		}
	}
	return bucket, nil
}

// querySpecCountsForTop50 counts spec_ids i
n top-50 runs.
// region "global" ? top 50 globally (filter
ed); region "us"/"eu"/etc ? top 50 within th
at region.
// Per-dungeon breakdown is also returned (e
ach dungeon's top 50 contributes 50 runs to 
its bucket).
func querySpecCountsForTop50(db *sql.DB, sea
sonNum int, region string) (SpecCountBucket,
 error) {
	rankingType := "global"
	rankingScope := "filtered"
	if region != "" && region != "global" {
		rankingType = "regional"
		rankingScope = region + "_filtered"
	}
	// Group by (dungeon, spec) so we get both 
per-dungeon and overall totals
	// from a single result set. cr.dungeon_id 
comes from joining challenge_runs.
	q := `
		SELECT cr.dungeon_id, rm.spec_id,
		       COUNT(*) AS slot_count,
		       COUNT(DISTINCT rm.run_id) AS run_co
unt
		FROM run_members rm
		JOIN run_rankings rr ON rm.run_id = rr.run
_id
		JOIN challenge_runs cr ON cr.id = rm.run_i
d
		WHERE rm.spec_id IS NOT NULL
		  AND rr.ranking_type = ?
		  AND rr.ranking_scope = ?
		  AND rr.ranking <= 50
	`
	args := []any{rankingType, rankingScope}
	if seasonNum != 0 {
		q += " AND rr.season_id = ?"
		args = append(args, seasonNum)
	}
	q += " GROUP BY cr.dungeon_id, rm.spec_id"

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

	perDungeon := make(map[int]map[int]*specRun
Count)
	overall := make(map[int]*specRunCount)
	for rows.Next() {
		var dungeonID, specID int
		var slot, runs int64
		if err := rows.Scan(&dungeonID, &specID, &
slot, &runs); err != nil {
			return SpecCountBucket{}, err
		}
		dm, ok := perDungeon[dungeonID]
		if !ok {
			dm = make(map[int]*specRunCount)
			perDungeon[dungeonID] = dm
		}
		dm[specID] = &specRunCount{count: slot, di
stinctRuns: runs}
		o := overall[specID]
		if o == nil {
			o = &specRunCount{}
			overall[specID] = o
		}
		o.count += slot
		o.distinctRuns += runs
	}
	if err := rows.Err(); err != nil {
		return SpecCountBucket{}, err
	}

	// Per-dungeon distinct top-50 run counts (
and total).
	totalsQ := `
		SELECT cr.dungeon_id, COUNT(DISTINCT rr.ru
n_id)
		FROM run_rankings rr
		JOIN challenge_runs cr ON cr.id = rr.run_i
d
		WHERE rr.ranking_type = ?
		  AND rr.ranking_scope = ?
		  AND rr.ranking <= 50
	`
	totalsArgs := []any{rankingType, rankingSco
pe}
	if seasonNum != 0 {
		totalsQ += " AND rr.season_id = ?"
		totalsArgs = append(totalsArgs, seasonNum)
	}
	totalsQ += " GROUP BY cr.dungeon_id"
	totalRows, err := db.Query(totalsQ, totalsA
rgs...)
	if err != nil {
		return SpecCountBucket{}, err
	}
	defer totalRows.Close()
	dungeonTotals := make(map[int]int64)
	var grandTotal int64
	for totalRows.Next() {
		var dungeonID int
		var count int64
		if err := totalRows.Scan(&dungeonID, &coun
t); err != nil {
			return SpecCountBucket{}, err
		}
		dungeonTotals[dungeonID] = count
		grandTotal += count
	}

	bucket := SpecCountBucket{
		TotalRuns: grandTotal,
		Entries:   entriesFromCounts(overall),
		ByDungeon: make(map[int]DungeonSpecBucket,
 len(dungeonTotals)),
	}
	for dungeonID, total := range dungeonTotals
 {
		bucket.ByDungeon[dungeonID] = DungeonSpecB
ucket{
			TotalRuns: total,
			Entries:   entriesFromCounts(perDungeon[d
ungeonID]),
		}
	}
	return bucket, nil
}

// in-memory aggregation when SQL counting i
s awkward (see querySpecCountsForTier)
type specAgg struct {
	count int64
	runs  map[int64]struct{}
}

func specAggsToSlice(aggs map[int]*specAgg) 
[]SpecCountEntry {
	out := make([]SpecCountEntry, 0, len(aggs))
	for specID, a := range aggs {
		entry := SpecCountEntry{SpecID: specID, Co
unt: a.count, RunsWithSpec: int64(len(a.runs
))}
		if cls, spec, ok := wow.GetClassAndSpec(sp
ecID); ok {
			entry.ClassName = cls
			entry.SpecName = spec
		}
		out = append(out, entry)
	}
	// simple insertion sort by count desc (sma
ll N: ~33 specs)
	for i := 1; i < len(out); i++ {
		for j := i; j > 0 && out[j].Count > out[j-
1].Count; j-- {
			out[j], out[j-1] = out[j-1], out[j]
		}
	}
	return out
}

// queryWeeklyActivity returns runs grouped 
by Monday-start week.
// Timestamps are stored in milliseconds; SQ
Lite date functions need seconds.
func queryWeeklyActivity(db *sql.DB, seasonN
um int, region string) ([]WeeklyActivityEntr
y, error) {
	regionJoin := ""
	if region != "" && region != "global" {
		regionJoin = "JOIN realms r ON r.id = cr.r
ealm_id"
	}
	sClause, sArgs := seasonClauseAndArg(season
Num, "cr")
	rClause, rArgs := regionClauseAndArg(region
, "r")
	args := append(sArgs, rArgs...)
	// 'weekday 1' advances to next Monday; '-7
 days' rolls back to start of current week.
	q := fmt.Sprintf(`
		SELECT
			date(cr.completed_timestamp / 1000, 'unix
epoch', 'weekday 1', '-7 days') AS week_star
t,
			COUNT(*) AS run_count
		FROM challenge_runs cr
		%s
		WHERE cr.completed_timestamp IS NOT NULL %
s %s
		GROUP BY week_start
		ORDER BY week_start
	`, regionJoin, sClause, rClause)
	rows, err := db.Query(q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []WeeklyActivityEntry
	for rows.Next() {
		var e WeeklyActivityEntry
		if err := rows.Scan(&e.WeekStart, &e.RunCo
unt); err != nil {
			return nil, err
		}
		out = append(out, e)
	}
	return out, rows.Err()
}

func round2(f float64) float64 {
	return float64(int64(f*100+0.5)) / 100
}

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