/**
 * Return Item Model
 * Represents individual items in a return (with UID traceability)
 * ENHANCEMENT: New table for proper return item tracking
 */

module.exports = (sequelize, DataTypes) => {
  // Define ReturnItem model
  const ReturnItem = sequelize.define('ReturnItem', {
    // Primary key
    id: {
      type: DataTypes.BIGINT, // Use BIGINT for large scale
      primaryKey: true, // Set as primary key
      autoIncrement: true, // Auto-increment ID
    },
    // Foreign key to Return
    return_id: {
      type: DataTypes.BIGINT, // Match Return ID type
      allowNull: false, // Return ID is required
      references: {
        model: 'returns', // Reference to returns table
        key: 'id', // Reference to id column
      },
      onUpdate: 'CASCADE', // Cascade update on return update
      onDelete: 'CASCADE', // Cascade delete when return is deleted
    },
    // Foreign key to SaleItem (original sale item being returned)
    sale_item_id: {
      type: DataTypes.BIGINT, // Match SaleItem ID type
      allowNull: false, // Sale item ID is required
      references: {
        model: 'sale_items', // Reference to sale_items table
        key: 'id', // Reference to id column
      },
      onUpdate: 'CASCADE', // Cascade update on sale item update
    },
    // Foreign key to InventoryItem (UID of returned item) - optional for quantity-based returns
    inventory_item_id: {
      type: DataTypes.BIGINT, // Match InventoryItem ID type
      allowNull: true, // Inventory item ID is optional (only required for UID-tracked items)
      references: {
        model: 'inventory_items', // Reference to inventory_items table
        key: 'id', // Reference to id column
      },
      onUpdate: 'CASCADE', // Cascade update on inventory item update
    },
    // Quantity returned
    quantity: {
      type: DataTypes.DECIMAL(12, 3), // Decimal with 12 digits, 3 decimal places
      allowNull: false, // Quantity is required
      validate: {
        min: 0, // Quantity cannot be negative
      },
    },
    // Reason for return
    reason: {
      type: DataTypes.STRING(255), // Return reason with max length
      allowNull: true, // Return reason is optional
      validate: {
        len: [0, 255], // Maximum length validation
      },
    },
  }, {
    // Model options
    tableName: 'return_items', // Explicit table name
    underscored: true, // Use snake_case for database columns
    timestamps: false, // No timestamps for return items
    indexes: [
      // Index on return_id for faster lookups
      {
        fields: ['return_id'], // Index on return_id field
      },
      // Index on sale_item_id for faster lookups
      {
        fields: ['sale_item_id'], // Index on sale_item_id field
      },
      // Index on inventory_item_id for UID traceability
      {
        fields: ['inventory_item_id'], // Index on inventory_item_id field
      },
    ],
  });

  // Define model associations
  ReturnItem.associate = (models) => {
    // ReturnItem belongs to Return (many-to-one relationship)
    ReturnItem.belongsTo(models.Return, {
      foreignKey: 'return_id', // Foreign key in ReturnItem table
      as: 'return', // Alias for association
    });
    
    // ReturnItem belongs to SaleItem (many-to-one relationship)
    ReturnItem.belongsTo(models.SaleItem, {
      foreignKey: 'sale_item_id', // Foreign key in ReturnItem table
      as: 'saleItem', // Alias for association
    });
    
    // ReturnItem belongs to InventoryItem (many-to-one relationship)
    ReturnItem.belongsTo(models.InventoryItem, {
      foreignKey: 'inventory_item_id', // Foreign key in ReturnItem table
      as: 'inventoryItem', // Alias for association
    });
  };

  // Return ReturnItem model
  return ReturnItem;
};

