import { and, eq, isNull } from "drizzle-orm";
import { db } from "@/db";
import { menuItemSbisLinks, menuItems, sbisCatalogItems } from "@/db/schema";

export type OrderLine = {
  sbis_id?: string | number | null;
  site_item_id?: string | number | null;
  name?: string | null;
  quantity?: number | null;
  unit_price?: number | null;
  line_total?: number | null;
  n?: string | null;
  q?: number | null;
  p?: number | null;
  s?: number | null;
  image_url?: string | null;
};

function key(value: unknown) {
  return String(value || "").trim().toLocaleLowerCase("ru-RU");
}

export function normalizeOrderLines(items: unknown): OrderLine[] {
  if (!Array.isArray(items)) return [];
  return items.filter((item): item is OrderLine => Boolean(item && typeof item === "object")).map((item) => {
    const name = item.name ?? item.n ?? null;
    const quantity = item.quantity ?? item.q ?? null;
    const unitPrice = item.unit_price ?? item.p ?? null;
    const lineTotal = item.line_total ?? item.s ?? null;
    return { ...item, sbis_id: item.sbis_id == null ? null : String(item.sbis_id), name, quantity, unit_price: unitPrice, line_total: lineTotal, n: name, q: quantity, p: unitPrice, s: lineTotal };
  });
}

export async function enrichOrderLines(items: unknown): Promise<OrderLine[]> {
  const lines = normalizeOrderLines(items);
  if (!db || !lines.length) return lines;
  let rows: { sbisId: string; siteItemId: number; localName: string; sbisName: string; imageUrl: string | null }[];
  try {
    rows = await db.select({ sbisId: menuItemSbisLinks.sbisId, siteItemId: menuItems.id, localName: menuItems.name, sbisName: sbisCatalogItems.name, imageUrl: menuItems.imageUrl })
      .from(menuItemSbisLinks)
      .innerJoin(menuItems, eq(menuItems.id, menuItemSbisLinks.menuItemId))
      .innerJoin(sbisCatalogItems, eq(sbisCatalogItems.sbisId, menuItemSbisLinks.sbisId))
      .where(and(isNull(menuItemSbisLinks.unlinkedAt), isNull(menuItems.archivedAt)));
  } catch {
    // Photos are optional presentation data and must not hide the order itself.
    return lines;
  }
  const byId = new Map(rows.map((row) => [row.sbisId, row]));
  const byName = new Map<string, string | null | undefined>();
  for (const row of rows) {
    for (const name of [row.localName, row.sbisName]) {
      const normalized = key(name);
      if (!normalized) continue;
      if (!byName.has(normalized)) byName.set(normalized, row.imageUrl);
      else if (byName.get(normalized) !== row.imageUrl) byName.set(normalized, undefined);
    }
  }
  return lines.map((line) => {
    const matched = line.sbis_id ? byId.get(String(line.sbis_id)) : undefined;
    const submittedName = String(line.name || "");
    const name = (!submittedName || /^Позиция СБИС \d+$/u.test(submittedName))
      ? matched?.localName || matched?.sbisName || line.name
      : line.name;
    return { ...line, site_item_id: line.site_item_id ?? matched?.siteItemId ?? null, name, n: name, image_url: matched?.imageUrl ?? byName.get(key(line.name)) ?? null };
  });
}