'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    // Add header-level required_date (optional; item-level required_date remains authoritative per line)
    await queryInterface.addColumn('material_request', 'required_date', {
      type: Sequelize.DATEONLY,
      allowNull: true,
      after: 'request_date',
    });

    // Backfill required_date from the earliest item.required_date where possible
    // (MySQL-compatible update with correlated subquery)
    try {
      await queryInterface.sequelize.query(`
        UPDATE material_request mr
        SET mr.required_date = (
          SELECT MIN(mri.required_date)
          FROM material_request_item mri
          WHERE mri.request_id = mr.id AND mri.required_date IS NOT NULL
        )
        WHERE mr.required_date IS NULL
      `);
    } catch (e) {
      // If DB doesn't support this SQL, skip backfill gracefully.
    }
  },

  async down(queryInterface) {
    await queryInterface.removeColumn('material_request', 'required_date');
  }
};


