'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    // Get required master data
    const [companies, plants, uoms, materialTypes, materialGroups, currencies, users, fiscalYears] = await Promise.all([
      queryInterface.sequelize.query("SELECT id, code FROM company WHERE code = 'COMP001' LIMIT 1"),
      queryInterface.sequelize.query("SELECT id, code FROM plant WHERE code = 'PLANT001' LIMIT 1"),
      queryInterface.sequelize.query("SELECT id, uom_code FROM uom WHERE uom_code IN ('KG', 'PC', 'MT', 'NOS', 'MTR', 'M2')"),
      queryInterface.sequelize.query("SELECT id, type_code FROM material_type WHERE type_code IN ('RAW_MAT', 'SEMI_FIN', 'FIN_GOOD')"),
      queryInterface.sequelize.query("SELECT id, group_code FROM material_group LIMIT 5"),
      queryInterface.sequelize.query("SELECT id, currency_code FROM currency WHERE currency_code = 'INR' LIMIT 1"),
      queryInterface.sequelize.query("SELECT id FROM user WHERE username = 'admin' LIMIT 1"),
      queryInterface.sequelize.query("SELECT id, fiscal_year FROM fiscal_year WHERE status = 'ACTIVE' ORDER BY fiscal_year DESC LIMIT 1")
    ]);

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

    const companyId = companies[0][0].id;
    const plantId = plants[0][0].id;
    const userId = users[0][0].id;
    const currencyId = currencies[0].length > 0 ? currencies[0][0].id : null;
    const fiscalYearId = fiscalYears[0].length > 0 ? fiscalYears[0][0].id : null;

    // Create UOM map
    const uomMap = {};
    uoms[0].forEach(uom => {
      uomMap[uom.uom_code] = uom.id;
    });

    // Create Material Type map
    const materialTypeMap = {};
    materialTypes[0].forEach(type => {
      materialTypeMap[type.type_code] = type.id;
    });

    // Get Material Group IDs (use first available or create default)
    const materialGroupIds = materialGroups[0].map(g => g.id);
    const defaultGroupId = materialGroupIds[0] || null;

    const now = new Date();
    const openingBalanceDate = new Date('2026-01-01');

    // Define comprehensive test materials
    const materials = [
      // RAW MATERIALS
      {
        company_id: companyId,
        material_number: 'RM-STEEL-SHEET-2MM',
        material_type: 'RAW_MATERIAL',
        material_type_id: materialTypeMap['RAW_MAT'] || null,
        material_group_id: defaultGroupId,
        description: 'Steel Sheet 2mm Thickness - MS Grade',
        base_uom_id: uomMap['KG'],
        weight_uom_id: uomMap['KG'],
        net_weight: 15.700,
        gross_weight: 16.000,
        thickness: 2.000,
        hsn_code: '72101200',
        procurement_type: 'E',
        make_buy_indicator: 'BUY',
        abc_indicator: 'A',
        status: 'ACTIVE',
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now
      },
      {
        company_id: companyId,
        material_number: 'RM-STEEL-SHEET-3MM',
        material_type: 'RAW_MATERIAL',
        material_type_id: materialTypeMap['RAW_MAT'] || null,
        material_group_id: defaultGroupId,
        description: 'Steel Sheet 3mm Thickness - MS Grade',
        base_uom_id: uomMap['KG'],
        weight_uom_id: uomMap['KG'],
        net_weight: 23.550,
        gross_weight: 24.000,
        thickness: 3.000,
        hsn_code: '72101200',
        procurement_type: 'E',
        make_buy_indicator: 'BUY',
        abc_indicator: 'A',
        status: 'ACTIVE',
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now
      },
      {
        company_id: companyId,
        material_number: 'RM-STEEL-PLATE-5MM',
        material_type: 'RAW_MATERIAL',
        material_type_id: materialTypeMap['RAW_MAT'] || null,
        material_group_id: defaultGroupId,
        description: 'Steel Plate 5mm Thickness - MS Grade',
        base_uom_id: uomMap['KG'],
        weight_uom_id: uomMap['KG'],
        net_weight: 39.250,
        gross_weight: 40.000,
        thickness: 5.000,
        hsn_code: '72101200',
        procurement_type: 'E',
        make_buy_indicator: 'BUY',
        abc_indicator: 'A',
        status: 'ACTIVE',
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now
      },
      {
        company_id: companyId,
        material_number: 'RM-GI-SHEET-1MM',
        material_type: 'RAW_MATERIAL',
        material_type_id: materialTypeMap['RAW_MAT'] || null,
        material_group_id: defaultGroupId,
        description: 'GI Sheet 1mm Thickness - Galvanized Iron',
        base_uom_id: uomMap['KG'],
        weight_uom_id: uomMap['KG'],
        net_weight: 7.850,
        gross_weight: 8.000,
        thickness: 1.000,
        hsn_code: '72125000',
        procurement_type: 'E',
        make_buy_indicator: 'BUY',
        abc_indicator: 'A',
        status: 'ACTIVE',
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now
      },
      {
        company_id: companyId,
        material_number: 'RM-SS-SHEET-2MM',
        material_type: 'RAW_MATERIAL',
        material_type_id: materialTypeMap['RAW_MAT'] || null,
        material_group_id: defaultGroupId,
        description: 'Stainless Steel Sheet 2mm - SS304',
        base_uom_id: uomMap['KG'],
        weight_uom_id: uomMap['KG'],
        net_weight: 16.000,
        gross_weight: 16.300,
        thickness: 2.000,
        hsn_code: '72191200',
        procurement_type: 'E',
        make_buy_indicator: 'BUY',
        abc_indicator: 'A',
        status: 'ACTIVE',
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now
      },
      {
        company_id: companyId,
        material_number: 'RM-ANGLE-50X50',
        material_type: 'RAW_MATERIAL',
        material_type_id: materialTypeMap['RAW_MAT'] || null,
        material_group_id: defaultGroupId,
        description: 'MS Angle 50x50x5mm',
        base_uom_id: uomMap['KG'],
        weight_uom_id: uomMap['KG'],
        net_weight: 3.770,
        gross_weight: 3.800,
        hsn_code: '72142000',
        procurement_type: 'E',
        make_buy_indicator: 'BUY',
        abc_indicator: 'B',
        status: 'ACTIVE',
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now
      },
      {
        company_id: companyId,
        material_number: 'RM-CHANNEL-75X40',
        material_type: 'RAW_MATERIAL',
        material_type_id: materialTypeMap['RAW_MAT'] || null,
        material_group_id: defaultGroupId,
        description: 'MS Channel 75x40x5mm',
        base_uom_id: uomMap['KG'],
        weight_uom_id: uomMap['KG'],
        net_weight: 5.800,
        gross_weight: 6.000,
        hsn_code: '72142000',
        procurement_type: 'E',
        make_buy_indicator: 'BUY',
        abc_indicator: 'B',
        status: 'ACTIVE',
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now
      },
      {
        company_id: companyId,
        material_number: 'RM-FLAT-25X5',
        material_type: 'RAW_MATERIAL',
        material_type_id: materialTypeMap['RAW_MAT'] || null,
        material_group_id: defaultGroupId,
        description: 'MS Flat Bar 25x5mm',
        base_uom_id: uomMap['KG'],
        weight_uom_id: uomMap['KG'],
        net_weight: 0.981,
        gross_weight: 1.000,
        hsn_code: '72142000',
        procurement_type: 'E',
        make_buy_indicator: 'BUY',
        abc_indicator: 'B',
        status: 'ACTIVE',
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now
      },
      {
        company_id: companyId,
        material_number: 'RM-BOLT-M8',
        material_type: 'RAW_MATERIAL',
        material_type_id: materialTypeMap['RAW_MAT'] || null,
        material_group_id: defaultGroupId,
        description: 'MS Bolt M8x25mm',
        base_uom_id: uomMap['PC'],
        weight_uom_id: uomMap['KG'],
        net_weight: 0.010,
        gross_weight: 0.012,
        hsn_code: '73181500',
        procurement_type: 'E',
        make_buy_indicator: 'BUY',
        abc_indicator: 'C',
        status: 'ACTIVE',
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now
      },
      {
        company_id: companyId,
        material_number: 'RM-NUT-M8',
        material_type: 'RAW_MATERIAL',
        material_type_id: materialTypeMap['RAW_MAT'] || null,
        material_group_id: defaultGroupId,
        description: 'MS Nut M8',
        base_uom_id: uomMap['PC'],
        weight_uom_id: uomMap['KG'],
        net_weight: 0.005,
        gross_weight: 0.006,
        hsn_code: '73181600',
        procurement_type: 'E',
        make_buy_indicator: 'BUY',
        abc_indicator: 'C',
        status: 'ACTIVE',
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now
      },
      // SEMI-FINISHED GOODS
      {
        company_id: companyId,
        material_number: 'SF-CUT-SHEET-2MM',
        material_type: 'SEMI_FINISHED',
        material_type_id: materialTypeMap['SEMI_FIN'] || null,
        material_group_id: defaultGroupId,
        description: 'Cut Steel Sheet 2mm - 1000x2000mm',
        base_uom_id: uomMap['PC'],
        weight_uom_id: uomMap['KG'],
        net_weight: 31.400,
        gross_weight: 32.000,
        length: 2000,
        width: 1000,
        thickness: 2.000,
        hsn_code: '72101200',
        procurement_type: 'F',
        make_buy_indicator: 'MAKE',
        abc_indicator: 'B',
        status: 'ACTIVE',
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now
      },
      {
        company_id: companyId,
        material_number: 'SF-WELDED-FRAME',
        material_type: 'SEMI_FINISHED',
        material_type_id: materialTypeMap['SEMI_FIN'] || null,
        material_group_id: defaultGroupId,
        description: 'Welded Frame Assembly - 500x500mm',
        base_uom_id: uomMap['PC'],
        weight_uom_id: uomMap['KG'],
        net_weight: 8.500,
        gross_weight: 9.000,
        length: 500,
        width: 500,
        hsn_code: '73089000',
        procurement_type: 'F',
        make_buy_indicator: 'MAKE',
        abc_indicator: 'B',
        status: 'ACTIVE',
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now
      },
      {
        company_id: companyId,
        material_number: 'SF-PAINTED-PANEL',
        material_type: 'SEMI_FINISHED',
        material_type_id: materialTypeMap['SEMI_FIN'] || null,
        material_group_id: defaultGroupId,
        description: 'Painted Panel - 1000x500x2mm',
        base_uom_id: uomMap['PC'],
        weight_uom_id: uomMap['KG'],
        net_weight: 7.850,
        gross_weight: 8.000,
        length: 1000,
        width: 500,
        thickness: 2.000,
        hsn_code: '72101200',
        procurement_type: 'F',
        make_buy_indicator: 'MAKE',
        abc_indicator: 'B',
        status: 'ACTIVE',
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now
      },
      // FINISHED GOODS
      {
        company_id: companyId,
        material_number: 'FG-CABINET-001',
        material_type: 'FINISHED_GOOD',
        material_type_id: materialTypeMap['FIN_GOOD'] || null,
        material_group_id: defaultGroupId,
        description: 'Steel Cabinet 1000x500x500mm',
        base_uom_id: uomMap['PC'],
        weight_uom_id: uomMap['KG'],
        net_weight: 45.000,
        gross_weight: 48.000,
        length: 1000,
        width: 500,
        height: 500,
        hsn_code: '73089000',
        procurement_type: 'F',
        make_buy_indicator: 'MAKE',
        abc_indicator: 'A',
        status: 'ACTIVE',
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now
      },
      {
        company_id: companyId,
        material_number: 'FG-ENCLOSURE-002',
        material_type: 'FINISHED_GOOD',
        material_type_id: materialTypeMap['FIN_GOOD'] || null,
        material_group_id: defaultGroupId,
        description: 'Electrical Enclosure 600x400x300mm',
        base_uom_id: uomMap['PC'],
        weight_uom_id: uomMap['KG'],
        net_weight: 25.000,
        gross_weight: 27.000,
        length: 600,
        width: 400,
        height: 300,
        hsn_code: '85369000',
        procurement_type: 'F',
        make_buy_indicator: 'MAKE',
        abc_indicator: 'A',
        status: 'ACTIVE',
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now
      },
      {
        company_id: companyId,
        material_number: 'FG-PANEL-003',
        material_type: 'FINISHED_GOOD',
        material_type_id: materialTypeMap['FIN_GOOD'] || null,
        material_group_id: defaultGroupId,
        description: 'Control Panel 800x600x200mm',
        base_uom_id: uomMap['PC'],
        weight_uom_id: uomMap['KG'],
        net_weight: 35.000,
        gross_weight: 37.000,
        length: 800,
        width: 600,
        height: 200,
        hsn_code: '85369000',
        procurement_type: 'F',
        make_buy_indicator: 'MAKE',
        abc_indicator: 'A',
        status: 'ACTIVE',
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now
      }
    ];

    // Check for existing materials and insert only new ones
    const existingMaterials = await queryInterface.sequelize.query(
      `SELECT material_number FROM material WHERE company_id = ${companyId} AND material_number IN (${materials.map(m => `'${m.material_number}'`).join(',')})`,
      { type: Sequelize.QueryTypes.SELECT }
    );

    const existingMaterialNumbers = new Set(existingMaterials.map(m => m.material_number));
    const newMaterials = materials.filter(m => !existingMaterialNumbers.has(m.material_number));

    // Insert only new materials
    if (newMaterials.length > 0) {
      await queryInterface.bulkInsert('material', newMaterials);
      console.log(`✓ Inserted ${newMaterials.length} new materials`);
    } else {
      console.log(`✓ All materials already exist, skipping insertion`);
    }

    // Get all material IDs (both existing and newly inserted)
    const allMaterials = await queryInterface.sequelize.query(
      `SELECT id, material_number FROM material WHERE company_id = ${companyId} AND material_number IN (${materials.map(m => `'${m.material_number}'`).join(',')})`,
      { type: Sequelize.QueryTypes.SELECT }
    );

    const materialIdMap = {};
    allMaterials.forEach(mat => {
      materialIdMap[mat.material_number] = mat.id;
    });

    // Define opening stock and prices
    const materialStockData = [
      // RAW MATERIALS - with opening stock
      { material_number: 'RM-STEEL-SHEET-2MM', opening_balance: 5000.000, unit_price: 65.00, standard_price: 65.00 },
      { material_number: 'RM-STEEL-SHEET-3MM', opening_balance: 3000.000, unit_price: 70.00, standard_price: 70.00 },
      { material_number: 'RM-STEEL-PLATE-5MM', opening_balance: 2000.000, unit_price: 75.00, standard_price: 75.00 },
      { material_number: 'RM-GI-SHEET-1MM', opening_balance: 4000.000, unit_price: 85.00, standard_price: 85.00 },
      { material_number: 'RM-SS-SHEET-2MM', opening_balance: 1500.000, unit_price: 250.00, standard_price: 250.00 },
      { material_number: 'RM-ANGLE-50X50', opening_balance: 2000.000, unit_price: 60.00, standard_price: 60.00 },
      { material_number: 'RM-CHANNEL-75X40', opening_balance: 1500.000, unit_price: 65.00, standard_price: 65.00 },
      { material_number: 'RM-FLAT-25X5', opening_balance: 1000.000, unit_price: 55.00, standard_price: 55.00 },
      { material_number: 'RM-BOLT-M8', opening_balance: 10000.000, unit_price: 2.50, standard_price: 2.50 },
      { material_number: 'RM-NUT-M8', opening_balance: 10000.000, unit_price: 1.50, standard_price: 1.50 },
      // SEMI-FINISHED GOODS - with opening stock
      { material_number: 'SF-CUT-SHEET-2MM', opening_balance: 100.000, unit_price: 2500.00, standard_price: 2500.00 },
      { material_number: 'SF-WELDED-FRAME', opening_balance: 50.000, unit_price: 800.00, standard_price: 800.00 },
      { material_number: 'SF-PAINTED-PANEL', opening_balance: 75.000, unit_price: 650.00, standard_price: 650.00 },
      // FINISHED GOODS - with opening stock
      { material_number: 'FG-CABINET-001', opening_balance: 20.000, unit_price: 5000.00, standard_price: 5000.00 },
      { material_number: 'FG-ENCLOSURE-002', opening_balance: 15.000, unit_price: 3500.00, standard_price: 3500.00 },
      { material_number: 'FG-PANEL-003', opening_balance: 10.000, unit_price: 4500.00, standard_price: 4500.00 }
    ];

    // Check for existing MaterialPlant and MaterialValuation records
    const materialIds = Object.values(materialIdMap);
    if (materialIds.length === 0) {
      console.log('No materials found to process');
      return;
    }

    const existingMaterialPlants = await queryInterface.sequelize.query(
      `SELECT material_id FROM material_plant WHERE plant_id = ${plantId} AND material_id IN (${materialIds.join(',')})`,
      { type: Sequelize.QueryTypes.SELECT }
    );
    const existingMaterialPlantIds = new Set(existingMaterialPlants.map(mp => mp.material_id));

    const existingMaterialValuations = await queryInterface.sequelize.query(
      `SELECT material_id FROM material_valuation WHERE plant_id = ${plantId} AND material_id IN (${materialIds.join(',')})`,
      { type: Sequelize.QueryTypes.SELECT }
    );
    const existingMaterialValuationIds = new Set(existingMaterialValuations.map(mv => mv.material_id));

    // Create MaterialPlant records with opening stock
    const materialPlantRecords = [];
    const materialValuationRecords = [];
    const materialPlantUpdates = [];
    const materialValuationUpdates = [];

    for (const stock of materialStockData) {
      const materialId = materialIdMap[stock.material_number];
      if (!materialId) continue;

      const material = materials.find(m => m.material_number === stock.material_number);
      const baseUomId = material.base_uom_id;

      // MaterialPlant record (only if not exists)
      if (!existingMaterialPlantIds.has(materialId)) {
        materialPlantRecords.push({
          material_id: materialId,
          plant_id: plantId,
          storage_location: 'STORE001',
          mrp_type: 'PD', // MRP Planning
          reorder_point: stock.opening_balance * 0.2, // 20% of opening stock
          safety_stock: stock.opening_balance * 0.1, // 10% of opening stock
          max_stock: stock.opening_balance * 2, // 2x opening stock
          procurement_type: material.procurement_type || 'E',
          opening_balance: stock.opening_balance,
          opening_balance_date: openingBalanceDate,
          opening_balance_uom_id: baseUomId,
          opening_balance_unit_cost: stock.unit_price,
          status: 'ACTIVE',
          created_by: userId,
          updated_by: userId,
          created_at: now,
          updated_at: now
        });
      } else {
        // Collect updates for existing MaterialPlant
        materialPlantUpdates.push({
          materialId,
          stock,
          baseUomId,
          material
        });
      }

      // MaterialValuation record (only if not exists)
      if (!existingMaterialValuationIds.has(materialId)) {
        materialValuationRecords.push({
          material_id: materialId,
          plant_id: plantId,
          valuation_area: 'PLANT001',
          valuation_type: 'S', // Standard
          price_control: 'S', // Standard Price
          standard_price: stock.standard_price,
          moving_average_price: stock.standard_price,
          original_price: stock.standard_price,
          currency_id: currencyId,
          costing_lot_size: 1.000,
          last_price_change_date: openingBalanceDate,
          status: 'ACTIVE',
          effective_from: openingBalanceDate,
          created_by: userId,
          updated_by: userId,
          created_at: now,
          updated_at: now
        });
      } else {
        // Collect updates for existing MaterialValuation
        materialValuationUpdates.push({
          materialId,
          stock
        });
      }
    }

    // Insert MaterialPlant records
    if (materialPlantRecords.length > 0) {
      await queryInterface.bulkInsert('material_plant', materialPlantRecords);
      console.log(`✓ Created ${materialPlantRecords.length} MaterialPlant records`);
    }

    // Update existing MaterialPlant records
    if (materialPlantUpdates.length > 0) {
      for (const update of materialPlantUpdates) {
        await queryInterface.sequelize.query(
          `UPDATE material_plant SET 
            opening_balance = ${update.stock.opening_balance},
            opening_balance_date = '${openingBalanceDate.toISOString().split('T')[0]}',
            opening_balance_uom_id = ${update.baseUomId},
            opening_balance_unit_cost = ${update.stock.unit_price},
            reorder_point = ${update.stock.opening_balance * 0.2},
            safety_stock = ${update.stock.opening_balance * 0.1},
            max_stock = ${update.stock.opening_balance * 2},
            updated_by = ${userId},
            updated_at = NOW()
          WHERE material_id = ${update.materialId} AND plant_id = ${plantId}`
        );
      }
      console.log(`✓ Updated ${materialPlantUpdates.length} existing MaterialPlant records`);
    }

    // Insert MaterialValuation records
    if (materialValuationRecords.length > 0) {
      await queryInterface.bulkInsert('material_valuation', materialValuationRecords);
      console.log(`✓ Created ${materialValuationRecords.length} MaterialValuation records`);
    }

    // Update existing MaterialValuation records
    if (materialValuationUpdates.length > 0) {
      for (const update of materialValuationUpdates) {
        await queryInterface.sequelize.query(
          `UPDATE material_valuation SET 
            standard_price = ${update.stock.standard_price},
            moving_average_price = ${update.stock.standard_price},
            original_price = ${update.stock.standard_price},
            last_price_change_date = '${openingBalanceDate.toISOString().split('T')[0]}',
            updated_by = ${userId},
            updated_at = NOW()
          WHERE material_id = ${update.materialId} AND plant_id = ${plantId} AND valuation_area = 'PLANT001'`
        );
      }
      console.log(`✓ Updated ${materialValuationUpdates.length} existing MaterialValuation records`);
    }

    // Create Inventory Goods Receipt for opening balance (to make stock available in system)
    if (fiscalYearId && materialStockData.length > 0) {
      const openingGRNumber = `OB-${new Date().getFullYear()}-001`;
      
      // Check if opening balance GR already exists
      const existingGRResult = await queryInterface.sequelize.query(
        `SELECT id FROM inventory_goods_receipt WHERE movement_number = '${openingGRNumber}' AND reference_number = 'OPENING_BALANCE' LIMIT 1`,
        { type: Sequelize.QueryTypes.SELECT }
      );

      let grId = existingGRResult && existingGRResult.length > 0 ? existingGRResult[0].id : null;

      if (!grId) {
        // Create Goods Receipt Header
        await queryInterface.bulkInsert('inventory_goods_receipt', [{
          company_id: companyId,
          plant_id: plantId,
          fiscal_year_id: fiscalYearId,
          movement_number: openingGRNumber,
          movement_date: openingBalanceDate,
          movement_type: '101',
          reference_type: 'ADJUSTMENT',
          reference_number: 'OPENING_BALANCE',
          storage_location: 'STORE001',
          status: 'POSTED',
          posted_at: now,
          posted_by: userId,
          created_by: userId,
          updated_by: userId,
          created_at: now,
          updated_at: now
        }]);

        // Get the inserted GR ID
        const grResult = await queryInterface.sequelize.query(
          `SELECT id FROM inventory_goods_receipt WHERE movement_number = '${openingGRNumber}' LIMIT 1`,
          { type: Sequelize.QueryTypes.SELECT }
        );

        grId = grResult && grResult.length > 0 ? grResult[0].id : null;
      }

      if (grId) {
        // Check for existing GR items
        const existingGRItems = await queryInterface.sequelize.query(
          `SELECT material_id FROM inventory_goods_receipt_item WHERE movement_id = ${grId}`,
          { type: Sequelize.QueryTypes.SELECT }
        );
        const existingGRMaterialIds = new Set((existingGRItems || []).map(item => item.material_id));

        // Create Goods Receipt Items (only for materials not already in GR)
        const grItems = [];
        let lineNumber = existingGRItems.length > 0 ? existingGRItems.length + 1 : 1;

        materialStockData.forEach(stock => {
          const materialId = materialIdMap[stock.material_number];
          if (!materialId || existingGRMaterialIds.has(materialId)) return;

          const material = materials.find(m => m.material_number === stock.material_number);
          const baseUomId = material.base_uom_id;
          const totalAmount = stock.opening_balance * stock.unit_price;

          grItems.push({
            movement_id: grId,
            line_number: lineNumber++,
            material_id: materialId,
            description: material.description,
            quantity: stock.opening_balance,
            uom_id: baseUomId,
            unit_price: stock.unit_price,
            total_amount: totalAmount,
            batch_number: null,
            remarks: 'Opening Balance Entry',
            created_at: now,
            updated_at: now
          });
        });

        if (grItems.length > 0) {
          await queryInterface.bulkInsert('inventory_goods_receipt_item', grItems);
          console.log(`✓ Added ${grItems.length} items to Opening Balance Goods Receipt: ${openingGRNumber}`);
        } else if (existingGRItems.length > 0) {
          console.log(`✓ Opening Balance Goods Receipt already exists with ${existingGRItems.length} items`);
        }
      }
    }

    console.log(`✓ Seeded ${materials.length} materials with opening stock and prices`);
    console.log(`✓ Created ${materialPlantRecords.length} MaterialPlant records`);
    console.log(`✓ Created ${materialValuationRecords.length} MaterialValuation records`);
  },

  async down(queryInterface, Sequelize) {
    // Delete in reverse order
    await queryInterface.bulkDelete('material_valuation', {
      valuation_area: 'PLANT001'
    }, {});

    await queryInterface.bulkDelete('material_plant', {
      storage_location: 'STORE001'
    }, {});

    await queryInterface.bulkDelete('material', {
      material_number: {
        [Sequelize.Op.like]: 'RM-%'
      }
    }, {});

    await queryInterface.bulkDelete('material', {
      material_number: {
        [Sequelize.Op.like]: 'SF-%'
      }
    }, {});

    await queryInterface.bulkDelete('material', {
      material_number: {
        [Sequelize.Op.like]: 'FG-%'
      }
    }, {});
  }
};

