# Consolidated SQL файл для миграции базы данных Telegram Tinder Bot
# Этот файл содержит все необходимые SQL-запросы для создания базы данных с нуля

-- Создание расширения для UUID
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Создание перечислений
CREATE TYPE gender_type AS ENUM ('male', 'female', 'other');
CREATE TYPE swipe_action AS ENUM ('like', 'dislike', 'superlike');

-- Создание таблицы пользователей
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    telegram_id BIGINT UNIQUE NOT NULL,
    username VARCHAR(255),
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255),
    language_code VARCHAR(10),
    is_premium BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Создание таблицы профилей
CREATE TABLE IF NOT EXISTS profiles (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL CHECK (age >= 18),
    gender gender_type NOT NULL,
    bio TEXT,
    photos TEXT[], -- JSON array of photo file_ids
    location VARCHAR(255),
    job VARCHAR(255),
    interests TEXT[], -- JSON array of interests
    last_active TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    is_completed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_user_profile UNIQUE (user_id)
);

-- Создание индекса для поиска по возрасту и полу
CREATE INDEX idx_profiles_age_gender ON profiles(age, gender);

-- Создание таблицы предпочтений поиска
CREATE TABLE IF NOT EXISTS search_preferences (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    age_min INTEGER NOT NULL DEFAULT 18 CHECK (age_min >= 18),
    age_max INTEGER NOT NULL DEFAULT 99 CHECK (age_max >= age_min),
    looking_for gender_type NOT NULL,
    distance_max INTEGER, -- max distance in km, NULL means no limit
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_user_preferences UNIQUE (user_id)
);

-- Создание таблицы действий (лайки/дизлайки)
CREATE TABLE IF NOT EXISTS swipes (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    target_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    action swipe_action NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_swipe UNIQUE (user_id, target_id)
);

-- Создание индекса для быстрого поиска матчей
CREATE INDEX idx_swipes_user_target ON swipes(user_id, target_id);

-- Создание таблицы матчей
CREATE TABLE IF NOT EXISTS matches (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id_1 UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    user_id_2 UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    CONSTRAINT unique_match UNIQUE (user_id_1, user_id_2)
);

-- Создание индекса для быстрого поиска матчей по пользователю
CREATE INDEX idx_matches_user_id_1 ON matches(user_id_1);
CREATE INDEX idx_matches_user_id_2 ON matches(user_id_2);

-- Создание таблицы блокировок
CREATE TABLE IF NOT EXISTS blocks (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    blocker_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    blocked_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_block UNIQUE (blocker_id, blocked_id)
);

-- Создание таблицы сообщений
CREATE TABLE IF NOT EXISTS messages (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    match_id UUID NOT NULL REFERENCES matches(id) ON DELETE CASCADE,
    sender_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    text TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Создание индекса для быстрого поиска сообщений
CREATE INDEX idx_messages_match_id ON messages(match_id);

-- Создание таблицы уведомлений
CREATE TABLE IF NOT EXISTS notifications (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type VARCHAR(50) NOT NULL, -- new_match, new_message, etc.
    content TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    reference_id UUID, -- Can reference a match_id, message_id, etc.
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Создание индекса для быстрого поиска уведомлений
CREATE INDEX idx_notifications_user_id ON notifications(user_id);

-- Создание таблицы настроек
CREATE TABLE IF NOT EXISTS settings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    notifications_enabled BOOLEAN DEFAULT TRUE,
    show_online_status BOOLEAN DEFAULT TRUE,
    visibility BOOLEAN DEFAULT TRUE, -- whether profile is visible in search
    theme VARCHAR(20) DEFAULT 'light',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_user_settings UNIQUE (user_id)
);

-- Создание таблицы просмотров профиля
CREATE TABLE IF NOT EXISTS profile_views (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    viewer_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    viewed_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    view_count INTEGER DEFAULT 1,
    last_viewed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_view UNIQUE (viewer_id, viewed_id)
);

-- Создание таблицы для премиум-пользователей
CREATE TABLE IF NOT EXISTS premium_features (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    is_premium BOOLEAN DEFAULT FALSE,
    superlike_quota INTEGER DEFAULT 1,
    spotlight_quota INTEGER DEFAULT 0,
    see_likes BOOLEAN DEFAULT FALSE, -- Can see who liked their profile
    unlimited_likes BOOLEAN DEFAULT FALSE,
    expires_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_user_premium UNIQUE (user_id)
);

-- Функция для обновления поля updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Триггеры для обновления поля updated_at
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER update_profiles_updated_at
BEFORE UPDATE ON profiles
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER update_search_preferences_updated_at
BEFORE UPDATE ON search_preferences
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER update_settings_updated_at
BEFORE UPDATE ON settings
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER update_premium_features_updated_at
BEFORE UPDATE ON premium_features
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

-- Индекс для поиска пользователей по Telegram ID (часто используемый запрос)
CREATE INDEX IF NOT EXISTS idx_users_telegram_id ON users(telegram_id);

-- Индекс для статуса профиля (активный/неактивный, завершенный/незавершенный)
CREATE INDEX IF NOT EXISTS idx_profiles_is_completed ON profiles(is_completed);

-- Представление для статистики
CREATE OR REPLACE VIEW user_statistics AS
SELECT 
    u.id,
    u.telegram_id,
    (SELECT COUNT(*) FROM swipes WHERE user_id = u.id AND action = 'like') AS likes_given,
    (SELECT COUNT(*) FROM swipes WHERE user_id = u.id AND action = 'dislike') AS dislikes_given,
    (SELECT COUNT(*) FROM swipes WHERE target_id = u.id AND action = 'like') AS likes_received,
    (SELECT COUNT(*) FROM matches WHERE user_id_1 = u.id OR user_id_2 = u.id) AS matches_count,
    (SELECT COUNT(*) FROM messages WHERE sender_id = u.id) AS messages_sent,
    (SELECT COUNT(*) FROM profile_views WHERE viewed_id = u.id) AS profile_views
FROM users u;

-- Функция для создания матча при взаимных лайках
CREATE OR REPLACE FUNCTION create_match_on_mutual_like()
RETURNS TRIGGER AS $$
DECLARE
    reverse_like_exists BOOLEAN;
BEGIN
    -- Check if there is a reverse like
    SELECT EXISTS (
        SELECT 1 
        FROM swipes 
        WHERE user_id = NEW.target_id 
        AND target_id = NEW.user_id 
        AND action = 'like'
    ) INTO reverse_like_exists;
    
    -- If there is a reverse like, create a match
    IF reverse_like_exists AND NEW.action = 'like' THEN
        INSERT INTO matches (user_id_1, user_id_2)
        VALUES (
            LEAST(NEW.user_id, NEW.target_id),
            GREATEST(NEW.user_id, NEW.target_id)
        )
        ON CONFLICT (user_id_1, user_id_2) DO NOTHING;
        
        -- Create notifications for both users
        INSERT INTO notifications (user_id, type, content, reference_id)
        VALUES (
            NEW.user_id,
            'new_match',
            'У вас новый матч!',
            (SELECT id FROM matches WHERE 
                (user_id_1 = LEAST(NEW.user_id, NEW.target_id) AND user_id_2 = GREATEST(NEW.user_id, NEW.target_id))
            )
        );
        
        INSERT INTO notifications (user_id, type, content, reference_id)
        VALUES (
            NEW.target_id,
            'new_match',
            'У вас новый матч!',
            (SELECT id FROM matches WHERE 
                (user_id_1 = LEAST(NEW.user_id, NEW.target_id) AND user_id_2 = GREATEST(NEW.user_id, NEW.target_id))
            )
        );
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Триггер для создания матча при взаимных лайках
CREATE TRIGGER create_match_trigger
AFTER INSERT ON swipes
FOR EACH ROW
EXECUTE FUNCTION create_match_on_mutual_like();

-- Функция для создания уведомления о новом сообщении
CREATE OR REPLACE FUNCTION notify_new_message()
RETURNS TRIGGER AS $$
DECLARE
    recipient_id UUID;
    match_record RECORD;
BEGIN
    -- Get the match record
    SELECT * INTO match_record FROM matches WHERE id = NEW.match_id;
    
    -- Determine the recipient
    IF match_record.user_id_1 = NEW.sender_id THEN
        recipient_id := match_record.user_id_2;
    ELSE
        recipient_id := match_record.user_id_1;
    END IF;
    
    -- Create notification
    INSERT INTO notifications (user_id, type, content, reference_id)
    VALUES (
        recipient_id,
        'new_message',
        'У вас новое сообщение!',
        NEW.id
    );
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Триггер для создания уведомления о новом сообщении
CREATE TRIGGER notify_new_message_trigger
AFTER INSERT ON messages
FOR EACH ROW
EXECUTE FUNCTION notify_new_message();

-- Функция для обновления времени последней активности пользователя
CREATE OR REPLACE FUNCTION update_last_active()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE profiles
    SET last_active = CURRENT_TIMESTAMP
    WHERE user_id = NEW.user_id;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Триггер для обновления времени последней активности при свайпах
CREATE TRIGGER update_last_active_on_swipe
AFTER INSERT ON swipes
FOR EACH ROW
EXECUTE FUNCTION update_last_active();

-- Триггер для обновления времени последней активности при отправке сообщений
CREATE TRIGGER update_last_active_on_message
AFTER INSERT ON messages
FOR EACH ROW
EXECUTE FUNCTION update_last_active();

-- Создание функции для автоматического создания профиля при создании пользователя
CREATE OR REPLACE FUNCTION create_initial_profile()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO profiles (user_id, name, age, gender)
    VALUES (NEW.id, COALESCE(NEW.first_name, 'User'), 18, 'other');
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Триггер для автоматического создания профиля при создании пользователя
CREATE TRIGGER create_profile_trigger
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION create_initial_profile();

-- Индексы для оптимизации частых запросов
CREATE INDEX IF NOT EXISTS idx_profiles_last_active ON profiles(last_active);
CREATE INDEX IF NOT EXISTS idx_swipes_action ON swipes(action);
CREATE INDEX IF NOT EXISTS idx_notifications_is_read ON notifications(is_read);
CREATE INDEX IF NOT EXISTS idx_messages_is_read ON messages(is_read);

-- Добавление ограничений для проверки возраста
ALTER TABLE profiles DROP CONSTRAINT IF EXISTS age_check;
ALTER TABLE profiles ADD CONSTRAINT age_check CHECK (age >= 18 AND age <= 99);

-- Добавление ограничений для предпочтений
ALTER TABLE search_preferences DROP CONSTRAINT IF EXISTS age_range_check;
ALTER TABLE search_preferences ADD CONSTRAINT age_range_check CHECK (age_min >= 18 AND age_max >= age_min AND age_max <= 99);

-- Комментарии к таблицам для документации
COMMENT ON TABLE users IS 'Таблица пользователей Telegram';
COMMENT ON TABLE profiles IS 'Профили пользователей для знакомств';
COMMENT ON TABLE search_preferences IS 'Предпочтения поиска пользователей';
COMMENT ON TABLE swipes IS 'История лайков/дислайков';
COMMENT ON TABLE matches IS 'Совпадения (матчи) между пользователями';
COMMENT ON TABLE messages IS 'Сообщения между пользователями';
COMMENT ON TABLE notifications IS 'Уведомления для пользователей';
COMMENT ON TABLE settings IS 'Настройки пользователей';
COMMENT ON TABLE profile_views IS 'История просмотров профилей';
COMMENT ON TABLE premium_features IS 'Премиум-функции для пользователей';

-- Представление для быстрого получения активных матчей с информацией о пользователе
CREATE OR REPLACE VIEW active_matches AS
SELECT 
    m.id AS match_id,
    m.created_at AS match_date,
    CASE 
        WHEN m.user_id_1 = u1.id THEN u2.id 
        ELSE u1.id 
    END AS partner_id,
    CASE 
        WHEN m.user_id_1 = u1.id THEN u2.telegram_id 
        ELSE u1.telegram_id 
    END AS partner_telegram_id,
    CASE 
        WHEN m.user_id_1 = u1.id THEN p2.name 
        ELSE p1.name 
    END AS partner_name,
    CASE 
        WHEN m.user_id_1 = u1.id THEN p2.photos[1] 
        ELSE p1.photos[1] 
    END AS partner_photo,
    (SELECT COUNT(*) FROM messages WHERE match_id = m.id) AS message_count,
    (SELECT COUNT(*) FROM messages WHERE match_id = m.id AND is_read = false AND sender_id != u1.id) AS unread_count,
    m.is_active
FROM matches m
JOIN users u1 ON m.user_id_1 = u1.id
JOIN users u2 ON m.user_id_2 = u2.id
JOIN profiles p1 ON u1.id = p1.user_id
JOIN profiles p2 ON u2.id = p2.user_id
WHERE m.is_active = true;