'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    // Check existing columns for ProductionOrderComponent
    const pocDescription = await queryInterface.describeTable('production_order_component');
    
    // Add substitution fields to ProductionOrderComponent
    if (!pocDescription.is_substituted) {
      await queryInterface.addColumn('production_order_component', 'is_substituted', {
        type: Sequelize.BOOLEAN,
        allowNull: false,
        defaultValue: false,
        comment: 'Whether this component has been substituted'
      });
    }
    
    if (!pocDescription.original_material_id) {
      await queryInterface.addColumn('production_order_component', 'original_material_id', {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: {
          model: 'material',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL',
        comment: 'Original material from BOM (if substituted)'
      });
    }
    
    if (!pocDescription.substitution_transaction_id) {
      await queryInterface.addColumn('production_order_component', 'substitution_transaction_id', {
        type: Sequelize.INTEGER,
        allowNull: true,
        comment: 'Reference to substitution transaction'
      });

      // Add foreign key separately with shorter constraint name
      try {
        await queryInterface.addConstraint('production_order_component', {
          fields: ['substitution_transaction_id'],
          type: 'foreign key',
          name: 'poc_substitution_txn_fk',
          references: {
            table: 'material_substitution_transaction',
            field: 'id'
          },
          onUpdate: 'CASCADE',
          onDelete: 'SET NULL'
        });
      } catch (error) {
        // Constraint may already exist, ignore error
        if (!error.message.includes('Duplicate key name')) {
          throw error;
        }
      }
    }

    // Add substitution fields to MaterialRequestItem
    const materialRequestItemDescription = await queryInterface.describeTable('material_request_item');
    if (!materialRequestItemDescription.is_substituted) {
      await queryInterface.addColumn('material_request_item', 'is_substituted', {
        type: Sequelize.BOOLEAN,
        allowNull: false,
        defaultValue: false,
        comment: 'Whether this item has been substituted'
      });
    }
    if (!materialRequestItemDescription.original_material_id) {
      await queryInterface.addColumn('material_request_item', 'original_material_id', {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: {
          model: 'material',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL',
        comment: 'Original material (if substituted)'
      });
    }
    if (!materialRequestItemDescription.substitution_transaction_id) {
      await queryInterface.addColumn('material_request_item', 'substitution_transaction_id', {
        type: Sequelize.INTEGER,
        allowNull: true,
        comment: 'Reference to substitution transaction'
      });

      // Add foreign key separately with shorter constraint name
      await queryInterface.addConstraint('material_request_item', {
        fields: ['substitution_transaction_id'],
        type: 'foreign key',
        name: 'mri_substitution_txn_fk',
        references: {
          table: 'material_substitution_transaction',
          field: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL'
      });
    }
  },

  down: async (queryInterface, Sequelize) => {
    // Remove foreign key constraints first
    try {
      await queryInterface.removeConstraint('production_order_component', 'poc_substitution_txn_fk');
    } catch (error) {
      // Constraint may not exist, ignore error
    }
    
    try {
      await queryInterface.removeConstraint('material_request_item', 'mri_substitution_txn_fk');
    } catch (error) {
      // Constraint may not exist, ignore error
    }

    // Remove columns
    await queryInterface.removeColumn('production_order_component', 'is_substituted');
    await queryInterface.removeColumn('production_order_component', 'original_material_id');
    await queryInterface.removeColumn('production_order_component', 'substitution_transaction_id');
    
    try {
      await queryInterface.removeColumn('material_request_item', 'is_substituted');
      await queryInterface.removeColumn('material_request_item', 'original_material_id');
      await queryInterface.removeColumn('material_request_item', 'substitution_transaction_id');
    } catch (error) {
      // Columns may not exist, ignore error
    }
  }
};

