-- 部门表
CREATE TABLE IF NOT EXISTS departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    location VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 员工表
CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    phone VARCHAR(50),
    salary DECIMAL(10,2) DEFAULT 0,
    department_id INT,
    manager_id INT,
    status VARCHAR(20) DEFAULT 'active',
    join_date DATE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (manager_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 项目表
CREATE TABLE IF NOT EXISTS projects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    description TEXT,
    start_date DATE,
    end_date DATE,
    budget DECIMAL(12,2),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 员工项目关联表
CREATE TABLE IF NOT EXISTS employee_projects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    project_id INT NOT NULL,
    role VARCHAR(100),
    join_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    UNIQUE(employee_id, project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 薪资记录表
CREATE TABLE IF NOT EXISTS salary_records (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    effective_date DATE NOT NULL,
    reason TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ============================================
-- 项目实战练习数据
-- ============================================

-- 插入部门数据
INSERT INTO departments (name, location) VALUES
('技术部', '北京'),
('市场部', '上海'),
('人事部', '广州'),
('财务部', '深圳'),
('产品部', '杭州');

-- 插入员工数据
INSERT INTO employees (name, email, phone, salary, department_id, manager_id, status, join_date) VALUES
('张伟', 'zhangwei@example.com', '13800138001', 15000, 1, NULL, 'active', '2023-01-15'),
('李娜', 'lina@example.com', '13800138002', 12000, 1, 1, 'active', '2023-02-20'),
('王强', 'wangqiang@example.com', '13800138003', 18000, 1, 1, 'active', '2022-11-10'),
('赵敏', 'zhaomin@example.com', '13800138004', 11000, 2, NULL, 'active', '2023-03-05'),
('陈杰', 'chenjie@example.com', '13800138005', 9000, 2, 4, 'active', '2023-04-12'),
('刘洋', 'liuyang@example.com', '13800138006', 13000, 2, 4, 'active', '2022-12-25'),
('杨梅', 'yangmei@example.com', '13800138007', 10000, 3, NULL, 'active', '2023-05-08'),
('周涛', 'zhoutao@example.com', '13800138008', 8500, 3, 7, 'inactive', '2022-08-15'),
('吴芳', 'wufang@example.com', '13800138009', 16000, 4, NULL, 'active', '2022-09-20'),
('郑军', 'zhengjun@example.com', '13800138010', 14000, 4, 9, 'active', '2023-06-01'),
('孙丽', 'sunli@example.com', '13800138011', 9500, 3, 7, 'active', '2023-07-10'),
('钱伟', 'qianwei@example.com', '13800138012', 17000, 5, NULL, 'active', '2022-10-05'),
('马红', 'mahong@example.com', '13800138013', 10500, 5, 12, 'active', '2023-08-15'),
('朱明', 'zhuming@example.com', '13800138014', 8800, 1, 1, 'active', '2023-09-01'),
('胡婷', 'huting@example.com', '13800138015', 11500, 2, 4, 'active', '2023-10-08');

-- 插入项目数据
INSERT INTO projects (name, description, start_date, end_date, budget) VALUES
('电商平台开发', '开发新的电商销售平台', '2024-01-01', '2024-12-31', 500000),
('移动APP升级', '升级现有移动应用', '2024-03-01', '2024-09-30', 300000),
('数据分析系统', '搭建企业数据分析平台', '2024-02-15', '2024-11-30', 400000),
('客户管理系统', '优化客户关系管理系统', '2024-04-01', '2024-10-31', 250000),
('内部办公系统', '开发企业内部办公工具', '2024-05-01', '2025-02-28', 350000);

-- 插入员工项目关联数据
INSERT INTO employee_projects (employee_id, project_id, role) VALUES
(1, 1, '项目经理'),
(2, 1, '前端开发'),
(3, 1, '后端开发'),
(1, 3, '技术顾问'),
(3, 3, '架构师'),
(4, 2, '产品经理'),
(5, 2, 'UI设计'),
(6, 4, '市场调研'),
(12, 5, '产品总监'),
(13, 5, '产品经理');

-- 插入薪资记录数据
INSERT INTO salary_records (employee_id, salary, effective_date, reason) VALUES
(1, 15000, '2023-01-15', '入职薪资'),
(1, 16500, '2024-01-15', '年度调薪'),
(2, 12000, '2023-02-20', '入职薪资'),
(3, 18000, '2022-11-10', '入职薪资'),
(4, 11000, '2023-03-05', '入职薪资'),
(9, 16000, '2022-09-20', '入职薪资'),
(12, 17000, '2022-10-05', '入职薪资');
