const XLSX = require('xlsx');
const Database = require('../models/database');
const logger = require('../utils/logger');
const { withFileRetry } = require('../utils/retry');
const { 
  ValidationError, 
  BusinessLogicError,
  AppError 
} = require('../middleware/errorHandler');

/**
 * Service for parsing and importing Excel files containing inventory data
 */
class ExcelImportService {
  constructor() {
    // Common column name patterns for auto-detection
    this.columnPatterns = {
      productCode: [
        'product_code', 'productcode', 'product code', 'code', 'item_code', 
        'itemcode', 'item code', 'sku', 'part_number', 'partnumber', 'part number',
        'item_id', 'itemid', 'item id'
      ],
      description: [
        'description', 'desc', 'product_description', 'productdescription',
        'product description', 'name', 'product_name', 'productname', 'product name',
        'item_name', 'itemname', 'item name', 'title'
      ],
      quantity: [
        'quantity', 'qty', 'current_quantity', 'currentquantity', 'current quantity',
        'inventory', 'stock', 'current_stock', 'currentstock', 'current stock',
        'level', 'inventory_level', 'inventorylevel', 'inventory level',
        'stock_count', 'stockcount', 'stock count'
      ],
      category: [
        'category', 'cat', 'product_category', 'productcategory', 'product category',
        'type', 'group', 'classification', 'product_type', 'producttype', 'product type'
      ]
    };
  }

  /**
   * Parse Excel file buffer and extract inventory data
   * @param {Buffer} fileBuffer - Excel file buffer
   * @param {Object} options - Parsing options
   * @returns {Object} Parsed data with products and metadata
   */
  async parseExcelFile(fileBuffer, options = {}) {
    const startTime = Date.now();
    
    try {
      logger.info('Starting Excel file parsing', {
        fileSize: fileBuffer.length,
        options: options
      });

      // Validate file buffer
      if (!fileBuffer || fileBuffer.length === 0) {
        throw new ValidationError('Empty or invalid file buffer provided');
      }

      if (fileBuffer.length > 50 * 1024 * 1024) { // 50MB limit
        throw new ValidationError('File size exceeds maximum limit of 50MB');
      }

      // Parse Excel file with retry logic for file operations
      const workbook = await withFileRetry(async () => {
        return XLSX.read(fileBuffer, { 
          type: 'buffer',
          cellDates: true,
          cellNF: false,
          cellText: false
        });
      });
      
      // Validate workbook
      if (!workbook || !workbook.SheetNames || workbook.SheetNames.length === 0) {
        throw new ValidationError('Invalid Excel file: No worksheets found');
      }

      // Get the first worksheet (or specified sheet)
      const sheetName = options.sheetName || workbook.SheetNames[0];
      const worksheet = workbook.Sheets[sheetName];
      
      if (!worksheet) {
        throw new ValidationError(`Sheet "${sheetName}" not found in Excel file. Available sheets: ${workbook.SheetNames.join(', ')}`);
      }

      logger.debug('Excel worksheet selected', {
        sheetName,
        availableSheets: workbook.SheetNames
      });

      // Convert worksheet to JSON
      const rawData = XLSX.utils.sheet_to_json(worksheet, { 
        header: 1, // Use array format to preserve original structure
        defval: '' // Default value for empty cells
      });

      if (rawData.length === 0) {
        throw new ValidationError('Excel file appears to be empty');
      }

      if (rawData.length === 1) {
        throw new ValidationError('Excel file contains only headers, no data rows found');
      }

      // Extract header row and data rows
      const headerRow = rawData[0];
      const dataRows = rawData.slice(1);

      logger.debug('Excel data extracted', {
        headerCount: headerRow.length,
        dataRowCount: dataRows.length,
        headers: headerRow
      });

      // Detect column mappings
      const columnMapping = this.detectColumns(headerRow);
      
      // Validate that required columns were found
      if (!columnMapping.productCode) {
        throw new ValidationError('Could not detect product code column. Please ensure your Excel file has a column for product codes.');
      }
      
      // Parse data rows
      const parsedProducts = this.parseDataRows(dataRows, columnMapping, headerRow);

      const duration = Date.now() - startTime;
      
      logger.info('Excel file parsing completed', {
        duration: `${duration}ms`,
        totalRows: dataRows.length,
        parsedProducts: parsedProducts.length,
        sheetName
      });

      return {
        success: true,
        data: {
          products: parsedProducts,
          totalRows: dataRows.length,
          columnMapping: columnMapping,
          sheetName: sheetName,
          availableSheets: workbook.SheetNames
        },
        errors: []
      };

    } catch (error) {
      const duration = Date.now() - startTime;
      
      logger.error('Excel file parsing failed', {
        duration: `${duration}ms`,
        error: error.message,
        fileSize: fileBuffer.length,
        options
      });

      // Re-throw validation errors as-is
      if (error instanceof ValidationError) {
        throw error;
      }

      // Convert other errors to appropriate types
      if (error.message.includes('Unsupported file')) {
        throw new ValidationError('Unsupported file format. Please upload a valid Excel file (.xlsx or .xls)');
      }

      if (error.message.includes('file is encrypted')) {
        throw new ValidationError('Encrypted Excel files are not supported. Please remove password protection and try again.');
      }

      // Generic parsing error
      throw new ValidationError(`Failed to parse Excel file: ${error.message}`);
    }
  }

  /**
   * Detect column mappings based on header row
   * @param {Array} headerRow - Array of column headers
   * @returns {Object} Column mapping object
   */
  detectColumns(headerRow) {
    const mapping = {
      productCode: null,
      description: null,
      quantity: null,
      category: null
    };

    // Convert headers to lowercase for comparison
    const normalizedHeaders = headerRow.map(header => 
      typeof header === 'string' ? header.toLowerCase().trim() : ''
    );

    // Find matches for each column type
    Object.keys(this.columnPatterns).forEach(columnType => {
      const patterns = this.columnPatterns[columnType];
      
      for (let i = 0; i < normalizedHeaders.length; i++) {
        const header = normalizedHeaders[i];
        
        // Check for exact matches first
        if (patterns.includes(header)) {
          mapping[columnType] = i;
          break;
        }
        
        // Check for partial matches
        const partialMatch = patterns.find(pattern => 
          header.includes(pattern) || pattern.includes(header)
        );
        
        if (partialMatch && mapping[columnType] === null) {
          mapping[columnType] = i;
        }
      }
    });

    return mapping;
  }

  /**
   * Parse data rows using column mapping
   * @param {Array} dataRows - Array of data rows
   * @param {Object} columnMapping - Column mapping object
   * @param {Array} headerRow - Original header row for reference
   * @returns {Array} Array of parsed product objects
   */
  parseDataRows(dataRows, columnMapping, headerRow) {
    const products = [];

    dataRows.forEach((row, index) => {
      // Skip empty rows
      if (!row || row.every(cell => !cell || cell.toString().trim() === '')) {
        return;
      }

      const product = {
        rowNumber: index + 2, // +2 because we start from row 2 (after header)
        productCode: this.getCellValue(row, columnMapping.productCode),
        description: this.getCellValue(row, columnMapping.description),
        quantity: this.parseQuantity(this.getCellValue(row, columnMapping.quantity)),
        category: this.getCellValue(row, columnMapping.category),
        originalRow: row,
        errors: []
      };

      // Basic validation
      if (!product.productCode) {
        product.errors.push({
          type: 'MISSING_PRODUCT_CODE',
          message: 'Product code is required but missing'
        });
      }

      if (product.quantity === null) {
        product.errors.push({
          type: 'INVALID_QUANTITY',
          message: 'Quantity must be a valid number'
        });
      }

      products.push(product);
    });

    return products;
  }

  /**
   * Get cell value safely
   * @param {Array} row - Data row
   * @param {number} columnIndex - Column index
   * @returns {string} Cell value or empty string
   */
  getCellValue(row, columnIndex) {
    if (columnIndex === null || columnIndex >= row.length) {
      return '';
    }
    
    const value = row[columnIndex];
    if (value === null || value === undefined) {
      return '';
    }
    return value.toString().trim();
  }

  /**
   * Parse quantity value to number
   * @param {string} value - Raw quantity value
   * @returns {number|null} Parsed quantity or null if invalid
   */
  parseQuantity(value) {
    if (!value || value === '') {
      return 0; // Default to 0 for empty quantities
    }

    // Remove common non-numeric characters
    const cleanValue = value.toString().replace(/[,\s]/g, '');
    const parsed = parseFloat(cleanValue);
    
    return isNaN(parsed) ? null : Math.max(0, Math.floor(parsed)); // Ensure non-negative integer
  }

  /**
   * Get column mapping suggestions for manual mapping
   * @param {Array} headerRow - Array of column headers
   * @returns {Object} Suggestions for each column type
   */
  getColumnSuggestions(headerRow) {
    const suggestions = {};
    
    Object.keys(this.columnPatterns).forEach(columnType => {
      suggestions[columnType] = [];
      
      headerRow.forEach((header, index) => {
        if (typeof header === 'string' && header.trim()) {
          const normalizedHeader = header.toLowerCase().trim();
          const patterns = this.columnPatterns[columnType];
          
          // Calculate relevance score
          let score = 0;
          patterns.forEach(pattern => {
            if (normalizedHeader === pattern) {
              score += 10; // Exact match
            } else if (normalizedHeader.includes(pattern)) {
              score += 5; // Contains pattern
            } else if (pattern.includes(normalizedHeader)) {
              score += 3; // Pattern contains header
            }
          });
          
          if (score > 0) {
            suggestions[columnType].push({
              index: index,
              header: header,
              score: score
            });
          }
        }
      });
      
      // Sort by score descending
      suggestions[columnType].sort((a, b) => b.score - a.score);
    });
    
    return suggestions;
  }

  /**
   * Validate parsed data for common issues
   * @param {Array} products - Array of parsed products
   * @returns {Object} Validation results
   */
  validateParsedData(products) {
    const validation = {
      isValid: true,
      errors: [],
      warnings: [],
      duplicates: [],
      statistics: {
        totalProducts: products.length,
        validProducts: 0,
        invalidProducts: 0,
        duplicateProducts: 0
      }
    };

    const productCodes = new Set();
    const duplicateCodes = new Set();

    products.forEach(product => {
      // Check for duplicates
      if (product.productCode) {
        if (productCodes.has(product.productCode)) {
          duplicateCodes.add(product.productCode);
          validation.duplicates.push({
            productCode: product.productCode,
            rows: [product.rowNumber] // Will be expanded when we find all duplicates
          });
        } else {
          productCodes.add(product.productCode);
        }
      }

      // Count valid/invalid products
      if (product.errors.length === 0) {
        validation.statistics.validProducts++;
      } else {
        validation.statistics.invalidProducts++;
        validation.isValid = false;
      }
    });

    // Update duplicate statistics
    validation.statistics.duplicateProducts = duplicateCodes.size;

    // Add warnings for duplicates
    if (duplicateCodes.size > 0) {
      validation.warnings.push({
        type: 'DUPLICATE_PRODUCT_CODES',
        message: `Found ${duplicateCodes.size} duplicate product codes`,
        details: Array.from(duplicateCodes)
      });
    }

    return validation;
  }

  /**
   * Comprehensive validation of parsed data with detailed error reporting
   * @param {Array} products - Array of parsed products
   * @param {Object} options - Validation options
   * @returns {Object} Detailed validation results
   */
  async validateImportData(products, options = {}) {
    const validation = {
      isValid: true,
      errors: [],
      warnings: [],
      duplicates: [],
      statistics: {
        totalProducts: products.length,
        validProducts: 0,
        invalidProducts: 0,
        duplicateProducts: 0,
        existingProducts: 0
      },
      validatedProducts: []
    };

    const productCodes = new Map(); // Track duplicates with row numbers
    const duplicateCodes = new Set();
    
    // Check for existing products in database if requested
    let existingProducts = new Set();
    if (options.checkExisting) {
      try {
        const db = Database.getInstance();
        const existing = db.prepare('SELECT product_code FROM products').all();
        existingProducts = new Set(existing.map(p => p.product_code));
      } catch (error) {
        validation.warnings.push({
          type: 'DATABASE_CHECK_FAILED',
          message: 'Could not check for existing products in database',
          details: error.message
        });
      }
    }

    // Validate each product
    for (let i = 0; i < products.length; i++) {
      const product = products[i];
      const validatedProduct = { ...product };
      
      // Reset errors for comprehensive validation
      validatedProduct.errors = [];
      validatedProduct.warnings = [];

      // 1. Product Code Validation
      if (!product.productCode || product.productCode.trim() === '') {
        validatedProduct.errors.push({
          type: 'MISSING_PRODUCT_CODE',
          message: 'Product code is required but missing',
          field: 'productCode'
        });
      } else {
        // Check product code format
        const codeValidation = this.validateProductCode(product.productCode);
        if (!codeValidation.isValid) {
          validatedProduct.errors.push({
            type: 'INVALID_PRODUCT_CODE_FORMAT',
            message: codeValidation.message,
            field: 'productCode'
          });
        }

        // Check for duplicates within the import
        if (productCodes.has(product.productCode)) {
          duplicateCodes.add(product.productCode);
          const existingRows = productCodes.get(product.productCode);
          existingRows.push(product.rowNumber);
          
          validatedProduct.errors.push({
            type: 'DUPLICATE_PRODUCT_CODE',
            message: `Product code "${product.productCode}" appears in multiple rows: ${existingRows.join(', ')}`,
            field: 'productCode'
          });
        } else {
          productCodes.set(product.productCode, [product.rowNumber]);
        }

        // Check if product exists in database
        if (existingProducts.has(product.productCode)) {
          validation.statistics.existingProducts++;
          validatedProduct.warnings.push({
            type: 'PRODUCT_EXISTS',
            message: `Product code "${product.productCode}" already exists in database`,
            field: 'productCode'
          });
        }
      }

      // 2. Description Validation
      if (product.description && product.description.length > 500) {
        validatedProduct.errors.push({
          type: 'DESCRIPTION_TOO_LONG',
          message: 'Description cannot exceed 500 characters',
          field: 'description'
        });
      }

      // 3. Quantity Validation
      if (product.quantity === null) {
        validatedProduct.errors.push({
          type: 'INVALID_QUANTITY',
          message: 'Quantity must be a valid non-negative number',
          field: 'quantity'
        });
      } else if (product.quantity < 0) {
        validatedProduct.errors.push({
          type: 'NEGATIVE_QUANTITY',
          message: 'Quantity cannot be negative',
          field: 'quantity'
        });
      } else if (product.quantity > 1000000) {
        validatedProduct.warnings.push({
          type: 'LARGE_QUANTITY',
          message: 'Quantity is unusually large, please verify',
          field: 'quantity'
        });
      }

      // 4. Category Validation
      if (product.category && product.category.length > 100) {
        validatedProduct.errors.push({
          type: 'CATEGORY_TOO_LONG',
          message: 'Category cannot exceed 100 characters',
          field: 'category'
        });
      }

      // Count valid/invalid products
      if (validatedProduct.errors.length === 0) {
        validation.statistics.validProducts++;
      } else {
        validation.statistics.invalidProducts++;
        validation.isValid = false;
      }

      validation.validatedProducts.push(validatedProduct);
    }

    // Process duplicates
    validation.statistics.duplicateProducts = duplicateCodes.size;
    duplicateCodes.forEach(code => {
      const rows = productCodes.get(code);
      validation.duplicates.push({
        productCode: code,
        rows: rows,
        count: rows.length
      });
    });

    // Add summary warnings
    if (duplicateCodes.size > 0) {
      validation.warnings.push({
        type: 'DUPLICATE_PRODUCT_CODES',
        message: `Found ${duplicateCodes.size} duplicate product codes affecting ${Array.from(duplicateCodes).reduce((sum, code) => sum + productCodes.get(code).length, 0)} rows`,
        details: Array.from(duplicateCodes)
      });
    }

    if (validation.statistics.existingProducts > 0) {
      validation.warnings.push({
        type: 'EXISTING_PRODUCTS_FOUND',
        message: `${validation.statistics.existingProducts} products already exist in the database`,
        details: validation.statistics.existingProducts
      });
    }

    return validation;
  }

  /**
   * Validate product code format
   * @param {string} productCode - Product code to validate
   * @returns {Object} Validation result
   */
  validateProductCode(productCode) {
    const code = productCode.trim();
    
    if (code.length === 0) {
      return { isValid: false, message: 'Product code cannot be empty' };
    }
    
    if (code.length > 50) {
      return { isValid: false, message: 'Product code cannot exceed 50 characters' };
    }
    
    // Check for invalid characters (allow alphanumeric, hyphens, underscores)
    if (!/^[A-Za-z0-9\-_]+$/.test(code)) {
      return { isValid: false, message: 'Product code can only contain letters, numbers, hyphens, and underscores' };
    }
    
    return { isValid: true, message: 'Valid product code' };
  }

  /**
   * Handle duplicate products based on specified strategy
   * @param {Array} products - Array of validated products
   * @param {string} duplicateStrategy - 'skip', 'update', or 'rename'
   * @returns {Array} Processed products array
   */
  handleDuplicates(products, duplicateStrategy = 'skip') {
    const processedProducts = [];
    const seenCodes = new Set();
    const duplicateCounters = new Map();

    products.forEach(product => {
      if (!product.productCode) {
        processedProducts.push(product);
        return;
      }

      const isDuplicate = seenCodes.has(product.productCode);
      
      if (!isDuplicate) {
        seenCodes.add(product.productCode);
        processedProducts.push(product);
        return;
      }

      // Handle duplicate based on strategy
      switch (duplicateStrategy) {
        case 'skip':
          // Skip duplicate, add warning
          product.warnings = product.warnings || [];
          product.warnings.push({
            type: 'DUPLICATE_SKIPPED',
            message: `Duplicate product code "${product.productCode}" skipped`,
            field: 'productCode'
          });
          product.skipped = true;
          processedProducts.push(product);
          break;

        case 'update':
          // Mark for update instead of insert
          product.warnings = product.warnings || [];
          product.warnings.push({
            type: 'DUPLICATE_WILL_UPDATE',
            message: `Duplicate product code "${product.productCode}" will update existing record`,
            field: 'productCode'
          });
          product.updateExisting = true;
          processedProducts.push(product);
          break;

        case 'rename':
          // Rename with suffix
          const baseCode = product.productCode;
          const counter = (duplicateCounters.get(baseCode) || 1) + 1;
          duplicateCounters.set(baseCode, counter);
          
          const newCode = `${baseCode}_${counter}`;
          product.originalProductCode = product.productCode;
          product.productCode = newCode;
          product.warnings = product.warnings || [];
          product.warnings.push({
            type: 'DUPLICATE_RENAMED',
            message: `Duplicate product code renamed from "${baseCode}" to "${newCode}"`,
            field: 'productCode'
          });
          
          seenCodes.add(newCode);
          processedProducts.push(product);
          break;

        default:
          processedProducts.push(product);
      }
    });

    return processedProducts;
  }

  /**
   * Import products to database with transaction support
   * @param {Array} products - Array of validated products
   * @param {Object} options - Import options
   * @returns {Object} Import results
   */
  async importToDatabase(products, options = {}) {
    const results = {
      success: false,
      imported: 0,
      updated: 0,
      skipped: 0,
      failed: 0,
      errors: [],
      sessionId: null
    };

    try {
      const db = Database.getInstance();
      
      // Create import session record
      const sessionResult = db.prepare(`
        INSERT INTO import_sessions (filename, total_records, status)
        VALUES (?, ?, 'in_progress')
      `).run(options.filename || 'unknown', products.length);
      
      results.sessionId = sessionResult.lastInsertRowid;

      // Begin transaction
      const transaction = db.transaction((productsToImport) => {
        const insertProduct = db.prepare(`
          INSERT INTO products (product_code, description, category, created_at, updated_at)
          VALUES (?, ?, ?, datetime('now'), datetime('now'))
        `);

        const updateProduct = db.prepare(`
          UPDATE products 
          SET description = ?, category = ?, updated_at = datetime('now')
          WHERE product_code = ?
        `);

        const insertInventory = db.prepare(`
          INSERT INTO inventory (product_id, current_level, last_updated, updated_by)
          VALUES (?, ?, datetime('now'), ?)
        `);

        const updateInventory = db.prepare(`
          UPDATE inventory 
          SET current_level = ?, last_updated = datetime('now'), updated_by = ?
          WHERE product_id = (SELECT id FROM products WHERE product_code = ?)
        `);

        const getProductId = db.prepare(`
          SELECT id FROM products WHERE product_code = ?
        `);

        productsToImport.forEach(product => {
          try {
            // Skip products with errors or marked as skipped
            if (product.errors?.length > 0 || product.skipped) {
              results.skipped++;
              return;
            }

            let productId;

            if (product.updateExisting) {
              // Update existing product
              updateProduct.run(
                product.description || '',
                product.category || '',
                product.productCode
              );
              
              const existingProduct = getProductId.get(product.productCode);
              if (existingProduct) {
                productId = existingProduct.id;
                results.updated++;
              } else {
                throw new Error(`Product ${product.productCode} not found for update`);
              }
            } else {
              // Insert new product
              const productResult = insertProduct.run(
                product.productCode,
                product.description || '',
                product.category || ''
              );
              productId = productResult.lastInsertRowid;
              results.imported++;
            }

            // Handle inventory
            if (product.quantity !== null && product.quantity !== undefined) {
              if (product.updateExisting) {
                updateInventory.run(
                  product.quantity,
                  options.updatedBy || 'system',
                  product.productCode
                );
              } else {
                insertInventory.run(
                  productId,
                  product.quantity,
                  options.updatedBy || 'system'
                );
              }
            }

          } catch (error) {
            results.failed++;
            results.errors.push({
              productCode: product.productCode,
              rowNumber: product.rowNumber,
              error: error.message
            });
          }
        });
      });

      // Execute transaction
      transaction(products);

      // Update import session
      db.prepare(`
        UPDATE import_sessions 
        SET successful_imports = ?, failed_imports = ?, status = 'completed'
        WHERE id = ?
      `).run(results.imported + results.updated, results.failed, results.sessionId);

      results.success = true;

    } catch (error) {
      results.errors.push({
        type: 'TRANSACTION_ERROR',
        message: error.message
      });

      // Update session as failed if it was created
      if (results.sessionId) {
        try {
          const db = Database.getInstance();
          db.prepare(`
            UPDATE import_sessions 
            SET status = 'failed'
            WHERE id = ?
          `).run(results.sessionId);
        } catch (updateError) {
          // Ignore update errors
        }
      }
    }

    return results;
  }

  /**
   * Complete import process with validation and error handling
   * @param {Buffer} fileBuffer - Excel file buffer
   * @param {Object} options - Import options
   * @returns {Object} Complete import results
   */
  async processImport(fileBuffer, options = {}) {
    const results = {
      success: false,
      parseResults: null,
      validationResults: null,
      importResults: null,
      errors: []
    };

    try {
      // Step 1: Parse Excel file
      results.parseResults = await this.parseExcelFile(fileBuffer, options);
      
      if (!results.parseResults.success) {
        results.errors.push(...results.parseResults.errors);
        return results;
      }

      // Step 2: Validate data
      results.validationResults = await this.validateImportData(
        results.parseResults.data.products,
        { checkExisting: options.checkExisting !== false }
      );

      // Step 3: Handle duplicates if strategy specified
      let productsToImport = results.validationResults.validatedProducts;
      if (options.duplicateStrategy && options.duplicateStrategy !== 'error') {
        productsToImport = this.handleDuplicates(productsToImport, options.duplicateStrategy);
      }

      // Step 4: Import to database if requested and validation passed
      if (options.importToDatabase && (results.validationResults.isValid || options.forceImport)) {
        results.importResults = await this.importToDatabase(productsToImport, options);
      }

      results.success = true;

    } catch (error) {
      results.errors.push({
        type: 'PROCESS_ERROR',
        message: error.message
      });
    }

    return results;
  }}


module.exports = ExcelImportService;