405 lines
15 KiB
Python
405 lines
15 KiB
Python
"""Репозиторий последовательностей сообщений (Блок 4).
|
||
|
||
Последовательность — несколько сообщений по порядку; у каждого может быть
|
||
картинка из общего volume и свои inline-кнопки. Бот отправляет их при входе
|
||
участника (пока используется для приветствия).
|
||
"""
|
||
|
||
from __future__ import annotations
|
||
|
||
from typing import Any
|
||
|
||
from psycopg.types.json import Jsonb
|
||
|
||
from common.db.pool import get_pool
|
||
|
||
#: Максимальный размер загружаемой картинки.
|
||
MAX_IMAGE_BYTES = 5 * 1024 * 1024
|
||
|
||
|
||
async def list_sequences() -> list[dict[str, Any]]:
|
||
"""Список последовательностей с количеством сообщений."""
|
||
async with get_pool().connection() as conn:
|
||
cur = await conn.execute(
|
||
"""
|
||
SELECT s.key, s.title, s.description, s.updated_at, s.updated_by,
|
||
count(i.id) AS item_count
|
||
FROM bot.message_sequences AS s
|
||
LEFT JOIN bot.message_sequence_items AS i ON i.sequence_key = s.key
|
||
GROUP BY s.key, s.title, s.description, s.updated_at, s.updated_by
|
||
ORDER BY s.key
|
||
"""
|
||
)
|
||
return await cur.fetchall()
|
||
|
||
|
||
async def get_sequence(key: str) -> dict[str, Any] | None:
|
||
async with get_pool().connection() as conn:
|
||
cur = await conn.execute(
|
||
"SELECT key, title, description, updated_at, updated_by FROM bot.message_sequences WHERE key = %s",
|
||
(key,),
|
||
)
|
||
return await cur.fetchone()
|
||
|
||
|
||
async def get_items(key: str) -> list[dict[str, Any]]:
|
||
"""Сообщения последовательности с кнопками, по порядку."""
|
||
async with get_pool().connection() as conn:
|
||
cur = await conn.execute(
|
||
"""
|
||
SELECT id, position, text, enabled, image_name
|
||
FROM bot.message_sequence_items
|
||
WHERE sequence_key = %s
|
||
ORDER BY position
|
||
""",
|
||
(key,),
|
||
)
|
||
items = await cur.fetchall()
|
||
|
||
cur = await conn.execute(
|
||
"""
|
||
SELECT b.item_id, b.position, b.text, b.url
|
||
FROM bot.message_sequence_buttons AS b
|
||
JOIN bot.message_sequence_items AS i ON i.id = b.item_id
|
||
WHERE i.sequence_key = %s
|
||
ORDER BY b.item_id, b.position
|
||
""",
|
||
(key,),
|
||
)
|
||
buttons = await cur.fetchall()
|
||
|
||
by_item: dict[int, list[dict[str, Any]]] = {}
|
||
for button in buttons:
|
||
by_item.setdefault(int(button["item_id"]), []).append({"text": button["text"], "url": button["url"]})
|
||
for item in items:
|
||
item["buttons"] = by_item.get(int(item["id"]), [])
|
||
return items
|
||
|
||
|
||
async def _snapshot(conn: Any, key: str, editor_id: int | None) -> None:
|
||
"""Сохранить снимок последовательности в историю изменений."""
|
||
cur = await conn.execute(
|
||
"SELECT key, title, description FROM bot.message_sequences WHERE key = %s",
|
||
(key,),
|
||
)
|
||
sequence = await cur.fetchone()
|
||
if sequence is None:
|
||
return
|
||
|
||
cur = await conn.execute(
|
||
"""
|
||
SELECT id, position, text, enabled, image_name
|
||
FROM bot.message_sequence_items
|
||
WHERE sequence_key = %s
|
||
ORDER BY position
|
||
""",
|
||
(key,),
|
||
)
|
||
items = await cur.fetchall()
|
||
|
||
cur = await conn.execute(
|
||
"""
|
||
SELECT b.item_id, b.position, b.text, b.url
|
||
FROM bot.message_sequence_buttons AS b
|
||
JOIN bot.message_sequence_items AS i ON i.id = b.item_id
|
||
WHERE i.sequence_key = %s
|
||
ORDER BY b.item_id, b.position
|
||
""",
|
||
(key,),
|
||
)
|
||
buttons = await cur.fetchall()
|
||
|
||
by_item: dict[int, list[dict[str, Any]]] = {}
|
||
for button in buttons:
|
||
by_item.setdefault(int(button["item_id"]), []).append(
|
||
{"position": button["position"], "text": button["text"], "url": button["url"]}
|
||
)
|
||
|
||
snapshot = {
|
||
"sequence": sequence,
|
||
"items": [{**item, "buttons": by_item.get(int(item["id"]), [])} for item in items],
|
||
}
|
||
await conn.execute(
|
||
"""
|
||
INSERT INTO bot.message_sequence_history (sequence_key, snapshot, editor_id)
|
||
VALUES (%s, %s, %s)
|
||
""",
|
||
(key, Jsonb(snapshot), editor_id),
|
||
)
|
||
|
||
|
||
async def create_sequence(
|
||
key: str,
|
||
title: str,
|
||
description: str,
|
||
*,
|
||
editor_id: int | None = None,
|
||
) -> bool:
|
||
async with get_pool().connection() as conn:
|
||
cur = await conn.execute("SELECT 1 FROM bot.message_sequences WHERE key = %s", (key,))
|
||
if await cur.fetchone() is not None:
|
||
return False
|
||
await conn.execute(
|
||
"""
|
||
INSERT INTO bot.message_sequences (key, title, description, updated_by)
|
||
VALUES (%s, %s, %s, %s)
|
||
""",
|
||
(key, title, description, editor_id),
|
||
)
|
||
await _snapshot(conn, key, editor_id)
|
||
return True
|
||
|
||
|
||
async def update_sequence(
|
||
key: str,
|
||
title: str,
|
||
description: str,
|
||
*,
|
||
editor_id: int | None = None,
|
||
) -> bool:
|
||
async with get_pool().connection() as conn:
|
||
cur = await conn.execute(
|
||
"""
|
||
UPDATE bot.message_sequences
|
||
SET title = %s, description = %s, updated_at = now(), updated_by = %s
|
||
WHERE key = %s
|
||
""",
|
||
(title, description, editor_id, key),
|
||
)
|
||
if not cur.rowcount:
|
||
return False
|
||
await _snapshot(conn, key, editor_id)
|
||
return True
|
||
|
||
|
||
async def add_item(key: str, *, editor_id: int | None = None) -> int | None:
|
||
async with get_pool().connection() as conn:
|
||
cur = await conn.execute("SELECT 1 FROM bot.message_sequences WHERE key = %s", (key,))
|
||
if await cur.fetchone() is None:
|
||
return None
|
||
cur = await conn.execute(
|
||
"SELECT coalesce(max(position), -1) + 1 AS next FROM bot.message_sequence_items WHERE sequence_key = %s",
|
||
(key,),
|
||
)
|
||
row = await cur.fetchone()
|
||
position = int(row["next"])
|
||
cur = await conn.execute(
|
||
"""
|
||
INSERT INTO bot.message_sequence_items (sequence_key, position, text, updated_by)
|
||
VALUES (%s, %s, '', %s)
|
||
RETURNING id
|
||
""",
|
||
(key, position, editor_id),
|
||
)
|
||
inserted = await cur.fetchone()
|
||
await _snapshot(conn, key, editor_id)
|
||
return int(inserted["id"])
|
||
|
||
|
||
async def update_item(
|
||
item_id: int,
|
||
text: str,
|
||
enabled: bool,
|
||
buttons: list[tuple[str, str]],
|
||
*,
|
||
image_name: str | None = None,
|
||
update_image: bool = False,
|
||
editor_id: int | None = None,
|
||
) -> tuple[str, str | None] | None:
|
||
"""Обновить сообщение и его кнопки. Возвращает (ключ, старое имя картинки)."""
|
||
async with get_pool().connection() as conn:
|
||
cur = await conn.execute(
|
||
"SELECT sequence_key, image_name FROM bot.message_sequence_items WHERE id = %s FOR UPDATE",
|
||
(item_id,),
|
||
)
|
||
item = await cur.fetchone()
|
||
if item is None:
|
||
return None
|
||
|
||
key = str(item["sequence_key"])
|
||
old_image = item["image_name"]
|
||
|
||
if update_image:
|
||
await conn.execute(
|
||
"""
|
||
UPDATE bot.message_sequence_items
|
||
SET text = %s, enabled = %s, image_name = %s, updated_at = now(), updated_by = %s
|
||
WHERE id = %s
|
||
""",
|
||
(text, enabled, image_name, editor_id, item_id),
|
||
)
|
||
else:
|
||
await conn.execute(
|
||
"""
|
||
UPDATE bot.message_sequence_items
|
||
SET text = %s, enabled = %s, updated_at = now(), updated_by = %s
|
||
WHERE id = %s
|
||
""",
|
||
(text, enabled, editor_id, item_id),
|
||
)
|
||
|
||
await conn.execute("DELETE FROM bot.message_sequence_buttons WHERE item_id = %s", (item_id,))
|
||
for position, (button_text, url) in enumerate(buttons):
|
||
await conn.execute(
|
||
"""
|
||
INSERT INTO bot.message_sequence_buttons (item_id, position, text, url)
|
||
VALUES (%s, %s, %s, %s)
|
||
""",
|
||
(item_id, position, button_text, url),
|
||
)
|
||
|
||
await _snapshot(conn, key, editor_id)
|
||
return key, old_image
|
||
|
||
|
||
async def save_items(
|
||
key: str,
|
||
items: list[dict[str, Any]],
|
||
*,
|
||
editor_id: int | None = None,
|
||
) -> list[str] | None:
|
||
"""Сохранить весь набор сообщений одной транзакцией.
|
||
|
||
``items`` — по порядку: ``id`` (None для нового), ``text``, ``enabled``,
|
||
``image_name``, ``buttons`` (список пар текст/URL). Возвращает имена
|
||
картинок, которые больше не используются (их можно удалить с диска).
|
||
"""
|
||
async with get_pool().connection() as conn:
|
||
cur = await conn.execute(
|
||
"SELECT 1 FROM bot.message_sequences WHERE key = %s",
|
||
(key,),
|
||
)
|
||
if await cur.fetchone() is None:
|
||
return None
|
||
|
||
cur = await conn.execute(
|
||
"SELECT id, image_name FROM bot.message_sequence_items WHERE sequence_key = %s",
|
||
(key,),
|
||
)
|
||
existing = {int(row["id"]): row["image_name"] for row in await cur.fetchall()}
|
||
submitted_ids = {int(item["id"]) for item in items if item.get("id")}
|
||
|
||
removed_images: list[str] = []
|
||
for item_id, image_name in existing.items():
|
||
if item_id not in submitted_ids:
|
||
await conn.execute("DELETE FROM bot.message_sequence_items WHERE id = %s", (item_id,))
|
||
if image_name:
|
||
removed_images.append(str(image_name))
|
||
|
||
# Освобождаем позиции, чтобы не нарушить UNIQUE(sequence_key, position).
|
||
await conn.execute(
|
||
"UPDATE bot.message_sequence_items SET position = -id WHERE sequence_key = %s",
|
||
(key,),
|
||
)
|
||
|
||
for position, item in enumerate(items):
|
||
item_id = int(item["id"]) if item.get("id") else None
|
||
if item_id is not None and item_id in existing:
|
||
old_image = existing[item_id]
|
||
if old_image and old_image != item.get("image_name"):
|
||
removed_images.append(str(old_image))
|
||
await conn.execute(
|
||
"""
|
||
UPDATE bot.message_sequence_items
|
||
SET position = %s, text = %s, enabled = %s, image_name = %s,
|
||
updated_at = now(), updated_by = %s
|
||
WHERE id = %s
|
||
""",
|
||
(position, item["text"], item["enabled"], item.get("image_name"), editor_id, item_id),
|
||
)
|
||
else:
|
||
cur = await conn.execute(
|
||
"""
|
||
INSERT INTO bot.message_sequence_items
|
||
(sequence_key, position, text, enabled, image_name, updated_by)
|
||
VALUES (%s, %s, %s, %s, %s, %s)
|
||
RETURNING id
|
||
""",
|
||
(key, position, item["text"], item["enabled"], item.get("image_name"), editor_id),
|
||
)
|
||
inserted = await cur.fetchone()
|
||
item_id = int(inserted["id"])
|
||
|
||
await conn.execute("DELETE FROM bot.message_sequence_buttons WHERE item_id = %s", (item_id,))
|
||
for button_position, (button_text, url) in enumerate(item.get("buttons") or []):
|
||
await conn.execute(
|
||
"""
|
||
INSERT INTO bot.message_sequence_buttons (item_id, position, text, url)
|
||
VALUES (%s, %s, %s, %s)
|
||
""",
|
||
(item_id, button_position, button_text, url),
|
||
)
|
||
|
||
await _snapshot(conn, key, editor_id)
|
||
return removed_images
|
||
|
||
|
||
async def delete_item(item_id: int, *, editor_id: int | None = None) -> str | None:
|
||
async with get_pool().connection() as conn:
|
||
cur = await conn.execute(
|
||
"SELECT sequence_key FROM bot.message_sequence_items WHERE id = %s FOR UPDATE",
|
||
(item_id,),
|
||
)
|
||
item = await cur.fetchone()
|
||
if item is None:
|
||
return None
|
||
key = str(item["sequence_key"])
|
||
await conn.execute("DELETE FROM bot.message_sequence_items WHERE id = %s", (item_id,))
|
||
await _snapshot(conn, key, editor_id)
|
||
return key
|
||
|
||
|
||
async def move_item(item_id: int, direction: str, *, editor_id: int | None = None) -> str | None:
|
||
"""Поменять сообщение с соседним (``up``/``down``)."""
|
||
delta = -1 if direction == "up" else 1
|
||
async with get_pool().connection() as conn:
|
||
cur = await conn.execute(
|
||
"SELECT sequence_key, position FROM bot.message_sequence_items WHERE id = %s FOR UPDATE",
|
||
(item_id,),
|
||
)
|
||
item = await cur.fetchone()
|
||
if item is None:
|
||
return None
|
||
key = str(item["sequence_key"])
|
||
position = int(item["position"])
|
||
|
||
cur = await conn.execute(
|
||
"""
|
||
SELECT id, position FROM bot.message_sequence_items
|
||
WHERE sequence_key = %s AND position = %s
|
||
FOR UPDATE
|
||
""",
|
||
(key, position + delta),
|
||
)
|
||
neighbour = await cur.fetchone()
|
||
if neighbour is None:
|
||
return key
|
||
|
||
# Через -1, чтобы не нарушить UNIQUE(sequence_key, position).
|
||
await conn.execute("UPDATE bot.message_sequence_items SET position = -1 WHERE id = %s", (item_id,))
|
||
await conn.execute(
|
||
"UPDATE bot.message_sequence_items SET position = %s WHERE id = %s",
|
||
(position, neighbour["id"]),
|
||
)
|
||
await conn.execute(
|
||
"UPDATE bot.message_sequence_items SET position = %s WHERE id = %s",
|
||
(position + delta, item_id),
|
||
)
|
||
await _snapshot(conn, key, editor_id)
|
||
return key
|
||
|
||
|
||
async def history(key: str, *, limit: int = 10) -> list[dict[str, Any]]:
|
||
async with get_pool().connection() as conn:
|
||
cur = await conn.execute(
|
||
"""
|
||
SELECT id, snapshot, editor_id, changed_at
|
||
FROM bot.message_sequence_history
|
||
WHERE sequence_key = %s
|
||
ORDER BY changed_at DESC
|
||
LIMIT %s
|
||
""",
|
||
(key, limit),
|
||
)
|
||
return await cur.fetchall()
|