from sqlalchemy import ( Column, Integer, String, Enum, TIMESTAMP, func, Boolean, ForeignKey, Float, Text, Index, DateTime, Date, UniqueConstraint ) from sqlalchemy.orm import declarative_base, relationship import enum from datetime import datetime, timezone Base = declarative_base() class UserRole(enum.Enum): user = "user" editor = "editor" admin = "admin" superadmin = "superadmin" class AcademicPeriodType(enum.Enum): schuljahr = "schuljahr" semester = "semester" trimester = "trimester" class LogLevel(enum.Enum): ERROR = "ERROR" WARN = "WARN" INFO = "INFO" DEBUG = "DEBUG" class ProcessStatus(enum.Enum): running = "running" crashed = "crashed" starting = "starting" stopped = "stopped" class ScreenHealthStatus(enum.Enum): OK = "OK" BLACK = "BLACK" FROZEN = "FROZEN" UNKNOWN = "UNKNOWN" class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True, autoincrement=True) username = Column(String(50), unique=True, nullable=False, index=True) password_hash = Column(String(128), nullable=False) role = Column(Enum(UserRole), nullable=False, default=UserRole.user) is_active = Column(Boolean, default=True, nullable=False) last_login_at = Column(TIMESTAMP(timezone=True), nullable=True) last_password_change_at = Column(TIMESTAMP(timezone=True), nullable=True) last_failed_login_at = Column(TIMESTAMP(timezone=True), nullable=True) failed_login_attempts = Column(Integer, nullable=False, default=0, server_default="0") locked_until = Column(TIMESTAMP(timezone=True), nullable=True) deactivated_at = Column(TIMESTAMP(timezone=True), nullable=True) deactivated_by = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True) created_at = Column(TIMESTAMP(timezone=True), server_default=func.current_timestamp()) updated_at = Column(TIMESTAMP(timezone=True), server_default=func.current_timestamp( ), onupdate=func.current_timestamp()) class AcademicPeriod(Base): __tablename__ = 'academic_periods' id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(100), nullable=False) # "Schuljahr 2024/25" display_name = Column(String(50), nullable=True) # "SJ 24/25" (kurz) start_date = Column(Date, nullable=False, index=True) end_date = Column(Date, nullable=False, index=True) period_type = Column(Enum(AcademicPeriodType), nullable=False, default=AcademicPeriodType.schuljahr) # nur eine aktive Periode zur Zeit is_active = Column(Boolean, default=False, nullable=False) # Archive lifecycle fields is_archived = Column(Boolean, default=False, nullable=False, index=True) archived_at = Column(TIMESTAMP(timezone=True), nullable=True) archived_by = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True) created_at = Column(TIMESTAMP(timezone=True), server_default=func.current_timestamp()) updated_at = Column(TIMESTAMP(timezone=True), server_default=func.current_timestamp( ), onupdate=func.current_timestamp()) # Constraint: nur eine aktive Periode zur Zeit; name unique among non-archived periods __table_args__ = ( Index('ix_academic_periods_active', 'is_active'), Index('ix_academic_periods_archived', 'is_archived'), # Unique constraint on active (non-archived) periods only is handled in code # This index facilitates the query for checking uniqueness Index('ix_academic_periods_name_not_archived', 'name', 'is_archived'), ) def to_dict(self): return { "id": self.id, "name": self.name, "display_name": self.display_name, "start_date": self.start_date.isoformat() if self.start_date else None, "end_date": self.end_date.isoformat() if self.end_date else None, "period_type": self.period_type.value if self.period_type else None, "is_active": self.is_active, "is_archived": self.is_archived, "archived_at": self.archived_at.isoformat() if self.archived_at else None, "archived_by": self.archived_by, "created_at": self.created_at.isoformat() if self.created_at else None, "updated_at": self.updated_at.isoformat() if self.updated_at else None, } class ClientGroup(Base): __tablename__ = 'client_groups' id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(100), unique=True, nullable=False) description = Column(String(255), nullable=True) # Manuell zu setzen created_at = Column(TIMESTAMP(timezone=True), server_default=func.current_timestamp()) is_active = Column(Boolean, default=True, nullable=False) class Client(Base): __tablename__ = 'clients' uuid = Column(String(36), primary_key=True, nullable=False) hardware_token = Column(String(64), nullable=True) ip = Column(String(45), nullable=True) type = Column(String(50), nullable=True) hostname = Column(String(100), nullable=True) os_version = Column(String(100), nullable=True) software_version = Column(String(100), nullable=True) macs = Column(String(255), nullable=True) model = Column(String(100), nullable=True) description = Column(String(255), nullable=True) # Manuell zu setzen registration_time = Column(TIMESTAMP( timezone=True), server_default=func.current_timestamp(), nullable=False) last_alive = Column(TIMESTAMP(timezone=True), server_default=func.current_timestamp( ), onupdate=func.current_timestamp(), nullable=False) is_active = Column(Boolean, default=True, nullable=False) group_id = Column(Integer, ForeignKey( 'client_groups.id'), nullable=False, default=1) # Health monitoring fields current_event_id = Column(Integer, nullable=True) current_process = Column(String(50), nullable=True) # 'vlc', 'chromium', 'pdf_viewer' process_status = Column(Enum(ProcessStatus), nullable=True) process_pid = Column(Integer, nullable=True) last_screenshot_analyzed = Column(TIMESTAMP(timezone=True), nullable=True) screen_health_status = Column(Enum(ScreenHealthStatus), nullable=True, server_default='UNKNOWN') last_screenshot_hash = Column(String(32), nullable=True) # Systemd service-failed tracking service_failed_at = Column(TIMESTAMP(timezone=True), nullable=True) service_failed_unit = Column(String(128), nullable=True) # MQTT broker connection health mqtt_reconnect_count = Column(Integer, nullable=True) mqtt_last_disconnect_at = Column(TIMESTAMP(timezone=True), nullable=True) class ClientLog(Base): __tablename__ = 'client_logs' id = Column(Integer, primary_key=True, autoincrement=True) client_uuid = Column(String(36), ForeignKey('clients.uuid', ondelete='CASCADE'), nullable=False, index=True) timestamp = Column(TIMESTAMP(timezone=True), nullable=False, index=True) level = Column(Enum(LogLevel), nullable=False, index=True) message = Column(Text, nullable=False) context = Column(Text, nullable=True) # JSON stored as text created_at = Column(TIMESTAMP(timezone=True), server_default=func.current_timestamp(), nullable=False) __table_args__ = ( Index('ix_client_logs_client_timestamp', 'client_uuid', 'timestamp'), Index('ix_client_logs_level_timestamp', 'level', 'timestamp'), ) class ClientCommand(Base): __tablename__ = 'client_commands' id = Column(Integer, primary_key=True, autoincrement=True) command_id = Column(String(36), nullable=False, unique=True, index=True) client_uuid = Column(String(36), ForeignKey('clients.uuid', ondelete='CASCADE'), nullable=False, index=True) action = Column(String(32), nullable=False, index=True) status = Column(String(40), nullable=False, index=True) reason = Column(Text, nullable=True) requested_by = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True, index=True) issued_at = Column(TIMESTAMP(timezone=True), nullable=False) expires_at = Column(TIMESTAMP(timezone=True), nullable=False) published_at = Column(TIMESTAMP(timezone=True), nullable=True) acked_at = Column(TIMESTAMP(timezone=True), nullable=True) execution_started_at = Column(TIMESTAMP(timezone=True), nullable=True) completed_at = Column(TIMESTAMP(timezone=True), nullable=True) failed_at = Column(TIMESTAMP(timezone=True), nullable=True) error_code = Column(String(64), nullable=True) error_message = Column(Text, nullable=True) created_at = Column(TIMESTAMP(timezone=True), server_default=func.current_timestamp(), nullable=False) updated_at = Column(TIMESTAMP(timezone=True), server_default=func.current_timestamp(), onupdate=func.current_timestamp(), nullable=False) __table_args__ = ( Index('ix_client_commands_client_status_created', 'client_uuid', 'status', 'created_at'), ) class EventType(enum.Enum): presentation = "presentation" website = "website" video = "video" message = "message" other = "other" webuntis = "webuntis" class MediaType(enum.Enum): # Präsentationen pdf = "pdf" ppt = "ppt" pptx = "pptx" odp = "odp" # Videos (gängige VLC-Formate) mp4 = "mp4" avi = "avi" mkv = "mkv" mov = "mov" wmv = "wmv" flv = "flv" webm = "webm" mpg = "mpg" mpeg = "mpeg" ogv = "ogv" # Bilder (benutzerfreundlich) jpg = "jpg" jpeg = "jpeg" png = "png" gif = "gif" bmp = "bmp" tiff = "tiff" svg = "svg" # HTML-Mitteilung html = "html" # Webseiten website = "website" class Event(Base): __tablename__ = 'events' id = Column(Integer, primary_key=True, autoincrement=True) group_id = Column(Integer, ForeignKey('client_groups.id'), nullable=False, index=True) academic_period_id = Column(Integer, ForeignKey('academic_periods.id'), nullable=True, index=True) # Optional für Rückwärtskompatibilität title = Column(String(100), nullable=False) description = Column(Text, nullable=True) start = Column(TIMESTAMP(timezone=True), nullable=False, index=True) end = Column(TIMESTAMP(timezone=True), nullable=False, index=True) event_type = Column(Enum(EventType), nullable=False) event_media_id = Column(Integer, ForeignKey('event_media.id'), nullable=True) autoplay = Column(Boolean, nullable=True) # NEU loop = Column(Boolean, nullable=True) # NEU volume = Column(Float, nullable=True) # NEU muted = Column(Boolean, nullable=True) # NEU: Video mute slideshow_interval = Column(Integer, nullable=True) # NEU page_progress = Column(Boolean, nullable=True) # NEU: Seitenfortschritt (Page-Progress) auto_progress = Column(Boolean, nullable=True) # NEU: Präsentationsfortschritt (Auto-Progress) # Recurrence fields recurrence_rule = Column(String(255), nullable=True, index=True) # iCalendar RRULE string recurrence_end = Column(TIMESTAMP(timezone=True), nullable=True, index=True) # When recurrence ends # Whether recurrences should skip school holidays skip_holidays = Column(Boolean, nullable=False, server_default='0') created_at = Column(TIMESTAMP(timezone=True), server_default=func.current_timestamp()) updated_at = Column(TIMESTAMP(timezone=True), server_default=func.current_timestamp(), onupdate=func.current_timestamp()) created_by = Column(Integer, ForeignKey('users.id'), nullable=False) updated_by = Column(Integer, ForeignKey('users.id'), nullable=True) is_active = Column(Boolean, default=True, nullable=False) # Add relationships academic_period = relationship("AcademicPeriod", foreign_keys=[academic_period_id]) event_media = relationship("EventMedia", foreign_keys=[event_media_id]) exceptions = relationship("EventException", back_populates="event", cascade="all, delete-orphan") # --- EventException: Store exceptions/overrides for recurring events --- class EventException(Base): __tablename__ = 'event_exceptions' id = Column(Integer, primary_key=True, autoincrement=True) event_id = Column(Integer, ForeignKey('events.id', ondelete='CASCADE'), nullable=False, index=True) exception_date = Column(Date, nullable=False, index=True) # Date of the exception/override is_skipped = Column(Boolean, default=False, nullable=False) # If this occurrence is skipped override_title = Column(String(100), nullable=True) override_description = Column(Text, nullable=True) override_start = Column(TIMESTAMP(timezone=True), nullable=True) override_end = Column(TIMESTAMP(timezone=True), nullable=True) created_at = Column(TIMESTAMP(timezone=True), server_default=func.current_timestamp()) updated_at = Column(TIMESTAMP(timezone=True), server_default=func.current_timestamp(), onupdate=func.current_timestamp()) event = relationship("Event", back_populates="exceptions") class EventMedia(Base): __tablename__ = 'event_media' id = Column(Integer, primary_key=True, autoincrement=True) academic_period_id = Column(Integer, ForeignKey( # Optional für bessere Organisation 'academic_periods.id'), nullable=True, index=True) media_type = Column(Enum(MediaType), nullable=False) url = Column(String(255), nullable=False) file_path = Column(String(255), nullable=True) message_content = Column(Text, nullable=True) uploaded_at = Column(TIMESTAMP, nullable=False, default=lambda: datetime.now(timezone.utc)) # Add relationship academic_period = relationship( "AcademicPeriod", foreign_keys=[academic_period_id]) def to_dict(self): return { "id": self.id, "academic_period_id": self.academic_period_id, "media_type": self.media_type.value if self.media_type else None, "url": self.url, "file_path": self.file_path, "message_content": self.message_content, } class SchoolHoliday(Base): __tablename__ = 'school_holidays' id = Column(Integer, primary_key=True, autoincrement=True) academic_period_id = Column(Integer, ForeignKey('academic_periods.id', ondelete='SET NULL'), nullable=True, index=True) name = Column(String(150), nullable=False) start_date = Column(Date, nullable=False, index=True) end_date = Column(Date, nullable=False, index=True) region = Column(String(100), nullable=True, index=True) source_file_name = Column(String(255), nullable=True) imported_at = Column(TIMESTAMP(timezone=True), server_default=func.current_timestamp()) academic_period = relationship("AcademicPeriod", foreign_keys=[academic_period_id]) __table_args__ = ( UniqueConstraint('name', 'start_date', 'end_date', 'region', 'academic_period_id', name='uq_school_holidays_unique'), ) def to_dict(self): return { "id": self.id, "academic_period_id": self.academic_period_id, "name": self.name, "start_date": self.start_date.isoformat() if self.start_date else None, "end_date": self.end_date.isoformat() if self.end_date else None, "region": self.region, "source_file_name": self.source_file_name, "imported_at": self.imported_at.isoformat() if self.imported_at else None, } # --- Conversions: Track PPT/PPTX/ODP -> PDF processing state --- class ConversionStatus(enum.Enum): pending = "pending" processing = "processing" ready = "ready" failed = "failed" class Conversion(Base): __tablename__ = 'conversions' id = Column(Integer, primary_key=True, autoincrement=True) # Source media to be converted source_event_media_id = Column( Integer, ForeignKey('event_media.id', ondelete='CASCADE'), nullable=False, index=True, ) target_format = Column(String(10), nullable=False, index=True) # e.g. 'pdf' # relative to server/media target_path = Column(String(512), nullable=True) status = Column(Enum(ConversionStatus), nullable=False, default=ConversionStatus.pending) file_hash = Column(String(64), nullable=False) # sha256 of source file started_at = Column(TIMESTAMP(timezone=True), nullable=True) completed_at = Column(TIMESTAMP(timezone=True), nullable=True) error_message = Column(Text, nullable=True) __table_args__ = ( # Fast lookup per media/format Index('ix_conv_source_target', 'source_event_media_id', 'target_format'), # Operational filtering Index('ix_conv_status_target', 'status', 'target_format'), # Idempotency: same source + target + file content should be unique UniqueConstraint('source_event_media_id', 'target_format', 'file_hash', name='uq_conv_source_target_hash'), ) # --- SystemSetting: Flexible key-value store for system-wide configuration --- class SystemSetting(Base): __tablename__ = 'system_settings' key = Column(String(100), primary_key=True, nullable=False) value = Column(Text, nullable=True) description = Column(String(255), nullable=True) updated_at = Column(TIMESTAMP(timezone=True), server_default=func.current_timestamp(), onupdate=func.current_timestamp()) def to_dict(self): return { "key": self.key, "value": self.value, "description": self.description, "updated_at": self.updated_at.isoformat() if self.updated_at else None, }