'use strict';

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 ('ASSEMBLY', 'RAW_MAT', 'FIN_GOOD')"),
      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 - use EA (Each) as default
    const uomMap = {};
    uoms[0].forEach(uom => {
      uomMap[uom.uom_code] = uom.id;
    });
    // Default to first UOM if EA not found
    const defaultUOMId = uomMap['EA'] || uomMap['PC'] || uomMap['PCS'] || uomMap['NOS'] || uoms[0][0]?.id;

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

    // Define all materials from the BOM
    const materials = [
      // Main Finished Good
      {
        material_number: 'RCAB00958',
        description: 'VARIAC LEG',
        material_type_id: materialTypeMap['FIN_GOOD'] || materialTypeMap['ASSEMBLY'],
        material_group: 'CABINET',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      // Sub-Assemblies
      {
        material_number: 'RCAB00958R4-01-01',
        description: 'BOTTOM PLATE ASSY',
        material_type_id: materialTypeMap['ASSEMBLY'],
        material_group: 'ASSEMBLY',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-02',
        description: 'TOP FRAME ASSY',
        material_type_id: materialTypeMap['ASSEMBLY'],
        material_group: 'ASSEMBLY',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R2-01-03',
        description: 'MIDDLE FRAME 1 ASSY',
        material_type_id: materialTypeMap['ASSEMBLY'],
        material_group: 'ASSEMBLY',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-05',
        description: 'MOTOR CLAMP ASSY X',
        material_type_id: materialTypeMap['ASSEMBLY'],
        material_group: 'ASSEMBLY',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R1-01-06',
        description: 'SMC PLATE ASSY X',
        material_type_id: materialTypeMap['ASSEMBLY'],
        material_group: 'ASSEMBLY',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'RCAB00958R0-01-07',
        description: 'CORNER PIECE ASSY 1 X',
        material_type_id: materialTypeMap['ASSEMBLY'],
        material_group: 'ASSEMBLY',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      // 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'
      }
    ];

    // Insert materials and create material map
    const materialMap = {};
    for (const mat of materials) {
      // Check if material already exists
      const [existing] = 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
        }
      );

      let materialId;
      if (existing && existing.length > 0) {
        materialId = existing[0].id;
      } else {
        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, 'RAW_MATERIAL', :matTypeId, :matGroup, :description, :uomId, false, false, :status, :userId, NOW(), NOW())`,
          {
            replacements: {
              companyId,
              matNumber: mat.material_number,
              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
          }
        );
        // Get the inserted material ID
        const [inserted] = 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
          }
        );
        materialId = inserted.id;
      }
      materialMap[mat.material_number] = materialId;
    }

    // Create BOM Header for VARIAC LEG
    const mainMaterialId = materialMap['RCAB00958'];
    if (!mainMaterialId) {
      console.log('Main material RCAB00958 not found. Cannot create BOM.');
      return;
    }

    // Check if BOM already exists
    const [existingBOM] = 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
      }
    );

    let bomId;
    if (existingBOM && existingBOM.length > 0) {
      bomId = existingBOM[0].id;
      // Delete existing components
      await queryInterface.sequelize.query(
        `DELETE FROM bom_component WHERE bom_id = :bomId`,
        {
          replacements: { bomId },
          type: Sequelize.QueryTypes.DELETE
        }
      );
    } else {
      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: mainMaterialId,
            uomId: defaultUOMId,
            userId
          },
          type: Sequelize.QueryTypes.INSERT
        }
      );
      // Get the inserted BOM ID
      const [insertedBOM] = 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
        }
      );
      bomId = insertedBOM.id;
    }

    // BOM Components based on the image
    const bomComponents = [
      { 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 }
    ];

    // Insert BOM Components
    for (const comp of bomComponents) {
      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,
              materialId: componentMaterialId,
              itemNo: comp.item_no,
              qty: comp.qty,
              uomId: defaultUOMId,
              userId
            },
            type: Sequelize.QueryTypes.INSERT
          }
        );
      }
    }

    console.log(`Successfully created ${materials.length} materials and BOM for VARIAC LEG (RCAB00958)`);
  },

  async down(queryInterface, Sequelize) {
    // Delete BOM components and header
    await queryInterface.sequelize.query(
      "DELETE FROM bom_component WHERE bom_id IN (SELECT id FROM bom_header WHERE bom_number = 'BOM-RCAB00958-REV03')"
    );
    await queryInterface.sequelize.query(
      "DELETE FROM bom_header WHERE bom_number = 'BOM-RCAB00958-REV03'"
    );
    
    // Delete materials
    await queryInterface.sequelize.query(
      "DELETE FROM material WHERE material_number LIKE 'RCAB00958%' OR material_number = 'SENSING-PLATE'"
    );
  }
};

