Skip to content

gdenis82/video-analytics-bot

BranchesTags

Repository files navigation

🤖 Video Analytics Bot

Telegram-бот для аналитики видео по запросам на естественном языке.

⚡ Быстрый старт

1. Клонирование и настройка

git clone <repo> && cd video-analytics-bot
cp .env.example .env

Отредактируйте .env:

TELEGRAM_BOT_TOKEN=your_bot_token_from_botfather
DATABASE_URL=postgresql://postgres:postgres@localhost:5432/video_analytics
OPENAI_API_KEY=sk-...  # обязательно для работы LLM
LLM_MODEL=gpt-4o-mini 
LOG_LEVEL=info

2. Запуск через Docker (рекомендуется)

# Запуск PostgreSQL и бота
docker-compose up -d

# Проверка что сервисы запущены
docker-compose ps

# Применение миграций
docker-compose exec bot python -m scripts.run_migrations

# Загрузка данных (после скачивания JSON)
# Скачайте файл с https://drive.google.com/file/d/1BZOYxhDmMGJrSbPdcQgQjh0HRzN1YZt5/view
# Сохраните как data/videos.json
docker-compose exec bot python -m scripts.import_json --path data/videos.json

Проверка логов:

docker-compose logs -f bot

3. Локальный запуск (без Docker)

# Установка зависимостей
pip install -e ".[dev]"

# Запуск PostgreSQL (локально)
# Убедитесь, что PostgreSQL запущен и создайте базу:
# createdb video_analytics

# Применение миграций
python -m scripts.run_migrations

# Загрузка данных
python -m scripts.import_json --path data/videos.json

# Запуск бота
python -m src.main

4. Проверка

  1. Откройте Telegram
  2. Найдите вашего бота (по токену из @BotFather)
  3. Отправьте тестовые запросы:
    • «Сколько всего видео есть в системе?»
    • «Сколько видео набрало больше 100000 просмотров?»
    • «На сколько просмотров в сумме выросли все видео 28 ноября 2025?»

🏗 Архитектура

flowchart LR
    A[Telegram Client] <--> B[Bot + NL Engine]
    B <--> C[PostgreSQL]
Loading

Ключевые решения

  1. LLM SQL Generation: Все запросы преобразуются в SQL через LLM (OpenAI GPT-4o-mini)

    • LLM генерирует безопасные SELECT запросы на основе естественного языка
    • Многоуровневая валидация SQL (только SELECT, блокировка опасных операций)
    • Автоматическая конвертация дат в формат YYYY-MM-DD
    • Параметризованные запросы для защиты от SQL инъекций

  2. 5 типов запросов (из технического задания):

    • count_videos_total — общее количество видео
    • count_videos_by_creator_date — видео креатора за период
    • count_videos_by_views — видео с просмотрами > порога (на дату или за период)
    • sum_delta_by_date — суммарный прирост метрики за дату/период
    • count_distinct_active — уникальные видео с активностью за дату/период

  3. Безопасность:

    • Валидация SQL: только SELECT, блокировка INSERT/UPDATE/DELETE/DROP
    • Параметризованные SQL-запросы (защита от инъекций)
    • Read-only доступ к БД

📚 Структура проекта

video-analytics-bot/
├── config/              # Настройки (Pydantic Settings)
│   ├── __init__.py
│   └── settings.py
│
├── src/
│   ├── __init__.py
│   ├── main.py          # Точка входа
│   │
│   ├── bot/             # Telegram layer (aiogram v3)
│   │   ├── __init__.py
│   │   ├── factory.py   # create_bot(dp, config)
│   │   ├── handlers.py  # MessageHandler (LLM SQL mode)
│   │   └── middlewares.py
│   │
│   ├── nl_engine/       # Natural Language → SQL
│   │   ├── __init__.py
│   │   ├── llm_client.py    # OpenAI async клиент
│   │   ├── llm_sql.py       # Генерация SQL + валидация
│   │   └── prompts.py       # LLM_SQL_PROMPT (схема БД)
│   │
│   ├── db/              # Data layer
│   │   ├── __init__.py
│   │   ├── pool.py      # asyncpg pool
│   │   ├── repository.py  # 5 методов + execute_custom_query
│   │   ├── models.py    # SQLAlchemy Core
│   │   └── migrations/
│   │
│   └── core/
│       ├── __init__.py
│       ├── exceptions.py
│       └── security.py      # validate_sql_query()
│
├── scripts/
│   ├── __init__.py
│   ├── import_json.py   # CLI: загрузка JSON
│   └── run_migrations.py # Alembic wrapper
│
├── tests/
│   ├── conftest.py
│   └── unit/
│       ├── test_llm_sql.py      # Тесты генерации SQL
│       ├── test_nl_parser.py    # Тесты парсинга
│       └── test_repository.py   # Тесты репозитория
│
├── data/
│   └── videos.json      # Исходные данные
│
├── docker-compose.yml
├── Dockerfile
├── pyproject.toml
├── alembic.ini
├── .env.example
└── README.md

🧪 Тестирование

Все тесты используют pytest с плагином pytest-anyio для асинхронных тестов.

# Запуск всех тестов
pytest -v

# Запуск конкретных категорий
pytest tests/unit/ -v

# Запуск отдельного теста
pytest tests/unit/test_llm_sql.py -v

Структура тестов

tests/
├── conftest.py              # Общие фикстуры (mock_llm_client, mock_pool)
└── unit/
    ├── test_llm_sql.py      # Тесты генерации SQL (12 тестов)
    ├── test_nl_parser.py    # Тесты парсинга запросов (8 тестов)
    └── test_repository.py   # Тесты репозитория (15 тестов)

Покрытие тестами

Компонент Тесты Описание
LLM SQL Generation test_llm_sql.py Генерация SQL, валидация безопасности, конвертация дат
NL Parser test_nl_parser.py Парсинг естественного языка, обработка ошибок LLM
Repository test_repository.py Все 5 методов + execute_custom_query
Security test_llm_sql.py Блокировка INSERT/UPDATE/DELETE/DROP, SQL-инъекции

Примеры тестов

Генерация SQL:

async def test_generate_count_all_videos(mock_llm_client):
    mock_llm_client.extract_params = AsyncMock(return_value={
        "sql": "SELECT COUNT(*) FROM videos",
        "params": [],
        "explanation": "Total video count",
    })
    result = await generate_sql_query("Сколько всего видео?", mock_llm_client)
    assert result.sql == "SELECT COUNT(*) FROM videos"

Безопасность:

async def test_rejects_unsafe_sql_insert(mock_llm_client):
    mock_llm_client.extract_params = AsyncMock(return_value={
        "sql": "INSERT INTO videos VALUES (...)",
    })
    with pytest.raises(NLProcessingError):
        await generate_sql_query("Добавь видео", mock_llm_client)

CI/CD

Для непрерывной интеграции рекомендуется запускать тесты при каждом commit:

# .github/workflows/tests.yml (пример)
name: Tests
on: [push, pull_request]
jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - uses: actions/setup-python@v4
        with:
          python-version: "3.12"
      - run: pip install -e ".[dev]"
      - run: pytest -v

🔍 Поддерживаемые запросы

Запрос Пример SQL операция
Total count «Сколько всего видео есть в системе?» SELECT COUNT(*) FROM videos
By creator + date «Сколько видео у креатора с id ... вышло с 1 ноября по 5 ноября?» COUNT(*) WHERE creator_id = $1 AND video_created_at BETWEEN $2 AND $3
By views threshold «Сколько видео набрало больше 100 000 просмотров?» COUNT(*) WHERE views_count > $1
By views + date «Сколько видео набрало больше 100 000 просмотров 26 ноября?» COUNT(*) FROM video_snapshots WHERE DATE = $1 AND views_count > $2
By views + period «Сколько видео набрало > 100 000 просмотров с 1 по 26 ноября?» COUNT(DISTINCT video_id) WHERE DATE BETWEEN $1 AND $2 AND views_count > $3
Sum delta by date «На сколько просмотров в сумме выросли все видео 28 ноября 2025?» SELECT COALESCE(SUM(delta_views_count), 0) WHERE DATE = $1
Sum delta + period «На сколько просмотров выросли видео с 26 по 28 ноября?» SELECT COALESCE(SUM(delta_views_count), 0) WHERE DATE BETWEEN $1 AND $2
Distinct active «Сколько разных видео получали просмотры 27 ноября 2025?» SELECT COUNT(DISTINCT video_id) WHERE DATE = $1 AND delta_views_count > 0
Distinct active + period «Сколько видео получали просмотры с 27 по 28 ноября?» SELECT COUNT(DISTINCT video_id) WHERE DATE BETWEEN $1 AND $2 AND delta_views_count > 0

📝 Исходное ТЗ

🚀 Проверка через @rlt_test_checker_bot

/check @yourbotnickname https://github.com/yourrepo

Важно: Бот должен быть запущен и доступен в Telegram на момент проверки.

Чек-лист перед проверкой

  • TELEGRAM_BOT_TOKEN установлен в .env
  • Миграции применены (python -m scripts.run_migrations)
  • Данные загружены (python -m scripts.import_json --path data/videos.json)
  • Бот отвечает на тестовые запросы в Telegram
  • Логи чистые (docker-compose logs bot)

🛠 Технологии

Компонент Технология Версия
Язык Python 3.12+
Telegram Bot aiogram v3
База данных PostgreSQL 15+
Async DB Driver asyncpg 0.29+
LLM OpenAI API gpt-3.5-turbo
Валидация Pydantic v2
Миграции Alembic 1.13+
Логирование structlog читабельный формат
Контейнеризация Docker —

📊 Логирование

Бот использует человекочитаемый формат логов с цветовой подсветкой:

2026-02-18 18:45:30 [info     ] query_parsed                 query='Сколько всего видео?'
2026-02-18 18:45:31 [info     ] llm_token_usage              input_tokens=1250 output_tokens=85 total_tokens=1335 model=gpt-4o-mini
2026-02-18 18:45:31 [info     ] llm_sql_mode_executing       sql='SELECT COUNT(*) FROM videos'
2026-02-18 18:45:32 [info     ] response_sent                result=1234

Мониторинг токенов

Каждый запрос к LLM логирует использование токенов:

  • input_tokens — токены входного запроса (промпт + вопрос)
  • output_tokens — токены ответа LLM
  • total_tokens — общее количество

❓ Troubleshooting

Бот не запускается

# Проверка переменных окружения
docker-compose config

# Проверка логов
docker-compose logs bot

Ошибка подключения к БД

# Проверка доступности PostgreSQL
docker-compose exec postgres pg_isready -U postgres

# Проверка DATABASE_URL
docker-compose exec bot printenv DATABASE_URL

Миграции не применяются

# Сброс и повторное применение
docker-compose exec bot python -m scripts.run_migrations downgrade base
docker-compose exec bot python -m scripts.run_migrations upgrade head

Ошибка импорта JSON

# Проверка формата JSON
python -c "import json; json.load(open('data/videos.json'))"

# Проверка количества записей
python -c "import json; d=json.load(open('data/videos.json')); print(len(d.get('videos', d)))"

📄 Лицензия

Проект создан в рамках тестового задания.

About

No description, website, or topics provided.

Resources

Readme
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

 
 
 

Contributors

Languages