import "dotenv/config";
import express from "express";
import path from "path";
import fs from "fs";
import { fileURLToPath } from "url";
import OpenAI from "openai";
import PptxGenJS from "pptxgenjs";
import { healthcheck, query, withTransaction } from "./db.js";

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);
const app = express();
const port = process.env.PORT || 3000;
const DEFAULT_SESSION_CODE = process.env.DEFAULT_SESSION_CODE || "TATER1";

if (!process.env.OPENAI_API_KEY) {
  console.warn("OPENAI_API_KEY is not set. Realtime and deck generation will fail until it is configured.");
}

function getOpenAIClient() {
  if (!process.env.OPENAI_API_KEY) {
    throw new Error("OPENAI_API_KEY is not configured.");
  }
  return new OpenAI({ apiKey: process.env.OPENAI_API_KEY });
}

const PUBLIC_DIR = path.join(__dirname, "public");
const GENERATED_DIR = path.join(__dirname, "generated");
if (!fs.existsSync(GENERATED_DIR)) {
  fs.mkdirSync(GENERATED_DIR, { recursive: true });
}

const STOPWORDS = new Set([
  "the","a","an","and","or","but","if","then","than","that","this","these","those","to","of","for","from","with","by","on","in","at","as","is","are","was","were","be","been","being","it","its","we","our","you","your","they","their","them","he","she","his","her","i","me","my","mine","do","does","did","can","could","should","would","will","just","into","about","over","under","very","more","most","less","few","some","any","all","not","no","yes","so","too","also","than","there","here","when","where","why","how","what","which","who","whom","because","while","during","across","through","up","down","out","off","again","still","really","live","session","discussion","workshop","participant","participants","tater"
]);

app.use("/generated", express.static(GENERATED_DIR));
app.use(express.static(PUBLIC_DIR));
app.use(express.json({ limit: "10mb" }));

function uid(prefix = "id") {
  return `${prefix}_${Date.now()}_${Math.random().toString(36).slice(2, 8)}`;
}

function parseBoolean(value, fallback = false) {
  if (typeof value === "boolean") return value;
  if (typeof value === "string") return value.toLowerCase() === "true";
  return fallback;
}

function sanitizeText(value, fallback = "") {
  return String(value ?? fallback).trim();
}

function extractTerms(seedTerms = [], title = "") {
  const source = [title, ...(Array.isArray(seedTerms) ? seedTerms : [])].join(" ");
  const words = source
    .toLowerCase()
    .replace(/[^a-z0-9\s-]/g, " ")
    .split(/\s+/)
    .filter(Boolean)
    .filter((word) => !STOPWORDS.has(word) && word.length > 2);

  const counts = new Map();
  for (const word of words) counts.set(word, (counts.get(word) || 0) + 1);

  return [...counts.entries()]
    .sort((a, b) => b[1] - a[1])
    .slice(0, 24)
    .map(([text, value]) => ({ text, value }));
}

function safeJsonParse(text, fallback = null) {
  try {
    return JSON.parse(text);
  } catch {
    const match = text.match(/```json\s*([\s\S]*?)```/i) || text.match(/\{[\s\S]*\}/);
    if (!match) return fallback;
    try {
      return JSON.parse(match[1] || match[0]);
    } catch {
      return fallback;
    }
  }
}

async function buildDeckContent({ title, audience, objective, slideCount, keyPoints }) {
  const model = process.env.OPENAI_TEXT_MODEL || "gpt-5.4-mini";
  const prompt = `You are creating a concise live-workshop slide deck for an in-person facilitation assistant named Tater.
Return JSON only.
Schema:
{
  "deck_title": string,
  "subtitle": string,
  "slides": [
    {
      "title": string,
      "bullets": string[3-5],
      "speaker_note": string
    }
  ]
}
Rules:
- Keep the tone practical, facilitative, and concrete.
- Make slides useful in live sessions.
- Avoid hype and jargon.
- Each bullet should be short.
- Create exactly ${slideCount} slides.
Inputs:
Title: ${title}
Audience: ${audience}
Objective: ${objective}
Key points: ${Array.isArray(keyPoints) ? keyPoints.join('; ') : ''}`;

  const openai = getOpenAIClient();
  const response = await openai.responses.create({
    model,
    input: prompt
  });

  const parsed = safeJsonParse(response.output_text, null);
  if (parsed?.slides?.length) return parsed;

  return {
    deck_title: title,
    subtitle: `${audience} · ${objective}`,
    slides: Array.from({ length: slideCount }, (_, index) => ({
      title: index === 0 ? title : `Slide ${index + 1}`,
      bullets: [
        `Audience: ${audience}`,
        `Objective: ${objective}`,
        ...(Array.isArray(keyPoints) && keyPoints.length ? [keyPoints[index % keyPoints.length]] : ["Live workshop support"])
      ],
      speaker_note: "Use this slide as a concise facilitation prompt."
    }))
  };
}

async function createDeckFile(deck) {
  const pptx = new PptxGenJS();
  pptx.layout = "LAYOUT_WIDE";
  pptx.author = "OpenAI + Tater";
  pptx.company = "Flying Cloud Solutions";
  pptx.subject = "Tater live facilitation deck";
  pptx.title = deck.deck_title || "Tater Deck";
  pptx.lang = "en-US";

  deck.slides.forEach((slide, idx) => {
    const s = pptx.addSlide();
    s.background = { color: idx === 0 ? "6F3F14" : "FFF8F1" };
    s.addText(slide.title || `Slide ${idx + 1}`, {
      x: 0.5, y: 0.35, w: 12.0, h: 0.6,
      fontFace: "Aptos Display", fontSize: idx === 0 ? 24 : 22,
      bold: true, color: idx === 0 ? "FFFFFF" : "6F3F14"
    });
    if (idx === 0) {
      s.addText(deck.subtitle || "Rapid facilitation deck", {
        x: 0.5, y: 1.15, w: 11.5, h: 0.5, fontSize: 12, color: "F2D9C2"
      });
    }
    const bulletRuns = (slide.bullets || []).map((bullet) => ({ text: bullet, options: { bullet: { indent: 18 } } }));
    s.addText(bulletRuns, {
      x: 0.9, y: idx === 0 ? 2.0 : 1.45, w: 11.0, h: 3.6,
      fontSize: 19, color: idx === 0 ? "FFFFFF" : "3B2A1C",
      breakLine: true, margin: 0.1, paraSpaceAfterPt: 12, valign: "top"
    });
  });

  const filename = `${(deck.deck_title || "tater-deck").toLowerCase().replace(/[^a-z0-9]+/g, "-")}-${Date.now()}.pptx`;
  const filepath = path.join(GENERATED_DIR, filename);
  await pptx.writeFile({ fileName: filepath });
  return { filename, filepath, url: `/generated/${filename}` };
}

async function getSessionByCode(sessionCode = DEFAULT_SESSION_CODE) {
  const rows = await query(`
    SELECT ws.workshop_session_id, ws.workshop_id, ws.session_code, ws.session_slug, ws.title,
           ws.session_type, ws.status, ws.facilitator_account_id,
           w.workshop_slug, w.title AS workshop_title
    FROM workshop_sessions ws
    JOIN workshops w ON w.workshop_id = ws.workshop_id
    WHERE ws.session_code = :sessionCode
    LIMIT 1
  `, { sessionCode });
  return rows[0] || null;
}

async function getTablesForSession(workshopSessionId) {
  return query(`
    SELECT session_table_id, workshop_session_id, table_code, table_name, capacity, topic_prompt, is_active
    FROM session_tables
    WHERE workshop_session_id = :workshopSessionId
    ORDER BY table_name ASC
  `, { workshopSessionId });
}

async function getTableByCode(workshopSessionId, tableCode) {
  if (!tableCode) return null;
  const rows = await query(`
    SELECT session_table_id, workshop_session_id, table_code, table_name
    FROM session_tables
    WHERE workshop_session_id = :workshopSessionId AND table_code = :tableCode
    LIMIT 1
  `, { workshopSessionId, tableCode });
  return rows[0] || null;
}

async function ensureDevice(workshopSessionId, deviceLabel) {
  const label = sanitizeText(deviceLabel);
  if (!label) return null;
  const existing = await query(`
    SELECT participant_device_id, session_participant_id, device_label
    FROM participant_devices
    WHERE workshop_session_id = :workshopSessionId AND device_label = :deviceLabel
    ORDER BY participant_device_id DESC
    LIMIT 1
  `, { workshopSessionId, deviceLabel: label });
  if (existing[0]) return existing[0];

  const result = await query(`
    INSERT INTO participant_devices (
      workshop_session_id, device_label, device_type, browser_name, os_name, audio_enabled, text_enabled, last_seen_at
    ) VALUES (
      :workshopSessionId, :deviceLabel, 'browser', NULL, NULL, 1, 1, NOW()
    )
  `, { workshopSessionId, deviceLabel: label });
  return { participant_device_id: result.insertId, session_participant_id: null, device_label: label };
}

async function loadPolls(workshopSessionId) {
  const pollRows = await query(`
    SELECT p.poll_id, p.prompt, p.title, p.status, p.created_at,
           po.poll_option_id, po.option_text, po.sort_order,
           pv.session_participant_id, pv.participant_device_id
    FROM polls p
    LEFT JOIN poll_options po ON po.poll_id = p.poll_id
    LEFT JOIN poll_votes pv ON pv.poll_option_id = po.poll_option_id
    WHERE p.workshop_session_id = :workshopSessionId AND p.status IN ('open', 'draft', 'closed')
    ORDER BY p.created_at DESC, po.sort_order ASC
  `, { workshopSessionId });

  const map = new Map();
  for (const row of pollRows) {
    if (!map.has(row.poll_id)) {
      map.set(row.poll_id, {
        id: `poll_${row.poll_id}`,
        dbId: row.poll_id,
        question: row.prompt,
        title: row.title,
        multiSelect: false,
        status: row.status,
        createdAt: row.created_at,
        options: [],
        votesByVoter: {}
      });
    }
    const poll = map.get(row.poll_id);
    if (row.poll_option_id && !poll.options.some((opt) => opt.dbId === row.poll_option_id)) {
      poll.options.push({ id: `opt_${row.poll_option_id}`, dbId: row.poll_option_id, label: row.option_text, votes: 0 });
    }
    if (row.poll_option_id && (row.participant_device_id || row.session_participant_id)) {
      const option = poll.options.find((opt) => opt.dbId === row.poll_option_id);
      if (option) option.votes += 1;
      const voterKey = row.participant_device_id ? `device_${row.participant_device_id}` : `participant_${row.session_participant_id}`;
      poll.votesByVoter[voterKey] = [`opt_${row.poll_option_id}`];
    }
  }
  return [...map.values()];
}

async function loadLatestWordCloud(workshopSessionId) {
  const cloudRows = await query(`
    SELECT wc.word_cloud_id, wc.title, wc.created_at, wct.term, wct.weight
    FROM word_clouds wc
    LEFT JOIN word_cloud_terms wct ON wct.word_cloud_id = wc.word_cloud_id
    WHERE wc.workshop_session_id = :workshopSessionId
    ORDER BY wc.created_at DESC, wct.weight DESC
  `, { workshopSessionId });
  if (!cloudRows.length) return [];
  const latestId = cloudRows[0].word_cloud_id;
  const rows = cloudRows.filter((row) => row.word_cloud_id === latestId);
  return [{
    id: `cloud_${latestId}`,
    dbId: latestId,
    title: rows[0].title,
    createdAt: rows[0].created_at,
    terms: rows.filter((r) => r.term).map((r) => ({ text: r.term, value: Number(r.weight) }))
  }];
}

async function loadDecks(workshopSessionId) {
  const rows = await query(`
    SELECT generated_deck_id, title, file_url, status, created_at, prompt
    FROM generated_decks
    WHERE workshop_session_id = :workshopSessionId
    ORDER BY created_at DESC
  `, { workshopSessionId });
  return rows.map((row) => ({
    id: `deck_${row.generated_deck_id}`,
    dbId: row.generated_deck_id,
    title: row.title,
    subtitle: row.prompt || '',
    slideCount: null,
    createdAt: row.created_at,
    url: row.file_url,
    status: row.status
  }));
}

async function loadNotes(workshopSessionId) {
  const rows = await query(`
    SELECT session_note_id, note_type, note_text, created_at
    FROM session_notes
    WHERE workshop_session_id = :workshopSessionId
    ORDER BY created_at DESC
    LIMIT 50
  `, { workshopSessionId });
  return rows.map((row) => ({ id: `note_${row.session_note_id}`, type: row.note_type, text: row.note_text, createdAt: row.created_at }));
}

async function loadState(workshopSessionId) {
  const [polls, wordClouds, decks, notes] = await Promise.all([
    loadPolls(workshopSessionId),
    loadLatestWordCloud(workshopSessionId),
    loadDecks(workshopSessionId),
    loadNotes(workshopSessionId)
  ]);
  return { polls, wordClouds, decks, notes };
}

async function loadTranscriptEvents({ workshopSessionId, tableId = null, limit = 100 }) {
  const rows = await query(`
    SELECT te.transcript_event_id, te.workshop_session_id, te.session_participant_id, te.session_table_id,
           te.participant_device_id, te.event_type, te.speaker_label, te.transcript_text,
           te.language_code, te.confidence, te.started_at, te.ended_at,
           st.table_code, st.table_name,
           sp.display_name,
           pd.device_label
    FROM transcript_events te
    LEFT JOIN session_tables st ON st.session_table_id = te.session_table_id
    LEFT JOIN session_participants sp ON sp.session_participant_id = te.session_participant_id
    LEFT JOIN participant_devices pd ON pd.participant_device_id = te.participant_device_id
    WHERE te.workshop_session_id = :workshopSessionId
      AND (:tableId IS NULL OR te.session_table_id = :tableId)
    ORDER BY te.transcript_event_id DESC
    LIMIT :limit
  `, { workshopSessionId, tableId, limit: Number(limit) });

  return rows.reverse().map((row) => ({
    id: row.transcript_event_id,
    sessionParticipantId: row.session_participant_id,
    sessionTableId: row.session_table_id,
    participantDeviceId: row.participant_device_id,
    eventType: row.event_type,
    speakerLabel: row.speaker_label,
    transcriptText: row.transcript_text,
    languageCode: row.language_code,
    confidence: row.confidence,
    startedAt: row.started_at,
    endedAt: row.ended_at,
    tableCode: row.table_code,
    tableName: row.table_name,
    displayName: row.display_name,
    deviceLabel: row.device_label
  }));
}

async function buildTranscriptContext({ workshopSessionId, tableId = null, limit = 40 }) {
  const transcriptEvents = await loadTranscriptEvents({ workshopSessionId, tableId, limit });
  if (!transcriptEvents.length) return 'No transcript context has been captured yet.';
  return transcriptEvents
    .map((event) => {
      const source = event.displayName || event.speakerLabel || event.deviceLabel || event.tableName || 'Speaker';
      return `${source}: ${event.transcriptText}`;
    })
    .join('\n');
}

async function createPollInDb({ workshopSessionId, question, options }) {
  return withTransaction(async (conn) => {
    const [pollResult] = await conn.execute(`
      INSERT INTO polls (workshop_session_id, title, prompt, status, opens_at)
      VALUES (?, ?, ?, 'open', NOW())
    `, [workshopSessionId, question, question]);
    let sort = 1;
    for (const label of options) {
      await conn.execute(`
        INSERT INTO poll_options (poll_id, option_text, sort_order)
        VALUES (?, ?, ?)
      `, [pollResult.insertId, label, sort++]);
    }
    return pollResult.insertId;
  });
}

async function createWordCloudInDb({ workshopSessionId, title, terms }) {
  return withTransaction(async (conn) => {
    const [cloudResult] = await conn.execute(`
      INSERT INTO word_clouds (workshop_session_id, title, prompt)
      VALUES (?, ?, ?)
    `, [workshopSessionId, title, title]);
    for (const term of terms) {
      await conn.execute(`
        INSERT INTO word_cloud_terms (word_cloud_id, term, weight, source_count)
        VALUES (?, ?, ?, ?)
      `, [cloudResult.insertId, term.text, Number(term.value || 1), Number(term.value || 1)]);
    }
    return cloudResult.insertId;
  });
}

async function createDeckRecordInDb({ workshopSessionId, title, prompt, url, filepath }) {
  const rows = await query(`
    INSERT INTO generated_decks (workshop_session_id, title, prompt, storage_path, file_url, status)
    VALUES (:workshopSessionId, :title, :prompt, :storagePath, :fileUrl, 'generated')
  `, { workshopSessionId, title, prompt, storagePath: filepath, fileUrl: url });
  return rows.insertId;
}

async function logAiAction({ workshopSessionId, actionType, requestPayload, responsePayload, status = 'completed' }) {
  await query(`
    INSERT INTO ai_actions (workshop_session_id, action_type, request_payload, response_payload, status, completed_at)
    VALUES (:workshopSessionId, :actionType, :requestPayload, :responsePayload, :status, NOW())
  `, {
    workshopSessionId,
    actionType,
    requestPayload: JSON.stringify(requestPayload ?? {}),
    responsePayload: JSON.stringify(responsePayload ?? {}),
    status
  });
}

async function ensureParticipantForJoin(session, { displayName, email, role = 'participant', tableCode = null }) {
  const cleanName = sanitizeText(displayName, 'Participant');
  const cleanEmail = sanitizeText(email, '') || null;
  return withTransaction(async (conn) => {
    let accountId = null;
    if (cleanEmail) {
      const [existingAccounts] = await conn.execute(`SELECT account_id FROM accounts WHERE email = ? LIMIT 1`, [cleanEmail]);
      if (existingAccounts[0]) {
        accountId = existingAccounts[0].account_id;
      } else {
        const [accountResult] = await conn.execute(`
          INSERT INTO accounts (email, display_name, role, auth_provider)
          VALUES (?, ?, ?, 'magic_link')
        `, [cleanEmail, cleanName, role === 'facilitator' ? 'facilitator' : 'participant']);
        accountId = accountResult.insertId;
      }
    }

    const [participantResult] = await conn.execute(`
      INSERT INTO session_participants (workshop_session_id, account_id, display_name, email, role, status, last_seen_at)
      VALUES (?, ?, ?, ?, ?, 'active', NOW())
    `, [session.workshop_session_id, accountId, cleanName, cleanEmail, role]);

    let assignedTable = null;
    if (tableCode) {
      const [tableRows] = await conn.execute(`
        SELECT session_table_id, table_code, table_name
        FROM session_tables
        WHERE workshop_session_id = ? AND table_code = ?
        LIMIT 1
      `, [session.workshop_session_id, tableCode]);
      if (tableRows[0]) {
        await conn.execute(`
          INSERT INTO table_assignments (workshop_session_id, session_participant_id, session_table_id, assigned_by, is_active)
          VALUES (?, ?, ?, 'self', 1)
        `, [session.workshop_session_id, participantResult.insertId, tableRows[0].session_table_id]);
        assignedTable = tableRows[0];
      }
    }

    return { session_participant_id: participantResult.insertId, display_name: cleanName, email: cleanEmail, table: assignedTable };
  });
}

async function assignParticipantToTable(session, { sessionParticipantId, tableCode }) {
  return withTransaction(async (conn) => {
    const [tableRows] = await conn.execute(`
      SELECT session_table_id, table_code, table_name
      FROM session_tables
      WHERE workshop_session_id = ? AND table_code = ?
      LIMIT 1
    `, [session.workshop_session_id, tableCode]);
    if (!tableRows[0]) throw new Error('Table not found.');
    const table = tableRows[0];
    await conn.execute(`
      UPDATE table_assignments
      SET is_active = 0, released_at = NOW()
      WHERE workshop_session_id = ? AND session_participant_id = ? AND is_active = 1
    `, [session.workshop_session_id, sessionParticipantId]);
    await conn.execute(`
      INSERT INTO table_assignments (workshop_session_id, session_participant_id, session_table_id, assigned_by, is_active)
      VALUES (?, ?, ?, 'self', 1)
    `, [session.workshop_session_id, sessionParticipantId, table.session_table_id]);
    return table;
  });
}

async function handleTool(name, args, session) {
  const workshopSessionId = session.workshop_session_id;
  switch (name) {
    case 'create_poll': {
      const question = sanitizeText(args.question, 'New poll');
      const options = (Array.isArray(args.options) ? args.options : [])
        .map((label) => sanitizeText(label))
        .filter(Boolean)
        .slice(0, 8);
      const pollId = await createPollInDb({ workshopSessionId, question, options: options.length ? options : ['Yes', 'No'] });
      const polls = await loadPolls(workshopSessionId);
      const poll = polls.find((item) => item.dbId === pollId);
      const result = { ok: true, poll, message: `Poll created: ${poll.question}` };
      await logAiAction({ workshopSessionId, actionType: 'poll', requestPayload: args, responsePayload: result });
      return result;
    }
    case 'generate_word_cloud': {
      const title = sanitizeText(args.title, 'Live word cloud');
      const terms = extractTerms(args.seed_terms, title);
      const cloudId = await createWordCloudInDb({ workshopSessionId, title, terms });
      const clouds = await loadLatestWordCloud(workshopSessionId);
      const wordCloud = clouds.find((item) => item.dbId === cloudId) || clouds[0];
      const result = { ok: true, wordCloud, message: `Word cloud created with ${wordCloud.terms.length} terms.` };
      await logAiAction({ workshopSessionId, actionType: 'word_cloud', requestPayload: args, responsePayload: result });
      return result;
    }
    case 'generate_slide_deck': {
      const payload = {
        title: sanitizeText(args.title, 'Live session deck'),
        audience: sanitizeText(args.audience, 'Workshop participants'),
        objective: sanitizeText(args.objective, 'Capture current discussion and next moves'),
        slideCount: Math.min(Math.max(Number(args.slide_count) || 5, 3), 10),
        keyPoints: Array.isArray(args.key_points) ? args.key_points.map((p) => sanitizeText(p)).filter(Boolean) : []
      };
      const transcriptContext = await buildTranscriptContext({ workshopSessionId, limit: 50 });
      const deck = await buildDeckContent({
        ...payload,
        keyPoints: payload.keyPoints.length ? payload.keyPoints : transcriptContext.split('\n').slice(-5)
      });
      const file = await createDeckFile(deck);
      const dbId = await createDeckRecordInDb({ workshopSessionId, title: deck.deck_title || payload.title, prompt: `${payload.audience} · ${payload.objective}`, url: file.url, filepath: file.filepath });
      const record = {
        id: `deck_${dbId}`,
        dbId,
        createdAt: new Date().toISOString(),
        title: deck.deck_title || payload.title,
        subtitle: deck.subtitle || `${payload.audience} · ${payload.objective}`,
        slideCount: deck.slides.length,
        url: file.url,
        slides: deck.slides
      };
      const result = { ok: true, deck: record, message: `Deck generated: ${record.title}` };
      await logAiAction({ workshopSessionId, actionType: 'deck', requestPayload: args, responsePayload: result });
      return result;
    }
    default:
      return { ok: false, error: `Unknown tool: ${name}` };
  }
}

app.get('/api/health', async (_req, res) => {
  try {
    const dbOk = await healthcheck();
    res.json({ ok: true, database: dbOk, defaultSessionCode: DEFAULT_SESSION_CODE, openaiConfigured: !!process.env.OPENAI_API_KEY });
  } catch (error) {
    res.status(500).json({ ok: false, error: error.message });
  }
});

app.get('/api/sessions/by-code/:sessionCode/bootstrap', async (req, res) => {
  try {
    const session = await getSessionByCode(req.params.sessionCode);
    if (!session) return res.status(404).json({ ok: false, error: 'Session not found.' });
    const [tables, state, transcriptEvents] = await Promise.all([
      getTablesForSession(session.workshop_session_id),
      loadState(session.workshop_session_id),
      loadTranscriptEvents({ workshopSessionId: session.workshop_session_id, limit: 50 })
    ]);
    res.json({ ok: true, session, tables, state, transcriptEvents });
  } catch (error) {
    res.status(500).json({ ok: false, error: error.message });
  }
});

app.post('/api/sessions/join', async (req, res) => {
  try {
    const sessionCode = sanitizeText(req.body?.sessionCode, DEFAULT_SESSION_CODE);
    const session = await getSessionByCode(sessionCode);
    if (!session) return res.status(404).json({ ok: false, error: 'Session not found.' });
    const participant = await ensureParticipantForJoin(session, req.body || {});
    res.json({ ok: true, session, participant });
  } catch (error) {
    res.status(500).json({ ok: false, error: error.message });
  }
});

app.post('/api/sessions/:sessionCode/assign-table', async (req, res) => {
  try {
    const session = await getSessionByCode(req.params.sessionCode);
    if (!session) return res.status(404).json({ ok: false, error: 'Session not found.' });
    const sessionParticipantId = Number(req.body?.sessionParticipantId);
    const tableCode = sanitizeText(req.body?.tableCode);
    if (!sessionParticipantId || !tableCode) {
      return res.status(400).json({ ok: false, error: 'sessionParticipantId and tableCode are required.' });
    }
    const table = await assignParticipantToTable(session, { sessionParticipantId, tableCode });
    res.json({ ok: true, table });
  } catch (error) {
    res.status(500).json({ ok: false, error: error.message });
  }
});

app.get('/api/transcripts', async (req, res) => {
  try {
    const sessionCode = sanitizeText(req.query.sessionCode, DEFAULT_SESSION_CODE);
    const session = await getSessionByCode(sessionCode);
    if (!session) return res.status(404).json({ ok: false, error: 'Session not found.' });

    const tableCode = sanitizeText(req.query.tableCode, '') || null;
    const table = tableCode ? await getTableByCode(session.workshop_session_id, tableCode) : null;
    const limit = Math.min(Math.max(Number(req.query.limit) || 100, 1), 500);
    const transcriptEvents = await loadTranscriptEvents({
      workshopSessionId: session.workshop_session_id,
      tableId: table?.session_table_id || null,
      limit
    });

    res.json({ ok: true, transcriptEvents });
  } catch (error) {
    res.status(500).json({ ok: false, error: error.message });
  }
});

app.post('/api/transcript-events', async (req, res) => {
  try {
    const sessionCode = sanitizeText(req.body?.sessionCode, DEFAULT_SESSION_CODE);
    const transcriptText = sanitizeText(req.body?.transcriptText);
    if (!transcriptText) return res.status(400).json({ ok: false, error: 'transcriptText is required.' });
    const session = await getSessionByCode(sessionCode);
    if (!session) return res.status(404).json({ ok: false, error: 'Session not found.' });

    const tableCode = sanitizeText(req.body?.tableCode, '') || null;
    const table = tableCode ? await getTableByCode(session.workshop_session_id, tableCode) : null;
    const speakerLabel = sanitizeText(req.body?.speakerLabel, '') || null;
    const eventType = sanitizeText(req.body?.eventType, 'final');
    const participantDeviceLabel = sanitizeText(req.body?.participantDeviceLabel, '') || null;
    const device = req.body?.participantDeviceId
      ? { participant_device_id: Number(req.body.participantDeviceId) }
      : await ensureDevice(session.workshop_session_id, participantDeviceLabel);

    const insertResult = await query(`
      INSERT INTO transcript_events (
        workshop_session_id, session_participant_id, session_table_id, participant_device_id,
        event_type, speaker_label, transcript_text, language_code, confidence, started_at, ended_at
      ) VALUES (
        :workshopSessionId, :sessionParticipantId, :sessionTableId, :participantDeviceId,
        :eventType, :speakerLabel, :transcriptText, :languageCode, :confidence, NOW(), NOW()
      )
    `, {
      workshopSessionId: session.workshop_session_id,
      sessionParticipantId: req.body?.sessionParticipantId || null,
      sessionTableId: table?.session_table_id || req.body?.sessionTableId || null,
      participantDeviceId: device?.participant_device_id || null,
      eventType,
      speakerLabel,
      transcriptText,
      languageCode: sanitizeText(req.body?.languageCode, '') || null,
      confidence: req.body?.confidence || null
    });

    if (device?.participant_device_id) {
      await query(`
        UPDATE participant_devices
        SET last_seen_at = NOW(), audio_enabled = 1, text_enabled = 1
        WHERE participant_device_id = :participantDeviceId
      `, { participantDeviceId: device.participant_device_id });
    }

    res.json({ ok: true, transcriptEventId: insertResult?.insertId || null, participantDeviceId: device?.participant_device_id || null });
  } catch (error) {
    console.error('POST /api/transcript-events failed', error);
    res.status(500).json({ ok: false, error: error.message });
  }
});

app.post('/session', express.text({ type: '*/*' }), async (req, res) => {
  try {
    const sessionConfig = JSON.stringify({
      type: 'realtime',
      model: process.env.OPENAI_REALTIME_MODEL || 'gpt-realtime',
      output_modalities: ['text'],
      audio: {
        input: {
          turn_detection: {
            type: 'server_vad',
            create_response: false,
            interrupt_response: false,
            silence_duration_ms: 700,
            prefix_padding_ms: 400,
            threshold: 0.55
          },
          transcription: {
            model: 'gpt-4o-mini-transcribe',
            language: 'en'
          }
        }
      }
    });

    const fd = new FormData();
    fd.set('sdp', req.body);
    fd.set('session', sessionConfig);

    const response = await fetch('https://api.openai.com/v1/realtime/calls', {
      method: 'POST',
      headers: { Authorization: `Bearer ${process.env.OPENAI_API_KEY}` },
      body: fd
    });

    if (!response.ok) {
      const text = await response.text();
      res.status(response.status).send(text);
      return;
    }

    const answerSdp = await response.text();
    res.type('application/sdp').send(answerSdp);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.get('/api/state', async (req, res) => {
  try {
    const sessionCode = sanitizeText(req.query.sessionCode, DEFAULT_SESSION_CODE);
    const session = await getSessionByCode(sessionCode);
    if (!session) return res.status(404).json({ ok: false, error: 'Session not found.' });
    const state = await loadState(session.workshop_session_id);
    res.json(state);
  } catch (error) {
    res.status(500).json({ ok: false, error: error.message });
  }
});

app.post('/api/tool', async (req, res) => {
  try {
    const sessionCode = sanitizeText(req.body?.sessionCode, DEFAULT_SESSION_CODE);
    const session = await getSessionByCode(sessionCode);
    if (!session) return res.status(404).json({ ok: false, error: 'Session not found.' });
    const name = sanitizeText(req.body?.name);
    const args = req.body?.arguments || {};
    const result = await handleTool(name, args, session);
    res.json(result);
  } catch (error) {
    res.status(500).json({ ok: false, error: error.message });
  }
});

app.post('/api/polls/:pollId/vote', async (req, res) => {
  try {
    const pollDbId = Number(String(req.params.pollId).replace(/^poll_/, ''));
    if (!pollDbId) return res.status(400).json({ ok: false, error: 'Invalid poll id.' });
    const pollRows = await query(`SELECT poll_id, workshop_session_id FROM polls WHERE poll_id = :pollId LIMIT 1`, { pollId: pollDbId });
    const poll = pollRows[0];
    if (!poll) return res.status(404).json({ ok: false, error: 'Poll not found.' });

    const voterId = sanitizeText(req.body?.voterId, uid('voter'));
    const device = await ensureDevice(poll.workshop_session_id, voterId);
    const optionIds = Array.isArray(req.body?.optionIds)
      ? req.body.optionIds.map((id) => Number(String(id).replace(/^opt_/, ''))).filter(Boolean)
      : [];

    await withTransaction(async (conn) => {
      await conn.execute(`DELETE FROM poll_votes WHERE poll_id = ? AND participant_device_id = ?`, [pollDbId, device.participant_device_id]);
      for (const optionId of optionIds.slice(0, 1)) {
        await conn.execute(`
          INSERT INTO poll_votes (poll_id, poll_option_id, workshop_session_id, participant_device_id)
          VALUES (?, ?, ?, ?)
        `, [pollDbId, optionId, poll.workshop_session_id, device.participant_device_id]);
      }
      await conn.execute(`UPDATE participant_devices SET last_seen_at = NOW() WHERE participant_device_id = ?`, [device.participant_device_id]);
    });

    const polls = await loadPolls(poll.workshop_session_id);
    const hydratedPoll = polls.find((item) => item.dbId === pollDbId);
    if (hydratedPoll) {
      hydratedPoll.votesByVoter[voterId] = optionIds.map((id) => `opt_${id}`);
    }
    res.json({ ok: true, poll: hydratedPoll });
  } catch (error) {
    res.status(500).json({ ok: false, error: error.message });
  }
});

app.get('*', (_req, res) => {
  res.sendFile(path.join(PUBLIC_DIR, 'index.html'));
});

app.listen(port, () => {
  console.log(`Tater running at http://localhost:${port}`);
  console.log(`Default session code: ${DEFAULT_SESSION_CODE}`);
});
