ai-interview/stastic.sql

-- 创建数据库
CREATE DATABASE IF NOT EXISTS recruitment_system;
USE recruitment_system;

-- 职位表
CREATE TABLE jobs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(100) NOT NULL,
  department VARCHAR(50) NOT NULL,
  location VARCHAR(50) NOT NULL,
  description TEXT,
  status ENUM('active', 'paused', 'draft') NOT NULL DEFAULT 'draft',
  pending_resumes INT NOT NULL DEFAULT 0,
  passed_resumes INT NOT NULL DEFAULT 0,
  ai_criteria JSON,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_status (status),
  INDEX idx_location (location),
  INDEX idx_department (department)
);

-- 候选人表
CREATE TABLE candidates (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  job_id INT NOT NULL,
  job_title VARCHAR(100) NOT NULL,
  match_score INT NOT NULL,
  status ENUM('pending', 'passed', 'rejected', 'interviewed') NOT NULL DEFAULT 'pending',
  highlights TEXT,
  concerns TEXT,
  summary TEXT,
  resume_text TEXT NOT NULL,
  education VARCHAR(255),
  experience TEXT,
  skills TEXT,
  submitted_at DATETIME NOT NULL,
  reviewed_at DATETIME,
  FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
  INDEX idx_job_id (job_id),
  INDEX idx_status (status),
  INDEX idx_match_score (match_score)
);

-- 面试表
CREATE TABLE interviews (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(100) NOT NULL,
  description TEXT,
  questions TEXT,
  duration INT NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_title (title)
);

-- 候选人-面试关联表
CREATE TABLE candidate_interviews (
  id INT PRIMARY KEY AUTO_INCREMENT,
  candidate_id INT NOT NULL,
  interview_id INT NOT NULL,
  interview_time DATETIME NOT NULL,
  interviewer_id VARCHAR(36),
  notes TEXT,
  result ENUM('scheduled', 'in_progress', 'completed', 'cancelled') NOT NULL DEFAULT 'scheduled',
  FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
  FOREIGN KEY (interview_id) REFERENCES interviews(id) ON DELETE CASCADE,
  UNIQUE INDEX idx_candidate_interview (candidate_id, interview_id),
  INDEX idx_result (result),
  INDEX idx_interview_time (interview_time)
);

-- 技能表
CREATE TABLE skills (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL UNIQUE,
  category VARCHAR(50),
  created_at DATETIME NOT NULL
);

-- 候选人技能关联表
CREATE TABLE candidate_skills (
  candidate_id INT NOT NULL,
  skill_id INT NOT NULL,
  proficiency ENUM('basic', 'intermediate', 'advanced') DEFAULT 'intermediate',
  PRIMARY KEY (candidate_id, skill_id),
  FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
  FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE
);

-- 候选人状态历史表
CREATE TABLE candidate_status_history (
  id INT PRIMARY KEY AUTO_INCREMENT,
  candidate_id INT NOT NULL,
  old_status ENUM('pending', 'passed', 'rejected', 'interviewed') NOT NULL,
  new_status ENUM('pending', 'passed', 'rejected', 'interviewed') NOT NULL,
  changed_by VARCHAR(50) NOT NULL,
  change_reason TEXT,
  change_time DATETIME NOT NULL,
  FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
  INDEX idx_candidate_id (candidate_id),
  INDEX idx_change_time (change_time)
);

-- 职位状态历史表
CREATE TABLE job_status_history (
  id INT PRIMARY KEY AUTO_INCREMENT,
  job_id INT NOT NULL,
  old_status ENUM('active', 'paused', 'draft') NOT NULL,
  new_status ENUM('active', 'paused', 'draft') NOT NULL,
  changed_by VARCHAR(50) NOT NULL,
  change_time DATETIME NOT NULL,
  FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
  INDEX idx_job_id (job_id),
  INDEX idx_change_time (change_time)
);

-- 用户表
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  full_name VARCHAR(100),
  department VARCHAR(50),
  role ENUM('admin', 'recruiter', 'interviewer', 'viewer') NOT NULL DEFAULT 'viewer',
  created_at DATETIME NOT NULL,
  last_login_at DATETIME,
  INDEX idx_username (username),
  INDEX idx_role (role)
);

-- 系统配置表
CREATE TABLE system_config (
  id INT PRIMARY KEY AUTO_INCREMENT,
  config_key VARCHAR(50) NOT NULL UNIQUE,
  config_value TEXT,
  description VARCHAR(255),
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);

-- 插入示例数据 - 技能表
INSERT INTO skills (name, category, created_at) VALUES
('Vue.js', '前端开发', NOW()),
('React', '前端开发', NOW()),
('TypeScript', '编程语言', NOW()),
('JavaScript', '编程语言', NOW()),
('HTML5', '前端开发', NOW()),
('CSS3', '前端开发', NOW()),
('Java', '后端开发', NOW()),
('Spring Boot', '后端框架', NOW()),
('MySQL', '数据库', NOW()),
('Redis', '缓存', NOW()),
('Figma', '设计工具', NOW()),
('Sketch', '设计工具', NOW()),
('Adobe Creative Suite', '设计工具', NOW());

-- 插入示例数据 - 用户表
INSERT INTO users (username, password_hash, email, full_name, department, role, created_at) VALUES
('admin', '$2a$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@example.com', '系统管理员', '人力资源部', 'admin', NOW()),
('recruiter1', '$2a$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'recruiter1@example.com', '招聘专员A', '人力资源部', 'recruiter', NOW()),
('interviewer1', '$2a$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'interviewer1@example.com', '面试官A', '技术部', 'interviewer', NOW());

-- 插入示例数据 - 系统配置表
INSERT INTO system_config (config_key, config_value, description, created_at, updated_at) VALUES
('system_name', '智能招聘系统', '系统名称', NOW(), NOW()),
('default_ai_score_threshold', '75', 'AI筛选默认分数阈值', NOW(), NOW()),
('max_interview_duration', '120', '最大面试时长(分钟)', NOW(), NOW());

-- 插入示例数据 - 职位表
INSERT INTO jobs (title, department, location, description, status, pending_resumes, passed_resumes, ai_criteria, created_at, updated_at)
VALUES
('Vue.js前端工程师', '技术部', '上海', '负责前端产品开发，具备Vue.js、TypeScript等技术栈经验', 'active', 2, 1, 
'{"education":{"condition":">=","value":"本科","weight":20},"experience":{"condition":">=","value":"3年","weight":30},"skills":{"condition":"includes","value":"Vue.js,TypeScript","weight":35},"language":{"condition":">=","value":"英语四级","weight":15}}',
'2024-01-15 00:00:00', '2024-01-20 00:00:00'),
('Java后端工程师', '技术部', '北京', '负责后端服务开发，熟悉Spring Boot、微服务架构', 'active', 1, 1, 
'{"education":{"condition":">=","value":"本科","weight":25},"experience":{"condition":">=","value":"5年","weight":35},"skills":{"condition":"includes","value":"Java,Spring Boot,MySQL","weight":30},"language":{"condition":">=","value":"英语四级","weight":10}}',
'2024-01-10 00:00:00', '2024-01-18 00:00:00'),
('UI/UX设计师', '设计部', '深圳', '负责产品界面和用户体验设计', 'active', 1, 0, 
'{"education":{"condition":">=","value":"专科","weight":15},"experience":{"condition":">=","value":"2年","weight":25},"skills":{"condition":"includes","value":"Figma,Sketch,Adobe","weight":45},"language":{"condition":">=","value":"无要求","weight":15}}',
'2024-01-12 00:00:00', '2024-01-19 00:00:00');

-- 插入示例数据 - 候选人表
INSERT INTO candidates (name, job_id, job_title, match_score, status, highlights, concerns, summary, resume_text, education, experience, skills, submitted_at, reviewed_at)
VALUES
('张小明', 1, 'Vue.js前端工程师', 88, 'pending', '["5年Vue.js开发经验", "熟悉TypeScript", "有大型项目经验", "具备团队协作能力"]', '["缺少移动端开发经验", "团队管理经验不足"]', 
'具备扎实的前端技术基础，Vue.js经验丰富，适合高级前端开发岗位。在技术深度和项目经验方面表现出色，但在移动端开发和团队管理方面还有提升空间。',
'张小明，本科学历，计算机科学专业毕业。拥有5年前端开发经验，精通Vue.js、TypeScript等现代前端技术栈。曾在多家互联网公司担任前端工程师，参与过多个大型项目的开发...',
'本科 - 计算机科学与技术', '5年前端开发经验', '["Vue.js", "TypeScript", "JavaScript", "CSS3", "Webpack"]', '2024-01-20 00:00:00', NULL),
('李小红', 1, 'Vue.js前端工程师', 75, 'pending', '["3年前端开发经验", "熟悉React和Vue.js", "有组件库开发经验"]', '["TypeScript经验较少", "项目规模偏小", "缺少性能优化经验"]', 
'前端基础良好，技术栈匹配度较高，有一定成长潜力。在组件化开发方面有实践经验，但在TypeScript和大型项目经验方面需要进一步提升。',
'李小红，本科学历，软件工程专业。3年前端开发经验，熟悉Vue.js和React框架，有组件库开发经验...',
'本科 - 软件工程', '3年前端开发经验', '["Vue.js", "React", "JavaScript", "HTML5", "CSS3"]', '2024-01-19 00:00:00', NULL),
('王大强', 2, 'Java后端工程师', 92, 'passed', '["8年Java开发经验", "精通Spring Boot", "有微服务架构经验", "熟悉分布式系统"]', '["缺少云原生经验", "团队规模相对较小"]', 
'资深Java后端工程师，技术功底扎实，项目经验丰富。在微服务架构和分布式系统方面有深入理解，是团队的技术骨干候选人。',
'王大强，硕士学历，8年Java后端开发经验。精通Spring Boot框架，有丰富的微服务架构设计和实施经验...',
'硕士 - 计算机科学', '8年Java后端开发经验', '["Java", "Spring Boot", "MySQL", "Redis", "Kafka"]', '2024-01-18 00:00:00', '2024-01-19 00:00:00'),
('陈小美', 3, 'UI/UX设计师', 85, 'interviewed', '["4年UI/UX设计经验", "精通Figma和Sketch", "有B端产品设计经验", "用户体验思维强"]', '["缺少移动端设计经验", "技术理解需要加强"]', 
'优秀的UI/UX设计师，设计功底扎实，用户体验意识强。在B端产品设计方面有丰富经验，但在移动端设计和技术理解方面还有提升空间。',
'陈小美，本科学历，视觉传达设计专业。4年UI/UX设计经验，精通各类设计工具，有丰富的B端产品设计经验...',
'本科 - 视觉传达设计', '4年UI/UX设计经验', '["Figma", "Sketch", "Adobe Creative Suite", "Principle", "Axure"]', '2024-01-17 00:00:00', '2024-01-18 00:00:00'),
('刘小刚', 2, 'Java后端工程师', 65, 'rejected', '["2年Java开发经验", "熟悉Spring框架"]', '["经验不足", "缺少大型项目经验", "技术深度有限", "沟通能力待提升"]', 
'初级Java开发工程师，基础技能掌握良好，但经验和技术深度不足以满足当前岗位要求。建议继续积累项目经验后再申请。',
'刘小刚，专科学历，2年Java开发经验。熟悉Spring框架，参与过小型项目开发...',
'专科 - 计算机应用技术', '2年Java开发经验', '["Java", "Spring", "MySQL", "HTML", "CSS"]', '2024-01-16 00:00:00', '2024-01-17 00:00:00');

-- 插入示例数据 - 候选人技能关联表
INSERT INTO candidate_skills (candidate_id, skill_id, proficiency) VALUES
(1, 1, 'advanced'),  -- Vue.js
(1, 3, 'advanced'),  -- TypeScript
(1, 4, 'advanced'),  -- JavaScript
(1, 5, 'intermediate'),  -- HTML5
(1, 6, 'intermediate'),  -- CSS3
(2, 1, 'intermediate'),  -- Vue.js
(2, 2, 'intermediate'),  -- React
(2, 4, 'advanced'),  -- JavaScript
(3, 7, 'advanced'),  -- Java
(3, 8, 'advanced'),  -- Spring Boot
(3, 9, 'intermediate'),  -- MySQL
(3, 10, 'intermediate'),  -- Redis
(4, 11, 'advanced'),  -- Figma
(4, 12, 'advanced'),  -- Sketch
(4, 13, 'intermediate'),  -- Adobe Creative Suite
(5, 7, 'intermediate'),  -- Java
(5, 8, 'basic'),  -- Spring Boot
(5, 9, 'intermediate');  -- MySQL

-- 插入示例数据 - 面试表
INSERT INTO interviews (title, description, questions, duration, created_at)
VALUES
('Vue.js前端工程师初面', '主要考察前端基础知识和Vue.js技术栈', 
'["Vue 3的Composition API相比Options API有什么优势？", "请解释Vue的响应式原理", "如何优化Vue应用的性能？", "描述一个你负责的复杂前端项目"]',
60, '2024-01-15 00:00:00'),
('Java后端工程师技术面试', '重点考察Java技术栈和系统设计能力', 
'["Spring Boot的自动配置原理是什么？", "如何设计一个高并发的秒杀系统？", "MySQL的索引优化策略有哪些？", "微服务架构的优缺点是什么？"]',
90, '2024-01-12 00:00:00'),
('UI/UX设计师面试', '考察设计理念和项目经验', 
'["你如何理解用户体验设计？", "描述一个你参与的成功设计案例", "你常用的设计工具和工作流程是什么？", "如何与开发团队协作实现设计方案？"]',
45, '2024-01-18 00:00:00');

-- 插入示例数据 - 候选人-面试关联表
INSERT INTO candidate_interviews (candidate_id, interview_id, interview_time, interviewer_id, notes, result)
VALUES
(1, 1, '2024-01-22 10:00:00', '1', '安排在上午10点，面试官：技术部张组长', 'scheduled'),
(3, 2, '2024-01-21 14:30:00', '1', '安排在下午2点半，面试官：技术总监李总', 'scheduled'),
(4, 3, '2024-01-20 16:00:00', '2', '安排在下午4点，面试官：设计部王经理', 'completed');

-- 插入示例数据 - 候选人状态历史表
INSERT INTO candidate_status_history (candidate_id, old_status, new_status, changed_by, change_reason, change_time)
VALUES
(3, 'pending', 'passed', 'recruiter1', 'AI筛选通过，匹配度高', '2024-01-19 09:30:00'),
(4, 'pending', 'interviewed', 'recruiter1', '已完成面试', '2024-01-18 17:30:00'),
(5, 'pending', 'rejected', 'recruiter1', '经验不足，技术深度有限', '2024-01-17 11:45:00');

-- 插入示例数据 - 职位状态历史表
INSERT INTO job_status_history (job_id, old_status, new_status, changed_by, change_time)
VALUES
(1, 'draft', 'active', 'admin', '2024-01-15 09:00:00'),
(2, 'draft', 'active', 'admin', '2024-01-10 14:30:00'),
(3, 'draft', 'active', 'admin', '2024-01-12 11:15:00');