498 lines
19 KiB
MySQL
498 lines
19 KiB
MySQL
|
-- Baseline Migration: Complete database schema
|
|||
|
|
-- This migration represents the current state of the database schema
|
|||
|
|
-- For existing databases, use: migrate force 1 (do not run this migration)
|
|||
|
|
-- For new databases, this will create the complete schema
|
|||
|
|
|
|||
|
|
-- ============================================
|
|||
|
|
-- Core Tables (no dependencies)
|
|||
|
|
-- ============================================
|
|||
|
|
|
|||
|
|
-- Users table (base for multi-tenancy)
|
|||
|
|
CREATE TABLE users (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|||
|
|
password_hash VARCHAR(255) NOT NULL,
|
|||
|
|
name VARCHAR(255),
|
|||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|||
|
|
last_login_at TIMESTAMP WITH TIME ZONE
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_users_email ON users(email);
|
|||
|
|
|
|||
|
|
-- Dictionaries table
|
|||
|
|
CREATE TABLE dictionaries (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
name VARCHAR(255) NOT NULL,
|
|||
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_dictionaries_user_id ON dictionaries(user_id);
|
|||
|
|
|
|||
|
|
-- Insert default dictionary with id = 0
|
|||
|
|
DO $$
|
|||
|
|
BEGIN
|
|||
|
|
-- Set sequence to -1 so next value will be 0
|
|||
|
|
PERFORM setval('dictionaries_id_seq', -1, false);
|
|||
|
|
|
|||
|
|
-- Insert the default dictionary with id = 0
|
|||
|
|
INSERT INTO dictionaries (id, name)
|
|||
|
|
VALUES (0, 'Все слова')
|
|||
|
|
ON CONFLICT (id) DO NOTHING;
|
|||
|
|
|
|||
|
|
-- Set the sequence to start from 1 (so next auto-increment will be 1)
|
|||
|
|
PERFORM setval('dictionaries_id_seq', 1, false);
|
|||
|
|
EXCEPTION
|
|||
|
|
WHEN others THEN
|
|||
|
|
-- If sequence doesn't exist or other error, try without sequence manipulation
|
|||
|
|
INSERT INTO dictionaries (id, name)
|
|||
|
|
VALUES (0, 'Все слова')
|
|||
|
|
ON CONFLICT (id) DO NOTHING;
|
|||
|
|
END $$;
|
|||
|
|
|
|||
|
|
-- Projects table
|
|||
|
|
CREATE TABLE projects (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
name VARCHAR(255) NOT NULL,
|
|||
|
|
priority SMALLINT,
|
|||
|
|
deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
|||
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_projects_deleted ON projects(deleted);
|
|||
|
|
CREATE INDEX idx_projects_user_id ON projects(user_id);
|
|||
|
|
|
|||
|
|
-- Entries table
|
|||
|
|
CREATE TABLE entries (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
text TEXT NOT NULL,
|
|||
|
|
created_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_entries_user_id ON entries(user_id);
|
|||
|
|
|
|||
|
|
-- ============================================
|
|||
|
|
-- Dependent Tables
|
|||
|
|
-- ============================================
|
|||
|
|
|
|||
|
|
-- Words table (depends on dictionaries, users)
|
|||
|
|
CREATE TABLE words (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
name VARCHAR(255) NOT NULL,
|
|||
|
|
translation TEXT NOT NULL,
|
|||
|
|
description TEXT,
|
|||
|
|
dictionary_id INTEGER NOT NULL DEFAULT 0 REFERENCES dictionaries(id),
|
|||
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_words_dictionary_id ON words(dictionary_id);
|
|||
|
|
CREATE INDEX idx_words_user_id ON words(user_id);
|
|||
|
|
|
|||
|
|
-- Progress table (depends on words, users)
|
|||
|
|
CREATE TABLE progress (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
word_id INTEGER NOT NULL REFERENCES words(id) ON DELETE CASCADE,
|
|||
|
|
success INTEGER DEFAULT 0,
|
|||
|
|
failure INTEGER DEFAULT 0,
|
|||
|
|
last_success_at TIMESTAMP,
|
|||
|
|
last_failure_at TIMESTAMP,
|
|||
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|||
|
|
CONSTRAINT progress_word_user_unique UNIQUE (word_id, user_id)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_progress_user_id ON progress(user_id);
|
|||
|
|
CREATE UNIQUE INDEX idx_progress_word_user_unique ON progress(word_id, user_id);
|
|||
|
|
|
|||
|
|
-- Configs table (depends on users)
|
|||
|
|
CREATE TABLE configs (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
words_count INTEGER NOT NULL,
|
|||
|
|
max_cards INTEGER,
|
|||
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_configs_user_id ON configs(user_id);
|
|||
|
|
|
|||
|
|
-- Config dictionaries table (depends on configs, dictionaries)
|
|||
|
|
CREATE TABLE config_dictionaries (
|
|||
|
|
config_id INTEGER NOT NULL REFERENCES configs(id) ON DELETE CASCADE,
|
|||
|
|
dictionary_id INTEGER NOT NULL REFERENCES dictionaries(id) ON DELETE CASCADE,
|
|||
|
|
PRIMARY KEY (config_id, dictionary_id)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_config_dictionaries_config_id ON config_dictionaries(config_id);
|
|||
|
|
CREATE INDEX idx_config_dictionaries_dictionary_id ON config_dictionaries(dictionary_id);
|
|||
|
|
|
|||
|
|
-- Nodes table (depends on projects, entries, users)
|
|||
|
|
CREATE TABLE nodes (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|||
|
|
entry_id INTEGER NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
|
|||
|
|
score NUMERIC(8,4),
|
|||
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_nodes_project_id ON nodes(project_id);
|
|||
|
|
CREATE INDEX idx_nodes_entry_id ON nodes(entry_id);
|
|||
|
|
CREATE INDEX idx_nodes_user_id ON nodes(user_id);
|
|||
|
|
|
|||
|
|
-- Weekly goals table (depends on projects, users)
|
|||
|
|
CREATE TABLE weekly_goals (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|||
|
|
goal_year INTEGER NOT NULL,
|
|||
|
|
goal_week INTEGER NOT NULL,
|
|||
|
|
min_goal_score NUMERIC(10,4) NOT NULL DEFAULT 0,
|
|||
|
|
max_goal_score NUMERIC(10,4),
|
|||
|
|
max_score NUMERIC(10,4),
|
|||
|
|
priority SMALLINT,
|
|||
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|||
|
|
CONSTRAINT weekly_goals_project_id_goal_year_goal_week_key UNIQUE (project_id, goal_year, goal_week)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_weekly_goals_project_id ON weekly_goals(project_id);
|
|||
|
|
CREATE INDEX idx_weekly_goals_user_id ON weekly_goals(user_id);
|
|||
|
|
|
|||
|
|
-- Tasks table (depends on users)
|
|||
|
|
CREATE TABLE tasks (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|||
|
|
name VARCHAR(255) NOT NULL,
|
|||
|
|
completed INTEGER DEFAULT 0,
|
|||
|
|
last_completed_at TIMESTAMP WITH TIME ZONE,
|
|||
|
|
parent_task_id INTEGER REFERENCES tasks(id) ON DELETE CASCADE,
|
|||
|
|
reward_message TEXT,
|
|||
|
|
progression_base NUMERIC(10,4),
|
|||
|
|
deleted BOOLEAN DEFAULT FALSE,
|
|||
|
|
repetition_period INTERVAL,
|
|||
|
|
next_show_at TIMESTAMP WITH TIME ZONE,
|
|||
|
|
repetition_date TEXT,
|
|||
|
|
config_id INTEGER REFERENCES configs(id) ON DELETE SET NULL,
|
|||
|
|
wishlist_id INTEGER,
|
|||
|
|
reward_policy VARCHAR(20) DEFAULT 'personal'
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_tasks_user_id ON tasks(user_id);
|
|||
|
|
CREATE INDEX idx_tasks_parent_task_id ON tasks(parent_task_id);
|
|||
|
|
CREATE INDEX idx_tasks_deleted ON tasks(deleted);
|
|||
|
|
CREATE INDEX idx_tasks_last_completed_at ON tasks(last_completed_at);
|
|||
|
|
CREATE INDEX idx_tasks_config_id ON tasks(config_id);
|
|||
|
|
CREATE UNIQUE INDEX idx_tasks_config_id_unique ON tasks(config_id) WHERE config_id IS NOT NULL AND deleted = FALSE;
|
|||
|
|
CREATE INDEX idx_tasks_wishlist_id ON tasks(wishlist_id);
|
|||
|
|
CREATE UNIQUE INDEX idx_tasks_wishlist_id_unique ON tasks(wishlist_id) WHERE wishlist_id IS NOT NULL AND deleted = FALSE;
|
|||
|
|
CREATE INDEX idx_tasks_id_user_deleted ON tasks(id, user_id, deleted) WHERE deleted = FALSE;
|
|||
|
|
CREATE INDEX idx_tasks_parent_deleted_covering ON tasks(parent_task_id, deleted, id)
|
|||
|
|
INCLUDE (name, completed, last_completed_at, reward_message, progression_base)
|
|||
|
|
WHERE deleted = FALSE;
|
|||
|
|
|
|||
|
|
COMMENT ON COLUMN tasks.config_id IS 'Link to test config. NULL if task is not a test.';
|
|||
|
|
COMMENT ON COLUMN tasks.reward_policy IS 'For wishlist tasks: personal = only if user completes, shared = anyone completes';
|
|||
|
|
|
|||
|
|
-- Reward configs table (depends on tasks, projects)
|
|||
|
|
CREATE TABLE reward_configs (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
position INTEGER NOT NULL,
|
|||
|
|
task_id INTEGER REFERENCES tasks(id) ON DELETE CASCADE,
|
|||
|
|
project_id INTEGER REFERENCES projects(id) ON DELETE CASCADE,
|
|||
|
|
value NUMERIC(10,4) NOT NULL,
|
|||
|
|
use_progression BOOLEAN DEFAULT FALSE
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_reward_configs_task_id ON reward_configs(task_id);
|
|||
|
|
CREATE INDEX idx_reward_configs_project_id ON reward_configs(project_id);
|
|||
|
|
CREATE UNIQUE INDEX idx_reward_configs_task_position ON reward_configs(task_id, position);
|
|||
|
|
|
|||
|
|
-- Telegram integrations table (depends on users)
|
|||
|
|
CREATE TABLE telegram_integrations (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
chat_id VARCHAR(255),
|
|||
|
|
telegram_user_id BIGINT,
|
|||
|
|
start_token VARCHAR(255),
|
|||
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE UNIQUE INDEX idx_telegram_integrations_user_id_unique ON telegram_integrations(user_id) WHERE user_id IS NOT NULL;
|
|||
|
|
CREATE INDEX idx_telegram_integrations_user_id ON telegram_integrations(user_id);
|
|||
|
|
CREATE UNIQUE INDEX idx_telegram_integrations_start_token ON telegram_integrations(start_token) WHERE start_token IS NOT NULL;
|
|||
|
|
CREATE UNIQUE INDEX idx_telegram_integrations_telegram_user_id ON telegram_integrations(telegram_user_id) WHERE telegram_user_id IS NOT NULL;
|
|||
|
|
CREATE INDEX idx_telegram_integrations_chat_id ON telegram_integrations(chat_id) WHERE chat_id IS NOT NULL;
|
|||
|
|
|
|||
|
|
-- Todoist integrations table (depends on users)
|
|||
|
|
CREATE TABLE todoist_integrations (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|||
|
|
todoist_user_id BIGINT,
|
|||
|
|
todoist_email VARCHAR(255),
|
|||
|
|
access_token TEXT,
|
|||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
CONSTRAINT todoist_integrations_user_id_unique UNIQUE (user_id)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_todoist_integrations_user_id ON todoist_integrations(user_id);
|
|||
|
|
CREATE UNIQUE INDEX idx_todoist_integrations_todoist_user_id ON todoist_integrations(todoist_user_id) WHERE todoist_user_id IS NOT NULL;
|
|||
|
|
CREATE UNIQUE INDEX idx_todoist_integrations_todoist_email ON todoist_integrations(todoist_email) WHERE todoist_email IS NOT NULL;
|
|||
|
|
|
|||
|
|
-- Wishlist boards table (depends on users)
|
|||
|
|
CREATE TABLE wishlist_boards (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|||
|
|
name VARCHAR(255) NOT NULL,
|
|||
|
|
invite_token VARCHAR(64) UNIQUE,
|
|||
|
|
invite_enabled 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 idx_wishlist_boards_owner_id ON wishlist_boards(owner_id);
|
|||
|
|
CREATE INDEX idx_wishlist_boards_invite_token ON wishlist_boards(invite_token) WHERE invite_token IS NOT NULL;
|
|||
|
|
CREATE INDEX idx_wishlist_boards_owner_deleted ON wishlist_boards(owner_id, deleted);
|
|||
|
|
|
|||
|
|
-- Wishlist board members table (depends on wishlist_boards, users)
|
|||
|
|
CREATE TABLE wishlist_board_members (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
board_id INTEGER NOT NULL REFERENCES wishlist_boards(id) ON DELETE CASCADE,
|
|||
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|||
|
|
joined_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
CONSTRAINT unique_board_member UNIQUE (board_id, user_id)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_board_members_board_id ON wishlist_board_members(board_id);
|
|||
|
|
CREATE INDEX idx_board_members_user_id ON wishlist_board_members(user_id);
|
|||
|
|
|
|||
|
|
-- Wishlist items table (depends on users, wishlist_boards)
|
|||
|
|
CREATE TABLE 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,
|
|||
|
|
board_id INTEGER REFERENCES wishlist_boards(id) ON DELETE CASCADE,
|
|||
|
|
author_id INTEGER REFERENCES users(id) ON DELETE SET NULL
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_wishlist_items_user_id ON wishlist_items(user_id);
|
|||
|
|
CREATE INDEX idx_wishlist_items_user_deleted ON wishlist_items(user_id, deleted);
|
|||
|
|
CREATE INDEX idx_wishlist_items_user_completed ON wishlist_items(user_id, completed, deleted);
|
|||
|
|
CREATE INDEX idx_wishlist_items_board_id ON wishlist_items(board_id);
|
|||
|
|
CREATE INDEX idx_wishlist_items_author_id ON wishlist_items(author_id);
|
|||
|
|
CREATE INDEX idx_wishlist_items_id_deleted_covering ON wishlist_items(id, deleted)
|
|||
|
|
INCLUDE (name)
|
|||
|
|
WHERE deleted = FALSE;
|
|||
|
|
|
|||
|
|
-- Add foreign key for tasks.wishlist_id after wishlist_items is created
|
|||
|
|
ALTER TABLE tasks ADD CONSTRAINT tasks_wishlist_id_fkey
|
|||
|
|
FOREIGN KEY (wishlist_id) REFERENCES wishlist_items(id) ON DELETE SET NULL;
|
|||
|
|
|
|||
|
|
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';
|
|||
|
|
|
|||
|
|
-- Task conditions table (depends on tasks)
|
|||
|
|
CREATE TABLE 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 idx_task_conditions_task_id ON task_conditions(task_id);
|
|||
|
|
|
|||
|
|
COMMENT ON TABLE task_conditions IS 'Reusable unlock conditions based on task completion';
|
|||
|
|
|
|||
|
|
-- Score conditions table (depends on projects, users)
|
|||
|
|
CREATE TABLE score_conditions (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|||
|
|
required_points NUMERIC(10,4) NOT NULL,
|
|||
|
|
start_date DATE,
|
|||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|||
|
|
CONSTRAINT unique_score_condition UNIQUE (project_id, required_points, start_date)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_score_conditions_project_id ON score_conditions(project_id);
|
|||
|
|
CREATE INDEX idx_score_conditions_user_id ON score_conditions(user_id);
|
|||
|
|
|
|||
|
|
COMMENT ON TABLE score_conditions IS 'Reusable unlock conditions based on project points';
|
|||
|
|
COMMENT ON COLUMN score_conditions.start_date IS 'Date from which to start counting points. NULL means count all time.';
|
|||
|
|
|
|||
|
|
-- Wishlist conditions table (depends on wishlist_items, task_conditions, score_conditions, users)
|
|||
|
|
CREATE TABLE 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,
|
|||
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|||
|
|
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 idx_wishlist_conditions_item_id ON wishlist_conditions(wishlist_item_id);
|
|||
|
|
CREATE INDEX idx_wishlist_conditions_item_order ON wishlist_conditions(wishlist_item_id, display_order);
|
|||
|
|
CREATE INDEX idx_wishlist_conditions_task_condition_id ON wishlist_conditions(task_condition_id);
|
|||
|
|
CREATE INDEX idx_wishlist_conditions_score_condition_id ON wishlist_conditions(score_condition_id);
|
|||
|
|
CREATE INDEX idx_wishlist_conditions_user_id ON wishlist_conditions(user_id);
|
|||
|
|
|
|||
|
|
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';
|
|||
|
|
COMMENT ON COLUMN wishlist_conditions.user_id IS 'Owner of this condition. Each user has their own goals on shared boards';
|
|||
|
|
|
|||
|
|
-- Refresh tokens table (depends on users)
|
|||
|
|
CREATE TABLE refresh_tokens (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|||
|
|
token_hash VARCHAR(255) NOT NULL,
|
|||
|
|
expires_at TIMESTAMP WITH TIME ZONE,
|
|||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_refresh_tokens_user_id ON refresh_tokens(user_id);
|
|||
|
|
CREATE INDEX idx_refresh_tokens_token_hash ON refresh_tokens(token_hash);
|
|||
|
|
|
|||
|
|
-- ============================================
|
|||
|
|
-- Materialized Views
|
|||
|
|
-- ============================================
|
|||
|
|
|
|||
|
|
-- Weekly report materialized view
|
|||
|
|
CREATE MATERIALIZED VIEW weekly_report_mv AS
|
|||
|
|
SELECT
|
|||
|
|
p.id AS project_id,
|
|||
|
|
agg.report_year,
|
|||
|
|
agg.report_week,
|
|||
|
|
COALESCE(agg.total_score, 0.0000) AS total_score,
|
|||
|
|
CASE
|
|||
|
|
WHEN wg.max_score IS NULL THEN COALESCE(agg.total_score, 0.0000)
|
|||
|
|
ELSE LEAST(COALESCE(agg.total_score, 0.0000), wg.max_score)
|
|||
|
|
END AS normalized_total_score
|
|||
|
|
FROM
|
|||
|
|
projects p
|
|||
|
|
LEFT JOIN
|
|||
|
|
(
|
|||
|
|
SELECT
|
|||
|
|
n.project_id,
|
|||
|
|
EXTRACT(ISOYEAR FROM e.created_date)::INTEGER AS report_year,
|
|||
|
|
EXTRACT(WEEK FROM e.created_date)::INTEGER AS report_week,
|
|||
|
|
SUM(n.score) AS total_score
|
|||
|
|
FROM
|
|||
|
|
nodes n
|
|||
|
|
JOIN
|
|||
|
|
entries e ON n.entry_id = e.id
|
|||
|
|
GROUP BY
|
|||
|
|
1, 2, 3
|
|||
|
|
) agg
|
|||
|
|
ON p.id = agg.project_id
|
|||
|
|
LEFT JOIN
|
|||
|
|
weekly_goals wg
|
|||
|
|
ON wg.project_id = p.id
|
|||
|
|
AND wg.goal_year = agg.report_year
|
|||
|
|
AND wg.goal_week = agg.report_week
|
|||
|
|
WHERE
|
|||
|
|
p.deleted = FALSE
|
|||
|
|
ORDER BY
|
|||
|
|
p.id, agg.report_year, agg.report_week
|
|||
|
|
WITH DATA;
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_weekly_report_mv_project_year_week ON weekly_report_mv(project_id, report_year, report_week);
|
|||
|
|
|
|||
|
|
COMMENT ON MATERIALIZED VIEW weekly_report_mv IS 'Materialized view aggregating weekly scores by project using ISOYEAR for correct week calculations at year boundaries. Includes all projects via LEFT JOIN. Adds normalized_total_score using weekly_goals.max_score snapshot.';
|
|||
|
|
|
|||
|
|
-- ============================================
|
|||
|
|
-- Comments
|
|||
|
|
-- ============================================
|
|||
|
|
|
|||
|
|
COMMENT ON TABLE configs IS 'Test configurations (words_count, max_cards, dictionary associations). Linked to tasks via tasks.config_id.';
|
|||
|
|
COMMENT ON TABLE wishlist_boards IS 'Wishlist boards for organizing and sharing wishes';
|
|||
|
|
COMMENT ON COLUMN wishlist_boards.invite_token IS 'Token for invite link, NULL = disabled';
|
|||
|
|
COMMENT ON COLUMN wishlist_boards.invite_enabled IS 'Whether invite link is active';
|
|||
|
|
COMMENT ON TABLE wishlist_board_members IS 'Users who joined boards via invite link (not owners)';
|
|||
|
|
COMMENT ON COLUMN wishlist_items.author_id IS 'User who created this item (may differ from board owner on shared boards)';
|
|||
|
|
COMMENT ON COLUMN wishlist_items.board_id IS 'Board this item belongs to';
|
|||
|
|
|
|||
|
|
-- ============================================
|
|||
|
|
-- Additional Tables
|
|||
|
|
-- ============================================
|
|||
|
|
|
|||
|
|
-- Eateries table
|
|||
|
|
CREATE TABLE eateries (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
name VARCHAR(255),
|
|||
|
|
address VARCHAR(255),
|
|||
|
|
type VARCHAR(50),
|
|||
|
|
distance DOUBLE PRECISION
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Interesting places table
|
|||
|
|
CREATE TABLE interesting_places (
|
|||
|
|
id INTEGER PRIMARY KEY,
|
|||
|
|
name TEXT,
|
|||
|
|
city TEXT,
|
|||
|
|
description TEXT,
|
|||
|
|
added_at TIMESTAMP WITH TIME ZONE,
|
|||
|
|
is_visited BOOLEAN,
|
|||
|
|
phone_number TEXT,
|
|||
|
|
address TEXT,
|
|||
|
|
updated_at TIMESTAMP WITH TIME ZONE
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Music groups table
|
|||
|
|
CREATE TABLE music_groups (
|
|||
|
|
id INTEGER PRIMARY KEY,
|
|||
|
|
name TEXT,
|
|||
|
|
possible_locations TEXT
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- N8N chat histories table
|
|||
|
|
CREATE TABLE n8n_chat_histories (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
session_id VARCHAR(255) NOT NULL,
|
|||
|
|
message JSONB NOT NULL
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Places to visit table
|
|||
|
|
CREATE TABLE places_to_visit (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
name TEXT NOT NULL,
|
|||
|
|
city TEXT,
|
|||
|
|
description TEXT,
|
|||
|
|
added_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|||
|
|
is_visited BOOLEAN DEFAULT FALSE,
|
|||
|
|
phone_number TEXT,
|
|||
|
|
address TEXT,
|
|||
|
|
updated_at TIMESTAMP WITH TIME ZONE
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Restaurants table
|
|||
|
|
CREATE TABLE restaurants (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
name VARCHAR(255),
|
|||
|
|
address VARCHAR(255),
|
|||
|
|
contact_info VARCHAR(255)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Upcoming concerts table (depends on music_groups)
|
|||
|
|
CREATE TABLE upcoming_concerts (
|
|||
|
|
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|||
|
|
group_id INTEGER NOT NULL REFERENCES music_groups(id),
|
|||
|
|
scheduled_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
|||
|
|
venue TEXT,
|
|||
|
|
city TEXT,
|
|||
|
|
tickets_url TEXT
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE UNIQUE INDEX idx_unique_concert ON upcoming_concerts(scheduled_at, city, group_id);
|