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 [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 [materialTypes] = await db.sequelize.query("SELECT id, type_code FROM material_type WHERE type_code IN ('SEMI_FIN', 'RAW_MAT')");
    const [users] = await db.sequelize.query("SELECT id FROM user WHERE username = 'admin' LIMIT 1");

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

    const companyId = companies[0].id;
    const defaultUOMId = uoms.find(u => ['EA', 'PC', 'PCS', 'NOS'].includes(u.uom_code))?.id || uoms[0].id;
    const semiFinTypeId = materialTypes.find(mt => mt.type_code === 'SEMI_FIN')?.id;
    const rawMatTypeId = materialTypes.find(mt => mt.type_code === 'RAW_MAT')?.id;
    const userId = users[0].id;

    // Define assemblies (Semi-Finished Goods)
    const assemblies = [
      { material_number: 'RCAB00958R4-01-01', description: 'BOTTOM PLATE ASSY', material_group: 'ASSEMBLY' },
      { material_number: 'RCAB00958R0-01-02', description: 'TOP FRAME ASSY', material_group: 'ASSEMBLY' },
      { material_number: 'RCAB00958R2-01-03', description: 'MIDDLE FRAME 1 ASSY', material_group: 'ASSEMBLY' },
      { material_number: 'RCAB00958R0-01-05', description: 'MOTOR CLAMP ASSY X', material_group: 'ASSEMBLY' },
      { material_number: 'RCAB00958R1-01-06', description: 'SMC PLATE ASSY X', material_group: 'ASSEMBLY' },
      { material_number: 'RCAB00958R0-01-07', description: 'CORNER PIECE ASSY 1 X', material_group: 'ASSEMBLY' },
    ];

    // Define raw materials
    const rawMaterials = [
      { material_number: 'RCAB00958R0-01-00_01', description: 'VERTICAL LEG FR X', material_group: 'PARTS' },
      { material_number: 'RCAB00958R0-01-00_02', description: 'VERTICAL LEG FL', material_group: 'PARTS' },
      { material_number: 'RCAB00958R0-01-00_03', description: 'VERTICAL LEG BR', material_group: 'PARTS' },
      { material_number: 'RCAB00958R0-01-00_04', description: 'VERTICAL LEG BL', material_group: 'PARTS' },
      { material_number: 'RCAB00958R1-01-00_05', description: 'MCB CLAMP X', material_group: 'PARTS' },
      { material_number: 'RCAB00958R0-01-00_06', description: 'BY PASS PLATE X', material_group: 'PARTS' },
      { material_number: 'RCAB00958R2-01-00_07', description: 'VARIAC ANGLE 1 X', material_group: 'PARTS' },
      { material_number: 'RCAB00958R2-01-00_08', description: 'VARIAC ANGLE 2', material_group: 'PARTS' },
      { material_number: 'RCAB00958R1-01-00_09', description: 'VARIAC TOP CHANNEL', material_group: 'PARTS' },
      { material_number: 'RCAB00958R0-01-00_10', description: 'CONTACTOR PLATE X', material_group: 'PARTS' },
      { material_number: 'RCAB00958R0-01-00_14', description: 'M5 X 12 SPACER X', material_group: 'FASTENERS' },
      { material_number: 'RCAB00958R0-01-00_15', description: 'TERMINAL MOUNTING PIECE 1 X', material_group: 'PARTS' },
      { material_number: 'RCAB00958R0-01-00_16', description: 'TERMINAL MOUNTING PIECE 2 X', material_group: 'PARTS' },
      { material_number: 'RCAB00958R0-01-00_18', description: 'M4 X 10 MM CSK WELD SCREW', material_group: 'FASTENERS' },
      { material_number: 'SENSING-PLATE', description: 'SENSING PLATE', material_group: 'PARTS' },
    ];

    let createdCount = 0;
    let updatedCount = 0;

    // Create/Update Assemblies (Semi-Finished)
    console.log('\n📦 Creating/Updating Assemblies (Semi-Finished Goods)...');
    for (const assembly of assemblies) {
      const [existing] = await db.sequelize.query(
        `SELECT id FROM material WHERE company_id = :companyId AND material_number = :matNumber LIMIT 1`,
        {
          replacements: { companyId, matNumber: assembly.material_number },
          type: db.sequelize.QueryTypes.SELECT
        }
      );

      if (existing && existing.length > 0) {
        await db.sequelize.query(
          `UPDATE material 
           SET material_type = 'SEMI_FINISHED', 
               material_type_id = :semiFinTypeId,
               description = :description,
               material_group = :matGroup,
               base_uom_id = :uomId,
               status = 'ACTIVE',
               updated_at = NOW()
           WHERE id = :materialId`,
          {
            replacements: {
              materialId: existing[0].id,
              semiFinTypeId,
              description: assembly.description,
              matGroup: assembly.material_group,
              uomId: defaultUOMId
            },
            type: db.sequelize.QueryTypes.UPDATE
          }
        );
        console.log(`  ✓ Updated: ${assembly.material_number} - ${assembly.description}`);
        updatedCount++;
      } else {
        await db.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, 'SEMI_FINISHED', :semiFinTypeId, :matGroup, :description, :uomId, false, false, 'ACTIVE', :userId, NOW(), NOW())`,
          {
            replacements: {
              companyId,
              matNumber: assembly.material_number,
              semiFinTypeId,
              matGroup: assembly.material_group,
              description: assembly.description,
              uomId: defaultUOMId,
              userId
            },
            type: db.sequelize.QueryTypes.INSERT
          }
        );
        console.log(`  ✓ Created: ${assembly.material_number} - ${assembly.description}`);
        createdCount++;
      }
    }

    // Create/Update Raw Materials
    console.log('\n🔩 Creating/Updating Raw Materials...');
    for (const rawMat of rawMaterials) {
      const [existing] = await db.sequelize.query(
        `SELECT id FROM material WHERE company_id = :companyId AND material_number = :matNumber LIMIT 1`,
        {
          replacements: { companyId, matNumber: rawMat.material_number },
          type: db.sequelize.QueryTypes.SELECT
        }
      );

      if (existing && existing.length > 0) {
        await db.sequelize.query(
          `UPDATE material 
           SET material_type = 'RAW_MATERIAL', 
               material_type_id = :rawMatTypeId,
               description = :description,
               material_group = :matGroup,
               base_uom_id = :uomId,
               status = 'ACTIVE',
               updated_at = NOW()
           WHERE id = :materialId`,
          {
            replacements: {
              materialId: existing[0].id,
              rawMatTypeId,
              description: rawMat.description,
              matGroup: rawMat.material_group,
              uomId: defaultUOMId
            },
            type: db.sequelize.QueryTypes.UPDATE
          }
        );
        console.log(`  ✓ Updated: ${rawMat.material_number} - ${rawMat.description}`);
        updatedCount++;
      } else {
        await db.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', :rawMatTypeId, :matGroup, :description, :uomId, false, false, 'ACTIVE', :userId, NOW(), NOW())`,
          {
            replacements: {
              companyId,
              matNumber: rawMat.material_number,
              rawMatTypeId,
              matGroup: rawMat.material_group,
              description: rawMat.description,
              uomId: defaultUOMId,
              userId
            },
            type: db.sequelize.QueryTypes.INSERT
          }
        );
        console.log(`  ✓ Created: ${rawMat.material_number} - ${rawMat.description}`);
        createdCount++;
      }
    }

    console.log('\n✅ Summary:');
    console.log(`  Created: ${createdCount} materials`);
    console.log(`  Updated: ${updatedCount} materials`);
    console.log(`  Total: ${createdCount + updatedCount} materials processed`);

    process.exit(0);
  } catch (error) {
    console.error('❌ Error:', error.message);
    process.exit(1);
  }
})();

