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 = 'SEMI_FIN'");
    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[0].id;
    const userId = users[0].id;

    // Check if material already exists
    const [existing] = 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 (existing && existing.length > 0) {
      console.log('Material RCAB00958 already exists. Updating it...');
      await db.sequelize.query(
        `UPDATE material 
         SET material_type = 'SEMI_FINISHED', 
             material_type_id = :semiFinTypeId,
             description = 'VARIAC LEG',
             material_group = 'ASSEMBLY',
             base_uom_id = :uomId,
             status = 'ACTIVE',
             updated_at = NOW()
         WHERE id = :materialId`,
        {
          replacements: {
            materialId: existing[0].id,
            semiFinTypeId,
            uomId: defaultUOMId
          },
          type: db.sequelize.QueryTypes.UPDATE
        }
      );
      console.log('✅ Material RCAB00958 updated successfully!');
    } else {
      // Create new material
      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, 'RCAB00958', 'SEMI_FINISHED', :semiFinTypeId, 'ASSEMBLY', 'VARIAC LEG', :uomId, false, false, 'ACTIVE', :userId, NOW(), NOW())`,
        {
          replacements: {
            companyId,
            semiFinTypeId,
            uomId: defaultUOMId,
            userId
          },
          type: db.sequelize.QueryTypes.INSERT
        }
      );
      console.log('✅ Material RCAB00958 created successfully!');
    }

    // Display the created material
    const [material] = await db.sequelize.query(
      `SELECT m.id, m.material_number, m.description, m.material_type, mt.type_name, m.material_group, m.status
       FROM material m
       LEFT JOIN material_type mt ON m.material_type_id = mt.id
       WHERE m.material_number = 'RCAB00958'`,
      {
        type: db.sequelize.QueryTypes.SELECT
      }
    );

    if (material && material.length > 0) {
      console.log('\n📦 Material Details:');
      console.log('  Material Number:', material[0].material_number);
      console.log('  Description:', material[0].description);
      console.log('  Material Type:', material[0].material_type, `(${material[0].type_name})`);
      console.log('  Material Group:', material[0].material_group);
      console.log('  Status:', material[0].status);
    }

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

