from __future__ import annotations import sqlite3 from dataclasses import dataclass from datetime import datetime, timezone UTC = timezone.utc from pathlib import Path from typing import Literal MessageRole = Literal["user", "assistant"] @dataclass(frozen=True) class ChatSettings: chat_id: int model: str custom_soul: str | None @dataclass(frozen=True) class MessageRecord: role: MessageRole user_id: int | None user_name: str | None text: str created_at: str class SQLiteStorage: def __init__(self, path: Path) -> None: self.path = path def initialize(self) -> None: self.path.parent.mkdir(parents=True, exist_ok=True) with self._connect() as connection: connection.executescript( """ CREATE TABLE IF NOT EXISTS chat_settings ( chat_id INTEGER PRIMARY KEY, model TEXT NOT NULL, custom_soul TEXT, updated_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS messages ( id INTEGER PRIMARY KEY AUTOINCREMENT, chat_id INTEGER NOT NULL, telegram_message_id INTEGER, role TEXT NOT NULL CHECK (role IN ('user', 'assistant')), user_id INTEGER, user_name TEXT, text TEXT NOT NULL, created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_messages_chat_id_id ON messages(chat_id, id); """ ) def get_chat_settings(self, chat_id: int, default_model: str) -> ChatSettings: with self._connect() as connection: row = connection.execute( "SELECT chat_id, model, custom_soul FROM chat_settings WHERE chat_id = ?", (chat_id,), ).fetchone() if row is None: now = self._now() connection.execute( """ INSERT INTO chat_settings(chat_id, model, custom_soul, updated_at) VALUES (?, ?, NULL, ?) """, (chat_id, default_model, now), ) return ChatSettings(chat_id=chat_id, model=default_model, custom_soul=None) return ChatSettings( chat_id=int(row["chat_id"]), model=str(row["model"]), custom_soul=row["custom_soul"], ) def set_model(self, chat_id: int, model: str, default_model: str) -> None: settings = self.get_chat_settings(chat_id, default_model) with self._connect() as connection: connection.execute( """ UPDATE chat_settings SET model = ?, custom_soul = ?, updated_at = ? WHERE chat_id = ? """, (model, settings.custom_soul, self._now(), chat_id), ) def set_custom_soul(self, chat_id: int, soul: str, default_model: str) -> None: settings = self.get_chat_settings(chat_id, default_model) with self._connect() as connection: connection.execute( """ UPDATE chat_settings SET model = ?, custom_soul = ?, updated_at = ? WHERE chat_id = ? """, (settings.model, soul, self._now(), chat_id), ) def reset_custom_soul(self, chat_id: int, default_model: str) -> None: settings = self.get_chat_settings(chat_id, default_model) with self._connect() as connection: connection.execute( """ UPDATE chat_settings SET model = ?, custom_soul = NULL, updated_at = ? WHERE chat_id = ? """, (settings.model, self._now(), chat_id), ) def append_message( self, chat_id: int, telegram_message_id: int | None, role: MessageRole, user_id: int | None, user_name: str | None, text: str, ) -> None: with self._connect() as connection: connection.execute( """ INSERT INTO messages( chat_id, telegram_message_id, role, user_id, user_name, text, created_at ) VALUES (?, ?, ?, ?, ?, ?, ?) """, (chat_id, telegram_message_id, role, user_id, user_name, text, self._now()), ) def get_recent_messages(self, chat_id: int, limit: int) -> list[MessageRecord]: with self._connect() as connection: rows = connection.execute( """ SELECT role, user_id, user_name, text, created_at FROM messages WHERE chat_id = ? ORDER BY id DESC LIMIT ? """, (chat_id, limit), ).fetchall() records = [self._row_to_message(row) for row in rows] records.reverse() return records def clear_messages(self, chat_id: int) -> None: with self._connect() as connection: connection.execute("DELETE FROM messages WHERE chat_id = ?", (chat_id,)) def _connect(self) -> sqlite3.Connection: connection = sqlite3.connect(self.path) connection.row_factory = sqlite3.Row return connection @staticmethod def _row_to_message(row: sqlite3.Row) -> MessageRecord: role = str(row["role"]) if role not in ("user", "assistant"): raise RuntimeError(f"Unknown message role in database: {role}") user_id = row["user_id"] return MessageRecord( role=role, user_id=None if user_id is None else int(user_id), user_name=row["user_name"], text=str(row["text"]), created_at=str(row["created_at"]), ) @staticmethod def _now() -> str: return datetime.now(UTC).isoformat()