import { and, dailyRewards, eq, gameRounds, gameStates, gameStatistics, sql, userGameStats, userSettings, users, type Tx } from "@spinza/database";
import { classifyWin, type SpinResponse } from "@spinza/shared";
import type { LockedWallet } from "./wallet";
import { advanceMissions, awardXp, checkAchievements, spinXp, upsertLeaderboards, type UserCounters } from "./progression";

export interface SettleArgs {
  roundId: string;
  gameId: string;
  gameSlug: string;
  gameVersion: string;
  clientRoundId: string;
  bet: number;
  win: number;
  multiplier: number;
  result: Record<string, unknown>;
  features: string[];
  freeSpins: boolean;
  bonus: boolean;
  jackpotTier: string | null;
  rngReference: string;
  durationMs: number;
  /** Persistent per-game state (slots); omitted for crash/arcade games. */
  stateAfter?: Record<string, unknown>;
}

export interface SettleResult {
  xp: SpinResponse["xp"];
  unlocked: SpinResponse["unlocked"];
  winClass: SpinResponse["winClass"];
}

/**
 * Shared bookkeeping for any settled round (slot spin, crash cash-out, arcade
 * round): round row, per-user/per-game stats, global stats, XP + level rewards,
 * achievements, missions, leaderboards. Must run inside the transaction that
 * holds the wallet lock, AFTER the bet/win ledger entries have been applied.
 */
export async function settleRound(tx: Tx, wallet: LockedWallet, a: SettleArgs): Promise<SettleResult> {
  const userId = wallet.userId;
  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, userId))
    .for("update");

  const ugsExisting = await tx.query.userGameStats.findFirst({ where: and(eq(userGameStats.userId, userId), eq(userGameStats.gameId, a.gameId)), columns: { spins: true } });
  const newGame = !ugsExisting;
  const bonus = a.bonus || a.freeSpins;
  const cls = classifyWin(a.multiplier);
  const isBigWin = cls !== "none" && cls !== "regular" && cls !== "win";
  const facts = { gameSlug: a.gameSlug, bet: a.bet, win: a.win, multiplier: a.multiplier, bonus, jackpot: !!a.jackpotTier, newGame };

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

  const agg = await tx.execute(sql`select coalesce(sum(bonuses),0)::bigint as bonuses from user_game_stats where user_id = ${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 = ${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, userId), columns: { streakDay: true } });

  await tx.insert(gameRounds).values({
    roundId: a.roundId,
    userId,
    gameId: a.gameId,
    gameSlug: a.gameSlug,
    gameVersion: a.gameVersion,
    clientRoundId: a.clientRoundId,
    bet: a.bet,
    win: a.win,
    multiplier: a.multiplier.toFixed(4),
    balanceAfter: wallet.balance,
    result: a.result,
    features: a.features,
    freeSpins: a.freeSpins,
    bonus: a.bonus,
    jackpotTier: a.jackpotTier,
    rngReference: a.rngReference,
    durationMs: a.durationMs,
  });

  if (a.stateAfter) {
    await tx
      .insert(gameStates)
      .values({ userId, gameId: a.gameId, state: a.stateAfter })
      .onConflictDoUpdate({ target: [gameStates.userId, gameStates.gameId], set: { state: a.stateAfter, updatedAt: new Date() } });
  }

  await tx
    .insert(userGameStats)
    .values({ userId, gameId: a.gameId, spins: 1, wagered: a.bet, won: a.win, bonuses: bonus ? 1 : 0, biggestWin: a.win, biggestMultiplier: a.multiplier.toFixed(2) })
    .onConflictDoUpdate({
      target: [userGameStats.userId, userGameStats.gameId],
      set: {
        spins: sql`${userGameStats.spins} + 1`,
        wagered: sql`${userGameStats.wagered} + ${a.bet}`,
        won: sql`${userGameStats.won} + ${a.win}`,
        bonuses: sql`${userGameStats.bonuses} + ${bonus ? 1 : 0}`,
        biggestWin: sql`greatest(${userGameStats.biggestWin}, ${a.win})`,
        biggestMultiplier: sql`greatest(${userGameStats.biggestMultiplier}, ${a.multiplier.toFixed(2)}::numeric)`,
        lastPlayedAt: new Date(),
      },
    });

  await tx
    .update(gameStatistics)
    .set({
      spins: sql`${gameStatistics.spins} + 1`,
      wagered: sql`${gameStatistics.wagered} + ${a.bet}`,
      won: sql`${gameStatistics.won} + ${a.win}`,
      wins: sql`${gameStatistics.wins} + ${a.win > 0 ? 1 : 0}`,
      bonuses: sql`${gameStatistics.bonuses} + ${a.bonus ? 1 : 0}`,
      freeSpins: sql`${gameStatistics.freeSpins} + ${a.freeSpins ? 1 : 0}`,
      bigWins: sql`${gameStatistics.bigWins} + ${isBigWin ? 1 : 0}`,
      maxWin: sql`greatest(${gameStatistics.maxWin}, ${a.win})`,
      maxMultiplier: sql`greatest(${gameStatistics.maxMultiplier}, ${a.multiplier.toFixed(2)}::numeric)`,
      updatedAt: new Date(),
    })
    .where(eq(gameStatistics.gameId, a.gameId));

  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) + (a.win > 0 ? 1 : 0),
    jackpots: Number(ba.jackpots) + (a.jackpotTier ? 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, a.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, userId));

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

  return {
    xp: { gained: xpGain, total: lvl.xp, level: lvl.level, leveledUp: lvl.leveledUp, levelReward: lvl.reward },
    unlocked: { achievements: ach.unlocked, missions: mis.completed },
    winClass: cls,
  };
}