import { sql } from "drizzle-orm";
import {
  bigint,
  boolean,
  index,
  integer,
  jsonb,
  numeric,
  pgTable,
  primaryKey,
  text,
  timestamp,
  uniqueIndex,
  uuid,
  varchar,
} from "drizzle-orm/pg-core";

const ts = (name?: string) => (name ? timestamp(name, { withTimezone: true, mode: "date" }) : timestamp({ withTimezone: true, mode: "date" }));
const now = () => ts().notNull().defaultNow();

/* ------------------------------------------------------------------ users */

export const users = pgTable(
  "users",
  {
    id: uuid().primaryKey().defaultRandom(),
    username: varchar({ length: 24 }).notNull(),
    usernameNormalized: varchar({ length: 24 }).notNull(),
    passwordHash: text().notNull(),
    level: integer().notNull().default(1),
    xp: bigint({ mode: "number" }).notNull().default(0),
    status: varchar({ length: 16 }).notNull().default("active"), // active | suspended | deleted
    ageConfirmedAt: ts(),
    createdAt: now(),
    lastLoginAt: ts(),
    lastRescueAt: ts(),
    totalSpins: bigint({ mode: "number" }).notNull().default(0),
    gamesPlayed: integer().notNull().default(0),
    biggestWin: bigint({ mode: "number" }).notNull().default(0),
    biggestMultiplier: numeric({ precision: 12, scale: 2 }).notNull().default("0"),
  },
  (t) => [uniqueIndex("users_username_normalized_idx").on(t.usernameNormalized), uniqueIndex("users_username_idx").on(t.username)],
);

export const sessions = pgTable(
  "sessions",
  {
    id: uuid().primaryKey().defaultRandom(),
    userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),
    tokenHash: text().notNull(),
    createdAt: now(),
    expiresAt: ts().notNull(),
    lastSeenAt: now(),
    userAgent: text(),
    ip: varchar({ length: 64 }),
  },
  (t) => [uniqueIndex("sessions_token_hash_idx").on(t.tokenHash), index("sessions_user_idx").on(t.userId)],
);

export const recoveryCodes = pgTable("recovery_codes", {
  userId: uuid().primaryKey().references(() => users.id, { onDelete: "cascade" }),
  codeHash: text().notNull(),
  createdAt: now(),
  rotatedAt: ts(),
  usedAt: ts(),
  useCount: integer().notNull().default(0),
});

export const userSettings = pgTable("user_settings", {
  userId: uuid().primaryKey().references(() => users.id, { onDelete: "cascade" }),
  soundEnabled: boolean().notNull().default(true),
  musicVolume: numeric({ precision: 3, scale: 2 }).notNull().default("0.6"),
  effectsVolume: numeric({ precision: 3, scale: 2 }).notNull().default("0.8"),
  masterVolume: numeric({ precision: 3, scale: 2 }).notNull().default("0.8"),
  reduceMotion: boolean().notNull().default(false),
  animationIntensity: varchar({ length: 8 }).notNull().default("high"),
  sessionReminderMinutes: integer().notNull().default(60),
  breakReminder: boolean().notNull().default(true),
  leaderboardOptIn: boolean().notNull().default(true),
  updatedAt: now(),
});

/* ----------------------------------------------------------------- wallet */

export const wallets = pgTable("wallets", {
  userId: uuid().primaryKey().references(() => users.id, { onDelete: "cascade" }),
  balance: bigint({ mode: "number" }).notNull().default(0),
  lifetimeWagered: bigint({ mode: "number" }).notNull().default(0),
  lifetimeWon: bigint({ mode: "number" }).notNull().default(0),
  lifetimeGranted: bigint({ mode: "number" }).notNull().default(0),
  updatedAt: now(),
});

export const creditTransactions = pgTable(
  "credit_transactions",
  {
    id: uuid().primaryKey().defaultRandom(),
    userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),
    type: varchar({ length: 24 }).notNull(),
    amount: bigint({ mode: "number" }).notNull(), // signed
    balanceAfter: bigint({ mode: "number" }).notNull(),
    reference: text(), // round id, achievement key, mission key, admin note...
    meta: jsonb().$type<Record<string, unknown>>(),
    createdAt: now(),
  },
  (t) => [index("ctx_user_created_idx").on(t.userId, t.createdAt), index("ctx_type_idx").on(t.type), index("ctx_reference_idx").on(t.reference)],
);

/* ------------------------------------------------------------------ games */

export const games = pgTable(
  "games",
  {
    id: uuid().primaryKey().defaultRandom(),
    slug: varchar({ length: 64 }).notNull(),
    name: varchar({ length: 80 }).notNull(),
    version: varchar({ length: 16 }).notNull(),
    lifecycle: varchar({ length: 16 }).notNull().default("draft"), // draft | simulation | approved | staging | published | disabled
    sortOrder: integer().notNull().default(0),
    isFeatured: boolean().notNull().default(false),
    isNew: boolean().notNull().default(true),
    summary: jsonb().$type<Record<string, unknown>>().notNull(), // public metadata snapshot (theme, volatility, palette, features)
    createdAt: now(),
    publishedAt: ts(),
    updatedAt: now(),
  },
  (t) => [uniqueIndex("games_slug_idx").on(t.slug)],
);

export const gameVersions = pgTable(
  "game_versions",
  {
    id: uuid().primaryKey().defaultRandom(),
    gameId: uuid().notNull().references(() => games.id, { onDelete: "cascade" }),
    version: varchar({ length: 16 }).notNull(),
    definition: jsonb().$type<Record<string, unknown>>().notNull(),
    definitionHash: varchar({ length: 64 }).notNull(),
    certification: jsonb().$type<Record<string, unknown>>(),
    status: varchar({ length: 16 }).notNull().default("draft"),
    createdAt: now(),
  },
  (t) => [uniqueIndex("game_versions_game_version_idx").on(t.gameId, t.version)],
);

export const gameRounds = pgTable(
  "game_rounds",
  {
    id: uuid().primaryKey().defaultRandom(),
    roundId: varchar({ length: 32 }).notNull(),
    userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),
    gameId: uuid().notNull().references(() => games.id),
    gameSlug: varchar({ length: 64 }).notNull(),
    gameVersion: varchar({ length: 16 }).notNull(),
    clientRoundId: uuid().notNull(),
    bet: bigint({ mode: "number" }).notNull(),
    win: bigint({ mode: "number" }).notNull(),
    multiplier: numeric({ precision: 12, scale: 4 }).notNull(),
    balanceAfter: bigint({ mode: "number" }).notNull(),
    result: jsonb().$type<Record<string, unknown>>().notNull(),
    features: text().array().notNull().default(sql`'{}'::text[]`),
    freeSpins: boolean().notNull().default(false),
    bonus: boolean().notNull().default(false),
    jackpotTier: varchar({ length: 8 }),
    rngReference: text().notNull(),
    durationMs: integer(),
    createdAt: now(),
  },
  (t) => [
    uniqueIndex("game_rounds_round_id_idx").on(t.roundId),
    uniqueIndex("game_rounds_user_client_idx").on(t.userId, t.clientRoundId),
    index("game_rounds_user_created_idx").on(t.userId, t.createdAt),
    index("game_rounds_game_created_idx").on(t.gameId, t.createdAt),
    index("game_rounds_created_idx").on(t.createdAt),
    index("game_rounds_win_idx").on(t.win),
  ],
);

/** In-flight cash-out ("crash") rounds. Settled rounds are also written to game_rounds. */
export const crashRounds = pgTable(
  "crash_rounds",
  {
    id: uuid().primaryKey().defaultRandom(),
    roundId: varchar({ length: 32 }).notNull(),
    userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),
    gameId: uuid().notNull().references(() => games.id),
    gameSlug: varchar({ length: 64 }).notNull(),
    gameVersion: varchar({ length: 16 }).notNull(),
    clientRoundId: uuid().notNull(),
    bet: bigint({ mode: "number" }).notNull(),
    crashMultiplier: numeric({ precision: 12, scale: 2 }).notNull(),
    crashAtMs: integer().notNull(),
    seed: text().notNull(),
    commitment: varchar({ length: 64 }).notNull(),
    events: jsonb().$type<unknown[]>().notNull().default(sql`'[]'::jsonb`),
    autoCashout: numeric({ precision: 12, scale: 2 }),
    status: varchar({ length: 12 }).notNull().default("running"), // running | cashed | crashed
    cashoutMultiplier: numeric({ precision: 12, scale: 2 }),
    win: bigint({ mode: "number" }).notNull().default(0),
    startedAt: now(),
    settledAt: ts(),
  },
  (t) => [
    uniqueIndex("crash_rounds_round_id_idx").on(t.roundId),
    uniqueIndex("crash_rounds_user_client_idx").on(t.userId, t.clientRoundId),
    index("crash_rounds_user_status_idx").on(t.userId, t.status),
  ],
);

/** Ladder-game sessions (The Vault, Escape 99): step-by-step rounds with cash-out. Settled rounds also go to game_rounds. */
export const arcadeSessions = pgTable(
  "arcade_sessions",
  {
    id: uuid().primaryKey().defaultRandom(),
    roundId: varchar({ length: 32 }).notNull(),
    userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),
    gameId: uuid().notNull().references(() => games.id),
    gameSlug: varchar({ length: 64 }).notNull(),
    gameVersion: varchar({ length: 16 }).notNull(),
    clientRoundId: uuid().notNull(),
    bet: bigint({ mode: "number" }).notNull(),
    state: jsonb().$type<Record<string, unknown>>().notNull(),
    status: varchar({ length: 12 }).notNull().default("running"), // running | cashed | busted | completed
    win: bigint({ mode: "number" }).notNull().default(0),
    startedAt: now(),
    updatedAt: now(),
    settledAt: ts(),
  },
  (t) => [
    uniqueIndex("arcade_sessions_round_id_idx").on(t.roundId),
    uniqueIndex("arcade_sessions_user_client_idx").on(t.userId, t.clientRoundId),
    index("arcade_sessions_user_status_idx").on(t.userId, t.status),
  ],
);

export const gameStatistics = pgTable("game_statistics", {
  gameId: uuid().primaryKey().references(() => games.id, { onDelete: "cascade" }),
  launches: bigint({ mode: "number" }).notNull().default(0),
  spins: bigint({ mode: "number" }).notNull().default(0),
  wagered: bigint({ mode: "number" }).notNull().default(0),
  won: bigint({ mode: "number" }).notNull().default(0),
  wins: bigint({ mode: "number" }).notNull().default(0),
  bonuses: bigint({ mode: "number" }).notNull().default(0),
  freeSpins: bigint({ mode: "number" }).notNull().default(0),
  bigWins: bigint({ mode: "number" }).notNull().default(0),
  maxWin: bigint({ mode: "number" }).notNull().default(0),
  maxMultiplier: numeric({ precision: 12, scale: 2 }).notNull().default("0"),
  favorites: integer().notNull().default(0),
  updatedAt: now(),
});

export const gameStates = pgTable(
  "game_states",
  {
    userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),
    gameId: uuid().notNull().references(() => games.id, { onDelete: "cascade" }),
    state: jsonb().$type<Record<string, unknown>>().notNull(),
    updatedAt: now(),
  },
  (t) => [primaryKey({ columns: [t.userId, t.gameId] })],
);

export const userGameStats = pgTable(
  "user_game_stats",
  {
    userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),
    gameId: uuid().notNull().references(() => games.id, { onDelete: "cascade" }),
    spins: bigint({ mode: "number" }).notNull().default(0),
    wagered: bigint({ mode: "number" }).notNull().default(0),
    won: bigint({ mode: "number" }).notNull().default(0),
    bonuses: integer().notNull().default(0),
    biggestWin: bigint({ mode: "number" }).notNull().default(0),
    biggestMultiplier: numeric({ precision: 12, scale: 2 }).notNull().default("0"),
    lastPlayedAt: now(),
    firstPlayedAt: now(),
  },
  (t) => [primaryKey({ columns: [t.userId, t.gameId] }), index("ugs_user_last_idx").on(t.userId, t.lastPlayedAt)],
);

export const favorites = pgTable(
  "favorites",
  {
    userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),
    gameId: uuid().notNull().references(() => games.id, { onDelete: "cascade" }),
    createdAt: now(),
  },
  (t) => [primaryKey({ columns: [t.userId, t.gameId] })],
);

/* ------------------------------------------------------------ progression */

export const achievements = pgTable("achievements", {
  key: varchar({ length: 48 }).primaryKey(),
  name: varchar({ length: 80 }).notNull(),
  description: text().notNull(),
  category: varchar({ length: 24 }).notNull(),
  metric: varchar({ length: 32 }).notNull(), // spins | bonuses | games_played | big_wins | multiplier | wins | level | daily_streak
  target: bigint({ mode: "number" }).notNull(),
  rewardCredits: integer().notNull().default(0),
  rewardXp: integer().notNull().default(0),
  icon: varchar({ length: 32 }).notNull().default("star"),
  sortOrder: integer().notNull().default(0),
  enabled: boolean().notNull().default(true),
});

export const userAchievements = pgTable(
  "user_achievements",
  {
    userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),
    achievementKey: varchar({ length: 48 }).notNull().references(() => achievements.key, { onDelete: "cascade" }),
    unlockedAt: now(),
  },
  (t) => [primaryKey({ columns: [t.userId, t.achievementKey] })],
);

export const missions = pgTable("missions", {
  key: varchar({ length: 48 }).primaryKey(),
  name: varchar({ length: 80 }).notNull(),
  description: text().notNull(),
  period: varchar({ length: 8 }).notNull(), // daily | weekly
  metric: varchar({ length: 32 }).notNull(), // spins | wins | bonuses | games | multiplier | wagered
  target: bigint({ mode: "number" }).notNull(),
  rewardCredits: integer().notNull().default(0),
  rewardXp: integer().notNull().default(0),
  enabled: boolean().notNull().default(true),
  sortOrder: integer().notNull().default(0),
});

export const userMissions = pgTable(
  "user_missions",
  {
    id: uuid().primaryKey().defaultRandom(),
    userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),
    missionKey: varchar({ length: 48 }).notNull().references(() => missions.key, { onDelete: "cascade" }),
    periodKey: varchar({ length: 16 }).notNull(), // 2026-09-07 or 2026-W36
    progress: bigint({ mode: "number" }).notNull().default(0),
    /** For "games" metric: distinct game slugs played this period. */
    progressSet: text().array().notNull().default(sql`'{}'::text[]`),
    completedAt: ts(),
    claimedAt: ts(),
    expiresAt: ts().notNull(),
  },
  (t) => [uniqueIndex("user_missions_unique_idx").on(t.userId, t.missionKey, t.periodKey), index("user_missions_user_idx").on(t.userId)],
);

export const dailyRewards = pgTable("daily_rewards", {
  userId: uuid().primaryKey().references(() => users.id, { onDelete: "cascade" }),
  streakDay: integer().notNull().default(0),
  lastClaimedAt: ts(),
  nextAvailableAt: ts(),
  totalClaimed: bigint({ mode: "number" }).notNull().default(0),
  claims: integer().notNull().default(0),
});

export const playerLevels = pgTable("player_levels", {
  level: integer().primaryKey(),
  xpRequired: bigint({ mode: "number" }).notNull(),
  xpCumulative: bigint({ mode: "number" }).notNull(),
  rewardCredits: integer().notNull(),
  title: varchar({ length: 48 }).notNull(),
});

export const leaderboards = pgTable(
  "leaderboards",
  {
    id: uuid().primaryKey().defaultRandom(),
    category: varchar({ length: 32 }).notNull(), // biggest_win | biggest_multiplier | most_spins | highest_level
    periodKey: varchar({ length: 16 }).notNull(), // 2026-09-07 | 2026-W36 | all
    userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),
    value: numeric({ precision: 18, scale: 2 }).notNull(),
    gameSlug: varchar({ length: 64 }),
    roundId: varchar({ length: 32 }),
    updatedAt: now(),
  },
  (t) => [uniqueIndex("leaderboards_unique_idx").on(t.category, t.periodKey, t.userId), index("leaderboards_rank_idx").on(t.category, t.periodKey, t.value)],
);

/* ---------------------------------------------------------------- security */

export const securityEvents = pgTable(
  "security_events",
  {
    id: uuid().primaryKey().defaultRandom(),
    userId: uuid().references(() => users.id, { onDelete: "set null" }),
    adminId: uuid(),
    type: varchar({ length: 48 }).notNull(),
    severity: varchar({ length: 8 }).notNull().default("info"), // info | warn | high
    ip: varchar({ length: 64 }),
    userAgent: text(),
    meta: jsonb().$type<Record<string, unknown>>(),
    createdAt: now(),
  },
  (t) => [index("security_events_created_idx").on(t.createdAt), index("security_events_type_idx").on(t.type), index("security_events_user_idx").on(t.userId)],
);

export const adminUsers = pgTable(
  "admin_users",
  {
    id: uuid().primaryKey().defaultRandom(),
    username: varchar({ length: 32 }).notNull(),
    passwordHash: text().notNull(),
    totpSecret: text().notNull(), // base32, encrypted at rest with SESSION_SECRET
    role: varchar({ length: 16 }).notNull().default("admin"),
    createdAt: now(),
    lastLoginAt: ts(),
    disabled: boolean().notNull().default(false),
  },
  (t) => [uniqueIndex("admin_users_username_idx").on(t.username)],
);

export const adminSessions = pgTable(
  "admin_sessions",
  {
    id: uuid().primaryKey().defaultRandom(),
    adminId: uuid().notNull().references(() => adminUsers.id, { onDelete: "cascade" }),
    tokenHash: text().notNull(),
    createdAt: now(),
    expiresAt: ts().notNull(),
    ip: varchar({ length: 64 }),
  },
  (t) => [uniqueIndex("admin_sessions_token_idx").on(t.tokenHash)],
);

/* ------------------------------------------------------------ platform */

export const featureFlags = pgTable("feature_flags", {
  key: varchar({ length: 64 }).primaryKey(),
  enabled: boolean().notNull().default(true),
  description: text(),
  updatedAt: now(),
});

export const platformSettings = pgTable("platform_settings", {
  key: varchar({ length: 64 }).primaryKey(),
  value: jsonb().$type<unknown>().notNull(),
  updatedAt: now(),
});

export const simulationRuns = pgTable(
  "simulation_runs",
  {
    id: uuid().primaryKey().defaultRandom(),
    gameSlug: varchar({ length: 64 }).notNull(),
    gameVersion: varchar({ length: 16 }).notNull(),
    spins: bigint({ mode: "number" }).notNull(),
    status: varchar({ length: 16 }).notNull().default("running"), // running | done | failed
    progress: bigint({ mode: "number" }).notNull().default(0),
    result: jsonb().$type<Record<string, unknown>>(),
    error: text(),
    requestedBy: uuid(),
    createdAt: now(),
    finishedAt: ts(),
  },
  (t) => [index("simulation_runs_game_idx").on(t.gameSlug, t.createdAt)],
);