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

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

    // Get material type IDs
    const [typeResults] = await db.sequelize.query(
      `SELECT id, type_code FROM material_type WHERE type_code IN ('FIN_GOOD', 'SEMI_FIN', 'RAW_MAT')`
    );
    
    const typeMap = {};
    typeResults.forEach(r => {
      typeMap[r.type_code] = r.id;
    });
    
    console.log('Material Type IDs:', typeMap);

    // Update CABINET-001 to Finished Good
    await db.sequelize.query(
      `UPDATE material SET material_type = 'FINISHED_GOOD', material_type_id = :finGoodId WHERE material_number = 'CABINET-001'`,
      {
        replacements: { finGoodId: typeMap['FIN_GOOD'] },
        type: db.sequelize.QueryTypes.UPDATE
      }
    );
    console.log('Updated CABINET-001 to FINISHED_GOOD');

    // Update RCAB00958 (VARIAC LEG) to Semi-Finished
    await db.sequelize.query(
      `UPDATE material SET material_type = 'SEMI_FINISHED', material_type_id = :semiFinId WHERE material_number = 'RCAB00958'`,
      {
        replacements: { semiFinId: typeMap['SEMI_FIN'] },
        type: db.sequelize.QueryTypes.UPDATE
      }
    );
    console.log('Updated RCAB00958 to SEMI_FINISHED');

    // Update all sub-assemblies (RCAB00958R* without underscore) to Semi-Finished
    await db.sequelize.query(
      `UPDATE material SET material_type = 'SEMI_FINISHED', material_type_id = :semiFinId 
       WHERE material_number LIKE 'RCAB00958R%' 
       AND material_number NOT LIKE '%_%' 
       AND material_number != 'RCAB00958'`,
      {
        replacements: { semiFinId: typeMap['SEMI_FIN'] },
        type: db.sequelize.QueryTypes.UPDATE
      }
    );
    console.log('Updated sub-assemblies to SEMI_FINISHED');

    // Update all raw materials (with underscore) to Raw Material
    await db.sequelize.query(
      `UPDATE material SET material_type = 'RAW_MATERIAL', material_type_id = :rawMatId 
       WHERE material_number LIKE 'RCAB00958%' 
       AND material_number LIKE '%_%'`,
      {
        replacements: { rawMatId: typeMap['RAW_MAT'] },
        type: db.sequelize.QueryTypes.UPDATE
      }
    );
    console.log('Updated raw materials to RAW_MATERIAL');

    // Update SENSING-PLATE to Raw Material
    await db.sequelize.query(
      `UPDATE material SET material_type = 'RAW_MATERIAL', material_type_id = :rawMatId WHERE material_number = 'SENSING-PLATE'`,
      {
        replacements: { rawMatId: typeMap['RAW_MAT'] },
        type: db.sequelize.QueryTypes.UPDATE
      }
    );
    console.log('Updated SENSING-PLATE to RAW_MATERIAL');

    console.log('\nMaterial types updated successfully!');
    process.exit(0);
  } catch (error) {
    console.error('Error:', error);
    process.exit(1);
  }
})();

