// Package player provides player management for the Felt tournament engine.
// It handles CRUD operations, FTS5 typeahead search, duplicate merge, CSV
// import, tournament player operations (bust, undo, re-entry), and QR code
// generation. All monetary values use int64 cents. All mutations record audit
// entries and broadcast via WebSocket.
package player

import (
	"context"
	"crypto/rand"
	"database/sql"
	"encoding/csv"
	"encoding/json"
	"fmt"
	"io"
	"log"
	"strings"
	"time"
	"unicode/utf8"

	"github.com/felt-app/felt/internal/audit"
	"github.com/felt-app/felt/internal/financial"
	"github.com/felt-app/felt/internal/server/middleware"
	"github.com/felt-app/felt/internal/server/ws"
)

// CSV import safety limits.
const (
	MaxCSVRows     = 10_000
	MaxCSVColumns  = 20
	MaxCSVFieldLen = 1_000
)

// Errors returned by the player service.
var (
	ErrPlayerNotFound       = fmt.Errorf("player: not found")
	ErrPlayerAlreadyActive  = fmt.Errorf("player: already active in tournament")
	ErrPlayerNotActive      = fmt.Errorf("player: not active in tournament")
	ErrPlayerNotBusted      = fmt.Errorf("player: not busted")
	ErrTournamentFull       = fmt.Errorf("player: tournament is full")
	ErrTournamentNotFound   = fmt.Errorf("player: tournament not found")
	ErrHitmanRequired       = fmt.Errorf("player: hitman is required for PKO tournament")
	ErrCSVTooManyRows       = fmt.Errorf("player: CSV exceeds maximum of 10,000 rows")
	ErrCSVTooManyColumns    = fmt.Errorf("player: CSV exceeds maximum of 20 columns")
	ErrCSVFieldTooLong      = fmt.Errorf("player: CSV field exceeds maximum of 1,000 characters")
	ErrCSVMissingNameColumn = fmt.Errorf("player: CSV must have a 'name' column")
)

// Player represents a venue-level player record.
type Player struct {
	ID           string           `json:"id"`
	Name         string           `json:"name"`
	Nickname     *string          `json:"nickname,omitempty"`
	Email        *string          `json:"email,omitempty"`
	Phone        *string          `json:"phone,omitempty"`
	PhotoURL     *string          `json:"photo_url,omitempty"`
	Notes        *string          `json:"notes,omitempty"`
	CustomFields *json.RawMessage `json:"custom_fields,omitempty"`
	CreatedAt    int64            `json:"created_at"`
	UpdatedAt    int64            `json:"updated_at"`
}

// TournamentPlayer represents a player's state within a tournament.
type TournamentPlayer struct {
	ID                int64   `json:"id"`
	TournamentID      string  `json:"tournament_id"`
	PlayerID          string  `json:"player_id"`
	Status            string  `json:"status"`
	SeatTableID       *int    `json:"seat_table_id,omitempty"`
	SeatPosition      *int    `json:"seat_position,omitempty"`
	BuyInAt           *int64  `json:"buy_in_at,omitempty"`
	BustOutAt         *int64  `json:"bust_out_at,omitempty"`
	BustOutOrder      *int    `json:"bust_out_order,omitempty"`
	FinishingPosition *int    `json:"finishing_position,omitempty"`
	CurrentChips      int64   `json:"current_chips"`
	Rebuys            int     `json:"rebuys"`
	Addons            int     `json:"addons"`
	Reentries         int     `json:"reentries"`
	BountyValue       int64   `json:"bounty_value"`
	BountiesCollected int     `json:"bounties_collected"`
	PrizeAmount       int64   `json:"prize_amount"`
	PointsAwarded     int64   `json:"points_awarded"`
	HitmanPlayerID    *string `json:"hitman_player_id,omitempty"`
	CreatedAt         int64   `json:"created_at"`
	UpdatedAt         int64   `json:"updated_at"`
}

// TournamentPlayerDetail combines player info with tournament-specific data.
type TournamentPlayerDetail struct {
	PlayerID          string  `json:"player_id"`
	PlayerName        string  `json:"player_name"`
	PlayerNickname    *string `json:"player_nickname,omitempty"`
	Status            string  `json:"status"`
	SeatTableID       *int    `json:"seat_table_id,omitempty"`
	SeatPosition      *int    `json:"seat_position,omitempty"`
	SeatTableName     *string `json:"seat_table_name,omitempty"`
	BuyInAt           *int64  `json:"buy_in_at,omitempty"`
	BustOutAt         *int64  `json:"bust_out_at,omitempty"`
	BustOutOrder      *int    `json:"bust_out_order,omitempty"`
	FinishingPosition *int    `json:"finishing_position,omitempty"`
	CurrentChips      int64   `json:"current_chips"`
	Rebuys            int     `json:"rebuys"`
	Addons            int     `json:"addons"`
	Reentries         int     `json:"reentries"`
	BountyValue       int64   `json:"bounty_value"`
	BountiesCollected int     `json:"bounties_collected"`
	PrizeAmount       int64   `json:"prize_amount"`
	PointsAwarded     int64   `json:"points_awarded"`
	HitmanPlayerID    *string `json:"hitman_player_id,omitempty"`
	HitmanPlayerName  *string `json:"hitman_player_name,omitempty"`
	TotalInvestment   int64   `json:"total_investment"`
	NetResult         int64   `json:"net_result"`

	// Action history
	Transactions []financial.Transaction `json:"transactions,omitempty"`
}

// ImportResult holds the outcome of a CSV import.
type ImportResult struct {
	Created          int              `json:"created"`
	Duplicates       int              `json:"duplicates"`
	Errors           int              `json:"errors"`
	DuplicateDetails []DuplicateEntry `json:"duplicate_details,omitempty"`
	ErrorDetails     []string         `json:"error_details,omitempty"`
}

// DuplicateEntry describes a potential duplicate found during import.
type DuplicateEntry struct {
	Row            int    `json:"row"`
	ImportedName   string `json:"imported_name"`
	ExistingID     string `json:"existing_id"`
	ExistingName   string `json:"existing_name"`
}

// Service provides player management operations.
type Service struct {
	db        *sql.DB
	trail     *audit.Trail
	financial *financial.Engine
	hub       *ws.Hub
}

// NewService creates a new player service.
func NewService(db *sql.DB, trail *audit.Trail, fin *financial.Engine, hub *ws.Hub) *Service {
	return &Service{
		db:        db,
		trail:     trail,
		financial: fin,
		hub:       hub,
	}
}

// ---------- Player CRUD ----------

// CreatePlayer creates a new player in the venue database.
func (s *Service) CreatePlayer(ctx context.Context, p Player) (*Player, error) {
	p.ID = generateUUID()
	now := time.Now().Unix()
	p.CreatedAt = now
	p.UpdatedAt = now

	_, err := s.db.ExecContext(ctx,
		`INSERT INTO players (id, name, nickname, email, phone, photo_url, notes, custom_fields, created_at, updated_at)
		 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		p.ID, p.Name, p.Nickname, p.Email, p.Phone, p.PhotoURL, p.Notes,
		nullableJSON(p.CustomFields), p.CreatedAt, p.UpdatedAt,
	)
	if err != nil {
		return nil, fmt.Errorf("player: create: %w", err)
	}

	// Audit
	s.recordAudit(ctx, nil, "player.create", "player", p.ID, nil, &p)

	return &p, nil
}

// GetPlayer retrieves a player by ID.
func (s *Service) GetPlayer(ctx context.Context, id string) (*Player, error) {
	p := &Player{}
	var nickname, email, phone, photoURL, notes, customFields sql.NullString

	err := s.db.QueryRowContext(ctx,
		`SELECT id, name, nickname, email, phone, photo_url, notes, custom_fields, created_at, updated_at
		 FROM players WHERE id = ?`, id,
	).Scan(
		&p.ID, &p.Name, &nickname, &email, &phone, &photoURL, &notes, &customFields,
		&p.CreatedAt, &p.UpdatedAt,
	)
	if err == sql.ErrNoRows {
		return nil, ErrPlayerNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("player: get: %w", err)
	}

	p.Nickname = nullStringPtr(nickname)
	p.Email = nullStringPtr(email)
	p.Phone = nullStringPtr(phone)
	p.PhotoURL = nullStringPtr(photoURL)
	p.Notes = nullStringPtr(notes)
	if customFields.Valid {
		raw := json.RawMessage(customFields.String)
		p.CustomFields = &raw
	}

	return p, nil
}

// UpdatePlayer updates a player's fields.
func (s *Service) UpdatePlayer(ctx context.Context, p Player) error {
	p.UpdatedAt = time.Now().Unix()

	result, err := s.db.ExecContext(ctx,
		`UPDATE players SET name = ?, nickname = ?, email = ?, phone = ?, photo_url = ?,
		 notes = ?, custom_fields = ?, updated_at = ?
		 WHERE id = ?`,
		p.Name, p.Nickname, p.Email, p.Phone, p.PhotoURL, p.Notes,
		nullableJSON(p.CustomFields), p.UpdatedAt, p.ID,
	)
	if err != nil {
		return fmt.Errorf("player: update: %w", err)
	}
	n, _ := result.RowsAffected()
	if n == 0 {
		return ErrPlayerNotFound
	}

	s.recordAudit(ctx, nil, "player.update", "player", p.ID, nil, &p)

	return nil
}

// SearchPlayers searches players using FTS5 prefix matching for typeahead.
func (s *Service) SearchPlayers(ctx context.Context, query string, limit int) ([]Player, error) {
	if limit <= 0 {
		limit = 20
	}
	if limit > 100 {
		limit = 100
	}

	// If query is empty, return most recently updated players
	if strings.TrimSpace(query) == "" {
		return s.ListPlayers(ctx, limit, 0)
	}

	// Build FTS5 query with prefix matching
	terms := strings.Fields(query)
	var ftsTerms []string
	for _, t := range terms {
		// Escape special FTS5 characters and append * for prefix matching
		escaped := strings.ReplaceAll(t, `"`, `""`)
		ftsTerms = append(ftsTerms, `"`+escaped+`"*`)
	}
	ftsQuery := strings.Join(ftsTerms, " ")

	rows, err := s.db.QueryContext(ctx,
		`SELECT p.id, p.name, p.nickname, p.email, p.phone, p.photo_url, p.notes,
		        p.custom_fields, p.created_at, p.updated_at
		 FROM players p
		 JOIN players_fts f ON p.rowid = f.rowid
		 WHERE players_fts MATCH ?
		 ORDER BY rank
		 LIMIT ?`,
		ftsQuery, limit,
	)
	if err != nil {
		return nil, fmt.Errorf("player: search: %w", err)
	}
	defer rows.Close()

	return scanPlayers(rows)
}

// ListPlayers returns a paginated list of players.
func (s *Service) ListPlayers(ctx context.Context, limit, offset int) ([]Player, error) {
	if limit <= 0 {
		limit = 50
	}
	if limit > 200 {
		limit = 200
	}

	rows, err := s.db.QueryContext(ctx,
		`SELECT id, name, nickname, email, phone, photo_url, notes,
		        custom_fields, created_at, updated_at
		 FROM players
		 ORDER BY updated_at DESC
		 LIMIT ? OFFSET ?`,
		limit, offset,
	)
	if err != nil {
		return nil, fmt.Errorf("player: list: %w", err)
	}
	defer rows.Close()

	return scanPlayers(rows)
}

// MergePlayers merges mergeID into keepID. Destructive, requires admin role.
func (s *Service) MergePlayers(ctx context.Context, keepID, mergeID string) error {
	// Verify admin role
	role := middleware.OperatorRoleFromCtx(ctx)
	if role != middleware.RoleAdmin {
		return fmt.Errorf("player: merge requires admin role")
	}

	// Verify both players exist
	keepPlayer, err := s.GetPlayer(ctx, keepID)
	if err != nil {
		return fmt.Errorf("player: keep player: %w", err)
	}
	mergePlayer, err := s.GetPlayer(ctx, mergeID)
	if err != nil {
		return fmt.Errorf("player: merge player: %w", err)
	}

	// Move tournament_players from mergeID to keepID
	_, err = s.db.ExecContext(ctx,
		`UPDATE tournament_players SET player_id = ?, updated_at = unixepoch()
		 WHERE player_id = ?`,
		keepID, mergeID,
	)
	if err != nil {
		return fmt.Errorf("player: merge tournament_players: %w", err)
	}

	// Move transactions from mergeID to keepID
	_, err = s.db.ExecContext(ctx,
		`UPDATE transactions SET player_id = ? WHERE player_id = ?`,
		keepID, mergeID,
	)
	if err != nil {
		return fmt.Errorf("player: merge transactions: %w", err)
	}

	// Merge non-empty fields from mergePlayer into keepPlayer
	if keepPlayer.Nickname == nil && mergePlayer.Nickname != nil {
		keepPlayer.Nickname = mergePlayer.Nickname
	}
	if keepPlayer.Email == nil && mergePlayer.Email != nil {
		keepPlayer.Email = mergePlayer.Email
	}
	if keepPlayer.Phone == nil && mergePlayer.Phone != nil {
		keepPlayer.Phone = mergePlayer.Phone
	}
	if keepPlayer.PhotoURL == nil && mergePlayer.PhotoURL != nil {
		keepPlayer.PhotoURL = mergePlayer.PhotoURL
	}
	if keepPlayer.Notes == nil && mergePlayer.Notes != nil {
		keepPlayer.Notes = mergePlayer.Notes
	}

	// Update keep player with merged fields
	if err := s.UpdatePlayer(ctx, *keepPlayer); err != nil {
		return fmt.Errorf("player: merge update: %w", err)
	}

	// Delete merge player
	_, err = s.db.ExecContext(ctx, `DELETE FROM players WHERE id = ?`, mergeID)
	if err != nil {
		return fmt.Errorf("player: merge delete: %w", err)
	}

	// Audit
	mergeDetails := map[string]interface{}{
		"keep_id":  keepID,
		"merge_id": mergeID,
		"merged_name": mergePlayer.Name,
	}
	s.recordAudit(ctx, nil, "player.merge", "player", keepID, nil, mergeDetails)

	return nil
}

// ImportFromCSV imports players from a CSV reader.
// Returns an ImportResult with counts of created, duplicate, and error records.
func (s *Service) ImportFromCSV(ctx context.Context, reader io.Reader) (*ImportResult, error) {
	// Verify admin role
	role := middleware.OperatorRoleFromCtx(ctx)
	if role != middleware.RoleAdmin {
		return nil, fmt.Errorf("player: import requires admin role")
	}

	csvReader := csv.NewReader(reader)
	csvReader.TrimLeadingSpace = true

	// Read header
	header, err := csvReader.Read()
	if err != nil {
		return nil, fmt.Errorf("player: CSV read header: %w", err)
	}

	// Check column count
	if len(header) > MaxCSVColumns {
		return nil, ErrCSVTooManyColumns
	}

	// Map header to column indices
	colMap := make(map[string]int)
	for i, h := range header {
		colMap[strings.ToLower(strings.TrimSpace(h))] = i
	}

	if _, ok := colMap["name"]; !ok {
		return nil, ErrCSVMissingNameColumn
	}

	result := &ImportResult{}
	rowNum := 0

	for {
		record, err := csvReader.Read()
		if err == io.EOF {
			break
		}
		if err != nil {
			result.Errors++
			result.ErrorDetails = append(result.ErrorDetails, fmt.Sprintf("row %d: %v", rowNum+2, err))
			rowNum++
			continue
		}

		rowNum++
		if rowNum > MaxCSVRows {
			return nil, ErrCSVTooManyRows
		}

		// Check field lengths
		tooLong := false
		for _, field := range record {
			if utf8.RuneCountInString(field) > MaxCSVFieldLen {
				tooLong = true
				break
			}
		}
		if tooLong {
			return nil, ErrCSVFieldTooLong
		}

		// Extract fields from record
		name := getField(record, colMap, "name")
		if name == "" {
			result.Errors++
			result.ErrorDetails = append(result.ErrorDetails, fmt.Sprintf("row %d: name is required", rowNum+1))
			continue
		}

		// Check for duplicate by exact name match
		existing, _ := s.SearchPlayers(ctx, name, 5)
		var exactMatch *Player
		for i, p := range existing {
			if strings.EqualFold(p.Name, name) {
				exactMatch = &existing[i]
				break
			}
		}

		if exactMatch != nil {
			result.Duplicates++
			result.DuplicateDetails = append(result.DuplicateDetails, DuplicateEntry{
				Row:          rowNum + 1,
				ImportedName: name,
				ExistingID:   exactMatch.ID,
				ExistingName: exactMatch.Name,
			})
			continue
		}

		// Create new player
		p := Player{Name: name}
		if v := getField(record, colMap, "nickname"); v != "" {
			p.Nickname = &v
		}
		if v := getField(record, colMap, "email"); v != "" {
			p.Email = &v
		}
		if v := getField(record, colMap, "phone"); v != "" {
			p.Phone = &v
		}
		if v := getField(record, colMap, "notes"); v != "" {
			p.Notes = &v
		}

		if _, err := s.CreatePlayer(ctx, p); err != nil {
			result.Errors++
			result.ErrorDetails = append(result.ErrorDetails, fmt.Sprintf("row %d: %v", rowNum+1, err))
			continue
		}
		result.Created++
	}

	// Audit
	s.recordAudit(ctx, nil, "player.import", "players", "csv", nil, result)

	return result, nil
}

// ---------- Tournament Player Operations ----------

// RegisterPlayer registers a player for a tournament.
func (s *Service) RegisterPlayer(ctx context.Context, tournamentID, playerID string) (*TournamentPlayer, error) {
	// Check tournament exists and get max_players
	var maxPlayers sql.NullInt64
	err := s.db.QueryRowContext(ctx,
		`SELECT max_players FROM tournaments WHERE id = ?`, tournamentID,
	).Scan(&maxPlayers)
	if err == sql.ErrNoRows {
		return nil, ErrTournamentNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("player: check tournament: %w", err)
	}

	// Check tournament isn't full
	if maxPlayers.Valid {
		var count int
		err = s.db.QueryRowContext(ctx,
			`SELECT COUNT(*) FROM tournament_players WHERE tournament_id = ?`, tournamentID,
		).Scan(&count)
		if err != nil {
			return nil, fmt.Errorf("player: count players: %w", err)
		}
		if count >= int(maxPlayers.Int64) {
			return nil, ErrTournamentFull
		}
	}

	// Create tournament_players record
	now := time.Now().Unix()
	result, err := s.db.ExecContext(ctx,
		`INSERT INTO tournament_players (tournament_id, player_id, status, created_at, updated_at)
		 VALUES (?, ?, 'registered', ?, ?)`,
		tournamentID, playerID, now, now,
	)
	if err != nil {
		return nil, fmt.Errorf("player: register: %w", err)
	}

	id, _ := result.LastInsertId()
	tp := &TournamentPlayer{
		ID:           id,
		TournamentID: tournamentID,
		PlayerID:     playerID,
		Status:       "registered",
		CreatedAt:    now,
		UpdatedAt:    now,
	}

	s.recordAudit(ctx, &tournamentID, "player.register", "player", playerID, nil, tp)
	s.broadcast(tournamentID, "player.registered", tp)

	return tp, nil
}

// GetTournamentPlayers returns all players in a tournament with computed fields.
func (s *Service) GetTournamentPlayers(ctx context.Context, tournamentID string) ([]TournamentPlayerDetail, error) {
	rows, err := s.db.QueryContext(ctx,
		`SELECT tp.player_id, p.name, p.nickname, tp.status,
		        tp.seat_table_id, tp.seat_position, t.name,
		        tp.buy_in_at, tp.bust_out_at, tp.bust_out_order, tp.finishing_position,
		        tp.current_chips, tp.rebuys, tp.addons, tp.reentries,
		        tp.bounty_value, tp.bounties_collected, tp.prize_amount, tp.points_awarded,
		        tp.hitman_player_id, hp.name
		 FROM tournament_players tp
		 JOIN players p ON p.id = tp.player_id
		 LEFT JOIN tables t ON t.id = tp.seat_table_id
		 LEFT JOIN players hp ON hp.id = tp.hitman_player_id
		 WHERE tp.tournament_id = ?
		 ORDER BY
		   CASE tp.status
		     WHEN 'active' THEN 1
		     WHEN 'registered' THEN 2
		     WHEN 'busted' THEN 3
		     WHEN 'deal' THEN 4
		   END,
		   p.name`,
		tournamentID,
	)
	if err != nil {
		return nil, fmt.Errorf("player: list tournament players: %w", err)
	}
	defer rows.Close()

	var players []TournamentPlayerDetail
	for rows.Next() {
		var d TournamentPlayerDetail
		var nickname, tableName, hitmanID, hitmanName sql.NullString
		var tableID, seatPos sql.NullInt64
		var buyInAt, bustOutAt sql.NullInt64
		var bustOutOrder, finishingPos sql.NullInt64

		if err := rows.Scan(
			&d.PlayerID, &d.PlayerName, &nickname, &d.Status,
			&tableID, &seatPos, &tableName,
			&buyInAt, &bustOutAt, &bustOutOrder, &finishingPos,
			&d.CurrentChips, &d.Rebuys, &d.Addons, &d.Reentries,
			&d.BountyValue, &d.BountiesCollected, &d.PrizeAmount, &d.PointsAwarded,
			&hitmanID, &hitmanName,
		); err != nil {
			return nil, fmt.Errorf("player: scan tournament player: %w", err)
		}

		d.PlayerNickname = nullStringPtr(nickname)
		if tableID.Valid {
			v := int(tableID.Int64)
			d.SeatTableID = &v
		}
		if seatPos.Valid {
			v := int(seatPos.Int64)
			d.SeatPosition = &v
		}
		d.SeatTableName = nullStringPtr(tableName)
		if buyInAt.Valid {
			d.BuyInAt = &buyInAt.Int64
		}
		if bustOutAt.Valid {
			d.BustOutAt = &bustOutAt.Int64
		}
		if bustOutOrder.Valid {
			v := int(bustOutOrder.Int64)
			d.BustOutOrder = &v
		}
		if finishingPos.Valid {
			v := int(finishingPos.Int64)
			d.FinishingPosition = &v
		}
		d.HitmanPlayerID = nullStringPtr(hitmanID)
		d.HitmanPlayerName = nullStringPtr(hitmanName)

		// Compute total investment from non-undone transactions
		d.TotalInvestment = s.computeInvestment(ctx, tournamentID, d.PlayerID)
		d.NetResult = d.PrizeAmount - d.TotalInvestment

		players = append(players, d)
	}

	return players, rows.Err()
}

// GetTournamentPlayer returns a single player's full detail within a tournament.
func (s *Service) GetTournamentPlayer(ctx context.Context, tournamentID, playerID string) (*TournamentPlayerDetail, error) {
	var d TournamentPlayerDetail
	var nickname, tableName, hitmanID, hitmanName sql.NullString
	var tableID, seatPos sql.NullInt64
	var buyInAt, bustOutAt sql.NullInt64
	var bustOutOrder, finishingPos sql.NullInt64

	err := s.db.QueryRowContext(ctx,
		`SELECT tp.player_id, p.name, p.nickname, tp.status,
		        tp.seat_table_id, tp.seat_position, t.name,
		        tp.buy_in_at, tp.bust_out_at, tp.bust_out_order, tp.finishing_position,
		        tp.current_chips, tp.rebuys, tp.addons, tp.reentries,
		        tp.bounty_value, tp.bounties_collected, tp.prize_amount, tp.points_awarded,
		        tp.hitman_player_id, hp.name
		 FROM tournament_players tp
		 JOIN players p ON p.id = tp.player_id
		 LEFT JOIN tables t ON t.id = tp.seat_table_id
		 LEFT JOIN players hp ON hp.id = tp.hitman_player_id
		 WHERE tp.tournament_id = ? AND tp.player_id = ?`,
		tournamentID, playerID,
	).Scan(
		&d.PlayerID, &d.PlayerName, &nickname, &d.Status,
		&tableID, &seatPos, &tableName,
		&buyInAt, &bustOutAt, &bustOutOrder, &finishingPos,
		&d.CurrentChips, &d.Rebuys, &d.Addons, &d.Reentries,
		&d.BountyValue, &d.BountiesCollected, &d.PrizeAmount, &d.PointsAwarded,
		&hitmanID, &hitmanName,
	)
	if err == sql.ErrNoRows {
		return nil, ErrPlayerNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("player: get tournament player: %w", err)
	}

	d.PlayerNickname = nullStringPtr(nickname)
	if tableID.Valid {
		v := int(tableID.Int64)
		d.SeatTableID = &v
	}
	if seatPos.Valid {
		v := int(seatPos.Int64)
		d.SeatPosition = &v
	}
	d.SeatTableName = nullStringPtr(tableName)
	if buyInAt.Valid {
		d.BuyInAt = &buyInAt.Int64
	}
	if bustOutAt.Valid {
		d.BustOutAt = &bustOutAt.Int64
	}
	if bustOutOrder.Valid {
		v := int(bustOutOrder.Int64)
		d.BustOutOrder = &v
	}
	if finishingPos.Valid {
		v := int(finishingPos.Int64)
		d.FinishingPosition = &v
	}
	d.HitmanPlayerID = nullStringPtr(hitmanID)
	d.HitmanPlayerName = nullStringPtr(hitmanName)

	// Compute total investment
	d.TotalInvestment = s.computeInvestment(ctx, tournamentID, playerID)
	d.NetResult = d.PrizeAmount - d.TotalInvestment

	// Load transaction history
	txs, err := s.financial.GetPlayerTransactions(ctx, tournamentID, playerID)
	if err != nil {
		log.Printf("player: load transactions for %s: %v", playerID, err)
	} else {
		d.Transactions = txs
	}

	return &d, nil
}

// BustPlayer busts a player out of the tournament.
func (s *Service) BustPlayer(ctx context.Context, tournamentID, playerID string, hitmanPlayerID *string) error {
	// Check if PKO
	var isPKO int
	err := s.db.QueryRowContext(ctx,
		`SELECT is_pko FROM tournaments WHERE id = ?`, tournamentID,
	).Scan(&isPKO)
	if err == sql.ErrNoRows {
		return ErrTournamentNotFound
	}
	if err != nil {
		return fmt.Errorf("player: check tournament: %w", err)
	}

	if isPKO != 0 && (hitmanPlayerID == nil || *hitmanPlayerID == "") {
		return ErrHitmanRequired
	}

	// Validate player is active
	var currentStatus string
	var prevTableID, prevSeatPos sql.NullInt64
	var prevChips int64
	err = s.db.QueryRowContext(ctx,
		`SELECT status, seat_table_id, seat_position, current_chips
		 FROM tournament_players
		 WHERE tournament_id = ? AND player_id = ?`,
		tournamentID, playerID,
	).Scan(&currentStatus, &prevTableID, &prevSeatPos, &prevChips)
	if err == sql.ErrNoRows {
		return ErrPlayerNotFound
	}
	if err != nil {
		return fmt.Errorf("player: bust check: %w", err)
	}
	if currentStatus != "active" {
		return ErrPlayerNotActive
	}

	// Calculate bust_out_order
	var bustOrder int
	err = s.db.QueryRowContext(ctx,
		`SELECT COUNT(*) FROM tournament_players
		 WHERE tournament_id = ? AND status = 'busted'`,
		tournamentID,
	).Scan(&bustOrder)
	if err != nil {
		return fmt.Errorf("player: count busted: %w", err)
	}
	bustOrder++ // 1-based

	now := time.Now().Unix()

	// Update player status
	_, err = s.db.ExecContext(ctx,
		`UPDATE tournament_players SET
		   status = 'busted',
		   bust_out_at = ?,
		   bust_out_order = ?,
		   current_chips = 0,
		   seat_table_id = NULL,
		   seat_position = NULL,
		   hitman_player_id = ?,
		   updated_at = ?
		 WHERE tournament_id = ? AND player_id = ?`,
		now, bustOrder, hitmanPlayerID, now, tournamentID, playerID,
	)
	if err != nil {
		return fmt.Errorf("player: bust update: %w", err)
	}

	// PKO bounty transfer
	if hitmanPlayerID != nil && *hitmanPlayerID != "" {
		if err := s.financial.ProcessBountyTransfer(ctx, tournamentID, playerID, *hitmanPlayerID); err != nil {
			log.Printf("player: bounty transfer for bust %s: %v", playerID, err)
			// Non-fatal for non-PKO tournaments that don't have bounties
		}
	}

	// Audit with previous state for undo
	prevState := map[string]interface{}{
		"status":        currentStatus,
		"current_chips": prevChips,
	}
	if prevTableID.Valid {
		prevState["seat_table_id"] = prevTableID.Int64
	}
	if prevSeatPos.Valid {
		prevState["seat_position"] = prevSeatPos.Int64
	}
	newState := map[string]interface{}{
		"status":         "busted",
		"bust_out_at":    now,
		"bust_out_order": bustOrder,
		"hitman":         hitmanPlayerID,
	}
	s.recordAudit(ctx, &tournamentID, audit.ActionPlayerBust, "player", playerID, prevState, newState)

	// Broadcast
	s.broadcast(tournamentID, "player.busted", map[string]interface{}{
		"player_id":        playerID,
		"bust_out_order":   bustOrder,
		"hitman_player_id": hitmanPlayerID,
	})

	// Check if tournament should auto-close (1 player remaining)
	var activeCount int
	err = s.db.QueryRowContext(ctx,
		`SELECT COUNT(*) FROM tournament_players
		 WHERE tournament_id = ? AND status = 'active'`,
		tournamentID,
	).Scan(&activeCount)
	if err == nil && activeCount <= 1 {
		s.broadcast(tournamentID, "tournament.auto_close", map[string]interface{}{
			"remaining_players": activeCount,
		})
	}

	return nil
}

// UndoBust restores a busted player to active status with full re-ranking.
func (s *Service) UndoBust(ctx context.Context, tournamentID, playerID string) error {
	// Get player's current bust state
	var status string
	var bustOutAt, bustOutOrder sql.NullInt64
	var hitmanPlayerID sql.NullString
	err := s.db.QueryRowContext(ctx,
		`SELECT status, bust_out_at, bust_out_order, hitman_player_id
		 FROM tournament_players
		 WHERE tournament_id = ? AND player_id = ?`,
		tournamentID, playerID,
	).Scan(&status, &bustOutAt, &bustOutOrder, &hitmanPlayerID)
	if err == sql.ErrNoRows {
		return ErrPlayerNotFound
	}
	if err != nil {
		return fmt.Errorf("player: undo bust check: %w", err)
	}
	if status != "busted" {
		return ErrPlayerNotBusted
	}

	// Restore player to active
	now := time.Now().Unix()
	_, err = s.db.ExecContext(ctx,
		`UPDATE tournament_players SET
		   status = 'active',
		   bust_out_at = NULL,
		   bust_out_order = NULL,
		   finishing_position = NULL,
		   hitman_player_id = NULL,
		   updated_at = ?
		 WHERE tournament_id = ? AND player_id = ?`,
		now, tournamentID, playerID,
	)
	if err != nil {
		return fmt.Errorf("player: undo bust update: %w", err)
	}

	// Trigger full re-ranking
	rankingEngine := NewRankingEngine(s.db, s.hub)
	if err := rankingEngine.RecalculateAllRankings(ctx, tournamentID); err != nil {
		log.Printf("player: re-ranking after undo bust: %v", err)
	}

	// Audit
	prevState := map[string]interface{}{
		"status":         "busted",
		"bust_out_order": bustOutOrder.Int64,
	}
	newState := map[string]interface{}{
		"status": "active",
	}
	s.recordAudit(ctx, &tournamentID, "undo."+audit.ActionPlayerBust, "player", playerID, prevState, newState)

	// Broadcast
	s.broadcast(tournamentID, "player.undo_bust", map[string]interface{}{
		"player_id": playerID,
	})

	return nil
}

// ---------- Helpers ----------

func (s *Service) computeInvestment(ctx context.Context, tournamentID, playerID string) int64 {
	var total int64
	err := s.db.QueryRowContext(ctx,
		`SELECT COALESCE(SUM(amount), 0) FROM transactions
		 WHERE tournament_id = ? AND player_id = ?
		   AND type IN ('buyin', 'rebuy', 'addon', 'reentry')
		   AND undone = 0`,
		tournamentID, playerID,
	).Scan(&total)
	if err != nil {
		return 0
	}
	return total
}

func (s *Service) recordAudit(ctx context.Context, tournamentID *string, action, targetType, targetID string, prevState, newState interface{}) {
	if s.trail == nil {
		return
	}
	var prev, next json.RawMessage
	if prevState != nil {
		prev, _ = json.Marshal(prevState)
	}
	if newState != nil {
		next, _ = json.Marshal(newState)
	}
	_, err := s.trail.Record(ctx, audit.AuditEntry{
		TournamentID:  tournamentID,
		Action:        action,
		TargetType:    targetType,
		TargetID:      targetID,
		PreviousState: prev,
		NewState:      next,
	})
	if err != nil {
		log.Printf("player: audit record failed: %v", err)
	}
}

func (s *Service) broadcast(tournamentID, eventType string, data interface{}) {
	if s.hub == nil {
		return
	}
	payload, err := json.Marshal(data)
	if err != nil {
		log.Printf("player: broadcast marshal error: %v", err)
		return
	}
	s.hub.Broadcast(tournamentID, eventType, payload)
}

func scanPlayers(rows *sql.Rows) ([]Player, error) {
	var players []Player
	for rows.Next() {
		var p Player
		var nickname, email, phone, photoURL, notes, customFields sql.NullString

		if err := rows.Scan(
			&p.ID, &p.Name, &nickname, &email, &phone, &photoURL, &notes,
			&customFields, &p.CreatedAt, &p.UpdatedAt,
		); err != nil {
			return nil, fmt.Errorf("player: scan: %w", err)
		}

		p.Nickname = nullStringPtr(nickname)
		p.Email = nullStringPtr(email)
		p.Phone = nullStringPtr(phone)
		p.PhotoURL = nullStringPtr(photoURL)
		p.Notes = nullStringPtr(notes)
		if customFields.Valid {
			raw := json.RawMessage(customFields.String)
			p.CustomFields = &raw
		}

		players = append(players, p)
	}
	return players, rows.Err()
}

func getField(record []string, colMap map[string]int, column string) string {
	idx, ok := colMap[column]
	if !ok || idx >= len(record) {
		return ""
	}
	return strings.TrimSpace(record[idx])
}

func nullStringPtr(ns sql.NullString) *string {
	if ns.Valid {
		return &ns.String
	}
	return nil
}

func nullableJSON(data *json.RawMessage) sql.NullString {
	if data == nil || len(*data) == 0 || string(*data) == "null" {
		return sql.NullString{}
	}
	return sql.NullString{String: string(*data), Valid: true}
}

// generateUUID generates a v4 UUID.
func generateUUID() string {
	b := make([]byte, 16)
	_, _ = rand.Read(b)
	b[6] = (b[6] & 0x0f) | 0x40 // Version 4
	b[8] = (b[8] & 0x3f) | 0x80 // Variant 1
	return fmt.Sprintf("%08x-%04x-%04x-%04x-%012x",
		b[0:4], b[4:6], b[6:8], b[8:10], b[10:16])
}