# Phân Hệ Quản Lý Bài Làm Của Học Viên & Chấm Bài Thi
## 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_SCORE : "student_id"
    API_MOODLE ||--o{ STUDENT_SCORE : "quiz_id"
    
    STUDENT_SCORE ||--o{ STUDENT_EXAM_HISTORIES : "student_score_id"
    STUDENT_SCORE ||--o{ STUDENT_SCORE_EXT : "student_score_id"
    STUDENT_SCORE ||--o{ STUDENT_SCORE_GRADING_HISTORIES : "student_score_id"
    STUDENT_SCORE ||--o{ TEACHER_ASSIGNED_HISTORIES : "student_score_id"
    STUDENT_SCORE ||--o{ STUDENT_EXAM_HISTORY_TEACHERS : "student_score_id"
    STUDENT_SCORE ||--o{ TEACHER_GRADING_LOGS : "student_score_id"
    STUDENT_SCORE ||--o{ STUDENT_SCORE_ICL_LOGS : "student_score_id"

    STUDENT_EXAM_HISTORIES ||--o{ STUDENT_SCORE_GRADING_HISTORIES : "student_exam_history_id"
    STUDENT_EXAM_HISTORIES ||--o{ STUDENT_EXAM_HISTORY_TEACHERS : "student_exam_history_id"

    USERS ||--o{ TEACHER_ASSIGNED_HISTORIES : "teacher_id / assigned_by"
    USERS ||--o{ STUDENT_SCORE_GRADING_HISTORIES : "grader_id"
    USERS ||--o{ TEACHER_GRADING_LOGS : "user_id"

    STUDENT_SCORE {
        bigint id PK "ID Khóa chính"
        bigint student_id FK "ID Học viên (students.id)"
        bigint quiz_id FK "ID Bài Quiz (api_moodle.id)"
        bigint idMockContest "ID Contest từ hệ thống EMS"
        bigint idHistoryContest "ID Phiên thi từ hệ thống EMS"
        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"
        decimal listening_score "Điểm kỹ năng Listening"
        decimal reading_score "Điểm kỹ năng Reading"
        decimal writing_score "Điểm kỹ năng Writing"
        decimal speaking_score "Điểm kỹ năng Speaking"
        decimal overall_score "Điểm tổng kết Band Score"
        tinyint cheating_status "0: Bình thường, 1: Nghi vấn gian lận"
        longtext cheating_reports "JSON lưu bằng chứng vi phạm"
        timestamp created_at "Thời gian tạo"
        timestamp updated_at "Thời gian cập nhật"
    }

    STUDENT_EXAM_HISTORIES {
        bigint id PK "ID Khóa chính"
        bigint student_score_id FK "ID bài thi gốc (student_score.id)"
        bigint idHistory "ID Part History từ EMS"
        int skill "13: Listening, 14: Reading, 15: Writing, 16: Speaking, 17: Exam, 12: Vocab"
        decimal lms_score "Điểm số kỹ năng do LMS lưu trữ"
        longtext externalGradingDetail "JSON chi tiết điểm tiêu chí, nhận xét, bài làm"
        string rescored_file "File đính kèm chấm lại"
        text rescored_comment "Nhận xét tổng thể khi chấm lại"
        tinyint isCompletedRescored "1: Đã hoàn thành chấm, 0: Chưa"
        timestamp created_at "Thời gian nộp bài part"
        timestamp updated_at "Thời gian cập nhật"
    }

    STUDENT_SCORE_GRADING_HISTORIES {
        bigint id PK "ID Khóa chính"
        bigint student_score_id FK "ID bài thi (student_score.id)"
        bigint student_exam_history_id FK "ID part thi (student_exam_histories.id)"
        bigint grader_id FK "ID người chấm (users.id)"
        string grader_role "Vai trò: teacher, qa, admin, ai"
        int skill "Kỹ năng chấm (15: Writing, 16: Speaking)"
        decimal old_score "Điểm trước khi sửa"
        decimal new_score "Điểm sau khi lưu"
        longtext criteria_data "JSON chi tiết điểm từng tiêu chí"
        text comment "Nhận xét chi tiết"
        string audio_comment_url "Link file ghi âm nhận xét"
        timestamp created_at "Thời điểm chấm"
    }

    TEACHER_ASSIGNED_HISTORIES {
        bigint id PK "ID Khóa chính"
        bigint student_score_id FK "ID bài thi"
        bigint teacher_id FK "ID Giáo viên được gán (users.id)"
        int part_number "Thứ tự part gán chấm"
        int skill "Kỹ năng gán chấm"
        bigint assigned_by FK "ID Cán bộ thực hiện phân công"
        tinyint status "0: Đã gán, 1: Đã chấm xong, 2: Đã thu hồi/chuyển giao"
        timestamp created_at "Thời gian phân công"
        timestamp updated_at "Thời gian cập nhật"
    }

    STUDENT_SCORE_ICL_LOGS {
        bigint id PK "ID Khóa chính"
        bigint student_score_id FK "ID bài thi"
        longtext payload "JSON payload gửi đồng bộ sang ICL"
        longtext response "JSON response nhận về hoặc log lỗi"
        tinyint status "0: Thất bại, 1: Thành công"
        timestamp created_at "Thời gian gửi"
    }
```

---

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

#### 2.1. Bảng `student_score` (Kết Quả Thi Của Học Viên - Bảng Chính)
- **Mô tả**: Lưu trữ kết quả tổng quát của mỗi lần học viên làm bài thi (Attempt), điểm 4 kỹ năng, điểm Overall và các trạng thái chấm/nộp bài.
- **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ài làm học viên. |
| `student_id` | `BIGINT UNSIGNED` | NO | | ID học viên trong bảng `students.id`. |
| `quiz_id` | `BIGINT UNSIGNED` | NO | | ID bài thi trong bảng `api_moodle.id` (`moodle_type = 'quiz'`). |
| `idMockContest` | `BIGINT UNSIGNED` | YES | `NULL` | ID đề thi trên hệ thống AI Hub / EMS Service. |
| `idHistoryContest`| `BIGINT UNSIGNED` | YES | `NULL` | ID phiên làm bài của học viên trên hệ thống EMS. |
| `exam_status` | `TINYINT` | NO | `0` | Trạng thái nộp bài:<br>• `0`: Chờ nộp (đang làm/chưa thi)<br>• `1`: Đã nộp bài<br>• `2`: Không nộp (quá hạn) |
| `submission_status`| `TINYINT` | NO | `0` | Trạng thái thời gian nộp:<br>• `0`: Đúng hạn<br>• `1`: Trễ hạn |
| `score_processing_status`| `TINYINT`| YES | `0` | Trạng thái xử lý điểm:<br>• `0`: Đang xử lý (chờ chấm)<br>• `1`: Hoàn tất chấm<br>• `2`: Giao chấm lại (Phúc khảo)<br>• `3`: Đã hủy kết quả |
| `listening_score` | `DECIMAL(4,2)` | YES | `NULL` | Điểm kỹ năng Nghe (Listening Band Score). |
| `reading_score` | `DECIMAL(4,2)` | YES | `NULL` | Điểm kỹ năng Đọc (Reading Band Score). |
| `writing_score` | `DECIMAL(4,2)` | YES | `NULL` | Điểm kỹ năng Viết (Writing Band Score). |
| `speaking_score` | `DECIMAL(4,2)` | YES | `NULL` | Điểm kỹ năng Nói (Speaking Band Score). |
| `overall_score` | `DECIMAL(4,2)` | YES | `NULL` | Điểm tổng kết toàn bài (Overall Band Score đã làm tròn). |
| `cheating_status` | `TINYINT` | NO | `0` | Trạng thái nghi vấn gian lận:<br>• `0`: Bình thường<br>• `1`: Có nghi vấn gian lận |
| `cheating_reports`| `LONGTEXT` | YES | `NULL` | Dữ liệu JSON ghi nhận các báo cáo vi phạm, link ảnh screenshot, video log. |
| `created_at` | `TIMESTAMP` | YES | `NULL` | Thời điểm bắt đầu tạo bài thi. |
| `updated_at` | `TIMESTAMP` | YES | `NULL` | Thời điểm cập nhật trạng thái/điểm số gần nhất. |

##### Danh sách Chỉ Mục (Indexes):
- `PRIMARY KEY (`id`)`
- `INDEX idx_student_quiz (`student_id`, `quiz_id`)`
- `INDEX idx_exam_status (`exam_status`)`
- `INDEX idx_score_processing (`score_processing_status`)`
- `INDEX idx_cheating (`cheating_status`)`
- `INDEX idx_created_at (`created_at`)`

---

#### 2.2. Bảng `student_exam_histories` (Lịch Sử Từng Kỹ Năng / Part Thi)
- **Mô tả**: Lưu trữ chi tiết bài làm, câu trả lời, điểm thành phần và nhận xét của từng Part / Kỹ năng (Listening, Reading, Writing, Speaking).
- **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 Part bài thi. |
| `student_score_id`| `BIGINT UNSIGNED`| NO | | ID bài thi liên kết trong `student_score.id`. |
| `idHistory` | `BIGINT UNSIGNED` | YES | `NULL` | ID phiên làm bài Part tương ứng từ EMS Service. |
| `skill` | `INT` | NO | | Mã định danh kỹ năng:<br>• `13`: Listening<br>• `14`: Reading<br>• `15`: Writing<br>• `16`: Speaking<br>• `17`: Exam trắc nghiệm tổng hợp<br>• `12`: Vocabulary |
| `lms_score` | `DECIMAL(4,2)` | YES | `NULL` | Điểm số của Part này do LMS tính toán hoặc Giáo viên chấm. |
| `externalGradingDetail`| `LONGTEXT`| YES | `NULL` | Dữ liệu JSON chứa toàn bộ bài làm học viên, rubric tiêu chí (TR, CC, LR, GRA/PR), nhận xét từng đoạn, nhận xét tổng thể. |
| `rescored_file` | `VARCHAR(255)` | YES | `NULL` | Đường dẫn file đính kèm nhận xét/chấm điểm của giáo viên. |
| `rescored_comment` | `TEXT` | YES | `NULL` | Nhận xét dạng văn bản của giáo viên khi chấm lại/phúc khảo. |
| `isCompletedRescored`| `TINYINT` | NO | `0` | Đánh dấu Part này đã hoàn thành chấm hay chưa (1: Đã hoàn tất, 0: Chưa). |
| `created_at` | `TIMESTAMP` | YES | `NULL` | Thời điểm nộp bài của Part. |
| `updated_at` | `TIMESTAMP` | YES | `NULL` | Thời điểm cập nhật điểm Part. |

##### Danh sách Chỉ Mục (Indexes):
- `PRIMARY KEY (`id`)`
- `INDEX idx_student_score_id (`student_score_id`)`
- `INDEX idx_skill (`skill`)`
- `INDEX idx_idHistory (`idHistory`)`

---

#### 2.3. Bảng `student_score_grading_histories` (Lịch Sử Chấm Điểm & Phúc Khảo - Audit Trail)
- **Mô tả**: Lưu trữ toàn bộ lịch sử các lần chấm bài, thay đổi điểm số của từng kỹ năng qua các lượt chấm (AI, Giáo viên lần 1, Giáo viên lần 2, QA Phúc khảo).

| Tên Cột | Kiểu Dữ Liệu | Nullable | Chú Thích (Comment) Chi Tiết |
| :--- | :--- | :---: | :--- |
| `id` | `BIGINT UNSIGNED` | NO | Khóa chính của bản ghi log chấm điểm. |
| `student_score_id` | `BIGINT UNSIGNED` | NO | ID bài thi trong `student_score.id`. |
| `student_exam_history_id`| `BIGINT UNSIGNED`| YES | ID part thi trong `student_exam_histories.id`. |
| `grader_id` | `BIGINT UNSIGNED` | YES | ID người chấm trong bảng `users.id`. |
| `grader_role` | `VARCHAR(50)` | NO | Vai trò của người chấm (`teacher`, `qa`, `admin`, `ai_system`). |
| `skill` | `INT` | NO | Kỹ năng được chấm (`15`: Writing, `16`: Speaking). |
| `old_score` | `DECIMAL(4,2)` | YES | Điểm số trước khi cập nhật. |
| `new_score` | `DECIMAL(4,2)` | YES | Điểm số mới sau khi chấm. |
| `criteria_data` | `LONGTEXT` | YES | Snapshot JSON lưu điểm chi tiết từng tiêu chí Rubric tại thời điểm chấm. |
| `comment` | `TEXT` | YES | Nội dung nhận xét chi tiết của người chấm. |
| `audio_comment_url` | `VARCHAR(500)` | YES | Đường dẫn file âm thanh nhận xét ghi âm (nếu có). |
| `created_at` | `TIMESTAMP` | YES | Thời điểm ghi nhận lượt chấm. |

---

#### 2.4. Bảng `teacher_assigned_histories` (Lịch Sử Phân Công Giáo Viên)
- **Mô tả**: Ghi nhận toàn bộ các lượt phân công giáo viên chấm bài (Ai gán, gán cho ai, part nào, thời điểm nào).

| Tên Cột | Kiểu Dữ Liệu | Nullable | Chú Thích (Comment) Chi Tiết |
| :--- | :--- | :---: | :--- |
| `id` | `BIGINT UNSIGNED` | NO | Khóa chính. |
| `student_score_id` | `BIGINT UNSIGNED` | NO | ID bài thi. |
| `teacher_id` | `BIGINT UNSIGNED` | NO | ID Giáo viên được phân công (`users.id`). |
| `part_number` | `INT` | YES | Số thứ tự Part được phân công chấm. |
| `skill` | `INT` | YES | Kỹ năng phân công (`15`: Writing, `16`: Speaking). |
| `assigned_by` | `BIGINT UNSIGNED` | YES | ID Cán bộ vận hành thực hiện thao tác phân công. |
| `status` | `TINYINT` | NO | Trạng thái: `0` (Đang phân công), `1` (Đã hoàn tất chấm), `2` (Đã thu hồi/chuyển GV khác). |
| `created_at` | `TIMESTAMP` | YES | Thời điểm phân công. |
| `updated_at` | `TIMESTAMP` | YES | Thời điểm cập nhật trạng thái phân công. |

---

#### 2.5. Bảng `student_score_icl_logs` (Nhật Ký Đồng Bộ Điểm Sang ICL / Zeus)
- **Mô tả**: Lưu trữ toàn bộ các gói tin đồng bộ điểm sang hệ thống đối tác ICL/Zeus phục vụ kiểm toán và xử lý lỗi đồng bộ.

| Tên Cột | Kiểu Dữ Liệu | Nullable | Chú Thích (Comment) Chi Tiết |
| :--- | :--- | :---: | :--- |
| `id` | `BIGINT UNSIGNED` | NO | Khóa chính. |
| `student_score_id` | `BIGINT UNSIGNED` | NO | ID bài thi trong `student_score.id`. |
| `payload` | `LONGTEXT` | YES | Nội dung JSON Request gửi sang ICL/Zeus (chứa điểm 4 kỹ năng & Overall). |
| `response` | `LONGTEXT` | YES | Nội dung JSON Response trả về từ ICL hoặc thông tin ngoại lệ lỗi. |
| `status` | `TINYINT` | NO | Trạng thái đồng bộ: `1` (Thành công), `0` (Lỗi / Cần thử lại). |
| `created_at` | `TIMESTAMP` | YES | Thời điểm phát sinh giao dịch đồng bộ. |
| `updated_at` | `TIMESTAMP` | YES | Thời điểm cập nhật log. |