import { readBatches } from "./read-batches.js";
import { readPages } from "./read-pages.js";
import type { Express, Request, Response, NextFunction } from "express";
import { z } from "zod";
import { env } from "./config.js";
import { supabaseAdmin } from "./supabase.js";
import { requireAdmin } from "./server.js";
import { invalidateSubscriptionCache } from "./subscriptions.js";
import { deleteStoredSession, refundAdminUsage } from "./resource-deletion.js";

const uuid = z.uuid();
const date = z.string().regex(/^\d{4}-\d{2}-\d{2}$/).refine((value) => {
  const parsed = new Date(`${value}T00:00:00.000Z`);
  return !Number.isNaN(parsed.valueOf()) && parsed.toISOString().slice(0, 10) === value;
}, "Enter a valid calendar date.");
const profileSchema = z.object({
  fname: z.string().trim().min(1).max(100), lname: z.string().trim().min(1).max(100),
  academy: z.string().trim().max(150).nullable(), role: z.string().trim().max(50).nullable(),
}).strict();
const playerSchema = z.object({
  fname: z.string().trim().min(1).max(100), lname: z.string().trim().max(100).nullable().optional().default(null),
  bdate: date.refine((value) => {
    const today = new Date();
    const [year, month, day] = value.split("-").map(Number);
    const age = today.getUTCFullYear() - year - Number(today.getUTCMonth() + 1 < month || (today.getUTCMonth() + 1 === month && today.getUTCDate() < day));
    return age >= 4 && age <= 60;
  }, "Enter a valid birth date for a player aged 4 to 60."),
  heightCm: z.number().min(100).max(230), weightKg: z.number().min(20).max(150),
  club: z.string().trim().max(150).nullable().optional(), position: z.string().trim().min(1).max(50),
  gender: z.enum(["male", "female"]), userId: uuid,
}).strict();
const playerUpdateSchema = playerSchema.omit({ userId: true });

function fail(res: Response, status: number, error: string) { return res.status(status).json({ error }); }
function asyncRoute(handler: (req: Request, res: Response) => Promise<unknown>) {
  return (req: Request, res: Response, next: NextFunction) => { void handler(req, res).catch(next); };
}
async function admin(req: Request, res: Response) { return requireAdmin(req, res); }
async function analysisRequest(path: string, userId: string, method: string, isAdmin = true, body?: unknown) {
  const response = await fetch(`${env.ANALYSIS_API_URL}/api${path}`, {
    method,
    headers: {
      "x-sporalize-proxy-secret": env.ANALYSIS_PROXY_SECRET,
      "x-sporalize-user-id": userId,
      ...(isAdmin ? { "x-sporalize-admin": "true" } : {}),
      ...(body !== undefined ? { "content-type": "application/json" } : {}),
    },
    ...(body !== undefined ? { body: JSON.stringify(body) } : {}),
  });
  if (!response.ok) throw new Error(`Analysis storage request failed (${response.status}).`);
  return response;
}

async function removeImages(paths: Array<string | null | undefined>) {
  const safe = [...new Set(paths.filter((path): path is string => Boolean(path) && /^(players|users)\/[0-9a-f-]{36}\/[0-9a-f-]{36}\.(jpg|png|webp)$/i.test(path!)))];
  if (!safe.length) return;
  const { error } = await supabaseAdmin.storage.from("profile-images").remove(safe);
  if (error) throw new Error(`Unable to remove profile images: ${error.message}`);
}

// Count only children of the displayed records, without oversized filters or
// silently losing rows to Supabase's response cap.
async function loadRelatedCounts(table: "players" | "sessions" | "actions", key: "user_id" | "player_id" | "session_id", ids: string[]) {
  const primaryKey = table === "players" ? "player_id" : table === "sessions" ? "session_id" : "action_id";
  return readBatches<string, Record<string, string | null>>(ids, (batch) => readPages(async (from, to) => {
    const { data, error } = await supabaseAdmin.from(table).select(`${primaryKey},${key}`)
      .in(key, batch).order(primaryKey).range(from, to);
    if (error) throw new Error(`Unable to load ${table} counts: ${error.message}`);
    return (data ?? []) as unknown as Record<string, string | null>[];
  }));
}

export function registerAdminRoutes(app: Express) {
  app.get("/api/admin/users", asyncRoute(async (req, res) => {
    if (!await admin(req, res)) return;
    const search = String(req.query.search ?? "").replace(/[^\p{L}\p{N}@._ -]/gu, "").trim().slice(0, 100);
    let query = supabaseAdmin.from("users").select("user_id,fname,lname,email,academy,role,image_path,account_role").order("fname").limit(250);
    if (search) query = query.or(`fname.ilike.%${search}%,lname.ilike.%${search}%,email.ilike.%${search}%`);
    const { data, error } = await query;
    if (error) throw new Error(`Unable to load user directory: ${error.message}`);
    const ids = (data ?? []).map((row) => row.user_id);
    const [subs, counts, playerCounts] = ids.length ? await Promise.all([
      supabaseAdmin.from("users_subscriptions").select("user_id,subscription_id,startdate,enddate,num_players,num_sessions").in("user_id", ids),
      loadRelatedCounts("sessions", "user_id", ids).then((data) => ({ data, error: null })),
      loadRelatedCounts("players", "user_id", ids).then((data) => ({ data, error: null })),
    ]) : [{ data: [], error: null }, { data: [], error: null }, { data: [], error: null }];
    if (subs.error) throw new Error(`Unable to load user plans: ${subs.error.message}`);
    const planIds = [...new Set((subs.data ?? []).flatMap((row) => row.subscription_id ? [row.subscription_id] : []))];
    const { data: plans, error: plansError } = planIds.length
      ? await supabaseAdmin.from("subscriptions").select("subscription_id,plan,price,player_limit,session_limit").in("subscription_id", planIds)
      : { data: [], error: null };
    if (plansError) throw new Error(`Unable to load plan names: ${plansError.message}`);
    const planMap = new Map((plans ?? []).map((plan) => [plan.subscription_id, plan]));
    const subMap = new Map((subs.data ?? []).map((row) => [row.user_id, row]));
    const sessionCounts = new Map<string, number>();
    const playerCountByUser = new Map<string, number>();
    for (const row of counts.data ?? []) if (row.user_id) sessionCounts.set(row.user_id, (sessionCounts.get(row.user_id) ?? 0) + 1);
    for (const row of playerCounts.data ?? []) if (row.user_id) playerCountByUser.set(row.user_id, (playerCountByUser.get(row.user_id) ?? 0) + 1);
    return res.json({ users: (data ?? []).map((row) => {
      const assignment = String(row.account_role).toLowerCase() === "admin" ? null : subMap.get(row.user_id);
      const plan = assignment?.subscription_id ? planMap.get(assignment.subscription_id) : null;
      return {
        id: row.user_id, fname: row.fname, lname: row.lname, name: `${row.fname} ${row.lname}`.trim(),
        email: row.email, academy: row.academy, role: row.role, imagePath: row.image_path, accountRole: row.account_role,
        subscription: plan ? { id: plan.subscription_id, name: plan.plan, startDate: assignment?.startdate, endDate: assignment?.enddate, playerLimit: plan.player_limit, sessionLimit: plan.session_limit } : null,
        usage: {
          players: String(row.account_role).toLowerCase() === "admin" ? playerCountByUser.get(row.user_id) ?? 0 : assignment?.num_players ?? 0,
          sessions: String(row.account_role).toLowerCase() === "admin" ? sessionCounts.get(row.user_id) ?? 0 : assignment?.num_sessions ?? 0,
          totalSessions: sessionCounts.get(row.user_id) ?? 0,
        },
        dataCounts: { players: playerCountByUser.get(row.user_id) ?? 0, sessions: sessionCounts.get(row.user_id) ?? 0 },
      };
    }) });
  }));

  app.get("/api/admin/plans", asyncRoute(async (req, res) => {
    if (!await admin(req, res)) return;
    const { data, error } = await supabaseAdmin.from("subscriptions").select("subscription_id,plan,price,player_limit,session_limit").order("price");
    if (error) throw new Error(`Unable to load plans: ${error.message}`);
    return res.json({ plans: data ?? [] });
  }));

  app.get("/api/admin/users/:userId", asyncRoute(async (req, res) => {
    if (!await admin(req, res)) return;
    const id = uuid.safeParse(req.params.userId); if (!id.success) return res.sendStatus(404);
    const [{ data: user, error }, { data: assignment, error: subError }, { count: players, error: playersError }, { count: sessions, error: sessionsError }] = await Promise.all([
      supabaseAdmin.from("users").select("user_id,fname,lname,email,academy,role,image_path,account_role").eq("user_id", id.data).maybeSingle(),
      supabaseAdmin.from("users_subscriptions").select("subscription_id,startdate,enddate,num_players,num_sessions").eq("user_id", id.data).maybeSingle(),
      supabaseAdmin.from("players").select("player_id", { count: "exact", head: true }).eq("user_id", id.data),
      supabaseAdmin.from("sessions").select("session_id", { count: "exact", head: true }).eq("user_id", id.data),
    ]);
    if (error) throw new Error(`Unable to load user: ${error.message}`);
    if (subError) throw new Error(`Unable to load user plan: ${subError.message}`);
    if (playersError || sessionsError) throw new Error(`Unable to load account data counts: ${(playersError ?? sessionsError)?.message}`);
    if (!user) return res.sendStatus(404);
    return res.json({ user, subscription: assignment ?? null, counts: { players: players ?? 0, sessions: sessions ?? 0 } });
  }));

  app.patch("/api/admin/users/:userId", asyncRoute(async (req, res) => {
    if (!await admin(req, res)) return;
    const id = uuid.safeParse(req.params.userId); const input = profileSchema.safeParse(req.body);
    if (!id.success || !input.success) return fail(res, 400, "Check the profile details.");
    const { data, error } = await supabaseAdmin.from("users").update(input.data).eq("user_id", id.data)
      .select("user_id,fname,lname,email,academy,role,image_path,account_role").maybeSingle();
    if (error) throw new Error(`Unable to update profile: ${error.message}`);
    if (!data) return res.sendStatus(404);
    return res.json({ user: data });
  }));

  app.put("/api/admin/users/:userId/subscription", asyncRoute(async (req, res) => {
    if (!await admin(req, res)) return;
    const id = uuid.safeParse(req.params.userId);
    const input = z.object({ subscriptionId: uuid, startDate: date, endDate: date }).strict().safeParse(req.body);
    if (!id.success || !input.success || input.data.endDate < input.data.startDate) return fail(res, 400, "Choose a valid plan period.");
    const { data: account, error: accountError } = await supabaseAdmin.from("users").select("account_role").eq("user_id", id.data).maybeSingle();
    if (accountError) throw new Error(`Unable to verify account: ${accountError.message}`);
    if (!account) return res.sendStatus(404);
    if (String(account.account_role).toLowerCase() === "admin") return fail(res, 409, "Administrator accounts do not use subscriptions.");
    const { data: plan, error: planError } = await supabaseAdmin.from("subscriptions").select("subscription_id").eq("subscription_id", input.data.subscriptionId).maybeSingle();
    if (planError) throw new Error(`Unable to verify plan: ${planError.message}`);
    if (!plan) return fail(res, 404, "Plan not found.");
    const { data: row, error } = await supabaseAdmin.from("users_subscriptions").upsert({
      user_id: id.data, subscription_id: plan.subscription_id, startdate: input.data.startDate, enddate: input.data.endDate,
      num_players: 0, num_sessions: 0,
    }, { onConflict: "user_id" }).select("user_id,subscription_id,startdate,enddate,num_players,num_sessions").single();
    if (error) throw new Error(`Unable to assign plan: ${error.message}`);
    invalidateSubscriptionCache(id.data);
    return res.json({ subscription: row });
  }));

  app.delete("/api/admin/users/:userId/subscription", asyncRoute(async (req, res) => {
    if (!await admin(req, res)) return;
    const id = uuid.safeParse(req.params.userId); if (!id.success) return res.sendStatus(404);
    const { error } = await supabaseAdmin.from("users_subscriptions").update({ subscription_id: null, num_players: 0, num_sessions: 0 })
      .eq("user_id", id.data);
    if (error) throw new Error(`Unable to cancel plan: ${error.message}`);
    invalidateSubscriptionCache(id.data);
    return res.sendStatus(204);
  }));

  app.get("/api/admin/players", asyncRoute(async (req, res) => {
    if (!await admin(req, res)) return;
    const [{ data: players, error }, { data: users, error: userError }] = await Promise.all([
      supabaseAdmin.from("players").select("player_id,user_id,fname,lname,bdate,height,weight,position,club,gender,image_path").order("fname").limit(1000),
      supabaseAdmin.from("users").select("user_id,fname,lname,email").order("fname").limit(1000),
    ]);
    if (error) throw new Error(`Unable to load players: ${error.message}`);
    if (userError) throw new Error(`Unable to load owners: ${userError.message}`);
    const sessionCounts = new Map<string, number>();
    const linkedSessions = await loadRelatedCounts("sessions", "player_id", (players ?? []).map((player) => player.player_id));
    for (const session of linkedSessions) {
      if (session.player_id) sessionCounts.set(session.player_id, (sessionCounts.get(session.player_id) ?? 0) + 1);
    }
    const owners = new Map((users ?? []).map((user) => [user.user_id, user]));
    return res.json({ players: (players ?? []).map((player) => {
      const owner = player.user_id ? owners.get(player.user_id) : null;
      return { ...player, sessionCount: sessionCounts.get(player.player_id) ?? 0, owner: owner ? `${owner.fname} ${owner.lname}`.trim() || owner.email : null };
    }), users: users ?? [] });
  }));

  app.post("/api/admin/players", asyncRoute(async (req, res) => {
    if (!await admin(req, res)) return;
    const input = playerSchema.safeParse(req.body); if (!input.success) return fail(res, 400, "Check the player details.");
    if (input.data.userId) {
      const { data, error } = await supabaseAdmin.from("users").select("user_id").eq("user_id", input.data.userId).maybeSingle();
      if (error) throw new Error(`Unable to verify player owner: ${error.message}`);
      if (!data) return fail(res, 404, "Player owner not found.");
    }
    const { data, error } = await supabaseAdmin.rpc("admin_create_player", {
      p_user_id: input.data.userId,
      p_fname: input.data.fname,
      p_lname: input.data.lname?.trim() || null,
      p_bdate: input.data.bdate,
      p_height: input.data.heightCm,
      p_weight: input.data.weightKg,
      p_club: input.data.club || null,
      p_position: input.data.position,
      p_gender: input.data.gender,
    });
    if (error) throw new Error(`Unable to add player: ${error.message}`);
    return res.status(201).json({ player: data });
  }));

  app.patch("/api/admin/players/:playerId", asyncRoute(async (req, res) => {
    if (!await admin(req, res)) return;
    const id = uuid.safeParse(req.params.playerId);
    const input = playerUpdateSchema.safeParse(req.body);
    if (!id.success || !input.success) return fail(res, 400, "Check the player details.");
    const { data, error } = await supabaseAdmin.from("players").update({
      fname: input.data.fname, lname: input.data.lname, bdate: input.data.bdate,
      height: input.data.heightCm, weight: input.data.weightKg, club: input.data.club,
      position: input.data.position, gender: input.data.gender,
    }).eq("player_id", id.data).select("player_id,user_id").maybeSingle();
    if (error) throw new Error(`Unable to update player: ${error.message}`);
    if (!data) return res.sendStatus(404);
    return res.json({ player: data });
  }));

  app.delete("/api/admin/players/:playerId", asyncRoute(async (req, res) => {
    const current = await admin(req, res); if (!current) return;
    const id = uuid.safeParse(req.params.playerId); if (!id.success) return res.sendStatus(404);
    const mode = z.enum(["keep_sessions", "delete_sessions"]).safeParse(req.body?.mode);
    if (!mode.success) return fail(res, 400, "Choose whether to keep or delete this player's sessions.");
    const { data: player, error: readError } = await supabaseAdmin.from("players").select("player_id,user_id,image_path").eq("player_id", id.data).maybeSingle();
    if (readError) throw new Error(`Unable to load player: ${readError.message}`);
    if (!player) return res.sendStatus(404);
    const { data: linkedSessions, error: sessionsError } = await supabaseAdmin.from("sessions").select("session_id,user_id,by_admin").eq("player_id", id.data);
    if (sessionsError) throw new Error(`Unable to load player sessions: ${sessionsError.message}`);
    if (mode.data === "delete_sessions") for (const session of linkedSessions ?? []) await deleteStoredSession(session.session_id, current.user.id, true);
    const { error } = await supabaseAdmin.from("players").delete().eq("player_id", id.data);
    if (error) throw new Error(`Unable to delete player: ${error.message}`);
    const chargedSessions = mode.data === "delete_sessions"
      ? (linkedSessions ?? []).filter((session) => session.user_id === player.user_id && !session.by_admin).length
      : 0;
    await refundAdminUsage(player.user_id, 1, chargedSessions);
    await removeImages([player.image_path]).catch((imageError) => console.error("Player was deleted but its profile image could not be removed", imageError));
    return res.sendStatus(204);
  }));

  app.get("/api/admin/sessions", asyncRoute(async (req, res) => {
    if (!await admin(req, res)) return;
    const [{ data: sessions, error }, { data: users, error: usersError }, { data: players, error: playersError }] = await Promise.all([
      supabaseAdmin.from("sessions").select("session_id,user_id,player_id,name,path,status,created_at,by_admin").order("created_at", { ascending: false }).limit(1000),
      supabaseAdmin.from("users").select("user_id,fname,lname,email").order("fname").limit(1000),
      supabaseAdmin.from("players").select("player_id,fname,lname").order("fname").limit(1000),
    ]);
    if (error) throw new Error(`Unable to load sessions: ${error.message}`);
    if (usersError) throw new Error(`Unable to load owners: ${usersError.message}`);
    if (playersError) throw new Error(`Unable to load session players: ${playersError.message}`);
    const owners = new Map((users ?? []).map((user) => [user.user_id, user]));
    const roster = new Map((players ?? []).map((player) => [player.player_id, player]));
    const ids = (sessions ?? []).map((session) => session.session_id);
    const actionRows = await loadRelatedCounts("actions", "session_id", ids);
    const actionCounts = new Map<string, number>();
    for (const action of actionRows ?? []) if (action.session_id) actionCounts.set(action.session_id, (actionCounts.get(action.session_id) ?? 0) + 1);
    return res.json({ sessions: (sessions ?? []).map((session) => {
      const owner = session.user_id ? owners.get(session.user_id) : null;
      const player = session.player_id ? roster.get(session.player_id) : null;
      return {
        ...session,
        owner: owner ? `${owner.fname} ${owner.lname}`.trim() || owner.email : null,
        player: player ? `${player.fname} ${player.lname}`.trim() : null,
        actionCount: actionCounts.get(session.session_id) ?? 0,
      };
    }), users: users ?? [], players: players ?? [] });
  }));

  app.delete("/api/admin/sessions/:sessionId", asyncRoute(async (req, res) => {
    const current = await admin(req, res); if (!current) return;
    const id = uuid.safeParse(req.params.sessionId); if (!id.success) return res.sendStatus(404);
    const { data: session, error } = await supabaseAdmin.from("sessions").select("user_id,by_admin").eq("session_id", id.data).maybeSingle();
    if (error) throw new Error(`Unable to load session before deletion: ${error.message}`);
    if (!session) return res.sendStatus(404);
    await deleteStoredSession(id.data, current.user.id, true);
    await refundAdminUsage(session.user_id, 0, session.by_admin ? 0 : 1);
    return res.sendStatus(204);
  }));

  app.get("/api/admin/sessions/:sessionId", asyncRoute(async (req, res) => {
    if (!await admin(req, res)) return;
    const id = uuid.safeParse(req.params.sessionId); if (!id.success) return res.sendStatus(404);
    const { data: session, error } = await supabaseAdmin.from("sessions").select("session_id,user_id,player_id,name,path,status,created_at,by_admin").eq("session_id", id.data).maybeSingle();
    if (error) throw new Error(`Unable to load session: ${error.message}`);
    if (!session) return res.sendStatus(404);
    const { data: actions, error: actionsError } = await supabaseAdmin.from("actions").select("action_id,start_frame,end_frame,type").eq("session_id", id.data).order("start_frame");
    if (actionsError) throw new Error(`Unable to load actions: ${actionsError.message}`);
    const actionIds = (actions ?? []).map((action) => action.action_id);
    const [{ data: annotations, error: annotationError }, { data: notes, error: notesError }] = actionIds.length ? await Promise.all([
      supabaseAdmin.from("annotations").select("action_id,score,user_id").in("action_id", actionIds),
      supabaseAdmin.from("annotations_notes").select("action_id,note").in("action_id", actionIds),
    ]) : [{ data: [], error: null }, { data: [], error: null }];
    if (annotationError) throw new Error(`Unable to load action annotations: ${annotationError.message}`);
    if (notesError) throw new Error(`Unable to load action notes: ${notesError.message}`);
    const annotationsById = new Map((annotations ?? []).map((row) => [row.action_id, row]));
    const notesById = new Map<string, string[]>();
    for (const row of notes ?? []) notesById.set(row.action_id, [...(notesById.get(row.action_id) ?? []), row.note]);
    return res.json({ session, actions: (actions ?? []).map((action) => ({ ...action, score: annotationsById.get(action.action_id)?.score ?? null, notes: notesById.get(action.action_id) ?? [] })) });
  }));

  app.patch("/api/admin/sessions/:sessionId/actions/:actionId", asyncRoute(async (req, res) => {
    const current = await admin(req, res); if (!current) return;
    const sessionId = uuid.safeParse(req.params.sessionId); const actionId = uuid.safeParse(req.params.actionId);
    const input = z.object({ startFrame: z.number().int().nonnegative(), endFrame: z.number().int().nonnegative(), type: z.string().trim().min(1).max(50), score: z.number().min(0).max(10).nullable(), notes: z.array(z.string().trim().min(1).max(2000)).max(30) }).strict().safeParse(req.body);
    if (!sessionId.success || !actionId.success || !input.success || input.data.endFrame < input.data.startFrame) return fail(res, 400, "Check the action review and frame interval.");
    const { data: sessionState, error: sessionStateError } = await supabaseAdmin.from("sessions")
      .select("status").eq("session_id", sessionId.data).maybeSingle();
    if (sessionStateError) throw new Error(`Unable to verify session state: ${sessionStateError.message}`);
    if (!sessionState) return res.sendStatus(404);
    if (["pending", "uploading", "queued", "running"].includes(sessionState.status)) return fail(res, 409, "Actions cannot be changed while the session is running.");
    const notes = [...new Set(input.data.notes)];
    const remote = await analysisRequest(`/sessions/${sessionId.data}/actions/${actionId.data}`, current.user.id, "PATCH", true, {
      startFrame: input.data.startFrame,
      endFrame: input.data.endFrame,
      type: input.data.type,
    });
    const remoteResult = await remote.json().catch(() => ({}));
    if (!remote.ok) return fail(res, remote.status, (remoteResult as { detail?: string }).detail ?? "Unable to update action data.");
    const { data: existing, error: readError } = await supabaseAdmin.from("annotations")
      .select("score,annotations_notes(note)").eq("action_id", actionId.data).maybeSingle();
    if (readError) throw new Error(`Unable to load action review: ${readError.message}`);
    if (input.data.score === null && notes.length === 0) {
      if (existing) {
        const { error } = await supabaseAdmin.from("annotations").delete().eq("action_id", actionId.data);
        if (error) throw new Error(`Unable to remove action review: ${error.message}`);
      }
      return res.json({ ok: true });
    }
    const previousNotes = (existing?.annotations_notes ?? []).map((row) => row.note);
    const previousScore = existing?.score == null ? null : Number(existing.score);
    const reviewUnchanged = existing && previousScore === input.data.score
      && previousNotes.length === notes.length && previousNotes.every((note) => notes.includes(note));
    if (reviewUnchanged) return res.json({ ok: true });
    const { error } = await supabaseAdmin.rpc("save_action_review", {
      p_session_id: sessionId.data, p_action_id: actionId.data,
      p_user_id: current.user.id, p_score: input.data.score, p_notes: notes,
    });
    if (error) throw new Error(`Unable to save action review: ${error.message}`);
    return res.json({ ok: true });
  }));

  app.delete("/api/admin/users/:userId", asyncRoute(async (req, res) => {
    const current = await admin(req, res); if (!current) return;
    const id = uuid.safeParse(req.params.userId); if (!id.success) return res.sendStatus(404);
    if (id.data === current.user.id) return fail(res, 400, "You cannot delete your own administrator account.");
    const mode = z.enum(["keep_data", "delete_data"]).safeParse(req.body?.mode);
    if (!mode.success) return fail(res, 400, "Choose whether to keep or delete this account's players and sessions.");
    const { data: target, error } = await supabaseAdmin.from("users").select("user_id,account_role,image_path").eq("user_id", id.data).maybeSingle();
    if (error) throw new Error(`Unable to load account: ${error.message}`);
    if (!target) return res.sendStatus(404);
    const { data: players, error: playersError } = await supabaseAdmin.from("players")
      .select("player_id,image_path").eq("user_id", id.data);
    if (playersError) throw new Error(`Unable to load account players: ${playersError.message}`);
    if (mode.data === "delete_data") {
      const playerIds = (players ?? []).map((player) => player.player_id);
      const direct = await supabaseAdmin.from("sessions").select("session_id").eq("user_id", id.data);
      if (direct.error) throw new Error(`Unable to load account sessions: ${direct.error.message}`);
      const linked = playerIds.length
        ? await supabaseAdmin.from("sessions").select("session_id").in("player_id", playerIds)
        : { data: [], error: null };
      if (linked.error) throw new Error(`Unable to load player sessions: ${linked.error.message}`);
      const sessionIds = [...new Set([...(direct.data ?? []), ...(linked.data ?? [])].map((row) => row.session_id))];
      for (const sessionId of sessionIds) await deleteStoredSession(sessionId, current.user.id, true);
      if (playerIds.length) {
        const { error: playerDeleteError } = await supabaseAdmin.from("players").delete().in("player_id", playerIds);
        if (playerDeleteError) throw new Error(`Unable to delete account players: ${playerDeleteError.message}`);
      }
      await removeImages((players ?? []).map((player) => player.image_path));
    }
    const { error: assignmentError } = await supabaseAdmin.from("users_subscriptions").delete().eq("user_id", id.data);
    if (assignmentError) throw new Error(`Unable to remove account subscription: ${assignmentError.message}`);
    const { error: authError } = await supabaseAdmin.auth.admin.deleteUser(id.data);
    if (authError) throw new Error(`Unable to remove authentication account: ${authError.message}`);
    const { error: profileError } = await supabaseAdmin.from("users").delete().eq("user_id", id.data);
    if (profileError) throw new Error(`Authentication account was removed but profile cleanup failed: ${profileError.message}`);
    await removeImages([target.image_path])
      .catch((imageError) => console.error("Account was deleted but some profile images could not be removed", imageError));
    return res.sendStatus(204);
  }));
}