/**
 * CUMAS365 CRM Read-only Client.
 *
 * Auth: POST /api/login_check { username, password } → JWT (Auto-Refresh 5 Min vor Expiry).
 * Drei API-Ressourcen (alle GET, application/ld+json, IriTemplate-konform):
 *   /api/v1/its_business_datas   — Geschäftskunden (~143k Records)
 *   /api/v1/evbs                 — Versicherungen (~380k Records)
 *   /api/v1/order_private_shops  — Privat-Bestellungen (~52k Records)
 *
 * WICHTIG — Server-Side vs. Client-Side Filter:
 * Die API listet im IriTemplate offiziell nur eine kleine Untermenge der Felder
 * als filterbar. Felder, die nur im Datensatz selbst auftauchen (its_ksm, ort,
 * branche, briefstatus, kzk-status, ...), filtern wir CLIENT-SEITIG nach dem
 * Fetch. Das ist robust und bricht keine API-Verträge.
 */

const BASE_URL = process.env.CRM_BASE_URL ?? 'https://nova.cumas365.com';

let cachedToken: string | null = null;
let tokenExpiresAt = 0;

async function getToken(): Promise<string> {
  const username = process.env.CRM_USERNAME;
  const password = process.env.CRM_PASSWORD;
  if (!username || !password) {
    throw new Error('CRM_USERNAME und CRM_PASSWORD fehlen in .env.local');
  }
  const nowSec = Math.floor(Date.now() / 1000);
  if (!cachedToken || nowSec >= tokenExpiresAt - 300) {
    const res = await fetch(`${BASE_URL}/api/login_check`, {
      method: 'POST',
      headers: { 'Content-Type': 'application/json', Accept: 'application/json' },
      body: JSON.stringify({ username, password }),
    });
    if (!res.ok) throw new Error(`Cumas Login HTTP ${res.status}`);
    const data = (await res.json()) as { token: string };
    if (!data.token) throw new Error('Cumas Login: kein Token in Response');
    cachedToken = data.token;
    try {
      const payload = JSON.parse(Buffer.from(data.token.split('.')[1], 'base64url').toString('utf8'));
      tokenExpiresAt = payload.exp ?? nowSec + 3600;
    } catch {
      tokenExpiresAt = nowSec + 3600;
    }
  }
  return cachedToken;
}

async function apiGet<T = unknown>(path: string, params?: Record<string, string | number | string[]>): Promise<T> {
  const token = await getToken();
  const url = new URL(`${BASE_URL}${path}`);
  if (params) {
    for (const [k, v] of Object.entries(params)) {
      if (v === undefined || v === '' || v === null) continue;
      if (Array.isArray(v)) {
        for (const item of v) url.searchParams.append(`${k}[]`, String(item));
      } else {
        url.searchParams.set(k, String(v));
      }
    }
  }
  const res = await fetch(url.toString(), {
    method: 'GET',
    headers: { Authorization: `Bearer ${token}`, Accept: 'application/ld+json' },
  });
  if (!res.ok) {
    const txt = await res.text().catch(() => '');
    throw new Error(`Cumas API ${res.status} ${url.pathname}: ${txt.slice(0, 200)}`);
  }
  return (await res.json()) as T;
}

// ─────────────────────────────────────────────────────────────────────────────
// Domain-Modelle (subset relevant für Marketing-Versand)
// ─────────────────────────────────────────────────────────────────────────────

export interface CumasBusinessRecord {
  '@id'?: string;
  id?: number;
  firma?: string | null;
  its_ksm?: string | null;
  plz?: string | null;
  ort?: string | null;
  strasse?: string | null;
  tel1?: string | null;
  email?: string | null;
  website?: string | null;
  branche?: string | null;
  its_access_id?: number | null;
  vorname_k1?: string | null;
  nachname_k1?: string | null;
  anrede_k1?: string | null;
  position_k1?: string | null;
  comment?: string | null;
  history?: string | null;
  special_features?: string | null;
  vshv_eigen?: boolean | null;
  vshv_fremd?: boolean | null;
  reseller?: string | null;
  its_nr?: number | null;
  ksm_nr?: number | null;
  spa_nr?: number | null;
  debitoren_its?: number | null;
  debitoren_ksm?: number | null;
  debitoren_spa?: number | null;
  update_datetime?: string | null;
}

export interface CumasEvbRecord {
  '@id'?: string;
  id?: number;
  evbnr?: string | null;
  kartennr?: number | null;
  typ?: 'KZK' | 'KZKA' | string | null;
  gesellschaft?: string | null;
  praefix?: string | null;
  suffix?: string | null;
  vkverst?: string | null;
  vknetto?: string | null;
  gueltigkeitsdauer?: number | null;
  storno?: boolean | null;
  umtausch?: boolean | null;
  aktiviert?: boolean | null;
  bezahlt?: boolean | null;
  reseller?: string | null;
  idkdnr?: number | null;
  idkdnr2?: number | null;
  idvermittler?: string | null;
  rechnr?: string | null;
  rechdatum?: string | null;
  aktivierbarbis?: string | null;
  activationdate?: string | null;
}

export interface CumasOrderRecord {
  '@id'?: string;
  id?: number;
  reference_number?: string | null;
  product?: string | null;
  client_name?: string | null;
  email?: string | null;
  address?: string | null;
  total?: string | null;
  reg_datetime?: string | null;
  invoice_number?: string | null;
  evbs?: unknown[] | null;
}

export interface CumasListResponse<T> {
  '@context'?: string;
  '@id'?: string;
  '@type'?: string;
  'hydra:member'?: T[];
  'hydra:totalItems'?: number;
  'hydra:view'?: { 'hydra:next'?: string };
  member?: T[];
  totalItems?: number;
  view?: { next?: string };
}

// ─────────────────────────────────────────────────────────────────────────────
// Filter-Schemata: Server-Side (IriTemplate) vs. Client-Side (Post-Fetch)
// ─────────────────────────────────────────────────────────────────────────────

/** Business — Felder, die das offizielle IriTemplate listet. */
export interface BusinessServerFilters {
  page?: number;
  itemsPerPage?: number;
  firma?: string;
  plz?: string;
  strasse?: string;
  email?: string;
  itsAccessId?: number;
  itsNr?: number;
  ksmNr?: number;
  spaNr?: number;
  idkdnr?: number;
}

/** Business — Felder, die wir post-fetch filtern (nicht im IriTemplate). */
export interface BusinessClientFilters {
  itsKsmIn?: string[];
  ortContains?: string;
  brancheContains?: string;
  plzPrefix?: string;
  hasEmail?: boolean;
  vshvEigen?: boolean;
  vshvFremd?: boolean;
  resellerIn?: string[];
  briefstatus?: string[];
  kzkStatus?: string[];
  kundengroesse?: string[];
  statusCodes?: string[];
  excludeStatusCodes?: string[];
}

/** EVB — IriTemplate-Felder. */
export interface EvbServerFilters {
  page?: number;
  itemsPerPage?: number;
  evbnr?: string;
  rechnr?: string;
  idkdnr?: number;
  itsBusinessData?: number;
  orderPrivateShop?: number;
  rechdatumAfter?: string;
  rechdatumBefore?: string;
}

/** EVB — Post-Fetch-Felder. */
export interface EvbClientFilters {
  typIn?: string[];
  gesellschaftIn?: string[];
  resellerIn?: string[];
  storno?: boolean;
  aktiviert?: boolean;
  bezahlt?: boolean;
  vkverstMin?: number;
  vkverstMax?: number;
}

/** Order — IriTemplate-Felder. */
export interface OrderServerFilters {
  page?: number;
  itemsPerPage?: number;
  invoiceNumber?: string;
  email?: string;
  clientName?: string;
  regDatetimeAfter?: string;
  regDatetimeBefore?: string;
}

/** Order — Post-Fetch-Felder. */
export interface OrderClientFilters {
  productContains?: string;
  hasEvbs?: boolean;
  totalMin?: number;
  totalMax?: number;
}

// ─────────────────────────────────────────────────────────────────────────────
// Param-Builder (TypeScript-Naming → Cumas-API-Naming)
// ─────────────────────────────────────────────────────────────────────────────

function buildBusinessParams(f: BusinessServerFilters): Record<string, string | number> {
  const p: Record<string, string | number> = {
    page: f.page ?? 1,
    itemsPerPage: f.itemsPerPage ?? 100,
  };
  if (f.firma) p.firma = f.firma;
  if (f.plz) p.plz = f.plz;
  if (f.strasse) p.strasse = f.strasse;
  if (f.email) p.email = f.email;
  if (f.itsAccessId !== undefined) p.its_access_id = f.itsAccessId;
  if (f.itsNr !== undefined) p.its_nr = f.itsNr;
  if (f.ksmNr !== undefined) p.ksm_nr = f.ksmNr;
  if (f.spaNr !== undefined) p.spa_nr = f.spaNr;
  if (f.idkdnr !== undefined) p.idkdnr = f.idkdnr;
  return p;
}

function buildEvbParams(f: EvbServerFilters): Record<string, string | number> {
  const p: Record<string, string | number> = {
    page: f.page ?? 1,
    itemsPerPage: f.itemsPerPage ?? 100,
  };
  if (f.evbnr) p.evbnr = f.evbnr;
  if (f.rechnr) p.rechnr = f.rechnr;
  if (f.idkdnr !== undefined) p.idkdnr = f.idkdnr;
  if (f.itsBusinessData !== undefined) p.its_business_data = f.itsBusinessData;
  if (f.orderPrivateShop !== undefined) p.order_private_shop = f.orderPrivateShop;
  if (f.rechdatumAfter) p['rechdatum[after]'] = f.rechdatumAfter;
  if (f.rechdatumBefore) p['rechdatum[before]'] = f.rechdatumBefore;
  return p;
}

function buildOrderParams(f: OrderServerFilters): Record<string, string | number> {
  const p: Record<string, string | number> = {
    page: f.page ?? 1,
    itemsPerPage: f.itemsPerPage ?? 100,
  };
  if (f.invoiceNumber) p.invoice_number = f.invoiceNumber;
  if (f.email) p.email = f.email;
  if (f.clientName) p.client_name = f.clientName;
  if (f.regDatetimeAfter) p['reg_datetime[after]'] = f.regDatetimeAfter;
  if (f.regDatetimeBefore) p['reg_datetime[before]'] = f.regDatetimeBefore;
  return p;
}

// ─────────────────────────────────────────────────────────────────────────────
// Low-Level List-Funktionen (eine Page)
// ─────────────────────────────────────────────────────────────────────────────

function unwrapList<T>(data: CumasListResponse<T>): { records: T[]; total: number; hasMore: boolean } {
  const records = data['hydra:member'] ?? data.member ?? [];
  const total = data['hydra:totalItems'] ?? data.totalItems ?? records.length;
  const hasMore = !!(data['hydra:view']?.['hydra:next'] ?? data.view?.next);
  return { records, total, hasMore };
}

export async function listBusiness(server: BusinessServerFilters = {}) {
  const data = await apiGet<CumasListResponse<CumasBusinessRecord>>(
    '/api/v1/its_business_datas',
    buildBusinessParams(server),
  );
  return unwrapList(data);
}

export async function listEvbs(server: EvbServerFilters = {}) {
  const data = await apiGet<CumasListResponse<CumasEvbRecord>>('/api/v1/evbs', buildEvbParams(server));
  return unwrapList(data);
}

export async function listOrders(server: OrderServerFilters = {}) {
  const data = await apiGet<CumasListResponse<CumasOrderRecord>>(
    '/api/v1/order_private_shops',
    buildOrderParams(server),
  );
  return unwrapList(data);
}

// ─────────────────────────────────────────────────────────────────────────────
// Client-Side Post-Filter (pure functions)
// ─────────────────────────────────────────────────────────────────────────────

const containsCi = (haystack: string | null | undefined, needle: string) =>
  !!haystack && haystack.toLowerCase().includes(needle.toLowerCase());

/** Extrahiert Status-Code-Anker aus einem Comment-String (z.B. "60", "11", "75"). */
function extractStatusCodes(text: string | null | undefined): string[] {
  if (!text) return [];
  const matches = text.match(/\b\d{2}\b/g);
  return matches ?? [];
}

export function applyBusinessClientFilters(
  records: CumasBusinessRecord[],
  f: BusinessClientFilters,
): CumasBusinessRecord[] {
  return records.filter((r) => {
    if (f.hasEmail && !r.email) return false;
    if (f.itsKsmIn?.length && (!r.its_ksm || !f.itsKsmIn.includes(r.its_ksm))) return false;
    if (f.ortContains && !containsCi(r.ort, f.ortContains)) return false;
    if (f.brancheContains && !containsCi(r.branche, f.brancheContains)) return false;
    if (f.plzPrefix) {
      // Cumas-PLZ-Format: "D  40589" → trim + strip Land-Prefix
      const plzDigits = (r.plz ?? '').replace(/\D/g, '');
      if (!plzDigits.startsWith(f.plzPrefix)) return false;
    }
    if (f.vshvEigen !== undefined && r.vshv_eigen !== f.vshvEigen) return false;
    if (f.vshvFremd !== undefined && r.vshv_fremd !== f.vshvFremd) return false;
    if (f.resellerIn?.length && (!r.reseller || !f.resellerIn.includes(r.reseller))) return false;

    // Status-Code-Filter (Heuristik: extrahiere 2-stellige Codes aus comment+history+special_features)
    if (f.briefstatus?.length || f.kzkStatus?.length || f.kundengroesse?.length || f.statusCodes?.length || f.excludeStatusCodes?.length) {
      const blob = `${r.comment ?? ''}\n${r.history ?? ''}\n${r.special_features ?? ''}`;
      const codes = new Set(extractStatusCodes(blob));
      if (f.briefstatus?.length && !f.briefstatus.some((c) => codes.has(c))) return false;
      if (f.kzkStatus?.length && !f.kzkStatus.some((c) => codes.has(c))) return false;
      if (f.kundengroesse?.length && !f.kundengroesse.some((c) => codes.has(c))) return false;
      if (f.statusCodes?.length && !f.statusCodes.some((c) => codes.has(c))) return false;
      if (f.excludeStatusCodes?.length && f.excludeStatusCodes.some((c) => codes.has(c))) return false;
    }
    return true;
  });
}

export function applyEvbClientFilters(records: CumasEvbRecord[], f: EvbClientFilters): CumasEvbRecord[] {
  return records.filter((r) => {
    if (f.typIn?.length && (!r.typ || !f.typIn.includes(r.typ))) return false;
    if (f.gesellschaftIn?.length && (!r.gesellschaft || !f.gesellschaftIn.includes(r.gesellschaft))) return false;
    if (f.resellerIn?.length && (!r.reseller || !f.resellerIn.includes(r.reseller))) return false;
    if (f.storno !== undefined && r.storno !== f.storno) return false;
    if (f.aktiviert !== undefined && r.aktiviert !== f.aktiviert) return false;
    if (f.bezahlt !== undefined && r.bezahlt !== f.bezahlt) return false;
    if (f.vkverstMin !== undefined || f.vkverstMax !== undefined) {
      const v = parseFloat(r.vkverst ?? '0');
      if (f.vkverstMin !== undefined && v < f.vkverstMin) return false;
      if (f.vkverstMax !== undefined && v > f.vkverstMax) return false;
    }
    return true;
  });
}

export function applyOrderClientFilters(records: CumasOrderRecord[], f: OrderClientFilters): CumasOrderRecord[] {
  return records.filter((r) => {
    if (f.productContains && !containsCi(r.product, f.productContains)) return false;
    if (f.hasEvbs !== undefined) {
      const has = !!r.evbs && r.evbs.length > 0;
      if (has !== f.hasEvbs) return false;
    }
    if (f.totalMin !== undefined || f.totalMax !== undefined) {
      const t = parseFloat(r.total ?? '0');
      if (f.totalMin !== undefined && t < f.totalMin) return false;
      if (f.totalMax !== undefined && t > f.totalMax) return false;
    }
    return true;
  });
}

// ─────────────────────────────────────────────────────────────────────────────
// Audience-Builder (paginiert + dedupliziert auf Email)
// ─────────────────────────────────────────────────────────────────────────────

export interface AudienceRecipient {
  email: string;
  firstName?: string;
  lastName?: string;
  fullName?: string;
  company?: string;
  source: 'business' | 'orders' | 'evb';
  cumasId?: number;
  rawRecord: CumasBusinessRecord | CumasOrderRecord;
}

const EMAIL_RX = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
const MAX_PAGES = 50; // Hard cap: 50 Pages × 100 = 5000 records gescannt — verhindert Endlos-Loops bei strikten Client-Filtern

function pushBusinessRecipient(
  rec: CumasBusinessRecord,
  seen: Set<string>,
  recipients: AudienceRecipient[],
): void {
  const email = rec.email?.trim().toLowerCase();
  if (!email || !EMAIL_RX.test(email) || seen.has(email)) return;
  seen.add(email);
  const firstName = rec.vorname_k1?.trim() || undefined;
  const lastName = rec.nachname_k1?.trim() || undefined;
  recipients.push({
    email,
    firstName,
    lastName,
    fullName: [firstName, lastName].filter(Boolean).join(' ') || undefined,
    company: rec.firma ?? undefined,
    source: 'business',
    cumasId: rec.id,
    rawRecord: rec,
  });
}

function pushOrderRecipient(
  rec: CumasOrderRecord,
  seen: Set<string>,
  recipients: AudienceRecipient[],
): void {
  const email = rec.email?.trim().toLowerCase();
  if (!email || !EMAIL_RX.test(email) || seen.has(email)) return;
  seen.add(email);
  const fullName = rec.client_name?.trim();
  const [firstName, ...rest] = (fullName ?? '').split(' ');
  recipients.push({
    email,
    firstName: firstName || undefined,
    lastName: rest.join(' ') || undefined,
    fullName: fullName ?? undefined,
    source: 'orders',
    cumasId: rec.id,
    rawRecord: rec,
  });
}

export async function buildBusinessAudience(opts: {
  server?: BusinessServerFilters;
  client?: BusinessClientFilters;
  maxRecords?: number;
}): Promise<{ recipients: AudienceRecipient[]; totalScanned: number; totalAvailable: number }> {
  const max = opts.maxRecords ?? 200;
  const client: BusinessClientFilters = { hasEmail: true, ...opts.client };
  const seen = new Set<string>();
  const recipients: AudienceRecipient[] = [];
  let scanned = 0;
  let total = 0;
  for (let page = 1; page <= MAX_PAGES && recipients.length < max; page++) {
    const r = await listBusiness({ ...opts.server, page, itemsPerPage: 100 });
    total = r.total;
    scanned += r.records.length;
    const filtered = applyBusinessClientFilters(r.records, client);
    for (const rec of filtered) {
      if (recipients.length >= max) break;
      pushBusinessRecipient(rec, seen, recipients);
    }
    if (!r.hasMore) break;
  }
  return { recipients, totalScanned: scanned, totalAvailable: total };
}

export async function buildOrderAudience(opts: {
  server?: OrderServerFilters;
  client?: OrderClientFilters;
  maxRecords?: number;
}): Promise<{ recipients: AudienceRecipient[]; totalScanned: number; totalAvailable: number }> {
  const max = opts.maxRecords ?? 200;
  const seen = new Set<string>();
  const recipients: AudienceRecipient[] = [];
  let scanned = 0;
  let total = 0;
  for (let page = 1; page <= MAX_PAGES && recipients.length < max; page++) {
    const r = await listOrders({ ...opts.server, page, itemsPerPage: 100 });
    total = r.total;
    scanned += r.records.length;
    const filtered = applyOrderClientFilters(r.records, opts.client ?? {});
    for (const rec of filtered) {
      if (recipients.length >= max) break;
      pushOrderRecipient(rec, seen, recipients);
    }
    if (!r.hasMore) break;
  }
  return { recipients, totalScanned: scanned, totalAvailable: total };
}

/**
 * EVB-Audience: filtert EVBs, sammelt verknüpfte Business-Customer-IDs, lädt deren
 * Business-Records und baut daraus die Recipient-Liste mit Emails.
 */
export async function buildEvbAudience(opts: {
  server?: EvbServerFilters;
  client?: EvbClientFilters;
  maxRecords?: number;
}): Promise<{ recipients: AudienceRecipient[]; totalScanned: number; totalAvailable: number; matchedEvbs: number }> {
  const max = opts.maxRecords ?? 200;
  const seenIds = new Set<number>();
  const matchedIds: number[] = [];
  let scanned = 0;
  let total = 0;
  let matchedEvbs = 0;

  // 1. EVBs scannen, distinct idkdnr (oder idkdnr2 fallback) sammeln
  for (let page = 1; page <= MAX_PAGES && matchedIds.length < max; page++) {
    const r = await listEvbs({ ...opts.server, page, itemsPerPage: 100 });
    total = r.total;
    scanned += r.records.length;
    const filtered = applyEvbClientFilters(r.records, opts.client ?? {});
    matchedEvbs += filtered.length;
    for (const evb of filtered) {
      const id = evb.idkdnr ?? evb.idkdnr2 ?? undefined;
      if (id !== undefined && id > 0 && !seenIds.has(id)) {
        seenIds.add(id);
        matchedIds.push(id);
        if (matchedIds.length >= max) break;
      }
    }
    if (!r.hasMore) break;
  }

  // 2. Business-Records für die gesammelten IDs holen (Batches à 50, IriTemplate `idkdnr[]=`)
  const seenEmails = new Set<string>();
  const recipients: AudienceRecipient[] = [];
  const BATCH = 50;
  for (let i = 0; i < matchedIds.length && recipients.length < max; i += BATCH) {
    const batch = matchedIds.slice(i, i + BATCH);
    const data = await apiGet<CumasListResponse<CumasBusinessRecord>>('/api/v1/its_business_datas', {
      itemsPerPage: 100,
      idkdnr: batch.map(String),
    });
    const records = data['hydra:member'] ?? data.member ?? [];
    for (const rec of records) {
      if (recipients.length >= max) break;
      pushBusinessRecipient(rec, seenEmails, recipients);
    }
  }

  // Source umlabeln auf 'evb' damit der Caller weiß, woher es kam
  for (const r of recipients) r.source = 'evb';

  return { recipients, totalScanned: scanned, totalAvailable: total, matchedEvbs };
}

// ─────────────────────────────────────────────────────────────────────────────
// Facets-Discovery (für UI-Multi-Selects)
// ─────────────────────────────────────────────────────────────────────────────

export interface CumasFacets {
  businessItsKsm: string[];
  businessReseller: string[];
  evbTyp: string[];
  evbGesellschaft: string[];
  evbReseller: string[];
  orderProducts: string[];
  cachedAt: string;
}

// Cache-Map: pageCount → { value, expiresAt }. Damit kann der UI-Inkrement-Flow
// (pages=5 → pages=6 → pages=7 …) jede Stufe einzeln cachen, ohne dass kleinere
// Anfragen den teuren großen Cache invalidieren.
const facetsCache = new Map<number, { value: CumasFacets; expiresAt: number }>();
const FACETS_TTL_MS = 60 * 60 * 1000;
const FACETS_DEFAULT_PAGES = 5;

export interface SampleOpts {
  /** Anzahl der zu samplenden Pages (à 100 Records). Default: 5 für Facets, 1 für Schema. */
  pages?: number;
  /** Wenn true: Cache-Slot für diesen `pages`-Wert ignorieren und neu fetchen. */
  refresh?: boolean;
}

export async function getFacets(opts: SampleOpts = {}): Promise<CumasFacets> {
  const pages = Math.max(1, Math.min(50, opts.pages ?? FACETS_DEFAULT_PAGES));
  const now = Date.now();
  if (!opts.refresh) {
    const cached = facetsCache.get(pages);
    if (cached && now < cached.expiresAt) return cached.value;
  }

  const businessItsKsm = new Set<string>();
  const businessReseller = new Set<string>();
  const evbTyp = new Set<string>();
  const evbGesellschaft = new Set<string>();
  const evbReseller = new Set<string>();
  const orderProducts = new Set<string>();

  for (let page = 1; page <= pages; page++) {
    const [b, e, o] = await Promise.all([
      listBusiness({ page, itemsPerPage: 100 }),
      listEvbs({ page, itemsPerPage: 100 }),
      listOrders({ page, itemsPerPage: 100 }),
    ]);
    for (const r of b.records) {
      if (r.its_ksm) businessItsKsm.add(r.its_ksm);
      if (r.reseller) businessReseller.add(r.reseller);
    }
    for (const r of e.records) {
      if (r.typ) evbTyp.add(r.typ);
      if (r.gesellschaft) evbGesellschaft.add(r.gesellschaft);
      if (r.reseller) evbReseller.add(r.reseller);
    }
    for (const r of o.records) {
      if (r.product) orderProducts.add(r.product);
    }
  }

  const value: CumasFacets = {
    businessItsKsm: [...businessItsKsm].sort(),
    businessReseller: [...businessReseller].filter(Boolean).sort(),
    evbTyp: [...evbTyp].sort(),
    evbGesellschaft: [...evbGesellschaft].sort(),
    evbReseller: [...evbReseller].filter(Boolean).sort(),
    orderProducts: [...orderProducts].sort(),
    cachedAt: new Date().toISOString(),
  };
  facetsCache.set(pages, { value, expiresAt: now + FACETS_TTL_MS });
  return value;
}

export const FACETS_CONST = { defaultPages: FACETS_DEFAULT_PAGES, recordsPerPage: 100 } as const;

export function isCumasConfigured(): boolean {
  return !!process.env.CRM_USERNAME && !!process.env.CRM_PASSWORD;
}

// ─────────────────────────────────────────────────────────────────────────────
// Schema-Introspection — sampled 100 records per table, derives Type + Cardinalities
// ─────────────────────────────────────────────────────────────────────────────

export type ColumnKind = 'string' | 'number' | 'enum' | 'date' | 'boolean' | 'unknown';

export interface ColumnInfo {
  /** Column-Key wie er von der Cumas-API kommt (snake_case, z.B. 'its_ksm', 'vorname_k1') */
  name: string;
  kind: ColumnKind;
  /** Wieviele der gesampelten Records haben einen Nicht-Null-Wert in dieser Spalte */
  filledCount: number;
  totalSampled: number;
  /** Top-distinkte Werte mit Counts (max 20 Einträge, sortiert nach Count desc). Bei `kind=string` mit hoher Cardinality leer. */
  topValues: Array<{ value: string; count: number }>;
  /** Bei kind='number'/'date' — Min/Max-Range */
  min?: string | number;
  max?: string | number;
  /** Heuristic: Wenn distinkte Werte ≤ 20, wird's automatisch als 'enum' eingestuft. */
  distinctCount: number;
}

export interface SchemaSnapshot {
  business: ColumnInfo[];
  evb: ColumnInfo[];
  orders: ColumnInfo[];
  cachedAt: string;
}

// Cache-Map: pageCount → { value, expiresAt }. Analog zu facetsCache (siehe oben).
const schemaCache = new Map<number, { value: SchemaSnapshot; expiresAt: number }>();
const SCHEMA_TTL_MS = 60 * 60 * 1000;
const SCHEMA_DEFAULT_PAGES = 1;
export const SCHEMA_CONST = { defaultPages: SCHEMA_DEFAULT_PAGES, recordsPerPage: 100 } as const;

/** Heuristik: type-detection aus einem Sample-Wert. */
function detectKindFromValue(v: unknown): ColumnKind {
  if (v === null || v === undefined) return 'unknown';
  if (typeof v === 'boolean') return 'boolean';
  if (typeof v === 'number') return 'number';
  if (typeof v === 'string') {
    // ISO-Datum: '2024-05-09T10:23:45+00:00' oder '2024-05-09 10:23:45'
    if (/^\d{4}-\d{2}-\d{2}([T ]\d{2}:\d{2})?/.test(v)) return 'date';
    return 'string';
  }
  return 'unknown';
}

/** Aggregate Sample-Records → ColumnInfo[] pro Spalte. Hydra-`@id`/`@type` werden ausgeschlossen. */
function aggregateColumns(records: Array<Record<string, unknown>>): ColumnInfo[] {
  const total = records.length;
  if (total === 0) return [];

  // Sammle alle Keys die in irgendeinem Record vorkommen (manche Felder sind nicht in jedem Record)
  const allKeys = new Set<string>();
  for (const r of records) for (const k of Object.keys(r)) allKeys.add(k);

  const columns: ColumnInfo[] = [];
  for (const name of allKeys) {
    if (name.startsWith('@')) continue; // Skip Hydra-Metadaten

    const valueCounts = new Map<string, number>();
    let filled = 0;
    let kindHint: ColumnKind = 'unknown';
    let minN: number | undefined; let maxN: number | undefined;
    let minD: string | undefined; let maxD: string | undefined;

    for (const r of records) {
      const v = r[name];
      if (v === null || v === undefined || v === '') continue;
      filled++;
      const k = detectKindFromValue(v);
      if (k !== 'unknown' && (kindHint === 'unknown' || kindHint === k)) kindHint = k;

      const str = String(v);
      valueCounts.set(str, (valueCounts.get(str) ?? 0) + 1);

      if (k === 'number' && typeof v === 'number') {
        if (minN === undefined || v < minN) minN = v;
        if (maxN === undefined || v > maxN) maxN = v;
      }
      if (k === 'date' && typeof v === 'string') {
        if (minD === undefined || v < minD) minD = v;
        if (maxD === undefined || v > maxD) maxD = v;
      }
    }

    const distinct = valueCounts.size;
    // Auto-Promote zu 'enum' wenn wenig distinkte Werte und kind=string
    let kind = kindHint;
    if (kind === 'string' && distinct > 0 && distinct <= 20) kind = 'enum';

    const topValues = [...valueCounts.entries()]
      .sort((a, b) => b[1] - a[1])
      .slice(0, 20)
      .map(([value, count]) => ({ value, count }));

    columns.push({
      name,
      kind,
      filledCount: filled,
      totalSampled: total,
      // High-Cardinality-Strings: skip topValues (irrelevant — wäre eine sinnlose 20-Sample-Liste)
      topValues: kind === 'string' && distinct > 50 ? [] : topValues,
      min: kind === 'number' ? minN : kind === 'date' ? minD : undefined,
      max: kind === 'number' ? maxN : kind === 'date' ? maxD : undefined,
      distinctCount: distinct,
    });
  }

  // Sortiere: zuerst die "fülligen" Spalten (filledCount desc), dann alphabetisch
  columns.sort((a, b) => b.filledCount - a.filledCount || a.name.localeCompare(b.name));
  return columns;
}

/**
 * Sampled `pages × 100` Records pro Tabelle → leitet Spalten-Schema ab.
 * Memory-Cache 60min, gekeyed auf den `pages`-Wert.
 *
 * Akkurate topValues/distinctCount: alle Pages werden in einen Records-Array
 * gemerged, dann gemeinsam aggregiert — `aggregateColumns()` sieht das volle
 * Sample und vergibt `kind=enum` ggf. anders als bei nur 1 Page.
 */
export async function inspectSchema(opts: SampleOpts = {}): Promise<SchemaSnapshot> {
  const pages = Math.max(1, Math.min(50, opts.pages ?? SCHEMA_DEFAULT_PAGES));
  const now = Date.now();
  if (!opts.refresh) {
    const cached = schemaCache.get(pages);
    if (cached && now < cached.expiresAt) return cached.value;
  }

  const businessRecords: Array<Record<string, unknown>> = [];
  const evbRecords: Array<Record<string, unknown>> = [];
  const orderRecords: Array<Record<string, unknown>> = [];

  for (let page = 1; page <= pages; page++) {
    const [b, e, o] = await Promise.all([
      listBusiness({ page, itemsPerPage: 100 }),
      listEvbs({ page, itemsPerPage: 100 }),
      listOrders({ page, itemsPerPage: 100 }),
    ]);
    businessRecords.push(...(b.records as unknown as Array<Record<string, unknown>>));
    evbRecords.push(...(e.records as unknown as Array<Record<string, unknown>>));
    orderRecords.push(...(o.records as unknown as Array<Record<string, unknown>>));
  }

  const value: SchemaSnapshot = {
    business: aggregateColumns(businessRecords),
    evb: aggregateColumns(evbRecords),
    orders: aggregateColumns(orderRecords),
    cachedAt: new Date().toISOString(),
  };
  schemaCache.set(pages, { value, expiresAt: now + SCHEMA_TTL_MS });
  return value;
}
