/**
 * item-intel.mjs — live smoke for the item-intel service.
 *
 * Runs the description-fuzzy, item-level intel algorithm against the
 * 3 quote_items from Q202608094 and prints the resulting JSON. Does
 * NOT write to PB unless WRITE_INTEL=1 is set in the env.
 *
 *   node scripts/smoke/item-intel.mjs                # dry-run, prints JSON
 *   WRITE_INTEL=1 node scripts/smoke/item-intel.mjs # also persists ai_item_insights
 *
 * The algorithm here is the spec for the future
 * `src/lib/server/item-intel-service.ts`. Once the JSON looks right,
 * port this file to TS + add the ItemIntel types to
 * `src/lib/types.ts`.
 */
import { readFileSync } from "node:fs";
import { resolve } from "node:path";
import PocketBase from "pocketbase";

// ── env (inline loader so we don't depend on tsx) ───────────────────
function loadEnvFile(name) {
  try {
    const raw = readFileSync(resolve(process.cwd(), name), "utf8");
    for (const line of raw.split("\n")) {
      const t = line.trim();
      if (!t || t.startsWith("#")) continue;
      const m = t.match(/^([A-Z0-9_]+)\s*=\s*(.+)$/i);
      if (m && !process.env[m[1]]) {
        process.env[m[1]] = m[2].replace(/^["']|["']$/g, "").trim();
      }
    }
  } catch {}
}
loadEnvFile(".env");
loadEnvFile(".env.local");

const PB_URL =
  process.env.NEXT_PUBLIC_POCKETBASE_URL || "http://127.0.0.1:8090";
const EMAIL = process.env.PB_ADMIN_EMAIL;
const PASS = process.env.PB_ADMIN_PASSWORD;
if (!EMAIL || !PASS) {
  console.error("PB_ADMIN_EMAIL / PB_ADMIN_PASSWORD missing in .env.local");
  process.exit(1);
}

const WRITE = process.env.WRITE_INTEL === "1";

// ── fuzzy helpers (mirror src/lib/server/order-quote-match.ts) ───────
const STOPWORDS = new Set([
  "the", "and", "with", "for", "of", "to", "a", "an", "in", "on", "at",
  "by", "from", "as", "is", "or", "x", "-", "/", "&",
]);
function tokenize(s) {
  const out = new Set();
  if (!s) return out;
  for (const tok of String(s).toLowerCase().match(/[a-z0-9]+/g) || []) {
    out.add(tok);
  }
  return out;
}
function informativeTokens(s, max = 4) {
  const toks = tokenize(s);
  const keep = [...toks]
    .filter((t) => !STOPWORDS.has(t) && t.length >= 3)
    .sort((a, b) => b.length - a.length)
    .slice(0, max);
  return keep;
}
function jaccard(a, b) {
  if (a.size === 0 || b.size === 0) return 0;
  let inter = 0;
  for (const t of a) if (b.has(t)) inter++;
  return inter / (a.size + b.size - inter);
}
function overlapScore(a, b) {
  if (a.size === 0 || b.size === 0) return 0;
  let inter = 0;
  for (const t of a) if (b.has(t)) inter++;
  return inter / Math.min(a.size, b.size);
}
function combinedScore(a, b) {
  return Math.max(jaccard(a, b), overlapScore(a, b));
}
/**
 * PB `~` is case-insensitive contains. We OR the top informative tokens
 * so the candidate set stays broad (a description that says "black"
 * still pulls stock rows labelled "BLK"/"ZPL"), then rank in-app with
 * Jaccard + overlap. The in-app scorer is where precision lives.
 */
function buildPbContainsFilter(field, targetDesc, maxTokens = 4) {
  const toks = informativeTokens(targetDesc, maxTokens);
  if (toks.length === 0) return "";
  return toks.map((t) => `${field} ~ "${t}"`).join(" || ");
}
function rankByDescription(targetDesc, candidates, opts = {}) {
  const { threshold = 0.25, useJaccardOnly = false } = opts;
  const targetTokens = tokenize(targetDesc);
  if (targetTokens.size === 0) return [];
  const scored = [];
  for (const c of candidates) {
    const desc = c.description || "";
    if (!desc) continue;
    const ct = tokenize(desc);
    const j = jaccard(targetTokens, ct);
    const o = overlapScore(targetTokens, ct);
    const score = useJaccardOnly ? j : Math.max(j, o);
    if (score >= threshold) scored.push({ item: c, score });
  }
  scored.sort((a, b) => b.score - a.score);
  return scored;
}
function daysAgo(iso) {
  if (!iso) return 0;
  const t = new Date(iso).getTime();
  if (Number.isNaN(t)) return 0;
  return Math.max(0, Math.floor((Date.now() - t) / 86_400_000));
}

// ── PB helpers ──────────────────────────────────────────────────────
async function safeGetList(pb, collection, opts, fallback = { items: [] }) {
  try {
    return await pb.collection(collection).getList(1, opts.max || 300, opts);
  } catch (err) {
    const status = err?.status;
    if (status === 400 || status === 404) {
      console.warn(`[safeGetList] ${collection} returned ${status} — degrading`);
      return fallback;
    }
    throw err;
  }
}
async function loadSuppliersById(pb) {
  const rows = await pb
    .collection("suppliers")
    .getFullList({ fields: "id,suppliername", batch: 500 });
  return new Map(rows.map((s) => [s.id, s.suppliername]));
}

// ── the 5 sections (description-fuzzy, no SKU gate) ────────────────

/** 1. STOCK: fuzzy stock_items → for each, stock_purchase_items join purchases. */
async function buildStock(pb, target, supplierNameById) {
  const filter = buildPbContainsFilter("description", target.description);
  if (!filter) return { matches: [], purchase_history: [] };
  const candRows = await safeGetList(pb, "stock_items", {
    filter,
    fields: "id,sku,name,description,cost_price,selling_price,current_qty,min_qty,supplier,markup,is_active",
    max: 500,
  });
  const rankedAll = rankByDescription(target.description, candRows.items, { threshold: 0.2 });
  // Two limits, two reasons:
  //   - matches cap (3): what the popover displays under "Stock List"
  //   - purchase-cap (10): how many stock_items we walk for purchase
  //     history so the supplier dedup sees the full supplier pool.
  //     The supplier_history section is the "call these N suppliers"
  //     list — truncating this low would hide legitimate suppliers
  //     we bought from (their stock_item just scored below the stock
  //     display cutoff).
  const stockItemsForDisplay = rankedAll.slice(0, 3);
  const stockItemsForPurchases = rankedAll.slice(0, 10);
  const matches = stockItemsForDisplay.map(({ item: si, score }) => buildStockMatchRow(si, score, supplierNameById));
  const purchase_history = (await Promise.all(
    stockItemsForPurchases.map(({ item: si }) => fetchStockPurchaseHistory(pb, si.id, supplierNameById))
  )).flat();
  purchase_history.sort((a, b) => (b.purchased_at || "").localeCompare(a.purchased_at || ""));
  return { matches, purchase_history };
}

function buildStockMatchRow(si, score, supplierNameById) {
  const current_qty = si.current_qty || 0;
  const min_qty = si.min_qty ?? null;
  let status = "out";
  if (current_qty > 0) status = min_qty != null && current_qty <= min_qty ? "low" : "in_stock";
  return {
    stock_item_id: si.id,
    score: +score.toFixed(3),
    name: si.name || "",
    sku: si.sku || "",
    description: si.description || "",
    current_qty,
    min_qty,
    cost_price: si.cost_price || 0,
    selling_price: si.selling_price || 0,
    supplier_id: si.supplier || "",
    supplier_name: supplierNameById.get(si.supplier) || si.supplier || "",
    status,
  };
}

async function fetchStockPurchaseHistory(pb, stockItemId, supplierNameById) {
  try {
    const spiRows = await pb
      .collection("stock_purchase_items")
      .getFullList({
        filter: `stock_item = "${stockItemId}"`,
        expand: "purchase",
        fields:
          "id,purchase,stock_item,cost_price,quantity,quantity_received,notes,status," +
          "expand.purchase.id,expand.purchase.purchase_number,expand.purchase.supplier,expand.purchase.created,expand.purchase.status",
      });
    const out = [];
    for (const spi of spiRows) {
      const p = spi.expand?.purchase;
      if (!p) continue;
      out.push({
        stock_item_id: stockItemId,
        stock_purchase_item_id: spi.id,
        purchase_id: p.id,
        purchase_number: p.purchase_number || "",
        purchased_at: p.created,
        days_ago: daysAgo(p.created),
        cost_price: spi.cost_price || 0,
        quantity: spi.quantity || 0,
        quantity_received: spi.quantity_received || 0,
        supplier_id: p.supplier || "",
        supplier_name: supplierNameById.get(p.supplier) || p.supplier || "",
        status: p.status || "",
      });
    }
    return out;
  } catch (err) {
    console.warn(`[stock] spi join failed for ${stockItemId}:`, err?.status || err?.message);
    return [];
  }
}

/** Derive multi-supplier "last supplier price" from purchase_history. */
function buildLastSupplierPrice(purchase_history) {
  const bySupplier = new Map();
  for (const r of purchase_history) {
    if (!r.supplier_id) continue;
    const prev = bySupplier.get(r.supplier_id);
    if (!prev) {
      bySupplier.set(r.supplier_id, {
        supplier_id: r.supplier_id,
        supplier_name: r.supplier_name,
        last_cost: r.cost_price,
        last_bought_at: r.purchased_at,
        days_ago: r.days_ago,
        times_bought: 1,
        last_purchase_number: r.purchase_number,
      });
    } else {
      prev.times_bought += 1;
      if (
        new Date(r.purchased_at).getTime() > new Date(prev.last_bought_at).getTime()
      ) {
        prev.last_cost = r.cost_price;
        prev.last_bought_at = r.purchased_at;
        prev.days_ago = r.days_ago;
        prev.last_purchase_number = r.purchase_number;
      }
    }
  }
  return [...bySupplier.values()].sort((a, b) =>
    (b.last_bought_at || "").localeCompare(a.last_bought_at || "")
  );
}

/** 2. PRICE LIST: fuzzy price_list across description + search_text + sku. */
async function buildPriceList(pb, target, supplierNameById) {
  const toks = informativeTokens(target.description, 4);
  if (toks.length === 0) return [];
  // OR informative tokens across every text column the repo already
  // keeps searchable. `search_text` is the denormalised token soup
  // built by `buildPriceListSearchText` — it's how the Catalogue tab
  // does fuzzy hits, and the cheapest way to catch items where the
  // catalogue wording diverges from the operator's wording
  // (e.g. "SAE 50 - 20L" → row "SAE 50 SF/CF4 - 20 LT").
  const cols = ["description", "search_text", "sku", "sku_normalised"];
  const clauses = cols.flatMap((c) => toks.map((t) => `${c} ~ "${t}"`));
  const filter = `(${clauses.join(" || ")})`;
  const candRows = await safeGetList(pb, "price_list", {
    filter,
    fields:
      "id,sku,sku_normalised,description,brand,category,subcategory,cost_price,selling_price,supplier,source_file,unit,stock_qty,updated",
    max: 300,
  });
  // Rank with overlapScore — "all the operator's distinctive tokens
  // appear somewhere in the row's token soup". Threshold 0.6 = at
  // least 60% of target tokens present (e.g. for 3 tokens → 2 of 3).
  // Drop the jaccard gate: for short queries against a rich
  // denormalised blob, overlap is the right measure.
  const targetTokens = tokenize(target.description);
  const ranked = [];
  for (const r of candRows.items) {
    const haystack = [r.description, r.search_text, r.sku, r.sku_normalised, r.brand, r.category, r.subcategory, r.supplier, r.source_file].filter(Boolean).join(" ");
    const hayTokens = tokenize(haystack);
    const o = overlapScore(targetTokens, hayTokens);
    if (o >= 0.6) ranked.push({ row: r, score: o });
  }
  ranked.sort((a, b) => b.score - a.score);
  return ranked.slice(0, 5).map(({ row: r, score }) => ({
    score: +score.toFixed(3),
    sku: r.sku || "",
    description: r.description || "",
    brand: r.brand || "",
    category: r.category || "",
    cost_price: r.cost_price || 0,
    selling_price: r.selling_price || 0,
    supplier: r.supplier || "",
    source_file: r.source_file || "",
    updated: r.updated,
  }));
}

/** 3. QUOTE HISTORY: fuzzy quote_items by description, expand quote. */
async function buildQuoteHistory(pb, target) {
  const filter = buildPbContainsFilter("description", target.description);
  if (!filter) return [];
  const candRows = await safeGetList(pb, "quote_items", {
    filter: `id != "${target.id}" && (${filter})`,
    expand: "quote",
    fields:
      "id,description,sku,cost_price,selling_price,quantity,created,quote," +
      "expand.quote.id,expand.quote.quote_number,expand.quote.status,expand.quote.customer_name,expand.quote.company",
    max: 300,
    sort: "-created",
  });
  const ranked = rankByDescription(target.description, candRows.items, { threshold: 0.25 }).slice(0, 2);
  return ranked.map(({ item: q, score }) => ({
    score: +score.toFixed(3),
    quote_item_id: q.id,
    quote_id: q.quote,
    quote_number: q.expand?.quote?.quote_number || "",
    quote_status: q.expand?.quote?.status || "",
    customer_name: q.expand?.quote?.customer_name || "",
    company: q.expand?.quote?.company || "",
    description: q.description || "",
    cost_price: q.cost_price || 0,
    selling_price: q.selling_price || 0,
    quantity: q.quantity || 0,
    quoted_at: q.created,
    days_ago: daysAgo(q.created),
  }));
}

/** 4. ORDER HISTORY: fuzzy order_items by description, expand order. */
async function buildOrderHistory(pb, target, supplierNameById) {
  const filter = buildPbContainsFilter("description", target.description);
  if (!filter) return [];
  const candRows = await safeGetList(pb, "order_items", {
    filter,
    expand: "order",
    fields:
      "id,description,sku,cost_price,selling_price,quantity,item_status,supplier,updated,order," +
      "expand.order.id,expand.order.order_number,expand.order.company",
    max: 300,
    sort: "-updated",
  });
  const ranked = rankByDescription(target.description, candRows.items, { threshold: 0.25 }).slice(0, 2);
  return ranked.map(({ item: o, score }) => ({
    score: +score.toFixed(3),
    order_item_id: o.id,
    order_id: o.order,
    order_number: o.expand?.order?.order_number || "",
    description: o.description || "",
    cost_price: o.cost_price || 0,
    selling_price: o.selling_price || 0,
    quantity: o.quantity || 0,
    item_status: o.item_status || "",
    supplier_id: o.supplier || "",
    supplier_name: supplierNameById.get(o.supplier) || o.supplier || "",
    updated: o.updated,
    days_ago: daysAgo(o.updated),
  }));
}

/** Compose the short summary. */
function composeSummary({ stock, last_supplier_price, quote_history, order_history, price_list }) {
  const bits = [];
  const topStock = stock.matches?.[0];
  if (topStock && topStock.current_qty > 0) {
    bits.push(
      `in stock ${topStock.current_qty} (${topStock.supplier_name || "—"}, cost R${topStock.cost_price.toFixed(2)})`
    );
  } else if (topStock) {
    bits.push(`out of stock (last supplier ${topStock.supplier_name || "—"})`);
  }
  if (last_supplier_price.length === 1) {
    const s = last_supplier_price[0];
    bits.push(
      `bought from ${s.supplier_name} ${s.days_ago}d ago @ R${s.last_cost.toFixed(2)} (×${s.times_bought})`
    );
  } else if (last_supplier_price.length > 1) {
    bits.push(`bought from ${last_supplier_price.length} suppliers — top: ${last_supplier_price[0].supplier_name} R${last_supplier_price[0].last_cost.toFixed(2)} ${last_supplier_price[0].days_ago}d ago`);
  }
  const lq = quote_history[0];
  if (lq) bits.push(`last quoted R${lq.selling_price.toFixed(2)} ${lq.days_ago}d ago (${lq.quote_number})`);
  const lo = order_history[0];
  if (lo) bits.push(`last order ${lo.days_ago}d ago (${lo.order_number}, ${lo.item_status})`);
  if (price_list.length > 0) bits.push(`on price list (${price_list.length})`);
  return bits.join(" · ") || "No history yet for this item.";
}

// ── main service function (mirrors future populateItemIntel) ────────
async function populateItemIntel(pb, itemId, collection, { write = false } = {}) {
  const target = await pb.collection(collection).getOne(itemId, {
    fields: "id,sku,description,cost_price,selling_price,quantity",
  });
  const supplierNameById = await loadSuppliersById(pb);
  const generated_at = new Date().toISOString();

  const [stock, price_list, quote_history, order_history] = await Promise.all([
    buildStock(pb, target, supplierNameById),
    buildPriceList(pb, target, supplierNameById),
    buildQuoteHistory(pb, target),
    buildOrderHistory(pb, target, supplierNameById),
  ]);

  // ── reshape to the operator-facing vertical layout ──────────────
  // Each section is an array. Empty array when nothing found.
  // One row per actionable record. No score noise (the rank only
  // matters to the algo; the operator wants the list).
  const stock_list = stock.matches.map((m) => ({
    sku: m.sku,
    name: m.name,
    description: m.description,
    qty: m.current_qty,
    cost_price: m.cost_price,
    selling_price: m.selling_price,
    supplier: m.supplier_name,
    status: m.status,
  }));
  // supplier_history = one row per unique supplier (latest purchase wins).
  // Source: the raw stock_purchase_items join; the derivation already
  // lives in `buildLastSupplierPrice` and is also used by the summary.
  const supplier_history = buildLastSupplierPrice(stock.purchase_history).map((s) => ({
    supplier: s.supplier_name,
    last_cost: s.last_cost,
    last_bought_at: s.last_bought_at,
    days_ago: s.days_ago,
    times_bought: s.times_bought,
    last_purchase_number: s.last_purchase_number,
  }));
  const recent_quote = quote_history.map((q) => ({
    quote_number: q.quote_number,
    quote_status: q.quote_status,
    quoted_at: q.quoted_at,
    days_ago: q.days_ago,
    quantity: q.quantity,
    cost_price: q.cost_price,
    selling_price: q.selling_price,
    description: q.description,
  }));
  const recent_order = order_history.map((o) => ({
    order_number: o.order_number,
    updated: o.updated,
    days_ago: o.days_ago,
    quantity: o.quantity,
    cost_price: o.cost_price,
    selling_price: o.selling_price,
    item_status: o.item_status,
    supplier: o.supplier_name,
    description: o.description,
  }));

  const summary = composeSummary({
    stock: { matches: stock.matches, purchase_history: stock.purchase_history },
    last_supplier_price: buildLastSupplierPrice(stock.purchase_history),
    quote_history,
    order_history,
    price_list,
  });

  const intel = {
    item: {
      id: itemId,
      collection,
      description: target.description || "",
      sku: target.sku || "",
      quantity: target.quantity || 0,
    },
    summary,
    generated_at,
    stock_list,
    supplier_history,
    price_list,
    recent_quote,
    recent_order,
  };

  if (write) {
    await pb.collection(collection).update(itemId, { ai_item_insights: intel });
  }
  return intel;
}

// ── runner ──────────────────────────────────────────────────────────
const TARGETS = [
  { id: "rar7m1d0m6jgeb5", desc: "M20 x 30 set screw black" },
  { id: "0zbkoxfdlz0t7mh", desc: "SAE 50 - 20L" },
  { id: "h5szkwdxs1u2quk", desc: "Silicone sealant clear" },
];

async function main() {
  console.log(`PB:      ${PB_URL}`);
  console.log(`Write:   ${WRITE ? "YES (WRITE_INTEL=1)" : "no (dry-run)"}`);
  console.log(`Targets: ${TARGETS.length} quote_items\n`);

  const pb = new PocketBase(PB_URL);
  pb.autoCancellation(false);
  await pb.collection("_superusers").authWithPassword(EMAIL, PASS);
  console.log("Auth OK\n");

  const out = [];
  for (const t of TARGETS) {
    const t0 = Date.now();
    console.log(`── ${t.desc} (${t.id}) ──`);
    try {
      const intel = await populateItemIntel(pb, t.id, "quote_items", { write: WRITE });
      const ms = Date.now() - t0;
      console.log(`  ${ms}ms · stock_list=${intel.stock_list.length} · supplier_history=${intel.supplier_history.length} · price_list=${intel.price_list.length} · recent_quote=${intel.recent_quote.length} · recent_order=${intel.recent_order.length}`);
      console.log(`  summary: ${intel.summary}`);
      out.push(intel);
    } catch (err) {
      console.error(`  FAILED:`, err?.stack || err?.message || err);
      out.push({ id: t.id, error: String(err?.message || err) });
    }
    console.log();
  }

  // full JSON
  console.log("════════════════════════════════════════════════════════");
  console.log("FULL JSON");
  console.log("════════════════════════════════════════════════════════");
  console.log(JSON.stringify(out, null, 2));
}

main().catch((err) => {
  console.error("FATAL:", err);
  process.exit(1);
});