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

package database

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

// migrateRealmsCompositeSlug upgrades the realms table from slug-unique to (regio
n,slug)-unique if necessary.
func migrateRealmsCompositeSlug(db *sql.DB) error {
	// Inspect table SQL
	var createSQL string
	_ = db.QueryRow(`SELECT sql FROM sqlite_master WHERE type='table' AND name='realm
s'`).Scan(&createSQL)
	// If table already created without UNIQUE on slug, nothing to do
	if !strings.Contains(strings.ToLower(createSQL), "slug text unique") {
		return nil
	}

	fmt.Printf("[MIGRATE] Upgrading realms table to composite (region,slug) uniquenes
s...\n")
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	// Create new table without UNIQUE on slug
	if _, err := tx.Exec(`
        CREATE TABLE IF NOT EXISTS realms_new (
            id INTEGER PRIMARY KEY,
            slug TEXT,
            name TEXT,
            region TEXT,
            connected_realm_id INTEGER UNIQUE,
            parent_realm_slug TEXT
        )
    `); err != nil {
		return fmt.Errorf("create realms_new: %w", err)
	}

	// Copy data
	if _, err := tx.Exec(`INSERT INTO realms_new (id, slug, name, region, connected_r
ealm_id, parent_realm_slug)
                          SELECT id, slug, name, region, connected_realm_id,
                                 CASE WHEN EXISTS(SELECT 1 FROM sqlite_master WHER
E type='table' AND name='realms' AND sql LIKE '%parent_realm_slug%')
                                      THEN parent_realm_slug ELSE NULL END
                          FROM realms`); err != nil {
		return fmt.Errorf("copy realms: %w", err)
	}

	// Rename old and new
	if _, err := tx.Exec(`ALTER TABLE realms RENAME TO realms_old`); err != nil {
		return fmt.Errorf("rename old: %w", err)
	}
	if _, err := tx.Exec(`ALTER TABLE realms_new RENAME TO realms`); err != nil {
		return fmt.Errorf("rename new: %w", err)
	}

	// Drop old
	if _, err := tx.Exec(`DROP TABLE realms_old`); err != nil {
		return fmt.Errorf("drop old: %w", err)
	}

	// Ensure composite unique index
	if _, err := tx.Exec(`CREATE UNIQUE INDEX IF NOT EXISTS idx_realms_region_slug ON
 realms(region, slug)`); err != nil {
		return fmt.Errorf("create composite unique index: %w", err)
	}

	if err := tx.Commit(); err != nil {
		return err
	}
	fmt.Printf("[OK] Realms table migrated\n")
	return nil
}

// migrateSeasonsAddRegion adds region column to seasons table if it doesn't exist
func migrateSeasonsAddRegion(db *sql.DB) error {
	// Check if region column already exists
	var createSQL string
	_ = db.QueryRow(`SELECT sql FROM sqlite_master WHERE type='table' AND name='seaso
ns'`).Scan(&createSQL)

	// If table already has region column, nothing to do
	if strings.Contains(strings.ToLower(createSQL), "region text") {
		return nil
	}

	fmt.Printf("[MIGRATE] Adding region column to seasons table...\n")
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	// Create new table with region column
	if _, err := tx.Exec(`
        CREATE TABLE IF NOT EXISTS seasons_new (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            season_number INTEGER NOT NULL,
            region TEXT NOT NULL,
            start_timestamp INTEGER,
            end_timestamp INTEGER,
            season_name TEXT,
            first_period_id INTEGER,
            last_period_id INTEGER,
            UNIQUE(season_number, region)
        )
    `); err != nil {
		return fmt.Errorf("create seasons_new: %w", err)
	}

	// Copy existing data, defaulting region to 'us' for backward compatibility
	if _, err := tx.Exec(`
        INSERT INTO seasons_new (id, season_number, region, start_timestamp, end_t
imestamp, season_name, first_period_id, last_period_id)
        SELECT id, season_number, 'us', start_timestamp, end_timestamp, season_nam
e, first_period_id, last_period_id
        FROM seasons
    `); err != nil {
		return fmt.Errorf("copy seasons: %w", err)
	}

	// Rename old and new
	if _, err := tx.Exec(`ALTER TABLE seasons RENAME TO seasons_old`); err != nil {
		return fmt.Errorf("rename old: %w", err)
	}
	if _, err := tx.Exec(`ALTER TABLE seasons_new RENAME TO seasons`); err != nil {
		return fmt.Errorf("rename new: %w", err)
	}

	// Drop old
	if _, err := tx.Exec(`DROP TABLE seasons_old`); err != nil {
		return fmt.Errorf("drop old: %w", err)
	}

	if err := tx.Commit(); err != nil {
		return err
	}
	fmt.Printf("[OK] Seasons table migrated - added region column\n")
	return nil
}

// migratePlayersIdentityColumns ensures identity + status metadata columns exist 
on players.
func migratePlayersIdentityColumns(db *sql.DB) error {
	hasBlizzardID, err := columnExists(db, "players", "blizzard_character_id")
	if err != nil {
		return err
	}
	if !hasBlizzardID {
		if _, err := db.Exec(`ALTER TABLE players ADD COLUMN blizzard_character_id INTEG
ER`); err != nil {
			return fmt.Errorf("add blizzard_character_id: %w", err)
		}
		if _, err := db.Exec(`UPDATE players SET blizzard_character_id = id WHERE blizza
rd_character_id IS NULL`); err != nil {
			return fmt.Errorf("backfill blizzard_character_id: %w", err)
		}
	}

	hasIsValid, err := columnExists(db, "players", "is_valid")
	if err != nil {
		return err
	}
	if !hasIsValid {
		if _, err := db.Exec(`ALTER TABLE players ADD COLUMN is_valid INTEGER DEFAULT 1`
); err != nil {
			return fmt.Errorf("add is_valid: %w", err)
		}
		if _, err := db.Exec(`UPDATE players SET is_valid = 1 WHERE is_valid IS NULL`); 
err != nil {
			return fmt.Errorf("backfill is_valid: %w", err)
		}
	}

	hasStatusChecked, err := columnExists(db, "players", "status_checked_at")
	if err != nil {
		return err
	}
	if !hasStatusChecked {
		if _, err := db.Exec(`ALTER TABLE players ADD COLUMN status_checked_at INTEGER`)
; err != nil {
			return fmt.Errorf("add status_checked_at: %w", err)
		}
	}

	hasFpAttempted, err := columnExists(db, "players", "account_fp_attempted_at")
	if err != nil {
		return err
	}
	if !hasFpAttempted {
		if _, err := db.Exec(`ALTER TABLE players ADD COLUMN account_fp_attempted_at INT
EGER`); err != nil {
			return fmt.Errorf("add account_fp_attempted_at: %w", err)
		}
	}
	return nil
}

func columnExists(db *sql.DB, table, column string) (bool, error) {
	rows, err := db.Query(fmt.Sprintf(`PRAGMA table_info(%s)`, table))
	if err != nil {
		return false, err
	}
	defer rows.Close()

	for rows.Next() {
		var (
			cid        int
			name       string
			ctype      string
			notnull    int
			dfltValue  any
			primaryKey int
		)
		if err := rows.Scan(&cid, &name, &ctype, &notnull, &dfltValue, &primaryKey); err
 != nil {
			return false, err
		}
		if strings.EqualFold(name, column) {
			return true, nil
		}
	}
	return false, rows.Err()
}

// migratePlayerRankingsPrimaryKey adds PRIMARY KEY constraint to player_rankings 
table if missing
func migratePlayerRankingsPrimaryKey(db *sql.DB) error {
	// Check if table already has PRIMARY KEY by inspecting schema
	var createSQL string
	err := db.QueryRow(`SELECT sql FROM sqlite_master WHERE type='table' AND name='pl
ayer_rankings'`).Scan(&createSQL)
	if err != nil {
		// Table doesn't exist yet, skip migration
		return nil
	}

	// If PRIMARY KEY already exists, skip migration
	if strings.Contains(strings.ToUpper(createSQL), "PRIMARY KEY") {
		return nil
	}

	fmt.Printf("Migrating player_rankings table to add PRIMARY KEY constraint...\n")

	// Begin transaction for safe migration
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("begin transaction: %w", err)
	}
	defer tx.Rollback()

	// Create new table with PRIMARY KEY
	if _, err := tx.Exec(`
		CREATE TABLE player_rankings_new (
			player_id INTEGER NOT NULL,
			ranking_type TEXT NOT NULL,
			ranking_scope TEXT NOT NULL,
			ranking INTEGER,
			combined_best_time INTEGER,
			last_updated INTEGER,
			PRIMARY KEY (player_id, ranking_type, ranking_scope)
		)
	`); err != nil {
		return fmt.Errorf("create new table: %w", err)
	}

	// Copy data, keeping only the most recent entry per (player_id, ranking_type, ra
nking_scope)
	if _, err := tx.Exec(`
		INSERT INTO player_rankings_new
		SELECT
			player_id,
			ranking_type,
			ranking_scope,
			ranking,
			combined_best_time,
			last_updated
		FROM player_rankings
		WHERE rowid IN (
			SELECT MAX(rowid)
			FROM player_rankings
			GROUP BY player_id, ranking_type, ranking_scope
		)
	`); err != nil {
		return fmt.Errorf("copy data: %w", err)
	}

	// Drop old table
	if _, err := tx.Exec(`DROP TABLE player_rankings`); err != nil {
		return fmt.Errorf("drop old table: %w", err)
	}

	// Rename new table
	if _, err := tx.Exec(`ALTER TABLE player_rankings_new RENAME TO player_rankings`)
; err != nil {
		return fmt.Errorf("rename new table: %w", err)
	}

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

	fmt.Printf("[OK] player_rankings table migrated - added PRIMARY KEY and removed d
uplicates\n")
	return nil
}

// migrateEquipmentToUpsert adds UNIQUE constraint on (player_id, slot_type) and r
emoves historical snapshots
func migrateEquipmentToUpsert(db *sql.DB) error {
	// Check if unique index already exists
	var indexCount int
	err := db.QueryRow(`
		SELECT COUNT(*) FROM sqlite_master
		WHERE type='index' AND name='idx_player_equipment_unique'
	`).Scan(&indexCount)
	if err != nil {
		return fmt.Errorf("check index: %w", err)
	}

	if indexCount > 0 {
		// Already migrated
		return nil
	}

	fmt.Printf("Migrating player_equipment to single-row-per-slot model...\n")

	// Count rows before
	var beforeCount int
	db.QueryRow(`SELECT COUNT(*) FROM player_equipment`).Scan(&beforeCount)

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

	// Create new table with UNIQUE constraint
	if _, err := tx.Exec(`
		CREATE TABLE player_equipment_new (
			id INTEGER PRIMARY KEY,
			player_id INTEGER,
			slot_type TEXT,
			item_id INTEGER,
			upgrade_id INTEGER,
			quality TEXT,
			item_name TEXT,
			snapshot_timestamp INTEGER,
			UNIQUE(player_id, slot_type)
		)
	`); err != nil {
		return fmt.Errorf("create new table: %w", err)
	}

	// Copy only one row per (player_id, slot_type) - pick the one with highest ID
	if _, err := tx.Exec(`
		INSERT INTO player_equipment_new (id, player_id, slot_type, item_id, upgrade_id,
 quality, item_name, snapshot_timestamp)
		SELECT pe.id, pe.player_id, pe.slot_type, pe.item_id, pe.upgrade_id, pe.quality,
 pe.item_name, pe.snapshot_timestamp
		FROM player_equipment pe
		INNER JOIN (
			SELECT MAX(id) as max_id
			FROM player_equipment
			GROUP BY player_id, slot_type
		) latest ON pe.id = latest.max_id
	`); err != nil {
		return fmt.Errorf("copy data: %w", err)
	}

	// Delete orphaned enchantments (those referencing equipment IDs not in the new t
able)
	if _, err := tx.Exec(`
		DELETE FROM player_equipment_enchantments
		WHERE equipment_id NOT IN (SELECT id FROM player_equipment_new)
	`); err != nil {
		return fmt.Errorf("delete orphaned enchantments: %w", err)
	}

	// Drop old table and rename
	if _, err := tx.Exec(`DROP TABLE player_equipment`); err != nil {
		return fmt.Errorf("drop old table: %w", err)
	}

	if _, err := tx.Exec(`ALTER TABLE player_equipment_new RENAME TO player_equipment
`); err != nil {
		return fmt.Errorf("rename table: %w", err)
	}

	// Create the unique index for the ON CONFLICT clause
	if _, err := tx.Exec(`CREATE UNIQUE INDEX idx_player_equipment_unique ON player_e
quipment(player_id, slot_type)`); err != nil {
		return fmt.Errorf("create unique index: %w", err)
	}

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

	// Count rows after
	var afterCount int
	db.QueryRow(`SELECT COUNT(*) FROM player_equipment`).Scan(&afterCount)

	fmt.Printf("[OK] player_equipment migrated: %d ? %d rows (removed %d historical s
napshots)\n",
		beforeCount, afterCount, beforeCount-afterCount)
	return nil
}

// migrateItemsAddItemEffect adds item_effect column to items table if missing
func migrateItemsAddItemEffect(db *sql.DB) error {
	// Check if column already exists
	rows, err := db.Query("PRAGMA table_info(items)")
	if err != nil {
		return nil // table doesn't exist yet
	}
	defer rows.Close()

	hasItemEffect := false
	for rows.Next() {
		var cid int
		var name, ctype string
		var notnull, pk int
		var dflt any
		if err := rows.Scan(&cid, &name, &ctype, &notnull, &dflt, &pk); err != nil {
			continue
		}
		if name == "item_effect" {
			hasItemEffect = true
			break
		}
	}

	if hasItemEffect {
		return nil
	}

	fmt.Printf("Adding item_effect column to items table...\n")
	_, err = db.Exec(`ALTER TABLE items ADD COLUMN item_effect TEXT`)
	if err != nil {
		return fmt.Errorf("add item_effect column: %w", err)
	}

	fmt.Printf("[OK] Added item_effect column to items table\n")
	return nil
}

// migrateItemsAddSpellDescription adds spell_description column to items table if
 missing
func migrateItemsAddSpellDescription(db *sql.DB) error {
	has, err := columnExists(db, "items", "spell_description")
	if err != nil {
		return nil // table doesn't exist yet
	}
	if has {
		return nil
	}

	fmt.Printf("Adding spell_description column to items table...\n")
	_, err = db.Exec(`ALTER TABLE items ADD COLUMN spell_description TEXT`)
	if err != nil {
		return fmt.Errorf("add spell_description column: %w", err)
	}

	fmt.Printf("[OK] Added spell_description column to items table\n")
	return nil
}

// migratePlayersAddAccountID adds account_id FK to players for the
// account-grouping feature. Populated by the process accounts step.
func migratePlayersAddAccountID(db *sql.DB) error {
	has, err := columnExists(db, "players", "account_id")
	if err != nil {
		return nil
	}
	if has {
		return nil
	}
	fmt.Printf("Adding account_id column to players table...\n")
	if _, err := db.Exec(`ALTER TABLE players ADD COLUMN account_id INTEGER REFERENCE
S accounts(id)`); err != nil {
		return fmt.Errorf("add account_id: %w", err)
	}
	fmt.Printf("[OK] Added account_id column to players table\n")
	return nil
}


package database

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

// migrateRealmsCompositeSlug upgrades the r
ealms table from slug-unique to (region,slug
)-unique if necessary.
func migrateRealmsCompositeSlug(db *sql.DB) 
error {
	// Inspect table SQL
	var createSQL string
	_ = db.QueryRow(`SELECT sql FROM sqlite_mas
ter WHERE type='table' AND name='realms'`).S
can(&createSQL)
	// If table already created without UNIQUE 
on slug, nothing to do
	if !strings.Contains(strings.ToLower(create
SQL), "slug text unique") {
		return nil
	}

	fmt.Printf("[MIGRATE] Upgrading realms tabl
e to composite (region,slug) uniqueness...\n
")
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	// Create new table without UNIQUE on slug
	if _, err := tx.Exec(`
        CREATE TABLE IF NOT EXISTS realms_ne
w (
            id INTEGER PRIMARY KEY,
            slug TEXT,
            name TEXT,
            region TEXT,
            connected_realm_id INTEGER UNIQU
E,
            parent_realm_slug TEXT
        )
    `); err != nil {
		return fmt.Errorf("create realms_new: %w",
 err)
	}

	// Copy data
	if _, err := tx.Exec(`INSERT INTO realms_ne
w (id, slug, name, region, connected_realm_i
d, parent_realm_slug)
                          SELECT id, slug, n
ame, region, connected_realm_id,
                                 CASE WHEN E
XISTS(SELECT 1 FROM sqlite_master WHERE type
='table' AND name='realms' AND sql LIKE '%pa
rent_realm_slug%')
                                      THEN p
arent_realm_slug ELSE NULL END
                          FROM realms`); err
 != nil {
		return fmt.Errorf("copy realms: %w", err)
	}

	// Rename old and new
	if _, err := tx.Exec(`ALTER TABLE realms RE
NAME TO realms_old`); err != nil {
		return fmt.Errorf("rename old: %w", err)
	}
	if _, err := tx.Exec(`ALTER TABLE realms_ne
w RENAME TO realms`); err != nil {
		return fmt.Errorf("rename new: %w", err)
	}

	// Drop old
	if _, err := tx.Exec(`DROP TABLE realms_old
`); err != nil {
		return fmt.Errorf("drop old: %w", err)
	}

	// Ensure composite unique index
	if _, err := tx.Exec(`CREATE UNIQUE INDEX I
F NOT EXISTS idx_realms_region_slug ON realm
s(region, slug)`); err != nil {
		return fmt.Errorf("create composite unique
 index: %w", err)
	}

	if err := tx.Commit(); err != nil {
		return err
	}
	fmt.Printf("[OK] Realms table migrated\n")
	return nil
}

// migrateSeasonsAddRegion adds region colum
n to seasons table if it doesn't exist
func migrateSeasonsAddRegion(db *sql.DB) err
or {
	// Check if region column already exists
	var createSQL string
	_ = db.QueryRow(`SELECT sql FROM sqlite_mas
ter WHERE type='table' AND name='seasons'`).
Scan(&createSQL)

	// If table already has region column, noth
ing to do
	if strings.Contains(strings.ToLower(createS
QL), "region text") {
		return nil
	}

	fmt.Printf("[MIGRATE] Adding region column 
to seasons table...\n")
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	// Create new table with region column
	if _, err := tx.Exec(`
        CREATE TABLE IF NOT EXISTS seasons_n
ew (
            id INTEGER PRIMARY KEY AUTOINCRE
MENT,
            season_number INTEGER NOT NULL,
            region TEXT NOT NULL,
            start_timestamp INTEGER,
            end_timestamp INTEGER,
            season_name TEXT,
            first_period_id INTEGER,
            last_period_id INTEGER,
            UNIQUE(season_number, region)
        )
    `); err != nil {
		return fmt.Errorf("create seasons_new: %w"
, err)
	}

	// Copy existing data, defaulting region to
 'us' for backward compatibility
	if _, err := tx.Exec(`
        INSERT INTO seasons_new (id, season_
number, region, start_timestamp, end_timesta
mp, season_name, first_period_id, last_perio
d_id)
        SELECT id, season_number, 'us', star
t_timestamp, end_timestamp, season_name, fir
st_period_id, last_period_id
        FROM seasons
    `); err != nil {
		return fmt.Errorf("copy seasons: %w", err)
	}

	// Rename old and new
	if _, err := tx.Exec(`ALTER TABLE seasons R
ENAME TO seasons_old`); err != nil {
		return fmt.Errorf("rename old: %w", err)
	}
	if _, err := tx.Exec(`ALTER TABLE seasons_n
ew RENAME TO seasons`); err != nil {
		return fmt.Errorf("rename new: %w", err)
	}

	// Drop old
	if _, err := tx.Exec(`DROP TABLE seasons_ol
d`); err != nil {
		return fmt.Errorf("drop old: %w", err)
	}

	if err := tx.Commit(); err != nil {
		return err
	}
	fmt.Printf("[OK] Seasons table migrated - a
dded region column\n")
	return nil
}

// migratePlayersIdentityColumns ensures ide
ntity + status metadata columns exist on pla
yers.
func migratePlayersIdentityColumns(db *sql.D
B) error {
	hasBlizzardID, err := columnExists(db, "pla
yers", "blizzard_character_id")
	if err != nil {
		return err
	}
	if !hasBlizzardID {
		if _, err := db.Exec(`ALTER TABLE players 
ADD COLUMN blizzard_character_id INTEGER`); 
err != nil {
			return fmt.Errorf("add blizzard_character
_id: %w", err)
		}
		if _, err := db.Exec(`UPDATE players SET b
lizzard_character_id = id WHERE blizzard_cha
racter_id IS NULL`); err != nil {
			return fmt.Errorf("backfill blizzard_char
acter_id: %w", err)
		}
	}

	hasIsValid, err := columnExists(db, "player
s", "is_valid")
	if err != nil {
		return err
	}
	if !hasIsValid {
		if _, err := db.Exec(`ALTER TABLE players 
ADD COLUMN is_valid INTEGER DEFAULT 1`); err
 != nil {
			return fmt.Errorf("add is_valid: %w", err
)
		}
		if _, err := db.Exec(`UPDATE players SET i
s_valid = 1 WHERE is_valid IS NULL`); err !=
 nil {
			return fmt.Errorf("backfill is_valid: %w"
, err)
		}
	}

	hasStatusChecked, err := columnExists(db, "
players", "status_checked_at")
	if err != nil {
		return err
	}
	if !hasStatusChecked {
		if _, err := db.Exec(`ALTER TABLE players 
ADD COLUMN status_checked_at INTEGER`); err 
!= nil {
			return fmt.Errorf("add status_checked_at:
 %w", err)
		}
	}

	hasFpAttempted, err := columnExists(db, "pl
ayers", "account_fp_attempted_at")
	if err != nil {
		return err
	}
	if !hasFpAttempted {
		if _, err := db.Exec(`ALTER TABLE players 
ADD COLUMN account_fp_attempted_at INTEGER`)
; err != nil {
			return fmt.Errorf("add account_fp_attempt
ed_at: %w", err)
		}
	}
	return nil
}

func columnExists(db *sql.DB, table, column 
string) (bool, error) {
	rows, err := db.Query(fmt.Sprintf(`PRAGMA t
able_info(%s)`, table))
	if err != nil {
		return false, err
	}
	defer rows.Close()

	for rows.Next() {
		var (
			cid        int
			name       string
			ctype      string
			notnull    int
			dfltValue  any
			primaryKey int
		)
		if err := rows.Scan(&cid, &name, &ctype, &
notnull, &dfltValue, &primaryKey); err != ni
l {
			return false, err
		}
		if strings.EqualFold(name, column) {
			return true, nil
		}
	}
	return false, rows.Err()
}

// migratePlayerRankingsPrimaryKey adds PRIM
ARY KEY constraint to player_rankings table 
if missing
func migratePlayerRankingsPrimaryKey(db *sql
.DB) error {
	// Check if table already has PRIMARY KEY b
y inspecting schema
	var createSQL string
	err := db.QueryRow(`SELECT sql FROM sqlite_
master WHERE type='table' AND name='player_r
ankings'`).Scan(&createSQL)
	if err != nil {
		// Table doesn't exist yet, skip migration
		return nil
	}

	// If PRIMARY KEY already exists, skip migr
ation
	if strings.Contains(strings.ToUpper(createS
QL), "PRIMARY KEY") {
		return nil
	}

	fmt.Printf("Migrating player_rankings table
 to add PRIMARY KEY constraint...\n")

	// Begin transaction for safe migration
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("begin transaction: %w",
 err)
	}
	defer tx.Rollback()

	// Create new table with PRIMARY KEY
	if _, err := tx.Exec(`
		CREATE TABLE player_rankings_new (
			player_id INTEGER NOT NULL,
			ranking_type TEXT NOT NULL,
			ranking_scope TEXT NOT NULL,
			ranking INTEGER,
			combined_best_time INTEGER,
			last_updated INTEGER,
			PRIMARY KEY (player_id, ranking_type, ran
king_scope)
		)
	`); err != nil {
		return fmt.Errorf("create new table: %w", 
err)
	}

	// Copy data, keeping only the most recent 
entry per (player_id, ranking_type, ranking_
scope)
	if _, err := tx.Exec(`
		INSERT INTO player_rankings_new
		SELECT
			player_id,
			ranking_type,
			ranking_scope,
			ranking,
			combined_best_time,
			last_updated
		FROM player_rankings
		WHERE rowid IN (
			SELECT MAX(rowid)
			FROM player_rankings
			GROUP BY player_id, ranking_type, ranking
_scope
		)
	`); err != nil {
		return fmt.Errorf("copy data: %w", err)
	}

	// Drop old table
	if _, err := tx.Exec(`DROP TABLE player_ran
kings`); err != nil {
		return fmt.Errorf("drop old table: %w", er
r)
	}

	// Rename new table
	if _, err := tx.Exec(`ALTER TABLE player_ra
nkings_new RENAME TO player_rankings`); err 
!= nil {
		return fmt.Errorf("rename new table: %w", 
err)
	}

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

	fmt.Printf("[OK] player_rankings table migr
ated - added PRIMARY KEY and removed duplica
tes\n")
	return nil
}

// migrateEquipmentToUpsert adds UNIQUE cons
traint on (player_id, slot_type) and removes
 historical snapshots
func migrateEquipmentToUpsert(db *sql.DB) er
ror {
	// Check if unique index already exists
	var indexCount int
	err := db.QueryRow(`
		SELECT COUNT(*) FROM sqlite_master
		WHERE type='index' AND name='idx_player_eq
uipment_unique'
	`).Scan(&indexCount)
	if err != nil {
		return fmt.Errorf("check index: %w", err)
	}

	if indexCount > 0 {
		// Already migrated
		return nil
	}

	fmt.Printf("Migrating player_equipment to s
ingle-row-per-slot model...\n")

	// Count rows before
	var beforeCount int
	db.QueryRow(`SELECT COUNT(*) FROM player_eq
uipment`).Scan(&beforeCount)

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

	// Create new table with UNIQUE constraint
	if _, err := tx.Exec(`
		CREATE TABLE player_equipment_new (
			id INTEGER PRIMARY KEY,
			player_id INTEGER,
			slot_type TEXT,
			item_id INTEGER,
			upgrade_id INTEGER,
			quality TEXT,
			item_name TEXT,
			snapshot_timestamp INTEGER,
			UNIQUE(player_id, slot_type)
		)
	`); err != nil {
		return fmt.Errorf("create new table: %w", 
err)
	}

	// Copy only one row per (player_id, slot_t
ype) - pick the one with highest ID
	if _, err := tx.Exec(`
		INSERT INTO player_equipment_new (id, play
er_id, slot_type, item_id, upgrade_id, quali
ty, item_name, snapshot_timestamp)
		SELECT pe.id, pe.player_id, pe.slot_type, 
pe.item_id, pe.upgrade_id, pe.quality, pe.it
em_name, pe.snapshot_timestamp
		FROM player_equipment pe
		INNER JOIN (
			SELECT MAX(id) as max_id
			FROM player_equipment
			GROUP BY player_id, slot_type
		) latest ON pe.id = latest.max_id
	`); err != nil {
		return fmt.Errorf("copy data: %w", err)
	}

	// Delete orphaned enchantments (those refe
rencing equipment IDs not in the new table)
	if _, err := tx.Exec(`
		DELETE FROM player_equipment_enchantments
		WHERE equipment_id NOT IN (SELECT id FROM 
player_equipment_new)
	`); err != nil {
		return fmt.Errorf("delete orphaned enchant
ments: %w", err)
	}

	// Drop old table and rename
	if _, err := tx.Exec(`DROP TABLE player_equ
ipment`); err != nil {
		return fmt.Errorf("drop old table: %w", er
r)
	}

	if _, err := tx.Exec(`ALTER TABLE player_eq
uipment_new RENAME TO player_equipment`); er
r != nil {
		return fmt.Errorf("rename table: %w", err)
	}

	// Create the unique index for the ON CONFL
ICT clause
	if _, err := tx.Exec(`CREATE UNIQUE INDEX i
dx_player_equipment_unique ON player_equipme
nt(player_id, slot_type)`); err != nil {
		return fmt.Errorf("create unique index: %w
", err)
	}

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

	// Count rows after
	var afterCount int
	db.QueryRow(`SELECT COUNT(*) FROM player_eq
uipment`).Scan(&afterCount)

	fmt.Printf("[OK] player_equipment migrated:
 %d ? %d rows (removed %d historical snapsho
ts)\n",
		beforeCount, afterCount, beforeCount-after
Count)
	return nil
}

// migrateItemsAddItemEffect adds item_effec
t column to items table if missing
func migrateItemsAddItemEffect(db *sql.DB) e
rror {
	// Check if column already exists
	rows, err := db.Query("PRAGMA table_info(it
ems)")
	if err != nil {
		return nil // table doesn't exist yet
	}
	defer rows.Close()

	hasItemEffect := false
	for rows.Next() {
		var cid int
		var name, ctype string
		var notnull, pk int
		var dflt any
		if err := rows.Scan(&cid, &name, &ctype, &
notnull, &dflt, &pk); err != nil {
			continue
		}
		if name == "item_effect" {
			hasItemEffect = true
			break
		}
	}

	if hasItemEffect {
		return nil
	}

	fmt.Printf("Adding item_effect column to it
ems table...\n")
	_, err = db.Exec(`ALTER TABLE items ADD COL
UMN item_effect TEXT`)
	if err != nil {
		return fmt.Errorf("add item_effect column:
 %w", err)
	}

	fmt.Printf("[OK] Added item_effect column t
o items table\n")
	return nil
}

// migrateItemsAddSpellDescription adds spel
l_description column to items table if missi
ng
func migrateItemsAddSpellDescription(db *sql
.DB) error {
	has, err := columnExists(db, "items", "spel
l_description")
	if err != nil {
		return nil // table doesn't exist yet
	}
	if has {
		return nil
	}

	fmt.Printf("Adding spell_description column
 to items table...\n")
	_, err = db.Exec(`ALTER TABLE items ADD COL
UMN spell_description TEXT`)
	if err != nil {
		return fmt.Errorf("add spell_description c
olumn: %w", err)
	}

	fmt.Printf("[OK] Added spell_description co
lumn to items table\n")
	return nil
}

// migratePlayersAddAccountID adds account_i
d FK to players for the
// account-grouping feature. Populated by th
e process accounts step.
func migratePlayersAddAccountID(db *sql.DB) 
error {
	has, err := columnExists(db, "players", "ac
count_id")
	if err != nil {
		return nil
	}
	if has {
		return nil
	}
	fmt.Printf("Adding account_id column to pla
yers table...\n")
	if _, err := db.Exec(`ALTER TABLE players A
DD COLUMN account_id INTEGER REFERENCES acco
unts(id)`); err != nil {
		return fmt.Errorf("add account_id: %w", er
r)
	}
	fmt.Printf("[OK] Added account_id column to
 players table\n")
	return nil
}

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