Spaces:

Shoraky
/

Sporalize-api

Running

Stream session video ranges directly from storage
a66365f about 2 hours ago
72.6 kB
import { activeSubscriptionFor } from "./subscriptions.js";
import express, { type NextFunction, type Request, type Response } from "express";
import cors from "cors";
import cookieParser from "cookie-parser";
import helmet from "helmet";
import rateLimit from "express-rate-limit";
import { Readable } from "node:stream";
import { pipeline } from "node:stream/promises";
import { z } from "zod";
import { authPublicOrigin, env } from "./config.js";
import { analysisRouter } from "./analysis.js";
import { actionReferences, analysisResults, generateSessionFeedback } from "./agent-session.js";
import { authenticatedUser, clearPasswordRecoveryCookie, clearSessionCookies, hasPasswordRecoveryCookie, safeRedirect, setPasswordRecoveryCookie, setSessionCookies } from "./auth.js";
import { hashPassword, oauthClient, supabaseAdmin, supabaseAuth, syncProfile, userHasPassword, userPayload } from "./supabase.js";
import { registerProfileImageRoutes } from "./profile-images.js";
import { registerAdminRoutes } from "./admin.js";
import { colabWorkerRouter } from "./colab-worker.js";
import { deleteStoredSession, refundAdminUsage } from "./resource-deletion.js";
import { singleFlight } from "./single-flight.js";
import { requestTiming } from "./request-timing.js";
import { readBatches } from "./read-batches.js";
import { readPages } from "./read-pages.js";

const app = express();
app.disable("x-powered-by");
app.set("trust proxy", env.TRUST_PROXY_HOPS);
app.use(requestTiming);
app.use(helmet());
app.use(cors({
  origin: env.FRONTEND_URL,
  credentials: true,
  methods: ["GET", "POST", "PUT", "PATCH", "DELETE", "OPTIONS"],
}));
app.use("/internal/analysis", analysisRouter(supabaseAdmin, env.ANALYSIS_PROXY_SECRET,
  (sessionId, userId) => generateSessionFeedback(supabaseAdmin, sessionId, userId)));
app.use(express.json({ limit: "32kb", type: ["application/json", "text/plain"] }));
app.use("/internal/colab-worker", colabWorkerRouter(env.ANALYSIS_PROXY_SECRET, env.COLAB_WORKER_SECRET));
app.use(cookieParser());
app.use((req, res, next) => {
  if (["GET", "HEAD", "OPTIONS"].includes(req.method)) return next();
  if (req.header("origin") !== new URL(env.FRONTEND_URL).origin) return res.sendStatus(403);
  return next();
});

// Browser POST transport avoids HF edge preflight handling. Origin checks above
// still apply before restoring the method, and every route retains its auth gate.
app.use((req, res, next) => {
  if (req.query._method === undefined) return next();
  if (req.method !== "POST" || !req.path.startsWith("/api/") ||
      typeof req.query._method !== "string" || !["PUT", "PATCH", "DELETE"].includes(req.query._method)) {
    return res.status(400).json({ error: "Invalid request method." });
  }
  req.method = req.query._method;
  const target = new URL(req.originalUrl, "http://localhost");
  target.searchParams.delete("_method");
  req.url = req.originalUrl = target.pathname + target.search;
  return next();
});

const authLimiter = rateLimit({ windowMs: 15 * 60 * 1000, limit: 25, standardHeaders: "draft-8", legacyHeaders: false });
const passwordResetRequestLimiter = rateLimit({ windowMs: 60 * 60 * 1000, limit: 5, standardHeaders: "draft-8", legacyHeaders: false });
const passwordRecoveryLimiter = rateLimit({ windowMs: 15 * 60 * 1000, limit: 15, standardHeaders: "draft-8", legacyHeaders: false });
const apiLimiter = rateLimit({ windowMs: 15 * 60 * 1000, limit: 3000, standardHeaders: "draft-8", legacyHeaders: false });
const analysisUploadLimiter = rateLimit({ windowMs: 15 * 60 * 1000, limit: 4, standardHeaders: "draft-8", legacyHeaders: false });
const signupSchema = z.object({ fname: z.string().trim().min(1).max(100), lname: z.string().trim().min(1).max(100), email: z.string().trim().toLowerCase().email().max(255), password: z.string().min(8).max(128), academy: z.string().trim().max(150).optional() }).strict();
const loginSchema = z.object({ email: z.string().trim().toLowerCase().email(), password: z.string().min(1).max(128) });
const passwordResetRequestSchema = z.object({ email: z.string().trim().toLowerCase().email().max(255) });
const passwordRecoveryVerifySchema = z.object({ tokenHash: z.string().trim().min(1).max(512) });
const passwordRecoverySchema = z.object({ newPassword: z.string().min(8).max(128) }).strict();
const profileRoles = ["football_player", "coach", "club_manager", "academy_manager", "scout", "performance_analyst", "sports_director", "other"] as const;
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).transform((value) => value || null).nullable(),
  role: z.enum(profileRoles).nullable(),
}).strict();
const passwordSchema = z.object({ currentPassword: z.string().max(128).optional(), newPassword: z.string().min(8).max(128) });
const actionReviewSchema = z.object({
  coachScore: z.number().int().min(0).max(10).nullable(),
  feedback: z.array(z.string().trim().min(1).max(2000)).max(30),
}).strict();
const playerProfileSchema = z.object({
  fname: z.string().trim().min(1).max(100).optional(),
  lname: z.string().trim().max(100).nullable().optional(),
  name: z.string().trim().max(200).optional(),
  bdate: z.string().regex(/^\d{4}-\d{2}-\d{2}$/),
  heightCm: z.string().regex(/^\d{2,3}(?:\.\d{1,2})?$/),
  weightKg: z.string().regex(/^\d{1,3}(?:\.\d{1,2})?$/),
  club: z.string().trim().max(150),
  position: z.string().trim().min(1).max(50),
  gender: z.enum(["male", "female"]),
}).strict().transform((input) => {
  const legacyNameParts = (input.name ?? "").split(/\s+/).filter(Boolean);
  const fname = input.fname ?? legacyNameParts.shift() ?? "";
  const lname = input.lname ?? legacyNameParts.join(" ");
  return { ...input, fname, lname };
}).refine((input) => Boolean(input.fname), { message: "First name is required." })
  .refine((input) => validPlayerBirthDate(input.bdate), { path: ["bdate"], message: "Enter a valid birth date for a player aged 4 to 60." });
const playerCreateSchema = z.object({
  fname: z.string().trim().min(1).max(100),
  lname: z.string().trim().max(100).nullable().optional().default(null),
  bdate: z.string().regex(/^\d{4}-\d{2}-\d{2}$/),
  heightCm: z.string().regex(/^\d{2,3}(?:\.\d{1,2})?$/),
  weightKg: z.string().regex(/^\d{1,3}(?:\.\d{1,2})?$/),
  club: z.string().trim().max(150),
  position: z.string().trim().min(1).max(50),
  gender: z.enum(["male", "female"]),
}).strict().refine((input) => validPlayerBirthDate(input.bdate), { path: ["bdate"], message: "Enter a valid birth date for a player aged 4 to 60." });

function validPlayerBirthDate(value: string) {
  const date = new Date(`${value}T00:00:00.000Z`);
  return !Number.isNaN(date.valueOf()) && date.toISOString().slice(0, 10) === value &&
    Number(ageFromBirthday(value)) >= 4 && Number(ageFromBirthday(value)) <= 60;
}

function ageFromBirthday(birthday: string | null): string {
  if (!birthday) return "";
  const today = new Date();
  const [year, month, day] = birthday.slice(0, 10).split("-").map(Number);
  if (!year || !month || !day) return "";
  const beforeBirthday = today.getUTCMonth() + 1 < month || (today.getUTCMonth() + 1 === month && today.getUTCDate() < day);
  return String(Math.max(0, today.getUTCFullYear() - year - Number(beforeBirthday)));
}

registerProfileImageRoutes(app);

// Stored coach ratings use a 0–10 scale. Player dashboards use a 0–100 scale.
const meanPlayerScore = (values: number[]) => values.length
  ? values.reduce((sum, value) => sum + value, 0) / values.length * 10
  : null;

const PLAYER_SKILL_CATEGORIES = [
  { axis: "Pass", aliases: ["pass", "passing"] },
  { axis: "Shoot", aliases: ["shoot", "shooting", "shot"] },
  { axis: "Receive", aliases: ["receive", "receiving", "reception"] },
  { axis: "Dribbling", aliases: ["dribble", "dribbling"] },
  { axis: "Pace", aliases: ["pace"] },
] as const;

function playerSkillRatings(
  actions: Array<{ action_id: string; type: string }>,
  scoreByAction: Map<string, number | null>,
) {
  return PLAYER_SKILL_CATEGORIES.map(({ axis, aliases }) => {
    const names = new Set<string>(aliases);
    const scores = actions
      .filter(({ type }) => names.has(type.trim().toLowerCase().replace(/[^a-z0-9]/g, "")))
      .map(({ action_id }) => scoreByAction.get(action_id))
      .filter((score): score is number => score != null && Number.isFinite(score));
    return { axis, score: meanPlayerScore(scores), samples: scores.length };
  });
}

export const accountRoleFor = singleFlight(async (userId: string) => {
  const { data, error } = await supabaseAdmin.from("users").select("account_role").eq("user_id", userId).maybeSingle();
  if (error) throw new Error(`Unable to load account permissions: ${error.message}`);
  return String(data?.account_role ?? "user").toLowerCase();
});

export async function userIsAdmin(userId: string) {
  return await accountRoleFor(userId) === "admin";
}

export async function requireAdmin(req: Request, res: Response) {
  const current = await authenticatedUser(req, res);
  if (!current) {
    res.status(401).json({ error: "Authentication required." });
    return null;
  }
  if (!await userIsAdmin(current.user.id)) {
    res.status(403).json({ error: "Administrator access is required." });
    return null;
  }
  return current;
}

type ScoredAction = { action_id: string; session_id: string; type: string; annotation: { score: number | null } | null };
async function loadScoredActions(sessionIds: string[]) {
  return readBatches<string, ScoredAction>(sessionIds, (batch) => readPages(async (from, to) => {
    const { data, error } = await supabaseAdmin.from("actions")
      .select("action_id,session_id,type,annotation:annotations(score)").in("session_id", batch)
      .order("action_id").range(from, to);
    if (error) throw new Error(`Unable to load action scores: ${error.message}`);
    return (data ?? []) as unknown as ScoredAction[];
  }));
}


async function requireActiveSubscription(req: Request, res: Response) {
  const current = await authenticatedUser(req, res);
  if (!current) {
    res.status(401).json({ error: "Authentication required." });
    return null;
  }
  if (await userIsAdmin(current.user.id)) {
    return { ...current, isAdmin: true, subscription: { player_limit: null, session_limit: null } };
  }
  const subscription = await activeSubscriptionFor(current.user.id, { refresh: true });
  if (!subscription) {
    res.status(402).json({ error: "An active subscription is required to access Sporalize Labs.", code: "subscription_required" });
    return null;
  }
  return { ...current, subscription };
}

registerAdminRoutes(app);

async function enforcePlayerQuota(userId: string, limit: number | null, res: Response) {
  const { data, error } = await supabaseAdmin.from("users_subscriptions")
    .select("num_players").eq("user_id", userId).maybeSingle();
  if (error) throw new Error(`Unable to check subscription usage: ${error.message}`);
  const used = Number(data?.num_players ?? 0);
  if (limit === null || used < limit) return true;
  res.status(403).json({
    error: "You have used all player slots included in this plan period. Renew or upgrade your plan to add another player.",
    code: "quota_exceeded", resource: "players", used, limit,
  });
  return false;
}

app.get("/health", (_req, res) => res.json({ ok: true }));

app.get("/api/subscriptions", async (_req, res, next) => {
  try {
    const { data, error } = await supabaseAdmin.from("subscriptions")
      .select("subscription_id,plan,price,player_limit,session_limit")
      .order("price", { ascending: true });
    if (error) throw new Error(`Unable to load subscription plans: ${error.message}`);
    const plans = (data ?? []).map((row) => ({
      id: row.plan.trim().toLowerCase().replace(/[^a-z0-9]+/g, "-"),
      subscriptionId: row.subscription_id,
      name: row.plan,
      monthlyPrice: Number(row.price),
      playerLimit: row.player_limit == null ? null : Number(row.player_limit),
      sessionLimit: row.session_limit == null ? null : Number(row.session_limit),
    }));
    return res.json({ plans });
  } catch (error) { next(error); }
});

app.get("/api/subscription/access", async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Authentication required." });
    const admin = await userIsAdmin(current.user.id);
    const subscription = admin ? null : await activeSubscriptionFor(current.user.id, { refresh: true });
    return res.json({ active: admin || Boolean(subscription), subscription, isAdmin: admin });
  } catch (error) { next(error); }
});

app.get("/api/plan-billing", async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Authentication required." });
    const userId = current.user.id;
    const [isAdmin, assignmentResult] = await Promise.all([
      userIsAdmin(userId),
      supabaseAdmin.from("users_subscriptions")
        .select("subscription_id,startdate,enddate,num_players,num_sessions,plan:subscriptions(subscription_id,plan,price,player_limit,session_limit)")
        .eq("user_id", userId).maybeSingle(),
    ]);
    if (assignmentResult.error) throw new Error(`Unable to load plan assignment: ${assignmentResult.error.message}`);
    const assignment = assignmentResult.data;
    let subscription = null;
    if (!isAdmin && assignment?.subscription_id) {
      const plan = assignment.plan as unknown as {
        subscription_id: string; plan: string; price: number; player_limit: number | null; session_limit: number | null;
      } | null;
      if (plan) {
        const today = new Date().toISOString().slice(0, 10);
        subscription = {
          subscriptionId: plan.subscription_id,
          name: plan.plan,
          price: Number(plan.price),
          playerLimit: plan.player_limit == null ? null : Number(plan.player_limit),
          sessionLimit: plan.session_limit == null ? null : Number(plan.session_limit),
          startDate: assignment.startdate,
          endDate: assignment.enddate,
          status: today < assignment.startdate ? "scheduled" : today > assignment.enddate ? "expired" : "active",
        };
      }
    }
    let usage = { players: Number(assignment?.num_players ?? 0), sessions: Number(assignment?.num_sessions ?? 0) };
    if (isAdmin) {
      const [players, sessions] = await Promise.all([
        supabaseAdmin.from("players").select("player_id", { count: "exact", head: true }).eq("user_id", userId),
        supabaseAdmin.from("sessions").select("session_id", { count: "exact", head: true }).eq("user_id", userId),
      ]);
      if (players.error) throw new Error(`Unable to load administrator player usage: ${players.error.message}`);
      if (sessions.error) throw new Error(`Unable to load administrator session usage: ${sessions.error.message}`);
      usage = { players: players.count ?? 0, sessions: sessions.count ?? 0 };
    }
    res.set("Cache-Control", "no-store");
    return res.json({
      subscription: isAdmin ? null : subscription,
      usage,
      billingHistoryAvailable: false,
      isAdmin,
    });
  } catch (error) { next(error); }
});

app.get("/api/owned-players", async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Authentication required." });
    const admin = await userIsAdmin(current.user.id);
    let query = supabaseAdmin.from("players")
      .select("player_id,user_id,fname,lname,position,club,gender,image_path")
      .order("fname", { ascending: true });
    if (!admin) query = query.eq("user_id", current.user.id);
    const { data, error } = await query;
    if (error) throw new Error(`Unable to load players: ${error.message}`);
    // Analysis player choices stay scoped to the signed-in user's own roster.
    return res.json({ players: (data ?? []).map((player) => ({
      playerId: player.player_id,
      fname: player.fname ?? "",
      lname: player.lname ?? "",
      name: [player.fname, player.lname].filter(Boolean).join(" "),
      isOwner: player.user_id === current.user.id,
      canManage: admin || player.user_id === current.user.id,
      position: player.position ?? "",
      club: player.club ?? "",
      gender: player.gender ?? null,
      imagePath: player.image_path ?? null,
    })) });
  } catch (error) { next(error); }
});

app.post("/api/players", async (req, res, next) => {
  try {
    const current = await requireActiveSubscription(req, res);
    if (!current) return;
    if (!await enforcePlayerQuota(current.user.id, current.subscription.player_limit == null ? null : Number(current.subscription.player_limit), res)) return;
    const input = playerCreateSchema.parse(req.body);
    const height = Number(input.heightCm);
    const weight = Number(input.weightKg);
    if (height < 100 || height > 230 || weight < 20 || weight > 150) {
      return res.status(400).json({ error: "Check the player's age, height and weight." });
    }
    const { data, error } = await supabaseAdmin.from("players").insert({
      user_id: current.user.id,
      fname: input.fname,
      lname: input.lname?.trim() || null,
      bdate: input.bdate,
      height,
      weight,
      club: input.club || null,
      position: input.position,
      gender: input.gender,
    }).select("player_id,fname,lname,position,club,gender,image_path").single();
    if (error?.code === "P0001" && error.message.includes("subscription_quota_exceeded")) {
      return res.status(403).json({ error: "You have used all player slots included in this plan period. Renew or upgrade your plan to add another player.", code: "quota_exceeded", resource: "players" });
    }
    if (error?.code === "P0001" && error.message.includes("subscription_required")) {
      return res.status(402).json({ error: "An active subscription is required to add a player.", code: "subscription_required" });
    }
    if (error) throw new Error(`Unable to create player: ${error.message}`);
    return res.status(201).json({ player: {
      playerId: data.player_id,
      fname: data.fname ?? "",
      lname: data.lname ?? "",
      name: [data.fname, data.lname].filter(Boolean).join(" "),
      position: data.position ?? "",
      club: data.club ?? "",
      gender: data.gender ?? null,
      imagePath: data.image_path ?? null,
    } });
  } catch (error) { next(error); }
});

app.put("/api/players/:playerId", async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Authentication required." });
    const id = z.string().uuid().safeParse(req.params.playerId);
    if (!id.success) return res.status(404).json({ error: "Player not found." });
    const isAdmin = await userIsAdmin(current.user.id);
    const input = playerProfileSchema.parse(req.body);
    const height = Number(input.heightCm);
    const weight = Number(input.weightKg);
    if (height < 100 || height > 230 || weight < 20 || weight > 150) {
      return res.status(400).json({ error: "Check the player's age, height and weight." });
    }
    let updateQuery = supabaseAdmin.from("players").update({
      fname: input.fname,
      lname: input.lname?.trim() || null,
      bdate: input.bdate,
      height,
      weight,
      club: input.club || null,
      position: input.position,
      gender: input.gender,
    }).eq("player_id", id.data);
    if (!isAdmin) updateQuery = updateQuery.eq("user_id", current.user.id);
    const { data, error } = await updateQuery
      .select("player_id,fname,lname,bdate,height,weight,club,position,gender,image_path").maybeSingle();
    if (error) throw new Error(`Unable to update player: ${error.message}`);
    if (!data) return res.status(404).json({ error: "Player not found." });
    return res.json({ ok: true, player: {
      playerId: data.player_id, fname: data.fname ?? "", lname: data.lname ?? "",
      name: [data.fname, data.lname].filter(Boolean).join(" "), bdate: data.bdate,
      age: ageFromBirthday(data.bdate), heightCm: String(data.height ?? ""),
      weightKg: String(data.weight ?? ""), club: data.club ?? "", position: data.position ?? "",
      gender: data.gender ?? null, imagePath: data.image_path ?? null,
    } });
  } catch (error) { next(error); }
});

app.delete("/api/players/:playerId", async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Authentication required." });
    const playerId = z.string().uuid().safeParse(req.params.playerId);
    if (!playerId.success) return res.status(404).json({ error: "Player not found." });

    const isAdmin = await userIsAdmin(current.user.id);
    let playerQuery = supabaseAdmin.from("players")
      .select("player_id,user_id,image_path").eq("player_id", playerId.data);
    if (!isAdmin) playerQuery = playerQuery.eq("user_id", current.user.id);
    const { data: player, error: playerError } = await playerQuery.maybeSingle();
    if (playerError) throw new Error(`Unable to load player for deletion: ${playerError.message}`);
    if (!player) return res.status(404).json({ error: "Player not found." });

    const mode = z.enum(["keep_sessions", "delete_sessions"]).safeParse(req.body?.mode);
    if (!mode.success) return res.status(400).json({ error: "Choose whether to keep or delete this player's sessions." });
    const { data: linkedSessions, error: sessionsError } = await supabaseAdmin.from("sessions")
      .select("session_id,user_id,by_admin").eq("player_id", playerId.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, isAdmin);
    }

    let deleteQuery = supabaseAdmin.from("players").delete().eq("player_id", playerId.data);
    if (!isAdmin) deleteQuery = deleteQuery.eq("user_id", current.user.id);
    const { data: deleted, error: deleteError } = await deleteQuery.select("player_id").maybeSingle();
    if (deleteError) throw new Error(`Unable to delete player: ${deleteError.message}`);
    if (!deleted) return res.status(404).json({ error: "Player not found." });

    if (isAdmin) {
      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);
    }

    const imagePath = player.image_path;
    if (imagePath?.startsWith(`players/${playerId.data}/`) && /^[0-9a-f-]{36}\.(jpg|png|webp)$/.test(imagePath.slice(`players/${playerId.data}/`.length))) {
      const { error: imageError } = await supabaseAdmin.storage.from("profile-images").remove([imagePath]);
      if (imageError) console.error("Player deleted but profile image cleanup failed", imageError);
    }
    return res.status(204).send();
  } catch (error) { next(error); }
});

// Non-admin roster reads are scoped to the signed-in player's own roster.
app.get("/api/players-overview", async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Authentication required." });

    const isAdmin = await userIsAdmin(current.user.id);
    // These reads share only the authenticated scope, so start them together.
    const [rows, visibleSessions] = await Promise.all([
      readPages(async (from, to) => {
        let query = supabaseAdmin.from("players")
          .select("player_id,user_id,fname,lname,bdate,height,weight,position,club,gender,image_path")
          .order("fname").order("player_id").range(from, to);
        if (!isAdmin) query = query.eq("user_id", current.user.id);
        const { data, error } = await query;
        if (error) throw new Error(`Unable to load players: ${error.message}`);
        return data ?? [];
      }),
      readPages(async (from, to) => {
        let query = supabaseAdmin.from("sessions").select("session_id,player_id,name,created_at,user_id")
          .order("created_at", { ascending: false }).order("session_id").range(from, to);
        if (!isAdmin) query = query.eq("user_id", current.user.id);
        const { data, error } = await query;
        if (error) throw new Error(`Unable to load player sessions: ${error.message}`);
        return data ?? [];
      }),
    ]);
    const playerIds = new Set(rows.map((row) => row.player_id));
    const sessions = visibleSessions.filter((row) => playerIds.has(row.player_id));

    const sessionIds = (sessions ?? []).map((row) => row.session_id);
    const actions = await loadScoredActions(sessionIds);
    const scoresByAction = new Map(actions.map((row) => [row.action_id, row.annotation?.score == null ? null : Number(row.annotation.score)]));
    const sessionsByPlayer = new Map<string, typeof sessions>();
    for (const session of sessions ?? []) {
      const group = sessionsByPlayer.get(session.player_id) ?? [];
      group.push(session);
      sessionsByPlayer.set(session.player_id, group);
    }
    const actionsBySession = new Map<string, typeof actions>();
    for (const action of actions) {
      const group = actionsBySession.get(action.session_id) ?? [];
      group.push(action);
      actionsBySession.set(action.session_id, group);
    }

    const players = (rows ?? []).map((row) => {
      const playerSessions = sessionsByPlayer.get(row.player_id) ?? [];
      const playerActions = playerSessions.flatMap((session) => actionsBySession.get(session.session_id) ?? []);
      const scored = playerActions.map((action) => scoresByAction.get(action.action_id)).filter((score): score is number => score != null && Number.isFinite(score));
      const byAction = [...new Set(playerActions.map((action) => action.type))].map((type) => {
        const matching = playerActions.filter((action) => action.type === type);
        const values = matching.map((action) => scoresByAction.get(action.action_id)).filter((score): score is number => score != null && Number.isFinite(score));
        return { axis: type, score: meanPlayerScore(values), samples: values.length };
      });
      const playerScoreSessions = playerSessions.map((session) => {
        const sessionActions = actionsBySession.get(session.session_id) ?? [];
        const values = sessionActions.map((action) => scoresByAction.get(action.action_id)).filter((score): score is number => score != null && Number.isFinite(score));
        return { sessionId: session.session_id, createdAt: Date.parse(session.created_at) || 0, score: meanPlayerScore(values), actionCount: sessionActions.length, scoredCount: values.length };
      });
      return {
        playerId: row.player_id,
        isOwner: row.user_id === current.user.id,
        fname: row.fname ?? "",
        lname: row.lname ?? "",
        name: [row.fname, row.lname].filter(Boolean).join(" "),
        age: ageFromBirthday(row.bdate),
        bdate: row.bdate,
        heightCm: row.height == null ? "" : String(row.height),
        weightKg: row.weight == null ? "" : String(row.weight),
        club: row.club ?? "", position: row.position ?? "", gender: row.gender ?? null,
        imagePath: row.image_path ?? null,
        createdAt: "", updatedAt: "", hasProfile: true,
        sessionCount: playerSessions.length, actionCount: playerActions.length,
        lastSessionAt: playerScoreSessions[0]?.createdAt ?? 0,
        scores: {
          playerId: row.player_id, rubricVersion: "database", totalActions: playerActions.length,
          scoredActions: scored.length, byAction, byQuality: [],
          overall: meanPlayerScore(scored),
          sessions: playerScoreSessions,
        },
      };
    });

    return res.json({ players });
  } catch (error) { next(error); }
});

app.get("/api/players-overview/:playerId", async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Authentication required." });
    const playerId = z.string().uuid().safeParse(req.params.playerId);
    if (!playerId.success) return res.status(404).json({ error: "Player not found." });
    const isAdmin = await userIsAdmin(current.user.id);
    let playerQuery = supabaseAdmin.from("players").select("player_id,user_id,fname,lname,bdate,height,weight,position,club,gender,image_path")
      .eq("player_id", playerId.data);
    if (!isAdmin) playerQuery = playerQuery.eq("user_id", current.user.id);
    const { data, error } = await playerQuery.maybeSingle();
    if (error) throw new Error(`Unable to load player: ${error.message}`);
    if (!data) return res.status(404).json({ error: "Player not found." });

    const [overview, sessions] = await Promise.all([
      readPages(async (from, to) => {
        let query = supabaseAdmin.from("players").select("player_id").order("player_id").range(from, to);
        if (!isAdmin) query = query.eq("user_id", current.user.id);
        const { data, error } = await query;
        if (error) throw new Error(`Unable to load players: ${error.message}`);
        return data ?? [];
      }),
      readPages(async (from, to) => {
        let query = supabaseAdmin.from("sessions").select("session_id,player_id,name,created_at,user_id")
          .order("created_at", { ascending: false }).order("session_id").range(from, to);
        if (!isAdmin) query = query.eq("user_id", current.user.id);
        const { data, error } = await query;
        if (error) throw new Error(`Unable to load sessions: ${error.message}`);
        return data ?? [];
      }),
    ]);
    const actions = await loadScoredActions((sessions ?? []).map((row) => row.session_id));
    const scoreMap = new Map(actions.map((row) => [row.action_id, row.annotation?.score == null ? null : Number(row.annotation.score)]));
    const ownSessions = (sessions ?? []).filter((row) => row.player_id === playerId.data);
    const ownSessionIds = new Set(ownSessions.map((session) => session.session_id));
    const ownActions = (actions ?? []).filter((row) => ownSessionIds.has(row.session_id));
    const ownScores = ownActions.map((row) => scoreMap.get(row.action_id)).filter((score): score is number => score != null && Number.isFinite(score));
    const byAction = playerSkillRatings(ownActions, scoreMap);
    const sessionPlayerById = new Map((sessions ?? []).map((row) => [row.session_id, row.player_id]));
    const squadActions = (actions ?? []).filter((action) => {
      const ownerId = sessionPlayerById.get(action.session_id);
      return Boolean(ownerId) && ownerId !== playerId.data;
    });
    const squadByAction = playerSkillRatings(squadActions, scoreMap);
    const player = data;
    if (!player) return res.status(404).json({ error: "Player not found." });
    const sessionsForPlayer = ownSessions.map((row) => {
      const sessionActions = ownActions.filter((action) => action.session_id === row.session_id);
      const values = sessionActions.map((action) => scoreMap.get(action.action_id)).filter((score): score is number => score != null && Number.isFinite(score));
      return { sessionId: row.session_id, createdAt: Date.parse(row.created_at) || 0, score: meanPlayerScore(values), actionCount: sessionActions.length, scoredCount: values.length };
    });
    const playerScores = new Map<string, number[]>();
    for (const action of actions ?? []) {
      const ownerId = sessionPlayerById.get(action.session_id);
      const score = scoreMap.get(action.action_id);
      if (ownerId && score != null && Number.isFinite(score)) {
        const values = playerScores.get(ownerId) ?? [];
        values.push(score);
        playerScores.set(ownerId, values);
      }
    }
    const rankedPlayers = [...playerScores.entries()]
      .map(([id, values]) => ({ id, score: meanPlayerScore(values)! }))
      .sort((a, b) => b.score - a.score);
    const rankIndex = rankedPlayers.findIndex((entry) => entry.id === playerId.data);
    const squadAverage = { byQuality: [], byAction: squadByAction };
    return res.json({
      playerId: player.player_id, fname: player.fname ?? "", lname: player.lname ?? "", name: [player.fname, player.lname].filter(Boolean).join(" "),
      isOwner: player.user_id === current.user.id,
      canManage: isAdmin || player.user_id === current.user.id,
      age: ageFromBirthday(player.bdate),
      bdate: player.bdate,
      heightCm: player.height == null ? "" : String(player.height), weightKg: player.weight == null ? "" : String(player.weight),
      club: player.club ?? "", position: player.position ?? "", gender: player.gender ?? null,
      imagePath: player.image_path ?? null,
      createdAt: "", updatedAt: "", hasProfile: true, sessionCount: ownSessions.length, actionCount: ownActions.length,
      lastSessionAt: sessionsForPlayer[0]?.createdAt ?? 0,
      scores: { playerId: player.player_id, rubricVersion: "database", totalActions: ownActions.length, scoredActions: ownScores.length, byAction, byQuality: [], overall: meanPlayerScore(ownScores), sessions: sessionsForPlayer },
      squadAverage, rank: rankIndex < 0 ? null : rankIndex + 1, playerCount: overview?.length ?? 0,
    });
  } catch (error) { next(error); }
});

app.get("/api/sessions-overview", async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Authentication required." });

    const isAdmin = await userIsAdmin(current.user.id);
    const sessions = await readPages(async (from, to) => {
      let query = supabaseAdmin.from("sessions")
        .select("session_id,player_id,user_id,name,path,created_at,status,by_admin,player:players(fname,lname,gender,image_path)")
        .order("created_at", { ascending: false }).order("session_id").range(from, to);
      if (!isAdmin) query = query.eq("user_id", current.user.id);
      const { data, error } = await query;
      if (error) throw new Error(`Unable to load sessions: ${error.message}`);
      return data ?? [];
    });
    const actions = await readBatches(sessions.map((row) => row.session_id), (batch) => readPages(async (from, to) => {
      const { data, error } = await supabaseAdmin.from("actions").select("action_id,session_id,type")
        .in("session_id", batch).order("action_id").range(from, to);
      if (error) throw new Error(`Unable to load session actions: ${error.message}`);
      return data ?? [];
    }));
    const breakdownBySession = new Map<string, Record<string, number>>();
    for (const action of actions ?? []) {
      const breakdown = breakdownBySession.get(action.session_id) ?? {};
      breakdown[action.type] = (breakdown[action.type] ?? 0) + 1;
      breakdownBySession.set(action.session_id, breakdown);
    }
    return res.json({ sessions: (sessions ?? []).map((row) => {
      const actionBreakdown = breakdownBySession.get(row.session_id) ?? {};
      const player = row.player as unknown as { fname: string; lname: string | null; gender: string | null; image_path: string | null } | null;
      return {
        sessionId: row.session_id, isOwner: row.user_id === current.user.id, canDelete: isAdmin || row.user_id === current.user.id, isAdmin, byAdmin: row.by_admin, playerId: row.player_id ?? "", playerName: row.player_id ? [player?.fname, player?.lname].filter(Boolean).join(" ") : "Deleted player",
        playerGender: row.player_id ? player?.gender ?? null : null,
        playerImagePath: row.player_id ? player?.image_path ?? null : null,
        name: row.name, createdAt: Date.parse(row.created_at) || 0,
        actionCount: Object.values(actionBreakdown).reduce((sum, count) => sum + count, 0),
        cameraCount: 0, actionBreakdown, path: row.path, status: row.status,
      };
    }) });
  } catch (error) { next(error); }
});

app.get("/api/branding-stats", async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Authentication required." });
    const [allPlayers, ownPlayers, allSessions, ownSessions] = await Promise.all([
      supabaseAdmin.from("players").select("player_id", { count: "exact", head: true }),
      supabaseAdmin.from("players").select("player_id", { count: "exact", head: true }).eq("user_id", current.user.id),
      supabaseAdmin.from("sessions").select("session_id", { count: "exact", head: true }),
      supabaseAdmin.from("sessions").select("session_id", { count: "exact", head: true }).eq("user_id", current.user.id),
    ]);
    const error = allPlayers.error ?? ownPlayers.error ?? allSessions.error ?? ownSessions.error;
    if (error) throw new Error(`Unable to load application statistics: ${error.message}`);
    return res.set("Cache-Control", "private, no-store").json({
      players: { total: allPlayers.count ?? 0, mine: ownPlayers.count ?? 0 },
      sessions: { total: allSessions.count ?? 0, mine: ownSessions.count ?? 0 },
    });
  } catch (error) { next(error); }
});

app.get("/api/sessions/:sessionId/reviews", async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Authentication required." });
    const sessionId = z.uuid().safeParse(req.params.sessionId);
    if (!sessionId.success) return res.sendStatus(404);

    const { data: session, error: sessionError } = await supabaseAdmin.from("sessions")
      .select("session_id,user_id").eq("session_id", sessionId.data).maybeSingle();
    if (sessionError) throw new Error(`Unable to verify review session: ${sessionError.message}`);
    if (!session) return res.sendStatus(404);
    const ownsSession = session.user_id === current.user.id;
    const isAdmin = await userIsAdmin(current.user.id);
    if (!ownsSession && !isAdmin) return res.sendStatus(404);
    const canReview = isAdmin || ownsSession;
    const reviewUnavailableReason = null;

    const { data: actions, error: actionsError } = await supabaseAdmin.from("actions")
      .select("action_id").eq("session_id", sessionId.data);
    if (actionsError) throw new Error(`Unable to load review actions: ${actionsError.message}`);
    const actionIds = (actions ?? []).map((action) => action.action_id);
    if (!actionIds.length) return res.json({ reviews: {}, canReview, reviewUnavailableReason });

    const [{ data: annotations, error: annotationsError }, { data: notes, error: notesError }] = 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),
    ]);
    if (annotationsError) throw new Error(`Unable to load action reviews: ${annotationsError.message}`);
    if (notesError) throw new Error(`Unable to load action notes: ${notesError.message}`);

    const userIds = [...new Set((annotations ?? []).map((annotation) => annotation.user_id).filter((id): id is string => Boolean(id)))];
    const { data: users, error: usersError } = userIds.length
      ? await supabaseAdmin.from("users").select("user_id,fname,lname").in("user_id", userIds)
      : { data: [], error: null };
    if (usersError) throw new Error(`Unable to load review authors: ${usersError.message}`);
    const authorNames = new Map((users ?? []).map((user) => [user.user_id, [user.fname, user.lname].filter(Boolean).join(" ").trim()]));

    const reviews: Record<string, { coachScore: number | null; feedback: string[]; feedbackAuthorName: string }> = Object.fromEntries(
      actionIds.map((actionId) => [actionId, { coachScore: null, feedback: [], feedbackAuthorName: "Unknown user" }]),
    );
    for (const annotation of annotations ?? []) {
      if (reviews[annotation.action_id]) {
        reviews[annotation.action_id].coachScore = annotation.score == null ? null : Number(annotation.score);
        reviews[annotation.action_id].feedbackAuthorName = annotation.user_id
          ? authorNames.get(annotation.user_id) || "Former user"
          : "Former user";
      }
    }
    for (const note of notes ?? []) reviews[note.action_id]?.feedback.push(note.note);
    return res.json({ reviews, canReview, reviewUnavailableReason });
  } catch (error) { next(error); }
});

app.get("/api/sessions/:sessionId/feedbacks", async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Authentication required." });
    const sessionId = z.uuid().safeParse(req.params.sessionId);
    if (!sessionId.success) return res.sendStatus(404);

    const { data: session, error: sessionError } = await supabaseAdmin.from("sessions")
      .select("session_id,user_id").eq("session_id", sessionId.data).maybeSingle();
    if (sessionError) throw new Error(`Unable to verify feedback session: ${sessionError.message}`);
    if (!session) return res.sendStatus(404);
    if (session.user_id !== current.user.id && !await userIsAdmin(current.user.id)) return res.sendStatus(404);

    const requestedActionId = req.query.actionId === undefined ? null : z.uuid().safeParse(req.query.actionId);
    if (requestedActionId && !requestedActionId.success) return res.sendStatus(404);
    let actionIds: string[] = [];
    if (requestedActionId) {
      const { data: action, error: actionError } = await supabaseAdmin.from("actions")
        .select("action_id").eq("session_id", sessionId.data).eq("action_id", requestedActionId.data).maybeSingle();
      if (actionError) throw new Error(`Unable to verify feedback action: ${actionError.message}`);
      if (!action) return res.sendStatus(404);
      actionIds = [action.action_id];
    } else {
      const { data: actions, error: actionsError } = await supabaseAdmin.from("actions")
        .select("action_id").eq("session_id", sessionId.data);
      if (actionsError) throw new Error(`Unable to load feedback actions: ${actionsError.message}`);
      actionIds = (actions ?? []).map((action) => action.action_id);
    }
    const [sessionResult, actionResult] = await Promise.all([
      requestedActionId
        ? Promise.resolve({ data: [], error: null })
        : supabaseAdmin.from("sessions_feedbacks").select("feedback").eq("session_id", sessionId.data).order("feedback"),
      actionIds.length
        ? supabaseAdmin.from("actions_feedbacks").select("action_id,feedback").in("action_id", actionIds).order("feedback")
        : Promise.resolve({ data: [], error: null }),
    ]);
    if (sessionResult.error) throw new Error(`Unable to load session feedback: ${sessionResult.error.message}`);
    if (actionResult.error) throw new Error(`Unable to load action feedback: ${actionResult.error.message}`);

    const actionFeedback: Record<string, string[]> = Object.fromEntries(actionIds.map((actionId) => [actionId, []]));
    for (const row of actionResult.data ?? []) actionFeedback[row.action_id]?.push(row.feedback);
    return res.json({ sessionFeedback: (sessionResult.data ?? []).map((row) => row.feedback), actionFeedback });
  } catch (error) { next(error); }
});

app.post("/api/agent/chat", async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Authentication required." });
    const isAdmin = await userIsAdmin(current.user.id);
    if (!isAdmin && !await activeSubscriptionFor(current.user.id, { refresh: true })) {
      return res.status(402).json({ error: "An active subscription is required to use the Agent.", code: "subscription_required" });
    }
    if (!env.AGENT_API_URL || !env.AGENT_API_KEY) {
      return res.status(503).json({ error: "The coaching Agent is not configured." });
    }
    const input = z.object({
      message: z.string().trim().min(1).max(6000),
      history: z.array(z.object({ role: z.enum(["user", "assistant"]), content: z.string().min(1).max(6000) })).max(30).default([]),
      sessionId: z.uuid().optional(), actionId: z.uuid().optional(), playerId: z.uuid().optional(),
    }).strict().parse(req.body);
    if (input.actionId && !input.sessionId) return res.status(400).json({ error: "A session is required for action context." });
    const context: Record<string, unknown> = {};
    if (input.sessionId) {
      const { data: session, error } = await supabaseAdmin.from("sessions")
        .select("session_id,user_id,name,player_id,players(fname,lname,bdate,gender,position,height,weight)")
        .eq("session_id", input.sessionId).maybeSingle();
      if (error) throw new Error(`Unable to verify chat session: ${error.message}`);
      if (!session) return res.sendStatus(404);
      if (session.user_id !== current.user.id && !isAdmin) return res.sendStatus(404);
      context.session = { name: session.name, player: session.players };
      const { data: sessionFeedback, error: sessionFeedbackError } = await supabaseAdmin.from("sessions_feedbacks")
        .select("feedback").eq("session_id", input.sessionId).order("feedback");
      if (sessionFeedbackError) throw new Error(`Unable to load chat feedback: ${sessionFeedbackError.message}`);
      context.sessionFeedback = (sessionFeedback ?? []).map((row) => row.feedback);
      if (!input.actionId) {
        const { data: sessionActions, error: sessionActionsError } = await supabaseAdmin.from("actions")
          .select("action_id,type,start_frame,end_frame,actions_feedbacks(feedback),annotations(score,annotations_notes(note))")
          .eq("session_id", input.sessionId).limit(30);
        if (sessionActionsError) throw new Error(`Unable to load session action context: ${sessionActionsError.message}`);
        context.actions = sessionActions ?? [];
      }
      if (input.actionId) {
        const { data: action, error: actionError } = await supabaseAdmin.from("actions")
          .select("action_id,type,start_frame,end_frame,annotations(score,annotations_notes(note))").eq("session_id", input.sessionId).eq("action_id", input.actionId).maybeSingle();
        if (actionError) throw new Error(`Unable to verify chat action: ${actionError.message}`);
        if (!action) return res.sendStatus(404);
        const { data: feedback, error: feedbackError } = await supabaseAdmin.from("actions_feedbacks")
          .select("feedback").eq("action_id", input.actionId).order("feedback");
        if (feedbackError) throw new Error(`Unable to load chat feedback: ${feedbackError.message}`);
        const results = await analysisResults(input.sessionId, current.user.id, isAdmin);
        const measured = results.actions.find((item) => item.id === input.actionId);
        if (!measured) return res.status(404).json({ error: "Analysis results for this action are unavailable." });
        const references = await actionReferences(supabaseAdmin, measured, action.type, results);
        context.action = { ...action,
          results: { metrics: measured.inActionMetrics ?? [], analytics: measured.rawAnalytics ?? {} },
          references,
          feedback: (feedback ?? []).map((row) => row.feedback) };
      }
    }
    if (input.playerId && !input.sessionId) {
      const { data: player, error } = await supabaseAdmin.from("players")
        .select("user_id,fname,lname,bdate,height,weight,position,gender").eq("player_id", input.playerId).maybeSingle();
      if (error) throw new Error(`Unable to load chat player context: ${error.message}`);
      if (!player) return res.sendStatus(404);
      if (player.user_id !== current.user.id && !isAdmin) return res.sendStatus(404);
      context.player = player;
    }
    const agentHeaders: Record<string, string> = { "content-type": "application/json", "x-api-key": env.AGENT_API_KEY, "x-user-id": current.user.id };
    if (env.HF_TOKEN) agentHeaders.authorization = `Bearer ${env.HF_TOKEN}`;
    const upstream = await fetch(new URL("/api/chat", env.AGENT_API_URL), {
      method: "POST", headers: agentHeaders,
      body: JSON.stringify({ actions: [], context, message: input.message, history: input.history }),
      signal: AbortSignal.timeout(95000),
    });
    const result = await upstream.json().catch(() => ({}));
    if (!upstream.ok) return res.status(upstream.status).json({ error: typeof result.detail === "string" ? result.detail : "The coaching Agent could not answer." });
    return res.json(result);
  } catch (error) { next(error); }
});

app.put("/api/sessions/:sessionId/actions/:actionId/review", async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Authentication required." });
    const sessionId = z.uuid().safeParse(req.params.sessionId);
    const actionId = z.uuid().safeParse(req.params.actionId);
    const input = actionReviewSchema.safeParse(req.body);
    if (!sessionId.success || !actionId.success || !input.success) {
      return res.status(400).json({ error: "Check the action score and feedback points." });
    }

    const { data: session, error: sessionError } = await supabaseAdmin.from("sessions")
      .select("session_id,user_id").eq("session_id", sessionId.data).maybeSingle();
    if (sessionError) throw new Error(`Unable to verify review session: ${sessionError.message}`);
    if (!session) return res.sendStatus(404);
    // An action has one annotation row and one notes collection. Its session
    // owner or an administrator can replace that shared review.
    const isAdmin = await userIsAdmin(current.user.id);
    if (session.user_id !== current.user.id && !isAdmin) {
      return res.status(403).json({ error: "Only the session owner or an administrator can save its action review." });
    }
    const { data: action, error: actionError } = await supabaseAdmin.from("actions")
      .select("action_id").eq("action_id", actionId.data).eq("session_id", sessionId.data).maybeSingle();
    if (actionError) throw new Error(`Unable to verify review action: ${actionError.message}`);
    if (!action) return res.sendStatus(404);

    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.coachScore,
      p_notes: [...new Set(input.data.feedback)],
    });
    if (error) {
      console.error("Unable to save action review", { code: error.code, message: error.message });
      throw new Error(`Unable to save action review: ${error.message}`);
    }
    const { data: author, error: authorError } = await supabaseAdmin.from("users")
      .select("fname,lname").eq("user_id", current.user.id).maybeSingle();
    if (authorError) throw new Error(`Unable to load review author: ${authorError.message}`);
    const authorName = [author?.fname, author?.lname].filter(Boolean).join(" ").trim() || "Unknown user";
    return res.json({ ok: true, authorName });
  } catch (error) { next(error); }
});

// Session deletion is handled by the AI service through the proxy below so
// the session folder and database records are deleted together.

app.post("/api/auth/signup", authLimiter, async (req, res, next) => {
  try {
    const input = signupSchema.parse(req.body);
    const passwordHash = await hashPassword(input.password);
    const { data, error } = await supabaseAuth.auth.signUp({ email: input.email, password: input.password, options: { data: { fname: input.fname, lname: input.lname, academy: input.academy ?? null } } });
    if (error) {
      console.error("Supabase signup failed", { status: error.status, code: error.code, message: error.message });
      return res.status(400).json({ error: env.NODE_ENV === "development" ? error.message : "Unable to create the account with these details." });
    }
    if (data.user?.identities?.length) await syncProfile(data.user, { ...input, passwordHash });
    if (data.user && data.session) {
      setSessionCookies(res, data.session);
      clearPasswordRecoveryCookie(res);
      return res.status(201).json({ user: userPayload(data.user), requiresEmailVerification: false });
    }
    return res.status(201).json({ user: data.user ? userPayload(data.user) : null, requiresEmailVerification: true });
  } catch (error) { next(error); }
});

app.post("/api/auth/password/reset", passwordResetRequestLimiter, async (req, res, next) => {
  try {
    const input = passwordResetRequestSchema.parse(req.body);
    const { error } = await supabaseAuth.auth.resetPasswordForEmail(input.email);
    if (error) {
      console.error("Supabase password reset request failed", { status: error.status, code: error.code });
      return res.status(503).json({ error: "We couldn't send the reset email right now. Please try again later." });
    }
    // Keep the response identical whether or not the email belongs to an account.
    return res.json({ ok: true });
  } catch (error) { next(error); }
});

app.post("/api/auth/password/recovery/verify", passwordRecoveryLimiter, async (req, res, next) => {
  try {
    const input = passwordRecoveryVerifySchema.parse(req.body);
    const { data, error } = await supabaseAuth.auth.verifyOtp({ token_hash: input.tokenHash, type: "recovery" });
    if (error || !data.user || !data.session) {
      return res.status(400).json({ error: "This reset link is invalid or has expired. Request a new one." });
    }
    setSessionCookies(res, data.session);
    setPasswordRecoveryCookie(res, data.user.id);
    res.set("Cache-Control", "no-store");
    return res.json({ ok: true });
  } catch (error) { next(error); }
});

app.post("/api/auth/password/recovery", passwordRecoveryLimiter, async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Your reset link has expired. Request a new one." });
    if (!hasPasswordRecoveryCookie(req, current.user.id)) {
      return res.status(403).json({ error: "Verify a password reset link before changing your password." });
    }
    const input = passwordRecoverySchema.parse(req.body);
    const { error } = await supabaseAdmin.auth.admin.updateUserById(current.user.id, {
      password: input.newPassword,
      app_metadata: { ...current.user.app_metadata, has_password: true },
    });
    if (error) return res.status(400).json({ error: "Unable to update your password. Please try again." });

    const passwordHash = await hashPassword(input.newPassword);
    const { error: profileError } = await supabaseAdmin.from("users")
      .update({ password_hash: passwordHash }).eq("user_id", current.user.id);
    if (profileError) throw new Error(`Password updated but profile hash sync failed: ${profileError.message}`);

    clearSessionCookies(res);
    clearPasswordRecoveryCookie(res);
    res.set("Cache-Control", "no-store");
    return res.json({ ok: true });
  } catch (error) { next(error); }
});

app.post("/api/auth/login", authLimiter, async (req, res, next) => {
  try {
    const input = loginSchema.parse(req.body);
    const { data, error } = await supabaseAuth.auth.signInWithPassword(input);
    if (error || !data.user || !data.session) return res.status(401).json({ error: "Invalid email or password." });
    await syncProfile(data.user);
    setSessionCookies(res, data.session);
    clearPasswordRecoveryCookie(res);
    const role = await accountRoleFor(data.user.id);
    const subscription = role === "admin" ? null : await activeSubscriptionFor(data.user.id);
    return res.json({ user: userPayload(data.user, role), access: { active: role === "admin" || Boolean(subscription), subscription } });
  } catch (error) { next(error); }
});

app.get("/api/auth/google", authLimiter, async (req, res, next) => {
  try {
    const redirect = typeof req.query.redirect === "string" ? req.query.redirect : "/labs";
    const { data, error } = await oauthClient(req, res).auth.signInWithOAuth({ provider: "google", options: { redirectTo: `${authPublicOrigin}/api/auth/callback?next=${encodeURIComponent(redirect)}` } });
    if (error || !data.url) return res.status(502).json({ error: "Google sign-in is currently unavailable." });
    return res.redirect(data.url);
  } catch (error) { next(error); }
});

app.get("/api/auth/callback", async (req, res, next) => {
  try {
    const code = typeof req.query.code === "string" ? req.query.code : null;
    // Explicitly terminate any fragment from an unexpected implicit-flow
    // response so access tokens cannot be inherited by this redirect URL.
    if (!code) return res.redirect(`${env.FRONTEND_URL}/login?error=oauth_callback_failed#`);
    const { data, error } = await oauthClient(req, res).auth.exchangeCodeForSession(code);
    if (error || !data.user || !data.session) return res.redirect(`${env.FRONTEND_URL}/login?error=oauth_callback_failed`);
    await syncProfile(data.user);
    setSessionCookies(res, data.session);
    clearPasswordRecoveryCookie(res);
    return res.redirect(safeRedirect(req.query.next));
  } catch (error) { next(error); }
});

// Supabase confirmation emails can use {{ .TokenHash }} to deliver the one-time
// token directly to this server. Verify the email, then ask the user to sign in.
app.get("/api/auth/confirm", async (req, res, next) => {
  try {
    const tokenHash = typeof req.query.token_hash === "string" ? req.query.token_hash : "";
    if (!tokenHash) return res.redirect(`${env.FRONTEND_URL}/login?error=email_confirmation_failed`);

    const { data, error } = await supabaseAuth.auth.verifyOtp({ token_hash: tokenHash, type: "email" });
    if (error || !data.user || !data.session) return res.redirect(`${env.FRONTEND_URL}/login?error=email_confirmation_failed`);

    await syncProfile(data.user);
    return res.redirect(`${env.FRONTEND_URL}/login?verified=1`);
  } catch (error) { next(error); }
});

app.post("/api/auth/logout", async (req, res, next) => {
  try {
    clearSessionCookies(res);
    clearPasswordRecoveryCookie(res);
    return res.status(204).send();
  } catch (error) { next(error); }
});

app.get("/api/auth/me", async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Authentication required." });
    const role = await accountRoleFor(current.user.id);
    const subscription = role === "admin" ? null : await activeSubscriptionFor(current.user.id);
    res.set("Cache-Control", "no-store");
    return res.json({ user: userPayload(current.user, role), access: { active: role === "admin" || Boolean(subscription), subscription } });
  } catch (error) { next(error); }
});

app.get("/api/auth/profile", async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Authentication required." });
    const readProfile = () => supabaseAdmin.from("users")
      .select("fname,lname,email,academy,role,image_path")
      .eq("user_id", current.user.id)
      .maybeSingle();
    let { data, error } = await readProfile();
    // Repair an absent profile only; normal reads must not write to the DB.
    if (!error && !data) {
      await syncProfile(current.user);
      ({ data, error } = await readProfile());
    }
    if (error || !data) throw new Error(`Unable to load user profile: ${error?.message ?? "Profile not found"}`);
    const hasPassword = userHasPassword(current.user);
    return res.json({ profile: { ...data, imagePath: data.image_path, emailVerified: Boolean(current.user.email_confirmed_at), hasPassword } });
  } catch (error) { next(error); }
});

app.patch("/api/auth/profile", async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Authentication required." });
    const input = profileSchema.parse(req.body);
    const profile = { ...input, academy: input.academy?.trim() || null };
    const { data, error } = await supabaseAdmin.from("users")
      .update(profile)
      .eq("user_id", current.user.id)
      .select("fname,lname,email,academy,role,image_path")
      .single();
    if (error || !data) throw new Error(`Unable to save user profile: ${error?.message ?? "Profile not found"}`);

    // Keep auth metadata useful for integrations while the users row remains canonical.
    const { error: metadataError } = await supabaseAdmin.auth.admin.updateUserById(current.user.id, {
      user_metadata: { ...current.user.user_metadata, ...profile },
    });
    if (metadataError) console.error("Unable to sync profile metadata", { code: metadataError.code, message: metadataError.message });
    return res.json({ profile: { ...data, imagePath: data.image_path, emailVerified: Boolean(current.user.email_confirmed_at), hasPassword: userHasPassword(current.user) } });
  } catch (error) { next(error); }
});

app.post("/api/auth/password", async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Authentication required." });
    const input = passwordSchema.parse(req.body);
    const hasPassword = userHasPassword(current.user);
    if (hasPassword) {
      if (!input.currentPassword) return res.status(400).json({ error: "Enter your current password." });
      const { error: verifyError } = await supabaseAuth.auth.signInWithPassword({ email: current.user.email ?? "", password: input.currentPassword });
      if (verifyError) return res.status(400).json({ error: "Your current password is incorrect." });
    }

    const { error } = await supabaseAdmin.auth.admin.updateUserById(current.user.id, {
      password: input.newPassword,
      app_metadata: { ...current.user.app_metadata, has_password: true },
    });
    if (error) return res.status(400).json({ error: "Unable to update the password. Please try again." });
    const passwordHash = await hashPassword(input.newPassword);
    const { error: profileError } = await supabaseAdmin.from("users").update({ password_hash: passwordHash }).eq("user_id", current.user.id);
    if (profileError) throw new Error(`Password updated but profile hash sync failed: ${profileError.message}`);
    return res.json({ ok: true });
  } catch (error) { next(error); }
});

app.get("/api/auth/account/impact", async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Authentication required." });
    const [{ count: players, error: playersError }, { count: sessions, error: sessionsError }] = await Promise.all([
      supabaseAdmin.from("players").select("player_id", { count: "exact", head: true }).eq("user_id", current.user.id),
      supabaseAdmin.from("sessions").select("session_id", { count: "exact", head: true }).eq("user_id", current.user.id),
    ]);
    if (playersError || sessionsError) throw new Error(`Unable to load account data counts: ${(playersError ?? sessionsError)?.message}`);
    return res.set("Cache-Control", "private, no-store").json({ players: players ?? 0, sessions: sessions ?? 0 });
  } catch (error) { next(error); }
});

app.delete("/api/auth/account", async (req, res, next) => {
  try {
    const current = await authenticatedUser(req, res);
    if (!current) return res.status(401).json({ error: "Authentication required." });
    if (await userIsAdmin(current.user.id)) return res.status(403).json({ error: "Admin accounts cannot delete themselves." });
    if (req.body?.confirmation !== "DELETE") return res.status(400).json({ error: "Confirm account deletion by typing DELETE." });
    const mode = z.enum(["keep_data", "delete_data"]).safeParse(req.body?.mode);
    if (!mode.success) return res.status(400).json({ error: "Choose whether to keep or delete your players and sessions." });
    const { data: imageRow, error: imageReadError } = await supabaseAdmin.from("users")
      .select("image_path").eq("user_id", current.user.id).maybeSingle();
    if (imageReadError) throw new Error(`Unable to load account image: ${imageReadError.message}`);
    if (mode.data === "delete_data") {
      const { data: players, error: playersError } = await supabaseAdmin.from("players")
        .select("player_id,image_path").eq("user_id", current.user.id);
      if (playersError) throw new Error(`Unable to load account players: ${playersError.message}`);
      const playerIds = (players ?? []).map((player) => player.player_id);
      const direct = await supabaseAdmin.from("sessions").select("session_id").eq("user_id", current.user.id);
      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, false);
      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}`);
      }
      const imagePaths = (players ?? []).map((player) => player.image_path).filter((path): path is string => Boolean(path));
      if (imagePaths.length) {
        const { error: playerImageError } = await supabaseAdmin.storage.from("profile-images").remove(imagePaths);
        if (playerImageError) throw new Error(`Unable to remove player images: ${playerImageError.message}`);
      }
    }
    const { error: subscriptionError } = await supabaseAdmin.from("users_subscriptions").delete().eq("user_id", current.user.id);
    if (subscriptionError) throw new Error(`Unable to remove account subscription: ${subscriptionError.message}`);
    const { error: authError } = await supabaseAdmin.auth.admin.deleteUser(current.user.id);
    if (authError) return res.status(500).json({ error: "Unable to delete your account. Please try again." });
    const { error: profileError } = await supabaseAdmin.from("users").delete().eq("user_id", current.user.id);
    if (profileError) throw new Error(`Authentication account was deleted but profile cleanup failed: ${profileError.message}`);
    clearSessionCookies(res);
    clearPasswordRecoveryCookie(res);
    if (imageRow?.image_path?.startsWith(`users/${current.user.id}/`)) {
      const { error: imageError } = await supabaseAdmin.storage.from("profile-images").remove([imageRow.image_path]);
      if (imageError) console.error("Account deleted but image cleanup failed", imageError);
    }
    return res.status(204).send();
  } catch (error) { next(error); }
});

async function proxyAnalysisRequest(req: Request, res: Response) {
  const current = await authenticatedUser(req, res);
  if (!current) return res.status(401).json({ error: "Authentication required." });
  const resourcePath = req.path;
  const isAdmin = await userIsAdmin(current.user.id);
  const exactSessionDelete = req.method === "DELETE"
    ? resourcePath.match(/^\/sessions\/([0-9a-f-]{36})$/i)
    : null;
  let deletedSessionQuota: { user_id: string | null; by_admin: boolean } | null = null;
  if (isAdmin && exactSessionDelete) {
    const { data, error } = await supabaseAdmin.from("sessions").select("user_id,by_admin")
      .eq("session_id", exactSessionDelete[1]).maybeSingle();
    if (error) throw new Error(`Unable to load session before deletion: ${error.message}`);
    if (!data) return res.sendStatus(404);
    deletedSessionQuota = data;
  }
  const sessionMatch = resourcePath.match(/^\/(?:sessions\/)?([0-9a-f]{8}-[0-9a-f]{4}-[1-8][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12})(?:\/|$)/i);
  if (sessionMatch && !isAdmin) {
    const { data: session, error } = await supabaseAdmin.from("sessions")
      .select("session_id").eq("session_id", sessionMatch[1]).eq("user_id", current.user.id).maybeSingle();
    if (error) throw new Error(`Unable to verify session ownership: ${error.message}`);
    if (!session) return res.status(404).json({ error: "Session not found." });
  }
  // Archived sessions and results remain available after expiry. Only starting
  // a new analysis is gated here; the internal begin-analysis route repeats
  // this check before creating its player/session rows.
  if (req.method === "POST" && req.path === "/analyze") {
    const active = isAdmin || await activeSubscriptionFor(current.user.id, { refresh: true });
    if (!active) return res.status(402).json({ error: "An active subscription is required to create or change Labs data.", code: "subscription_required" });
  }

  const target = new URL(req.originalUrl, env.ANALYSIS_API_URL);
  const headers = new Headers();
  headers.set("x-sporalize-proxy-secret", env.ANALYSIS_PROXY_SECRET);
  // Derived from the verified cookie session; never trust a browser-supplied ID.
  headers.set("x-sporalize-user-id", current.user.id);
  if (isAdmin) headers.set("x-sporalize-admin", "true");
  headers.set("x-forwarded-host", new URL(env.BACKEND_URL).host);
  headers.set("x-forwarded-proto", new URL(env.BACKEND_URL).protocol.replace(":", ""));
  for (const name of ["accept", "content-type", "range", "if-range"]) {
    const value = req.header(name);
    if (value) headers.set(name, value);
  }

  const hasBody = req.method !== "GET" && req.method !== "HEAD";
  const isJson = Boolean(req.is(["application/json", "text/plain"]));
  if (isJson) headers.set("content-type", "application/json");
  const body = !hasBody ? undefined : isJson
    ? JSON.stringify(req.body ?? {})
    : req as unknown as BodyInit;
  const init: RequestInit & { duplex?: "half" } = {
    method: req.method,
    headers,
    body,
    ...(body && typeof body !== "string" ? { duplex: "half" as const } : {}),
  };

  const upstream = await fetch(target, init);
  if (upstream.ok && deletedSessionQuota) {
    await refundAdminUsage(deletedSessionQuota.user_id, 0, deletedSessionQuota.by_admin ? 0 : 1);
  }
  res.status(upstream.status);
  res.set("Cross-Origin-Resource-Policy", "cross-origin");
  for (const name of ["content-type", "content-length", "content-disposition", "cache-control", "accept-ranges", "content-range", "etag", "last-modified"]) {
    const value = upstream.headers.get(name);
    if (value) res.set(name, value);
  }
  if (!upstream.body) return res.end();
  await pipeline(Readable.fromWeb(upstream.body as import("node:stream/web").ReadableStream), res);
}

const analysisProxy = (req: Request, res: Response, next: NextFunction) => {
  void proxyAnalysisRequest(req, res).catch((error: unknown) => {
    if (res.headersSent) return res.destroy(error instanceof Error ? error : undefined);
    console.error("Analysis API proxy failed", error);
    return res.status(502).json({ error: "The analysis service is unavailable." });
  });
};

// All analysis API traffic and stored media pass through this subscription
// gate. Auth and subscription status endpoints remain available without a plan.
app.use("/api", (req, res, next) => {
  if (req.originalUrl.startsWith("/api/auth/") || req.originalUrl === "/api/subscriptions" || req.originalUrl === "/api/subscription/access" || req.originalUrl === "/api/public-config") return next();
  const limiter = req.method === "POST" && req.path === "/analyze" ? analysisUploadLimiter : apiLimiter;
  return limiter(req, res, (error?: unknown) => error ? next(error) : analysisProxy(req, res, next));
});
app.use("/storage", apiLimiter, analysisProxy);

app.use((_req, res) => res.status(404).json({ error: "Route not found." }));
app.use((error: unknown, _req: Request, res: Response, _next: NextFunction) => {
  console.error(error);
  if (error instanceof z.ZodError) return res.status(400).json({ error: "Please check the submitted fields." });
  if (typeof error === "object" && error !== null && "type" in error && error.type === "entity.too.large") {
    return res.status(413).json({ error: "Profile images must be 4 MB or smaller." });
  }
  return res.status(500).json({ error: "Something went wrong. Please try again." });
});

app.listen(env.PORT, () => console.log(`Sporalize API listening on http://localhost:${env.PORT}`));