Intelligent_Health_Evaluation_System_4_Civilian_Health_Monitoring/数据库框架.md

# 数据库表结构定义

## `user_info` 表

用于存储管理员用户的基本信息、认证凭据和权限。**注意：此表只存储管理员用户，患者用户存储在 `patient_info` 表中。**

### 表结构 (SQL DDL)

```sql
CREATE TABLE user_info (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    hashed_password VARCHAR(255) NOT NULL,
    department VARCHAR(100),
    position VARCHAR(100),
    is_admin BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
```

### 字段说明

| 字段名          | 类型          | 约束            | 说明                                     |
| --------------- | ------------- | --------------- | ---------------------------------------- |
| `id`            | SERIAL        | PRIMARY KEY     | 管理员唯一标识，自增主键。               |
| `username`      | VARCHAR(50)   | UNIQUE, NOT NULL| 管理员用户名，用于登录，必须唯一。       |
| `email`         | VARCHAR(255)  | UNIQUE, NOT NULL| 管理员邮箱，必须唯一。                   |
| `hashed_password` | VARCHAR(255)  | NOT NULL        | 使用 bcrypt 哈希算法加密后的密码。       |
| `department`    | VARCHAR(100)  |                 | 管理员所属部门。                         |
| `position`      | VARCHAR(100)  |                 | 管理员职位。                             |
| `is_admin`      | BOOLEAN       | DEFAULT TRUE    | 标识用户是否为管理员，默认为 `TRUE`。   |
| `created_at`    | TIMESTAMPTZ   | DEFAULT NOW()   | 记录创建时间。                           |
| `updated_at`    | TIMESTAMPTZ   | DEFAULT NOW()   | 记录最后更新时间。                       |

### 示例数据

为了方便测试，可以插入以下数据：

```sql
-- 密码: admin123
INSERT INTO user_info (username, email, hashed_password, department, position, is_admin) 
VALUES ('admin', 'admin@example.com', '$2b$12$EixZaYVK1eCjG4n6b5D1zuR8yA5d1E0n.3Jd9G.Yk.H8zJ.p7.wI.', '系统管理', '管理员', TRUE);

-- 密码: manager123
INSERT INTO user_info (username, email, hashed_password, department, position, is_admin) 
VALUES ('manager', 'manager@example.com', '$2b$12$xG.Vn2G7f2wJdDGkQxGjA.w0q.K6.Qj3S.h2Z/d.aY5q.w2K.X5U.', '医疗管理', '主管', TRUE);
```

## `patient_info` 表

用于存储患者用户的基本信息和认证凭据。患者通过扫码注册，使用就诊号+身份证后六位登录。

### 表结构 (SQL DDL)

```sql
CREATE TABLE patient_info (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR(50) UNIQUE NOT NULL,
    medical_record_number VARCHAR(50) UNIQUE NOT NULL,
    id_last_six VARCHAR(6) NOT NULL,
    name VARCHAR(100) NOT NULL,
    gender VARCHAR(10) NOT NULL,
    dominant_hand VARCHAR(10) NOT NULL,
    diagnosis TEXT,
    dob DATE,
    height_cm INTEGER,
    weight_kg DECIMAL(5,2),
    contact VARCHAR(20),
    inpatient_number VARCHAR(50),
    bed_number VARCHAR(20),
    education_level VARCHAR(50),
    education_years INTEGER,
    remarks TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
```

### 字段说明

| 字段名                | 类型          | 约束            | 说明                                     |
| --------------------- | ------------- | --------------- | ---------------------------------------- |
| `id`                  | SERIAL        | PRIMARY KEY     | 患者唯一标识，自增主键。                 |
| `user_id`             | VARCHAR(50)   | UNIQUE, NOT NULL| 系统生成的用户ID，用于内部标识。         |
| `medical_record_number` | VARCHAR(50)   | UNIQUE, NOT NULL| 就诊号，用作登录用户名，必须唯一。       |
| `id_last_six`         | VARCHAR(6)    | NOT NULL        | 身份证后六位，用作登录密码。             |
| `name`                | VARCHAR(100)  | NOT NULL        | 患者姓名。                               |
| `gender`              | VARCHAR(10)   | NOT NULL        | 性别（男/女）。                          |
| `dominant_hand`       | VARCHAR(10)   | NOT NULL        | 利手（左/右）。                          |
| `diagnosis`           | TEXT          |                 | 诊断信息。                               |
| `dob`                 | DATE          |                 | 出生日期（格式：YYYY-MM-DD）。           |
| `height_cm`           | INTEGER       |                 | 身高（厘米）。                           |
| `weight_kg`           | DECIMAL(5,2)  |                 | 体重（公斤）。                           |
| `contact`             | VARCHAR(20)   |                 | 联系方式。                               |
| `inpatient_number`    | VARCHAR(50)   |                 | 住院号（可选）。                         |
| `bed_number`          | VARCHAR(20)   |                 | 床号（可选）。                           |
| `education_level`     | VARCHAR(50)   |                 | 教育程度（可选）。                       |
| `education_years`     | INTEGER       |                 | 教育年数（可选）。                       |
| `remarks`             | TEXT          |                 | 备注信息（可选）。                       |
| `created_at`          | TIMESTAMPTZ   | DEFAULT NOW()   | 记录创建时间。                           |
| `updated_at`          | TIMESTAMPTZ   | DEFAULT NOW()   | 记录最后更新时间。                       |

### 示例数据

```sql
INSERT INTO patient_info (
    user_id, medical_record_number, id_last_six, name, gender, dominant_hand, 
    diagnosis, dob, height_cm, weight_kg, contact, inpatient_number, 
    bed_number, education_level, education_years, remarks
) VALUES (
    'user-abc123', 'J0012345', '123456', '张三', '男', '右',
    '无', '1960-01-15', 175, 70.5, '13800138000', 'Z67890',
    '503', '本科', 16, '测试患者'
);
```

## `logs_metadata` 表

用于记录所有历史日志表的元信息。管理员通过查询此表来获取历史日志列表。**此表在管理员登录时由系统自动更新。**

### 表结构 (SQL DDL)

```sql
CREATE TABLE logs_metadata (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
```

### 字段说明

| 字段名       | 类型        | 约束            | 说明                                     |
| ------------ | ----------- | --------------- | ---------------------------------------- |
| `id`         | SERIAL      | PRIMARY KEY     | 元数据记录的唯一标识。                   |
| `table_name` | VARCHAR(100)| UNIQUE, NOT NULL| 实际存储日志数据的表名。                 |
| `description`| TEXT        |                 | 对该日志的描述。                         |
| `created_at` | TIMESTAMPTZ | DEFAULT NOW()   | 记录创建的时间，默认为当前时间。         |
| `updated_at` | TIMESTAMPTZ | DEFAULT NOW()   | 记录最后更新时间。                       |

### 示例数据

```sql
INSERT INTO logs_metadata (table_name, description, created_at) VALUES 
('log_20240315_1', '2024年3月15日第一次交互日志', '2024-03-15T14:30:00Z'),
('log_20240315_2', '2024年3月15日第二次交互日志', '2024-03-15T16:30:00Z');
```

## 动态测试日志表 (`log_...` 表)

这种表由系统在管理员登录时动态创建，用于存储一个特定测试批次的所有用户测试数据。表名格式为 `log_YYYYMMDD_N` (例如 `log_20240401_1`)。

### 表结构 (SQL DDL)

```sql
CREATE TABLE log_YYYYMMDD_N (
    log_id SERIAL PRIMARY KEY,
    user_id VARCHAR(255) UNIQUE NOT NULL,
    medical_record_number VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(50) DEFAULT 'initialized',
    gait_analysis JSONB,
    facial_expression INTEGER,
    eye_tracking INTEGER,
    handwriting INTEGER,
    speech_analysis INTEGER,
    semantic_analysis INTEGER,
    raw_data_path VARCHAR(255)
);
```

### 字段说明

| 字段名                | 类型          | 说明                                                         |
| --------------------- | ------------- | ------------------------------------------------------------ |
| `log_id`              | SERIAL        | 本次测试日志的唯一ID，主键。                                 |
| `user_id`             | VARCHAR(255)  | 患者的用户ID，关联 `patient_info` 表。                      |
| `medical_record_number`| VARCHAR(100)  | 患者就诊号。                                                 |
| `name`                | VARCHAR(100)  | 患者姓名。                                                   |
| `created_at`          | TIMESTAMPTZ   | 记录创建时间。                                               |
| `status`              | VARCHAR(50)   | 测试日志的状态。默认为 `initialized` (已初始化)。流程: `initialized` -> `processing` (数据处理中) -> `pending_review` (待专家审核) -> `completed` (已完成确认)。 |
| `gait_analysis`       | JSONB         | 存储步态分析结果的JSON对象。                                 |
| `facial_expression`   | INTEGER       | 存储面部表情的UPDRS评级（0-4），可为空。                     |
| `eye_tracking`        | INTEGER       | 存储眼动功能的UPDRS评级（0-4），可为空。                     |
| `handwriting`         | INTEGER       | 存储手写功能的UPDRS评级（0-4），可为空。                     |
| `speech_analysis`     | INTEGER       | 存储言语功能的UPDRS评级（0-4），可为空。                     |
| `semantic_analysis`   | INTEGER       | 存储认知与语义连贯性的评级（0-4），可为空。                  |
| `raw_data_path`       | VARCHAR(255)  | 指向原始时序数据文件（如CSV）的路径。                        |


### 示例数据

```sql
-- 此表示例数据为概念性展示，实际数据结构嵌套在JSONB字段中
INSERT INTO log_20240401_1 (user_id, medical_record_number, name, status, gait_analysis, facial_expression) VALUES
('user-abc123', 'J0012345', '张三', 'completed', '{
    "procedural_events": [{"event": "stand_up", "frame": 85}],
    "walk_segments": [{"segment_id": 1, "gait_speed": 1.2}]
}', 2);
```

## `questions` 表

用于存储视频问卷的所有问题。

### 表结构 (SQL DDL)

```sql
CREATE TABLE questions (
    id SERIAL PRIMARY KEY,
    type VARCHAR(50) NOT NULL,
    content TEXT NOT NULL,
    required BOOLEAN DEFAULT TRUE,
    max_length INTEGER,
    -- 可以增加一个 category 字段，用于关联特定的视频或场景
    category VARCHAR(100)
);
```

### 字段说明

| 字段名     | 类型        | 说明                                               |
| ---------- | ----------- | -------------------------------------------------- |
| `id`       | SERIAL      | 问题的唯一ID。                                     |
| `type`     | VARCHAR(50) | 问题类型，例如 `text`。                            |
| `content`  | TEXT        | 问题的主要内容。                                   |
| `required` | BOOLEAN     | 该问题是否为必答题，默认为 `TRUE`。                |
| `max_length`| INTEGER     | 答案的最大长度限制。                               |
| `category` | VARCHAR(100)| 问题分类，可用于从特定类别的题库中抽题。           |

### 示例数据

```sql
INSERT INTO questions (type, content, required, max_length, category) VALUES
('text', '请描述视频中展示的主要问题是什么？', TRUE, 500, 'general'),
('text', '您认为应该如何解决这个问题？', TRUE, 1000, 'general'),
('text', '您对这个解决方案有什么建议？', FALSE, 800, 'general'),
('text', '视频中的场景让您联想到了什么？', TRUE, 500, 'emotion'),
('text', '您是否在现实生活中遇到过类似的情况？', FALSE, 1000, 'experience'),
('text', '如果您是视频中的主角，您会怎么做？', TRUE, 1000, 'decision'),
('text', '这个事件对您的触动是什么？', TRUE, 800, 'emotion'),
('text', '请用三个词来形容您的观看感受。', TRUE, 100, 'emotion');
```

## 用户类型说明

### 管理员用户 (user_info表)
- **注册方式**: 管理后台创建
- **登录方式**: 用户名 + 密码
- **权限**: 管理员权限，可查看所有测试记录
- **特点**: 使用bcrypt加密密码，支持邮箱验证

### 患者用户 (patient_info表)
- **注册方式**: 扫码自助注册
- **登录方式**: 就诊号 + 身份证后六位
- **权限**: 普通用户权限，可进行测试
- **特点**: 使用就诊号作为用户名，身份证后六位作为密码