| from datetime import datetime |
| from typing import Optional |
| from uuid import UUID, uuid4 |
| from sqlalchemy import Boolean, DateTime, Float, ForeignKey, Integer, String, Text, func |
| from sqlalchemy.dialects.postgresql import UUID as PGUUID |
| from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship |
|
|
|
|
| class Base(DeclarativeBase): |
| pass |
|
|
|
|
| class Department(Base): |
| __tablename__ = "departments" |
| |
| id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True, default=uuid4) |
| name: Mapped[str] = mapped_column(String(100), nullable=False, unique=True) |
| code: Mapped[str] = mapped_column(String(20), nullable=False, unique=True) |
| description: Mapped[Optional[str]] = mapped_column(Text, nullable=True) |
| |
| categories: Mapped[Optional[str]] = mapped_column(Text, nullable=True) |
| |
| default_sla_hours: Mapped[int] = mapped_column(Integer, default=48) |
| escalation_email: Mapped[Optional[str]] = mapped_column(String(255), nullable=True) |
| |
| is_active: Mapped[bool] = mapped_column(Boolean, default=True) |
| created_at: Mapped[datetime] = mapped_column(DateTime, default=func.now()) |
| updated_at: Mapped[datetime] = mapped_column(DateTime, default=func.now(), onupdate=func.now()) |
| |
| members: Mapped[list["Member"]] = relationship(back_populates="department", lazy="selectin") |
|
|
|
|
| class Member(Base): |
| __tablename__ = "members" |
| |
| id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True, default=uuid4) |
| department_id: Mapped[Optional[UUID]] = mapped_column(PGUUID(as_uuid=True), ForeignKey("departments.id", ondelete="SET NULL"), nullable=True, index=True) |
| |
| name: Mapped[str] = mapped_column(String(100), nullable=False) |
| email: Mapped[str] = mapped_column(String(255), nullable=False, unique=True) |
| phone: Mapped[Optional[str]] = mapped_column(String(20), nullable=True) |
| password_hash: Mapped[Optional[str]] = mapped_column(String(255), nullable=True) |
| |
| role: Mapped[str] = mapped_column(String(50), default="worker") |
| city: Mapped[Optional[str]] = mapped_column(String(100), nullable=True, index=True) |
| locality: Mapped[Optional[str]] = mapped_column(String(100), nullable=True) |
| |
| is_active: Mapped[bool] = mapped_column(Boolean, default=True) |
| current_workload: Mapped[int] = mapped_column(Integer, default=0) |
| max_workload: Mapped[int] = mapped_column(Integer, default=10) |
| |
| created_at: Mapped[datetime] = mapped_column(DateTime, default=func.now()) |
| updated_at: Mapped[datetime] = mapped_column(DateTime, default=func.now(), onupdate=func.now()) |
| |
| department: Mapped[Optional["Department"]] = relationship(back_populates="members") |
|
|
|
|
|
|
|
|
| class Issue(Base): |
| __tablename__ = "issues" |
| |
| id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True, default=uuid4) |
| user_id: Mapped[Optional[str]] = mapped_column(String(255), nullable=True, index=True) |
| description: Mapped[Optional[str]] = mapped_column(Text, nullable=True) |
| latitude: Mapped[float] = mapped_column(Float, nullable=False, index=True) |
| longitude: Mapped[float] = mapped_column(Float, nullable=False, index=True) |
| accuracy_meters: Mapped[Optional[float]] = mapped_column(Float, nullable=True) |
| |
| state: Mapped[str] = mapped_column(String(20), default="reported", index=True) |
| priority: Mapped[Optional[int]] = mapped_column(Integer, nullable=True, index=True) |
| priority_reason: Mapped[Optional[str]] = mapped_column(Text, nullable=True) |
| |
| validation_source: Mapped[Optional[str]] = mapped_column(String(20), nullable=True) |
| validation_reason: Mapped[Optional[str]] = mapped_column(Text, nullable=True) |
| |
| is_duplicate: Mapped[bool] = mapped_column(Boolean, default=False, index=True) |
| parent_issue_id: Mapped[Optional[UUID]] = mapped_column(PGUUID(as_uuid=True), ForeignKey("issues.id"), nullable=True) |
| geo_cluster_id: Mapped[Optional[str]] = mapped_column(String(50), nullable=True, index=True) |
| |
| platform: Mapped[str] = mapped_column(String(50), nullable=False) |
| device_model: Mapped[Optional[str]] = mapped_column(String(100), nullable=True) |
| |
| department_id: Mapped[Optional[UUID]] = mapped_column(PGUUID(as_uuid=True), ForeignKey("departments.id"), nullable=True) |
| assigned_member_id: Mapped[Optional[UUID]] = mapped_column(PGUUID(as_uuid=True), ForeignKey("members.id"), nullable=True) |
| city: Mapped[Optional[str]] = mapped_column(String(100), nullable=True, index=True) |
| locality: Mapped[Optional[str]] = mapped_column(String(100), nullable=True) |
| full_address: Mapped[Optional[str]] = mapped_column(Text, nullable=True) |
| |
| sla_deadline: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True) |
| sla_hours: Mapped[Optional[int]] = mapped_column(Integer, nullable=True) |
| escalation_level: Mapped[int] = mapped_column(Integer, default=0) |
| escalated_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True) |
| |
| resolved_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True, index=True) |
| resolution_notes: Mapped[Optional[str]] = mapped_column(Text, nullable=True) |
| proof_image_path: Mapped[Optional[str]] = mapped_column(String(500), nullable=True) |
| completed_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True) |
| |
| created_at: Mapped[datetime] = mapped_column(DateTime, default=func.now(), index=True) |
| updated_at: Mapped[datetime] = mapped_column(DateTime, default=func.now(), onupdate=func.now()) |
|
|
| |
| images: Mapped[list["IssueImage"]] = relationship(back_populates="issue", lazy="selectin", foreign_keys="IssueImage.issue_id") |
| classification: Mapped[Optional["Classification"]] = relationship(back_populates="issue", uselist=False, lazy="selectin") |
| |
| department: Mapped[Optional["Department"]] = relationship("Department", lazy="selectin") |
| assigned_member: Mapped[Optional["Member"]] = relationship("Member", foreign_keys=[assigned_member_id], lazy="selectin") |
| |
| events: Mapped[list["IssueEvent"]] = relationship(back_populates="issue", lazy="noload") |
| duplicates: Mapped[list["Issue"]] = relationship(back_populates="parent_issue", foreign_keys=[parent_issue_id]) |
| parent_issue: Mapped[Optional["Issue"]] = relationship(back_populates="duplicates", remote_side=[id], foreign_keys=[parent_issue_id]) |
|
|
|
|
| class IssueImage(Base): |
| __tablename__ = "issue_images" |
| |
| id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True, default=uuid4) |
| issue_id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), ForeignKey("issues.id", ondelete="CASCADE"), index=True) |
| file_path: Mapped[str] = mapped_column(String(500), nullable=False) |
| annotated_path: Mapped[Optional[str]] = mapped_column(String(500), nullable=True) |
| original_filename: Mapped[Optional[str]] = mapped_column(String(255), nullable=True) |
| created_at: Mapped[datetime] = mapped_column(DateTime, default=func.now()) |
| |
| issue: Mapped["Issue"] = relationship(back_populates="images", foreign_keys=[issue_id]) |
|
|
|
|
| class Classification(Base): |
| __tablename__ = "classifications" |
| |
| id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True, default=uuid4) |
| issue_id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), ForeignKey("issues.id", ondelete="CASCADE"), unique=True) |
| |
| primary_category: Mapped[Optional[str]] = mapped_column(String(100), nullable=True, index=True) |
| primary_confidence: Mapped[float] = mapped_column(Float, default=0.0) |
| |
| detections_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True) |
| inference_time_ms: Mapped[float] = mapped_column(Float, default=0.0) |
| model_version: Mapped[str] = mapped_column(String(20), default="1.0") |
| |
| created_at: Mapped[datetime] = mapped_column(DateTime, default=func.now()) |
| |
| issue: Mapped["Issue"] = relationship(back_populates="classification") |
|
|
|
|
| class IssueEvent(Base): |
| __tablename__ = "issue_events" |
| |
| id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True, default=uuid4) |
| issue_id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), ForeignKey("issues.id", ondelete="CASCADE"), index=True) |
| |
| event_type: Mapped[str] = mapped_column(String(50), nullable=False, index=True) |
| agent_name: Mapped[Optional[str]] = mapped_column(String(50), nullable=True) |
| event_data: Mapped[Optional[str]] = mapped_column(Text, nullable=True) |
| |
| created_at: Mapped[datetime] = mapped_column(DateTime, default=func.now(), index=True) |
| |
| issue: Mapped["Issue"] = relationship(back_populates="events") |
|
|
|
|
| class Escalation(Base): |
| __tablename__ = "escalations" |
| |
| id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True, default=uuid4) |
| issue_id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), ForeignKey("issues.id", ondelete="CASCADE"), index=True) |
| |
| from_level: Mapped[int] = mapped_column(Integer, nullable=False) |
| to_level: Mapped[int] = mapped_column(Integer, nullable=False) |
| reason: Mapped[str] = mapped_column(Text, nullable=False) |
| |
| escalated_by: Mapped[str] = mapped_column(String(50), default="system") |
| notified_emails: Mapped[Optional[str]] = mapped_column(Text, nullable=True) |
| |
| created_at: Mapped[datetime] = mapped_column(DateTime, default=func.now()) |
|
|
