'use strict';

/**
 * Complete Cabinet BOM Structure Seeder
 * 
 * Structure:
 * Level 0: Finished Good (CABINET - RCAB00958R4)
 *   └── Level 1: Main Assemblies (7 assemblies)
 *         └── Level 2: Sub-Assemblies (for FRAME ASSY)
 *               └── Level 3: Raw Materials
 * 
 * This seeder:
 * 1. Creates all materials (Finished Good, Main Assemblies, Sub-Assemblies, Raw Materials)
 * 2. Creates BOM for Finished Good (CABINET)
 * 3. Creates BOM for Main Assembly (FRAME ASSY) with its sub-assemblies
 */

module.exports = {
  async up(queryInterface, Sequelize) {
    // Get required master data
    const [companies, plants, uoms, materialTypes, materialGroups, users] = await Promise.all([
      queryInterface.sequelize.query("SELECT id FROM company LIMIT 1"),
      queryInterface.sequelize.query("SELECT id, company_id FROM plant LIMIT 1"),
      queryInterface.sequelize.query("SELECT id, uom_code FROM uom WHERE uom_code IN ('EA', 'PC', 'PCS', 'NOS') OR uom_code LIKE '%EA%' LIMIT 10"),
      queryInterface.sequelize.query("SELECT id, type_code FROM material_type WHERE type_code IN ('FIN_GOOD', 'SEMI_FIN', 'RAW_MAT')"),
      queryInterface.sequelize.query("SELECT id, group_code FROM material_group WHERE group_code IN ('CABINET', 'ASSEMBLY', 'PARTS', 'FASTENERS') LIMIT 10"),
      queryInterface.sequelize.query("SELECT id FROM user WHERE username = 'admin' LIMIT 1")
    ]);

    if (companies[0].length === 0 || plants[0].length === 0 || users[0].length === 0) {
      console.log('Required master data not found. Skipping seeder.');
      return;
    }

    const companyId = companies[0][0].id;
    const plantId = plants[0][0].id;
    const userId = users[0][0].id;

    // Create UOM map
    const uomMap = {};
    uoms[0].forEach(uom => {
      uomMap[uom.uom_code] = uom.id;
    });
    const defaultUOMId = uomMap['EA'] || uomMap['PC'] || uomMap['PCS'] || uoms[0][0]?.id;

    // Create Material Type map
    const materialTypeMap = {};
    const materialTypeIdToEnumMap = {};
    materialTypes[0].forEach(mt => {
      materialTypeMap[mt.type_code] = mt.id;
      const enumMap = {
        'FIN_GOOD': 'FINISHED_GOOD',
        'SEMI_FIN': 'SEMI_FINISHED',
        'RAW_MAT': 'RAW_MATERIAL'
      };
      if (enumMap[mt.type_code]) {
        materialTypeIdToEnumMap[mt.id] = enumMap[mt.type_code];
      }
    });

    // Create Material Group map
    const materialGroupMap = {};
    materialGroups[0].forEach(mg => {
      materialGroupMap[mg.group_code] = mg.id;
    });

    // Helper function to get ENUM value from material_type_id
    const getMaterialTypeEnum = (materialTypeId) => {
      return materialTypeIdToEnumMap[materialTypeId] || 'RAW_MATERIAL';
    };

    console.log('Creating Complete Cabinet BOM Structure...\n');

    // Define all materials
    const materials = [
      // ===== LEVEL 0: Finished Good =====
      {
        material_number: 'RCAB00958R4',
        description: 'CABINET - 12-15KVA SCVS DM DG',
        material_type_id: materialTypeMap['FIN_GOOD'],
        material_group_id: materialGroupMap['CABINET'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R4',
        revision_number: 'REV 04',
        status: 'ACTIVE'
      },

      // ===== LEVEL 1: Main Assemblies =====
      {
        material_number: 'RCAB00958R3-01',
        description: 'FRAME ASSY',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group_id: materialGroupMap['ASSEMBLY'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R3-01',
        revision_number: 'REV 03',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R2-03',
        description: 'FRONT DOOR ASSY',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group_id: materialGroupMap['ASSEMBLY'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R2-03',
        revision_number: 'REV 02',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R4-04',
        description: 'LEFT COVER',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group_id: materialGroupMap['ASSEMBLY'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R4-04',
        revision_number: 'REV 04',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-05',
        description: 'RIGHT COVER ASSY',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group_id: materialGroupMap['ASSEMBLY'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R0-05',
        revision_number: 'REV 00',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R4-06',
        description: 'REAR SIDE COVER',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group_id: materialGroupMap['ASSEMBLY'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R4-06',
        revision_number: 'REV 04',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R4-07',
        description: 'TOP COVER',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group_id: materialGroupMap['ASSEMBLY'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R4-07',
        revision_number: 'REV 04',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R1-08',
        description: 'TERMINAL BOX ASSY',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group_id: materialGroupMap['ASSEMBLY'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R1-08',
        revision_number: 'REV 01',
        status: 'ACTIVE'
      },

      // ===== LEVEL 2: Sub-Assemblies (for FRAME ASSY) =====
      {
        material_number: 'RCAB00958R4-01-01',
        description: 'BOTTOM PLATE ASSY',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group_id: materialGroupMap['ASSEMBLY'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R4-01-01',
        revision_number: 'REV 04',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-02',
        description: 'TOP FRAME ASSY',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group_id: materialGroupMap['ASSEMBLY'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R0-01-02',
        revision_number: 'REV 00',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R2-01-03',
        description: 'MIDDLE FRAME 1 ASSY',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group_id: materialGroupMap['ASSEMBLY'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R2-01-03',
        revision_number: 'REV 02',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-05',
        description: 'MOTOR CLAMP ASSY',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group_id: materialGroupMap['ASSEMBLY'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R0-01-05',
        revision_number: 'REV 00',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R1-01-06',
        description: 'SMC PLATE ASSY',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group_id: materialGroupMap['ASSEMBLY'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R1-01-06',
        revision_number: 'REV 01',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-07',
        description: 'CORNER PIECE ASSY 1',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group_id: materialGroupMap['ASSEMBLY'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R0-01-07',
        revision_number: 'REV 00',
        status: 'ACTIVE'
      },

      // ===== LEVEL 3: Raw Materials =====
      // Raw Materials for FRAME ASSY
      {
        material_number: 'RCAB00958R0-01-00_01',
        description: 'VERTICAL LEG FR',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group_id: materialGroupMap['PARTS'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R0-01-00_01',
        revision_number: 'REV 00',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-00_02',
        description: 'VERTICAL LEG FL',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group_id: materialGroupMap['PARTS'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R0-01-00_02',
        revision_number: 'REV 00',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-00_03',
        description: 'VERTICAL LEG BR',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group_id: materialGroupMap['PARTS'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R0-01-00_03',
        revision_number: 'REV 00',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-00_04',
        description: 'VERTICAL LEG BL',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group_id: materialGroupMap['PARTS'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R0-01-00_04',
        revision_number: 'REV 00',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R1-01-00_05',
        description: 'MCB CLAMP',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group_id: materialGroupMap['PARTS'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R1-01-00_05',
        revision_number: 'REV 01',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-00_06',
        description: 'BY PASS PLATE',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group_id: materialGroupMap['PARTS'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R0-01-00_06',
        revision_number: 'REV 00',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R2-01-00_07',
        description: 'VARIAC ANGLE 1',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group_id: materialGroupMap['PARTS'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R2-01-00_07',
        revision_number: 'REV 02',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R2-01-00_08',
        description: 'VARIAC ANGLE 2',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group_id: materialGroupMap['PARTS'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R2-01-00_08',
        revision_number: 'REV 02',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R1-01-00_09',
        description: 'VARIAC TOP CHANNEL',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group_id: materialGroupMap['PARTS'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R1-01-00_09',
        revision_number: 'REV 01',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-00_10',
        description: 'CONTACTOR PLATE',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group_id: materialGroupMap['PARTS'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R0-01-00_10',
        revision_number: 'REV 00',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-00_14',
        description: 'M5 X 12 SPACER',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group_id: materialGroupMap['FASTENERS'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R0-01-00_14',
        revision_number: 'REV 00',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-00_15',
        description: 'TERMINAL MOUNTING PIECE 1',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group_id: materialGroupMap['PARTS'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R0-01-00_15',
        revision_number: 'REV 00',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-00_16',
        description: 'TERMINAL MOUNTING PIECE 2',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group_id: materialGroupMap['PARTS'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R0-01-00_16',
        revision_number: 'REV 00',
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-00_18',
        description: 'M4 X 10 MM CSK WELD SCREW',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group_id: materialGroupMap['FASTENERS'] || null,
        base_uom_id: defaultUOMId,
        drawing_number: 'RCAB00958R0-01-00_18',
        revision_number: 'REV 00',
        status: 'ACTIVE'
      },
      {
        material_number: 'SENSING-PLATE',
        description: 'SENSING PLATE',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group_id: materialGroupMap['PARTS'] || null,
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },

      // Raw Materials for CABINET (direct components)
      {
        material_number: 'M12-THREAD',
        description: 'M12 THREAD',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group_id: materialGroupMap['FASTENERS'] || null,
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'JAINSON-WHEEL-RPU60x25-BB',
        description: 'JAINSON WHEEL RPU60x25 BB',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group_id: materialGroupMap['PARTS'] || null,
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      }
    ];

    // Create or update materials
    const materialIdMap = {};
    console.log('Creating/Updating materials...');
    
    // First, get all existing materials to build a map
    const existingMaterialsResult = await queryInterface.sequelize.query(
      `SELECT id, material_number FROM material WHERE company_id = :companyId`,
      {
        replacements: { companyId }
      }
    );
    
    // queryInterface.sequelize.query returns [rows, metadata]
    const existingMaterials = Array.isArray(existingMaterialsResult) && existingMaterialsResult[0] ? existingMaterialsResult[0] : [];
    
    const existingMaterialMap = {};
    existingMaterials.forEach(m => {
      existingMaterialMap[m.material_number] = m.id;
    });
    
    // Prepare materials for bulk insert/update
    const materialsToInsert = [];
    const materialsToUpdate = [];
    
    for (const mat of materials) {
      const materialTypeEnum = getMaterialTypeEnum(mat.material_type_id);
      
      // Ensure required fields are not null
      if (!mat.material_type_id) {
        console.error(`  ✗ Material type ID is missing for: ${mat.material_number}`);
        continue;
      }
      if (!mat.base_uom_id) {
        console.error(`  ✗ Base UOM ID is missing for: ${mat.material_number}`);
        continue;
      }
      
      const materialData = {
        company_id: companyId,
        material_number: mat.material_number,
        description: mat.description,
        material_type: materialTypeEnum,
        material_type_id: mat.material_type_id,
        material_group: null,
        material_group_id: mat.material_group_id || null,
        base_uom_id: mat.base_uom_id,
        drawing_number: mat.drawing_number || null,
        revision_number: mat.revision_number || null,
        status: mat.status,
        is_batch_managed: false,
        is_serial_managed: false,
        is_shelf_life_managed: false,
        created_by: userId,
        updated_by: userId,
        created_at: new Date(),
        updated_at: new Date()
      };
      
      if (existingMaterialMap[mat.material_number]) {
        materialsToUpdate.push({ ...materialData, id: existingMaterialMap[mat.material_number] });
      } else {
        materialsToInsert.push(materialData);
      }
    }
    
    // Update existing materials
    for (const mat of materialsToUpdate) {
      await queryInterface.sequelize.query(
        `UPDATE material SET
          description = :description,
          material_type = :material_type,
          material_type_id = :material_type_id,
          material_group_id = :material_group_id,
          base_uom_id = :base_uom_id,
          drawing_number = :drawing_number,
          revision_number = :revision_number,
          status = :status,
          updated_by = :updated_by,
          updated_at = NOW()
        WHERE id = :id`,
        {
          replacements: mat,
          type: Sequelize.QueryTypes.UPDATE
        }
      );
      materialIdMap[mat.material_number] = mat.id;
      console.log(`  ✓ Updated: ${mat.material_number} - ${mat.description}`);
    }
    
    // Insert new materials using bulkInsert (bypasses Sequelize validations)
    if (materialsToInsert.length > 0) {
      await queryInterface.bulkInsert('material', materialsToInsert, {
        ignoreDuplicates: true
      });
    }
    
    // Get IDs for all materials (both newly inserted and existing)
    // Query all materials we need by material_number
    for (const mat of materials) {
      const materialResult = await queryInterface.sequelize.query(
        `SELECT id FROM material WHERE company_id = :companyId AND material_number = :matNumber LIMIT 1`,
        {
          replacements: { companyId, matNumber: mat.material_number }
        }
      );
      const materialRows = materialResult[0] || [];
      
      if (materialRows.length > 0) {
        materialIdMap[mat.material_number] = materialRows[0].id;
        if (existingMaterialMap[mat.material_number]) {
          console.log(`  ✓ Updated: ${mat.material_number} - ${mat.description}`);
        } else {
          console.log(`  ✓ Created: ${mat.material_number} - ${mat.description}`);
        }
      } else {
        console.error(`  ✗ Failed to get ID for: ${mat.material_number}`);
      }
    }

    // ===== CREATE BOM FOR FINISHED GOOD (CABINET) =====
    console.log('\nCreating BOM for CABINET (RCAB00958R4)...');
    
    const cabinetMaterialId = materialIdMap['RCAB00958R4'];
    if (!cabinetMaterialId) {
      console.error('CABINET material not found. Cannot create BOM.');
      return;
    }

    // Check if BOM already exists
    const [existingCabinetBOM] = await queryInterface.sequelize.query(
      `SELECT id FROM bom_header WHERE company_id = :companyId AND material_id = :materialId AND bom_type = 'PRODUCTION' LIMIT 1`,
      {
        replacements: { companyId, materialId: cabinetMaterialId },
        type: Sequelize.QueryTypes.SELECT
      }
    );

    let cabinetBOMHeaderId;
    const cabinetBOMNumber = 'BOM-RCAB00958R4-REV04';

    if (existingCabinetBOM && existingCabinetBOM.length > 0) {
      cabinetBOMHeaderId = existingCabinetBOM[0].id;
      // Update BOM Header
      await queryInterface.sequelize.query(
        `UPDATE bom_header SET
          plant_id = :plantId,
          base_quantity = 1.000,
          base_uom_id = :uomId,
          drawing_number = 'RCAB00958R4',
          revision_number = 'REV 04',
          valid_from = COALESCE(valid_from, CURDATE()),
          status = 'ACTIVE',
          updated_by = :userId,
          updated_at = NOW()
        WHERE id = :bomHeaderId`,
        {
          replacements: {
            bomHeaderId: cabinetBOMHeaderId,
            plantId,
            uomId: defaultUOMId,
            userId
          },
          type: Sequelize.QueryTypes.UPDATE
        }
      );
      console.log(`  ✓ Updated BOM Header: ${cabinetBOMNumber}`);

      // Delete existing components
      await queryInterface.sequelize.query(
        `DELETE FROM bom_component WHERE bom_id = :bomHeaderId`,
        {
          replacements: { bomHeaderId: cabinetBOMHeaderId },
          type: Sequelize.QueryTypes.DELETE
        }
      );
    } else {
      // Create BOM Header
      await queryInterface.sequelize.query(
        `INSERT INTO bom_header
        (company_id, plant_id, material_id, bom_number, bom_type, base_quantity, base_uom_id, drawing_number, revision_number, alternative, valid_from, status, created_by, created_at, updated_at)
        VALUES
        (:companyId, :plantId, :materialId, :bomNumber, 'PRODUCTION', 1.000, :uomId, 'RCAB00958R4', 'REV 04', '01', CURDATE(), 'ACTIVE', :userId, NOW(), NOW())`,
        {
          replacements: {
            companyId,
            plantId,
            materialId: cabinetMaterialId,
            bomNumber: cabinetBOMNumber,
            uomId: defaultUOMId,
            userId
          },
          type: Sequelize.QueryTypes.INSERT
        }
      );

      // Get the inserted BOM Header ID
      const insertedCabinetBOMResult = await queryInterface.sequelize.query(
        `SELECT id FROM bom_header WHERE bom_number = :bomNumber LIMIT 1`,
        {
          replacements: { bomNumber: cabinetBOMNumber }
        }
      );
      const insertedCabinetBOM = insertedCabinetBOMResult[0] || [];

      if (insertedCabinetBOM && insertedCabinetBOM.length > 0) {
        cabinetBOMHeaderId = insertedCabinetBOM[0].id;
        console.log(`  ✓ Created BOM Header: ${cabinetBOMNumber}`);
      } else {
        console.error('  ✗ Failed to get BOM Header ID');
        return;
      }
    }

    // Create BOM Components for CABINET
    console.log('\nCreating BOM Components for CABINET...');
    
    const cabinetBOMComponents = [
      { item_number: 1, material_number: 'RCAB00958R3-01', quantity: 1, description: 'FRAME ASSY' },
      { item_number: 2, material_number: 'RCAB00958R2-03', quantity: 1, description: 'FRONT DOOR ASSY' },
      { item_number: 3, material_number: 'RCAB00958R4-04', quantity: 1, description: 'LEFT COVER' },
      { item_number: 4, material_number: 'RCAB00958R0-05', quantity: 1, description: 'RIGHT COVER ASSY' },
      { item_number: 5, material_number: 'RCAB00958R4-06', quantity: 1, description: 'REAR SIDE COVER' },
      { item_number: 6, material_number: 'RCAB00958R4-07', quantity: 1, description: 'TOP COVER' },
      { item_number: 7, material_number: 'RCAB00958R1-08', quantity: 1, description: 'TERMINAL BOX ASSY' },
      { item_number: 8, material_number: 'M12-THREAD', quantity: 2, description: 'M12 THREAD' },
      { item_number: 9, material_number: 'JAINSON-WHEEL-RPU60x25-BB', quantity: 4, description: 'JAINSON WHEEL RPU60x25 BB' }
    ];

    for (const comp of cabinetBOMComponents) {
      const componentMaterialId = materialIdMap[comp.material_number];
      if (!componentMaterialId) {
        console.warn(`  ⚠ Component material ${comp.material_number} not found. Skipping.`);
        continue;
      }

      await queryInterface.sequelize.query(
        `INSERT INTO bom_component
        (bom_id, item_number, material_id, component_quantity, component_uom_id, scrap_percentage, created_at, updated_at)
        VALUES
        (:bomId, :itemNumber, :materialId, :componentQuantity, :componentUomId, :scrapPercentage, NOW(), NOW())`,
        {
          replacements: {
            bomId: cabinetBOMHeaderId,
            itemNumber: comp.item_number,
            materialId: componentMaterialId,
            componentQuantity: comp.quantity,
            componentUomId: defaultUOMId,
            scrapPercentage: 0.000
          },
          type: Sequelize.QueryTypes.INSERT
        }
      );
      console.log(`  ✓ Added: Item ${comp.item_number} - ${comp.description} (Qty: ${comp.quantity})`);
    }

    // ===== CREATE BOM FOR MAIN ASSEMBLY (FRAME ASSY) =====
    console.log('\nCreating BOM for FRAME ASSY (RCAB00958R3-01)...');
    
    const frameAssyMaterialId = materialIdMap['RCAB00958R3-01'];
    if (!frameAssyMaterialId) {
      console.error('FRAME ASSY material not found. Cannot create BOM.');
      return;
    }

    // Check if BOM already exists
    const existingFrameBOMResult = await queryInterface.sequelize.query(
      `SELECT id FROM bom_header WHERE company_id = :companyId AND material_id = :materialId AND bom_type = 'PRODUCTION' LIMIT 1`,
      {
        replacements: { companyId, materialId: frameAssyMaterialId }
      }
    );
    const existingFrameBOM = existingFrameBOMResult[0] || [];

    let frameBOMHeaderId;
    const frameBOMNumber = 'BOM-RCAB00958R3-01-REV03';

    if (existingFrameBOM && existingFrameBOM.length > 0) {
      frameBOMHeaderId = existingFrameBOM[0].id;
      // Update BOM Header
      await queryInterface.sequelize.query(
        `UPDATE bom_header SET
          plant_id = :plantId,
          base_quantity = 1.000,
          base_uom_id = :uomId,
          drawing_number = 'RCAB00958R3-01',
          revision_number = 'REV 03',
          valid_from = COALESCE(valid_from, CURDATE()),
          status = 'ACTIVE',
          updated_by = :userId,
          updated_at = NOW()
        WHERE id = :bomHeaderId`,
        {
          replacements: {
            bomHeaderId: frameBOMHeaderId,
            plantId,
            uomId: defaultUOMId,
            userId
          },
          type: Sequelize.QueryTypes.UPDATE
        }
      );
      console.log(`  ✓ Updated BOM Header: ${frameBOMNumber}`);

      // Delete existing components
      await queryInterface.sequelize.query(
        `DELETE FROM bom_component WHERE bom_id = :bomHeaderId`,
        {
          replacements: { bomHeaderId: frameBOMHeaderId },
          type: Sequelize.QueryTypes.DELETE
        }
      );
    } else {
      // Create BOM Header
      await queryInterface.sequelize.query(
        `INSERT INTO bom_header
        (company_id, plant_id, material_id, bom_number, bom_type, base_quantity, base_uom_id, drawing_number, revision_number, alternative, valid_from, status, created_by, created_at, updated_at)
        VALUES
        (:companyId, :plantId, :materialId, :bomNumber, 'PRODUCTION', 1.000, :uomId, 'RCAB00958R3-01', 'REV 03', '01', CURDATE(), 'ACTIVE', :userId, NOW(), NOW())`,
        {
          replacements: {
            companyId,
            plantId,
            materialId: frameAssyMaterialId,
            bomNumber: frameBOMNumber,
            uomId: defaultUOMId,
            userId
          },
          type: Sequelize.QueryTypes.INSERT
        }
      );

      // Get the inserted BOM Header ID
      const insertedFrameBOMResult = await queryInterface.sequelize.query(
        `SELECT id FROM bom_header WHERE bom_number = :bomNumber LIMIT 1`,
        {
          replacements: { bomNumber: frameBOMNumber }
        }
      );
      const insertedFrameBOM = insertedFrameBOMResult[0] || [];

      if (insertedFrameBOM && insertedFrameBOM.length > 0) {
        frameBOMHeaderId = insertedFrameBOM[0].id;
        console.log(`  ✓ Created BOM Header: ${frameBOMNumber}`);
      } else {
        console.error('  ✗ Failed to get BOM Header ID');
        return;
      }
    }

    // Create BOM Components for FRAME ASSY
    console.log('\nCreating BOM Components for FRAME ASSY...');
    
    const frameBOMComponents = [
      { item_number: 1, material_number: 'RCAB00958R4-01-01', quantity: 1, description: 'BOTTOM PLATE ASSY' },
      { item_number: 2, material_number: 'RCAB00958R0-01-02', quantity: 1, description: 'TOP FRAME ASSY' },
      { item_number: 3, material_number: 'RCAB00958R2-01-03', quantity: 2, description: 'MIDDLE FRAME 1 ASSY' },
      { item_number: 4, material_number: 'RCAB00958R0-01-05', quantity: 3, description: 'MOTOR CLAMP ASSY' },
      { item_number: 5, material_number: 'RCAB00958R1-01-06', quantity: 3, description: 'SMC PLATE ASSY' },
      { item_number: 6, material_number: 'RCAB00958R0-01-07', quantity: 6, description: 'CORNER PIECE ASSY 1' },
      { item_number: 7, material_number: 'RCAB00958R0-01-00_01', quantity: 1, description: 'VERTICAL LEG FR' },
      { item_number: 8, material_number: 'RCAB00958R0-01-00_02', quantity: 1, description: 'VERTICAL LEG FL' },
      { item_number: 9, material_number: 'RCAB00958R0-01-00_03', quantity: 1, description: 'VERTICAL LEG BR' },
      { item_number: 10, material_number: 'RCAB00958R0-01-00_04', quantity: 1, description: 'VERTICAL LEG BL' },
      { item_number: 11, material_number: 'RCAB00958R1-01-00_05', quantity: 1, description: 'MCB CLAMP' },
      { item_number: 12, material_number: 'RCAB00958R0-01-00_06', quantity: 1, description: 'BY PASS PLATE' },
      { item_number: 13, material_number: 'RCAB00958R2-01-00_07', quantity: 1, description: 'VARIAC ANGLE 1' },
      { item_number: 14, material_number: 'RCAB00958R2-01-00_08', quantity: 1, description: 'VARIAC ANGLE 2' },
      { item_number: 15, material_number: 'RCAB00958R1-01-00_09', quantity: 1, description: 'VARIAC TOP CHANNEL' },
      { item_number: 16, material_number: 'RCAB00958R0-01-00_10', quantity: 1, description: 'CONTACTOR PLATE' },
      { item_number: 17, material_number: 'RCAB00958R0-01-00_14', quantity: 8, description: 'M5 X 12 SPACER' },
      { item_number: 18, material_number: 'RCAB00958R0-01-00_15', quantity: 2, description: 'TERMINAL MOUNTING PIECE 1' },
      { item_number: 19, material_number: 'RCAB00958R0-01-00_16', quantity: 4, description: 'TERMINAL MOUNTING PIECE 2' },
      { item_number: 20, material_number: 'RCAB00958R0-01-00_18', quantity: 6, description: 'M4 X 10 MM CSK WELD SCREW' },
      { item_number: 21, material_number: 'SENSING-PLATE', quantity: 1, description: 'SENSING PLATE' }
    ];

    for (const comp of frameBOMComponents) {
      const componentMaterialId = materialIdMap[comp.material_number];
      if (!componentMaterialId) {
        console.warn(`  ⚠ Component material ${comp.material_number} not found. Skipping.`);
        continue;
      }

      await queryInterface.sequelize.query(
        `INSERT INTO bom_component
        (bom_id, item_number, material_id, component_quantity, component_uom_id, scrap_percentage, created_at, updated_at)
        VALUES
        (:bomId, :itemNumber, :materialId, :componentQuantity, :componentUomId, :scrapPercentage, NOW(), NOW())`,
        {
          replacements: {
            bomId: frameBOMHeaderId,
            itemNumber: comp.item_number,
            materialId: componentMaterialId,
            componentQuantity: comp.quantity,
            componentUomId: defaultUOMId,
            scrapPercentage: 0.000
          },
          type: Sequelize.QueryTypes.INSERT
        }
      );
      console.log(`  ✓ Added: Item ${comp.item_number} - ${comp.description} (Qty: ${comp.quantity})`);
    }

    console.log('\n✅ Complete Cabinet BOM Structure created successfully!');
    console.log('\n📦 BOM Hierarchy:');
    console.log('  Level 0: RCAB00958R4 - CABINET (Finished Good)');
    console.log('    └── Level 1: Main Assemblies (7 items)');
    console.log('          └── Level 2: FRAME ASSY (RCAB00958R3-01)');
    console.log('                └── Level 3: Sub-Assemblies (6) + Raw Materials (15)');
  },

  async down(queryInterface, Sequelize) {
    // Delete BOM components and headers
    await queryInterface.sequelize.query(
      `DELETE bc FROM bom_component bc
       INNER JOIN bom_header bh ON bc.bom_id = bh.id
       WHERE bh.bom_number IN ('BOM-RCAB00958R4-REV04', 'BOM-RCAB00958R3-01-REV03')`
    );
    
    await queryInterface.sequelize.query(
      `DELETE FROM bom_header WHERE bom_number IN ('BOM-RCAB00958R4-REV04', 'BOM-RCAB00958R3-01-REV03')`
    );

    // Delete materials (in reverse order to handle foreign keys)
    const materialNumbers = [
      // Raw Materials
      'M12-THREAD', 'JAINSON-WHEEL-RPU60x25-BB', 'SENSING-PLATE',
      'RCAB00958R0-01-00_18', 'RCAB00958R0-01-00_16', 'RCAB00958R0-01-00_15',
      'RCAB00958R0-01-00_14', 'RCAB00958R0-01-00_10', 'RCAB00958R1-01-00_09',
      'RCAB00958R2-01-00_08', 'RCAB00958R2-01-00_07', 'RCAB00958R0-01-00_06',
      'RCAB00958R1-01-00_05', 'RCAB00958R0-01-00_04', 'RCAB00958R0-01-00_03',
      'RCAB00958R0-01-00_02', 'RCAB00958R0-01-00_01',
      // Sub-Assemblies
      'RCAB00958R0-01-07', 'RCAB00958R1-01-06', 'RCAB00958R0-01-05',
      'RCAB00958R2-01-03', 'RCAB00958R0-01-02', 'RCAB00958R4-01-01',
      // Main Assemblies
      'RCAB00958R1-08', 'RCAB00958R4-07', 'RCAB00958R4-06',
      'RCAB00958R0-05', 'RCAB00958R4-04', 'RCAB00958R2-03', 'RCAB00958R3-01',
      // Finished Good
      'RCAB00958R4'
    ];

    for (const matNum of materialNumbers) {
      try {
        await queryInterface.sequelize.query(
          `DELETE FROM material WHERE material_number = :matNum`,
          {
            replacements: { matNum },
            type: Sequelize.QueryTypes.DELETE
          }
        );
      } catch (err) {
        console.log(`Could not delete ${matNum}: ${err.message}`);
      }
    }
  }
};

