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

function truthy(v) {
  if (v === true || v === 1) return true;
  if (typeof v === "string") {
    const s = v.trim().toLowerCase();
    return s === "1" || s === "true" || s === "yes";
  }
  return false;
}

/**
 * GET /orac_form_details
 * - Default: all rows (same as generic CRUD list).
 * - with_version=1: adds `version`, `version_root_id`, `is_archived`, `has_child_version`
 *   (superseded parent = has a newer row with this `form_id` as `form_parent_id`).
 *   Every `orac_form_details` row is returned: rows not reachable in the version tree (orphan / broken chain)
 *   get `version` = 1 and `version_root_id` = their `form_id`.
 * - leaf_only=1: only current, usable forms — not archived and not a parent of another row.
 */
export async function listOracFormDetails(req, res, next) {
  try {
    const q = req.query || {};
    const withVersion = truthy(q.with_version);
    const leafOnly = truthy(q.leaf_only);

    if (!withVersion && !leafOnly) {
      const [rows] = await pool.query("SELECT * FROM `orac_form_details`");
      return res.json(rows);
    }

    const sql = `
      WITH RECURSIVE tree AS (
        SELECT \`form_id\`, \`form_parent_id\`, \`form_id\` AS \`version_root_id\`, 1 AS \`version\`
        FROM \`orac_form_details\`
        WHERE \`form_parent_id\` IS NULL
        UNION ALL
        SELECT f.\`form_id\`, f.\`form_parent_id\`, t.\`version_root_id\`, t.\`version\` + 1
        FROM \`orac_form_details\` f
        INNER JOIN tree t ON f.\`form_parent_id\` = t.\`form_id\`
      )
      SELECT
        fd.*,
        COALESCE(t.\`version\`, 1) AS \`version\`,
        COALESCE(t.\`version_root_id\`, fd.\`form_id\`) AS \`version_root_id\`,
        EXISTS (SELECT 1 FROM \`orac_archive_form\` af WHERE af.\`form_id\` = fd.\`form_id\`) AS \`is_archived\`,
        EXISTS (
          SELECT 1 FROM \`orac_form_details\` ch
          WHERE ch.\`form_parent_id\` = fd.\`form_id\`
        ) AS \`has_child_version\`
      FROM \`orac_form_details\` fd
      LEFT JOIN tree t ON fd.\`form_id\` = t.\`form_id\`
      WHERE 1=1
      ${leafOnly
        ? `AND fd.\`form_id\` NOT IN (SELECT \`form_id\` FROM \`orac_archive_form\`)
           AND fd.\`form_id\` NOT IN (
             SELECT \`form_parent_id\` FROM \`orac_form_details\` WHERE \`form_parent_id\` IS NOT NULL
           )`
        : ""}
      ORDER BY COALESCE(t.\`version_root_id\`, fd.\`form_id\`), COALESCE(t.\`version\`, 1), fd.\`form_id\`
    `;

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