from datetime import datetime
from sqlalchemy import Boolean, Column, DateTime, ForeignKey, Integer, String, UniqueConstraint
from app.database import Base


class LabReference(Base):
    __tablename__ = "lab_reference_values"

    id = Column(Integer, primary_key=True)
    name = Column(String(120), nullable=False)
    group = Column(String(60), nullable=False)
    reference_range = Column(String(250), nullable=False)
    units = Column(String(80), nullable=False)
    age_group = Column(String(120), nullable=False)
    specimen = Column(String(120), nullable=False)
    source = Column(String(500), nullable=False)
    source_url = Column(String(2000), nullable=True)
    # Orthobullets-style deep link: jump straight to an article subsection.
    article_id = Column(Integer, ForeignKey("articles.id", ondelete="SET NULL"), nullable=True)
    article_section_id = Column(String(64), nullable=True)
    is_published = Column(Boolean, nullable=False, default=False, server_default="false")
    updated_by = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    updated_at = Column(DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow)


class LabReferenceCardLink(Base):
    __tablename__ = "lab_reference_card_links"
    __table_args__ = (UniqueConstraint("lab_reference_id", "flashcard_id", name="uq_lab_card_link"),)

    id = Column(Integer, primary_key=True)
    lab_reference_id = Column(Integer, ForeignKey("lab_reference_values.id", ondelete="CASCADE"), nullable=False)
    flashcard_id = Column(Integer, ForeignKey("flashcards.id", ondelete="CASCADE"), nullable=False)