/**
 * Category Model
 * Represents product categories (hierarchical structure)
 */

module.exports = (sequelize, DataTypes) => {
  // Define Category model
  const Category = sequelize.define('Category', {
    // Primary key
    id: {
      type: DataTypes.BIGINT, // Use BIGINT for large scale
      primaryKey: true, // Set as primary key
      autoIncrement: true, // Auto-increment ID
    },
    // Category name
    name: {
      type: DataTypes.STRING(150), // Category name with max length
      allowNull: false, // Name is required
      validate: {
        notEmpty: true, // Name cannot be empty string
        len: [1, 150], // Length validation
      },
    },
    // Category slug (URL-friendly identifier)
    slug: {
      type: DataTypes.STRING(150), // Category slug with max length
      allowNull: true, // Slug is optional
      unique: true, // Slug must be unique if provided
      validate: {
        len: [0, 150], // Maximum length validation
      },
    },
    // Foreign key to parent Category (for hierarchical categories)
    parent_id: {
      type: DataTypes.BIGINT, // Match Category ID type
      allowNull: true, // Parent ID is optional (null for root categories)
      references: {
        model: 'categories', // Self-referential to categories table
        key: 'id', // Reference to id column
      },
      onUpdate: 'CASCADE', // Cascade update on parent update
    },
    // Category description
    description: {
      type: DataTypes.TEXT, // Text field for description
      allowNull: true, // Description is optional
    },
    // Category image URL or path
    image_url: {
      type: DataTypes.STRING(255), // Image URL with max length
      allowNull: true, // Image URL is optional
      validate: {
        len: [0, 255], // Maximum length validation
      },
    },
    // Sort order for display
    sort_order: {
      type: DataTypes.INTEGER, // Integer for sort order
      defaultValue: 0, // Default sort order is 0
      allowNull: false, // Sort order is required
    },
    // Whether this category is active
    active: {
      type: DataTypes.BOOLEAN, // Boolean for active status
      defaultValue: true, // Default to active
      allowNull: false, // Active status is required
    },
  }, {
    // Model options
    tableName: 'categories', // 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 parent_id for hierarchical queries
      {
        fields: ['parent_id'], // Index on parent_id field
      },
      // Index on slug for URL lookups (unique where not null)
      {
        unique: true, // Unique index
        fields: ['slug'], // Index on slug field
        where: {
          slug: {
            [sequelize.Sequelize.Op.ne]: null, // Only index non-null slugs
          },
        },
      },
      // Index on active for filtering
      {
        fields: ['active'], // Index on active field
      },
      // Index on sort_order for ordering
      {
        fields: ['sort_order'], // Index on sort_order field
      },
    ],
  });

  // Define model associations
  Category.associate = (models) => {
    // Category has many child Categories (self-referential one-to-many)
    Category.hasMany(models.Category, {
      foreignKey: 'parent_id', // Foreign key in Categories table
      as: 'children', // Alias for child categories
    });
    
    // Category belongs to parent Category (self-referential many-to-one)
    Category.belongsTo(models.Category, {
      foreignKey: 'parent_id', // Foreign key in Category table
      as: 'parent', // Alias for parent category
    });
    
    // Category has many ProductCategories (one-to-many relationship)
    Category.hasMany(models.ProductCategory, {
      foreignKey: 'category_id', // Foreign key in ProductCategories table
      as: 'productCategories', // Alias for association
    });
  };

  // Return Category model
  return Category;
};

