'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    // Add scrap_reason_id to scrap_reporting table
    await queryInterface.addColumn('scrap_reporting', 'scrap_reason_id', {
      type: Sequelize.INTEGER,
      allowNull: true,
      references: {
        model: 'scrap_reason_master',
        key: 'id'
      },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
      comment: 'Link to Scrap Reason Master'
    });

    // Add lot_number for lot tracking
    await queryInterface.addColumn('scrap_reporting', 'lot_number', {
      type: Sequelize.STRING(50),
      allowNull: true,
      comment: 'Lot number for lot tracking'
    });

    // Add storage_location if not exists
    const tableDescription = await queryInterface.describeTable('scrap_reporting');
    if (!tableDescription.storage_location) {
      await queryInterface.addColumn('scrap_reporting', 'storage_location', {
        type: Sequelize.STRING(10),
        allowNull: true
      });
    }

    // Add approval fields if not exists
    if (!tableDescription.approved_by) {
      await queryInterface.addColumn('scrap_reporting', 'approved_by', {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: {
          model: 'user',
          key: 'id'
        }
      });
    }

    if (!tableDescription.approved_at) {
      await queryInterface.addColumn('scrap_reporting', 'approved_at', {
        type: Sequelize.DATE,
        allowNull: true
      });
    }

    await queryInterface.addIndex('scrap_reporting', ['scrap_reason_id']);
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.removeIndex('scrap_reporting', ['scrap_reason_id']);
    
    const tableDescription = await queryInterface.describeTable('scrap_reporting');
    
    if (tableDescription.approved_at) {
      await queryInterface.removeColumn('scrap_reporting', 'approved_at');
    }
    if (tableDescription.approved_by) {
      await queryInterface.removeColumn('scrap_reporting', 'approved_by');
    }
    if (tableDescription.storage_location) {
      await queryInterface.removeColumn('scrap_reporting', 'storage_location');
    }
    if (tableDescription.lot_number) {
      await queryInterface.removeColumn('scrap_reporting', 'lot_number');
    }
    await queryInterface.removeColumn('scrap_reporting', 'scrap_reason_id');
  }
};

