233 lines
9.8 KiB
SQL
233 lines
9.8 KiB
SQL
-- 医学信息支持系统数据库初始化脚本
|
||
-- Database: medical_info_system
|
||
|
||
CREATE DATABASE IF NOT EXISTS medical_info_system
|
||
DEFAULT CHARACTER SET utf8mb4
|
||
DEFAULT COLLATE utf8mb4_unicode_ci;
|
||
|
||
USE medical_info_system;
|
||
|
||
-- 用户表
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
username VARCHAR(50) NOT NULL UNIQUE,
|
||
password VARCHAR(255) NOT NULL,
|
||
full_name VARCHAR(100) NOT NULL,
|
||
email VARCHAR(100),
|
||
role VARCHAR(20) NOT NULL COMMENT 'ADMIN, MEDICAL_SPECIALIST, REVIEWER',
|
||
enabled BOOLEAN DEFAULT TRUE,
|
||
created_at DATETIME NOT NULL,
|
||
last_login_at DATETIME,
|
||
INDEX idx_username (username),
|
||
INDEX idx_role (role)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 查询请求表
|
||
CREATE TABLE IF NOT EXISTS inquiry_requests (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
request_number VARCHAR(50) NOT NULL UNIQUE,
|
||
customer_name VARCHAR(100) NOT NULL,
|
||
customer_email VARCHAR(100),
|
||
customer_title VARCHAR(100),
|
||
inquiry_content TEXT,
|
||
keywords TEXT COMMENT '提取的关键词（JSON格式）',
|
||
status VARCHAR(20) NOT NULL COMMENT 'PENDING, KEYWORD_EXTRACTED, SEARCHING, SEARCH_COMPLETED, UNDER_REVIEW, DOWNLOADING, COMPLETED, REJECTED',
|
||
search_results TEXT COMMENT '检索结果（JSON格式）',
|
||
response_content TEXT COMMENT '回复内容',
|
||
assigned_to VARCHAR(50),
|
||
created_at DATETIME NOT NULL,
|
||
updated_at DATETIME,
|
||
completed_at DATETIME,
|
||
INDEX idx_request_number (request_number),
|
||
INDEX idx_status (status),
|
||
INDEX idx_customer_name (customer_name),
|
||
INDEX idx_assigned_to (assigned_to),
|
||
INDEX idx_created_at (created_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 知识库表
|
||
CREATE TABLE IF NOT EXISTS knowledge_bases (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
name VARCHAR(100) NOT NULL,
|
||
type VARCHAR(20) NOT NULL COMMENT 'INTERNAL, PUBLIC, EXTENDED',
|
||
description TEXT,
|
||
data_source VARCHAR(255),
|
||
priority INT COMMENT '检索优先级（数字越小优先级越高）',
|
||
enabled BOOLEAN DEFAULT TRUE,
|
||
configuration TEXT COMMENT '配置信息（JSON格式）',
|
||
created_at DATETIME NOT NULL,
|
||
updated_at DATETIME,
|
||
INDEX idx_type (type),
|
||
INDEX idx_enabled (enabled),
|
||
INDEX idx_priority (priority)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 文献表
|
||
CREATE TABLE IF NOT EXISTS literatures (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
inquiry_request_id BIGINT,
|
||
title VARCHAR(500) NOT NULL,
|
||
authors VARCHAR(500),
|
||
journal VARCHAR(200),
|
||
publication_date VARCHAR(50),
|
||
doi VARCHAR(100),
|
||
pmid VARCHAR(50),
|
||
abstract_text TEXT,
|
||
source_database VARCHAR(50),
|
||
source_url VARCHAR(500),
|
||
file_path VARCHAR(500),
|
||
download_status VARCHAR(20) COMMENT 'PENDING, DOWNLOADING, COMPLETED, FAILED',
|
||
selected BOOLEAN DEFAULT FALSE,
|
||
created_at DATETIME NOT NULL,
|
||
downloaded_at DATETIME,
|
||
INDEX idx_inquiry_request_id (inquiry_request_id),
|
||
INDEX idx_download_status (download_status),
|
||
INDEX idx_selected (selected),
|
||
FOREIGN KEY (inquiry_request_id) REFERENCES inquiry_requests(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 审核日志表
|
||
CREATE TABLE IF NOT EXISTS audit_logs (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
inquiry_request_id BIGINT,
|
||
user_id BIGINT,
|
||
action VARCHAR(20) NOT NULL COMMENT 'SUBMITTED, APPROVED, REJECTED, REVISION_REQUESTED, COMPLETED',
|
||
comments TEXT,
|
||
created_at DATETIME NOT NULL,
|
||
INDEX idx_inquiry_request_id (inquiry_request_id),
|
||
INDEX idx_user_id (user_id),
|
||
INDEX idx_created_at (created_at),
|
||
FOREIGN KEY (inquiry_request_id) REFERENCES inquiry_requests(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 药物表
|
||
CREATE TABLE IF NOT EXISTS drugs (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
drug_code VARCHAR(50) NOT NULL UNIQUE,
|
||
generic_name VARCHAR(200) NOT NULL,
|
||
trade_name VARCHAR(200),
|
||
active_ingredient VARCHAR(200),
|
||
description TEXT,
|
||
manufacturer VARCHAR(200),
|
||
approval_number VARCHAR(100),
|
||
indications TEXT,
|
||
dosage_and_administration TEXT,
|
||
contraindications TEXT,
|
||
therapeutic_class VARCHAR(100),
|
||
atc_code VARCHAR(20),
|
||
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' COMMENT 'ACTIVE, SUSPENDED, WITHDRAWN',
|
||
created_at DATETIME NOT NULL,
|
||
updated_at DATETIME,
|
||
INDEX idx_drug_code (drug_code),
|
||
INDEX idx_generic_name (generic_name),
|
||
INDEX idx_trade_name (trade_name),
|
||
INDEX idx_therapeutic_class (therapeutic_class),
|
||
INDEX idx_status (status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 药物安全信息表
|
||
CREATE TABLE IF NOT EXISTS drug_safety_infos (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
drug_id BIGINT NOT NULL,
|
||
source VARCHAR(30) NOT NULL COMMENT 'INTERNAL, LITERATURE, REGULATORY, SOCIAL_MEDIA, CLINICAL_TRIAL',
|
||
title VARCHAR(500) NOT NULL,
|
||
content TEXT,
|
||
category VARCHAR(50) NOT NULL COMMENT 'ADVERSE_REACTION, DRUG_INTERACTION, CONTRAINDICATION, etc.',
|
||
severity_level VARCHAR(20) COMMENT 'MILD, MODERATE, SEVERE, CRITICAL',
|
||
reference_url VARCHAR(500),
|
||
reference_document VARCHAR(500),
|
||
reported_by VARCHAR(100),
|
||
reported_at DATETIME,
|
||
additional_info TEXT COMMENT 'JSON格式的附加信息',
|
||
verified BOOLEAN DEFAULT FALSE,
|
||
verified_by VARCHAR(100),
|
||
verified_at DATETIME,
|
||
created_at DATETIME NOT NULL,
|
||
updated_at DATETIME,
|
||
INDEX idx_drug_id (drug_id),
|
||
INDEX idx_source (source),
|
||
INDEX idx_category (category),
|
||
INDEX idx_severity_level (severity_level),
|
||
INDEX idx_verified (verified),
|
||
FOREIGN KEY (drug_id) REFERENCES drugs(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 临床试验表
|
||
CREATE TABLE IF NOT EXISTS clinical_trials (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
inquiry_id BIGINT COMMENT '关联的查询请求ID',
|
||
nct_id VARCHAR(50) NOT NULL COMMENT 'ClinicalTrials.gov唯一标识',
|
||
study_title TEXT COMMENT '研究标题',
|
||
brief_title VARCHAR(500) COMMENT '简短标题',
|
||
official_title TEXT COMMENT '官方标题',
|
||
overall_status VARCHAR(50) COMMENT '研究状态',
|
||
start_date VARCHAR(50) COMMENT '开始日期',
|
||
completion_date VARCHAR(50) COMMENT '完成日期',
|
||
study_type VARCHAR(50) COMMENT '研究类型',
|
||
phase VARCHAR(50) COMMENT '研究阶段',
|
||
enrollment INT COMMENT '入组人数',
|
||
conditions TEXT COMMENT '适应症（JSON数组）',
|
||
interventions TEXT COMMENT '干预措施（JSON数组）',
|
||
sponsor VARCHAR(500) COMMENT '主要研究者/机构',
|
||
collaborators TEXT COMMENT '合作者（JSON数组）',
|
||
locations TEXT COMMENT '研究地点（JSON数组）',
|
||
brief_summary TEXT COMMENT '简要摘要',
|
||
detailed_description LONGTEXT COMMENT '详细描述',
|
||
primary_outcome TEXT COMMENT '主要终点',
|
||
secondary_outcome TEXT COMMENT '次要终点',
|
||
eligibility_criteria TEXT COMMENT '入选标准',
|
||
url VARCHAR(500) COMMENT 'ClinicalTrials.gov链接',
|
||
raw_data LONGTEXT COMMENT '原始API返回数据（JSON格式）',
|
||
created_at DATETIME NOT NULL,
|
||
updated_at DATETIME,
|
||
INDEX idx_inquiry_id (inquiry_id),
|
||
INDEX idx_nct_id (nct_id),
|
||
INDEX idx_overall_status (overall_status),
|
||
INDEX idx_phase (phase),
|
||
INDEX idx_created_at (created_at),
|
||
UNIQUE KEY uk_inquiry_nct (inquiry_id, nct_id),
|
||
FOREIGN KEY (inquiry_id) REFERENCES inquiry_requests(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- RPA 任务表
|
||
CREATE TABLE IF NOT EXISTS rpa_task (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
inquiry_id BIGINT NOT NULL COMMENT '关联的查询请求ID',
|
||
source VARCHAR(32) NOT NULL COMMENT '数据源: CNKI, WANFANG',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '任务状态: PENDING, RUNNING, COMPLETED, FAILED',
|
||
query_expression VARCHAR(2000) NOT NULL COMMENT '检索表达式',
|
||
remark VARCHAR(1024) COMMENT '备注信息（如失败原因）',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
started_at DATETIME COMMENT '开始执行时间',
|
||
finished_at DATETIME COMMENT '完成时间',
|
||
INDEX idx_inquiry_id (inquiry_id),
|
||
INDEX idx_source (source),
|
||
INDEX idx_status (status),
|
||
INDEX idx_created_at (created_at),
|
||
INDEX idx_source_status (source, status),
|
||
FOREIGN KEY (inquiry_id) REFERENCES inquiry_requests(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
||
COMMENT='RPA自动化检索任务表';
|
||
|
||
-- 插入默认管理员用户 (密码: admin123, 实际使用时应该加密)
|
||
INSERT INTO users (username, password, full_name, email, role, enabled, created_at)
|
||
VALUES ('admin', '$2a$10$rK8WpQYJzxJ8Y5X5YqFvRO5K7K5K7K5K7K5K7K5K7K5K7K5K7K5K7',
|
||
'系统管理员', 'admin@ipsen.com', 'ADMIN', TRUE, NOW())
|
||
ON DUPLICATE KEY UPDATE username=username;
|
||
|
||
-- 插入示例知识库配置
|
||
INSERT INTO knowledge_bases (name, type, description, data_source, priority, enabled, created_at)
|
||
VALUES
|
||
('企业研究数据库', 'INTERNAL', '益普生内部研究数据和历史回复', 'internal_db', 1, TRUE, NOW()),
|
||
('PubMed', 'PUBLIC', '美国国家医学图书馆公开数据库', 'https://pubmed.ncbi.nlm.nih.gov', 2, TRUE, NOW()),
|
||
('EMBASE', 'PUBLIC', 'Elsevier医学文献数据库', 'https://www.embase.com', 3, TRUE, NOW()),
|
||
('中国知网', 'PUBLIC', '中国学术期刊数据库', 'https://www.cnki.net', 4, TRUE, NOW()),
|
||
('ClinicalTrials.gov', 'PUBLIC', '临床试验注册数据库', 'https://clinicaltrials.gov', 5, TRUE, NOW()),
|
||
('疾病药物关联库', 'EXTENDED', '疾病与药物关联扩展数据', 'extended_db', 6, TRUE, NOW())
|
||
ON DUPLICATE KEY UPDATE name=name;
|
||
|
||
|
||
|
||
|