"""SQLAlchemy-backed notification-template store (global / shared).""" from __future__ import annotations import uuid from datetime import UTC, datetime from typing import Any from sqlalchemy import or_, select from sqlalchemy.exc import IntegrityError from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker from deerflow.persistence.notification_templates.base import ( DuplicateNotificationTemplateNameError, NotificationTemplateStore, VersionConflictError, ) from deerflow.persistence.notification_templates.model import NotificationTemplateRow _WRITABLE = { "name", "title_template", "content_template", "tags", "enabled", } def _to_dict(row: NotificationTemplateRow) -> dict[str, Any]: data = row.to_dict() for key in ("created_at", "updated_at"): value = data.get(key) if isinstance(value, datetime): data[key] = value.isoformat() data["tags"] = list(data.get("tags") or []) return data class NotificationTemplateRepository(NotificationTemplateStore): def __init__(self, session_factory: async_sessionmaker[AsyncSession]) -> None: self._sf = session_factory async def name_exists(self, name: str, *, exclude_id: str | None = None) -> bool: async with self._sf() as session: return await self._name_taken(session, name, exclude_id=exclude_id) async def _name_taken(self, session: AsyncSession, name: str, *, exclude_id: str | None = None) -> bool: stmt = select(NotificationTemplateRow.id).where(NotificationTemplateRow.name == name) if exclude_id is not None: stmt = stmt.where(NotificationTemplateRow.id != exclude_id) result = await session.execute(stmt.limit(1)) return result.first() is not None def _filter_stmt( self, *, keyword: str | None, enabled: bool | None, tag: str | None, ): stmt = select(NotificationTemplateRow) if enabled is not None: stmt = stmt.where(NotificationTemplateRow.enabled.is_(enabled)) if keyword: needle = f"%{keyword.strip()}%" if keyword.strip(): stmt = stmt.where( or_( NotificationTemplateRow.name.ilike(needle), NotificationTemplateRow.title_template.ilike(needle), NotificationTemplateRow.content_template.ilike(needle), ) ) # Tag filter is applied in Python (PortableJSON differs across backends). return stmt async def list_templates( self, *, keyword: str | None = None, enabled: bool | None = None, tag: str | None = None, page: int = 1, page_size: int = 20, ) -> tuple[list[dict[str, Any]], int]: page = max(1, page) page_size = max(1, page_size) stmt = self._filter_stmt(keyword=keyword, enabled=enabled, tag=tag) stmt = stmt.order_by(NotificationTemplateRow.created_at.desc()) async with self._sf() as session: result = await session.execute(stmt) rows = [_to_dict(r) for r in result.scalars()] if tag is not None: rows = [r for r in rows if tag in (r.get("tags") or [])] total = len(rows) start = (page - 1) * page_size return rows[start : start + page_size], total async def get_template(self, template_id: str) -> dict[str, Any] | None: async with self._sf() as session: row = await session.get(NotificationTemplateRow, template_id) return _to_dict(row) if row is not None else None async def create_template(self, data: dict[str, Any]) -> dict[str, Any]: row = NotificationTemplateRow( id=data.get("id") or uuid.uuid4().hex[:12], name=data["name"], title_template=data["title_template"], content_template=data["content_template"], tags=list(data.get("tags") or []), enabled=data.get("enabled", True), version=1, created_by=data.get("created_by"), ) async with self._sf() as session: if await self._name_taken(session, row.name): raise DuplicateNotificationTemplateNameError(row.name) session.add(row) try: await session.commit() except IntegrityError as exc: await session.rollback() raise DuplicateNotificationTemplateNameError(row.name) from exc await session.refresh(row) return _to_dict(row) async def update_template( self, template_id: str, data: dict[str, Any], *, expected_version: int, ) -> dict[str, Any] | None: async with self._sf() as session: row = await session.get(NotificationTemplateRow, template_id) if row is None: return None if int(row.version) != int(expected_version): raise VersionConflictError(_to_dict(row)) new_name = data.get("name") if new_name is not None and new_name != row.name and await self._name_taken(session, new_name, exclude_id=template_id): raise DuplicateNotificationTemplateNameError(new_name) for key, value in data.items(): if key in _WRITABLE: setattr(row, key, value) row.version = int(row.version) + 1 row.updated_at = datetime.now(UTC) try: await session.commit() except IntegrityError as exc: await session.rollback() raise DuplicateNotificationTemplateNameError(new_name or row.name) from exc await session.refresh(row) return _to_dict(row) async def delete_template(self, template_id: str) -> bool: async with self._sf() as session: row = await session.get(NotificationTemplateRow, template_id) if row is None: return False await session.delete(row) await session.commit() return True