Comments

Akademi Eğitim Platformu Comments Dökümanı

// Yorumlar & Puanlama Ana Tablosu
Table "reviews" {
  "reviewId" bigint [pk, not null, increment]
  "userId" int [not null]
  "academyId" bigint []
  
  // İçerik tipi ve ID'si (polymorphic ilişki)
  "targetType" enum_review_target_type [not null, note: 'academy, course, module, instructor']
  "targetId" bigint [not null, note: 'ilgili tablonun IDsi']
  
  // Puanlama
  "rating" decimal(2,1) [not null, note: '1.0 - 5.0 arası']
  "criteria_ratings" json [note: 'Kriter bazlı puanlamalar: {
    "content": 4.5,
    "instructor": 5.0,
    "difficulty": 3.5,
    "duration": 4.0
  }']
  
  // Yorum içeriği
  "title" varchar(255)
  "content" text [note: 'Yorum metni']
  "pros" text [note: 'Artılar']
  "cons" text [note: 'Eksiler']
  
  // Medya ekleri
  "attachments" json [note: 'Fotoğraf/video ekleri']
  
  // Durum ve onay
  "status" enum_review_status [default: 'pending', note: 'pending, approved, rejected, spam']
  "moderation_note" text [note: 'Reddedilme nedeni']
  "moderatedBy" int []
  "moderatedAt" datetime
  
  // Yardımseverlik
  "helpful_count" int [default: 0]
  "unhelpful_count" int [default: 0]
  "reported_count" int [default: 0]
  
  // Yanıtlar
  "reply" text [note: 'Admin/Eğitmen yanıtı']
  "repliedBy" int []
  "repliedAt" datetime
  
  // Zaman damgaları
  "createdAt" datetime [default: `now()`]
  "updatedAt" datetime
  
  indexes {
    (targetType, targetId) [name: 'idx_reviews_target']
    (userId) [name: 'idx_reviews_user']
    (status) [name: 'idx_reviews_status']
    (rating) [name: 'idx_reviews_rating']
    (createdAt) [name: 'idx_reviews_created']
  }
}

// Kullanıcıların yorumları değerlendirmesi (beğenme/şikayet)
Table "review_feedback" {
  "feedbackId" bigint [pk, not null, increment]
  "reviewId" bigint [not null, ref: > "reviews"."reviewId"]
  "userId" int [not null]
  "feedbackType" enum_feedback_type [not null, note: 'helpful, unhelpful, report']
  "reportReason" enum_report_reason [note: 'spam, offensive, irrelevant']
  "createdAt" datetime [default: `now()`]
  
  indexes {
    (reviewId) [name: 'idx_feedback_review']
    (userId) [name: 'idx_feedback_user']
  }
  
  // Bir kullanıcı bir yorum için sadece bir feedback verebilir
  indexes {
    (reviewId, userId) [unique, name: 'uq_review_user_feedback']
  }
}

// Yorum şablonları (hızlı geri bildirim için)
Table "review_templates" {
  "templateId" int [pk, not null, increment]
  "academyId" bigint []
  "targetType" enum_review_target_type [not null]
  "name" varchar(255) [not null]
  "questions" json [not null, note: 'Soru listesi: [
    {
      "id": "q1",
      "type": "rating",
      "question": "İçerik kalitesi",
      "required": true
    },
    {
      "id": "q2",
      "type": "text",
      "question": "Yorumunuz",
      "required": true
    }
  ]']
  "isDefault" boolean [default: false]
  "isActive" boolean [default: true]
  "createdAt" datetime [default: `now()`]
  "createdBy" int []
  
  indexes {
    (academyId) [name: 'idx_templates_academy']
    (targetType) [name: 'idx_templates_target']
  }
}

// Puan istatistikleri (özet tablo)
Table "rating_stats" {
  "statId" bigint [pk, not null, increment]
  "targetType" enum_review_target_type [not null]
  "targetId" bigint [not null]
  "average_rating" decimal(2,1) [default: 0.0]
  "total_reviews" int [default: 0]
  "rating_distribution" json [note: '{
    "5": 124,
    "4": 45,
    "3": 12,
    "2": 3,
    "1": 1
  }']
  "criteria_averages" json [note: '{
    "content": 4.5,
    "instructor": 4.8,
    "difficulty": 3.2
  }']
  "lastUpdated" datetime [default: `now()`]
  
  indexes {
    (targetType, targetId) [unique, name: 'uq_stats_target']
  }
}

// Şikayet yönetimi
Table "reported_content" {
  "reportId" bigint [pk, not null, increment]
  "reviewId" bigint [ref: > "reviews"."reviewId"]
  "reportedBy" int [not null]
  "reportType" enum_report_reason [not null]
  "description" text
  "status" enum_report_status [default: 'pending', note: 'pending, reviewed, dismissed']
  "actionTaken" enum_report_action [note: 'none, warning, removed, banned']
  "reviewedBy" int []
  "reviewedAt" datetime
  "createdAt" datetime [default: `now()`]
  
  indexes {
    (status) [name: 'idx_reports_status']
    (reviewId) [name: 'idx_reports_review']
  }
}

// Enum Tanımları
Enum "enum_review_target_type" {
  "academy","course","module","instructor","learning_path"
}

Enum "enum_review_status" {
  "pending","approved",  "rejected",  "spam",  "archived"
}

Enum "enum_feedback_type" {
  "helpful"
  "unhelpful"
  "report"
}

Enum "enum_report_reason" {
  "spam"
  "offensive"
  "irrelevant"
  "misinformation"
  "copyright"
  "other"
}

Enum "enum_report_status" {
  "pending"
  "reviewed"
  "dismissed"
}

Enum "enum_report_action" {
  "none"
  "warning"
  "removed"
  "user_banned"
}

// View: Aktif yorumlar (onaylanmış)
View "active_reviews" as
  select * from "reviews" 
  where "status" = 'approved' 
  order by "createdAt" desc;

// View: Kullanıcı bazlı ortalama puan
View "user_rating_average" as
  select 
    "userId",
    avg("rating") as "average_rating",
    count(*) as "total_reviews"
  from "reviews"
  where "status" = 'approved'
  group by "userId";