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 material IDs
    const cabinetMaterialRows = await db.sequelize.query(
      `SELECT id FROM material WHERE company_id = :companyId AND material_number = 'CABINET-001' LIMIT 1`,
      {
        replacements: { companyId },
        type: db.sequelize.QueryTypes.SELECT
      }
    );

    const variacLegMaterialRows = 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 (!cabinetMaterialRows || cabinetMaterialRows.length === 0) {
      console.error('❌ CABINET-001 material not found. Please create it first.');
      process.exit(1);
    }

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

    const cabinetId = cabinetMaterialRows[0].id;
    const variacLegId = variacLegMaterialRows[0].id;

    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: cabinetId },
          type: db.sequelize.QueryTypes.SELECT,
          transaction
        }
      );

      let bomHeaderId;

      if (existingBOM && existingBOM.length > 0) {
        console.log('BOM for CABINET-001 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 = NULL,
               revision_number = NULL,
               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-CABINET-001`;

        // 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, NULL, NULL, '01', 'ACTIVE', :userId, NOW(), NOW())`,
          {
            replacements: {
              companyId,
              plantId,
              materialId: cabinetId,
              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...');

      // Component: VARIAC LEG (RCAB00958) - 4 units (based on typical cabinet structure)
      const components = [
        {
          item_number: 10,
          material_id: variacLegId,
          quantity: 4.000,
          uom_id: defaultUOMId,
          scrap_percentage: 0.000
        }
      ];

      for (const component of components) {
        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, :scrapPercentage, NOW(), NOW())`,
          {
            replacements: {
              bomId: bomHeaderId,
              itemNumber: component.item_number,
              materialId: component.material_id,
              quantity: component.quantity,
              uomId: component.uom_id,
              scrapPercentage: component.scrap_percentage
            },
            type: db.sequelize.QueryTypes.INSERT,
            transaction
          }
        );
        console.log(`  ✓ Added: Item ${component.item_number} - VARIAC LEG (Qty: ${component.quantity})`);
      }

      await transaction.commit();

      console.log('\n✅ BOM for CABINET-001 created successfully!');
      console.log('\n📦 BOM Structure:');
      console.log('  Finished Good: CABINET-001');
      console.log('    └── VARIAC LEG (RCAB00958) x 4 units');

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

