'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn('production_order', 'standard_batch_quantity', {
      type: Sequelize.DECIMAL(18, 3),
      allowNull: true
    });
    await queryInterface.addColumn('production_order', 'batch_planning_enabled', {
      type: Sequelize.BOOLEAN,
      allowNull: false,
      defaultValue: false
    });

    await queryInterface.createTable('production_order_batch', {
      id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
      production_order_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'production_order', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      batch_sequence: { type: Sequelize.INTEGER, allowNull: false },
      batch_label: { type: Sequelize.STRING(50), allowNull: true },
      quantity: { type: Sequelize.DECIMAL(18, 3), allowNull: false },
      planned_start_date: { type: Sequelize.DATEONLY, allowNull: true },
      planned_finish_date: { type: Sequelize.DATEONLY, allowNull: true },
      status: {
        type: Sequelize.ENUM('PLANNED', 'RELEASED', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED'),
        allowNull: false,
        defaultValue: 'PLANNED'
      },
      released_at: { type: Sequelize.DATE, allowNull: true },
      released_by: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: { model: 'user', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL'
      },
      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('production_order_batch', ['production_order_id'], {
      name: 'idx_production_order_batch_po_id'
    });
    await queryInterface.addIndex('production_order_batch', ['production_order_id', 'batch_sequence'], {
      name: 'idx_production_order_batch_po_seq',
      unique: true
    });
    await queryInterface.addIndex('production_order_batch', ['status'], {
      name: 'idx_production_order_batch_status'
    });
  },

  down: async (queryInterface) => {
    await queryInterface.removeIndex('production_order_batch', 'idx_production_order_batch_status');
    await queryInterface.removeIndex('production_order_batch', 'idx_production_order_batch_po_seq');
    await queryInterface.removeIndex('production_order_batch', 'idx_production_order_batch_po_id');
    await queryInterface.dropTable('production_order_batch');
    await queryInterface.removeColumn('production_order', 'batch_planning_enabled');
    await queryInterface.removeColumn('production_order', 'standard_batch_quantity');
  }
};
