-- +goose Up
-- +goose StatementBegin

-- =========================================
-- ТАБЛИЦА: user_roles - Связь пользователей и ролей (Many-to-Many)
-- =========================================
CREATE TABLE user_roles (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id BIGINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    assigned_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    assigned_by BIGINT REFERENCES users(id),
    UNIQUE(user_id, role_id)
);

COMMENT ON TABLE user_roles IS 'Связь пользователей и ролей (Many-to-Many). Один пользователь может иметь несколько ролей';
COMMENT ON COLUMN user_roles.assigned_by IS 'Кто назначил роль (для аудита)';

-- =========================================
-- ТАБЛИЦА: role_permissions - Связь ролей и разрешений (Many-to-Many)
-- =========================================
CREATE TABLE role_permissions (
    id BIGSERIAL PRIMARY KEY,
    role_id BIGINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    permission_id BIGINT NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(role_id, permission_id)
);

COMMENT ON TABLE role_permissions IS 'Связь ролей и разрешений (Many-to-Many) для гибкой системы RBAC';

-- Индексы для оптимизации запросов прав доступа
CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);
CREATE INDEX idx_user_roles_role_id ON user_roles(role_id);
CREATE INDEX idx_role_permissions_role_id ON role_permissions(role_id);
CREATE INDEX idx_role_permissions_permission_id ON role_permissions(permission_id);

-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
DROP TABLE IF EXISTS role_permissions CASCADE;
DROP TABLE IF EXISTS user_roles CASCADE;
-- +goose StatementEnd