src/app/api/db/route.ts

/**
 * Unified database API — replaces all Supabase queries.
 *
 * All operations use POST with a JSON body containing:
 *   { table, action, ...args }
 *
 * See the action handlers below for per-table operations.
 */

import { NextRequest, NextResponse } from "next/server";
import {
  readDb,
  writeDb,
  nextId,
  generateUniqueClaimCode,
  getTimestamp,
  type DbData,
  type PlayerRecord,
  type LeaderboardEntryRecord,
} from "@/lib/server/db";

// ---------------------------------------------------------------------------
// Action router
// ---------------------------------------------------------------------------

async function handle(req: NextRequest) {
  const body = await req.json().catch(() => ({}));
  const { table, action } = body;

  const handlers = {
    players: handlePlayers,
    leaderboard: handleLeaderboard,
    sessions: handleSessions,
    achievements: handleAchievements,
    cosmetics: handleCosmetics,
    matches: handleMatches,
    debates: handleDebates,
    stats: handleStats,
    admin: handleAdmin,
  } as Record<string, (b: Record<string, unknown>) => unknown>;

  const tableHandler = handlers[table as string];
  if (!tableHandler) {
    return NextResponse.json({ error: `Unknown table: ${table}` }, { status: 400 });
  }

  try {
    const result = await tableHandler(body);
    return NextResponse.json(result);
  } catch (err) {
    console.error(`[api/db] ${table}/${action} error:`, err);
    return NextResponse.json({ error: String(err) }, { status: 500 });
  }
}

export const POST = handle;

// ---------------------------------------------------------------------------
// Players
// ---------------------------------------------------------------------------

function handlePlayers(body: Record<string, unknown>): unknown {
  const action = body.action as string;
  const db = readDb();

  switch (action) {
    case "get_or_create": {
      const anonymousId = body.anonymousId as string;
      const displayName = body.displayName as string;
      if (!anonymousId || !displayName) return null;

      const existing = db.players.find((p) => p.anonymous_id === anonymousId);
      if (existing) {
        if (existing.display_name !== displayName) {
          existing.display_name = displayName;
          writeDb(db);
        }
        return existing;
      }

      const player: PlayerRecord = {
        id: nextId(),
        display_name: displayName,
        anonymous_id: anonymousId,
        elo_rating: 1200,
        games_played: 0,
        star_coins: 0,
        claim_code: generateUniqueClaimCode(),
        spectrum_position: 6,
        created_at: getTimestamp(),
      };
      db.players.push(player);
      writeDb(db);
      return player;
    }

    case "get_by_id": {
      const id = body.id as string;
      return db.players.find((p) => p.id === id) ?? null;
    }

    case "get_by_claim_code": {
      const code = (body.code as string)?.toUpperCase().trim();
      return db.players.find((p) => p.claim_code === code) ?? null;
    }

    case "update_name": {
      const id = body.id as string;
      const name = body.displayName as string;
      const player = db.players.find((p) => p.id === id);
      if (player) {
        player.display_name = name;
        writeDb(db);
        return player;
      }
      return null;
    }

    case "add_star_coins": {
      const id = body.id as string;
      const amount = Number(body.amount) || 0;
      const player = db.players.find((p) => p.id === id);
      if (player) {
        player.star_coins += amount;
        writeDb(db);
        return player.star_coins;
      }
      return null;
    }

    case "get_star_coins": {
      const id = body.id as string;
      const player = db.players.find((p) => p.id === id);
      return player?.star_coins ?? 0;
    }

    case "increment_games_played": {
      const id = body.id as string;
      const player = db.players.find((p) => p.id === id);
      if (player) {
        player.games_played++;
        writeDb(db);
      }
      return null;
    }

    case "set_spectrum": {
      const id = body.id as string;
      const position = Number(body.position) || 6;
      const player = db.players.find((p) => p.id === id);
      if (player) {
        player.spectrum_position = position;
        writeDb(db);
      }
      return null;
    }

    case "get_spectrum": {
      const id = body.id as string;
      const player = db.players.find((p) => p.id === id);
      return player?.spectrum_position ?? null;
    }

    default:
      return { error: `Unknown players action: ${action}` };
  }
}

// ---------------------------------------------------------------------------
// Leaderboard
// ---------------------------------------------------------------------------

function handleLeaderboard(body: Record<string, unknown>): unknown {
  const action = body.action as string;
  const db = readDb();

  switch (action) {
    case "get": {
      const mode = body.mode as string;
      const limit = Number(body.limit) || 10;
      const asc = body.ascending === true;
      let entries = db.leaderboard_entries.filter((e) => e.mode === mode);
      entries.sort((a, b) => asc ? a.score - b.score : b.score - a.score);
      return entries.slice(0, limit);
    }

    case "get_all_modes": {
      const limit = Number(body.limit) || 5;
      const modes = ["quick-fire", "challenge-me", "streak", "multiplayer"] as const;
      const result: Record<string, LeaderboardEntryRecord[]> = {};
      for (const mode of modes) {
        let entries = db.leaderboard_entries.filter((e) => e.mode === mode);
        entries.sort((a, b) => b.score - a.score);
        result[mode] = entries.slice(0, limit);
      }
      return result;
    }

    case "submit": {
      const entry = body.entry as Record<string, unknown>;
      if (!entry) return null;
      const newEntry: LeaderboardEntryRecord = {
        id: nextId(),
        player_id: entry.player_id as string,
        player_name: entry.player_name as string,
        mode: entry.mode as string,
        score: Number(entry.score) || 0,
        accuracy: Number(entry.accuracy) || 0,
        season_id: (entry.season_id as string) ?? null,
        created_at: getTimestamp(),
      };
      db.leaderboard_entries.push(newEntry);
      writeDb(db);
      return newEntry;
    }

    default:
      return { error: `Unknown leaderboard action: ${action}` };
  }
}

// ---------------------------------------------------------------------------
// Game Sessions
// ---------------------------------------------------------------------------

function handleSessions(body: Record<string, unknown>): unknown {
  const action = body.action as string;
  const db = readDb();

  switch (action) {
    case "record": {
      const s = body.session as Record<string, unknown>;
      if (!s) return null;
      const session = {
        id: nextId(),
        player_id: s.player_id as string,
        mode: s.mode as string,
        score: Number(s.score) || 0,
        accuracy: Number(s.accuracy) || 0,
        streak_max: Number(s.streak_max) || 0,
        duration_ms: Number(s.duration_ms) || 0,
        questions_answered: Number(s.questions_answered) || 0,
        questions_correct: Number(s.questions_correct) || 0,
        created_at: getTimestamp(),
      };
      db.game_sessions.push(session);
      writeDb(db);

      // Increment games played
      const player = db.players.find((p) => p.id === session.player_id);
      if (player) player.games_played++;
      writeDb(db);

      return session;
    }

    default:
      return { error: `Unknown sessions action: ${action}` };
  }
}

// ---------------------------------------------------------------------------
// Player Achievements
// ---------------------------------------------------------------------------

function handleAchievements(body: Record<string, unknown>): unknown {
  const action = body.action as string;
  const db = readDb();

  switch (action) {
    case "get": {
      const playerId = body.playerId as string;
      return db.player_achievements.filter((a) => a.player_id === playerId);
    }

    case "get_player_stats": {
      const playerId = body.playerId as string;
      const sessions = db.game_sessions.filter((s) => s.player_id === playerId);
      return {
        totalGames: sessions.length,
        distinctModes: [...new Set(sessions.map((s) => s.mode))],
        totalCorrectAnswers: sessions.reduce((sum, s) => sum + s.questions_correct, 0),
      };
    }

    case "upsert": {
      const playerId = body.playerId as string;
      const achievementId = body.achievementId as string;
      const progress = Number(body.progress) || 0;
      const unlocked = body.unlocked === true;
      const existing = db.player_achievements.find(
        (a) => a.player_id === playerId && a.achievement_id === achievementId
      );
      if (existing) {
        existing.progress = Math.max(existing.progress, progress);
        existing.unlocked = existing.unlocked || unlocked;
        if (unlocked) existing.unlocked_at = existing.unlocked_at ?? getTimestamp();
      } else {
        db.player_achievements.push({
          id: nextId(),
          player_id: playerId,
          achievement_id: achievementId,
          progress,
          unlocked,
          unlocked_at: unlocked ? getTimestamp() : null,
        });
      }
      writeDb(db);
      return null;
    }

    default:
      return { error: `Unknown achievements action: ${action}` };
  }
}

// ---------------------------------------------------------------------------
// Cosmetics
// ---------------------------------------------------------------------------

function handleCosmetics(body: Record<string, unknown>): unknown {
  const action = body.action as string;
  const db = readDb();

  switch (action) {
    case "get_cosmetics": {
      const playerId = body.playerId as string;
      return db.player_cosmetics.filter((c) => c.player_id === playerId);
    }

    case "get_equipped": {
      const playerId = body.playerId as string;
      return db.player_equipped.filter((e) => e.player_id === playerId);
    }

    case "purchase": {
      const playerId = body.playerId as string;
      const cosmeticId = body.cosmeticId as string;
      const price = Number(body.price) || 0;
      const player = db.players.find((p) => p.id === playerId);
      if (!player) return false;
      if (player.star_coins < price) return false;
      player.star_coins -= price;
      db.player_cosmetics.push({
        id: nextId(),
        player_id: playerId,
        cosmetic_id: cosmeticId,
        purchased_at: getTimestamp(),
      });
      writeDb(db);
      return true;
    }

    case "equip": {
      const playerId = body.playerId as string;
      const category = body.category as string;
      const cosmeticId = body.cosmeticId as string;
      const existing = db.player_equipped.find(
        (e) => e.player_id === playerId && e.category === category
      );
      if (existing) {
        existing.cosmetic_id = cosmeticId;
      } else {
        db.player_equipped.push({
          id: nextId(),
          player_id: playerId,
          category,
          cosmetic_id: cosmeticId,
        });
      }
      writeDb(db);
      return true;
    }

    case "unequip": {
      const playerId = body.playerId as string;
      const category = body.category as string;
      db.player_equipped = db.player_equipped.filter(
        (e) => !(e.player_id === playerId && e.category === category)
      );
      writeDb(db);
      return true;
    }

    default:
      return { error: `Unknown cosmetics action: ${action}` };
  }
}

// ---------------------------------------------------------------------------
// Matches
// ---------------------------------------------------------------------------

function handleMatches(body: Record<string, unknown>): unknown {
  const action = body.action as string;
  const db = readDb();

  switch (action) {
    case "record": {
      const m = body.match as Record<string, unknown>;
      if (!m) return null;
      const match = {
        id: nextId(),
        player1_id: m.player1_id as string,
        player2_id: m.player2_id as string,
        player1_score: Number(m.player1_score) || 0,
        player2_score: Number(m.player2_score) || 0,
        winner_id: (m.winner_id as string) ?? null,
        elo_change: Number(m.elo_change) || 0,
        question_ids: (m.question_ids as string[]) ?? [],
        created_at: getTimestamp(),
      };
      db.matches.push(match);
      writeDb(db);
      return match;
    }

    default:
      return { error: `Unknown matches action: ${action}` };
  }
}

// ---------------------------------------------------------------------------
// Debates (Spark)
// ---------------------------------------------------------------------------

function handleDebates(body: Record<string, unknown>): unknown {
  const action = body.action as string;
  const db = readDb();

  switch (action) {
    case "record_debate": {
      const d = body.debate as Record<string, unknown>;
      if (!d) return null;
      const debate = {
        id: nextId(),
        topic_id: (d.topic_id as string) ?? "",
        player1_id: (d.player1_id as string) ?? null,
        player2_id: (d.player2_id as string) ?? null,
        duration_minutes: Number(d.duration_minutes) || 5,
        rounds: Number(d.rounds) || 0,
        winner_id: (d.winner_id as string) ?? null,
        player1_score: (d.player1_score as number) ?? null,
        player2_score: (d.player2_score as number) ?? null,
        verdict: (d.verdict as string) ?? null,
        elo_change: Number(d.elo_change) || 0,
        is_practice: d.is_practice === true,
        created_at: getTimestamp(),
      };
      db.debates.push(debate);
      writeDb(db);
      return debate;
    }

    case "get_preview": {
      const [latest] = db.debates.sort(
        (a, b) => new Date(b.created_at).getTime() - new Date(a.created_at).getTime()
      );
      if (!latest) return null;
      const round = db.debate_rounds
        .filter((r) => r.debate_id === latest.id)
        .sort((a, b) => a.round_number - b.round_number)[0];
      return {
        topic: `Debate #${latest.id}`,
        question: `A competitive debate on a challenging topic.`,
        openerArgument: round?.player1_argument ?? "A compelling opening argument was made.",
        rebuttalArgument: round?.player2_argument ?? "A strong rebuttal followed.",
        commentary: null,
      };
    }

    case "get_leaderboard": {
      const limit = Number(body.limit) || 5;
      return db.players
        .filter((p) => p.elo_rating !== 1200 || p.games_played > 0)
        .sort((a, b) => b.elo_rating - a.elo_rating)
        .slice(0, limit)
        .map((p) => ({
          name: p.display_name,
          elo: p.elo_rating,
          gamesPlayed: p.games_played,
        }));
    }

    case "get_recent": {
      const limit = Number(body.limit) || 5;
      return db.debates
        .sort((a, b) => new Date(b.created_at).getTime() - new Date(a.created_at).getTime())
        .slice(0, limit)
        .map((d) => {
          const p1 = db.players.find((p) => p.id === d.player1_id);
          const p2 = db.players.find((p) => p.id === d.player2_id);
          return {
            topic: `Debate #${d.id}`,
            player1Score: d.player1_score ?? 0,
            player2Score: d.player2_score ?? 0,
            winnerName: d.winner_id
              ? db.players.find((p) => p.id === d.winner_id)?.display_name ?? "Unknown"
              : "Tie",
            createdAt: d.created_at,
          };
        });
    }

    case "get_total_count": {
      return db.debates.length;
    }

    default:
      return { error: `Unknown debates action: ${action}` };
  }
}

// ---------------------------------------------------------------------------
// Stats / Admin
// ---------------------------------------------------------------------------

function handleStats(body: Record<string, unknown>): unknown {
  const action = body.action as string;

  switch (action) {
    case "total_games": {
      const db = readDb();
      return db.game_sessions.length;
    }

    default:
      return { error: `Unknown stats action: ${action}` };
  }
}

function handleAdmin(_body: Record<string, unknown>): unknown {
  const db = readDb();
  const modeMap = new Map<string, { total: number; scoreSum: number; accSum: number }>();
  for (const s of db.game_sessions) {
    const existing = modeMap.get(s.mode) ?? { total: 0, scoreSum: 0, accSum: 0 };
    modeMap.set(s.mode, {
      total: existing.total + 1,
      scoreSum: existing.scoreSum + s.score,
      accSum: existing.accSum + s.accuracy,
    });
  }
  const modeBreakdown = Array.from(modeMap.entries())
    .map(([mode, d]) => ({
      mode,
      count: d.total,
      avgScore: Math.round(d.scoreSum / d.total),
      avgAccuracy: Math.round((d.accSum / d.total) * 100),
    }))
    .sort((a, b) => b.count - a.count);

  const recentSessions = db.game_sessions
    .sort((a, b) => new Date(b.created_at).getTime() - new Date(a.created_at).getTime())
    .slice(0, 10)
    .map((s) => {
      const player = db.players.find((p) => p.id === s.player_id);
      return {
        id: s.id,
        mode: s.mode,
        score: s.score,
        accuracy: s.accuracy,
        created_at: s.created_at,
        playerName: player?.display_name ?? "Unknown",
      };
    });

  return {
    totalPlayers: db.players.length,
    totalSessions: db.game_sessions.length,
    totalMatches: db.matches.length,
    totalDebates: db.debates.length,
    modeBreakdown,
    recentSessions,
    topPlayers: db.leaderboard_entries
      .sort((a, b) => b.score - a.score)
      .slice(0, 5)
      .map((e) => ({ name: e.player_name, score: e.score, mode: e.mode })),
    recentDebates: db.debates
      .sort((a, b) => new Date(b.created_at).getTime() - new Date(a.created_at).getTime())
      .slice(0, 10)
      .map((d) => ({
        id: d.id,
        created_at: d.created_at,
        winner_id: d.winner_id,
        player1_id: d.player1_id,
        player2_id: d.player2_id,
        player1_name: db.players.find((p) => p.id === d.player1_id)?.display_name ?? "Unknown",
        player2_name: db.players.find((p) => p.id === d.player2_id)?.display_name ?? "Unknown",
        topic: `Debate #${d.id}`,
        verdict: d.verdict,
      })),
    topDebaters: db.players
      .filter((p) => p.spectrum_position && p.games_played > 0)
      .sort((a, b) => b.elo_rating - a.elo_rating)
      .slice(0, 10)
      .map((p) => ({
        name: p.display_name,
        elo: p.elo_rating,
        games: p.games_played,
        spectrumPosition: p.spectrum_position,
      })),
  };
}