实战项目：用户管理系统

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    password_hash VARCHAR(255) NOT NULL,
    real_name VARCHAR(50),
    avatar_url VARCHAR(255),
    department_id INT,
    status VARCHAR(20) DEFAULT 'active',  -- active, inactive, locked
    last_login_at TIMESTAMPTZ,
    login_count INT DEFAULT 0,
    failed_login_count INT DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by BIGINT,
    updated_by BIGINT
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_phone ON users(phone);
CREATE INDEX idx_users_department ON users(department_id);
CREATE INDEX idx_users_status ON users(status);

CREATE TABLE roles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    code VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    is_system BOOLEAN DEFAULT FALSE,  -- 系统角色不能删除
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 插入默认角色
INSERT INTO roles (name, code, is_system) VALUES 
    ('超级管理员', 'SUPER_ADMIN', TRUE),
    ('管理员', 'ADMIN', TRUE),
    ('普通用户', 'USER', TRUE);

CREATE TABLE permissions (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(100) NOT NULL UNIQUE,
    resource VARCHAR(50),  -- 资源类型：user, role, post等
    action VARCHAR(50),    -- 操作：read, write, delete等
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 插入基础权限
INSERT INTO permissions (name, code, resource, action) VALUES 
    ('查看用户', 'user:read', 'user', 'read'),
    ('创建用户', 'user:create', 'user', 'create'),
    ('更新用户', 'user:update', 'user', 'update'),
    ('删除用户', 'user:delete', 'user', 'delete'),
    ('查看角色', 'role:read', 'role', 'read'),
    ('管理角色', 'role:manage', 'role', 'manage');

CREATE TABLE user_roles (
    user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    role_id INT REFERENCES roles(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by BIGINT,
    PRIMARY KEY (user_id, role_id)
);

CREATE INDEX idx_user_roles_user ON user_roles(user_id);
CREATE INDEX idx_user_roles_role ON user_roles(role_id);

CREATE TABLE role_permissions (
    role_id INT REFERENCES roles(id) ON DELETE CASCADE,
    permission_id INT REFERENCES permissions(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (role_id, permission_id)
);

-- 为超级管理员分配所有权限
INSERT INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions;

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INT REFERENCES departments(id),
    level INT DEFAULT 1,
    sort_order INT DEFAULT 0,
    leader_id BIGINT REFERENCES users(id),
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 添加外键
ALTER TABLE users ADD FOREIGN KEY (department_id) REFERENCES departments(id);

CREATE INDEX idx_departments_parent ON departments(parent_id);

CREATE TABLE audit_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),
    action VARCHAR(50),  -- login, logout, create, update, delete
    resource VARCHAR(50),  -- user, role, permission
    resource_id VARCHAR(100),
    ip_address VARCHAR(45),
    user_agent TEXT,
    details JSONB,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_audit_user ON audit_logs(user_id);
CREATE INDEX idx_audit_action ON audit_logs(action);
CREATE INDEX idx_audit_resource ON audit_logs(resource);
CREATE INDEX idx_audit_created ON audit_logs(created_at);

-- 检查用户是否有某个权限
CREATE OR REPLACE FUNCTION user_has_permission(
    p_user_id BIGINT,
    p_permission_code VARCHAR
) RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1
        FROM user_roles ur
        JOIN role_permissions rp ON ur.role_id = rp.role_id
        JOIN permissions p ON rp.permission_id = p.id
        WHERE ur.user_id = p_user_id
          AND p.code = p_permission_code
    );
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT user_has_permission(1, 'user:create');

CREATE OR REPLACE FUNCTION get_user_permissions(p_user_id BIGINT)
RETURNS TABLE(permission_code VARCHAR, permission_name VARCHAR) AS $$
BEGIN
    RETURN QUERY
    SELECT DISTINCT p.code, p.name
    FROM user_roles ur
    JOIN role_permissions rp ON ur.role_id = rp.role_id
    JOIN permissions p ON rp.permission_id = p.id
    WHERE ur.user_id = p_user_id;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT * FROM get_user_permissions(1);

CREATE OR REPLACE FUNCTION log_audit(
    p_user_id BIGINT,
    p_action VARCHAR,
    p_resource VARCHAR,
    p_resource_id VARCHAR,
    p_details JSONB DEFAULT NULL
) RETURNS VOID AS $$
BEGIN
    INSERT INTO audit_logs (user_id, action, resource, resource_id, details)
    VALUES (p_user_id, p_action, p_resource, p_resource_id, p_details);
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT log_audit(1, 'create', 'user', '123', '{"username": "newuser"}');

CREATE OR REPLACE FUNCTION user_login(
    p_username VARCHAR,
    p_password_hash VARCHAR,
    p_ip_address VARCHAR DEFAULT NULL
) RETURNS TABLE(
    user_id BIGINT,
    username VARCHAR,
    email VARCHAR,
    roles TEXT[],
    permissions TEXT[]
) AS $$
DECLARE
    v_user_id BIGINT;
    v_status VARCHAR;
BEGIN
    -- 检查用户
    SELECT id, status INTO v_user_id, v_status
    FROM users
    WHERE username = p_username AND password_hash = p_password_hash;
    
    IF v_user_id IS NULL THEN
        RAISE EXCEPTION '用户名或密码错误';
    END IF;
    
    IF v_status != 'active' THEN
        RAISE EXCEPTION '账户已被锁定';
    END IF;
    
    -- 更新登录信息
    UPDATE users
    SET last_login_at = NOW(),
        login_count = login_count + 1,
        failed_login_count = 0
    WHERE id = v_user_id;
    
    -- 记录日志
    PERFORM log_audit(v_user_id, 'login', 'user', v_user_id::VARCHAR,
        jsonb_build_object('ip', p_ip_address));
    
    -- 返回用户信息
    RETURN QUERY
    SELECT 
        u.id,
        u.username,
        u.email,
        ARRAY_AGG(DISTINCT r.name) AS roles,
        ARRAY_AGG(DISTINCT p.code) AS permissions
    FROM users u
    LEFT JOIN user_roles ur ON u.id = ur.user_id
    LEFT JOIN roles r ON ur.role_id = r.id
    LEFT JOIN role_permissions rp ON r.id = rp.role_id
    LEFT JOIN permissions p ON rp.permission_id = p.id
    WHERE u.id = v_user_id
    GROUP BY u.id, u.username, u.email;
END;
$$ LANGUAGE plpgsql;

SELECT 
    u.id,
    u.username,
    u.email,
    u.real_name,
    u.status,
    d.name AS department,
    ARRAY_AGG(DISTINCT r.name) AS roles,
    u.last_login_at,
    u.created_at
FROM users u
LEFT JOIN departments d ON u.department_id = d.id
LEFT JOIN user_roles ur ON u.id = ur.user_id
LEFT JOIN roles r ON ur.role_id = r.id
GROUP BY u.id, d.name
ORDER BY u.created_at DESC;

SELECT 
    r.name AS role_name,
    ARRAY_AGG(p.name ORDER BY p.name) AS permissions
FROM roles r
LEFT JOIN role_permissions rp ON r.id = rp.role_id
LEFT JOIN permissions p ON rp.permission_id = p.id
GROUP BY r.id, r.name;

WITH RECURSIVE dept_tree AS (
    -- 根部门
    SELECT id, name, parent_id, 1 AS level, name AS path
    FROM departments
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 子部门
    SELECT d.id, d.name, d.parent_id, dt.level + 1,
           dt.path || ' > ' || d.name
    FROM departments d
    JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree ORDER BY path;

SELECT 
    al.id,
    u.username,
    al.action,
    al.resource,
    al.ip_address,
    al.details,
    al.created_at
FROM audit_logs al
LEFT JOIN users u ON al.user_id = u.id
WHERE al.created_at > NOW() - INTERVAL '7 days'
ORDER BY al.created_at DESC
LIMIT 100;