export async function saveProductMetaFromForm(
  connection,
  formData,
  reference_type,
  reference_id,
  uploadedFiles = {}
) {
  if (!formData || !reference_type || !reference_id) return;

  const metaAttributeIds = [];

  // Collect meta indexes from formData keys
  const metaIndexes = new Set();
  for (const key of formData.keys()) {
    const match = key.match(/metas\[(\d+)]/);
    if (match) {
      metaIndexes.add(parseInt(match[1]));
    }
  }

  for (const index of metaIndexes) {
    const attribute_scope =
      formData.get(`metas[${index}][attribute_scope]`) || "";
    const attribute_type =
      formData.get(`metas[${index}][attribute_type]`) || "";
    const attribute_key = formData.get(`metas[${index}][attribute_key]`) || "";
    const is_content = formData.get(`metas[${index}][is_content]`) === "true";

    let content = "";

    if (is_content) {
      content = formData.get(`metas[${index}][content]`) || "";
    } else {
      const imageField = `metas[${index}][image]`;
      const existingImage = formData.get(`metas[${index}][existing_image]`);
      const imageFileOrPath = formData.get(imageField);

      if (imageFileOrPath instanceof File && uploadedFiles[imageField]?.[0]) {
        // New file uploaded — use uploaded file path
        content = uploadedFiles[imageField][0];
      } else if (
        typeof imageFileOrPath === "string" &&
        imageFileOrPath.startsWith("/uploads/")
      ) {
        // Already uploaded image path passed directly in field
        content = imageFileOrPath;
      } else if (existingImage) {
        // Legacy fallback
        content = existingImage;
      }
    }

    const [attributeResult] = await connection.query(
      `INSERT INTO meta_attributes (attribute_scope, attribute_type, attribute_key, content)
       VALUES (?, ?, ?, ?)`,
      [attribute_scope, attribute_type, attribute_key, content]
    );

    metaAttributeIds.push(attributeResult.insertId);
  }

  // Check for existing meta
  const [existingMeta] = await connection.query(
    `SELECT id, meta_attribute_ids FROM metas WHERE reference_type = ? AND reference_id = ?`,
    [reference_type, reference_id]
  );

  if (existingMeta.length > 0) {
    const metaId = existingMeta[0].id;

    let oldIds = [];
    try {
      oldIds = globalJsonParse(existingMeta[0].meta_attribute_ids);
      if (!Array.isArray(oldIds)) oldIds = [oldIds];
    } catch {
      oldIds = [existingMeta[0].meta_attribute_ids];
    }

    if (oldIds.length > 0) {
      const placeholders = oldIds.map(() => "?").join(",");
      await connection.query(
        `DELETE FROM meta_attributes WHERE id IN (${placeholders})`,
        oldIds
      );
    }

    await connection.query(
      `UPDATE metas SET meta_attribute_ids = ? WHERE id = ?`,
      [JSON.stringify(metaAttributeIds), metaId]
    );
  } else {
    await connection.query(
      `INSERT INTO metas (reference_type, reference_id, meta_attribute_ids)
       VALUES (?, ?, ?)`,
      [reference_type, reference_id, JSON.stringify(metaAttributeIds)]
    );
  }

  return metaAttributeIds;
}

export async function deleteMetaAndAttributes(
  connection,
  reference_type,
  reference_id
) {
  if (!reference_type || !reference_id) return;

  const [metas] = await connection.query(
    `SELECT id, meta_attribute_ids FROM metas WHERE reference_type = ? AND reference_id = ?`,
    [reference_type, reference_id]
  );

  for (const meta of metas) {
    let attributeIds = [];

    try {
      attributeIds = globalJsonParse(meta.meta_attribute_ids);
      if (!Array.isArray(attributeIds)) attributeIds = [attributeIds];
    } catch {
      attributeIds = [meta.meta_attribute_ids];
    }

    if (attributeIds.length > 0) {
      const placeholders = attributeIds.map(() => "?").join(",");
      await connection.query(
        `DELETE FROM meta_attributes WHERE id IN (${placeholders})`,
        attributeIds
      );
    }

    await connection.query(`DELETE FROM metas WHERE id = ?`, [meta.id]);
  }
}
