wordloop/backend/models.py

from sqlalchemy import Column, Float, ForeignKey, Integer, String, Text, UniqueConstraint

from database import Base


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(255), nullable=False)
    created_at = Column(String(32), nullable=False)


class WordBook(Base):
    """全局词书目录（部署时导入）。"""

    __tablename__ = "word_books"

    id = Column(Integer, primary_key=True, autoincrement=True)
    slug = Column(String(64), unique=True, nullable=False, index=True)
    title = Column(String(128), nullable=False)
    description = Column(Text, nullable=True)
    level = Column(String(32), nullable=False)
    word_count = Column(Integer, nullable=False, default=0)
    unit_count = Column(Integer, nullable=False, default=0)
    sort_order = Column(Integer, nullable=False, default=0)
    daily_target = Column(Integer, nullable=False, default=15)
    master_required_count = Column(Integer, nullable=False, default=4)
    weak_wrong_threshold = Column(Integer, nullable=False, default=2)
    learn_mode = Column(String(32), nullable=False, default="sequential")
    is_published = Column(Integer, nullable=False, default=1)


class WordBookEntry(Base):
    __tablename__ = "word_book_entries"
    __table_args__ = (UniqueConstraint("book_id", "lemma_en", name="uq_book_lemma"),)

    id = Column(Integer, primary_key=True, autoincrement=True)
    book_id = Column(Integer, ForeignKey("word_books.id"), nullable=False, index=True)
    unit = Column(Integer, nullable=False, default=1)
    unit_title = Column(String(128), nullable=True)
    sort_index = Column(Integer, nullable=False, index=True)
    lemma_en = Column(String(128), nullable=False)
    zh = Column(String(256), nullable=False)
    phonetic = Column(String(128), nullable=True)
    example_en = Column(Text, nullable=True)
    example_cn = Column(Text, nullable=True)


class Word(Base):
    __tablename__ = "words"
    __table_args__ = (
        UniqueConstraint(
            "user_id", "book_id", "source_text", "target_text", name="uq_user_book_word"
        ),
    )

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
    book_id = Column(Integer, nullable=False, default=0, index=True)
    book_entry_id = Column(Integer, ForeignKey("word_book_entries.id"), nullable=True)
    source_text = Column(String(500), nullable=False)
    target_text = Column(String(500), nullable=False)
    source_lang = Column(String(8), nullable=False)
    target_lang = Column(String(8), nullable=False)
    phonetic = Column(String(128), nullable=True)
    example_en = Column(Text, nullable=True)
    example_cn = Column(Text, nullable=True)
    status = Column(String(32), nullable=False, default="new")
    correct_count = Column(Integer, nullable=False, default=0)
    wrong_count = Column(Integer, nullable=False, default=0)
    consecutive_correct_count = Column(Integer, nullable=False, default=0)
    mastery_score = Column(Integer, nullable=False, default=0)
    train_count = Column(Integer, nullable=False, default=0)
    error_bank_member = Column(Integer, nullable=False, default=0)
    error_reinforce_streak = Column(Integer, nullable=False, default=0)
    error_bank_entered_at = Column(String(32), nullable=True)
    total_train_seconds = Column(Integer, nullable=False, default=0)
    difficulty = Column(Float, nullable=False, default=5.0)
    stability_hours = Column(Float, nullable=False, default=24.0)
    next_review_at = Column(String(32), nullable=True)
    review_due_date = Column(String(32), nullable=True)
    last_reviewed_at = Column(String(32), nullable=True)
    created_at = Column(String(32), nullable=False)


class QuizRecord(Base):
    __tablename__ = "quiz_records"

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
    word_id = Column(Integer, ForeignKey("words.id"), nullable=False)
    question_type = Column(String(32), nullable=False)
    user_answer = Column(String(500), nullable=False)
    correct_answer = Column(String(500), nullable=False)
    is_correct = Column(Integer, nullable=False)
    duration_seconds = Column(Integer, nullable=False, default=0)
    created_at = Column(String(32), nullable=False)


class UserSettings(Base):
    __tablename__ = "user_settings"

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.id"), unique=True, nullable=False)
    daily_target = Column(Integer, nullable=False, default=20)
    master_required_count = Column(Integer, nullable=False, default=3)
    weak_wrong_threshold = Column(Integer, nullable=False, default=3)
    error_clear_correct_count = Column(Integer, nullable=False, default=2)
    active_book_id = Column(Integer, ForeignKey("word_books.id"), nullable=True)
    ai_wiki_enabled = Column(Integer, nullable=False, default=0)
    ai_wiki_auto_organize = Column(Integer, nullable=False, default=0)


class UserBookSettings(Base):
    """用户对某本词书的练习参数（每本书独立保存）。"""

    __tablename__ = "user_book_settings"
    __table_args__ = (UniqueConstraint("user_id", "book_id", name="uq_user_book_settings"),)

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
    book_id = Column(Integer, ForeignKey("word_books.id"), nullable=False, index=True)
    daily_target = Column(Integer, nullable=False, default=12)
    master_required_count = Column(Integer, nullable=False, default=3)
    weak_wrong_threshold = Column(Integer, nullable=False, default=2)
    error_clear_correct_count = Column(Integer, nullable=False, default=2)


class DictionaryEntry(Base):
    """全局离线翻译词典，与用户个人 words 表分离。"""

    __tablename__ = "dictionary_entries"

    id = Column(Integer, primary_key=True, autoincrement=True)
    lemma_en = Column(String(128), unique=True, nullable=False, index=True)
    zh = Column(String(256), nullable=False, index=True)
    phonetic = Column(String(128), nullable=True)
    example_en = Column(Text, nullable=True)
    example_cn = Column(Text, nullable=True)
    source = Column(String(64), nullable=True)


class WikiSource(Base):
    """Immutable learning event ingested into a user's Wiki."""

    __tablename__ = "wiki_sources"
    __table_args__ = (
        UniqueConstraint("user_id", "source_key", name="uq_wiki_source_user_key"),
    )

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
    word_id = Column(Integer, ForeignKey("words.id"), nullable=True, index=True)
    source_key = Column(String(96), nullable=False)
    source_type = Column(String(32), nullable=False)
    payload = Column(Text, nullable=False)
    created_at = Column(String(32), nullable=False)


class WikiPage(Base):
    """Compiled Markdown page owned by one user."""

    __tablename__ = "wiki_pages"
    __table_args__ = (UniqueConstraint("user_id", "slug", name="uq_wiki_page_user_slug"),)

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
    word_id = Column(Integer, ForeignKey("words.id"), nullable=True, index=True)
    slug = Column(String(160), nullable=False)
    title = Column(String(500), nullable=False)
    category = Column(String(32), nullable=False)
    summary = Column(Text, nullable=False)
    content = Column(Text, nullable=False)
    source_count = Column(Integer, nullable=False, default=0)
    created_at = Column(String(32), nullable=False)
    updated_at = Column(String(32), nullable=False)


class WikiLink(Base):
    __tablename__ = "wiki_links"
    __table_args__ = (
        UniqueConstraint(
            "user_id",
            "source_page_id",
            "target_page_id",
            "relation",
            name="uq_wiki_link_edge",
        ),
    )

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
    source_page_id = Column(Integer, ForeignKey("wiki_pages.id"), nullable=False, index=True)
    target_page_id = Column(Integer, ForeignKey("wiki_pages.id"), nullable=False, index=True)
    relation = Column(String(32), nullable=False)
    created_at = Column(String(32), nullable=False)


class WikiLog(Base):
    __tablename__ = "wiki_logs"

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
    action = Column(String(32), nullable=False)
    page_slug = Column(String(160), nullable=True)
    detail = Column(Text, nullable=False)
    created_at = Column(String(32), nullable=False)


class WikiLlmUsage(Base):
    """Per-account daily DeepSeek call counter for Wiki compilation."""

    __tablename__ = "wiki_llm_usage"
    __table_args__ = (
        UniqueConstraint("user_id", "usage_date", name="uq_wiki_llm_usage_day"),
    )

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
    usage_date = Column(String(10), nullable=False)
    call_count = Column(Integer, nullable=False, default=0)