import { pool } from "../config/db.js";
import { PTW_MAX_UPLOAD_BYTES, PTW_MAX_UPLOAD_MB } from "../constants/uploadLimits.js";
import { ApiError } from "../utils/ApiError.js";
import { getJwtUserId } from "../middlewares/authMiddleware.js";
import { isPtwFormArchived } from "../utils/ptwVersioning.js";
import { normalizeBlobForPersist, bufferFromDb, sniffContentType } from "../utils/binary.js";
import { inferSectionTypeCode } from "../utils/sectionTypeInfer.js";
import {
  isSectionCompleted,
  requireSectionAnchorQuestionId
} from "../utils/ptwSectionWorkflow.js";
import { PERMIT_STATUS, ensurePermitStatuses } from "../utils/permitStatus.js";
import { notifyHandoverRecipient } from "../services/handoverNotificationService.js";

/** How long each opening/handover keeps the permit valid. */
const PERMIT_VALIDITY_HOURS = 8;

function friendlyMediaPersistError(err) {
  const msg = String(err?.message || "");
  if (/max_allowed_packet|packet bigger/i.test(msg)) {
    return `Video or media file is too large for the database (limit ${PTW_MAX_UPLOAD_MB} MB). Record a shorter clip or lower resolution.`;
  }
  return msg || "unknown error";
}

function assertUploadSizes(files) {
  for (const file of files || []) {
    const size = file?.buffer?.length ?? file?.size ?? 0;
    if (size > PTW_MAX_UPLOAD_BYTES) {
      const name = file?.originalname || file?.fieldname || "upload";
      throw new ApiError(
        400,
        `"${name}" is too large (${Math.ceil(size / (1024 * 1024))} MB). Maximum allowed is ${PTW_MAX_UPLOAD_MB} MB.`
      );
    }
  }
}

async function persistSectionMediaForSubmission(conn, submissionId, sectionMediaMap) {
  let mediaCount = 0;
  for (const [sectionIdStr, kinds] of sectionMediaMap.entries()) {
    const sectionId = Number(sectionIdStr);
    if (!Number.isInteger(sectionId) || sectionId <= 0) continue;
    const anchorQuestionId = await requireSectionAnchorQuestionId(conn, sectionId);

    if (kinds.video?.length) {
      const { mediaCount: mc } = await insertAnswerWithMedia(conn, {
        submissionId,
        questionId: anchorQuestionId,
        isMedia: 1,
        answerValue: "Section video upload",
        files: kinds.video
      });
      mediaCount += mc;
    }
    if (kinds.signature?.length) {
      const { mediaCount: mc } = await insertAnswerWithMedia(conn, {
        submissionId,
        questionId: anchorQuestionId,
        isMedia: 1,
        answerValue: "Digital signature",
        files: kinds.signature
      });
      mediaCount += mc;
    }
    if (kinds.selfie?.length) {
      const { mediaCount: mc } = await insertAnswerWithMedia(conn, {
        submissionId,
        questionId: anchorQuestionId,
        isMedia: 1,
        answerValue: "Authorization selfie",
        files: kinds.selfie
      });
      mediaCount += mc;
    }
  }
  return mediaCount;
}

function toPositiveInt(value, fieldName) {
  const n = Number(value);
  if (!Number.isInteger(n) || n <= 0) {
    throw new ApiError(400, `${fieldName} must be a positive integer`);
  }
  return n;
}

function normalizeQuestionId(raw) {
  const n = Number(raw);
  return Number.isInteger(n) && n > 0 ? n : null;
}

/** Parse `answers` whether sent as a JSON string (multipart) or a real array (JSON body). */
function parseAnswers(raw) {
  if (Array.isArray(raw)) return raw;
  if (typeof raw === "string" && raw.trim()) {
    try {
      const parsed = JSON.parse(raw);
      return Array.isArray(parsed) ? parsed : [];
    } catch {
      throw new ApiError(400, "`answers` must be valid JSON array");
    }
  }
  return [];
}

/**
 * Group uploaded files by question. Convention: each file's fieldname is
 * `media_<question_id>`. Multiple files per question become occurrences 1..n.
 */
function groupFilesByQuestion(files) {
  const map = new Map();
  for (const file of files || []) {
    const match = /^media_(\d+)$/.exec(file.fieldname || "");
    if (!match) continue;
    const qid = Number(match[1]);
    if (!Number.isInteger(qid) || qid <= 0) continue;
    if (!map.has(qid)) map.set(qid, []);
    map.get(qid).push(file);
  }
  return map;
}

/** Section-level uploads: media_section_<sectionId>_video|signature|selfie */
function groupSectionMedia(files) {
  const map = new Map();
  for (const file of files || []) {
    const match = /^media_section_(\d+)_(video|signature|selfie)$/.exec(
      file.fieldname || ""
    );
    if (!match) continue;
    const sectionId = Number(match[1]);
    const kind = match[2];
    if (!Number.isInteger(sectionId) || sectionId <= 0) continue;
    if (!map.has(sectionId)) {
      map.set(sectionId, { video: [], signature: [], selfie: [] });
    }
    map.get(sectionId)[kind].push(file);
  }
  return map;
}

function parseJsonField(raw, fieldName) {
  if (raw == null || raw === "") return null;
  if (typeof raw === "object") return raw;
  if (typeof raw === "string" && raw.trim()) {
    try {
      return JSON.parse(raw);
    } catch {
      throw new ApiError(400, `${fieldName} must be valid JSON`);
    }
  }
  return null;
}

async function insertAnswerWithMedia(conn, {
  submissionId,
  questionId,
  isMedia,
  answerValue,
  files = [],
  inlineMedia = []
}) {
  const qid = Number(questionId);
  if (!Number.isInteger(qid) || qid <= 0) {
    throw new ApiError(400, "question_id is required for ptw_answers");
  }
  const [ansResult] = await conn.query(
    `INSERT INTO ptw_answers (submission_id, question_id, is_media, answer_value)
     VALUES (?, ?, ?, ?)`,
    [submissionId, qid, isMedia ? 1 : 0, answerValue ?? null]
  );
  const answersId = Number(ansResult.insertId);
  let mediaCount = 0;
  let occurrence = 0;
  for (const file of files) {
    occurrence += 1;
    await conn.query(
      "INSERT INTO ptw_answers_media (answers_id, occurrence, media) VALUES (?, ?, ?)",
      [answersId, occurrence, file.buffer]
    );
    mediaCount += 1;
  }
  for (const m of inlineMedia) {
    const buf = normalizeBlobForPersist(m);
    if (!buf) continue;
    occurrence += 1;
    await conn.query(
      "INSERT INTO ptw_answers_media (answers_id, occurrence, media) VALUES (?, ?, ?)",
      [answersId, occurrence, buf]
    );
    mediaCount += 1;
  }
  return { answersId, mediaCount };
}

/** Prefer JWT/body submitter when that user exists in `users`; else NULL (column allows NULL). */
async function resolveSubmitterId(conn, body, userPayload, permitId = null) {
  const candidates = [
    body.submitter_id,
    getJwtUserId(userPayload)
  ];
  for (const raw of candidates) {
    if (raw == null || raw === "") continue;
    const n = Number(raw);
    if (!Number.isInteger(n) || n <= 0) continue;
    const [rows] = await conn.query(
      "SELECT user_id FROM users WHERE user_id = ? LIMIT 1",
      [n]
    );
    if (rows.length) return n;
  }
  if (permitId != null) {
    const [permitRows] = await conn.query(
      "SELECT uploader_user_id FROM ptw_permits WHERE permit_id = ? LIMIT 1",
      [permitId]
    );
    const uploaderId = Number(permitRows[0]?.uploader_user_id);
    if (Number.isInteger(uploaderId) && uploaderId > 0) {
      return uploaderId;
    }
  }
  return null;
}

async function assertQuestionsExist(conn, questionIds) {
  if (!questionIds.length) return;
  const placeholders = questionIds.map(() => "?").join(",");
  const [rows] = await conn.query(
    `SELECT question_id FROM ptw_questions WHERE question_id IN (${placeholders})`,
    questionIds
  );
  const found = new Set(rows.map((r) => Number(r.question_id)));
  const missing = questionIds.filter((id) => !found.has(id));
  if (missing.length) {
    throw new ApiError(400, `Unknown question_id(s): ${missing.join(", ")}`);
  }
}

/**
 * POST /api/ptw/submissions
 *
 * Ingests a permit submission in one transaction:
 *   ptw_submissions -> ptw_answers -> ptw_answers_media (LONGBLOB media).
 * Also links the submission to a permit via ptw_opening_closing_map when
 * `submission_role` is "opening", "closing", "handover", "handback", or "audit".
 *
 * Body (multipart or JSON):
 *   permit_id, form_id, submitter_id?, submission_role? ("opening"|"closing"|"handover"|"handback"|"audit"),
 *   opening_sub_id? (required to close), answers (JSON array of
 *   { question_id, is_media, answer_value })
 *   Media files: fieldname `media_<question_id>` (repeatable).
 */
export async function createSubmission(req, res, next) {
  const conn = await pool.getConnection();
  try {
    const body = req.body || {};
    const permitId = toPositiveInt(body.permit_id, "permit_id");
    const formId = toPositiveInt(body.form_id, "form_id");

    const answers = parseAnswers(body.answers);
    if (!answers.length) {
      throw new ApiError(400, "answers must be a non-empty array");
    }

    for (const ans of answers) {
      if (normalizeQuestionId(ans?.question_id) == null) {
        throw new ApiError(400, "Each answer must have a valid question_id");
      }
    }
    const questionIds = [
      ...new Set(answers.map((a) => normalizeQuestionId(a.question_id)))
    ];

    const role = String(body.submission_role || "").trim().toLowerCase();
    const VALID_ROLES = ["opening", "closing", "handover", "handback", "audit"];
    if (role && !VALID_ROLES.includes(role)) {
      throw new ApiError(
        400,
        "submission_role must be 'opening', 'closing', 'handover', 'handback', or 'audit'"
      );
    }

    // Validate references before writing.
    const [permitRows] = await conn.query(
      "SELECT permit_id, permit_status_id, valid_from, valid_until FROM ptw_permits WHERE permit_id = ? LIMIT 1",
      [permitId]
    );
    if (!permitRows.length) throw new ApiError(404, "Permit not found");
    const permitRow = permitRows[0];

    // A closed permit is final — no opening, specialized, handover, handback, or audit.
    if (Number(permitRow.permit_status_id) === PERMIT_STATUS.CLOSED) {
      throw new ApiError(400, "This permit is closed; no further submissions are allowed.");
    }

    if (role === "opening") {
      if (permitRow.valid_from != null) {
        throw new ApiError(400, "Permit validity has already started; cannot submit another opening");
      }
    } else if (role === "handover" || role === "handback" || role === "audit") {
      if (permitRow.valid_from == null) {
        throw new ApiError(400, "Permit has no active validity window; submit the general permit first");
      }
    }

    if (role === "handback") {
      const [handbackRows] = await conn.query(
        "SELECT submission_id FROM ptw_submissions WHERE permit_id = ? AND submission_role = 'handback' LIMIT 1",
        [permitId]
      );
      if (handbackRows.length) {
        throw new ApiError(400, "This permit already has a handback submission");
      }
    }

    const [formRows] = await conn.query(
      "SELECT 1 FROM ptw_form_details WHERE form_id = ? LIMIT 1",
      [formId]
    );
    if (!formRows.length) throw new ApiError(404, "Form not found");
    if (await isPtwFormArchived(conn, formId)) {
      throw new ApiError(400, "This form version is archived; submissions must use the current form.");
    }

    await assertQuestionsExist(conn, questionIds);

    const submitterId = await resolveSubmitterId(conn, body, req.user, permitId);
    if (submitterId == null) {
      throw new ApiError(400, "Could not resolve submitter_id for this submission");
    }
    const filesByQuestion = groupFilesByQuestion(req.files);
    const sectionMediaMap = groupSectionMedia(req.files);
    assertUploadSizes(req.files);

    await conn.beginTransaction();

    const [subResult] = await conn.query(
      "INSERT INTO ptw_submissions (permit_id, submitter_id, form_id, submission_role) VALUES (?, ?, ?, ?)",
      [permitId, submitterId, formId, role || null]
    );
    const submissionId = Number(subResult.insertId);

    let mediaCount = 0;
    for (const ans of answers) {
      const qid = normalizeQuestionId(ans?.question_id);
      if (qid == null) {
        throw new ApiError(400, "Each answer must have a valid question_id");
      }
      const isMedia = ans.is_media ? 1 : 0;
      const [ansResult] = await conn.query(
        "INSERT INTO ptw_answers (submission_id, question_id, is_media, answer_value) VALUES (?, ?, ?, ?)",
        [submissionId, qid, isMedia, ans.answer_value ?? null]
      );
      const answersId = Number(ansResult.insertId);

      // Persist media (uploaded files first, else inline base64 in answer.media[]).
      const files = filesByQuestion.get(qid) || [];
      let occurrence = 0;
      for (const file of files) {
        occurrence += 1;
        await conn.query(
          "INSERT INTO ptw_answers_media (answers_id, occurrence, media) VALUES (?, ?, ?)",
          [answersId, occurrence, file.buffer]
        );
        mediaCount += 1;
      }
      const inlineMedia = Array.isArray(ans.media) ? ans.media : [];
      for (const m of inlineMedia) {
        const buf = normalizeBlobForPersist(m);
        if (!buf) continue;
        occurrence += 1;
        await conn.query(
          "INSERT INTO ptw_answers_media (answers_id, occurrence, media) VALUES (?, ?, ?)",
          [answersId, occurrence, buf]
        );
        mediaCount += 1;
      }
    }

    try {
      mediaCount += await persistSectionMediaForSubmission(
        conn,
        submissionId,
        sectionMediaMap
      );
    } catch (mediaErr) {
      if (mediaErr instanceof ApiError) throw mediaErr;
      throw new ApiError(400, `Failed to save section media: ${friendlyMediaPersistError(mediaErr)}`);
    }

    let permitValidity = null;

    async function readPermitValidity() {
      const [rows] = await conn.query(
        "SELECT valid_from, valid_until FROM ptw_permits WHERE permit_id = ? LIMIT 1",
        [permitId]
      );
      return rows[0] || null;
    }

    /** Link this closing/handback submission to the latest open opening row. */
    async function linkClosingSubmission({ required }) {
      let openingSubId = body.opening_sub_id != null ? Number(body.opening_sub_id) : null;
      if (!Number.isInteger(openingSubId) || openingSubId <= 0) {
        const [openRows] = await conn.query(
          `SELECT opening_sub_id FROM ptw_opening_closing_map
            WHERE permit_id = ? AND closing_sub_id IS NULL
            ORDER BY opening_sub_id DESC LIMIT 1`,
          [permitId]
        );
        if (!openRows.length) {
          if (required) {
            throw new ApiError(400, "No open opening-submission found for this permit to close");
          }
          return;
        }
        openingSubId = Number(openRows[0].opening_sub_id);
      }
      const [updateResult] = await conn.query(
        "UPDATE ptw_opening_closing_map SET closing_sub_id = ? WHERE permit_id = ? AND opening_sub_id = ?",
        [submissionId, permitId, openingSubId]
      );
      if (!updateResult.affectedRows && required) {
        throw new ApiError(404, "Matching opening submission not found for this permit");
      }
    }

    // Make sure the lifecycle statuses exist before any status transition so the
    // ptw_permits.permit_status_id foreign key never fails.
    if (role === "opening" || role === "handover" || role === "handback") {
      await ensurePermitStatuses(conn);
    }

    // Lifecycle linkage / state transitions.
    if (role === "opening") {
      // GENERAL permit submitted -> open the permit and start the validity clock.
      await conn.query(
        "INSERT INTO ptw_opening_closing_map (permit_id, opening_sub_id, closing_sub_id) VALUES (?, ?, NULL)",
        [permitId, submissionId]
      );
      const [validityResult] = await conn.query(
        `UPDATE ptw_permits
         SET valid_from = NOW(),
             valid_until = DATE_ADD(NOW(), INTERVAL ? HOUR),
             permit_status_id = ?
         WHERE permit_id = ? AND valid_from IS NULL`,
        [PERMIT_VALIDITY_HOURS, PERMIT_STATUS.ACTIVE, permitId]
      );
      if (!validityResult.affectedRows) {
        throw new ApiError(400, "Permit validity could not be started");
      }
      permitValidity = await readPermitValidity();
    } else if (role === "handover") {
      // Permit handed to the next person -> keep it active and refresh the window.
      await conn.query(
        `UPDATE ptw_permits
         SET valid_until = DATE_ADD(NOW(), INTERVAL ? HOUR),
             permit_status_id = ?
         WHERE permit_id = ?`,
        [PERMIT_VALIDITY_HOURS, PERMIT_STATUS.ACTIVE, permitId]
      );
      permitValidity = await readPermitValidity();

      const [permitNumberRows] = await conn.query(
        "SELECT permit_number FROM ptw_permits WHERE permit_id = ? LIMIT 1",
        [permitId]
      );
      try {
        await notifyHandoverRecipient(conn, {
          permitId,
          submissionId,
          submitterId,
          permitNumber: permitNumberRows[0]?.permit_number,
          answers
        });
      } catch (notifyErr) {
        if (process.env.NODE_ENV !== "test") {
          console.error("PTW handover notification failed:", notifyErr?.message || notifyErr);
        }
      }
    } else if (role === "handback") {
      // Final handback / cancellation -> close the permit forever.
      await linkClosingSubmission({ required: false });
      await conn.query(
        `UPDATE ptw_permits
         SET permit_status_id = ?, valid_until = NOW()
         WHERE permit_id = ?`,
        [PERMIT_STATUS.CLOSED, permitId]
      );
      permitValidity = await readPermitValidity();
    } else if (role === "audit") {
      // Shift audit — recorded while permit is active; no validity or status change.
    } else if (role === "closing") {
      await linkClosingSubmission({ required: true });
    }

    const [savedAnswers] = await conn.query(
      `SELECT answers_id, submission_id, question_id, is_media, answer_value
       FROM ptw_answers WHERE submission_id = ? ORDER BY answers_id`,
      [submissionId]
    );

    await conn.commit();
    res.status(201).json({
      message: "Submission created successfully",
      data: {
        submission_id: submissionId,
        permit_id: permitId,
        form_id: formId,
        submitter_id: submitterId,
        submission_role: role || null,
        permit_status_id:
          role === "handback"
            ? PERMIT_STATUS.CLOSED
            : role === "opening" || role === "handover"
              ? PERMIT_STATUS.ACTIVE
              : Number(permitRow.permit_status_id) || null,
        valid_from: permitValidity?.valid_from ?? permitRow.valid_from ?? null,
        valid_until: permitValidity?.valid_until ?? permitRow.valid_until ?? null,
        answers_count: answers.length,
        media_count: mediaCount,
        answers: savedAnswers
      }
    });
  } catch (err) {
    await conn.rollback().catch(() => {});
    next(err);
  } finally {
    conn.release();
  }
}

/**
 * GET /api/ptw/submissions/:submission_id/answers
 * Rows from ptw_answers (+ question text) and ptw_answers_media metadata for a submission.
 */
export async function getSubmissionAnswers(req, res, next) {
  try {
    const submissionId = toPositiveInt(req.params.submission_id, "submission_id");

    const [subRows] = await pool.query(
      "SELECT * FROM ptw_submissions WHERE submission_id = ? LIMIT 1",
      [submissionId]
    );
    if (!subRows.length) {
      throw new ApiError(404, "Submission not found");
    }

    const [answers] = await pool.query(
      `SELECT
         a.answers_id,
         a.submission_id,
         a.question_id,
         a.is_media,
         a.answer_value,
         q.question,
         q.question_type_id,
         qt.question_type
       FROM ptw_answers a
       LEFT JOIN ptw_questions q ON q.question_id = a.question_id
       LEFT JOIN ptw_question_type qt ON qt.question_type_id = q.question_type_id
       WHERE a.submission_id = ?
       ORDER BY a.answers_id`,
      [submissionId]
    );

    const answerIds = answers.map((a) => a.answers_id).filter(Boolean);
    let mediaByAnswer = {};
    if (answerIds.length) {
      const [mediaRows] = await pool.query(
        `SELECT answers_id, occurrence
         FROM ptw_answers_media
         WHERE answers_id IN (?)
         ORDER BY answers_id, occurrence`,
        [answerIds]
      );
      for (const row of mediaRows) {
        if (!mediaByAnswer[row.answers_id]) {
          mediaByAnswer[row.answers_id] = [];
        }
        mediaByAnswer[row.answers_id].push({
          occurrence: row.occurrence,
          file_url: `/api/ptw/ptw_answers_media/${row.answers_id}/${row.occurrence}/file`
        });
      }
    }

    const answersWithMedia = answers.map((a) => ({
      ...a,
      media: mediaByAnswer[a.answers_id] || []
    }));

    res.json({
      submission: subRows[0],
      answers: answersWithMedia
    });
  } catch (err) {
    next(err);
  }
}

/**
 * POST /api/ptw/submissions/section
 * Save one form section within a single permit submission (step-by-step workflow).
 *
 * Body: permit_id, form_id, section_id, submission_id? (omit on first section),
 *   skip_section? (risk skip), risk_orac_declaration? (JSON),
 *   answers (JSON array), section_media? (JSON: { has_video, signature_data })
 * Files: media_<question_id>, media_section_<section_id>_video|signature|selfie
 */
export async function submitFormSection(req, res, next) {
  const conn = await pool.getConnection();
  try {
    const body = req.body || {};
    const permitId = toPositiveInt(body.permit_id, "permit_id");
    const formId = toPositiveInt(body.form_id, "form_id");
    const sectionId = toPositiveInt(body.section_id, "section_id");
    const skipSection = body.skip_section === true || body.skip_section === "true" || body.skip_section === 1 || body.skip_section === "1";

    const answers = parseAnswers(body.answers);
    const riskDecl = parseJsonField(body.risk_orac_declaration, "risk_orac_declaration");

    const [permitRows] = await conn.query(
      "SELECT 1 FROM ptw_permits WHERE permit_id = ? LIMIT 1",
      [permitId]
    );
    if (!permitRows.length) throw new ApiError(404, "Permit not found");

    const [formRows] = await conn.query(
      "SELECT 1 FROM ptw_form_details WHERE form_id = ? LIMIT 1",
      [formId]
    );
    if (!formRows.length) throw new ApiError(404, "Form not found");
    if (await isPtwFormArchived(conn, formId)) {
      throw new ApiError(400, "This form version is archived; submissions must use the current form.");
    }

    const [sectionRows] = await conn.query(
      "SELECT section_id, title FROM ptw_section_details WHERE section_id = ? LIMIT 1",
      [sectionId]
    );
    if (!sectionRows.length) throw new ApiError(404, "Section not found");

    const [mapRows] = await conn.query(
      "SELECT skipable FROM ptw_form_section_map WHERE form_id = ? AND section_id = ? LIMIT 1",
      [formId, sectionId]
    );
    if (!mapRows.length) {
      throw new ApiError(400, "Section is not mapped to this form");
    }
    const sectionSkipable =
      inferSectionTypeCode(sectionRows[0]) === "risk_assessment" ||
      mapRows[0].skipable === 1 ||
      mapRows[0].skipable === true ||
      mapRows[0].skipable === "1";

    if (skipSection && !sectionSkipable) {
      throw new ApiError(400, "This section cannot be skipped for this form");
    }

    if (!skipSection) {
      for (const ans of answers) {
        if (normalizeQuestionId(ans?.question_id) == null) {
          throw new ApiError(400, "Each answer must have a valid question_id");
        }
      }
      const questionIds = [...new Set(answers.map((a) => normalizeQuestionId(a.question_id)))];
      await assertQuestionsExist(conn, questionIds);
    }

    if (skipSection && !riskDecl) {
      throw new ApiError(400, "skip_declaration is required when skipping a section");
    }

    const submitterId = await resolveSubmitterId(conn, body, req.user, permitId);
    if (submitterId == null) {
      throw new ApiError(400, "Could not resolve submitter_id for this submission");
    }
    const filesByQuestion = groupFilesByQuestion(req.files);
    const sectionMediaMap = groupSectionMedia(req.files);
    assertUploadSizes(req.files);
    const sectionMedia = sectionMediaMap.get(sectionId) || {
      video: [],
      signature: [],
      selfie: []
    };

    await conn.beginTransaction();

    let submissionId =
      body.submission_id != null && body.submission_id !== ""
        ? Number(body.submission_id)
        : null;
    if (submissionId != null) {
      if (!Number.isInteger(submissionId) || submissionId <= 0) {
        throw new ApiError(400, "submission_id must be a positive integer");
      }
      const [subRows] = await conn.query(
        "SELECT submission_id FROM ptw_submissions WHERE submission_id = ? AND permit_id = ? AND form_id = ? LIMIT 1",
        [submissionId, permitId, formId]
      );
      if (!subRows.length) {
        throw new ApiError(404, "submission_id does not match this permit and form");
      }
    } else {
      const [subResult] = await conn.query(
        "INSERT INTO ptw_submissions (permit_id, submitter_id, form_id) VALUES (?, ?, ?)",
        [permitId, submitterId, formId]
      );
      submissionId = Number(subResult.insertId);
    }

    if (await isSectionCompleted(conn, submissionId, sectionId)) {
      throw new ApiError(409, "This section was already submitted for this permit form");
    }

    let mediaCount = 0;
    const savedAnswers = [];

    if (skipSection) {
      const alreadyCompleted =
        riskDecl.already_completed === true ||
        riskDecl.already_completed === 1 ||
        riskDecl.already_completed === "1" ||
        riskDecl.already_completed === "true";
      const anchorQuestionId = await requireSectionAnchorQuestionId(conn, sectionId);
      const skipNotes = [
        alreadyCompleted
          ? `${sectionRows[0].title || "Section"} skipped — already completed elsewhere.`
          : `${sectionRows[0].title || "Section"} skipped`,
        riskDecl.orac_reference ? `Reference: ${riskDecl.orac_reference}` : null,
        riskDecl.notes ? `Notes: ${riskDecl.notes}` : null
      ]
        .filter(Boolean)
        .join(" ");

      const { answersId, mediaCount: mc } = await insertAnswerWithMedia(conn, {
        submissionId,
        questionId: anchorQuestionId,
        isMedia: 0,
        answerValue: skipNotes
      });
      savedAnswers.push({
        answers_id: answersId,
        question_id: anchorQuestionId,
        section_id: sectionId,
        skipped: true
      });
      mediaCount += mc;
    } else {
      for (const ans of answers) {
        const qid = normalizeQuestionId(ans.question_id);
        const files = filesByQuestion.get(qid) || [];
        const isMedia =
          ans.is_media ||
          files.length > 0 ||
          (Array.isArray(ans.media) && ans.media.length > 0)
            ? 1
            : 0;
        const { answersId, mediaCount: mc } = await insertAnswerWithMedia(conn, {
          submissionId,
          questionId: qid,
          isMedia,
          answerValue: ans.answer_value ?? null,
          files,
          inlineMedia: ans.media
        });
        savedAnswers.push({ answers_id: answersId, question_id: qid, section_id: sectionId });
        mediaCount += mc;
      }

      const anchorQuestionId = await requireSectionAnchorQuestionId(conn, sectionId);

      if (sectionMedia.video.length) {
        const { answersId, mediaCount: mc } = await insertAnswerWithMedia(conn, {
          submissionId,
          questionId: anchorQuestionId,
          isMedia: 1,
          answerValue: "Section video upload",
          files: sectionMedia.video
        });
        savedAnswers.push({
          answers_id: answersId,
          question_id: anchorQuestionId,
          section_id: sectionId,
          media_type: "video"
        });
        mediaCount += mc;
      }

      if (sectionMedia.signature.length) {
        const { answersId, mediaCount: mc } = await insertAnswerWithMedia(conn, {
          submissionId,
          questionId: anchorQuestionId,
          isMedia: 1,
          answerValue: "Digital signature",
          files: sectionMedia.signature
        });
        savedAnswers.push({
          answers_id: answersId,
          question_id: anchorQuestionId,
          section_id: sectionId,
          media_type: "signature"
        });
        mediaCount += mc;
      }

      const signatureInline = parseJsonField(body.signature_data, "signature_data");
      if (signatureInline && !sectionMedia.signature.length) {
        const { answersId, mediaCount: mc } = await insertAnswerWithMedia(conn, {
          submissionId,
          questionId: anchorQuestionId,
          isMedia: 1,
          answerValue: "Digital signature",
          inlineMedia: [signatureInline]
        });
        savedAnswers.push({
          answers_id: answersId,
          question_id: anchorQuestionId,
          section_id: sectionId,
          media_type: "signature"
        });
        mediaCount += mc;
      }

      if (sectionMedia.selfie.length) {
        const { answersId, mediaCount: mc } = await insertAnswerWithMedia(conn, {
          submissionId,
          questionId: anchorQuestionId,
          isMedia: 1,
          answerValue: "Authorization selfie",
          files: sectionMedia.selfie
        });
        savedAnswers.push({
          answers_id: answersId,
          question_id: anchorQuestionId,
          section_id: sectionId,
          media_type: "selfie"
        });
        mediaCount += mc;
      }
    }

    const [orderedSections] = await conn.query(
      `SELECT section_id FROM ptw_form_section_map
       WHERE form_id = ?
       ORDER BY COALESCE(section_in_form_order, 999999), section_id`,
      [formId]
    );
    const sectionOrder = orderedSections.map((r) => Number(r.section_id));
    const currentIdx = sectionOrder.indexOf(sectionId);
    const nextSectionId =
      currentIdx >= 0 && currentIdx < sectionOrder.length - 1
        ? sectionOrder[currentIdx + 1]
        : null;
    const formComplete = nextSectionId == null;

    await conn.commit();

    res.status(201).json({
      message: skipSection ? "Section skipped and saved" : "Section saved successfully",
      data: {
        submission_id: submissionId,
        permit_id: permitId,
        form_id: formId,
        section_id: sectionId,
        skipped: skipSection,
        next_section_id: nextSectionId,
        form_complete: formComplete,
        answers_count: savedAnswers.length,
        media_count: mediaCount,
        answers: savedAnswers
      }
    });
  } catch (err) {
    await conn.rollback().catch(() => {});
    next(err);
  } finally {
    conn.release();
  }
}

/** GET /api/ptw/ptw_answers_media — metadata only (no LONGBLOB payloads). */
export async function listAnswersMedia(req, res, next) {
  try {
    const filters = req.query || {};
    const whereParts = [];
    const values = [];

    Object.entries(filters).forEach(([key, value]) => {
      if (value === undefined || value === null || value === "") return;
      if (key.endsWith("_like")) {
        whereParts.push(`\`${key.slice(0, -5)}\` LIKE ?`);
        values.push(`%${value}%`);
      } else {
        whereParts.push(`\`${key}\` = ?`);
        values.push(value);
      }
    });

    let sql =
      "SELECT answers_id, occurrence FROM ptw_answers_media";
    if (whereParts.length) {
      sql += ` WHERE ${whereParts.join(" AND ")}`;
    }
    sql += " ORDER BY answers_id, occurrence";

    const [rows] = await pool.query(sql, values);
    res.json(rows);
  } catch (err) {
    next(err);
  }
}

/** GET /api/ptw/answers_media/:answers_id/:occurrence/file — serve a media blob. */
export async function serveAnswerMedia(req, res, next) {
  try {
    const answersId = toPositiveInt(req.params.answers_id, "answers_id");
    const occurrence = toPositiveInt(req.params.occurrence, "occurrence");
    const [rows] = await pool.query(
      "SELECT media FROM ptw_answers_media WHERE answers_id = ? AND occurrence = ? LIMIT 1",
      [answersId, occurrence]
    );
    if (!rows.length || !rows[0].media) {
      return res.status(404).json({ message: "Media not found" });
    }
    const buf = bufferFromDb(rows[0].media);
    if (!buf || buf.length === 0) {
      return res.status(404).json({ message: "Media not found" });
    }
    const contentType = sniffContentType(buf);
    res.set("Content-Type", contentType);
    res.set("Content-Length", String(buf.length));
    res.set("Accept-Ranges", "bytes");
    res.set("Cache-Control", "private, max-age=86400");
    res.send(buf);
  } catch (err) {
    next(err);
  }
}
