from datetime import datetime
from sqlalchemy import Column, Integer, String, Text, DateTime, ForeignKey, UniqueConstraint
from sqlalchemy.orm import relationship
from app.database import Base
from app.models.embeddable import Embeddable

class FlashcardDeck(Base):
    __tablename__ = "flashcard_decks"
    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, nullable=False)
    section_id = Column(Integer, ForeignKey("sections.id", ondelete="SET NULL"), nullable=True)
    # Same tree as questions and articles, so one category filters all three.
    category_id = Column(Integer, ForeignKey("question_categories.id", ondelete="SET NULL"), nullable=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=True)
    card_count = Column(Integer, default=0)
    is_shared = Column(Integer, default=0)  # 0 = private, 1 = shared
    created_at = Column(DateTime, default=datetime.utcnow)
    deleted_at = Column(DateTime, nullable=True)
    cards = relationship("Flashcard", back_populates="deck", cascade="all, delete-orphan")
    ratings = relationship("FlashcardDeckRating", back_populates="deck", cascade="all, delete-orphan")
    user = relationship("User")


class FlashcardDeckRating(Base):
    __tablename__ = "flashcard_deck_ratings"
    __table_args__ = (UniqueConstraint("user_id", "deck_id", name="uq_deck_rating_user"),)
    id = Column(Integer, primary_key=True, index=True)
    deck_id = Column(Integer, ForeignKey("flashcard_decks.id", ondelete="CASCADE"), nullable=False)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    rating = Column(Integer, nullable=False)  # 1-5
    created_at = Column(DateTime, default=datetime.utcnow)
    deck = relationship("FlashcardDeck", back_populates="ratings")

class Flashcard(Base, Embeddable):
    __tablename__ = "flashcards"
    id = Column(Integer, primary_key=True, index=True)
    deck_id = Column(Integer, ForeignKey("flashcard_decks.id", ondelete="CASCADE"), nullable=False)
    front = Column(Text, nullable=False)
    back = Column(Text, nullable=False)
    page_reference = Column(Integer, nullable=True)
    image_path = Column(String, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    deck = relationship("FlashcardDeck", back_populates="cards")


class FlashcardReview(Base):
    """One verdict on one card, kept so the deck can come back at the right time.

    Cards had no memory at all: "known" and "to review" were React state that
    vanished on reload, so a deck of two hundred was two hundred cards every
    time and the only spacing was whichever ones you happened to remember to
    skip.

    A log rather than a row per card. What the scheduler needs is the *latest*
    verdict and how long ago it was, which a log gives; and keeping the history
    means a card answered wrongly three times in a row can eventually be
    treated differently from one missed once, without a migration to add the
    column that would have recorded it.
    """

    __tablename__ = "flashcard_reviews"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True)
    flashcard_id = Column(Integer, ForeignKey("flashcards.id", ondelete="CASCADE"),
                          nullable=False, index=True)
    #: "known" or "again". Two outcomes, because a self-graded scale of four
    #: asks a learner to rate their own recall on a scale they have not
    #: calibrated, and the extra resolution is noise.
    outcome = Column(String(10), nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow, index=True)


class FlashcardQuestionLink(Base):
    __tablename__ = "flashcard_question_links"
    __table_args__ = (UniqueConstraint("flashcard_id", "question_id", name="uq_card_question"),)

    id = Column(Integer, primary_key=True, index=True)
    flashcard_id = Column(Integer, ForeignKey("flashcards.id", ondelete="CASCADE"), nullable=False)
    question_id = Column(Integer, ForeignKey("questions.id", ondelete="CASCADE"), nullable=False)


class FlashcardArticleLink(Base):
    __tablename__ = "flashcard_article_links"
    __table_args__ = (UniqueConstraint("flashcard_id", "article_id", "article_section_id", name="uq_card_article_section"),)

    id = Column(Integer, primary_key=True, index=True)
    flashcard_id = Column(Integer, ForeignKey("flashcards.id", ondelete="CASCADE"), nullable=False)
    article_id = Column(Integer, ForeignKey("articles.id", ondelete="CASCADE"), nullable=False)
    article_section_id = Column(String(64), nullable=True)