play-life/play-life-backend/migrations/023_add_wishlist_boards.sql

-- Migration: Add wishlist boards for multi-user collaboration
-- Each user can have multiple boards, share them via invite links,
-- and collaborate with other users on shared wishes

-- ============================================
-- Table: wishlist_boards (доски желаний)
-- ============================================
CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS idx_wishlist_boards_owner_id ON wishlist_boards(owner_id);
CREATE INDEX IF NOT EXISTS idx_wishlist_boards_invite_token ON wishlist_boards(invite_token) 
    WHERE invite_token IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_wishlist_boards_owner_deleted ON wishlist_boards(owner_id, deleted);

-- ============================================
-- Table: wishlist_board_members (участники доски)
-- ============================================
CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS idx_board_members_board_id ON wishlist_board_members(board_id);
CREATE INDEX IF NOT EXISTS idx_board_members_user_id ON wishlist_board_members(user_id);

-- ============================================
-- Modify: wishlist_items - добавляем board_id и author_id
-- ============================================
ALTER TABLE wishlist_items 
ADD COLUMN IF NOT EXISTS board_id INTEGER REFERENCES wishlist_boards(id) ON DELETE CASCADE;

ALTER TABLE wishlist_items 
ADD COLUMN IF NOT EXISTS author_id INTEGER REFERENCES users(id) ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS idx_wishlist_items_board_id ON wishlist_items(board_id);
CREATE INDEX IF NOT EXISTS idx_wishlist_items_author_id ON wishlist_items(author_id);

-- ============================================
-- Modify: wishlist_conditions - добавляем user_id для персональных целей
-- ============================================
ALTER TABLE wishlist_conditions 
ADD COLUMN IF NOT EXISTS user_id INTEGER REFERENCES users(id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS idx_wishlist_conditions_user_id ON wishlist_conditions(user_id);

-- ============================================
-- Modify: tasks - добавляем политику награждения для wishlist задач
-- ============================================
ALTER TABLE tasks 
ADD COLUMN IF NOT EXISTS reward_policy VARCHAR(20) DEFAULT 'personal';

COMMENT ON COLUMN tasks.reward_policy IS 
    'For wishlist tasks: personal = only if user completes, shared = anyone completes';

-- ============================================
-- Миграция данных: Этап 1 - создаём персональные доски
-- ============================================
-- Создаём доску "Мои желания" для каждого пользователя с желаниями
INSERT INTO wishlist_boards (owner_id, name)
SELECT DISTINCT user_id, 'Мои желания'
FROM wishlist_items
WHERE user_id IS NOT NULL 
  AND deleted = FALSE
  AND NOT EXISTS (
      SELECT 1 FROM wishlist_boards wb 
      WHERE wb.owner_id = wishlist_items.user_id AND wb.name = 'Мои желания'
  );

-- ============================================
-- Миграция данных: Этап 2 - привязываем желания к доскам
-- ============================================
UPDATE wishlist_items wi
SET 
    board_id = wb.id,
    author_id = COALESCE(wi.author_id, wi.user_id)
FROM wishlist_boards wb
WHERE wi.board_id IS NULL 
  AND wi.user_id = wb.owner_id 
  AND wb.name = 'Мои желания';

-- ============================================
-- Миграция данных: Этап 3 - заполняем user_id в условиях
-- ============================================
UPDATE wishlist_conditions wc
SET user_id = wi.user_id
FROM wishlist_items wi
WHERE wc.wishlist_item_id = wi.id
  AND wc.user_id IS NULL;

-- ============================================
-- Comments
-- ============================================
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_conditions.user_id IS 'Owner of this condition. Each user has their own goals on shared boards.';
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';