/**
 * Inventory Movement Model
 * Tracks all inventory changes for audit trail and keeps dual inventory in sync
 */

module.exports = (sequelize, DataTypes) => {
  // Define InventoryMovement model
  const InventoryMovement = sequelize.define('InventoryMovement', {
    // 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 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 - null for quantity-only movements)
    inventory_item_id: {
      type: DataTypes.BIGINT, // Match InventoryItem ID type
      allowNull: true, // Inventory item ID is optional (for quantity-only movements)
      references: {
        model: 'inventory_items', // Reference to inventory_items table
        key: 'id', // Reference to id column
      },
      onUpdate: 'CASCADE', // Cascade update on inventory item update
    },
    // Quantity change (positive for increases, negative for decreases)
    quantity_change: {
      type: DataTypes.DECIMAL(12, 3), // Decimal with 12 digits, 3 decimal places
      allowNull: false, // Quantity change is required
    },
    // Reason for the inventory movement
    reason: {
      type: DataTypes.ENUM(
        'GRN',                    // Goods Receipt Note (stock received)
        'SALE',                   // Sale (stock sold)
        'SALE_RETURN',            // Sale return (stock returned)
        'PRODUCTION_CONSUME',     // Raw materials consumed in production
        'PRODUCTION_OUTPUT',      // Finished goods produced
        'ADJUSTMENT'              // Manual adjustment
      ),
      allowNull: false, // Reason is required
      validate: {
        isIn: [['GRN', 'SALE', 'SALE_RETURN', 'PRODUCTION_CONSUME', 'PRODUCTION_OUTPUT', 'ADJUSTMENT']], // Validate enum values
      },
    },
    // Reference ID to the source transaction (sale_id, grn_id, production_order_id, etc.)
    reference_id: {
      type: DataTypes.BIGINT, // Reference ID
      allowNull: true, // Reference ID is optional
    },
  }, {
    // Model options
    tableName: 'inventory_movements', // Explicit table name
    underscored: true, // Use snake_case for database columns
    timestamps: true, // Enable createdAt and updatedAt timestamps
    createdAt: 'created_at', // Map createdAt to created_at column
    updatedAt: false, // No updatedAt for movements (immutable records)
    indexes: [
      // Index on product_id for faster lookups
      {
        fields: ['product_id'], // Index on product_id field
      },
      // Index on inventory_item_id for faster lookups
      {
        fields: ['inventory_item_id'], // Index on inventory_item_id field
      },
      // Index on reason for filtering by reason
      {
        fields: ['reason'], // Index on reason field
      },
      // Index on reference_id for linking to source transactions
      {
        fields: ['reference_id'], // Index on reference_id field
      },
      // Composite index on reason and reference_id for common queries
      {
        fields: ['reason', 'reference_id'], // Composite index
      },
    ],
  });

  // Define model associations
  InventoryMovement.associate = (models) => {
    // InventoryMovement belongs to Product (many-to-one relationship)
    InventoryMovement.belongsTo(models.Product, {
      foreignKey: 'product_id', // Foreign key in InventoryMovement table
      as: 'product', // Alias for association
    });
    
    // InventoryMovement belongs to InventoryItem (many-to-one relationship, optional)
    InventoryMovement.belongsTo(models.InventoryItem, {
      foreignKey: 'inventory_item_id', // Foreign key in InventoryMovement table
      as: 'inventoryItem', // Alias for association
    });
  };

  // Return InventoryMovement model
  return InventoryMovement;
};

