'use strict';

/**
 * Cabinet Finished Good BOM Seeder
 * Based on the BOM document for RCAB00958R4 - CABINET
 * 
 * Structure:
 * - Finished Good: RCAB00958R4 (CABINET)
 * - Assemblies: 7 sub-assemblies
 * - Raw Materials: 2 items (M12 THREAD, JAINSON WHEEL)
 * 
 * Drawing Number: RCAB00958R4
 * Revision: REV 04
 */

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 Cabinet Finished Good and Components...');

    // Define all materials
    const materials = [
      // 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'
      },
      // Assemblies (Semi-Finished)
      {
        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'
      },
      // Raw Materials
      {
        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 = {};
    for (const mat of materials) {
      // Check if material 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
        }
      );

      const materialTypeEnum = getMaterialTypeEnum(mat.material_type_id);
      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,
        base_uom_id: mat.base_uom_id,
        drawing_number: mat.drawing_number || null,
        revision_number: mat.revision_number || null,
        status: mat.status,
        created_by: userId,
        updated_by: userId,
        created_at: new Date(),
        updated_at: new Date()
      };

      if (existing && existing.length > 0) {
        // Update existing material
        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,
            drawing_number = :drawing_number,
            revision_number = :revision_number,
            status = :status,
            updated_by = :updated_by,
            updated_at = NOW()
          WHERE id = :id`,
          {
            replacements: { ...materialData, id: existing[0].id },
            type: Sequelize.QueryTypes.UPDATE
          }
        );
        materialIdMap[mat.material_number] = existing[0].id;
        console.log(`  ✓ Updated: ${mat.material_number} - ${mat.description}`);
      } else {
        // Insert new material
        const [result] = await queryInterface.sequelize.query(
          `INSERT INTO material 
          (company_id, material_number, description, material_type, material_type_id, material_group, material_group_id, base_uom_id, drawing_number, revision_number, status, created_by, updated_by, created_at, updated_at)
          VALUES 
          (:company_id, :material_number, :description, :material_type, :material_type_id, :material_group, :material_group_id, :base_uom_id, :drawing_number, :revision_number, :status, :created_by, :updated_by, :created_at, :updated_at)`,
          {
            replacements: materialData,
            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
          }
        );
        
        if (inserted && inserted.length > 0) {
          materialIdMap[mat.material_number] = inserted[0].id;
          console.log(`  ✓ Created: ${mat.material_number} - ${mat.description}`);
        } else {
          console.error(`  ✗ Failed to get ID for: ${mat.material_number}`);
        }
      }
    }

    // Create BOM for CABINET (RCAB00958R4)
    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 [existingBOM] = 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 bomHeaderId;
    const bomNumber = 'BOM-RCAB00958R4-REV04';

    if (existingBOM && existingBOM.length > 0) {
      bomHeaderId = existingBOM[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',
          status = 'ACTIVE',
          updated_by = :userId,
          updated_at = NOW()
        WHERE id = :bomHeaderId`,
        {
          replacements: {
            bomHeaderId,
            plantId,
            uomId: defaultUOMId,
            userId
          },
          type: Sequelize.QueryTypes.UPDATE
        }
      );
      console.log(`  ✓ Updated BOM Header: ${bomNumber}`);

      // Delete existing components
      await queryInterface.sequelize.query(
        `DELETE FROM bom_component WHERE bom_id = :bomHeaderId`,
        {
          replacements: { bomHeaderId },
          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, status, created_by, created_at, updated_at)
        VALUES
        (:companyId, :plantId, :materialId, :bomNumber, 'PRODUCTION', 1.000, :uomId, 'RCAB00958R4', 'REV 04', '01', 'ACTIVE', :userId, NOW(), NOW())`,
        {
          replacements: {
            companyId,
            plantId,
            materialId: cabinetMaterialId,
            bomNumber,
            uomId: defaultUOMId,
            userId
          },
          type: Sequelize.QueryTypes.INSERT
        }
      );

      // Get the inserted BOM Header ID
      const [insertedBOM] = await queryInterface.sequelize.query(
        `SELECT id FROM bom_header WHERE bom_number = :bomNumber LIMIT 1`,
        {
          replacements: { bomNumber },
          type: Sequelize.QueryTypes.SELECT
        }
      );

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

    // Create BOM Components
    console.log('\nCreating BOM Components...');
    
    const bomComponents = [
      { 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 bomComponents) {
      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: bomHeaderId,
            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✅ Cabinet Finished Good BOM created successfully!');
    console.log('\n📦 BOM Structure:');
    console.log('  Finished Good: RCAB00958R4 - CABINET (REV 04)');
    console.log('    Drawing: RCAB00958R4');
    console.log('    Components:');
    bomComponents.forEach(comp => {
      console.log(`      ${comp.item_number}. ${comp.description} (${comp.material_number}) x ${comp.quantity}`);
    });
  },

  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 = 'BOM-RCAB00958R4-REV04'`
    );
    
    await queryInterface.sequelize.query(
      `DELETE FROM bom_header WHERE bom_number = 'BOM-RCAB00958R4-REV04'`
    );

    // Delete materials
    const materialNumbers = [
      'RCAB00958R4', 'RCAB00958R3-01', 'RCAB00958R2-03', 'RCAB00958R4-04',
      'RCAB00958R0-05', 'RCAB00958R4-06', 'RCAB00958R4-07', 'RCAB00958R1-08',
      'M12-THREAD', 'JAINSON-WHEEL-RPU60x25-BB'
    ];

    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}`);
      }
    }
  }
};

