InnoResCompMS/数据库设计文档.sql

-- =====================================================
-- 科研创新与学科竞赛综合管理系统数据库设计
-- 版本: v1.0
-- 创建日期: 2024年
-- 数据库: MySQL 8.0
-- 字符集: utf8mb4
-- 排序规则: utf8mb4_unicode_ci
-- =====================================================

-- 创建数据库
CREATE DATABASE IF NOT EXISTS inno_res_comp_ms 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE inno_res_comp_ms;

-- =====================================================
-- 1. 用户管理模块表结构
-- =====================================================

-- 用户基础信息表
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码(加密)',
    email VARCHAR(100) UNIQUE COMMENT '邮箱',
    phone VARCHAR(20) COMMENT '手机号',
    real_name VARCHAR(50) NOT NULL COMMENT '真实姓名',
    avatar_url VARCHAR(500) COMMENT '头像URL',
    gender TINYINT DEFAULT 0 COMMENT '性别: 0-未知, 1-男, 2-女',
    birth_date DATE COMMENT '出生日期',
    id_card VARCHAR(18) COMMENT '身份证号',
    department_id BIGINT COMMENT '所属部门ID',
    status TINYINT DEFAULT 1 COMMENT '状态: 0-禁用, 1-启用',
    last_login_time DATETIME COMMENT '最后登录时间',
    last_login_ip VARCHAR(45) COMMENT '最后登录IP',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_phone (phone),
    INDEX idx_department (department_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) COMMENT '用户基础信息表';

-- 角色表
CREATE TABLE roles (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '角色ID',
    role_name VARCHAR(50) NOT NULL COMMENT '角色名称',
    role_code VARCHAR(50) NOT NULL UNIQUE COMMENT '角色编码',
    description TEXT COMMENT '角色描述',
    permissions JSON COMMENT '权限列表(JSON格式)',
    is_system TINYINT DEFAULT 0 COMMENT '是否系统角色: 0-否, 1-是',
    status TINYINT DEFAULT 1 COMMENT '状态: 0-禁用, 1-启用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_role_code (role_code),
    INDEX idx_status (status)
) COMMENT '角色表';

-- 用户角色关联表
CREATE TABLE user_roles (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '关联ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    role_id BIGINT NOT NULL COMMENT '角色ID',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    UNIQUE KEY uk_user_role (user_id, role_id),
    INDEX idx_user_id (user_id),
    INDEX idx_role_id (role_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) COMMENT '用户角色关联表';

-- 部门表
CREATE TABLE departments (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID',
    name VARCHAR(100) NOT NULL COMMENT '部门名称',
    code VARCHAR(50) UNIQUE COMMENT '部门编码',
    parent_id BIGINT DEFAULT 0 COMMENT '父部门ID',
    level TINYINT DEFAULT 1 COMMENT '部门层级',
    sort_order INT DEFAULT 0 COMMENT '排序',
    manager_id BIGINT COMMENT '部门负责人ID',
    description TEXT COMMENT '部门描述',
    status TINYINT DEFAULT 1 COMMENT '状态: 0-禁用, 1-启用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_parent_id (parent_id),
    INDEX idx_code (code),
    INDEX idx_manager_id (manager_id),
    INDEX idx_status (status)
) COMMENT '部门表';

-- =====================================================
-- 2. 产教融合模块表结构
-- =====================================================

-- 企业信息表
CREATE TABLE enterprises (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '企业ID',
    name VARCHAR(200) NOT NULL COMMENT '企业名称',
    short_name VARCHAR(100) COMMENT '企业简称',
    unified_social_credit_code VARCHAR(18) UNIQUE COMMENT '统一社会信用代码',
    enterprise_type TINYINT NOT NULL COMMENT '企业类型: 1-国企, 2-民企, 3-外企, 4-合资',
    industry VARCHAR(100) COMMENT '所属行业',
    scale TINYINT COMMENT '企业规模: 1-大型, 2-中型, 3-小型, 4-微型',
    contact_person VARCHAR(50) COMMENT '联系人',
    contact_phone VARCHAR(20) COMMENT '联系电话',
    contact_email VARCHAR(100) COMMENT '联系邮箱',
    address VARCHAR(500) COMMENT '企业地址',
    website VARCHAR(200) COMMENT '企业官网',
    business_scope TEXT COMMENT '经营范围',
    tags JSON COMMENT '企业标签(技术领域、设备类型等)',
    cooperation_history JSON COMMENT '合作历史记录',
    credit_rating TINYINT DEFAULT 5 COMMENT '信用评级(1-10)',
    status TINYINT DEFAULT 1 COMMENT '状态: 0-禁用, 1-启用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_name (name),
    INDEX idx_industry (industry),
    INDEX idx_enterprise_type (enterprise_type),
    INDEX idx_scale (scale),
    INDEX idx_status (status),
    INDEX idx_credit_rating (credit_rating)
) COMMENT '企业信息表';

-- 校企合作项目表
CREATE TABLE cooperation_projects (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '项目ID',
    project_name VARCHAR(200) NOT NULL COMMENT '项目名称',
    project_code VARCHAR(50) UNIQUE COMMENT '项目编号',
    enterprise_id BIGINT NOT NULL COMMENT '合作企业ID',
    department_id BIGINT NOT NULL COMMENT '学校部门ID',
    project_type TINYINT NOT NULL COMMENT '项目类型: 1-技术开发, 2-人才培养, 3-实习实训, 4-成果转化',
    cooperation_mode TINYINT NOT NULL COMMENT '合作模式: 1-委托开发, 2-合作开发, 3-技术服务, 4-人才交流',
    project_leader_id BIGINT COMMENT '项目负责人ID',
    enterprise_contact_id BIGINT COMMENT '企业联系人ID',
    start_date DATE NOT NULL COMMENT '开始日期',
    end_date DATE NOT NULL COMMENT '结束日期',
    budget DECIMAL(15,2) DEFAULT 0 COMMENT '项目预算',
    actual_amount DECIMAL(15,2) DEFAULT 0 COMMENT '实际金额',
    project_description TEXT COMMENT '项目描述',
    objectives TEXT COMMENT '项目目标',
    deliverables TEXT COMMENT '交付成果',
    risk_assessment TEXT COMMENT '风险评估',
    progress_status TINYINT DEFAULT 1 COMMENT '进度状态: 1-立项, 2-进行中, 3-验收, 4-完成, 5-暂停, 6-终止',
    quality_score DECIMAL(3,1) DEFAULT 0 COMMENT '质量评分(0-10)',
    satisfaction_score DECIMAL(3,1) DEFAULT 0 COMMENT '满意度评分(0-10)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_project_code (project_code),
    INDEX idx_enterprise_id (enterprise_id),
    INDEX idx_department_id (department_id),
    INDEX idx_project_type (project_type),
    INDEX idx_progress_status (progress_status),
    INDEX idx_start_date (start_date),
    INDEX idx_end_date (end_date),
    FOREIGN KEY (enterprise_id) REFERENCES enterprises(id),
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (project_leader_id) REFERENCES users(id)
) COMMENT '校企合作项目表';

-- 技术成熟度评估表
CREATE TABLE tech_maturity_assessments (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '评估ID',
    project_id BIGINT NOT NULL COMMENT '项目ID',
    assessor_id BIGINT NOT NULL COMMENT '评估人ID',
    assessment_round TINYINT DEFAULT 1 COMMENT '评估轮次',
    technology_readiness_level TINYINT NOT NULL COMMENT '技术成熟度等级(1-9)',
    market_readiness_score DECIMAL(3,1) COMMENT '市场成熟度评分(0-10)',
    commercial_potential_score DECIMAL(3,1) COMMENT '商业化潜力评分(0-10)',
    risk_level TINYINT COMMENT '风险等级: 1-低, 2-中, 3-高',
    assessment_content TEXT COMMENT '评估内容',
    improvement_suggestions TEXT COMMENT '改进建议',
    next_milestone TEXT COMMENT '下一里程碑',
    assessment_date DATE NOT NULL COMMENT '评估日期',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_project_id (project_id),
    INDEX idx_assessor_id (assessor_id),
    INDEX idx_assessment_date (assessment_date),
    INDEX idx_trl (technology_readiness_level),
    FOREIGN KEY (project_id) REFERENCES cooperation_projects(id),
    FOREIGN KEY (assessor_id) REFERENCES users(id)
) COMMENT '技术成熟度评估表';

-- 路演匹配表
CREATE TABLE roadshow_matches (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '匹配ID',
    project_id BIGINT NOT NULL COMMENT '项目ID',
    investor_type TINYINT NOT NULL COMMENT '投资机构类型: 1-天使投资, 2-VC, 3-PE, 4-产业基金',
    investor_name VARCHAR(200) COMMENT '投资机构名称',
    contact_person VARCHAR(50) COMMENT '联系人',
    contact_info VARCHAR(200) COMMENT '联系方式',
    match_score DECIMAL(3,1) COMMENT '匹配度评分(0-10)',
    match_reason TEXT COMMENT '匹配原因',
    roadshow_date DATETIME COMMENT '路演时间',
    roadshow_result TINYINT COMMENT '路演结果: 1-通过, 2-待定, 3-拒绝',
    feedback TEXT COMMENT '反馈意见',
    follow_up_actions TEXT COMMENT '后续行动',
    status TINYINT DEFAULT 1 COMMENT '状态: 1-待路演, 2-已路演, 3-已签约, 4-已拒绝',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_project_id (project_id),
    INDEX idx_investor_type (investor_type),
    INDEX idx_roadshow_date (roadshow_date),
    INDEX idx_status (status),
    FOREIGN KEY (project_id) REFERENCES cooperation_projects(id)
) COMMENT '路演匹配表';

-- =====================================================
-- 3. 学科竞赛模块表结构
-- =====================================================

-- 竞赛信息表
CREATE TABLE competitions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '竞赛ID',
    name VARCHAR(200) NOT NULL COMMENT '竞赛名称',
    english_name VARCHAR(200) COMMENT '英文名称',
    competition_code VARCHAR(50) UNIQUE COMMENT '竞赛编号',
    competition_type TINYINT NOT NULL COMMENT '竞赛类型: 1-学科竞赛, 2-创新创业, 3-技能竞赛, 4-文体竞赛',
    level TINYINT NOT NULL COMMENT '竞赛级别: 1-国际级, 2-国家级, 3-省部级, 4-市厅级, 5-校级',
    category VARCHAR(100) COMMENT '竞赛类别',
    organizer VARCHAR(200) COMMENT '主办单位',
    co_organizer VARCHAR(500) COMMENT '协办单位',
    competition_year YEAR NOT NULL COMMENT '竞赛年度',
    registration_start_date DATE COMMENT '报名开始日期',
    registration_end_date DATE COMMENT '报名结束日期',
    competition_start_date DATE COMMENT '竞赛开始日期',
    competition_end_date DATE COMMENT '竞赛结束日期',
    venue VARCHAR(200) COMMENT '竞赛地点',
    official_website VARCHAR(200) COMMENT '官方网站',
    description TEXT COMMENT '竞赛描述',
    rules_document_url VARCHAR(500) COMMENT '竞赛规则文档URL',
    prize_setting TEXT COMMENT '奖项设置',
    registration_fee DECIMAL(10,2) DEFAULT 0 COMMENT '报名费用',
    max_team_size INT DEFAULT 1 COMMENT '最大团队人数',
    status TINYINT DEFAULT 1 COMMENT '状态: 0-禁用, 1-启用, 2-已结束',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_competition_code (competition_code),
    INDEX idx_competition_type (competition_type),
    INDEX idx_level (level),
    INDEX idx_competition_year (competition_year),
    INDEX idx_status (status),
    INDEX idx_registration_dates (registration_start_date, registration_end_date),
    INDEX idx_competition_dates (competition_start_date, competition_end_date)
) COMMENT '竞赛信息表';

-- 获奖记录表
CREATE TABLE awards (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '获奖ID',
    competition_id BIGINT NOT NULL COMMENT '竞赛ID',
    award_name VARCHAR(200) NOT NULL COMMENT '获奖名称',
    award_level TINYINT NOT NULL COMMENT '获奖等级: 1-特等奖, 2-一等奖, 3-二等奖, 4-三等奖, 5-优秀奖, 6-其他',
    team_name VARCHAR(200) COMMENT '团队名称',
    is_team TINYINT DEFAULT 0 COMMENT '是否团队: 0-个人, 1-团队',
    team_leader_id BIGINT COMMENT '团队负责人ID',
    team_members JSON COMMENT '团队成员ID列表',
    instructor_id BIGINT COMMENT '指导教师ID',
    co_instructors JSON COMMENT '协助指导教师ID列表',
    work_title VARCHAR(200) COMMENT '作品标题',
    work_description TEXT COMMENT '作品描述',
    certificate_number VARCHAR(100) COMMENT '证书编号',
    certificate_url VARCHAR(500) COMMENT '证书文件URL',
    supporting_materials JSON COMMENT '佐证材料URL列表',
    award_date DATE NOT NULL COMMENT '获奖日期',
    points DECIMAL(5,2) DEFAULT 0 COMMENT '获奖积分',
    bonus_amount DECIMAL(10,2) DEFAULT 0 COMMENT '奖金金额',
    publicity_period_start DATE COMMENT '公示期开始',
    publicity_period_end DATE COMMENT '公示期结束',
    audit_status TINYINT DEFAULT 0 COMMENT '审核状态: 0-待审核, 1-审核通过, 2-审核驳回, 3-需要补充材料',
    audit_comment TEXT COMMENT '审核意见',
    auditor_id BIGINT COMMENT '审核人ID',
    audit_time DATETIME COMMENT '审核时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_competition_id (competition_id),
    INDEX idx_award_level (award_level),
    INDEX idx_team_leader_id (team_leader_id),
    INDEX idx_instructor_id (instructor_id),
    INDEX idx_award_date (award_date),
    INDEX idx_audit_status (audit_status),
    INDEX idx_auditor_id (auditor_id),
    FOREIGN KEY (competition_id) REFERENCES competitions(id),
    FOREIGN KEY (team_leader_id) REFERENCES users(id),
    FOREIGN KEY (instructor_id) REFERENCES users(id),
    FOREIGN KEY (auditor_id) REFERENCES users(id)
) COMMENT '获奖记录表';

-- 审核记录表
CREATE TABLE audit_records (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '审核记录ID',
    award_id BIGINT NOT NULL COMMENT '获奖记录ID',
    auditor_id BIGINT NOT NULL COMMENT '审核人ID',
    audit_action TINYINT NOT NULL COMMENT '审核动作: 1-提交审核, 2-审核通过, 3-审核驳回, 4-要求补充',
    audit_status_before TINYINT COMMENT '审核前状态',
    audit_status_after TINYINT COMMENT '审核后状态',
    audit_comment TEXT COMMENT '审核意见',
    required_materials TEXT COMMENT '要求补充的材料',
    audit_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '审核时间',
    ip_address VARCHAR(45) COMMENT '审核IP地址',
    INDEX idx_award_id (award_id),
    INDEX idx_auditor_id (auditor_id),
    INDEX idx_audit_time (audit_time),
    FOREIGN KEY (award_id) REFERENCES awards(id),
    FOREIGN KEY (auditor_id) REFERENCES users(id)
) COMMENT '审核记录表';

-- =====================================================
-- 4. 实验室管理模块表结构
-- =====================================================

-- 实验室信息表
CREATE TABLE laboratories (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '实验室ID',
    name VARCHAR(200) NOT NULL COMMENT '实验室名称',
    lab_code VARCHAR(50) UNIQUE COMMENT '实验室编号',
    lab_type TINYINT NOT NULL COMMENT '实验室类型: 1-教学实验室, 2-科研实验室, 3-开放实验室, 4-虚拟实验室',
    department_id BIGINT NOT NULL COMMENT '所属部门ID',
    building VARCHAR(100) COMMENT '所在建筑',
    floor VARCHAR(20) COMMENT '楼层',
    room_number VARCHAR(50) COMMENT '房间号',
    area DECIMAL(8,2) COMMENT '面积(平方米)',
    capacity INT DEFAULT 0 COMMENT '容纳人数',
    manager_id BIGINT COMMENT '实验室负责人ID',
    assistant_managers JSON COMMENT '实验室管理员ID列表',
    equipment_count INT DEFAULT 0 COMMENT '设备数量',
    total_value DECIMAL(15,2) DEFAULT 0 COMMENT '设备总价值',
    safety_level TINYINT DEFAULT 1 COMMENT '安全等级: 1-一般, 2-较高, 3-高',
    access_control TINYINT DEFAULT 1 COMMENT '门禁控制: 0-无, 1-刷卡, 2-指纹, 3-人脸识别',
    opening_hours VARCHAR(200) COMMENT '开放时间',
    booking_required TINYINT DEFAULT 1 COMMENT '是否需要预约: 0-否, 1-是',
    description TEXT COMMENT '实验室描述',
    rules TEXT COMMENT '使用规则',
    emergency_contact VARCHAR(200) COMMENT '紧急联系方式',
    status TINYINT DEFAULT 1 COMMENT '状态: 0-停用, 1-正常, 2-维护中, 3-装修中',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_lab_code (lab_code),
    INDEX idx_lab_type (lab_type),
    INDEX idx_department_id (department_id),
    INDEX idx_manager_id (manager_id),
    INDEX idx_status (status),
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (manager_id) REFERENCES users(id)
) COMMENT '实验室信息表';

-- 设备信息表
CREATE TABLE equipment (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '设备ID',
    lab_id BIGINT NOT NULL COMMENT '所属实验室ID',
    equipment_name VARCHAR(200) NOT NULL COMMENT '设备名称',
    equipment_code VARCHAR(50) UNIQUE COMMENT '设备编号',
    equipment_type TINYINT NOT NULL COMMENT '设备类型: 1-计算机, 2-测量仪器, 3-实验装置, 4-其他',
    brand VARCHAR(100) COMMENT '品牌',
    model VARCHAR(100) COMMENT '型号',
    specifications TEXT COMMENT '技术规格',
    purchase_date DATE COMMENT '购买日期',
    purchase_price DECIMAL(12,2) COMMENT '购买价格',
    supplier VARCHAR(200) COMMENT '供应商',
    warranty_period INT COMMENT '保修期(月)',
    warranty_end_date DATE COMMENT '保修到期日期',
    depreciation_years INT DEFAULT 5 COMMENT '折旧年限',
    current_value DECIMAL(12,2) COMMENT '当前价值',
    location VARCHAR(200) COMMENT '存放位置',
    responsible_person_id BIGINT COMMENT '责任人ID',
    usage_instructions TEXT COMMENT '使用说明',
    maintenance_cycle INT COMMENT '维护周期(天)',
    last_maintenance_date DATE COMMENT '上次维护日期',
    next_maintenance_date DATE COMMENT '下次维护日期',
    maintenance_records JSON COMMENT '维护记录',
    remote_controllable TINYINT DEFAULT 0 COMMENT '是否支持远程控制: 0-否, 1-是',
    remote_control_url VARCHAR(500) COMMENT '远程控制地址',
    network_status TINYINT DEFAULT 0 COMMENT '网络状态: 0-离线, 1-在线',
    power_status TINYINT DEFAULT 0 COMMENT '电源状态: 0-关闭, 1-开启',
    usage_status TINYINT DEFAULT 0 COMMENT '使用状态: 0-空闲, 1-使用中, 2-故障, 3-维护中',
    booking_required TINYINT DEFAULT 1 COMMENT '是否需要预约: 0-否, 1-是',
    max_booking_duration INT DEFAULT 240 COMMENT '最大预约时长(分钟)',
    status TINYINT DEFAULT 1 COMMENT '设备状态: 0-报废, 1-正常, 2-故障, 3-维修中',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_equipment_code (equipment_code),
    INDEX idx_lab_id (lab_id),
    INDEX idx_equipment_type (equipment_type),
    INDEX idx_responsible_person_id (responsible_person_id),
    INDEX idx_usage_status (usage_status),
    INDEX idx_status (status),
    INDEX idx_network_status (network_status),
    FOREIGN KEY (lab_id) REFERENCES laboratories(id),
    FOREIGN KEY (responsible_person_id) REFERENCES users(id)
) COMMENT '设备信息表';

-- 设备借用记录表
CREATE TABLE equipment_borrowings (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '借用记录ID',
    equipment_id BIGINT NOT NULL COMMENT '设备ID',
    borrower_id BIGINT NOT NULL COMMENT '借用人ID',
    borrower_type TINYINT NOT NULL COMMENT '借用人类型: 1-学生, 2-教师, 3-外部人员',
    purpose TEXT NOT NULL COMMENT '借用目的',
    project_id BIGINT COMMENT '关联项目ID',
    course_id BIGINT COMMENT '关联课程ID',
    planned_start_time DATETIME NOT NULL COMMENT '计划开始时间',
    planned_end_time DATETIME NOT NULL COMMENT '计划结束时间',
    actual_start_time DATETIME COMMENT '实际开始时间',
    actual_end_time DATETIME COMMENT '实际结束时间',
    approver_id BIGINT COMMENT '审批人ID',
    approval_time DATETIME COMMENT '审批时间',
    approval_comment TEXT COMMENT '审批意见',
    usage_notes TEXT COMMENT '使用说明',
    return_condition TEXT COMMENT '归还状态说明',
    damage_description TEXT COMMENT '损坏描述',
    compensation_amount DECIMAL(10,2) DEFAULT 0 COMMENT '赔偿金额',
    borrowing_status TINYINT DEFAULT 1 COMMENT '借用状态: 1-申请中, 2-已批准, 3-使用中, 4-已归还, 5-逾期, 6-已拒绝',
    reminder_sent TINYINT DEFAULT 0 COMMENT '是否已发送提醒: 0-否, 1-是',
    rating TINYINT COMMENT '使用评价(1-5星)',
    feedback TEXT COMMENT '使用反馈',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_equipment_id (equipment_id),
    INDEX idx_borrower_id (borrower_id),
    INDEX idx_approver_id (approver_id),
    INDEX idx_borrowing_status (borrowing_status),
    INDEX idx_planned_times (planned_start_time, planned_end_time),
    INDEX idx_actual_times (actual_start_time, actual_end_time),
    FOREIGN KEY (equipment_id) REFERENCES equipment(id),
    FOREIGN KEY (borrower_id) REFERENCES users(id),
    FOREIGN KEY (approver_id) REFERENCES users(id)
) COMMENT '设备借用记录表';

-- 远程控制日志表
CREATE TABLE remote_control_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '日志ID',
    equipment_id BIGINT NOT NULL COMMENT '设备ID',
    operator_id BIGINT NOT NULL COMMENT '操作人ID',
    operation_type TINYINT NOT NULL COMMENT '操作类型: 1-开机, 2-关机, 3-重启, 4-状态查询, 5-其他',
    operation_command VARCHAR(500) COMMENT '操作命令',
    operation_result TINYINT COMMENT '操作结果: 0-失败, 1-成功',
    result_message TEXT COMMENT '结果消息',
    ip_address VARCHAR(45) COMMENT '操作IP地址',
    user_agent VARCHAR(500) COMMENT '用户代理',
    operation_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
    INDEX idx_equipment_id (equipment_id),
    INDEX idx_operator_id (operator_id),
    INDEX idx_operation_type (operation_type),
    INDEX idx_operation_time (operation_time),
    FOREIGN KEY (equipment_id) REFERENCES equipment(id),
    FOREIGN KEY (operator_id) REFERENCES users(id)
) COMMENT '远程控制日志表';

-- =====================================================
-- 5. 工作室建设与管理模块表结构
-- =====================================================

-- 学生技能标签表
CREATE TABLE student_skills (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '技能ID',
    student_id BIGINT NOT NULL COMMENT '学生ID',
    skill_category TINYINT NOT NULL COMMENT '技能类别: 1-编程语言, 2-开发框架, 3-工具软件, 4-专业技能, 5-软技能',
    skill_name VARCHAR(100) NOT NULL COMMENT '技能名称',
    skill_level TINYINT NOT NULL COMMENT '技能水平: 1-初级, 2-中级, 3-高级, 4-专家',
    proficiency_score DECIMAL(3,1) COMMENT '熟练度评分(0-10)',
    certification_name VARCHAR(200) COMMENT '认证名称',
    certification_url VARCHAR(500) COMMENT '认证证书URL',
    certification_date DATE COMMENT '认证日期',
    certification_expiry DATE COMMENT '认证到期日期',
    self_assessment TEXT COMMENT '自我评价',
    teacher_assessment TEXT COMMENT '教师评价',
    project_experience TEXT COMMENT '项目经验',
    learning_resources TEXT COMMENT '学习资源',
    improvement_plan TEXT COMMENT '提升计划',
    last_used_date DATE COMMENT '最后使用日期',
    usage_frequency TINYINT COMMENT '使用频率: 1-很少, 2-偶尔, 3-经常, 4-每天',
    is_verified TINYINT DEFAULT 0 COMMENT '是否已验证: 0-否, 1-是',
    verifier_id BIGINT COMMENT '验证人ID',
    verification_date DATE COMMENT '验证日期',
    status TINYINT DEFAULT 1 COMMENT '状态: 0-已删除, 1-有效',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_student_id (student_id),
    INDEX idx_skill_category (skill_category),
    INDEX idx_skill_name (skill_name),
    INDEX idx_skill_level (skill_level),
    INDEX idx_is_verified (is_verified),
    INDEX idx_status (status),
    FOREIGN KEY (student_id) REFERENCES users(id),
    FOREIGN KEY (verifier_id) REFERENCES users(id)
) COMMENT '学生技能标签表';

-- 学生可用时间表
CREATE TABLE student_availability (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '可用时间ID',
    student_id BIGINT NOT NULL COMMENT '学生ID',
    semester VARCHAR(20) NOT NULL COMMENT '学期',
    week_day TINYINT NOT NULL COMMENT '星期几(1-7)',
    start_time TIME NOT NULL COMMENT '开始时间',
    end_time TIME NOT NULL COMMENT '结束时间',
    availability_type TINYINT NOT NULL COMMENT '时间类型: 1-空闲, 2-课程, 3-项目, 4-其他',
    description VARCHAR(200) COMMENT '描述',
    is_flexible TINYINT DEFAULT 1 COMMENT '是否灵活: 0-固定, 1-灵活',
    priority TINYINT DEFAULT 3 COMMENT '优先级: 1-低, 2-中, 3-高',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_student_id (student_id),
    INDEX idx_semester (semester),
    INDEX idx_week_day (week_day),
    INDEX idx_availability_type (availability_type),
    FOREIGN KEY (student_id) REFERENCES users(id)
) COMMENT '学生可用时间表';

-- 项目信息表
CREATE TABLE projects (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '项目ID',
    project_name VARCHAR(200) NOT NULL COMMENT '项目名称',
    project_code VARCHAR(50) UNIQUE COMMENT '项目编号',
    project_type TINYINT NOT NULL COMMENT '项目类型: 1-科研项目, 2-竞赛项目, 3-实训项目, 4-创业项目',
    project_category VARCHAR(100) COMMENT '项目类别',
    teacher_id BIGINT NOT NULL COMMENT '指导教师ID',
    co_teachers JSON COMMENT '协助教师ID列表',
    department_id BIGINT COMMENT '所属部门ID',
    project_description TEXT COMMENT '项目描述',
    objectives TEXT COMMENT '项目目标',
    expected_outcomes TEXT COMMENT '预期成果',
    technical_requirements TEXT COMMENT '技术要求',
    skill_requirements JSON COMMENT '技能要求',
    team_size_min INT DEFAULT 1 COMMENT '最小团队人数',
    team_size_max INT DEFAULT 10 COMMENT '最大团队人数',
    difficulty_level TINYINT NOT NULL COMMENT '难度等级: 1-初级, 2-中级, 3-高级, 4-专家',
    estimated_duration INT COMMENT '预计持续时间(天)',
    start_date DATE COMMENT '开始日期',
    end_date DATE COMMENT '结束日期',
    budget DECIMAL(12,2) DEFAULT 0 COMMENT '项目预算',
    funding_source VARCHAR(200) COMMENT '资金来源',
    recruitment_status TINYINT DEFAULT 1 COMMENT '招募状态: 0-未开始, 1-招募中, 2-已满员, 3-已结束',
    project_status TINYINT DEFAULT 1 COMMENT '项目状态: 1-立项, 2-进行中, 3-暂停, 4-完成, 5-终止',
    progress_percentage DECIMAL(5,2) DEFAULT 0 COMMENT '进度百分比',
    health_status TINYINT DEFAULT 1 COMMENT '健康状态: 1-健康, 2-风险, 3-问题',
    quality_score DECIMAL(3,1) DEFAULT 0 COMMENT '质量评分(0-10)',
    innovation_score DECIMAL(3,1) DEFAULT 0 COMMENT '创新性评分(0-10)',
    practical_score DECIMAL(3,1) DEFAULT 0 COMMENT '实用性评分(0-10)',
    final_score DECIMAL(3,1) DEFAULT 0 COMMENT '最终评分(0-10)',
    achievements TEXT COMMENT '项目成果',
    lessons_learned TEXT COMMENT '经验教训',
    next_steps TEXT COMMENT '后续计划',
    visibility TINYINT DEFAULT 1 COMMENT '可见性: 0-私有, 1-公开, 2-部门内',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_project_code (project_code),
    INDEX idx_project_type (project_type),
    INDEX idx_teacher_id (teacher_id),
    INDEX idx_department_id (department_id),
    INDEX idx_recruitment_status (recruitment_status),
    INDEX idx_project_status (project_status),
    INDEX idx_difficulty_level (difficulty_level),
    INDEX idx_start_date (start_date),
    INDEX idx_end_date (end_date),
    FOREIGN KEY (teacher_id) REFERENCES users(id),
    FOREIGN KEY (department_id) REFERENCES departments(id)
) COMMENT '项目信息表';

-- 项目成员表
CREATE TABLE project_members (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '成员ID',
    project_id BIGINT NOT NULL COMMENT '项目ID',
    student_id BIGINT NOT NULL COMMENT '学生ID',
    member_role TINYINT NOT NULL COMMENT '成员角色: 1-组长, 2-核心成员, 3-普通成员, 4-实习成员',
    responsibilities TEXT COMMENT '职责描述',
    join_date DATE NOT NULL COMMENT '加入日期',
    leave_date DATE COMMENT '离开日期',
    planned_workload DECIMAL(5,2) DEFAULT 0 COMMENT '计划工作量(小时/周)',
    actual_workload DECIMAL(5,2) DEFAULT 0 COMMENT '实际工作量(小时/周)',
    contribution_rate DECIMAL(5,2) DEFAULT 0 COMMENT '贡献度百分比',
    performance_score DECIMAL(3,1) DEFAULT 0 COMMENT '表现评分(0-10)',
    attendance_rate DECIMAL(5,2) DEFAULT 100 COMMENT '出勤率百分比',
    task_completion_rate DECIMAL(5,2) DEFAULT 0 COMMENT '任务完成率百分比',
    quality_rating TINYINT DEFAULT 3 COMMENT '质量评级: 1-差, 2-一般, 3-良好, 4-优秀, 5-卓越',
    collaboration_rating TINYINT DEFAULT 3 COMMENT '协作评级: 1-差, 2-一般, 3-良好, 4-优秀, 5-卓越',
    innovation_rating TINYINT DEFAULT 3 COMMENT '创新评级: 1-差, 2-一般, 3-良好, 4-优秀, 5-卓越',
    learning_growth TEXT COMMENT '学习成长',
    achievements TEXT COMMENT '个人成果',
    feedback_from_teacher TEXT COMMENT '教师反馈',
    feedback_from_peers TEXT COMMENT '同伴反馈',
    self_reflection TEXT COMMENT '自我反思',
    member_status TINYINT DEFAULT 1 COMMENT '成员状态: 1-活跃, 2-请假, 3-已退出, 4-被移除',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    UNIQUE KEY uk_project_student (project_id, student_id),
    INDEX idx_project_id (project_id),
    INDEX idx_student_id (student_id),
    INDEX idx_member_role (member_role),
    INDEX idx_member_status (member_status),
    INDEX idx_join_date (join_date),
    FOREIGN KEY (project_id) REFERENCES projects(id),
    FOREIGN KEY (student_id) REFERENCES users(id)
) COMMENT '项目成员表';

-- 项目任务表
CREATE TABLE project_tasks (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '任务ID',
    project_id BIGINT NOT NULL COMMENT '项目ID',
    parent_task_id BIGINT DEFAULT 0 COMMENT '父任务ID',
    task_name VARCHAR(200) NOT NULL COMMENT '任务名称',
    task_description TEXT COMMENT '任务描述',
    task_type TINYINT NOT NULL COMMENT '任务类型: 1-需求分析, 2-设计, 3-开发, 4-测试, 5-文档, 6-其他',
    priority TINYINT DEFAULT 3 COMMENT '优先级: 1-低, 2-中, 3-高, 4-紧急',
    difficulty TINYINT DEFAULT 2 COMMENT '难度: 1-简单, 2-中等, 3-困难, 4-很困难',
    estimated_hours DECIMAL(6,2) DEFAULT 0 COMMENT '预估工时',
    actual_hours DECIMAL(6,2) DEFAULT 0 COMMENT '实际工时',
    assignee_id BIGINT COMMENT '负责人ID',
    reviewer_id BIGINT COMMENT '审核人ID',
    planned_start_date DATE COMMENT '计划开始日期',
    planned_end_date DATE COMMENT '计划结束日期',
    actual_start_date DATE COMMENT '实际开始日期',
    actual_end_date DATE COMMENT '实际结束日期',
    progress_percentage DECIMAL(5,2) DEFAULT 0 COMMENT '进度百分比',
    task_status TINYINT DEFAULT 1 COMMENT '任务状态: 1-待开始, 2-进行中, 3-待审核, 4-已完成, 5-已取消, 6-已延期',
    quality_score DECIMAL(3,1) DEFAULT 0 COMMENT '质量评分(0-10)',
    rework_count INT DEFAULT 0 COMMENT '返工次数',
    rework_reason TEXT COMMENT '返工原因',
    deliverables TEXT COMMENT '交付物',
    acceptance_criteria TEXT COMMENT '验收标准',
    notes TEXT COMMENT '备注',
    tags JSON COMMENT '标签',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_project_id (project_id),
    INDEX idx_parent_task_id (parent_task_id),
    INDEX idx_assignee_id (assignee_id),
    INDEX idx_reviewer_id (reviewer_id),
    INDEX idx_task_status (task_status),
    INDEX idx_priority (priority),
    INDEX idx_planned_dates (planned_start_date, planned_end_date),
    FOREIGN KEY (project_id) REFERENCES projects(id),
    FOREIGN KEY (assignee_id) REFERENCES users(id),
    FOREIGN KEY (reviewer_id) REFERENCES users(id)
) COMMENT '项目任务表';

-- 项目申请表
CREATE TABLE project_applications (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '申请ID',
    project_id BIGINT NOT NULL COMMENT '项目ID',
    student_id BIGINT NOT NULL COMMENT '申请学生ID',
    application_type TINYINT NOT NULL COMMENT '申请类型: 1-加入项目, 2-退出项目, 3-角色变更',
    desired_role TINYINT COMMENT '期望角色: 1-组长, 2-核心成员, 3-普通成员',
    motivation TEXT NOT NULL COMMENT '申请动机',
    relevant_skills JSON COMMENT '相关技能',
    previous_experience TEXT COMMENT '相关经验',
    available_time_per_week DECIMAL(4,1) COMMENT '每周可投入时间(小时)',
    expected_contribution TEXT COMMENT '预期贡献',
    portfolio_url VARCHAR(500) COMMENT '作品集URL',
    recommendation_letter_url VARCHAR(500) COMMENT '推荐信URL',
    application_status TINYINT DEFAULT 1 COMMENT '申请状态: 1-待审核, 2-已通过, 3-已拒绝, 4-已撤回',
    reviewer_id BIGINT COMMENT '审核人ID',
    review_comment TEXT COMMENT '审核意见',
    review_time DATETIME COMMENT '审核时间',
    interview_required TINYINT DEFAULT 0 COMMENT '是否需要面试: 0-否, 1-是',
    interview_time DATETIME COMMENT '面试时间',
    interview_feedback TEXT COMMENT '面试反馈',
    rejection_reason TEXT COMMENT '拒绝原因',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_project_id (project_id),
    INDEX idx_student_id (student_id),
    INDEX idx_application_status (application_status),
    INDEX idx_reviewer_id (reviewer_id),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (project_id) REFERENCES projects(id),
    FOREIGN KEY (student_id) REFERENCES users(id),
    FOREIGN KEY (reviewer_id) REFERENCES users(id)
) COMMENT '项目申请表';

-- =====================================================
-- 6. 科研与国际化模块表结构
-- =====================================================

-- 短期交流项目表
CREATE TABLE exchange_programs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '项目ID',
    program_name VARCHAR(200) NOT NULL COMMENT '项目名称',
    program_code VARCHAR(50) UNIQUE COMMENT '项目编号',
    program_type TINYINT NOT NULL COMMENT '项目类型: 1-学术交流, 2-实习实训, 3-文化交流, 4-研究合作',
    partner_institution VARCHAR(200) NOT NULL COMMENT '合作机构',
    partner_country VARCHAR(100) NOT NULL COMMENT '合作国家',
    partner_city VARCHAR(100) COMMENT '合作城市',
    program_duration INT NOT NULL COMMENT '项目时长(天)',
    start_date DATE NOT NULL COMMENT '开始日期',
    end_date DATE NOT NULL COMMENT '结束日期',
    application_deadline DATE NOT NULL COMMENT '申请截止日期',
    max_participants INT DEFAULT 0 COMMENT '最大参与人数',
    current_participants INT DEFAULT 0 COMMENT '当前参与人数',
    target_audience TINYINT NOT NULL COMMENT '目标群体: 1-本科生, 2-研究生, 3-博士生, 4-教师, 5-全部',
    language_requirement VARCHAR(100) COMMENT '语言要求',
    gpa_requirement DECIMAL(3,2) COMMENT 'GPA要求',
    major_requirements JSON COMMENT '专业要求',
    program_description TEXT COMMENT '项目描述',
    learning_objectives TEXT COMMENT '学习目标',
    activities TEXT COMMENT '活动安排',
    accommodation_info TEXT COMMENT '住宿信息',
    cost_info TEXT COMMENT '费用信息',
    scholarship_available TINYINT DEFAULT 0 COMMENT '是否有奖学金: 0-否, 1-是',
    scholarship_amount DECIMAL(10,2) DEFAULT 0 COMMENT '奖学金金额',
    application_requirements TEXT COMMENT '申请要求',
    required_documents JSON COMMENT '所需文档',
    contact_person VARCHAR(100) COMMENT '联系人',
    contact_email VARCHAR(100) COMMENT '联系邮箱',
    contact_phone VARCHAR(20) COMMENT '联系电话',
    program_status TINYINT DEFAULT 1 COMMENT '项目状态: 1-招募中, 2-已满员, 3-进行中, 4-已结束, 5-已取消',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_program_code (program_code),
    INDEX idx_program_type (program_type),
    INDEX idx_partner_country (partner_country),
    INDEX idx_target_audience (target_audience),
    INDEX idx_application_deadline (application_deadline),
    INDEX idx_program_status (program_status),
    INDEX idx_start_date (start_date)
) COMMENT '短期交流项目表';

-- 交流项目申请表
CREATE TABLE exchange_applications (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '申请ID',
    program_id BIGINT NOT NULL COMMENT '项目ID',
    applicant_id BIGINT NOT NULL COMMENT '申请人ID',
    application_form JSON COMMENT '申请表单数据',
    motivation_letter_url VARCHAR(500) COMMENT '动机信URL',
    recommendation_letters JSON COMMENT '推荐信URL列表',
    transcript_url VARCHAR(500) COMMENT '成绩单URL',
    language_certificate_url VARCHAR(500) COMMENT '语言证书URL',
    passport_url VARCHAR(500) COMMENT '护照URL',
    other_documents JSON COMMENT '其他文档URL列表',
    current_gpa DECIMAL(3,2) COMMENT '当前GPA',
    language_proficiency VARCHAR(100) COMMENT '语言水平',
    previous_exchange_experience TEXT COMMENT '以往交流经验',
    special_requirements TEXT COMMENT '特殊要求',
    emergency_contact JSON COMMENT '紧急联系人信息',
    application_status TINYINT DEFAULT 1 COMMENT '申请状态: 1-待审核, 2-初审通过, 3-面试通过, 4-最终录取, 5-已拒绝, 6-已撤回',
    reviewer_id BIGINT COMMENT '审核人ID',
    review_score DECIMAL(3,1) DEFAULT 0 COMMENT '审核评分(0-10)',
    review_comment TEXT COMMENT '审核意见',
    interview_required TINYINT DEFAULT 0 COMMENT '是否需要面试: 0-否, 1-是',
    interview_time DATETIME COMMENT '面试时间',
    interview_score DECIMAL(3,1) DEFAULT 0 COMMENT '面试评分(0-10)',
    interview_feedback TEXT COMMENT '面试反馈',
    final_decision TINYINT COMMENT '最终决定: 1-录取, 2-候补, 3-拒绝',
    decision_reason TEXT COMMENT '决定原因',
    notification_sent TINYINT DEFAULT 0 COMMENT '是否已发送通知: 0-否, 1-是',
    acceptance_deadline DATE COMMENT '接受截止日期',
    participant_response TINYINT COMMENT '参与者回复: 1-接受, 2-拒绝',
    response_time DATETIME COMMENT '回复时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_program_id (program_id),
    INDEX idx_applicant_id (applicant_id),
    INDEX idx_application_status (application_status),
    INDEX idx_reviewer_id (reviewer_id),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (program_id) REFERENCES exchange_programs(id),
    FOREIGN KEY (applicant_id) REFERENCES users(id),
    FOREIGN KEY (reviewer_id) REFERENCES users(id)
) COMMENT '交流项目申请表';

-- 科研项目表
CREATE TABLE research_projects (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '科研项目ID',
    project_name VARCHAR(200) NOT NULL COMMENT '项目名称',
    project_code VARCHAR(50) UNIQUE COMMENT '项目编号',
    project_type TINYINT NOT NULL COMMENT '项目类型: 1-国家级, 2-省部级, 3-市厅级, 4-校级, 5-企业合作',
    funding_agency VARCHAR(200) COMMENT '资助机构',
    principal_investigator_id BIGINT NOT NULL COMMENT '项目负责人ID',
    co_investigators JSON COMMENT '合作研究者ID列表',
    department_id BIGINT COMMENT '所属部门ID',
    research_field VARCHAR(100) COMMENT '研究领域',
    keywords JSON COMMENT '关键词',
    project_abstract TEXT COMMENT '项目摘要',
    research_objectives TEXT COMMENT '研究目标',
    research_methodology TEXT COMMENT '研究方法',
    expected_outcomes TEXT COMMENT '预期成果',
    innovation_points TEXT COMMENT '创新点',
    total_budget DECIMAL(15,2) DEFAULT 0 COMMENT '总预算',
    approved_budget DECIMAL(15,2) DEFAULT 0 COMMENT '批准预算',
    used_budget DECIMAL(15,2) DEFAULT 0 COMMENT '已使用预算',
    start_date DATE NOT NULL COMMENT '开始日期',
    end_date DATE NOT NULL COMMENT '结束日期',
    current_phase TINYINT DEFAULT 1 COMMENT '当前阶段: 1-申报, 2-立项, 3-执行, 4-结题, 5-验收',
    progress_percentage DECIMAL(5,2) DEFAULT 0 COMMENT '进度百分比',
    milestone_plan JSON COMMENT '里程碑计划',
    risk_assessment TEXT COMMENT '风险评估',
    quality_control_plan TEXT COMMENT '质量控制计划',
    ethics_approval_required TINYINT DEFAULT 0 COMMENT '是否需要伦理审批: 0-否, 1-是',
    ethics_approval_status TINYINT DEFAULT 0 COMMENT '伦理审批状态: 0-未申请, 1-审批中, 2-已通过, 3-被拒绝',
    intellectual_property_plan TEXT COMMENT '知识产权计划',
    collaboration_agreements JSON COMMENT '合作协议',
    project_status TINYINT DEFAULT 1 COMMENT '项目状态: 1-申报中, 2-立项, 3-执行中, 4-暂停, 5-完成, 6-终止',
    final_report_url VARCHAR(500) COMMENT '结题报告URL',
    achievements JSON COMMENT '项目成果',
    publications JSON COMMENT '发表论文',
    patents JSON COMMENT '专利申请',
    awards JSON COMMENT '获得奖项',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_project_code (project_code),
    INDEX idx_project_type (project_type),
    INDEX idx_principal_investigator_id (principal_investigator_id),
    INDEX idx_department_id (department_id),
    INDEX idx_research_field (research_field),
    INDEX idx_current_phase (current_phase),
    INDEX idx_project_status (project_status),
    INDEX idx_start_date (start_date),
    INDEX idx_end_date (end_date),
    FOREIGN KEY (principal_investigator_id) REFERENCES users(id),
    FOREIGN KEY (department_id) REFERENCES departments(id)
) COMMENT '科研项目表';

-- =====================================================
-- 7. 系统管理表结构
-- =====================================================

-- 系统配置表
CREATE TABLE system_configs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '配置ID',
    config_key VARCHAR(100) NOT NULL UNIQUE COMMENT '配置键',
    config_value TEXT COMMENT '配置值',
    config_type TINYINT DEFAULT 1 COMMENT '配置类型: 1-字符串, 2-数字, 3-布尔, 4-JSON',
    config_group VARCHAR(50) COMMENT '配置分组',
    description TEXT COMMENT '配置描述',
    is_system TINYINT DEFAULT 0 COMMENT '是否系统配置: 0-否, 1-是',
    is_encrypted TINYINT DEFAULT 0 COMMENT '是否加密: 0-否, 1-是',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_config_key (config_key),
    INDEX idx_config_group (config_group)
) COMMENT '系统配置表';

-- 文件管理表
CREATE TABLE file_storage (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '文件ID',
    original_name VARCHAR(255) NOT NULL COMMENT '原始文件名',
    stored_name VARCHAR(255) NOT NULL COMMENT '存储文件名',
    file_path VARCHAR(500) NOT NULL COMMENT '文件路径',
    file_size BIGINT NOT NULL COMMENT '文件大小(字节)',
    file_type VARCHAR(100) COMMENT '文件类型',
    mime_type VARCHAR(100) COMMENT 'MIME类型',
    file_hash VARCHAR(64) COMMENT '文件哈希值',
    uploader_id BIGINT NOT NULL COMMENT '上传者ID',
    business_type VARCHAR(50) COMMENT '业务类型',
    business_id BIGINT COMMENT '业务ID',
    access_level TINYINT DEFAULT 1 COMMENT '访问级别: 1-公开, 2-登录可见, 3-权限控制',
    download_count INT DEFAULT 0 COMMENT '下载次数',
    is_deleted TINYINT DEFAULT 0 COMMENT '是否已删除: 0-否, 1-是',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_stored_name (stored_name),
    INDEX idx_uploader_id (uploader_id),
    INDEX idx_business (business_type, business_id),
    INDEX idx_file_hash (file_hash),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (uploader_id) REFERENCES users(id)
) COMMENT '文件管理表';

-- 操作日志表
CREATE TABLE operation_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '日志ID',
    user_id BIGINT COMMENT '操作用户ID',
    username VARCHAR(50) COMMENT '用户名',
    operation_type VARCHAR(50) NOT NULL COMMENT '操作类型',
    operation_name VARCHAR(100) NOT NULL COMMENT '操作名称',
    operation_method VARCHAR(10) COMMENT '请求方法',
    operation_url VARCHAR(500) COMMENT '请求URL',
    operation_params TEXT COMMENT '请求参数',
    operation_result TINYINT DEFAULT 1 COMMENT '操作结果: 0-失败, 1-成功',
    error_message TEXT COMMENT '错误信息',
    execution_time INT DEFAULT 0 COMMENT '执行时间(毫秒)',
    ip_address VARCHAR(45) COMMENT 'IP地址',
    user_agent VARCHAR(500) COMMENT '用户代理',
    browser VARCHAR(100) COMMENT '浏览器',
    operating_system VARCHAR(100) COMMENT '操作系统',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_user_id (user_id),
    INDEX idx_operation_type (operation_type),
    INDEX idx_operation_result (operation_result),
    INDEX idx_created_at (created_at),
    INDEX idx_ip_address (ip_address),
    FOREIGN KEY (user_id) REFERENCES users(id)
) COMMENT '操作日志表';

-- 通知消息表
CREATE TABLE notifications (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '通知ID',
    title VARCHAR(200) NOT NULL COMMENT '通知标题',
    content TEXT NOT NULL COMMENT '通知内容',
    notification_type TINYINT NOT NULL COMMENT '通知类型: 1-系统通知, 2-项目通知, 3-竞赛通知, 4-审核通知',
    sender_id BIGINT COMMENT '发送者ID',
    receiver_id BIGINT NOT NULL COMMENT '接收者ID',
    business_type VARCHAR(50) COMMENT '业务类型',
    business_id BIGINT COMMENT '业务ID',
    priority TINYINT DEFAULT 2 COMMENT '优先级: 1-低, 2-中, 3-高, 4-紧急',
    is_read TINYINT DEFAULT 0 COMMENT '是否已读: 0-未读, 1-已读',
    read_time DATETIME COMMENT '阅读时间',
    is_deleted TINYINT DEFAULT 0 COMMENT '是否已删除: 0-否, 1-是',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_receiver_id (receiver_id),
    INDEX idx_sender_id (sender_id),
    INDEX idx_notification_type (notification_type),
    INDEX idx_is_read (is_read),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (sender_id) REFERENCES users(id),
    FOREIGN KEY (receiver_id) REFERENCES users(id)
) COMMENT '通知消息表';

-- =====================================================
-- 8. 初始化数据
-- =====================================================

-- 插入默认角色
INSERT INTO roles (role_name, role_code, description, is_system, permissions) VALUES
('系统管理员', 'ADMIN', '系统管理员，拥有所有权限', 1, '["*"]'),
('教师', 'TEACHER', '教师角色，可以指导项目和竞赛', 1, '["project:manage", "competition:manage", "student:view"]'),
('学生', 'STUDENT', '学生角色，可以参与项目和竞赛', 1, '["project:join", "competition:join", "profile:manage"]'),
('企业用户', 'ENTERPRISE', '企业用户，可以参与产教融合', 1, '["cooperation:manage", "project:view"]'),
('实验室管理员', 'LAB_ADMIN', '实验室管理员，管理实验室和设备', 1, '["lab:manage", "equipment:manage"]');

-- 插入默认部门
INSERT INTO departments (name, code, parent_id, level, description) VALUES
('计算机学院', 'CS', 0, 1, '计算机科学与技术学院'),
('电子信息学院', 'EE', 0, 1, '电子信息工程学院'),
('机械工程学院', 'ME', 0, 1, '机械工程学院'),
('管理学院', 'MBA', 0, 1, '工商管理学院'),
('科研处', 'RESEARCH', 0, 1, '科学研究处'),
('教务处', 'ACADEMIC', 0, 1, '教务处'),
('学生处', 'STUDENT_AFFAIRS', 0, 1, '学生工作处');

-- 插入系统配置
INSERT INTO system_configs (config_key, config_value, config_type, config_group, description) VALUES
('system.name', '科研创新与学科竞赛综合管理系统', 1, 'system', '系统名称'),
('system.version', '1.0.0', 1, 'system', '系统版本'),
('file.upload.max_size', '104857600', 2, 'file', '文件上传最大大小(字节)'),
('file.upload.allowed_types', '["pdf","doc","docx","xls","xlsx","ppt","pptx","jpg","jpeg","png","gif"]', 4, 'file', '允许上传的文件类型'),
('notification.email.enabled', 'true', 3, 'notification', '是否启用邮件通知'),
('notification.sms.enabled', 'false', 3, 'notification', '是否启用短信通知'),
('security.password.min_length', '8', 2, 'security', '密码最小长度'),
('security.session.timeout', '7200', 2, 'security', '会话超时时间(秒)'),
('competition.auto_audit', 'false', 3, 'competition', '竞赛获奖是否自动审核'),
('project.max_members', '10', 2, 'project', '项目最大成员数');

-- =====================================================
-- 9. 视图定义
-- =====================================================

-- 用户详细信息视图
CREATE VIEW v_user_details AS
SELECT 
    u.id,
    u.username,
    u.email,
    u.phone,
    u.real_name,
    u.avatar_url,
    u.gender,
    u.birth_date,
    u.status,
    u.last_login_time,
    d.name AS department_name,
    d.code AS department_code,
    GROUP_CONCAT(r.role_name) AS roles,
    GROUP_CONCAT(r.role_code) AS role_codes,
    u.created_at,
    u.updated_at
FROM users u
LEFT JOIN departments d ON u.department_id = d.id
LEFT JOIN user_roles ur ON u.id = ur.user_id
LEFT JOIN roles r ON ur.role_id = r.id
WHERE u.status = 1
GROUP BY u.id;

-- 项目统计视图
CREATE VIEW v_project_statistics AS
SELECT 
    p.id,
    p.project_name,
    p.project_code,
    p.project_type,
    p.project_status,
    p.teacher_id,
    t.real_name AS teacher_name,
    d.name AS department_name,
    COUNT(pm.id) AS member_count,
    COUNT(pt.id) AS task_count,
    AVG(pt.progress_percentage) AS avg_task_progress,
    p.progress_percentage,
    p.health_status,
    p.start_date,
    p.end_date,
    DATEDIFF(p.end_date, CURDATE()) AS days_remaining
FROM projects p
LEFT JOIN users t ON p.teacher_id = t.id
LEFT JOIN departments d ON p.department_id = d.id
LEFT JOIN project_members pm ON p.id = pm.project_id AND pm.member_status = 1
LEFT JOIN project_tasks pt ON p.id = pt.project_id
GROUP BY p.id;

-- 竞赛获奖统计视图
CREATE VIEW v_competition_award_statistics AS
SELECT 
    c.id AS competition_id,
    c.name AS competition_name,
    c.competition_type,
    c.level,
    c.competition_year,
    COUNT(a.id) AS total_awards,
    COUNT(CASE WHEN a.award_level = 1 THEN 1 END) AS special_awards,
    COUNT(CASE WHEN a.award_level = 2 THEN 1 END) AS first_awards,
    COUNT(CASE WHEN a.award_level = 3 THEN 1 END) AS second_awards,
    COUNT(CASE WHEN a.award_level = 4 THEN 1 END) AS third_awards,
    COUNT(CASE WHEN a.award_level = 5 THEN 1 END) AS excellent_awards,
    SUM(a.points) AS total_points,
    SUM(a.bonus_amount) AS total_bonus
FROM competitions c
LEFT JOIN awards a ON c.id = a.competition_id AND a.audit_status = 1
GROUP BY c.id;

-- 实验室设备使用统计视图
CREATE VIEW v_lab_equipment_usage AS
SELECT 
    l.id AS lab_id,
    l.name AS lab_name,
    l.lab_code,
    COUNT(e.id) AS total_equipment,
    COUNT(CASE WHEN e.status = 1 THEN 1 END) AS normal_equipment,
    COUNT(CASE WHEN e.status = 2 THEN 1 END) AS fault_equipment,
    COUNT(CASE WHEN e.usage_status = 1 THEN 1 END) AS in_use_equipment,
    COUNT(eb.id) AS total_borrowings,
    COUNT(CASE WHEN eb.borrowing_status = 3 THEN 1 END) AS current_borrowings,
    AVG(eb.rating) AS avg_rating
FROM laboratories l
LEFT JOIN equipment e ON l.id = e.lab_id
LEFT JOIN equipment_borrowings eb ON e.id = eb.equipment_id
GROUP BY l.id;

-- =====================================================
-- 10. 存储过程
-- =====================================================

DELIMITER //

-- 自动计算项目进度的存储过程
CREATE PROCEDURE UpdateProjectProgress(IN project_id BIGINT)
BEGIN
    DECLARE total_tasks INT DEFAULT 0;
    DECLARE completed_tasks INT DEFAULT 0;
    DECLARE progress_percentage DECIMAL(5,2) DEFAULT 0;
    
    -- 计算任务总数和完成数
    SELECT COUNT(*), COUNT(CASE WHEN task_status = 4 THEN 1 END)
    INTO total_tasks, completed_tasks
    FROM project_tasks 
    WHERE project_id = project_id;
    
    -- 计算进度百分比
    IF total_tasks > 0 THEN
        SET progress_percentage = (completed_tasks / total_tasks) * 100;
    END IF;
    
    -- 更新项目进度
    UPDATE projects 
    SET progress_percentage = progress_percentage,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = project_id;
END //

-- 发送通知的存储过程
CREATE PROCEDURE SendNotification(
    IN p_title VARCHAR(200),
    IN p_content TEXT,
    IN p_notification_type TINYINT,
    IN p_sender_id BIGINT,
    IN p_receiver_id BIGINT,
    IN p_business_type VARCHAR(50),
    IN p_business_id BIGINT,
    IN p_priority TINYINT
)
BEGIN
    INSERT INTO notifications (
        title, content, notification_type, sender_id, receiver_id,
        business_type, business_id, priority
    ) VALUES (
        p_title, p_content, p_notification_type, p_sender_id, p_receiver_id,
        p_business_type, p_business_id, p_priority
    );
END //

DELIMITER ;

-- =====================================================
-- 11. 触发器
-- =====================================================

-- 项目成员变更时更新项目统计
DELIMITER //
CREATE TRIGGER tr_project_member_update
AFTER INSERT ON project_members
FOR EACH ROW
BEGIN
    UPDATE projects 
    SET updated_at = CURRENT_TIMESTAMP
    WHERE id = NEW.project_id;
END //

-- 任务状态变更时自动更新项目进度
CREATE TRIGGER tr_task_status_update
AFTER UPDATE ON project_tasks
FOR EACH ROW
BEGIN
    IF OLD.task_status != NEW.task_status THEN
        CALL UpdateProjectProgress(NEW.project_id);
    END IF;
END //

-- 设备借用状态变更时更新设备状态
CREATE TRIGGER tr_equipment_borrowing_update
AFTER UPDATE ON equipment_borrowings
FOR EACH ROW
BEGIN
    IF OLD.borrowing_status != NEW.borrowing_status THEN
        IF NEW.borrowing_status = 3 THEN -- 使用中
            UPDATE equipment SET usage_status = 1 WHERE id = NEW.equipment_id;
        ELSEIF NEW.borrowing_status = 4 THEN -- 已归还
            UPDATE equipment SET usage_status = 0 WHERE id = NEW.equipment_id;
        END IF;
    END IF;
END //

DELIMITER ;

-- =====================================================
-- 12. 索引优化建议
-- =====================================================

-- 复合索引优化
CREATE INDEX idx_awards_competition_level_date ON awards(competition_id, award_level, award_date);
CREATE INDEX idx_projects_status_teacher_date ON projects(project_status, teacher_id, start_date);
CREATE INDEX idx_equipment_lab_status_type ON equipment(lab_id, status, equipment_type);
CREATE INDEX idx_borrowings_equipment_status_date ON equipment_borrowings(equipment_id, borrowing_status, planned_start_time);
CREATE INDEX idx_tasks_project_status_assignee ON project_tasks(project_id, task_status, assignee_id);
CREATE INDEX idx_notifications_receiver_read_type ON notifications(receiver_id, is_read, notification_type);

-- =====================================================
-- 数据库设计文档完成
-- 版本: v1.0
-- 最后更新: 2024年
-- =====================================================