"""Identity & multi-garden scoping tables (NEW in v2 — not carried from v1).

v1 was single-user/single-garden; these tables introduce users, gardens, and
the membership join that ``garden_fk()`` (deviation D1) points every scoped
entity at.
"""
from __future__ import annotations

from datetime import datetime
from typing import Any

import sqlalchemy as sa
from sqlalchemy import (
    BigInteger,
    CheckConstraint,
    DateTime,
    Double,
    ForeignKey,
    Text,
)
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm import Mapped, mapped_column

from .base import Base, created_at_column, pk_column


class User(Base):
    """An authenticated person. ``external_username`` is the id-auth username."""

    __tablename__ = "users"

    id: Mapped[int] = pk_column()
    external_username: Mapped[str] = mapped_column(
        Text, nullable=False, unique=True
    )
    display_name: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("''")
    )
    email: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("''")
    )
    settings: Mapped[dict[str, Any]] = mapped_column(
        JSONB, nullable=False, server_default=sa.text("'{}'::jsonb")
    )
    # App-defined access level, reported to the BW hub via report_role
    # (display-only there — this app is the sole authority on what it grants).
    # Distinct from garden_members.role (owner/member membership). Seeded on
    # first login from INITIAL_ACCESS_LEVELS; never clobbered on re-login.
    access_level: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("''")
    )
    # App-only profile field — NEVER sent to BW Auth; proves the app can hold
    # account info outside BW's scope. Preserved across logins.
    city: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("''")
    )
    created_at: Mapped[datetime] = created_at_column()
    last_seen_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), nullable=True
    )


class Garden(Base):
    """A physical garden. Every scoped entity row carries a garden_id (D1)."""

    __tablename__ = "gardens"

    id: Mapped[int] = pk_column()
    name: Mapped[str] = mapped_column(Text, nullable=False)
    timezone: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("'America/Vancouver'")
    )
    latitude: Mapped[float | None] = mapped_column(Double, nullable=True)
    longitude: Mapped[float | None] = mapped_column(Double, nullable=True)
    settings: Mapped[dict[str, Any]] = mapped_column(
        JSONB, nullable=False, server_default=sa.text("'{}'::jsonb")
    )
    created_at: Mapped[datetime] = created_at_column()


class GardenMember(Base):
    """Membership join: which users belong to which gardens, and their role.

    ``role`` vocabulary (enforced by CHECK, no native PG enum): 'owner', 'member'.
    """

    __tablename__ = "garden_members"
    __table_args__ = (
        CheckConstraint("role IN ('owner', 'member')", name="role"),
    )

    garden_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("gardens.id"), primary_key=True
    )
    user_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("users.id"), primary_key=True
    )
    role: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = created_at_column()