打卡系統enter_log db schema

dbml檔：

// 學生打卡系統資料庫 Schema
// 用於 https://dbdiagram.io 視覺化

Table enter_cards {
  card_id    varchar(255) [pk, note: "實體卡號或 QR Code ID"]
  card_id_old varchar(255)
  school_no  varchar(255) [note: "學號，格式如 AM092051"]
  username   varchar(20)  [note: "姓名"]
  encodehash  char(32)    [note: "phone + school_no 的 MD5，供自助查詢"]
  encodehash2 char(32)    [note: "email + school_no 的 MD5，供自助查詢"]
  create_datetime datetime
}

Table enter_kind {
  kind_id        smallint    [pk, increment, note: "班級 ID"]
  kind_name      varchar(255) [not null, note: "班級全名，如 台北總校經理人週末研修班第一期AM071"]
  classprefix    varchar(10) [not null, note: "班級代碼，如 AM071，對應 class_dates"]
  is_not4student tinyint     [not null, default: 0, note: "1 = 員工卡/訪客證，非學生班"]
}

Table enter_card_kind {
  card_id varchar(255) [note: "FK -> enter_cards"]
  kind_id smallint     [note: "FK -> enter_kind；一張卡可屬於多班"]

  indexes {
    (card_id, kind_id) [name: "idx_card_kind"]
  }
}

Table enter_rooms {
  room_id        mediumint   [pk, increment]
  room_name      varchar(255) [note: "如「遠端上課」、「台北教室」"]
  photosrc       varchar(255)
  local_photosrc varchar(255)
  room_order     tinyint     [default: 0]
}

Table enter_logs {
  id           int         [pk, increment]
  room_id      mediumint   [note: "FK -> enter_rooms"]
  room_name    varchar(255) [note: "冗餘快取，避免 JOIN"]
  card_id      varchar(255) [note: "FK -> enter_cards"]
  card_kind    smallint    [note: "打卡當下的 kind_id 快照"]
  username     varchar(255) [note: "打卡當下的姓名快照"]
  encodehash   char(32)
  encodehash2  char(32)
  is_bye       tinyint     [note: "1 = 離開打卡"]
  log_datetime datetime    [not null]
  log_weekday  tinyint     [note: "0=週日, 1=週一 ... 6=週六"]
  reason       varchar(255) [note: "補打卡時記錄來源 URL，含 supplement.php 表示補打"]

  indexes {
    card_id [name: "idx_card_id"]
  }
}

Table class_dates {
  classprefix   varchar(9) [pk, note: "FK -> enter_kind.classprefix"]
  dates         text       [not null, note: "JSON/CSV，班級上課日期列表"]
  maxhr_by_date text       [not null, note: "JSON，各日期最大上課時數"]
}

// ── Relationships ──────────────────────────────────────

// 學生卡屬於哪些班（多對多）
Ref: enter_card_kind.card_id > enter_cards.card_id
Ref: enter_card_kind.kind_id > enter_kind.kind_id

// 打卡記錄關聯學生卡
Ref: enter_logs.card_id > enter_cards.card_id

// 打卡記錄關聯教室
Ref: enter_logs.room_id > enter_rooms.room_id

// 班級上課日期
Ref: class_dates.classprefix > enter_kind.classprefix