models.py - master - ring0/support-system

models.py
214 строк8 KB
ring0
Init project
22 июн 2026, 17:13
22 июн 2026, 17:139df53b7
from sqlalchemy import Column, Integer, String, Text, ForeignKey, text
from sqlalchemy.orm import relationship
from pydantic import BaseModel, EmailStr, validator
from typing import Optional
from db import Base

# Допустимые роли пользователей и статусы заявок
ROLES = ["user", "specialist", "admin"]
STATUSES = ["open", "in_progress", "resolved", "closed"]


# ---------------------------------------------------------------------------
# Модели SQLAlchemy (физическая структура базы данных)
# ---------------------------------------------------------------------------
class User(Base):
    """Модель User (Пользователь)"""
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String, nullable=False, unique=True)
    password_hash = Column(String, nullable=False)
    full_name = Column(String, nullable=False)
    email = Column(String, nullable=False, unique=True)
    role = Column(String, nullable=False, default="user")  # user, specialist, admin
    created_at = Column(String, server_default=text("CURRENT_TIMESTAMP"))

    authored_tickets = relationship(
        "Ticket", foreign_keys="Ticket.author_id", back_populates="author"
    )
    assigned_tickets = relationship(
        "Ticket", foreign_keys="Ticket.assignee_id", back_populates="assignee"
    )
    comments = relationship("Comment", back_populates="author")

    def to_dict(self):
        return {
            "id": self.id,
            "username": self.username,
            "full_name": self.full_name,
            "email": self.email,
            "role": self.role,
            "created_at": self.created_at,
        }


class Category(Base):
    """Модель Category (Категория) — справочник типов обращений"""
    __tablename__ = "categories"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False, unique=True)

    tickets = relationship("Ticket", back_populates="category")

    def to_dict(self):
        return {"id": self.id, "name": self.name}


class Priority(Base):
    """Модель Priority (Приоритет) — справочник уровней срочности"""
    __tablename__ = "priorities"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False, unique=True)
    weight = Column(Integer, nullable=False, default=1)  # числовой вес для сортировки

    tickets = relationship("Ticket", back_populates="priority")

    def to_dict(self):
        return {"id": self.id, "name": self.name, "weight": self.weight}


class Ticket(Base):
    """Модель Ticket (Заявка) — центральная сущность системы"""
    __tablename__ = "tickets"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, nullable=False)
    description = Column(Text, nullable=False)
    category_id = Column(Integer, ForeignKey("categories.id"), nullable=False)
    priority_id = Column(Integer, ForeignKey("priorities.id"), nullable=False)
    author_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    assignee_id = Column(Integer, ForeignKey("users.id"), nullable=True)
    status = Column(String, nullable=False, default="open")  # open, in_progress, resolved, closed
    created_at = Column(String, server_default=text("CURRENT_TIMESTAMP"))
    updated_at = Column(String, server_default=text("CURRENT_TIMESTAMP"))

    category = relationship("Category", back_populates="tickets")
    priority = relationship("Priority", back_populates="tickets")
    author = relationship("User", foreign_keys=[author_id], back_populates="authored_tickets")
    assignee = relationship("User", foreign_keys=[assignee_id], back_populates="assigned_tickets")
    comments = relationship("Comment", back_populates="ticket", cascade="all, delete-orphan")

    def to_dict(self):
        return {
            "id": self.id,
            "title": self.title,
            "description": self.description,
            "category_id": self.category_id,
            "category_name": self.category.name if self.category else None,
            "priority_id": self.priority_id,
            "priority_name": self.priority.name if self.priority else None,
            "author_id": self.author_id,
            "author_name": self.author.full_name if self.author else None,
            "assignee_id": self.assignee_id,
            "assignee_name": self.assignee.full_name if self.assignee else None,
            "status": self.status,
            "created_at": self.created_at,
            "updated_at": self.updated_at,
        }


class Comment(Base):
    """Модель Comment (Комментарий) — сообщение к заявке"""
    __tablename__ = "comments"

    id = Column(Integer, primary_key=True, index=True)
    text = Column(Text, nullable=False)
    ticket_id = Column(Integer, ForeignKey("tickets.id"), nullable=False)
    author_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    created_at = Column(String, server_default=text("CURRENT_TIMESTAMP"))

    ticket = relationship("Ticket", back_populates="comments")
    author = relationship("User", back_populates="comments")

    def to_dict(self):
        return {
            "id": self.id,
            "text": self.text,
            "ticket_id": self.ticket_id,
            "author_id": self.author_id,
            "author_name": self.author.full_name if self.author else None,
            "created_at": self.created_at,
        }


# ---------------------------------------------------------------------------
# Pydantic-модели для валидации входных данных
# ---------------------------------------------------------------------------
class UserRegister(BaseModel):
    username: str
    password: str
    full_name: str
    email: EmailStr
    role: str = "user"

    @validator("role")
    def validate_role(cls, v):
        if v not in ROLES:
            raise ValueError(f"role must be one of {ROLES}")
        return v

    @validator("password")
    def password_not_empty(cls, v):
        if len(v) < 4:
            raise ValueError("password must be at least 4 characters")
        return v


class LoginRequest(BaseModel):
    username: str
    password: str


class TicketCreate(BaseModel):
    title: str
    description: str
    category_id: int
    priority_id: int

    @validator("title")
    def title_not_empty(cls, v):
        if not v.strip():
            raise ValueError("title must not be empty")
        return v.strip()

    @validator("description")
    def description_not_empty(cls, v):
        if not v.strip():
            raise ValueError("description must not be empty")
        return v.strip()


class TicketUpdate(BaseModel):
    """Обновление заявки. Специалист меняет status; администратор — любые поля."""
    status: Optional[str] = None
    assignee_id: Optional[int] = None
    category_id: Optional[int] = None
    priority_id: Optional[int] = None

    @validator("status")
    def validate_status(cls, v):
        if v is not None and v not in STATUSES:
            raise ValueError(f"status must be one of {STATUSES}")
        return v


class CommentCreate(BaseModel):
    text: str

    @validator("text")
    def text_not_empty(cls, v):
        if not v.strip():
            raise ValueError("text must not be empty")
        return v.strip()


class CategoryCreate(BaseModel):
    name: str


class PriorityCreate(BaseModel):
    name: str
    weight: int = 1