'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    // Get all storage locations with their IDs and codes
    const [storageLocations] = await queryInterface.sequelize.query(`
      SELECT id, storage_location_code
      FROM storage_location
      WHERE status = 'ACTIVE'
    `);

    // Create a map of ID to code
    const idToCodeMap = new Map();
    storageLocations.forEach((sl) => {
      const code = sl.storage_location_code;
      if (code) {
        idToCodeMap.set(String(sl.id), code);
      }
    });

    // Get all material_plant records with storage_location that might be an ID
    const [materialPlants] = await queryInterface.sequelize.query(`
      SELECT id, storage_location
      FROM material_plant
      WHERE storage_location IS NOT NULL
        AND storage_location != ''
        AND storage_location REGEXP '^[0-9]+$'
    `);

    // Update each record to use code instead of ID
    for (const mp of materialPlants) {
      const storageLocationId = String(mp.storage_location);
      const code = idToCodeMap.get(storageLocationId);
      
      if (code) {
        await queryInterface.sequelize.query(`
          UPDATE material_plant
          SET storage_location = :code
          WHERE id = :id
        `, {
          replacements: { code, id: mp.id }
        });
      } else {
        // If ID not found in storage_location table, set to NULL
        console.warn(`Storage location ID ${storageLocationId} not found in storage_location table for material_plant id ${mp.id}`);
        await queryInterface.sequelize.query(`
          UPDATE material_plant
          SET storage_location = NULL
          WHERE id = :id
        `, {
          replacements: { id: mp.id }
        });
      }
    }

    console.log(`Migrated ${materialPlants.length} material_plant storage_location records from ID to code`);
  },

  async down(queryInterface, Sequelize) {
    // Revert: Convert codes back to IDs (if needed)
    // Note: This is a one-way migration in practice, but we provide a down method
    const [storageLocations] = await queryInterface.sequelize.query(`
      SELECT id, storage_location_code
      FROM storage_location
      WHERE status = 'ACTIVE'
    `);

    // Create a map of code to ID
    const codeToIdMap = new Map();
    storageLocations.forEach((sl) => {
      const code = sl.storage_location_code;
      if (code) {
        codeToIdMap.set(code, String(sl.id));
      }
    });

    // Get all material_plant records with storage_location codes
    const [materialPlants] = await queryInterface.sequelize.query(`
      SELECT id, storage_location
      FROM material_plant
      WHERE storage_location IS NOT NULL
        AND storage_location != ''
        AND storage_location NOT REGEXP '^[0-9]+$'
    `);

    // Update each record to use ID instead of code
    for (const mp of materialPlants) {
      const code = mp.storage_location;
      const id = codeToIdMap.get(code);
      
      if (id) {
        await queryInterface.sequelize.query(`
          UPDATE material_plant
          SET storage_location = :id
          WHERE id = :mpId
        `, {
          replacements: { id, mpId: mp.id }
        });
      }
    }

    console.log(`Reverted ${materialPlants.length} material_plant storage_location records from code to ID`);
  }
};

