387 lines
14 KiB
PL/PgSQL
387 lines
14 KiB
PL/PgSQL
-- Схема базы данных для интернет-магазина
|
|
-- PostgreSQL
|
|
|
|
-- Таблица ресурсов
|
|
CREATE TABLE IF NOT EXISTS resources (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
slug VARCHAR(50) UNIQUE NOT NULL,
|
|
description TEXT,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Таблица действий
|
|
CREATE TABLE IF NOT EXISTS actions (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
slug VARCHAR(50) UNIQUE NOT NULL,
|
|
description TEXT,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Таблица прав доступа
|
|
CREATE TABLE IF NOT EXISTS permissions (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
slug VARCHAR(100) UNIQUE NOT NULL,
|
|
resource_id INTEGER NOT NULL REFERENCES resources(id) ON DELETE RESTRICT,
|
|
action_id INTEGER NOT NULL REFERENCES actions(id) ON DELETE RESTRICT,
|
|
description TEXT,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(resource_id, action_id)
|
|
);
|
|
|
|
-- Таблица ролей
|
|
CREATE TABLE IF NOT EXISTS roles (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
slug VARCHAR(50) UNIQUE NOT NULL,
|
|
description TEXT,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Таблица связи ролей и прав (многие-ко-многим)
|
|
CREATE TABLE IF NOT EXISTS role_permissions (
|
|
id SERIAL PRIMARY KEY,
|
|
role_id INTEGER NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
|
|
permission_id INTEGER NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(role_id, permission_id)
|
|
);
|
|
|
|
-- Таблица пользователей
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
email VARCHAR(255) UNIQUE,
|
|
password_hash VARCHAR(255),
|
|
first_name VARCHAR(100),
|
|
last_name VARCHAR(100),
|
|
phone VARCHAR(20),
|
|
telegram_id BIGINT UNIQUE,
|
|
telegram_user VARCHAR(100),
|
|
role_id INTEGER REFERENCES roles(id) ON DELETE SET NULL,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Таблица аватаров пользователей
|
|
CREATE TABLE IF NOT EXISTS user_avatars (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
image_url TEXT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(user_id)
|
|
);
|
|
|
|
-- Таблица категорий товаров
|
|
CREATE TABLE IF NOT EXISTS categories (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
slug VARCHAR(255) UNIQUE NOT NULL,
|
|
description TEXT,
|
|
parent_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
|
|
image_url VARCHAR(500),
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Таблица товаров
|
|
CREATE TABLE IF NOT EXISTS products (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
slug VARCHAR(255) UNIQUE NOT NULL,
|
|
description TEXT,
|
|
short_description TEXT,
|
|
sku VARCHAR(100) UNIQUE NOT NULL,
|
|
price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
|
|
compare_at_price DECIMAL(10, 2) CHECK (compare_at_price >= 0),
|
|
cost_price DECIMAL(10, 2) CHECK (cost_price >= 0),
|
|
discount_percent DECIMAL(5, 2) DEFAULT 0 CHECK (discount_percent >= 0 AND discount_percent <= 100),
|
|
category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
|
|
stock_quantity INTEGER DEFAULT 0 CHECK (stock_quantity >= 0),
|
|
weight DECIMAL(8, 2),
|
|
dimensions VARCHAR(100),
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
is_featured BOOLEAN DEFAULT FALSE,
|
|
rating DECIMAL(3, 2) DEFAULT 0 CHECK (rating >= 0 AND rating <= 5),
|
|
review_count INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Таблица изображений товаров
|
|
CREATE TABLE IF NOT EXISTS product_images (
|
|
id SERIAL PRIMARY KEY,
|
|
product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
|
|
image_url TEXT NOT NULL,
|
|
alt_text VARCHAR(255),
|
|
is_primary BOOLEAN DEFAULT FALSE,
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Таблица адресов доставки
|
|
CREATE TABLE IF NOT EXISTS addresses (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
first_name VARCHAR(100) NOT NULL,
|
|
last_name VARCHAR(100) NOT NULL,
|
|
phone VARCHAR(20),
|
|
country VARCHAR(100) NOT NULL,
|
|
city VARCHAR(100) NOT NULL,
|
|
street VARCHAR(255) NOT NULL,
|
|
building VARCHAR(50),
|
|
apartment VARCHAR(50),
|
|
postal_code VARCHAR(20),
|
|
is_default BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Таблица статусов заказов
|
|
CREATE TABLE IF NOT EXISTS order_statuses (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
slug VARCHAR(50) UNIQUE NOT NULL,
|
|
description TEXT,
|
|
color VARCHAR(20),
|
|
sort_order INTEGER DEFAULT 0,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Таблица способов оплаты
|
|
CREATE TABLE IF NOT EXISTS payment_methods (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
slug VARCHAR(50) UNIQUE NOT NULL,
|
|
description TEXT,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Таблица статусов платежей
|
|
CREATE TABLE IF NOT EXISTS payment_statuses (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
slug VARCHAR(50) UNIQUE NOT NULL,
|
|
description TEXT,
|
|
color VARCHAR(20),
|
|
sort_order INTEGER DEFAULT 0,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Таблица заказов
|
|
CREATE TABLE IF NOT EXISTS orders (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
order_number VARCHAR(50) UNIQUE NOT NULL,
|
|
status_id INTEGER NOT NULL REFERENCES order_statuses(id) ON DELETE RESTRICT,
|
|
subtotal DECIMAL(10, 2) NOT NULL CHECK (subtotal >= 0),
|
|
tax_amount DECIMAL(10, 2) DEFAULT 0 CHECK (tax_amount >= 0),
|
|
shipping_cost DECIMAL(10, 2) DEFAULT 0 CHECK (shipping_cost >= 0),
|
|
discount_amount DECIMAL(10, 2) DEFAULT 0 CHECK (discount_amount >= 0),
|
|
total_amount DECIMAL(10, 2) NOT NULL CHECK (total_amount >= 0),
|
|
shipping_address_id INTEGER REFERENCES addresses(id) ON DELETE SET NULL,
|
|
billing_address_id INTEGER REFERENCES addresses(id) ON DELETE SET NULL,
|
|
payment_method_id INTEGER REFERENCES payment_methods(id) ON DELETE SET NULL,
|
|
payment_status_id INTEGER REFERENCES payment_statuses(id) ON DELETE RESTRICT,
|
|
notes TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Таблица элементов заказа
|
|
CREATE TABLE IF NOT EXISTS order_items (
|
|
id SERIAL PRIMARY KEY,
|
|
order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
|
|
product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
|
|
product_name VARCHAR(255) NOT NULL,
|
|
product_sku VARCHAR(100) NOT NULL,
|
|
quantity INTEGER NOT NULL CHECK (quantity > 0),
|
|
price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
|
|
subtotal DECIMAL(10, 2) NOT NULL CHECK (subtotal >= 0),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Таблица корзины
|
|
CREATE TABLE IF NOT EXISTS cart_items (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
|
|
quantity INTEGER NOT NULL CHECK (quantity > 0),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(user_id, product_id)
|
|
);
|
|
|
|
-- Таблица отзывов
|
|
CREATE TABLE IF NOT EXISTS reviews (
|
|
id SERIAL PRIMARY KEY,
|
|
product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
|
|
title VARCHAR(255),
|
|
comment TEXT,
|
|
is_approved BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(product_id, user_id)
|
|
);
|
|
|
|
-- Таблица платежей
|
|
CREATE TABLE IF NOT EXISTS payments (
|
|
id SERIAL PRIMARY KEY,
|
|
order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
|
|
payment_method_id INTEGER NOT NULL REFERENCES payment_methods(id) ON DELETE RESTRICT,
|
|
transaction_id VARCHAR(255) UNIQUE,
|
|
amount DECIMAL(10, 2) NOT NULL CHECK (amount >= 0),
|
|
status_id INTEGER NOT NULL REFERENCES payment_statuses(id) ON DELETE RESTRICT,
|
|
payment_date TIMESTAMP,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Функция для автоматического обновления updated_at
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = CURRENT_TIMESTAMP;
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
-- Функция для обновления рейтинга товара при добавлении/изменении отзыва
|
|
CREATE OR REPLACE FUNCTION update_product_rating()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
UPDATE products
|
|
SET
|
|
rating = (
|
|
SELECT COALESCE(AVG(rating), 0)
|
|
FROM reviews
|
|
WHERE product_id = COALESCE(NEW.product_id, OLD.product_id)
|
|
AND is_approved = TRUE
|
|
),
|
|
review_count = (
|
|
SELECT COUNT(*)
|
|
FROM reviews
|
|
WHERE product_id = COALESCE(NEW.product_id, OLD.product_id)
|
|
AND is_approved = TRUE
|
|
)
|
|
WHERE id = COALESCE(NEW.product_id, OLD.product_id);
|
|
|
|
RETURN COALESCE(NEW, OLD);
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
-- Функция для генерации номера заказа
|
|
CREATE OR REPLACE FUNCTION generate_order_number()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
IF NEW.order_number IS NULL OR NEW.order_number = '' THEN
|
|
NEW.order_number := 'ORD-' || TO_CHAR(NOW(), 'YYYYMMDD') || '-' || LPAD(NEW.id::TEXT, 6, '0');
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
-- Функция для получения ID статуса заказа по slug
|
|
CREATE OR REPLACE FUNCTION get_order_status_id(status_slug VARCHAR)
|
|
RETURNS INTEGER AS $$
|
|
DECLARE
|
|
status_id INTEGER;
|
|
BEGIN
|
|
SELECT id INTO status_id
|
|
FROM order_statuses
|
|
WHERE slug = status_slug AND is_active = TRUE
|
|
LIMIT 1;
|
|
|
|
RETURN status_id;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
-- Функция для получения ID статуса платежа по slug
|
|
CREATE OR REPLACE FUNCTION get_payment_status_id(status_slug VARCHAR)
|
|
RETURNS INTEGER AS $$
|
|
DECLARE
|
|
status_id INTEGER;
|
|
BEGIN
|
|
SELECT id INTO status_id
|
|
FROM payment_statuses
|
|
WHERE slug = status_slug AND is_active = TRUE
|
|
LIMIT 1;
|
|
|
|
RETURN status_id;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
-- Функция для получения ID способа оплаты по slug
|
|
CREATE OR REPLACE FUNCTION get_payment_method_id(method_slug VARCHAR)
|
|
RETURNS INTEGER AS $$
|
|
DECLARE
|
|
method_id INTEGER;
|
|
BEGIN
|
|
SELECT id INTO method_id
|
|
FROM payment_methods
|
|
WHERE slug = method_slug AND is_active = TRUE
|
|
LIMIT 1;
|
|
|
|
RETURN method_id;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
-- Комментарии к таблицам
|
|
COMMENT ON TABLE resources IS 'Ресурсы системы (products, orders, categories и т.д.)';
|
|
COMMENT ON TABLE actions IS 'Действия над ресурсами (view, create, edit, delete и т.д.)';
|
|
COMMENT ON TABLE permissions IS 'Права доступа к ресурсам системы';
|
|
COMMENT ON TABLE roles IS 'Роли пользователей системы';
|
|
COMMENT ON TABLE role_permissions IS 'Связь ролей и прав доступа (многие-ко-многим)';
|
|
COMMENT ON TABLE users IS 'Пользователи системы (покупатели и администраторы)';
|
|
COMMENT ON TABLE categories IS 'Категории товаров с поддержкой иерархии';
|
|
COMMENT ON TABLE order_statuses IS 'Статусы заказов';
|
|
COMMENT ON TABLE payment_methods IS 'Способы оплаты';
|
|
COMMENT ON TABLE payment_statuses IS 'Статусы платежей';
|
|
COMMENT ON TABLE products IS 'Товары магазина';
|
|
COMMENT ON TABLE product_images IS 'Изображения товаров';
|
|
COMMENT ON TABLE addresses IS 'Адреса доставки пользователей';
|
|
COMMENT ON TABLE orders IS 'Заказы покупателей';
|
|
COMMENT ON TABLE order_items IS 'Элементы заказа (товары в заказе)';
|
|
COMMENT ON TABLE cart_items IS 'Корзина покупок';
|
|
COMMENT ON TABLE reviews IS 'Отзывы на товары';
|
|
COMMENT ON TABLE payments IS 'Платежи по заказам';
|
|
|
|
-- Таблица настроек системы
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
id SERIAL PRIMARY KEY,
|
|
key VARCHAR(100) UNIQUE NOT NULL,
|
|
value TEXT,
|
|
description TEXT,
|
|
type VARCHAR(50) DEFAULT 'string' CHECK (type IN ('string', 'boolean', 'number', 'json')),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
COMMENT ON TABLE settings IS 'Настройки системы';
|
|
COMMENT ON COLUMN settings.key IS 'Уникальный ключ настройки';
|
|
COMMENT ON COLUMN settings.value IS 'Значение настройки (текст)';
|
|
COMMENT ON COLUMN settings.type IS 'Тип значения: string, boolean, number, json';
|
|
|