stundenfuchs/app/models.py

from datetime import date, datetime, timezone
import uuid

from sqlalchemy import Boolean, Date, DateTime, ForeignKey, Integer, String, Text, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.database import Base


class User(Base):
    __tablename__ = "users"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    email: Mapped[str] = mapped_column(String(255), unique=True, index=True, nullable=False)
    password_hash: Mapped[str] = mapped_column(String(255), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )
    weekly_target_minutes: Mapped[int] = mapped_column(Integer, default=1500, nullable=False)
    role: Mapped[str] = mapped_column(String(32), default="user", nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    preferred_home_view: Mapped[str] = mapped_column(String(16), default="week", nullable=False)
    theme_preference: Mapped[str] = mapped_column(String(16), default="auto", nullable=False)
    preferred_month_view_mode: Mapped[str] = mapped_column(String(16), default="flat", nullable=False)
    entry_mode: Mapped[str] = mapped_column(String(16), default="manual", nullable=False)
    working_days_csv: Mapped[str] = mapped_column(String(32), default="0,1,2,3,4", nullable=False)
    count_vacation_as_worktime: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    count_holiday_as_worktime: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    count_sick_as_worktime: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    automatic_break_rules_enabled: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    default_break_minutes: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    overtime_start_date: Mapped[date | None] = mapped_column(Date, default=None)
    overtime_expiry_days: Mapped[int | None] = mapped_column(Integer, default=None)
    expire_negative_overtime: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    vacation_days_total: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    vacation_show_in_header: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    workhours_counter_enabled: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    workhours_counter_show_in_header: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    workhours_counter_start_date: Mapped[date | None] = mapped_column(Date, default=None)
    workhours_counter_end_date: Mapped[date | None] = mapped_column(Date, default=None)
    workhours_counter_manual_offset_minutes: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    workhours_counter_target_minutes: Mapped[int | None] = mapped_column(Integer, default=None)
    workhours_counter_target_email_enabled: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    workhours_counter_warning_last_sent_on: Mapped[date | None] = mapped_column(Date, default=None)
    workhours_counter_warning_last_sent_key: Mapped[str | None] = mapped_column(String(120), default=None)
    federal_state: Mapped[str | None] = mapped_column(String(8), default=None)
    email_verified: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    email_verification_token_hash: Mapped[str | None] = mapped_column(String(128), default=None, index=True)
    email_verification_expires_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), default=None)
    email_verification_sent_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), default=None)
    mfa_method: Mapped[str] = mapped_column(String(16), default="none", nullable=False)
    mfa_totp_secret_encrypted: Mapped[str | None] = mapped_column(Text, default=None)
    mfa_email_code_hash: Mapped[str | None] = mapped_column(String(255), default=None)
    mfa_email_code_expires_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), default=None)
    mfa_email_code_sent_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), default=None)

    time_entries: Mapped[list["TimeEntry"]] = relationship(
        "TimeEntry", back_populates="user", cascade="all, delete-orphan"
    )
    weekly_target_rules: Mapped[list["WeeklyTargetRule"]] = relationship(
        "WeeklyTargetRule",
        back_populates="user",
        cascade="all, delete-orphan",
        order_by="WeeklyTargetRule.effective_from",
    )
    vacation_periods: Mapped[list["VacationPeriod"]] = relationship(
        "VacationPeriod",
        back_populates="user",
        cascade="all, delete-orphan",
        order_by="VacationPeriod.start_date",
    )
    special_day_statuses: Mapped[list["SpecialDayStatus"]] = relationship(
        "SpecialDayStatus",
        back_populates="user",
        cascade="all, delete-orphan",
        order_by="SpecialDayStatus.date",
    )
    overtime_adjustments: Mapped[list["OvertimeAdjustment"]] = relationship(
        "OvertimeAdjustment",
        back_populates="user",
        cascade="all, delete-orphan",
        order_by="OvertimeAdjustment.date",
    )
    auto_entry_skips: Mapped[list["AutoEntrySkip"]] = relationship(
        "AutoEntrySkip",
        back_populates="user",
        cascade="all, delete-orphan",
        order_by="AutoEntrySkip.date",
    )
    password_reset_tokens: Mapped[list["PasswordResetToken"]] = relationship(
        "PasswordResetToken",
        back_populates="user",
        cascade="all, delete-orphan",
        order_by="PasswordResetToken.created_at.desc()",
    )
    import_previews: Mapped[list["ImportPreview"]] = relationship(
        "ImportPreview",
        back_populates="user",
        cascade="all, delete-orphan",
        order_by="ImportPreview.created_at.desc()",
    )
    support_tickets: Mapped[list["SupportTicket"]] = relationship(
        "SupportTicket",
        back_populates="user",
        cascade="all, delete-orphan",
        order_by="SupportTicket.created_at.desc()",
        foreign_keys="SupportTicket.user_id",
    )


class TimeEntry(Base):
    __tablename__ = "time_entries"
    __table_args__ = (UniqueConstraint("user_id", "date", name="uq_user_date"),)

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    user_id: Mapped[str] = mapped_column(String(36), ForeignKey("users.id", ondelete="CASCADE"), index=True)
    date: Mapped[date] = mapped_column(Date, index=True, nullable=False)

    start_minutes: Mapped[int] = mapped_column(Integer, nullable=False)
    end_minutes: Mapped[int] = mapped_column(Integer, nullable=False)
    break_minutes: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    break_rule_mode: Mapped[str] = mapped_column(String(16), default="manual", nullable=False)

    notes: Mapped[str | None] = mapped_column(Text, default=None)

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc)
    )

    user: Mapped[User] = relationship("User", back_populates="time_entries")


class LoginAttempt(Base):
    __tablename__ = "login_attempts"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    email: Mapped[str] = mapped_column(String(255), index=True, nullable=False)
    ip_address: Mapped[str] = mapped_column(String(64), index=True, nullable=False)
    success: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False, index=True
    )


class WeeklyTargetRule(Base):
    __tablename__ = "weekly_target_rules"
    __table_args__ = (UniqueConstraint("user_id", "effective_from", name="uq_user_effective_from"),)

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    user_id: Mapped[str] = mapped_column(String(36), ForeignKey("users.id", ondelete="CASCADE"), index=True)
    effective_from: Mapped[date] = mapped_column(Date, index=True, nullable=False)
    weekly_target_minutes: Mapped[int] = mapped_column(Integer, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )

    user: Mapped[User] = relationship("User", back_populates="weekly_target_rules")


class VacationPeriod(Base):
    __tablename__ = "vacation_periods"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    user_id: Mapped[str] = mapped_column(String(36), ForeignKey("users.id", ondelete="CASCADE"), index=True)
    start_date: Mapped[date] = mapped_column(Date, index=True, nullable=False)
    end_date: Mapped[date] = mapped_column(Date, index=True, nullable=False)
    include_weekends: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    notes: Mapped[str | None] = mapped_column(Text, default=None)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )

    user: Mapped[User] = relationship("User", back_populates="vacation_periods")


class SpecialDayStatus(Base):
    __tablename__ = "special_day_statuses"
    __table_args__ = (UniqueConstraint("user_id", "date", name="uq_user_special_day_date"),)

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    user_id: Mapped[str] = mapped_column(String(36), ForeignKey("users.id", ondelete="CASCADE"), index=True)
    date: Mapped[date] = mapped_column(Date, index=True, nullable=False)
    status: Mapped[str] = mapped_column(String(16), nullable=False)  # holiday | sick
    notes: Mapped[str | None] = mapped_column(Text, default=None)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )

    user: Mapped[User] = relationship("User", back_populates="special_day_statuses")


class OvertimeAdjustment(Base):
    __tablename__ = "overtime_adjustments"
    __table_args__ = (UniqueConstraint("user_id", "date", name="uq_user_overtime_adjustment_date"),)

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    user_id: Mapped[str] = mapped_column(String(36), ForeignKey("users.id", ondelete="CASCADE"), index=True)
    date: Mapped[date] = mapped_column(Date, index=True, nullable=False)
    minutes: Mapped[int] = mapped_column(Integer, nullable=False)
    notes: Mapped[str | None] = mapped_column(Text, default=None)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )

    user: Mapped[User] = relationship("User", back_populates="overtime_adjustments")


class AutoEntrySkip(Base):
    __tablename__ = "auto_entry_skips"
    __table_args__ = (UniqueConstraint("user_id", "date", name="uq_user_auto_entry_skip_date"),)

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    user_id: Mapped[str] = mapped_column(String(36), ForeignKey("users.id", ondelete="CASCADE"), index=True)
    date: Mapped[date] = mapped_column(Date, index=True, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )

    user: Mapped[User] = relationship("User", back_populates="auto_entry_skips")


class PasswordResetToken(Base):
    __tablename__ = "password_reset_tokens"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    user_id: Mapped[str] = mapped_column(String(36), ForeignKey("users.id", ondelete="CASCADE"), index=True)
    token_hash: Mapped[str] = mapped_column(String(128), unique=True, index=True, nullable=False)
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, index=True)
    used_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), default=None)
    requested_ip: Mapped[str | None] = mapped_column(String(64), default=None)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False, index=True
    )

    user: Mapped[User] = relationship("User", back_populates="password_reset_tokens")


class ImportPreview(Base):
    __tablename__ = "import_previews"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    user_id: Mapped[str] = mapped_column(String(36), ForeignKey("users.id", ondelete="CASCADE"), index=True)
    mode: Mapped[str] = mapped_column(String(32), nullable=False)
    payload_json: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False, index=True
    )

    user: Mapped[User] = relationship("User", back_populates="import_previews")


class EmailServerConfig(Base):
    __tablename__ = "email_server_config"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    smtp_host: Mapped[str] = mapped_column(String(255), default="", nullable=False)
    smtp_port: Mapped[int] = mapped_column(Integer, default=587, nullable=False)
    smtp_username: Mapped[str | None] = mapped_column(String(255), default=None)
    smtp_password_encrypted: Mapped[str | None] = mapped_column(Text, default=None)
    from_email: Mapped[str] = mapped_column(String(255), default="", nullable=False)
    from_name: Mapped[str] = mapped_column(String(255), default="Stundenfuchs", nullable=False)
    use_starttls: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    use_ssl: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    verify_tls: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    registration_mails_enabled: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    password_reset_mails_enabled: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    registration_admin_notify_enabled: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    registration_admin_notify_admin_ids_csv: Mapped[str | None] = mapped_column(String(1024), default=None)
    updated_by_user_id: Mapped[str | None] = mapped_column(String(36), ForeignKey("users.id", ondelete="SET NULL"))
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc)
    )


class SiteContent(Base):
    __tablename__ = "site_content"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    key: Mapped[str] = mapped_column(String(64), unique=True, index=True, nullable=False)
    markdown_text: Mapped[str] = mapped_column(Text, default="", nullable=False)
    updated_by_user_id: Mapped[str | None] = mapped_column(String(36), ForeignKey("users.id", ondelete="SET NULL"))
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc)
    )


class SupportTicket(Base):
    __tablename__ = "support_tickets"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    user_id: Mapped[str | None] = mapped_column(String(36), ForeignKey("users.id", ondelete="SET NULL"), index=True)
    category: Mapped[str] = mapped_column(String(24), default="problem", nullable=False)
    status: Mapped[str] = mapped_column(String(24), default="open", nullable=False, index=True)
    name: Mapped[str] = mapped_column(String(255), default="", nullable=False)
    email: Mapped[str] = mapped_column(String(255), index=True, nullable=False)
    subject: Mapped[str] = mapped_column(String(255), nullable=False)
    message: Mapped[str] = mapped_column(Text, nullable=False)
    admin_notes: Mapped[str | None] = mapped_column(Text, default=None)
    source_ip_hash: Mapped[str | None] = mapped_column(String(128), index=True)
    source_user_agent: Mapped[str | None] = mapped_column(String(512), default=None)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False, index=True
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc)
    )
    closed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), default=None)

    user: Mapped[User | None] = relationship("User", back_populates="support_tickets", foreign_keys=[user_id])