"""saved_views — per-user named views over a filtered table (M8).

A saved view is a personal navigation shortcut: a name plus the URL
filter/sort state (a query string) for one of the app's filtered tables
(`entries`, `projects`, `cc`). It is the R1 "easier to navigate"
centerpiece and RETIRES the hardcoded quick-chips (09-build-plan §M8 /
§UX program). Views are PER-USER (keyed on the REAL user — personal
prefs, never impersonated) and carry a `pinned` flag so a user can pin
their recurring views into the nav.

Not tenant-scoped and NOT part of the tenant audit trail: a view holds
no tenant data, only a saved filter string the owner chose. Uniqueness
is per (user, page, lower(name)) so "Last month's PlusROI" is one view
per page for a given user. See ADR #009.
"""

import uuid
from datetime import datetime

from sqlalchemy import (
    Boolean,
    CheckConstraint,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    Text,
    Uuid,
    func,
    text,
)
from sqlalchemy.orm import Mapped, mapped_column

from app.models.base import Base

#: The filtered tables a view can belong to (the pages with a FilterBar).
SAVED_VIEW_PAGES = ("entries", "projects", "cc")


class SavedView(Base):
    __tablename__ = "saved_views"
    __table_args__ = (
        CheckConstraint(
            "page IN ('entries', 'projects', 'cc')", name="ck_saved_views_page"
        ),
        # One view name per (user, page), case-insensitive — the friendly
        # "already exists" guard is enforced by the DB, not just the service.
        Index(
            "uq_saved_views_user_page_lname",
            "user_id",
            "page",
            text("lower(name)"),
            unique=True,
        ),
        Index("ix_saved_views_user_page", "user_id", "page"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid(), primary_key=True, default=uuid.uuid4)

    # The OWNER (real user). ON DELETE CASCADE: a user's views die with them.
    user_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("users.id", ondelete="CASCADE"), nullable=False
    )

    # Which filtered table this view targets ('entries' | 'projects' | 'cc').
    page: Mapped[str] = mapped_column(Text(), nullable=False)

    name: Mapped[str] = mapped_column(Text(), nullable=False)

    # The saved URL filter/sort state as a query string (no leading '?').
    # e.g. "date=last-month&status=all&operator=PlusROI".
    search: Mapped[str] = mapped_column(Text(), nullable=False, default="")

    pinned: Mapped[bool] = mapped_column(Boolean(), nullable=False, default=False)

    # Stable ordering within a user's list (and their pinned nav strip).
    sort_order: Mapped[int] = mapped_column(Integer(), nullable=False, default=0)

    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()
    )