# Phân Hệ Quản Lý Danh Sách Học Viên Đến Lịch Thi (Web Exam Schedule)
## 04. Cơ Sở Dữ Liệu & Lược Đồ Chi Tiết (Database & Schema Design)

---

### 1. Sơ Đồ Quan Hệ Thực Thể (Entity Relationship Diagram - ERD)

```mermaid
erDiagram
    STUDENTS ||--o{ STUDENT_EXAM_SCHEDULES : "student_id"
    STUDENT_EXAM_SCHEDULES ||--o{ STUDENT_EXAM_SCHEDULE_HISTORIES : "schedule_id"
    STUDENT_EXAM_SCHEDULES ||--o{ STUDENT_EXAM_SCHEDULE_ICL_LOGS : "student_exam_schedule_id"
    
    API_MOODLE ||--o{ STUDENT_EXAM_SCHEDULES : "course_id / section_id / product_id"
    STUDENTS ||--o{ COURSE_STUDENT : "student_id"
    API_MOODLE ||--o{ COURSE_STUDENT : "course_id"
    
    STUDENTS ||--o{ USER_QUIZ_ATTEMPTS : "student_id"
    API_MOODLE ||--o{ USER_QUIZ_ATTEMPTS : "quiz_id (moodle_id)"
    
    STUDENTS ||--o{ STUDENT_SCORE : "student_id"
    API_MOODLE ||--o{ STUDENT_SCORE : "quiz_id"

    STUDENTS ||--o{ ZEUS_SESSION_COMPLETIONS : "zeus_id"

    STUDENT_EXAM_SCHEDULES {
        bigint id PK "ID Khóa chính"
        bigint student_id FK "ID Học viên (students.id)"
        bigint course_id FK "ID Khóa học (api_moodle.id)"
        bigint section_id FK "ID Bài thi/Section (api_moodle.id)"
        bigint product_id FK "ID Sản phẩm/Category gốc"
        string product_name "Tên sản phẩm phẳng"
        string course_name "Tên khóa học phẳng"
        string exam_name "Tên bài thi phẳng"
        datetime zeus_start_date "Thời gian mở bài từ Zeus"
        datetime zeus_deadline "Thời gian đóng bài từ Zeus"
        datetime start_date "Thời gian mở bài thực tế"
        datetime deadline "Thời gian đóng bài thực tế"
        tinyint requirement_type "0: Đúng lộ trình, 1: Thi lại, 2: Thi sớm"
        tinyint status "1: Mở (Active), 0: Đóng (Inactive)"
        tinyint exam_status "0: Chờ nộp, 1: Đã nộp, 2: Không nộp"
        tinyint submission_status "0: Đúng hạn, 1: Trễ hạn"
        tinyint score_processing_status "0: Đang xử lý, 1: Hoàn tất, 2: Giao chấm lại, 3: Đã hủy"
        tinyint is_24h_reminded "Đã nhắc trước 24h (0: Chưa, 1: Rồi)"
        tinyint is_48h_reminded "Đã nhắc trước 48h (0: Chưa, 1: Rồi)"
        timestamp created_at "Thời gian tạo"
        timestamp updated_at "Thời gian cập nhật"
    }

    STUDENT_EXAM_SCHEDULE_HISTORIES {
        bigint id PK "ID Khóa chính"
        bigint schedule_id FK "ID Lịch thi (student_exam_schedules.id)"
        bigint user_id FK "ID Cán bộ thực hiện (users.id)"
        string action "Loại hành động: create, update, extend"
        datetime old_start_date "Ngày mở cũ"
        datetime new_start_date "Ngày mở mới"
        datetime old_deadline "Hạn nộp cũ"
        datetime new_deadline "Hạn nộp mới"
        tinyint old_requirement_type "Phân loại yêu cầu cũ"
        tinyint new_requirement_type "Phân loại yêu cầu mới"
        tinyint old_status "Trạng thái cũ"
        tinyint new_status "Trạng thái mới"
        timestamp created_at "Thời gian tạo"
        timestamp updated_at "Thời gian cập nhật"
    }

    STUDENT_EXAM_SCHEDULE_ICL_LOGS {
        bigint id PK "ID Khóa chính"
        bigint student_exam_schedule_id FK "ID Lịch thi"
        longtext payload "JSON payload gửi sang Zeus"
        longtext response "JSON response hoặc log lỗi"
        tinyint status "0: Thất bại, 1: Thành công"
        timestamp created_at "Thời gian tạo"
        timestamp updated_at "Thời gian cập nhật"
    }

    USER_QUIZ_ATTEMPTS {
        bigint id PK "ID Khóa chính"
        bigint student_id FK "ID Học viên"
        bigint quiz_id "Moodle ID của Quiz"
        int attempt_count "Số lượt đã làm bài"
        int reset_allowance "Số lượt được cấp thi lại"
        timestamp updated_at "Thời gian cập nhật"
    }
```

---

### 2. Chi Tiết Lược Đồ Bảng (Data Dictionary & Column Comments)

#### 2.1. Bảng `student_exam_schedules` (Bảng Lịch Thi Học Viên - Bảng Chính)
- **Mô tả**: Lưu trữ thông tin lịch thi được đồng bộ từ Zeus hoặc tạo/đổi/gia hạn thủ công bởi cán bộ vận hành.
- **Engine**: InnoDB | **Charset**: `utf8mb4_unicode_ci`

| Tên Cột | Kiểu Dữ Liệu | Nullable | Mặc định | Chú Thích (Comment) Chi Tiết |
| :--- | :--- | :---: | :---: | :--- |
| `id` | `BIGINT UNSIGNED` | NO | Auto Increment | Khóa chính duy nhất của bản ghi lịch thi. |
| `student_id` | `BIGINT UNSIGNED` | NO | | ID học viên, liên kết với bảng `students.id`. |
| `course_id` | `BIGINT UNSIGNED` | NO | | ID khóa học trong LMS, liên kết với `api_moodle.id` (`moodle_type = 'course'`). |
| `section_id` | `BIGINT UNSIGNED` | NO | | ID Section/Chủ đề trong LMS chứa bài Quiz, liên kết với `api_moodle.id` (`moodle_type = 'section'`). |
| `product_id` | `BIGINT UNSIGNED` | YES | `NULL` | ID danh mục sản phẩm cao nhất (Root Category), liên kết với `api_moodle.id`. Dùng để lọc nhanh theo sản phẩm. |
| `product_name` | `VARCHAR(255)` | YES | `NULL` | Tên danh mục sản phẩm (Denormalized) phục vụ hiển thị và xuất file không cần query đệ quy. |
| `course_name` | `VARCHAR(255)` | YES | `NULL` | Tên khóa học (Denormalized) lấy từ `api_moodle.moodle_name`. |
| `exam_name` | `VARCHAR(255)` | YES | `NULL` | Tên bài thi / Section (Denormalized) lấy từ `api_moodle.moodle_name`. |
| `zeus_start_date` | `DATETIME` | YES | `NULL` | Mốc thời gian mở bài gốc do hệ thống Zeus đồng bộ về (Backup & đối chiếu). |
| `zeus_deadline` | `DATETIME` | YES | `NULL` | Mốc thời hạn đóng bài gốc do hệ thống Zeus đồng bộ về (Backup & đối chiếu). |
| `start_date` | `DATETIME` | YES | `NULL` | Thời gian mở bài thực tế được thiết lập bởi cán bộ vận hành khi tạo hoặc đổi lịch thi. |
| `deadline` | `DATETIME` | YES | `NULL` | Thời hạn đóng bài thực tế được thiết lập khi tạo, đổi lịch hoặc gia hạn. |
| `requirement_type`| `TINYINT` | NO | `0` | Phân loại yêu cầu lịch thi: <br>• `0`: Thi đúng lộ trình<br>• `1`: Thi lại (Kích hoạt cấp thêm lượt làm bài)<br>• `2`: Thi sớm |
| `status` | `TINYINT` | NO | `1` | Trạng thái hiệu lực của bản ghi lịch thi:<br>• `1`: Mở (Active - Lịch thi có hiệu lực)<br>• `0`: Đóng (Inactive - Lịch cũ bị khóa khi Đổi lịch) |
| `exam_status` | `TINYINT` | YES | `0` | Trạng thái làm bài thi của học viên:<br>• `0`: Chờ nộp (Chưa thi hoặc đang làm)<br>• `1`: Đã nộp<br>• `2`: Không nộp (Quá hạn) |
| `submission_status`| `TINYINT` | YES | `0` | Trạng thái nộp bài so với hạn chót:<br>• `0`: Đúng hạn<br>• `1`: Trễ hạn |
| `score_processing_status`| `TINYINT` | YES | `NULL` | Trạng thái xử lý điểm bài thi:<br>• `0`: Đang xử lý<br>• `1`: Hoàn tất<br>• `2`: Giao chấm lại<br>• `3`: Đã hủy |
| `is_24h_reminded`| `TINYINT` | NO | `0` | Cờ ghi nhận đã gửi thông báo nhắc thi trước 24h:<br>• `0`: Chưa gửi<br>• `1`: Đã gửi |
| `is_48h_reminded`| `TINYINT` | NO | `0` | Cờ ghi nhận đã gửi thông báo nhắc thi trước 48h:<br>• `0`: Chưa gửi<br>• `1`: Đã gửi |
| `created_at` | `TIMESTAMP` | YES | `NULL` | Thời điểm tạo bản ghi. |
| `updated_at` | `TIMESTAMP` | YES | `NULL` | Thời điểm cập nhật bản ghi gần nhất. |

##### Danh sách Chỉ Mục (Indexes):
- `PRIMARY KEY (`id`)`
- `INDEX idx_student_section (`student_id`, `section_id`)`: Tối ưu tra cứu lịch thi theo học viên và bài thi.
- `INDEX idx_start_date (`start_date`)`: Tối ưu lọc theo khung thời gian mở bài.
- `INDEX idx_product_id (`product_id`)`: Tối ưu lọc theo sản phẩm.
- `INDEX idx_exam_status (`exam_status`)`: Tối ưu lọc theo trạng thái bài thi.
- `INDEX idx_score_processing_status (`score_processing_status`)`: Tối ưu lọc theo trạng thái xử lý điểm.
- `INDEX idx_is_24h_reminded (`is_24h_reminded`)`: Tối ưu job quét gửi thông báo nhắc thi 24h.
- `INDEX idx_is_48h_reminded (`is_48h_reminded`)`: Tối ưu job quét gửi thông báo nhắc thi 48h.

---

#### 2.2. Bảng `student_exam_schedule_histories` (Lịch Sử Thay Đổi Lịch Thi - Audit Log)
- **Mô tả**: Lưu trữ lịch sử toàn bộ các thao tác tạo mới, đổi lịch, gia hạn để phục vụ đối soát và truy vết người thực hiện.
- **Engine**: InnoDB | **Charset**: `utf8mb4_unicode_ci`

| Tên Cột | Kiểu Dữ Liệu | Nullable | Mặc định | Chú Thích (Comment) Chi Tiết |
| :--- | :--- | :---: | :---: | :--- |
| `id` | `BIGINT UNSIGNED` | NO | Auto Increment | Khóa chính của bản ghi lịch sử. |
| `schedule_id` | `BIGINT UNSIGNED` | NO | | ID Lịch thi trong bảng `student_exam_schedules.id`. |
| `user_id` | `BIGINT UNSIGNED` | YES | `NULL` | ID tài khoản người dùng/cán bộ thực hiện thao tác (`users.id`). |
| `action` | `VARCHAR(50)` | NO | `'update'` | Tên hành động: <br>• `create`: Tạo mới lịch thi<br>• `update`: Đổi lịch thi<br>• `extend`: Gia hạn hạn nộp<br>• `system_auto`: Hệ thống tự động điều chỉnh |
| `old_start_date` | `DATETIME` | YES | `NULL` | Thời gian mở bài trước khi thay đổi. |
| `new_start_date` | `DATETIME` | YES | `NULL` | Thời gian mở bài sau khi thay đổi. |
| `old_deadline` | `DATETIME` | YES | `NULL` | Thời hạn nộp bài trước khi thay đổi. |
| `new_deadline` | `DATETIME` | YES | `NULL` | Thời hạn nộp bài sau khi thay đổi. |
| `old_requirement_type`| `TINYINT` | YES | `NULL` | Phân loại yêu cầu trước khi thay đổi (`0`, `1`, `2`). |
| `new_requirement_type`| `TINYINT` | YES | `NULL` | Phân loại yêu cầu sau khi thay đổi (`0`, `1`, `2`). |
| `old_status` | `TINYINT` | YES | `NULL` | Trạng thái hiệu lực trước khi thay đổi (`0`, `1`). |
| `new_status` | `TINYINT` | YES | `NULL` | Trạng thái hiệu lực sau khi thay đổi (`0`, `1`). |
| `created_at` | `TIMESTAMP` | YES | `NULL` | Thời điểm thực hiện thao tác. |
| `updated_at` | `TIMESTAMP` | YES | `NULL` | Thời điểm cập nhật log. |

##### Danh sách Chỉ Mục (Indexes):
- `PRIMARY KEY (`id`)`
- `INDEX idx_schedule_id (`schedule_id`)`: Tối ưu truy xuất toàn bộ lịch sử của một lịch thi cụ thể.

---

#### 2.3. Bảng `student_exam_schedule_icl_logs` (Nhật Ký Tích Hợp Zeus / ICL)
- **Mô tả**: Lưu trữ chi tiết các bản tin Request Payload và Response khi hệ thống LMS gửi thông báo nhắc thi/cập nhật hạn nộp sang Zeus.
- **Engine**: InnoDB | **Charset**: `utf8mb4_unicode_ci`

| Tên Cột | Kiểu Dữ Liệu | Nullable | Mặc định | Chú Thích (Comment) Chi Tiết |
| :--- | :--- | :---: | :---: | :--- |
| `id` | `BIGINT UNSIGNED` | NO | Auto Increment | Khóa chính của bản ghi log ICL. |
| `student_exam_schedule_id`| `BIGINT UNSIGNED` | NO | | ID của lịch thi liên kết trong `student_exam_schedules.id`. |
| `log_type` | `VARCHAR(50)` | YES | `NULL` | Loại bản tin gửi (ví dụ: `reminding_mocktest`, `manual`). |
| `payload` | `LONGTEXT` | YES | `NULL` | Nội dung JSON Request gửi sang Zeus (chứa `user_id`, `lms_course_section_id`, `end`, ...). |
| `response` | `LONGTEXT` | YES | `NULL` | Nội dung JSON Response nhận về từ Zeus hoặc thông tin ngoại lệ lỗi nếu gửi thất bại. |
| `status` | `TINYINT` | NO | `0` | Trạng thái gửi:<br>• `0`: Lỗi / Thất bại / Chờ thử lại<br>• `1`: Thành công |
| `created_at` | `TIMESTAMP` | YES | `NULL` | Thời điểm phát sinh giao dịch gửi. |
| `updated_at` | `TIMESTAMP` | YES | `NULL` | Thời điểm cập nhật. |

##### Danh sách Chỉ Mục (Indexes):
- `PRIMARY KEY (`id`)`
- `INDEX idx_schedule_id (`student_exam_schedule_id`)`: Tối ưu lọc log theo lịch thi.
- `INDEX idx_status (`status`)`: Tối ưu lọc các bản ghi lỗi để thực hiện cơ chế Retry.

---

### 3. Các Bảng Dữ Liệu Liên Quan Trực Tiếp (Associated Tables)

#### 3.1. Bảng `user_quiz_attempts` (Quản Lý Lượt Làm Bài Moodle)
- **Mục đích**: Được điều chỉnh khi có yêu cầu **Thi lại** (`requirement_type = 1`).
- **Các trường quan trọng**:
  - `student_id`: ID học viên.
  - `quiz_id`: Moodle ID của bài Quiz (`api_moodle.moodle_id`).
  - `attempt_count`: Số lượt học viên đã thực hiện làm bài.
  - `reset_allowance`: Số lượt bổ sung được cấp phép thi lại. Hệ thống tăng cột này (`+1`) qua hàm `processRetakeAllowance`.

#### 3.2. Bảng `course_student` (Liên Kết Học Viên - Khóa Học & Nhận Diện VIP)
- **Mục đích**: Nhận diện học viên có phải là đối tượng VIP hay không.
- **Các trường quan trọng**:
  - `student_id`: ID học viên.
  - `course_id`: ID khóa học.
  - `is_abnormal`: Cờ đánh dấu học viên đặc biệt/VIP (`1` = VIP, `0` = Thường).
  - `deleted_at`: Cờ soft delete (Chỉ xét bản ghi `deleted_at IS NULL`).

#### 3.3. Bảng `zeus_session_completions` (Trạng Thái Hoàn Thành Phiên Học Zeus)
- **Mục đích**: Được dọn dẹp khi thực hiện **Xóa lịch thi** (`deleteExamSchedule`).
- **Các trường quan trọng**:
  - `zeus_id`: ID người dùng trên hệ thống Zeus (`students.zeus_id`).
  - `course_id`: Moodle ID của khóa học.
  - `section_id`: Moodle ID của bài thi/section.