from datetime import datetime from sqlalchemy import (Boolean, Column, Integer, String, Text, JSON, DateTime, ForeignKey, UniqueConstraint) from sqlalchemy.orm import relationship from app.database import Base from app.models.embeddable import Embeddable class Article(Base, Embeddable): """Educator-authored topic reading with stable section IDs for linking.""" __tablename__ = "articles" id = Column(Integer, primary_key=True, index=True) slug = Column(String(120), unique=True, nullable=False, index=True) title = Column(String(300), nullable=False) summary = Column(Text, nullable=True) content = Column(Text, nullable=True) # Whole-article introduction (markdown). # Stable subsections: [{"id": uuid-hex, "slug": "...", "title": "...", "content": "markdown"}]. sections = Column(JSON, nullable=False, default=list) category_id = Column(Integer, ForeignKey("question_categories.id", ondelete="SET NULL"), nullable=True) section_id = Column(Integer, ForeignKey("sections.id", ondelete="SET NULL"), nullable=True) # Optional PDF source range. user_id = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"), nullable=True) status = Column(String, default="draft") # draft | in_review | published submitted_at = Column(DateTime, nullable=True) reviewed_at = Column(DateTime, nullable=True) reviewed_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"), nullable=True) # Sources the article was written from. A list, not in-text markers. references_json = Column(JSON, nullable=True) # What wrote it, so a machine-written draft stays visibly one until an # educator has been through it. generated_by = Column(String(80), nullable=True) generated_at = Column(DateTime, nullable=True) # The first time this was published, and never cleared afterwards. It is # what decides whether deleting is reversible: an article the world has # seen goes to the trash, a draft nobody ever saw is simply gone. first_published_at = Column(DateTime, nullable=True) # In the trash. Set rather than deleted, so restoring is one click and not # a database restore. deleted_at = Column(DateTime, nullable=True) deleted_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"), nullable=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) category = relationship("QuestionCategory") links = relationship("QuestionArticleLink", back_populates="article", cascade="all, delete-orphan") class QuestionArticleLink(Base): """Question -> whole article or one stable section.""" __tablename__ = "question_article_links" __table_args__ = (UniqueConstraint("question_id", "article_id", "section_id", name="uq_question_article_section"),) id = Column(Integer, primary_key=True, index=True) question_id = Column(Integer, ForeignKey("questions.id", ondelete="CASCADE"), nullable=False) article_id = Column(Integer, ForeignKey("articles.id", ondelete="CASCADE"), nullable=False) section_id = Column(String(64), nullable=True) # Article.sections[].id; None = whole article. user_id = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"), nullable=True) created_at = Column(DateTime, default=datetime.utcnow) article = relationship("Article", back_populates="links") class ArticleTopicClaim(Base): """An article claims a category: every question filed there links to it. The alternative was a one-off copy, and it was wrong for the thing people actually do here. "The Cardiology article covers the Cardiology questions" is a *standing* statement, not a snapshot of who was in the room in September — and a copy quietly stops being true the first time somebody files a new question, with nothing on any screen to say so. So this table holds the claim, and `question_article_links` is still the only thing anything reads. The rows are *materialised* from the claim: made when it is staked, and made again for a question the moment it is filed into the category. One source of truth to read, one place that writes it. A link made this way is an ordinary row and can be removed one at a time — but re-syncing puts it back, which is the honest consequence of a standing claim. Dropping the claim is how you stop it. """ __tablename__ = "article_topic_claims" __table_args__ = (UniqueConstraint("article_id", "category_id", "section_id", name="uq_article_topic_claim"),) id = Column(Integer, primary_key=True, index=True) article_id = Column(Integer, ForeignKey("articles.id", ondelete="CASCADE"), nullable=False, index=True) category_id = Column(Integer, ForeignKey("question_categories.id", ondelete="CASCADE"), nullable=False, index=True) #: Where the links land: a section of the article, or the whole of it. section_id = Column(String(64), nullable=True) #: Whether the claim reaches the category's subtopics, which is what an #: educator usually means by the name of a discipline. include_subtopics = Column(Boolean, nullable=False, default=True) user_id = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"), nullable=True) created_at = Column(DateTime, default=datetime.utcnow) class ArticleRevision(Base): """A snapshot of an article as it was before a save. Kept for the same reason question versions are: an editor who breaks something at 2am needs to get back to what was there, and a diff nobody can reach is not a safety net. """ __tablename__ = "article_revisions" id = Column(Integer, primary_key=True, index=True) article_id = Column(Integer, ForeignKey("articles.id", ondelete="CASCADE"), nullable=False, index=True) title = Column(String(300), nullable=False) summary = Column(Text, nullable=True) content = Column(Text, nullable=True) sections = Column(JSON, nullable=False, default=list) references_json = Column(JSON, nullable=True) status = Column(String(20), nullable=True) note = Column(String(200), nullable=True) created_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"), nullable=True) created_at = Column(DateTime, default=datetime.utcnow) class ArticleSlug(Base): """Every slug an article has ever had. A cross-reference written today must survive a rename tomorrow. Old slugs redirect rather than 404, so the author who renamed something does not silently break every article that pointed at it. """ __tablename__ = "article_slugs" id = Column(Integer, primary_key=True, index=True) slug = Column(String(120), unique=True, nullable=False, index=True) article_id = Column(Integer, ForeignKey("articles.id", ondelete="CASCADE"), nullable=False, index=True) created_at = Column(DateTime, default=datetime.utcnow) class ArticleView(Base): """Last time a learner opened an article — one row per user and article.""" __tablename__ = "article_views" __table_args__ = (UniqueConstraint("user_id", "article_id", name="uq_article_view"),) id = Column(Integer, primary_key=True, index=True) user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) article_id = Column(Integer, ForeignKey("articles.id", ondelete="CASCADE"), nullable=False) viewed_at = Column(DateTime, default=datetime.utcnow) class ArticleSectionIndex(Base, Embeddable): """One row per article section, so retrieval can cite a section not a whole article. Sections are stored in `Article.sections` as JSON; this is a projection of them, rebuilt whenever the article is saved. """ __tablename__ = "article_section_index" __table_args__ = (UniqueConstraint("article_id", "section_id", name="uq_article_section_index"),) id = Column(Integer, primary_key=True, index=True) article_id = Column(Integer, ForeignKey("articles.id", ondelete="CASCADE"), nullable=False, index=True) section_id = Column(String(64), nullable=False) title = Column(Text, nullable=True) content = Column(Text, nullable=True)