import { BadRequestException, Injectable, Logger, NotFoundException } from '@nestjs/common';
import { PrismaService } from '@core/database/prisma/prisma.service';
import {
  ImportBatchStatus,
  ImportBatchType,
  ImportEntryStatus,
  Prisma,
} from '@prisma/client';
import * as crypto from 'crypto';
import * as XLSX from 'xlsx';
import type { FieldMetadata, ParsedRow, ValidationIssue } from './importer.interface';
import { SkipAssertAccess } from '@common/decorators/skip-assert-access.decorator';

const MAX_ROWS = 1000;
const MAX_FILE_BYTES = 10 * 1024 * 1024;
const ERROR_PREVIEW_LIMIT = 50;

export interface ErrorPreviewEntry {
  rowNo: number;
  errorDetail: ValidationIssue[];
}

/**
 * Import 工具通用 service（详见 14-import-export-tool-prd.md §7）
 *
 * 职责：
 * - Excel 解析（xlsx 库）+ 安全门（文件大小 / sheet 检查 / 行数上限）
 * - ImportBatch / Entry CRUD
 * - 模板生成（xlsx 写 3 sheet）+ schemaHash 计算
 * - 错误报告 Excel 生成
 *
 * 不负责：具体 importer 业务逻辑（在 PurchaseOrderImporter 等里）
 */
@Injectable()
export class ImportBatchService {
  private readonly logger = new Logger(ImportBatchService.name);

  constructor(private readonly prisma: PrismaService) {}

  /** 流式 SHA256 计算（避免 binary buffer 整 load） */
  computeFileHash(buffer: Buffer): string {
    return crypto.createHash('sha256').update(buffer).digest('hex');
  }

  /** FieldMetadata 数组的稳定 hash — 模板版本号 */
  computeSchemaHash(metadata: FieldMetadata[]): string {
    const canonical = metadata.map((f) => ({
      field: f.field,
      type: f.type,
      required: f.required,
      enumValues: f.enumValues,
      fkRef: f.fkRef ? { table: f.fkRef.table, column: f.fkRef.column } : undefined,
    }));
    return crypto.createHash('sha256').update(JSON.stringify(canonical)).digest('hex').substring(0, 16);
  }

  /** 解析 Excel buffer → 一组 raw row（无类型） */
  parseExcel(buffer: Buffer): { headers: string[]; rows: Record<string, unknown>[] } {
    if (buffer.length === 0) throw new BadRequestException({ code: 'IMPORT_FILE_EMPTY' });
    if (buffer.length > MAX_FILE_BYTES)
      throw new BadRequestException({ code: 'IMPORT_FILE_TOO_LARGE', params: { actual: buffer.length, limit: MAX_FILE_BYTES } });

    let wb: XLSX.WorkBook;
    try {
      wb = XLSX.read(buffer, { type: 'buffer', cellDates: true });
    } catch (e: any) {
      throw new BadRequestException({ code: 'IMPORT_FILE_INVALID', params: { error: e.message } });
    }

    const firstSheetName = wb.SheetNames[0];
    if (!firstSheetName) throw new BadRequestException({ code: 'IMPORT_SHEET_MISSING' });

    const sheet = wb.Sheets[firstSheetName];
    const rows = XLSX.utils.sheet_to_json<Record<string, unknown>>(sheet, { defval: null, raw: false });

    if (rows.length === 0) throw new BadRequestException({ code: 'IMPORT_FILE_EMPTY' });
    if (rows.length > MAX_ROWS)
      throw new BadRequestException({ code: 'IMPORT_ROW_LIMIT', params: { actual: String(rows.length), limit: String(MAX_ROWS) } });

    const headers = Object.keys(rows[0] ?? {});
    return { headers, rows };
  }

  /** 模板生成 — 3 sheet（数据 + 字段说明 + 示例） */
  generateTemplate(metadata: FieldMetadata[], locale: 'zh' | 'en', type: ImportBatchType): Buffer {
    const wb = XLSX.utils.book_new();

    // Sheet1: 数据（仅表头 + 空行）
    const headers = metadata.map((f) => f.label[locale]);
    const ws1 = XLSX.utils.aoa_to_sheet([headers]);
    XLSX.utils.book_append_sheet(wb, ws1, 'Data');

    // Sheet2: 字段说明
    const sheet2Headers = locale === 'zh'
      ? ['字段', '必填', '类型', '枚举值', 'FK 引用', '示例', '说明']
      : ['Field', 'Required', 'Type', 'Enum', 'FK Ref', 'Example', 'Description'];
    const sheet2Data: any[][] = [sheet2Headers];
    for (const f of metadata) {
      sheet2Data.push([
        f.label[locale],
        f.required ? (locale === 'zh' ? '是' : 'Yes') : (locale === 'zh' ? '否' : 'No'),
        f.type,
        f.enumValues?.join(' / ') ?? '',
        f.fkRef ? (locale === 'zh' ? '关联' : 'Linked') : '', // 安全：不暴露物理表名
        f.example ?? '',
        f.description?.[locale] ?? '',
      ]);
    }
    const ws2 = XLSX.utils.aoa_to_sheet(sheet2Data);
    XLSX.utils.book_append_sheet(wb, ws2, locale === 'zh' ? '字段说明' : 'Fields');

    // Sheet3: 示例（合成假数据）
    const sheet3Row1 = metadata.map((f) => f.example ?? (f.required ? 'REQUIRED' : ''));
    const ws3 = XLSX.utils.aoa_to_sheet([headers, sheet3Row1]);
    XLSX.utils.book_append_sheet(wb, ws3, locale === 'zh' ? '示例' : 'Examples');

    return XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' });
  }

  /** 创建 batch + entries（preview 阶段调用） */
  async createBatch(
    type: ImportBatchType,
    fileName: string,
    fileHash: string,
    templateSchemaHash: string,
    parsedRows: ParsedRow<unknown>[],
    extraIssues: ValidationIssue[],
    userId: string,
    organizationId: string,
    clientIp?: string,
    userAgent?: string,
  ) {
    const issuesByRow = new Map<number, ValidationIssue[]>();
    for (const r of parsedRows) {
      if (r.parseIssues.length > 0) issuesByRow.set(r.rowNo, r.parseIssues);
    }
    for (const i of extraIssues) {
      const arr = issuesByRow.get(i.rowNo) ?? [];
      arr.push(i);
      issuesByRow.set(i.rowNo, arr);
    }

    let errorRows = 0;
    let warningRows = 0;
    let successRows = 0;
    const entries: Prisma.ImportBatchEntryCreateManyInput[] = [];
    for (const r of parsedRows) {
      const issues = issuesByRow.get(r.rowNo) ?? [];
      const hasError = issues.some((i) => i.severity === 'ERROR');
      const hasWarning = issues.some((i) => i.severity === 'WARNING');
      let status: ImportEntryStatus;
      if (hasError) {
        status = ImportEntryStatus.ERROR;
        errorRows++;
      } else if (hasWarning) {
        status = ImportEntryStatus.WARNING;
        warningRows++;
      } else {
        status = ImportEntryStatus.OK;
        successRows++;
      }
      const payload = r.typed ?? r.raw;
      entries.push({
        id: crypto.randomUUID(),
        batchId: '__placeholder__', // 替换
        rowNo: r.rowNo,
        status,
        entityIds: [],
        payload: payload as Prisma.InputJsonValue,
        payloadHash: crypto.createHash('sha256').update(JSON.stringify(payload)).digest('hex'),
        errorDetail: issues.length > 0 ? (issues as unknown as Prisma.InputJsonValue) : Prisma.DbNull,
      });
    }

    const batch = await this.prisma.$transaction(async (tx) => {
      const created = await tx.importBatch.create({
        data: {
          type,
          status: ImportBatchStatus.VALIDATED,
          fileName,
          fileHash,
          templateSchemaHash,
          totalRows: parsedRows.length,
          successRows,
          errorRows,
          warningRows,
          startedAt: new Date(),
          clientIp,
          userAgent,
          organizationId,
          createdById: userId,
        },
      });
      for (const e of entries) e.batchId = created.id;
      await tx.importBatchEntry.createMany({ data: entries });
      return created;
    });

    // errorPreview: 让 controller 一次 RTT 返给前端，避免 FE 紧接着再调 GET /batches/:id
    const errorPreview: ErrorPreviewEntry[] = entries
      .filter((e) => e.status === ImportEntryStatus.ERROR)
      .slice(0, ERROR_PREVIEW_LIMIT)
      .map((e) => ({
        rowNo: e.rowNo,
        errorDetail: e.errorDetail as unknown as ValidationIssue[],
      }));
    return { batch, errorPreview };
  }

  /** idempotent 路径（findInFlightSameFile 命中）专用 — 查 ERROR 行的精简 preview */
  async getErrorPreview(batchId: string, limit = ERROR_PREVIEW_LIMIT): Promise<ErrorPreviewEntry[]> {
    const entries = await this.prisma.importBatchEntry.findMany({
      where: { batchId, status: ImportEntryStatus.ERROR },
      orderBy: { rowNo: 'asc' },
      take: limit,
      select: { rowNo: true, errorDetail: true },
    });
    return entries.map((e) => ({
      rowNo: e.rowNo,
      errorDetail: (e.errorDetail ?? []) as unknown as ValidationIssue[],
    }));
  }

  /** 找 batch（含权限三元组 — IDOR 防护） */
  async findBatch(batchId: string, userId: string, organizationId: string, isAdmin: boolean) {
    const batch = await this.prisma.importBatch.findFirst({
      where: {
        id: batchId,
        organizationId,
        deletedAt: null,
        ...(isAdmin ? {} : { createdById: userId }),
      },
      include: { entries: { orderBy: { rowNo: 'asc' } } },
    });
    if (!batch) throw new NotFoundException({ code: 'IMPORT_BATCH_NOT_FOUND' });
    return batch;
  }

  /** CAS 锁 confirm：UPDATE WHERE status=VALIDATED RETURNING；防 race + 越权 */
  @SkipAssertAccess('CAS 锁 WHERE id+createdById+status，原子操作内含权限校验')
  async lockForConfirm(batchId: string, userId: string, organizationId: string) {
    const result = await this.prisma.importBatch.updateMany({
      where: {
        id: batchId,
        organizationId,
        createdById: userId,
        status: ImportBatchStatus.VALIDATED,
        deletedAt: null,
      },
      data: { status: ImportBatchStatus.IMPORTING, startedAt: new Date() },
    });
    if (result.count === 0) {
      throw new BadRequestException({ code: 'IMPORT_BATCH_ALREADY_CONFIRMED' });
    }
    return this.prisma.importBatch.findUnique({
      where: { id: batchId },
      include: { entries: { where: { status: ImportEntryStatus.OK }, orderBy: { rowNo: 'asc' } } },
    });
  }

  /** 标记 batch 完成 */
  @SkipAssertAccess('内部 helper，由 ImportController.confirm 调用，前置已 CAS 锁')
  async markCompleted(batchId: string, confirmedById: string, entityIdsMap: Map<number, string[]>) {
    return this.prisma.$transaction(async (tx) => {
      // 回写 entityIds
      for (const [rowNo, ids] of entityIdsMap.entries()) {
        await tx.importBatchEntry.updateMany({
          where: { batchId, rowNo },
          data: { entityIds: ids },
        });
      }
      return tx.importBatch.update({
        where: { id: batchId },
        data: {
          status: ImportBatchStatus.COMPLETED,
          completedAt: new Date(),
          confirmedById,
          confirmedAt: new Date(),
        },
      });
    });
  }

  @SkipAssertAccess('内部 helper，仅由 confirm 失败路径调用')
  async markFailed(batchId: string, errorCode: string, detail?: any) {
    return this.prisma.importBatch.update({
      where: { id: batchId },
      data: {
        status: ImportBatchStatus.FAILED,
        completedAt: new Date(),
        errorSummary: { code: errorCode, detail } as Prisma.InputJsonValue,
      },
    });
  }

  /** 查同 user + 同 fileHash + 24h 内 + 非终态的 batch（idempotent 防重复上传） */
  async findInFlightSameFile(userId: string, organizationId: string, fileHash: string) {
    const dayAgo = new Date(Date.now() - 24 * 60 * 60 * 1000);
    return this.prisma.importBatch.findFirst({
      where: {
        createdById: userId,
        organizationId,
        fileHash,
        createdAt: { gte: dayAgo },
        status: { notIn: [ImportBatchStatus.COMPLETED, ImportBatchStatus.FAILED, ImportBatchStatus.SUPERSEDED] },
        deletedAt: null,
      },
    });
  }

  /** per-user in-flight 上限校验（≤ 1） */
  async ensureNoOtherInFlight(userId: string, organizationId: string, excludeBatchId?: string) {
    const count = await this.prisma.importBatch.count({
      where: {
        createdById: userId,
        organizationId,
        status: { in: [ImportBatchStatus.PENDING, ImportBatchStatus.VALIDATING, ImportBatchStatus.VALIDATED, ImportBatchStatus.IMPORTING] },
        deletedAt: null,
        ...(excludeBatchId ? { NOT: { id: excludeBatchId } } : {}),
      },
    });
    if (count > 0) throw new BadRequestException({ code: 'IMPORT_CONCURRENT_BATCH' });
  }

  /** list 用户的 batch 历史 */
  async listBatches(
    type: ImportBatchType | undefined,
    userId: string,
    organizationId: string,
    isAdmin: boolean,
    scope: 'mine' | 'all',
    page: number,
    limit: number,
  ) {
    const cappedLimit = Math.min(limit, 50);
    const where: Prisma.ImportBatchWhereInput = {
      organizationId,
      deletedAt: null,
      ...(type ? { type } : {}),
      ...(isAdmin && scope === 'all' ? {} : { createdById: userId }),
    };
    const [items, total] = await this.prisma.$transaction([
      this.prisma.importBatch.findMany({
        where,
        orderBy: { createdAt: 'desc' },
        skip: (page - 1) * cappedLimit,
        take: cappedLimit,
      }),
      this.prisma.importBatch.count({ where }),
    ]);
    return { items, total, page, limit: cappedLimit, totalPages: Math.ceil(total / cappedLimit) };
  }

  /** 生成错误报告 Excel（仅 ERROR 行） */
  async generateErrorReport(batchId: string, userId: string, organizationId: string, isAdmin: boolean): Promise<Buffer> {
    const batch = await this.findBatch(batchId, userId, organizationId, isAdmin);
    const errorEntries = batch.entries.filter((e) => e.status === ImportEntryStatus.ERROR);

    if (errorEntries.length === 0) {
      throw new BadRequestException({ code: 'IMPORT_BATCH_NO_ERRORS' });
    }

    const headers = ['Row No', 'Errors', ...Object.keys(errorEntries[0].payload as object)];
    const rows: any[][] = [headers];
    for (const e of errorEntries) {
      const payload = e.payload as Record<string, unknown>;
      const errors = e.errorDetail as ValidationIssue[] | null;
      const errorMsg = errors?.map((i) => `[${i.field}] ${i.code} ${JSON.stringify(i.params)}`).join('; ') ?? '';
      const row = [e.rowNo, errorMsg];
      for (const k of Object.keys(errorEntries[0].payload as object)) {
        let v = payload[k] ?? '';
        // CSV/Formula injection 防御：以 = / + / - / @ 起头的单元格前置 '
        if (typeof v === 'string' && /^[=+\-@]/.test(v)) v = `'${v}`;
        row.push(v as any);
      }
      rows.push(row);
    }
    const wb = XLSX.utils.book_new();
    const ws = XLSX.utils.aoa_to_sheet(rows);
    XLSX.utils.book_append_sheet(wb, ws, 'Errors');
    return XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' });
  }
}