"""Planting models: plantings + plants.

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

Port notes / warts kept on purpose ("don't improve a mature schema"):

- ``plants.quantity`` STAYS Text — v1 holds free-form values ("3", "a few",
  "2 rows"). Do not "fix" to Integer.
- ``plants.positions`` STAYS Text — v1 default is ``''`` which is invalid
  JSON, so it cannot be JSONB without rewriting history.
- ``featured_image_path`` stays Text (photo-path wart).
- ``plants.status`` gets a CheckConstraint — v1 code enforced the vocabulary
  ('idea','unplanted','planted','removed').
- ``plantings.status`` gets NO check — v1 history contains legacy values;
  only the Text default 'planted' is carried.
- Both tables gain ``garden_id`` (deviation D1): plantings and plants are
  entity tables in their own right (plants can exist with NULL planting_id
  and NULL area_id, so scoping must be direct).
"""
from __future__ import annotations

from datetime import datetime

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

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


class Planting(Base):
    __tablename__ = "plantings"
    __table_args__ = (
        # v1 index carried: idx_plantings_archived
        sa.Index("idx_plantings_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)
    year_id: Mapped[int | None] = mapped_column(
        BigInteger,
        ForeignKey("years.id", ondelete="SET NULL"),
        nullable=True,
    )
    source: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("''")
    )
    notes: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("''")
    )
    created_at: Mapped[datetime] = created_at_column()
    # No CHECK: v1 history contains legacy status values beyond the current UI
    # vocabulary, so only the default is enforced.
    status: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("'planted'")
    )
    featured_image_path: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("''")
    )
    is_archived: Mapped[bool | None] = mapped_column(
        sa.Boolean, nullable=True, server_default=sa.text("false")
    )


class Plant(Base):
    __tablename__ = "plants"
    __table_args__ = (
        # v1 code enforced this vocabulary (unlike plantings.status).
        CheckConstraint(
            "status IN ('idea', 'unplanted', 'planted', 'removed')",
            name="status_vocab",
        ),
        # v1 index carried: idx_plants_archived
        sa.Index("idx_plants_archived", "is_archived"),
    )

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

    # --- carried v1 columns (order per pragma) ---
    planting_id: Mapped[int | None] = mapped_column(
        BigInteger,
        ForeignKey("plantings.id", ondelete="SET NULL"),
        nullable=True,
    )
    species_id: Mapped[int | None] = mapped_column(
        BigInteger,
        ForeignKey("species.id", ondelete="SET NULL"),
        nullable=True,
    )
    variety_id: Mapped[int | None] = mapped_column(
        BigInteger,
        ForeignKey("varieties.id", ondelete="SET NULL"),
        nullable=True,
    )
    area_id: Mapped[int | None] = mapped_column(
        BigInteger,
        ForeignKey("areas.id", ondelete="SET NULL"),
        nullable=True,
    )
    # WART KEPT: free-form Text quantity ("3", "a few", "2 rows").
    quantity: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("''")
    )
    source: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("''")
    )
    status: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("'planted'")
    )
    notes: 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("''")
    )
    # WART KEPT: Text, default '' (invalid JSON — cannot be JSONB).
    positions: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("''")
    )
    is_archived: Mapped[bool | None] = mapped_column(
        sa.Boolean, nullable=True, server_default=sa.text("false")
    )

    # --- new planner columns (v2) ---
    flavor_rating: Mapped[int | None] = mapped_column(Integer, nullable=True)
    vigor_rating: Mapped[int | None] = mapped_column(Integer, nullable=True)
    would_plant_again: Mapped[int | None] = mapped_column(Integer, nullable=True)
    outcome_summary: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("''")
    )