mailova/app/models.py

from datetime import datetime, timezone

from sqlalchemy import Boolean, Column, DateTime, ForeignKey, Integer, String, Text, UniqueConstraint
from sqlalchemy.orm import relationship, validates

from app.database import Base


def _utcnow() -> datetime:
    return datetime.now(timezone.utc)


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(64), unique=True, nullable=False, index=True)
    password_hash = Column(String(128), nullable=False)
    is_admin = Column(Boolean, nullable=False, default=False)
    invited_by_code_id = Column(Integer, ForeignKey("invite_codes.id", ondelete="SET NULL"), nullable=True)
    showcase_mode = Column(String(16), nullable=False, default="profile")
    language = Column(String(4), nullable=False, default="zh")
    created_at = Column(DateTime, default=_utcnow)

    profiles = relationship("Profile", back_populates="user", cascade="all, delete-orphan")
    sessions = relationship("UserSession", back_populates="user", cascade="all, delete-orphan")
    invite_codes = relationship("InviteCode", back_populates="creator", foreign_keys="InviteCode.created_by", cascade="all, delete-orphan")
    invited_by_code = relationship("InviteCode", foreign_keys=[invited_by_code_id])
    api_keys = relationship("ApiKey", back_populates="user", cascade="all, delete-orphan")


class InviteCode(Base):
    __tablename__ = "invite_codes"

    id = Column(Integer, primary_key=True, index=True)
    code = Column(String(8), unique=True, nullable=False, index=True)
    max_uses = Column(Integer, nullable=True)  # None = unlimited
    expires_at = Column(DateTime, nullable=True)  # None = no expiration
    use_count = Column(Integer, nullable=False, default=0)
    created_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    created_at = Column(DateTime, default=_utcnow)

    creator = relationship("User", back_populates="invite_codes", foreign_keys=[created_by])
    registered_users = relationship("User", back_populates="invited_by_code", foreign_keys="User.invited_by_code_id")


class UserSession(Base):
    __tablename__ = "user_sessions"

    token = Column(String(64), primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    created_at = Column(DateTime, default=_utcnow)
    expires_at = Column(DateTime, nullable=False)

    user = relationship("User", back_populates="sessions")


class Profile(Base):
    __tablename__ = "profiles"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    nickname = Column(String(64), nullable=False)
    country = Column(String(2), nullable=True)
    showcase_enabled = Column(default=False, nullable=False)
    showcase_visible = Column(String(16), nullable=False, default="both")
    notes = Column(Text, nullable=False, default="")
    created_at = Column(DateTime, default=_utcnow)

    user = relationship("User", back_populates="profiles")
    postcards = relationship("Postcard", back_populates="profile", cascade="all, delete-orphan")


class Postcard(Base):
    __tablename__ = "postcards"
    __table_args__ = (
        UniqueConstraint("profile_id", "card_number", name="uq_postcards_profile_card_number"),
    )

    id = Column(Integer, primary_key=True, index=True)
    profile_id = Column(Integer, ForeignKey("profiles.id", ondelete="CASCADE"), nullable=False)
    card_number = Column(String(64), nullable=False)
    status = Column(String(16), nullable=False)
    # sent & delivered
    recipient_name = Column(String(64))
    country_from = Column(String(2))
    country_to = Column(String(2))
    send_time = Column(DateTime)
    arrival_time = Column(DateTime)
    # received
    sender_name = Column(String(64))
    receive_time = Column(DateTime)
    # misc
    notes = Column(Text, nullable=False, default="")
    # images
    showcase_hidden = Column(default=False, nullable=False)
    image_front = Column(String(256))
    image_back = Column(String(256))
    created_at = Column(DateTime, default=_utcnow)
    updated_at = Column(DateTime, default=_utcnow, onupdate=_utcnow)

    profile = relationship("Profile", back_populates="postcards")

    @validates("country_from")
    def _upper_country_from(self, key, value):
        return value.strip().upper() if value else None

    @validates("country_to")
    def _upper_country_to(self, key, value):
        return value.strip().upper() if value else None


class ApiKey(Base):
    __tablename__ = "api_keys"

    id = Column(Integer, primary_key=True, index=True)
    key = Column(String(64), unique=True, nullable=False, index=True)
    name = Column(String(64), nullable=False, default="")
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    is_active = Column(Boolean, nullable=False, default=True)
    permissions = Column(Text, nullable=False, default="[]")
    created_at = Column(DateTime, default=_utcnow)
    last_used_at = Column(DateTime, nullable=True)

    user = relationship("User", back_populates="api_keys")


class SystemConfig(Base):
    __tablename__ = "system_config"

    key = Column(String(64), primary_key=True)
    value = Column(Text, nullable=False, default="")
    updated_at = Column(DateTime, default=_utcnow, onupdate=_utcnow)


# ─── API Key Permission System ───────────────────────────────────

# Scopes: "resource:action"
# Empty list [] means full access (backward compatible).
PERM_PROFILES_READ = "profiles:read"
PERM_PROFILES_WRITE = "profiles:write"
PERM_POSTCARDS_READ = "postcards:read"
PERM_POSTCARDS_WRITE = "postcards:write"
PERM_IMAGES_UPLOAD = "images:upload"

ALL_PERMISSIONS = [
    PERM_PROFILES_READ,
    PERM_PROFILES_WRITE,
    PERM_POSTCARDS_READ,
    PERM_POSTCARDS_WRITE,
    PERM_IMAGES_UPLOAD,
]

PERMISSION_GROUPS: dict[str, list[str]] = {
    "profiles": [PERM_PROFILES_READ, PERM_PROFILES_WRITE],
    "postcards": [PERM_POSTCARDS_READ, PERM_POSTCARDS_WRITE],
    "images": [PERM_IMAGES_UPLOAD],
}