'use strict';

/**
 * Material reversal history for Route Card → Production Storage (WIP).
 * FKs added after createTable to avoid MySQL errno 150 issues.
 */
module.exports = {
  async up(queryInterface, Sequelize) {
    const noFk = { withoutForeignKeyConstraints: true };

    await queryInterface.createTable(
      'route_card_operation_material_reversal',
      {
        id: {
          type: Sequelize.INTEGER,
          primaryKey: true,
          autoIncrement: true,
        },
        route_card_operation_id: {
          type: Sequelize.INTEGER,
          allowNull: false,
        },
        production_order_id: {
          type: Sequelize.INTEGER,
          allowNull: false,
        },
        material_id: {
          type: Sequelize.INTEGER,
          allowNull: false,
        },
        uom_id: {
          type: Sequelize.INTEGER,
          allowNull: true,
        },
        reversed_qty: {
          type: Sequelize.DECIMAL(18, 3),
          allowNull: false,
        },
        consumed_before_qty: {
          type: Sequelize.DECIMAL(18, 3),
          allowNull: false,
          defaultValue: 0,
        },
        consumed_after_qty: {
          type: Sequelize.DECIMAL(18, 3),
          allowNull: false,
          defaultValue: 0,
        },
        remarks: {
          type: Sequelize.STRING(500),
          allowNull: true,
        },
        created_by: {
          type: Sequelize.INTEGER,
          allowNull: true,
        },
        created_at: {
          type: Sequelize.DATE,
          allowNull: false,
          defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
        },
      },
      noFk
    );

    await queryInterface.addConstraint('route_card_operation_material_reversal', {
      fields: ['route_card_operation_id'],
      type: 'foreign key',
      name: 'rcomr_fk_route_card_operation_id',
      references: { table: 'route_card_operation', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'CASCADE',
    });
    await queryInterface.addConstraint('route_card_operation_material_reversal', {
      fields: ['production_order_id'],
      type: 'foreign key',
      name: 'rcomr_fk_production_order_id',
      references: { table: 'production_order', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'CASCADE',
    });
    await queryInterface.addConstraint('route_card_operation_material_reversal', {
      fields: ['material_id'],
      type: 'foreign key',
      name: 'rcomr_fk_material_id',
      references: { table: 'material', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'RESTRICT',
    });
    await queryInterface.addConstraint('route_card_operation_material_reversal', {
      fields: ['uom_id'],
      type: 'foreign key',
      name: 'rcomr_fk_uom_id',
      references: { table: 'uom', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
    });
    await queryInterface.addConstraint('route_card_operation_material_reversal', {
      fields: ['created_by'],
      type: 'foreign key',
      name: 'rcomr_fk_created_by',
      references: { table: 'user', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
    });

    await queryInterface.addIndex('route_card_operation_material_reversal', ['route_card_operation_id', 'material_id'], {
      name: 'idx_rcomr_op_material',
    });
    await queryInterface.addIndex('route_card_operation_material_reversal', ['production_order_id'], {
      name: 'idx_rcomr_po',
    });
    await queryInterface.addIndex('route_card_operation_material_reversal', ['created_at'], {
      name: 'idx_rcomr_created_at',
    });
  },

  async down(queryInterface) {
    await queryInterface.dropTable('route_card_operation_material_reversal');
  },
};

