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

/**
 * Non-column fields for `POST /api/orac_form_details/`: optional location link(s) for `orac_location_forms_details`.
 * @param {object} body
 * @returns {number[]}
 */
function extractOracFormLocationIdsFromBody(body) {
  const ids = [];
  if (body?.location_ids != null) {
    const raw = Array.isArray(body.location_ids) ? body.location_ids : [body.location_ids];
    for (const x of raw) {
      const n = Number(x);
      if (Number.isInteger(n) && n > 0) ids.push(n);
    }
  }
  if (body?.location_id != null && body.location_id !== "") {
    const n = Number(body.location_id);
    if (Number.isInteger(n) && n > 0) ids.push(n);
  }
  return [...new Set(ids)];
}

function buildWhereFromParams(primaryKeys, params) {
  const whereParts = [];
  const values = [];
  primaryKeys.forEach((pk) => {
    if (!(pk in params)) {
      throw new ApiError(400, `Missing primary key param: ${pk}`);
    }
    whereParts.push(`\`${pk}\` = ?`);
    values.push(params[pk]);
  });
  return { where: whereParts.join(" AND "), values };
}

export function listRecords(tableName) {
  return async (req, res, next) => {
    try {
      const filters = req.query || {};
      const whereParts = [];
      const values = [];

      Object.entries(filters).forEach(([key, value]) => {
        if (value === undefined || value === null || value === "") {
          return;
        }

        // Support simple "like" search using *_like query params
        if (key.endsWith("_like")) {
          const column = key.slice(0, -5);
          whereParts.push(`\`${column}\` LIKE ?`);
          values.push(`%${value}%`);
        } else {
          whereParts.push(`\`${key}\` = ?`);
          values.push(value);
        }
      });

      let sql = `SELECT * FROM \`${tableName}\``;
      if (whereParts.length) {
        sql += ` WHERE ${whereParts.join(" AND ")}`;
      }

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

export function getRecordByPk(tableName, primaryKeys) {
  return async (req, res, next) => {
    try {
      const { where, values } = buildWhereFromParams(primaryKeys, req.params);
      const [rows] = await pool.query(
        `SELECT * FROM \`${tableName}\` WHERE ${where} LIMIT 1`,
        values
      );
      if (!rows.length) {
        throw new ApiError(404, `${tableName} record not found`);
      }
      res.json(rows[0]);
    } catch (err) {
      next(err);
    }
  };
}

export function createRecord(tableName) {
  return async (req, res, next) => {
    try {
      const body = req.body;
      if (!body || typeof body !== "object") {
        throw new ApiError(400, "Body must be JSON object");
      }

      if (tableName === "orac_form_details") {
        const locationIds = extractOracFormLocationIdsFromBody(body);
        const insertBody = { ...body };
        delete insertBody.location_ids;
        delete insertBody.location_id;
        const columns = Object.keys(insertBody);
        const values = Object.values(insertBody);
        if (!columns.length) {
          throw new ApiError(400, "Body cannot be empty");
        }
        const conn = await pool.getConnection();
        try {
          await conn.beginTransaction();
          const placeholders = columns.map(() => "?").join(",");
          const sql = `INSERT INTO \`orac_form_details\` (${columns
            .map((c) => `\`${c}\``)
            .join(",")}) VALUES (${placeholders})`;
          const [result] = await conn.query(sql, values);
          const newId = result.insertId;
          const formId = Number(newId);
          if (!Number.isInteger(formId) || formId <= 0) {
            throw new ApiError(500, "Could not allocate form_id");
          }
          for (const lid of locationIds) {
            await conn.query(
              "INSERT INTO `orac_location_forms_details` (`form_id`, `location_id`) VALUES (?, ?)",
              [formId, lid]
            );
          }
          await conn.commit();
          res.status(201).json({
            insertedId: formId,
            affectedRows: result.affectedRows
          });
        } catch (err) {
          await conn.rollback().catch(() => {});
          throw err;
        } finally {
          conn.release();
        }
        return;
      }

      const columns = Object.keys(body);
      const values = Object.values(body);
      if (!columns.length) {
        throw new ApiError(400, "Body cannot be empty");
      }
      const placeholders = columns.map(() => "?").join(",");
      const sql = `INSERT INTO \`${tableName}\` (${columns
        .map((c) => `\`${c}\``)
        .join(",")}) VALUES (${placeholders})`;
      const [result] = await pool.query(sql, values);
      if (tableName === "orac_section_details") {
        const pp = body.section_parent_id;
        const parentId = Number(pp);
        if (pp != null && pp !== "" && Number.isInteger(parentId) && parentId > 0) {
          await archiveOracSectionParent(pool, parentId);
        }
      }
      res.status(201).json({
        insertedId: result.insertId != null ? Number(result.insertId) : null,
        affectedRows: result.affectedRows
      });
    } catch (err) {
      next(err);
    }
  };
}

export function updateRecordByPk(tableName, primaryKeys) {
  return async (req, res, next) => {
    try {
      const body = req.body;
      if (!body || typeof body !== "object") {
        throw new ApiError(400, "Body must be JSON object");
      }
      if (tableName === "orac_form_details") {
        delete body.location_ids;
        delete body.location_id;
      }
      const columns = Object.keys(body);
      const values = Object.values(body);
      if (!columns.length) {
        throw new ApiError(400, "Body cannot be empty");
      }
      const { where, values: whereValues } = buildWhereFromParams(primaryKeys, req.params);
      if (tableName === "orac_assessments") {
        const [existing] = await pool.query(
          `SELECT 1 FROM \`${tableName}\` WHERE ${where} LIMIT 1`,
          whereValues
        );
        if (!existing.length) {
          throw new ApiError(404, `${tableName} record not found`);
        }
      }
      const setClause = columns.map((c) => `\`${c}\` = ?`).join(", ");
      const sql = `UPDATE \`${tableName}\` SET ${setClause} WHERE ${where}`;
      const [result] = await pool.query(sql, [...values, ...whereValues]);
      res.json({ affectedRows: result.affectedRows });
    } catch (err) {
      next(err);
    }
  };
}

export function deleteRecordByPk(tableName, primaryKeys) {
  return async (req, res, next) => {
    try {
      const { where, values } = buildWhereFromParams(primaryKeys, req.params);
      const sql = `DELETE FROM \`${tableName}\` WHERE ${where}`;
      const [result] = await pool.query(sql, values);
      res.json({ affectedRows: result.affectedRows });
    } catch (err) {
      next(err);
    }
  };
}

