task_track/database/init.sql

-- 任务跟踪系统数据库初始化脚本

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

USE task_track;

-- 用户表
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    real_name VARCHAR(50) COMMENT '真实姓名',
    phone VARCHAR(20) COMMENT '手机号',
    avatar VARCHAR(255) COMMENT '头像',
    status TINYINT DEFAULT 1 COMMENT '状态 1:启用 0:禁用',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    deleted_at TIMESTAMP NULL COMMENT '删除时间',
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_deleted_at (deleted_at)
) COMMENT '用户表';

-- 机构表
CREATE TABLE IF NOT EXISTS organizations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '机构名称',
    code VARCHAR(50) NOT NULL UNIQUE COMMENT '机构代码',
    parent_id INT UNSIGNED DEFAULT 0 COMMENT '父级机构ID',
    level TINYINT DEFAULT 1 COMMENT '机构层级',
    sort INT DEFAULT 0 COMMENT '排序',
    status TINYINT DEFAULT 1 COMMENT '状态 1:启用 0:禁用',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    deleted_at TIMESTAMP NULL COMMENT '删除时间',
    INDEX idx_code (code),
    INDEX idx_parent_id (parent_id),
    INDEX idx_level (level),
    INDEX idx_status (status),
    INDEX idx_deleted_at (deleted_at)
) COMMENT '机构表';

-- 用户机构关系表
CREATE TABLE IF NOT EXISTS user_organizations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL COMMENT '用户ID',
    organization_id INT UNSIGNED NOT NULL COMMENT '机构ID',
    role VARCHAR(20) DEFAULT 'member' COMMENT '角色 admin:管理员 member:成员',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    UNIQUE KEY uk_user_org (user_id, organization_id),
    INDEX idx_user_id (user_id),
    INDEX idx_organization_id (organization_id)
) COMMENT '用户机构关系表';

-- 任务表
CREATE TABLE IF NOT EXISTS tasks (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL COMMENT '任务标题',
    description TEXT COMMENT '任务描述',
    type VARCHAR(50) COMMENT '任务类型',
    priority VARCHAR(20) DEFAULT 'medium' COMMENT '优先级 urgent:紧急 high:高 medium:中 low:低',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '状态 pending:待处理 in_progress:进行中 completed:已完成 cancelled:已取消',
    creator_id INT UNSIGNED NOT NULL COMMENT '创建者ID',
    assignee_id INT UNSIGNED COMMENT '执行者ID',
    organization_id INT UNSIGNED NOT NULL COMMENT '所属机构ID',
    start_time TIMESTAMP NULL COMMENT '开始时间',
    end_time TIMESTAMP NULL COMMENT '截止时间',
    completed_at TIMESTAMP NULL COMMENT '完成时间',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    deleted_at TIMESTAMP NULL COMMENT '删除时间',
    INDEX idx_title (title),
    INDEX idx_status (status),
    INDEX idx_priority (priority),
    INDEX idx_creator_id (creator_id),
    INDEX idx_assignee_id (assignee_id),
    INDEX idx_organization_id (organization_id),
    INDEX idx_end_time (end_time),
    INDEX idx_deleted_at (deleted_at)
) COMMENT '任务表';

-- 任务标签表
CREATE TABLE IF NOT EXISTS task_tags (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL COMMENT '标签名称',
    color VARCHAR(20) DEFAULT '#409EFF' COMMENT '标签颜色',
    organization_id INT UNSIGNED NOT NULL COMMENT '所属机构ID',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    deleted_at TIMESTAMP NULL COMMENT '删除时间',
    INDEX idx_name (name),
    INDEX idx_organization_id (organization_id),
    INDEX idx_deleted_at (deleted_at)
) COMMENT '任务标签表';

-- 任务标签关系表
CREATE TABLE IF NOT EXISTS task_tag_relations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    task_id INT UNSIGNED NOT NULL COMMENT '任务ID',
    tag_id INT UNSIGNED NOT NULL COMMENT '标签ID',
    UNIQUE KEY uk_task_tag (task_id, tag_id),
    INDEX idx_task_id (task_id),
    INDEX idx_tag_id (tag_id)
) COMMENT '任务标签关系表';

-- 任务评论表
CREATE TABLE IF NOT EXISTS task_comments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    task_id INT UNSIGNED NOT NULL COMMENT '任务ID',
    user_id INT UNSIGNED NOT NULL COMMENT '用户ID',
    content TEXT NOT NULL COMMENT '评论内容',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    deleted_at TIMESTAMP NULL COMMENT '删除时间',
    INDEX idx_task_id (task_id),
    INDEX idx_user_id (user_id),
    INDEX idx_deleted_at (deleted_at)
) COMMENT '任务评论表';

-- 任务附件表
CREATE TABLE IF NOT EXISTS task_attachments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    task_id INT UNSIGNED NOT NULL COMMENT '任务ID',
    file_name VARCHAR(255) NOT NULL COMMENT '文件名',
    file_path VARCHAR(500) NOT NULL COMMENT '文件路径',
    file_size BIGINT COMMENT '文件大小',
    file_type VARCHAR(50) COMMENT '文件类型',
    uploaded_by INT UNSIGNED NOT NULL COMMENT '上传者ID',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    deleted_at TIMESTAMP NULL COMMENT '删除时间',
    INDEX idx_task_id (task_id),
    INDEX idx_uploaded_by (uploaded_by),
    INDEX idx_deleted_at (deleted_at)
) COMMENT '任务附件表';

-- 插入初始数据

-- 插入默认机构
INSERT INTO organizations (name, code, parent_id, level, sort, status) VALUES
('总公司', 'HQ', 0, 1, 0, 1),
('技术部', 'TECH', 1, 2, 1, 1),
('市场部', 'MKT', 1, 2, 2, 1),
('人事部', 'HR', 1, 2, 3, 1);

-- 插入默认用户（密码为 123456，已加密）
INSERT INTO users (username, email, password, real_name, status) VALUES
('admin', 'admin@example.com', '$2a$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '系统管理员', 1),
('zhangsan', 'zhangsan@example.com', '$2a$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '张三', 1),
('lisi', 'lisi@example.com', '$2a$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '李四', 1);

-- 插入用户机构关系
INSERT INTO user_organizations (user_id, organization_id, role) VALUES
(1, 1, 'admin'),
(2, 2, 'member'),
(3, 3, 'member');

-- 插入默认任务标签
INSERT INTO task_tags (name, color, organization_id) VALUES
('紧急', '#F56C6C', 1),
('重要', '#E6A23C', 1),
('bug修复', '#909399', 1),
('新功能', '#67C23A', 1),
('优化', '#409EFF', 1);

-- 插入示例任务
INSERT INTO tasks (title, description, type, priority, status, creator_id, assignee_id, organization_id, start_time, end_time) VALUES
('完成用户认证模块', '实现用户登录、注册、权限验证功能', '开发', 'high', 'in_progress', 1, 2, 2, '2025-07-08 09:00:00', '2025-07-10 18:00:00'),
('设计任务管理界面', '设计任务列表、任务详情、任务编辑等界面', '设计', 'medium', 'pending', 1, 3, 3, '2025-07-09 09:00:00', '2025-07-12 18:00:00'),
('数据库优化', '优化查询性能，添加必要的索引', '优化', 'low', 'completed', 1, 2, 2, '2025-07-05 09:00:00', '2025-07-08 18:00:00');

-- 插入任务标签关系
INSERT INTO task_tag_relations (task_id, tag_id) VALUES
(1, 2), -- 任务1 标记为重要
(1, 4), -- 任务1 标记为新功能
(2, 4), -- 任务2 标记为新功能
(3, 5); -- 任务3 标记为优化

-- 插入示例评论
INSERT INTO task_comments (task_id, user_id, content) VALUES
(1, 2, '已开始开发，预计明天完成登录功能'),
(1, 1, '注意安全性和用户体验'),
(2, 3, '界面设计稿已完成初版，请查看');

COMMIT;