import { pool } from "../config/db.js";
import { ApiError } from "../utils/ApiError.js";
import { archivePtwFormParent, archivePtwSectionParent } from "../utils/ptwVersioning.js";

/**
 * POST /api/ptw/form-builder
 *
 * Creates a complete dynamic form in ONE transaction:
 *   Form -> Sections -> Questions -> Options -> Section/Question map ->
 *   Form/Section map (with order) -> Location map -> Alternative (skip) sections.
 *
 * Expected body:
 * {
 *   "form": { form_type_id, job_type_id, title, description, created_by, created_date, form_parent_id? },
 *   "location_ids": [Number],
 *   "sections": [
 *     {
 *       "section_id"?: Number,          // reuse existing section instead of creating
 *       "section_parent_id"?: Number,
 *       "title": String, "created_by"?, "created_date"?,
 *       "order"?: Number,               // section_in_form_order (defaults to array index + 1)
 *       "questions": [
 *         { "question_id"?: Number, "question": String, "question_type_id": Number,
 *           "allows_media"?: 0|1, "options"?: [String] }
 *       ]
 *     }
 *   ],
 *   "alternative_sections": [
 *     { "skipable_section_index"?: Number, "other_section_index"?: Number,
 *       "skipable_section_id"?: Number,    "other_section_id"?: Number }
 *   ]
 * }
 */
export async function createFormWithChildren(req, res, next) {
  const body = req.body;
  if (!body || typeof body !== "object") {
    return next(new ApiError(400, "Body must be a JSON object"));
  }
  const formInput = body.form;
  if (!formInput || typeof formInput !== "object") {
    return next(new ApiError(400, "`form` object is required"));
  }
  if (!Array.isArray(body.sections)) {
    return next(new ApiError(400, "`sections` must be an array"));
  }

  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();

    // 1) Form
    const formCols = [
      "form_parent_id",
      "form_type_id",
      "job_type_id",
      "title",
      "description",
      "created_by",
      "created_date"
    ].filter((c) => formInput[c] !== undefined);
    if (!formCols.length) {
      throw new ApiError(400, "`form` cannot be empty");
    }
    const [formResult] = await conn.query(
      `INSERT INTO ptw_form_details (${formCols.map((c) => `\`${c}\``).join(",")})
       VALUES (${formCols.map(() => "?").join(",")})`,
      formCols.map((c) => formInput[c])
    );
    const formId = Number(formResult.insertId);
    if (!Number.isInteger(formId) || formId <= 0) {
      throw new ApiError(500, "Could not allocate form_id");
    }

    if (formInput.form_parent_id != null && formInput.form_parent_id !== "") {
      await archivePtwFormParent(conn, formInput.form_parent_id);
    }

    // 2) Location maps
    const locationIds = Array.isArray(body.location_ids) ? body.location_ids : [];
    for (const raw of locationIds) {
      const lid = Number(raw);
      if (Number.isInteger(lid) && lid > 0) {
        await conn.query(
          "INSERT INTO ptw_location_forms_details (form_id, location_id) VALUES (?, ?)",
          [formId, lid]
        );
      }
    }

    // 3) Sections + questions + options + maps
    const sectionIdByIndex = [];
    for (let i = 0; i < body.sections.length; i += 1) {
      const section = body.sections[i] || {};
      let sectionId;

      if (section.section_id != null && section.section_id !== "") {
        sectionId = Number(section.section_id);
        if (!Number.isInteger(sectionId) || sectionId <= 0) {
          throw new ApiError(400, `Invalid section_id at sections[${i}]`);
        }
      } else {
        if (section.title == null || String(section.title).trim() === "") {
          throw new ApiError(400, `sections[${i}].title is required`);
        }
        const secCols = [
          "section_parent_id",
          "title",
          "created_by",
          "created_date"
        ].filter((c) => section[c] !== undefined);
        const [secResult] = await conn.query(
          `INSERT INTO ptw_section_details (${secCols.map((c) => `\`${c}\``).join(",")})
           VALUES (${secCols.map(() => "?").join(",")})`,
          secCols.map((c) => section[c])
        );
        sectionId = Number(secResult.insertId);
        if (section.section_parent_id != null && section.section_parent_id !== "") {
          await archivePtwSectionParent(conn, section.section_parent_id);
        }
      }
      sectionIdByIndex[i] = sectionId;

      // Form/Section map with ordering
      const order = Number(section.order);
      const sectionOrder = Number.isInteger(order) && order > 0 ? order : i + 1;
      await conn.query(
        `INSERT INTO ptw_form_section_map (form_id, section_id, section_in_form_order, skipable)
         VALUES (?, ?, ?, ?)`,
        [formId, sectionId, sectionOrder, section.skipable ? 1 : 0]
      );

      // Questions
      const questions = Array.isArray(section.questions) ? section.questions : [];
      for (let q = 0; q < questions.length; q += 1) {
        const question = questions[q] || {};
        let questionId;

        if (question.question_id != null && question.question_id !== "") {
          questionId = Number(question.question_id);
          if (!Number.isInteger(questionId) || questionId <= 0) {
            throw new ApiError(400, `Invalid question_id at sections[${i}].questions[${q}]`);
          }
        } else {
          if (question.question == null || String(question.question).trim() === "") {
            throw new ApiError(400, `sections[${i}].questions[${q}].question is required`);
          }
          const [qResult] = await conn.query(
            `INSERT INTO ptw_questions (question, question_type_id, allows_media)
             VALUES (?, ?, ?)`,
            [
              question.question,
              question.question_type_id ?? null,
              question.allows_media ?? 0
            ]
          );
          questionId = Number(qResult.insertId);

          // Options
          const options = Array.isArray(question.options) ? question.options : [];
          for (const opt of options) {
            const value = typeof opt === "object" && opt !== null ? opt._options ?? opt.value : opt;
            if (value == null || String(value).trim() === "") continue;
            await conn.query(
              "INSERT INTO ptw_question_options (question_id, _options) VALUES (?, ?)",
              [questionId, value]
            );
          }
        }

        // Section/Question map
        await conn.query(
          "INSERT INTO ptw_section_question_map (section_id, question_id) VALUES (?, ?)",
          [sectionId, questionId]
        );
      }
    }

    // 4) Alternative (skip-logic) section mappings
    const alternatives = Array.isArray(body.alternative_sections)
      ? body.alternative_sections
      : [];
    for (const alt of alternatives) {
      const skipable =
        alt.skipable_section_id != null && alt.skipable_section_id !== ""
          ? Number(alt.skipable_section_id)
          : sectionIdByIndex[Number(alt.skipable_section_index)];
      const other =
        alt.other_section_id != null && alt.other_section_id !== ""
          ? Number(alt.other_section_id)
          : sectionIdByIndex[Number(alt.other_section_index)];
      if (!Number.isInteger(skipable) || skipable <= 0 || !Number.isInteger(other) || other <= 0) {
        throw new ApiError(400, "alternative_sections entries need valid section references");
      }
      await conn.query(
        "INSERT INTO ptw_alternative_sections (skipable_section_id, other_section_id) VALUES (?, ?)",
        [skipable, other]
      );
    }

    await conn.commit();
    res.status(201).json({
      message: "Form created successfully",
      data: {
        form_id: formId,
        section_ids: sectionIdByIndex,
        sections_count: sectionIdByIndex.length
      }
    });
  } catch (err) {
    await conn.rollback().catch(() => {});
    next(err);
  } finally {
    conn.release();
  }
}
