File size: 9,349 Bytes
b55355a | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 | 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())
|