"""Design-direction options and the per-person reactions to them.

An option is one candidate site: anonymized for clients ("Option C" + a neutral
descriptor + screenshots, D8), fully attributed for admins. Reviews, aspect
votes, and final picks are all keyed per person (D7 — never shared, never
averaged in front of the client) and upserted on every tap (D12 — autosave, no
submit cliff).
"""

from datetime import datetime

from sqlalchemy import (
    Boolean,
    DateTime,
    ForeignKey,
    Integer,
    String,
    Text,
    UniqueConstraint,
    func,
)
from sqlalchemy.orm import Mapped, mapped_column

from app.constants import OptionStatus
from app.models.base import Base


class Option(Base):
    __tablename__ = "options"
    # The import contract is idempotent by slug within a project (re-upload
    # updates, never duplicates), which is exactly this constraint.
    __table_args__ = (UniqueConstraint("project_id", "slug", name="uq_options_project_slug"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    project_id: Mapped[int] = mapped_column(
        Integer, ForeignKey("projects.id", ondelete="CASCADE"), index=True
    )

    slug: Mapped[str] = mapped_column(String(80))
    display_label: Mapped[str] = mapped_column(String(40))  # "Option A"
    descriptor: Mapped[str] = mapped_column(String(160), default="", server_default="")

    # The real source. Admin-facing unless reveal_source is flipped (D8).
    source_name: Mapped[str] = mapped_column(String(200), default="", server_default="")
    source_url: Mapped[str] = mapped_column(String(500), default="", server_default="")
    why_selected: Mapped[str] = mapped_column(Text, default="", server_default="")
    design_notes: Mapped[str] = mapped_column(Text, default="", server_default="")
    reveal_source: Mapped[bool] = mapped_column(Boolean, default=False, server_default="false")

    status: Mapped[str] = mapped_column(
        String(16), default=OptionStatus.DRAFT, server_default=OptionStatus.DRAFT
    )
    sort_order: Mapped[int] = mapped_column(Integer, default=0, server_default="0")

    # Relative paths under the data root; empty when not captured.
    screenshot_desktop: Mapped[str] = mapped_column(String(300), default="", server_default="")
    screenshot_mobile: Mapped[str] = mapped_column(String(300), default="", server_default="")

    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class Review(Base):
    """One person's gut reaction to one option. rating is nullable: a note or an
    aspect vote can exist before they have committed to a rating."""

    __tablename__ = "reviews"

    option_id: Mapped[int] = mapped_column(
        Integer, ForeignKey("options.id", ondelete="CASCADE"), primary_key=True
    )
    username: Mapped[str] = mapped_column(
        String(64), ForeignKey("app_accounts.username", ondelete="CASCADE"), primary_key=True
    )
    project_id: Mapped[int] = mapped_column(
        Integer, ForeignKey("projects.id", ondelete="CASCADE"), index=True
    )

    rating: Mapped[int | None] = mapped_column(Integer, nullable=True)  # 0–3 (D9)
    note: Mapped[str] = mapped_column(Text, default="", server_default="")
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class AspectVote(Base):
    """A thumbs up/down on one fixed aspect of one option. vote is -1 or +1;
    clearing a vote deletes the row rather than storing a 0."""

    __tablename__ = "aspect_votes"

    option_id: Mapped[int] = mapped_column(
        Integer, ForeignKey("options.id", ondelete="CASCADE"), primary_key=True
    )
    username: Mapped[str] = mapped_column(
        String(64), ForeignKey("app_accounts.username", ondelete="CASCADE"), primary_key=True
    )
    aspect: Mapped[str] = mapped_column(String(32), primary_key=True)  # ASPECT_LABELS key
    vote: Mapped[int] = mapped_column(Integer)  # -1 | +1
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class FinalPick(Base):
    """The closing question: which option is closest to how *your* site should
    feel. completed_at is set when they tap Finish; reopening does not clear it."""

    __tablename__ = "final_picks"

    project_id: Mapped[int] = mapped_column(
        Integer, ForeignKey("projects.id", ondelete="CASCADE"), primary_key=True
    )
    username: Mapped[str] = mapped_column(
        String(64), ForeignKey("app_accounts.username", ondelete="CASCADE"), primary_key=True
    )

    option_id: Mapped[int | None] = mapped_column(
        Integer, ForeignKey("options.id", ondelete="CASCADE"), nullable=True
    )
    closing_note: Mapped[str] = mapped_column(Text, default="", server_default="")
    completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )