-- 创建数据库，如果不存在
CREATE DATABASE IF NOT EXISTS attendance_system CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 使用创建的数据库
USE attendance_system;

-- 1. 创建员工表（Employee），该表需要先创建
CREATE TABLE IF NOT EXISTS Employee (
    EmployeeID VARCHAR(50) PRIMARY KEY,       -- 员工ID，主键
    Name VARCHAR(100) NOT NULL,               -- 姓名
    Gender VARCHAR(10) NOT NULL,              -- 性别（男/女）
    DateOfBirth DATE NOT NULL DEFAULT '2000-01-01',  -- 出生日期，默认值
    Position VARCHAR(100) NOT NULL,           -- 职位
    DepartmentID VARCHAR(50),                 -- 部门ID
    ContactInfo VARCHAR(50),                  -- 联系方式
    HireDate DATE NOT NULL,                   -- 入职日期
    EmploymentStatus VARCHAR(50) NOT NULL     -- 状态（在职/离职/休假）
);

-- 2. 创建部门表（Department），需要在Employee表后创建，因为Department表需要引用Employee表中的ManagerID
CREATE TABLE IF NOT EXISTS Department (
    DepartmentID VARCHAR(50) PRIMARY KEY,    -- 部门ID，主键
    DepartmentName VARCHAR(100) NOT NULL,     -- 部门名称
    ManagerID VARCHAR(50),                    -- 主管ID，外键，关联到员工表
    FOREIGN KEY (ManagerID) REFERENCES Employee(EmployeeID)  -- 外键，关联到员工表
);

-- 3. 创建考勤记录表（Attendance），该表依赖Employee表
CREATE TABLE IF NOT EXISTS Attendance (
    AttendanceID VARCHAR(50) PRIMARY KEY,    -- 考勤ID，主键
    EmployeeID VARCHAR(50),                   -- 员工ID，外键，关联到员工表
    AttendanceDate DATE NOT NULL,             -- 考勤日期
    CheckInTime DATETIME,                     -- 上班时间
    CheckOutTime DATETIME,                    -- 下班时间
    WorkingHours INT,                         -- 工作时长（小时）
    OvertimeHours INT,                        -- 加班时长（小时）
    AttendanceStatus VARCHAR(20) NOT NULL,    -- 考勤状态（正常/迟到/早退/缺勤）
    FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)  -- 外键，关联到员工表
);

-- 4. 创建假期申请表（LeaveRequest），该表依赖Employee表
CREATE TABLE IF NOT EXISTS LeaveRequest (
    RequestID VARCHAR(50) PRIMARY KEY,        -- 申请ID，主键
    EmployeeID VARCHAR(50),                   -- 员工ID，外键，关联到员工表
    LeaveType VARCHAR(50) NOT NULL,            -- 假期类型
    StartDate DATE NOT NULL,                  -- 开始日期
    EndDate DATE NOT NULL,                    -- 结束日期
    LeaveDays INT NOT NULL,                   -- 假期天数
    RequestStatus VARCHAR(20) NOT NULL,       -- 申请状态（待审批/已批准/已拒绝）
    ApproverID VARCHAR(50),                   -- 审批人ID，外键，关联到员工表
    Remarks TEXT,                             -- 备注
    FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID),  -- 外键，关联到员工表
    FOREIGN KEY (ApproverID) REFERENCES Employee(EmployeeID)   -- 外键，关联到员工表
);

-- 5. 创建用户角色表（UserRoles）
CREATE TABLE IF NOT EXISTS UserRoles (
    RoleID VARCHAR(50) PRIMARY KEY,           -- 角色ID，主键
    RoleName VARCHAR(100) NOT NULL,           -- 角色名称
    RoleDescription TEXT NOT NULL             -- 角色描述
);

-- 6. 创建用户账号表（UserLogin），该表依赖Employee表和UserRoles表
CREATE TABLE IF NOT EXISTS UserLogin (
    UserID VARCHAR(50) PRIMARY KEY,           -- 用户ID，主键
    EmployeeID VARCHAR(50),                   -- 员工ID，外键，关联到员工表
    RoleID VARCHAR(50),                       -- 角色ID，外键，关联到用户角色表
    Username VARCHAR(50) NOT NULL,            -- 用户名
    Password VARCHAR(255) NOT NULL,           -- 密码（加密存储）
    LastLoginTime DATETIME,                   -- 最后登录时间
    AccountStatus VARCHAR(20) NOT NULL,       -- 账号状态（有效/禁用）
    FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID),  -- 外键，关联到员工表
    FOREIGN KEY (RoleID) REFERENCES UserRoles(RoleID)          -- 外键，关联到用户角色表
);

-- 7. 创建考勤统计表（AttendanceSummary），该表依赖Employee表
CREATE TABLE IF NOT EXISTS AttendanceSummary (
    SummaryID VARCHAR(50) PRIMARY KEY,        -- 统计ID，主键
    EmployeeID VARCHAR(50),                   -- 员工ID，外键，关联到员工表
    Month DATE NOT NULL,                      -- 统计月份
    PresentDays INT,                          -- 出勤天数
    LateCount INT,                            -- 迟到次数
    EarlyLeaveCount INT,                      -- 早退次数
    AbsentDays INT,                           -- 缺勤天数
    TotalOvertimeHours INT,                   -- 加班总时长（小时）
    FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)  -- 外键，关联到员工表
);

-- 8. 创建系统日志表（SystemLogs），该表依赖UserLogin表
CREATE TABLE IF NOT EXISTS SystemLogs (
    LogID VARCHAR(50) PRIMARY KEY,            -- 日志ID，主键
    UserID VARCHAR(50),                       -- 用户ID，外键，关联到用户账号表
    ActionType VARCHAR(50) NOT NULL,          -- 操作类型
    ActionTime DATETIME NOT NULL,             -- 操作时间
    Description TEXT,                         -- 操作描述
    FOREIGN KEY (UserID) REFERENCES UserLogin(UserID)  -- 外键，关联到用户账号表
);

-- 9. 创建培训记录表（TrainingRecords），该表依赖Employee表
CREATE TABLE IF NOT EXISTS TrainingRecords (
    TrainingID VARCHAR(50) PRIMARY KEY,       -- 培训ID，主键
    EmployeeID VARCHAR(50),                   -- 员工ID，外键，关联到员工表
    TrainingName VARCHAR(100) NOT NULL,       -- 培训名称
    TrainingDate DATE NOT NULL,               -- 培训日期
    TrainingHours INT,                        -- 培训时长（小时）
    TrainingStatus VARCHAR(50) NOT NULL,      -- 培训状态（已完成/进行中/未完成）
    FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)  -- 外键，关联到员工表
);

-- 10. 创建绩效考核表（PerformanceAppraisal），该表依赖Employee表
CREATE TABLE IF NOT EXISTS PerformanceAppraisal (
    ReviewID VARCHAR(50) PRIMARY KEY,         -- 考核ID，主键
    EmployeeID VARCHAR(50),                   -- 员工ID，外键，标识被考核的员工
    ReviewDate DATE NOT NULL,                 -- 考核日期
    ReviewPeriod VARCHAR(50) NOT NULL,        -- 考核周期（季度/年度）
    ReviewScore INT,                          -- 考核评分
    ReviewGrade VARCHAR(50),                  -- 考核等级（A/B/C等）
    ReviewerID VARCHAR(50),                   -- 考核人ID，外键，标识考核主管
    FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID),   -- 外键，关联到员工表
    FOREIGN KEY (ReviewerID) REFERENCES Employee(EmployeeID)    -- 外键，关联到员工表
);

-- 提示：所有的表创建完成后，可以执行数据插入脚本（如 `initialize_data.sql`）。