import path from "node:path";
import type { Db } from "../db.js";
import type { AppConfig } from "../config.js";
import { rootDir } from "../config.js";
import { buildOfferPackage, type OfferPackage } from "./offerPackage.js";
import { getFacts, type PublicFact } from "./facts.js";
import { baseColorForShade } from "./baseColor.js";
import {
  listVariants,
  listShades,
  parseFabricParams,
  type FabricRow,
  type VariantRow,
} from "./fabric.js";
import type { PreviewBase, PreviewItem } from "./baseContract.js";
import ExcelJS from "exceljs";

export class BrwExportError extends Error {}

const BRW_TEMPLATE_PATH = path.resolve(rootDir, "docs", "szablon_brw.xlsx");

const REQUIRED_PACKSHOTS = ["packshot_front", "packshot_34"] as const;
const PHOTOS_SLOT_ORDER = [
  "packshot_front",
  "packshot_34",
  "hero",
  "side",
  "back",
  "sleep",
  "storage",
  "detail",
] as const;

type BrwCellValue = string | number | boolean | null;
type BrwColumnMapper = (ctx: BrwRowContext) => BrwCellValue;
type BrwRowContext = {
  db: Db;
  cfg: AppConfig;
  productId: string;
  pkg: OfferPackage;
  facts: PublicFact[];
  base: PreviewBase;
  variant: VariantRow;
  item: PreviewItem;
  fabric: FabricRow;
  shadeName: string | null;
  shadeColorHex: string | null;
};

const BRW_COLUMN_MAP: Record<string, BrwColumnMapper> = {
  "pimcore-model-attribute-miraklCategory": () => "Meble/Meble wypoczynkowe/Narożniki",
  "product sku": (_ctx) => String(_ctx.item.sku ?? _ctx.item.parameters.sku ?? ""),
  "pimcore-model-attribute-name[pl]": (ctx) =>
    String(
      ctx.item.channelText?.allegro?.title ??
        ctx.item.parameters.text_fields?.name ??
        ""
    ),
  "pimcore-model-attribute-ean": (ctx) =>
    String(ctx.item.parameters.ean ?? ""),
  "pimcore-model-attribute-description[pl]": (ctx) => {
    const d = ctx.item.channelText?.allegro?.description;
    return typeof d === "string" ? d : "";
  },
  "pimcore-model-attribute-producer": (ctx) =>
    textFact(ctx.facts, "gpsr.manufacturer") ?? "Vilmax",
  "pimcore-classificationstore-key-marka": () => "Vilmax",
  "mirakl-idgrupywariantow": (ctx) => {
    const fromFacts = textFact(ctx.facts, "commerce.parent_sku");
    if (fromFacts) return fromFacts;
    return ctx.base.parent.parameters.sku ?? `VIL-GRP-${ctx.productId.slice(0, 6)}`;
  },
  "pimcore-classificationstore-key-nazwa_wzoru_wariantu": (ctx) => {
    const fabricLabel = ctx.fabric.name?.trim() ?? "";
    const shadeLabel =
      ctx.shadeName && ctx.shadeName.trim().length
        ? `${ctx.item.shade} ${ctx.shadeName.trim()}`
        : ctx.item.shade;
    return fabricLabel ? `${fabricLabel} ${shadeLabel}` : shadeLabel;
  },
  "pimcore-model-attribute-room": () => "Salon, jadalnia",
  "pimcore-model-attribute-style": () => "Nowoczesny",
  "pimcore-classificationstore-key-kolor": (ctx) =>
    baseColorForShade({
      name: ctx.shadeName,
      color_hex: ctx.shadeColorHex,
      code: ctx.item.shade,
    }),
  "pimcore-classificationstore-key-nazwa_koloru_dekoru": (ctx) => {
    const fabric = ctx.fabric.name?.trim() ?? "";
    const shade =
      ctx.shadeName && ctx.shadeName.trim().length
        ? `${ctx.item.shade} ${ctx.shadeName.trim()}`
        : ctx.item.shade;
    return fabric ? `${fabric} ${shade}` : shade;
  },
  "pimcore-classificationstore-key-mat_obicia": (ctx) => {
    const params = parseFabricParams(ctx.fabric.params_json);
    if (typeof params.structureType === "string" && params.structureType.length) {
      return params.structureType;
    }
    return "";
  },
  "pimcore-classificationstore-key-szerokosc_cm": (ctx) =>
    numFact(ctx.facts, "dimensions.width_cm") ??
    (typeof ctx.item.parameters.width === "number" ? ctx.item.parameters.width : null),
  "pimcore-classificationstore-key-glebokosc_cm": (ctx) =>
    numFact(ctx.facts, "dimensions.depth_cm") ??
    (typeof ctx.item.parameters.length === "number"
      ? ctx.item.parameters.length
      : null),
  "pimcore-classificationstore-key-wysokosc_cm": (ctx) =>
    numFact(ctx.facts, "dimensions.height_with_cushions_cm") ??
    (typeof ctx.item.parameters.height === "number"
      ? ctx.item.parameters.height
      : null),
  "pimcore-classificationstore-key-glebokosc_siedziska_cm": (ctx) =>
    numFact(ctx.facts, "dimensions.seat_depth_cm"),
  "pimcore-classificationstore-key-wysokosc_siedziska_cm": (ctx) =>
    numFact(ctx.facts, "dimensions.seat_height_cm"),
  "pimcore-classificationstore-key-wypelnienie_siedziska": (ctx) => {
    const fill = textFact(ctx.facts, "cushions.seat_fill");
    if (fill) return fill;
    const suspension = textFact(ctx.facts, "cushions.seat_suspension");
    if (suspension) return suspension;
    return "";
  },
  "pimcore-classificationstore-key-funkcja_spania": (ctx) => {
    const w = numFact(ctx.facts, "sleeping.width_cm");
    const l = numFact(ctx.facts, "sleeping.length_cm");
    return w !== null && l !== null ? "tak" : "nie";
  },
  "pimcore-classificationstore-key-szerokosc_powierzchni_spania_cm": (ctx) =>
    numFact(ctx.facts, "sleeping.width_cm"),
  "pimcore-classificationstore-key-dlugosc_powierzchni_spania_cm": (ctx) =>
    numFact(ctx.facts, "sleeping.length_cm"),
  "pimcore-classificationstore-key-sposob_rozkladania": (ctx) => {
    const m = textFact(ctx.facts, "mechanism.sleep");
    if (!m) return "";
    if (m === "seat_pullout_backfold") return "delfin";
    return m;
  },
  "pimcore-classificationstore-key-pojemnik_na_posciel": (ctx) =>
    boolFact(ctx.facts, "features.bedding_container") ? "tak" : "nie",
  "pimcore-classificationstore-key-podlokietnik": () => "tak",
  "pimcore-classificationstore-key-rodzaj_podlokietnika": (ctx) =>
    textFact(ctx.facts, "armrest.type") ?? "stały",
  "pimcore-classificationstore-key-poduszka_oparciowa": (ctx) => {
    const c = numFact(ctx.facts, "cushions.loose_back_count");
    return c !== null && c > 0 ? "tak" : "nie";
  },
  "pimcore-classificationstore-key-liczba_poduszek_oparciowych": (ctx) =>
    numFact(ctx.facts, "cushions.loose_back_count") ?? 0,
  "pimcore-classificationstore-key-poduszki_dekoracyjne": () => "nie",
  "pimcore-classificationstore-key-liczba_poduszek_dekoracyjnych": () => 0,
  "pimcore-classificationstore-key-zaglowek": () => "nie",
  "pimcore-classificationstore-key-rodzaj*narozniki": (ctx) => {
    if (ctx.item.side === "L") return "lewostronne";
    if (ctx.item.side === "P") return "prawostronne";
    return "";
  },
  "pimcore-classificationstore-key-kraj_pochodzenia": (ctx) =>
    textFact(ctx.facts, "production.country") ?? "Polska",
  "pimcore-classificationstore-key-tkanina": (ctx) => {
    const producer =
      typeof ctx.fabric.producer === "string" && ctx.fabric.producer.trim().length
        ? ` (${ctx.fabric.producer.trim()})`
        : "";
    return `${ctx.fabric.name ?? ""}${producer}`;
  },
  "pimcore-classificationstore-key-sklad_tkaniny": (ctx) => {
    const params = parseFabricParams(ctx.fabric.params_json);
    return typeof params.composition === "string" ? params.composition : "";
  },
  "pimcore-classificationstore-key-gramatura_tkaniny_g_m2": (ctx) => {
    const params = parseFabricParams(ctx.fabric.params_json);
    const g = params.grammage_g_m2 as
      | { value?: unknown; tolerance?: unknown }
      | undefined;
    if (typeof g?.value === "number") {
      if (typeof g.tolerance === "string" && g.tolerance.length) {
        return `${g.value} ${g.tolerance}`;
      }
      return g.value;
    }
    return null;
  },
  "pimcore-classificationstore-key-test_martindale_tkaniny_cykle": (ctx) => {
    const params = parseFabricParams(ctx.fabric.params_json);
    const m = params.martindale_cycles as
      | { min?: unknown; max?: unknown }
      | undefined;
    if (typeof m?.min === "number" && typeof m?.max === "number" && m.min <= m.max) {
      return m.min === m.max ? m.min : `${m.min}–${m.max}`;
    }
    return null;
  },
  "pimcore-classificationstore-key-strona": (ctx) => {
    if (ctx.item.side === "L") return "lewy";
    if (ctx.item.side === "P") return "prawy";
    return "";
  },
  "pimcore-classificationstore-key-montaz": (ctx) => {
    const a = textFact(ctx.facts, "assembly.by_customer");
    if (!a) return "meble do samodzielnego montażu";
    return a;
  },
  "pimcore-classificationstore-key-waga_brutto_kg": (ctx) => {
    const total = numFact(ctx.facts, "packages.total_weight_kg");
    if (total !== null) return total;
    return typeof ctx.item.parameters.weight === "number" ? ctx.item.parameters.weight : null;
  },
  "pimcore-classificationstore-key-liczba_paczek": (ctx) => {
    const v = factValue(ctx.facts, "packages.items");
    if (Array.isArray(v)) return v.length;
    return null;
  },
  "pimcore-classificationstore-key-gwarancja_miesiace": (ctx) =>
    numFact(ctx.facts, "warranty.months"),
  "sku": (_ctx) => String(_ctx.item.sku ?? _ctx.item.parameters.sku ?? ""),
  "product-id": (_ctx) => String(_ctx.item.sku ?? _ctx.item.parameters.sku ?? ""),
  "product-id-type": () => "SHOP_SKU",
  "price": (ctx) => {
    const prices = ctx.item.parameters.prices;
    if (Array.isArray(prices) && prices.length) {
      const first = prices[0];
      if (first && typeof first.grossPln === "number") return first.grossPln;
    }
    return numFact(ctx.facts, "commerce.price_pln");
  },
  "quantity": (ctx) => {
    const stock = ctx.item.parameters.stock;
    if (Array.isArray(stock) && stock.length) {
      const first = stock[0];
      if (first && typeof first.quantity === "number") return first.quantity;
    }
    return numFact(ctx.facts, "commerce.stock_quantity");
  },
  "state": () => "Nowy",
  "leadtime-to-ship": () => 14,
  "logistic-class": () => "",
};

const PHOTO_COLUMNS = Array.from({ length: 15 }, (_, i) => i + 1).map((n) => {
  const key = `pimcore-model-attribute-photos_${n}`;
  const slotIndex = n - 1;
  const mapper: BrwColumnMapper = (ctx) => {
    const slot = PHOTOS_SLOT_ORDER[slotIndex];
    if (!slot) return null;
    const rendition = ctx.pkg.renditions.items.find(
      (r) => r.variantId === ctx.item.variantId && r.role === slot && r.rendition
    );
    if (!rendition || !rendition.rendition) return null;
    return `/api/renditions/${rendition.rendition.id}`;
  };
  return { key, mapper };
});

for (const p of PHOTO_COLUMNS) {
  BRW_COLUMN_MAP[p.key] = p.mapper;
}

function factValue(facts: PublicFact[], key: string): unknown {
  return facts.find(
    (f) => f.key === key && f.status === "confirmed" && !!f.sourceRef
  )?.value;
}
function textFact(facts: PublicFact[], key: string): string | null {
  const v = factValue(facts, key);
  return typeof v === "string" && v.trim().length ? v.trim() : null;
}
function numFact(facts: PublicFact[], key: string): number | null {
  const v = factValue(facts, key);
  return typeof v === "number" && Number.isFinite(v) && v >= 0 ? v : null;
}
function boolFact(facts: PublicFact[], key: string): boolean {
  return factValue(facts, key) === true;
}

function buildRow(ctx: BrwRowContext, miraklColumns: string[]): Array<BrwCellValue> {
  return miraklColumns.map((col) => {
    const fn = BRW_COLUMN_MAP[col];
    if (!fn) return null;
    try {
      return fn(ctx) ?? null;
    } catch {
      return null;
    }
  });
}

function validate(
  ctx: BrwRowContext
): { blockers: string[]; warnings: string[] } {
  const blockers: string[] = [];
  const warnings: string[] = [];

  if (!ctx.item.sku) blockers.push(`Wariant ${ctx.item.side}/${ctx.item.shade}: brak SKU.`);
  if (!ctx.item.parameters.ean) blockers.push(`Wariant ${ctx.item.side}/${ctx.item.shade}: brak EAN.`);

  const name = String(
    ctx.item.channelText?.allegro?.title ?? ctx.item.parameters.text_fields?.name ?? ""
  );
  if (!name.length) blockers.push(`Wariant ${ctx.item.side}/${ctx.item.shade}: brak tytułu.`);

  const desc = ctx.item.channelText?.allegro?.description;
  if (!desc || typeof desc !== "string" || !desc.length) {
    blockers.push(`Wariant ${ctx.item.side}/${ctx.item.shade}: brak opisu.`);
  }

  if (ctx.fabric.verification !== "confirmed") {
    warnings.push(
      `Wariant ${ctx.item.side}/${ctx.item.shade}: tkanina ${ctx.fabric.code} nie ma statusu confirmed.`
    );
  }
  if (!ctx.shadeName) {
    warnings.push(
      `Wariant ${ctx.item.side}/${ctx.item.shade}: nazwa odcienia niepotwierdzona.`
    );
  }

  for (const role of REQUIRED_PACKSHOTS) {
    const r = ctx.pkg.renditions.items.find(
      (r) => r.variantId === ctx.item.variantId && r.role === role && r.rendition
    );
    if (!r || !r.rendition) {
      blockers.push(
        `Wariant ${ctx.item.side}/${ctx.item.shade}: brak rendycji ${role} (wymagane photos_1/photos_2 w BRW).`
      );
    }
  }

  return { blockers, warnings };
}

export type BrwPreview = {
  variantCount: number;
  blockers: string[];
  warnings: string[];
  columns: string[];
  sampleRow: Array<{ column: string; value: BrwCellValue }>;
};

export async function buildBrwWorkbook(
  db: Db,
  cfg: AppConfig,
  productId: string
): Promise<{
  buffer: Buffer;
  variantCount: number;
  blockers: string[];
  warnings: string[];
  columns: string[];
}> {
  const pkg = buildOfferPackage(db, cfg, productId);
  const base = pkg.base as PreviewBase;
  if (!base.variants.length) {
    throw new BrwExportError("Produkt nie ma wariantów gotowych do BRW.");
  }
  const facts = getFacts(db, productId);
  const variants = listVariants(db, productId);

  let workbook: ExcelJS.Workbook;
  try {
    workbook = new ExcelJS.Workbook();
    await workbook.xlsx.readFile(BRW_TEMPLATE_PATH);
  } catch (err) {
    throw new BrwExportError(
      `Nie udało się odczytać szablonu BRW (${BRW_TEMPLATE_PATH}): ${err instanceof Error ? err.message : String(err)}`
    );
  }

  const dataSheet = workbook.worksheets.find((w) => w.name === "Data");
  if (!dataSheet) {
    throw new BrwExportError('Szablon BRW nie zawiera arkusza "Data".');
  }

  const headerRow = dataSheet.getRow(2);
  const miraklColumns: string[] = [];
  headerRow.eachCell((cell) => {
    miraklColumns.push(typeof cell.value === "string" ? cell.value : "");
  });
  if (miraklColumns.length === 0 || miraklColumns.every((c) => !c.length)) {
    throw new BrwExportError(
      'Nie odnaleziono nagłówka kolumn w wierszu 2 arkusza "Data".'
    );
  }

  const allBlockers: string[] = [];
  const allWarnings: string[] = [];
  let dataRowIndex = 3;

  for (const item of base.variants) {
    const variant = variants.find((v) => v.id === item.variantId);
    if (!variant) continue;
    const fabric = db
      .prepare("SELECT * FROM fabrics WHERE id = ?")
      .get(variant.fabric_id) as unknown as FabricRow | undefined;
    if (!fabric) continue;
    const shade = listShades(db, variant.fabric_id).find(
      (s) => s.code === variant.shade_code
    );
    const shadeName = shade?.verification === "confirmed" ? shade.name ?? null : null;
    const shadeColorHex = shade?.color_hex ?? null;

    const ctx: BrwRowContext = {
      db,
      cfg,
      productId,
      pkg,
      facts,
      base,
      variant,
      item,
      fabric,
      shadeName,
      shadeColorHex,
    };

    const { blockers, warnings } = validate(ctx);
    allBlockers.push(...blockers);
    allWarnings.push(...warnings);

    const row = buildRow(ctx, miraklColumns);
    const xlRow = dataSheet.getRow(dataRowIndex);
    xlRow.values = row.map((v) => (v === null ? undefined : v));
    dataRowIndex += 1;
  }

  const buffer = (await workbook.xlsx.writeBuffer()) as unknown as Buffer;
  return {
    buffer,
    variantCount: base.variants.length,
    blockers: Array.from(new Set(allBlockers)).sort(),
    warnings: Array.from(new Set(allWarnings)).sort(),
    columns: miraklColumns.filter((c) => c.length > 0),
  };
}

export async function previewBrwExport(
  db: Db,
  cfg: AppConfig,
  productId: string
): Promise<BrwPreview> {
  const { variantCount, blockers, warnings, columns } = await buildBrwWorkbook(
    db,
    cfg,
    productId
  );
  const pkg = buildOfferPackage(db, cfg, productId);
  const base = pkg.base as PreviewBase;
  const first = base.variants[0];
  let sampleRow: BrwPreview["sampleRow"] = [];
  if (first) {
    const facts = getFacts(db, productId);
    const variants = listVariants(db, productId);
    const variant = variants.find((v) => v.id === first.variantId);
    const fabric = variant
      ? (db.prepare("SELECT * FROM fabrics WHERE id = ?").get(variant.fabric_id) as
          | FabricRow
          | undefined)
      : undefined;
    const shade =
      variant && fabric
        ? listShades(db, variant.fabric_id).find((s) => s.code === variant.shade_code)
        : undefined;
    if (variant && fabric) {
      const ctx: BrwRowContext = {
        db,
        cfg,
        productId,
        pkg,
        facts,
        base,
        variant,
        item: first,
        fabric,
        shadeName: shade?.verification === "confirmed" ? shade.name ?? null : null,
        shadeColorHex: shade?.color_hex ?? null,
      };
      sampleRow = columns.map((c) => {
        const fn = BRW_COLUMN_MAP[c];
        let value: BrwCellValue = null;
        if (fn) {
          try {
            value = fn(ctx) ?? null;
          } catch {
            value = null;
          }
        }
        return { column: c, value };
      });
    }
  }
  return { variantCount, blockers, warnings, columns, sampleRow };
}