play-life/play-life-backend/migrations/009_add_users_and_multitenancy.sql

-- Migration: Add users table and user_id to all tables for multi-tenancy
-- This script adds user authentication and makes all data user-specific
-- All statements use IF NOT EXISTS / IF EXISTS for idempotency

-- ============================================
-- Table: users
-- ============================================
CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS idx_users_email ON users(email);

-- ============================================
-- Table: refresh_tokens
-- ============================================
CREATE TABLE IF NOT EXISTS 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 NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_refresh_tokens_user_id ON refresh_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_token_hash ON refresh_tokens(token_hash);

-- ============================================
-- Add user_id to projects
-- ============================================
ALTER TABLE projects 
ADD COLUMN IF NOT EXISTS user_id INTEGER REFERENCES users(id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS idx_projects_user_id ON projects(user_id);

-- Drop old unique constraint (name now unique per user, handled in app)
ALTER TABLE projects DROP CONSTRAINT IF EXISTS unique_project_name;

-- ============================================
-- Add user_id to entries
-- ============================================
ALTER TABLE entries 
ADD COLUMN IF NOT EXISTS user_id INTEGER REFERENCES users(id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS idx_entries_user_id ON entries(user_id);

-- ============================================
-- Add user_id to dictionaries
-- ============================================
ALTER TABLE dictionaries 
ADD COLUMN IF NOT EXISTS user_id INTEGER REFERENCES users(id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS idx_dictionaries_user_id ON dictionaries(user_id);

-- ============================================
-- Add user_id to words
-- ============================================
ALTER TABLE words 
ADD COLUMN IF NOT EXISTS user_id INTEGER REFERENCES users(id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS idx_words_user_id ON words(user_id);

-- ============================================
-- Add user_id to progress
-- ============================================
ALTER TABLE progress 
ADD COLUMN IF NOT EXISTS user_id INTEGER REFERENCES users(id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS idx_progress_user_id ON progress(user_id);

-- Drop old unique constraint (word_id now unique per user)
ALTER TABLE progress DROP CONSTRAINT IF EXISTS progress_word_id_key;

-- Create new unique constraint per user
CREATE UNIQUE INDEX IF NOT EXISTS idx_progress_word_user_unique ON progress(word_id, user_id);

-- ============================================
-- Add user_id to configs
-- ============================================
ALTER TABLE configs 
ADD COLUMN IF NOT EXISTS user_id INTEGER REFERENCES users(id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS idx_configs_user_id ON configs(user_id);

-- ============================================
-- Add user_id to telegram_integrations
-- ============================================
ALTER TABLE telegram_integrations 
ADD COLUMN IF NOT EXISTS user_id INTEGER REFERENCES users(id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS idx_telegram_integrations_user_id ON telegram_integrations(user_id);

-- ============================================
-- Add user_id to weekly_goals
-- ============================================
ALTER TABLE weekly_goals 
ADD COLUMN IF NOT EXISTS user_id INTEGER REFERENCES users(id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS idx_weekly_goals_user_id ON weekly_goals(user_id);

-- ============================================
-- Add user_id to nodes (score data)
-- ============================================
ALTER TABLE nodes 
ADD COLUMN IF NOT EXISTS user_id INTEGER REFERENCES users(id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS idx_nodes_user_id ON nodes(user_id);

-- ============================================
-- Comments for documentation
-- ============================================
COMMENT ON TABLE users IS 'Users table for authentication and multi-tenancy';
COMMENT ON COLUMN users.email IS 'User email address (unique, used for login)';
COMMENT ON COLUMN users.password_hash IS 'Bcrypt hashed password';
COMMENT ON COLUMN users.name IS 'User display name';
COMMENT ON COLUMN users.is_active IS 'Whether the user account is active';
COMMENT ON TABLE refresh_tokens IS 'JWT refresh tokens for persistent login';

-- Note: The first user who logs in will automatically become the owner of all
-- existing data (projects, entries, dictionaries, words, etc.) that have NULL user_id.
-- This is handled in the application code (claimOrphanedData function).