mysql 简易版流程审批系统， 建表，含 sql 执行流程

-- 1. 用户表 (Users)
CREATE TABLE Users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    role VARCHAR(50), -- 例如: 'Admin', 'Approver', 'Requester'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 2. 流程表 (Processes)
CREATE TABLE Processes (
    process_id INT PRIMARY KEY AUTO_INCREMENT,
    process_name VARCHAR(100) NOT NULL,
    description TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES Users(user_id)
);
-- 3. 流程步骤表 (ProcessSteps)
CREATE TABLE ProcessSteps (
    step_id INT PRIMARY KEY AUTO_INCREMENT,
    process_id INT,
    step_name VARCHAR(100) NOT NULL,
    step_order INT,
    approver_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (process_id) REFERENCES Processes(process_id),
    FOREIGN KEY (approver_id) REFERENCES Users(user_id)
);
-- 4. 申请表 (Requests)
CREATE TABLE Requests (
    request_id INT PRIMARY KEY AUTO_INCREMENT,
    process_id INT,
    requester_id INT,
    status VARCHAR(50), -- 例如: 'Pending', 'Approved', 'Rejected'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (process_id) REFERENCES Processes(process_id),
    FOREIGN KEY (requester_id) REFERENCES Users(user_id)
);
-- 5. 申请步骤记录表 (RequestStepRecords)
CREATE TABLE RequestStepRecords (
    record_id INT PRIMARY KEY AUTO_INCREMENT,
    request_id INT,
    step_id INT,
    status VARCHAR(50), -- 例如: 'Pending', 'Approved', 'Rejected'
    comments TEXT,
    action_by INT,
    action_at TIMESTAMP,
    FOREIGN KEY (request_id) REFERENCES Requests(request_id),
    FOREIGN KEY (step_id) REFERENCES ProcessSteps(step_id),
    FOREIGN KEY (action_by) REFERENCES Users(user_id)
);
-- 6. 审批历史记录表 (ApprovalHistory)
CREATE TABLE ApprovalHistory (
    history_id INT PRIMARY KEY AUTO_INCREMENT,
    request_id INT,
    approver_id INT,
    status VARCHAR(50), -- 例如: 'Approved', 'Rejected'
    comments TEXT,
    action_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (request_id) REFERENCES Requests(request_id),
    FOREIGN KEY (approver_id) REFERENCES Users(user_id)
);


-- 1. 创建用户
INSERT INTO Users (username, password, email, role)
VALUES
('admin', 'password123', 'admin@example.com', 'Admin'),
('approver1', 'password123', 'approver1@example.com', 'Approver'),
('requester1', 'password123', 'requester1@example.com', 'Requester');

-- 2. 创建流程
INSERT INTO Processes (process_name, description, created_by)
VALUES ('Leave Approval', 'Approval process for leave requests', 1); -- 假设 admin 的 user_id 是 1

-- 3. 添加流程步骤
INSERT INTO ProcessSteps (process_id, step_name, step_order, approver_id)
VALUES
(1, 'Supervisor Approval', 1, 2), -- 假设 approver1 的 user_id 是 2
(1, 'HR Approval', 2, 2); -- 假设 HR 也是 approver1

-- 4. 发起审批请求
INSERT INTO Requests (process_id, requester_id, status)
VALUES (1, 3, 'Pending'); -- 假设 requester1 的 user_id 是 3

-- 5. 查看所有待审批的请求
SELECT r.request_id, r.process_id, r.requester_id, r.status, u.username as requester
FROM Requests r
JOIN Users u ON r.requester_id = u.user_id
WHERE r.status = 'Pending';

-- 6. 审批第一个步骤

-- 假设我们审批 request_id 为 1 的请求
INSERT INTO RequestStepRecords (request_id, step_id, status, comments, action_by, action_at)
VALUES (1, 1, 'Approved', 'Approved by supervisor', 2, NOW());

-- 更新请求状态，如果所有步骤都通过，则更新为 'Approved'，否则继续保持 'Pending'
UPDATE Requests
SET status = 'Approved'
WHERE request_id = 1 AND NOT EXISTS (
    SELECT 1
    FROM ProcessSteps ps
    LEFT JOIN RequestStepRecords rsr ON ps.step_id = rsr.step_id AND rsr.request_id = 1
    WHERE ps.process_id = 1 AND (rsr.status IS NULL OR rsr.status != 'Approved')
);

-- 插入审批历史记录
INSERT INTO ApprovalHistory (request_id, approver_id, status, comments, action_at)
VALUES (1, 2, 'Approved', 'Approved by supervisor', NOW());

-- 7. 审批第二个步骤

-- 假设我们继续审批 request_id 为 1 的请求
INSERT INTO RequestStepRecords (request_id, step_id, status, comments, action_by, action_at)
VALUES (1, 2, 'Approved', 'Approved by HR', 2, NOW());

-- 更新请求状态
UPDATE Requests
SET status = 'Approved'
WHERE request_id = 1 AND NOT EXISTS (
    SELECT 1
    FROM ProcessSteps ps
    LEFT JOIN RequestStepRecords rsr ON ps.step_id = rsr.step_id AND rsr.request_id = 1
    WHERE ps.process_id = 1 AND (rsr.status IS NULL OR rsr.status != 'Approved')
);

-- 插入审批历史记录
INSERT INTO ApprovalHistory (request_id, approver_id, status, comments, action_at)
VALUES (1, 2, 'Approved', 'Approved by HR', NOW());

-- 8. 查看审批结果

SELECT r.request_id, r.process_id, r.requester_id, r.status, u.username as requester
FROM Requests r
JOIN Users u ON r.requester_id = u.user_id
WHERE r.request_id = 1;