12.4

-- 1. 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS attendance_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- 使用数据库
USE attendance_db;

-- 2. 创建部门表 (department)
CREATE TABLE IF NOT EXISTS department (
department_id VARCHAR(2) PRIMARY KEY COMMENT '部门编码，两位数字',
department_name VARCHAR(50) NOT NULL UNIQUE COMMENT '部门名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='部门信息表';

-- 3. 创建员工表 (employee)
CREATE TABLE IF NOT EXISTS employee (
job_id VARCHAR(8) PRIMARY KEY COMMENT '工号，8位数字，格式：20190001',
name VARCHAR(50) NOT NULL COMMENT '姓名',
sex ENUM('男', '女') NOT NULL COMMENT '性别',
birthday VARCHAR(10) NOT NULL COMMENT '出生日期，格式：2000-01-01',
department_id VARCHAR(2) COMMENT '部门编码',
role ENUM('管理员', '部门经理', '员工') DEFAULT '员工' COMMENT '角色',
password VARCHAR(100) NOT NULL DEFAULT '123456' COMMENT '密码（MD5加密存储）',

-- 添加索引和外键
INDEX idx_department (department_id),
INDEX idx_role (role),
FOREIGN KEY (department_id) REFERENCES department(department_id) 
ON DELETE SET NULL ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='员工信息表';

-- 4. 创建考勤记录表 (attendance)
CREATE TABLE IF NOT EXISTS attendance (
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '序号，自增长',
attendance_time DATETIME NOT NULL COMMENT '考勤时间',
job_id VARCHAR(8) NOT NULL COMMENT '工号',
name VARCHAR(50) NOT NULL COMMENT '姓名',
sex ENUM('男', '女') NOT NULL COMMENT '性别',
birthday VARCHAR(10) NOT NULL COMMENT '出生日期',
department_id VARCHAR(2) COMMENT '部门编码',
attendance_type INT NOT NULL COMMENT '考勤类型：0上班；1下班；2事假；3病假',
notes VARCHAR(200) DEFAULT '' COMMENT '请假事由',
approved_type INT DEFAULT 0 COMMENT '审批状态：0未审批；1已审批通过',

-- 添加索引和外键
INDEX idx_job_id (job_id),
INDEX idx_attendance_time (attendance_time),
INDEX idx_department_id (department_id),
INDEX idx_attendance_type (attendance_type),
FOREIGN KEY (job_id) REFERENCES employee(job_id) 
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (department_id) REFERENCES department(department_id) 
ON DELETE SET NULL ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='考勤记录表';