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

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

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

    try {
      // Step 1: Delete all BOM components first (they reference materials and BOM headers)
      console.log('Deleting BOM components...');
      const [bomComponentsDeleted] = await db.sequelize.query(
        `DELETE FROM bom_component`,
        { transaction }
      );
      console.log(`Deleted BOM components`);

      // Step 2: Delete all BOM headers
      console.log('Deleting BOM headers...');
      const [bomHeadersDeleted] = await db.sequelize.query(
        `DELETE FROM bom_header`,
        { transaction }
      );
      console.log(`Deleted BOM headers`);

      // Step 3: Delete all materials (check for any other foreign key references first)
      console.log('Deleting materials...');
      
      // Get list of materials to delete
      const materialNumbers = [
        'CABINET-001', 'RCAB00958', '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'
      ];

      let deletedCount = 0;
      for (const matNumber of materialNumbers) {
        try {
          const [result] = await db.sequelize.query(
            `DELETE FROM material WHERE material_number = :matNumber`,
            {
              replacements: { matNumber },
              transaction
            }
          );
          if (result.affectedRows > 0) {
            deletedCount++;
            console.log(`  Deleted material: ${matNumber}`);
          }
        } catch (err) {
          console.log(`  Could not delete ${matNumber}: ${err.message}`);
        }
      }

      console.log(`\nDeleted ${deletedCount} materials`);

      // Commit transaction
      await transaction.commit();
      console.log('\n✅ All materials and BOM data cleaned up successfully!');
      process.exit(0);
    } catch (error) {
      await transaction.rollback();
      throw error;
    }
  } catch (error) {
    console.error('❌ Error:', error.message);
    process.exit(1);
  }
})();

