play-life/play-life-backend/migrations/019_add_wishlist.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';