import {
  Injectable,
  BadRequestException,
} from '@nestjs/common';
import { PrismaService } from '@core/database/prisma/prisma.service';
import { PartsService } from './parts.service';
import { PartStatus } from '@prisma/client';
import * as crypto from 'crypto';

interface ExcelRow {
  partNumber: string;
  partNameEn: string;  // 英文名称（必填）
  partNameCn?: string;  // 中文名称（可选）
  unit?: string;
  source?: string;
  specifications?: string;
  remark?: string;
  station?: string;
  warehouseLocation?: string;
  currentStock?: number;
  minStock?: number;
  maxStock?: number;
  customFields?: Record<string, any>;  // 自定义字段
  groupIds?: string[];  // 分组ID
}

@Injectable()
export class ExcelService {
  constructor(
    private readonly prisma: PrismaService,
    private readonly partsService: PartsService,
  ) {}

  /**
   * 导入 Excel 数据
   * 注意：这里只是处理逻辑，实际的 Excel 解析需要在 Controller 中完成
   */
  async importFromExcel(
    rows: ExcelRow[],
    userId: string,
    userName: string,
    fileName: string,
    fileSize: number,
    updateExisting: boolean = false,
  ) {
    // 计算文件哈希
    const fileHash = crypto
      .createHash('md5')
      .update(JSON.stringify(rows))
      .digest('hex');

    // 创建导入日志
    const importLog = await this.prisma.inventoryImportLog.create({
      data: {
        fileName,
        fileSize,
        fileHash,
        status: 'PROCESSING',
        totalRows: rows.length,
        importedBy: userName,
        startedAt: new Date(),
      },
    });

    const results = {
      total: rows.length,
      success: 0,
      failed: 0,
      updated: 0,
      errors: [] as any[],
      warnings: [] as any[],
    };

    try {
      for (let i = 0; i < rows.length; i++) {
        const row = rows[i];
        const rowNumber = i + 2; // Excel 行号（从 1 开始，第 1 行是标题）

        try {
          // 验证必填字段
          if (!row.partNumber || !row.partNameEn) {
            throw new Error('Part Number and Part Name (English) are required');
          }

          // 检查零件是否已存在
          // 注意：part_number 在数据库有唯一约束
          const existing = await this.prisma.part.findFirst({
            where: { 
              partNumber: row.partNumber,
            },
          });

          if (existing) {
            if (updateExisting) {
              // 更新现有零件
              await this.partsService.update(
                existing.id,
                {
                  partNameEn: row.partNameEn,
                  partNameCn: row.partNameCn,
                  unit: row.unit,
                  source: row.source,
                  specifications: row.specifications,
                  remark: row.remark,
                  station: row.station,
                  warehouseLocation: row.warehouseLocation,
                  currentStock: row.currentStock,
                  minStock: row.minStock,
                  maxStock: row.maxStock,
                  customFields: row.customFields,
                  groupIds: row.groupIds,  // ✅ 添加 groupIds 以重建分组关系
                },
                userId,
              );
              results.updated++;
              results.success++;
            } else {
              results.warnings.push({
                row: rowNumber,
                partNumber: row.partNumber,
                message: 'Part already exists (skipped)',
              });
            }
          } else {
            // 创建新零件
            await this.partsService.create(
              {
                partNumber: row.partNumber,
                partNameEn: row.partNameEn,
                partNameCn: row.partNameCn,
                unit: row.unit ?? 'pcs',
                source: row.source,
                specifications: row.specifications,
                remark: row.remark,
                station: row.station,
                warehouseLocation: row.warehouseLocation,
                currentStock: row.currentStock ?? 0,
                minStock: row.minStock ?? 0,
                maxStock: row.maxStock,
                customFields: row.customFields,
                groupIds: row.groupIds,
              },
              userId,
            );
            results.success++;
          }
        } catch (error) {
          results.failed++;
          results.errors.push({
            row: rowNumber,
            partNumber: row.partNumber,
            error: error.message,
          });
        }
      }

      // 更新导入日志
      await this.prisma.inventoryImportLog.update({
        where: { id: importLog.id },
        data: {
          status: results.failed === 0 ? 'COMPLETED' : 'PARTIAL',
          successRows: results.success,
          failedRows: results.failed,
          skippedRows: results.warnings.length,
          errors: results.errors,
          summary: results,
          completedAt: new Date(),
        },
      });

      return {
        importLogId: importLog.id,
        ...results,
      };
    } catch (error) {
      // 更新导入日志为失败
      await this.prisma.inventoryImportLog.update({
        where: { id: importLog.id },
        data: {
          status: 'FAILED',
          errors: [{ error: error.message }],
          completedAt: new Date(),
        },
      });

      throw error;
    }
  }

  /**
   * 导出零件数据到 Excel 格式
   * 返回数据数组，实际的 Excel 生成在 Controller 中完成
   */
  async exportToExcel(query?: {
    category?: string;
    station?: string;
    warehouseLocation?: string;
    status?: PartStatus;
  }) {
    const where: any = {
      deletedAt: null,
    };

    if (query?.category) {
      where.category = query.category;
    }

    if (query?.station) {
      where.station = query.station;
    }

    if (query?.warehouseLocation) {
      where.warehouseLocation = query.warehouseLocation;
    }

    if (query?.status) {
      where.status = query.status;
    }

    const parts = await this.prisma.part.findMany({
      where,
      orderBy: { partNumber: 'asc' },
    });

    // 转换为 Excel 行格式
    const rows = parts.map((part) => {
      // 从 customFields 中提取数据
      const customFields = (part.customFields as any) || {};
      
      return {
        'Part Number': part.partNumber,
        'Part Name (EN)': part.partNameEn,
        'Part Name (CN)': part.partNameCn || '',
        'Unit': part.unit,
        'Source': part.source || '',
        'Specifications': part.specifications || '',
        'Remark': part.remark || '',
        'Station': part.station || '',
        'Warehouse Location': part.warehouseLocation || '',
        'Current Stock': part.currentStock,
        'Min Stock': part.minStock,
        'Max Stock': part.maxStock || '',
        'Status': part.status,
        'Image URL': part.imageUrl || '',
        'Created At': part.createdAt.toISOString(),
      'Updated At': part.updatedAt.toISOString(),
        // 可以添加更多 customFields 的导出（如果需要）
        ...customFields,
      };
    });

    return rows;
  }

  /**
   * 获取 Excel 模板
   */
  getExcelTemplate() {
    return [
      {
        'Part Number': 'PN-001',
        'Part Name (EN)': 'Sample Part',
        'Part Name (CN)': '示例零件',
        'Unit': 'pcs',
        'Source': 'Supplier A',
        'Specifications': 'Spec details',
        'Remark': 'Remark info',
        'Station': 'Station A',
        'Warehouse Location': 'A-01-001',
        'Current Stock': '100',
        'Min Stock': '10',
        'Max Stock': '500',
      },
      {
        'Part Number': 'PN-002',
        'Part Name (EN)': 'Sample Part 2',
        'Part Name (CN)': '示例零件2',
        'Unit': 'pcs',
        'Source': 'Supplier B',
        'Specifications': 'Specification',
        'Remark': 'Test remark',
        'Station': 'Station B',
        'Warehouse Location': 'B-02-001',
        'Current Stock': '50',
        'Min Stock': '5',
        'Max Stock': '200',
      },
    ];
  }

  /**
   * 验证 Excel 数据
   */
  validateExcelData(rows: any[]): { valid: boolean; errors: any[]; warnings?: any[] } {
    const errors: any[] = [];
    const warnings: any[] = [];

    if (!Array.isArray(rows) || rows.length === 0) {
      return {
        valid: false,
        errors: [{ error: 'No data found in Excel file' }],
      };
    }

    // 警告：数据量较大
    if (rows.length > 1000) {
      warnings.push({
        message: `Large dataset detected (${rows.length} rows). Import may take several minutes.`,
      });
    }

    // 限制：超过5000行需要分批导入
    if (rows.length > 5000) {
      return {
        valid: false,
        errors: [{
          error: `Too many rows (${rows.length}). Maximum 5000 rows per import. Please split into multiple files.`,
        }],
      };
    }

    // 检查必需的列
    const requiredColumns = ['Part Number', 'Part Name (EN)'];
    const firstRow = rows[0];

    for (const col of requiredColumns) {
      if (!(col in firstRow)) {
        errors.push({
          error: `Missing required column: ${col}`,
        });
      }
    }

    // 检查数据行
    const partNumbers = new Set();

    rows.forEach((row, index) => {
      const rowNumber = index + 2;

      // 检查必填字段
      if (!row['Part Number']) {
        errors.push({
          row: rowNumber,
          error: 'Part Number is required',
        });
      }

      if (!row['Part Name (EN)']) {
        errors.push({
          row: rowNumber,
          error: 'Part Name (EN) is required',
        });
      }

      // 检查重复
      if (row['Part Number']) {
        if (partNumbers.has(row['Part Number'])) {
          errors.push({
            row: rowNumber,
            partNumber: row['Part Number'],
            error: 'Duplicate Part Number in file',
          });
        }
        partNumbers.add(row['Part Number']);
      }

      // 检查数值字段
      const numericFields = [
        'Current Stock',
        'Min Stock',
        'Max Stock',
      ];

      numericFields.forEach((field) => {
        const value = row[field];
        if (value !== '' && value !== null && value !== undefined) {
          if (isNaN(Number(value))) {
            errors.push({
              row: rowNumber,
              field,
              value,
              error: `${field} must be a number`,
            });
          }
        }
      });

      // 检查状态值
      if (row['Status']) {
        const validStatuses = ['ACTIVE', 'INACTIVE', 'DISCONTINUED', 'OBSOLETE'];
        if (!validStatuses.includes(row['Status'])) {
          errors.push({
            row: rowNumber,
            field: 'Status',
            value: row['Status'],
            error: `Invalid status. Must be one of: ${validStatuses.join(', ')}`,
          });
        }
      }
    });

    return {
      valid: errors.length === 0,
      errors,
      warnings: warnings.length > 0 ? warnings : undefined,
    };
  }

  /**
   * 获取导入历史
   */
  async getImportHistory(page: number = 1, limit: number = 20) {
    const skip = (page - 1) * limit;

    const [items, total] = await Promise.all([
      this.prisma.inventoryImportLog.findMany({
        skip,
        take: limit,
        orderBy: { importedAt: 'desc' },
      }),
      this.prisma.inventoryImportLog.count(),
    ]);

    return {
      items,
      total,
      page,
      limit,
      totalPages: Math.ceil(total / limit),
    };
  }

  /**
   * 获取导入日志详情
   */
  async getImportLog(id: string) {
    const log = await this.prisma.inventoryImportLog.findUnique({
      where: { id },
    });

    if (!log) {
      throw new BadRequestException(`Import log with ID ${id} not found`);
    }

    return log;
  }
}