init.sql

-- ========================================
-- UNIFIED USER SYSTEM MIGRATION
-- ========================================
-- This script merges 'user' and 'vk_user' tables into a single 'users' table
-- Benefits:
-- - Single source of truth for all users
-- - Unified session management
-- - Simpler queries and maintenance
-- - Better scalability
--
-- NOTE: When running via Docker, the database and user are created automatically
-- from POSTGRES_USER, POSTGRES_PASSWORD, and POSTGRES_DB environment variables.
-- This script only creates tables and schema within the existing database.

-- Create achievements table if not exists
CREATE TABLE IF NOT EXISTS achievements (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    icon VARCHAR(50) NOT NULL,
    category VARCHAR(50) NOT NULL,
    category_icon VARCHAR(50) NOT NULL,
    rarity VARCHAR(20) NOT NULL,
    required_progress INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default achievements (only if they don't exist)
INSERT INTO achievements (id, title, description, icon, category, category_icon, rarity, required_progress)
VALUES
(1, 'Первый шаг', 'Сделал первую голосовую запись в дневнике', '🎤', 'voice', '🎤', 'common', 1),
(2, '7 дней подряд', 'Вел голосовой дневник неделю без пропусков', '🔥', 'regularity', '📅', 'rare', 7),
(3, 'Месячный марафон', '30 дней ведения голосового дневника', '🏆', 'regularity', '📅', 'epic', 30),
(4, 'Радуга эмоций', 'Выразил 5 или более разных эмоций в записях', '🌈', 'variety', '🎭', 'rare', 5),
(5, 'Взгляд в прошлое', 'Прослушал записи за другой день (месяц назад)', '🔍', 'reflection', '🤔', 'rare', 1),
(6, 'Луч света', 'Серия из 5 позитивных записей после грустной', '✨', 'positivity', '😊', 'epic', 5),
(7, 'Эмоциональный детектив', 'Проанализировал 50 различных записей', '🕵️', 'analysis', '📊', 'legendary', 50),
(8, 'Голос сердца', 'Записал 100 минут размышлений', '💖', 'voice', '🎤', 'common', 100),
(9, 'Сердечный друг', 'Поделился достижениями с друзьями', '💖', 'social', '💬', 'common', 1)
ON CONFLICT (id) DO NOTHING;

-- ========================================
-- UNIFIED USERS TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS users (
    user_id SERIAL PRIMARY KEY,
    user_type VARCHAR(20) NOT NULL CHECK (user_type IN ('regular', 'vk')),
    
    -- Regular user fields (NULL for VK users)
    login VARCHAR(255) UNIQUE,
    password VARCHAR(255),
    
    -- Common fields
    nickname VARCHAR(100) NOT NULL,
    
    -- VK user fields (NULL for regular users)
    vk_user_id INTEGER UNIQUE,
    
    -- Universal fields
    coins INTEGER DEFAULT 100,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT check_regular_user CHECK (
        (user_type = 'regular' AND login IS NOT NULL AND password IS NOT NULL AND vk_user_id IS NULL)
        OR
        (user_type = 'vk' AND vk_user_id IS NOT NULL AND login IS NULL AND password IS NULL)
    )
);

-- ========================================
-- UNIFIED SESSION TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS session (
    session_id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL UNIQUE REFERENCES users(user_id) ON DELETE CASCADE,
    token VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ========================================
-- RECORDS TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS record (
    record_id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    record_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    emotion VARCHAR(50),
    summary TEXT,
    feedback INTEGER CHECK (feedback >= 1 AND feedback <= 5),
    insights TEXT
);

-- ========================================
-- USER TOTALS TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS user_totals (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    date DATE NOT NULL,
    emotion VARCHAR(50),
    summary TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, date)
);

-- USER ACHIEVEMENTS TABLE
-- ========================================
-- Drop old version if it exists (without user_type)
DROP TABLE IF EXISTS user_achievements;

-- New unified user_achievements table that supports both regular and VK users
CREATE TABLE IF NOT EXISTS user_achievements (
    id SERIAL PRIMARY KEY,
    user_type VARCHAR(20) NOT NULL CHECK (user_type IN ('regular', 'vk')),
    user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    achievement_id INTEGER REFERENCES achievements(id),
    progress INTEGER DEFAULT 0,
    unlocked BOOLEAN DEFAULT FALSE,
    date_unlocked TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_type, user_id, achievement_id)
);

-- ========================================
-- INDEXES FOR PERFORMANCE
-- ========================================
CREATE INDEX IF NOT EXISTS idx_users_login ON users(login);
CREATE INDEX IF NOT EXISTS idx_users_vk_user_id ON users(vk_user_id);
CREATE INDEX IF NOT EXISTS idx_users_type ON users(user_type);

CREATE INDEX IF NOT EXISTS idx_session_token ON session(token);
CREATE INDEX IF NOT EXISTS idx_session_user_id ON session(user_id);

CREATE INDEX IF NOT EXISTS idx_record_user_id ON record(user_id);
CREATE INDEX IF NOT EXISTS idx_record_date ON record(record_date);
CREATE INDEX IF NOT EXISTS idx_record_user_date ON record(user_id, record_date);

CREATE INDEX IF NOT EXISTS idx_user_totals_user_id ON user_totals(user_id);
CREATE INDEX IF NOT EXISTS idx_user_totals_date ON user_totals(date);
CREATE INDEX IF NOT EXISTS idx_user_totals_user_date ON user_totals(user_id, date);

CREATE INDEX IF NOT EXISTS idx_user_achievements_user_id ON user_achievements(user_id);
CREATE INDEX IF NOT EXISTS idx_user_achievements_unlocked ON user_achievements(unlocked);

-- ========================================
-- MIGRATION SCRIPT (Run this after backup!)
-- ========================================
-- Step 1: Backup existing data
-- pg_dump voicebook_db > backup_before_migration.sql

-- Step 2: If migrating from old schema, run this:
/*
-- Migrate regular users
INSERT INTO users (user_type, login, password, nickname, coins, created_at)
SELECT 'regular', login, password, nickname, 100, created_at
FROM "user"
ON CONFLICT DO NOTHING;

-- Migrate VK users
INSERT INTO users (user_type, vk_user_id, nickname, coins, created_at)
SELECT 'vk', vk_user_id, COALESCE('VK User ' || vk_user_id, 'VK User'), coins, created_at
FROM vk_user
ON CONFLICT DO NOTHING;

-- Migrate regular user sessions
INSERT INTO session (user_id, token, created_at)
SELECT u.user_id, s.token, s.created_at
FROM (SELECT * FROM session) s
JOIN "user" old_u ON s.user_id = old_u.user_id
JOIN users u ON u.login = old_u.login AND u.user_type = 'regular'
ON CONFLICT (user_id) DO UPDATE SET token = EXCLUDED.token, created_at = EXCLUDED.created_at;

-- Migrate VK user sessions (from vk_session if it exists)
INSERT INTO session (user_id, token, created_at)
SELECT u.user_id, vs.token, vs.created_at
FROM vk_session vs
JOIN vk_user vu ON vs.vk_user_id = vu.id
JOIN users u ON u.vk_user_id = vu.vk_user_id AND u.user_type = 'vk'
ON CONFLICT (user_id) DO UPDATE SET token = EXCLUDED.token, created_at = EXCLUDED.created_at;

-- Update records to point to new user IDs (if needed)
-- This depends on your current record structure

-- Step 3: After verification, drop old tables
-- DROP TABLE IF EXISTS vk_session;
-- DROP TABLE IF EXISTS "user" CASCADE;
-- DROP TABLE IF EXISTS vk_user CASCADE;
*/