import { randomBytes } from "node:crypto";
import { mkdirSync } from "node:fs";
import { createRequire } from "node:module";
import { homedir } from "node:os";
import { dirname, join, resolve } from "node:path";
import { fileURLToPath } from "node:url";
import { buildPricingWorkbook } from "./excel.mjs";

const require = createRequire(import.meta.url);
const Database = require("../node_modules/better-sqlite3");
const localRoot = resolve(dirname(fileURLToPath(import.meta.url)), "..");

function appDataDir() {
  return process.env.HANMI_LOCAL_QUOTE_HOME || join(localRoot, "data");
}

function nowText() {
  const date = new Date();
  const pad = (value) => String(value).padStart(2, "0");
  return `${date.getFullYear()}-${pad(date.getMonth() + 1)}-${pad(date.getDate())} ${pad(date.getHours())}:${pad(date.getMinutes())}:${pad(date.getSeconds())}`;
}

function todayIso() {
  const date = new Date();
  const pad = (value) => String(value).padStart(2, "0");
  return `${date.getFullYear()}-${pad(date.getMonth() + 1)}-${pad(date.getDate())}`;
}

function defaultSourceRoot() {
  return join(homedir(), "Documents", "WXWork", "1688857712834306", "Cache", "File");
}

function defaultStockSourceRoot() {
  return join(homedir(), "Desktop");
}

function defaultPricingDir() {
  return join(homedir(), "Desktop", "定价");
}

function id(prefix) {
  return `${prefix}_${Date.now().toString(36)}_${randomBytes(5).toString("hex")}`;
}

function normalizeBarcodeKey(value) {
  const text = String(value || "")
    .normalize("NFKC")
    .replace(/[\u200B-\u200D\uFEFF\u00A0]/g, "")
    .trim()
    .replace(/^'+/, "")
    .replace(/\s+/g, "")
    .toUpperCase();
  return (scientificIntegerText(text) || text).replace(/\.0+$/g, "");
}

function scientificIntegerText(text) {
  const match = String(text || "").match(/^([+-]?)(\d+)(?:\.(\d+))?E([+-]?\d+)$/i);
  if (!match) return "";
  if (match[1] === "-") return "";
  const integer = match[2];
  const fraction = match[3] || "";
  const exponent = Number(match[4]);
  if (!Number.isInteger(exponent)) return "";
  const digits = `${integer}${fraction}`;
  const decimalIndex = integer.length + exponent;
  if (decimalIndex <= 0) return "";
  if (decimalIndex >= digits.length) return `${digits}${"0".repeat(decimalIndex - digits.length)}`;
  const whole = digits.slice(0, decimalIndex);
  const rest = digits.slice(decimalIndex);
  return /^0*$/.test(rest) ? whole : "";
}

function barcodeKeyVariants(value) {
  const raw = normalizeBarcodeKey(value);
  const variants = new Set();
  if (raw) variants.add(raw);
  const withoutDecimalZero = raw.replace(/\.0+$/g, "");
  if (withoutDecimalZero) variants.add(withoutDecimalZero);
  const withoutTrailingPunctuation = raw.replace(/[。．.]+$/g, "");
  if (withoutTrailingPunctuation) variants.add(withoutTrailingPunctuation);
  const beforeSuffix = raw.split(/[-_\/]/)[0];
  if (beforeSuffix && beforeSuffix.replace(/\D+/g, "").length >= 8) variants.add(beforeSuffix);
  const digits = raw.replace(/\D+/g, "");
  if (digits.length >= 8) variants.add(digits);
  return [...variants];
}

function primaryBarcodeKey(value) {
  const variants = barcodeKeyVariants(value);
  return variants.find((key) => /^\d{8,}$/.test(key)) || variants[0] || "";
}

function safeLimit(value, fallback = 100, max = 1000) {
  const number = Number(value);
  if (!Number.isInteger(number) || number <= 0) return fallback;
  return Math.min(number, max);
}

function safeOffset(value) {
  const number = Number(value);
  return Number.isInteger(number) && number > 0 ? number : 0;
}

function parseJson(value, fallback) {
  try {
    return value ? JSON.parse(value) : fallback;
  } catch {
    return fallback;
  }
}

function autoFileReadSummary(autoFiles = {}) {
  autoFiles ||= {};
  const parts = [
    autoFiles.quoteDate ? `读取日期 ${autoFiles.quoteDate}` : "",
    autoFiles.stockSourceDir ? `现货1目录 ${autoFiles.stockSourceDir}` : "",
    autoFiles.regularStockSourceDir ? `现货目录 ${autoFiles.regularStockSourceDir}` : "",
    autoFiles.sourceDir ? `业务数据目录 ${autoFiles.sourceDir}` : "",
    autoFiles.pricingDir ? `定价目录 ${autoFiles.pricingDir}` : "",
    autoFiles.fallbackFromRequestedDate ? `所选日期 ${autoFiles.requestedDate || "-"} 已回退` : "",
    autoFiles.fallbackFromHistoryBatch ? `回退历史批次 ${autoFiles.historyBatchId || "-"}` : "",
    autoFiles.stockFileName ? `现货1 ${autoFiles.stockFileName}` : "",
    autoFiles.regularStockFileName ? `现货 ${autoFiles.regularStockFileName}` : "",
    autoFiles.costFileName ? `成本 ${autoFiles.costFileName}` : "",
    autoFiles.purchaseFileName ? `进货明细 ${autoFiles.purchaseFileName}` : "",
    autoFiles.pricingFileDisplayName || autoFiles.pricingFileName ? `定价表 ${autoFiles.pricingFileDisplayName || autoFiles.pricingFileName}` : ""
  ].filter(Boolean);
  return parts.join("；");
}

function listFromInput(value) {
  if (Array.isArray(value)) return value.map((item) => String(item).trim()).filter(Boolean);
  return String(value || "").split(",").map((item) => item.trim()).filter(Boolean);
}

function rowStableKey(row) {
  return `${primaryBarcodeKey(row.barcodeKey || row.barcode)}\u001f${String(row.productName || "").trim()}`;
}

function addLike(where, params, column, value) {
  const text = String(value || "").trim();
  if (!text) return where;
  params.push(`%${text}%`);
  return `${where} AND ${column} LIKE ?`;
}

function pricingStatusFromStockHkd(value) {
  const text = String(value ?? "").normalize("NFKC").trim().toUpperCase();
  if (!text || text === "#N/A" || text === "N/A" || text === "NA" || text === "A/N") return "needs_pricing";
  return "priced";
}

function repairMojibakeText(value) {
  const text = String(value ?? "");
  if (!text || /[\u4e00-\u9fff]/.test(text)) return text;
  const repaired = Buffer.from(text, "latin1").toString("utf8");
  return /[\u4e00-\u9fff]/.test(repaired) && !repaired.includes("\uFFFD") ? repaired : text;
}

export function createStore(dbPath = join(appDataDir(), "hanmi-pricing-tool.sqlite")) {
  mkdirSync(dirname(dbPath), { recursive: true });
  const db = new Database(dbPath);
  db.pragma("journal_mode = WAL");
  db.pragma("synchronous = NORMAL");
  db.pragma("foreign_keys = ON");

  db.exec(`
    CREATE TABLE IF NOT EXISTS settings (
      key TEXT PRIMARY KEY,
      value TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
    CREATE TABLE IF NOT EXISTS pricing_batches (
      id TEXT PRIMARY KEY,
      quote_date TEXT NOT NULL,
      created_at TEXT NOT NULL,
      confirmed_at TEXT,
      stock_file_name TEXT NOT NULL DEFAULT '',
      pricing_file_name TEXT NOT NULL DEFAULT '',
      cost_file_name TEXT NOT NULL DEFAULT '',
      status TEXT NOT NULL DEFAULT 'draft',
      stock_red_count INTEGER NOT NULL DEFAULT 0,
      priced_match_count INTEGER NOT NULL DEFAULT 0,
      new_pending_count INTEGER NOT NULL DEFAULT 0,
      carryover_count INTEGER NOT NULL DEFAULT 0,
      cost_match_count INTEGER NOT NULL DEFAULT 0,
      cost_missing_count INTEGER NOT NULL DEFAULT 0,
      details TEXT NOT NULL DEFAULT '{}'
    );
    CREATE TABLE IF NOT EXISTS pending_items (
      id TEXT PRIMARY KEY,
      batch_id TEXT NOT NULL,
      quote_date TEXT NOT NULL,
      source_type TEXT NOT NULL DEFAULT 'stock',
      previous_item_id TEXT NOT NULL DEFAULT '',
      previous_batch_id TEXT NOT NULL DEFAULT '',
      stock_file_name TEXT NOT NULL DEFAULT '',
      source_sheet TEXT NOT NULL DEFAULT '',
      source_row INTEGER,
      category1 TEXT NOT NULL DEFAULT '',
      category2 TEXT NOT NULL DEFAULT '',
      barcode TEXT NOT NULL,
      barcode_key TEXT NOT NULL,
      product_name TEXT NOT NULL DEFAULT '',
      quantity REAL,
      stock_hkd TEXT NOT NULL DEFAULT '',
      stock_usd TEXT NOT NULL DEFAULT '',
      stock_rmb TEXT NOT NULL DEFAULT '',
      carton_size TEXT NOT NULL DEFAULT '',
      shelf_life TEXT NOT NULL DEFAULT '',
      remark TEXT NOT NULL DEFAULT '',
      brand TEXT NOT NULL DEFAULT '',
      owner TEXT NOT NULL DEFAULT '',
      cost_hkd REAL,
      cost_status TEXT NOT NULL DEFAULT 'missing',
      pricing_status TEXT NOT NULL DEFAULT 'needs_pricing',
      status TEXT NOT NULL DEFAULT 'pending',
      created_at TEXT NOT NULL,
      confirmed_at TEXT,
      superseded_at TEXT,
      superseded_by_batch_id TEXT NOT NULL DEFAULT '',
      raw_json TEXT NOT NULL DEFAULT '{}',
      FOREIGN KEY (batch_id) REFERENCES pricing_batches(id)
    );
    CREATE INDEX IF NOT EXISTS idx_pending_items_batch ON pending_items(batch_id);
    CREATE INDEX IF NOT EXISTS idx_pending_items_status ON pending_items(status);
    CREATE INDEX IF NOT EXISTS idx_pending_items_barcode ON pending_items(barcode_key);
    CREATE INDEX IF NOT EXISTS idx_pending_items_quote_date ON pending_items(quote_date);
    CREATE TABLE IF NOT EXISTS operation_logs (
      id TEXT PRIMARY KEY,
      created_at TEXT NOT NULL,
      action TEXT NOT NULL,
      target TEXT NOT NULL DEFAULT '',
      file_name TEXT NOT NULL DEFAULT '',
      status TEXT NOT NULL DEFAULT '',
      summary TEXT NOT NULL DEFAULT '',
      details TEXT NOT NULL DEFAULT '{}'
    );
    CREATE INDEX IF NOT EXISTS idx_operation_logs_created_at ON operation_logs(created_at);
  `);

  function ensureColumn(table, column, definition) {
    const columns = db.prepare(`PRAGMA table_info(${table})`).all().map((row) => row.name);
    if (!columns.includes(column)) db.exec(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);
  }

  function refreshDerivedPricingStatus() {
    const derivedStatus = "CASE WHEN UPPER(TRIM(COALESCE(stock_hkd, ''))) IN ('', '#N/A', 'N/A', 'NA', 'A/N') THEN 'needs_pricing' ELSE 'priced' END";
    db.exec(`
      UPDATE pending_items
      SET pricing_status = ${derivedStatus}
      WHERE pricing_status IS NULL
         OR pricing_status NOT IN ('needs_pricing', 'priced')
         OR pricing_status <> ${derivedStatus};

      UPDATE pricing_batches
      SET
        stock_red_count = COALESCE((
          SELECT COUNT(*) FROM pending_items
          WHERE batch_id = pricing_batches.id AND source_type <> 'carryover'
        ), 0),
        priced_match_count = COALESCE((
          SELECT COUNT(*) FROM pending_items
          WHERE batch_id = pricing_batches.id AND source_type <> 'carryover' AND pricing_status = 'priced'
        ), 0),
        new_pending_count = COALESCE((
          SELECT COUNT(*) FROM pending_items
          WHERE batch_id = pricing_batches.id AND source_type <> 'carryover' AND pricing_status = 'needs_pricing'
        ), 0),
        carryover_count = COALESCE((
          SELECT COUNT(*) FROM pending_items
          WHERE batch_id = pricing_batches.id AND source_type = 'carryover'
        ), 0),
        cost_match_count = COALESCE((
          SELECT COUNT(*) FROM pending_items
          WHERE batch_id = pricing_batches.id AND pricing_status = 'needs_pricing' AND cost_status = 'matched'
        ), 0),
        cost_missing_count = COALESCE((
          SELECT COUNT(*) FROM pending_items
          WHERE batch_id = pricing_batches.id AND pricing_status = 'needs_pricing' AND cost_status = 'missing'
        ), 0);
    `);
  }

  function refreshMojibakeFileNames() {
    const targets = [
      { table: "pricing_batches", idColumn: "id", columns: ["stock_file_name", "pricing_file_name", "cost_file_name"] },
      { table: "pending_items", idColumn: "id", columns: ["stock_file_name"] },
      { table: "operation_logs", idColumn: "id", columns: ["file_name"] }
    ];

    for (const target of targets) {
      const columns = target.columns.join(", ");
      const rows = db.prepare(`SELECT ${target.idColumn} AS id, ${columns} FROM ${target.table}`).all();
      for (const row of rows) {
        const updates = [];
        const params = [];
        for (const column of target.columns) {
          const repaired = repairMojibakeText(row[column]);
          if (repaired !== row[column]) {
            updates.push(`${column} = ?`);
            params.push(repaired);
          }
        }
        if (updates.length) {
          params.push(row.id);
          db.prepare(`UPDATE ${target.table} SET ${updates.join(", ")} WHERE ${target.idColumn} = ?`).run(...params);
        }
      }
    }
  }

  ensureColumn("pricing_batches", "confirmed_at", "TEXT");
  ensureColumn("pending_items", "confirmed_at", "TEXT");
  ensureColumn("pending_items", "superseded_at", "TEXT");
  ensureColumn("pending_items", "superseded_by_batch_id", "TEXT NOT NULL DEFAULT ''");
  ensureColumn("pending_items", "pricing_status", "TEXT NOT NULL DEFAULT 'needs_pricing'");
  ensureColumn("pending_items", "remark", "TEXT NOT NULL DEFAULT ''");
  refreshDerivedPricingStatus();
  refreshMojibakeFileNames();

  const defaultSettings = {
    pricingPassword: "1103",
    costPassword: "1103",
    purchasePassword: "1103",
    stockSourceRoot: defaultStockSourceRoot(),
    sourceRoot: defaultSourceRoot(),
    pricingDir: defaultPricingDir()
  };
  for (const [key, value] of Object.entries(defaultSettings)) {
    const settingCount = db.prepare("SELECT COUNT(*) AS count FROM settings WHERE key = ?").get(key).count;
    if (!settingCount) {
      db.prepare("INSERT INTO settings (key, value, updated_at) VALUES (?, ?, ?)")
        .run(key, value, nowText());
    }
  }

  function addOperationLog({ action, target = "", fileName = "", status = "success", summary = "", details = {} }) {
    const createdAt = nowText();
    const logId = id("log");
    db.prepare(`
      INSERT INTO operation_logs (id, created_at, action, target, file_name, status, summary, details)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      logId,
      createdAt,
      String(action || "").trim(),
      String(target || "").trim(),
      String(fileName || "").trim(),
      String(status || "").trim(),
      String(summary || "").trim(),
      JSON.stringify(details || {})
    );
    return { id: logId, createdAt };
  }

  function settings() {
    const rows = db.prepare("SELECT key, value, updated_at AS updatedAt FROM settings").all();
    return Object.fromEntries(rows.map((row) => [row.key, { value: row.value, updatedAt: row.updatedAt }]));
  }

  function updateSettings(input = {}) {
    const updatedAt = nowText();
    const allowed = ["pricingPassword", "costPassword", "purchasePassword", "stockSourceRoot", "sourceRoot", "pricingDir"];
    db.transaction(() => {
      for (const key of allowed) {
        if (input[key] === undefined) continue;
        db.prepare(`
          INSERT INTO settings (key, value, updated_at)
          VALUES (?, ?, ?)
          ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at
        `).run(key, String(input[key] ?? ""), updatedAt);
      }
    })();
    addOperationLog({
      action: "保存配置",
      target: "系统设置",
      status: "success",
      summary: "已保存加密表打开密码配置",
      details: { keys: Object.keys(input).filter((key) => allowed.includes(key)) }
    });
    return settings();
  }

  function pricingPassword() {
    return db.prepare("SELECT value FROM settings WHERE key = 'pricingPassword'").get()?.value || "1103";
  }

  function costPassword() {
    return db.prepare("SELECT value FROM settings WHERE key = 'costPassword'").get()?.value || pricingPassword();
  }

  function purchasePassword() {
    return db.prepare("SELECT value FROM settings WHERE key = 'purchasePassword'").get()?.value || costPassword() || pricingPassword();
  }

  function sourceRoot() {
    return db.prepare("SELECT value FROM settings WHERE key = 'sourceRoot'").get()?.value || defaultSourceRoot();
  }

  function stockSourceRoot() {
    return db.prepare("SELECT value FROM settings WHERE key = 'stockSourceRoot'").get()?.value || defaultStockSourceRoot();
  }

  function pricingDir() {
    return db.prepare("SELECT value FROM settings WHERE key = 'pricingDir'").get()?.value || defaultPricingDir();
  }

  function latestBatchId() {
    return db.prepare(`
      SELECT id
      FROM pricing_batches
      ORDER BY created_at DESC, id DESC
      LIMIT 1
    `).get()?.id || "";
  }

  const insertBatch = db.prepare(`
    INSERT INTO pricing_batches
      (id, quote_date, created_at, stock_file_name, pricing_file_name, cost_file_name, status,
       stock_red_count, priced_match_count, new_pending_count, carryover_count,
       cost_match_count, cost_missing_count, details)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);

  const insertPending = db.prepare(`
    INSERT INTO pending_items
      (id, batch_id, quote_date, source_type, previous_item_id, previous_batch_id,
       stock_file_name, source_sheet, source_row, category1, category2, barcode, barcode_key,
       product_name, quantity, stock_hkd, stock_usd, stock_rmb, carton_size, shelf_life,
       remark, brand, owner, cost_hkd, cost_status, pricing_status, status, created_at, raw_json)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);

  function firstText(...values) {
    for (const value of values) {
      const text = String(value ?? "").trim();
      if (text) return text;
    }
    return "";
  }

  function costMapFromRows(rows) {
    const map = new Map();
    for (const row of rows || []) {
      for (const key of barcodeKeyVariants(row.barcode)) {
        if (key && !map.has(key)) map.set(key, row);
      }
    }
    return map;
  }

  function brandMapFromRows(rows) {
    const map = new Map();
    for (const row of rows || []) {
      for (const key of barcodeKeyVariants(row.barcode)) {
        if (key && !map.has(key)) map.set(key, row);
      }
    }
    return map;
  }

  function pricingInfoMapFromRows(rows) {
    const map = new Map();
    for (const row of rows || []) {
      for (const key of barcodeKeyVariants(row.barcode)) {
        if (key && !map.has(key)) map.set(key, row);
      }
    }
    return map;
  }

  function makePendingRow({ batchId, quoteDate, stockFileName, sourceType, previousItem = null, row, brandMap, costMap, pricingInfoMap, createdAt }) {
    const barcodeKey = primaryBarcodeKey(row.barcode);
    const brand = barcodeKeyVariants(row.barcode).map((key) => brandMap.get(key)).find(Boolean) || {};
    const pricingInfo = barcodeKeyVariants(row.barcode).map((key) => pricingInfoMap.get(key)).find(Boolean) || {};
    const cost = barcodeKeyVariants(row.barcode).map((key) => costMap.get(key)).find(Boolean) || null;
    const costHkd = cost ? Number(cost.costHkd) : null;
    const pricingStatus = row.pricingStatus || previousItem?.pricingStatus || pricingStatusFromStockHkd(row.stockHkd);
    const needsPricing = pricingStatus === "needs_pricing";
    const costStatus = needsPricing ? (Number.isFinite(costHkd) ? "matched" : "missing") : "not_required";
    const remark = firstText(pricingInfo.remark, row.remark, previousItem?.remark);
    return {
      id: id("item"),
      batchId,
      quoteDate,
      sourceType,
      previousItemId: previousItem?.id || "",
      previousBatchId: previousItem?.batchId || "",
      stockFileName: sourceType === "carryover" ? (previousItem?.stockFileName || "") : stockFileName,
      sourceSheet: row.sourceSheet || previousItem?.sourceSheet || "",
      sourceRow: row.sourceRow ?? previousItem?.sourceRow ?? null,
      category1: row.category1 || previousItem?.category1 || "",
      category2: row.category2 || previousItem?.category2 || "",
      barcode: row.barcode,
      barcodeKey,
      productName: row.productName || "",
      quantity: row.quantity ?? null,
      stockHkd: row.stockHkd || "",
      stockUsd: row.stockUsd || "",
      stockRmb: row.stockRmb || "",
      cartonSize: row.cartonSize || "",
      shelfLife: row.shelfLife || "",
      remark,
      brand: brand.brand || row.brand || previousItem?.brand || "",
      owner: brand.owner || row.owner || previousItem?.owner || "",
      costHkd: costStatus === "matched" ? costHkd : null,
      costStatus,
      pricingStatus,
      status: "pending",
      createdAt,
      rawJson: JSON.stringify({ stock: row, cost, brand, previousItemId: previousItem?.id || "" })
    };
  }

  function insertPendingRow(item) {
    insertPending.run(
      item.id,
      item.batchId,
      item.quoteDate,
      item.sourceType,
      item.previousItemId,
      item.previousBatchId,
      item.stockFileName,
      item.sourceSheet,
      item.sourceRow,
      item.category1,
      item.category2,
      item.barcode,
      item.barcodeKey,
      item.productName,
      item.quantity,
      item.stockHkd,
      item.stockUsd,
      item.stockRmb,
      item.cartonSize,
      item.shelfLife,
      item.remark,
      item.brand,
      item.owner,
      item.costHkd,
      item.costStatus,
      item.pricingStatus,
      item.status,
      item.createdAt,
      item.rawJson
    );
  }

  function activePendingRows() {
    return db.prepare(`
      SELECT id, batch_id AS batchId, quote_date AS quoteDate, source_type AS sourceType,
             previous_item_id AS previousItemId, previous_batch_id AS previousBatchId,
             stock_file_name AS stockFileName, source_sheet AS sourceSheet, source_row AS sourceRow,
             category1, category2, barcode, barcode_key AS barcodeKey, product_name AS productName,
             quantity, stock_hkd AS stockHkd, stock_usd AS stockUsd, stock_rmb AS stockRmb,
             carton_size AS cartonSize, shelf_life AS shelfLife, remark, brand, owner, cost_hkd AS costHkd,
             cost_status AS costStatus, pricing_status AS pricingStatus, status, created_at AS createdAt, raw_json AS rawJson
      FROM pending_items
      WHERE status = 'pending'
        AND pricing_status = 'needs_pricing'
      ORDER BY quote_date ASC, created_at ASC, source_row ASC, id ASC
    `).all();
  }

  function buildPurchaseRows({ purchase, stock, brandMap, pricingInfoMap }) {
    const pricedStockMap = new Map();
    for (const row of stock.allRows || stock.rows || []) {
      if (pricingStatusFromStockHkd(row.stockHkd) !== "priced") continue;
      for (const key of barcodeKeyVariants(row.barcode)) {
        if (key && !pricedStockMap.has(key)) pricedStockMap.set(key, row);
      }
    }

    let pricedCount = 0;
    const rows = (purchase?.rows || []).map((row) => {
      const brand = barcodeKeyVariants(row.barcode).map((key) => brandMap.get(key)).find(Boolean) || {};
      const pricingInfo = barcodeKeyVariants(row.barcode).map((key) => pricingInfoMap.get(key)).find(Boolean) || {};
      const stockPrice = barcodeKeyVariants(row.barcode).map((key) => pricedStockMap.get(key)).find(Boolean) || null;
      if (stockPrice) pricedCount += 1;
      return {
        ...row,
        brand: brand.brand || row.brand || "",
        owner: brand.owner || row.owner || "",
        productName: row.productName || brand.productName || stockPrice?.productName || "",
        priceHkd: stockPrice?.stockHkd || "",
        priceUsd: stockPrice?.stockUsd || "",
        priceRmb: stockPrice?.stockRmb || "",
        shelfLife: row.shelfLife || stockPrice?.shelfLife || pricingInfo.shelfLife || "",
        remark: firstText(pricingInfo.remark, row.remark, stockPrice?.remark)
      };
    });
    return { rows, pricedCount };
  }

  function processBatch({ stockFileName, pricingFileName, costFileName, purchaseFileName = "", stock, purchaseStock = null, pricing, cost, purchase = null, autoFiles = null }) {
    const createdAt = nowText();
    const batchId = id("batch");
    const quoteDate = stock.quoteDate || pricing.quoteDate || cost.quoteDate || todayIso();
    const brandMap = brandMapFromRows(pricing.brandRows);
    const pricingInfoMap = pricingInfoMapFromRows(pricing.pricedRows);
    const costMap = costMapFromRows(cost.rows);
    const purchaseResult = purchase ? buildPurchaseRows({ purchase, stock: purchaseStock || stock, brandMap, pricingInfoMap }) : { rows: [], pricedCount: 0 };
    const currentRows = [];
    const currentKeys = new Set();
    const currentPricingStatusByKey = new Map();
    let pricedMatchCount = 0;
    for (const row of stock.rows || []) {
      const key = primaryBarcodeKey(row.barcode);
      if (!key) continue;
      const pricingStatus = pricingStatusFromStockHkd(row.stockHkd);
      if (pricingStatus === "priced") {
        pricedMatchCount += 1;
      }
      const nextRow = { ...row, pricingStatus };
      currentRows.push(nextRow);
      const stableKey = rowStableKey(nextRow);
      currentKeys.add(stableKey);
      currentPricingStatusByKey.set(stableKey, pricingStatus);
    }

    const previousRows = activePendingRows();
    const carriedRows = [];
    const resolvedRows = [];
    const replacedRows = [];
    for (const row of previousRows) {
      const nextPricingStatus = currentPricingStatusByKey.get(rowStableKey(row));
      if (nextPricingStatus === "priced") {
        resolvedRows.push(row);
      } else if (nextPricingStatus === "needs_pricing") {
        replacedRows.push(row);
      } else {
        carriedRows.push(row);
      }
    }

    const newItems = [];
    const carryoverItems = [];
    let newPendingCount = 0;
    let costMatchCount = 0;
    let costMissingCount = 0;

    db.transaction(() => {
      for (const row of resolvedRows) {
        db.prepare(`
          UPDATE pending_items
          SET status = 'resolved', superseded_at = ?, superseded_by_batch_id = ?
          WHERE id = ? AND status = 'pending'
        `).run(createdAt, batchId, row.id);
      }
      for (const row of replacedRows) {
        db.prepare(`
          UPDATE pending_items
          SET status = 'replaced', superseded_at = ?, superseded_by_batch_id = ?
          WHERE id = ? AND status = 'pending'
        `).run(createdAt, batchId, row.id);
      }
      for (const row of carriedRows) {
        db.prepare(`
          UPDATE pending_items
          SET status = 'carried', superseded_at = ?, superseded_by_batch_id = ?
          WHERE id = ? AND status = 'pending'
        `).run(createdAt, batchId, row.id);
      }

      for (const row of currentRows) {
        const item = makePendingRow({ batchId, quoteDate, stockFileName, sourceType: "stock", row, brandMap, costMap, pricingInfoMap, createdAt });
        if (item.pricingStatus === "needs_pricing") {
          newPendingCount += 1;
          if (item.costStatus === "matched") costMatchCount += 1;
          else costMissingCount += 1;
        }
        newItems.push(item);
      }
      for (const previous of carriedRows) {
        const row = {
          sourceSheet: previous.sourceSheet,
          sourceRow: previous.sourceRow,
          category1: previous.category1,
          category2: previous.category2,
          barcode: previous.barcode,
          productName: previous.productName,
          quantity: previous.quantity,
          stockHkd: previous.stockHkd,
          stockUsd: previous.stockUsd,
          stockRmb: previous.stockRmb,
          cartonSize: previous.cartonSize,
          shelfLife: previous.shelfLife,
          remark: previous.remark,
          brand: previous.brand,
          owner: previous.owner,
          pricingStatus: "needs_pricing"
        };
        const item = makePendingRow({
          batchId,
          quoteDate,
          stockFileName: previous.stockFileName,
          sourceType: "carryover",
          previousItem: previous,
          row,
          brandMap,
          costMap,
          pricingInfoMap,
          createdAt
        });
        if (item.costStatus === "matched") costMatchCount += 1;
        else costMissingCount += 1;
        carryoverItems.push(item);
      }

      insertBatch.run(
        batchId,
        quoteDate,
        createdAt,
        stockFileName,
        pricingFileName,
        costFileName,
        "draft",
        stock.rows?.length || 0,
        pricedMatchCount,
        newPendingCount,
        carryoverItems.length,
        costMatchCount,
        costMissingCount,
        JSON.stringify({
          stock: {
            sheetSummaries: stock.sheetSummaries || [],
            errors: stock.errors || []
          },
          pricing: {
            priceSheetName: pricing.priceSheetName,
            brandSheetName: pricing.brandSheetName,
            pricedRows: pricing.pricedRows?.length || 0,
            brandRows: pricing.brandRows?.length || 0,
            encrypted: pricing.encrypted
          },
          cost: {
            sheetSummaries: cost.sheetSummaries || [],
            errors: cost.errors || []
          },
          purchase: purchase ? {
            fileName: purchaseFileName || purchase.fileName || "",
            encrypted: purchase.encrypted,
            sheetSummaries: purchase.sheetSummaries || [],
            errors: purchase.errors || [],
            rawRows: purchase.rawRows?.length || 0,
            dedupedRows: purchaseResult.rows.length,
            syncedPricingRows: purchaseResult.pricedCount,
            stockFileName: purchaseStock?.fileName || "",
            stockSheetSummaries: purchaseStock?.sheetSummaries || [],
            rows: purchaseResult.rows
          } : null,
          autoFiles,
          resolvedPreviousCount: resolvedRows.length,
          replacedPreviousCount: replacedRows.length
        })
      );
      for (const item of newItems) insertPendingRow(item);
      for (const item of carryoverItems) insertPendingRow(item);
    })();

    const readFilesSummary = autoFileReadSummary(autoFiles);
    addOperationLog({
      action: "生成批次",
      target: "待定价",
      fileName: readFilesSummary || stockFileName,
      status: "success",
      summary: `处理完成：标红 ${stock.rows?.length || 0} 行，已定价 ${pricedMatchCount} 行，待定价 ${newPendingCount} 行，结转 ${carryoverItems.length} 行，成本缺失 ${costMissingCount} 行${readFilesSummary ? `；读取文件：${readFilesSummary}` : ""}`,
      details: {
        batchId,
        quoteDate,
        stockFileName,
        pricingFileName,
        costFileName,
        purchaseFileName,
        autoFiles,
        readFilesSummary,
        pricedMatchCount,
        newPendingCount,
        carryoverCount: carryoverItems.length,
        costMatchCount,
        costMissingCount,
        purchaseRows: purchaseResult.rows.length,
        purchaseSyncedPricingRows: purchaseResult.pricedCount
      }
    });

    return batch(batchId, { includeRows: true });
  }

  function stats() {
    const latest = latestBatchId();
    const latestParams = latest ? [latest] : [""];
    return {
      batches: db.prepare("SELECT COUNT(*) AS count FROM pricing_batches").get().count,
      pendingRows: db.prepare("SELECT COUNT(*) AS count FROM pending_items WHERE batch_id = ? AND pricing_status = 'needs_pricing'").get(...latestParams).count,
      pricedRows: db.prepare("SELECT COUNT(*) AS count FROM pending_items WHERE batch_id = ? AND pricing_status = 'priced'").get(...latestParams).count,
      confirmedRows: db.prepare("SELECT COUNT(*) AS count FROM pending_items WHERE batch_id = ? AND status = 'confirmed' AND pricing_status = 'needs_pricing'").get(...latestParams).count,
      carriedRows: db.prepare("SELECT COUNT(*) AS count FROM pending_items WHERE batch_id = ? AND source_type = 'carryover'").get(...latestParams).count,
      costMissingRows: db.prepare("SELECT COUNT(*) AS count FROM pending_items WHERE batch_id = ? AND pricing_status = 'needs_pricing' AND cost_status = 'missing'").get(...latestParams).count,
      latestBatchId: latest
    };
  }

  function batches(input = {}) {
    const limit = safeLimit(input.limit, 50, 200);
    const offset = safeOffset(input.offset);
    const total = db.prepare("SELECT COUNT(*) AS count FROM pricing_batches").get().count;
    const rows = db.prepare(`
      SELECT id, quote_date AS quoteDate, created_at AS createdAt, confirmed_at AS confirmedAt,
             stock_file_name AS stockFileName, pricing_file_name AS pricingFileName, cost_file_name AS costFileName,
             status, stock_red_count AS stockRedCount, priced_match_count AS pricedMatchCount,
             new_pending_count AS newPendingCount, carryover_count AS carryoverCount,
             cost_match_count AS costMatchCount, cost_missing_count AS costMissingCount
      FROM pricing_batches
      ORDER BY created_at DESC, id DESC
      LIMIT ? OFFSET ?
    `).all(limit, offset);
    return { total, limit, offset, rows };
  }

  function batch(batchId, options = {}) {
    const idToRead = batchId || latestBatchId();
    if (!idToRead) return null;
    const row = db.prepare(`
      SELECT id, quote_date AS quoteDate, created_at AS createdAt, confirmed_at AS confirmedAt,
             stock_file_name AS stockFileName, pricing_file_name AS pricingFileName, cost_file_name AS costFileName,
             status, stock_red_count AS stockRedCount, priced_match_count AS pricedMatchCount,
             new_pending_count AS newPendingCount, carryover_count AS carryoverCount,
             cost_match_count AS costMatchCount, cost_missing_count AS costMissingCount,
             details
      FROM pricing_batches
      WHERE id = ?
    `).get(idToRead);
    if (!row) return null;
    const result = { ...row, details: parseJson(row.details, {}) };
    if (options.includeRows) {
      result.rows = queryItems({ batchId: idToRead, status: "all", pricingStatus: "all", limit: 1000 }).rows;
    }
    return result;
  }

  function latestAutoFiles() {
    const row = db.prepare(`
      SELECT id, quote_date AS quoteDate, created_at AS createdAt, details
      FROM pricing_batches
      ORDER BY created_at DESC, id DESC
      LIMIT 1
    `).get();
    if (!row) return null;
    const details = parseJson(row.details, {});
    const autoFiles = details?.autoFiles || null;
    if (!autoFiles?.quoteDate) return null;
    return { batchId: row.id, quoteDate: row.quoteDate, createdAt: row.createdAt, autoFiles };
  }

  function queryItems(input = {}) {
    const idToRead = input.batchId || latestBatchId();
    const limit = safeLimit(input.limit);
    const offset = safeOffset(input.offset);
    if (!idToRead) return { total: 0, limit, offset, rows: [], batch: null, options: {} };

    const status = String(input.status || "all").trim();
    const params = [idToRead];
    let where = "WHERE batch_id = ?";
    if (status && status !== "all") {
      params.push(status);
      where += " AND status = ?";
    }
    const costStatus = String(input.costStatus || "").trim();
    if (costStatus && costStatus !== "all") {
      params.push(costStatus);
      where += " AND cost_status = ? AND pricing_status = 'needs_pricing'";
    }
    const pricingStatus = String(input.pricingStatus || "").trim();
    if (pricingStatus && pricingStatus !== "all") {
      params.push(pricingStatus);
      where += " AND pricing_status = ?";
    }
    const sourceType = String(input.sourceType || "").trim();
    if (sourceType && sourceType !== "all") {
      params.push(sourceType);
      where += " AND source_type = ?";
    }
    where = addLike(where, params, "brand", input.brand);
    where = addLike(where, params, "owner", input.owner);
    const keyword = String(input.keyword || "").trim();
    if (keyword) {
      params.push(`%${keyword}%`, `%${keyword}%`, `%${keyword}%`, `%${keyword}%`);
      where += " AND (barcode LIKE ? OR product_name LIKE ? OR stock_file_name LIKE ? OR remark LIKE ?)";
    }

    const total = db.prepare(`SELECT COUNT(*) AS count FROM pending_items ${where}`).get(...params).count;
    const rows = db.prepare(`
      SELECT id, batch_id AS batchId, quote_date AS quoteDate, source_type AS sourceType,
             previous_item_id AS previousItemId, previous_batch_id AS previousBatchId,
             stock_file_name AS stockFileName, source_sheet AS sourceSheet, source_row AS sourceRow,
             category1, category2, barcode, barcode_key AS barcodeKey, product_name AS productName,
             quantity, stock_hkd AS stockHkd, stock_usd AS stockUsd, stock_rmb AS stockRmb,
             carton_size AS cartonSize, shelf_life AS shelfLife, remark, brand, owner, cost_hkd AS costHkd,
             cost_status AS costStatus, pricing_status AS pricingStatus, status, created_at AS createdAt, confirmed_at AS confirmedAt
      FROM pending_items
      ${where}
      ORDER BY CASE pricing_status WHEN 'needs_pricing' THEN 0 ELSE 1 END,
               source_type DESC, source_sheet ASC, source_row ASC, created_at ASC, id ASC
      LIMIT ? OFFSET ?
    `).all(...params, limit, offset);

    const brandOptions = db.prepare(`
      SELECT DISTINCT brand AS value FROM pending_items
      WHERE batch_id = ? AND brand IS NOT NULL AND TRIM(brand) <> ''
      ORDER BY brand COLLATE NOCASE ASC LIMIT 500
    `).all(idToRead).map((row) => row.value);
    const ownerOptions = db.prepare(`
      SELECT DISTINCT owner AS value FROM pending_items
      WHERE batch_id = ? AND owner IS NOT NULL AND TRIM(owner) <> ''
      ORDER BY owner COLLATE NOCASE ASC LIMIT 500
    `).all(idToRead).map((row) => row.value);

    return {
      total,
      limit,
      offset,
      rows,
      batch: batch(idToRead),
      options: { brands: brandOptions, owners: ownerOptions }
    };
  }

  function confirmBatch(batchId, itemIds = []) {
    const target = batch(batchId);
    if (!target) throw Object.assign(new Error("未找到待确认批次"), { statusCode: 404 });
    const confirmedAt = nowText();
    const ids = listFromInput(itemIds);
    let changed = 0;
    db.transaction(() => {
      if (ids.length) {
        const placeholders = ids.map(() => "?").join(",");
        const result = db.prepare(`
          UPDATE pending_items
          SET status = 'confirmed', confirmed_at = ?
          WHERE batch_id = ? AND status = 'pending' AND pricing_status = 'needs_pricing' AND id IN (${placeholders})
        `).run(confirmedAt, batchId, ...ids);
        changed = result.changes;
      } else {
        const result = db.prepare(`
          UPDATE pending_items
          SET status = 'confirmed', confirmed_at = ?
          WHERE batch_id = ? AND status = 'pending' AND pricing_status = 'needs_pricing'
        `).run(confirmedAt, batchId);
        changed = result.changes;
      }
      db.prepare(`
        UPDATE pricing_batches
        SET status = CASE
          WHEN EXISTS (SELECT 1 FROM pending_items WHERE batch_id = pricing_batches.id AND status = 'pending' AND pricing_status = 'needs_pricing')
          THEN 'part_confirmed'
          ELSE 'confirmed'
        END,
        confirmed_at = COALESCE(confirmed_at, ?)
        WHERE id = ?
      `).run(confirmedAt, batchId);
    })();
    addOperationLog({
      action: "业务确认",
      target: "待定价",
      fileName: target.stockFileName,
      status: "success",
      summary: `已确认 ${changed} 行待定价数据`,
      details: { batchId, changed, itemIds: ids }
    });
    return { batchId, changed, confirmedAt, batch: batch(batchId) };
  }

  async function exportBatch(batchId) {
    const target = batch(batchId);
    if (!target) throw Object.assign(new Error("未找到可导出的批次"), { statusCode: 404 });
    const rows = db.prepare(`
      SELECT id, batch_id AS batchId, quote_date AS quoteDate, source_type AS sourceType,
             stock_file_name AS stockFileName, source_sheet AS sourceSheet, source_row AS sourceRow,
             category1, category2, barcode, product_name AS productName, quantity,
             stock_hkd AS stockHkd, stock_usd AS stockUsd, stock_rmb AS stockRmb,
             carton_size AS cartonSize, shelf_life AS shelfLife, remark, brand, owner,
             cost_hkd AS costHkd, cost_status AS costStatus, pricing_status AS pricingStatus, status
      FROM pending_items
      WHERE batch_id = ?
        AND UPPER(TRIM(COALESCE(stock_hkd, ''))) IN ('', '#N/A', 'N/A', 'NA', 'A/N')
        AND status IN ('pending', 'confirmed')
      ORDER BY source_type DESC, source_sheet ASC, source_row ASC, id ASC
    `).all(batchId);
    if (!rows.length) {
      throw Object.assign(new Error("该批次没有可导出的待定价行"), { statusCode: 400 });
    }
    const purchaseRows = Array.isArray(target.details?.purchase?.rows) ? target.details.purchase.rows : [];
    const body = await buildPricingWorkbook({ quoteDate: target.quoteDate, rows, purchaseRows });
    const readFilesSummary = autoFileReadSummary(target.details?.autoFiles || {});
    addOperationLog({
      action: "导出",
      target: "当天待定价表",
      fileName: readFilesSummary || target.stockFileName,
      status: "success",
      summary: `导出完成：${rows.length} 行，文件大小 ${body.byteLength} 字节${readFilesSummary ? `；读取文件：${readFilesSummary}` : ""}`,
      details: { batchId, rows: rows.length, bytes: body.byteLength, autoFiles: target.details?.autoFiles || null, readFilesSummary }
    });
    return body;
  }

  function operationLogs(input = {}) {
    const limit = safeLimit(input.limit, 120, 500);
    const offset = safeOffset(input.offset);
    const total = db.prepare("SELECT COUNT(*) AS count FROM operation_logs").get().count;
    const rows = db.prepare(`
      SELECT id, created_at AS createdAt, action, target, file_name AS fileName, status, summary, details
      FROM operation_logs
      ORDER BY created_at DESC, id DESC
      LIMIT ? OFFSET ?
    `).all(limit, offset).map((row) => ({ ...row, details: parseJson(row.details, {}) }));
    return { total, limit, offset, rows };
  }

  return {
    db,
    dbPath,
    settings,
    updateSettings,
    pricingPassword,
    costPassword,
    purchasePassword,
    stockSourceRoot,
    sourceRoot,
    pricingDir,
    processBatch,
    stats,
    batches,
    batch,
    latestAutoFiles,
    queryItems,
    confirmBatch,
    exportBatch,
    addOperationLog,
    operationLogs
  };
}
