'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    // Add thickness, grade, and sheet type references
    await queryInterface.addColumn('material', 'thickness_id', {
      type: Sequelize.INTEGER,
      allowNull: true,
      references: {
        model: 'material_thickness',
        key: 'id'
      },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
      comment: 'Reference to Material Thickness master',
      after: 'material_group_id'
    });

    await queryInterface.addColumn('material', 'grade_id', {
      type: Sequelize.INTEGER,
      allowNull: true,
      references: {
        model: 'material_grade',
        key: 'id'
      },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
      comment: 'Reference to Material Grade master',
      after: 'thickness_id'
    });

    await queryInterface.addColumn('material', 'sheet_type_id', {
      type: Sequelize.INTEGER,
      allowNull: true,
      references: {
        model: 'material_sheet_type',
        key: 'id'
      },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
      comment: 'Reference to Material Sheet Type master',
      after: 'grade_id'
    });

    // Add dimensions fields
    await queryInterface.addColumn('material', 'length', {
      type: Sequelize.DECIMAL(15, 3),
      allowNull: true,
      comment: 'Length in mm',
      after: 'volume_uom_id'
    });

    await queryInterface.addColumn('material', 'width', {
      type: Sequelize.DECIMAL(15, 3),
      allowNull: true,
      comment: 'Width in mm',
      after: 'length'
    });

    await queryInterface.addColumn('material', 'height', {
      type: Sequelize.DECIMAL(15, 3),
      allowNull: true,
      comment: 'Height in mm',
      after: 'width'
    });

    await queryInterface.addColumn('material', 'thickness', {
      type: Sequelize.DECIMAL(10, 3),
      allowNull: true,
      comment: 'Thickness in mm (for sheets/plates)',
      after: 'height'
    });

    await queryInterface.addColumn('material', 'diameter', {
      type: Sequelize.DECIMAL(15, 3),
      allowNull: true,
      comment: 'Diameter in mm (for round materials)',
      after: 'thickness'
    });

    await queryInterface.addColumn('material', 'dimension_unit', {
      type: Sequelize.STRING(10),
      allowNull: true,
      defaultValue: 'mm',
      comment: 'Unit for dimensions (default: mm)',
      after: 'diameter'
    });

    // Add Priority 1 fields: Lot Sizing
    await queryInterface.addColumn('material', 'minimum_order_qty', {
      type: Sequelize.DECIMAL(15, 3),
      allowNull: true,
      comment: 'Minimum order quantity',
      after: 'dimension_unit'
    });

    await queryInterface.addColumn('material', 'maximum_order_qty', {
      type: Sequelize.DECIMAL(15, 3),
      allowNull: true,
      comment: 'Maximum order quantity',
      after: 'minimum_order_qty'
    });

    await queryInterface.addColumn('material', 'rounding_value', {
      type: Sequelize.DECIMAL(15, 3),
      allowNull: true,
      comment: 'Rounding value for lot sizing',
      after: 'maximum_order_qty'
    });

    await queryInterface.addColumn('material', 'lot_size', {
      type: Sequelize.DECIMAL(15, 3),
      allowNull: true,
      comment: 'Standard lot size',
      after: 'rounding_value'
    });

    // Add Priority 1 fields: Lead Time
    await queryInterface.addColumn('material', 'planned_delivery_time', {
      type: Sequelize.INTEGER,
      allowNull: true,
      comment: 'Planned delivery time in days',
      after: 'lot_size'
    });

    await queryInterface.addColumn('material', 'gr_processing_time', {
      type: Sequelize.INTEGER,
      allowNull: true,
      comment: 'Goods receipt processing time in days',
      after: 'planned_delivery_time'
    });

    await queryInterface.addColumn('material', 'in_house_production_time', {
      type: Sequelize.INTEGER,
      allowNull: true,
      comment: 'In-house production time in days',
      after: 'gr_processing_time'
    });

    // Add Priority 1 fields: Make/Buy & Procurement
    await queryInterface.addColumn('material', 'procurement_type', {
      type: Sequelize.ENUM('E', 'F', 'X'),
      allowNull: true,
      comment: 'E=External (Buy), F=In-house (Make), X=Both',
      after: 'in_house_production_time'
    });

    await queryInterface.addColumn('material', 'make_buy_indicator', {
      type: Sequelize.ENUM('MAKE', 'BUY', 'BOTH'),
      allowNull: true,
      comment: 'Make or Buy indicator',
      after: 'procurement_type'
    });

    await queryInterface.addColumn('material', 'special_procurement_type', {
      type: Sequelize.STRING(20),
      allowNull: true,
      comment: 'Special procurement type code',
      after: 'make_buy_indicator'
    });

    // Add ABC classification
    await queryInterface.addColumn('material', 'abc_indicator', {
      type: Sequelize.ENUM('A', 'B', 'C', 'X'),
      allowNull: true,
      comment: 'ABC classification for inventory control',
      after: 'special_procurement_type'
    });

    // Add manufacturer details
    await queryInterface.addColumn('material', 'manufacturer_part_number', {
      type: Sequelize.STRING(100),
      allowNull: true,
      comment: 'Manufacturer part number',
      after: 'abc_indicator'
    });

    await queryInterface.addColumn('material', 'manufacturer_name', {
      type: Sequelize.STRING(200),
      allowNull: true,
      comment: 'Manufacturer name',
      after: 'manufacturer_part_number'
    });

    // Add quality management
    await queryInterface.addColumn('material', 'inspection_required', {
      type: Sequelize.BOOLEAN,
      allowNull: false,
      defaultValue: false,
      comment: 'Quality inspection required',
      after: 'manufacturer_name'
    });

    await queryInterface.addColumn('material', 'inspection_type', {
      type: Sequelize.ENUM('NONE', 'OPTIONAL', 'MANDATORY'),
      allowNull: false,
      defaultValue: 'NONE',
      comment: 'Type of inspection',
      after: 'inspection_required'
    });

    // Add indexes for new fields
    await queryInterface.addIndex('material', ['thickness_id'], {
      name: 'idx_material_thickness'
    });

    await queryInterface.addIndex('material', ['grade_id'], {
      name: 'idx_material_grade'
    });

    await queryInterface.addIndex('material', ['sheet_type_id'], {
      name: 'idx_material_sheet_type'
    });

    await queryInterface.addIndex('material', ['procurement_type'], {
      name: 'idx_material_procurement'
    });

    await queryInterface.addIndex('material', ['abc_indicator'], {
      name: 'idx_material_abc'
    });

    await queryInterface.addIndex('material', ['manufacturer_part_number'], {
      name: 'idx_material_mfr_part'
    });
  },

  down: async (queryInterface, Sequelize) => {
    // Remove indexes
    await queryInterface.removeIndex('material', 'idx_material_thickness');
    await queryInterface.removeIndex('material', 'idx_material_grade');
    await queryInterface.removeIndex('material', 'idx_material_sheet_type');
    await queryInterface.removeIndex('material', 'idx_material_procurement');
    await queryInterface.removeIndex('material', 'idx_material_abc');
    await queryInterface.removeIndex('material', 'idx_material_mfr_part');

    // Remove columns
    await queryInterface.removeColumn('material', 'inspection_type');
    await queryInterface.removeColumn('material', 'inspection_required');
    await queryInterface.removeColumn('material', 'manufacturer_name');
    await queryInterface.removeColumn('material', 'manufacturer_part_number');
    await queryInterface.removeColumn('material', 'abc_indicator');
    await queryInterface.removeColumn('material', 'special_procurement_type');
    await queryInterface.removeColumn('material', 'make_buy_indicator');
    await queryInterface.removeColumn('material', 'procurement_type');
    await queryInterface.removeColumn('material', 'in_house_production_time');
    await queryInterface.removeColumn('material', 'gr_processing_time');
    await queryInterface.removeColumn('material', 'planned_delivery_time');
    await queryInterface.removeColumn('material', 'lot_size');
    await queryInterface.removeColumn('material', 'rounding_value');
    await queryInterface.removeColumn('material', 'maximum_order_qty');
    await queryInterface.removeColumn('material', 'minimum_order_qty');
    await queryInterface.removeColumn('material', 'dimension_unit');
    await queryInterface.removeColumn('material', 'diameter');
    await queryInterface.removeColumn('material', 'thickness');
    await queryInterface.removeColumn('material', 'height');
    await queryInterface.removeColumn('material', 'width');
    await queryInterface.removeColumn('material', 'length');
    await queryInterface.removeColumn('material', 'sheet_type_id');
    await queryInterface.removeColumn('material', 'grade_id');
    await queryInterface.removeColumn('material', 'thickness_id');
  }
};



