const db = require('../models');
const { sendResponse, sendError } = require('../utils/response');
const asyncHandler = require('../middlewares/asyncHandler');
const { Op } = require('sequelize');

/**
 * Bulk import materials from Excel
 * Converts UOM codes to IDs and creates materials
 */
const bulkImportMaterials = asyncHandler(async (req, res) => {
  const { materials } = req.body;

  if (!Array.isArray(materials) || materials.length === 0) {
    return sendError(res, 'Materials array is required and cannot be empty', 400);
  }

  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;

  if (!companyId) {
    return sendError(res, 'Company ID is required', 400);
  }

  // Get all UOMs to create code to ID mapping
  const uoms = await db.UOM.findAll({
    where: { status: 'ACTIVE' },
    attributes: ['id', 'uom_code'],
  });

  const uomCodeToId = {};
  uoms.forEach((uom) => {
    uomCodeToId[uom.uom_code.toUpperCase()] = uom.id;
  });

  // Get all Tax Masters to create code to ID mapping
  const taxMasters = await db.TaxMaster.findAll({
    where: { 
      company_id: companyId,
      status: 'ACTIVE' 
    },
    attributes: ['id', 'tax_code'],
  });

  const taxCodeToId = {};
  taxMasters.forEach((tax) => {
    taxCodeToId[tax.tax_code.toUpperCase()] = tax.id;
  });

  // Get all Material Types to create ENUM to ID mapping
  const materialTypes = await db.MaterialType.findAll({
    where: { 
      company_id: companyId,
      is_active: true 
    },
    attributes: ['id', 'type_code'],
  });

  const materialTypeEnumToId = {};
  const materialTypeCodeToId = {};
  materialTypes.forEach((mt) => {
    materialTypeCodeToId[mt.type_code.toUpperCase()] = mt.id;
    // Map ENUM values to type_code and then to ID
    const enumToCodeMap = {
      'RAW_MATERIAL': 'RAW_MAT',
      'SEMI_FINISHED': 'SEMI_FIN',
      'FINISHED_GOOD': 'FIN_GOOD',
      'PACKAGING': 'PACKAGING',
      'CONSUMABLE': 'CONSUMABLE',
      'SERVICE': 'SERVICE',
      'ASSET': 'ASSET'
    };
    Object.entries(enumToCodeMap).forEach(([enumValue, typeCode]) => {
      if (mt.type_code.toUpperCase() === typeCode) {
        materialTypeEnumToId[enumValue] = mt.id;
      }
    });
  });

  // Get all Material Groups to create code to ID mapping
  const materialGroups = await db.MaterialGroup.findAll({
    where: { 
      company_id: companyId,
      is_active: true 
    },
    attributes: ['id', 'group_code', 'group_name'],
  });

  const materialGroupCodeToId = {};
  const materialGroupNameToId = {};
  materialGroups.forEach((mg) => {
    materialGroupCodeToId[mg.group_code.toUpperCase()] = mg.id;
    materialGroupNameToId[mg.group_name.toUpperCase()] = mg.id;
  });

  const results = {
    created: 0,
    failed: 0,
    errors: [],
  };

  // Process each material
  for (let i = 0; i < materials.length; i++) {
    const materialData = materials[i];
    const rowNumber = materialData.rowNumber || i + 1;

    try {
      // Validate required fields
      if (!materialData.material_number?.trim()) {
        results.errors.push({
          row: rowNumber,
          material_number: materialData.material_number || '',
          message: 'Material Number is required',
        });
        results.failed++;
        continue;
      }

      if (!materialData.material_type) {
        results.errors.push({
          row: rowNumber,
          material_number: materialData.material_number || '',
          message: 'Material Type is required',
        });
        results.failed++;
        continue;
      }

      if (!materialData.description?.trim()) {
        results.errors.push({
          row: rowNumber,
          material_number: materialData.material_number || '',
          message: 'Description is required',
        });
        results.failed++;
        continue;
      }

      if (!materialData.base_uom_code?.trim()) {
        results.errors.push({
          row: rowNumber,
          material_number: materialData.material_number || '',
          message: 'Base UOM Code is required',
        });
        results.failed++;
        continue;
      }

      // Convert UOM codes to IDs
      const baseUomCode = materialData.base_uom_code.trim().toUpperCase();
      const baseUomId = uomCodeToId[baseUomCode];

      if (!baseUomId) {
        results.errors.push({
          row: rowNumber,
          material_number: materialData.material_number || '',
          message: `Base UOM Code "${materialData.base_uom_code}" not found`,
        });
        results.failed++;
        continue;
      }

      let weightUomId = null;
      if (materialData.weight_uom_code?.trim()) {
        const weightUomCode = materialData.weight_uom_code.trim().toUpperCase();
        weightUomId = uomCodeToId[weightUomCode];
        if (!weightUomId) {
          results.errors.push({
            row: rowNumber,
            material_number: materialData.material_number || '',
            message: `Weight UOM Code "${materialData.weight_uom_code}" not found`,
          });
          results.failed++;
          continue;
        }
      }

      let volumeUomId = null;
      if (materialData.volume_uom_code?.trim()) {
        const volumeUomCode = materialData.volume_uom_code.trim().toUpperCase();
        volumeUomId = uomCodeToId[volumeUomCode];
        if (!volumeUomId) {
          results.errors.push({
            row: rowNumber,
            material_number: materialData.material_number || '',
            message: `Volume UOM Code "${materialData.volume_uom_code}" not found`,
          });
          results.failed++;
          continue;
        }
      }

      // Convert Material Type (ENUM or type_code) to material_type_id
      let materialTypeId = null;
      const materialTypeInput = materialData.material_type.trim().toUpperCase();
      
      // Try ENUM value first (for backward compatibility)
      if (materialTypeEnumToId[materialTypeInput]) {
        materialTypeId = materialTypeEnumToId[materialTypeInput];
      } 
      // Try type_code
      else if (materialTypeCodeToId[materialTypeInput]) {
        materialTypeId = materialTypeCodeToId[materialTypeInput];
      }
      // If not found, try to match by mapping common variations
      else {
        const typeCodeVariations = {
          'RAW_MAT': 'RAW_MAT',
          'RAW MATERIAL': 'RAW_MAT',
          'SEMI_FIN': 'SEMI_FIN',
          'SEMI FINISHED': 'SEMI_FIN',
          'FIN_GOOD': 'FIN_GOOD',
          'FINISHED GOOD': 'FIN_GOOD',
          'FINISHED_GOOD': 'FIN_GOOD'
        };
        const normalizedType = typeCodeVariations[materialTypeInput] || materialTypeInput;
        materialTypeId = materialTypeCodeToId[normalizedType];
      }

      if (!materialTypeId) {
        results.errors.push({
          row: rowNumber,
          material_number: materialData.material_number || '',
          message: `Material Type "${materialData.material_type}" not found in Material Type master`,
        });
        results.failed++;
        continue;
      }

      // Convert Material Group (code or name) to material_group_id
      let materialGroupId = null;
      if (materialData.material_group?.trim()) {
        const materialGroupInput = materialData.material_group.trim().toUpperCase();
        materialGroupId = materialGroupCodeToId[materialGroupInput] || materialGroupNameToId[materialGroupInput];
        
        if (!materialGroupId) {
          // Material group is optional, so log warning but don't fail
          results.errors.push({
            row: rowNumber,
            material_number: materialData.material_number || '',
            message: `Warning: Material Group "${materialData.material_group}" not found. Material will be created without group.`,
          });
        }
      }

      // Convert Tax Code to Tax ID (optional field)
      let taxId = null;
      if (materialData.tax_code?.trim()) {
        const taxCode = materialData.tax_code.trim().toUpperCase();
        taxId = taxCodeToId[taxCode];
        if (!taxId) {
          // Tax code is optional, so log warning but don't fail the import
          results.errors.push({
            row: rowNumber,
            material_number: materialData.material_number || '',
            message: `Warning: Tax Code "${materialData.tax_code}" not found. Material will be created without tax.`,
          });
          // Continue without tax_id (set to null)
          taxId = null;
        }
      }

      // Check if material number already exists for this company
      const existingMaterial = await db.Material.findOne({
        where: {
          company_id: companyId,
          material_number: materialData.material_number.trim(),
          status: { [Op.ne]: 'DELETED' },
        },
      });

      if (existingMaterial) {
        results.errors.push({
          row: rowNumber,
          material_number: materialData.material_number || '',
          message: `Material Number "${materialData.material_number}" already exists`,
        });
        results.failed++;
        continue;
      }

      // Prepare material data
      const materialToCreate = {
        company_id: companyId,
        material_number: materialData.material_number.trim(),
        material_type: materialData.material_type.toUpperCase(), // Legacy ENUM field
        material_type_id: materialTypeId, // New master table reference
        description: materialData.description.trim(),
        material_group: materialData.material_group?.trim() || null, // Legacy string field
        material_group_id: materialGroupId, // New master table reference
        base_uom_id: baseUomId,
        hsn_code: materialData.hsn_code?.trim() || null,
        sac_code: materialData.sac_code?.trim() || null,
        tax_id: taxId,
        gross_weight: materialData.gross_weight ? parseFloat(materialData.gross_weight) : null,
        net_weight: materialData.net_weight ? parseFloat(materialData.net_weight) : null,
        weight_uom_id: weightUomId,
        volume: materialData.volume ? parseFloat(materialData.volume) : null,
        volume_uom_id: volumeUomId,
        is_batch_managed: materialData.is_batch_managed || false,
        is_serial_managed: materialData.is_serial_managed || false,
        is_shelf_life_managed: materialData.is_shelf_life_managed || false,
        shelf_life_days: materialData.shelf_life_days || null,
        status: materialData.status?.toUpperCase() || 'ACTIVE',
        created_by: userId,
        updated_by: userId,
      };

      // Create material
      await db.Material.create(materialToCreate);
      results.created++;
    } catch (error) {
      results.errors.push({
        row: rowNumber,
        material_number: materialData.material_number || '',
        message: error.message || 'Failed to create material',
      });
      results.failed++;
    }
  }

  sendResponse(res, 200, true, 'Bulk import completed', results);
});

module.exports = {
  bulkImportMaterials,
};

