/**
 * Sale Item Model
 * Represents individual items in a sale (with inventory_item_id for UID traceability)
 */

module.exports = (sequelize, DataTypes) => {
  // Define SaleItem model
  const SaleItem = sequelize.define('SaleItem', {
    // 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 Sale
    sale_id: {
      type: DataTypes.BIGINT, // Match Sale ID type
      allowNull: false, // Sale ID is required
      references: {
        model: 'sales', // Reference to sales table
        key: 'id', // Reference to id column
      },
      onUpdate: 'CASCADE', // Cascade update on sale update
      onDelete: 'CASCADE', // Cascade delete when sale is deleted
    },
    // Foreign key to Product
    product_id: {
      type: DataTypes.BIGINT, // Match Product ID type
      allowNull: false, // Product ID is required
      references: {
        model: 'products', // Reference to products table
        key: 'id', // Reference to id column
      },
      onUpdate: 'CASCADE', // Cascade update on product update
    },
    // Foreign key to InventoryItem (optional - for UID traceability) - ENHANCEMENT
    inventory_item_id: {
      type: DataTypes.BIGINT, // Match InventoryItem ID type
      allowNull: true, // Inventory item ID is optional (for non-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 sold
    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
      },
    },
    // Unit price at time of sale
    unit_price: {
      type: DataTypes.DECIMAL(12, 2), // Decimal with 12 digits, 2 decimal places
      allowNull: false, // Unit price is required
      validate: {
        min: 0, // Unit price cannot be negative
      },
    },
    // Quantity fulfilled (sold immediately, stock deducted)
    quantity_fulfilled: {
      type: DataTypes.DECIMAL(12, 3), // Decimal with 12 digits, 3 decimal places
      allowNull: false, // Quantity fulfilled is required
      defaultValue: 0, // Default to 0
      validate: {
        min: 0, // Cannot be negative
      },
    },
    // Quantity preordered (backordered, stock not deducted yet)
    quantity_preordered: {
      type: DataTypes.DECIMAL(12, 3), // Decimal with 12 digits, 3 decimal places
      allowNull: false, // Quantity preordered is required
      defaultValue: 0, // Default to 0
      validate: {
        min: 0, // Cannot be negative
      },
    },
  }, {
    // Model options
    tableName: 'sale_items', // Explicit table name
    underscored: true, // Use snake_case for database columns
    timestamps: false, // No timestamps for sale items
    indexes: [
      // Index on sale_id for faster lookups
      {
        fields: ['sale_id'], // Index on sale_id field
      },
      // Index on product_id for faster lookups
      {
        fields: ['product_id'], // Index on product_id field
      },
      // Index on inventory_item_id for UID traceability (ENHANCEMENT)
      {
        fields: ['inventory_item_id'], // Index on inventory_item_id field
      },
    ],
  });

  // Define model associations
  SaleItem.associate = (models) => {
    // SaleItem belongs to Sale (many-to-one relationship)
    SaleItem.belongsTo(models.Sale, {
      foreignKey: 'sale_id', // Foreign key in SaleItem table
      as: 'sale', // Alias for association
    });
    
    // SaleItem belongs to Product (many-to-one relationship)
    SaleItem.belongsTo(models.Product, {
      foreignKey: 'product_id', // Foreign key in SaleItem table
      as: 'product', // Alias for association
    });
    
    // SaleItem belongs to InventoryItem (many-to-one relationship, optional) - ENHANCEMENT
    SaleItem.belongsTo(models.InventoryItem, {
      foreignKey: 'inventory_item_id', // Foreign key in SaleItem table
      as: 'inventoryItem', // Alias for association
    });
    
    // SaleItem has many ReturnItems (one-to-many relationship)
    SaleItem.hasMany(models.ReturnItem, {
      foreignKey: 'sale_item_id', // Foreign key in ReturnItems table
      as: 'returnItems', // Alias for association
    });
  };

  // Return SaleItem model
  return SaleItem;
};

