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

package cmd

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"os"
	"path/filepath"
	"strings"
	"time"

	"github.com/charmbracelet/log"
	"github.com/spf13/cobra"
	"ookstats/internal/blizzard"
	"ookstats/internal/database"
	"ookstats/internal/generator"
	"ookstats/internal/generator/indexes"
	"ookstats/internal/pipeline"
)

// buildCmd orchestrates a full from-scratch rebuild + static API generation
var buildCmd = &cobra.Command{
	Use:   "build",
	Short: "Full rebuild of database and static API",
	Long:  `From-scratch hourly rebuild: init schema, fetch CM runs (period sweep), p
rocess players + rankings, fetch profiles, and generate static API.`,
	RunE: func(cmd *cobra.Command, args []string) error {
		outDir, _ := cmd.Flags().GetString("out")
		if strings.TrimSpace(outDir) == "" {
			return errors.New("--out is required (e.g. web/public or web/public/api)")
		}

		// Normalize output: our generator writes under "+/api". Accept either web/publi
c or web/public/api and normalize to parent.
		normalizedOut := strings.TrimRight(outDir, string(os.PathSeparator))
		if filepath.Base(normalizedOut) == "api" {
			normalizedOut = filepath.Dir(normalizedOut)
		}

		fromScratch, _ := cmd.Flags().GetBool("from-scratch")
		regionsCSV, _ := cmd.Flags().GetString("regions")
		pageSize, _ := cmd.Flags().GetInt("page-size")
		shardSize, _ := cmd.Flags().GetInt("shard-size")
		wowsimsDB, _ := cmd.Flags().GetString("wowsims-db")
		skipProfiles, _ := cmd.Flags().GetBool("skip-profiles")
		periodsCSV, _ := cmd.Flags().GetString("periods")
		concurrency, _ := cmd.Flags().GetInt("concurrency")
		workers, _ := cmd.Flags().GetInt("workers")
		latestPeriodsOnly, _ := cmd.Flags().GetBool("latest-periods")

		// optional verbose logging propagated to API client
		verbose, _ := cmd.InheritedFlags().GetBool("verbose")

		// Handle from-scratch (file DSN only)
		if fromScratch {
			dbPath := database.DBFilePath()
			// Only remove if it's a plain local file path
			if !strings.Contains(dbPath, "://") {
				if _, err := os.Stat(dbPath); err == nil {
					log.Info("removing existing database", "path", dbPath)
					if rmErr := os.Remove(dbPath); rmErr != nil {
						return fmt.Errorf("failed to remove db file: %w", rmErr)
					}
				}
			} else {
				log.Warn("from-scratch requested but DSN is not a local file - skipping deleti
on", "dsn", dbPath)
			}
		}

		// 1) Schema init
		db, err := database.Connect()
		if err != nil {
			return fmt.Errorf("db connect: %w", err)
		}
		defer db.Close()

		if err := database.EnsureCompleteSchema(db); err != nil {
			return fmt.Errorf("schema init: %w", err)
		}

		// 2) Populate items (embedded default; file can override)
		log.Info("populating items", "source", func() string {
			if wowsimsDB != "" {
				return "file override"
			}
			return "embedded default"
		}())
		if err := populateItems(db, wowsimsDB); err != nil {
			return fmt.Errorf("populate items: %w", err)
		}

		// 3) Initialize Blizzard API client
		client, err := blizzard.NewClient()
		if err != nil {
			return fmt.Errorf("blizzard client: %w", err)
		}
		client.Verbose = verbose
		if concurrency > 0 {
			client.SetConcurrency(concurrency)
		}

		// 4) Sync season metadata from API
		log.Info("syncing season metadata")
		if err := syncSeasons(db, client, regionsCSV); err != nil {
			return fmt.Errorf("sync seasons: %w", err)
		}

		// 5) Fetch CM runs using pipeline (includes child realm filtering)
		log.Info("fetching challenge mode leaderboards", "sweep", "global period")

		dbService := database.NewDatabaseService(db)

		// control database-internal verbosity (hide 404 noise unless verbose)
		database.SetVerbose(verbose)

		// Parse regions for filter
		var regions []string
		if strings.TrimSpace(regionsCSV) != "" {
			for _, r := range strings.Split(regionsCSV, ",") {
				if trimmed := strings.TrimSpace(r); trimmed != "" {
					regions = append(regions, trimmed)
				}
			}
		}

		// Parse periods (if provided)
		var periods []string
		if strings.TrimSpace(periodsCSV) != "" {
			var err error
			periods, err = blizzard.ParsePeriods(periodsCSV)
			if err != nil {
				return fmt.Errorf("failed to parse periods: %w", err)
			}
		}

		// Use pipeline function (handles child realm filtering automatically)
		fetchOpts := pipeline.FetchCMOptions{
			Verbose:           verbose,
			Regions:           regions,
			Realms:            []string{}, // no realm filter
			Dungeons:          []string{}, // no dungeon filter
			Periods:           periods,    // empty means fetch dynamically
			LatestPeriodsOnly: latestPeriodsOnly,
			Concurrency:       concurrency,
			Timeout:           45 * time.Minute,
		}

		result, err := pipeline.FetchChallengeMode(dbService, client, fetchOpts)
		if err != nil {
			return fmt.Errorf("fetch challenge mode: %w", err)
		}

		log.Info("fetch complete",
			"runs", result.TotalRuns,
			"players", result.TotalPlayers,
			"duration", result.Duration)

		// 4) Assign seasons to runs based on timestamps
		log.Info("assigning seasons to runs", "method", "timestamp-based")
		if err := dbService.AssignRunsToSeasons(); err != nil {
			return fmt.Errorf("assign seasons: %w", err)
		}

		// 5) Fingerprint players (merge duplicates)
		log.Info("fingerprinting players", "stage", "identity detection + merge")
		if err := fingerprintPlayersOnce(db, client); err != nil {
			return err
		}

		// 6) Process players (aggregations + rankings)
		log.Info("processing players", "stage", "aggregations + rankings")
		if err := processPlayersOnce(db); err != nil {
			return err
		}

		// 7) Fetch detailed player profiles (optional)
		if !skipProfiles {
			log.Info("fetching detailed player profiles", "coverage", "9/9")
			if err := fetchProfilesOnce(db, client); err != nil {
				return err
			}
		} else {
			log.Info("skipping player profile fetch", "reason", "flag")
		}

		// 8) Process run rankings (global/regional)
		log.Info("processing run rankings", "scopes", "global + regional")
		if err := processRunRankingsOnce(db); err != nil {
			return err
		}

		// 8a) Group characters into accounts
		log.Info("grouping characters into accounts", "stage", "account fingerprinting")
		if err := processAccountsOnce(db, client); err != nil {
			return err
		}

		// 9) Generate static API
		log.Info("generating static API")
		if err := generateAllAPI(db, normalizedOut, pageSize, shardSize, workers, region
sCSV); err != nil {
			return err
		}

		// 9a) Generate home page JSON
		log.Info("generating home page JSON")
		if err := generator.GenerateHome(db, normalizedOut); err != nil {
			return err
		}

		// 9b) Generate stats page JSON
		log.Info("generating stats page JSON")
		if err := generator.GenerateStats(db, normalizedOut); err != nil {
			return err
		}

		// 9c) Generate gear popularity JSON
		log.Info("generating gear page JSON")
		if err := generator.GenerateGear(db, normalizedOut); err != nil {
			return err
		}

		// 10) Generate status API via analyze
		log.Info("generating status API", "method", "analyze")
		statusDir := filepath.Join(normalizedOut, "api", "status")
		outPath := filepath.Join(statusDir, "latest-runs.json")
		// Get realms and dungeons for analyze
		_, dungeons := blizzard.GetHardcodedPeriodAndDungeons()
		allRealms := blizzard.GetAllRealms()
		if err := runAnalyze(db, allRealms, dungeons, periodsCSV, outPath, statusDir); e
rr != nil {
			return fmt.Errorf("analyze status: %w", err)
		}

		// Print summary
		summarizeBuild(db, normalizedOut)

		log.Info("build complete", "api_location", normalizedOut+"/api")
		return nil
	},
}

// processPlayersOnce runs the same steps as `process players`
func processPlayersOnce(db *sql.DB) error {
	opts := pipeline.ProcessPlayersOptions{
		Verbose: false,
	}
	_, _, err := pipeline.ProcessPlayers(db, opts)
	return err
}

// processRunRankingsOnce runs the same steps as `process rankings`
func processRunRankingsOnce(db *sql.DB) error {
	opts := pipeline.ProcessRunRankingsOptions{
		Verbose: false,
	}
	return pipeline.ProcessRunRankings(db, opts)
}

// fingerprintPlayersOnce runs fingerprinting to detect and merge duplicate player
 identities
func fingerprintPlayersOnce(db *sql.DB, client *blizzard.Client) error {
	dbService := database.NewDatabaseService(db)
	opts := pipeline.FingerprintOptions{
		Verbose:    false,
		BatchSize:  25,
		MaxPlayers: 0, // process all players
	}
	result, err := pipeline.BuildPlayerFingerprints(dbService, client, opts)
	if err != nil {
		return fmt.Errorf("fingerprint players: %w", err)
	}
	log.Info("fingerprinting complete",
		"processed", result.Processed,
		"created", result.Created,
		"merged", result.MarkedInvalid,
		"duration", result.Duration)
	return nil
}

// processAccountsOnce runs the account-grouping pass with default thresholds.
// Backfills any missing account fingerprints, then connected-components.
// Manual link overrides are read from the repo-root JSON so CI/prod builds
// pick them up without an extra flag.
func processAccountsOnce(db *sql.DB, client *blizzard.Client) error {
	dbService := database.NewDatabaseService(db)
	res, err := pipeline.ProcessAccounts(dbService, client, pipeline.AccountGroupingO
ptions{
		ManualLinksPath: "account-manual-links.json",
	})
	if err != nil {
		return fmt.Errorf("process accounts: %w", err)
	}
	log.Info("account grouping complete",
		"backfilled", res.Backfilled,
		"backfill_errors", res.BackfillErrors,
		"characters", res.Characters,
		"accounts", res.Accounts,
		"multi_char", res.MultiCharAccounts,
		"duration", res.Duration)
	return nil
}

// fetchProfilesOnce runs the same logic as `fetch profiles`
func fetchProfilesOnce(db *sql.DB, client *blizzard.Client) error {
	dbService := database.NewDatabaseService(db)
	// refetch profiles older than 72 hours
	staleThreshold := time.Now().Add(-72 * time.Hour).UnixMilli()
	players, err := dbService.GetEligiblePlayersForProfileFetch(staleThreshold)
	if err != nil {
		return fmt.Errorf("eligible players: %w", err)
	}
	if len(players) == 0 {
		log.Info("no eligible players with 9/9 coverage - skipping profiles")
		return nil
	}

	batchSize := 20
	totalProfiles := 0
	totalItems := 0
	processed := 0
	start := time.Now()

	for i := 0; i < len(players); i += batchSize {
		end := i + batchSize
		if end > len(players) {
			end = len(players)
		}
		batch := players[i:end]
		batchNum := (i / batchSize) + 1
		totalBatches := (len(players) + batchSize - 1) / batchSize
		log.Info("processing profiles batch",
			"batch", batchNum,
			"total_batches", totalBatches,
			"players", len(batch))
		batchCtx, batchCancel := context.WithTimeout(context.Background(), 2*time.Minute
)
		results := client.FetchPlayerProfilesConcurrent(batchCtx, batch)
		ts := time.Now().UnixMilli()
		batchProfiles := 0
		batchItems := 0
		for res := range results {
			processed++
			if res.Error != nil {
				log.Error("profile fetch failed",
					"player", res.PlayerName,
					"region", res.Region,
					"error", res.Error)
				continue
			}
			profs, items, err := dbService.InsertPlayerProfileData(res, ts)
			if err != nil {
				log.Error("profile insert failed",
					"player", res.PlayerName,
					"region", res.Region,
					"error", err)
				continue
			}
			batchProfiles += profs
			batchItems += items
		}
		batchCancel()
		totalProfiles += batchProfiles
		totalItems += batchItems
		log.Info("batch complete",
			"profiles", batchProfiles,
			"items", batchItems,
			"total_processed", processed,
			"total_players", len(players))
		if i+batchSize < len(players) {
			time.Sleep(1 * time.Second)
		}
	}

	log.Info("profiles complete",
		"profiles", totalProfiles,
		"items", totalItems,
		"duration", time.Since(start))
	return nil
}

// generateAllAPI mirrors the behavior of `generate api`
func generateAllAPI(db *sql.DB, outParent string, pageSize, shardSize, workers int
, regionsCSV string) error {
	base := filepath.Join(outParent, "api")
	if err := os.MkdirAll(base, 0o755); err != nil {
		return fmt.Errorf("mkdir: %w", err)
	}

	// players
	if err := generator.GeneratePlayers(db, filepath.Join(base, "player"), ""); err !
= nil {
		return err
	}

	// leaderboards (+ players rankings)
	regions := []string{}
	if strings.TrimSpace(regionsCSV) != "" {
		for _, r := range strings.Split(regionsCSV, ",") {
			rr := strings.TrimSpace(r)
			if rr != "" {
				regions = append(regions, rr)
			}
		}
	}
	if err := generator.GenerateLeaderboards(db, filepath.Join(base, "leaderboard"), 
pageSize, regions, workers); err != nil {
		return err
	}
	if err := generator.GeneratePlayerLeaderboards(db, filepath.Join(base, "leaderboa
rd"), pageSize, regions, workers); err != nil {
		return err
	}
	if err := generator.GenerateTotalRunsLeaderboard(db, filepath.Join(base, "leaderb
oard"), pageSize, regions, workers); err != nil {
		return err
	}
	if err := generator.GenerateAccountTotalRunsLeaderboard(db, filepath.Join(base, "
leaderboard"), pageSize, regions, workers); err != nil {
		return err
	}
	if err := generator.GenerateAccountLeaderboards(db, filepath.Join(base, "leaderbo
ard"), pageSize, regions, workers); err != nil {
		return err
	}
	if err := generator.GenerateAllTimePlayerLeaderboard(db, filepath.Join(base, "lea
derboard"), pageSize, regions, workers); err != nil {
		return err
	}
	if err := generator.GenerateAllTimeAccountLeaderboard(db, filepath.Join(base, "le
aderboard"), pageSize, regions, workers); err != nil {
		return err
	}
	if err := generator.GeneratePlayerSeasonRunsLeaderboard(db, filepath.Join(base, "
leaderboard"), pageSize, regions, workers); err != nil {
		return err
	}
	if err := generator.GenerateAccountSeasonRunsLeaderboard(db, filepath.Join(base, 
"leaderboard"), pageSize, regions, workers); err != nil {
		return err
	}

	// search index
	if err := generator.GenerateSearchIndex(db, filepath.Join(base, "search"), shardS
ize); err != nil {
		return err
	}

	// indexes
	if err := indexes.GenerateAllIndexes(db, outParent); err != nil {
		return err
	}

	log.Info("static API generated")
	return nil
}

// summarizeBuild prints DB summary and per-realm period coverage
func summarizeBuild(db *sql.DB, outParent string) {
	var runCount, playerCount, completePlayers, detailsCount int
	_ = db.QueryRow("SELECT COUNT(*) FROM challenge_runs").Scan(&runCount)
	_ = db.QueryRow("SELECT COUNT(*) FROM players").Scan(&playerCount)
	_ = db.QueryRow("SELECT COUNT(*) FROM player_profiles WHERE has_complete_coverage
 = 1").Scan(&completePlayers)
	_ = db.QueryRow("SELECT COUNT(*) FROM player_details").Scan(&detailsCount)

	log.Info("build summary",
		"runs", runCount,
		"players", playerCount,
		"complete_coverage", completePlayers,
		"player_details", detailsCount)

	log.Info("per-realm period coverage")
	rows, err := db.Query(`
        SELECT r.slug, GROUP_CONCAT(DISTINCT cr.period_id ORDER BY cr.period_id DE
SC)
        FROM challenge_runs cr
        JOIN realms r ON cr.realm_id = r.id
        GROUP BY r.slug
        ORDER BY r.region, r.slug`)
	if err == nil {
		defer rows.Close()
		for rows.Next() {
			var slug, periods string
			if err := rows.Scan(&slug, &periods); err == nil {
				if periods == "" {
					periods = "-"
				}
				log.Debug("realm coverage", "realm", slug, "periods", periods)
			}
		}
	}
}

// syncSeasons syncs season metadata from Blizzard API for all regions
func syncSeasons(db *sql.DB, client *blizzard.Client, regionsCSV string) error {
	dbService := database.NewDatabaseService(db)

	// Parse regions from CSV (seasons and periods are region-specific)
	var regions []string
	if strings.TrimSpace(regionsCSV) != "" {
		for _, r := range strings.Split(regionsCSV, ",") {
			if trimmed := strings.TrimSpace(r); trimmed != "" {
				regions = append(regions, trimmed)
			}
		}
	}

	// Default to all regions if none specified
	if len(regions) == 0 {
		regions = []string{"us", "eu", "kr", "tw"}
	}

	log.Info("fetching season metadata", "regions", len(regions))

	// Process each region
	for _, region := range regions {
		log.Info("processing region", "region", strings.ToUpper(region))

		// Fetch season index for this region
		seasonIndex, err := client.FetchSeasonIndex(region)
		if err != nil {
			log.Error("failed to fetch season index - skipping region",
				"region", strings.ToUpper(region),
				"error", err)
			continue
		}

		if len(seasonIndex.Seasons) == 0 {
			log.Warn("no seasons found", "region", strings.ToUpper(region))
			continue
		}

		log.Info("found seasons", "count", len(seasonIndex.Seasons), "region", strings.T
oUpper(region))

		// Process each season for this region
		for _, seasonRef := range seasonIndex.Seasons {
			seasonID := seasonRef.ID

			// Fetch season details
			seasonDetail, err := client.FetchSeasonDetail(region, seasonID)
			if err != nil {
				log.Error("error fetching season details",
					"season", seasonID,
					"error", err)
				continue
			}

			// Upsert season with region
			dbSeasonID, err := dbService.UpsertSeason(seasonDetail.ID, region, seasonDetail
.SeasonName, seasonDetail.StartTimestamp)
			if err != nil {
				log.Error("error upserting season",
					"season", seasonID,
					"error", err)
				continue
			}

			// Link periods to season
			if len(seasonDetail.Periods) > 0 {
				firstPeriod := seasonDetail.Periods[0].ID
				lastPeriod := seasonDetail.Periods[len(seasonDetail.Periods)-1].ID

				// Update period range
				err = dbService.UpdateSeasonPeriodRange(dbSeasonID, firstPeriod, lastPeriod)
				if err != nil {
					log.Error("error updating period range", "error", err)
				}

				// Link each period
				for _, periodRef := range seasonDetail.Periods {
					err = dbService.LinkPeriodToSeason(periodRef.ID, dbSeasonID)
					if err != nil {
						log.Error("error linking period",
							"period", periodRef.ID,
							"error", err)
					}
				}
			}
			log.Info("season synced",
				"season", seasonID,
				"name", seasonDetail.SeasonName,
				"periods", len(seasonDetail.Periods))
		}
	}

	log.Info("season metadata synced for all regions")
	return nil
}

func init() {
	rootCmd.AddCommand(buildCmd)
	buildCmd.Flags().String("out", "", "Parent output directory for static API (e.g. 
web/public or web/public/api)")
	buildCmd.Flags().Bool("from-scratch", false, "Delete local DB file first if using
 a file-based DSN")
	buildCmd.Flags().String("regions", "", "Comma-separated regions to include (defau
lt: all)")
	buildCmd.Flags().Int("page-size", 25, "Leaderboard pagination size")
	buildCmd.Flags().Int("shard-size", 5000, "Search index shard size")
	buildCmd.Flags().String("wowsims-db", "", "Optional path to WoWSims items JSON fo
r item enrichment")
	buildCmd.Flags().Bool("skip-profiles", false, "Skip fetching player detailed prof
iles")
	buildCmd.Flags().String("periods", "", "Period specification: comma-separated lis
t or ranges (e.g., '1020-1036' or '1020,1025,1030-1036'). Default: fetch all perio
ds from API")
	buildCmd.Flags().Bool("latest-periods", false, "Only fetch the latest 2 periods f
rom the current season per region (optimized for persistent databases)")
	buildCmd.Flags().Int("concurrency", 20, "Max concurrent API requests")
	buildCmd.Flags().Int("workers", 10, "Number of parallel workers for leaderboard g
eneration")
}


package cmd

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"os"
	"path/filepath"
	"strings"
	"time"

	"github.com/charmbracelet/log"
	"github.com/spf13/cobra"
	"ookstats/internal/blizzard"
	"ookstats/internal/database"
	"ookstats/internal/generator"
	"ookstats/internal/generator/indexes"
	"ookstats/internal/pipeline"
)

// buildCmd orchestrates a full from-scratch
 rebuild + static API generation
var buildCmd = &cobra.Command{
	Use:   "build",
	Short: "Full rebuild of database and static
 API",
	Long:  `From-scratch hourly rebuild: init s
chema, fetch CM runs (period sweep), process
 players + rankings, fetch profiles, and gen
erate static API.`,
	RunE: func(cmd *cobra.Command, args []strin
g) error {
		outDir, _ := cmd.Flags().GetString("out")
		if strings.TrimSpace(outDir) == "" {
			return errors.New("--out is required (e.g
. web/public or web/public/api)")
		}

		// Normalize output: our generator writes 
under "+/api". Accept either web/public or w
eb/public/api and normalize to parent.
		normalizedOut := strings.TrimRight(outDir,
 string(os.PathSeparator))
		if filepath.Base(normalizedOut) == "api" {
			normalizedOut = filepath.Dir(normalizedOu
t)
		}

		fromScratch, _ := cmd.Flags().GetBool("fro
m-scratch")
		regionsCSV, _ := cmd.Flags().GetString("re
gions")
		pageSize, _ := cmd.Flags().GetInt("page-si
ze")
		shardSize, _ := cmd.Flags().GetInt("shard-
size")
		wowsimsDB, _ := cmd.Flags().GetString("wow
sims-db")
		skipProfiles, _ := cmd.Flags().GetBool("sk
ip-profiles")
		periodsCSV, _ := cmd.Flags().GetString("pe
riods")
		concurrency, _ := cmd.Flags().GetInt("conc
urrency")
		workers, _ := cmd.Flags().GetInt("workers"
)
		latestPeriodsOnly, _ := cmd.Flags().GetBoo
l("latest-periods")

		// optional verbose logging propagated to 
API client
		verbose, _ := cmd.InheritedFlags().GetBool
("verbose")

		// Handle from-scratch (file DSN only)
		if fromScratch {
			dbPath := database.DBFilePath()
			// Only remove if it's a plain local file
 path
			if !strings.Contains(dbPath, "://") {
				if _, err := os.Stat(dbPath); err == nil
 {
					log.Info("removing existing database", 
"path", dbPath)
					if rmErr := os.Remove(dbPath); rmErr !=
 nil {
						return fmt.Errorf("failed to remove db
 file: %w", rmErr)
					}
				}
			} else {
				log.Warn("from-scratch requested but DSN
 is not a local file - skipping deletion", "
dsn", dbPath)
			}
		}

		// 1) Schema init
		db, err := database.Connect()
		if err != nil {
			return fmt.Errorf("db connect: %w", err)
		}
		defer db.Close()

		if err := database.EnsureCompleteSchema(db
); err != nil {
			return fmt.Errorf("schema init: %w", err)
		}

		// 2) Populate items (embedded default; fi
le can override)
		log.Info("populating items", "source", fun
c() string {
			if wowsimsDB != "" {
				return "file override"
			}
			return "embedded default"
		}())
		if err := populateItems(db, wowsimsDB); er
r != nil {
			return fmt.Errorf("populate items: %w", e
rr)
		}

		// 3) Initialize Blizzard API client
		client, err := blizzard.NewClient()
		if err != nil {
			return fmt.Errorf("blizzard client: %w", 
err)
		}
		client.Verbose = verbose
		if concurrency > 0 {
			client.SetConcurrency(concurrency)
		}

		// 4) Sync season metadata from API
		log.Info("syncing season metadata")
		if err := syncSeasons(db, client, regionsC
SV); err != nil {
			return fmt.Errorf("sync seasons: %w", err
)
		}

		// 5) Fetch CM runs using pipeline (includ
es child realm filtering)
		log.Info("fetching challenge mode leaderbo
ards", "sweep", "global period")

		dbService := database.NewDatabaseService(d
b)

		// control database-internal verbosity (hi
de 404 noise unless verbose)
		database.SetVerbose(verbose)

		// Parse regions for filter
		var regions []string
		if strings.TrimSpace(regionsCSV) != "" {
			for _, r := range strings.Split(regionsCS
V, ",") {
				if trimmed := strings.TrimSpace(r); trim
med != "" {
					regions = append(regions, trimmed)
				}
			}
		}

		// Parse periods (if provided)
		var periods []string
		if strings.TrimSpace(periodsCSV) != "" {
			var err error
			periods, err = blizzard.ParsePeriods(peri
odsCSV)
			if err != nil {
				return fmt.Errorf("failed to parse perio
ds: %w", err)
			}
		}

		// Use pipeline function (handles child re
alm filtering automatically)
		fetchOpts := pipeline.FetchCMOptions{
			Verbose:           verbose,
			Regions:           regions,
			Realms:            []string{}, // no real
m filter
			Dungeons:          []string{}, // no dung
eon filter
			Periods:           periods,    // empty m
eans fetch dynamically
			LatestPeriodsOnly: latestPeriodsOnly,
			Concurrency:       concurrency,
			Timeout:           45 * time.Minute,
		}

		result, err := pipeline.FetchChallengeMode
(dbService, client, fetchOpts)
		if err != nil {
			return fmt.Errorf("fetch challenge mode: 
%w", err)
		}

		log.Info("fetch complete",
			"runs", result.TotalRuns,
			"players", result.TotalPlayers,
			"duration", result.Duration)

		// 4) Assign seasons to runs based on time
stamps
		log.Info("assigning seasons to runs", "met
hod", "timestamp-based")
		if err := dbService.AssignRunsToSeasons();
 err != nil {
			return fmt.Errorf("assign seasons: %w", e
rr)
		}

		// 5) Fingerprint players (merge duplicate
s)
		log.Info("fingerprinting players", "stage"
, "identity detection + merge")
		if err := fingerprintPlayersOnce(db, clien
t); err != nil {
			return err
		}

		// 6) Process players (aggregations + rank
ings)
		log.Info("processing players", "stage", "a
ggregations + rankings")
		if err := processPlayersOnce(db); err != n
il {
			return err
		}

		// 7) Fetch detailed player profiles (opti
onal)
		if !skipProfiles {
			log.Info("fetching detailed player profil
es", "coverage", "9/9")
			if err := fetchProfilesOnce(db, client); 
err != nil {
				return err
			}
		} else {
			log.Info("skipping player profile fetch",
 "reason", "flag")
		}

		// 8) Process run rankings (global/regiona
l)
		log.Info("processing run rankings", "scope
s", "global + regional")
		if err := processRunRankingsOnce(db); err 
!= nil {
			return err
		}

		// 8a) Group characters into accounts
		log.Info("grouping characters into account
s", "stage", "account fingerprinting")
		if err := processAccountsOnce(db, client);
 err != nil {
			return err
		}

		// 9) Generate static API
		log.Info("generating static API")
		if err := generateAllAPI(db, normalizedOut
, pageSize, shardSize, workers, regionsCSV);
 err != nil {
			return err
		}

		// 9a) Generate home page JSON
		log.Info("generating home page JSON")
		if err := generator.GenerateHome(db, norma
lizedOut); err != nil {
			return err
		}

		// 9b) Generate stats page JSON
		log.Info("generating stats page JSON")
		if err := generator.GenerateStats(db, norm
alizedOut); err != nil {
			return err
		}

		// 9c) Generate gear popularity JSON
		log.Info("generating gear page JSON")
		if err := generator.GenerateGear(db, norma
lizedOut); err != nil {
			return err
		}

		// 10) Generate status API via analyze
		log.Info("generating status API", "method"
, "analyze")
		statusDir := filepath.Join(normalizedOut, 
"api", "status")
		outPath := filepath.Join(statusDir, "lates
t-runs.json")
		// Get realms and dungeons for analyze
		_, dungeons := blizzard.GetHardcodedPeriod
AndDungeons()
		allRealms := blizzard.GetAllRealms()
		if err := runAnalyze(db, allRealms, dungeo
ns, periodsCSV, outPath, statusDir); err != 
nil {
			return fmt.Errorf("analyze status: %w", e
rr)
		}

		// Print summary
		summarizeBuild(db, normalizedOut)

		log.Info("build complete", "api_location",
 normalizedOut+"/api")
		return nil
	},
}

// processPlayersOnce runs the same steps as
 `process players`
func processPlayersOnce(db *sql.DB) error {
	opts := pipeline.ProcessPlayersOptions{
		Verbose: false,
	}
	_, _, err := pipeline.ProcessPlayers(db, op
ts)
	return err
}

// processRunRankingsOnce runs the same step
s as `process rankings`
func processRunRankingsOnce(db *sql.DB) erro
r {
	opts := pipeline.ProcessRunRankingsOptions{
		Verbose: false,
	}
	return pipeline.ProcessRunRankings(db, opts
)
}

// fingerprintPlayersOnce runs fingerprintin
g to detect and merge duplicate player ident
ities
func fingerprintPlayersOnce(db *sql.DB, clie
nt *blizzard.Client) error {
	dbService := database.NewDatabaseService(db
)
	opts := pipeline.FingerprintOptions{
		Verbose:    false,
		BatchSize:  25,
		MaxPlayers: 0, // process all players
	}
	result, err := pipeline.BuildPlayerFingerpr
ints(dbService, client, opts)
	if err != nil {
		return fmt.Errorf("fingerprint players: %w
", err)
	}
	log.Info("fingerprinting complete",
		"processed", result.Processed,
		"created", result.Created,
		"merged", result.MarkedInvalid,
		"duration", result.Duration)
	return nil
}

// processAccountsOnce runs the account-grou
ping pass with default thresholds.
// Backfills any missing account fingerprint
s, then connected-components.
// Manual link overrides are read from the r
epo-root JSON so CI/prod builds
// pick them up without an extra flag.
func processAccountsOnce(db *sql.DB, client 
*blizzard.Client) error {
	dbService := database.NewDatabaseService(db
)
	res, err := pipeline.ProcessAccounts(dbServ
ice, client, pipeline.AccountGroupingOptions
{
		ManualLinksPath: "account-manual-links.jso
n",
	})
	if err != nil {
		return fmt.Errorf("process accounts: %w", 
err)
	}
	log.Info("account grouping complete",
		"backfilled", res.Backfilled,
		"backfill_errors", res.BackfillErrors,
		"characters", res.Characters,
		"accounts", res.Accounts,
		"multi_char", res.MultiCharAccounts,
		"duration", res.Duration)
	return nil
}

// fetchProfilesOnce runs the same logic as 
`fetch profiles`
func fetchProfilesOnce(db *sql.DB, client *b
lizzard.Client) error {
	dbService := database.NewDatabaseService(db
)
	// refetch profiles older than 72 hours
	staleThreshold := time.Now().Add(-72 * time
.Hour).UnixMilli()
	players, err := dbService.GetEligiblePlayer
sForProfileFetch(staleThreshold)
	if err != nil {
		return fmt.Errorf("eligible players: %w", 
err)
	}
	if len(players) == 0 {
		log.Info("no eligible players with 9/9 cov
erage - skipping profiles")
		return nil
	}

	batchSize := 20
	totalProfiles := 0
	totalItems := 0
	processed := 0
	start := time.Now()

	for i := 0; i < len(players); i += batchSiz
e {
		end := i + batchSize
		if end > len(players) {
			end = len(players)
		}
		batch := players[i:end]
		batchNum := (i / batchSize) + 1
		totalBatches := (len(players) + batchSize 
- 1) / batchSize
		log.Info("processing profiles batch",
			"batch", batchNum,
			"total_batches", totalBatches,
			"players", len(batch))
		batchCtx, batchCancel := context.WithTimeo
ut(context.Background(), 2*time.Minute)
		results := client.FetchPlayerProfilesConcu
rrent(batchCtx, batch)
		ts := time.Now().UnixMilli()
		batchProfiles := 0
		batchItems := 0
		for res := range results {
			processed++
			if res.Error != nil {
				log.Error("profile fetch failed",
					"player", res.PlayerName,
					"region", res.Region,
					"error", res.Error)
				continue
			}
			profs, items, err := dbService.InsertPlay
erProfileData(res, ts)
			if err != nil {
				log.Error("profile insert failed",
					"player", res.PlayerName,
					"region", res.Region,
					"error", err)
				continue
			}
			batchProfiles += profs
			batchItems += items
		}
		batchCancel()
		totalProfiles += batchProfiles
		totalItems += batchItems
		log.Info("batch complete",
			"profiles", batchProfiles,
			"items", batchItems,
			"total_processed", processed,
			"total_players", len(players))
		if i+batchSize < len(players) {
			time.Sleep(1 * time.Second)
		}
	}

	log.Info("profiles complete",
		"profiles", totalProfiles,
		"items", totalItems,
		"duration", time.Since(start))
	return nil
}

// generateAllAPI mirrors the behavior of `g
enerate api`
func generateAllAPI(db *sql.DB, outParent st
ring, pageSize, shardSize, workers int, regi
onsCSV string) error {
	base := filepath.Join(outParent, "api")
	if err := os.MkdirAll(base, 0o755); err != 
nil {
		return fmt.Errorf("mkdir: %w", err)
	}

	// players
	if err := generator.GeneratePlayers(db, fil
epath.Join(base, "player"), ""); err != nil 
{
		return err
	}

	// leaderboards (+ players rankings)
	regions := []string{}
	if strings.TrimSpace(regionsCSV) != "" {
		for _, r := range strings.Split(regionsCSV
, ",") {
			rr := strings.TrimSpace(r)
			if rr != "" {
				regions = append(regions, rr)
			}
		}
	}
	if err := generator.GenerateLeaderboards(db
, filepath.Join(base, "leaderboard"), pageSi
ze, regions, workers); err != nil {
		return err
	}
	if err := generator.GeneratePlayerLeaderboa
rds(db, filepath.Join(base, "leaderboard"), 
pageSize, regions, workers); err != nil {
		return err
	}
	if err := generator.GenerateTotalRunsLeader
board(db, filepath.Join(base, "leaderboard")
, pageSize, regions, workers); err != nil {
		return err
	}
	if err := generator.GenerateAccountTotalRun
sLeaderboard(db, filepath.Join(base, "leader
board"), pageSize, regions, workers); err !=
 nil {
		return err
	}
	if err := generator.GenerateAccountLeaderbo
ards(db, filepath.Join(base, "leaderboard"),
 pageSize, regions, workers); err != nil {
		return err
	}
	if err := generator.GenerateAllTimePlayerLe
aderboard(db, filepath.Join(base, "leaderboa
rd"), pageSize, regions, workers); err != ni
l {
		return err
	}
	if err := generator.GenerateAllTimeAccountL
eaderboard(db, filepath.Join(base, "leaderbo
ard"), pageSize, regions, workers); err != n
il {
		return err
	}
	if err := generator.GeneratePlayerSeasonRun
sLeaderboard(db, filepath.Join(base, "leader
board"), pageSize, regions, workers); err !=
 nil {
		return err
	}
	if err := generator.GenerateAccountSeasonRu
nsLeaderboard(db, filepath.Join(base, "leade
rboard"), pageSize, regions, workers); err !
= nil {
		return err
	}

	// search index
	if err := generator.GenerateSearchIndex(db,
 filepath.Join(base, "search"), shardSize); 
err != nil {
		return err
	}

	// indexes
	if err := indexes.GenerateAllIndexes(db, ou
tParent); err != nil {
		return err
	}

	log.Info("static API generated")
	return nil
}

// summarizeBuild prints DB summary and per-
realm period coverage
func summarizeBuild(db *sql.DB, outParent st
ring) {
	var runCount, playerCount, completePlayers,
 detailsCount int
	_ = db.QueryRow("SELECT COUNT(*) FROM chall
enge_runs").Scan(&runCount)
	_ = db.QueryRow("SELECT COUNT(*) FROM playe
rs").Scan(&playerCount)
	_ = db.QueryRow("SELECT COUNT(*) FROM playe
r_profiles WHERE has_complete_coverage = 1")
.Scan(&completePlayers)
	_ = db.QueryRow("SELECT COUNT(*) FROM playe
r_details").Scan(&detailsCount)

	log.Info("build summary",
		"runs", runCount,
		"players", playerCount,
		"complete_coverage", completePlayers,
		"player_details", detailsCount)

	log.Info("per-realm period coverage")
	rows, err := db.Query(`
        SELECT r.slug, GROUP_CONCAT(DISTINCT
 cr.period_id ORDER BY cr.period_id DESC)
        FROM challenge_runs cr
        JOIN realms r ON cr.realm_id = r.id
        GROUP BY r.slug
        ORDER BY r.region, r.slug`)
	if err == nil {
		defer rows.Close()
		for rows.Next() {
			var slug, periods string
			if err := rows.Scan(&slug, &periods); err
 == nil {
				if periods == "" {
					periods = "-"
				}
				log.Debug("realm coverage", "realm", slu
g, "periods", periods)
			}
		}
	}
}

// syncSeasons syncs season metadata from Bl
izzard API for all regions
func syncSeasons(db *sql.DB, client *blizzar
d.Client, regionsCSV string) error {
	dbService := database.NewDatabaseService(db
)

	// Parse regions from CSV (seasons and peri
ods are region-specific)
	var regions []string
	if strings.TrimSpace(regionsCSV) != "" {
		for _, r := range strings.Split(regionsCSV
, ",") {
			if trimmed := strings.TrimSpace(r); trimm
ed != "" {
				regions = append(regions, trimmed)
			}
		}
	}

	// Default to all regions if none specified
	if len(regions) == 0 {
		regions = []string{"us", "eu", "kr", "tw"}
	}

	log.Info("fetching season metadata", "regio
ns", len(regions))

	// Process each region
	for _, region := range regions {
		log.Info("processing region", "region", st
rings.ToUpper(region))

		// Fetch season index for this region
		seasonIndex, err := client.FetchSeasonInde
x(region)
		if err != nil {
			log.Error("failed to fetch season index -
 skipping region",
				"region", strings.ToUpper(region),
				"error", err)
			continue
		}

		if len(seasonIndex.Seasons) == 0 {
			log.Warn("no seasons found", "region", st
rings.ToUpper(region))
			continue
		}

		log.Info("found seasons", "count", len(sea
sonIndex.Seasons), "region", strings.ToUpper
(region))

		// Process each season for this region
		for _, seasonRef := range seasonIndex.Seas
ons {
			seasonID := seasonRef.ID

			// Fetch season details
			seasonDetail, err := client.FetchSeasonDe
tail(region, seasonID)
			if err != nil {
				log.Error("error fetching season details
",
					"season", seasonID,
					"error", err)
				continue
			}

			// Upsert season with region
			dbSeasonID, err := dbService.UpsertSeason
(seasonDetail.ID, region, seasonDetail.Seaso
nName, seasonDetail.StartTimestamp)
			if err != nil {
				log.Error("error upserting season",
					"season", seasonID,
					"error", err)
				continue
			}

			// Link periods to season
			if len(seasonDetail.Periods) > 0 {
				firstPeriod := seasonDetail.Periods[0].I
D
				lastPeriod := seasonDetail.Periods[len(s
easonDetail.Periods)-1].ID

				// Update period range
				err = dbService.UpdateSeasonPeriodRange(
dbSeasonID, firstPeriod, lastPeriod)
				if err != nil {
					log.Error("error updating period range"
, "error", err)
				}

				// Link each period
				for _, periodRef := range seasonDetail.P
eriods {
					err = dbService.LinkPeriodToSeason(peri
odRef.ID, dbSeasonID)
					if err != nil {
						log.Error("error linking period",
							"period", periodRef.ID,
							"error", err)
					}
				}
			}
			log.Info("season synced",
				"season", seasonID,
				"name", seasonDetail.SeasonName,
				"periods", len(seasonDetail.Periods))
		}
	}

	log.Info("season metadata synced for all re
gions")
	return nil
}

func init() {
	rootCmd.AddCommand(buildCmd)
	buildCmd.Flags().String("out", "", "Parent 
output directory for static API (e.g. web/pu
blic or web/public/api)")
	buildCmd.Flags().Bool("from-scratch", false
, "Delete local DB file first if using a fil
e-based DSN")
	buildCmd.Flags().String("regions", "", "Com
ma-separated regions to include (default: al
l)")
	buildCmd.Flags().Int("page-size", 25, "Lead
erboard pagination size")
	buildCmd.Flags().Int("shard-size", 5000, "S
earch index shard size")
	buildCmd.Flags().String("wowsims-db", "", "
Optional path to WoWSims items JSON for item
 enrichment")
	buildCmd.Flags().Bool("skip-profiles", fals
e, "Skip fetching player detailed profiles")
	buildCmd.Flags().String("periods", "", "Per
iod specification: comma-separated list or r
anges (e.g., '1020-1036' or '1020,1025,1030-
1036'). Default: fetch all periods from API"
)
	buildCmd.Flags().Bool("latest-periods", fal
se, "Only fetch the latest 2 periods from th
e current season per region (optimized for p
ersistent databases)")
	buildCmd.Flags().Int("concurrency", 20, "Ma
x concurrent API requests")
	buildCmd.Flags().Int("workers", 10, "Number
 of parallel workers for leaderboard generat
ion")
}

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