package game

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"time"

	"github.com/go-jet/jet/v2/postgres"
	"github.com/go-jet/jet/v2/qrm"
	"github.com/google/uuid"

	"scrabble/backend/internal/engine"
	"scrabble/backend/internal/postgres/jet/backend/model"
	"scrabble/backend/internal/postgres/jet/backend/table"
)

// Store is the Postgres-backed query surface for games, seats, the move journal,
// complaints and per-account statistics.
type Store struct {
	db *sql.DB
}

// NewStore constructs a Store wrapping db.
func NewStore(db *sql.DB) *Store {
	return &Store{db: db}
}

// gameInsert carries the immutable fields of a new game.
type gameInsert struct {
	id              uuid.UUID
	variant         string
	dictVersion     string
	seed            int64
	players         int
	turnTimeoutSecs int
	hintsAllowed    bool
	hintsPerPlayer  int
	dropoutTiles    string
}

// statDelta is one account's contribution to its statistics on a game finish.
type statDelta struct {
	accountID  uuid.UUID
	wins       int
	losses     int
	draws      int
	gamePoints int
	wordPoints int
}

// commit is everything a single committed transition persists: the journal row,
// the post-move game cursor and per-seat scores, and — when the move ended the
// game — the finish stamp and the statistics deltas.
type commit struct {
	gameID       uuid.UUID
	seq          int
	seat         int
	action       string
	score        int
	runningTotal int
	exchanged    []string
	rec          engine.MoveRecord
	rackBefore   []string

	toMove        int
	turnStartedAt time.Time
	moveCount     int
	scores        []int
	now           time.Time

	finished   bool
	endReason  string
	finishedAt time.Time
	winner     int // -1 on a draw
	stats      []statDelta
}

// activeGame is the sweeper's view of an in-progress game's turn clock.
type activeGame struct {
	gameID          uuid.UUID
	toMove          int
	turnStartedAt   time.Time
	turnTimeoutSecs int
}

// CreateGame inserts the games row and one game_players row per seat (seat 0
// first) inside a single transaction.
func (s *Store) CreateGame(ctx context.Context, ins gameInsert, seats []uuid.UUID) error {
	return withTx(ctx, s.db, func(tx *sql.Tx) error {
		gi := table.Games.INSERT(
			table.Games.GameID, table.Games.Variant, table.Games.DictVersion, table.Games.Seed,
			table.Games.Players, table.Games.TurnTimeoutSecs, table.Games.HintsAllowed, table.Games.HintsPerPlayer,
			table.Games.DropoutTiles,
		).VALUES(ins.id, ins.variant, ins.dictVersion, ins.seed, ins.players, ins.turnTimeoutSecs, ins.hintsAllowed, ins.hintsPerPlayer, ins.dropoutTiles)
		if _, err := gi.ExecContext(ctx, tx); err != nil {
			return fmt.Errorf("insert game: %w", err)
		}
		for seat, accountID := range seats {
			pi := table.GamePlayers.INSERT(
				table.GamePlayers.GameID, table.GamePlayers.Seat, table.GamePlayers.AccountID,
			).VALUES(ins.id, seat, accountID)
			if _, err := pi.ExecContext(ctx, tx); err != nil {
				return fmt.Errorf("insert seat %d: %w", seat, err)
			}
		}
		return nil
	})
}

// GetGame loads the games row joined with its seats (ordered by seat), or
// ErrNotFound.
func (s *Store) GetGame(ctx context.Context, id uuid.UUID) (Game, error) {
	gstmt := postgres.SELECT(table.Games.AllColumns).
		FROM(table.Games).
		WHERE(table.Games.GameID.EQ(postgres.UUID(id))).
		LIMIT(1)
	var grow model.Games
	if err := gstmt.QueryContext(ctx, s.db, &grow); err != nil {
		if errors.Is(err, qrm.ErrNoRows) {
			return Game{}, ErrNotFound
		}
		return Game{}, fmt.Errorf("game: get %s: %w", id, err)
	}

	sstmt := postgres.SELECT(table.GamePlayers.AllColumns).
		FROM(table.GamePlayers).
		WHERE(table.GamePlayers.GameID.EQ(postgres.UUID(id))).
		ORDER_BY(table.GamePlayers.Seat.ASC())
	var srows []model.GamePlayers
	if err := sstmt.QueryContext(ctx, s.db, &srows); err != nil {
		return Game{}, fmt.Errorf("game: get seats %s: %w", id, err)
	}
	return projectGame(grow, srows)
}

// GetGameVariant reads just a game's variant — a cheap single-column lookup the edge uses
// to map wire alphabet indices to concrete letters (Stage 13) without loading the whole
// game and its seats.
func (s *Store) GetGameVariant(ctx context.Context, id uuid.UUID) (engine.Variant, error) {
	stmt := postgres.SELECT(table.Games.Variant).
		FROM(table.Games).
		WHERE(table.Games.GameID.EQ(postgres.UUID(id))).
		LIMIT(1)
	var row model.Games
	if err := stmt.QueryContext(ctx, s.db, &row); err != nil {
		if errors.Is(err, qrm.ErrNoRows) {
			return 0, ErrNotFound
		}
		return 0, fmt.Errorf("game: get variant %s: %w", id, err)
	}
	return engine.ParseVariant(row.Variant)
}

// SharedGameExists reports whether accounts a and b are both seated in at least
// one game (active or finished). It backs the social package's "befriend an
// opponent" gate via a self-join on game_players.
func (s *Store) SharedGameExists(ctx context.Context, a, b uuid.UUID) (bool, error) {
	other := table.GamePlayers.AS("other")
	stmt := postgres.SELECT(table.GamePlayers.GameID).
		FROM(table.GamePlayers.INNER_JOIN(other, other.GameID.EQ(table.GamePlayers.GameID))).
		WHERE(
			table.GamePlayers.AccountID.EQ(postgres.UUID(a)).
				AND(other.AccountID.EQ(postgres.UUID(b))),
		).LIMIT(1)
	var rows []model.GamePlayers
	if err := stmt.QueryContext(ctx, s.db, &rows); err != nil {
		return false, fmt.Errorf("game: shared game exists: %w", err)
	}
	return len(rows) > 0, nil
}

// ListGamesForAccount loads every game the account is seated in (active and
// finished), newest first, each joined with its ordered seats. It backs the lobby's
// "my games" lists.
func (s *Store) ListGamesForAccount(ctx context.Context, accountID uuid.UUID) ([]Game, error) {
	gstmt := postgres.SELECT(table.Games.AllColumns).
		FROM(table.Games.INNER_JOIN(table.GamePlayers, table.GamePlayers.GameID.EQ(table.Games.GameID))).
		WHERE(table.GamePlayers.AccountID.EQ(postgres.UUID(accountID))).
		ORDER_BY(table.Games.UpdatedAt.DESC())
	var grows []model.Games
	if err := gstmt.QueryContext(ctx, s.db, &grows); err != nil {
		return nil, fmt.Errorf("game: list for account: %w", err)
	}
	if len(grows) == 0 {
		return nil, nil
	}

	ids := make([]postgres.Expression, len(grows))
	for i, g := range grows {
		ids[i] = postgres.UUID(g.GameID)
	}
	sstmt := postgres.SELECT(table.GamePlayers.AllColumns).
		FROM(table.GamePlayers).
		WHERE(table.GamePlayers.GameID.IN(ids...)).
		ORDER_BY(table.GamePlayers.GameID.ASC(), table.GamePlayers.Seat.ASC())
	var srows []model.GamePlayers
	if err := sstmt.QueryContext(ctx, s.db, &srows); err != nil {
		return nil, fmt.Errorf("game: list seats for account: %w", err)
	}
	byGame := make(map[uuid.UUID][]model.GamePlayers, len(grows))
	for _, r := range srows {
		byGame[r.GameID] = append(byGame[r.GameID], r)
	}

	out := make([]Game, 0, len(grows))
	for _, g := range grows {
		pg, err := projectGame(g, byGame[g.GameID])
		if err != nil {
			return nil, err
		}
		out = append(out, pg)
	}
	return out, nil
}

// ListGames returns games for the admin games list, most-recently-updated first,
// paginated. status filters by lifecycle ("active"/"finished") when non-empty.
// The seats are not loaded — the list shows summaries; the detail view uses
// GetGame.
func (s *Store) ListGames(ctx context.Context, status string, limit, offset int) ([]Game, error) {
	where := postgres.Bool(true)
	if status != "" {
		where = table.Games.Status.EQ(postgres.String(status))
	}
	stmt := postgres.SELECT(table.Games.AllColumns).
		FROM(table.Games).
		WHERE(where).
		ORDER_BY(table.Games.UpdatedAt.DESC()).
		LIMIT(int64(limit)).
		OFFSET(int64(offset))
	var rows []model.Games
	if err := stmt.QueryContext(ctx, s.db, &rows); err != nil {
		return nil, fmt.Errorf("game: list games: %w", err)
	}
	out := make([]Game, 0, len(rows))
	for _, g := range rows {
		pg, err := projectGame(g, nil)
		if err != nil {
			return nil, err
		}
		out = append(out, pg)
	}
	return out, nil
}

// CountGames returns the number of games, optionally restricted to a status, for
// admin-list pagination.
func (s *Store) CountGames(ctx context.Context, status string) (int, error) {
	where := postgres.Bool(true)
	if status != "" {
		where = table.Games.Status.EQ(postgres.String(status))
	}
	stmt := postgres.SELECT(postgres.COUNT(table.Games.GameID).AS("count")).
		FROM(table.Games).
		WHERE(where)
	var dest struct{ Count int64 }
	if err := stmt.QueryContext(ctx, s.db, &dest); err != nil {
		return 0, fmt.Errorf("game: count games: %w", err)
	}
	return int(dest.Count), nil
}

// GetJournal loads the ordered, decoded move journal for a game.
func (s *Store) GetJournal(ctx context.Context, id uuid.UUID) ([]HistoryMove, error) {
	stmt := postgres.SELECT(table.GameMoves.AllColumns).
		FROM(table.GameMoves).
		WHERE(table.GameMoves.GameID.EQ(postgres.UUID(id))).
		ORDER_BY(table.GameMoves.Seq.ASC())
	var rows []model.GameMoves
	if err := stmt.QueryContext(ctx, s.db, &rows); err != nil {
		return nil, fmt.Errorf("game: get journal %s: %w", id, err)
	}
	out := make([]HistoryMove, 0, len(rows))
	for _, r := range rows {
		p, err := parsePayload(r.Payload)
		if err != nil {
			return nil, err
		}
		out = append(out, HistoryMove{
			Seq:          int(r.Seq),
			Seat:         int(r.Seat),
			Action:       r.Action,
			Score:        int(r.Score),
			RunningTotal: int(r.RunningTotal),
			Dir:          p.Dir,
			MainRow:      p.MainRow,
			MainCol:      p.MainCol,
			Tiles:        p.tileRecords(),
			Words:        p.Words,
			Exchanged:    p.Exchanged,
			Rack:         p.Rack,
		})
	}
	return out, nil
}

// CommitMove appends the move and applies the post-move game state — the turn
// cursor and per-seat scores, plus the finish stamp and statistics when the move
// ended the game — in one transaction.
func (s *Store) CommitMove(ctx context.Context, c commit) error {
	payload, err := buildPayload(c.rec, c.rackBefore, c.exchanged).marshal()
	if err != nil {
		return err
	}
	return withTx(ctx, s.db, func(tx *sql.Tx) error {
		mi := table.GameMoves.INSERT(
			table.GameMoves.GameID, table.GameMoves.Seq, table.GameMoves.Seat, table.GameMoves.Action,
			table.GameMoves.Score, table.GameMoves.RunningTotal, table.GameMoves.ExchangedCount, table.GameMoves.Payload,
		).VALUES(c.gameID, c.seq, c.seat, c.action, c.score, c.runningTotal, len(c.exchanged), payload)
		if _, err := mi.ExecContext(ctx, tx); err != nil {
			return fmt.Errorf("append move: %w", err)
		}

		if c.finished {
			gu := table.Games.UPDATE(
				table.Games.Status, table.Games.ToMove, table.Games.MoveCount,
				table.Games.EndReason, table.Games.UpdatedAt, table.Games.FinishedAt,
			).SET(
				postgres.String(StatusFinished), postgres.Int(int64(c.toMove)), postgres.Int(int64(c.moveCount)),
				postgres.String(c.endReason), postgres.TimestampzT(c.now), postgres.TimestampzT(c.finishedAt),
			).WHERE(table.Games.GameID.EQ(postgres.UUID(c.gameID)))
			if _, err := gu.ExecContext(ctx, tx); err != nil {
				return fmt.Errorf("finish game: %w", err)
			}
		} else {
			gu := table.Games.UPDATE(
				table.Games.ToMove, table.Games.TurnStartedAt, table.Games.MoveCount, table.Games.UpdatedAt,
			).SET(
				postgres.Int(int64(c.toMove)), postgres.TimestampzT(c.turnStartedAt), postgres.Int(int64(c.moveCount)), postgres.TimestampzT(c.now),
			).WHERE(table.Games.GameID.EQ(postgres.UUID(c.gameID)))
			if _, err := gu.ExecContext(ctx, tx); err != nil {
				return fmt.Errorf("advance game: %w", err)
			}
		}

		for seat, score := range c.scores {
			if err := updateSeatScore(ctx, tx, c.gameID, seat, score, c.finished, c.finished && seat == c.winner); err != nil {
				return fmt.Errorf("update seat %d: %w", seat, err)
			}
		}

		if c.finished {
			for _, d := range c.stats {
				if err := upsertStats(ctx, tx, d, c.now); err != nil {
					return err
				}
			}
		}
		return nil
	})
}

// updateSeatScore writes a seat's running score, also stamping is_winner when the
// game has finished.
func updateSeatScore(ctx context.Context, tx *sql.Tx, gameID uuid.UUID, seat, score int, finished, isWinner bool) error {
	where := table.GamePlayers.GameID.EQ(postgres.UUID(gameID)).
		AND(table.GamePlayers.Seat.EQ(postgres.Int(int64(seat))))
	var stmt postgres.UpdateStatement
	if finished {
		stmt = table.GamePlayers.
			UPDATE(table.GamePlayers.Score, table.GamePlayers.IsWinner).
			SET(postgres.Int(int64(score)), postgres.Bool(isWinner)).
			WHERE(where)
	} else {
		stmt = table.GamePlayers.
			UPDATE(table.GamePlayers.Score).
			SET(postgres.Int(int64(score))).
			WHERE(where)
	}
	_, err := stmt.ExecContext(ctx, tx)
	return err
}

// upsertStats folds one account's deltas into account_stats, locking the row for
// the read-modify-write so concurrent finishes accumulate correctly.
func upsertStats(ctx context.Context, tx *sql.Tx, d statDelta, now time.Time) error {
	ensure := table.AccountStats.
		INSERT(table.AccountStats.AccountID).
		VALUES(d.accountID).
		ON_CONFLICT(table.AccountStats.AccountID).
		DO_NOTHING()
	if _, err := ensure.ExecContext(ctx, tx); err != nil {
		return fmt.Errorf("ensure stats %s: %w", d.accountID, err)
	}

	sel := postgres.SELECT(table.AccountStats.AllColumns).
		FROM(table.AccountStats).
		WHERE(table.AccountStats.AccountID.EQ(postgres.UUID(d.accountID))).
		FOR(postgres.UPDATE())
	var row model.AccountStats
	if err := sel.QueryContext(ctx, tx, &row); err != nil {
		return fmt.Errorf("lock stats %s: %w", d.accountID, err)
	}

	wins := row.Wins + int32(d.wins)
	losses := row.Losses + int32(d.losses)
	draws := row.Draws + int32(d.draws)
	maxGame := max(row.MaxGamePoints, int32(d.gamePoints))
	maxWord := max(row.MaxWordPoints, int32(d.wordPoints))

	upd := table.AccountStats.UPDATE(
		table.AccountStats.Wins, table.AccountStats.Losses, table.AccountStats.Draws,
		table.AccountStats.MaxGamePoints, table.AccountStats.MaxWordPoints, table.AccountStats.UpdatedAt,
	).SET(
		postgres.Int(int64(wins)), postgres.Int(int64(losses)), postgres.Int(int64(draws)),
		postgres.Int(int64(maxGame)), postgres.Int(int64(maxWord)), postgres.TimestampzT(now),
	).WHERE(table.AccountStats.AccountID.EQ(postgres.UUID(d.accountID)))
	if _, err := upd.ExecContext(ctx, tx); err != nil {
		return fmt.Errorf("update stats %s: %w", d.accountID, err)
	}
	return nil
}

// SpendHintAllowance increments a seat's per-game hint counter by one.
func (s *Store) SpendHintAllowance(ctx context.Context, gameID uuid.UUID, seat int) error {
	stmt := table.GamePlayers.
		UPDATE(table.GamePlayers.HintsUsed).
		SET(table.GamePlayers.HintsUsed.ADD(postgres.Int(1))).
		WHERE(
			table.GamePlayers.GameID.EQ(postgres.UUID(gameID)).
				AND(table.GamePlayers.Seat.EQ(postgres.Int(int64(seat)))),
		)
	if _, err := stmt.ExecContext(ctx, s.db); err != nil {
		return fmt.Errorf("game: spend hint allowance: %w", err)
	}
	return nil
}

// FileComplaint persists a word-check complaint in status open and returns the
// stored row.
func (s *Store) FileComplaint(ctx context.Context, c Complaint) (Complaint, error) {
	id, err := uuid.NewV7()
	if err != nil {
		return Complaint{}, fmt.Errorf("game: new complaint id: %w", err)
	}
	stmt := table.Complaints.INSERT(
		table.Complaints.ComplaintID, table.Complaints.ComplainantID, table.Complaints.GameID,
		table.Complaints.Variant, table.Complaints.DictVersion, table.Complaints.Word,
		table.Complaints.WasValid, table.Complaints.Note,
	).VALUES(
		id, c.ComplainantID, c.GameID, c.Variant.String(), c.DictVersion, c.Word, c.WasValid, c.Note,
	).RETURNING(table.Complaints.AllColumns)
	var row model.Complaints
	if err := stmt.QueryContext(ctx, s.db, &row); err != nil {
		return Complaint{}, fmt.Errorf("game: file complaint: %w", err)
	}
	return projectComplaint(row)
}

// ListComplaints returns complaints for the admin review queue, newest first.
// status filters by lifecycle state when non-empty; limit and offset paginate.
func (s *Store) ListComplaints(ctx context.Context, status string, limit, offset int) ([]Complaint, error) {
	where := postgres.Bool(true)
	if status != "" {
		where = table.Complaints.Status.EQ(postgres.String(status))
	}
	stmt := postgres.SELECT(table.Complaints.AllColumns).
		FROM(table.Complaints).
		WHERE(where).
		ORDER_BY(table.Complaints.CreatedAt.DESC()).
		LIMIT(int64(limit)).
		OFFSET(int64(offset))
	var rows []model.Complaints
	if err := stmt.QueryContext(ctx, s.db, &rows); err != nil {
		return nil, fmt.Errorf("game: list complaints: %w", err)
	}
	return projectComplaints(rows)
}

// GetComplaint loads one complaint by id, or ErrNotFound.
func (s *Store) GetComplaint(ctx context.Context, id uuid.UUID) (Complaint, error) {
	stmt := postgres.SELECT(table.Complaints.AllColumns).
		FROM(table.Complaints).
		WHERE(table.Complaints.ComplaintID.EQ(postgres.UUID(id))).
		LIMIT(1)
	var row model.Complaints
	if err := stmt.QueryContext(ctx, s.db, &row); err != nil {
		if errors.Is(err, qrm.ErrNoRows) {
			return Complaint{}, ErrNotFound
		}
		return Complaint{}, fmt.Errorf("game: get complaint %s: %w", id, err)
	}
	return projectComplaint(row)
}

// ResolveComplaint closes a complaint with a disposition and note, stamping
// resolved_at, and returns the updated row (ErrNotFound when none matches). It
// leaves applied_in_version untouched.
func (s *Store) ResolveComplaint(ctx context.Context, id uuid.UUID, disposition, note string, now time.Time) (Complaint, error) {
	stmt := table.Complaints.UPDATE(
		table.Complaints.Status, table.Complaints.Disposition,
		table.Complaints.ResolutionNote, table.Complaints.ResolvedAt,
	).SET(
		postgres.String(StatusComplaintResolved), postgres.String(disposition),
		postgres.String(note), postgres.TimestampzT(now),
	).WHERE(table.Complaints.ComplaintID.EQ(postgres.UUID(id))).
		RETURNING(table.Complaints.AllColumns)
	var row model.Complaints
	if err := stmt.QueryContext(ctx, s.db, &row); err != nil {
		if errors.Is(err, qrm.ErrNoRows) {
			return Complaint{}, ErrNotFound
		}
		return Complaint{}, fmt.Errorf("game: resolve complaint %s: %w", id, err)
	}
	return projectComplaint(row)
}

// ListDictionaryChanges returns the resolved, accepted complaints not yet marked
// applied (the pending wordlist edits), ordered by variant then resolution time.
func (s *Store) ListDictionaryChanges(ctx context.Context) ([]Complaint, error) {
	stmt := postgres.SELECT(table.Complaints.AllColumns).
		FROM(table.Complaints).
		WHERE(
			table.Complaints.Status.EQ(postgres.String(StatusComplaintResolved)).
				AND(table.Complaints.Disposition.IN(
					postgres.String(DispositionAcceptAdd), postgres.String(DispositionAcceptRemove),
				)).
				AND(table.Complaints.AppliedInVersion.EQ(postgres.String(""))),
		).
		ORDER_BY(table.Complaints.Variant.ASC(), table.Complaints.ResolvedAt.ASC())
	var rows []model.Complaints
	if err := stmt.QueryContext(ctx, s.db, &rows); err != nil {
		return nil, fmt.Errorf("game: list dictionary changes: %w", err)
	}
	return projectComplaints(rows)
}

// MarkChangesApplied stamps every pending accepted change for variant with
// version (so it drops out of ListDictionaryChanges) and returns the count.
func (s *Store) MarkChangesApplied(ctx context.Context, variant, version string) (int64, error) {
	stmt := table.Complaints.UPDATE(table.Complaints.AppliedInVersion).
		SET(postgres.String(version)).
		WHERE(
			table.Complaints.Status.EQ(postgres.String(StatusComplaintResolved)).
				AND(table.Complaints.Variant.EQ(postgres.String(variant))).
				AND(table.Complaints.Disposition.IN(
					postgres.String(DispositionAcceptAdd), postgres.String(DispositionAcceptRemove),
				)).
				AND(table.Complaints.AppliedInVersion.EQ(postgres.String(""))),
		)
	res, err := stmt.ExecContext(ctx, s.db)
	if err != nil {
		return 0, fmt.Errorf("game: mark changes applied: %w", err)
	}
	n, _ := res.RowsAffected()
	return n, nil
}

// CountComplaints returns the number of complaints, optionally restricted to a
// status, for the admin queue pager and the dashboard counts.
func (s *Store) CountComplaints(ctx context.Context, status string) (int, error) {
	where := postgres.Bool(true)
	if status != "" {
		where = table.Complaints.Status.EQ(postgres.String(status))
	}
	stmt := postgres.SELECT(postgres.COUNT(table.Complaints.ComplaintID).AS("count")).
		FROM(table.Complaints).
		WHERE(where)
	var dest struct{ Count int64 }
	if err := stmt.QueryContext(ctx, s.db, &dest); err != nil {
		return 0, fmt.Errorf("game: count complaints: %w", err)
	}
	return int(dest.Count), nil
}

// ActiveGames returns the turn clocks of every in-progress game; the sweeper
// filters them against the per-move deadline and the player's away window.
func (s *Store) ActiveGames(ctx context.Context) ([]activeGame, error) {
	stmt := postgres.SELECT(
		table.Games.GameID, table.Games.ToMove, table.Games.TurnStartedAt, table.Games.TurnTimeoutSecs,
	).FROM(table.Games).
		WHERE(table.Games.Status.EQ(postgres.String(StatusActive))).
		ORDER_BY(table.Games.TurnStartedAt.ASC())
	var rows []model.Games
	if err := stmt.QueryContext(ctx, s.db, &rows); err != nil {
		return nil, fmt.Errorf("game: list active: %w", err)
	}
	out := make([]activeGame, 0, len(rows))
	for _, r := range rows {
		out = append(out, activeGame{
			gameID:          r.GameID,
			toMove:          int(r.ToMove),
			turnStartedAt:   r.TurnStartedAt,
			turnTimeoutSecs: int(r.TurnTimeoutSecs),
		})
	}
	return out, nil
}

// RobotTurns returns one row per active game seating any of the given accounts,
// for the robot scheduler. It joins games to game_players on the robot's seat and
// carries the game's turn cursor and bag seed; the driver filters these against
// each robot's per-game deadline. An empty id list returns no rows.
func (s *Store) RobotTurns(ctx context.Context, ids []uuid.UUID) ([]RobotTurn, error) {
	if len(ids) == 0 {
		return nil, nil
	}
	exprs := make([]postgres.Expression, len(ids))
	for i, id := range ids {
		exprs[i] = postgres.UUID(id)
	}
	stmt := postgres.SELECT(
		table.Games.GameID, table.Games.ToMove, table.Games.TurnStartedAt,
		table.Games.MoveCount, table.Games.Seed,
		table.GamePlayers.Seat, table.GamePlayers.AccountID,
	).FROM(
		table.Games.INNER_JOIN(table.GamePlayers, table.GamePlayers.GameID.EQ(table.Games.GameID)),
	).WHERE(
		table.Games.Status.EQ(postgres.String(StatusActive)).
			AND(table.GamePlayers.AccountID.IN(exprs...)),
	).ORDER_BY(table.Games.TurnStartedAt.ASC())

	var rows []struct {
		model.Games
		model.GamePlayers
	}
	if err := stmt.QueryContext(ctx, s.db, &rows); err != nil {
		return nil, fmt.Errorf("game: list robot turns: %w", err)
	}
	out := make([]RobotTurn, 0, len(rows))
	for _, r := range rows {
		out = append(out, RobotTurn{
			GameID:        r.Games.GameID,
			RobotID:       r.GamePlayers.AccountID,
			RobotSeat:     int(r.GamePlayers.Seat),
			ToMove:        int(r.Games.ToMove),
			TurnStartedAt: r.Games.TurnStartedAt,
			MoveCount:     int(r.Games.MoveCount),
			Seed:          r.Games.Seed,
		})
	}
	return out, nil
}

// GameSeed returns the bag seed a game was dealt from, used to replay it. The
// seed is server-only state and never travels in the public Game view.
func (s *Store) GameSeed(ctx context.Context, id uuid.UUID) (int64, error) {
	stmt := postgres.SELECT(table.Games.Seed).
		FROM(table.Games).
		WHERE(table.Games.GameID.EQ(postgres.UUID(id))).
		LIMIT(1)
	var row model.Games
	if err := stmt.QueryContext(ctx, s.db, &row); err != nil {
		if errors.Is(err, qrm.ErrNoRows) {
			return 0, ErrNotFound
		}
		return 0, fmt.Errorf("game: get seed %s: %w", id, err)
	}
	return row.Seed, nil
}

// projectGame builds a Game from a games row and its ordered seat rows.
func projectGame(g model.Games, seats []model.GamePlayers) (Game, error) {
	variant, err := engine.ParseVariant(g.Variant)
	if err != nil {
		return Game{}, fmt.Errorf("game: %s: %w", g.GameID, err)
	}
	dropout, err := engine.ParseDropoutTiles(g.DropoutTiles)
	if err != nil {
		return Game{}, fmt.Errorf("game: %s: %w", g.GameID, err)
	}
	out := Game{
		ID:             g.GameID,
		Variant:        variant,
		DictVersion:    g.DictVersion,
		Status:         g.Status,
		Players:        int(g.Players),
		ToMove:         int(g.ToMove),
		TurnStartedAt:  g.TurnStartedAt,
		TurnTimeout:    time.Duration(g.TurnTimeoutSecs) * time.Second,
		HintsAllowed:   g.HintsAllowed,
		HintsPerPlayer: int(g.HintsPerPlayer),
		DropoutTiles:   dropout,
		MoveCount:      int(g.MoveCount),
		CreatedAt:      g.CreatedAt,
		UpdatedAt:      g.UpdatedAt,
	}
	if g.EndReason != nil {
		out.EndReason = *g.EndReason
	}
	if g.FinishedAt != nil {
		t := *g.FinishedAt
		out.FinishedAt = &t
	}
	out.Seats = make([]Seat, 0, len(seats))
	for _, p := range seats {
		out.Seats = append(out.Seats, Seat{
			Seat:      int(p.Seat),
			AccountID: p.AccountID,
			Score:     int(p.Score),
			HintsUsed: int(p.HintsUsed),
			IsWinner:  p.IsWinner,
		})
	}
	return out, nil
}

// projectComplaint builds a Complaint from a stored row.
func projectComplaint(row model.Complaints) (Complaint, error) {
	variant, err := engine.ParseVariant(row.Variant)
	if err != nil {
		return Complaint{}, fmt.Errorf("game: complaint %s: %w", row.ComplaintID, err)
	}
	return Complaint{
		ID:               row.ComplaintID,
		ComplainantID:    row.ComplainantID,
		GameID:           row.GameID,
		Variant:          variant,
		DictVersion:      row.DictVersion,
		Word:             row.Word,
		WasValid:         row.WasValid,
		Note:             row.Note,
		Status:           row.Status,
		CreatedAt:        row.CreatedAt,
		Disposition:      row.Disposition,
		ResolutionNote:   row.ResolutionNote,
		ResolvedAt:       row.ResolvedAt,
		AppliedInVersion: row.AppliedInVersion,
	}, nil
}

// projectComplaints projects a slice of complaint rows, preserving order.
func projectComplaints(rows []model.Complaints) ([]Complaint, error) {
	out := make([]Complaint, 0, len(rows))
	for _, r := range rows {
		c, err := projectComplaint(r)
		if err != nil {
			return nil, err
		}
		out = append(out, c)
	}
	return out, nil
}

// withTx wraps fn in a transaction, committing on nil and rolling back on error.
func withTx(ctx context.Context, db *sql.DB, fn func(tx *sql.Tx) error) error {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("begin tx: %w", err)
	}
	if err := fn(tx); err != nil {
		_ = tx.Rollback()
		return err
	}
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit tx: %w", err)
	}
	return nil
}