module.exports = {
  up: async (queryInterface, Sequelize) => {
    // 1) Make production_order_id nullable to support GENERAL material requests
    // MySQL requires: to change FK behavior (ON DELETE SET NULL), we must drop the existing FK first.
    // Existing FK name in this DB: material_request_ibfk_4
    try {
      await queryInterface.removeConstraint('material_request', 'material_request_ibfk_4');
    } catch (e) {
      // Ignore if constraint doesn't exist (fresh DB or different naming)
    }

    // Make the column nullable (required for ON DELETE SET NULL)
    await queryInterface.changeColumn('material_request', 'production_order_id', {
      type: Sequelize.INTEGER,
      allowNull: true,
    });

    // Re-add FK with ON DELETE SET NULL
    await queryInterface.addConstraint('material_request', {
      fields: ['production_order_id'],
      type: 'foreign key',
      name: 'material_request_production_order_id_fk',
      references: {
        table: 'production_order',
        field: 'id',
      },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
    });

    // 2) Add request_type to distinguish Production Order vs General requests
    await queryInterface.addColumn('material_request', 'request_type', {
      type: Sequelize.ENUM('PRODUCTION_ORDER', 'GENERAL'),
      allowNull: false,
      defaultValue: 'PRODUCTION_ORDER',
      comment: 'PRODUCTION_ORDER = linked to production order, GENERAL = departmental ad-hoc request (stationery, maintenance, etc.)',
    });

    await queryInterface.addIndex('material_request', ['request_type'], {
      name: 'idx_material_request_request_type',
    });
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.removeIndex('material_request', 'idx_material_request_request_type');
    await queryInterface.removeColumn('material_request', 'request_type');

    // Revert FK back to original style (no ON DELETE SET NULL)
    try {
      await queryInterface.removeConstraint('material_request', 'material_request_production_order_id_fk');
    } catch (e) {
      // ignore
    }

    // Revert to NOT NULL (best-effort; will fail if rows exist with NULL production_order_id)
    await queryInterface.changeColumn('material_request', 'production_order_id', {
      type: Sequelize.INTEGER,
      allowNull: false,
    });

    // Re-add original FK (existing before this migration)
    await queryInterface.addConstraint('material_request', {
      fields: ['production_order_id'],
      type: 'foreign key',
      name: 'material_request_ibfk_4',
      references: {
        table: 'production_order',
        field: 'id',
      },
      onUpdate: 'CASCADE',
      onDelete: 'RESTRICT',
    });

    // Drop enum type in MySQL is implicit when column removed; in Postgres it would require extra work.
  },
};


