import { describe, it, expect, beforeEach, afterEach } from "vitest";
import mysql from "mysql2/promise";

const shouldRun = process.env.RUN_DB_TESTS === "true";
const maybeDescribe = shouldRun ? describe : describe.skip;

const readEnv = (key: string): string => {
  const value = process.env[key];
  if (!value) {
    throw new Error(`Missing env var: ${key}`);
  }
  return value;
};

maybeDescribe("leaderboard routes", () => {
  let pool: mysql.Pool;

  beforeEach(async () => {
    const host = readEnv("DB_HOST");
    const port = Number(process.env.DB_PORT ?? "3306");
    const user = readEnv("DB_USER");
    const database = readEnv("DB_NAME");
    const password = readEnv("DB_PASSWORD");

    pool = mysql.createPool({
      host,
      port,
      user,
      password,
      database,
      connectionLimit: 1,
    });
  });

  afterEach(async () => {
    await pool.end();
  });

  it("calcule les scores du leaderboard correctement", async () => {
    const email = `leaderboard+${Date.now()}@test.local`;
    const [registerResult] = await pool.execute(
      "INSERT INTO users (email, display_name, password_hash) VALUES (?, ?, ?)",
      [email, "Leaderboard Tester", "dummy"],
    );
    const userId = (registerResult as any).insertId;

    // Ajoute des entrées au leaderboard (score est GENERATED)
    await pool.execute(
      "INSERT INTO leaderboard_entries (user_id, difficulty, level_index, time_ms, moves) VALUES (?, ?, ?, ?, ?)",
      [userId, "easy", 1, 15000, 25],
    );
    await pool.execute(
      "INSERT INTO leaderboard_entries (user_id, difficulty, level_index, time_ms, moves) VALUES (?, ?, ?, ?, ?)",
      [userId, "easy", 2, 16000, 24],
    );

    // Récupère le score agrégé (score = MAX(100, 10000 - floor(time_ms/1000)*100 - moves*10))
    // Entry 1: MAX(100, 10000 - 1500 - 250) = 8250
    // Entry 2: MAX(100, 10000 - 1600 - 240) = 8160
    const [rows] = await pool.execute(
      `
      SELECT 
        SUM(score) as total_score,
        COUNT(*) as levels_completed
      FROM leaderboard_entries
      WHERE user_id = ?
      `,
      [userId],
    );

    const result = (rows as any[])[0];
    expect(Number(result.total_score)).toBe(8250 + 8160);
    expect(Number(result.levels_completed)).toBe(2);

    // Cleanup
    await pool.execute("DELETE FROM leaderboard_entries WHERE user_id = ?", [
      userId,
    ]);
    await pool.execute("DELETE FROM users WHERE id = ?", [userId]);
  });

  it("classe les utilisateurs par score décroissant", async () => {
    const email1 = `lb-player1-${Date.now()}@test.local`;
    const email2 = `lb-player2-${Date.now()}@test.local`;

    const [r1] = await pool.execute(
      "INSERT INTO users (email, display_name, password_hash) VALUES (?, ?, ?)",
      [email1, "Player 1", "dummy"],
    );
    const [r2] = await pool.execute(
      "INSERT INTO users (email, display_name, password_hash) VALUES (?, ?, ?)",
      [email2, "Player 2", "dummy"],
    );

    const userId1 = (r1 as any).insertId;
    const userId2 = (r2 as any).insertId;

    // Player 1: slow (20s, 30 moves) → score = MAX(100, 10000-2000-300) = 7700
    await pool.execute(
      "INSERT INTO leaderboard_entries (user_id, difficulty, level_index, time_ms, moves) VALUES (?, ?, ?, ?, ?)",
      [userId1, "easy", 1, 20000, 30],
    );

    // Player 2: fast (10s, 15 moves) → score = MAX(100, 10000-1000-150) = 8850
    await pool.execute(
      "INSERT INTO leaderboard_entries (user_id, difficulty, level_index, time_ms, moves) VALUES (?, ?, ?, ?, ?)",
      [userId2, "easy", 1, 10000, 15],
    );

    // Récupère le classement
    const [rows] = await pool.execute(
      `
      SELECT 
        user_id,
        SUM(score) as total_score
      FROM leaderboard_entries
      WHERE user_id IN (?, ?)
      GROUP BY user_id
      ORDER BY total_score DESC
      `,
      [userId1, userId2],
    );

    const results = rows as any[];
    expect(results[0].user_id).toBe(userId2);
    expect(Number(results[0].total_score)).toBe(8850);
    expect(results[1].user_id).toBe(userId1);
    expect(Number(results[1].total_score)).toBe(7700);

    // Cleanup
    await pool.execute(
      "DELETE FROM leaderboard_entries WHERE user_id IN (?, ?)",
      [userId1, userId2],
    );
    await pool.execute("DELETE FROM users WHERE id IN (?, ?)", [
      userId1,
      userId2,
    ]);
  });

  it("filtre le leaderboard par difficulté", async () => {
    const email = `lb-difficulty-${Date.now()}@test.local`;
    const [r] = await pool.execute(
      "INSERT INTO users (email, display_name, password_hash) VALUES (?, ?, ?)",
      [email, "Difficulty Tester", "dummy"],
    );
    const userId = (r as any).insertId;

    // Easy: 15s, 25 moves → score = MAX(100, 10000-1500-250) = 8250
    await pool.execute(
      "INSERT INTO leaderboard_entries (user_id, difficulty, level_index, time_ms, moves) VALUES (?, ?, ?, ?, ?)",
      [userId, "easy", 1, 15000, 25],
    );

    // Hard: 30s, 50 moves → score = MAX(100, 10000-3000-500) = 6500
    await pool.execute(
      "INSERT INTO leaderboard_entries (user_id, difficulty, level_index, time_ms, moves) VALUES (?, ?, ?, ?, ?)",
      [userId, "hard", 1, 30000, 50],
    );

    // Récupère easy seulement
    const [easyRows] = await pool.execute(
      `
      SELECT SUM(score) as total_score
      FROM leaderboard_entries
      WHERE user_id = ? AND difficulty = 'easy'
      `,
      [userId],
    );

    // Récupère hard seulement
    const [hardRows] = await pool.execute(
      `
      SELECT SUM(score) as total_score
      FROM leaderboard_entries
      WHERE user_id = ? AND difficulty = 'hard'
      `,
      [userId],
    );

    expect(Number((easyRows as any[])[0].total_score)).toBe(8250);
    expect(Number((hardRows as any[])[0].total_score)).toBe(6500);

    // Cleanup
    await pool.execute("DELETE FROM leaderboard_entries WHERE user_id = ?", [
      userId,
    ]);
    await pool.execute("DELETE FROM users WHERE id = ?", [userId]);
  });

  it("gère les égalités de score avec time_ms comme critère", async () => {
    const email1 = `lb-tie1-${Date.now()}@test.local`;
    const email2 = `lb-tie2-${Date.now()}@test.local`;

    const [r1] = await pool.execute(
      "INSERT INTO users (email, display_name, password_hash) VALUES (?, ?, ?)",
      [email1, "Tie Player 1", "dummy"],
    );
    const [r2] = await pool.execute(
      "INSERT INTO users (email, display_name, password_hash) VALUES (?, ?, ?)",
      [email2, "Tie Player 2", "dummy"],
    );

    const userId1 = (r1 as any).insertId;
    const userId2 = (r2 as any).insertId;

    // Same moves (25) but different times → different scores, same level
    // Player 1: 20s → score = MAX(100, 10000-2000-250) = 7750
    await pool.execute(
      "INSERT INTO leaderboard_entries (user_id, difficulty, level_index, time_ms, moves) VALUES (?, ?, ?, ?, ?)",
      [userId1, "easy", 1, 20000, 25], // Slower
    );
    // Player 2: 15s → score = MAX(100, 10000-1500-250) = 8250
    await pool.execute(
      "INSERT INTO leaderboard_entries (user_id, difficulty, level_index, time_ms, moves) VALUES (?, ?, ?, ?, ?)",
      [userId2, "easy", 1, 15000, 25], // Faster
    );

    // Récupère avec tie-breaking
    const [rows] = await pool.execute(
      `
      SELECT 
        user_id,
        SUM(score) as total_score,
        MIN(time_ms) as best_time
      FROM leaderboard_entries
      WHERE user_id IN (?, ?)
      GROUP BY user_id
      ORDER BY total_score DESC, best_time ASC
      `,
      [userId1, userId2],
    );

    const results = rows as any[];
    // Player 2 (8250) ranks above Player 1 (7750) by score
    expect(results[0].user_id).toBe(userId2);
    expect(results[1].user_id).toBe(userId1);

    // Cleanup
    await pool.execute(
      "DELETE FROM leaderboard_entries WHERE user_id IN (?, ?)",
      [userId1, userId2],
    );
    await pool.execute("DELETE FROM users WHERE id IN (?, ?)", [
      userId1,
      userId2,
    ]);
  });

  it("compte les entrées par niveau et difficulté", async () => {
    const email = `lb-count-${Date.now()}@test.local`;
    const [r] = await pool.execute(
      "INSERT INTO users (email, display_name, password_hash) VALUES (?, ?, ?)",
      [email, "Count Tester", "dummy"],
    );
    const userId = (r as any).insertId;

    // 3 entrées sur différents niveaux (score est GENERATED)
    await pool.execute(
      "INSERT INTO leaderboard_entries (user_id, difficulty, level_index, time_ms, moves) VALUES (?, ?, ?, ?, ?)",
      [userId, "easy", 0, 15000, 25],
    );
    await pool.execute(
      "INSERT INTO leaderboard_entries (user_id, difficulty, level_index, time_ms, moves) VALUES (?, ?, ?, ?, ?)",
      [userId, "easy", 1, 12000, 20],
    );
    await pool.execute(
      "INSERT INTO leaderboard_entries (user_id, difficulty, level_index, time_ms, moves) VALUES (?, ?, ?, ?, ?)",
      [userId, "medium", 0, 18000, 30],
    );

    // Compte par difficulté
    const [rows] = await pool.execute(
      `
      SELECT difficulty, COUNT(*) as entry_count, SUM(score) as total
      FROM leaderboard_entries
      WHERE user_id = ?
      GROUP BY difficulty
      ORDER BY difficulty
      `,
      [userId],
    );

    const results = rows as any[];
    expect(results.length).toBe(2);
    expect(results[0].difficulty).toBe("easy");
    expect(Number(results[0].entry_count)).toBe(2);
    expect(results[1].difficulty).toBe("medium");
    expect(Number(results[1].entry_count)).toBe(1);

    // Cleanup
    await pool.execute("DELETE FROM leaderboard_entries WHERE user_id = ?", [
      userId,
    ]);
    await pool.execute("DELETE FROM users WHERE id = ?", [userId]);
  });
});