"""The client's to-do list and the files they hand us.

Sources of truth: this module and alembic/versions/b7c8d9e0f1a2_client_todos.py. Seeded
by main/scripts/seed-todos.py from the accepted quote's NEEDED list.

Who-columns keep the typed name as the raw record beside an `*_id` FK to accounts (build
plan §2, who-columns rule), written from the session since the app's own login. Uploads
are stored on disk under the workspace data/ directory, never inside the instance or a
static mount, and are only ever served through the authenticated-by-gate download route.
"""

from datetime import datetime

from sqlalchemy import Boolean, DateTime, ForeignKey, Integer, String, Text, func
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.models.base import Base


class ClientTodo(Base):
    __tablename__ = "client_todos"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    # adam | mark | both
    owner: Mapped[str] = mapped_column(String(16), nullable=False, index=True)
    # todo | question. A question is answered in its comment thread, then marked answered.
    kind: Mapped[str] = mapped_column(String(12), nullable=False, default="todo", server_default="todo")
    # Only items with a lot of text (articles, category paragraphs) take files; the rest are
    # comments. Images go to the shared drive, not here.
    accepts_files: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, server_default="false")
    title: Mapped[str] = mapped_column(String(200), nullable=False)
    detail: Mapped[str | None] = mapped_column(Text)
    # ISO date the item is wanted by; text so a "Fri 11 Sep" can be shown as typed.
    due: Mapped[str | None] = mapped_column(String(40))
    # open | done | waiting (not ready for the client yet; shown greyed with the detail as the reason)
    status: Mapped[str] = mapped_column(String(12), nullable=False, default="open", server_default="open")
    sort: Mapped[int] = mapped_column(Integer, nullable=False, default=0, server_default="0")
    completed_by: Mapped[str | None] = mapped_column(String(120))
    completed_by_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False)

    uploads: Mapped[list["ClientUpload"]] = relationship(
        back_populates="todo", cascade="all, delete-orphan", order_by="ClientUpload.created_at"
    )


class ClientUpload(Base):
    __tablename__ = "client_uploads"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    todo_id: Mapped[int] = mapped_column(ForeignKey("client_todos.id", ondelete="CASCADE"), index=True, nullable=False)
    original_name: Mapped[str] = mapped_column(String(255), nullable=False)
    # Random name on disk; the original name is never used as a path.
    stored_name: Mapped[str] = mapped_column(String(80), nullable=False, unique=True)
    content_type: Mapped[str] = mapped_column(String(80), nullable=False)
    kind: Mapped[str] = mapped_column(String(16), nullable=False)  # image | document | text
    bytes: Mapped[int] = mapped_column(Integer, nullable=False)
    uploaded_by: Mapped[str] = mapped_column(String(120), nullable=False)
    uploaded_by_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    note: Mapped[str | None] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False)

    todo: Mapped[ClientTodo] = relationship(back_populates="uploads")