import type { Pool } from "mysql2/promise";
import {
  LEVELS_PER_DIFFICULTY as CATALOG_LEVELS,
  difficultyOrder as CATALOG_DIFFICULTIES,
  getLevel,
  getLevelSeed,
  buildLevelSignature,
} from "../level-generator.js";
import { DIFFICULTIES } from "./constants.js";

export const ensureSchema = async (db: Pool) => {
  await db.execute(`
    CREATE TABLE IF NOT EXISTS users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      email VARCHAR(255) NOT NULL UNIQUE,
      password_hash VARCHAR(255),
      failed_login_attempts INT DEFAULT 0,
      locked_until DATETIME NULL,
      reset_token_hash CHAR(64) NULL,
      reset_token_expires DATETIME NULL,
      email_verified BOOLEAN NOT NULL DEFAULT TRUE,
      email_verified_at DATETIME NULL,
      email_verify_token_hash CHAR(64) NULL,
      email_verify_token_expires DATETIME NULL,
      display_name VARCHAR(100),
      avatar VARCHAR(100) DEFAULT NULL,
      accent VARCHAR(50),
      title VARCHAR(80),
      motto VARCHAR(200),
      guest BOOLEAN DEFAULT FALSE,
      is_admin BOOLEAN DEFAULT FALSE,
      banned_at DATETIME DEFAULT NULL,
      ban_reason VARCHAR(500) DEFAULT NULL,
      ban_until DATETIME DEFAULT NULL,
      vip_no_ads BOOLEAN DEFAULT FALSE,
      vip_expires_at DATETIME DEFAULT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS wallets (
      user_id INT PRIMARY KEY,
      points INT DEFAULT 0,
      hints INT DEFAULT 0,
      undos INT DEFAULT 0,
      replays INT DEFAULT 0,
      bonus_points INT DEFAULT 0,
      last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS progress (
      user_id INT,
      difficulty ENUM('easy','medium','hard','expert') NOT NULL,
      completed INT DEFAULT 0,
      PRIMARY KEY (user_id, difficulty),
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS refresh_tokens (
      id INT AUTO_INCREMENT PRIMARY KEY,
      user_id INT NOT NULL,
      token_hash CHAR(64) NOT NULL UNIQUE,
      expires_at DATETIME NOT NULL,
      revoked_at DATETIME NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_refresh_user (user_id),
      INDEX idx_refresh_expires (expires_at),
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS audit_log (
      id INT AUTO_INCREMENT PRIMARY KEY,
      user_id INT NULL,
      action VARCHAR(80) NOT NULL,
      meta JSON NULL,
      installation_id VARCHAR(64) DEFAULT NULL,
      ip VARCHAR(64),
      user_agent VARCHAR(255),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_audit_user (user_id),
      INDEX idx_audit_created (created_at),
      INDEX idx_audit_install (installation_id),
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS user_cash_pack_purchases (
      id INT AUTO_INCREMENT PRIMARY KEY,
      user_id INT NOT NULL,
      pack_id VARCHAR(50) NOT NULL,
      pack_name VARCHAR(100) NOT NULL,
      price_eur DECIMAL(10,2) NOT NULL,
      items_hints INT DEFAULT 0,
      items_undos INT DEFAULT 0,
      items_replays INT DEFAULT 0,
      bonus_points INT DEFAULT 0,
      stripe_session_id VARCHAR(255) DEFAULT NULL,
      stripe_payment_intent_id VARCHAR(255) DEFAULT NULL,
      status VARCHAR(50) DEFAULT 'pending',
      purchased_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      processed_at TIMESTAMP NULL,
      INDEX idx_user (user_id),
      INDEX idx_pack (pack_id),
      INDEX idx_stripe_session (stripe_session_id),
      INDEX idx_status (status),
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS user_stats (
      user_id INT PRIMARY KEY,
      total_completions INT DEFAULT 0,
      total_moves INT DEFAULT 0,
      total_time INT DEFAULT 0,
      tutorial_completed BOOLEAN DEFAULT FALSE,
      badges JSON NULL,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS level_stats (
      user_id INT NOT NULL,
      difficulty ENUM('easy','medium','hard','expert') NOT NULL,
      level_id INT NOT NULL,
      completions INT DEFAULT 0,
      last_moves INT DEFAULT 0,
      last_time INT DEFAULT 0,
      best_moves INT NULL,
      best_time INT NULL,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (user_id, difficulty, level_id),
      INDEX idx_level_user (user_id),
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS recent_runs (
      id INT AUTO_INCREMENT PRIMARY KEY,
      user_id INT NOT NULL,
      difficulty ENUM('easy','medium','hard','expert') NOT NULL,
      level_id INT NOT NULL,
      moves INT NOT NULL,
      time INT NOT NULL,
      completed_at DATETIME NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_recent_user (user_id),
      INDEX idx_recent_created (created_at),
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS arcade_stats (
      user_id INT PRIMARY KEY,
      best_score INT DEFAULT 0,
      best_levels INT DEFAULT 0,
      last_score INT DEFAULT 0,
      last_levels INT DEFAULT 0,
      last_played_at DATETIME NULL,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS infinite_stats (
      user_id INT PRIMARY KEY,
      best_levels INT DEFAULT 0,
      last_levels INT DEFAULT 0,
      last_played_at DATETIME NULL,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS daily_progress (
      user_id INT PRIMARY KEY,
      completed JSON NULL,
      monthly_claims JSON NULL,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS user_settings (
      user_id INT PRIMARY KEY,
      music BOOLEAN DEFAULT TRUE,
      sfx BOOLEAN DEFAULT TRUE,
      haptics BOOLEAN DEFAULT TRUE,
      theme VARCHAR(20) DEFAULT 'ocean',
      animation_speed VARCHAR(20) DEFAULT 'normal',
      sfx_volume DECIMAL(4,2) DEFAULT 0.7,
      music_volume DECIMAL(4,2) DEFAULT 0.4,
      ball_skin VARCHAR(50) DEFAULT NULL,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS user_privacy_consents (
      user_id INT PRIMARY KEY,
      consent_version VARCHAR(20) NOT NULL,
      consent_status VARCHAR(20) NOT NULL,
      ads_consent BOOLEAN NOT NULL DEFAULT FALSE,
      personalized_ads_consent BOOLEAN NOT NULL DEFAULT FALSE,
      analytics_consent BOOLEAN NOT NULL DEFAULT FALSE,
      consent_source VARCHAR(40) DEFAULT 'app',
      installation_id VARCHAR(64) DEFAULT NULL,
      ip VARCHAR(64) DEFAULT NULL,
      user_agent VARCHAR(255) DEFAULT NULL,
      granted_at DATETIME NOT NULL,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
      INDEX idx_privacy_status (consent_status),
      INDEX idx_privacy_updated (updated_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS avatar_packs (
      id INT AUTO_INCREMENT PRIMARY KEY,
      pack_id VARCHAR(50) NOT NULL,
      name VARCHAR(100) NOT NULL,
      description TEXT DEFAULT NULL,
      price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
      folder_path VARCHAR(255) NOT NULL,
      avatar_count INT DEFAULT 0,
      is_active TINYINT(1) DEFAULT 1,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      UNIQUE KEY uniq_pack_id (pack_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS ball_skin_packs (
      id INT AUTO_INCREMENT PRIMARY KEY,
      pack_id VARCHAR(50) NOT NULL,
      name VARCHAR(100) NOT NULL,
      description TEXT DEFAULT NULL,
      price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
      skin_ids JSON NOT NULL,
      skin_count INT DEFAULT 0,
      is_active TINYINT(1) DEFAULT 1,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      UNIQUE KEY uniq_ball_pack_id (pack_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS user_avatar_packs (
      user_id INT NOT NULL,
      pack_id VARCHAR(50) NOT NULL,
      purchased_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      transaction_id VARCHAR(255) DEFAULT NULL,
      price_paid DECIMAL(10,2) NOT NULL,
      PRIMARY KEY (user_id, pack_id),
      INDEX idx_user_avatar_packs_user (user_id),
      INDEX idx_user_avatar_packs_pack (pack_id),
      CONSTRAINT fk_user_avatar_packs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS user_ball_skin_packs (
      user_id INT NOT NULL,
      pack_id VARCHAR(50) NOT NULL,
      purchased_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      transaction_id VARCHAR(255) DEFAULT NULL,
      price_paid DECIMAL(10,2) NOT NULL,
      PRIMARY KEY (user_id, pack_id),
      INDEX idx_user_ball_packs_user (user_id),
      INDEX idx_user_ball_packs_pack (pack_id),
      CONSTRAINT fk_user_ball_packs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS user_ball_skins (
      user_id INT NOT NULL,
      skin_id VARCHAR(50) NOT NULL,
      source_pack VARCHAR(50) DEFAULT NULL,
      unlocked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (user_id, skin_id),
      INDEX idx_user_ball_skins_user (user_id),
      CONSTRAINT fk_user_ball_skins_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS payment_transactions (
      id INT AUTO_INCREMENT PRIMARY KEY,
      user_id INT NOT NULL,
      pack_id VARCHAR(50) NOT NULL,
      pack_type VARCHAR(30) NOT NULL DEFAULT 'avatar',
      provider VARCHAR(20) NOT NULL,
      status VARCHAR(20) NOT NULL,
      amount DECIMAL(10,2) NOT NULL,
      currency VARCHAR(10) NOT NULL,
      stripe_session_id VARCHAR(255) DEFAULT NULL,
      stripe_payment_intent_id VARCHAR(255) DEFAULT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      UNIQUE KEY uniq_payment_session (stripe_session_id),
      INDEX idx_payment_user (user_id),
      INDEX idx_payment_pack (pack_id),
      CONSTRAINT fk_payment_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS avatars (
      id INT AUTO_INCREMENT PRIMARY KEY,
      code VARCHAR(50) NOT NULL,
      category ENUM('base','recompense','achat') NOT NULL,
      file_path VARCHAR(255) NOT NULL,
      price INT DEFAULT 0,
      unlock_condition JSON DEFAULT NULL COMMENT 'Conditions de déblocage pour les récompenses',
      display_order INT DEFAULT 0,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      UNIQUE KEY code (code),
      INDEX idx_avatar_category (category)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS pack_avatars (
      pack_id VARCHAR(50) NOT NULL,
      avatar_id INT NOT NULL,
      display_order INT DEFAULT 0,
      PRIMARY KEY (pack_id, avatar_id),
      INDEX idx_pack_avatars_pack (pack_id),
      INDEX idx_pack_avatars_avatar (avatar_id),
      CONSTRAINT fk_pack_avatars_pack FOREIGN KEY (pack_id) REFERENCES avatar_packs(pack_id) ON DELETE CASCADE,
      CONSTRAINT fk_pack_avatars_avatar FOREIGN KEY (avatar_id) REFERENCES avatars(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS user_avatars (
      user_id INT NOT NULL,
      avatar_id INT NOT NULL,
      source VARCHAR(50) DEFAULT 'unknown',
      metadata JSON DEFAULT NULL,
      unlocked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (user_id, avatar_id),
      INDEX idx_user_avatars_user (user_id),
      INDEX idx_user_avatars_avatar (avatar_id),
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
      FOREIGN KEY (avatar_id) REFERENCES avatars(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS ad_rewards (
      user_id INT NOT NULL,
      reward_date DATE NOT NULL,
      watched_count INT DEFAULT 0,
      hints_earned INT DEFAULT 0,
      bonus_points_earned INT DEFAULT 0,
      last_watched_at DATETIME DEFAULT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (user_id, reward_date),
      INDEX idx_ad_rewards_date (reward_date),
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS ad_reward_events (
      id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      user_id INT NOT NULL,
      reward_date DATE NOT NULL,
      ad_type VARCHAR(32) NOT NULL COMMENT 'daily_reward, double_points, free_solution',
      source VARCHAR(16) NOT NULL DEFAULT 'ad' COMMENT 'ad, vip',
      reward_status VARCHAR(16) NOT NULL DEFAULT 'claimed' COMMENT 'started, claimed, expired, rejected',
      session_nonce VARCHAR(96) DEFAULT NULL,
      idempotency_key VARCHAR(128) DEFAULT NULL,
      reward_amount INT NOT NULL DEFAULT 0 COMMENT 'points/hints selon ad_type',
      watched_count_after INT DEFAULT NULL,
      expires_at DATETIME DEFAULT NULL,
      claimed_at DATETIME DEFAULT NULL,
      meta JSON DEFAULT NULL,
      ip VARCHAR(45) DEFAULT NULL,
      user_agent VARCHAR(255) DEFAULT NULL,
      installation_id VARCHAR(64) DEFAULT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_ad_reward_events_user_date (user_id, reward_date),
      INDEX idx_ad_reward_events_type_date (ad_type, reward_date),
      UNIQUE KEY uniq_ad_reward_events_session (session_nonce),
      UNIQUE KEY uniq_ad_reward_events_idempotency (user_id, idempotency_key),
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  // Compat migration: enrichir ad_reward_events si la table existait avant le protocole start/claim.
  await ensureTableColumn(
    db,
    "ad_reward_events",
    "reward_status",
    "ALTER TABLE ad_reward_events ADD COLUMN reward_status VARCHAR(16) NOT NULL DEFAULT 'claimed' COMMENT 'started, claimed, expired, rejected' AFTER source",
  );
  await ensureTableColumn(
    db,
    "ad_reward_events",
    "session_nonce",
    "ALTER TABLE ad_reward_events ADD COLUMN session_nonce VARCHAR(96) DEFAULT NULL AFTER reward_status",
  );
  await ensureTableColumn(
    db,
    "ad_reward_events",
    "idempotency_key",
    "ALTER TABLE ad_reward_events ADD COLUMN idempotency_key VARCHAR(128) DEFAULT NULL AFTER session_nonce",
  );
  await ensureTableColumn(
    db,
    "ad_reward_events",
    "expires_at",
    "ALTER TABLE ad_reward_events ADD COLUMN expires_at DATETIME DEFAULT NULL AFTER watched_count_after",
  );
  await ensureTableColumn(
    db,
    "ad_reward_events",
    "claimed_at",
    "ALTER TABLE ad_reward_events ADD COLUMN claimed_at DATETIME DEFAULT NULL AFTER expires_at",
  );
  await ensureTableIndex(
    db,
    "ad_reward_events",
    "uniq_ad_reward_events_session",
    "ALTER TABLE ad_reward_events ADD UNIQUE KEY uniq_ad_reward_events_session (session_nonce)",
  );
  await ensureTableIndex(
    db,
    "ad_reward_events",
    "uniq_ad_reward_events_idempotency",
    "ALTER TABLE ad_reward_events ADD UNIQUE KEY uniq_ad_reward_events_idempotency (user_id, idempotency_key)",
  );

  await db.execute(`
    CREATE TABLE IF NOT EXISTS daily_bonus (
      user_id INT PRIMARY KEY,
      last_claim_date DATE DEFAULT NULL,
      consecutive_days INT DEFAULT 0,
      total_hints_earned INT DEFAULT 0,
      total_solutions_earned INT DEFAULT 0,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS daily_challenges (
      date_key DATE NOT NULL PRIMARY KEY,
      difficulty VARCHAR(20) DEFAULT 'medium',
      level_index INT NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS arcade_bonus (
      user_id INT PRIMARY KEY,
      total_games_played INT DEFAULT 0,
      last_bonus_at INT DEFAULT 0,
      total_hints_earned INT DEFAULT 0,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS used_weekly_avatars (
      avatar_id INT NOT NULL PRIMARY KEY COMMENT 'ID de l avatar (1-380)',
      used_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS user_weekly_avatars (
      user_id INT NOT NULL,
      avatar_id INT NOT NULL COMMENT 'ID de l avatar animal (1-380)',
      week_key VARCHAR(10) DEFAULT NULL,
      obtained_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      obtained_from VARCHAR(50) DEFAULT NULL,
      PRIMARY KEY (user_id, avatar_id),
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS user_challenge_pack (
      user_id INT NOT NULL PRIMARY KEY,
      purchased_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      transaction_id VARCHAR(255) DEFAULT NULL,
      price_paid DECIMAL(10,2) DEFAULT 9.99,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS weekly_progress (
      user_id INT NOT NULL,
      week_key VARCHAR(10) NOT NULL COMMENT 'Format: YYYY-WXX',
      completed_days JSON DEFAULT NULL COMMENT 'Liste des jours complétés [1,2,3...]',
      reward_claimed BOOLEAN DEFAULT FALSE,
      reward_type ENUM('avatar','points') DEFAULT NULL COMMENT 'Type de récompense reçue',
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (user_id, week_key),
      INDEX idx_weekly_progress_week (week_key),
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS weekly_rewards (
      week_key VARCHAR(10) NOT NULL PRIMARY KEY COMMENT 'Format: YYYY-WXX (ex: 2026-W05)',
      avatar_id INT NOT NULL COMMENT 'ID de l avatar (1-380)',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      UNIQUE KEY idx_weekly_avatar (avatar_id, week_key)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS wallet_history (
      id INT AUTO_INCREMENT PRIMARY KEY,
      user_id INT NOT NULL,
      resource ENUM('points','hints','undos','replays','bonus_points') NOT NULL,
      delta INT NOT NULL,
      balance_before INT NOT NULL DEFAULT 0,
      balance_after INT NOT NULL DEFAULT 0,
      source VARCHAR(80) NOT NULL,
      meta JSON DEFAULT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_wh_user (user_id, created_at),
      INDEX idx_wh_source (source, created_at),
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS app_config (
      config_key VARCHAR(40) PRIMARY KEY,
      config_value TEXT NOT NULL,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS levels_catalog (
      id INT AUTO_INCREMENT PRIMARY KEY,
      difficulty ENUM('easy','medium','hard','expert') NOT NULL,
      level_index INT NOT NULL,
      level_id INT NOT NULL,
      seed INT NOT NULL,
      signature CHAR(64) NOT NULL,
      payload JSON NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      UNIQUE KEY uniq_level (difficulty, level_index),
      INDEX idx_level_signature (signature)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await db.execute(`
    CREATE TABLE IF NOT EXISTS leaderboard_entries (
      id INT AUTO_INCREMENT PRIMARY KEY,
      user_id INT NOT NULL,
      difficulty ENUM('easy','medium','hard','expert') NOT NULL,
      level_index INT NOT NULL,
      time_ms INT NOT NULL,
      moves INT NOT NULL,
      score INT GENERATED ALWAYS AS (GREATEST(100, 10000 - FLOOR(time_ms/1000)*100 - moves*10)) STORED,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_leaderboard_global (score DESC),
      INDEX idx_leaderboard_difficulty (difficulty, score DESC),
      INDEX idx_leaderboard_level (difficulty, level_index, score DESC),
      INDEX idx_leaderboard_user (user_id),
      UNIQUE KEY uniq_user_level (user_id, difficulty, level_index),
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);
};

export const ensureAdminColumn = async (db: Pool) => {
  const [rows] = await db.query(
    "SELECT COUNT(*) as total FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'is_admin'",
  );
  const count =
    Array.isArray(rows) && rows[0]
      ? Number((rows[0] as { total?: number | string }).total) || 0
      : 0;
  if (count === 0) {
    await db.execute(
      "ALTER TABLE users ADD COLUMN is_admin BOOLEAN DEFAULT FALSE",
    );
  }
};

export const ensureVipNoAdsColumn = async (db: Pool) => {
  await ensureColumn(
    db,
    "vip_no_ads",
    "ALTER TABLE users ADD COLUMN vip_no_ads BOOLEAN DEFAULT FALSE",
  );
  await ensureColumn(
    db,
    "vip_expires_at",
    "ALTER TABLE users ADD COLUMN vip_expires_at DATETIME DEFAULT NULL",
  );
};

export const ensureColumn = async (db: Pool, column: string, ddl: string) => {
  const [rows] = await db.query(
    "SELECT COUNT(*) as total FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = ?",
    [column],
  );
  const count =
    Array.isArray(rows) && rows[0]
      ? Number((rows[0] as { total?: number | string }).total) || 0
      : 0;
  if (count === 0) {
    await db.execute(ddl);
  }
};

const ensureTableColumn = async (
  db: Pool,
  table: string,
  column: string,
  ddl: string,
) => {
  const [rows] = await db.query(
    "SELECT COUNT(*) as total FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?",
    [table, column],
  );
  const count =
    Array.isArray(rows) && rows[0]
      ? Number((rows[0] as { total?: number | string }).total) || 0
      : 0;
  if (count === 0) {
    await db.execute(ddl);
  }
};

const ensureTableIndex = async (
  db: Pool,
  table: string,
  index: string,
  ddl: string,
) => {
  const [rows] = await db.query(
    "SELECT COUNT(*) as total FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND INDEX_NAME = ?",
    [table, index],
  );
  const count =
    Array.isArray(rows) && rows[0]
      ? Number((rows[0] as { total?: number | string }).total) || 0
      : 0;
  if (count === 0) {
    await db.execute(ddl);
  }
};

export const ensureUserSettingsColumn = async (
  db: Pool,
  column: string,
  ddl: string,
) => {
  const [rows] = await db.query(
    "SELECT COUNT(*) as total FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_settings' AND COLUMN_NAME = ?",
    [column],
  );
  const count =
    Array.isArray(rows) && rows[0]
      ? Number((rows[0] as { total?: number | string }).total) || 0
      : 0;
  if (count === 0) {
    await db.execute(ddl);
  }
};

export const ensureAuthColumns = async (db: Pool) => {
  await ensureColumn(
    db,
    "password_hash",
    "ALTER TABLE users ADD COLUMN password_hash VARCHAR(255)",
  );
  await ensureColumn(
    db,
    "avatar",
    "ALTER TABLE users ADD COLUMN avatar VARCHAR(10)",
  );
  await ensureColumn(
    db,
    "failed_login_attempts",
    "ALTER TABLE users ADD COLUMN failed_login_attempts INT DEFAULT 0",
  );
  await ensureColumn(
    db,
    "locked_until",
    "ALTER TABLE users ADD COLUMN locked_until DATETIME NULL",
  );
  await ensureColumn(
    db,
    "reset_token_hash",
    "ALTER TABLE users ADD COLUMN reset_token_hash CHAR(64) NULL",
  );
  await ensureColumn(
    db,
    "reset_token_expires",
    "ALTER TABLE users ADD COLUMN reset_token_expires DATETIME NULL",
  );
  await ensureColumn(
    db,
    "email_verified",
    "ALTER TABLE users ADD COLUMN email_verified BOOLEAN NOT NULL DEFAULT TRUE",
  );
  await ensureColumn(
    db,
    "email_verified_at",
    "ALTER TABLE users ADD COLUMN email_verified_at DATETIME NULL",
  );
  await ensureColumn(
    db,
    "email_verify_token_hash",
    "ALTER TABLE users ADD COLUMN email_verify_token_hash CHAR(64) NULL",
  );
  await ensureColumn(
    db,
    "email_verify_token_expires",
    "ALTER TABLE users ADD COLUMN email_verify_token_expires DATETIME NULL",
  );
  await ensureTableIndex(
    db,
    "users",
    "idx_users_email_verify_token_hash",
    "CREATE INDEX idx_users_email_verify_token_hash ON users(email_verify_token_hash)",
  );
};

export const ensureUserSettingsColumns = async (db: Pool) => {
  await ensureUserSettingsColumn(
    db,
    "ball_skin",
    "ALTER TABLE user_settings ADD COLUMN ball_skin VARCHAR(50) DEFAULT NULL",
  );
};

export const ensureWallet = async (db: Pool, userId: number) => {
  await db.execute("INSERT IGNORE INTO wallets (user_id) VALUES (?)", [userId]);
};

export const ensureProgressRows = async (db: Pool, userId: number) => {
  await Promise.all(
    DIFFICULTIES.map((difficulty) =>
      db.execute(
        "INSERT IGNORE INTO progress (user_id, difficulty) VALUES (?, ?)",
        [userId, difficulty],
      ),
    ),
  );
};

export const ensureUserStats = async (db: Pool, userId: number) => {
  await db.execute(
    "INSERT IGNORE INTO user_stats (user_id, badges) VALUES (?, ?)",
    [userId, JSON.stringify([])],
  );
};

export const ensureArcadeStats = async (db: Pool, userId: number) => {
  await db.execute("INSERT IGNORE INTO arcade_stats (user_id) VALUES (?)", [
    userId,
  ]);
};

export const ensureInfiniteStats = async (db: Pool, userId: number) => {
  await db.execute("INSERT IGNORE INTO infinite_stats (user_id) VALUES (?)", [
    userId,
  ]);
};

export const ensureDailyProgress = async (db: Pool, userId: number) => {
  await db.execute(
    "INSERT IGNORE INTO daily_progress (user_id, completed, monthly_claims) VALUES (?, ?, ?)",
    [userId, JSON.stringify([]), JSON.stringify([])],
  );
};

export const ensureUserSettings = async (db: Pool, userId: number) => {
  await db.execute("INSERT IGNORE INTO user_settings (user_id) VALUES (?)", [
    userId,
  ]);
};

export const ensureLaunchDateSeed = async (db: Pool) => {
  await db.execute(
    // launch_date est volontairement initialisé à une valeur non parseable tant que tu n'as pas fixé ta date de lancement.
    // Cela évite d'attribuer des badges limités par erreur avant le go-live.
    "INSERT INTO app_config (config_key, config_value) VALUES ('launch_date', 'unset') ON DUPLICATE KEY UPDATE config_value = config_value",
  );
};

export const ensureAvatarPacksSeed = async (db: Pool) => {
  const [rows] = await db.query("SELECT COUNT(*) as total FROM avatar_packs");
  const total =
    Array.isArray(rows) && rows[0]
      ? Number((rows[0] as { total?: number | string }).total) || 0
      : 0;
  if (total > 0) {
    return;
  }
  await db.execute(
    `INSERT INTO avatar_packs (pack_id, name, description, price, folder_path, avatar_count, is_active)
     VALUES (?, ?, ?, ?, ?, ?, ?)`,
    [
      "cool",
      "Pack Avatars Cool",
      "Une collection exclusive de 19 avatars fun et colorés !",
      23.99,
      "avatars/avatard_cool",
      19,
      1,
    ],
  );
};

export const ensureBallSkinPacksSeed = async (db: Pool) => {
  const [rows] = await db.query(
    "SELECT COUNT(*) as total FROM ball_skin_packs",
  );
  const total =
    Array.isArray(rows) && rows[0]
      ? Number((rows[0] as { total?: number | string }).total) || 0
      : 0;
  if (total > 0) {
    return;
  }

  const elementaires = Array.from(
    { length: 10 },
    (_, i) => `elementaire_${i + 1}`,
  );
  const halloween = Array.from({ length: 10 }, (_, i) => `halloween_${i + 1}`);
  const varie = Array.from({ length: 20 }, (_, i) => `varie_${i + 1}`);

  await db.execute(
    `INSERT INTO ball_skin_packs (pack_id, name, description, price, skin_ids, skin_count, is_active)
     VALUES (?, ?, ?, ?, ?, ?, ?)`,
    [
      "elementaires",
      "Pack Billes Élémentaires",
      "10 billes élémentaires uniques.",
      0.99,
      JSON.stringify(elementaires),
      elementaires.length,
      1,
    ],
  );

  await db.execute(
    `INSERT INTO ball_skin_packs (pack_id, name, description, price, skin_ids, skin_count, is_active)
     VALUES (?, ?, ?, ?, ?, ?, ?)`,
    [
      "halloween",
      "Pack Billes Halloween",
      "10 billes Halloween à l'effigie de la saison.",
      1.49,
      JSON.stringify(halloween),
      halloween.length,
      1,
    ],
  );

  await db.execute(
    `INSERT INTO ball_skin_packs (pack_id, name, description, price, skin_ids, skin_count, is_active)
     VALUES (?, ?, ?, ?, ?, ?, ?)`,
    [
      "folie",
      "Pack Billes Folie",
      "20 billes variées pour un style complètement fou.",
      2.49,
      JSON.stringify(varie),
      varie.length,
      1,
    ],
  );
};

export const ensureUserData = async (db: Pool, userId: number) => {
  await Promise.all([
    ensureWallet(db, userId),
    ensureProgressRows(db, userId),
    ensureUserStats(db, userId),
    ensureArcadeStats(db, userId),
    ensureInfiniteStats(db, userId),
    ensureDailyProgress(db, userId),
    ensureUserSettings(db, userId),
  ]);
};

export type SeedLevelsCatalogOptions = {
  force?: boolean;
  backupBeforeReplace?: boolean;
  resetRunAndLeaderboardData?: boolean;
  backupTableName?: string;
  log?: (message: string) => void;
};

export type SeedLevelsCatalogResult = {
  forced: boolean;
  expected: number;
  previousTotal: number;
  insertedOrUpdated: number;
  backupTableName: string | null;
  resetRunAndLeaderboardData: boolean;
};

const isSafeSqlIdentifier = (value: string): boolean => {
  return /^[A-Za-z0-9_]+$/.test(value);
};

const buildBackupTableName = (): string => {
  const stamp = new Date().toISOString().replace(/\D/g, "").slice(0, 14);
  return `levels_catalog_backup_${stamp}`;
};

export const seedLevelsCatalog = async (
  db: Pool,
  options: SeedLevelsCatalogOptions = {},
): Promise<SeedLevelsCatalogResult> => {
  const forced = options.force === true;
  const backupBeforeReplace =
    options.backupBeforeReplace === undefined
      ? forced
      : options.backupBeforeReplace;
  const resetRunAndLeaderboardData = options.resetRunAndLeaderboardData === true;
  const log = options.log ?? (() => undefined);
  const [rows] = await db.query("SELECT COUNT(*) as total FROM levels_catalog");
  const total =
    Array.isArray(rows) && rows[0]
      ? Number((rows[0] as { total?: number | string }).total) || 0
      : 0;
  const expected = CATALOG_LEVELS * CATALOG_DIFFICULTIES.length;
  if (!forced && total >= expected) {
    return {
      forced,
      expected,
      previousTotal: total,
      insertedOrUpdated: 0,
      backupTableName: null,
      resetRunAndLeaderboardData: false,
    };
  }

  let backupTableName: string | null = null;
  if (forced) {
    if (backupBeforeReplace && total > 0) {
      backupTableName = options.backupTableName?.trim() || buildBackupTableName();
      if (!isSafeSqlIdentifier(backupTableName)) {
        throw new Error(`Nom de table de backup invalide: ${backupTableName}`);
      }
      await db.execute(`DROP TABLE IF EXISTS \`${backupTableName}\``);
      await db.execute(
        `CREATE TABLE \`${backupTableName}\` LIKE levels_catalog`,
      );
      await db.execute(
        `INSERT INTO \`${backupTableName}\` SELECT * FROM levels_catalog`,
      );
      log(`Backup levels_catalog -> ${backupTableName}`);
    }

    await db.execute("DELETE FROM levels_catalog");
    log("Table levels_catalog vidée.");

    if (resetRunAndLeaderboardData) {
      await db.execute("DELETE FROM leaderboard_entries");
      await db.execute("DELETE FROM recent_runs");
      await db.execute("DELETE FROM level_stats");
      log("Tables leaderboard_entries, recent_runs, level_stats vidées.");
    }
  }

  let insertedOrUpdated = 0;
  for (const difficulty of CATALOG_DIFFICULTIES) {
    for (let index = 0; index < CATALOG_LEVELS; index += 1) {
      const level = getLevel(difficulty, index);
      const signature = buildLevelSignature(level);
      const seed = getLevelSeed(difficulty, index);
      await db.execute(
        `INSERT INTO levels_catalog (difficulty, level_index, level_id, seed, signature, payload)
         VALUES (?, ?, ?, ?, ?, ?)
         ON DUPLICATE KEY UPDATE
           level_id = VALUES(level_id),
           seed = VALUES(seed),
           signature = VALUES(signature),
           payload = VALUES(payload)`,
        [difficulty, index, level.id, seed, signature, JSON.stringify(level)],
      );
      insertedOrUpdated += 1;
    }
  }

  return {
    forced,
    expected,
    previousTotal: total,
    insertedOrUpdated,
    backupTableName,
    resetRunAndLeaderboardData,
  };
};

// Migration pour corriger la formule de score du leaderboard
export const migrateLeaderboardScore = async (db: Pool) => {
  try {
    // Vérifier si la table existe
    const [tables] = await db.query(
      "SELECT COUNT(*) as cnt FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'leaderboard_entries'",
    );
    const tableExists =
      Array.isArray(tables) && tables[0]
        ? Number((tables[0] as { cnt?: number }).cnt) > 0
        : false;

    if (!tableExists) return;

    // Modifier la colonne GENERATED pour utiliser la nouvelle formule
    // On doit d'abord supprimer puis recréer la colonne
    await db.execute(`
      ALTER TABLE leaderboard_entries 
      DROP COLUMN score
    `);
    await db.execute(`
      ALTER TABLE leaderboard_entries 
      ADD COLUMN score INT GENERATED ALWAYS AS (GREATEST(100, 10000 - FLOOR(time_ms/1000)*100 - moves*10)) STORED
      AFTER moves
    `);
    // Recréer les index
    await db
      .execute(
        `
      ALTER TABLE leaderboard_entries
      ADD INDEX idx_leaderboard_global (score DESC),
      ADD INDEX idx_leaderboard_difficulty (difficulty, score DESC),
      ADD INDEX idx_leaderboard_level (difficulty, level_index, score DESC)
    `,
      )
      .catch(() => {
        // Index peut déjà exister
      });
    console.log("Leaderboard score formula migrated successfully");
  } catch (error) {
    // La migration a peut-être déjà été faite ou la colonne n'existe pas encore
    console.log("Leaderboard score migration skipped or already done");
  }
};