module.exports = {
  up: async (queryInterface, Sequelize) => {
    // For GENERAL Material Requests, Material Issues may not have a production_order_id.
    // Make material_issue.production_order_id nullable and adjust FK to ON DELETE SET NULL.

    // Find existing FK constraint name dynamically (MySQL)
    const [rows] = await queryInterface.sequelize.query(`
      SELECT CONSTRAINT_NAME
      FROM information_schema.KEY_COLUMN_USAGE
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'material_issue'
        AND COLUMN_NAME = 'production_order_id'
        AND REFERENCED_TABLE_NAME = 'production_order'
        AND REFERENCED_COLUMN_NAME = 'id'
      LIMIT 1
    `);

    const existingFkName = rows?.[0]?.CONSTRAINT_NAME;
    if (existingFkName) {
      try {
        await queryInterface.removeConstraint('material_issue', existingFkName);
      } catch (e) {
        // ignore
      }
    }

    await queryInterface.changeColumn('material_issue', 'production_order_id', {
      type: Sequelize.INTEGER,
      allowNull: true,
    });

    await queryInterface.addConstraint('material_issue', {
      fields: ['production_order_id'],
      type: 'foreign key',
      name: 'material_issue_production_order_id_fk',
      references: {
        table: 'production_order',
        field: 'id',
      },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
    });
  },

  down: async (queryInterface, Sequelize) => {
    try {
      await queryInterface.removeConstraint('material_issue', 'material_issue_production_order_id_fk');
    } catch (e) {
      // ignore
    }

    // Best-effort: restore NOT NULL + original FK (name may differ in some DBs)
    await queryInterface.changeColumn('material_issue', 'production_order_id', {
      type: Sequelize.INTEGER,
      allowNull: false,
    });

    await queryInterface.addConstraint('material_issue', {
      fields: ['production_order_id'],
      type: 'foreign key',
      name: 'material_issue_ibfk_1',
      references: {
        table: 'production_order',
        field: 'id',
      },
      onUpdate: 'CASCADE',
      onDelete: 'RESTRICT',
    });
  },
};


