import { pool } from "../config/db.js";
import { ApiError } from "../utils/ApiError.js";
import { attachCombinedMetadata, loadCombinedQuestionMaps } from "../utils/oracCombinedQuestionEnrichment.js";
import { sortSectionQuestionsRpnLast } from "../utils/oracSortSectionQuestionsRpnLast.js";

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

/** Read-only: first form linked to a location (MIN(form_id)). Executor JWT can use via requireAuth parent route. */
export async function getFormIdForLocation(req, res, next) {
  try {
    const locationId = toPositiveInteger(req.params.location_id, "location_id");
    const [rows] = await pool.query(
      "SELECT MIN(`form_id`) AS `form_id` FROM `orac_location_forms_details` WHERE `location_id` = ?",
      [locationId]
    );
    const raw = rows[0]?.form_id;
    const formId = raw != null && raw !== "" ? Number(raw) : null;
    return res.json({ form_id: Number.isFinite(formId) ? formId : null });
  } catch (error) {
    return next(error);
  }
}

/** Smallest `form_id` that has at least one section in `orac_form_section_map` (org default ORAC form). */
export async function getDefaultFormId(req, res, next) {
  try {
    const [rows] = await pool.query(
      "SELECT MIN(`form_id`) AS `form_id` FROM `orac_form_section_map` WHERE `form_id` IS NOT NULL"
    );
    const raw = rows[0]?.form_id;
    const formId = raw != null && raw !== "" ? Number(raw) : null;
    return res.json({ form_id: Number.isFinite(formId) && formId > 0 ? formId : null });
  } catch (error) {
    return next(error);
  }
}

export async function getFormWithSectionsAndQuestions(req, res, next) {
  try {
    const formId = toPositiveInteger(req.params.form_id, "form_id");

    const [formRows] = await pool.query(
      "SELECT * FROM `orac_form_details` WHERE `form_id` = ? LIMIT 1",
      [formId]
    );
    if (!formRows.length) {
      throw new ApiError(404, "Form not found");
    }

    const [sections] = await pool.query(
      `SELECT fsm.section_id AS section_id,
              s.section_parent_id,
              s.title,
              s.created_by,
              s.created_date
       FROM orac_form_section_map fsm
       LEFT JOIN orac_section_details s ON s.section_id = fsm.section_id
       WHERE fsm.form_id = ?
       ORDER BY fsm.section_id ASC`,
      [formId]
    );

    if (!sections.length) {
      return res.json({ ...formRows[0], sections: [] });
    }

    const sectionIds = sections.map((s) => s.section_id);

    const [questions] = await pool.query(
      `SELECT
         m.section_id,
         q.question_id,
         q.question,
         q.question_type_id,
         qt.question_type
       FROM orac_section_question_map m
       JOIN orac_questions q ON q.question_id = m.question_id
       LEFT JOIN orac_question_type qt ON qt.question_type_id = q.question_type_id
       WHERE m.section_id IN (?)
       ORDER BY m.section_id, q.question_id`,
      [sectionIds]
    );

    const maps = await loadCombinedQuestionMaps();

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

    if (questionIds.length) {
      const [options] = await pool.query(
        `SELECT question_options_id, question_id, _options
         FROM orac_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 questionsBySection = {};
    for (const q of questions) {
      if (!questionsBySection[q.section_id]) {
        questionsBySection[q.section_id] = [];
      }
      const base = {
        question_id: q.question_id,
        question: q.question,
        question_type_id: q.question_type_id,
        question_type: q.question_type,
        options: optionsMap[q.question_id] || []
      };
      questionsBySection[q.section_id].push(attachCombinedMetadata(base, maps));
    }

    const sectionsWithQuestions = sections.map((s) => ({
      ...s,
      questions: sortSectionQuestionsRpnLast(questionsBySection[s.section_id] || [])
    }));

    return res.json({
      ...formRows[0],
      sections: sectionsWithQuestions
    });
  } catch (error) {
    return next(error);
  }
}

export async function createFormSectionMap(req, res, next) {
  try {
    const formId = toPositiveInteger(req.body?.form_id, "form_id");
    const sectionId = toPositiveInteger(req.body?.section_id, "section_id");

    const [formRows] = await pool.query(
      "SELECT 1 FROM `orac_form_details` WHERE `form_id` = ? LIMIT 1",
      [formId]
    );
    if (!formRows.length) {
      throw new ApiError(404, "Form not found for provided form_id");
    }

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

    const [existingRows] = await pool.query(
      "SELECT 1 FROM `orac_form_section_map` WHERE `form_id` = ? AND `section_id` = ? LIMIT 1",
      [formId, sectionId]
    );
    if (existingRows.length) {
      throw new ApiError(409, "Section is already mapped to this form");
    }

    await pool.query(
      "INSERT INTO `orac_form_section_map` (`form_id`, `section_id`) VALUES (?, ?)",
      [formId, sectionId]
    );

    return res.status(201).json({
      message: "Section mapped to form successfully",
      data: {
        form_id: formId,
        section_id: sectionId
      }
    });
  } catch (error) {
    return next(error);
  }
}
