'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('in_progress_material_request', {
      id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
      company_id: { type: Sequelize.INTEGER, allowNull: false },
      plant_id: { type: Sequelize.INTEGER, allowNull: false },
      fiscal_year_id: { type: Sequelize.INTEGER, allowNull: false },
      production_order_id: { type: Sequelize.INTEGER, allowNull: false },
      customer_po_id: { type: Sequelize.INTEGER, allowNull: true },
      ipmr_number: { type: Sequelize.STRING(50), allowNull: false, unique: true },
      reference_production_no: { type: Sequelize.STRING(80), allowNull: true },
      item_code: { type: Sequelize.STRING(100), allowNull: true },
      item_name: { type: Sequelize.STRING(255), allowNull: true },
      customer_name: { type: Sequelize.STRING(255), allowNull: true },
      order_qty: { type: Sequelize.DECIMAL(18, 3), allowNull: true, defaultValue: 0 },
      required_date: { type: Sequelize.DATEONLY, allowNull: true },
      material_request_id: { type: Sequelize.INTEGER, allowNull: true },
      material_request_number: { type: Sequelize.STRING(50), allowNull: true },
      status: {
        type: Sequelize.ENUM('DRAFT', 'SENT_TO_MR', 'PARTIAL', 'COMPLETED', 'CANCELLED'),
        allowNull: false,
        defaultValue: 'SENT_TO_MR'
      },
      remarks: { type: Sequelize.TEXT, allowNull: true },
      created_by: { type: Sequelize.INTEGER, allowNull: true },
      updated_by: { type: Sequelize.INTEGER, allowNull: true },
      created_at: { type: Sequelize.DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP') },
      updated_at: { type: Sequelize.DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP') },
    });

    await queryInterface.createTable('in_progress_material_request_item', {
      id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
      in_progress_request_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'in_progress_material_request', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE',
      },
      line_number: { type: Sequelize.INTEGER, allowNull: false },
      material_id: { type: Sequelize.INTEGER, allowNull: false },
      material_number: { type: Sequelize.STRING(100), allowNull: true },
      description: { type: Sequelize.STRING(500), allowNull: true },
      uom_code: { type: Sequelize.STRING(20), allowNull: true },
      store_code: { type: Sequelize.STRING(20), allowNull: true },
      required_quantity: { type: Sequelize.DECIMAL(18, 3), allowNull: false, defaultValue: 0 },
      available_quantity: { type: Sequelize.DECIMAL(18, 3), allowNull: false, defaultValue: 0 },
      shortage_quantity: { type: Sequelize.DECIMAL(18, 3), allowNull: false, defaultValue: 0 },
      status: {
        type: Sequelize.ENUM('OPEN', 'SENT_TO_MR', 'PARTIAL', 'COMPLETED', 'CANCELLED'),
        allowNull: false,
        defaultValue: 'SENT_TO_MR'
      },
      remarks: { type: Sequelize.TEXT, allowNull: true },
      created_at: { type: Sequelize.DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP') },
      updated_at: { type: Sequelize.DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP') },
    });

    await queryInterface.addIndex('in_progress_material_request', ['company_id'], { name: 'idx_ipmr_company_id' });
    await queryInterface.addIndex('in_progress_material_request', ['plant_id'], { name: 'idx_ipmr_plant_id' });
    await queryInterface.addIndex('in_progress_material_request', ['production_order_id'], { name: 'idx_ipmr_production_order_id' });
    await queryInterface.addIndex('in_progress_material_request_item', ['in_progress_request_id'], { name: 'idx_ipmr_item_request_id' });
    await queryInterface.addIndex('in_progress_material_request_item', ['material_id'], { name: 'idx_ipmr_item_material_id' });
  },

  down: async (queryInterface) => {
    await queryInterface.removeIndex('in_progress_material_request_item', 'idx_ipmr_item_material_id');
    await queryInterface.removeIndex('in_progress_material_request_item', 'idx_ipmr_item_request_id');
    await queryInterface.removeIndex('in_progress_material_request', 'idx_ipmr_production_order_id');
    await queryInterface.removeIndex('in_progress_material_request', 'idx_ipmr_plant_id');
    await queryInterface.removeIndex('in_progress_material_request', 'idx_ipmr_company_id');
    await queryInterface.dropTable('in_progress_material_request_item');
    await queryInterface.dropTable('in_progress_material_request');
  }
};

