-- 文件名：view_creation_scripts.sql
-- 这是为员工考勤管理系统编写的视图创建脚本

-- 1. 员工考勤视图
CREATE VIEW EmployeeAttendanceView AS
SELECT 
    E.Name AS EmployeeName,
    A.AttendanceDate,
    A.CheckInTime,
    A.CheckOutTime,
    A.WorkingHours,
    A.OvertimeHours,
    A.AttendanceStatus
FROM 
    Employee E
JOIN 
    Attendance A ON E.EmployeeID = A.EmployeeID;

-- 2. 部门员工视图
CREATE VIEW DepartmentEmployeeView AS
SELECT
    D.DepartmentName,
    E.Name AS EmployeeName,
    E.Position,
    E.HireDate,
    E.EmploymentStatus
FROM 
    Department D
JOIN 
    Employee E ON D.DepartmentID = E.DepartmentID;

-- 3. 假期申请状态视图
CREATE VIEW LeaveRequestStatusView AS
SELECT 
    E.Name AS EmployeeName,
    L.LeaveType,
    L.LeaveDays,
    L.RequestStatus,
    A.Name AS ApproverName
FROM 
    LeaveRequest L
JOIN 
    Employee E ON L.EmployeeID = E.EmployeeID
LEFT JOIN 
    Employee A ON L.ApproverID = A.EmployeeID;

-- 4. 绩效考核视图
CREATE VIEW PerformanceReviewView AS
SELECT 
    E.Name AS EmployeeName,
    P.ReviewDate,
    P.ReviewPeriod,
    P.ReviewScore,
    P.ReviewGrade,
    A.Name AS ReviewerName
FROM 
    PerformanceAppraisal P
JOIN 
    Employee E ON P.EmployeeID = E.EmployeeID
LEFT JOIN 
    Employee A ON P.ReviewerID = A.EmployeeID;

-- 5.部门详细信息视图
CREATE VIEW DepartmentDetailsView AS
SELECT 
    D.DepartmentID,
    D.DepartmentName,
    D.ManagerID,
    M.Name AS ManagerName,
    COUNT(E.EmployeeID) AS TotalEmployees
FROM 
    Department D
LEFT JOIN 
    Employee M ON D.ManagerID = M.EmployeeID
LEFT JOIN 
    Employee E ON D.DepartmentID = E.DepartmentID
GROUP BY 
    D.DepartmentID, D.DepartmentName, D.ManagerID, M.Name;