678 lines
29 KiB
SQL
678 lines
29 KiB
SQL
-- Начальные данные для базы данных интернет-магазина
|
|
-- PostgreSQL
|
|
--
|
|
-- ВАЖНО: Этот файл должен выполняться ПОСЛЕ schema.sql
|
|
-- Сначала создайте структуру базы данных (schema.sql), затем заполните данными (seed_data.sql)
|
|
-- Этот файл можно безопасно выполнять многократно - все INSERT используют ON CONFLICT
|
|
|
|
-- ============================================================================
|
|
-- 1. РЕСУРСЫ (RESOURCES)
|
|
-- ============================================================================
|
|
|
|
-- Основные ресурсы системы
|
|
INSERT INTO resources (name, slug, description) VALUES
|
|
('Товары', 'products', 'Управление товарами магазина')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO resources (name, slug, description) VALUES
|
|
('Заказы', 'orders', 'Управление заказами покупателей')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO resources (name, slug, description) VALUES
|
|
('Категории', 'categories', 'Управление категориями товаров')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO resources (name, slug, description) VALUES
|
|
('Отзывы', 'reviews', 'Управление отзывами на товары')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO resources (name, slug, description) VALUES
|
|
('Корзина', 'cart', 'Управление корзиной покупок')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO resources (name, slug, description) VALUES
|
|
('Пользователи', 'users', 'Управление пользователями системы')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO resources (name, slug, description) VALUES
|
|
('Все ресурсы', '*', 'Все ресурсы системы (для администратора)')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
-- Дополнительные ресурсы
|
|
INSERT INTO resources (name, slug, description) VALUES
|
|
('Платежи', 'payments', 'Управление платежами по заказам')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO resources (name, slug, description) VALUES
|
|
('Адреса', 'addresses', 'Управление адресами доставки')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO resources (name, slug, description) VALUES
|
|
('Роли', 'roles', 'Управление ролями пользователей')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO resources (name, slug, description) VALUES
|
|
('Права доступа', 'permissions', 'Управление правами доступа')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
-- ============================================================================
|
|
-- 2. ДЕЙСТВИЯ (ACTIONS)
|
|
-- ============================================================================
|
|
|
|
-- CRUD операции
|
|
INSERT INTO actions (name, slug, description) VALUES
|
|
('Просмотр', 'view', 'Просмотр списка и деталей записей (Read)')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO actions (name, slug, description) VALUES
|
|
('Создание', 'create', 'Создание новых записей (Create)')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO actions (name, slug, description) VALUES
|
|
('Редактирование', 'edit', 'Редактирование существующих записей (Update)')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO actions (name, slug, description) VALUES
|
|
('Удаление', 'delete', 'Удаление записей (Delete)')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
-- Дополнительные действия
|
|
INSERT INTO actions (name, slug, description) VALUES
|
|
('Модерация', 'moderate', 'Одобрение и отклонение записей')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO actions (name, slug, description) VALUES
|
|
('Управление', 'manage', 'Полное управление ресурсом')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO actions (name, slug, description) VALUES
|
|
('Просмотр своих', 'view_own', 'Просмотр только своих записей')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO actions (name, slug, description) VALUES
|
|
('Все действия', '*', 'Все действия (для администратора)')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
-- ============================================================================
|
|
-- 3. ПРАВА ДОСТУПА (PERMISSIONS)
|
|
-- ============================================================================
|
|
|
|
-- Products
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT
|
|
'Просмотр товаров',
|
|
'products.view',
|
|
r.id,
|
|
a.id,
|
|
'Просмотр списка и деталей товаров'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'products' AND a.slug = 'view'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Создание товаров', 'products.create', r.id, a.id, 'Создание новых товаров'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'products' AND a.slug = 'create'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Редактирование товаров', 'products.edit', r.id, a.id, 'Редактирование существующих товаров'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'products' AND a.slug = 'edit'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Удаление товаров', 'products.delete', r.id, a.id, 'Удаление товаров'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'products' AND a.slug = 'delete'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
-- Orders
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Просмотр заказов', 'orders.view', r.id, a.id, 'Просмотр списка и деталей заказов'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'orders' AND a.slug = 'view'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Создание заказов', 'orders.create', r.id, a.id, 'Создание новых заказов'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'orders' AND a.slug = 'create'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Редактирование заказов', 'orders.edit', r.id, a.id, 'Редактирование заказов'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'orders' AND a.slug = 'edit'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Просмотр своих заказов', 'orders.view_own', r.id, a.id, 'Просмотр только своих заказов'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'orders' AND a.slug = 'view_own'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
-- Categories
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Просмотр категорий', 'categories.view', r.id, a.id, 'Просмотр категорий'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'categories' AND a.slug = 'view'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Создание категорий', 'categories.create', r.id, a.id, 'Создание новых категорий'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'categories' AND a.slug = 'create'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Редактирование категорий', 'categories.edit', r.id, a.id, 'Редактирование категорий'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'categories' AND a.slug = 'edit'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Удаление категорий', 'categories.delete', r.id, a.id, 'Удаление категорий'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'categories' AND a.slug = 'delete'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
-- Reviews
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Просмотр отзывов', 'reviews.view', r.id, a.id, 'Просмотр отзывов'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'reviews' AND a.slug = 'view'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Создание отзывов', 'reviews.create', r.id, a.id, 'Создание отзывов на товары'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'reviews' AND a.slug = 'create'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Модерация отзывов', 'reviews.moderate', r.id, a.id, 'Одобрение и отклонение отзывов'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'reviews' AND a.slug = 'moderate'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Удаление отзывов', 'reviews.delete', r.id, a.id, 'Удаление отзывов'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'reviews' AND a.slug = 'delete'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
-- Cart
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Управление корзиной', 'cart.manage', r.id, a.id, 'Добавление и удаление товаров из корзины'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'cart' AND a.slug = 'manage'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
-- Users
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Просмотр пользователей', 'users.view', r.id, a.id, 'Просмотр списка пользователей'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'users' AND a.slug = 'view'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Редактирование пользователей', 'users.edit', r.id, a.id, 'Редактирование данных пользователей'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'users' AND a.slug = 'edit'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Удаление пользователей', 'users.delete', r.id, a.id, 'Удаление пользователей'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = 'users' AND a.slug = 'delete'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
-- Admin (полные права)
|
|
INSERT INTO permissions (name, slug, resource_id, action_id, description)
|
|
SELECT 'Полные права', 'admin.all', r.id, a.id, 'Полные права администратора на все ресурсы'
|
|
FROM resources r, actions a
|
|
WHERE r.slug = '*' AND a.slug = '*'
|
|
ON CONFLICT (resource_id, action_id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
-- ============================================================================
|
|
-- 4. РОЛИ (ROLES)
|
|
-- ============================================================================
|
|
|
|
INSERT INTO roles (name, slug, description) VALUES
|
|
('Администратор', 'admin', 'Администратор системы с полными правами доступа ко всем ресурсам')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO roles (name, slug, description) VALUES
|
|
('Владелец', 'owner', 'Владелец магазина с расширенными правами управления')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO roles (name, slug, description) VALUES
|
|
('Клиент', 'customer', 'Обычный покупатель магазина с базовыми правами')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO roles (name, slug, description) VALUES
|
|
('Сотрудник', 'employee', 'Сотрудник магазина с правами на управление товарами и заказами')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO roles (name, slug, description) VALUES
|
|
('Менеджер', 'manager', 'Менеджер магазина')
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
-- ============================================================================
|
|
-- 5. СВЯЗЬ РОЛЕЙ И ПРАВ (ROLE_PERMISSIONS)
|
|
-- ============================================================================
|
|
|
|
-- Администратор (admin) - все права
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r, permissions p
|
|
WHERE r.slug = 'admin'
|
|
ON CONFLICT (role_id, permission_id) DO NOTHING;
|
|
|
|
-- Владелец (owner) - расширенные права (почти как администратор, но без управления ролями и правами)
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r, permissions p
|
|
WHERE r.slug = 'owner'
|
|
AND p.slug NOT LIKE 'roles.%'
|
|
AND p.slug NOT LIKE 'permissions.%'
|
|
ON CONFLICT (role_id, permission_id) DO NOTHING;
|
|
|
|
-- Сотрудник (employee) - управление товарами и заказами
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r, permissions p
|
|
WHERE r.slug = 'employee'
|
|
AND p.slug IN (
|
|
'products.view', 'products.create', 'products.edit', 'products.delete',
|
|
'orders.view', 'orders.create', 'orders.edit',
|
|
'categories.view', 'categories.create', 'categories.edit', 'categories.delete',
|
|
'reviews.view', 'reviews.moderate', 'reviews.delete',
|
|
'users.view',
|
|
'cart.manage'
|
|
)
|
|
ON CONFLICT (role_id, permission_id) DO NOTHING;
|
|
|
|
-- Менеджер (manager) - аналогично сотруднику
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r, permissions p
|
|
WHERE r.slug = 'manager'
|
|
AND p.slug IN (
|
|
'products.view', 'products.create', 'products.edit', 'products.delete',
|
|
'orders.view', 'orders.edit',
|
|
'categories.view', 'categories.create', 'categories.edit', 'categories.delete',
|
|
'reviews.view', 'reviews.moderate', 'reviews.delete',
|
|
'users.view',
|
|
'cart.manage'
|
|
)
|
|
ON CONFLICT (role_id, permission_id) DO NOTHING;
|
|
|
|
-- Клиент (customer) - базовые права
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r, permissions p
|
|
WHERE r.slug = 'customer'
|
|
AND p.slug IN (
|
|
'products.view',
|
|
'orders.create',
|
|
'orders.view_own',
|
|
'cart.manage',
|
|
'reviews.create',
|
|
'categories.view'
|
|
)
|
|
ON CONFLICT (role_id, permission_id) DO NOTHING;
|
|
|
|
-- ============================================================================
|
|
-- 6. СТАТУСЫ ЗАКАЗОВ (ORDER_STATUSES)
|
|
-- ============================================================================
|
|
|
|
INSERT INTO order_statuses (name, slug, description, color, sort_order) VALUES
|
|
('Ожидает обработки', 'pending', 'Заказ создан и ожидает обработки', '#FFA500', 1)
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
color = EXCLUDED.color,
|
|
sort_order = EXCLUDED.sort_order,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO order_statuses (name, slug, description, color, sort_order) VALUES
|
|
('В обработке', 'processing', 'Заказ обрабатывается', '#4169E1', 2)
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
color = EXCLUDED.color,
|
|
sort_order = EXCLUDED.sort_order,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO order_statuses (name, slug, description, color, sort_order) VALUES
|
|
('Отправлен', 'shipped', 'Заказ отправлен покупателю', '#32CD32', 3)
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
color = EXCLUDED.color,
|
|
sort_order = EXCLUDED.sort_order,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO order_statuses (name, slug, description, color, sort_order) VALUES
|
|
('Доставлен', 'delivered', 'Заказ доставлен покупателю', '#228B22', 4)
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
color = EXCLUDED.color,
|
|
sort_order = EXCLUDED.sort_order,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO order_statuses (name, slug, description, color, sort_order) VALUES
|
|
('Отменен', 'cancelled', 'Заказ отменен', '#DC143C', 5)
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
color = EXCLUDED.color,
|
|
sort_order = EXCLUDED.sort_order,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO order_statuses (name, slug, description, color, sort_order) VALUES
|
|
('Возвращен', 'refunded', 'Заказ возвращен, средства возвращены', '#8B0000', 6)
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
color = EXCLUDED.color,
|
|
sort_order = EXCLUDED.sort_order,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
-- ============================================================================
|
|
-- 7. СПОСОБЫ ОПЛАТЫ (PAYMENT_METHODS)
|
|
-- ============================================================================
|
|
|
|
INSERT INTO payment_methods (name, slug, description, sort_order) VALUES
|
|
('Банковская карта', 'card', 'Оплата банковской картой онлайн', 1)
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
sort_order = EXCLUDED.sort_order,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO payment_methods (name, slug, description, sort_order) VALUES
|
|
('Электронный кошелек', 'e-wallet', 'Оплата через электронный кошелек (Яндекс.Деньги, Qiwi и т.д.)', 2)
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
sort_order = EXCLUDED.sort_order,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO payment_methods (name, slug, description, sort_order) VALUES
|
|
('Наличные при получении', 'cash_on_delivery', 'Оплата наличными при получении заказа', 3)
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
sort_order = EXCLUDED.sort_order,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO payment_methods (name, slug, description, sort_order) VALUES
|
|
('Банковский перевод', 'bank_transfer', 'Оплата через банковский перевод', 4)
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
sort_order = EXCLUDED.sort_order,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO payment_methods (name, slug, description, sort_order) VALUES
|
|
('Криптовалюта', 'crypto', 'Оплата криптовалютой', 5)
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
sort_order = EXCLUDED.sort_order,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
-- ============================================================================
|
|
-- 8. СТАТУСЫ ПЛАТЕЖЕЙ (PAYMENT_STATUSES)
|
|
-- ============================================================================
|
|
|
|
INSERT INTO payment_statuses (name, slug, description, color, sort_order) VALUES
|
|
('Ожидает оплаты', 'pending', 'Платеж ожидает оплаты', '#FFA500', 1)
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
color = EXCLUDED.color,
|
|
sort_order = EXCLUDED.sort_order,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO payment_statuses (name, slug, description, color, sort_order) VALUES
|
|
('Оплачен', 'paid', 'Платеж успешно оплачен', '#32CD32', 2)
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
color = EXCLUDED.color,
|
|
sort_order = EXCLUDED.sort_order,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO payment_statuses (name, slug, description, color, sort_order) VALUES
|
|
('Завершен', 'completed', 'Платеж завершен', '#228B22', 3)
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
color = EXCLUDED.color,
|
|
sort_order = EXCLUDED.sort_order,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO payment_statuses (name, slug, description, color, sort_order) VALUES
|
|
('Ошибка оплаты', 'failed', 'Ошибка при обработке платежа', '#DC143C', 4)
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
color = EXCLUDED.color,
|
|
sort_order = EXCLUDED.sort_order,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
INSERT INTO payment_statuses (name, slug, description, color, sort_order) VALUES
|
|
('Возвращен', 'refunded', 'Платеж возвращен покупателю', '#8B0000', 5)
|
|
ON CONFLICT (slug) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
color = EXCLUDED.color,
|
|
sort_order = EXCLUDED.sort_order,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
-- ============================================================================
|
|
-- 9. ОБНОВЛЕНИЕ ПРАВ ДЛЯ СУЩЕСТВУЮЩИХ РОЛЕЙ
|
|
-- ============================================================================
|
|
-- Этот раздел гарантирует, что сотрудники и менеджеры имеют все необходимые права
|
|
-- Можно безопасно выполнять многократно благодаря ON CONFLICT
|
|
|
|
-- Убедиться, что сотрудники (employee) имеют право на управление корзиной
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r, permissions p
|
|
WHERE r.slug = 'employee'
|
|
AND p.slug = 'cart.manage'
|
|
ON CONFLICT (role_id, permission_id) DO NOTHING;
|
|
|
|
-- Убедиться, что менеджеры (manager) имеют право на управление корзиной
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r, permissions p
|
|
WHERE r.slug = 'manager'
|
|
AND p.slug = 'cart.manage'
|
|
ON CONFLICT (role_id, permission_id) DO NOTHING;
|
|
|
|
-- Убедиться, что сотрудники (employee) имеют право на редактирование заказов
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r, permissions p
|
|
WHERE r.slug = 'employee'
|
|
AND p.slug = 'orders.edit'
|
|
ON CONFLICT (role_id, permission_id) DO NOTHING;
|
|
|
|
-- Убедиться, что менеджеры (manager) имеют право на редактирование заказов
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r, permissions p
|
|
WHERE r.slug = 'manager'
|
|
AND p.slug = 'orders.edit'
|
|
ON CONFLICT (role_id, permission_id) DO NOTHING;
|
|
|
|
-- Убедиться, что все роли имеют право на создание заказов
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r, permissions p
|
|
WHERE r.slug IN ('customer', 'employee', 'manager', 'owner', 'admin')
|
|
AND p.slug = 'orders.create'
|
|
ON CONFLICT (role_id, permission_id) DO NOTHING; |