import { pool } from "../config/db.js";
import { ApiError } from "./ApiError.js";

/**
 * Version ordinal and lineage root for a section row (same tree rules as orac_form_details).
 * @param {import("mysql2/promise").PoolConnection | import("mysql2/promise").Pool} conn
 * @param {number} sectionId
 * @returns {Promise<{ version: number, version_root_id: number | null }>}
 */
export async function getOracSectionVersionInfo(conn, sectionId) {
  const id = Number(sectionId);
  if (!Number.isInteger(id) || id <= 0) {
    return { version: 1, version_root_id: null };
  }
  const [rows] = await conn.query(
    `WITH RECURSIVE tree AS (
      SELECT \`section_id\`, \`section_parent_id\`, \`section_id\` AS \`version_root_id\`, 1 AS \`version\`
      FROM \`orac_section_details\`
      WHERE \`section_parent_id\` IS NULL
      UNION ALL
      SELECT s.\`section_id\`, s.\`section_parent_id\`, t.\`version_root_id\`, t.\`version\` + 1
      FROM \`orac_section_details\` s
      INNER JOIN tree t ON s.\`section_parent_id\` = t.\`section_id\`
    )
    SELECT
      COALESCE(t.\`version\`, 1) AS \`version\`,
      COALESCE(t.\`version_root_id\`, sd.\`section_id\`) AS \`version_root_id\`
    FROM \`orac_section_details\` sd
    LEFT JOIN tree t ON sd.\`section_id\` = t.\`section_id\`
    WHERE sd.\`section_id\` = ?
    LIMIT 1`,
    [id]
  );
  const r = rows?.[0];
  if (!r) {
    return { version: 1, version_root_id: null };
  }
  return {
    version: Number(r.version) || 1,
    version_root_id:
      r.version_root_id != null ? Number(r.version_root_id) : null
  };
}

/**
 * Marks a section row as archived (superseded by a child version). Idempotent.
 * Stores section_version and version_root_id at archive time.
 * @param {import("mysql2/promise").PoolConnection | import("mysql2/promise").Pool} conn
 * @param {number} parentSectionId
 */
export async function archiveOracSectionParent(conn, parentSectionId) {
  const pid = Number(parentSectionId);
  if (!Number.isInteger(pid) || pid <= 0) {
    return;
  }
  const { version, version_root_id } = await getOracSectionVersionInfo(conn, pid);
  await conn.query(
    "INSERT IGNORE INTO `orac_archive_section` (`section_id`, `archived_at`, `section_version`, `version_root_id`) VALUES (?, NOW(), ?, ?)",
    [pid, version, version_root_id]
  );
}

/**
 * @param {import("mysql2/promise").PoolConnection | import("mysql2/promise").Pool} conn
 * @param {number} sectionId
 * @returns {Promise<boolean>}
 */
export async function isOracSectionArchived(conn, sectionId) {
  const id = Number(sectionId);
  if (!Number.isInteger(id) || id <= 0) {
    return false;
  }
  const [rows] = await conn.query(
    "SELECT 1 AS ok FROM `orac_archive_section` WHERE `section_id` = ? LIMIT 1",
    [id]
  );
  return Boolean(rows?.length);
}

/** Pool helper for callers without a transaction connection. */
export async function assertOracSectionNotArchived(sectionId, message) {
  const archived = await isOracSectionArchived(pool, sectionId);
  if (archived) {
    throw new ApiError(
      400,
      message ||
        "This section version is archived; use the current section for new work."
    );
  }
}
