'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('purchase_costing', {
      id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true, allowNull: false },
      material_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'material', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      vendor_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'business_partner', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      po_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'purchase_order', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      rate: { type: Sequelize.DECIMAL(18, 2), allowNull: false, defaultValue: 0 },
      previous_rate: { type: Sequelize.DECIMAL(18, 2), allowNull: true },
      difference: { type: Sequelize.DECIMAL(18, 2), allowNull: true },
      percentage: { type: Sequelize.DECIMAL(10, 4), allowNull: true },
      date: { type: Sequelize.DATEONLY, allowNull: false },
      created_at: { type: Sequelize.DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP') },
      updated_at: { type: Sequelize.DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP') }
    });

    await queryInterface.addIndex('purchase_costing', ['material_id', 'date'], { name: 'purchase_costing_material_date_idx' });
    await queryInterface.addIndex('purchase_costing', ['vendor_id', 'date'], { name: 'purchase_costing_vendor_date_idx' });
    await queryInterface.addIndex('purchase_costing', ['po_id'], { name: 'purchase_costing_po_idx' });
  },

  async down(queryInterface) {
    await queryInterface.removeIndex('purchase_costing', 'purchase_costing_po_idx');
    await queryInterface.removeIndex('purchase_costing', 'purchase_costing_vendor_date_idx');
    await queryInterface.removeIndex('purchase_costing', 'purchase_costing_material_date_idx');
    await queryInterface.dropTable('purchase_costing');
  }
};
