init_db.sql

-- 创建数据库
CREATE DATABASE IF NOT EXISTS uemp_db DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE uemp_db;

-- 1. 潜在客户表
CREATE TABLE IF NOT EXISTS potential_customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    gender VARCHAR(10),
    age INT,
    education VARCHAR(50),
    phone VARCHAR(20),
    source_type VARCHAR(20),
    source_content TEXT,
    matched_project VARCHAR(200),
    match_score INT DEFAULT 0,
    match_reason TEXT,
    status VARCHAR(20) DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 员工表
CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_no VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(100),
    position VARCHAR(100),
    phone VARCHAR(20),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. 日报记录表
CREATE TABLE IF NOT EXISTS daily_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id VARCHAR(50) NOT NULL,
    report_date DATE NOT NULL,
    today_work TEXT,
    completion_status VARCHAR(200),
    tomorrow_plan TEXT,
    problems TEXT,
    needs_support TEXT,
    status VARCHAR(20) DEFAULT 'submitted',  -- submitted/approved/rejected
    review_comment TEXT,
    reviewer_id VARCHAR(50),
    reviewed_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_emp_date (employee_id, report_date),
    INDEX idx_employee (employee_id),
    INDEX idx_date (report_date),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_no) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. 公司组织架构表
CREATE TABLE IF NOT EXISTS org_structure (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL,
    parent_dept VARCHAR(100),
    dept_level INT DEFAULT 1,
    manager VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. 日报总结表
CREATE TABLE IF NOT EXISTS daily_summaries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    report_date DATE NOT NULL UNIQUE,
    summary_content TEXT,
    dept_data JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. 周报总结表
CREATE TABLE IF NOT EXISTS weekly_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    week_start DATE NOT NULL,
    week_end DATE NOT NULL,
    title VARCHAR(200),
    content TEXT,
    created_by VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_week (week_start, week_end)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入示例员工数据
INSERT IGNORE INTO employees (employee_no, name, department, position) VALUES
('EMP001', '张三', '销售部', '销售经理'),
('EMP002', '李四', '销售部', '销售代表'),
('EMP003', '王五', '技术部', '工程师');