`apps/web/app/api/episodes/route.ts`

89 lines
import { NextResponse } from "next/server";
import { pool } from "@/lib/db";
import { EpisodeSchema } from "@nineoh/core";

export const dynamic = "force-dynamic";

/**
 * Shared API surface consumed by BOTH the web app and the Expo app.
 * Rows are mapped to camelCase and validated against the shared EpisodeSchema
 * before they leave the server, so a malformed row can never reach a client.
 */
export async function GET() {
  try {
    const { rows } = await pool.query(
      `select id, show_id, season_number, episode_number, title, air_date,
              writer, director, summary_short_original, summary_full_original,
              spoiler_rating, external_ids
         from episodes
        order by show_id, season_number, episode_number
        limit 500`
    );
    // Series regulars, scoped PER SHOW (they're that show's stars). With two
    // shows we must NOT attach one show's regulars to the other's episodes.
    const { rows: regRows } = await pool.query(
      `select cr.show_id, cp.name, ch.name as character
         from credits cr
         join cast_people cp on cp.id = cr.person_id
         left join characters ch on ch.id = cr.character_id
        where cr.credit_type = 'main-cast'
        order by cr.show_id, coalesce(cr.billing_order, 999), cp.name`
    );
    const regularsByShow = new Map<
      string,
      { name: string; character: string | null }[]
    >();
    for (const r of regRows) {
      const arr = regularsByShow.get(r.show_id) ?? [];
      arr.push({
        name: r.name as string,
        character: (r.character ?? null) as string | null,
      });
      regularsByShow.set(r.show_id, arr);
    }

    // This episode's guest stars (factual, per-episode, from TVmaze).
    const { rows: guestRows } = await pool.query(
      `select episode_id, person_name, character_name
         from episode_guest_cast
        order by episode_id, ord`
    );
    const guestsByEp = new Map<string, { name: string; character: string | null }[]>();
    for (const g of guestRows) {
      const arr = guestsByEp.get(g.episode_id) ?? [];
      arr.push({ name: g.person_name, character: g.character_name ?? null });
      guestsByEp.set(g.episode_id, arr);
    }

    const episodes = rows
      .map((r) => {
        const seen = new Set<string>();
        const showRegulars = regularsByShow.get(r.show_id) ?? [];
        const cast = [...showRegulars, ...(guestsByEp.get(r.id) ?? [])].filter((c) =>
          seen.has(c.name) ? false : (seen.add(c.name), true)
        );
        return EpisodeSchema.safeParse({
          id: r.id,
          showId: r.show_id,
          seasonNumber: r.season_number,
          episodeNumber: r.episode_number,
          title: r.title,
          airDate: r.air_date ? new Date(r.air_date).toISOString().slice(0, 10) : null,
          writer: r.writer,
          director: r.director,
          summaryShortOriginal: r.summary_short_original,
          summaryFullOriginal: r.summary_full_original,
          spoilerRating: r.spoiler_rating ?? 0,
          externalIds: r.external_ids ?? {},
          cast,
        });
      })
      .filter((p) => p.success)
      .map((p) => p.data);

    return NextResponse.json({ episodes });
  } catch {
    return NextResponse.json({ episodes: [] });
  }
}