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

const LOGO_BLOCK_COUNT = 1;
const MAX_TEXT_BLOCKS = 12;
const DEFAULT_PAGE_ROW_COUNT = 3;
const DEFAULT_TEXT_BLOCK_HEIGHT = 0;
const LOGOS_PER_BLOCK = 6;
const PAGE_CONTENT_HEIGHT_PX = 461;
const MIN_ROW_HEIGHT_PX = 0;
const ROW_BORDER_PX = 1;

function parseHeightPx(value) {
  const h = Number(value);
  return Number.isFinite(h) && h >= MIN_ROW_HEIGHT_PX ? h : DEFAULT_TEXT_BLOCK_HEIGHT;
}

function defaultEmptyTextBlocks() {
  return Array.from({ length: DEFAULT_PAGE_ROW_COUNT }, (_, index) => ({
    block_index: index + 1,
    text: "",
    height_px: DEFAULT_TEXT_BLOCK_HEIGHT,
  }));
}

function defaultEmptyBlockIcons() {
  const icons = {};
  for (let i = 1; i <= DEFAULT_PAGE_ROW_COUNT; i += 1) {
    icons[String(i)] = Array(LOGOS_PER_BLOCK).fill("");
  }
  return icons;
}

function rowHeightPx(block) {
  return parseHeightPx(block?.height_px);
}

function totalContentHeightPx(blocks) {
  if (!blocks?.length) return 0;
  const rowHeights = blocks.reduce((sum, block) => sum + rowHeightPx(block), 0);
  const borders = (blocks.length - 1) * ROW_BORDER_PX;
  return rowHeights + borders;
}

function maxHeightForRow(blocks, rowIndex) {
  if (rowIndex < 0 || rowIndex >= blocks.length) return MIN_ROW_HEIGHT_PX;
  const othersHeight = blocks.reduce(
    (sum, block, index) => (index === rowIndex ? sum : sum + rowHeightPx(block)),
    0
  );
  const borders = Math.max(0, blocks.length - 1) * ROW_BORDER_PX;
  return Math.max(MIN_ROW_HEIGHT_PX, PAGE_CONTENT_HEIGHT_PX - othersHeight - borders);
}

function clampRowHeight(blocks, rowIndex, requestedHeight) {
  const maxH = maxHeightForRow(blocks, rowIndex);
  const requested = parseHeightPx(requestedHeight);
  return Math.min(maxH, requested);
}

function clampTextBlocksToPageBudget(blocks) {
  const normalized = blocks.map((block) => ({ ...block }));
  for (let i = 0; i < normalized.length; i += 1) {
    normalized[i].height_px = clampRowHeight(
      normalized,
      i,
      normalized[i].height_px
    );
  }
  return normalized;
}

let schemaReady = false;

async function nextPageId(conn) {
  const [rows] = await conn.query(
    "SELECT COALESCE(MAX(`page_id`), 0) + 1 AS next_id FROM `swi_document_page_map`"
  );
  return Number(rows[0].next_id);
}

export async function ensurePageContentSchema() {
  if (schemaReady) return;

  const [blockIdx] = await pool.query(
    "SHOW COLUMNS FROM `swi_text_blocks` LIKE 'block_index'"
  );
  if (!blockIdx.length) {
    await pool.query(
      "ALTER TABLE `swi_text_blocks` ADD COLUMN `block_index` TINYINT NOT NULL DEFAULT 1 AFTER `page_id`"
    );
  }

  const [textCol] = await pool.query(
    "SHOW COLUMNS FROM `swi_text_blocks` WHERE Field = 'text_block'"
  );
  if (textCol[0]?.Type?.includes("varchar")) {
    await pool.query(
      "ALTER TABLE `swi_text_blocks` MODIFY COLUMN `text_block` TEXT NULL"
    );
  }

  const [heightCol] = await pool.query(
    "SHOW COLUMNS FROM `swi_text_blocks` LIKE 'block_height_px'"
  );
  if (!heightCol.length) {
    await pool.query(
      "ALTER TABLE `swi_text_blocks` ADD COLUMN `block_height_px` SMALLINT UNSIGNED NULL DEFAULT NULL AFTER `text_block`"
    );
  }

  const [slotIdx] = await pool.query(
    "SHOW COLUMNS FROM `swi_images` LIKE 'slot_index'"
  );
  if (!slotIdx.length) {
    await pool.query(
      "ALTER TABLE `swi_images` ADD COLUMN `slot_index` TINYINT NOT NULL DEFAULT 1 AFTER `page_id`"
    );
  }

  const canvasColumns = [
    ["pos_x", "DECIMAL(6,2) NULL DEFAULT NULL"],
    ["pos_y", "DECIMAL(6,2) NULL DEFAULT NULL"],
    ["width", "DECIMAL(6,2) NULL DEFAULT NULL"],
    ["height", "DECIMAL(6,2) NULL DEFAULT NULL"],
    ["z_index", "SMALLINT NULL DEFAULT NULL"],
    ["label", "SMALLINT NULL DEFAULT NULL"],
  ];
  for (const [name, definition] of canvasColumns) {
    const [col] = await pool.query(
      "SHOW COLUMNS FROM `swi_images` LIKE ?",
      [name]
    );
    if (!col.length) {
      await pool.query(
        `ALTER TABLE \`swi_images\` ADD COLUMN \`${name}\` ${definition}`
      );
    }
  }

  const [logoSlotCol] = await pool.query(
    "SHOW COLUMNS FROM `swi_text_blocks` LIKE 'logo_slot_count'"
  );
  if (!logoSlotCol.length) {
    await pool.query(
      "ALTER TABLE `swi_text_blocks` ADD COLUMN `logo_slot_count` TINYINT UNSIGNED NULL DEFAULT NULL"
    );
  }

  const [picMergeCol] = await pool.query(
    "SHOW COLUMNS FROM `swi_text_blocks` LIKE 'pic_merge_next'"
  );
  if (!picMergeCol.length) {
    await pool.query(
      "ALTER TABLE `swi_text_blocks` ADD COLUMN `pic_merge_next` TINYINT(1) NOT NULL DEFAULT 0 AFTER `logo_slot_count`"
    );
  }

  const [imageBlockCol] = await pool.query(
    "SHOW COLUMNS FROM `swi_images` LIKE 'block_index'"
  );
  if (!imageBlockCol.length) {
    await pool.query(
      "ALTER TABLE `swi_images` ADD COLUMN `block_index` TINYINT UNSIGNED NULL DEFAULT NULL AFTER `slot_index`"
    );
  }

  const [imageCellSpanCol] = await pool.query(
    "SHOW COLUMNS FROM `swi_images` LIKE 'cell_row_span'"
  );
  if (!imageCellSpanCol.length) {
    await pool.query(
      "ALTER TABLE `swi_images` ADD COLUMN `cell_row_span` TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER `block_index`"
    );
  }

  const markerColumns = [
    ["marker_tips", "TINYINT(1) NOT NULL DEFAULT 0"],
    ["marker_quality", "TINYINT(1) NOT NULL DEFAULT 0"],
    ["marker_safety", "TINYINT(1) NOT NULL DEFAULT 0"],
    ["marker_visual", "TINYINT(1) NOT NULL DEFAULT 0"],
  ];
  for (const [name, definition] of markerColumns) {
    const [col] = await pool.query(
      "SHOW COLUMNS FROM `swi_document_page_map` LIKE ?",
      [name]
    );
    if (!col.length) {
      await pool.query(
        `ALTER TABLE \`swi_document_page_map\` ADD COLUMN \`${name}\` ${definition}`
      );
    }
  }

  schemaReady = true;
}

export function parsePageNum(pageLabel, fallback = 1) {
  if (pageLabel == null || pageLabel === "") return fallback;
  const m = String(pageLabel).trim().match(/^(\d+)\s*\//);
  if (m) return Number(m[1]) || fallback;
  const n = Number(pageLabel);
  return Number.isInteger(n) && n > 0 ? n : fallback;
}

function parsePageMarkers(source = {}) {
  return {
    marker_tips: parseMarkerFlag(source.marker_tips ?? source.tips),
    marker_quality: parseMarkerFlag(source.marker_quality ?? source.quality),
    marker_safety: parseMarkerFlag(source.marker_safety ?? source.safety),
    marker_visual: parseMarkerFlag(source.marker_visual ?? source.visual),
  };
}

function dataUrlToBuffer(dataUrl) {
  if (!dataUrl || typeof dataUrl !== "string") return null;
  const m = dataUrl.match(/^data:[^;]+;base64,(.+)$/);
  if (!m) return null;
  return Buffer.from(m[1], "base64");
}

export function bufferToDataUrl(buf, mime = "image/png") {
  if (!buf) return null;
  const b = Buffer.isBuffer(buf) ? buf : Buffer.from(buf);
  return `data:${mime};base64,${b.toString("base64")}`;
}

function parsePct(value, fallback) {
  const n = Number(value);
  return Number.isFinite(n) ? n : fallback;
}

/** Default 2-column grid position (percentages) for legacy images lacking geometry. */
function defaultCanvasPosition(index, total) {
  const cols = 2;
  const rows = Math.max(1, Math.ceil(Math.max(1, total) / cols));
  const col = index % cols;
  const row = Math.floor(index / cols);
  const cellW = 100 / cols;
  const cellH = 100 / rows;
  const pad = 2;
  return {
    x_pct: col * cellW + pad,
    y_pct: row * cellH + pad,
    w_pct: cellW - pad * 2,
    h_pct: cellH - pad * 2,
  };
}

/** Convert the legacy flat image_slots map into positioned canvas items. */
function canvasItemsFromImageSlots(imageSlots) {
  if (!imageSlots || typeof imageSlots !== "object") return [];
  const entries = Object.entries(imageSlots)
    .filter(([, dataUrl]) => Boolean(dataUrl))
    .map(([slot, dataUrl]) => ({ slot: Number(slot), dataUrl }))
    .sort((a, b) => a.slot - b.slot);
  return entries.map((entry, index) => ({
    image_id: null,
    image_data_url: entry.dataUrl,
    ...defaultCanvasPosition(index, entries.length),
    z_index: index + 1,
    label: index + 1,
  }));
}

/** Normalize incoming canvas items from a save payload (or legacy image_slots fallback). */
function collectCanvasItemsFromForm(form) {
  if (Array.isArray(form?.picture_canvas)) {
    return form.picture_canvas
      .map((item, index) => ({
        image_id:
          item?.image_id != null &&
          Number.isInteger(Number(item.image_id)) &&
          Number(item.image_id) > 0
            ? Number(item.image_id)
            : null,
        image_data_url: item?.image_data_url || null,
        x_pct: parsePct(item?.x_pct, 2),
        y_pct: parsePct(item?.y_pct, 2),
        w_pct: parsePct(item?.w_pct, 45),
        h_pct: parsePct(item?.h_pct, 30),
        z_index: Number.isFinite(Number(item?.z_index))
          ? Number(item.z_index)
          : index + 1,
        label: Number.isFinite(Number(item?.label))
          ? Number(item.label)
          : index + 1,
        block_index:
          item?.row_index != null || item?.block_index != null
            ? Number(item.row_index ?? item.block_index)
            : null,
        cell_row_span:
          Number.isFinite(Number(item?.cell_row_span)) && Number(item.cell_row_span) > 0
            ? Math.trunc(Number(item.cell_row_span))
            : 1,
      }))
      .filter((item) => item.image_data_url || item.image_id);
  }
  return canvasItemsFromImageSlots(form?.image_slots);
}

export async function listIconsForSelect() {
  await ensurePageContentSchema();
  const [rows] = await pool.query(
    "SELECT `icon_id`, `icon` FROM `swi_icons` ORDER BY `icon_id` ASC"
  );
  return rows.map((row) => ({
    icon_id: row.icon_id,
    icon_data_url: bufferToDataUrl(row.icon),
  }));
}

export async function getOrCreatePage(documentId, pageNum, pageHeader = null) {
  await ensurePageContentSchema();
  const [existing] = await pool.query(
    `SELECT page_id, page_num, page_header
     FROM \`swi_document_page_map\`
     WHERE document_id = ? AND page_num = ?
     LIMIT 1`,
    [documentId, pageNum]
  );
  if (existing.length) {
    return existing[0];
  }

  const pageId = await nextPageId(pool);
  await pool.query(
    `INSERT INTO \`swi_document_page_map\` (document_id, page_id, page_num, page_header, marker_tips, marker_quality, marker_safety, marker_visual)
     VALUES (?, ?, ?, ?, 0, 0, 0, 0)`,
    [documentId, pageId, pageNum, pageHeader]
  );
  return {
    page_id: pageId,
    page_num: pageNum,
    page_header: pageHeader,
  };
}

export async function loadPageContent(pageId) {
  await ensurePageContentSchema();

  const [pageRows] = await pool.query(
    `SELECT marker_tips, marker_quality, marker_safety, marker_visual
     FROM \`swi_document_page_map\`
     WHERE page_id = ?
     LIMIT 1`,
    [pageId]
  );
  const pageMarkers = parsePageMarkers(pageRows[0] || {});

  const [blocks] = await pool.query(
    `SELECT tb.text_block_id, tb.block_index, tb.text_block, tb.block_height_px, tb.logo_slot_count, tb.pic_merge_next, tbi.icon_id
     FROM \`swi_text_blocks\` tb
     LEFT JOIN \`swi_text_block_icons\` tbi ON tbi.text_block_id = tb.text_block_id
     WHERE tb.page_id = ?
     ORDER BY tb.block_index ASC, tb.text_block_id ASC`,
    [pageId]
  );

  const [images] = await pool.query(
    `SELECT image_id, slot_index, block_index, cell_row_span, image, pos_x, pos_y, width, height, z_index, label
     FROM \`swi_images\`
     WHERE page_id = ?
     ORDER BY z_index ASC, slot_index ASC, image_id ASC`,
    [pageId]
  );

  const formPatch = {
    block1: "",
    block2: "",
    block3: "",
    block1_icon_id: "",
    block2_icon_id: "",
    block3_icon_id: "",
    block_icons: {
      "1": Array(LOGOS_PER_BLOCK).fill(""),
    },
    image_slots: {},
    picture_canvas: [],
    text_block_ids: {},
    text_blocks: [],
    image_ids: {},
    block_logo_counts: {},
    picture_merge_next: {},
    marker_tips: Boolean(pageMarkers.marker_tips),
    marker_quality: Boolean(pageMarkers.marker_quality),
    marker_safety: Boolean(pageMarkers.marker_safety),
    marker_visual: Boolean(pageMarkers.marker_visual),
    page_markers: {
      tips: Boolean(pageMarkers.marker_tips),
      quality: Boolean(pageMarkers.marker_quality),
      safety: Boolean(pageMarkers.marker_safety),
      visual: Boolean(pageMarkers.marker_visual),
    },
  };

  const blockRows = new Map();

  for (const row of blocks) {
    const idx = Number(row.block_index);
    if (idx < 1) continue;
    const key = String(idx);
    if (!blockRows.has(key)) {
      blockRows.set(key, {
        text: row.text_block || "",
        text_block_id: row.text_block_id,
        height_px:
          row.block_height_px != null
            ? parseHeightPx(row.block_height_px)
            : DEFAULT_TEXT_BLOCK_HEIGHT,
        logo_slot_count: parseLogoSlotCount(row.logo_slot_count),
        pic_merge_next: Boolean(Number(row.pic_merge_next)),
        icon_ids: [],
      });
    }
    if (row.icon_id != null) {
      blockRows.get(key).icon_ids.push(String(row.icon_id));
    }
  }

  const sortedKeys = [...blockRows.keys()]
    .map((key) => Number(key))
    .sort((a, b) => a - b);

  for (const key of sortedKeys) {
    const data = blockRows.get(String(key));
    formPatch.text_blocks.push({
      block_index: key,
      text: data.text,
      height_px: data.height_px,
      text_block_id: data.text_block_id,
      pic_merge_next: Boolean(data.pic_merge_next),
    });
    formPatch.picture_merge_next = formPatch.picture_merge_next || {};
    formPatch.picture_merge_next[String(key)] = Boolean(data.pic_merge_next);

    if (!formPatch.block_icons[String(key)]) {
      formPatch.block_icons[String(key)] = Array(LOGOS_PER_BLOCK).fill("");
    }
    formPatch.text_block_ids[String(key)] = data.text_block_id;
    if (data.logo_slot_count != null) {
      formPatch.block_logo_counts[String(key)] = data.logo_slot_count;
    }
    data.icon_ids.slice(0, LOGOS_PER_BLOCK).forEach((iconId, slot) => {
      formPatch.block_icons[String(key)][slot] = iconId;
    });

    if (key <= LOGO_BLOCK_COUNT) {
      formPatch[`block${key}`] = data.text;
      if (data.icon_ids[0]) {
        formPatch[`block${key}_icon_id`] = data.icon_ids[0];
      }
    }
  }

  if (!formPatch.text_blocks.length) {
    formPatch.text_blocks = defaultEmptyTextBlocks();
  }

  const imageRows = images.filter((row) => row.image != null);
  imageRows.forEach((row, index) => {
    const slot = Number(row.slot_index);
    if (slot >= 1) {
      const key = String(slot - 1);
      formPatch.image_slots[key] = bufferToDataUrl(row.image);
      formPatch.image_ids[key] = row.image_id;
    }

    const hasGeom =
      row.pos_x != null &&
      row.pos_y != null &&
      row.width != null &&
      row.height != null;
    const geom = hasGeom
      ? {
          x_pct: Number(row.pos_x),
          y_pct: Number(row.pos_y),
          w_pct: Number(row.width),
          h_pct: Number(row.height),
        }
      : defaultCanvasPosition(index, imageRows.length);

    formPatch.picture_canvas.push({
      id: String(row.image_id),
      image_id: row.image_id,
      image_data_url: bufferToDataUrl(row.image),
      x_pct: geom.x_pct,
      y_pct: geom.y_pct,
      w_pct: geom.w_pct,
      h_pct: geom.h_pct,
      z_index: row.z_index != null ? Number(row.z_index) : index + 1,
      label: row.label != null ? Number(row.label) : index + 1,
      row_index: row.block_index != null ? Number(row.block_index) : undefined,
      cell_row_span:
        Number.isFinite(Number(row.cell_row_span)) && Number(row.cell_row_span) > 0
          ? Number(row.cell_row_span)
          : 1,
    });
  });

  return formPatch;
}

function parseLogoSlotCount(value) {
  const n = Number(value);
  if (!Number.isFinite(n)) return null;
  const truncated = Math.trunc(n);
  if (truncated < 1) return null;
  return Math.min(LOGOS_PER_BLOCK, truncated);
}

function parseMarkerFlag(value) {
  if (value === true || value === 1 || value === "1") return 1;
  return 0;
}

async function upsertTextBlock(
  conn,
  pageId,
  blockIndex,
  text,
  heightPx,
  logoSlotCount,
  picMergeNext = false
) {
  const height = parseHeightPx(heightPx);
  const slotCount = parseLogoSlotCount(logoSlotCount);
  const mergeNext = picMergeNext ? 1 : 0;

  const [existing] = await conn.query(
    `SELECT text_block_id FROM \`swi_text_blocks\`
     WHERE page_id = ? AND block_index = ?
     LIMIT 1`,
    [pageId, blockIndex]
  );

  if (existing.length) {
    await conn.query(
      "UPDATE `swi_text_blocks` SET `text_block` = ?, `block_height_px` = ?, `logo_slot_count` = ?, `pic_merge_next` = ? WHERE `text_block_id` = ?",
      [
        text ?? "",
        height,
        slotCount,
        mergeNext,
        existing[0].text_block_id,
      ]
    );
    return existing[0].text_block_id;
  }

  const [result] = await conn.query(
    `INSERT INTO \`swi_text_blocks\` (page_id, block_index, text_block, block_height_px, logo_slot_count, pic_merge_next)
     VALUES (?, ?, ?, ?, ?, ?)`,
    [
      pageId,
      blockIndex,
      text ?? "",
      height,
      slotCount,
      mergeNext,
    ]
  );
  return result.insertId;
}

function collectTextBlocksFromForm(form) {
  let blocks;
  if (Array.isArray(form?.text_blocks) && form.text_blocks.length) {
    blocks = form.text_blocks
      .map((block, index) => ({
        block_index: Number(block.block_index) || index + 1,
        text: block.text ?? "",
        height_px: parseHeightPx(block.height_px),
        pic_merge_next: Boolean(
          block.pic_merge_next ??
            form?.picture_merge_next?.[String(Number(block.block_index) || index + 1)]
        ),
      }))
      .slice(0, MAX_TEXT_BLOCKS)
      .map((block, index) => ({ ...block, block_index: index + 1 }));
  } else {
    blocks = [];
    for (let i = 1; i <= LOGO_BLOCK_COUNT; i += 1) {
      blocks.push({
        block_index: i,
        text: form?.[`block${i}`] ?? "",
        height_px: DEFAULT_TEXT_BLOCK_HEIGHT,
      });
    }
  }

  return clampTextBlocksToPageBudget(blocks);
}

async function syncTextBlocks(conn, pageId, form) {
  const blocks = collectTextBlocksFromForm(form);
  const keepIndices = new Set(blocks.map((block) => block.block_index));

  const [existingRows] = await conn.query(
    `SELECT text_block_id, block_index FROM \`swi_text_blocks\` WHERE page_id = ?`,
    [pageId]
  );

  for (const row of existingRows) {
    if (!keepIndices.has(Number(row.block_index))) {
      await conn.query(
        "DELETE FROM `swi_text_block_icons` WHERE `text_block_id` = ?",
        [row.text_block_id]
      );
      await conn.query(
        "DELETE FROM `swi_text_blocks` WHERE `text_block_id` = ?",
        [row.text_block_id]
      );
    }
  }

  for (const block of blocks) {
    const textBlockId = await upsertTextBlock(
      conn,
      pageId,
      block.block_index,
      block.text,
      block.height_px,
      logoSlotCountForBlock(form, block.block_index),
      block.pic_merge_next
    );
    await setTextBlockIcons(
      conn,
      textBlockId,
      iconIdsForBlock(form, block.block_index)
    );
  }
}

function logoSlotCountForBlock(form, blockIndex) {
  const counts = form?.block_logo_counts;
  if (!counts || typeof counts !== "object") return null;
  const raw = counts[String(blockIndex)] ?? counts[blockIndex];
  return raw == null ? null : raw;
}

function iconIdForBlock(form, blockIndex) {
  const byBlockKey = form?.[`block${blockIndex}_icon_id`];
  const byLogoKey =
    blockIndex === 1
      ? form?.logo_icon_id
      : form?.[`logo${blockIndex}_icon_id`];
  return byLogoKey ?? byBlockKey ?? null;
}

function iconIdsForBlock(form, blockIndex) {
  const key = String(blockIndex);
  const fromBlockIcons = form?.block_icons?.[key];
  if (Array.isArray(fromBlockIcons)) {
    return fromBlockIcons.filter(
      (id) => id != null && id !== "" && Number(id) > 0
    );
  }

  const single = iconIdForBlock(form, blockIndex);
  return single ? [single] : [];
}

async function setTextBlockIcons(conn, textBlockId, iconIds) {
  await conn.query(
    "DELETE FROM `swi_text_block_icons` WHERE `text_block_id` = ?",
    [textBlockId]
  );

  const ids = Array.isArray(iconIds) ? iconIds : [iconIds];
  for (const iconId of ids) {
    if (iconId == null || iconId === "" || Number(iconId) <= 0) continue;
    await conn.query(
      `INSERT INTO \`swi_text_block_icons\` (text_block_id, icon_id)
       VALUES (?, ?)`,
      [textBlockId, Number(iconId)]
    );
  }
}

async function syncPageImages(conn, pageId, form) {
  const items = collectCanvasItemsFromForm(form);
  const keepIds = new Set();

  for (let i = 0; i < items.length; i += 1) {
    const item = items[i];
    const buffer = dataUrlToBuffer(item.image_data_url);
    const slotIndex = i + 1;
    let imageId = item.image_id;

    if (imageId) {
      const [owned] = await conn.query(
        "SELECT image_id FROM `swi_images` WHERE image_id = ? AND page_id = ? LIMIT 1",
        [imageId, pageId]
      );
      if (!owned.length) imageId = null;
    }

    if (imageId) {
      if (buffer) {
        await conn.query(
          "UPDATE `swi_images` SET `image` = ?, `slot_index` = ?, `block_index` = ?, `cell_row_span` = ?, `pos_x` = ?, `pos_y` = ?, `width` = ?, `height` = ?, `z_index` = ?, `label` = ? WHERE `image_id` = ?",
          [
            buffer,
            slotIndex,
            item.block_index,
            item.cell_row_span,
            item.x_pct,
            item.y_pct,
            item.w_pct,
            item.h_pct,
            item.z_index,
            item.label,
            imageId,
          ]
        );
      } else {
        await conn.query(
          "UPDATE `swi_images` SET `slot_index` = ?, `block_index` = ?, `cell_row_span` = ?, `pos_x` = ?, `pos_y` = ?, `width` = ?, `height` = ?, `z_index` = ?, `label` = ? WHERE `image_id` = ?",
          [
            slotIndex,
            item.block_index,
            item.cell_row_span,
            item.x_pct,
            item.y_pct,
            item.w_pct,
            item.h_pct,
            item.z_index,
            item.label,
            imageId,
          ]
        );
      }
      keepIds.add(imageId);
    } else if (buffer) {
      const [result] = await conn.query(
        `INSERT INTO \`swi_images\` (page_id, slot_index, block_index, cell_row_span, image, pos_x, pos_y, width, height, z_index, label)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          pageId,
          slotIndex,
          item.block_index,
          item.cell_row_span,
          buffer,
          item.x_pct,
          item.y_pct,
          item.w_pct,
          item.h_pct,
          item.z_index,
          item.label,
        ]
      );
      keepIds.add(result.insertId);
    }
  }

  const [allRows] = await conn.query(
    "SELECT image_id FROM `swi_images` WHERE page_id = ?",
    [pageId]
  );
  for (const row of allRows) {
    if (!keepIds.has(row.image_id)) {
      await conn.query("DELETE FROM `swi_images` WHERE `image_id` = ?", [
        row.image_id,
      ]);
    }
  }
}

/**
 * Persist text blocks, block icons, and page images for one document page.
 * @param {number} documentId
 * @param {object} form — user form payload
 */
export async function savePageContent(documentId, form) {
  await ensurePageContentSchema();
  const pageNum = parsePageNum(form?.page, 1);
  const pageIdFromForm =
    form?.page_id != null && form.page_id !== ""
      ? Number(form.page_id)
      : null;
  const pageHeader =
    form?.materials_label != null
      ? String(form.materials_label).slice(0, 255)
      : null;
  const pageMarkers = parsePageMarkers(form?.page_markers || form);

  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();

    let pageId = null;

    if (pageIdFromForm && Number.isInteger(pageIdFromForm) && pageIdFromForm > 0) {
      const [byId] = await conn.query(
        `SELECT page_id, page_num, page_header
         FROM \`swi_document_page_map\`
         WHERE page_id = ? AND document_id = ?
         LIMIT 1`,
        [pageIdFromForm, documentId]
      );
      if (byId.length) {
        pageId = byId[0].page_id;
        await conn.query(
          `UPDATE \`swi_document_page_map\`
           SET \`page_num\` = ?, \`page_header\` = COALESCE(?, \`page_header\`),
               \`marker_tips\` = ?, \`marker_quality\` = ?, \`marker_safety\` = ?, \`marker_visual\` = ?
           WHERE \`page_id\` = ?`,
          [
            pageNum,
            pageHeader,
            pageMarkers.marker_tips,
            pageMarkers.marker_quality,
            pageMarkers.marker_safety,
            pageMarkers.marker_visual,
            pageId,
          ]
        );
      }
    }

    if (!pageId) {
      const [existingPage] = await conn.query(
        `SELECT page_id, page_num, page_header
         FROM \`swi_document_page_map\`
         WHERE document_id = ? AND page_num = ?
         LIMIT 1`,
        [documentId, pageNum]
      );

      if (existingPage.length) {
        pageId = existingPage[0].page_id;
        if (pageHeader != null) {
          await conn.query(
            "UPDATE `swi_document_page_map` SET `page_header` = ?, `marker_tips` = ?, `marker_quality` = ?, `marker_safety` = ?, `marker_visual` = ? WHERE `page_id` = ?",
            [
              pageHeader,
              pageMarkers.marker_tips,
              pageMarkers.marker_quality,
              pageMarkers.marker_safety,
              pageMarkers.marker_visual,
              pageId,
            ]
          );
        } else {
          await conn.query(
            "UPDATE `swi_document_page_map` SET `marker_tips` = ?, `marker_quality` = ?, `marker_safety` = ?, `marker_visual` = ? WHERE `page_id` = ?",
            [
              pageMarkers.marker_tips,
              pageMarkers.marker_quality,
              pageMarkers.marker_safety,
              pageMarkers.marker_visual,
              pageId,
            ]
          );
        }
      } else {
        const newPageId = await nextPageId(conn);
        await conn.query(
          `INSERT INTO \`swi_document_page_map\` (document_id, page_id, page_num, page_header, marker_tips, marker_quality, marker_safety, marker_visual)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            documentId,
            newPageId,
            pageNum,
            pageHeader,
            pageMarkers.marker_tips,
            pageMarkers.marker_quality,
            pageMarkers.marker_safety,
            pageMarkers.marker_visual,
          ]
        );
        pageId = newPageId;
      }
    }

    await syncTextBlocks(conn, pageId, form);

    await syncPageImages(conn, pageId, form);

    await conn.commit();
    return { page_id: pageId, page_num: pageNum };
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

export async function loadAllPagesContentForDocument(documentId) {
  await ensurePageContentSchema();
  const [pages] = await pool.query(
    `SELECT page_id, page_num, page_header, marker_tips, marker_quality, marker_safety, marker_visual
     FROM \`swi_document_page_map\`
     WHERE document_id = ?
     ORDER BY page_num ASC, page_id ASC`,
    [documentId]
  );

  const out = [];
  for (const page of pages) {
    const content = await loadPageContent(page.page_id);
    out.push({
      page_id: page.page_id,
      page_num: page.page_num,
      page_header: page.page_header,
      marker_tips: Boolean(Number(page.marker_tips)),
      marker_quality: Boolean(Number(page.marker_quality)),
      marker_safety: Boolean(Number(page.marker_safety)),
      marker_visual: Boolean(Number(page.marker_visual)),
      page_markers: {
        tips: Boolean(Number(page.marker_tips)),
        quality: Boolean(Number(page.marker_quality)),
        safety: Boolean(Number(page.marker_safety)),
        visual: Boolean(Number(page.marker_visual)),
      },
      blocks: (content.text_blocks || []).map((block) => {
        const key = String(block.block_index);
        return {
          block_index: block.block_index,
          text_block_id: block.text_block_id ?? content.text_block_ids?.[key] ?? null,
          text: block.text ?? "",
          height_px: parseHeightPx(block.height_px),
          pic_merge_next: Boolean(block.pic_merge_next),
          logo_slot_count: content.block_logo_counts?.[key] ?? null,
          icon_id: content[`block${block.block_index}_icon_id`] || null,
          icon_ids: content.block_icons?.[key]?.filter(Boolean) || [],
        };
      }),
      block_logo_counts: content.block_logo_counts || {},
      images: Object.entries(content.image_slots || {}).map(([slot, dataUrl]) => ({
        slot_index: Number(slot) + 1,
        image_id: content.image_ids?.[slot] ?? null,
        image_data_url: dataUrl,
      })),
      picture_canvas: content.picture_canvas || [],
    });
  }
  return out;
}

export function emptyPageContent(pageNum = 1) {
  return {
    page_id: null,
    page_num: pageNum,
    page_header: "",
    materials_label: "",
    block1: "",
    block1_icon_id: "",
    text_blocks: defaultEmptyTextBlocks(),
    marker_tips: false,
    marker_quality: false,
    marker_safety: false,
    marker_visual: false,
    page_markers: {
      tips: false,
      quality: false,
      safety: false,
      visual: false,
    },
    block_icons: defaultEmptyBlockIcons(),
    image_slots: {},
    picture_canvas: [],
  };
}

export async function listDocumentPages(documentId) {
  await ensurePageContentSchema();
  const [rows] = await pool.query(
    `SELECT page_id, page_num, page_header
     FROM \`swi_document_page_map\`
     WHERE document_id = ?
     ORDER BY page_num ASC, page_id ASC`,
    [documentId]
  );
  return rows;
}

export async function createDocumentPage(documentId, documentNumber) {
  await ensurePageContentSchema();
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();

    const [maxRows] = await conn.query(
      `SELECT COALESCE(MAX(page_num), 0) AS max_num
       FROM \`swi_document_page_map\`
       WHERE document_id = ?`,
      [documentId]
    );
    const nextNum = Number(maxRows[0]?.max_num || 0) + 1;
    const newPageId = await nextPageId(conn);

    await conn.query(
      `INSERT INTO \`swi_document_page_map\` (document_id, page_id, page_num, page_header, marker_tips, marker_quality, marker_safety, marker_visual)
       VALUES (?, ?, ?, ?, 0, 0, 0, 0)`,
      [documentId, newPageId, nextNum, ""]
    );

    await conn.query(
      `UPDATE \`swi_document\`
       SET page_count = GREATEST(COALESCE(page_count, 1), ?)
       WHERE document_id = ? AND document_number = ?`,
      [nextNum, documentId, documentNumber]
    );

    await conn.commit();
    return {
      page_id: newPageId,
      page_num: nextNum,
      page_header: "",
    };
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

async function deletePageContent(conn, pageId) {
  const [blocks] = await conn.query(
    `SELECT text_block_id FROM \`swi_text_blocks\` WHERE page_id = ?`,
    [pageId]
  );

  for (const block of blocks) {
    await conn.query(
      "DELETE FROM `swi_text_block_icons` WHERE `text_block_id` = ?",
      [block.text_block_id]
    );
  }

  await conn.query("DELETE FROM `swi_text_blocks` WHERE page_id = ?", [pageId]);
  await conn.query("DELETE FROM `swi_images` WHERE page_id = ?", [pageId]);
}

export async function deleteDocumentPage(documentId, documentNumber, pageId) {
  await ensurePageContentSchema();
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();

    const [pageRows] = await conn.query(
      `SELECT page_id, page_num
       FROM \`swi_document_page_map\`
       WHERE document_id = ? AND page_id = ?
       LIMIT 1`,
      [documentId, pageId]
    );

    if (!pageRows.length) {
      const err = new Error("Page not found");
      err.code = "PAGE_NOT_FOUND";
      throw err;
    }

    const [countRows] = await conn.query(
      `SELECT COUNT(*) AS cnt FROM \`swi_document_page_map\` WHERE document_id = ?`,
      [documentId]
    );
    if (Number(countRows[0]?.cnt || 0) <= 1) {
      const err = new Error("Cannot delete the only page in a document");
      err.code = "LAST_PAGE";
      throw err;
    }

    const deletedPageNum = Number(pageRows[0].page_num);
    await deletePageContent(conn, pageId);
    await conn.query("DELETE FROM `swi_document_page_map` WHERE page_id = ?", [
      pageId,
    ]);

    await conn.query(
      `UPDATE \`swi_document_page_map\`
       SET page_num = page_num - 1
       WHERE document_id = ? AND page_num > ?`,
      [documentId, deletedPageNum]
    );

    const [maxRows] = await conn.query(
      `SELECT COALESCE(MAX(page_num), 1) AS max_num
       FROM \`swi_document_page_map\`
       WHERE document_id = ?`,
      [documentId]
    );
    const newCount = Number(maxRows[0]?.max_num || 1);

    await conn.query(
      `UPDATE \`swi_document\`
       SET page_count = ?
       WHERE document_id = ? AND document_number = ?`,
      [newCount, documentId, documentNumber]
    );

    await conn.commit();
    return { deleted_page_id: pageId, page_count: newCount };
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

export async function resolvePageIdForDocument(documentId, pageNum = 1) {
  await ensurePageContentSchema();
  const [rows] = await pool.query(
    `SELECT page_id FROM \`swi_document_page_map\`
     WHERE document_id = ? AND page_num = ?
     LIMIT 1`,
    [documentId, pageNum]
  );
  return rows[0]?.page_id ?? null;
}

export async function loadPageContentForDocument(documentId, pageNum = 1) {
  await ensurePageContentSchema();
  const [pageRows] = await pool.query(
    `SELECT page_id, page_num, page_header, marker_tips, marker_quality, marker_safety, marker_visual
     FROM \`swi_document_page_map\`
     WHERE document_id = ? AND page_num = ?
     LIMIT 1`,
    [documentId, pageNum]
  );

  if (!pageRows.length) {
    return emptyPageContent(pageNum);
  }

  const pageRow = pageRows[0];
  const content = await loadPageContent(pageRow.page_id);
  const header = pageRow.page_header || "";

  return {
    page_id: pageRow.page_id,
    page_num: pageRow.page_num,
    page_header: header,
    materials_label: header,
    marker_tips: Boolean(Number(pageRow.marker_tips)),
    marker_quality: Boolean(Number(pageRow.marker_quality)),
    marker_safety: Boolean(Number(pageRow.marker_safety)),
    marker_visual: Boolean(Number(pageRow.marker_visual)),
    page_markers: {
      tips: Boolean(Number(pageRow.marker_tips)),
      quality: Boolean(Number(pageRow.marker_quality)),
      safety: Boolean(Number(pageRow.marker_safety)),
      visual: Boolean(Number(pageRow.marker_visual)),
    },
    ...content,
  };
}

async function clonePageContent(conn, sourcePageId, targetPageId) {
  const [blocks] = await conn.query(
    `SELECT text_block_id, block_index, text_block, block_height_px, logo_slot_count
     FROM \`swi_text_blocks\`
     WHERE page_id = ?
     ORDER BY block_index ASC, text_block_id ASC`,
    [sourcePageId]
  );

  for (const block of blocks) {
    const [ins] = await conn.query(
      `INSERT INTO \`swi_text_blocks\` (page_id, block_index, text_block, block_height_px, logo_slot_count)
       VALUES (?, ?, ?, ?, ?)`,
      [
        targetPageId,
        block.block_index,
        block.text_block ?? "",
        block.block_height_px != null
          ? parseHeightPx(block.block_height_px)
          : DEFAULT_TEXT_BLOCK_HEIGHT,
        parseLogoSlotCount(block.logo_slot_count),
      ]
    );
    const newBlockId = ins.insertId;
    const [icons] = await conn.query(
      `SELECT icon_id FROM \`swi_text_block_icons\` WHERE text_block_id = ?`,
      [block.text_block_id]
    );
    for (const iconRow of icons) {
      await conn.query(
        `INSERT INTO \`swi_text_block_icons\` (text_block_id, icon_id)
         VALUES (?, ?)`,
        [newBlockId, iconRow.icon_id]
      );
    }
  }

  const [images] = await conn.query(
    `SELECT slot_index, block_index, cell_row_span, image, pos_x, pos_y, width, height, z_index, label
     FROM \`swi_images\` WHERE page_id = ?`,
    [sourcePageId]
  );
  for (const img of images) {
    await conn.query(
      `INSERT INTO \`swi_images\` (page_id, slot_index, block_index, cell_row_span, image, pos_x, pos_y, width, height, z_index, label)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        targetPageId,
        img.slot_index,
        img.block_index,
        Number.isFinite(Number(img.cell_row_span)) && Number(img.cell_row_span) > 0
          ? Number(img.cell_row_span)
          : 1,
        img.image,
        img.pos_x,
        img.pos_y,
        img.width,
        img.height,
        img.z_index,
        img.label,
      ]
    );
  }
}

/**
 * Deep-copy a saved page into another document (new page_id, next page_num).
 * @param {number} sourcePageId
 * @param {number} targetDocumentId
 * @param {number} targetDocumentNumber
 */
export async function copyPageToDocument(
  sourcePageId,
  targetDocumentId,
  targetDocumentNumber
) {
  await ensurePageContentSchema();
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();

    const [sourceRows] = await conn.query(
      `SELECT page_id, page_header, marker_tips, marker_quality, marker_safety, marker_visual
       FROM \`swi_document_page_map\`
       WHERE page_id = ?
       LIMIT 1`,
      [sourcePageId]
    );
    if (!sourceRows.length) {
      throw new Error("Source page not found");
    }
    const sourceHeader = sourceRows[0].page_header || "";
    const sourceMarkers = parsePageMarkers(sourceRows[0]);

    const [maxRows] = await conn.query(
      `SELECT COALESCE(MAX(page_num), 0) AS max_num
       FROM \`swi_document_page_map\`
       WHERE document_id = ?`,
      [targetDocumentId]
    );
    const nextNum = Number(maxRows[0]?.max_num || 0) + 1;
    const newPageId = await nextPageId(conn);

    await conn.query(
      `INSERT INTO \`swi_document_page_map\` (document_id, page_id, page_num, page_header, marker_tips, marker_quality, marker_safety, marker_visual)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        targetDocumentId,
        newPageId,
        nextNum,
        sourceHeader,
        sourceMarkers.marker_tips,
        sourceMarkers.marker_quality,
        sourceMarkers.marker_safety,
        sourceMarkers.marker_visual,
      ]
    );

    await clonePageContent(conn, sourcePageId, newPageId);

    await conn.query(
      `UPDATE \`swi_document\`
       SET page_count = GREATEST(COALESCE(page_count, 1), ?)
       WHERE document_id = ? AND document_number = ?`,
      [nextNum, targetDocumentId, targetDocumentNumber]
    );

    await conn.commit();
    return {
      page_id: newPageId,
      page_num: nextNum,
      page_header: sourceHeader,
    };
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

/**
 * Assign new page_num values for existing pages (two-phase update avoids collisions).
 * @param {number} documentId
 * @param {{ page_id: number, page_num: number }[]} assignments
 */
export async function reorderDocumentPages(documentId, assignments) {
  await ensurePageContentSchema();
  if (!Array.isArray(assignments) || !assignments.length) {
    return { updated: 0 };
  }

  const normalized = assignments
    .map((row) => ({
      page_id: Number(row.page_id),
      page_num: Number(row.page_num),
    }))
    .filter(
      (row) =>
        Number.isInteger(row.page_id) &&
        row.page_id > 0 &&
        Number.isInteger(row.page_num) &&
        row.page_num > 0
    );

  if (!normalized.length) {
    return { updated: 0 };
  }

  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();

    const pageIds = normalized.map((row) => row.page_id);
    const placeholders = pageIds.map(() => "?").join(", ");
    const [owned] = await conn.query(
      `SELECT page_id FROM \`swi_document_page_map\`
       WHERE document_id = ? AND page_id IN (${placeholders})`,
      [documentId, ...pageIds]
    );

    if (owned.length !== pageIds.length) {
      throw new Error("One or more pages do not belong to this document");
    }

    for (let i = 0; i < normalized.length; i += 1) {
      const row = normalized[i];
      await conn.query(
        `UPDATE \`swi_document_page_map\` SET \`page_num\` = ? WHERE \`page_id\` = ?`,
        [-(i + 1), row.page_id]
      );
    }

    for (const row of normalized) {
      await conn.query(
        `UPDATE \`swi_document_page_map\` SET \`page_num\` = ? WHERE \`page_id\` = ?`,
        [row.page_num, row.page_id]
      );
    }

    await conn.commit();
    return { updated: normalized.length };
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}
