"""Data access for the reusable template-title library (字段管理).""" from collections.abc import Sequence from sqlalchemy import Select, func, select from sqlalchemy.orm import Session from app.crud.filters import LIKE_ESCAPE, like_pattern from app.models import TemplateTitlePlacement, TemplateTitle from app.schemas.template_title import ( TemplateTitleCreate, TemplateTitleUpdate, ) def _conditions(keyword: str | None, level: int | None) -> list: """Translate the list filters into SQLAlchemy predicates.""" conditions = [] if keyword: conditions.append( TemplateTitle.name.like(like_pattern(keyword), escape=LIKE_ESCAPE) ) if level is not None: conditions.append(TemplateTitle.level == level) return conditions def _ordered(stmt: Select) -> Select: """Apply the library's canonical browse order. Grouped by ``level`` first so the picker reads as an outline, then by insertion order so a title stays where the user put it. Deliberately *not* ordered by ``name``: names carry hand-written numbering ("1.", "10.", "2."), and string ordering would scramble it. """ return stmt.order_by(TemplateTitle.level.asc(), TemplateTitle.id.asc()) def list_titles( db: Session, *, keyword: str | None = None, level: int | None = None, page: int = 1, page_size: int = 20, ) -> tuple[list[TemplateTitle], int]: """Return one page of the title library, plus the unpaged total.""" conditions = _conditions(keyword, level) total = db.scalar( select(func.count(TemplateTitle.id)).where(*conditions) ) or 0 stmt = _ordered( select(TemplateTitle) .where(*conditions) .offset((page - 1) * page_size) .limit(page_size) ) return list(db.scalars(stmt).all()), total def get(db: Session, title_id: int) -> TemplateTitle | None: """Return one library entry, or ``None``.""" return db.get(TemplateTitle, title_id) def get_many(db: Session, title_ids: Sequence[int]) -> list[TemplateTitle]: """Return every entry whose id is in ``title_ids`` (missing ids ignored).""" if not title_ids: return [] stmt = select(TemplateTitle).where(TemplateTitle.id.in_(list(title_ids))) return list(db.scalars(stmt).all()) def usage_counts(db: Session, title_ids: Sequence[int]) -> dict[int, int]: """Count how many *distinct templates* place each title. Drives the refusal message when a title in use is deleted. ``DISTINCT`` matters because one template may legitimately place the same title twice. """ if not title_ids: return {} stmt = ( select( TemplateTitlePlacement.title_id, func.count(func.distinct(TemplateTitlePlacement.template_id)), ) .where(TemplateTitlePlacement.title_id.in_(list(title_ids))) .group_by(TemplateTitlePlacement.title_id) ) return {title_id: count for title_id, count in db.execute(stmt).all()} def create(db: Session, data: TemplateTitleCreate) -> TemplateTitle: """Insert a library entry.""" title = TemplateTitle(**data.model_dump()) db.add(title) db.commit() db.refresh(title) return title def update( db: Session, title: TemplateTitle, data: TemplateTitleUpdate, ) -> TemplateTitle: """Apply a partial update to a library entry. ``exclude_unset`` is what makes PATCH semantics work: a key the client did not send leaves the column alone, while an explicit ``null`` — which the schemas reject for every nullable-typed column here — would not. """ for key, value in data.model_dump(exclude_unset=True).items(): setattr(title, key, value) db.commit() db.refresh(title) return title def delete(db: Session, title: TemplateTitle) -> None: """Delete a library entry. Callers must check :func:`usage_counts` first.""" db.delete(title) db.commit()