/**
 * Product Category Model
 * Links products to categories (many-to-many relationship)
 */

module.exports = (sequelize, DataTypes) => {
  // Define ProductCategory model
  const ProductCategory = sequelize.define('ProductCategory', {
    // 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
      onDelete: 'CASCADE', // Cascade delete when product is deleted
    },
    // Foreign key to Category
    category_id: {
      type: DataTypes.BIGINT, // Match Category ID type
      allowNull: false, // Category ID is required
      references: {
        model: 'categories', // Reference to categories table
        key: 'id', // Reference to id column
      },
      onUpdate: 'CASCADE', // Cascade update on category update
      onDelete: 'CASCADE', // Cascade delete when category is deleted
    },
    // Whether this is the primary category for the product
    is_primary: {
      type: DataTypes.BOOLEAN, // Boolean for primary category flag
      defaultValue: false, // Default to not primary
      allowNull: false, // Primary flag is required
    },
  }, {
    // Model options
    tableName: 'product_categories', // Explicit table name
    underscored: true, // Use snake_case for database columns
    timestamps: false, // No timestamps for junction table
    indexes: [
      // Composite unique index on product_id and category_id
      {
        unique: true, // Unique constraint
        fields: ['product_id', 'category_id'], // Index on both fields
      },
      // Index on product_id for faster lookups
      {
        fields: ['product_id'], // Index on product_id field
      },
      // Index on category_id for faster lookups
      {
        fields: ['category_id'], // Index on category_id field
      },
      // Index on is_primary for filtering
      {
        fields: ['is_primary'], // Index on is_primary field
      },
    ],
  });

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

  // Return ProductCategory model
  return ProductCategory;
};

