cophr.dbml

Project Cophr {
  database_type: 'PostgreSQL'
  Note: '''
    # Cophr Database
  '''
}

Table places {
  id int [pk, increment]
  title varchar [not null, note: "地點名稱"]
  address text [not null, note: "活動地址"]
  note: "活動地點"
}

Table users {
  id int [pk, increment]
  email varchar(256) [unique, not null, note: "用作登入等用途之信箱"]
  name varchar [not null, note: "顯示名"]
  account varchar(30) [unique, not null, note: "帳號"]
  password char(60) [null, note: "密碼"]
  createdAt timestamp [not null, default: `now()`]
  note: "使用者基本資料"
}

Table user_follows {
  user int [pk, ref: > users.id]
  target int [pk, ref: > users.id]
  createdAt timestamp [not null, default: `now()`]

  note: "使用者追蹤的人"
}

Table user_profile_page {
  user int [not null, ref: > users.id]
  settings JSON [not null, note: "個人頁面相關設定，JSON 格式原始資料"]
  items JSON [not null, note: "個人頁面中元素，JSON 格式原始資料"]
  note: "使用者個人頁面"
}

Table social_media_types {
  id int [pk, increment]
  title varchar [unique, not null]
  authProvider int [null, ref: > auth_providers.id]
  note: "社群媒體的種類，e.q. Facebook, Instagram..."
}

// 須考量到粉絲專頁等的驗證，登入後還需要取粉絲專頁等資訊來驗證
Table user_social_media {
  id int [pk, increment]
  user int [not null, ref: > users.id]
  type int [not null, ref: > social_media_types.id]

  // 如果驗證了，那麼就強制使用這個連結跳轉，避免使用者自己亂改
  link text [not null, note: "目標連結，由系統依照該社群媒體組成一個有效連結"]
  createdAt timestamp [not null, default: `now()`]

  verifiedAt timestamp [null, note: "驗證的時間"]
  externalId varchar [null, note: "儲存對應到的資料 id，如 Facebook User ID"]
  note: "使用者的社群媒體（特別有驗證的）"
}





// OAuth Integration
Table auth_providers {
  id int [pk, increment]
  title varchar [not null]
  canLogin boolean [not null, note: "是否可以透過這個登入"]
  note: "驗證服務提供者"
}

Table oauth_integrations {
  id int [pk, increment]
  authProvider int [not null, ref: > auth_providers.id]
  user int [not null, ref: > users.id]
  externalId varchar [not null, note: "3rd party service id"]
  accessToken varchar [null, note: "user's access token"]
  refreshToken varchar [null, note: "user's refresh token"]
  expireAt timestamp [null, note: "expire time of token"]

  indexes {
    (authProvider, user)[unique]
  }
  note: "帳號整合"
}





// Event
Table events {
  id int [pk, increment]
  title varchar [not null, note: "活動標題"]
  type event_type [note: "活動類型, 說明這個活動與 Cosplay 的關係，可能值見 event_type"]
  description text [null, note: "活動說明, markdown"]
  link text [not null, note: "活動網站"]
  note: "Cosplay 場次"
}

enum event_type {
  additional [note: "附帶 Cosplay 的活動"]
  specialized [note: "專門給 Cosplay 的活動"]
}

Ref event_images: events.id <> images.id

Table event_days {
  id int [pk, increment]
  event int [not null, ref: > events.id]
  description text [null, note: "補充說明, markdown"]
  eventDate date [not null, note: "活動日期"]
  startTime time [not null, note: "活動開始時間"]
  endTime time [not null, note: "活動結束時間"]
  place int [not null, ref: > places.id, note: "活動地點"]
}

Table event_cosplayer_costume {
  id int [pk, increment]
  eventDay int [not null, ref: > event_days.id]
  user int [not null, ref: > users.id]
  character int [not null, ref: > characters.id]
  costume int [not null, ref: > costumes.id]
  comment varchar [null, note: "補充說明"]

  indexes {
     (eventDay, user, character, costume) [unique]
  }
  note: "場次中會出現的 Cosplayer 跟其時裝"
}

Table event_photographers {
  id int [pk, increment]
  eventDay int [not null, ref: > event_days.id]
  user int [not null, ref: > users.id]
  comment varchar [null, note: "補充說明"]

  indexes {
     (user, eventDay) [unique]
  }
  note: "場次中會出現的攝影師"
}





// Cosplay related 
// 使用者可以自己新增
// 可能撈取資料的時候，可以顯示個人新增的，以及達到一定人數關聯的，又或是創造者是使用系統帳號的。
Table works {
  id int [pk, increment]
  creator int [not null, ref: > users.id]
  title varchar [not null, note: "作品標題"]
  description text [null, note: "作品簡介, markdown"]
  link text [null, note: "相關連結"]
  note: "Cosplay 的相關作品"
}

Ref work_images: works.id <> images.id
Ref character_work: works.id <> characters.id

Table characters {
  id int [pk, increment]
  creator int [not null, ref: > users.id]
  title varchar [not null, note: "角色名"]
  description text [null, note: "角色簡介, markdown"]
  link text [null, note: "相關連結"]
  note: "Cosplay 的角色"
}

Ref character_images: characters.id <> images.id
Ref costume_character: characters.id <> costumes.id

Table costumes {
  id int [pk, increment]
  creator int [not null, ref: > users.id]
  title varchar [not null, note: "時裝標題"]
  description text [null, note: "時裝簡介, markdown"]
  link text [null, note: "相關連結"]
  note: "時裝"
}

Ref costume_images: costumes.id <> images.id





// Photograph (use flickr)
Table albums {
  id int [pk, increment]
  flickrId varchar [unique, not null, note: "Flickr 上的 album ID"]
  eventDay int [not null, ref: > event_days.id]

  note: "Flickr 相簿，可以收錄上傳的照片"
}

Table galleries {
  id int [pk, increment]
  flickrId varchar [unique, not null, note: "Flickr 上的 gallery ID"]
  eventDay int [not null, ref: > event_days.id]

  note: "Flickr 展覽館，可以收錄包括非該帳號上傳的照片"
}

Table photos {
  id int [pk, increment]
  flickrId varchar [unique, not null, note: "Flickr 上的 photo ID"]
  user int [not null, ref: > users.id, note: "攝影師/上傳者"]
  note: "場次中拍到的照片"
}

// 保留給當場有兩個活動的可能
Ref photo_event: photos.id <> event_days.id

Ref photo_cosplayers: photos.id <> event_cosplayer_costume.id
Ref photo_likes: photos.id <> users.id

Table photo_cosplayer_recommendations {
  id int [pk, increment]
  photo int [not null, ref: > photos.id, note: "在哪張照片被 tag"]
  user int [not null, ref: > users.id, note: "被 Tag 的使用者"]

  indexes {
     (photo, user) [unique]
  }
  note: "一般使用者在照片上標記人（建議誰可能是相中人）"
}

Ref photo_cosplayer_recommended_by: photo_cosplayer_recommendations.id <> users.id





// Images (use imgur)
Table images {
  id int [pk, increment]
  uploader int [not null, ref: > users.id, note: "上傳者"]
  imgurId varchar [not null, note: "Imgur 圖片 ID"]
  note: "圖片，用於作品、角色的敘述圖（非照片）"
}