"""Catalog models: species + varieties.

Near-verbatim port of the v1 tables (see
``data/v1-snapshot/v1_schema_pragma.txt``) plus the approved planner columns.

Port notes:

- ``species`` gains ``garden_id`` (deviation D1); v1's ``UNIQUE(name)``
  becomes ``UniqueConstraint(garden_id, name)``.
- ``varieties`` is scoped through ``species`` — NO ``garden_id``.
- ``varieties.plants_per_unit`` / ``space_per_unit_sqft`` are NULLABLE
  overrides of the species values (COALESCE resolution in v1), so no
  server defaults — unlike the species columns, which default to 1 / 1.0.
- ``featured_image_path`` stays Text (photo-path wart, kept on purpose).
- No CheckConstraints here: v1 code did not enforce a vocabulary for
  ``species.type`` / ``primary_function`` beyond defaults.
"""
from __future__ import annotations

from datetime import datetime

import sqlalchemy as sa
from sqlalchemy import BigInteger, Double, ForeignKey, Integer, Text, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column

from app.models.base import Base, created_at_column, garden_fk, pk_column


class Species(Base):
    __tablename__ = "species"
    __table_args__ = (
        # v1: UNIQUE(name) — widened to per-garden uniqueness (deviation D1).
        UniqueConstraint("garden_id", "name"),
        # v1 index carried: idx_species_archived
        sa.Index("idx_species_archived", "is_archived"),
    )

    id: Mapped[int] = pk_column()
    garden_id: Mapped[int] = garden_fk()

    # --- carried v1 columns (order per pragma) ---
    name: Mapped[str] = mapped_column(Text, nullable=False)
    common_name: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("''")
    )
    description: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("''")
    )
    created_at: Mapped[datetime] = created_at_column()
    featured_image_path: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("''")
    )
    plants_per_unit: Mapped[int | None] = mapped_column(
        Integer, nullable=True, server_default=sa.text("1")
    )
    space_per_unit_sqft: Mapped[float | None] = mapped_column(
        Double, nullable=True, server_default=sa.text("1.0")
    )
    is_archived: Mapped[bool | None] = mapped_column(
        sa.Boolean, nullable=True, server_default=sa.text("false")
    )
    type: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("'plant'")
    )
    primary_function: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("'edible'")
    )

    # --- new planner columns (v2) ---
    mature_spacing_sqft: Mapped[float | None] = mapped_column(Double, nullable=True)
    rotation_family: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("''")
    )
    sun_pref: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("''")
    )
    life_cycle: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("''")
    )
    edible_part: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("''")
    )
    notes_local: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("''")
    )


class Variety(Base):
    __tablename__ = "varieties"
    __table_args__ = (
        # v1 index carried: idx_varieties_archived
        sa.Index("idx_varieties_archived", "is_archived"),
    )

    id: Mapped[int] = pk_column()
    # NO garden_id — varieties are garden-scoped via their species.

    # --- carried v1 columns (order per pragma) ---
    species_id: Mapped[int | None] = mapped_column(
        BigInteger,
        ForeignKey("species.id", ondelete="SET NULL"),
        nullable=True,
    )
    name: Mapped[str] = mapped_column(Text, nullable=False)
    description: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("''")
    )
    created_at: Mapped[datetime] = created_at_column()
    featured_image_path: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("''")
    )
    # Nullable overrides of the species-level spacing (no defaults — NULL
    # means "fall through to species" via COALESCE, exactly as in v1).
    plants_per_unit: Mapped[int | None] = mapped_column(Integer, nullable=True)
    space_per_unit_sqft: Mapped[float | None] = mapped_column(Double, nullable=True)
    is_archived: Mapped[bool | None] = mapped_column(
        sa.Boolean, nullable=True, server_default=sa.text("false")
    )

    # --- new planner columns (v2) ---
    mature_spacing_sqft: Mapped[float | None] = mapped_column(Double, nullable=True)
    harvest_window: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("''")
    )
    flavor_notes: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("''")
    )