/**
 * Server-side data helpers for the host dashboard (and the admin views that reuse it).
 *
 * Venue-owned rows are always filtered by `venueId` here, exactly like forVenue() in @pintrush/db;
 * these cover the few reads/writes forVenue does not offer (TV link reset, daily join series, final
 * standings). Server-only: imported by server components and server actions.
 */
import { z } from 'zod';
import { getDb, isUuid, NotFoundError } from '@pintrush/db';
import type { Standing } from '@pintrush/engine';

/** Where the web server reaches the game server (an internal URL can differ from the public one). */
export function gameServerBaseUrl(): string {
  return (process.env.GAME_SERVER_INTERNAL_URL || process.env.NEXT_PUBLIC_GAME_SERVER_URL || 'http://localhost:4000').replace(/\/$/, '');
}

const RoomPublicSchema = z.object({
  roomId: z.string(),
  active: z.boolean(),
  activeEventKind: z.enum(['ALWAYS_ON', 'CHAMPIONSHIP']).nullable(),
  activeEventName: z.string().nullable(),
});

export type RoomLiveStatus =
  | { reachable: true; active: boolean; activeEventKind: 'ALWAYS_ON' | 'CHAMPIONSHIP' | null; activeEventName: string | null }
  | { reachable: false };

/** Live status from the game server, with a short timeout so a down game server never hangs the page. */
export async function roomLiveStatus(roomId: string, timeoutMs = 1500): Promise<RoomLiveStatus> {
  if (!isUuid(roomId)) return { reachable: false };
  try {
    const res = await fetch(`${gameServerBaseUrl()}/rooms/${roomId}/public`, { cache: 'no-store', signal: AbortSignal.timeout(timeoutMs) });
    if (!res.ok) return { reachable: false };
    const parsed = RoomPublicSchema.safeParse(await res.json());
    if (!parsed.success) return { reachable: false };
    const { active, activeEventKind, activeEventName } = parsed.data;
    return { reachable: true, active, activeEventKind, activeEventName };
  } catch {
    return { reachable: false };
  }
}

export interface PackOption {
  key: string;
  name: string;
  kind: 'GENERAL' | 'MUSIC';
  questionCount: number;
  ownedByVenue: boolean;
}

/** PUBLISHED packs a venue may use: platform packs (venueId null) plus the venue's own. */
export async function visiblePacks(venueId: string): Promise<PackOption[]> {
  const rows = await getDb().questionPack.findMany({
    where: { status: 'PUBLISHED', OR: [{ venueId: null }, { venueId }] },
    select: { key: true, name: true, kind: true, venueId: true, _count: { select: { questions: { where: { status: 'ACTIVE' } } } } },
    orderBy: [{ kind: 'asc' }, { name: 'asc' }],
  });
  return rows.map((p) => ({ key: p.key, name: p.name, kind: p.kind, questionCount: p._count.questions, ownedByVenue: p.venueId !== null }));
}

/**
 * Revoke every TV link of a room by bumping its token version (venue-scoped). TVs using an old link
 * are refused the next time they connect. Throws NotFoundError for a room of another venue.
 */
export async function resetRoomTvLink(venueId: string, roomId: string): Promise<{ name: string; tvTokenVersion: number }> {
  if (!isUuid(venueId) || !isUuid(roomId)) throw new NotFoundError('Room', roomId);
  const db = getDb();
  return db.$transaction(async (tx) => {
    const { count } = await tx.room.updateMany({
      where: { id: roomId, venueId },
      data: { tvTokenVersion: { increment: 1 }, version: { increment: 1 } },
    });
    if (count === 0) throw new NotFoundError('Room', roomId);
    return tx.room.findFirstOrThrow({ where: { id: roomId, venueId }, select: { name: true, tvTokenVersion: true } });
  });
}

export interface DailyPoint {
  /** Venue business date (YYYY-MM-DD, 5 am cutoff). */
  date: string;
  joins: number;
  guests: number;
}

/** Joins and distinct guests per venue business date in [from, to). */
export async function dailyJoins(venueId: string, timeZone: string, from: Date, to: Date): Promise<DailyPoint[]> {
  if (!isUuid(venueId)) return [];
  // Same UTC → venue-local conversion and 5 am cutoff as forVenue().metrics().
  const rows = await getDb().$queryRaw<{ d: Date; joins: number; guests: number }[]>`
    SELECT ((("joinedAt" AT TIME ZONE 'UTC') AT TIME ZONE ${timeZone}::text) - make_interval(hours => 5))::date AS d,
           COUNT(*)::int AS joins,
           COUNT(DISTINCT "deviceHash")::int AS guests
    FROM "PlayerSession"
    WHERE "venueId" = ${venueId}::uuid
      AND "joinedAt" >= (${from.toISOString()}::timestamptz AT TIME ZONE 'UTC')
      AND "joinedAt" < (${to.toISOString()}::timestamptz AT TIME ZONE 'UTC')
    GROUP BY d
    ORDER BY d`;
  return rows.map((r) => ({ date: r.d.toISOString().slice(0, 10), joins: Number(r.joins), guests: Number(r.guests) }));
}

/** Final standings saved when an event's session completed (null if it never ran). */
export async function finalStandings(venueId: string, eventId: string): Promise<Standing[] | null> {
  if (!isUuid(venueId) || !isUuid(eventId)) return null;
  const db = getDb();
  const session = await db.gameSession.findFirst({ where: { eventId, venueId }, select: { id: true } });
  if (!session) return null;
  const snap = await db.leaderboardSnapshot.findFirst({
    where: { venueId, gameSessionId: session.id, scope: { in: ['EVENT', 'NIGHTLY'] } },
    orderBy: { createdAt: 'desc' },
    select: { entries: true },
  });
  return Array.isArray(snap?.entries) ? (snap.entries as unknown as Standing[]) : null;
}

/** Player count of an event's session (for listings), venue-scoped. */
export async function eventPlayerCounts(venueId: string, eventIds: string[]): Promise<Map<string, number>> {
  const ids = eventIds.filter(isUuid);
  if (!isUuid(venueId) || ids.length === 0) return new Map();
  const rows = await getDb().playerSession.groupBy({ by: ['eventId'], where: { venueId, eventId: { in: ids } }, _count: { _all: true } });
  return new Map(rows.map((r) => [r.eventId, r._count._all]));
}

/** Spec §11 pilot metrics for one venue over [from, to). Every query is scoped by venueId. */
export interface PilotMetrics {
  /** Visits = one device on one business night with at least one answered round. */
  visits: { played: number; multiRound: number };
  /** Server-measured time from a question opening to the answer arriving. */
  latency: { answers: number; medianMs: number | null; p90Ms: number | null };
  /** Reconnects after the guest first reached the game screen (see the query for why). */
  reconnects: { players: number; total: number; reconnected: number };
  /** Host commands (pause, skip, kick, rename, …) per event started in the range. */
  hostEffort: Record<'ALWAYS_ON' | 'CHAMPIONSHIP', { events: number; actions: number }>;
  /** One-tap "Was that round fair?" votes from phones. */
  fairness: { votes: number; yes: number };
}

export async function pilotMetrics(venueId: string, timeZone: string, from: Date, to: Date): Promise<PilotMetrics> {
  const empty: PilotMetrics = {
    visits: { played: 0, multiRound: 0 },
    latency: { answers: 0, medianMs: null, p90Ms: null },
    reconnects: { players: 0, total: 0, reconnected: 0 },
    hostEffort: { ALWAYS_ON: { events: 0, actions: 0 }, CHAMPIONSHIP: { events: 0, actions: 0 } },
    fairness: { votes: 0, yes: 0 },
  };
  if (!isUuid(venueId)) return empty;
  const db = getDb();
  const fromIso = from.toISOString();
  const toIso = to.toISOString();
  // Timestamps are UTC in `timestamp` columns; convert explicitly, exactly like dailyJoins().
  const [visits, latency, reconnects, effort, fairness] = await Promise.all([
    // A round counts as played when the guest answered at least one of its questions (a board dealt
    // to an idle phone doesn't). A device's always-on and championship sessions on the same night
    // are one visit.
    db.$queryRaw<{ played: number; multi: number }[]>`
      WITH ps AS (
        SELECT id, "deviceHash" AS device,
               ((("joinedAt" AT TIME ZONE 'UTC') AT TIME ZONE ${timeZone}::text) - make_interval(hours => 5))::date AS night
        FROM "PlayerSession"
        WHERE "venueId" = ${venueId}::uuid
          AND "deviceHash" IS NOT NULL
          AND "joinedAt" >= (${fromIso}::timestamptz AT TIME ZONE 'UTC')
          AND "joinedAt" < (${toIso}::timestamptz AT TIME ZONE 'UTC')
      ), visits AS (
        SELECT ps.device, ps.night, COUNT(DISTINCT a."roundId") AS rounds
        FROM ps JOIN "AnswerSubmission" a ON a."playerSessionId" = ps.id AND a."venueId" = ${venueId}::uuid
        GROUP BY ps.device, ps.night
      )
      SELECT COUNT(*)::int AS played, (COUNT(*) FILTER (WHERE rounds >= 2))::int AS multi FROM visits`,
    db.$queryRaw<{ answers: number; median: number | null; p90: number | null }[]>`
      SELECT COUNT(*)::int AS answers,
             percentile_cont(0.5) WITHIN GROUP (ORDER BY "elapsedMs") AS median,
             percentile_cont(0.9) WITHIN GROUP (ORDER BY "elapsedMs") AS p90
      FROM "AnswerSubmission"
      WHERE "venueId" = ${venueId}::uuid
        AND "receivedAt" >= (${fromIso}::timestamptz AT TIME ZONE 'UTC')
        AND "receivedAt" < (${toIso}::timestamptz AT TIME ZONE 'UTC')`,
    // tokenVersion starts at 1 and is bumped on every reconnect. The normal path (join page → game
    // screen) is itself one new connection, so versions above 2 are the real drops. Kicked players
    // are excluded (kicking jumps the version by 1000 to revoke their token).
    db.$queryRaw<{ players: number; total: number; reconnected: number }[]>`
      SELECT COUNT(*)::int AS players,
             COALESCE(SUM(GREATEST("tokenVersion" - 2, 0)), 0)::int AS total,
             (COUNT(*) FILTER (WHERE "tokenVersion" > 2))::int AS reconnected
      FROM "PlayerSession"
      WHERE "venueId" = ${venueId}::uuid
        AND "status" <> 'KICKED'
        AND "joinedAt" >= (${fromIso}::timestamptz AT TIME ZONE 'UTC')
        AND "joinedAt" < (${toIso}::timestamptz AT TIME ZONE 'UTC')`,
    db.$queryRaw<{ kind: 'ALWAYS_ON' | 'CHAMPIONSHIP'; events: number; actions: number }[]>`
      SELECT e."kind"::text AS kind, COUNT(DISTINCT e.id)::int AS events, COUNT(a.id)::int AS actions
      FROM "Event" e
      JOIN "GameSession" g ON g."eventId" = e.id AND g."venueId" = ${venueId}::uuid
      LEFT JOIN "AuditEvent" a ON a."gameSessionId" = g.id AND a."venueId" = ${venueId}::uuid AND a."actorType" = 'HOST'
      WHERE e."venueId" = ${venueId}::uuid
        AND e."startedAt" >= (${fromIso}::timestamptz AT TIME ZONE 'UTC')
        AND e."startedAt" < (${toIso}::timestamptz AT TIME ZONE 'UTC')
      GROUP BY e."kind"`,
    db.$queryRaw<{ votes: number; yes: number }[]>`
      SELECT COUNT(*)::int AS votes, (COUNT(*) FILTER (WHERE "props"->>'fair' = 'true'))::int AS yes
      FROM "AnalyticsEvent"
      WHERE "venueId" = ${venueId}::uuid
        AND "kind" = 'ROUND_FAIRNESS'
        AND "props"->>'fair' IN ('true', 'false')
        AND "createdAt" >= (${fromIso}::timestamptz AT TIME ZONE 'UTC')
        AND "createdAt" < (${toIso}::timestamptz AT TIME ZONE 'UTC')`,
  ]);
  const hostEffort = { ...empty.hostEffort };
  for (const row of effort) hostEffort[row.kind] = { events: Number(row.events), actions: Number(row.actions) };
  const v = visits[0];
  const l = latency[0];
  const r = reconnects[0];
  const f = fairness[0];
  return {
    visits: { played: Number(v?.played ?? 0), multiRound: Number(v?.multi ?? 0) },
    latency: { answers: Number(l?.answers ?? 0), medianMs: l?.median == null ? null : Number(l.median), p90Ms: l?.p90 == null ? null : Number(l.p90) },
    reconnects: { players: Number(r?.players ?? 0), total: Number(r?.total ?? 0), reconnected: Number(r?.reconnected ?? 0) },
    hostEffort,
    fairness: { votes: Number(f?.votes ?? 0), yes: Number(f?.yes ?? 0) },
  };
}