import type { FastifyInstance } from "fastify";
import type { RouteContext } from "./types.js";

const parseNumber = (value: unknown, fallback: number): number => {
  const parsed = Number(value);
  return Number.isFinite(parsed) ? parsed : fallback;
};

const parsePage = (value: unknown) => Math.max(1, parseNumber(value, 1));
const parsePageSize = (value: unknown, fallback: number, max = 100) =>
  Math.max(1, Math.min(max, parseNumber(value, fallback)));

const toDateString = (value: unknown) => {
  if (value instanceof Date) return value.toISOString().slice(0, 10);
  if (typeof value === "string") return value.slice(0, 10);
  return "";
};

const getFirstNumber = (rows: unknown, key: string, fallback = 0): number => {
  if (!Array.isArray(rows) || rows.length === 0) return fallback;
  const value = (rows[0] as Record<string, unknown> | undefined)?.[key];
  const num = Number(value);
  return Number.isFinite(num) ? num : fallback;
};

export const registerAdminPanelRoutes = async (
  app: FastifyInstance,
  ctx: RouteContext,
): Promise<void> => {
  const { db, requireAdmin } = ctx;
  const calcChangePercent = (
    current: number,
    previous: number,
  ): number | null => {
    if (
      !Number.isFinite(current) ||
      !Number.isFinite(previous) ||
      previous <= 0
    )
      return null;
    return ((current - previous) / previous) * 100;
  };
  const onlineWindowMinutesFromCtx = Number(ctx.onlinePresenceWindowMs) / 60000;
  const onlineWindowMinutes = Math.max(
    1,
    Math.min(
      60,
      Math.round(
        Number.isFinite(onlineWindowMinutesFromCtx) &&
          onlineWindowMinutesFromCtx > 0
          ? onlineWindowMinutesFromCtx
          : parseNumber(process.env.ADMIN_ONLINE_WINDOW_MINUTES, 10),
      ),
    ),
  );

  const dbToolsEnabled =
    ctx.appEnv !== "production" ||
    process.env.ADMIN_DB_TOOLS_ENABLED === "true";

  const requireAdminDbTools = async (request: any, reply: any) => {
    await requireAdmin(request, reply);
    if (reply.sent) return;
    if (!dbToolsEnabled) {
      reply.code(403);
      return reply.send({
        error: "Fonctionnalité désactivée en production",
      });
    }
  };

  app.get(
    "/admin/stats",
    { preHandler: requireAdmin },
    async (_request, reply) => {
      try {
        const [avatarTodayRows] = await db.execute(
          `SELECT COALESCE(SUM(price_paid), 0) as total
           FROM user_avatar_packs
           WHERE DATE(purchased_at) = CURDATE()`,
        );
        const [ballTodayRows] = await db.execute(
          `SELECT COALESCE(SUM(price_paid), 0) as total
	           FROM user_ball_skin_packs
	           WHERE DATE(purchased_at) = CURDATE()`,
        );
        const [cashTodayRows] = await db.execute(
          `SELECT COALESCE(SUM(price_eur), 0) as total
	           FROM user_cash_pack_purchases
	           WHERE status = 'completed' AND DATE(processed_at) = CURDATE()`,
        );
        const [challengeTodayRows] = await db.execute(
          `SELECT COALESCE(SUM(price_paid), 0) as total
	           FROM user_challenge_pack
	           WHERE DATE(purchased_at) = CURDATE()`,
        );
        const [vipTodayRows] = await db.execute(
          `SELECT COALESCE(SUM(amount), 0) as total
	           FROM payment_transactions
	           WHERE pack_type = 'vip' AND status = 'completed'
	             AND DATE(updated_at) = CURDATE()`,
        );
        const [avatarYesterdayRows] = await db.execute(
          `SELECT COALESCE(SUM(price_paid), 0) as total
	           FROM user_avatar_packs
	           WHERE DATE(purchased_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)`,
        );
        const [ballYesterdayRows] = await db.execute(
          `SELECT COALESCE(SUM(price_paid), 0) as total
	           FROM user_ball_skin_packs
	           WHERE DATE(purchased_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)`,
        );
        const [cashYesterdayRows] = await db.execute(
          `SELECT COALESCE(SUM(price_eur), 0) as total
	           FROM user_cash_pack_purchases
	           WHERE status = 'completed' AND DATE(processed_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)`,
        );
        const [challengeYesterdayRows] = await db.execute(
          `SELECT COALESCE(SUM(price_paid), 0) as total
	           FROM user_challenge_pack
	           WHERE DATE(purchased_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)`,
        );
        const [vipYesterdayRows] = await db.execute(
          `SELECT COALESCE(SUM(amount), 0) as total
	           FROM payment_transactions
	           WHERE pack_type = 'vip' AND status = 'completed'
	             AND DATE(updated_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)`,
        );

        const todayRevenue =
          getFirstNumber(avatarTodayRows, "total", 0) +
          getFirstNumber(ballTodayRows, "total", 0);
        const cashToday = getFirstNumber(cashTodayRows, "total", 0);
        const challengeToday = getFirstNumber(challengeTodayRows, "total", 0);
        const vipToday = getFirstNumber(vipTodayRows, "total", 0);
        const todayRevenueAll =
          todayRevenue + cashToday + challengeToday + vipToday;

        const yesterdayRevenue =
          getFirstNumber(avatarYesterdayRows, "total", 0) +
          getFirstNumber(ballYesterdayRows, "total", 0);
        const cashYesterday = getFirstNumber(cashYesterdayRows, "total", 0);
        const challengeYesterday = getFirstNumber(
          challengeYesterdayRows,
          "total",
          0,
        );
        const vipYesterday = getFirstNumber(vipYesterdayRows, "total", 0);
        const yesterdayRevenueAll =
          yesterdayRevenue + cashYesterday + challengeYesterday + vipYesterday;

        const revenueChange = calcChangePercent(
          todayRevenueAll,
          yesterdayRevenueAll,
        );

        const [newUsersRows] = await db.execute(
          `SELECT COUNT(*) as count
           FROM users
           WHERE DATE(created_at) = CURDATE()`,
        );
        const [newUsersYesterdayRows] = await db.execute(
          `SELECT COUNT(*) as count
           FROM users
           WHERE DATE(created_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)`,
        );

        const newUsers = getFirstNumber(newUsersRows, "count", 0);
        const newUsersYesterday = getFirstNumber(
          newUsersYesterdayRows,
          "count",
          0,
        );
        const newUsersChange = calcChangePercent(newUsers, newUsersYesterday);

        let onlinePlayers = 0;
        let onlinePlayersChange: number | null = null;
        if (typeof ctx.getOnlineUserIds === "function") {
          const onlineUserIds = ctx
            .getOnlineUserIds()
            .filter((userId) => Number.isInteger(userId) && userId > 0);
          if (onlineUserIds.length > 0) {
            const placeholders = onlineUserIds.map(() => "?").join(",");
            const [onlinePlayersRows] = await db.execute(
              `SELECT COUNT(*) as count
               FROM users
               WHERE is_admin = 0
                 AND id IN (${placeholders})`,
              onlineUserIds,
            );
            onlinePlayers = getFirstNumber(onlinePlayersRows, "count", 0);
          }
        } else {
          const [onlinePlayersRows] = await db.execute(
            `SELECT COUNT(DISTINCT user_id) as count
             FROM leaderboard_entries
             WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? MINUTE)`,
            [onlineWindowMinutes],
          );
          const [onlinePlayersPrevRows] = await db.execute(
            `SELECT COUNT(DISTINCT user_id) as count
             FROM leaderboard_entries
             WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? MINUTE)
               AND created_at < DATE_SUB(NOW(), INTERVAL ? MINUTE)`,
            [onlineWindowMinutes * 2, onlineWindowMinutes],
          );
          onlinePlayers = getFirstNumber(onlinePlayersRows, "count", 0);
          const onlinePlayersPrev = getFirstNumber(onlinePlayersPrevRows, "count", 0);
          onlinePlayersChange = calcChangePercent(onlinePlayers, onlinePlayersPrev);
        }

        const [activePlayersRows] = await db.execute(
          `SELECT COUNT(DISTINCT user_id) as count
           FROM leaderboard_entries
           WHERE DATE(created_at) = CURDATE()`,
        );
        const [activePlayersYesterdayRows] = await db.execute(
          `SELECT COUNT(DISTINCT user_id) as count
           FROM leaderboard_entries
           WHERE DATE(created_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)`,
        );
        const activePlayers = getFirstNumber(activePlayersRows, "count", 0);
        const activePlayersYesterday = getFirstNumber(
          activePlayersYesterdayRows,
          "count",
          0,
        );
        const activePlayersChange = calcChangePercent(
          activePlayers,
          activePlayersYesterday,
        );

        const [gamesRows] = await db.execute(
          `SELECT COUNT(*) as count
           FROM leaderboard_entries
           WHERE DATE(created_at) = CURDATE()`,
        );
        const [gamesYesterdayRows] = await db.execute(
          `SELECT COUNT(*) as count
           FROM leaderboard_entries
           WHERE DATE(created_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)`,
        );

        const gamesPlayed = getFirstNumber(gamesRows, "count", 0);
        const gamesYesterday = getFirstNumber(gamesYesterdayRows, "count", 0);
        const gamesPlayedChange = calcChangePercent(
          gamesPlayed,
          gamesYesterday,
        );

        const [activityRows] = await db.execute(
          `SELECT
            DATE(created_at) as date,
            COUNT(DISTINCT user_id) as players,
            COUNT(*) as games
           FROM leaderboard_entries
           WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
           GROUP BY DATE(created_at)
           ORDER BY date ASC`,
        );

        const activityData = Array.isArray(activityRows)
          ? activityRows.map((row) => ({
              date: toDateString((row as { date?: string | Date }).date),
              players: Number((row as { players?: number }).players) || 0,
              games: Number((row as { games?: number }).games) || 0,
            }))
          : [];

        const [recentRows] = await db.execute(
          `SELECT
            u.display_name as playerName,
            'Partie terminée' as action,
            CONCAT('Completions: ', us.total_completions) as details,
            'success' as status,
            us.updated_at as timestamp
           FROM user_stats us
           JOIN users u ON us.user_id = u.id
           ORDER BY us.updated_at DESC
           LIMIT 10`,
        );

        return reply.send({
          onlinePlayers,
          onlinePlayersChange:
            onlinePlayersChange === null
              ? null
              : Math.round(onlinePlayersChange * 10) / 10,
          onlineWindowMinutes,
          activePlayers,
          activePlayersChange:
            activePlayersChange === null
              ? null
              : Math.round(activePlayersChange * 10) / 10,
          todayRevenue: Math.round(todayRevenueAll * 100) / 100,
          revenueChange:
            revenueChange === null ? null : Math.round(revenueChange * 10) / 10,
          newUsers,
          newUsersChange:
            newUsersChange === null
              ? null
              : Math.round(newUsersChange * 10) / 10,
          gamesPlayed,
          gamesPlayedChange:
            gamesPlayedChange === null
              ? null
              : Math.round(gamesPlayedChange * 10) / 10,
          activityData: {
            labels: activityData.map((item) => item.date),
            players: activityData.map((item) => item.players),
            games: activityData.map((item) => item.games),
          },
          recentActivity: Array.isArray(recentRows) ? recentRows : [],
        });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/stats");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  // Détails des joueurs actuellement en ligne (fenêtre de présence)
  app.get(
    "/admin/stats/online-players",
    { preHandler: requireAdmin },
    async (_request, reply) => {
      try {
        const toOnlinePlayer = (
          row: Record<string, unknown>,
          nowMs: number,
          lastSeenMs: number | null,
        ) => ({
          id: Number(row.id) || 0,
          display_name: String(row.display_name ?? ""),
          email: String(row.email ?? ""),
          created_at: row.created_at ?? null,
          last_game: row.last_game ?? null,
          last_seen_at: lastSeenMs ? new Date(lastSeenMs).toISOString() : null,
          last_seen_seconds_ago: lastSeenMs
            ? Math.max(0, Math.floor((nowMs - lastSeenMs) / 1000))
            : null,
        });

        if (
          typeof ctx.getOnlineUserIds === "function" &&
          typeof ctx.getUserLastSeen === "function"
        ) {
          const onlineUserIds = ctx
            .getOnlineUserIds()
            .filter((userId) => Number.isInteger(userId) && userId > 0)
            .slice(0, 200);
          if (onlineUserIds.length === 0) {
            return reply.send({ players: [], onlineWindowMinutes });
          }

          const placeholders = onlineUserIds.map(() => "?").join(",");
          const [rows] = await db.execute(
            `SELECT u.id, u.display_name, u.email, u.created_at,
                    MAX(le.created_at) as last_game
             FROM users u
             LEFT JOIN leaderboard_entries le ON le.user_id = u.id
             WHERE u.is_admin = 0
               AND u.id IN (${placeholders})
             GROUP BY u.id, u.display_name, u.email, u.created_at`,
            onlineUserIds,
          );

          const nowMs = Date.now();
          const players = (Array.isArray(rows) ? rows : [])
            .map((row) => {
              const item = row as Record<string, unknown>;
              const userId = Number(item.id) || 0;
              const lastSeenMs = userId > 0 ? ctx.getUserLastSeen!(userId) : null;
              return toOnlinePlayer(item, nowMs, lastSeenMs);
            })
            .filter((player) => player.last_seen_at !== null)
            .sort(
              (a, b) =>
                Number(a.last_seen_seconds_ago ?? Number.MAX_SAFE_INTEGER) -
                Number(b.last_seen_seconds_ago ?? Number.MAX_SAFE_INTEGER),
            )
            .slice(0, 50);

          return reply.send({ players, onlineWindowMinutes });
        }

        const [rows] = await db.execute(
          `SELECT u.id, u.display_name, u.email, u.created_at,
                  COUNT(le.id) as games_in_window,
                  MAX(le.created_at) as last_game
           FROM users u
           JOIN leaderboard_entries le ON le.user_id = u.id
           WHERE u.is_admin = 0
             AND le.created_at >= DATE_SUB(NOW(), INTERVAL ? MINUTE)
           GROUP BY u.id, u.display_name, u.email, u.created_at
           ORDER BY last_game DESC
           LIMIT 50`,
          [onlineWindowMinutes],
        );

        const nowMs = Date.now();
        const players = (Array.isArray(rows) ? rows : []).map((row) => {
          const item = row as Record<string, unknown>;
          const lastGame = item.last_game;
          const lastSeenMs =
            lastGame instanceof Date
              ? lastGame.getTime()
              : typeof lastGame === "string"
                ? new Date(lastGame).getTime()
                : Number.NaN;
          return {
            id: Number(item.id) || 0,
            display_name: String(item.display_name ?? ""),
            email: String(item.email ?? ""),
            created_at: item.created_at ?? null,
            games_in_window: Number(item.games_in_window) || 0,
            last_game: item.last_game ?? null,
            last_seen_at: Number.isFinite(lastSeenMs)
              ? new Date(lastSeenMs).toISOString()
              : null,
            last_seen_seconds_ago: Number.isFinite(lastSeenMs)
              ? Math.max(0, Math.floor((nowMs - lastSeenMs) / 1000))
              : null,
          };
        });

        return reply.send({ players, onlineWindowMinutes });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/stats/online-players");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  // Détails des joueurs actifs (ceux qui ont joué aujourd'hui)
  app.get(
    "/admin/stats/active-players",
    { preHandler: requireAdmin },
    async (_request, reply) => {
      try {
        const [rows] = await db.execute(
          `SELECT DISTINCT u.id, u.display_name, u.email, u.created_at,
                  COUNT(le.id) as games_today,
                  MAX(le.created_at) as last_game
           FROM users u
           JOIN leaderboard_entries le ON le.user_id = u.id
           WHERE DATE(le.created_at) = CURDATE()
           GROUP BY u.id, u.display_name, u.email, u.created_at
           ORDER BY games_today DESC
           LIMIT 50`,
        );
        return reply.send({ players: rows || [] });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/stats/active-players");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  // Détails des revenus du jour
  app.get(
    "/admin/stats/revenue",
    { preHandler: requireAdmin },
    async (_request, reply) => {
      try {
        const [avatarRows] = await db.execute(
          `SELECT u.id as user_id, u.display_name, u.email, ap.name as pack_name, 
	                  uap.price_paid, uap.purchased_at
	           FROM user_avatar_packs uap
	           JOIN users u ON u.id = uap.user_id
	           JOIN avatar_packs ap ON ap.pack_id = uap.pack_id
	           WHERE DATE(uap.purchased_at) = CURDATE()
	           ORDER BY uap.purchased_at DESC`,
        );
        const [ballRows] = await db.execute(
          `SELECT u.id as user_id, u.display_name, u.email, bsp.name as pack_name,
	                  ubsp.price_paid, ubsp.purchased_at
	           FROM user_ball_skin_packs ubsp
	           JOIN users u ON u.id = ubsp.user_id
	           JOIN ball_skin_packs bsp ON bsp.pack_id = ubsp.pack_id
	           WHERE DATE(ubsp.purchased_at) = CURDATE()
	           ORDER BY ubsp.purchased_at DESC`,
        );
        const [cashRows] = await db.execute(
          `SELECT u.id as user_id, u.display_name, u.email, ucpp.pack_name as pack_name,
	                  ucpp.price_eur as amount, ucpp.processed_at as created_at
	           FROM user_cash_pack_purchases ucpp
	           JOIN users u ON u.id = ucpp.user_id
	           WHERE ucpp.status = 'completed' AND DATE(ucpp.processed_at) = CURDATE()
	           ORDER BY ucpp.processed_at DESC`,
        );
        const [challengeRows] = await db.execute(
          `SELECT u.id as user_id, u.display_name, u.email, 'Pack Défis' as pack_name,
	                  ucp.price_paid as amount, ucp.purchased_at as created_at
	           FROM user_challenge_pack ucp
	           JOIN users u ON u.id = ucp.user_id
	           WHERE DATE(ucp.purchased_at) = CURDATE()
	           ORDER BY ucp.purchased_at DESC`,
        );
        const [vipRows] = await db.execute(
          `SELECT u.id as user_id, u.display_name, u.email, 'Pack VIP' as pack_name,
	                  pt.amount, pt.updated_at as created_at
	           FROM payment_transactions pt
	           JOIN users u ON u.id = pt.user_id
	           WHERE pt.pack_type = 'vip' AND pt.status = 'completed'
	             AND DATE(pt.updated_at) = CURDATE()
	           ORDER BY pt.updated_at DESC`,
        );

        const purchases = [
          ...(Array.isArray(avatarRows) ? avatarRows : []).map((r) => ({
            ...(r as object),
            type: "Avatar",
            product_name: (r as { pack_name?: string }).pack_name,
            amount: Number((r as { price_paid?: number }).price_paid) || 0,
            created_at: (r as { purchased_at?: string | Date }).purchased_at,
          })),
          ...(Array.isArray(ballRows) ? ballRows : []).map((r) => ({
            ...(r as object),
            type: "Skin Balle",
            product_name: (r as { pack_name?: string }).pack_name,
            amount: Number((r as { price_paid?: number }).price_paid) || 0,
            created_at: (r as { purchased_at?: string | Date }).purchased_at,
          })),
          ...(Array.isArray(cashRows) ? cashRows : []).map((r) => ({
            ...(r as object),
            type: "Cash Pack",
            product_name: (r as { pack_name?: string }).pack_name,
            amount: Number((r as { amount?: number }).amount) || 0,
            created_at: (r as { created_at?: string | Date }).created_at,
          })),
          ...(Array.isArray(challengeRows) ? challengeRows : []).map((r) => ({
            ...(r as object),
            type: "Challenge Pack",
            product_name: (r as { pack_name?: string }).pack_name,
            amount: Number((r as { amount?: number }).amount) || 0,
            created_at: (r as { created_at?: string | Date }).created_at,
          })),
          ...(Array.isArray(vipRows) ? vipRows : []).map((r) => ({
            ...(r as object),
            type: "VIP",
            product_name: (r as { pack_name?: string }).pack_name,
            amount: Number((r as { amount?: number }).amount) || 0,
            created_at: (r as { created_at?: string | Date }).created_at,
          })),
        ].sort(
          (a, b) =>
            new Date(
              (b as unknown as { created_at: string }).created_at,
            ).getTime() -
            new Date(
              (a as unknown as { created_at: string }).created_at,
            ).getTime(),
        );

        const totalRevenue = purchases.reduce(
          (sum, p) => sum + Number((p as { amount?: number }).amount || 0),
          0,
        );

        return reply.send({
          purchases,
          totalRevenue: Math.round(totalRevenue * 100) / 100,
        });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/stats/revenue");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  // Détails des nouvelles inscriptions
  app.get(
    "/admin/stats/new-users",
    { preHandler: requireAdmin },
    async (_request, reply) => {
      try {
        const [rows] = await db.execute(
          `SELECT u.id, u.display_name, u.email, u.created_at, u.guest,
                  COALESCE(w.points, 0) as points,
                  COALESCE(SUM(p.completed), 0) as levels_completed
           FROM users u
           LEFT JOIN wallets w ON w.user_id = u.id
           LEFT JOIN progress p ON p.user_id = u.id
           WHERE DATE(u.created_at) = CURDATE()
           GROUP BY u.id, u.display_name, u.email, u.created_at, u.guest, w.points
           ORDER BY u.created_at DESC`,
        );
        return reply.send({ users: rows || [] });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/stats/new-users");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  // Détails des parties jouées aujourd'hui
  app.get(
    "/admin/stats/games",
    { preHandler: requireAdmin },
    async (_request, reply) => {
      try {
        const [rows] = await db.execute(
          `SELECT le.id, u.display_name, u.email, le.score, le.time_ms, 
                  CONCAT(le.difficulty, ' - Niveau ', le.level_index + 1) as level_name, le.created_at
           FROM leaderboard_entries le
           JOIN users u ON u.id = le.user_id
           WHERE DATE(le.created_at) = CURDATE()
           ORDER BY le.created_at DESC
           LIMIT 100`,
        );
        return reply.send({ games: rows || [] });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/stats/games");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  app.get(
    "/admin/players",
    { preHandler: requireAdmin },
    async (request, reply) => {
      try {
        const query = request.query as Record<string, unknown>;
        const page = parsePage(query.page);
        const pageSize = parsePageSize(query.pageSize, 20, 100);
        const search = String(query.search ?? "").trim();
        const status = String(query.status ?? "").trim();
        const sort = String(query.sort ?? "created_desc").trim();

        const offset = (page - 1) * pageSize;

        const joins = `
          LEFT JOIN wallets w ON w.user_id = u.id
          LEFT JOIN progress p ON p.user_id = u.id
          LEFT JOIN user_stats us ON us.user_id = u.id
          LEFT JOIN (
            SELECT user_id, MAX(created_at) as last_login
            FROM audit_log
            WHERE action = 'auth.login'
            GROUP BY user_id
          ) al ON al.user_id = u.id
        `;

        let whereClause = "WHERE 1=1";
        const params: Array<string | number> = [];

        if (search) {
          whereClause +=
            " AND (u.display_name LIKE ? OR u.email LIKE ? OR u.id = ?)";
          params.push(`%${search}%`, `%${search}%`, Number(search) || 0);
        }

        // Le panel frontend utilise active/inactive/banned
        if (status === "active") {
          whereClause +=
            " AND (u.banned_at IS NULL OR (u.ban_until IS NOT NULL AND u.ban_until <= NOW())) AND u.guest = 0 AND al.last_login IS NOT NULL AND al.last_login >= DATE_SUB(NOW(), INTERVAL 30 DAY)";
        } else if (status === "inactive") {
          whereClause +=
            " AND (u.banned_at IS NULL OR (u.ban_until IS NOT NULL AND u.ban_until <= NOW())) AND (u.guest = 1 OR al.last_login IS NULL OR al.last_login < DATE_SUB(NOW(), INTERVAL 30 DAY))";
        } else if (status === "banned") {
          // Bans actifs uniquement (permanent ou pas encore expirés)
          whereClause +=
            " AND u.banned_at IS NOT NULL AND (u.ban_until IS NULL OR u.ban_until > NOW())";
        }

        let orderClause = "ORDER BY u.created_at DESC";
        if (sort === "created_asc") orderClause = "ORDER BY u.created_at ASC";
        if (sort === "level_desc") orderClause = "ORDER BY level DESC";
        if (sort === "coins_desc") orderClause = "ORDER BY coins DESC";

        const [countRows] = await db.execute(
          `SELECT COUNT(DISTINCT u.id) as total
           FROM users u
           ${joins}
           ${whereClause}`,
          params,
        );
        const total = Array.isArray(countRows)
          ? Number((countRows[0] as { total?: number }).total) || 0
          : 0;

        const [players] = await db.execute(
          `SELECT
            u.id as user_id,
            u.display_name as username,
            u.email,
            u.guest as is_guest,
            COALESCE(w.points, 0) as coins,
            COALESCE(SUM(p.completed), 0) as level,
            MAX(al.last_login) as last_login,
            u.banned_at,
            u.ban_reason,
            u.ban_until,
            CASE
              WHEN u.banned_at IS NOT NULL AND (u.ban_until IS NULL OR u.ban_until > NOW()) THEN 'banned'
              WHEN u.guest = 1 THEN 'inactive'
              WHEN MAX(al.last_login) IS NULL THEN 'inactive'
              WHEN MAX(al.last_login) >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 'active'
              ELSE 'inactive'
            END as status,
            u.created_at,
            MAX(us.total_completions) as completions,
            MAX(us.total_moves) as moves,
            MAX(us.updated_at) as last_activity
           FROM users u
           ${joins}
           ${whereClause}
           GROUP BY u.id, u.display_name, u.email, u.guest, w.points, u.created_at, u.banned_at, u.ban_reason, u.ban_until
           ${orderClause}
           LIMIT ${Math.trunc(pageSize)} OFFSET ${Math.trunc(offset)}`,
          params,
        );

        return reply.send({
          players: Array.isArray(players) ? players : [],
          total,
          page,
          pageSize,
          totalPages: Math.ceil(total / pageSize),
        });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/players");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  app.get(
    "/admin/players/:id",
    { preHandler: requireAdmin },
    async (request, reply) => {
      try {
        const { id } = request.params as { id: string };
        const userId = parseNumber(id, 0);
        if (!userId) {
          return reply.code(400).send({ error: "Invalid user id" });
        }

        // Requête principale sans GROUP BY problématique
        const [playerRows] = await db.execute(
          `SELECT
            u.id as user_id,
            u.display_name as username,
            u.email,
            u.guest as is_guest,
            u.created_at,
            u.banned_at,
            u.ban_reason,
            COALESCE(w.points, 0) as coins,
            us.total_completions,
            us.total_moves,
            us.total_time,
            us.updated_at as last_activity
           FROM users u
           LEFT JOIN wallets w ON w.user_id = u.id
           LEFT JOIN user_stats us ON us.user_id = u.id
           WHERE u.id = ?
           LIMIT 1`,
          [userId],
        );

        if (!Array.isArray(playerRows) || playerRows.length === 0) {
          return reply.code(404).send({ error: "Player not found" });
        }

        const player = playerRows[0] as {
          user_id: number;
          username?: string | null;
          email?: string | null;
          is_guest?: number | boolean | null;
          created_at?: string | Date | null;
          banned_at?: string | Date | null;
          ban_reason?: string | null;
          coins?: number | null;
          total_completions?: number | null;
          total_moves?: number | null;
          total_time?: number | null;
          last_activity?: string | Date | null;
        };

        // Last login séparément
        const [loginRows] = await db.execute(
          `SELECT MAX(created_at) as last_login
           FROM audit_log
           WHERE user_id = ? AND action = 'auth.login'`,
          [userId],
        );
        const lastLogin =
          Array.isArray(loginRows) && loginRows.length > 0
            ? (loginRows[0] as { last_login?: string | Date | null }).last_login
            : null;

        // Level (somme des completed dans progress)
        const [levelRows] = await db.execute(
          `SELECT COALESCE(SUM(completed), 0) as level FROM progress WHERE user_id = ?`,
          [userId],
        );
        const level = Array.isArray(levelRows)
          ? Number((levelRows[0] as { level?: number }).level) || 0
          : 0;

        // Parties jouées
        const [gamesRows] = await db.execute(
          `SELECT COUNT(*) as count FROM leaderboard_entries WHERE user_id = ?`,
          [userId],
        );
        const gamesPlayed = Array.isArray(gamesRows)
          ? Number((gamesRows[0] as { count?: number }).count) || 0
          : 0;

        const completions = Number(player.total_completions) || 0;
        const winRate =
          gamesPlayed > 0 ? Math.round((completions / gamesPlayed) * 100) : 0;

        // Packs d'avatars achetés
        const [avatarPacksRows] = await db.execute(
          `SELECT uap.pack_id, ap.name as pack_name, ap.price, uap.price_paid, uap.purchased_at
           FROM user_avatar_packs uap
           LEFT JOIN avatar_packs ap ON uap.pack_id = ap.pack_id
           WHERE uap.user_id = ?
           ORDER BY uap.purchased_at DESC`,
          [userId],
        );

        // Packs de skins achetés
        const [skinPacksRows] = await db.execute(
          `SELECT ubsp.pack_id, bsp.name as pack_name, bsp.price, ubsp.price_paid, ubsp.purchased_at
           FROM user_ball_skin_packs ubsp
           LEFT JOIN ball_skin_packs bsp ON ubsp.pack_id = bsp.pack_id
           WHERE ubsp.user_id = ?
           ORDER BY ubsp.purchased_at DESC`,
          [userId],
        );

        // Historique récent
        const [recentRows] = await db.execute(
          `SELECT action, meta, created_at as timestamp
           FROM audit_log
           WHERE user_id = ?
           ORDER BY created_at DESC
           LIMIT 20`,
          [userId],
        );

        const recentActivity = Array.isArray(recentRows)
          ? recentRows.map((row) => ({
              action: (row as { action?: string }).action ?? "",
              meta: (row as { meta?: string }).meta ?? null,
              timestamp:
                (row as { timestamp?: string | Date }).timestamp ?? null,
            }))
          : [];

        const purchases = [
          ...(Array.isArray(avatarPacksRows)
            ? avatarPacksRows.map((p: any) => ({
                type: "avatar_pack",
                pack_id: p.pack_id,
                name: p.pack_name || `Pack #${p.pack_id}`,
                price: p.price_paid || p.price || 0,
                purchased_at: p.purchased_at,
              }))
            : []),
          ...(Array.isArray(skinPacksRows)
            ? skinPacksRows.map((p: any) => ({
                type: "skin_pack",
                pack_id: p.pack_id,
                name: p.pack_name || `Pack #${p.pack_id}`,
                price: p.price_paid || p.price || 0,
                purchased_at: p.purchased_at,
              }))
            : []),
        ].sort(
          (a, b) =>
            new Date(b.purchased_at || 0).getTime() -
            new Date(a.purchased_at || 0).getTime(),
        );

        return reply.send({
          user_id: player.user_id,
          username: player.username ?? null,
          email: player.email ?? null,
          is_guest: Boolean(player.is_guest),
          created_at: player.created_at ?? null,
          last_login: lastLogin ?? null,
          banned_at: player.banned_at ?? null,
          ban_reason: player.ban_reason ?? null,
          level,
          coins: Number(player.coins) || 0,
          gamesPlayed,
          completions,
          winRate,
          total_moves: Number(player.total_moves) || 0,
          total_time: Number(player.total_time) || 0,
          purchases,
          recentActivity,
        });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/players/:id");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  app.get(
    "/admin/analytics",
    { preHandler: requireAdmin },
    async (request, reply) => {
      try {
        const period = String((request.query as any).period ?? "30d");
        let days = 30;
        if (period === "7d") days = 7;
        if (period === "90d") days = 90;
        if (period === "1y") days = 365;

        const fetchRevenueTotal = async (
          startDaysAgo: number,
          endDaysAgo: number,
        ): Promise<number> => {
          const [rows] = await db.execute(
            `SELECT COALESCE(SUM(amount), 0) as total
	             FROM (
	               SELECT price_paid as amount
	               FROM user_avatar_packs
	               WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
	                 AND purchased_at < DATE_SUB(NOW(), INTERVAL ? DAY)
	               UNION ALL
	               SELECT price_paid as amount
	               FROM user_ball_skin_packs
	               WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
	                 AND purchased_at < DATE_SUB(NOW(), INTERVAL ? DAY)
	               UNION ALL
	               SELECT price_eur as amount
	               FROM user_cash_pack_purchases
	               WHERE status = 'completed'
	                 AND processed_at IS NOT NULL
	                 AND processed_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
	                 AND processed_at < DATE_SUB(NOW(), INTERVAL ? DAY)
	               UNION ALL
	               SELECT price_paid as amount
	               FROM user_challenge_pack
	               WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
	                 AND purchased_at < DATE_SUB(NOW(), INTERVAL ? DAY)
	               UNION ALL
	               SELECT amount
	               FROM payment_transactions
	               WHERE pack_type = 'vip' AND status = 'completed'
	                 AND updated_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
	                 AND updated_at < DATE_SUB(NOW(), INTERVAL ? DAY)
	             ) as revenue`,
            [
              startDaysAgo,
              endDaysAgo,
              startDaysAgo,
              endDaysAgo,
              startDaysAgo,
              endDaysAgo,
              startDaysAgo,
              endDaysAgo,
              startDaysAgo,
              endDaysAgo,
            ],
          );
          return getFirstNumber(rows, "total", 0);
        };

        const fetchUsersCount = async (
          startDaysAgo: number,
          endDaysAgo: number,
        ): Promise<number> => {
          const [rows] = await db.execute(
            `SELECT COUNT(DISTINCT id) as count
	             FROM users
	             WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
	               AND created_at < DATE_SUB(NOW(), INTERVAL ? DAY)`,
            [startDaysAgo, endDaysAgo],
          );
          const count = getFirstNumber(rows, "count", 0);
          return Math.max(count, 0);
        };

        const fetchPayersCount = async (
          startDaysAgo: number,
          endDaysAgo: number,
        ): Promise<number> => {
          const [rows] = await db.execute(
            `SELECT COUNT(DISTINCT user_id) as count
	             FROM (
	               SELECT user_id
	               FROM user_avatar_packs
	               WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
	                 AND purchased_at < DATE_SUB(NOW(), INTERVAL ? DAY)
	               UNION
	               SELECT user_id
	               FROM user_ball_skin_packs
	               WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
	                 AND purchased_at < DATE_SUB(NOW(), INTERVAL ? DAY)
	               UNION
	               SELECT user_id
	               FROM user_cash_pack_purchases
	               WHERE status = 'completed'
	                 AND processed_at IS NOT NULL
	                 AND processed_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
	                 AND processed_at < DATE_SUB(NOW(), INTERVAL ? DAY)
	               UNION
	               SELECT user_id
	               FROM user_challenge_pack
	               WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
	                 AND purchased_at < DATE_SUB(NOW(), INTERVAL ? DAY)
	               UNION
	               SELECT user_id
	               FROM payment_transactions
	               WHERE pack_type = 'vip' AND status = 'completed'
	                 AND updated_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
	                 AND updated_at < DATE_SUB(NOW(), INTERVAL ? DAY)
	             ) as paying_users`,
            [
              startDaysAgo,
              endDaysAgo,
              startDaysAgo,
              endDaysAgo,
              startDaysAgo,
              endDaysAgo,
              startDaysAgo,
              endDaysAgo,
              startDaysAgo,
              endDaysAgo,
            ],
          );
          return getFirstNumber(rows, "count", 0);
        };

        const [totalRevenue, prevTotalRevenue] = await Promise.all([
          fetchRevenueTotal(days, 0),
          fetchRevenueTotal(days * 2, days),
        ]);

        const [usersCount, prevUsersCount] = await Promise.all([
          fetchUsersCount(days, 0),
          fetchUsersCount(days * 2, days),
        ]);

        const [payersCount, prevPayersCount] = await Promise.all([
          fetchPayersCount(days, 0),
          fetchPayersCount(days * 2, days),
        ]);

        const fetchLeaderboardStats = async (
          startDaysAgo: number,
          endDaysAgo: number,
        ) => {
          const [rows] = await db.execute(
            `SELECT
	              COUNT(*) as games,
	              COUNT(DISTINCT user_id) as players,
	              AVG(time_ms) as avgTimeMs
	             FROM leaderboard_entries
	             WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
	               AND created_at < DATE_SUB(NOW(), INTERVAL ? DAY)`,
            [startDaysAgo, endDaysAgo],
          );
          const row =
            Array.isArray(rows) && rows.length > 0
              ? (rows[0] as {
                  games?: number;
                  players?: number;
                  avgTimeMs?: number;
                })
              : {};
          return {
            games: Number(row.games) || 0,
            players: Number(row.players) || 0,
            avgTimeMs: Number(row.avgTimeMs) || 0,
          };
        };

        const [lb, prevLb] = await Promise.all([
          fetchLeaderboardStats(days, 0),
          fetchLeaderboardStats(days * 2, days),
        ]);

        const activeUsersCount = lb.players;
        const prevActiveUsersCount = prevLb.players;

        const arpu =
          activeUsersCount > 0 ? totalRevenue / activeUsersCount : null;
        const prevArpu =
          prevActiveUsersCount > 0
            ? prevTotalRevenue / prevActiveUsersCount
            : null;

        const conversionRate =
          activeUsersCount > 0 ? (payersCount / activeUsersCount) * 100 : null;
        const prevConversionRate =
          prevActiveUsersCount > 0
            ? (prevPayersCount / prevActiveUsersCount) * 100
            : null;

        const arpuChange =
          arpu === null || prevArpu === null
            ? null
            : calcChangePercent(arpu, prevArpu);
        const conversionChange =
          conversionRate === null || prevConversionRate === null
            ? null
            : calcChangePercent(conversionRate, prevConversionRate);

        const formatSigned = (value: number | null) => {
          if (value === null || !Number.isFinite(value)) return "—";
          const rounded = Math.round(value * 10) / 10;
          return `${rounded >= 0 ? "+" : ""}${rounded}%`;
        };

        const formatDuration = (ms: number) => {
          const totalSeconds = Math.max(0, Math.round(ms / 1000));
          const mins = Math.floor(totalSeconds / 60);
          const secs = totalSeconds % 60;
          return `${mins}m ${secs}s`;
        };

        const [topLevelsRows] = await db.execute(
          `SELECT
	            difficulty,
            level_index,
            COUNT(*) as plays
           FROM leaderboard_entries
	           WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
	           GROUP BY difficulty, level_index
	           ORDER BY plays DESC
	           LIMIT 10`,
          [days],
        );

        return reply.send({
          arpu: arpu === null ? null : Math.round(arpu * 100) / 100,
          arpuChange:
            arpuChange === null ? null : Math.round(arpuChange * 10) / 10,
          conversionRate:
            conversionRate === null
              ? null
              : Math.round(conversionRate * 10) / 10,
          conversionChange:
            conversionChange === null
              ? null
              : Math.round(conversionChange * 10) / 10,
          topLevels: {
            labels: Array.isArray(topLevelsRows)
              ? topLevelsRows.map(
                  (row) =>
                    `${(row as { difficulty?: string }).difficulty} #${(row as { level_index?: number }).level_index}`,
                )
              : [],
            values: Array.isArray(topLevelsRows)
              ? topLevelsRows.map(
                  (row) => Number((row as { plays?: number }).plays) || 0,
                )
              : [],
          },
          keyMetrics: [
            {
              name: "Joueurs actifs (période)",
              value: lb.players.toLocaleString("fr-FR"),
              change: formatSigned(
                calcChangePercent(lb.players, prevLb.players),
              ),
              target: "-",
            },
            {
              name: "Parties jouées (période)",
              value: lb.games.toLocaleString("fr-FR"),
              change: formatSigned(calcChangePercent(lb.games, prevLb.games)),
              target: "-",
            },
            {
              name: "Durée moyenne (période)",
              value: formatDuration(lb.avgTimeMs),
              change: formatSigned(
                calcChangePercent(lb.avgTimeMs, prevLb.avgTimeMs),
              ),
              target: "-",
            },
            {
              name: "Nouveaux joueurs (période)",
              value: usersCount.toLocaleString("fr-FR"),
              change: formatSigned(
                calcChangePercent(usersCount, prevUsersCount),
              ),
              target: "-",
            },
            {
              name: "Acheteurs (période)",
              value: payersCount.toLocaleString("fr-FR"),
              change: formatSigned(
                calcChangePercent(payersCount, prevPayersCount),
              ),
              target: "-",
            },
          ],
        });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/analytics");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  app.get(
    "/admin/analytics/revenue",
    { preHandler: requireAdmin },
    async (request, reply) => {
      try {
        const period = String((request.query as any).period ?? "30d");
        let days = 30;
        if (period === "7d") days = 7;
        if (period === "90d") days = 90;
        if (period === "1y") days = 365;

        // Total des packs avatars
        const [avatarTotalRows] = await db.execute(
          `SELECT COALESCE(SUM(price_paid), 0) as total
	           FROM user_avatar_packs
	           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL ? DAY)`,
          [days],
        );
        // Total des packs skins billes
        const [ballTotalRows] = await db.execute(
          `SELECT COALESCE(SUM(price_paid), 0) as total
           FROM user_ball_skin_packs
           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL ? DAY)`,
          [days],
        );
        // Total des cash packs (utilise price_eur et status completed comme la page Paiements)
        const [cashTotalRows] = await db.execute(
          `SELECT COALESCE(SUM(price_eur), 0) as total
           FROM user_cash_pack_purchases
           WHERE processed_at >= DATE_SUB(NOW(), INTERVAL ? DAY) AND status = 'completed'`,
          [days],
        );
        // Total des challenge packs (utilise price_paid comme la page Paiements)
        const [challengeTotalRows] = await db.execute(
          `SELECT COALESCE(SUM(price_paid), 0) as total
           FROM user_challenge_pack
           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL ? DAY)`,
          [days],
        );
        // Total des packs VIP
        const [vipTotalRows] = await db.execute(
          `SELECT COALESCE(SUM(amount), 0) as total
           FROM payment_transactions
           WHERE pack_type = 'vip' AND status = 'completed'
             AND updated_at >= DATE_SUB(NOW(), INTERVAL ? DAY)`,
          [days],
        );

        const total =
          (Array.isArray(avatarTotalRows)
            ? Number((avatarTotalRows[0] as { total?: number }).total) || 0
            : 0) +
          (Array.isArray(ballTotalRows)
            ? Number((ballTotalRows[0] as { total?: number }).total) || 0
            : 0) +
          (Array.isArray(cashTotalRows)
            ? Number((cashTotalRows[0] as { total?: number }).total) || 0
            : 0) +
          (Array.isArray(challengeTotalRows)
            ? Number((challengeTotalRows[0] as { total?: number }).total) || 0
            : 0) +
          (Array.isArray(vipTotalRows)
            ? Number((vipTotalRows[0] as { total?: number }).total) || 0
            : 0);

        const [prevAvatarTotalRows] = await db.execute(
          `SELECT COALESCE(SUM(price_paid), 0) as total
	           FROM user_avatar_packs
	           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
	             AND purchased_at < DATE_SUB(NOW(), INTERVAL ? DAY)`,
          [days * 2, days],
        );
        const [prevBallTotalRows] = await db.execute(
          `SELECT COALESCE(SUM(price_paid), 0) as total
	           FROM user_ball_skin_packs
	           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
	             AND purchased_at < DATE_SUB(NOW(), INTERVAL ? DAY)`,
          [days * 2, days],
        );
        const [prevCashTotalRows] = await db.execute(
          `SELECT COALESCE(SUM(price_eur), 0) as total
	           FROM user_cash_pack_purchases
	           WHERE processed_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
	             AND processed_at < DATE_SUB(NOW(), INTERVAL ? DAY)
	             AND status = 'completed'`,
          [days * 2, days],
        );
        const [prevChallengeTotalRows] = await db.execute(
          `SELECT COALESCE(SUM(price_paid), 0) as total
	           FROM user_challenge_pack
	           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
	             AND purchased_at < DATE_SUB(NOW(), INTERVAL ? DAY)`,
          [days * 2, days],
        );
        const [prevVipTotalRows] = await db.execute(
          `SELECT COALESCE(SUM(amount), 0) as total
	           FROM payment_transactions
	           WHERE pack_type = 'vip' AND status = 'completed'
	             AND updated_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
	             AND updated_at < DATE_SUB(NOW(), INTERVAL ? DAY)`,
          [days * 2, days],
        );
        const prevTotal =
          (Array.isArray(prevAvatarTotalRows)
            ? Number((prevAvatarTotalRows[0] as { total?: number }).total) || 0
            : 0) +
          (Array.isArray(prevBallTotalRows)
            ? Number((prevBallTotalRows[0] as { total?: number }).total) || 0
            : 0) +
          (Array.isArray(prevCashTotalRows)
            ? Number((prevCashTotalRows[0] as { total?: number }).total) || 0
            : 0) +
          (Array.isArray(prevChallengeTotalRows)
            ? Number((prevChallengeTotalRows[0] as { total?: number }).total) ||
              0
            : 0) +
          (Array.isArray(prevVipTotalRows)
            ? Number((prevVipTotalRows[0] as { total?: number }).total) || 0
            : 0);

        const change = calcChangePercent(total, prevTotal);

        // Daily breakdown - avatars
        const [avatarDailyRows] = await db.execute(
          `SELECT DATE(purchased_at) as date, COALESCE(SUM(price_paid), 0) as revenue
	           FROM user_avatar_packs
           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
           GROUP BY DATE(purchased_at)`,
          [days],
        );
        // Daily breakdown - ball skins
        const [ballDailyRows] = await db.execute(
          `SELECT DATE(purchased_at) as date, COALESCE(SUM(price_paid), 0) as revenue
           FROM user_ball_skin_packs
           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
           GROUP BY DATE(purchased_at)`,
          [days],
        );
        // Daily breakdown - cash packs
        const [cashDailyRows] = await db.execute(
          `SELECT DATE(processed_at) as date, COALESCE(SUM(price_eur), 0) as revenue
           FROM user_cash_pack_purchases
           WHERE processed_at >= DATE_SUB(NOW(), INTERVAL ? DAY) AND status = 'completed'
           GROUP BY DATE(processed_at)`,
          [days],
        );
        // Daily breakdown - challenge packs
        const [challengeDailyRows] = await db.execute(
          `SELECT DATE(purchased_at) as date, COALESCE(SUM(price_paid), 0) as revenue
           FROM user_challenge_pack
           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
           GROUP BY DATE(purchased_at)`,
          [days],
        );
        // Daily breakdown - VIP
        const [vipDailyRows] = await db.execute(
          `SELECT DATE(updated_at) as date, COALESCE(SUM(amount), 0) as revenue
           FROM payment_transactions
           WHERE pack_type = 'vip' AND status = 'completed'
             AND updated_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
           GROUP BY DATE(updated_at)`,
          [days],
        );

        const dailyMap = new Map<string, number>();
        [
          ...(Array.isArray(avatarDailyRows) ? avatarDailyRows : []),
          ...(Array.isArray(ballDailyRows) ? ballDailyRows : []),
          ...(Array.isArray(cashDailyRows) ? cashDailyRows : []),
          ...(Array.isArray(challengeDailyRows) ? challengeDailyRows : []),
          ...(Array.isArray(vipDailyRows) ? vipDailyRows : []),
        ].forEach((row) => {
          const date = toDateString((row as { date?: string | Date }).date);
          if (!date) return;
          dailyMap.set(
            date,
            (dailyMap.get(date) || 0) +
              (Number((row as { revenue?: number }).revenue) || 0),
          );
        });

        const labels = Array.from(dailyMap.keys()).sort();
        const values = labels.map((label) => dailyMap.get(label) || 0);

        return reply.send({
          total: Math.round(total * 100) / 100,
          change: change === null ? null : Math.round(change * 10) / 10,
          daily: {
            labels,
            values,
          },
        });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/analytics/revenue");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  app.get(
    "/admin/analytics/retention",
    { preHandler: requireAdmin },
    async (_request, reply) => {
      try {
        const windowDays = 30;

        const fetchRetention = async (
          dayOffset: number,
          previous: boolean,
        ): Promise<{
          pct: number | null;
          cohortSize: number;
          retained: number;
        }> => {
          const shift = previous ? windowDays : 0;
          const startDaysAgo = windowDays + dayOffset + shift;
          const endDaysAgo = dayOffset + shift;

          const [rows] = await db.execute(
            `SELECT
              COUNT(DISTINCT u.id) as cohortSize,
              COUNT(DISTINCT CASE WHEN al.user_id IS NOT NULL THEN u.id END) as retained
             FROM users u
             LEFT JOIN audit_log al
               ON al.user_id = u.id
              AND al.action = 'auth.login'
              AND DATE(al.created_at) = DATE_ADD(DATE(u.created_at), INTERVAL ? DAY)
             WHERE u.guest = 0
               AND u.created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
               AND u.created_at < DATE_SUB(NOW(), INTERVAL ? DAY)`,
            [dayOffset, startDaysAgo, endDaysAgo],
          );

          const cohortSize = Array.isArray(rows)
            ? Number((rows[0] as { cohortSize?: number }).cohortSize) || 0
            : 0;
          const retained = Array.isArray(rows)
            ? Number((rows[0] as { retained?: number }).retained) || 0
            : 0;

          if (cohortSize <= 0) {
            return { pct: null, cohortSize, retained };
          }
          const pct = (retained / cohortSize) * 100;
          return { pct: Math.round(pct * 10) / 10, cohortSize, retained };
        };

        const [day1, day7, day14, day30] = await Promise.all([
          fetchRetention(1, false),
          fetchRetention(7, false),
          fetchRetention(14, false),
          fetchRetention(30, false),
        ]);

        const [prevDay1, prevDay7, prevDay14, prevDay30] = await Promise.all([
          fetchRetention(1, true),
          fetchRetention(7, true),
          fetchRetention(14, true),
          fetchRetention(30, true),
        ]);

        const calcDelta = (current: number | null, prev: number | null) => {
          if (typeof current !== "number" || !Number.isFinite(current))
            return null;
          if (typeof prev !== "number" || !Number.isFinite(prev)) return null;
          return Math.round((current - prev) * 10) / 10;
        };

        const day1Pct = day1.pct;
        const day7Pct = day7.pct;
        const day14Pct = day14.pct;
        const day30Pct = day30.pct;

        return reply.send({
          day1: day1Pct,
          day1Change: calcDelta(day1Pct, prevDay1.pct),
          day7: day7Pct,
          day7Change: calcDelta(day7Pct, prevDay7.pct),
          day14: day14Pct,
          day14Change: calcDelta(day14Pct, prevDay14.pct),
          day30: day30Pct,
          day30Change: calcDelta(day30Pct, prevDay30.pct),
          cohorts: [day1Pct, day7Pct, day14Pct, day30Pct],
          samples: {
            day1: { cohortSize: day1.cohortSize, retained: day1.retained },
            day7: { cohortSize: day7.cohortSize, retained: day7.retained },
            day14: { cohortSize: day14.cohortSize, retained: day14.retained },
            day30: { cohortSize: day30.cohortSize, retained: day30.retained },
          },
        });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/analytics/retention");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  app.get(
    "/admin/logs",
    { preHandler: requireAdmin },
    async (request, reply) => {
      try {
        const query = request.query as Record<string, unknown>;
        const page = parsePage(query.page);
        const pageSize = parsePageSize(query.pageSize, 50, 200);
        const search = String(query.search ?? "").trim();
        const typeFilter = String(query.type ?? "").trim();
        const levelFilter = String(query.level ?? "").trim();

        const offset = (page - 1) * pageSize;
        let whereClause = "WHERE 1=1";
        const params: Array<string | number> = [];

        // Filtre par type basé sur le préfixe de l'action
        if (typeFilter) {
          const typeMapping: Record<string, string[]> = {
            auth: ["auth.%", "login.%", "logout.%", "register.%"],
            payment: ["payment.%", "purchase.%", "subscription.%"],
            gameplay: ["game.%", "level.%", "challenge.%"],
            error: ["error.%", "fail.%"],
            admin: ["admin.%"],
            system: ["system.%", "cron.%", "maintenance.%"],
          };
          const patterns = typeMapping[typeFilter];
          if (patterns && patterns.length > 0) {
            const orConditions = patterns
              .map(() => "action LIKE ?")
              .join(" OR ");
            whereClause += ` AND (${orConditions})`;
            params.push(...patterns);
          }
        }

        // Filtre par niveau (basé sur certains mots dans l'action ou meta)
        if (levelFilter) {
          if (levelFilter === "error" || levelFilter === "critical") {
            whereClause +=
              " AND (action LIKE '%error%' OR action LIKE '%fail%' OR action LIKE '%denied%')";
          } else if (levelFilter === "warn") {
            whereClause +=
              " AND (action LIKE '%warn%' OR action LIKE '%invalid%' OR action LIKE '%expired%')";
          }
        }

        if (search) {
          whereClause +=
            " AND (action LIKE ? OR CAST(meta AS CHAR) LIKE ? OR user_id = ?)";
          params.push(
            `%${search}%`,
            `%${search}%`,
            isNaN(Number(search)) ? -1 : Number(search),
          );
        }

        // Jointure avec users pour avoir le nom
        const [logsRows] = await db.execute(
          `SELECT
            al.id,
            al.user_id,
            al.action,
            al.meta,
            al.ip,
            al.user_agent,
            al.created_at,
            u.display_name,
            u.email
           FROM audit_log al
           LEFT JOIN users u ON al.user_id = u.id
           ${whereClause}
           ORDER BY al.created_at DESC
           LIMIT ${Math.trunc(pageSize)} OFFSET ${Math.trunc(offset)}`,
          params,
        );

        const [countRows] = await db.execute(
          `SELECT COUNT(*) as total FROM audit_log al ${whereClause}`,
          params,
        );

        const [todayRows] = await db.execute(
          `SELECT COUNT(*) as total FROM audit_log WHERE DATE(created_at) = CURDATE()`,
        );
        const [errorsRows] = await db.execute(
          `SELECT COUNT(*) as total FROM audit_log WHERE action LIKE '%error%' OR action LIKE '%fail%' OR action LIKE '%denied%'`,
        );
        const [adminRows] = await db.execute(
          `SELECT COUNT(*) as total FROM audit_log WHERE action LIKE 'admin.%'`,
        );
        const [paymentRows] = await db.execute(
          `SELECT COUNT(*) as total FROM audit_log WHERE action LIKE 'payment.%' OR action LIKE 'purchase.%'`,
        );

        const total = Array.isArray(countRows)
          ? Number((countRows[0] as { total?: number }).total) || 0
          : 0;

        // Fonction pour déterminer le type basé sur l'action
        const getTypeFromAction = (action: string): string => {
          if (
            action.startsWith("auth.") ||
            action.startsWith("login.") ||
            action.startsWith("logout.") ||
            action.startsWith("register.")
          )
            return "auth";
          if (action.startsWith("payment.") || action.startsWith("purchase."))
            return "payment";
          if (
            action.startsWith("game.") ||
            action.startsWith("level.") ||
            action.startsWith("challenge.")
          )
            return "gameplay";
          if (action.includes("error") || action.includes("fail"))
            return "error";
          if (action.startsWith("admin.")) return "admin";
          return "system";
        };

        // Fonction pour déterminer le niveau basé sur l'action
        const getLevelFromAction = (action: string): string => {
          if (
            action.includes("error") ||
            action.includes("fail") ||
            action.includes("denied")
          )
            return "error";
          if (
            action.includes("warn") ||
            action.includes("invalid") ||
            action.includes("expired")
          )
            return "warn";
          if (action.includes("success") || action.includes("complete"))
            return "info";
          return "info";
        };

        // Fonction pour formater le message depuis meta
        const formatMessage = (action: string, meta: unknown): string => {
          if (!meta) return "";
          try {
            const data = typeof meta === "string" ? JSON.parse(meta) : meta;
            if (typeof data === "object" && data !== null) {
              const parts: string[] = [];
              if (data.level_id) parts.push(`Niveau: ${data.level_id}`);
              if (data.pack_id) parts.push(`Pack: ${data.pack_id}`);
              if (data.amount) parts.push(`Montant: ${data.amount}€`);
              if (data.reason) parts.push(`Raison: ${data.reason}`);
              if (data.error) parts.push(`Erreur: ${data.error}`);
              if (data.message) parts.push(data.message);
              if (data.email) parts.push(`Email: ${data.email}`);
              if (data.target_user_id)
                parts.push(`Cible: #${data.target_user_id}`);
              if (parts.length > 0) return parts.join(" | ");
              return JSON.stringify(data);
            }
            return String(meta);
          } catch {
            return String(meta);
          }
        };

        const logs = Array.isArray(logsRows)
          ? logsRows.map((row) => {
              const item = row as {
                id?: number;
                user_id?: number;
                action?: string;
                meta?: unknown;
                ip?: string | null;
                user_agent?: string | null;
                created_at?: string | Date;
                display_name?: string | null;
                email?: string | null;
              };
              const action = item.action ?? "";
              return {
                id: item.id ?? 0,
                timestamp: item.created_at ?? null,
                type: getTypeFromAction(action),
                level: getLevelFromAction(action),
                user_id: item.user_id ?? null,
                user_name: item.display_name ?? null,
                user_email: item.email ?? null,
                action: action,
                message: formatMessage(action, item.meta),
                metadata: item.meta,
                ip_address: item.ip ?? null,
                user_agent: item.user_agent ?? null,
              };
            })
          : [];

        return reply.send({
          logs,
          total,
          page,
          pageSize,
          totalPages: Math.ceil(total / pageSize),
          stats: {
            todayCount: Array.isArray(todayRows)
              ? Number((todayRows[0] as { total?: number }).total) || 0
              : 0,
            errorsCount: Array.isArray(errorsRows)
              ? Number((errorsRows[0] as { total?: number }).total) || 0
              : 0,
            adminCount: Array.isArray(adminRows)
              ? Number((adminRows[0] as { total?: number }).total) || 0
              : 0,
            paymentsCount: Array.isArray(paymentRows)
              ? Number((paymentRows[0] as { total?: number }).total) || 0
              : 0,
          },
        });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/logs");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  app.get(
    "/admin/payments",
    { preHandler: requireAdmin },
    async (request, reply) => {
      try {
        const query = request.query as Record<string, unknown>;
        const page = parsePage(query.page);
        const pageSize = parsePageSize(query.pageSize, 20, 100);
        const period = String(query.period ?? "month").trim();
        const status = String(query.status ?? "").trim();
        const search = String(query.search ?? "").trim();

        const offset = (page - 1) * pageSize;

        const buildWhere = (timestampColumn: string) => {
          let clause = "WHERE 1=1";
          const outParams: Array<string | number> = [];

          if (search) {
            clause += " AND u.display_name LIKE ?";
            outParams.push(`%${search}%`);
          }

          if (period === "today") {
            clause += ` AND DATE(${timestampColumn}) = CURDATE()`;
          } else if (period === "week") {
            clause += ` AND ${timestampColumn} >= DATE_SUB(NOW(), INTERVAL 7 DAY)`;
          } else if (period === "month") {
            clause += ` AND ${timestampColumn} >= DATE_SUB(NOW(), INTERVAL 30 DAY)`;
          }

          return { clause, outParams };
        };

        const { clause: avatarWhere, outParams: avatarParams } =
          buildWhere("uap.purchased_at");
        const { clause: ballWhere, outParams: ballParams } =
          buildWhere("ubsp.purchased_at");
        const { clause: cashWhere, outParams: cashParams } = buildWhere(
          "COALESCE(ucpp.processed_at, ucpp.purchased_at)",
        );
        const { clause: challengeWhere, outParams: challengeParams } =
          buildWhere("ucp.purchased_at");
        const { clause: vipWhere, outParams: vipParams } =
          buildWhere("pt.updated_at");

        const unionSql = [
          `SELECT
            CRC32(CONCAT('avatar:', uap.user_id, ':', uap.pack_id, ':', uap.purchased_at)) as id,
            'avatar' as type,
            uap.user_id,
            u.display_name as username,
            uap.pack_id as product_id,
            ap.name as product_name,
            uap.price_paid as amount,
            uap.purchased_at as created_at,
            'stripe' as payment_method,
            'completed' as status,
            uap.transaction_id as external_id,
            pt.stripe_session_id,
            pt.stripe_payment_intent_id
           FROM user_avatar_packs uap
           JOIN users u ON uap.user_id = u.id
           LEFT JOIN avatar_packs ap ON uap.pack_id = ap.pack_id
           LEFT JOIN payment_transactions pt ON pt.stripe_session_id = uap.transaction_id AND pt.user_id = uap.user_id AND pt.pack_id = uap.pack_id
           ${avatarWhere}`,
          `SELECT
            CRC32(CONCAT('ball:', ubsp.user_id, ':', ubsp.pack_id, ':', ubsp.purchased_at)) as id,
            'ball_skin' as type,
            ubsp.user_id,
            u.display_name as username,
            ubsp.pack_id as product_id,
            bsp.name as product_name,
            ubsp.price_paid as amount,
            ubsp.purchased_at as created_at,
            'stripe' as payment_method,
            'completed' as status,
            ubsp.transaction_id as external_id,
            pt.stripe_session_id,
            pt.stripe_payment_intent_id
           FROM user_ball_skin_packs ubsp
           JOIN users u ON ubsp.user_id = u.id
           LEFT JOIN ball_skin_packs bsp ON ubsp.pack_id = bsp.pack_id
           LEFT JOIN payment_transactions pt ON pt.stripe_session_id = ubsp.transaction_id AND pt.user_id = ubsp.user_id AND pt.pack_id = ubsp.pack_id
           ${ballWhere}`,
          `SELECT
	            ucpp.id as id,
	            'cash_pack' as type,
	            ucpp.user_id,
	            u.display_name as username,
	            ucpp.pack_id as product_id,
	            ucpp.pack_name as product_name,
	            ucpp.price_eur as amount,
	            COALESCE(ucpp.processed_at, ucpp.purchased_at) as created_at,
	            'stripe' as payment_method,
	            ucpp.status as status,
	            ucpp.stripe_payment_intent_id as external_id,
	            ucpp.stripe_session_id,
	            ucpp.stripe_payment_intent_id
	           FROM user_cash_pack_purchases ucpp
	           JOIN users u ON ucpp.user_id = u.id
	           ${cashWhere}`,
          `SELECT
            CRC32(CONCAT('challenge:', ucp.user_id, ':', ucp.purchased_at)) as id,
            'challenge_pack' as type,
            ucp.user_id,
            u.display_name as username,
            'challenge_pack' as product_id,
            'Pack Défis' as product_name,
            ucp.price_paid as amount,
            ucp.purchased_at as created_at,
            'stripe' as payment_method,
            'completed' as status,
            ucp.transaction_id as external_id,
            pt.stripe_session_id,
            pt.stripe_payment_intent_id
           FROM user_challenge_pack ucp
           JOIN users u ON ucp.user_id = u.id
           LEFT JOIN payment_transactions pt ON pt.stripe_session_id = ucp.transaction_id AND pt.user_id = ucp.user_id
           ${challengeWhere}`,
          `SELECT
            pt.id as id,
            'vip' as type,
            pt.user_id,
            u.display_name as username,
            pt.pack_id as product_id,
            'Pack VIP' as product_name,
            pt.amount as amount,
            pt.updated_at as created_at,
            'stripe' as payment_method,
            CASE
              WHEN pt.status IN ('created', 'processing') THEN 'pending'
              WHEN pt.status = 'completed' THEN 'completed'
              WHEN pt.status IN ('failed', 'canceled', 'cancelled', 'expired') THEN 'failed'
              WHEN pt.status = 'refunded' THEN 'refunded'
              ELSE pt.status
            END as status,
            pt.stripe_payment_intent_id as external_id,
            pt.stripe_session_id,
            pt.stripe_payment_intent_id
           FROM payment_transactions pt
           JOIN users u ON pt.user_id = u.id
           ${vipWhere} AND pt.pack_type = 'vip'`,
        ].join(" UNION ALL ");

        const baseParams = [
          ...avatarParams,
          ...ballParams,
          ...cashParams,
          ...challengeParams,
          ...vipParams,
        ];

        const filteredSql = status
          ? `SELECT * FROM (${unionSql}) t WHERE t.status = ?`
          : `SELECT * FROM (${unionSql}) t`;
        const filteredParams = status ? [...baseParams, status] : baseParams;

        const [countRows] = await db.execute(
          `SELECT COUNT(*) as total FROM (${filteredSql}) c`,
          filteredParams,
        );
        const total = Array.isArray(countRows)
          ? Number((countRows[0] as { total?: number }).total) || 0
          : 0;

        const [paymentsRows] = await db.execute(
          `${filteredSql}
           ORDER BY created_at DESC
           LIMIT ${Math.trunc(pageSize)} OFFSET ${Math.trunc(offset)}`,
          filteredParams,
        );
        const payments = Array.isArray(paymentsRows) ? paymentsRows : [];

        const [avatarStatsRows] = await db.execute(
          `SELECT COALESCE(SUM(price_paid), 0) as total, COUNT(*) as count
           FROM user_avatar_packs
           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)`,
        );
        const [ballStatsRows] = await db.execute(
          `SELECT COALESCE(SUM(price_paid), 0) as total, COUNT(*) as count
           FROM user_ball_skin_packs
           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)`,
        );
        const [cashStatsRows] = await db.execute(
          `SELECT COALESCE(SUM(price_eur), 0) as total, COUNT(*) as count
	           FROM user_cash_pack_purchases
	           WHERE processed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND status = 'completed'`,
        );
        const [challengeStatsRows] = await db.execute(
          `SELECT COALESCE(SUM(price_paid), 0) as total, COUNT(*) as count
           FROM user_challenge_pack
           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)`,
        );
        const [vipStatsRows] = await db.execute(
          `SELECT COALESCE(SUM(amount), 0) as total, COUNT(*) as count
           FROM payment_transactions
           WHERE updated_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND pack_type = 'vip' AND status = 'completed'`,
        );

        // Stats par type de cash pack
        const [cashPackBreakdown] = await db.execute(
          `SELECT 
            pack_id,
            pack_name,
            COUNT(*) as purchase_count,
            COALESCE(SUM(price_eur), 0) as total_revenue
           FROM user_cash_pack_purchases
           WHERE processed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND status = 'completed'
           GROUP BY pack_id, pack_name
           ORDER BY FIELD(pack_id, 'silver', 'gold', 'platinum')`,
        );

        const monthRevenue =
          (Array.isArray(avatarStatsRows)
            ? Number((avatarStatsRows[0] as { total?: number }).total) || 0
            : 0) +
          (Array.isArray(ballStatsRows)
            ? Number((ballStatsRows[0] as { total?: number }).total) || 0
            : 0) +
          (Array.isArray(cashStatsRows)
            ? Number((cashStatsRows[0] as { total?: number }).total) || 0
            : 0) +
          (Array.isArray(challengeStatsRows)
            ? Number((challengeStatsRows[0] as { total?: number }).total) || 0
            : 0) +
          (Array.isArray(vipStatsRows)
            ? Number((vipStatsRows[0] as { total?: number }).total) || 0
            : 0);
        const transactionCount =
          (Array.isArray(avatarStatsRows)
            ? Number((avatarStatsRows[0] as { count?: number }).count) || 0
            : 0) +
          (Array.isArray(ballStatsRows)
            ? Number((ballStatsRows[0] as { count?: number }).count) || 0
            : 0) +
          (Array.isArray(cashStatsRows)
            ? Number((cashStatsRows[0] as { count?: number }).count) || 0
            : 0) +
          (Array.isArray(challengeStatsRows)
            ? Number((challengeStatsRows[0] as { count?: number }).count) || 0
            : 0) +
          (Array.isArray(vipStatsRows)
            ? Number((vipStatsRows[0] as { count?: number }).count) || 0
            : 0);
        const avgTransaction =
          transactionCount > 0 ? monthRevenue / transactionCount : 0;

        const [prevAvatarStatsRows] = await db.execute(
          `SELECT COALESCE(SUM(price_paid), 0) as total, COUNT(*) as count
	           FROM user_avatar_packs
	           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL 60 DAY)
	             AND purchased_at < DATE_SUB(NOW(), INTERVAL 30 DAY)`,
        );
        const [prevBallStatsRows] = await db.execute(
          `SELECT COALESCE(SUM(price_paid), 0) as total, COUNT(*) as count
	           FROM user_ball_skin_packs
	           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL 60 DAY)
	             AND purchased_at < DATE_SUB(NOW(), INTERVAL 30 DAY)`,
        );
        const [prevCashStatsRows] = await db.execute(
          `SELECT COALESCE(SUM(price_eur), 0) as total, COUNT(*) as count
	           FROM user_cash_pack_purchases
	           WHERE processed_at >= DATE_SUB(NOW(), INTERVAL 60 DAY)
	             AND processed_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
	             AND status = 'completed'`,
        );
        const [prevChallengeStatsRows] = await db.execute(
          `SELECT COALESCE(SUM(price_paid), 0) as total, COUNT(*) as count
	           FROM user_challenge_pack
	           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL 60 DAY)
	             AND purchased_at < DATE_SUB(NOW(), INTERVAL 30 DAY)`,
        );
        const [prevVipStatsRows] = await db.execute(
          `SELECT COALESCE(SUM(amount), 0) as total, COUNT(*) as count
           FROM payment_transactions
           WHERE updated_at >= DATE_SUB(NOW(), INTERVAL 60 DAY)
             AND updated_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
             AND pack_type = 'vip' AND status = 'completed'`,
        );

        const prevMonthRevenue =
          (Array.isArray(prevAvatarStatsRows)
            ? Number((prevAvatarStatsRows[0] as { total?: number }).total) || 0
            : 0) +
          (Array.isArray(prevBallStatsRows)
            ? Number((prevBallStatsRows[0] as { total?: number }).total) || 0
            : 0) +
          (Array.isArray(prevCashStatsRows)
            ? Number((prevCashStatsRows[0] as { total?: number }).total) || 0
            : 0) +
          (Array.isArray(prevChallengeStatsRows)
            ? Number((prevChallengeStatsRows[0] as { total?: number }).total) ||
              0
            : 0) +
          (Array.isArray(prevVipStatsRows)
            ? Number((prevVipStatsRows[0] as { total?: number }).total) || 0
            : 0);
        const prevTransactionCount =
          (Array.isArray(prevAvatarStatsRows)
            ? Number((prevAvatarStatsRows[0] as { count?: number }).count) || 0
            : 0) +
          (Array.isArray(prevBallStatsRows)
            ? Number((prevBallStatsRows[0] as { count?: number }).count) || 0
            : 0) +
          (Array.isArray(prevCashStatsRows)
            ? Number((prevCashStatsRows[0] as { count?: number }).count) || 0
            : 0) +
          (Array.isArray(prevChallengeStatsRows)
            ? Number((prevChallengeStatsRows[0] as { count?: number }).count) ||
              0
            : 0) +
          (Array.isArray(prevVipStatsRows)
            ? Number((prevVipStatsRows[0] as { count?: number }).count) || 0
            : 0);
        const prevAvgTransaction =
          prevTransactionCount > 0
            ? prevMonthRevenue / prevTransactionCount
            : 0;

        const monthRevenueChange = calcChangePercent(
          monthRevenue,
          prevMonthRevenue,
        );
        const transactionCountChange = calcChangePercent(
          transactionCount,
          prevTransactionCount,
        );
        const avgTransactionChange = calcChangePercent(
          avgTransaction,
          prevAvgTransaction,
        );

        const [dailyAvatarRows] = await db.execute(
          `SELECT DATE(purchased_at) as date, COALESCE(SUM(price_paid), 0) as revenue
	           FROM user_avatar_packs
           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
           GROUP BY DATE(purchased_at)`,
        );
        const [dailyBallRows] = await db.execute(
          `SELECT DATE(purchased_at) as date, COALESCE(SUM(price_paid), 0) as revenue
           FROM user_ball_skin_packs
           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
           GROUP BY DATE(purchased_at)`,
        );
        const [dailyCashRows] = await db.execute(
          `SELECT DATE(processed_at) as date, COALESCE(SUM(price_eur), 0) as revenue
	           FROM user_cash_pack_purchases
	           WHERE processed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND status = 'completed'
	           GROUP BY DATE(processed_at)`,
        );
        const [dailyChallengeRows] = await db.execute(
          `SELECT DATE(purchased_at) as date, COALESCE(SUM(price_paid), 0) as revenue
	           FROM user_challenge_pack
	           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
	           GROUP BY DATE(purchased_at)`,
        );
        const [dailyVipRows] = await db.execute(
          `SELECT DATE(updated_at) as date, COALESCE(SUM(amount), 0) as revenue
           FROM payment_transactions
           WHERE updated_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND pack_type = 'vip' AND status = 'completed'
           GROUP BY DATE(updated_at)`,
        );

        const [cashAllRows] = await db.execute(
          `SELECT
	            COUNT(*) as total,
	            SUM(CASE WHEN status != 'completed' THEN 1 ELSE 0 END) as non_completed
	           FROM user_cash_pack_purchases
	           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)`,
        );
        const cashTotal = Array.isArray(cashAllRows)
          ? Number((cashAllRows[0] as { total?: number }).total) || 0
          : 0;
        const cashNonCompleted = Array.isArray(cashAllRows)
          ? Number(
              (cashAllRows[0] as { non_completed?: number }).non_completed,
            ) || 0
          : 0;
        const failureRate =
          cashTotal > 0 ? (cashNonCompleted / cashTotal) * 100 : null;

        const [prevCashAllRows] = await db.execute(
          `SELECT
	            COUNT(*) as total,
	            SUM(CASE WHEN status != 'completed' THEN 1 ELSE 0 END) as non_completed
	           FROM user_cash_pack_purchases
	           WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL 60 DAY)
	             AND purchased_at < DATE_SUB(NOW(), INTERVAL 30 DAY)`,
        );
        const prevCashTotal = Array.isArray(prevCashAllRows)
          ? Number((prevCashAllRows[0] as { total?: number }).total) || 0
          : 0;
        const prevCashNonCompleted = Array.isArray(prevCashAllRows)
          ? Number(
              (prevCashAllRows[0] as { non_completed?: number }).non_completed,
            ) || 0
          : 0;
        const prevFailureRate =
          prevCashTotal > 0
            ? (prevCashNonCompleted / prevCashTotal) * 100
            : null;
        const failureRateChange =
          failureRate === null || prevFailureRate === null
            ? null
            : calcChangePercent(failureRate, prevFailureRate);

        const dailyMap = new Map<string, number>();
        [
          ...(Array.isArray(dailyAvatarRows) ? dailyAvatarRows : []),
          ...(Array.isArray(dailyBallRows) ? dailyBallRows : []),
          ...(Array.isArray(dailyCashRows) ? dailyCashRows : []),
          ...(Array.isArray(dailyChallengeRows) ? dailyChallengeRows : []),
          ...(Array.isArray(dailyVipRows) ? dailyVipRows : []),
        ].forEach((row) => {
          const date = toDateString((row as { date?: string | Date }).date);
          if (!date) return;
          dailyMap.set(
            date,
            (dailyMap.get(date) || 0) +
              (Number((row as { revenue?: number }).revenue) || 0),
          );
        });
        const dailyLabels = Array.from(dailyMap.keys()).sort();
        const dailyValues = dailyLabels.map(
          (label) => dailyMap.get(label) || 0,
        );

        return reply.send({
          payments,
          total,
          page,
          pageSize,
          totalPages: Math.ceil(total / pageSize),
          stats: {
            monthRevenue: Math.round(monthRevenue * 100) / 100,
            monthRevenueChange:
              monthRevenueChange === null
                ? null
                : Math.round(monthRevenueChange * 10) / 10,
            transactionCount,
            transactionCountChange:
              transactionCountChange === null
                ? null
                : Math.round(transactionCountChange * 10) / 10,
            avgTransaction: Math.round(avgTransaction * 100) / 100,
            avgTransactionChange:
              avgTransactionChange === null
                ? null
                : Math.round(avgTransactionChange * 10) / 10,
            failureRate:
              failureRate === null ? null : Math.round(failureRate * 10) / 10,
            failureRateChange:
              failureRateChange === null || failureRateChange === undefined
                ? null
                : Math.round(failureRateChange * 10) / 10,
            dailyRevenue: {
              labels: dailyLabels,
              values: dailyValues,
            },
            byType: {
              avatar: {
                count: getFirstNumber(avatarStatsRows, "count", 0),
                revenue:
                  Math.round(
                    getFirstNumber(avatarStatsRows, "total", 0) * 100,
                  ) / 100,
              },
              ball_skin: {
                count: getFirstNumber(ballStatsRows, "count", 0),
                revenue:
                  Math.round(getFirstNumber(ballStatsRows, "total", 0) * 100) /
                  100,
              },
              cash_pack: {
                count: getFirstNumber(cashStatsRows, "count", 0),
                revenue:
                  Math.round(getFirstNumber(cashStatsRows, "total", 0) * 100) /
                  100,
              },
              challenge_pack: {
                count: getFirstNumber(challengeStatsRows, "count", 0),
                revenue:
                  Math.round(
                    getFirstNumber(challengeStatsRows, "total", 0) * 100,
                  ) / 100,
              },
              vip: {
                count: getFirstNumber(vipStatsRows, "count", 0),
                revenue:
                  Math.round(getFirstNumber(vipStatsRows, "total", 0) * 100) /
                  100,
              },
            },
            cashPacks: {
              breakdown: Array.isArray(cashPackBreakdown)
                ? (
                    cashPackBreakdown as Array<{
                      pack_id: string;
                      pack_name: string;
                      purchase_count: number;
                      total_revenue: number;
                    }>
                  ).map((row) => ({
                    packId: row.pack_id,
                    packName: row.pack_name,
                    count: Number(row.purchase_count) || 0,
                    revenue: Math.round(Number(row.total_revenue) * 100) / 100,
                  }))
                : [],
              totalCount: Array.isArray(cashStatsRows)
                ? Number((cashStatsRows[0] as { count?: number }).count) || 0
                : 0,
              totalRevenue: Array.isArray(cashStatsRows)
                ? Math.round(
                    Number((cashStatsRows[0] as { total?: number }).total) *
                      100,
                  ) / 100
                : 0,
            },
          },
        });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/payments");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  app.get(
    "/admin/payments/:id",
    { preHandler: requireAdmin },
    async (request, reply) => {
      try {
        const { id } = request.params as { id: string };
        const numericId = parseNumber(id, 0);

        const [avatarRows] = await db.execute(
          `SELECT
            CRC32(CONCAT('avatar:', uap.user_id, ':', uap.pack_id, ':', uap.purchased_at)) as id,
            'avatar' as type,
            uap.user_id,
            u.display_name as username,
            u.email,
            uap.pack_id as product_id,
            ap.name as product_name,
            uap.price_paid as amount,
            uap.purchased_at as created_at,
            'stripe' as payment_method,
            'completed' as status,
            uap.transaction_id as external_id,
            pt.stripe_session_id,
            pt.stripe_payment_intent_id
           FROM user_avatar_packs uap
           JOIN users u ON uap.user_id = u.id
           LEFT JOIN avatar_packs ap ON uap.pack_id = ap.pack_id
           LEFT JOIN payment_transactions pt ON pt.stripe_session_id = uap.transaction_id AND pt.user_id = uap.user_id AND pt.pack_id = uap.pack_id
           WHERE CRC32(CONCAT('avatar:', uap.user_id, ':', uap.pack_id, ':', uap.purchased_at)) = ?
           LIMIT 1`,
          [numericId],
        );

        if (Array.isArray(avatarRows) && avatarRows.length > 0) {
          return reply.send(avatarRows[0]);
        }

        const [ballRows] = await db.execute(
          `SELECT
            CRC32(CONCAT('ball:', ubsp.user_id, ':', ubsp.pack_id, ':', ubsp.purchased_at)) as id,
            'ball_skin' as type,
            ubsp.user_id,
            u.display_name as username,
            u.email,
            ubsp.pack_id as product_id,
            bsp.name as product_name,
            ubsp.price_paid as amount,
            ubsp.purchased_at as created_at,
            'stripe' as payment_method,
            'completed' as status,
            ubsp.transaction_id as external_id,
            pt.stripe_session_id,
            pt.stripe_payment_intent_id
           FROM user_ball_skin_packs ubsp
           JOIN users u ON ubsp.user_id = u.id
           LEFT JOIN ball_skin_packs bsp ON ubsp.pack_id = bsp.pack_id
           LEFT JOIN payment_transactions pt ON pt.stripe_session_id = ubsp.transaction_id AND pt.user_id = ubsp.user_id AND pt.pack_id = ubsp.pack_id
           WHERE CRC32(CONCAT('ball:', ubsp.user_id, ':', ubsp.pack_id, ':', ubsp.purchased_at)) = ?
           LIMIT 1`,
          [numericId],
        );

        if (Array.isArray(ballRows) && ballRows.length > 0) {
          return reply.send(ballRows[0]);
        }

        // Cash packs - l'ID est directement l'ID de la table
        const [cashRows] = await db.execute(
          `SELECT
            ucpp.id,
            'cash_pack' as type,
            ucpp.user_id,
            u.display_name as username,
            u.email,
            ucpp.pack_id as product_id,
            ucpp.pack_name as product_name,
            ucpp.price_eur as amount,
            COALESCE(ucpp.processed_at, ucpp.purchased_at) as created_at,
            'stripe' as payment_method,
            ucpp.status,
            ucpp.stripe_payment_intent_id as external_id,
            ucpp.items_hints,
            ucpp.items_undos,
            ucpp.items_replays,
            ucpp.bonus_points,
            ucpp.stripe_session_id,
            ucpp.stripe_payment_intent_id
           FROM user_cash_pack_purchases ucpp
           JOIN users u ON ucpp.user_id = u.id
           WHERE ucpp.id = ?
           LIMIT 1`,
          [numericId],
        );

        if (Array.isArray(cashRows) && cashRows.length > 0) {
          return reply.send(cashRows[0]);
        }

        // Challenge pack
        const [challengeRows] = await db.execute(
          `SELECT
            CRC32(CONCAT('challenge:', ucp.user_id, ':', ucp.purchased_at)) as id,
            'challenge_pack' as type,
            ucp.user_id,
            u.display_name as username,
            u.email,
            'challenge_pack' as product_id,
            'Pack Défis' as product_name,
            ucp.price_paid as amount,
            ucp.purchased_at as created_at,
            'stripe' as payment_method,
            'completed' as status,
            ucp.transaction_id as external_id,
            pt.stripe_session_id,
            pt.stripe_payment_intent_id
           FROM user_challenge_pack ucp
           JOIN users u ON ucp.user_id = u.id
           LEFT JOIN payment_transactions pt ON pt.stripe_session_id = ucp.transaction_id AND pt.user_id = ucp.user_id
           WHERE CRC32(CONCAT('challenge:', ucp.user_id, ':', ucp.purchased_at)) = ?
           LIMIT 1`,
          [numericId],
        );

        if (Array.isArray(challengeRows) && challengeRows.length > 0) {
          return reply.send(challengeRows[0]);
        }

        // VIP pack
        const [vipRows] = await db.execute(
          `SELECT
            pt.id as id,
            'vip' as type,
            pt.user_id,
            u.display_name as username,
            u.email,
            pt.pack_id as product_id,
            'Pack VIP' as product_name,
            pt.amount as amount,
            pt.updated_at as created_at,
            'stripe' as payment_method,
            CASE
              WHEN pt.status IN ('created', 'processing') THEN 'pending'
              WHEN pt.status = 'completed' THEN 'completed'
              WHEN pt.status IN ('failed', 'canceled', 'cancelled', 'expired') THEN 'failed'
              WHEN pt.status = 'refunded' THEN 'refunded'
              ELSE pt.status
            END as status,
            pt.status as raw_status,
            pt.stripe_session_id,
            pt.stripe_payment_intent_id as external_id,
            pt.stripe_payment_intent_id
           FROM payment_transactions pt
           JOIN users u ON pt.user_id = u.id
           WHERE pt.id = ? AND pt.pack_type = 'vip'
           LIMIT 1`,
          [numericId],
        );

        if (Array.isArray(vipRows) && vipRows.length > 0) {
          return reply.send(vipRows[0]);
        }

        return reply.code(404).send({ error: "Payment not found" });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/payments/:id");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  app.get(
    "/admin/levels",
    { preHandler: requireAdmin },
    async (request, reply) => {
      try {
        const query = request.query as Record<string, unknown>;
        const filterDifficulty = String(query.difficulty ?? "").trim();

        // Constantes du jeu
        const LEVELS_PER_DIFFICULTY = 240;
        const difficulties = ["easy", "medium", "hard", "expert"];

        // 1. Stats globales par difficulté (joueurs ayant terminé au moins 1 niveau)
        const [difficultyStatsRows] = await db.execute(`
          SELECT 
            difficulty,
            COUNT(DISTINCT user_id) as unique_players,
            COUNT(*) as total_completions,
            MAX(level_index) as max_level_reached
          FROM leaderboard_entries
          GROUP BY difficulty
        `);

        const difficultyStats: Record<
          string,
          {
            players: number;
            completions: number;
            maxLevel: number;
            avgProgress: number;
          }
        > = {};
        difficulties.forEach((d) => {
          difficultyStats[d] = {
            players: 0,
            completions: 0,
            maxLevel: 0,
            avgProgress: 0,
          };
        });

        if (Array.isArray(difficultyStatsRows)) {
          difficultyStatsRows.forEach((row: unknown) => {
            const r = row as {
              difficulty: string;
              unique_players: number;
              total_completions: number;
              max_level_reached: number;
            };
            difficultyStats[r.difficulty] = {
              players: Number(r.unique_players) || 0,
              completions: Number(r.total_completions) || 0,
              maxLevel: Number(r.max_level_reached) || 0,
              avgProgress: 0,
            };
          });
        }

        // 2. Progression moyenne par difficulté (nb moyen de niveaux terminés par joueur)
        const [progressRows] = await db.execute(`
          SELECT 
            difficulty,
            AVG(levels_count) as avg_progress
          FROM (
            SELECT user_id, difficulty, COUNT(*) as levels_count
            FROM leaderboard_entries
            GROUP BY user_id, difficulty
          ) sub
          GROUP BY difficulty
        `);

        if (Array.isArray(progressRows)) {
          progressRows.forEach((row: unknown) => {
            const r = row as { difficulty: string; avg_progress: number };
            if (difficultyStats[r.difficulty]) {
              difficultyStats[r.difficulty].avgProgress = Math.round(
                Number(r.avg_progress) || 0,
              );
            }
          });
        }

        // 3. Top 10 niveaux les plus joués
        let topPlayedWhere = "";
        const topPlayedParams: string[] = [];
        if (filterDifficulty) {
          topPlayedWhere = "WHERE difficulty = ?";
          topPlayedParams.push(filterDifficulty);
        }

        const [topPlayedRows] = await db.execute(
          `
          SELECT 
            difficulty,
            level_index,
            COUNT(*) as plays,
            AVG(moves) as avg_moves,
            AVG(time_ms) as avg_time_ms
          FROM leaderboard_entries
          ${topPlayedWhere}
          GROUP BY difficulty, level_index
          ORDER BY plays DESC
          LIMIT 10
        `,
          topPlayedParams,
        );

        const topPlayed = Array.isArray(topPlayedRows)
          ? topPlayedRows.map((row: unknown) => {
              const r = row as {
                difficulty: string;
                level_index: number;
                plays: number;
                avg_moves: number;
                avg_time_ms: number;
              };
              return {
                difficulty: r.difficulty,
                levelIndex: Number(r.level_index),
                plays: Number(r.plays),
                avgMoves: Math.round(Number(r.avg_moves) || 0),
                avgTime: Math.round((Number(r.avg_time_ms) || 0) / 1000),
              };
            })
          : [];

        // 4. Tentatives par niveau (via level_stats - completions > 1 = plusieurs essais)
        const [attemptsRows] = await db.execute(`
          SELECT 
            difficulty,
            level_id as level_index,
            AVG(completions) as avg_attempts,
            SUM(completions) as total_attempts,
            COUNT(DISTINCT user_id) as unique_completers
          FROM level_stats
          WHERE completions > 0
          GROUP BY difficulty, level_id
          ORDER BY avg_attempts DESC
          LIMIT 15
        `);

        const hardestLevels = Array.isArray(attemptsRows)
          ? attemptsRows.map((row: unknown) => {
              const r = row as {
                difficulty: string;
                level_index: number;
                avg_attempts: number;
                total_attempts: number;
                unique_completers: number;
              };
              return {
                difficulty: r.difficulty,
                levelIndex: Number(r.level_index),
                avgAttempts:
                  Math.round((Number(r.avg_attempts) || 1) * 10) / 10,
                totalAttempts: Number(r.total_attempts) || 0,
                completers: Number(r.unique_completers) || 0,
              };
            })
          : [];

        // 5. Activité récente (parties des 7 derniers jours)
        const [recentActivityRows] = await db.execute(`
          SELECT 
            DATE(created_at) as date,
            difficulty,
            COUNT(*) as games
          FROM leaderboard_entries
          WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
          GROUP BY DATE(created_at), difficulty
          ORDER BY date DESC
        `);

        const recentActivity = Array.isArray(recentActivityRows)
          ? recentActivityRows.map((row: unknown) => {
              const r = row as {
                date: string;
                difficulty: string;
                games: number;
              };
              return {
                date: r.date,
                difficulty: r.difficulty,
                games: Number(r.games),
              };
            })
          : [];

        // 6. Joueurs ayant terminé 100% d'une difficulté (240 niveaux)
        const [completionistsRows] = await db.execute(`
          SELECT 
            difficulty,
            COUNT(*) as completionists
          FROM (
            SELECT user_id, difficulty, COUNT(*) as completed
            FROM leaderboard_entries
            GROUP BY user_id, difficulty
            HAVING completed >= 240
          ) sub
          GROUP BY difficulty
        `);

        const completionists: Record<string, number> = {};
        if (Array.isArray(completionistsRows)) {
          completionistsRows.forEach((row: unknown) => {
            const r = row as { difficulty: string; completionists: number };
            completionists[r.difficulty] = Number(r.completionists) || 0;
          });
        }

        // 7. Totaux globaux
        const [totalsRow] = await db.execute(`
          SELECT 
            COUNT(DISTINCT user_id) as total_players,
            COUNT(*) as total_games,
            SUM(moves) as total_moves
          FROM leaderboard_entries
        `);

        const totals =
          Array.isArray(totalsRow) && totalsRow[0]
            ? {
                players:
                  Number(
                    (totalsRow[0] as { total_players: number }).total_players,
                  ) || 0,
                games:
                  Number(
                    (totalsRow[0] as { total_games: number }).total_games,
                  ) || 0,
                moves:
                  Number(
                    (totalsRow[0] as { total_moves: number }).total_moves,
                  ) || 0,
              }
            : { players: 0, games: 0, moves: 0 };

        return reply.send({
          overview: {
            totalLevels: LEVELS_PER_DIFFICULTY * 4,
            levelsPerDifficulty: LEVELS_PER_DIFFICULTY,
            difficulties: difficulties.length,
            totalPlayers: totals.players,
            totalGames: totals.games,
            totalMoves: totals.moves,
          },
          difficultyStats,
          completionists,
          topPlayed,
          hardestLevels,
          recentActivity,
        });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/levels");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  app.get(
    "/admin/avatars",
    { preHandler: requireAdmin },
    async (_request, reply) => {
      try {
        // Retourner les packs d'avatars payants (pas les avatars individuels)
        const [rows] = await db.execute(
          `SELECT ap.pack_id as id,
                  ap.name,
                  ap.price,
                  'pack' as category,
                  COALESCE(p.purchases, 0) as owned_count
           FROM avatar_packs ap
           LEFT JOIN (
             SELECT pack_id, COUNT(*) as purchases
             FROM user_avatar_packs
             GROUP BY pack_id
           ) p ON p.pack_id = ap.pack_id
           WHERE ap.is_active = 1
           ORDER BY ap.created_at DESC
           LIMIT 100`,
        );

        return reply.send(Array.isArray(rows) ? rows : []);
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/avatars");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  app.get(
    "/admin/challenges",
    { preHandler: requireAdmin },
    async (_request, reply) => {
      try {
        const [rows] = await db.execute(
          `SELECT date_key, difficulty, level_index, created_at
           FROM daily_challenges
           ORDER BY date_key DESC
           LIMIT 200`,
        );

        const list = Array.isArray(rows)
          ? rows.map((row) => {
              const item = row as {
                date_key?: string | Date;
                difficulty?: string;
                level_index?: number;
              };
              const dateKey = toDateString(item.date_key);
              const isActive =
                Boolean(dateKey) && dateKey >= toDateString(new Date());
              return {
                id: dateKey || "-",
                name: `Défi ${item.difficulty ?? "medium"}`,
                description: `Niveau ${item.level_index ?? 0}`,
                reward: 0,
                participants: 0,
                active: isActive,
              };
            })
          : [];

        const active = list.filter((item) => item.active).length;

        return reply.send({
          active,
          participants: 0,
          list,
        });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/challenges");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  app.get(
    "/admin/skins",
    { preHandler: requireAdmin },
    async (_request, reply) => {
      try {
        const [rows] = await db.execute(
          `SELECT bsp.id,
                  bsp.name,
                  bsp.price,
                  '#999999' as color,
                  COALESCE(p.purchases, 0) as purchases
           FROM ball_skin_packs bsp
           LEFT JOIN (
             SELECT pack_id, COUNT(*) as purchases
             FROM user_ball_skin_packs
             GROUP BY pack_id
           ) p ON p.pack_id = bsp.pack_id
           ORDER BY bsp.created_at DESC
           LIMIT 300`,
        );

        return reply.send(Array.isArray(rows) ? rows : []);
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/skins");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  app.get(
    "/admin/db/structure",
    { preHandler: requireAdminDbTools },
    async (request, reply) => {
      try {
        const query = request.query as Record<string, unknown>;
        const includeData = String(query.includeData ?? "true") !== "false";
        const limit = Math.max(1, Math.min(20, parseNumber(query.limit, 5)));

        const [tablesRows] = await db.execute(
          `SELECT table_name as name
           FROM information_schema.tables
           WHERE table_schema = DATABASE()
           ORDER BY table_name ASC`,
        );

        const tables = Array.isArray(tablesRows)
          ? tablesRows
              .map((row) => (row as { name?: string }).name)
              .filter((name): name is string => Boolean(name))
          : [];

        const safeIdentifier = /^[0-9A-Za-z_]+$/;
        const safeTables = tables.filter((name) => safeIdentifier.test(name));

        const tableDetails = await Promise.all(
          safeTables.map(async (tableName) => {
            const [columnsRows] = await db.execute(
              `SELECT column_name as name,
                      data_type as dataType,
                      column_type as columnType,
                      is_nullable as isNullable,
                      column_default as defaultValue,
                      extra
               FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = ?
               ORDER BY ordinal_position ASC`,
              [tableName],
            );

            const [countRows] = await db.execute(
              `SELECT COUNT(*) as total FROM \`${tableName}\``,
            );
            const totalRows = Array.isArray(countRows)
              ? Number((countRows[0] as { total?: number }).total) || 0
              : 0;

            let sampleRows: unknown[] = [];
            if (includeData) {
              const [sample] = await db.execute(
                `SELECT * FROM \`${tableName}\` LIMIT ${limit}`,
              );
              sampleRows = Array.isArray(sample) ? sample : [];
            }

            return {
              name: tableName,
              totalRows,
              columns: Array.isArray(columnsRows) ? columnsRows : [],
              sampleRows,
            };
          }),
        );

        return reply.send({
          generatedAt: new Date().toISOString(),
          includeData,
          limit,
          tables: tableDetails,
        });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/db/structure");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  app.get(
    "/admin/db/reset/preview",
    { preHandler: requireAdminDbTools },
    async (_request, reply) => {
      try {
        const keepTables = [
          "app_config",
          "levels_catalog",
          "avatars",
          "avatar_packs",
          "ball_skin_packs",
          "daily_challenges",
          "weekly_rewards",
          "used_weekly_avatars",
          "pack_avatars",
        ];

        const resetTables = [
          "wallet_history",
          "user_cash_pack_purchases",
          "payment_transactions",
          "daily_bonus",
          "arcade_bonus",
          "ad_rewards",
          "ad_reward_events",
          "user_ball_skins",
          "user_ball_skin_packs",
          "user_avatar_packs",
          "user_challenge_pack",
          "user_weekly_avatars",
          "user_avatars",
          "weekly_progress",
          "daily_progress",
          "leaderboard_entries",
          "recent_runs",
          "level_stats",
          "user_stats",
          "arcade_stats",
          "infinite_stats",
          "progress",
          "user_settings",
          "refresh_tokens",
          "audit_log",
          "wallets",
          "users",
        ];

        return reply.send({
          keepTables,
          resetTables,
        });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/db/reset/preview");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  app.post(
    "/admin/db/reset",
    { preHandler: requireAdminDbTools },
    async (request, reply) => {
      try {
        const body = (request.body ?? {}) as {
          confirm?: string;
          preserveAdmins?: boolean;
        };

        if (body.confirm !== "RESET") {
          return reply.code(400).send({ error: "Confirmation requise" });
        }

        const preserveAdmins = Boolean(body.preserveAdmins);
        const resetTables = [
          "wallet_history",
          "user_cash_pack_purchases",
          "payment_transactions",
          "daily_bonus",
          "arcade_bonus",
          "ad_rewards",
          "ad_reward_events",
          "user_ball_skins",
          "user_ball_skin_packs",
          "user_avatar_packs",
          "user_challenge_pack",
          "user_weekly_avatars",
          "user_avatars",
          "weekly_progress",
          "daily_progress",
          "leaderboard_entries",
          "recent_runs",
          "level_stats",
          "user_stats",
          "arcade_stats",
          "infinite_stats",
          "progress",
          "user_settings",
          "refresh_tokens",
          "audit_log",
          "wallets",
        ];

        const conn = await db.getConnection();
        try {
          await conn.execute("SET FOREIGN_KEY_CHECKS = 0");

          try {
            for (const tableName of resetTables) {
              await conn.execute(`DELETE FROM \`${tableName}\``);
            }

            if (preserveAdmins) {
              await conn.execute("DELETE FROM users WHERE is_admin = 0");
            } else {
              await conn.execute("DELETE FROM users");
            }
          } finally {
            await conn.execute("SET FOREIGN_KEY_CHECKS = 1");
          }

          // Reset des AUTO_INCREMENT pour repartir propre
          const autoIncrementTables = [
            "users",
            "refresh_tokens",
            "audit_log",
            "recent_runs",
            "leaderboard_entries",
            "payment_transactions",
            "user_cash_pack_purchases",
            "wallet_history",
          ];
          for (const t of autoIncrementTables) {
            await conn.execute(`ALTER TABLE \`${t}\` AUTO_INCREMENT = 1`);
          }
        } finally {
          conn.release();
        }

        return reply.send({
          status: "ok",
          preservedAdmins: preserveAdmins,
          tablesCleared: resetTables.length + 1, // +1 pour users
        });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/db/reset");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  // Bannir un joueur
  app.post(
    "/admin/players/:id/ban",
    { preHandler: requireAdmin },
    async (request, reply) => {
      try {
        const { id } = request.params as { id: string };
        const userId = parseNumber(id, 0);
        if (!userId) {
          return reply.code(400).send({ error: "Invalid user id" });
        }

        const body = (request.body ?? {}) as {
          reason?: string;
          duration?: string;
        };
        const reason = String(body.reason ?? "Banned by admin").trim();
        const duration = String(body.duration ?? "permanent").trim(); // permanent, 1d, 7d, 30d, 90d

        // Vérifier que l'utilisateur existe et n'est pas admin
        const [userRows] = await db.execute(
          `SELECT id, is_admin, banned_at FROM users WHERE id = ?`,
          [userId],
        );

        if (!Array.isArray(userRows) || userRows.length === 0) {
          return reply.code(404).send({ error: "Player not found" });
        }

        const user = userRows[0] as {
          id: number;
          is_admin?: number;
          banned_at?: Date | null;
        };
        if (user.is_admin) {
          return reply.code(403).send({ error: "Cannot ban an admin" });
        }

        // Calculer la date de fin de ban selon la durée
        let banDurationLabel = "permanent";
        const durationMap: Record<string, { days: number; label: string }> = {
          "1d": { days: 1, label: "1 jour" },
          "7d": { days: 7, label: "7 jours" },
          "30d": { days: 30, label: "30 jours" },
          "90d": { days: 90, label: "90 jours" },
        };
        const durationConfig = durationMap[duration];
        banDurationLabel = durationConfig?.label ?? "permanent";

        if (durationConfig) {
          // Ban temporaire avec DATE_ADD paramétré
          await db.execute(
            `UPDATE users
             SET banned_at = NOW(),
                 ban_until = DATE_ADD(NOW(), INTERVAL ? DAY),
                 ban_reason = ?
             WHERE id = ?`,
            [durationConfig.days, reason, userId],
          );
        } else {
          // Ban permanent
          await db.execute(
            `UPDATE users SET banned_at = NOW(), ban_until = NULL, ban_reason = ? WHERE id = ?`,
            [reason, userId],
          );
        }

        // Log l'action
        const admin = (request as any).user;
        await db.execute(
          `INSERT INTO audit_log (user_id, action, meta, created_at)
           VALUES (?, 'admin.ban_player', ?, NOW())`,
          [
            admin?.id || null,
            JSON.stringify({
              target_user_id: userId,
              reason,
              duration: banDurationLabel,
            }),
          ],
        );

        return reply.send({
          success: true,
          message: `Player banned (${banDurationLabel})`,
        });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/players/:id/ban");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  // Débannir un joueur
  app.post(
    "/admin/players/:id/unban",
    { preHandler: requireAdmin },
    async (request, reply) => {
      try {
        const { id } = request.params as { id: string };
        const userId = parseNumber(id, 0);
        if (!userId) {
          return reply.code(400).send({ error: "Invalid user id" });
        }

        const [userRows] = await db.execute(
          `SELECT id, banned_at FROM users WHERE id = ?`,
          [userId],
        );

        if (!Array.isArray(userRows) || userRows.length === 0) {
          return reply.code(404).send({ error: "Player not found" });
        }

        await db.execute(
          `UPDATE users SET banned_at = NULL, ban_until = NULL, ban_reason = NULL WHERE id = ?`,
          [userId],
        );

        const admin = (request as any).user;
        await db.execute(
          `INSERT INTO audit_log (user_id, action, meta, created_at)
           VALUES (?, 'admin.unban_player', ?, NOW())`,
          [admin?.id || null, JSON.stringify({ target_user_id: userId })],
        );

        return reply.send({ success: true, message: "Player unbanned" });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/players/:id/unban");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  // Supprimer un joueur
  app.delete(
    "/admin/players/:id",
    { preHandler: requireAdmin },
    async (request, reply) => {
      try {
        const { id } = request.params as { id: string };
        const userId = parseNumber(id, 0);
        if (!userId) {
          return reply.code(400).send({ error: "Invalid user id" });
        }

        // Vérifier que l'utilisateur existe et n'est pas admin
        const [userRows] = await db.execute(
          `SELECT id, is_admin, display_name, email FROM users WHERE id = ?`,
          [userId],
        );

        if (!Array.isArray(userRows) || userRows.length === 0) {
          return reply.code(404).send({ error: "Player not found" });
        }

        const user = userRows[0] as {
          id: number;
          is_admin?: number;
          display_name?: string;
          email?: string;
        };
        if (user.is_admin) {
          return reply.code(403).send({ error: "Cannot delete an admin" });
        }

        // Log l'action AVANT de supprimer (pour éviter les problèmes de FK)
        const admin = (request as any).user;
        await db.execute(
          `INSERT INTO audit_log (user_id, action, meta, created_at)
           VALUES (?, 'admin.delete_player', ?, NOW())`,
          [
            admin?.id || null,
            JSON.stringify({
              deleted_user_id: userId,
              display_name: user.display_name,
              email: user.email,
            }),
          ],
        );

        // Supprimer toutes les données liées au joueur
        const conn = await db.getConnection();
        try {
          await conn.execute("SET FOREIGN_KEY_CHECKS = 0");

          try {
            const tablesToClean = [
              "audit_log",
              "leaderboard_entries",
              "refresh_tokens",
              "progress",
              "user_stats",
              "recent_runs",
              "arcade_stats",
              "infinite_stats",
              "daily_progress",
              "user_settings",
              "user_avatars",
              "user_weekly_avatars",
              "user_challenge_pack",
              "user_avatar_packs",
              "user_ball_skin_packs",
              "wallets",
            ];

            for (const tableName of tablesToClean) {
              try {
                // Ne pas supprimer les logs de l'admin, seulement ceux du joueur supprimé
                if (tableName === "audit_log") {
                  await conn.execute(
                    `DELETE FROM audit_log WHERE user_id = ? AND action != 'admin.delete_player'`,
                    [userId],
                  );
                } else {
                  await conn.execute(
                    `DELETE FROM \`${tableName}\` WHERE user_id = ?`,
                    [userId],
                  );
                }
              } catch {
                // Table might not exist or have different structure
              }
            }

            // Supprimer l'utilisateur
            await conn.execute(`DELETE FROM users WHERE id = ?`, [userId]);
          } finally {
            await conn.execute("SET FOREIGN_KEY_CHECKS = 1");
          }
        } finally {
          conn.release();
        }

        return reply.send({ success: true, message: "Player deleted" });
      } catch (error) {
        app.log.error({ err: error }, "Erreur DELETE /admin/players/:id");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );

  // Bannir par email (ajoute à une liste noire)
  app.post(
    "/admin/ban-email",
    { preHandler: requireAdmin },
    async (request, reply) => {
      try {
        const body = (request.body ?? {}) as {
          email?: string;
          reason?: string;
        };
        const email = String(body.email ?? "")
          .trim()
          .toLowerCase();
        const reason = String(body.reason ?? "Banned by admin").trim();

        if (!email || !email.includes("@")) {
          return reply.code(400).send({ error: "Invalid email" });
        }

        // Bannir tous les comptes avec cet email
        const [result] = await db.execute(
          `UPDATE users
           SET banned_at = NOW(), ban_until = NULL, ban_reason = ?
           WHERE LOWER(email) = ? AND is_admin = 0`,
          [reason, email],
        );

        const affectedRows =
          (result as { affectedRows?: number }).affectedRows || 0;

        const admin = (request as any).user;
        await db.execute(
          `INSERT INTO audit_log (user_id, action, meta, created_at)
           VALUES (?, 'admin.ban_email', ?, NOW())`,
          [
            admin?.id || null,
            JSON.stringify({ email, reason, accounts_banned: affectedRows }),
          ],
        );

        return reply.send({
          success: true,
          message: `Email banned. ${affectedRows} account(s) affected.`,
          accountsBanned: affectedRows,
        });
      } catch (error) {
        app.log.error({ err: error }, "Erreur /admin/ban-email");
        return reply.code(500).send({ error: "Erreur serveur" });
      }
    },
  );
};