'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.addColumn('gate_entry_item', 'excess_quantity', {
      type: Sequelize.DECIMAL(18, 3),
      allowNull: true,
      defaultValue: 0,
      comment: 'Excess quantity beyond PO quantity, if any',
      after: 'received_quantity'
    });

    await queryInterface.sequelize.query(`
      UPDATE gate_entry_item
      SET excess_quantity = CASE
        WHEN po_quantity IS NOT NULL AND po_quantity > 0 AND received_quantity > po_quantity
          THEN ROUND(received_quantity - po_quantity, 3)
        ELSE 0
      END
    `);
  },

  async down(queryInterface) {
    await queryInterface.removeColumn('gate_entry_item', 'excess_quantity');
  }
};
