const db = require('../models');

(async () => {
  try {
    await db.sequelize.authenticate();
    console.log('Connected to database');

    // Get required master data
    const [companies] = await db.sequelize.query("SELECT id FROM company LIMIT 1");
    const [plants] = await db.sequelize.query("SELECT id, company_id FROM plant LIMIT 1");
    const [uoms] = await db.sequelize.query("SELECT id, uom_code FROM uom WHERE uom_code IN ('EA', 'PC', 'PCS', 'NOS') OR uom_code LIKE '%EA%' LIMIT 10");
    const [users] = await db.sequelize.query("SELECT id FROM user WHERE username = 'admin' LIMIT 1");

    if (companies.length === 0 || plants.length === 0 || uoms.length === 0 || users.length === 0) {
      console.error('Required master data not found. Please ensure company, plant, UOM, and user data exists.');
      process.exit(1);
    }

    const companyId = companies[0].id;
    const plantId = plants[0].id;
    const defaultUOMId = uoms.find(u => ['EA', 'PC', 'PCS', 'NOS'].includes(u.uom_code))?.id || uoms[0].id;
    const userId = users[0].id;

    // Get VARIAC LEG material ID
    const variacLegMaterial = await db.sequelize.query(
      `SELECT id FROM material WHERE company_id = :companyId AND material_number = 'RCAB00958' LIMIT 1`,
      {
        replacements: { companyId },
        type: db.sequelize.QueryTypes.SELECT
      }
    );

    if (!variacLegMaterial || variacLegMaterial.length === 0) {
      console.error('❌ RCAB00958 (VARIAC LEG) material not found. Please create it first.');
      process.exit(1);
    }

    const variacLegId = variacLegMaterial[0].id;

    // Get all component material IDs
    const componentMaterials = await db.sequelize.query(
      `SELECT id, material_number FROM material 
       WHERE company_id = :companyId 
       AND material_number IN (
         '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'
       )`,
      {
        replacements: { companyId },
        type: db.sequelize.QueryTypes.SELECT
      }
    );

    // Create a map of material_number to material_id
    const materialMap = {};
    componentMaterials.forEach(mat => {
      materialMap[mat.material_number] = mat.id;
    });

    // Define BOM components based on the image
    const components = [
      { item_number: 1, material_number: 'RCAB00958R4-01-01', quantity: 1.000, description: 'BOTTOM PLATE ASSY' },
      { item_number: 2, material_number: 'RCAB00958R0-01-02', quantity: 1.000, description: 'TOP FRAME ASSY' },
      { item_number: 3, material_number: 'RCAB00958R2-01-03', quantity: 2.000, description: 'MIDDLE FRAME 1 ASSY' },
      { item_number: 4, material_number: 'RCAB00958R0-01-05', quantity: 3.000, description: 'MOTOR CLAMP ASSY' },
      { item_number: 5, material_number: 'RCAB00958R1-01-06', quantity: 3.000, description: 'SMC PLATE ASSY' },
      { item_number: 6, material_number: 'RCAB00958R0-01-07', quantity: 6.000, description: 'CORNER PIECE ASSY 1' },
      { item_number: 7, material_number: 'RCAB00958R0-01-00_01', quantity: 1.000, description: 'VERTICAL LEG FR' },
      { item_number: 8, material_number: 'RCAB00958R0-01-00_02', quantity: 1.000, description: 'VERTICAL LEG FL' },
      { item_number: 9, material_number: 'RCAB00958R0-01-00_03', quantity: 1.000, description: 'VERTICAL LEG BR' },
      { item_number: 10, material_number: 'RCAB00958R0-01-00_04', quantity: 1.000, description: 'VERTICAL LEG BL' },
      { item_number: 11, material_number: 'RCAB00958R1-01-00_05', quantity: 1.000, description: 'MCB CLAMP' },
      { item_number: 12, material_number: 'RCAB00958R0-01-00_06', quantity: 1.000, description: 'BY PASS PLATE' },
      { item_number: 13, material_number: 'RCAB00958R2-01-00_07', quantity: 1.000, description: 'VARIAC ANGLE 1' },
      { item_number: 14, material_number: 'RCAB00958R2-01-00_08', quantity: 1.000, description: 'VARIAC ANGLE 2' },
      { item_number: 15, material_number: 'RCAB00958R1-01-00_09', quantity: 1.000, description: 'VARIAC TOP CHANNEL' },
      { item_number: 16, material_number: 'RCAB00958R0-01-00_10', quantity: 1.000, description: 'CONTACTOR PLATE' },
      { item_number: 21, material_number: 'RCAB00958R0-01-00_14', quantity: 8.000, description: 'M5 X 12 SPACER' },
      { item_number: 22, material_number: 'RCAB00958R0-01-00_15', quantity: 2.000, description: 'TERMINAL MOUNTING PIECE 1' },
      { item_number: 23, material_number: 'RCAB00958R0-01-00_16', quantity: 4.000, description: 'TERMINAL MOUNTING PIECE 2' },
      { item_number: 24, material_number: 'RCAB00958R0-01-00_18', quantity: 6.000, description: 'M4 X 10 MM CSK WELD SCREW' },
      { item_number: 25, material_number: 'SENSING-PLATE', quantity: 1.000, description: 'SENSING PLATE' },
    ];

    const transaction = await db.sequelize.transaction();

    try {
      // Check if BOM already exists
      const existingBOM = await db.sequelize.query(
        `SELECT id FROM bom_header WHERE company_id = :companyId AND material_id = :materialId AND bom_type = 'PRODUCTION' LIMIT 1`,
        {
          replacements: { companyId, materialId: variacLegId },
          type: db.sequelize.QueryTypes.SELECT,
          transaction
        }
      );

      let bomHeaderId;

      if (existingBOM && existingBOM.length > 0) {
        console.log('BOM for VARIAC LEG (RCAB00958) already exists. Updating it...');
        bomHeaderId = existingBOM[0].id;

        // Update BOM Header
        await db.sequelize.query(
          `UPDATE bom_header 
           SET plant_id = :plantId,
               base_quantity = 1.000,
               base_uom_id = :uomId,
               drawing_number = 'RCAB00958',
               revision_number = 'REV 03',
               status = 'ACTIVE',
               updated_by = :userId,
               updated_at = NOW()
           WHERE id = :bomHeaderId`,
          {
            replacements: {
              bomHeaderId,
              plantId,
              uomId: defaultUOMId,
              userId
            },
            type: db.sequelize.QueryTypes.UPDATE,
            transaction
          }
        );

        // Delete existing components
        await db.sequelize.query(
          `DELETE FROM bom_component WHERE bom_id = :bomHeaderId`,
          {
            replacements: { bomHeaderId },
            type: db.sequelize.QueryTypes.DELETE,
            transaction
          }
        );
      } else {
        const bomNumber = `BOM-RCAB00958-REV03`;

        // Create BOM Header
        await db.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, 'RCAB00958', 'REV 03', '01', 'ACTIVE', :userId, NOW(), NOW())`,
          {
            replacements: {
              companyId,
              plantId,
              materialId: variacLegId,
              bomNumber,
              uomId: defaultUOMId,
              userId
            },
            type: db.sequelize.QueryTypes.INSERT,
            transaction
          }
        );

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

        bomHeaderId = insertedBOM[0].id;
        console.log(`✅ Created BOM Header: ${bomNumber}`);
      }

      // Create BOM Components
      console.log('\n📋 Creating BOM Components...');
      let addedCount = 0;
      let missingCount = 0;

      for (const component of components) {
        const materialId = materialMap[component.material_number];
        
        if (!materialId) {
          console.log(`  ⚠ Skipped: Item ${component.item_number} - ${component.material_number} (Material not found)`);
          missingCount++;
          continue;
        }

        await db.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, :quantity, :uomId, 0.000, NOW(), NOW())`,
          {
            replacements: {
              bomId: bomHeaderId,
              itemNumber: component.item_number,
              materialId: materialId,
              quantity: component.quantity,
              uomId: defaultUOMId
            },
            type: db.sequelize.QueryTypes.INSERT,
            transaction
          }
        );
        console.log(`  ✓ Added: Item ${component.item_number} - ${component.description} (Qty: ${component.quantity})`);
        addedCount++;
      }

      await transaction.commit();

      console.log('\n✅ BOM for VARIAC LEG (RCAB00958) created successfully!');
      console.log(`\n📊 Summary:`);
      console.log(`  Components added: ${addedCount}`);
      if (missingCount > 0) {
        console.log(`  Components skipped (material not found): ${missingCount}`);
      }
      console.log(`\n📦 BOM Structure:`);
      console.log(`  Assembly: VARIAC LEG (RCAB00958) - REV 03`);
      console.log(`    Drawing Number: RCAB00958`);
      console.log(`    Total Components: ${addedCount} items`);

      process.exit(0);
    } catch (error) {
      await transaction.rollback();
      throw error;
    }
  } catch (error) {
    console.error('❌ Error:', error.message);
    console.error(error.stack);
    process.exit(1);
  }
})();

