import { Router } from 'express';
import { and, asc, desc, eq, getTableColumns, inArray, sql } from 'drizzle-orm';
import { db } from '../db/client';
import {
  showEvents,
  showEventShows,
  showEventEntries,
  showEventJudges,
  showEventPlacements,
  rabbits,
  settings,
} from '../db/schema';
import { toCamelCase, toSnakeCase, pick } from '../utils/case';

const router = Router();

// Mass-assignment guard (see routes/events.ts): only `name`/`notes` are
// client-writable on an event; id/timestamps are server-managed.
const EVENT_SYSTEM_FIELDS = new Set(['id', 'createdAt', 'updatedAt']);
const EVENT_WRITABLE = Object.keys(getTableColumns(showEvents)).filter(
  (key) => !EVENT_SYSTEM_FIELDS.has(key)
);

function nowIso(): string {
  return new Date().toISOString();
}

// The show within an event identified by both ids, or undefined — used to verify
// a /:showId path actually belongs to the /:id event before mutating its rows.
function getEventShow(eventId: number, showId: number) {
  return db
    .select()
    .from(showEventShows)
    .where(and(eq(showEventShows.id, showId), eq(showEventShows.showEventId, eventId)))
    .get();
}

// Entries for a set of shows, each joined to its rabbit's display fields, keyed
// by `showEventShowId`. Rows are camelCase here; callers snake-case at the
// response boundary (the report builder uses them camel). leftJoin so an entry
// whose rabbit was later deleted still returns (with null rabbit fields) rather
// than vanishing.
function entriesByShow(showIds: number[]) {
  const map = new Map<number, Record<string, unknown>[]>();
  if (showIds.length === 0) return map;

  const rows = db
    .select({
      id: showEventEntries.id,
      showEventShowId: showEventEntries.showEventShowId,
      rabbitId: showEventEntries.rabbitId,
      entryClass: showEventEntries.entryClass,
      entryNumber: showEventEntries.entryNumber,
      notes: showEventEntries.notes,
      createdAt: showEventEntries.createdAt,
      rabbitName: rabbits.name,
      rabbitFarm: rabbits.farm,
      rabbitEarNumber: rabbits.earNumber,
      rabbitBreed: rabbits.breed,
      rabbitColorName: rabbits.colorName,
      rabbitSex: rabbits.sex,
    })
    .from(showEventEntries)
    .leftJoin(rabbits, eq(showEventEntries.rabbitId, rabbits.id))
    .where(inArray(showEventEntries.showEventShowId, showIds))
    .orderBy(asc(showEventEntries.id))
    .all();

  for (const row of rows) {
    const list = map.get(row.showEventShowId) ?? [];
    list.push(row);
    map.set(row.showEventShowId, list);
  }
  return map;
}

// Judges for a set of shows, keyed by `showEventShowId`.
function judgesByShow(showIds: number[]) {
  const map = new Map<number, Record<string, unknown>[]>();
  if (showIds.length === 0) return map;
  const rows = db
    .select()
    .from(showEventJudges)
    .where(inArray(showEventJudges.showEventShowId, showIds))
    .orderBy(asc(showEventJudges.id))
    .all();
  for (const row of rows) {
    const list = map.get(row.showEventShowId) ?? [];
    list.push(row);
    map.set(row.showEventShowId, list);
  }
  return map;
}

// Placements keyed by `entryId` (at most one per entry — see the UNIQUE column).
function placementsByEntry(entryIds: number[]) {
  const map = new Map<number, Record<string, unknown>>();
  if (entryIds.length === 0) return map;
  const rows = db
    .select()
    .from(showEventPlacements)
    .where(inArray(showEventPlacements.entryId, entryIds))
    .all();
  for (const row of rows) map.set(row.entryId, row);
  return map;
}

// List events with derived counts (number of shows) and the date span of their
// shows, computed in one grouped query and merged in JS — no cached counters,
// matching the dashboard-stats approach. Newest-updated first.
router.get('/', (req, res) => {
  const events = db.select().from(showEvents).orderBy(desc(showEvents.updatedAt)).all();

  const agg = db
    .select({
      showEventId: showEventShows.showEventId,
      showCount: sql<number>`count(*)`,
      firstDate: sql<string | null>`min(${showEventShows.showDate})`,
      lastDate: sql<string | null>`max(${showEventShows.showDate})`,
    })
    .from(showEventShows)
    .groupBy(showEventShows.showEventId)
    .all();

  const byEvent = new Map(agg.map((row) => [row.showEventId, row]));

  const result = events.map((event) => {
    const stats = byEvent.get(event.id);
    return {
      ...toSnakeCase(event),
      show_count: stats?.showCount ?? 0,
      first_show_date: stats?.firstDate ?? null,
      last_show_date: stats?.lastDate ?? null,
    };
  });

  res.json(result);
});

// One rabbit's show history across every event (`?rabbit_id=X`, required), so
// its timeline can list the shows it was entered in with their results — the
// entries/placements live here, not in `rabbit_events`. Registered before
// `GET /:id` so "entries" isn't parsed as an event id.
router.get('/entries', (req, res) => {
  const rabbitId = Number(req.query.rabbit_id);
  if (!Number.isInteger(rabbitId)) {
    res.status(400).json({ error: 'rabbit_id is required' });
    return;
  }

  const rows = db
    .select({
      entryId: showEventEntries.id,
      entryClass: showEventEntries.entryClass,
      eventId: showEvents.id,
      eventName: showEvents.name,
      clubName: showEventShows.clubName,
      showDate: showEventShows.showDate,
      showType: showEventShows.showType,
    })
    .from(showEventEntries)
    .innerJoin(showEventShows, eq(showEventEntries.showEventShowId, showEventShows.id))
    .innerJoin(showEvents, eq(showEventShows.showEventId, showEvents.id))
    .where(eq(showEventEntries.rabbitId, rabbitId))
    .orderBy(asc(showEventShows.showDate))
    .all();

  const placements = placementsByEntry(rows.map((r) => r.entryId));
  res.json(
    rows.map((row) => {
      const placement = placements.get(row.entryId);
      return { ...toSnakeCase(row), placement: placement ? toSnakeCase(placement) : null };
    })
  );
});

// Event detail: the event plus its snapshotted shows (date order). Judges and
// entries (Phases 3–4) will be embedded here later.
router.get('/:id', (req, res) => {
  const id = Number(req.params.id);
  const event = db.select().from(showEvents).where(eq(showEvents.id, id)).get();
  if (!event) {
    res.status(404).json({ error: 'Event not found' });
    return;
  }

  const shows = db
    .select()
    .from(showEventShows)
    .where(eq(showEventShows.showEventId, id))
    .orderBy(asc(showEventShows.showDate), asc(showEventShows.id))
    .all();

  const entriesMap = entriesByShow(shows.map((s) => s.id));
  const judgesMap = judgesByShow(shows.map((s) => s.id));
  const allEntryIds = [...entriesMap.values()].flat().map((e) => Number(e.id));
  const placementsMap = placementsByEntry(allEntryIds);

  const showsOut = shows.map((s) => ({
    ...toSnakeCase(s),
    judges: (judgesMap.get(s.id) ?? []).map(toSnakeCase),
    entries: (entriesMap.get(s.id) ?? []).map((e) => {
      const placement = placementsMap.get(Number(e.id));
      return {
        ...toSnakeCase(e),
        placement: placement ? toSnakeCase(placement) : null,
      };
    }),
  }));

  res.json({ ...toSnakeCase(event), shows: showsOut });
});

router.post('/', (req, res) => {
  const { name } = req.body;
  if (typeof name !== 'string' || name.trim() === '') {
    res.status(400).json({ error: 'A name is required' });
    return;
  }

  const values = pick(toCamelCase(req.body), EVENT_WRITABLE) as typeof showEvents.$inferInsert;
  const created = db.insert(showEvents).values(values).returning().get();
  res.status(201).json(toSnakeCase(created));
});

router.patch('/:id', (req, res) => {
  const id = Number(req.params.id);
  const values = pick(toCamelCase(req.body), EVENT_WRITABLE) as Partial<
    typeof showEvents.$inferInsert
  >;
  values.updatedAt = nowIso();

  const updated = db
    .update(showEvents)
    .set(values)
    .where(eq(showEvents.id, id))
    .returning()
    .get();

  if (!updated) {
    res.status(404).json({ error: 'Event not found' });
    return;
  }

  res.json(toSnakeCase(updated));
});

router.delete('/:id', (req, res) => {
  const id = Number(req.params.id);
  const existing = db.select().from(showEvents).where(eq(showEvents.id, id)).get();
  if (!existing) {
    res.status(404).json({ error: 'Event not found' });
    return;
  }
  // show_event_shows cascades via the FK.
  db.delete(showEvents).where(eq(showEvents.id, id)).run();
  res.status(204).end();
});

// Add an ARBA show to an event. The request body is an ARBA show object (the
// snake_case shape GET /api/shows/arba returns); we snapshot the relevant
// fields rather than storing a reference, since the ARBA listing is transient.
// Idempotent per (event, sanction_number): re-adding returns the existing row.
router.post('/:id/shows', (req, res) => {
  const eventId = Number(req.params.id);
  const event = db.select().from(showEvents).where(eq(showEvents.id, eventId)).get();
  if (!event) {
    res.status(404).json({ error: 'Event not found' });
    return;
  }

  const body = req.body ?? {};
  const sanctionNumber = body.sanction_number;
  const clubName = body.club_name;
  if (typeof sanctionNumber !== 'string' || sanctionNumber.trim() === '') {
    res.status(400).json({ error: 'sanction_number is required' });
    return;
  }
  if (typeof clubName !== 'string' || clubName.trim() === '') {
    res.status(400).json({ error: 'club_name is required' });
    return;
  }

  const existing = db
    .select()
    .from(showEventShows)
    .where(
      and(
        eq(showEventShows.showEventId, eventId),
        eq(showEventShows.sanctionNumber, sanctionNumber)
      )
    )
    .get();
  if (existing) {
    res.status(200).json(toSnakeCase(existing));
    return;
  }

  const snapshot: typeof showEventShows.$inferInsert = {
    showEventId: eventId,
    sanctionNumber,
    showDate: body.date || body.raw_date || null,
    clubName,
    showType: body.show_type_full || body.show_type || null,
    specialtyBreed: body.special_breed || null,
    city: body.city || null,
    state: body.state || null,
    address: body.address || null,
    secretary: body.secretary || null,
    secretaryEmail: body.email || null,
    catalogUrl: body.catalog_url || null,
    mapsUrl: body.maps_url || null,
  };

  const created = db.transaction((tx) => {
    const row = tx.insert(showEventShows).values(snapshot).returning().get();
    tx.update(showEvents).set({ updatedAt: nowIso() }).where(eq(showEvents.id, eventId)).run();
    return row;
  });

  res.status(201).json(toSnakeCase(created));
});

router.delete('/:id/shows/:showId', (req, res) => {
  const eventId = Number(req.params.id);
  const showId = Number(req.params.showId);

  const existing = db
    .select()
    .from(showEventShows)
    .where(and(eq(showEventShows.id, showId), eq(showEventShows.showEventId, eventId)))
    .get();
  if (!existing) {
    res.status(404).json({ error: 'Show not found in this event' });
    return;
  }

  db.transaction((tx) => {
    tx.delete(showEventShows).where(eq(showEventShows.id, showId)).run();
    tx.update(showEvents).set({ updatedAt: nowIso() }).where(eq(showEvents.id, eventId)).run();
  });

  res.status(204).end();
});

// ---- Entries (a rabbit entered into a specific show within the event) ----

// Returns the entry joined to its rabbit's display fields (the same shape the
// detail endpoint embeds), so the client can render it without a refetch.
function entryWithRabbit(entryId: number) {
  const [row] = entriesByShowEntryIds([entryId]);
  return row;
}
function entriesByShowEntryIds(ids: number[]): Record<string, unknown>[] {
  if (ids.length === 0) return [];
  return db
    .select({
      id: showEventEntries.id,
      showEventShowId: showEventEntries.showEventShowId,
      rabbitId: showEventEntries.rabbitId,
      entryClass: showEventEntries.entryClass,
      entryNumber: showEventEntries.entryNumber,
      notes: showEventEntries.notes,
      createdAt: showEventEntries.createdAt,
      rabbitName: rabbits.name,
      rabbitFarm: rabbits.farm,
      rabbitEarNumber: rabbits.earNumber,
      rabbitBreed: rabbits.breed,
      rabbitColorName: rabbits.colorName,
      rabbitSex: rabbits.sex,
    })
    .from(showEventEntries)
    .leftJoin(rabbits, eq(showEventEntries.rabbitId, rabbits.id))
    .where(inArray(showEventEntries.id, ids))
    .all();
}

router.post('/:id/shows/:showId/entries', (req, res) => {
  const eventId = Number(req.params.id);
  const showId = Number(req.params.showId);
  if (!getEventShow(eventId, showId)) {
    res.status(404).json({ error: 'Show not found in this event' });
    return;
  }

  const rabbitId = req.body?.rabbit_id;
  if (typeof rabbitId !== 'number') {
    res.status(400).json({ error: 'rabbit_id is required' });
    return;
  }
  const rabbit = db.select().from(rabbits).where(eq(rabbits.id, rabbitId)).get();
  if (!rabbit) {
    res.status(400).json({ error: 'Rabbit not found' });
    return;
  }

  const values: typeof showEventEntries.$inferInsert = {
    showEventShowId: showId,
    rabbitId,
    entryClass: req.body.entry_class ?? null,
    entryNumber: req.body.entry_number ?? null,
    notes: req.body.notes ?? null,
  };

  const created = db.transaction((tx) => {
    const row = tx.insert(showEventEntries).values(values).returning().get();
    tx.update(showEvents).set({ updatedAt: nowIso() }).where(eq(showEvents.id, eventId)).run();
    return row;
  });

  res.status(201).json(toSnakeCase(entryWithRabbit(created.id)));
});

const ENTRY_WRITABLE = ['entryClass', 'entryNumber', 'notes'];

router.patch('/:id/shows/:showId/entries/:entryId', (req, res) => {
  const eventId = Number(req.params.id);
  const showId = Number(req.params.showId);
  const entryId = Number(req.params.entryId);
  if (!getEventShow(eventId, showId)) {
    res.status(404).json({ error: 'Show not found in this event' });
    return;
  }

  const entry = db
    .select()
    .from(showEventEntries)
    .where(and(eq(showEventEntries.id, entryId), eq(showEventEntries.showEventShowId, showId)))
    .get();
  if (!entry) {
    res.status(404).json({ error: 'Entry not found' });
    return;
  }

  // Only the editable fields — never the rabbit or which show it's in.
  const values = pick(toCamelCase(req.body), ENTRY_WRITABLE);
  db.update(showEventEntries).set(values).where(eq(showEventEntries.id, entryId)).run();

  res.json(toSnakeCase(entryWithRabbit(entryId)));
});

router.delete('/:id/shows/:showId/entries/:entryId', (req, res) => {
  const eventId = Number(req.params.id);
  const showId = Number(req.params.showId);
  const entryId = Number(req.params.entryId);
  if (!getEventShow(eventId, showId)) {
    res.status(404).json({ error: 'Show not found in this event' });
    return;
  }

  const entry = db
    .select()
    .from(showEventEntries)
    .where(and(eq(showEventEntries.id, entryId), eq(showEventEntries.showEventShowId, showId)))
    .get();
  if (!entry) {
    res.status(404).json({ error: 'Entry not found' });
    return;
  }

  db.transaction((tx) => {
    tx.delete(showEventEntries).where(eq(showEventEntries.id, entryId)).run();
    tx.update(showEvents).set({ updatedAt: nowIso() }).where(eq(showEvents.id, eventId)).run();
  });

  res.status(204).end();
});

// ---- Entries report (plain text, for emailing a show secretary) ----

// ISO YYYY-MM-DD → MM/DD/YYYY (the format show secretaries expect); pass through
// anything that isn't a clean ISO date.
function reportDate(value: string | null): string {
  const m = (value ?? '').match(/^(\d{4})-(\d{2})-(\d{2})$/);
  return m ? `${m[2]}/${m[3]}/${m[1]}` : (value ?? '');
}

router.get('/:id/report', (req, res) => {
  const id = Number(req.params.id);
  const event = db.select().from(showEvents).where(eq(showEvents.id, id)).get();
  if (!event) {
    res.status(404).json({ error: 'Event not found' });
    return;
  }

  const shows = db
    .select()
    .from(showEventShows)
    .where(eq(showEventShows.showEventId, id))
    .orderBy(asc(showEventShows.showDate), asc(showEventShows.id))
    .all();
  const entriesMap = entriesByShow(shows.map((s) => s.id));

  // Owner line comes from the rabbitry name in settings (per the resolved
  // decision: no ARBA membership number is collected, so it's omitted).
  const rabbitryRow = db.select().from(settings).where(eq(settings.key, 'rabbitry_name')).get();
  const rabbitryName = rabbitryRow?.value ?? '';

  const lines: string[] = [];
  lines.push(`SHOW ENTRIES — ${event.name}`);
  lines.push(`Generated: ${reportDate(new Date().toISOString().slice(0, 10))}`);
  lines.push('');

  let anyEntries = false;
  for (const show of shows) {
    const entries = entriesMap.get(show.id) ?? [];
    if (entries.length === 0) continue;
    anyEntries = true;

    const sep = '='.repeat(46);
    lines.push(sep);
    lines.push(`${show.clubName}${show.showType ? ` — ${show.showType}` : ''}`);
    lines.push(`Sanction #: ${show.sanctionNumber}`);
    lines.push(`Date: ${reportDate(show.showDate)}`);
    if (show.secretary || show.secretaryEmail) {
      const email = show.secretaryEmail ? ` <${show.secretaryEmail}>` : '';
      lines.push(`Secretary: ${show.secretary ?? ''}${email}`.trim());
    }
    lines.push(sep);
    lines.push('');
    lines.push('RABBITS ENTERED:');
    lines.push('');

    entries.forEach((e, idx) => {
      const tattoo = e.rabbitEarNumber ? ` (${e.rabbitEarNumber})` : '';
      lines.push(`${idx + 1}. ${e.rabbitName ?? 'Unknown'}${tattoo}`);
      const breedVariety = [e.rabbitBreed, e.rabbitColorName].filter(Boolean).join(' / ');
      if (breedVariety) lines.push(`   Breed: ${breedVariety}`);
      if (e.entryClass) lines.push(`   Class: ${e.entryClass}`);
      if (rabbitryName) lines.push(`   Owner: ${rabbitryName}`);
      if (e.notes) lines.push(`   Notes: ${e.notes}`);
      lines.push('');
    });
  }

  if (!anyEntries) {
    lines.push('No rabbits have been entered yet.');
    lines.push('');
  }

  res.json({ report: lines.join('\n') });
});

// ---- Judges (assigned per show within an event) ----

const JUDGE_WRITABLE = ['judgeName', 'judgeArbaNumber', 'notes'];

router.post('/:id/shows/:showId/judges', (req, res) => {
  const eventId = Number(req.params.id);
  const showId = Number(req.params.showId);
  if (!getEventShow(eventId, showId)) {
    res.status(404).json({ error: 'Show not found in this event' });
    return;
  }
  const judgeName = req.body?.judge_name;
  if (typeof judgeName !== 'string' || judgeName.trim() === '') {
    res.status(400).json({ error: 'judge_name is required' });
    return;
  }

  const created = db.transaction((tx) => {
    const row = tx
      .insert(showEventJudges)
      .values({
        showEventShowId: showId,
        judgeName: judgeName.trim(),
        judgeArbaNumber: req.body.judge_arba_number ?? null,
        notes: req.body.notes ?? null,
      })
      .returning()
      .get();
    tx.update(showEvents).set({ updatedAt: nowIso() }).where(eq(showEvents.id, eventId)).run();
    return row;
  });

  res.status(201).json(toSnakeCase(created));
});

router.patch('/:id/shows/:showId/judges/:judgeId', (req, res) => {
  const eventId = Number(req.params.id);
  const showId = Number(req.params.showId);
  const judgeId = Number(req.params.judgeId);
  if (!getEventShow(eventId, showId)) {
    res.status(404).json({ error: 'Show not found in this event' });
    return;
  }
  const judge = db
    .select()
    .from(showEventJudges)
    .where(and(eq(showEventJudges.id, judgeId), eq(showEventJudges.showEventShowId, showId)))
    .get();
  if (!judge) {
    res.status(404).json({ error: 'Judge not found' });
    return;
  }

  const values = pick(toCamelCase(req.body), JUDGE_WRITABLE);
  const updated = db
    .update(showEventJudges)
    .set(values)
    .where(eq(showEventJudges.id, judgeId))
    .returning()
    .get();
  res.json(toSnakeCase(updated));
});

router.delete('/:id/shows/:showId/judges/:judgeId', (req, res) => {
  const eventId = Number(req.params.id);
  const showId = Number(req.params.showId);
  const judgeId = Number(req.params.judgeId);
  if (!getEventShow(eventId, showId)) {
    res.status(404).json({ error: 'Show not found in this event' });
    return;
  }
  const judge = db
    .select()
    .from(showEventJudges)
    .where(and(eq(showEventJudges.id, judgeId), eq(showEventJudges.showEventShowId, showId)))
    .get();
  if (!judge) {
    res.status(404).json({ error: 'Judge not found' });
    return;
  }
  db.delete(showEventJudges).where(eq(showEventJudges.id, judgeId)).run();
  res.status(204).end();
});

// ---- Placement (one per entry; PUT is a full-object upsert) ----

function clampLegs(value: unknown): number {
  const n = Math.trunc(Number(value));
  if (Number.isNaN(n)) return 0;
  return Math.max(0, Math.min(3, n));
}

router.put('/:id/shows/:showId/entries/:entryId/placement', (req, res) => {
  const eventId = Number(req.params.id);
  const showId = Number(req.params.showId);
  const entryId = Number(req.params.entryId);
  if (!getEventShow(eventId, showId)) {
    res.status(404).json({ error: 'Show not found in this event' });
    return;
  }
  const entry = db
    .select()
    .from(showEventEntries)
    .where(and(eq(showEventEntries.id, entryId), eq(showEventEntries.showEventShowId, showId)))
    .get();
  if (!entry) {
    res.status(404).json({ error: 'Entry not found' });
    return;
  }

  const body = req.body ?? {};
  const fields = {
    placement: typeof body.placement === 'string' && body.placement.trim() ? body.placement.trim() : null,
    legsEarned: clampLegs(body.legs_earned),
    bob: Boolean(body.bob),
    bos: Boolean(body.bos),
    bis: Boolean(body.bis),
    ris: Boolean(body.ris),
    notes: typeof body.notes === 'string' && body.notes.trim() ? body.notes.trim() : null,
  };

  const existing = db
    .select()
    .from(showEventPlacements)
    .where(eq(showEventPlacements.entryId, entryId))
    .get();

  const saved = db.transaction((tx) => {
    let row;
    if (existing) {
      row = tx
        .update(showEventPlacements)
        .set(fields)
        .where(eq(showEventPlacements.id, existing.id))
        .returning()
        .get();
    } else {
      row = tx
        .insert(showEventPlacements)
        .values({ entryId, ...fields })
        .returning()
        .get();
    }
    tx.update(showEvents).set({ updatedAt: nowIso() }).where(eq(showEvents.id, eventId)).run();
    return row;
  });

  res.json(toSnakeCase(saved));
});

export default router;