"""Scouting projects and their membership.

A scouting project is one engagement: a client, the design question being
explored, and the people who may see it. Membership is the access boundary — a
non-admin sees a project only when they hold a row in project_members.
"""

from datetime import datetime

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

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


class Project(Base):
    __tablename__ = "projects"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    slug: Mapped[str] = mapped_column(String(80), unique=True, index=True)
    name: Mapped[str] = mapped_column(String(160))
    client_name: Mapped[str] = mapped_column(String(160), default="", server_default="")

    # What the client is trying to get clarity on — the framing shown to them.
    brief: Mapped[str] = mapped_column(Text, default="", server_default="")

    # The client's current site — the baseline the redesign moves away from.
    client_website: Mapped[str] = mapped_column(String(500), default="", server_default="")

    status: Mapped[str] = mapped_column(
        String(32), default=ProjectStatus.DRAFT, server_default=ProjectStatus.DRAFT
    )

    created_by: Mapped[str] = mapped_column(
        String(64), ForeignKey("app_accounts.username", ondelete="RESTRICT")
    )
    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()
    )

    members: Mapped[list["ProjectMember"]] = relationship(
        back_populates="project", cascade="all, delete-orphan", lazy="selectin"
    )


class ProjectMember(Base):
    __tablename__ = "project_members"

    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
    )
    # The level this person holds ON THIS PROJECT — the kit's "grant". It wins
    # over the account's app-wide level (resolution order in levels.py).
    level: Mapped[str] = mapped_column(String(40), default="reviewer", server_default="reviewer")
    added_by: Mapped[str] = mapped_column(String(64))
    added_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())

    project: Mapped[Project] = relationship(back_populates="members")


Index("ix_project_members_username", ProjectMember.username)