'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('production_nesting_confirmation', {
      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: true },
      production_order_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'production_order', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },

      thickness: { type: Sequelize.DECIMAL(18, 3), allowNull: false },
      grade_short: { type: Sequelize.STRING(60), allowNull: false },

      production_order_number: { type: Sequelize.STRING(50), allowNull: true },
      customer_po_number: { type: Sequelize.STRING(50), allowNull: true },
      customer_name: { type: Sequelize.STRING(200), allowNull: true },
      finished_material_number: { type: Sequelize.STRING(60), allowNull: true },
      finished_material_description: { type: Sequelize.STRING(500), allowNull: true },

      planned_qty: { type: Sequelize.DECIMAL(18, 3), allowNull: false, defaultValue: 0 },
      confirmed_qty: { type: Sequelize.DECIMAL(18, 3), allowNull: false, defaultValue: 0 },

      max_length: { type: Sequelize.DECIMAL(18, 3), allowNull: false, defaultValue: 0 },
      max_width: { type: Sequelize.DECIMAL(18, 3), allowNull: false, defaultValue: 0 },

      sheet_count: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 0 },
      used_area_mm2: { type: Sequelize.DECIMAL(28, 3), allowNull: false, defaultValue: 0 },
      scrap_area_mm2: { type: Sequelize.DECIMAL(28, 3), allowNull: false, defaultValue: 0 },
      scrap_percentage: { type: Sequelize.DECIMAL(10, 3), allowNull: false, defaultValue: 0 },
      scrap_qty: { type: Sequelize.DECIMAL(18, 3), allowNull: false, defaultValue: 0 },

      nesting_file_path: { type: Sequelize.STRING(500), allowNull: true },
      nesting_file_name: { type: Sequelize.STRING(255), allowNull: true },
      nesting_file_type: { type: Sequelize.STRING(100), allowNull: true },
      nesting_file_size: { type: Sequelize.INTEGER, allowNull: true },

      created_by: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: { model: 'user', key: 'id' }
      },
      updated_by: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: { model: 'user', key: 'id' }
      },

      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_nesting_confirmation', ['company_id', 'production_order_id'], {
      name: 'idx_pnc_company_po'
    });
    await queryInterface.addIndex('production_nesting_confirmation', ['production_order_id', 'thickness', 'grade_short'], {
      name: 'idx_pnc_po_thickness_grade'
    });
    await queryInterface.addIndex('production_nesting_confirmation', ['production_order_id', 'thickness', 'grade_short'], {
      unique: true,
      name: 'uq_pnc_po_thickness_grade'
    });
  },

  down: async (queryInterface) => {
    await queryInterface.dropTable('production_nesting_confirmation');
  }
};

