generic.rbac.system.sql

BEGIN;

/* SETUP */

CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE Users
(
    user_id    UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name       VARCHAR(255)        NOT NULL,
    email      VARCHAR(255) UNIQUE NOT NULL,
    passwordHash   VARCHAR(255)     NOT NULL,
    created_at TIMESTAMP        DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP        DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP        DEFAULT NULL
);



CREATE TABLE Systems
(
    system_id   UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    system_slug VARCHAR(255) NOT NULL,
    description TEXT,
    created_at  TIMESTAMP        DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP        DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP        DEFAULT NULL
);

CREATE TABLE Roles
(
    role_id     UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    system_id   UUID         NOT NULL,
    role_name   VARCHAR(255) NOT NULL,
    description TEXT,
    created_at  TIMESTAMP        DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP        DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP        DEFAULT NULL,
    FOREIGN KEY (system_id) REFERENCES Systems (system_id) ON DELETE CASCADE
);

CREATE TABLE Permissions
(
    permission_id  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    system_id      UUID         NOT NULL,
    permission_key VARCHAR(255) NOT NULL,
    description    TEXT,
    created_at     TIMESTAMP        DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP        DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP        DEFAULT NULL,
    FOREIGN KEY (system_id) REFERENCES Systems (system_id) ON DELETE CASCADE
);

CREATE TABLE User_System
(
    user_system_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id        UUID NOT NULL,
    system_id      UUID NOT NULL,
    created_at     TIMESTAMP        DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP        DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP        DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES Users (user_id) ON DELETE CASCADE,
    FOREIGN KEY (system_id) REFERENCES Systems (system_id) ON DELETE CASCADE,
    UNIQUE (user_id, system_id) -- Um usuário só pode ser associado a um sistema uma vez
);

CREATE TABLE User_Role
(
    user_role_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id      UUID NOT NULL,
    role_id      UUID NOT NULL,
    system_id    UUID NOT NULL,
    created_at   TIMESTAMP        DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP        DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP        DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES Users (user_id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES Roles (role_id) ON DELETE CASCADE,
    FOREIGN KEY (system_id) REFERENCES Systems (system_id) ON DELETE CASCADE,
    UNIQUE (user_id, role_id, system_id) -- Garantir unicidade do relacionamento
);

CREATE TABLE User_Permission
(
    user_permission_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id            UUID NOT NULL,
    permission_id      UUID NOT NULL,
    system_id          UUID NOT NULL,
    created_at         TIMESTAMP        DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP        DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP        DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES Users (user_id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES Permissions (permission_id) ON DELETE CASCADE,
    FOREIGN KEY (system_id) REFERENCES Systems (system_id) ON DELETE CASCADE,
    UNIQUE (user_id, permission_id, system_id)
);

CREATE TABLE Role_Permission
(
    role_permission_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    role_id            UUID NOT NULL,
    permission_id      UUID NOT NULL,
    system_id          UUID NOT NULL,
    created_at         TIMESTAMP        DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP        DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP        DEFAULT NULL,
    FOREIGN KEY (role_id) REFERENCES Roles (role_id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES Permissions (permission_id) ON DELETE CASCADE,
    FOREIGN KEY (system_id) REFERENCES Systems (system_id) ON DELETE CASCADE,
    UNIQUE (role_id, permission_id, system_id)
);


/* SEED */

-- Inserindo sistemas (ex: Sistema de Chat, Sistema de Vendas)
INSERT INTO Systems (system_id, system_slug, description)
VALUES
    (uuid_generate_v4(), 'sistema-de-chat', 'Sistema de comunicação interno da empresa'),
    (uuid_generate_v4(), 'sistema-de-vendas', 'Sistema de gerenciamento de vendas da empresa');

-- Inserindo usuários
INSERT INTO Users (user_id, name, email, passwordHash)
VALUES
    (uuid_generate_v4(), 'Alice', '[email protected]', crypt('alice', gen_salt('bf', 8))),
    (uuid_generate_v4(), 'Bob', '[email protected]', crypt('bob', gen_salt('bf', 8))),
    (uuid_generate_v4(), 'Carol', '[email protected]', crypt('carol', gen_salt('bf', 8)));


WITH system_data AS (
    -- Recuperando os IDs dos sistemas inseridos
    SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-chat' OR system_slug = 'sistema-de-vendas'
)
-- Depois de recuperar, inserir os cargos
INSERT INTO Roles (role_id, system_id, role_name, description)
VALUES
    (uuid_generate_v4(), (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-chat'), 'Admin', 'Administrador do sistema de chat'),
    (uuid_generate_v4(), (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-chat'), 'Usuário', 'Usuário comum do sistema de chat'),
    (uuid_generate_v4(), (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-vendas'), 'Vendedor', 'Responsável por vendas'),
    (uuid_generate_v4(), (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-vendas'), 'Supervisor', 'Supervisor de vendas');

-- Inserindo permissões (ex: criar e deletar mensagens no sistema de chat, criar e deletar vendas no sistema de vendas)
INSERT INTO Permissions (permission_id, system_id, permission_key, description)
VALUES
    (uuid_generate_v4(), (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-chat'), 'message:create', 'Permissão para criar mensagens'),
    (uuid_generate_v4(), (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-chat'), 'message:delete', 'Permissão para deletar mensagens'),
    (uuid_generate_v4(), (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-vendas'), 'sale:create', 'Permissão para criar vendas'),
    (uuid_generate_v4(), (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-vendas'), 'sale:delete', 'Permissão para deletar vendas');

-- Associando usuários a sistemas
INSERT INTO User_System (user_system_id, user_id, system_id)
VALUES
    (uuid_generate_v4(), (SELECT user_id FROM Users WHERE name = 'Alice'), (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-chat')),
    (uuid_generate_v4(), (SELECT user_id FROM Users WHERE name = 'Bob'), (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-vendas')),
    (uuid_generate_v4(), (SELECT user_id FROM Users WHERE name = 'Carol'), (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-vendas'));

-- Associando usuários a cargos (ex: Alice é Admin no Sistema de Chat, Bob é Vendedor no Sistema de Vendas, Carol é Supervisor no Sistema de Vendas)
INSERT INTO User_Role (user_role_id, user_id, role_id, system_id)
VALUES
    (uuid_generate_v4(), (SELECT user_id FROM Users WHERE name = 'Alice'), (SELECT role_id FROM Roles WHERE role_name = 'Admin' AND system_id = (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-chat')), (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-chat')),
    (uuid_generate_v4(), (SELECT user_id FROM Users WHERE name = 'Bob'), (SELECT role_id FROM Roles WHERE role_name = 'Vendedor' AND system_id = (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-vendas')), (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-vendas')),
    (uuid_generate_v4(), (SELECT user_id FROM Users WHERE name = 'Carol'), (SELECT role_id FROM Roles WHERE role_name = 'Supervisor' AND system_id = (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-vendas')), (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-vendas'));

-- Associando permissões aos cargos
INSERT INTO Role_Permission (role_permission_id, role_id, permission_id, system_id)
VALUES
    (uuid_generate_v4(), (SELECT role_id FROM Roles WHERE role_name = 'Admin' AND system_id = (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-chat')), (SELECT permission_id FROM Permissions WHERE permission_key = 'message:create' AND system_id = (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-chat')), (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-chat')),
    (uuid_generate_v4(), (SELECT role_id FROM Roles WHERE role_name = 'Admin' AND system_id = (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-chat')), (SELECT permission_id FROM Permissions WHERE permission_key = 'message:delete' AND system_id = (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-chat')), (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-chat')),
    (uuid_generate_v4(), (SELECT role_id FROM Roles WHERE role_name = 'Vendedor' AND system_id = (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-vendas')), (SELECT permission_id FROM Permissions WHERE permission_key = 'sale:create' AND system_id = (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-vendas')), (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-vendas')),
    (uuid_generate_v4(), (SELECT role_id FROM Roles WHERE role_name = 'Supervisor' AND system_id = (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-vendas')), (SELECT permission_id FROM Permissions WHERE permission_key = 'sale:delete' AND system_id = (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-vendas')), (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-vendas'));

-- Associando permissões avulsas diretamente aos usuários (ex: Carol pode deletar mensagens no sistema de chat)
INSERT INTO User_Permission (user_permission_id, user_id, permission_id, system_id)
VALUES
    (uuid_generate_v4(), (SELECT user_id FROM Users WHERE name = 'Carol'), (SELECT permission_id FROM Permissions WHERE permission_key = 'message:delete' AND system_id = (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-chat')), (SELECT system_id FROM Systems WHERE system_slug = 'sistema-de-chat'));

COMMIT;