import { Injectable, Logger } from '@nestjs/common';
import { and, eq, ilike, inArray, or } from 'drizzle-orm';
import { DocumentIntakeDto, AiAsnDraftSchema, AiOrderDraftSchema, AiAsnDraft, AiOrderDraft, DraftDiscrepancyEmailSchema } from '@wms/shared';
import { z } from 'zod';
import { DbService, Tx } from '@/common/db/db.service';
import { skus, skuBarcodes, skuPacks, suppliers, customers, attachments, asns, asnLines, receivingDiscrepancies, storers, grns } from '@/db/schema';
import { StorageService } from '@/common/storage/storage.service';
import { requireRequestContext } from '@/common/context/request-context';
import { ValidationError, NotFoundError } from '@/common/errors/domain-error';
import { assertWarehouseAccess, requireStorerScope, serialize } from '@/common/util';
import { LlmService } from './llm/llm.service';
import { ProposalsService } from './proposals.service';

type SkuCandidate = { id: string; code: string; description: string; barcodes: string[]; packs: { level: string; uomCode: string; qtyInBase: string }[] };

export interface UploadedDoc {
  buffer: Buffer;
  originalname: string;
  mimetype: string;
}

const MAX_TEXT_CHARS = 60_000;

/**
 * Turns an unstructured purchase order / packing list / customer order
 * (PDF, text, email body) into an ASN or outbound order draft.
 *
 * Pipeline:
 *  1. extract text (pdf-parse for PDFs, utf-8 otherwise),
 *  2. LLM extraction into a strict JSON contract (validated with zod),
 *  3. deterministic matching of every line to the storer's SKU master
 *     (exact code, barcode, then fuzzy description),
 *  4. stored as a proposal; a human reviews, edits and accepts before any
 *     document is created.
 */
@Injectable()
export class DocumentIntakeService {
  private readonly logger = new Logger(DocumentIntakeService.name);

  constructor(
    private readonly db: DbService,
    private readonly llm: LlmService,
    private readonly proposals: ProposalsService,
    private readonly storage: StorageService,
  ) {}

  async intake(dto: DocumentIntakeDto, file?: UploadedDoc) {
    assertWarehouseAccess(dto.warehouseId);
    const storerId = requireStorerScope(dto.storerId);
    await this.llm.assertAllowed('ai.document_intake');

    const text = await this.extractText(dto, file);
    if (!text.trim()) throw new ValidationError('No readable text found in the document.');

    // Give the model the storer's catalogue (codes + descriptions) so it can map lines itself when the document uses customer item numbers.
    const catalogue = await this.db.run((tx) => this.loadCatalogue(tx, storerId));
    const catalogueText = catalogue.slice(0, 400).map((s) => `${s.code} | ${s.description}${s.barcodes.length ? ` | EAN ${s.barcodes.join(',')}` : ''}`).join('\n');

    const target = dto.target;
    const schema = target === 'ASN' ? AiAsnDraftSchema : AiOrderDraftSchema;
    const system = [
      `You are a data-entry assistant for a warehouse management system. Extract a ${target === 'ASN' ? 'supplier delivery (ASN / purchase order)' : 'customer outbound order'} from the document text.`,
      'Rules:',
      '- Output must follow the JSON schema described below exactly. Do not invent lines or quantities. If a value is not in the document, omit it.',
      '- Quantities must be numbers. Keep the unit the document uses in "uom" (EA, CS, PLT ...).',
      '- Dates in YYYY-MM-DD.',
      '- When a line matches an item in the catalogue, put the catalogue SKU code in "itemCode". If no clear match, leave itemCode empty and keep the description.',
      '- Give a confidence between 0 and 1 per line and list anything ambiguous in "warnings".',
      '',
      'JSON schema:',
      target === 'ASN'
        ? '{ "reference": string, "supplierName": string, "expectedDate": "YYYY-MM-DD", "containerNo": string, "containerSize": string, "lines": [ { "itemCode": string, "description": string, "qty": number, "uom": string, "lot": string, "expiryDate": "YYYY-MM-DD", "confidence": number } ], "warnings": [string] }'
        : '{ "reference": string, "customerName": string, "deliveryLocationCode": string, "requestedShipDate": "YYYY-MM-DD", "address": string, "lines": [ { "itemCode": string, "description": string, "qty": number, "uom": string, "serials": [string], "confidence": number } ], "warnings": [string] }',
      '',
      'Catalogue (SKU code | description | barcodes):',
      catalogueText || '(empty)',
    ].join('\n');

    const user = `${dto.hints ? `Operator hints: ${dto.hints}\n\n` : ''}Document text:\n"""\n${text.slice(0, MAX_TEXT_CHARS)}\n"""`;

    const result = await this.llm.structured({ system, messages: [{ role: 'user', content: user }], maxTokens: 4096 }, schema, { feature: 'ai.document_intake', purpose: `document_intake.${target.toLowerCase()}` });

    return this.db.run(async (tx) => {
      const attachment = file ? await this.storeSource(tx, file) : null;
      const built = target === 'ASN'
        ? await this.buildAsnDraft(tx, dto, storerId, result.data as AiAsnDraft, catalogue)
        : await this.buildOrderDraft(tx, dto, storerId, result.data as AiOrderDraft, catalogue);

      const proposal = await this.proposals.create(tx, {
        type: target === 'ASN' ? 'ASN_FROM_DOCUMENT' : 'ORDER_FROM_DOCUMENT',
        warehouseId: dto.warehouseId,
        storerId,
        title: built.title,
        summary: built.summary,
        payload: built.payload,
        evidence: { extracted: result.data, matching: built.matching, warnings: built.warnings, sourceAttachmentId: attachment?.id ?? null, sourceChars: text.length, attempts: result.attempts },
        confidence: built.confidence,
        severity: built.warnings.length ? 'MEDIUM' : 'INFO',
        model: result.model,
        promptTokens: result.usage.inputTokens,
        completionTokens: result.usage.outputTokens,
        ttlDays: 7,
      });
      if (attachment) await tx.update(attachments).set({ refId: proposal.id }).where(eq(attachments.id, attachment.id));
      return proposal;
    });
  }

  /** Drafts a supplier discrepancy notification for an ASN with open discrepancies. */
  async draftDiscrepancyEmail(dto: z.infer<typeof DraftDiscrepancyEmailSchema>) {
    await this.llm.assertAllowed('ai.document_intake');
    const ctx = await this.db.run(async (tx) => {
      const [a] = await tx.select().from(asns).where(eq(asns.id, dto.asnId)).limit(1);
      if (!a) throw new NotFoundError('ASN', dto.asnId);
      assertWarehouseAccess(a.warehouseId);
      requireStorerScope(a.storerId);
      const [storer] = await tx.select().from(storers).where(eq(storers.id, a.storerId)).limit(1);
      const supplier = a.supplierId ? (await tx.select().from(suppliers).where(eq(suppliers.id, a.supplierId)).limit(1))[0] : null;
      const [grn] = await tx.select().from(grns).where(eq(grns.asnId, a.id)).limit(1);
      const disc = await tx
        .select({ type: receivingDiscrepancies.type, expectedQty: receivingDiscrepancies.expectedQty, actualQty: receivingDiscrepancies.actualQty, varianceQty: receivingDiscrepancies.varianceQty, notes: receivingDiscrepancies.notes, skuCode: skus.code, description: skus.description, lineNo: asnLines.lineNo })
        .from(receivingDiscrepancies)
        .innerJoin(skus, eq(skus.id, receivingDiscrepancies.skuId))
        .leftJoin(asnLines, eq(asnLines.id, receivingDiscrepancies.asnLineId))
        .where(eq(receivingDiscrepancies.asnId, a.id));
      if (!disc.length) throw new ValidationError('This ASN has no discrepancies to report.');
      return { asn: a, storer, supplier, grn, disc: serialize(disc) };
    });

    const contacts = ((ctx.supplier?.contacts ?? []) as { name?: string; email?: string }[]).filter((c) => c.email);
    const system = [
      'You write professional supplier correspondence for a third-party logistics warehouse. Write on behalf of the warehouse operator to the supplier about receiving discrepancies.',
      `Tone: ${dto.tone}. Language: ${dto.language === 'ar' ? 'Arabic' : 'English'}.`,
      'Return JSON: { "subject": string, "bodyHtml": string }. bodyHtml is simple HTML (p, table, ul). Include a table of the discrepancies with expected, received and variance. Ask for a credit note or replacement as appropriate. Do not invent facts, amounts or dates not given.',
    ].join('\n');
    const facts = {
      asnNumber: ctx.asn.asnNumber,
      supplierReference: ctx.asn.externalRef,
      grnNumber: ctx.grn?.grnNumber ?? null,
      receivedAt: ctx.asn.receivedAt,
      storer: ctx.storer?.name,
      supplier: ctx.supplier?.name ?? 'Supplier',
      discrepancies: ctx.disc,
    };
    const EmailSchema = z.object({ subject: z.string().min(1).max(200), bodyHtml: z.string().min(1) });
    const result = await this.llm.structured({ system, messages: [{ role: 'user', content: JSON.stringify(facts, null, 2) }], maxTokens: 2048 }, EmailSchema, { feature: 'ai.document_intake', purpose: 'discrepancy_email' });

    return this.db.run((tx) =>
      this.proposals.create(tx, {
        type: 'DISCREPANCY_EMAIL',
        warehouseId: ctx.asn.warehouseId,
        storerId: ctx.asn.storerId,
        title: `Discrepancy notice for ${ctx.asn.asnNumber}`,
        summary: `${ctx.disc.length} discrepancy line(s) on ${ctx.asn.asnNumber}${ctx.asn.externalRef ? ` (ref ${ctx.asn.externalRef})` : ''} to ${ctx.supplier?.name ?? 'supplier'}.`,
        payload: { asnId: ctx.asn.id, to: contacts.map((c) => c.email), cc: [], subject: result.data.subject, bodyHtml: result.data.bodyHtml },
        evidence: { facts, recipientsFound: contacts.length },
        severity: contacts.length ? 'INFO' : 'LOW',
        model: result.model,
        promptTokens: result.usage.inputTokens,
        completionTokens: result.usage.outputTokens,
        ttlDays: 7,
      }),
    );
  }

  // ---- text extraction ----

  private async extractText(dto: DocumentIntakeDto, file?: UploadedDoc): Promise<string> {
    if (dto.text) return dto.text;
    if (!file) throw new ValidationError('Provide text or upload a file.');
    const name = file.originalname.toLowerCase();
    if (file.mimetype === 'application/pdf' || name.endsWith('.pdf')) {
      // eslint-disable-next-line @typescript-eslint/no-var-requires
      const pdfParse = require('pdf-parse') as (b: Buffer) => Promise<{ text: string; numpages: number }>;
      const out = await pdfParse(file.buffer);
      if (!out.text.trim()) throw new ValidationError('The PDF has no text layer (scanned image). OCR is not enabled on this server; paste the text instead.');
      return out.text;
    }
    if (name.endsWith('.xlsx') || name.endsWith('.xls')) {
      const ExcelJS = await import('exceljs');
      const wb = new ExcelJS.Workbook();
      await wb.xlsx.load(file.buffer as unknown as ArrayBuffer);
      const lines: string[] = [];
      wb.eachSheet((ws) => {
        lines.push(`# Sheet ${ws.name}`);
        ws.eachRow((row) => lines.push((row.values as unknown[]).slice(1).map((v) => (v == null ? '' : String((v as { text?: string }).text ?? v))).join(' | ')));
      });
      return lines.join('\n');
    }
    return file.buffer.toString('utf8');
  }

  private async storeSource(tx: Tx, file: UploadedDoc) {
    const ctx = requireRequestContext();
    const key = this.storage.buildKey(ctx.tenantId!, 'ai-intake', file.originalname);
    await this.storage.put(key, file.buffer, file.mimetype);
    const [row] = await tx
      .insert(attachments)
      .values({ tenantId: ctx.tenantId!, refType: 'AI_PROPOSAL', refId: '00000000-0000-0000-0000-000000000000', fileName: file.originalname, mimeType: file.mimetype, sizeBytes: file.buffer.length, storageKey: key, kind: 'SOURCE_DOCUMENT', uploadedById: ctx.userId })
      .returning({ id: attachments.id });
    return row;
  }

  // ---- matching ----

  private async loadCatalogue(tx: Tx, storerId: string): Promise<SkuCandidate[]> {
    const rows = await tx
      .select({ id: skus.id, code: skus.code, description: skus.description })
      .from(skus)
      .where(and(eq(skus.storerId, storerId), eq(skus.status, 'ACTIVE')))
      .orderBy(skus.code)
      .limit(2000);
    if (!rows.length) return [];
    const ids = rows.map((r) => r.id);
    const bcs = await tx.select({ skuId: skuBarcodes.skuId, barcode: skuBarcodes.barcode }).from(skuBarcodes).where(inArray(skuBarcodes.skuId, ids));
    const packs = await tx.select({ skuId: skuPacks.skuId, level: skuPacks.level, uomCode: skuPacks.uomCode, qtyInBase: skuPacks.qtyInBase }).from(skuPacks).where(inArray(skuPacks.skuId, ids));
    return rows.map((r) => ({
      ...r,
      barcodes: bcs.filter((b) => b.skuId === r.id).map((b) => b.barcode),
      packs: packs.filter((p) => p.skuId === r.id).map((p) => ({ level: p.level, uomCode: p.uomCode, qtyInBase: String(p.qtyInBase) })),
    }));
  }

  /** Exact code -> barcode -> normalised code -> description similarity. */
  private matchSku(line: { itemCode?: string; description?: string }, catalogue: SkuCandidate[]): { sku: SkuCandidate | null; method: string; score: number } {
    const code = (line.itemCode ?? '').trim();
    if (code) {
      const exact = catalogue.find((s) => s.code.toLowerCase() === code.toLowerCase());
      if (exact) return { sku: exact, method: 'CODE', score: 1 };
      const byBarcode = catalogue.find((s) => s.barcodes.includes(code));
      if (byBarcode) return { sku: byBarcode, method: 'BARCODE', score: 1 };
      const norm = normalize(code);
      const loose = catalogue.find((s) => normalize(s.code) === norm);
      if (loose) return { sku: loose, method: 'CODE_NORMALISED', score: 0.9 };
    }
    const desc = (line.description ?? '').trim();
    if (desc) {
      let best: { sku: SkuCandidate; score: number } | null = null;
      for (const s of catalogue) {
        const score = tokenSimilarity(desc, s.description);
        if (!best || score > best.score) best = { sku: s, score };
      }
      if (best && best.score >= 0.6) return { sku: best.sku, method: 'DESCRIPTION', score: best.score };
    }
    return { sku: null, method: 'NONE', score: 0 };
  }

  private packLevelFor(sku: SkuCandidate | null, uom?: string): 'EACH' | 'INNER' | 'CASE' | 'PALLET' {
    const u = (uom ?? '').toUpperCase();
    if (!u || ['EA', 'EACH', 'PC', 'PCS', 'UNIT', 'UNITS'].includes(u)) return 'EACH';
    if (['PLT', 'PAL', 'PALLET', 'PALLETS'].includes(u)) return 'PALLET';
    if (['CS', 'CASE', 'CASES', 'CTN', 'CARTON', 'CARTONS', 'BOX', 'BOXES'].includes(u)) return 'CASE';
    if (['INNER', 'PK', 'PACK'].includes(u)) return 'INNER';
    const pack = sku?.packs.find((p) => p.uomCode.toUpperCase() === u);
    return (pack?.level as 'EACH' | 'INNER' | 'CASE' | 'PALLET') ?? 'EACH';
  }

  private async buildAsnDraft(tx: Tx, dto: DocumentIntakeDto, storerId: string, draft: AiAsnDraft, catalogue: SkuCandidate[]) {
    const warnings = [...draft.warnings];
    const matching: unknown[] = [];
    let supplierId: string | undefined;
    if (draft.supplierName) {
      const [sup] = await tx.select({ id: suppliers.id, name: suppliers.name }).from(suppliers).where(and(eq(suppliers.storerId, storerId), or(ilike(suppliers.name, `%${draft.supplierName}%`), ilike(suppliers.code, draft.supplierName)))).limit(1);
      if (sup) supplierId = sup.id; else warnings.push(`Supplier "${draft.supplierName}" not found in master data; ASN will be created without supplier.`);
    }
    const lines = draft.lines.map((l, i) => {
      const m = this.matchSku(l, catalogue);
      matching.push({ lineNo: i + 1, itemCode: l.itemCode, description: l.description, matched: m.sku?.code ?? null, method: m.method, score: Number(m.score.toFixed(2)), llmConfidence: l.confidence ?? null });
      if (!m.sku) warnings.push(`Line ${i + 1}: no SKU match for "${l.itemCode ?? l.description ?? '?'}". Pick a SKU before accepting.`);
      return {
        lineNo: i + 1,
        skuId: m.sku?.id,
        skuCode: m.sku?.code ?? l.itemCode,
        expectedQty: String(l.qty),
        packLevel: this.packLevelFor(m.sku, l.uom),
        expectedLot: l.lot,
        expectedExpiry: validDate(l.expiryDate),
        notes: m.sku ? undefined : `Unmatched: ${l.description ?? ''}`.trim(),
      };
    });
    const matched = matching.filter((m) => (m as { matched: string | null }).matched).length;
    const confidence = lines.length ? (matched / lines.length) * avg(draft.lines.map((l) => l.confidence ?? 0.8)) : 0;
    const payload = {
      warehouseId: dto.warehouseId,
      storerId,
      externalRef: draft.reference?.slice(0, 60),
      type: 'PURCHASE',
      supplierId,
      expectedAt: validDate(draft.expectedDate) ? `${validDate(draft.expectedDate)}T08:00:00.000Z` : undefined,
      containerNo: draft.containerNo?.slice(0, 40),
      containerSize: normaliseContainer(draft.containerSize),
      notes: 'Created from document by AI intake',
      lines,
    };
    return {
      title: `ASN draft${draft.reference ? ` ${draft.reference}` : ''} (${lines.length} lines)`,
      summary: `${matched}/${lines.length} lines matched to SKUs${draft.supplierName ? `, supplier ${draft.supplierName}` : ''}${warnings.length ? `, ${warnings.length} warning(s)` : ''}.`,
      payload,
      matching,
      warnings,
      confidence,
    };
  }

  private async buildOrderDraft(tx: Tx, dto: DocumentIntakeDto, storerId: string, draft: AiOrderDraft, catalogue: SkuCandidate[]) {
    const warnings = [...draft.warnings];
    const matching: unknown[] = [];
    let customerId: string | undefined;
    let customerName = draft.customerName;
    if (draft.customerName || draft.deliveryLocationCode) {
      const needle = draft.deliveryLocationCode ?? draft.customerName!;
      const [cust] = await tx.select({ id: customers.id, name: customers.name }).from(customers).where(and(eq(customers.storerId, storerId), or(ilike(customers.code, needle), ilike(customers.name, `%${draft.customerName ?? needle}%`)))).limit(1);
      if (cust) { customerId = cust.id; customerName = cust.name; } else warnings.push(`Customer "${needle}" not found in master data; ship-to will be free text.`);
    }
    const lines = draft.lines.map((l, i) => {
      const m = this.matchSku(l, catalogue);
      matching.push({ lineNo: i + 1, itemCode: l.itemCode, description: l.description, matched: m.sku?.code ?? null, method: m.method, score: Number(m.score.toFixed(2)), llmConfidence: l.confidence ?? null });
      if (!m.sku) warnings.push(`Line ${i + 1}: no SKU match for "${l.itemCode ?? l.description ?? '?'}". Pick a SKU before accepting.`);
      return {
        lineNo: i + 1,
        skuId: m.sku?.id,
        skuCode: m.sku?.code ?? l.itemCode,
        orderedQty: String(l.qty),
        packLevel: this.packLevelFor(m.sku, l.uom),
        requestedSerials: l.serials ?? [],
        notes: m.sku ? undefined : `Unmatched: ${l.description ?? ''}`.trim(),
      };
    });
    const matched = matching.filter((m) => (m as { matched: string | null }).matched).length;
    const confidence = lines.length ? (matched / lines.length) * avg(draft.lines.map((l) => l.confidence ?? 0.8)) : 0;
    const payload = {
      warehouseId: dto.warehouseId,
      storerId,
      externalRef: draft.reference?.slice(0, 60),
      type: 'SALES',
      customerId,
      shipTo: { name: customerName?.slice(0, 200), deliveryLocationCode: draft.deliveryLocationCode?.slice(0, 40), instructions: draft.address?.slice(0, 1000) },
      requestedShipDate: validDate(draft.requestedShipDate),
      notes: 'Created from document by AI intake',
      lines,
    };
    return {
      title: `Order draft${draft.reference ? ` ${draft.reference}` : ''} (${lines.length} lines)`,
      summary: `${matched}/${lines.length} lines matched to SKUs${customerName ? `, customer ${customerName}` : ''}${warnings.length ? `, ${warnings.length} warning(s)` : ''}.`,
      payload,
      matching,
      warnings,
      confidence,
    };
  }
}

// ---- helpers ----

function normalize(s: string): string {
  return s.toLowerCase().replace(/[^a-z0-9]/g, '').replace(/^0+/, '');
}

function tokens(s: string): Set<string> {
  return new Set(s.toLowerCase().split(/[^a-z0-9]+/).filter((t) => t.length > 1));
}

/** Jaccard similarity over word tokens; cheap, deterministic, good enough for a shortlist. */
function tokenSimilarity(a: string, b: string): number {
  const ta = tokens(a);
  const tb = tokens(b);
  if (!ta.size || !tb.size) return 0;
  let inter = 0;
  for (const t of ta) if (tb.has(t)) inter++;
  return inter / (ta.size + tb.size - inter);
}

function validDate(v?: string): string | undefined {
  if (!v) return undefined;
  const m = /^(\d{4})-(\d{2})-(\d{2})/.exec(v);
  if (!m) return undefined;
  const d = new Date(`${m[1]}-${m[2]}-${m[3]}T00:00:00Z`);
  return isNaN(d.getTime()) ? undefined : `${m[1]}-${m[2]}-${m[3]}`;
}

function normaliseContainer(v?: string): '20' | '40' | '40HC' | '45' | 'LCL' | 'TRUCK' | 'VAN' | 'AIR' | undefined {
  if (!v) return undefined;
  const u = v.toUpperCase().replace(/[^A-Z0-9]/g, '');
  if (u.includes('40HC') || u.includes('40HQ')) return '40HC';
  if (u.includes('45')) return '45';
  if (u.includes('40')) return '40';
  if (u.includes('20')) return '20';
  if (u.includes('LCL')) return 'LCL';
  if (u.includes('TRUCK') || u.includes('TRAILER')) return 'TRUCK';
  if (u.includes('VAN')) return 'VAN';
  if (u.includes('AIR')) return 'AIR';
  return undefined;
}

function avg(xs: number[]): number {
  return xs.length ? xs.reduce((a, b) => a + b, 0) / xs.length : 0;
}