/**
 * Services de récupération de données utilisateur
 * Fonctions pour charger les stats, wallet, progression, etc.
 */
import type { Pool } from "mysql2/promise";
import {
  ensureArcadeStats,
  ensureDailyProgress,
  ensureInfiniteStats,
  ensureUserSettings,
  ensureUserStats,
  ensureWallet,
} from "./db-schema.js";
import { clampInt } from "./crypto-utils.js";
import { DIFFICULTIES, type DifficultyKey } from "./constants.js";
import { parseJsonArray } from "./parsers.js";
import { computeBadges } from "./badges.js";
import type {
  ArcadeStats,
  DailyProgress,
  InfiniteStats,
  LevelStat,
  ProgressPayload,
  RecentRun,
  SettingsState,
  StatsState,
  WalletPayload,
  WalletRow,
} from "./types.js";

/**
 * Récupère les stats complètes d'un utilisateur (avec calcul des badges)
 */
export const fetchStats = async (
  db: Pool,
  userId: number,
): Promise<StatsState> => {
  await ensureUserStats(db, userId);
  const [userRows] = await db.execute(
    "SELECT guest, is_admin as isAdmin, created_at as createdAt FROM users WHERE id = ? LIMIT 1",
    [userId],
  );
  const userRow = Array.isArray(userRows) ? userRows[0] : undefined;
  const isGuest = Boolean((userRow as { guest?: unknown } | undefined)?.guest);
  const isAdmin = Boolean(
    (userRow as { isAdmin?: unknown } | undefined)?.isAdmin,
  );
  const createdAtRaw = (userRow as { createdAt?: unknown } | undefined)
    ?.createdAt;
  const userCreatedAtMs =
    createdAtRaw instanceof Date
      ? createdAtRaw.getTime()
      : typeof createdAtRaw === "string"
        ? Date.parse(createdAtRaw)
        : null;

  let launchDateMs: number | null = null;
  if (!isAdmin) {
    const [launchRows] = await db.execute(
      "SELECT config_value as configValue FROM app_config WHERE config_key = 'launch_date' LIMIT 1",
    );
    const launchValue = Array.isArray(launchRows)
      ? ((launchRows[0] as { configValue?: unknown } | undefined)
          ?.configValue ?? null)
      : null;
    launchDateMs =
      typeof launchValue === "string" ? Date.parse(launchValue) : null;
  }

  let rankSinceLaunch: number | null = null;
  if (
    !isAdmin &&
    typeof userCreatedAtMs === "number" &&
    Number.isFinite(userCreatedAtMs)
  ) {
    const createdAtForRank = new Date(userCreatedAtMs);
    const [rankRows] = await db.execute(
      `SELECT COUNT(*) as total
       FROM users
       WHERE is_admin = 0
         AND (
           created_at < ?
           OR (created_at = ? AND id <= ?)
         )`,
      [createdAtForRank, createdAtForRank, userId],
    );
    const row = Array.isArray(rankRows) ? rankRows[0] : undefined;
    rankSinceLaunch = Number((row as { total?: unknown } | undefined)?.total);
    if (!Number.isFinite(rankSinceLaunch)) {
      rankSinceLaunch = null;
    }
  }
  const [statsRows] = await db.execute(
    "SELECT total_completions as totalCompletions, total_moves as totalMoves, total_time as totalTime, tutorial_completed as tutorialCompleted, badges FROM user_stats WHERE user_id = ? LIMIT 1",
    [userId],
  );
  const statsRow = Array.isArray(statsRows) ? statsRows[0] : undefined;
  const totals = statsRow as
    | {
        totalCompletions?: number | string;
        totalMoves?: number | string;
        totalTime?: number | string;
        tutorialCompleted?: number | boolean;
        badges?: unknown;
      }
    | undefined;

  const [levelRows] = await db.execute(
    "SELECT difficulty, level_id as levelId, completions, last_moves as lastMoves, last_time as lastTime, best_moves as bestMoves, best_time as bestTime FROM level_stats WHERE user_id = ?",
    [userId],
  );
  const levels: Record<string, LevelStat> = {};
  if (Array.isArray(levelRows)) {
    levelRows.forEach((row) => {
      const item = row as {
        difficulty?: DifficultyKey;
        levelId?: number;
        completions?: number;
        lastMoves?: number;
        lastTime?: number;
        bestMoves?: number | null;
        bestTime?: number | null;
      };
      if (!item.difficulty || typeof item.levelId !== "number") {
        return;
      }
      const key = `${item.difficulty}:${item.levelId}`;
      levels[key] = {
        completions: Number(item.completions) || 0,
        lastMoves: Number(item.lastMoves) || 0,
        lastTime: Number(item.lastTime) || 0,
        bestMoves:
          item.bestMoves === null || item.bestMoves === undefined
            ? undefined
            : Number(item.bestMoves) || 0,
        bestTime:
          item.bestTime === null || item.bestTime === undefined
            ? undefined
            : Number(item.bestTime) || 0,
      };
    });
  }

  const [recentRows] = await db.execute(
    "SELECT difficulty, level_id as levelId, moves, time, UNIX_TIMESTAMP(completed_at) as completedAt FROM recent_runs WHERE user_id = ? ORDER BY completed_at DESC, id DESC LIMIT 6",
    [userId],
  );
  const recentRuns: RecentRun[] = Array.isArray(recentRows)
    ? recentRows.map((row) => {
        const item = row as {
          difficulty?: DifficultyKey;
          levelId?: number;
          moves?: number;
          time?: number;
          completedAt?: number | string;
        };
        return {
          difficulty: item.difficulty ?? "easy",
          levelId: Number(item.levelId) || 0,
          moves: Number(item.moves) || 0,
          time: Number(item.time) || 0,
          completedAt: Number(item.completedAt) * 1000 || Date.now(),
        };
      })
    : [];

  const totalsPayload = {
    totalCompletions: Number(totals?.totalCompletions) || 0,
    totalMoves: Number(totals?.totalMoves) || 0,
    totalTime: Number(totals?.totalTime) || 0,
  };
  const tutorialCompleted = Boolean(totals?.tutorialCompleted);
  const progress = await fetchProgress(db, userId);
  const computedBadges = computeBadges({
    progress,
    totals: totalsPayload,
    tutorialCompleted,
    isGuest,
    isAdmin,
    userCreatedAtMs:
      typeof userCreatedAtMs === "number" && Number.isFinite(userCreatedAtMs)
        ? userCreatedAtMs
        : undefined,
    launchDateMs:
      typeof launchDateMs === "number" && Number.isFinite(launchDateMs)
        ? launchDateMs
        : undefined,
    rankSinceLaunch:
      typeof rankSinceLaunch === "number" && Number.isFinite(rankSinceLaunch)
        ? rankSinceLaunch
        : undefined,
  });
  const storedBadges = parseJsonArray(totals?.badges);
  if (JSON.stringify(computedBadges) !== JSON.stringify(storedBadges)) {
    await db.execute("UPDATE user_stats SET badges = ? WHERE user_id = ?", [
      JSON.stringify(computedBadges),
      userId,
    ]);
  }

  return {
    levels,
    totals: {
      completions: totalsPayload.totalCompletions,
      totalMoves: totalsPayload.totalMoves,
      totalTime: totalsPayload.totalTime,
    },
    recentRuns,
    tutorialCompleted,
    badgesUnlocked: computedBadges,
  };
};

/**
 * Récupère les stats du mode arcade
 */
export const fetchArcadeStats = async (
  db: Pool,
  userId: number,
): Promise<ArcadeStats> => {
  await ensureArcadeStats(db, userId);
  const [rows] = await db.execute(
    "SELECT best_score as bestScore, best_levels as bestLevels, last_score as lastScore, last_levels as lastLevels, UNIX_TIMESTAMP(last_played_at) as lastPlayedAt FROM arcade_stats WHERE user_id = ? LIMIT 1",
    [userId],
  );
  const row = Array.isArray(rows) ? rows[0] : undefined;
  const item = row as
    | {
        bestScore?: number;
        bestLevels?: number;
        lastScore?: number;
        lastLevels?: number;
        lastPlayedAt?: number | string | null;
      }
    | undefined;
  return {
    bestScore: Number(item?.bestScore) || 0,
    bestLevels: Number(item?.bestLevels) || 0,
    lastScore: Number(item?.lastScore) || 0,
    lastLevels: Number(item?.lastLevels) || 0,
    lastPlayedAt: item?.lastPlayedAt ? Number(item.lastPlayedAt) * 1000 : 0,
  };
};

/**
 * Récupère les stats du mode infini
 */
export const fetchInfiniteStats = async (
  db: Pool,
  userId: number,
): Promise<InfiniteStats> => {
  await ensureInfiniteStats(db, userId);
  const [rows] = await db.execute(
    "SELECT best_levels as bestLevels, last_levels as lastLevels, UNIX_TIMESTAMP(last_played_at) as lastPlayedAt FROM infinite_stats WHERE user_id = ? LIMIT 1",
    [userId],
  );
  const row = Array.isArray(rows) ? rows[0] : undefined;
  const item = row as
    | {
        bestLevels?: number;
        lastLevels?: number;
        lastPlayedAt?: number | string | null;
      }
    | undefined;
  return {
    bestLevels: Number(item?.bestLevels) || 0,
    lastLevels: Number(item?.lastLevels) || 0,
    lastPlayedAt: item?.lastPlayedAt ? Number(item.lastPlayedAt) * 1000 : 0,
  };
};

/**
 * Récupère la progression quotidienne
 */
export const fetchDailyProgress = async (
  db: Pool,
  userId: number,
): Promise<DailyProgress> => {
  await ensureDailyProgress(db, userId);
  const [rows] = await db.execute(
    "SELECT completed, monthly_claims as monthlyClaims FROM daily_progress WHERE user_id = ? LIMIT 1",
    [userId],
  );
  const row = Array.isArray(rows) ? rows[0] : undefined;
  const item = row as
    | { completed?: unknown; monthlyClaims?: unknown }
    | undefined;
  return {
    completed: parseJsonArray(item?.completed),
    monthlyClaims: parseJsonArray(item?.monthlyClaims),
  };
};

/**
 * Récupère les paramètres utilisateur
 */
export const fetchSettings = async (
  db: Pool,
  userId: number,
): Promise<SettingsState> => {
  await ensureUserSettings(db, userId);
  const [rows] = await db.execute(
    "SELECT music, sfx, haptics, theme, animation_speed as animationSpeed, sfx_volume as sfxVolume, music_volume as musicVolume, ball_skin as ballSkin FROM user_settings WHERE user_id = ? LIMIT 1",
    [userId],
  );
  const row = Array.isArray(rows) ? rows[0] : undefined;
  const item = row as
    | {
        music?: number | boolean;
        sfx?: number | boolean;
        haptics?: number | boolean;
        theme?: string;
        animationSpeed?: string;
        sfxVolume?: number | string;
        musicVolume?: number | string;
        ballSkin?: string | null;
      }
    | undefined;
  return {
    music: Boolean(item?.music ?? true),
    sfx: Boolean(item?.sfx ?? true),
    haptics: Boolean(item?.haptics ?? true),
    theme: item?.theme ?? "ocean",
    animationSpeed: item?.animationSpeed ?? "normal",
    sfxVolume: Number(item?.sfxVolume) || 0.7,
    musicVolume: Number(item?.musicVolume) || 0.4,
    ballSkin: item?.ballSkin ?? null,
  };
};

/**
 * Récupère le portefeuille (wallet) de l'utilisateur
 */
export const fetchWallet = async (
  db: Pool,
  userId: number,
): Promise<WalletPayload> => {
  const [rows] = await db.execute(
    "SELECT points, hints, undos, replays FROM wallets WHERE user_id = ? LIMIT 1",
    [userId],
  );
  const wallet = Array.isArray(rows) ? (rows[0] as WalletRow) : null;
  if (!wallet) {
    await ensureWallet(db, userId);
    return { points: 0, inventory: { hints: 0, undos: 0, replays: 0 } };
  }
  return {
    points: clampInt(wallet.points),
    inventory: {
      hints: clampInt(wallet.hints),
      undos: clampInt(wallet.undos),
      replays: clampInt(wallet.replays),
    },
  };
};

/**
 * Récupère la progression par difficulté
 */
export const fetchProgress = async (
  db: Pool,
  userId: number,
): Promise<ProgressPayload> => {
  const map: ProgressPayload = {
    easy: 0,
    medium: 0,
    hard: 0,
    expert: 0,
  };
  const [rows] = await db.execute(
    "SELECT difficulty, completed FROM progress WHERE user_id = ?",
    [userId],
  );
  if (Array.isArray(rows)) {
    rows.forEach((row) => {
      const item = row as { difficulty: DifficultyKey; completed: number };
      if (DIFFICULTIES.includes(item.difficulty)) {
        map[item.difficulty] = clampInt(item.completed, 0, 9999);
      }
    });
  }
  return map;
};

/**
 * Récupère la configuration publique de l'application
 */
export const fetchAppConfig = async (db: Pool) => {
  const [rows] = await db.execute(
    "SELECT config_key as configKey, config_value as configValue FROM app_config WHERE config_key IN ('ui_music_src', 'public_theme', 'public_theme_force', 'ball_skin', 'challenge_pack_enabled', 'launch_date', 'announcement_enabled', 'announcement_message', 'announcement_speed', 'ads_ui_enabled')",
  );
  const config: {
    musicSrc?: string | null;
    publicTheme?: string | null;
    publicThemeForce?: boolean;
    ballSkin?: string | null;
    challengePackEnabled?: boolean;
    launchDate?: string | null;
    announcementEnabled?: boolean;
    announcementMessage?: string | null;
    announcementSpeed?: number;
    adsUiEnabled?: boolean;
  } = {};
  if (Array.isArray(rows)) {
    rows.forEach((row) => {
      const item = row as { configKey?: string; configValue?: string };
      if (item.configKey === "ui_music_src") {
        config.musicSrc = item.configValue ?? null;
      }
      if (item.configKey === "public_theme") {
        config.publicTheme = item.configValue ?? null;
      }
      if (item.configKey === "public_theme_force") {
        const value = (item.configValue ?? "").trim().toLowerCase();
        config.publicThemeForce = value === "1" || value === "true";
      }
      if (item.configKey === "ball_skin") {
        config.ballSkin = item.configValue ?? null;
      }
      if (item.configKey === "challenge_pack_enabled") {
        const value = (item.configValue ?? "").trim().toLowerCase();
        config.challengePackEnabled = value === "1" || value === "true";
      }
      if (item.configKey === "launch_date") {
        config.launchDate = item.configValue ?? null;
      }
      if (item.configKey === "announcement_enabled") {
        const value = (item.configValue ?? "").trim().toLowerCase();
        config.announcementEnabled = value === "1" || value === "true";
      }
      if (item.configKey === "announcement_message") {
        config.announcementMessage = item.configValue ?? null;
      }
      if (item.configKey === "announcement_speed") {
        const parsed = Number(item.configValue);
        if (Number.isFinite(parsed)) {
          config.announcementSpeed = Math.max(20, Math.min(200, parsed));
        }
      }
      if (item.configKey === "ads_ui_enabled") {
        const value = (item.configValue ?? "").trim().toLowerCase();
        config.adsUiEnabled = value === "1" || value === "true";
      }
    });
  }
  if (config.announcementEnabled === undefined) {
    config.announcementEnabled = false;
  }
  if (config.announcementSpeed === undefined) {
    config.announcementSpeed = 72;
  }
  if (config.adsUiEnabled === undefined) {
    config.adsUiEnabled = true;
  }
  return config;
};

/**
 * Récupère la configuration admin (inclut des clés non publiques)
 */
export const fetchAdminConfig = async (db: Pool) => {
  const [rows] = await db.execute(
    "SELECT config_key as configKey, config_value as configValue FROM app_config WHERE config_key IN ('ui_music_src', 'public_theme', 'public_theme_force', 'ball_skin', 'challenge_pack_enabled', 'launch_date', 'maintenance_mode', 'maintenance_message', 'maintenance_end', 'announcement_enabled', 'announcement_message', 'announcement_speed', 'ads_ui_enabled')",
  );
  const config: {
    musicSrc?: string | null;
    publicTheme?: string | null;
    publicThemeForce?: boolean;
    ballSkin?: string | null;
    challengePackEnabled?: boolean;
    launchDate?: string | null;
    maintenanceMode?: boolean;
    maintenanceMessage?: string | null;
    maintenanceEnd?: string | null;
    announcementEnabled?: boolean;
    announcementMessage?: string | null;
    announcementSpeed?: number;
    adsUiEnabled?: boolean;
  } = {};
  if (Array.isArray(rows)) {
    rows.forEach((row) => {
      const item = row as { configKey?: string; configValue?: string };
      const key = item.configKey;
      const value = (item.configValue ?? "").trim();
      const lower = value.toLowerCase();

      if (key === "ui_music_src") config.musicSrc = item.configValue ?? null;
      if (key === "public_theme") config.publicTheme = item.configValue ?? null;
      if (key === "public_theme_force") {
        config.publicThemeForce = lower === "1" || lower === "true";
      }
      if (key === "ball_skin") config.ballSkin = item.configValue ?? null;
      if (key === "challenge_pack_enabled") {
        config.challengePackEnabled = lower === "1" || lower === "true";
      }
      if (key === "launch_date") config.launchDate = item.configValue ?? null;
      if (key === "maintenance_mode") {
        config.maintenanceMode = lower === "1" || lower === "true";
      }
      if (key === "maintenance_message") {
        config.maintenanceMessage = item.configValue ?? null;
      }
      if (key === "maintenance_end") {
        config.maintenanceEnd = item.configValue ?? null;
      }
      if (key === "announcement_enabled") {
        config.announcementEnabled = lower === "1" || lower === "true";
      }
      if (key === "announcement_message") {
        config.announcementMessage = item.configValue ?? null;
      }
      if (key === "announcement_speed") {
        const parsed = Number(item.configValue);
        if (Number.isFinite(parsed)) {
          config.announcementSpeed = Math.max(20, Math.min(200, parsed));
        }
      }
      if (key === "ads_ui_enabled") {
        config.adsUiEnabled = lower === "1" || lower === "true";
      }
    });
  }

  if (config.maintenanceMode === undefined) config.maintenanceMode = false;
  if (config.maintenanceMessage === undefined)
    config.maintenanceMessage = "Mise à jour en cours. Veuillez patienter...";
  if (config.announcementEnabled === undefined) config.announcementEnabled = false;
  if (config.announcementSpeed === undefined) config.announcementSpeed = 72;
  if (config.adsUiEnabled === undefined) config.adsUiEnabled = true;

  return config;
};

/**
 * Définit une valeur de configuration
 */
export const setAppConfigValue = async (
  db: Pool,
  key: string,
  value?: string | null,
) => {
  const cleaned = value?.trim() ?? "";
  if (!cleaned) {
    await db.execute("DELETE FROM app_config WHERE config_key = ? LIMIT 1", [
      key,
    ]);
    return;
  }
  await db.execute(
    "INSERT INTO app_config (config_key, config_value) VALUES (?, ?) ON DUPLICATE KEY UPDATE config_value = VALUES(config_value)",
    [key, cleaned],
  );
};