import { and, db, eq, gameRounds, gameStates, gameStatistics, games, sql, userGameStats, userSettings, users, dailyRewards } from "@spinza/database";
import { runSpin, type PlayerGameState, type SpinOutcome } from "@spinza/game-core";
import { getGame } from "@spinza/games";
import { BET_LEVELS, classifyWin, type SpinResponse } from "@spinza/shared";
import { errors } from "../lib/errors";
import { newRoundId } from "../lib/crypto";
import { flag, maintenance } from "../lib/settings";
import { applyCredit, lockWallet, saveWallet } from "./wallet";
import { advanceMissions, awardXp, checkAchievements, spinXp, upsertLeaderboards, type UserCounters } from "./progression";
import { redis } from "../lib/redis";
import { PG_UNIQUE_VIOLATION, pgCode } from "../lib/pg";

export interface SpinInput {
  userId: string;
  slug: string;
  bet: number;
  clientRoundId: string;
}

/** Strip the pre-state from the stored result to keep rounds compact. */
function storableResult(out: SpinOutcome): Record<string, unknown> {
  const { stateBefore: _b, stateAfter: _a, rngReference: _r, ...rest } = out;
  return rest as unknown as Record<string, unknown>;
}

async function replay(userId: string, clientRoundId: string): Promise<SpinResponse | null> {
  const row = await db.query.gameRounds.findFirst({ where: and(eq(gameRounds.userId, userId), eq(gameRounds.clientRoundId, clientRoundId)) });
  if (!row) return null;
  const u = await db.query.users.findFirst({ where: eq(users.id, userId), columns: { xp: true, level: true } });
  return {
    roundId: row.roundId,
    game: row.gameSlug,
    version: row.gameVersion,
    bet: row.bet,
    win: row.win,
    multiplier: Number(row.multiplier),
    winClass: classifyWin(Number(row.multiplier)),
    balance: row.balanceAfter,
    xp: { gained: 0, total: u?.xp ?? 0, level: u?.level ?? 1, leveledUp: false, levelReward: 0 },
    result: row.result,
    unlocked: { achievements: [], missions: [] },
    replayed: true,
  };
}

export async function spin(input: SpinInput): Promise<SpinResponse> {
  const m = maintenance();
  if (m.enabled) throw errors.maintenance(m.message);
  const def = getGame(input.slug);
  if (!def) throw errors.notFound("Unknown game");
  if (!flag(`game.${def.slug}.enabled`)) throw errors.unavailable("This game is temporarily unavailable.");
  if (!(BET_LEVELS as readonly number[]).includes(input.bet) || input.bet < def.minBet || input.bet > def.maxBet) throw errors.badRequest("Invalid bet for this game.");

  const game = await db.query.games.findFirst({ where: eq(games.slug, def.slug) });
  if (!game || game.lifecycle !== "published") throw errors.unavailable("This game is not published.");
  if (game.version !== def.version) throw errors.unavailable("Game version mismatch — please reload.");

  // Fast idempotency path.
  const existing = await replay(input.userId, input.clientRoundId);
  if (existing) return existing;

  const started = Date.now();
  try {
    return await db.transaction(async (tx) => {
      const wallet = await lockWallet(tx, input.userId);
      if (wallet.balance < input.bet) throw errors.insufficient(wallet.balance, input.bet);

      const stateRow = await tx.execute(sql`select state from game_states where user_id = ${input.userId} and game_id = ${game.id} for update`);
      const state = ((stateRow.rows[0] as { state?: PlayerGameState } | undefined)?.state ?? undefined) as PlayerGameState | undefined;

      const outcome = runSpin(def, input.bet, { state });
      const roundId = newRoundId();

      await applyCredit(tx, wallet, "BET", -input.bet, roundId, { game: def.slug });
      if (outcome.totalWin > 0) await applyCredit(tx, wallet, "WIN", outcome.totalWin, roundId, { game: def.slug, multiplier: outcome.multiplier });

      // User counters.
      const [u] = await tx
        .select({
          xp: users.xp,
          level: users.level,
          totalSpins: users.totalSpins,
          gamesPlayed: users.gamesPlayed,
          biggestWin: users.biggestWin,
          biggestMultiplier: users.biggestMultiplier,
        })
        .from(users)
        .where(eq(users.id, input.userId))
        .for("update");

      const ugsExisting = await tx.query.userGameStats.findFirst({ where: and(eq(userGameStats.userId, input.userId), eq(userGameStats.gameId, game.id)) });
      const newGame = !ugsExisting;
      const bonus = outcome.bonusTriggered || outcome.freeSpinsTriggered;
      const cls = classifyWin(outcome.multiplier);
      const isBigWin = cls !== "none" && cls !== "regular" && cls !== "win";

      const facts = { gameSlug: def.slug, bet: input.bet, win: outcome.totalWin, multiplier: outcome.multiplier, bonus, jackpot: !!outcome.jackpot, newGame };

      const totalSpins = u.totalSpins + 1;
      const gamesPlayed = u.gamesPlayed + (newGame ? 1 : 0);
      const biggestWin = Math.max(u.biggestWin, outcome.totalWin);
      const biggestMultiplier = Math.max(Number(u.biggestMultiplier), outcome.multiplier);

      // Aggregate counters for achievements (bonuses / big wins / wins / jackpots from ledger-independent stats).
      const agg = await tx.execute(sql`
        select coalesce(sum(bonuses),0)::bigint as bonuses from user_game_stats where user_id = ${input.userId}`);
      const bonusesBefore = Number((agg.rows[0] as { bonuses: number }).bonuses);
      const bigAgg = await tx.execute(sql`
        select count(*) filter (where multiplier >= 20)::bigint as big_wins,
               count(*) filter (where win > 0)::bigint as wins,
               count(*) filter (where jackpot_tier is not null)::bigint as jackpots
        from game_rounds where user_id = ${input.userId}`);
      const ba = bigAgg.rows[0] as { big_wins: number; wins: number; jackpots: number };
      const streak = await tx.query.dailyRewards.findFirst({ where: eq(dailyRewards.userId, input.userId), columns: { streakDay: true } });

      // Round row (unique on user+clientRoundId → duplicate protection).
      await tx.insert(gameRounds).values({
        roundId,
        userId: input.userId,
        gameId: game.id,
        gameSlug: def.slug,
        gameVersion: def.version,
        clientRoundId: input.clientRoundId,
        bet: input.bet,
        win: outcome.totalWin,
        multiplier: outcome.multiplier.toFixed(4),
        balanceAfter: wallet.balance,
        result: storableResult(outcome),
        features: outcome.features,
        freeSpins: outcome.freeSpinsTriggered,
        bonus: outcome.bonusTriggered,
        jackpotTier: outcome.jackpot?.tier ?? null,
        rngReference: outcome.rngReference,
        durationMs: Date.now() - started,
      });

      // Persistent game state.
      await tx
        .insert(gameStates)
        .values({ userId: input.userId, gameId: game.id, state: outcome.stateAfter as unknown as Record<string, unknown> })
        .onConflictDoUpdate({ target: [gameStates.userId, gameStates.gameId], set: { state: outcome.stateAfter as unknown as Record<string, unknown>, updatedAt: new Date() } });

      // Per-user per-game stats.
      await tx
        .insert(userGameStats)
        .values({
          userId: input.userId,
          gameId: game.id,
          spins: 1,
          wagered: input.bet,
          won: outcome.totalWin,
          bonuses: bonus ? 1 : 0,
          biggestWin: outcome.totalWin,
          biggestMultiplier: outcome.multiplier.toFixed(2),
        })
        .onConflictDoUpdate({
          target: [userGameStats.userId, userGameStats.gameId],
          set: {
            spins: sql`${userGameStats.spins} + 1`,
            wagered: sql`${userGameStats.wagered} + ${input.bet}`,
            won: sql`${userGameStats.won} + ${outcome.totalWin}`,
            bonuses: sql`${userGameStats.bonuses} + ${bonus ? 1 : 0}`,
            biggestWin: sql`greatest(${userGameStats.biggestWin}, ${outcome.totalWin})`,
            biggestMultiplier: sql`greatest(${userGameStats.biggestMultiplier}, ${outcome.multiplier.toFixed(2)}::numeric)`,
            lastPlayedAt: new Date(),
          },
        });

      // Global game statistics.
      await tx
        .update(gameStatistics)
        .set({
          spins: sql`${gameStatistics.spins} + 1`,
          wagered: sql`${gameStatistics.wagered} + ${input.bet}`,
          won: sql`${gameStatistics.won} + ${outcome.totalWin}`,
          wins: sql`${gameStatistics.wins} + ${outcome.totalWin > 0 ? 1 : 0}`,
          bonuses: sql`${gameStatistics.bonuses} + ${outcome.bonusTriggered ? 1 : 0}`,
          freeSpins: sql`${gameStatistics.freeSpins} + ${outcome.freeSpinsTriggered ? 1 : 0}`,
          bigWins: sql`${gameStatistics.bigWins} + ${isBigWin ? 1 : 0}`,
          maxWin: sql`greatest(${gameStatistics.maxWin}, ${outcome.totalWin})`,
          maxMultiplier: sql`greatest(${gameStatistics.maxMultiplier}, ${outcome.multiplier.toFixed(2)}::numeric)`,
          updatedAt: new Date(),
        })
        .where(eq(gameStatistics.gameId, game.id));

      // XP, achievements, missions.
      let xpGain = spinXp(facts);
      const counters: UserCounters = {
        totalSpins,
        gamesPlayed,
        biggestWin,
        biggestMultiplier,
        level: u.level,
        xp: u.xp,
        bonuses: bonusesBefore + (bonus ? 1 : 0),
        bigWins: Number(ba.big_wins) + (isBigWin ? 1 : 0),
        wins: Number(ba.wins) + (outcome.totalWin > 0 ? 1 : 0),
        jackpots: Number(ba.jackpots) + (outcome.jackpot ? 1 : 0),
        dailyStreak: streak?.streakDay ?? 0,
      };
      const ach = await checkAchievements(tx, wallet, counters);
      const mis = await advanceMissions(tx, wallet, facts);
      xpGain += ach.xp + mis.xp;
      const lvl = await awardXp(tx, wallet, { xp: u.xp, level: u.level }, xpGain, roundId);
      if (lvl.leveledUp) {
        counters.level = lvl.level;
        const ach2 = await checkAchievements(tx, wallet, counters);
        ach.unlocked.push(...ach2.unlocked);
      }

      await tx
        .update(users)
        .set({ totalSpins, gamesPlayed, biggestWin, biggestMultiplier: biggestMultiplier.toFixed(2) })
        .where(eq(users.id, input.userId));

      const settings = await tx.query.userSettings.findFirst({ where: eq(userSettings.userId, input.userId), columns: { leaderboardOptIn: true } });
      await upsertLeaderboards(tx, input.userId, facts, roundId, { totalSpins, level: lvl.level }, settings?.leaderboardOptIn ?? true);

      // Balance after the round (before rewards) is recorded on the round row; the returned balance includes rewards.
      await saveWallet(tx, wallet);

      // Live counters (best effort, outside the ledger).
      redis()
        .multi()
        .incr("stats:spins:today:" + new Date().toISOString().slice(0, 10))
        .pfadd("stats:players:today:" + new Date().toISOString().slice(0, 10), input.userId)
        .zadd("live:players", Date.now(), input.userId)
        .zadd(`live:game:${def.slug}`, Date.now(), input.userId)
        .exec()
        .catch(() => {});

      return {
        roundId,
        game: def.slug,
        version: def.version,
        bet: input.bet,
        win: outcome.totalWin,
        multiplier: outcome.multiplier,
        winClass: cls,
        balance: wallet.balance,
        xp: { gained: xpGain, total: lvl.xp, level: lvl.level, leveledUp: lvl.leveledUp, levelReward: lvl.reward },
        result: storableResult(outcome),
        unlocked: { achievements: ach.unlocked, missions: mis.completed },
      } satisfies SpinResponse;
    });
  } catch (e) {
    // Concurrent duplicate: the unique index on (user_id, client_round_id) fired → return the stored round.
    if (pgCode(e) === PG_UNIQUE_VIOLATION) {
      const again = await replay(input.userId, input.clientRoundId);
      if (again) return again;
    }
    throw e;
  }
}