'use strict';

/**
 * Complete Cabinet BOM Structure:
 * 
 * Level 2: CABINET (Finished Good)
 *   └── Level 1: VARIAC LEG (Assembly/Semi-Finished)
 *         └── Level 0: Raw Materials (from the image BOM)
 * 
 * This seeder:
 * 1. Deletes existing BOMs and materials
 * 2. Creates all materials with correct types
 * 3. Creates VARIAC LEG BOM (assembly level)
 * 4. Creates CABINET BOM (finished good level)
 */

module.exports = {
  async up(queryInterface, Sequelize) {
    // Get required master data
    const [companies, plants, uoms, materialTypes, 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', 'ASSEMBLY', 'RAW_MAT')"),
      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 = {}; // Map material_type_id to ENUM value
    materialTypes[0].forEach(mt => {
      materialTypeMap[mt.type_code] = mt.id;
      // Map type_code to ENUM value
      const enumMap = {
        'FIN_GOOD': 'FINISHED_GOOD',
        'SEMI_FIN': 'SEMI_FINISHED',
        'RAW_MAT': 'RAW_MATERIAL',
        'PACKAGING': 'PACKAGING',
        'CONSUMABLE': 'CONSUMABLE',
        'SERVICE': 'SERVICE',
        'ASSET': 'ASSET'
      };
      if (enumMap[mt.type_code]) {
        materialTypeIdToEnumMap[mt.id] = enumMap[mt.type_code];
      }
    });
    
    // Helper function to get ENUM value from material_type_id
    const getMaterialTypeEnum = (materialTypeId) => {
      return materialTypeIdToEnumMap[materialTypeId] || 'RAW_MATERIAL';
    };

    // Step 1: Delete existing BOMs and materials (handle foreign keys)
    console.log('Cleaning up existing data...');
    
    try {
      // Delete BOM components first (for all BOMs that might reference these materials)
      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-RCAB00958-REV03', 'BOM-CABINET-001')`
      );
      
      // Delete BOM headers
      await queryInterface.sequelize.query(
        `DELETE FROM bom_header WHERE bom_number IN ('BOM-RCAB00958-REV03', 'BOM-CABINET-001')`
      );
      
      // Delete materials (only if not referenced by other BOMs)
      // We'll use a safer approach - just try to delete, ignore errors
      const materialNumbers = [
        'CABINET-001', 'RCAB00958', 'RCAB00958R4-01-01', 'RCAB00958R0-01-02', 
        'RCAB00958R2-01-03', 'RCAB00958R0-01-05', 'RCAB00958R1-01-06', 
        'RCAB00958R0-01-07', 'RCAB00958R0-01-00_01', 'RCAB00958R0-01-00_02',
        'RCAB00958R0-01-00_03', 'RCAB00958R0-01-00_04', 'RCAB00958R1-01-00_05',
        'RCAB00958R0-01-00_06', 'RCAB00958R2-01-00_07', 'RCAB00958R2-01-00_08',
        'RCAB00958R1-01-00_09', 'RCAB00958R0-01-00_10', 'RCAB00958R0-01-00_14',
        'RCAB00958R0-01-00_15', 'RCAB00958R0-01-00_16', 'RCAB00958R0-01-00_18',
        'SENSING-PLATE'
      ];
      
      for (const matNum of materialNumbers) {
        try {
          await queryInterface.sequelize.query(
            `DELETE FROM material WHERE company_id = :companyId AND material_number = :matNumber`,
            {
              replacements: { companyId, matNumber: matNum },
              type: Sequelize.QueryTypes.DELETE
            }
          );
        } catch (err) {
          // Ignore foreign key errors - material might be in use
          console.log(`Could not delete ${matNum}, will reuse if exists`);
        }
      }
    } catch (err) {
      console.log('Some cleanup operations failed, continuing with creation...');
    }

    console.log('Creating materials...');

    // Step 2: Define all materials with correct types
    const materials = [
      // Level 2: Finished Good
      {
        material_number: 'CABINET-001',
        description: 'Complete Cabinet Assembly',
        material_type_id: materialTypeMap['FIN_GOOD'],
        material_group: 'CABINET',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      // Level 1: Assembly (VARIAC LEG)
      {
        material_number: 'RCAB00958',
        description: 'VARIAC LEG',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group: 'ASSEMBLY',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      // Level 0: Sub-Assemblies (components of VARIAC LEG)
      {
        material_number: 'RCAB00958R4-01-01',
        description: 'BOTTOM PLATE ASSY',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group: 'ASSEMBLY',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-02',
        description: 'TOP FRAME ASSY',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group: 'ASSEMBLY',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R2-01-03',
        description: 'MIDDLE FRAME 1 ASSY',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group: 'ASSEMBLY',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-05',
        description: 'MOTOR CLAMP ASSY X',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group: 'ASSEMBLY',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R1-01-06',
        description: 'SMC PLATE ASSY X',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group: 'ASSEMBLY',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-07',
        description: 'CORNER PIECE ASSY 1 X',
        material_type_id: materialTypeMap['SEMI_FIN'],
        material_group: 'ASSEMBLY',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      // Level 0: Raw Materials (individual parts)
      {
        material_number: 'RCAB00958R0-01-00_01',
        description: 'VERTICAL LEG FR X',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group: 'PARTS',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-00_02',
        description: 'VERTICAL LEG FL',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group: 'PARTS',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-00_03',
        description: 'VERTICAL LEG BR',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group: 'PARTS',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-00_04',
        description: 'VERTICAL LEG BL',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group: 'PARTS',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R1-01-00_05',
        description: 'MCB CLAMP X',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group: 'PARTS',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-00_06',
        description: 'BY PASS PLATE X',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group: 'PARTS',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R2-01-00_07',
        description: 'VARIAC ANGLE 1 X',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group: 'PARTS',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R2-01-00_08',
        description: 'VARIAC ANGLE 2',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group: 'PARTS',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R1-01-00_09',
        description: 'VARIAC TOP CHANNEL',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group: 'PARTS',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-00_10',
        description: 'CONTACTOR PLATE X',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group: 'PARTS',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-00_14',
        description: 'M5 X 12 SPACER X',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group: 'FASTENERS',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-00_15',
        description: 'TERMINAL MOUNTING PIECE 1 X',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group: 'PARTS',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-00_16',
        description: 'TERMINAL MOUNTING PIECE 2 X',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group: 'PARTS',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-00_18',
        description: 'M4 X 10 MM CSK WELD SCREW',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group: 'FASTENERS',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'SENSING-PLATE',
        description: 'SENSING PLATE',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group: 'PARTS',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      }
    ];

    // Step 3: Insert materials and create material map (check if exists first)
    const materialMap = {};
    for (const mat of materials) {
      // Check if material already exists
      const existingResult = await queryInterface.sequelize.query(
        `SELECT id FROM material WHERE company_id = :companyId AND material_number = :matNumber LIMIT 1`,
        {
          replacements: { companyId, matNumber: mat.material_number },
          type: Sequelize.QueryTypes.SELECT
        }
      );
      // Sequelize SELECT returns [rows, metadata], but sometimes just rows
      const existingRows = Array.isArray(existingResult) && existingResult.length === 2 ? existingResult[0] : existingResult;

      let materialId;
      if (existingRows && existingRows.length > 0 && existingRows[0] && existingRows[0].id) {
        materialId = existingRows[0].id;
        // Update material type if needed
        const materialTypeEnum = getMaterialTypeEnum(mat.material_type_id);
        await queryInterface.sequelize.query(
          `UPDATE material SET material_type = :matTypeEnum, material_type_id = :matTypeId, description = :description, material_group = :matGroup 
           WHERE id = :materialId`,
          {
            replacements: {
              materialId,
              matTypeEnum,
              matTypeId: mat.material_type_id,
              description: mat.description,
              matGroup: mat.material_group
            },
            type: Sequelize.QueryTypes.UPDATE
          }
        );
      } else {
        // Material doesn't exist, insert it
        try {
          const materialTypeEnum = getMaterialTypeEnum(mat.material_type_id);
          await queryInterface.sequelize.query(
            `INSERT INTO material (company_id, material_number, material_type, material_type_id, material_group, description, base_uom_id, is_batch_managed, is_serial_managed, status, created_by, created_at, updated_at) 
             VALUES (:companyId, :matNumber, :matTypeEnum, :matTypeId, :matGroup, :description, :uomId, false, false, :status, :userId, NOW(), NOW())`,
            {
              replacements: {
                companyId,
                matNumber: mat.material_number,
                matTypeEnum,
                matTypeId: mat.material_type_id,
                matGroup: mat.material_group,
                description: mat.description,
                uomId: mat.base_uom_id,
                status: mat.status,
                userId
              },
              type: Sequelize.QueryTypes.INSERT
            }
          );
        } catch (err) {
          // Material might already exist (unique constraint), just get its ID
          console.log(`Material ${mat.material_number} already exists, using existing`);
        }
      }
      
      // Always get the material ID (whether newly inserted or existing)
      const finalResult = await queryInterface.sequelize.query(
        `SELECT id FROM material WHERE company_id = :companyId AND material_number = :matNumber LIMIT 1`,
        {
          replacements: { companyId, matNumber: mat.material_number },
          type: Sequelize.QueryTypes.SELECT
        }
      );
      const finalRows = Array.isArray(finalResult) && finalResult.length === 2 ? finalResult[0] : finalResult;
      if (finalRows && finalRows.length > 0 && finalRows[0] && finalRows[0].id) {
        materialId = finalRows[0].id;
        materialMap[mat.material_number] = materialId;
      } else {
        console.error(`Failed to get material ID for ${mat.material_number}`);
      }
    }

    console.log('Creating VARIAC LEG BOM (Assembly Level)...');

    // Step 4: Create BOM Header for VARIAC LEG (Assembly)
    const variacLegId = materialMap['RCAB00958'];
    await queryInterface.sequelize.query(
      `INSERT INTO bom_header (company_id, plant_id, bom_number, material_id, bom_type, alternative, base_quantity, base_uom_id, valid_from, drawing_number, revision_number, status, created_by, created_at, updated_at)
       VALUES (:companyId, :plantId, 'BOM-RCAB00958-REV03', :materialId, 'PRODUCTION', '01', 1.000, :uomId, CURDATE(), 'RCAB00958', 'REV 03', 'ACTIVE', :userId, NOW(), NOW())`,
      {
        replacements: {
          companyId,
          plantId,
          materialId: variacLegId,
          uomId: defaultUOMId,
          userId
        },
        type: Sequelize.QueryTypes.INSERT
      }
    );
    
    // Get the VARIAC LEG BOM ID
    const [variacLegBOM] = await queryInterface.sequelize.query(
      `SELECT id FROM bom_header WHERE company_id = :companyId AND plant_id = :plantId AND bom_number = 'BOM-RCAB00958-REV03' LIMIT 1`,
      {
        replacements: { companyId, plantId },
        type: Sequelize.QueryTypes.SELECT
      }
    );
    const variacLegBOMId = variacLegBOM.id;

    // Step 5: Create BOM Components for VARIAC LEG (from the image)
    const variacLegComponents = [
      { item_no: 1, part_no: 'RCAB00958R4-01-01', qty: 1 },
      { item_no: 2, part_no: 'RCAB00958R0-01-02', qty: 1 },
      { item_no: 3, part_no: 'RCAB00958R2-01-03', qty: 2 },
      { item_no: 4, part_no: 'RCAB00958R0-01-05', qty: 3 },
      { item_no: 5, part_no: 'RCAB00958R1-01-06', qty: 3 },
      { item_no: 6, part_no: 'RCAB00958R0-01-07', qty: 6 },
      { item_no: 7, part_no: 'RCAB00958R0-01-00_01', qty: 1 },
      { item_no: 8, part_no: 'RCAB00958R0-01-00_02', qty: 1 },
      { item_no: 9, part_no: 'RCAB00958R0-01-00_03', qty: 1 },
      { item_no: 10, part_no: 'RCAB00958R0-01-00_04', qty: 1 },
      { item_no: 11, part_no: 'RCAB00958R1-01-00_05', qty: 1 },
      { item_no: 12, part_no: 'RCAB00958R0-01-00_06', qty: 1 },
      { item_no: 13, part_no: 'RCAB00958R2-01-00_07', qty: 1 },
      { item_no: 14, part_no: 'RCAB00958R2-01-00_08', qty: 1 },
      { item_no: 15, part_no: 'RCAB00958R1-01-00_09', qty: 1 },
      { item_no: 16, part_no: 'RCAB00958R0-01-00_10', qty: 1 },
      { item_no: 21, part_no: 'RCAB00958R0-01-00_14', qty: 8 },
      { item_no: 22, part_no: 'RCAB00958R0-01-00_15', qty: 2 },
      { item_no: 23, part_no: 'RCAB00958R0-01-00_16', qty: 4 },
      { item_no: 24, part_no: 'RCAB00958R0-01-00_18', qty: 6 },
      { item_no: 25, part_no: 'SENSING-PLATE', qty: 1 }
    ];

    for (const comp of variacLegComponents) {
      const componentMaterialId = materialMap[comp.part_no];
      if (componentMaterialId) {
        await queryInterface.sequelize.query(
          `INSERT INTO bom_component (bom_id, material_id, item_number, component_quantity, component_uom_id, scrap_percentage, item_category, fixed_quantity, status, created_by, created_at, updated_at)
           VALUES (:bomId, :materialId, :itemNo, :qty, :uomId, 0.00, 'L', false, 'ACTIVE', :userId, NOW(), NOW())`,
          {
            replacements: {
              bomId: variacLegBOMId,
              materialId: componentMaterialId,
              itemNo: comp.item_no,
              qty: comp.qty,
              uomId: defaultUOMId,
              userId
            },
            type: Sequelize.QueryTypes.INSERT
          }
        );
      }
    }

    console.log('Creating CABINET BOM (Finished Good Level)...');

    // Step 6: Create BOM Header for CABINET (Finished Good)
    const cabinetId = materialMap['CABINET-001'];
    await queryInterface.sequelize.query(
      `INSERT INTO bom_header (company_id, plant_id, bom_number, material_id, bom_type, alternative, base_quantity, base_uom_id, valid_from, drawing_number, revision_number, status, created_by, created_at, updated_at)
       VALUES (:companyId, :plantId, 'BOM-CABINET-001', :materialId, 'PRODUCTION', '01', 1.000, :uomId, CURDATE(), 'CABINET-001', 'REV 01', 'ACTIVE', :userId, NOW(), NOW())`,
      {
        replacements: {
          companyId,
          plantId,
          materialId: cabinetId,
          uomId: defaultUOMId,
          userId
        },
        type: Sequelize.QueryTypes.INSERT
      }
    );
    
    // Get the CABINET BOM ID
    const [cabinetBOM] = await queryInterface.sequelize.query(
      `SELECT id FROM bom_header WHERE company_id = :companyId AND plant_id = :plantId AND bom_number = 'BOM-CABINET-001' LIMIT 1`,
      {
        replacements: { companyId, plantId },
        type: Sequelize.QueryTypes.SELECT
      }
    );
    const cabinetBOMId = cabinetBOM.id;

    // Step 7: Create BOM Components for CABINET
    // VARIAC LEG is a component of CABINET (it has its own BOM!)
    const cabinetComponents = [
      { item_no: 1, part_no: 'RCAB00958', qty: 4, description: 'VARIAC LEG (Assembly with BOM)' }
    ];

    for (const comp of cabinetComponents) {
      const componentMaterialId = materialMap[comp.part_no];
      if (componentMaterialId) {
        await queryInterface.sequelize.query(
          `INSERT INTO bom_component (bom_id, material_id, item_number, component_quantity, component_uom_id, scrap_percentage, item_category, fixed_quantity, status, created_by, created_at, updated_at)
           VALUES (:bomId, :materialId, :itemNo, :qty, :uomId, 0.00, 'L', false, 'ACTIVE', :userId, NOW(), NOW())`,
          {
            replacements: {
              bomId: cabinetBOMId,
              materialId: componentMaterialId,
              itemNo: comp.item_no,
              qty: comp.qty,
              uomId: defaultUOMId,
              userId
            },
            type: Sequelize.QueryTypes.INSERT
          }
        );
      }
    }

    console.log(`
    ============================================
    ✅ BOM Structure Created Successfully!
    ============================================
    
    📦 Level 2: CABINET (Finished Good)
       BOM: BOM-CABINET-001
       Components:
       - 4x RCAB00958 (VARIAC LEG) ← Has its own BOM!
    
    🔧 Level 1: VARIAC LEG (Assembly)
       BOM: BOM-RCAB00958-REV03
       Drawing: RCAB00958
       Revision: REV 03
       Components: 21 items (from the image)
    
    📋 Level 0: Raw Materials
       - 6 Sub-Assemblies
       - 15 Individual Parts
    
    ============================================
    Total Materials Created: ${materials.length}
    Total BOMs Created: 2
    ============================================
    `);
  },

  async down(queryInterface, Sequelize) {
    // Delete in reverse order
    await queryInterface.sequelize.query(
      `DELETE FROM bom_component WHERE bom_id IN (
        SELECT id FROM bom_header WHERE bom_number IN ('BOM-RCAB00958-REV03', 'BOM-CABINET-001')
      )`
    );
    await queryInterface.sequelize.query(
      `DELETE FROM bom_header WHERE bom_number IN ('BOM-RCAB00958-REV03', 'BOM-CABINET-001')`
    );
    await queryInterface.sequelize.query(
      `DELETE FROM material WHERE material_number IN (
        'CABINET-001', 'RCAB00958', 'RCAB00958R4-01-01', 'RCAB00958R0-01-02', 
        'RCAB00958R2-01-03', 'RCAB00958R0-01-05', 'RCAB00958R1-01-06', 
        'RCAB00958R0-01-07', 'RCAB00958R0-01-00_01', 'RCAB00958R0-01-00_02',
        'RCAB00958R0-01-00_03', 'RCAB00958R0-01-00_04', 'RCAB00958R1-01-00_05',
        'RCAB00958R0-01-00_06', 'RCAB00958R2-01-00_07', 'RCAB00958R2-01-00_08',
        'RCAB00958R1-01-00_09', 'RCAB00958R0-01-00_10', 'RCAB00958R0-01-00_14',
        'RCAB00958R0-01-00_15', 'RCAB00958R0-01-00_16', 'RCAB00958R0-01-00_18',
        'SENSING-PLATE'
      )`
    );
  }
};

