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())