/**
 * Product Price Model
 * Represents product prices in different price lists with effective dates
 */

module.exports = (sequelize, DataTypes) => {
  // Define ProductPrice model
  const ProductPrice = sequelize.define('ProductPrice', {
    // 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 PriceList
    price_list_id: {
      type: DataTypes.BIGINT, // Match PriceList ID type
      allowNull: false, // Price list ID is required
      references: {
        model: 'price_lists', // Reference to price_lists table
        key: 'id', // Reference to id column
      },
      onUpdate: 'CASCADE', // Cascade update on price list update
      onDelete: 'CASCADE', // Cascade delete when price list 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
      onDelete: 'CASCADE', // Cascade delete when product is deleted
    },
    // Unit price
    price: {
      type: DataTypes.DECIMAL(12, 2), // Decimal with 12 digits, 2 decimal places
      allowNull: false, // Price is required
      validate: {
        min: 0, // Price cannot be negative
      },
    },
    // Minimum quantity for this price (for quantity-based pricing)
    min_quantity: {
      type: DataTypes.DECIMAL(12, 3), // Decimal with 12 digits, 3 decimal places
      defaultValue: 1, // Default minimum quantity is 1
      allowNull: false, // Minimum quantity is required
      validate: {
        min: 0, // Minimum quantity cannot be negative
      },
    },
    // Maximum quantity for this price (null for no maximum)
    max_quantity: {
      type: DataTypes.DECIMAL(12, 3), // Decimal with 12 digits, 3 decimal places
      allowNull: true, // Maximum quantity is optional (null for no limit)
      validate: {
        min: 0, // Maximum quantity cannot be negative if provided
      },
    },
    // Effective start date
    effective_from: {
      type: DataTypes.DATE, // Start date
      allowNull: false, // Effective from date is required
      defaultValue: DataTypes.NOW, // Default to current date
    },
    // Effective end date (null for current/ongoing price)
    effective_to: {
      type: DataTypes.DATE, // End date
      allowNull: true, // Effective to date is optional (null for ongoing price)
    },
    // Whether this price is active
    active: {
      type: DataTypes.BOOLEAN, // Boolean for active status
      defaultValue: true, // Default to active
      allowNull: false, // Active status is required
    },
  }, {
    // Model options
    tableName: 'product_prices', // 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: 'updated_at', // Map updatedAt to updated_at column
    indexes: [
      // Index on price_list_id for faster lookups
      {
        fields: ['price_list_id'], // Index on price_list_id field
      },
      // Index on product_id for faster lookups
      {
        fields: ['product_id'], // Index on product_id field
      },
      // Index on effective_from for date-based queries
      {
        fields: ['effective_from'], // Index on effective_from field
      },
      // Index on effective_to for date-based queries
      {
        fields: ['effective_to'], // Index on effective_to field
      },
      // Index on active for filtering
      {
        fields: ['active'], // Index on active field
      },
    ],
  });

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

  // Return ProductPrice model
  return ProductPrice;
};

