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

package database

import (
	"database/sql"
	"fmt"
	"ookstats/internal/blizzard"
	"strings"
)

// GetEligiblePlayersForProfileFetch returns players with complete coverage (9/9 d
ungeons)
// staleBefore: Unix timestamp in milliseconds. If > 0, returns players whose prof
ile is NULL or older than this.
// If 0, returns only players who have never had a profile fetched.
func (ds *DatabaseService) GetEligiblePlayersForProfileFetch(staleBefore int64) ([
]blizzard.PlayerInfo, error) {
	var rows *sql.Rows
	var err error

	if staleBefore > 0 {
		rows, err = ds.db.Query(`
			SELECT p.id, p.name, r.slug as realm_slug, r.region
			FROM players p
			JOIN player_profiles pp ON p.id = pp.player_id
			JOIN realms r ON p.realm_id = r.id
			LEFT JOIN player_details pd ON p.id = pd.player_id
			WHERE pp.has_complete_coverage = 1
			  AND COALESCE(p.is_valid, 1) = 1
			  AND (pd.last_updated IS NULL OR pd.last_updated < ?)
			ORDER BY pp.global_ranking
		`, staleBefore)
	} else {
		rows, err = ds.db.Query(`
			SELECT p.id, p.name, r.slug as realm_slug, r.region
			FROM players p
			JOIN player_profiles pp ON p.id = pp.player_id
			JOIN realms r ON p.realm_id = r.id
			LEFT JOIN player_details pd ON p.id = pd.player_id
			WHERE pp.has_complete_coverage = 1
			  AND COALESCE(p.is_valid, 1) = 1
			  AND pd.last_updated IS NULL
			ORDER BY pp.global_ranking
		`)
	}

	if err != nil {
		return nil, fmt.Errorf("failed to query eligible players: %w", err)
	}
	defer rows.Close()

	var players []blizzard.PlayerInfo
	for rows.Next() {
		var player blizzard.PlayerInfo
		err := rows.Scan(&player.ID, &player.Name, &player.RealmSlug, &player.Region)
		if err != nil {
			return nil, fmt.Errorf("failed to scan player row: %w", err)
		}
		players = append(players, player)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating player rows: %w", err)
	}

	return players, nil
}

// InsertPlayerProfileData inserts player profile data and returns counts
func (ds *DatabaseService) InsertPlayerProfileData(result blizzard.PlayerProfileRe
sult, timestamp int64) (int, int, error) {
	if result.Error != nil {
		return 0, 0, result.Error
	}

	profilesUpdated := 0
	equipmentUpdated := 0

	tx, err := ds.db.Begin()
	if err != nil {
		return 0, 0, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer tx.Rollback()

	if result.Summary != nil {
		err := ds.insertPlayerDetailsTx(tx, result.PlayerID, result.Summary, result.Medi
a, timestamp)
		if err != nil {
			return 0, 0, fmt.Errorf("failed to insert player details: %w", err)
		}
		profilesUpdated++
	}

	if result.Equipment != nil {
		itemCount, err := ds.insertPlayerEquipmentTx(tx, result.PlayerID, result.Equipme
nt, timestamp)
		if err != nil {
			return 0, 0, fmt.Errorf("failed to insert player equipment: %w", err)
		}
		equipmentUpdated += itemCount
	}

	if err := tx.Commit(); err != nil {
		return 0, 0, fmt.Errorf("failed to commit transaction: %w", err)
	}

	return profilesUpdated, equipmentUpdated, nil
}

// insertPlayerDetailsTx inserts player summary data within a transaction
func (ds *DatabaseService) insertPlayerDetailsTx(tx *sql.Tx, playerID int, summary
 *blizzard.CharacterSummaryResponse, media *blizzard.CharacterMediaResponse, times
tamp int64) error {
	var avatarURL *string
	if media != nil {
		for _, asset := range media.Assets {
			if asset.Key == "avatar" {
				avatarURL = &asset.Value
				break
			}
		}
	}

	var guildName *string
	if summary.Guild != nil {
		guildName = &summary.Guild.Name
	}

	_, err := tx.Exec(`
        INSERT INTO player_details (
            player_id, race_id, race_name, gender, class_id, class_name,
            active_spec_id, active_spec_name, guild_name, level,
            average_item_level, equipped_item_level, avatar_url, last_updated
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(player_id) DO UPDATE SET
            race_id = excluded.race_id,
            race_name = excluded.race_name,
            gender = excluded.gender,
            class_id = excluded.class_id,
            class_name = excluded.class_name,
            active_spec_id = excluded.active_spec_id,
            active_spec_name = excluded.active_spec_name,
            guild_name = excluded.guild_name,
            level = excluded.level,
            average_item_level = excluded.average_item_level,
            equipped_item_level = excluded.equipped_item_level,
            avatar_url = excluded.avatar_url,
            last_updated = excluded.last_updated
        WHERE
            player_details.race_id               IS NOT excluded.race_id OR
            player_details.race_name            IS NOT excluded.race_name OR
            player_details.gender               IS NOT excluded.gender OR
            player_details.class_id             IS NOT excluded.class_id OR
            player_details.class_name           IS NOT excluded.class_name OR
            player_details.active_spec_id       IS NOT excluded.active_spec_id OR
            player_details.active_spec_name     IS NOT excluded.active_spec_name O
R
            player_details.guild_name           IS NOT excluded.guild_name OR
            player_details.level                IS NOT excluded.level OR
            player_details.average_item_level   IS NOT excluded.average_item_level
 OR
            player_details.equipped_item_level  IS NOT excluded.equipped_item_leve
l OR
            player_details.avatar_url           IS NOT excluded.avatar_url
    `,
		playerID,
		summary.Race.ID,
		summary.Race.Name,
		summary.Gender.Type,
		summary.CharacterClass.ID,
		summary.CharacterClass.Name,
		summary.ActiveSpec.ID,
		summary.ActiveSpec.Name,
		guildName,
		summary.Level,
		summary.AverageItemLevel,
		summary.EquippedItemLevel,
		avatarURL,
		timestamp,
	)
	if err != nil {
		return err
	}

	// always refresh last_updated to prevent re-fetching unchanged profiles
	_, err = tx.Exec(
		`UPDATE player_details SET last_updated = ? WHERE player_id = ?`,
		timestamp, playerID,
	)
	return err
}

// insertPlayerEquipmentTx upserts player equipment data within a transaction
// Only stores current gear - one row per (player_id, slot_type)
func (ds *DatabaseService) insertPlayerEquipmentTx(tx *sql.Tx, playerID int, equip
ment *blizzard.CharacterEquipmentResponse, timestamp int64) (int, error) {
	if equipment == nil || len(equipment.EquippedItems) == 0 {
		return 0, nil
	}

	equipmentCount := 0

	for _, item := range equipment.EquippedItems {
		// upsert equipment - replace if exists
		result, err := tx.Exec(`
			INSERT INTO player_equipment (
				player_id, slot_type, item_id, upgrade_id, quality, item_name, snapshot_timest
amp
			) VALUES (?, ?, ?, ?, ?, ?, ?)
			ON CONFLICT(player_id, slot_type) DO UPDATE SET
				item_id = excluded.item_id,
				upgrade_id = excluded.upgrade_id,
				quality = excluded.quality,
				item_name = excluded.item_name,
				snapshot_timestamp = excluded.snapshot_timestamp
		`,
			playerID,
			item.Slot.Type,
			item.Item.ID,
			item.UpgradeID,
			item.Quality.Type,
			item.Name,
			timestamp,
		)
		if err != nil {
			return 0, fmt.Errorf("failed to upsert equipment item: %w", err)
		}

		// get the equipment ID (works for both insert and update)
		var equipmentID int64
		err = tx.QueryRow(
			`SELECT id FROM player_equipment WHERE player_id = ? AND slot_type = ?`,
			playerID, item.Slot.Type,
		).Scan(&equipmentID)
		if err != nil {
			return 0, fmt.Errorf("failed to get equipment ID: %w", err)
		}

		// delete old enchantments for this equipment slot
		_, err = tx.Exec(`DELETE FROM player_equipment_enchantments WHERE equipment_id =
 ?`, equipmentID)
		if err != nil {
			return 0, fmt.Errorf("failed to delete old enchantments: %w", err)
		}

		rowsAffected, _ := result.RowsAffected()
		if rowsAffected > 0 {
			equipmentCount++
		}

		// insert new enchantments
		for _, enchant := range item.Enchantments {
			var sourceItemID *int
			var sourceItemName *string
			if enchant.SourceItem != nil {
				sourceItemID = &enchant.SourceItem.ID
				sourceItemName = &enchant.SourceItem.Name
			}

			var spellID *int
			if enchant.Spell != nil {
				spellID = &enchant.Spell.Spell.ID
			}

			var slotID *int
			var slotType *string
			if enchant.EnchantmentSlot != nil {
				slotID = &enchant.EnchantmentSlot.ID
				slotType = &enchant.EnchantmentSlot.Type
			}

			_, err := tx.Exec(`
				INSERT INTO player_equipment_enchantments (
					equipment_id, enchantment_id, slot_id, slot_type,
					display_string, source_item_id, source_item_name, spell_id
				) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
			`,
				equipmentID,
				enchant.EnchantmentID,
				slotID,
				slotType,
				enchant.DisplayString,
				sourceItemID,
				sourceItemName,
				spellID,
			)
			if err != nil {
				return 0, fmt.Errorf("failed to insert enchantment: %w", err)
			}
		}
	}

	return equipmentCount, nil
}

// CountPlayersMissingFingerprints returns how many valid players still lack a fin
gerprint
func (ds *DatabaseService) CountPlayersMissingFingerprints() (int, error) {
	var count int
	err := ds.db.QueryRow(`
		SELECT COUNT(*)
		FROM players p
		LEFT JOIN player_fingerprints pf ON pf.player_id = p.id
		WHERE pf.player_id IS NULL AND COALESCE(p.is_valid, 1) != 0
	`).Scan(&count)
	if err != nil {
		return 0, fmt.Errorf("count missing fingerprints: %w", err)
	}
	return count, nil
}

// CountPlayersNeedingStatusCheck returns how many players require a new status ch
eck
func (ds *DatabaseService) CountPlayersNeedingStatusCheck(staleBefore int64) (int,
 error) {
	var count int
	var err error
	if staleBefore > 0 {
		err = ds.db.QueryRow(`
			SELECT COUNT(*)
			FROM players
			WHERE status_checked_at IS NULL OR status_checked_at < ?
		`, staleBefore).Scan(&count)
	} else {
		err = ds.db.QueryRow(`
			SELECT COUNT(*)
			FROM players
			WHERE status_checked_at IS NULL
		`).Scan(&count)
	}
	if err != nil {
		return 0, fmt.Errorf("count stale statuses: %w", err)
	}
	return count, nil
}

// UpdatePlayerStatus updates the cached status flags for a player
func (ds *DatabaseService) UpdatePlayerStatus(playerID int64, isValid bool, checke
dAt int64, blizzardID *int) error {
	statusInt := 0
	if isValid {
		statusInt = 1
	}

	if blizzardID != nil {
		return retryOnBusy(func() error {
			_, err := ds.db.Exec(`
				UPDATE players
				SET is_valid = ?, status_checked_at = ?, blizzard_character_id = ?
				WHERE id = ?
			`, statusInt, checkedAt, *blizzardID, playerID)
			return err
		})
	}

	return retryOnBusy(func() error {
		_, err := ds.db.Exec(`
			UPDATE players
			SET is_valid = ?, status_checked_at = ?
			WHERE id = ?
		`, statusInt, checkedAt, playerID)
		return err
	})
}

// GetPlayersNeedingFingerprints returns a batch of players lacking fingerprint da
ta
func (ds *DatabaseService) GetPlayersNeedingFingerprints(limit int) ([]PlayerFinge
rprintCandidate, error) {
	if limit <= 0 {
		limit = 50
	}

	query := `
		SELECT
			p.id,
			p.name,
			r.region,
			r.slug,
			p.blizzard_character_id,
			(
				SELECT rm.spec_id
				FROM run_members rm
				WHERE rm.player_id = p.id AND rm.spec_id IS NOT NULL
				ORDER BY rm.run_id DESC
				LIMIT 1
			) AS latest_spec_id,
			pd.class_id,
			(
				SELECT MIN(cr.completed_timestamp)
				FROM run_members rm
				JOIN challenge_runs cr ON cr.id = rm.run_id
				WHERE rm.player_id = p.id
			) AS first_run_ts,
			(
				SELECT MAX(cr.completed_timestamp)
				FROM run_members rm
				JOIN challenge_runs cr ON cr.id = rm.run_id
				WHERE rm.player_id = p.id
			) AS last_run_ts
		FROM players p
		JOIN realms r ON r.id = p.realm_id
		LEFT JOIN player_fingerprints pf ON pf.player_id = p.id
		LEFT JOIN player_details pd ON pd.player_id = p.id
		WHERE pf.player_id IS NULL AND COALESCE(p.is_valid, 1) != 0
		ORDER BY last_run_ts DESC
		LIMIT ?
	`

	rows, err := ds.db.Query(query, limit)
	if err != nil {
		return nil, fmt.Errorf("failed to query fingerprint candidates: %w", err)
	}
	defer rows.Close()

	var candidates []PlayerFingerprintCandidate
	for rows.Next() {
		var c PlayerFingerprintCandidate
		if err := rows.Scan(
			&c.PlayerID,
			&c.Name,
			&c.Region,
			&c.RealmSlug,
			&c.BlizzardCharacterID,
			&c.LatestSpecID,
			&c.DetailsClassID,
			&c.FirstRunTimestamp,
			&c.LastRunTimestamp,
		); err != nil {
			return nil, err
		}
		candidates = append(candidates, c)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return candidates, nil
}

// GetPlayersNeedingStatusCheck returns players whose status check is stale
func (ds *DatabaseService) GetPlayersNeedingStatusCheck(limit int, staleBefore int
64) ([]PlayerStatusCandidate, error) {
	if limit <= 0 {
		limit = 100
	}

	var rows *sql.Rows
	var err error
	if staleBefore > 0 {
		rows, err = ds.db.Query(`
			SELECT p.id, p.name, r.region, r.slug, p.status_checked_at, p.blizzard_characte
r_id
			FROM players p
			JOIN realms r ON r.id = p.realm_id
			WHERE p.status_checked_at IS NULL OR p.status_checked_at < ?
			ORDER BY CASE WHEN p.status_checked_at IS NULL THEN 0 ELSE 1 END, p.status_chec
ked_at ASC
			LIMIT ?
		`, staleBefore, limit)
	} else {
		rows, err = ds.db.Query(`
			SELECT p.id, p.name, r.region, r.slug, p.status_checked_at, p.blizzard_characte
r_id
			FROM players p
			JOIN realms r ON r.id = p.realm_id
			WHERE p.status_checked_at IS NULL
			ORDER BY p.id
			LIMIT ?
		`, limit)
	}
	if err != nil {
		return nil, fmt.Errorf("failed to query status candidates: %w", err)
	}
	defer rows.Close()

	var candidates []PlayerStatusCandidate
	for rows.Next() {
		var c PlayerStatusCandidate
		if err := rows.Scan(&c.PlayerID, &c.Name, &c.Region, &c.RealmSlug, &c.StatusChec
kedAt, &c.BlizzardCharacterID); err != nil {
			return nil, err
		}
		candidates = append(candidates, c)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return candidates, nil
}

// GetPlayerIDByFingerprintHash returns the player_id owning a fingerprint hash
func (ds *DatabaseService) GetPlayerIDByFingerprintHash(hash string) (int64, error
) {
	if strings.TrimSpace(hash) == "" {
		return 0, nil
	}
	var playerID int64
	err := retryOnBusy(func() error {
		return ds.db.QueryRow(`SELECT player_id FROM player_fingerprints WHERE fingerpri
nt_hash = ?`, hash).Scan(&playerID)
	})
	if err == sql.ErrNoRows {
		return 0, nil
	}
	return playerID, err
}

// UpsertPlayerFingerprint inserts or updates a player's fingerprint record
func (ds *DatabaseService) UpsertPlayerFingerprint(fp PlayerFingerprint) error {
	return retryOnBusy(func() error {
		_, err := ds.db.Exec(`
			INSERT INTO player_fingerprints (
				player_id,
				fingerprint_hash,
				class_id,
				level85_timestamp,
				level90_timestamp,
				earliest_heroic_timestamp,
				last_seen_name,
				last_seen_realm_slug,
				last_seen_timestamp,
				first_run_timestamp,
				created_at
			) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
			ON CONFLICT(player_id) DO UPDATE SET
				fingerprint_hash = excluded.fingerprint_hash,
				class_id = excluded.class_id,
				level85_timestamp = excluded.level85_timestamp,
				level90_timestamp = excluded.level90_timestamp,
				earliest_heroic_timestamp = excluded.earliest_heroic_timestamp,
				last_seen_name = excluded.last_seen_name,
				last_seen_realm_slug = excluded.last_seen_realm_slug,
				last_seen_timestamp = excluded.last_seen_timestamp,
				first_run_timestamp = excluded.first_run_timestamp
		`, fp.PlayerID, fp.FingerprintHash, fp.ClassID, fp.Level85Timestamp, fp.Level90T
imestamp, fp.EarliestHeroicTimestamp,
			fp.LastSeenName, fp.LastSeenRealmSlug, fp.LastSeenTimestamp, fp.FirstRunTimesta
mp, fp.CreatedAt)
		return err
	})
}

// GetAllFingerprintHashes loads all existing fingerprint hashes into memory for c
ollision detection
func (ds *DatabaseService) GetAllFingerprintHashes() (map[string]int64, error) {
	query := `SELECT fingerprint_hash, player_id FROM player_fingerprints`

	rows, err := ds.db.Query(query)
	if err != nil {
		return nil, fmt.Errorf("query all fingerprint hashes: %w", err)
	}
	defer rows.Close()

	result := make(map[string]int64)
	for rows.Next() {
		var hash string
		var playerID int64
		if err := rows.Scan(&hash, &playerID); err != nil {
			return nil, err
		}
		result[hash] = playerID
	}
	return result, rows.Err()
}

// DeletePlayerFingerprint removes a player's fingerprint record
func (ds *DatabaseService) DeletePlayerFingerprint(playerID int64) error {
	return retryOnBusy(func() error {
		_, err := ds.db.Exec(`DELETE FROM player_fingerprints WHERE player_id = ?`, play
erID)
		return err
	})
}

// MigratePlayerRuns updates all run_members records from one player to another
func (ds *DatabaseService) MigratePlayerRuns(fromPlayerID, toPlayerID int64) (int,
 error) {
	var rowsAffected int
	err := retryOnBusy(func() error {
		result, err := ds.db.Exec(`
			UPDATE run_members
			SET player_id = ?
			WHERE player_id = ?
		`, toPlayerID, fromPlayerID)

		if err != nil {
			return fmt.Errorf("migrate runs %d?%d: %w", fromPlayerID, toPlayerID, err)
		}

		rows, _ := result.RowsAffected()
		rowsAffected = int(rows)
		return nil
	})
	return rowsAffected, err
}

// InvalidatePlayerProfile deletes cached profile to force rebuild
func (ds *DatabaseService) InvalidatePlayerProfile(playerID int64) error {
	return retryOnBusy(func() error {
		_, err := ds.db.Exec(`
			DELETE FROM player_profiles
			WHERE player_id = ?
		`, playerID)
		return err
	})
}

// GetPlayerByNameRealmRegion looks up a player by name, realm slug, and region
func (ds *DatabaseService) GetPlayerByNameRealmRegion(name, realmSlug, region stri
ng) (int64, error) {
	var playerID int64
	err := retryOnBusy(func() error {
		return ds.db.QueryRow(`
			SELECT p.id
			FROM players p
			JOIN realms r ON r.id = p.realm_id
			WHERE p.name_lower = LOWER(?)
			  AND r.slug = ?
			  AND r.region = ?
			LIMIT 1
		`, name, realmSlug, region).Scan(&playerID)
	})

	if err == sql.ErrNoRows {
		return 0, fmt.Errorf("player not found: %s-%s (%s)", name, realmSlug, region)
	}
	if err != nil {
		return 0, fmt.Errorf("query player: %w", err)
	}
	return playerID, nil
}

// getExistingPlayerIDTx looks up an existing player by name+realm within a transa
ction.
// Returns 0 if no player found (not an error).
func getExistingPlayerIDTx(tx *sql.Tx, name string, realmID int) (int64, error) {
	var playerID int64
	err := tx.QueryRow(`
		SELECT id FROM players
		WHERE name_lower = LOWER(?) AND realm_id = ?
		LIMIT 1
	`, name, realmID).Scan(&playerID)

	if err == sql.ErrNoRows {
		return 0, nil
	}
	if err != nil {
		return 0, err
	}
	return playerID, nil
}


package database

import (
	"database/sql"
	"fmt"
	"ookstats/internal/blizzard"
	"strings"
)

// GetEligiblePlayersForProfileFetch returns
 players with complete coverage (9/9 dungeon
s)
// staleBefore: Unix timestamp in millisecon
ds. If > 0, returns players whose profile is
 NULL or older than this.
// If 0, returns only players who have never
 had a profile fetched.
func (ds *DatabaseService) GetEligiblePlayer
sForProfileFetch(staleBefore int64) ([]blizz
ard.PlayerInfo, error) {
	var rows *sql.Rows
	var err error

	if staleBefore > 0 {
		rows, err = ds.db.Query(`
			SELECT p.id, p.name, r.slug as realm_slug
, r.region
			FROM players p
			JOIN player_profiles pp ON p.id = pp.play
er_id
			JOIN realms r ON p.realm_id = r.id
			LEFT JOIN player_details pd ON p.id = pd.
player_id
			WHERE pp.has_complete_coverage = 1
			  AND COALESCE(p.is_valid, 1) = 1
			  AND (pd.last_updated IS NULL OR pd.last
_updated < ?)
			ORDER BY pp.global_ranking
		`, staleBefore)
	} else {
		rows, err = ds.db.Query(`
			SELECT p.id, p.name, r.slug as realm_slug
, r.region
			FROM players p
			JOIN player_profiles pp ON p.id = pp.play
er_id
			JOIN realms r ON p.realm_id = r.id
			LEFT JOIN player_details pd ON p.id = pd.
player_id
			WHERE pp.has_complete_coverage = 1
			  AND COALESCE(p.is_valid, 1) = 1
			  AND pd.last_updated IS NULL
			ORDER BY pp.global_ranking
		`)
	}

	if err != nil {
		return nil, fmt.Errorf("failed to query el
igible players: %w", err)
	}
	defer rows.Close()

	var players []blizzard.PlayerInfo
	for rows.Next() {
		var player blizzard.PlayerInfo
		err := rows.Scan(&player.ID, &player.Name,
 &player.RealmSlug, &player.Region)
		if err != nil {
			return nil, fmt.Errorf("failed to scan pl
ayer row: %w", err)
		}
		players = append(players, player)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating pl
ayer rows: %w", err)
	}

	return players, nil
}

// InsertPlayerProfileData inserts player pr
ofile data and returns counts
func (ds *DatabaseService) InsertPlayerProfi
leData(result blizzard.PlayerProfileResult, 
timestamp int64) (int, int, error) {
	if result.Error != nil {
		return 0, 0, result.Error
	}

	profilesUpdated := 0
	equipmentUpdated := 0

	tx, err := ds.db.Begin()
	if err != nil {
		return 0, 0, fmt.Errorf("failed to begin t
ransaction: %w", err)
	}
	defer tx.Rollback()

	if result.Summary != nil {
		err := ds.insertPlayerDetailsTx(tx, result
.PlayerID, result.Summary, result.Media, tim
estamp)
		if err != nil {
			return 0, 0, fmt.Errorf("failed to insert
 player details: %w", err)
		}
		profilesUpdated++
	}

	if result.Equipment != nil {
		itemCount, err := ds.insertPlayerEquipment
Tx(tx, result.PlayerID, result.Equipment, ti
mestamp)
		if err != nil {
			return 0, 0, fmt.Errorf("failed to insert
 player equipment: %w", err)
		}
		equipmentUpdated += itemCount
	}

	if err := tx.Commit(); err != nil {
		return 0, 0, fmt.Errorf("failed to commit 
transaction: %w", err)
	}

	return profilesUpdated, equipmentUpdated, n
il
}

// insertPlayerDetailsTx inserts player summ
ary data within a transaction
func (ds *DatabaseService) insertPlayerDetai
lsTx(tx *sql.Tx, playerID int, summary *bliz
zard.CharacterSummaryResponse, media *blizza
rd.CharacterMediaResponse, timestamp int64) 
error {
	var avatarURL *string
	if media != nil {
		for _, asset := range media.Assets {
			if asset.Key == "avatar" {
				avatarURL = &asset.Value
				break
			}
		}
	}

	var guildName *string
	if summary.Guild != nil {
		guildName = &summary.Guild.Name
	}

	_, err := tx.Exec(`
        INSERT INTO player_details (
            player_id, race_id, race_name, g
ender, class_id, class_name,
            active_spec_id, active_spec_name
, guild_name, level,
            average_item_level, equipped_ite
m_level, avatar_url, last_updated
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?,
 ?, ?, ?, ?, ?)
        ON CONFLICT(player_id) DO UPDATE SET
            race_id = excluded.race_id,
            race_name = excluded.race_name,
            gender = excluded.gender,
            class_id = excluded.class_id,
            class_name = excluded.class_name
,
            active_spec_id = excluded.active
_spec_id,
            active_spec_name = excluded.acti
ve_spec_name,
            guild_name = excluded.guild_name
,
            level = excluded.level,
            average_item_level = excluded.av
erage_item_level,
            equipped_item_level = excluded.e
quipped_item_level,
            avatar_url = excluded.avatar_url
,
            last_updated = excluded.last_upd
ated
        WHERE
            player_details.race_id          
     IS NOT excluded.race_id OR
            player_details.race_name        
    IS NOT excluded.race_name OR
            player_details.gender           
    IS NOT excluded.gender OR
            player_details.class_id         
    IS NOT excluded.class_id OR
            player_details.class_name       
    IS NOT excluded.class_name OR
            player_details.active_spec_id   
    IS NOT excluded.active_spec_id OR
            player_details.active_spec_name 
    IS NOT excluded.active_spec_name OR
            player_details.guild_name       
    IS NOT excluded.guild_name OR
            player_details.level            
    IS NOT excluded.level OR
            player_details.average_item_leve
l   IS NOT excluded.average_item_level OR
            player_details.equipped_item_lev
el  IS NOT excluded.equipped_item_level OR
            player_details.avatar_url       
    IS NOT excluded.avatar_url
    `,
		playerID,
		summary.Race.ID,
		summary.Race.Name,
		summary.Gender.Type,
		summary.CharacterClass.ID,
		summary.CharacterClass.Name,
		summary.ActiveSpec.ID,
		summary.ActiveSpec.Name,
		guildName,
		summary.Level,
		summary.AverageItemLevel,
		summary.EquippedItemLevel,
		avatarURL,
		timestamp,
	)
	if err != nil {
		return err
	}

	// always refresh last_updated to prevent r
e-fetching unchanged profiles
	_, err = tx.Exec(
		`UPDATE player_details SET last_updated = 
? WHERE player_id = ?`,
		timestamp, playerID,
	)
	return err
}

// insertPlayerEquipmentTx upserts player eq
uipment data within a transaction
// Only stores current gear - one row per (p
layer_id, slot_type)
func (ds *DatabaseService) insertPlayerEquip
mentTx(tx *sql.Tx, playerID int, equipment *
blizzard.CharacterEquipmentResponse, timesta
mp int64) (int, error) {
	if equipment == nil || len(equipment.Equipp
edItems) == 0 {
		return 0, nil
	}

	equipmentCount := 0

	for _, item := range equipment.EquippedItem
s {
		// upsert equipment - replace if exists
		result, err := tx.Exec(`
			INSERT INTO player_equipment (
				player_id, slot_type, item_id, upgrade_i
d, quality, item_name, snapshot_timestamp
			) VALUES (?, ?, ?, ?, ?, ?, ?)
			ON CONFLICT(player_id, slot_type) DO UPDA
TE SET
				item_id = excluded.item_id,
				upgrade_id = excluded.upgrade_id,
				quality = excluded.quality,
				item_name = excluded.item_name,
				snapshot_timestamp = excluded.snapshot_t
imestamp
		`,
			playerID,
			item.Slot.Type,
			item.Item.ID,
			item.UpgradeID,
			item.Quality.Type,
			item.Name,
			timestamp,
		)
		if err != nil {
			return 0, fmt.Errorf("failed to upsert eq
uipment item: %w", err)
		}

		// get the equipment ID (works for both in
sert and update)
		var equipmentID int64
		err = tx.QueryRow(
			`SELECT id FROM player_equipment WHERE pl
ayer_id = ? AND slot_type = ?`,
			playerID, item.Slot.Type,
		).Scan(&equipmentID)
		if err != nil {
			return 0, fmt.Errorf("failed to get equip
ment ID: %w", err)
		}

		// delete old enchantments for this equipm
ent slot
		_, err = tx.Exec(`DELETE FROM player_equip
ment_enchantments WHERE equipment_id = ?`, e
quipmentID)
		if err != nil {
			return 0, fmt.Errorf("failed to delete ol
d enchantments: %w", err)
		}

		rowsAffected, _ := result.RowsAffected()
		if rowsAffected > 0 {
			equipmentCount++
		}

		// insert new enchantments
		for _, enchant := range item.Enchantments 
{
			var sourceItemID *int
			var sourceItemName *string
			if enchant.SourceItem != nil {
				sourceItemID = &enchant.SourceItem.ID
				sourceItemName = &enchant.SourceItem.Nam
e
			}

			var spellID *int
			if enchant.Spell != nil {
				spellID = &enchant.Spell.Spell.ID
			}

			var slotID *int
			var slotType *string
			if enchant.EnchantmentSlot != nil {
				slotID = &enchant.EnchantmentSlot.ID
				slotType = &enchant.EnchantmentSlot.Type
			}

			_, err := tx.Exec(`
				INSERT INTO player_equipment_enchantment
s (
					equipment_id, enchantment_id, slot_id, 
slot_type,
					display_string, source_item_id, source_
item_name, spell_id
				) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
			`,
				equipmentID,
				enchant.EnchantmentID,
				slotID,
				slotType,
				enchant.DisplayString,
				sourceItemID,
				sourceItemName,
				spellID,
			)
			if err != nil {
				return 0, fmt.Errorf("failed to insert e
nchantment: %w", err)
			}
		}
	}

	return equipmentCount, nil
}

// CountPlayersMissingFingerprints returns h
ow many valid players still lack a fingerpri
nt
func (ds *DatabaseService) CountPlayersMissi
ngFingerprints() (int, error) {
	var count int
	err := ds.db.QueryRow(`
		SELECT COUNT(*)
		FROM players p
		LEFT JOIN player_fingerprints pf ON pf.pla
yer_id = p.id
		WHERE pf.player_id IS NULL AND COALESCE(p.
is_valid, 1) != 0
	`).Scan(&count)
	if err != nil {
		return 0, fmt.Errorf("count missing finger
prints: %w", err)
	}
	return count, nil
}

// CountPlayersNeedingStatusCheck returns ho
w many players require a new status check
func (ds *DatabaseService) CountPlayersNeedi
ngStatusCheck(staleBefore int64) (int, error
) {
	var count int
	var err error
	if staleBefore > 0 {
		err = ds.db.QueryRow(`
			SELECT COUNT(*)
			FROM players
			WHERE status_checked_at IS NULL OR status
_checked_at < ?
		`, staleBefore).Scan(&count)
	} else {
		err = ds.db.QueryRow(`
			SELECT COUNT(*)
			FROM players
			WHERE status_checked_at IS NULL
		`).Scan(&count)
	}
	if err != nil {
		return 0, fmt.Errorf("count stale statuses
: %w", err)
	}
	return count, nil
}

// UpdatePlayerStatus updates the cached sta
tus flags for a player
func (ds *DatabaseService) UpdatePlayerStatu
s(playerID int64, isValid bool, checkedAt in
t64, blizzardID *int) error {
	statusInt := 0
	if isValid {
		statusInt = 1
	}

	if blizzardID != nil {
		return retryOnBusy(func() error {
			_, err := ds.db.Exec(`
				UPDATE players
				SET is_valid = ?, status_checked_at = ?,
 blizzard_character_id = ?
				WHERE id = ?
			`, statusInt, checkedAt, *blizzardID, pla
yerID)
			return err
		})
	}

	return retryOnBusy(func() error {
		_, err := ds.db.Exec(`
			UPDATE players
			SET is_valid = ?, status_checked_at = ?
			WHERE id = ?
		`, statusInt, checkedAt, playerID)
		return err
	})
}

// GetPlayersNeedingFingerprints returns a b
atch of players lacking fingerprint data
func (ds *DatabaseService) GetPlayersNeeding
Fingerprints(limit int) ([]PlayerFingerprint
Candidate, error) {
	if limit <= 0 {
		limit = 50
	}

	query := `
		SELECT
			p.id,
			p.name,
			r.region,
			r.slug,
			p.blizzard_character_id,
			(
				SELECT rm.spec_id
				FROM run_members rm
				WHERE rm.player_id = p.id AND rm.spec_id
 IS NOT NULL
				ORDER BY rm.run_id DESC
				LIMIT 1
			) AS latest_spec_id,
			pd.class_id,
			(
				SELECT MIN(cr.completed_timestamp)
				FROM run_members rm
				JOIN challenge_runs cr ON cr.id = rm.run
_id
				WHERE rm.player_id = p.id
			) AS first_run_ts,
			(
				SELECT MAX(cr.completed_timestamp)
				FROM run_members rm
				JOIN challenge_runs cr ON cr.id = rm.run
_id
				WHERE rm.player_id = p.id
			) AS last_run_ts
		FROM players p
		JOIN realms r ON r.id = p.realm_id
		LEFT JOIN player_fingerprints pf ON pf.pla
yer_id = p.id
		LEFT JOIN player_details pd ON pd.player_i
d = p.id
		WHERE pf.player_id IS NULL AND COALESCE(p.
is_valid, 1) != 0
		ORDER BY last_run_ts DESC
		LIMIT ?
	`

	rows, err := ds.db.Query(query, limit)
	if err != nil {
		return nil, fmt.Errorf("failed to query fi
ngerprint candidates: %w", err)
	}
	defer rows.Close()

	var candidates []PlayerFingerprintCandidate
	for rows.Next() {
		var c PlayerFingerprintCandidate
		if err := rows.Scan(
			&c.PlayerID,
			&c.Name,
			&c.Region,
			&c.RealmSlug,
			&c.BlizzardCharacterID,
			&c.LatestSpecID,
			&c.DetailsClassID,
			&c.FirstRunTimestamp,
			&c.LastRunTimestamp,
		); err != nil {
			return nil, err
		}
		candidates = append(candidates, c)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return candidates, nil
}

// GetPlayersNeedingStatusCheck returns play
ers whose status check is stale
func (ds *DatabaseService) GetPlayersNeeding
StatusCheck(limit int, staleBefore int64) ([
]PlayerStatusCandidate, error) {
	if limit <= 0 {
		limit = 100
	}

	var rows *sql.Rows
	var err error
	if staleBefore > 0 {
		rows, err = ds.db.Query(`
			SELECT p.id, p.name, r.region, r.slug, p.
status_checked_at, p.blizzard_character_id
			FROM players p
			JOIN realms r ON r.id = p.realm_id
			WHERE p.status_checked_at IS NULL OR p.st
atus_checked_at < ?
			ORDER BY CASE WHEN p.status_checked_at IS
 NULL THEN 0 ELSE 1 END, p.status_checked_at
 ASC
			LIMIT ?
		`, staleBefore, limit)
	} else {
		rows, err = ds.db.Query(`
			SELECT p.id, p.name, r.region, r.slug, p.
status_checked_at, p.blizzard_character_id
			FROM players p
			JOIN realms r ON r.id = p.realm_id
			WHERE p.status_checked_at IS NULL
			ORDER BY p.id
			LIMIT ?
		`, limit)
	}
	if err != nil {
		return nil, fmt.Errorf("failed to query st
atus candidates: %w", err)
	}
	defer rows.Close()

	var candidates []PlayerStatusCandidate
	for rows.Next() {
		var c PlayerStatusCandidate
		if err := rows.Scan(&c.PlayerID, &c.Name, 
&c.Region, &c.RealmSlug, &c.StatusCheckedAt,
 &c.BlizzardCharacterID); err != nil {
			return nil, err
		}
		candidates = append(candidates, c)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return candidates, nil
}

// GetPlayerIDByFingerprintHash returns the 
player_id owning a fingerprint hash
func (ds *DatabaseService) GetPlayerIDByFing
erprintHash(hash string) (int64, error) {
	if strings.TrimSpace(hash) == "" {
		return 0, nil
	}
	var playerID int64
	err := retryOnBusy(func() error {
		return ds.db.QueryRow(`SELECT player_id FR
OM player_fingerprints WHERE fingerprint_has
h = ?`, hash).Scan(&playerID)
	})
	if err == sql.ErrNoRows {
		return 0, nil
	}
	return playerID, err
}

// UpsertPlayerFingerprint inserts or update
s a player's fingerprint record
func (ds *DatabaseService) UpsertPlayerFinge
rprint(fp PlayerFingerprint) error {
	return retryOnBusy(func() error {
		_, err := ds.db.Exec(`
			INSERT INTO player_fingerprints (
				player_id,
				fingerprint_hash,
				class_id,
				level85_timestamp,
				level90_timestamp,
				earliest_heroic_timestamp,
				last_seen_name,
				last_seen_realm_slug,
				last_seen_timestamp,
				first_run_timestamp,
				created_at
			) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
			ON CONFLICT(player_id) DO UPDATE SET
				fingerprint_hash = excluded.fingerprint_
hash,
				class_id = excluded.class_id,
				level85_timestamp = excluded.level85_tim
estamp,
				level90_timestamp = excluded.level90_tim
estamp,
				earliest_heroic_timestamp = excluded.ear
liest_heroic_timestamp,
				last_seen_name = excluded.last_seen_name
,
				last_seen_realm_slug = excluded.last_see
n_realm_slug,
				last_seen_timestamp = excluded.last_seen
_timestamp,
				first_run_timestamp = excluded.first_run
_timestamp
		`, fp.PlayerID, fp.FingerprintHash, fp.Cla
ssID, fp.Level85Timestamp, fp.Level90Timesta
mp, fp.EarliestHeroicTimestamp,
			fp.LastSeenName, fp.LastSeenRealmSlug, fp
.LastSeenTimestamp, fp.FirstRunTimestamp, fp
.CreatedAt)
		return err
	})
}

// GetAllFingerprintHashes loads all existin
g fingerprint hashes into memory for collisi
on detection
func (ds *DatabaseService) GetAllFingerprint
Hashes() (map[string]int64, error) {
	query := `SELECT fingerprint_hash, player_i
d FROM player_fingerprints`

	rows, err := ds.db.Query(query)
	if err != nil {
		return nil, fmt.Errorf("query all fingerpr
int hashes: %w", err)
	}
	defer rows.Close()

	result := make(map[string]int64)
	for rows.Next() {
		var hash string
		var playerID int64
		if err := rows.Scan(&hash, &playerID); err
 != nil {
			return nil, err
		}
		result[hash] = playerID
	}
	return result, rows.Err()
}

// DeletePlayerFingerprint removes a player'
s fingerprint record
func (ds *DatabaseService) DeletePlayerFinge
rprint(playerID int64) error {
	return retryOnBusy(func() error {
		_, err := ds.db.Exec(`DELETE FROM player_f
ingerprints WHERE player_id = ?`, playerID)
		return err
	})
}

// MigratePlayerRuns updates all run_members
 records from one player to another
func (ds *DatabaseService) MigratePlayerRuns
(fromPlayerID, toPlayerID int64) (int, error
) {
	var rowsAffected int
	err := retryOnBusy(func() error {
		result, err := ds.db.Exec(`
			UPDATE run_members
			SET player_id = ?
			WHERE player_id = ?
		`, toPlayerID, fromPlayerID)

		if err != nil {
			return fmt.Errorf("migrate runs %d?%d: %w
", fromPlayerID, toPlayerID, err)
		}

		rows, _ := result.RowsAffected()
		rowsAffected = int(rows)
		return nil
	})
	return rowsAffected, err
}

// InvalidatePlayerProfile deletes cached pr
ofile to force rebuild
func (ds *DatabaseService) InvalidatePlayerP
rofile(playerID int64) error {
	return retryOnBusy(func() error {
		_, err := ds.db.Exec(`
			DELETE FROM player_profiles
			WHERE player_id = ?
		`, playerID)
		return err
	})
}

// GetPlayerByNameRealmRegion looks up a pla
yer by name, realm slug, and region
func (ds *DatabaseService) GetPlayerByNameRe
almRegion(name, realmSlug, region string) (i
nt64, error) {
	var playerID int64
	err := retryOnBusy(func() error {
		return ds.db.QueryRow(`
			SELECT p.id
			FROM players p
			JOIN realms r ON r.id = p.realm_id
			WHERE p.name_lower = LOWER(?)
			  AND r.slug = ?
			  AND r.region = ?
			LIMIT 1
		`, name, realmSlug, region).Scan(&playerID
)
	})

	if err == sql.ErrNoRows {
		return 0, fmt.Errorf("player not found: %s
-%s (%s)", name, realmSlug, region)
	}
	if err != nil {
		return 0, fmt.Errorf("query player: %w", e
rr)
	}
	return playerID, nil
}

// getExistingPlayerIDTx looks up an existin
g player by name+realm within a transaction.
// Returns 0 if no player found (not an erro
r).
func getExistingPlayerIDTx(tx *sql.Tx, name 
string, realmID int) (int64, error) {
	var playerID int64
	err := tx.QueryRow(`
		SELECT id FROM players
		WHERE name_lower = LOWER(?) AND realm_id =
 ?
		LIMIT 1
	`, name, realmID).Scan(&playerID)

	if err == sql.ErrNoRows {
		return 0, nil
	}
	if err != nil {
		return 0, err
	}
	return playerID, nil
}

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