'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.addColumn('goods_receipt_note_item', 'excess_quantity', {
      type: Sequelize.DECIMAL(18, 3),
      allowNull: true,
      defaultValue: 0,
      comment: 'Excess quantity beyond PO quantity for this GRN line',
      after: 'quantity'
    });

    await queryInterface.sequelize.query(`
      UPDATE goods_receipt_note_item
      SET excess_quantity = CASE
        WHEN po_item_id IS NOT NULL THEN GREATEST(0, quantity - IFNULL((
          SELECT poi.quantity
          FROM purchase_order_item poi
          WHERE poi.id = goods_receipt_note_item.po_item_id
          LIMIT 1
        ), 0))
        ELSE 0
      END
    `);
  },

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