SQL多表查询与数据关联：构建用户与管理员审批系统

-- 创建用户组表
CREATE TABLE user_group (
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);

-- 插入用户组数据
INSERT INTO user_group (name, description) VALUES('Super User', 'Complete Access');
INSERT INTO user_group (name, description) VALUES('Moderator', 'Limited Access');
INSERT INTO user_group (name, description) VALUES('Guest', 'Web Users');

-- 创建用户表，并定义外键关联到用户组表
CREATE TABLE users (
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    password VARCHAR(100) NOT NULL,
    user_group_id INT NOT NULL, -- 这是一个外键字段
    PRIMARY KEY (id),
    FOREIGN KEY (user_group_id) REFERENCES user_group(id) -- 定义外键约束
);

-- 插入用户数据
INSERT INTO users (name, password, user_group_id) VALUES('god', 'aaa666', 1);    -- god 属于 Super User 组
INSERT INTO users (name, password, user_group_id) VALUES('davo', 'xyx123', 2);   -- davo 属于 Moderator 组
INSERT INTO users (name, password, user_group_id) VALUES('prole', 'abc101', 3);  -- prole 属于 Guest 组

SELECT
    u.name AS user_name,
    u.password AS user_password,
    ug.name AS group_name,
    ug.description AS group_description
FROM
    users AS u
INNER JOIN
    user_group AS ug ON u.user_group_id = ug.id;

-- 假设 users_account 表结构如下：
-- CREATE TABLE users_account (
--     user_id INT PRIMARY KEY AUTO_INCREMENT,
--     user_fullname VARCHAR(255),
--     user_email VARCHAR(255),
--     password VARCHAR(255),
--     facility VARCHAR(255),
--     terms_and_conditions BOOLEAN,
--     isApproved BOOLEAN DEFAULT FALSE,
--     date DATETIME, -- 用户注册日期
--     approved_by_admin_id INT, -- 审批该用户的管理员ID
--     approved_date DATETIME,   -- 审批日期
--     FOREIGN KEY (approved_by_admin_id) REFERENCES super_admin(id)
-- );

-- 假设 super_admin 表结构如下：
-- CREATE TABLE super_admin (
--     id INT PRIMARY KEY AUTO_INCREMENT,
--     username VARCHAR(255),
--     password VARCHAR(255)
-- );

-- 模拟插入一些数据
INSERT INTO users_account (user_fullname, user_email, password, isApproved, date)
VALUES ('Alice', 'alice@example.com', 'pass123', FALSE, NOW());
INSERT INTO users_account (user_fullname, user_email, password, isApproved, date)
VALUES ('Bob', 'bob@example.com', 'pass456', FALSE, NOW());

INSERT INTO super_admin (username, password)
VALUES ('admin1', 'adminpass');
INSERT INTO super_admin (username, password)
VALUES ('admin2', 'adminpass');

-- 审批操作：admin1 审批 Alice
UPDATE users_account
SET
    isApproved = TRUE,
    approved_by_admin_id = (SELECT id FROM super_admin WHERE username = 'admin1'),
    approved_date = NOW()
WHERE
    user_fullname = 'Alice';

-- 审批操作：admin2 审批 Bob
UPDATE users_account
SET
    isApproved = TRUE,
    approved_by_admin_id = (SELECT id FROM super_admin WHERE username = 'admin2'),
    approved_date = NOW()
WHERE
    user_fullname = 'Bob';

-- 查询已审批用户及其审批管理员信息
SELECT
    ua.user_fullname,
    ua.user_email,
    ua.isApproved,
    ua.date AS registration_date,
    ua.approved_date,
    sa.username AS approved_by_admin
FROM
    users_account AS ua
INNER JOIN
    super_admin AS sa ON ua.approved_by_admin_id = sa.id
WHERE
    ua.isApproved = TRUE;

-- 创建审批日志表
CREATE TABLE approval_logs (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    admin_id INT NOT NULL,
    action_type VARCHAR(50) NOT NULL, -- 例如 'APPROVE', 'REJECT'
    action_date DATETIME NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users_account(user_id),
    FOREIGN KEY (admin_id) REFERENCES super_admin(id)
);

-- 审批操作：admin1 审批 Alice
-- 首先更新 users_account 状态
UPDATE users_account
SET isApproved = TRUE
WHERE user_fullname = 'Alice';

-- 然后记录审批日志
INSERT INTO approval_logs (user_id, admin_id, action_type, action_date)
VALUES (
    (SELECT user_id FROM users_account WHERE user_fullname = 'Alice'),
    (SELECT id FROM super_admin WHERE username = 'admin1'),
    'APPROVE',
    NOW()
);

-- 查询用户及其最新的审批日志
SELECT
    ua.user_fullname,
    ua.user_email,
    ua.isApproved,
    al.action_type,
    al.action_date,
    sa.username AS admin_username
FROM
    users_account AS ua
LEFT JOIN
    approval_logs AS al ON ua.user_id = al.user_id
LEFT JOIN
    super_admin AS sa ON al.admin_id = sa.id
WHERE
    ua.isApproved = TRUE;
-- 注意：如果需要最新的审批记录，可能需要子查询或窗口函数来筛选。
-- 例如，获取每个用户最新的审批记录：
-- SELECT
--     ua.user_fullname,
--     ua.user_email,
--     ua.isApproved,
--     al.action_type,
--     al.action_date,
--     sa.username AS admin_username
-- FROM
--     users_account AS ua
-- JOIN (
--     SELECT
--         user_id,
--         admin_id,
--         action_type,
--         action_date,
--         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY action_date DESC) as rn
--     FROM approval_logs
-- ) AS al_latest ON ua.user_id = al_latest.user_id AND al_latest.rn = 1
-- JOIN
--     super_admin AS sa ON al_latest.admin_id = sa.id
-- WHERE
--     ua.isApproved = TRUE;