import { pool } from "../config/db.js";
import { ApiError } from "../utils/ApiError.js";
import { withInferredSectionType, inferSectionTypeCode } from "../utils/sectionTypeInfer.js";

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;
}

/** Section row plus questions linked via ptw_section_question_map. */
export async function getSectionWithQuestions(req, res, next) {
  try {
    const sectionId = toPositiveInt(req.params.section_id, "section_id");

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

    const sectionRow = sectionRows[0];
    const isAuditSection = inferSectionTypeCode(sectionRow) === "audit";

    const questionOrderSql = isAuditSection
      ? `ORDER BY
           CASE LOWER(REPLACE(REPLACE(TRIM(q.question), '—', '-'), '–', '-'))
             WHEN 'who' THEN 1
             WHEN 'who :' THEN 1
             WHEN 'date' THEN 2
             WHEN 'action to be implemented' THEN 3
             WHEN 'action 1 - to be implemented' THEN 3
             WHEN 'action 1 - who' THEN 4
             WHEN 'action 1 - when' THEN 5
             WHEN 'action 2 - to be implemented' THEN 6
             WHEN 'action 2 - who' THEN 7
             WHEN 'action 2 - when' THEN 8
             WHEN 'action 3 - to be implemented' THEN 9
             WHEN 'action 3 - who' THEN 10
             WHEN 'action 3 - when' THEN 11
             WHEN 'when' THEN 12
             WHEN 'when:' THEN 12
             ELSE 99
           END,
           q.question_id`
      : "ORDER BY q.question_id";

    const [questions] = await pool.query(
      `SELECT
         q.question_id,
         q.question,
         q.question_type_id,
         q.allows_media,
         qt.question_type
       FROM ptw_section_question_map m
       JOIN ptw_questions q ON q.question_id = m.question_id
       LEFT JOIN ptw_question_type qt ON qt.question_type_id = q.question_type_id
       WHERE m.section_id = ?
       ${questionOrderSql}`,
      [sectionId]
    );

    const questionIds = questions.map((q) => q.question_id);
    let optionsMap = {};

    if (questionIds.length) {
      const [options] = await pool.query(
        `SELECT question_options_id, question_id, _options
         FROM ptw_question_options
         WHERE question_id IN (?)
         ORDER BY question_options_id`,
        [questionIds]
      );
      for (const opt of options) {
        if (!optionsMap[opt.question_id]) {
          optionsMap[opt.question_id] = [];
        }
        optionsMap[opt.question_id].push({
          question_options_id: opt.question_options_id,
          option: opt._options
        });
      }
    }

    const questionsWithOptions = questions.map((q) => ({
      ...q,
      options: optionsMap[q.question_id] || []
    }));

    res.json({
      ...withInferredSectionType(sectionRow),
      questions: questionsWithOptions
    });
  } catch (err) {
    next(err);
  }
}

/** Insert into ptw_section_question_map with FK + duplicate checks. */
export async function createSectionQuestionMap(req, res, next) {
  try {
    const sectionId = toPositiveInt(req.body?.section_id, "section_id");
    const questionId = toPositiveInt(req.body?.question_id, "question_id");

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

    const [questionRows] = await pool.query(
      "SELECT 1 FROM `ptw_questions` WHERE `question_id` = ? LIMIT 1",
      [questionId]
    );
    if (!questionRows.length) {
      throw new ApiError(404, "Question not found for provided question_id");
    }

    const [existingRows] = await pool.query(
      "SELECT 1 FROM `ptw_section_question_map` WHERE `section_id` = ? AND `question_id` = ? LIMIT 1",
      [sectionId, questionId]
    );
    if (existingRows.length) {
      throw new ApiError(409, "Question is already mapped to this section");
    }

    await pool.query(
      "INSERT INTO `ptw_section_question_map` (`section_id`, `question_id`) VALUES (?, ?)",
      [sectionId, questionId]
    );

    res.status(201).json({
      message: "Question mapped to section successfully",
      section_id: sectionId,
      question_id: questionId
    });
  } catch (err) {
    next(err);
  }
}
