from datetime import datetime

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

from app.database import Base


class UserNote(Base):
    __tablename__ = "user_notes"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), unique=True, nullable=False, index=True)
    content = Column(Text, nullable=False, default="")
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)

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


class QuestionNote(Base):
    """A learner's own note on one question.

    Separate from the single global note, which was one page for everything and
    so was never about the question in front of you.
    """

    __tablename__ = "question_notes"
    __table_args__ = (UniqueConstraint("user_id", "question_id", name="uq_question_note"),)

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True)
    question_id = Column(Integer, ForeignKey("questions.id", ondelete="CASCADE"), nullable=False, index=True)
    content = Column(Text, nullable=False, default="")
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)


class ArticleSectionNote(Base):
    """A learner's own note on one section of an article.

    The same thing as `QuestionNote` pointed at different reading: one row per
    person per section, empty means deleted, and nobody but the writer ever
    reads it. Feedback to the article's maintainer is a separate table, because
    a private note that could be read by an educator is not a private note.

    `section_id` is a key inside `articles.sections`, deliberately not a foreign
    key onto `article_section_index`: those rows are dropped when an article is
    unpublished, and a cascade from them would delete a learner's writing
    because an educator took an article down for an afternoon.

    A section can be renamed (the id survives, so the note stays attached) or
    deleted (the id goes, and the note has nowhere to render). It is kept
    either way and reported as orphaned, with the heading it was written under
    recorded here — losing somebody's writing quietly is worse than showing
    them a note whose section has gone.
    """

    __tablename__ = "article_section_notes"
    __table_args__ = (UniqueConstraint("user_id", "article_id", "section_id",
                                       name="uq_article_section_note"),)

    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, index=True)
    section_id = Column(String(64), nullable=False)
    section_title = Column(String(300), nullable=True)
    content = Column(Text, nullable=False, default="")
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)