-- 医学信息支持系统数据库初始化脚本
-- 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;




