YLHP_HR_2.0/migrations/001_init_schema.sql

-- 简历智能体系统 - 数据库初始化脚本
-- 支持: MySQL 8.0+ / SQLite 3 / PostgreSQL

-- ============================================
-- 1. 招聘者账号表 (存储各平台账号信息)
-- ============================================
CREATE TABLE IF NOT EXISTS recruiters (
    id VARCHAR(64) PRIMARY KEY,
    name VARCHAR(128) NOT NULL,             -- 招聘者名称/标识
    source VARCHAR(32) NOT NULL,            -- 平台: BOSS, LIEPIN, etc.
    wt_token VARCHAR(512) NOT NULL,         -- WT Token (加密存储)
    status VARCHAR(32) DEFAULT 'ACTIVE',    -- ACTIVE, INACTIVE, EXPIRED
    last_used_at TIMESTAMP,                 -- 最后使用时间
    
    -- 账号权益信息
    vip_level VARCHAR(32),                  -- VIP等级
    vip_status VARCHAR(32),                 -- VIP状态
    vip_expire_at TIMESTAMP,                -- VIP过期时间
    resume_view_count INT DEFAULT 0,        -- 剩余简历查看次数
    resume_view_total INT DEFAULT 0,        -- 总简历查看次数
    
    -- 账号统计信息
    last_sync_at TIMESTAMP,                 -- 最后同步时间
    sync_status VARCHAR(32),                -- 同步状态: SUCCESS, FAILED, PENDING
    sync_error TEXT,                        -- 同步错误信息
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_source_token (source, wt_token(255)),
    INDEX idx_status (status),
    INDEX idx_sync_status (sync_status),
    INDEX idx_last_sync_at (last_sync_at)
);

-- ============================================
-- 2. 候选人主表
-- ============================================
CREATE TABLE IF NOT EXISTS candidates (
    id VARCHAR(64) PRIMARY KEY,
    source VARCHAR(32) NOT NULL,           -- BOSS, LIEPIN, ZHILIAN, OTHER
    source_id VARCHAR(128) NOT NULL,       -- 来源平台ID
    name VARCHAR(64) NOT NULL,
    phone VARCHAR(32),
    email VARCHAR(128),
    wechat VARCHAR(64),
    gender TINYINT DEFAULT 0,              -- 0:未知, 1:男, 2:女
    age INT,
    location VARCHAR(128),
    current_company VARCHAR(256),
    current_position VARCHAR(128),
    work_years DECIMAL(4,1),
    education VARCHAR(64),
    school VARCHAR(256),
    salary_min INT,                        -- 期望薪资下限(K)
    salary_max INT,                        -- 期望薪资上限(K)
    status VARCHAR(32) DEFAULT 'NEW',      -- NEW, ANALYZED, PUSHED, CONTACTED, INTERVIEWED, HIRED, REJECTED
    llm_filtered TINYINT DEFAULT 0,        -- 是否通过LLM筛选: 0-否, 1-是
    llm_score DECIMAL(4,1),                -- LLM综合评分 0-100
    llm_score_details JSON,                -- LLM各维度评分详情
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_source_source_id (source, source_id),
    INDEX idx_phone (phone),
    INDEX idx_email (email),
    INDEX idx_name (name),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_llm_filtered (llm_filtered),
    INDEX idx_llm_score (llm_score),
    INDEX idx_llm_filtered_score (llm_filtered, llm_score)
);

-- ============================================
-- 3. 简历内容表
-- ============================================
CREATE TABLE IF NOT EXISTS resumes (
    id VARCHAR(64) PRIMARY KEY,
    candidate_id VARCHAR(64) NOT NULL,
    raw_content TEXT,                       -- 原始简历文本
    parsed_content JSON,                    -- 结构化解析内容
    attachment_url VARCHAR(512),            -- 附件URL
    attachment_type VARCHAR(32),            -- pdf, doc, docx
    version INT DEFAULT 1,                  -- 版本号
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    INDEX idx_candidate_id (candidate_id)
);

-- ============================================
-- 4. 职位信息表
-- ============================================
CREATE TABLE IF NOT EXISTS jobs (
    id VARCHAR(64) PRIMARY KEY,
    source VARCHAR(32) NOT NULL,            -- BOSS, LIEPIN, etc.
    source_id VARCHAR(128) NOT NULL,
    recruiter_id VARCHAR(64),               -- 关联的招聘者账号ID
    evaluation_schema_id VARCHAR(64),       -- 关联的评价方案ID
    title VARCHAR(256) NOT NULL,
    department VARCHAR(128),
    location VARCHAR(128),
    salary_min INT,
    salary_max INT,
    requirements TEXT,                      -- 职位要求JSON
    description TEXT,                       -- 职位描述
    status VARCHAR(32) DEFAULT 'ACTIVE',    -- ACTIVE, PAUSED, CLOSED, ARCHIVED
    
    -- 职位统计信息
    candidate_count INT DEFAULT 0,          -- 候选人数量
    new_candidate_count INT DEFAULT 0,      -- 新候选人数量
    last_sync_at TIMESTAMP,                 -- 最后同步时间
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_source_source_id (source, source_id),
    INDEX idx_status (status),
    INDEX idx_recruiter_id (recruiter_id),
    INDEX idx_evaluation_schema_id (evaluation_schema_id),
    INDEX idx_last_sync_at (last_sync_at),
    FOREIGN KEY (recruiter_id) REFERENCES recruiters(id) ON DELETE SET NULL
);

-- ============================================
-- 5. 评价方案表
-- ============================================
CREATE TABLE IF NOT EXISTS evaluation_schemas (
    id VARCHAR(64) PRIMARY KEY,
    name VARCHAR(128) NOT NULL,
    description TEXT,
    dimensions JSON NOT NULL,               -- 评价维度配置
    weights JSON NOT NULL,                  -- 维度权重
    prompt_template TEXT,                   -- AI提示词模板
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_is_default (is_default)
);

-- ============================================
-- 6. 评价记录表
-- ============================================
CREATE TABLE IF NOT EXISTS evaluations (
    id VARCHAR(64) PRIMARY KEY,
    candidate_id VARCHAR(64) NOT NULL,
    schema_id VARCHAR(64) NOT NULL,
    job_id VARCHAR(64),
    overall_score DECIMAL(4,1),             -- 综合评分 0-100
    dimension_scores JSON,                  -- 各维度评分详情
    tags JSON,                              -- AI标签
    summary TEXT,                           -- 评价摘要
    strengths JSON,                         -- 优势列表
    weaknesses JSON,                        -- 不足列表
    recommendation VARCHAR(32),             -- strong_recommend, recommend, consider, not_recommend
    raw_response TEXT,                      -- LLM原始响应
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    FOREIGN KEY (schema_id) REFERENCES evaluation_schemas(id),
    FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE SET NULL,
    INDEX idx_candidate_id (candidate_id),
    INDEX idx_schema_id (schema_id),
    INDEX idx_overall_score (overall_score),
    INDEX idx_created_at (created_at)
);

-- ============================================
-- 7. 通知渠道表
-- ============================================
CREATE TABLE IF NOT EXISTS notification_channels (
    id VARCHAR(64) PRIMARY KEY,
    name VARCHAR(128) NOT NULL,                    -- 渠道名称
    channel_type VARCHAR(32) NOT NULL,             -- 渠道类型: wechat_work, dingtalk, email, feishu, webhook
    config JSON NOT NULL,                          -- 渠道配置JSON
    status VARCHAR(32) DEFAULT 'active',           -- active, inactive
    description TEXT,                              -- 描述
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_channel_type (channel_type),
    INDEX idx_status (status)
);

-- ============================================
-- 8. 招聘者与通知渠道绑定关系表（多对多）
-- ============================================
CREATE TABLE IF NOT EXISTS recruiter_channel_bindings (
    id VARCHAR(64) PRIMARY KEY,
    recruiter_id VARCHAR(64) NOT NULL,
    channel_id VARCHAR(64) NOT NULL,
    is_enabled TINYINT(1) DEFAULT 1,               -- 是否启用该渠道
    notify_on_new_candidate TINYINT(1) DEFAULT 1,  -- 新候选人时通知
    notify_on_evaluation TINYINT(1) DEFAULT 1,     -- 完成评价时通知
    notify_on_high_score TINYINT(1) DEFAULT 0,     -- 高分候选人时通知
    high_score_threshold INT DEFAULT 85,           -- 高分阈值
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (recruiter_id) REFERENCES recruiters(id) ON DELETE CASCADE,
    FOREIGN KEY (channel_id) REFERENCES notification_channels(id) ON DELETE CASCADE,
    UNIQUE KEY uk_recruiter_channel (recruiter_id, channel_id),
    INDEX idx_recruiter_id (recruiter_id),
    INDEX idx_channel_id (channel_id)
);

-- ============================================
-- 9. 通知记录表
-- ============================================
CREATE TABLE IF NOT EXISTS notifications (
    id VARCHAR(64) PRIMARY KEY,
    candidate_id VARCHAR(64) NOT NULL,
    evaluation_id VARCHAR(64),
    channel_id VARCHAR(64),                 -- 关联的通知渠道ID
    channel VARCHAR(32) NOT NULL,           -- 渠道类型: WECHAT_WORK, DINGTALK, EMAIL, FEISHU, WEBHOOK
    content TEXT,
    status VARCHAR(32) DEFAULT 'PENDING',   -- PENDING, SENT, FAILED
    error_message TEXT,
    sent_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    FOREIGN KEY (evaluation_id) REFERENCES evaluations(id) ON DELETE SET NULL,
    FOREIGN KEY (channel_id) REFERENCES notification_channels(id) ON DELETE SET NULL,
    INDEX idx_candidate_id (candidate_id),
    INDEX idx_channel_id (channel_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- ============================================
-- 10. 插入默认通知渠道（可选）
-- ============================================
INSERT INTO notification_channels (id, name, channel_type, config, description) VALUES
('demo_wechat', '企业微信通知', 'wechat_work', 
 '{"webhook_url": "https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=YOUR_KEY", "secret": ""}',
 '企业微信机器人通知渠道（示例配置）')
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO notification_channels (id, name, channel_type, config, description) VALUES
('demo_dingtalk', '钉钉通知', 'dingtalk', 
 '{"access_token": "YOUR_ACCESS_TOKEN", "sign_secret": ""}',
 '钉钉机器人通知渠道（示例配置）')
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO notification_channels (id, name, channel_type, config, description) VALUES
('demo_feishu', '飞书通知', 'feishu', 
 '{"feishu_webhook": "https://open.feishu.cn/open-apis/bot/v2/hook/YOUR_TOKEN", "feishu_secret": ""}',
 '飞书机器人通知渠道（示例配置）')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- ============================================
-- 11. 插入默认评价方案
-- ============================================
INSERT INTO evaluation_schemas (id, name, description, dimensions, weights, is_default) VALUES
('general', '通用评价方案', '适用于各类岗位的通用评价方案', 
 '[
   {"id": "professional", "name": "专业能力", "description": "岗位相关专业技能水平"},
   {"id": "experience", "name": "工作经验", "description": "相关工作经验丰富度"},
   {"id": "education", "name": "教育背景", "description": "学历和专业匹配度"},
   {"id": "potential", "name": "发展潜力", "description": "未来成长空间"},
   {"id": "culture_fit", "name": "文化匹配", "description": "与企业文化的匹配度"}
 ]',
 '{"professional": 0.30, "experience": 0.25, "education": 0.15, "potential": 0.15, "culture_fit": 0.15}',
 TRUE)
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    description = VALUES(description),
    dimensions = VALUES(dimensions),
    weights = VALUES(weights);


INSERT INTO evaluation_schemas (id, name, description, dimensions, weights) VALUES
('java_backend', 'Java后端工程师评价方案', '针对Java后端开发岗位的综合评价方案',
 '[
   {"id": "tech_capability", "name": "技术能力", "description": "Java技术栈掌握程度", "criteria": ["Java基础扎实程度", "Spring生态熟悉度", "数据库设计与优化", "分布式系统经验"]},
   {"id": "project_exp", "name": "项目经验", "description": "项目经历的丰富度和质量", "criteria": ["项目复杂度", "承担角色重要性", "技术挑战解决能力"]},
   {"id": "learning_ability", "name": "学习能力", "description": "学习新技术和适应新环境的能力", "criteria": ["技术广度", "新技术掌握速度", "自我驱动学习"]},
   {"id": "communication", "name": "沟通协作", "description": "团队协作和沟通能力", "criteria": ["跨团队协作经验", "技术文档能力", "问题表达能力"]},
   {"id": "stability", "name": "稳定性", "description": "职业稳定性和忠诚度", "criteria": ["平均在职时长", "跳槽频率", "职业发展规划清晰度"]}
 ]',
 '{"tech_capability": 0.35, "project_exp": 0.25, "learning_ability": 0.15, "communication": 0.15, "stability": 0.10}')
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    description = VALUES(description),
    dimensions = VALUES(dimensions),
    weights = VALUES(weights);