'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.addColumn('production_nesting', 'mrp_unit_weight', {
      type: Sequelize.DECIMAL(18, 3),
      allowNull: true,
      after: 'required_quantity'
    });

    await queryInterface.addColumn('production_nesting', 'mrp_total_weight', {
      type: Sequelize.DECIMAL(18, 3),
      allowNull: true,
      after: 'mrp_unit_weight'
    });

    await queryInterface.addColumn('production_nesting', 'mrp_weight_uom_code', {
      type: Sequelize.STRING(30),
      allowNull: true,
      after: 'mrp_total_weight'
    });

    // Backfill existing nesting rows from material master (same basis as MRP).
    await queryInterface.sequelize.query(`
      UPDATE production_nesting pn
      JOIN material m ON m.id = pn.material_id
      LEFT JOIN uom wu ON wu.id = m.weight_uom_id
      SET
        pn.mrp_unit_weight = COALESCE(m.net_weight, m.gross_weight),
        pn.mrp_total_weight = CASE
          WHEN COALESCE(m.net_weight, m.gross_weight) IS NULL THEN NULL
          ELSE COALESCE(m.net_weight, m.gross_weight) * COALESCE(pn.required_quantity, 0)
        END,
        pn.mrp_weight_uom_code = wu.uom_code
    `);
  },

  async down(queryInterface) {
    await queryInterface.removeColumn('production_nesting', 'mrp_weight_uom_code');
    await queryInterface.removeColumn('production_nesting', 'mrp_total_weight');
    await queryInterface.removeColumn('production_nesting', 'mrp_unit_weight');
  }
};

