Blog

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

// Blog ve Duyuru Yönetimi Tabloları

// Ana gönderi tablosu
Table "posts" {
  "postId" bigint [pk, not null, increment]
  "academyId" bigint [ref: > "academy"."academyId"]
  "authorId" int [not null, ref: > "mobildev360.user"."userId"]
  "postType" enum_post_type [not null, note: 'blog, announcement']
  "title" varchar(500) [not null]
  "slug" varchar(500) [not null]
  "content" text [not null]
  "excerpt" text [note: 'Kısa özet']
  "featuredImage" varchar(500) [note: 'Kapak görseli URL']
  
  // Durum ve yayınlama
  "status" enum_post_status [default: 'draft', note: 'draft, published, archived, scheduled']
  "publishedAt" datetime
  "scheduledAt" datetime [note: 'Zamanlanmış yayın']
  
  // İstatistikler
  "views" int [default: 0]
  "likes" int [default: 0]
  "commentCount" int [default: 0]
  "readingTime" int [note: 'Dakika cinsinden']
  
  // Hedef kitle (duyurular için)
  "targetType" enum_post_target [note: 'all, students, instructors, admins, specific']
  "targetAcademies" json [note: 'Belirli akademiler için hedefleme']
  
  // SEO
  "metaTitle" varchar(500)
  "metaDescription" text
  "metaKeywords" varchar(500)
  "ogImage" varchar(500)
  
  // Zaman damgaları
  "createdAt" datetime [default: `now()`]
  "createdBy" int [ref: > "mobildev360.user"."userId"]
  "updatedAt" datetime
  "updatedBy" int [ref: > "mobildev360.user"."userId"]
  
  indexes {
    (academyId, status) [name: 'idx_posts_academy_status']
    (authorId) [name: 'idx_posts_author']
    (slug) [unique, name: 'idx_posts_slug']
    (publishedAt) [name: 'idx_posts_published']
  }
}

// Kategoriler tablosu
Table "post_categories" {
  "categoryId" int [pk, not null, increment]
  "academyId" bigint [ref: > "academy"."academyId"]
  "name" varchar(255) [not null]
  "slug" varchar(255) [not null]
  "description" varchar(500)
  "color" varchar(50) [note: 'Renk kodu veya gradient class']
  "icon" varchar(100) [note: 'FontAwesome ikon sınıfı']
  "orderIndex" int [default: 0]
  "isActive" boolean [default: true]
  "createdAt" datetime [default: `now()`]
  "createdBy" int [ref: > "mobildev360.user"."userId"]
  
  indexes {
    (academyId) [name: 'idx_categories_academy']
    (slug) [unique, name: 'idx_categories_slug']
  }
}

// Gönderi-Kategori ilişki tablosu (ÇOKA ÇOK)
Table "post_category_links" {
  "postId" bigint [ref: > "posts"."postId"]
  "categoryId" int [ref: > "post_categories"."categoryId"]
  
  indexes {
    (postId, categoryId) [unique, name: 'uq_post_category']
  }
}

// Etiketler tablosu
Table "post_tags" {
  "tagId" int [pk, not null, increment]
  "academyId" bigint [ref: > "academy"."academyId"]
  "name" varchar(100) [not null]
  "slug" varchar(100) [not null]
  "createdAt" datetime [default: `now()`]
  
  indexes {
    (academyId) [name: 'idx_tags_academy']
    (slug) [unique, name: 'idx_tags_slug']
  }
}

// Gönderi-Etiket ilişki tablosu
Table "post_tag_links" {
  "postId" bigint [ref: > "posts"."postId"]
  "tagId" int [ref: > "post_tags"."tagId"]
  
  indexes {
    (postId, tagId) [unique, name: 'uq_post_tag']
  }
}

// Yorumlar tablosu
Table "post_comments" {
  "commentId" bigint [pk, not null, increment]
  "postId" bigint [not null, ref: > "posts"."postId"]
  "userId" int [ref: > "mobildev360.user"."userId"]
  "guestName" varchar(255) [note: 'Misafir yorumcu adı']
  "guestEmail" varchar(255) [note: 'Misafir yorumcu email']
  "content" text [not null]
  "status" enum_comment_status [default: 'pending', note: 'pending, approved, spam, trashed']
  "parentId" bigint [ref: > "post_comments"."commentId", note: 'Üst yorum (yanıtlar için)']
  "likes" int [default: 0]
  "dislikes" int [default: 0]
  "reported" boolean [default: false]
  "createdAt" datetime [default: `now()`]
  "updatedAt" datetime
  
  indexes {
    (postId, status) [name: 'idx_comments_post']
    (userId) [name: 'idx_comments_user']
    (parentId) [name: 'idx_comments_parent']
    (createdAt) [name: 'idx_comments_created']
  }
}

// Yorum beğeni/şikayet tablosu
Table "comment_feedback" {
  "feedbackId" bigint [pk, not null, increment]
  "commentId" bigint [not null, ref: > "post_comments"."commentId"]
  "userId" int [not null, ref: > "mobildev360.user"."userId"]
  "feedbackType" enum_comment_feedback [not null, note: 'like, dislike, report']
  "createdAt" datetime [default: `now()`]
  
  indexes {
    (commentId, userId) [unique, name: 'uq_comment_user_feedback']
  }
}

// Öne çıkan gönderiler
Table "featured_posts" {
  "featuredId" bigint [pk, not null, increment]
  "postId" bigint [not null, ref: > "posts"."postId"]
  "academyId" bigint [ref: > "academy"."academyId"]
  "startDate" datetime [default: `now()`]
  "endDate" datetime
  "orderIndex" int [default: 0]
  "createdAt" datetime [default: `now()`]
  "createdBy" int [ref: > "mobildev360.user"."userId"]
  
  indexes {
    (academyId) [name: 'idx_featured_academy']
    (postId) [unique, name: 'idx_featured_post']
  }
}

// Görüntülenme istatistikleri (detaylı)
Table "post_views" {
  "viewId" bigint [pk, not null, increment]
  "postId" bigint [not null, ref: > "posts"."postId"]
  "userId" int [ref: > "mobildev360.user"."userId"]
  "ipAddress" varchar(50)
  "userAgent" text
  "viewedAt" datetime [default: `now()`]
  
  indexes {
    (postId) [name: 'idx_views_post']
    (userId) [name: 'idx_views_user']
    (viewedAt) [name: 'idx_views_date']
  }
}

// Duyuru hedef kitle (detaylı)
Table "announcement_targets" {
  "targetId" bigint [pk, not null, increment]
  "postId" bigint [not null, ref: > "posts"."postId"]
  "targetType" enum_announcement_target [not null, note: 'role, group, academy, user']
  "targetId" bigint [not null, note: 'rolId, grupId, akademiId, userId']
  "createdAt" datetime [default: `now()`]
  
  indexes {
    (postId) [name: 'idx_targets_post']
    (targetType, targetId) [name: 'idx_targets_type']
  }
}

// Enum Tanımları
Enum "enum_post_type" {
  "blog"
  "announcement"
}

Enum "enum_post_status" {
  "draft"
  "published"
  "archived"
  "scheduled"
}

Enum "enum_post_target" {
  "all"
  "students"
  "instructors"
  "admins"
  "specific"
}

Enum "enum_comment_status" {
  "pending"
  "approved"
  "spam"
  "trashed"
}

Enum "enum_comment_feedback" {
  "like"
  "dislike"
  "report"
}

Enum "enum_announcement_target" {
  "role"
  "group"
  "academy"
  "user"
}

// View: Popüler gönderiler
View "popular_posts" as
  select 
    p.*,
    (p.views * 0.6 + p.likes * 0.3 + p.commentCount * 0.1) as popularity_score
  from "posts" p
  where p.status = 'published'
  order by popularity_score desc
  limit 10;

// View: Son yorumlar (onaylanmış)
View "recent_comments" as
  select 
    c.*,
    p.title as post_title
  from "post_comments" c
  join "posts" p on c.postId = p.postId
  where c.status = 'approved'
  order by c.createdAt desc
  limit 20;