Implement Hierarchical Multilingual Category System with Audit Fields

[hootanht]

📋 Overview

This issue outlines the implementation of a modern, scalable hierarchical category system with full multilingual support and comprehensive audit tracking to replace the current simple category structure.

🎯 Objectives

• Hierarchical Categories: Enable parent-child relationships for better content organization
• Multilingual Support: Support multiple languages with dedicated translation tables
• Audit Tracking: Implement comprehensive audit fields for change tracking
• Enhanced Media: Add cover image support for visual category representation
• Scalability: Use bigint IDs for future-proofing
• Data Integrity: Implement proper constraints and indexes for optimal performance

🏗️ Current vs. Proposed Structure

Current Implementation

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string slug { get; set; }
    public string Description { get; set; }
    public List<Book> Books { get; set; }
}

Proposed Database Schema

Core Categories Table

CREATE TABLE "Categories"
(
    "Id"          bigserial PRIMARY KEY,
    "ParentId"    bigint REFERENCES "Categories" ("Id") ON DELETE SET NULL,
    "Cover"       varchar(255) NOT NULL,
    
    -- Auditing Fields
    "CreatedAt"   timestamptz NOT NULL DEFAULT now(),
    "CreatedBy"   bigint NOT NULL,
    "UpdatedAt"   timestamptz NOT NULL DEFAULT now(),
    "UpdatedBy"   bigint NULL
);

CREATE INDEX "IdxCategoriesParentId" ON "Categories" ("ParentId");

Multilingual Translations Table

CREATE TABLE "CategoryTranslations"
(
    "Id"            bigserial PRIMARY KEY,
    "CategoryId"    bigint       NOT NULL REFERENCES "Categories" ("Id") ON DELETE CASCADE,
    "LanguageCode"  varchar(5)   NOT NULL,
    "Name"          varchar(100) NOT NULL,
    "Description"   text         NOT NULL,

    CONSTRAINT "UniqueCategoryLanguage" UNIQUE ("CategoryId", "LanguageCode")
);

CREATE INDEX "IdxCategoryTranslationsCategoryId" ON "CategoryTranslations" ("CategoryId");
CREATE INDEX "IdxCategoryTranslationsLanguageCode" ON "CategoryTranslations" ("LanguageCode");

🚀 Benefits

1. Hierarchical Organization

• Create category trees (e.g., Technology → Programming → Web Development)
• Enable breadcrumb navigation
• Support unlimited nesting levels
• Simplify category management in admin panel

2. True Multilingual Support

• Current: Mixed languages in single fields
• Proposed: Dedicated translations per language
• Seamless language switching without data loss
• Consistent with existing i18n implementation (fa-IR, en-US)

3. Comprehensive Audit Trail

• Track who created/modified categories and when
• Essential for compliance and debugging
• Enables change history and rollback capabilities

4. Enhanced User Experience

• Cover images for visual category representation
• Better SEO with proper hierarchical URLs
• Improved content discovery through category trees

5. Scalability & Performance

• bigint IDs support massive scale (9 quintillion records)
• Optimized indexes for fast hierarchy queries
• Efficient language-specific content retrieval

🔧 Implementation Tasks

Phase 1: Database Migration

• Create new Categories table with audit fields and hierarchy support
• Create CategoryTranslations table with proper constraints
• Add required indexes for performance optimization
• Create migration to transfer existing category data
• Update foreign key references in Books table

Phase 2: Entity Models & Configuration

• Update Category entity model
• Create CategoryTranslation entity model
• Update Entity Framework configurations
• Implement audit field auto-population
• Add validation attributes and constraints

Phase 3: Service Layer Updates

• Refactor ICategoryService interface for hierarchy support
• Implement CategoryService with multilingual methods
• Add hierarchy traversal methods (get children, get path, etc.)
• Implement translation management methods
• Add cover image handling methods

Phase 4: API & Controller Updates

• Update ManageCategoryController for hierarchy management
• Add translation endpoints in admin area
• Implement cover image upload/management
• Update category selection components
• Add hierarchy display in admin panel

Phase 5: View Models & DTOs

• Create hierarchical category view models
• Update existing view models for multilingual support
• Add audit information display models
• Create translation management view models
• Update dropdown and selection components

Phase 6: Frontend Integration

• Update category management UI with hierarchy support
• Implement translation management interface
• Add cover image upload components
• Update category display components
• Implement breadcrumb navigation

Phase 7: Data Migration & Testing

• Create data migration scripts for existing categories
• Migrate existing Persian names to translation table
• Add default English translations
• Create comprehensive unit tests
• Perform integration testing
• Validate performance with large datasets

📊 Technical Considerations

Migration Strategy

1. Backward Compatibility: Maintain existing functionality during migration
2. Data Preservation: Ensure no data loss during schema changes
3. Gradual Rollout: Implement feature flags for progressive deployment

Performance Optimization

• Materialized Paths: Consider for deep hierarchies
• Caching Strategy: Implement Redis caching for frequently accessed categories
• Lazy Loading: Optimize Entity Framework queries

Integration Points

• Existing i18n System: Leverage current Persian/English support
• User Management: Connect audit fields to existing ApplicationUser
• File Management: Integrate with existing image upload system

🎯 Success Criteria

• ✅ Hierarchical categories with unlimited nesting
• ✅ Full Persian/English translation support
• ✅ Complete audit trail for all changes
• ✅ Cover image support for all categories
• ✅ Backward compatibility with existing books
• ✅ Performance: < 100ms category tree loading
• ✅ Admin UI: Intuitive hierarchy management
• ✅ 100% test coverage for new functionality

🔍 Future Enhancements

• SEO Optimization: Automatic slug generation from translations
• Category Analytics: Usage statistics and popular categories
• Advanced Permissions: Role-based category management
• Category Templates: Predefined category structures
• Bulk Operations: Mass category operations and migrations

📚 Related Issues

• Internationalization System: Leverages existing multilingual infrastructure
• File Upload System: Requires cover image management integration
• Admin Panel Enhancement: Part of broader admin UI improvements
• Performance Optimization: Supports overall application scalability goals

---

Priority: High
Complexity: Medium-High
Estimated Effort: 3-4 sprints
Dependencies: Entity Framework, existing i18n system, file upload functionality