e2059ef555
All checks were successful
Build and Push Docker Image / build-and-push (push) Successful in 1m5s
- Добавлен эндпоинт /api/wishlist/metadata для извлечения метаданных из URL - Реализовано извлечение Open Graph тегов (title, image, description) - Добавлена кнопка Pull для ручной загрузки информации из ссылки - Автоматическое заполнение полей: название, цена, картинка - Обновлена версия до 3.9.0
87 lines
4.2 KiB
SQL
87 lines
4.2 KiB
SQL
-- Migration: Add wishlist tables
|
|
-- This script creates tables for wishlist management system
|
|
-- Supports multiple unlock conditions per wishlist item (AND logic)
|
|
|
|
-- ============================================
|
|
-- Table: wishlist_items
|
|
-- ============================================
|
|
CREATE TABLE IF NOT EXISTS wishlist_items (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
name VARCHAR(255) NOT NULL,
|
|
price NUMERIC(10,2),
|
|
image_path VARCHAR(500),
|
|
link TEXT,
|
|
completed BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
deleted BOOLEAN DEFAULT FALSE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_wishlist_items_user_id ON wishlist_items(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_wishlist_items_user_deleted ON wishlist_items(user_id, deleted);
|
|
CREATE INDEX IF NOT EXISTS idx_wishlist_items_user_completed ON wishlist_items(user_id, completed, deleted);
|
|
|
|
-- ============================================
|
|
-- Table: task_conditions
|
|
-- ============================================
|
|
-- Reusable conditions for task completion
|
|
CREATE TABLE IF NOT EXISTS task_conditions (
|
|
id SERIAL PRIMARY KEY,
|
|
task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT unique_task_condition UNIQUE (task_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_task_conditions_task_id ON task_conditions(task_id);
|
|
|
|
-- ============================================
|
|
-- Table: score_conditions
|
|
-- ============================================
|
|
-- Reusable conditions for project points
|
|
CREATE TABLE IF NOT EXISTS score_conditions (
|
|
id SERIAL PRIMARY KEY,
|
|
project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
required_points NUMERIC(10,4) NOT NULL,
|
|
period_type VARCHAR(20), -- 'week', 'month', 'year', NULL (all time)
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT unique_score_condition UNIQUE (project_id, required_points, period_type)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_score_conditions_project_id ON score_conditions(project_id);
|
|
|
|
-- ============================================
|
|
-- Table: wishlist_conditions
|
|
-- ============================================
|
|
-- Links wishlist items to unlock conditions
|
|
CREATE TABLE IF NOT EXISTS wishlist_conditions (
|
|
id SERIAL PRIMARY KEY,
|
|
wishlist_item_id INTEGER NOT NULL REFERENCES wishlist_items(id) ON DELETE CASCADE,
|
|
task_condition_id INTEGER REFERENCES task_conditions(id) ON DELETE CASCADE,
|
|
score_condition_id INTEGER REFERENCES score_conditions(id) ON DELETE CASCADE,
|
|
display_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT check_exactly_one_condition CHECK (
|
|
(task_condition_id IS NOT NULL AND score_condition_id IS NULL) OR
|
|
(task_condition_id IS NULL AND score_condition_id IS NOT NULL)
|
|
)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_wishlist_conditions_item_id ON wishlist_conditions(wishlist_item_id);
|
|
CREATE INDEX IF NOT EXISTS idx_wishlist_conditions_item_order ON wishlist_conditions(wishlist_item_id, display_order);
|
|
CREATE INDEX IF NOT EXISTS idx_wishlist_conditions_task_condition_id ON wishlist_conditions(task_condition_id);
|
|
CREATE INDEX IF NOT EXISTS idx_wishlist_conditions_score_condition_id ON wishlist_conditions(score_condition_id);
|
|
|
|
-- ============================================
|
|
-- Comments for documentation
|
|
-- ============================================
|
|
COMMENT ON TABLE wishlist_items IS 'Wishlist items for users';
|
|
COMMENT ON COLUMN wishlist_items.completed IS 'Flag indicating item was purchased/received';
|
|
COMMENT ON COLUMN wishlist_items.image_path IS 'Path to image file relative to uploads root';
|
|
|
|
COMMENT ON TABLE task_conditions IS 'Reusable unlock conditions based on task completion';
|
|
COMMENT ON TABLE score_conditions IS 'Reusable unlock conditions based on project points';
|
|
COMMENT ON TABLE wishlist_conditions IS 'Links between wishlist items and unlock conditions. Multiple conditions per item use AND logic.';
|
|
COMMENT ON COLUMN wishlist_conditions.display_order IS 'Order for displaying conditions in UI';
|
|
|