module.exports = {
  up: async (queryInterface, Sequelize) => {
    // 1) Allow returns not linked to production orders
    // Drop FK if exists, change column nullable, re-add with ON DELETE SET NULL
    const [rows] = await queryInterface.sequelize.query(`
      SELECT CONSTRAINT_NAME
      FROM information_schema.KEY_COLUMN_USAGE
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'material_return_note'
        AND COLUMN_NAME = 'production_order_id'
        AND REFERENCED_TABLE_NAME = 'production_order'
      LIMIT 1
    `);
    const existingFkName = rows?.[0]?.CONSTRAINT_NAME;
    if (existingFkName) {
      try {
        await queryInterface.removeConstraint('material_return_note', existingFkName);
      } catch (e) {
        // ignore
      }
    }

    await queryInterface.changeColumn('material_return_note', 'production_order_id', {
      type: Sequelize.INTEGER,
      allowNull: true,
    });

    await queryInterface.addConstraint('material_return_note', {
      fields: ['production_order_id'],
      type: 'foreign key',
      name: 'material_return_note_production_order_id_fk',
      references: { table: 'production_order', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
    });

    // 2) Add department_id to capture which department returned materials
    await queryInterface.addColumn('material_return_note', 'department_id', {
      type: Sequelize.INTEGER,
      allowNull: true,
      references: { model: 'department', key: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
      comment: 'Department that returned the materials to stores',
    });
    await queryInterface.addIndex('material_return_note', ['department_id'], {
      name: 'idx_material_return_note_department_id',
    });

    // 3) Add source/destination storage locations per item
    await queryInterface.addColumn('material_return_note_item', 'source_storage_location', {
      type: Sequelize.STRING(10),
      allowNull: true,
      comment: 'Source (department) storage location code where stock will be reduced',
    });
    await queryInterface.addColumn('material_return_note_item', 'destination_storage_location', {
      type: Sequelize.STRING(10),
      allowNull: true,
      comment: 'Destination (stores) storage location code where stock will be increased',
    });
    await queryInterface.addIndex('material_return_note_item', ['source_storage_location'], {
      name: 'idx_mrn_item_source_storage_location',
    });
    await queryInterface.addIndex('material_return_note_item', ['destination_storage_location'], {
      name: 'idx_mrn_item_destination_storage_location',
    });
  },

  down: async (queryInterface, Sequelize) => {
    try {
      await queryInterface.removeIndex('material_return_note_item', 'idx_mrn_item_source_storage_location');
    } catch (e) {}
    try {
      await queryInterface.removeIndex('material_return_note_item', 'idx_mrn_item_destination_storage_location');
    } catch (e) {}
    try {
      await queryInterface.removeColumn('material_return_note_item', 'source_storage_location');
    } catch (e) {}
    try {
      await queryInterface.removeColumn('material_return_note_item', 'destination_storage_location');
    } catch (e) {}

    try {
      await queryInterface.removeIndex('material_return_note', 'idx_material_return_note_department_id');
    } catch (e) {}
    try {
      await queryInterface.removeColumn('material_return_note', 'department_id');
    } catch (e) {}

    try {
      await queryInterface.removeConstraint('material_return_note', 'material_return_note_production_order_id_fk');
    } catch (e) {}
    await queryInterface.changeColumn('material_return_note', 'production_order_id', {
      type: Sequelize.INTEGER,
      allowNull: false,
    });
  },
};


