'use strict';

/**
 * Example Seeder: Creating a CABINET BOM where VARIAC LEG is a component
 * 
 * This demonstrates how to create a higher-level BOM where:
 * - CABINET is the finished good
 * - VARIAC LEG (which has its own BOM) is a component
 */

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') LIMIT 10"),
      queryInterface.sequelize.query("SELECT id, type_code FROM material_type WHERE type_code IN ('FIN_GOOD', '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 = {};
    materialTypes[0].forEach(mt => {
      materialTypeMap[mt.type_code] = mt.id;
    });

    // Check if VARIAC LEG exists (from previous seeder)
    const [variacLeg] = await queryInterface.sequelize.query(
      `SELECT id FROM material WHERE company_id = :companyId AND material_number = 'RCAB00958' LIMIT 1`,
      {
        replacements: { companyId },
        type: Sequelize.QueryTypes.SELECT
      }
    );

    if (!variacLeg || variacLeg.length === 0) {
      console.log('VARIAC LEG (RCAB00958) not found. Please run the VARIAC LEG seeder first.');
      return;
    }

    const variacLegId = variacLeg.id;

    // Define CABINET and additional components
    const cabinetMaterials = [
      {
        material_number: 'CABINET-001',
        description: 'Complete Cabinet Assembly',
        material_type_id: materialTypeMap['FIN_GOOD'],
        material_group: 'CABINET',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'CABINET-DOOR-001',
        description: 'Cabinet Door Assembly',
        material_type_id: materialTypeMap['ASSEMBLY'],
        material_group: 'CABINET',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'CABINET-BACK-001',
        description: 'Cabinet Back Panel',
        material_type_id: materialTypeMap['ASSEMBLY'],
        material_group: 'CABINET',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'CABINET-TOP-001',
        description: 'Cabinet Top Panel',
        material_type_id: materialTypeMap['ASSEMBLY'],
        material_group: 'CABINET',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'CABINET-SIDE-001',
        description: 'Cabinet Side Panel',
        material_type_id: materialTypeMap['ASSEMBLY'],
        material_group: 'CABINET',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      },
      {
        material_number: 'SCREW-M6-001',
        description: 'M6 Mounting Screw',
        material_type_id: materialTypeMap['RAW_MAT'],
        material_group: 'FASTENERS',
        base_uom_id: defaultUOMId,
        status: 'ACTIVE'
      }
    ];

    // Insert materials and create material map
    const materialMap = {};
    materialMap['RCAB00958'] = variacLegId; // Use existing VARIAC LEG

    for (const mat of cabinetMaterials) {
      // 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 CABINET
    const cabinetMaterialId = materialMap['CABINET-001'];
    if (!cabinetMaterialId) {
      console.log('CABINET material 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-CABINET-001' 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-CABINET-001', :materialId, 'PRODUCTION', '01', 1.000, :uomId, CURDATE(), 'CABINET-001', 'REV 01', 'ACTIVE', :userId, NOW(), NOW())`,
        {
          replacements: {
            companyId,
            plantId,
            materialId: cabinetMaterialId,
            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-CABINET-001' LIMIT 1`,
        {
          replacements: { companyId, plantId },
          type: Sequelize.QueryTypes.SELECT
        }
      );
      bomId = insertedBOM.id;
    }

    // BOM Components for CABINET
    // NOTE: VARIAC LEG (RCAB00958) is a component here, but it has its own BOM!
    const bomComponents = [
      { item_no: 1, part_no: 'RCAB00958', qty: 4, description: 'VARIAC LEG (has its own BOM)' },
      { item_no: 2, part_no: 'CABINET-DOOR-001', qty: 1, description: 'Cabinet Door' },
      { item_no: 3, part_no: 'CABINET-BACK-001', qty: 1, description: 'Cabinet Back Panel' },
      { item_no: 4, part_no: 'CABINET-TOP-001', qty: 1, description: 'Cabinet Top Panel' },
      { item_no: 5, part_no: 'CABINET-SIDE-001', qty: 2, description: 'Cabinet Side Panels' },
      { item_no: 6, part_no: 'SCREW-M6-001', qty: 20, description: 'Mounting Screws' }
    ];

    // 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(`
    ============================================
    CABINET BOM Created Successfully!
    ============================================
    
    BOM Number: BOM-CABINET-001
    Finished Good: CABINET-001 (Complete Cabinet Assembly)
    
    Components:
    - 4x RCAB00958 (VARIAC LEG) ← This has its own BOM!
    - 1x CABINET-DOOR-001
    - 1x CABINET-BACK-001
    - 1x CABINET-TOP-001
    - 2x CABINET-SIDE-001
    - 20x SCREW-M6-001
    
    ============================================
    IMPORTANT: Multi-Level BOM Structure
    ============================================
    
    Level 2: CABINET (Finished Good)
      └── Level 1: VARIAC LEG (Component with its own BOM)
            └── Level 0: All VARIAC LEG components (from BOM-RCAB00958-REV03)
    
    When you produce 1 CABINET, the system will:
    1. Require 4 VARIAC LEGs
    2. Each VARIAC LEG requires all its components (from its BOM)
    3. Plus other cabinet components
    
    ============================================
    `);
  },

  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-CABINET-001')"
    );
    await queryInterface.sequelize.query(
      "DELETE FROM bom_header WHERE bom_number = 'BOM-CABINET-001'"
    );
    
    // Delete materials (but keep VARIAC LEG)
    await queryInterface.sequelize.query(
      "DELETE FROM material WHERE material_number IN ('CABINET-001', 'CABINET-DOOR-001', 'CABINET-BACK-001', 'CABINET-TOP-001', 'CABINET-SIDE-001', 'SCREW-M6-001')"
    );
  }
};

