"""CARRIED v1 tables: areas, watering_stations, area_stations, supplies, years.

Near-verbatim port of the v1 SQLite schema (see
``data/v1-snapshot/v1_schema_pragma.txt``), column-for-column, with the
following planned deviations only:

- D1: every entity table gains ``garden_id`` (``garden_fk()``);
  ``area_stations`` is a pure M2M and does NOT get one.
- ``years.year`` was globally UNIQUE in v1 → ``UNIQUE (garden_id, year)``.
- Type mapping per base.py conventions: TEXT ISO timestamps → TIMESTAMPTZ,
  INTEGER 0/1 flags → Boolean (server_default false), REAL → Double,
  TEXT DEFAULT '' → Text (server_default '').

v1 non-autoindex indexes are recreated with their v1 names and column sets.

NOTE on ``areas.parent_id``: v1 declared it as a plain INTEGER with **no**
foreign-key constraint (added via ALTER TABLE without REFERENCES — verified
against the frozen snapshot DB). It is ported as a plain BigInteger; the
self-referential integrity stays app-enforced, as in v1.
"""
from __future__ import annotations

from datetime import datetime

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

from .base import Base, created_at_column, garden_fk, pk_column

_EMPTY = sa.text("''")
_FALSE = sa.text("false")
_ZERO = sa.text("0")


class Area(Base):
    """v1 ``areas`` — garden areas, self-nesting via ``parent_id``."""

    __tablename__ = "areas"
    __table_args__ = (Index("idx_areas_archived", "is_archived"),)

    id: Mapped[int] = pk_column()
    garden_id: Mapped[int] = garden_fk()
    name: Mapped[str] = mapped_column(Text, nullable=False)
    notes: Mapped[str | None] = mapped_column(Text, server_default=_EMPTY)
    created_at: Mapped[datetime] = created_at_column()
    # v1: plain INTEGER, no FK constraint (see module docstring).
    parent_id: Mapped[int | None] = mapped_column(BigInteger)
    sort_order: Mapped[int | None] = mapped_column(Integer)
    short_name: Mapped[str | None] = mapped_column(Text, server_default=_EMPTY)
    length_ft: Mapped[float | None] = mapped_column(Double, server_default=_ZERO)
    width_ft: Mapped[float | None] = mapped_column(Double, server_default=_ZERO)
    garden_area_sqft: Mapped[float | None] = mapped_column(
        Double, server_default=_ZERO
    )
    structure_features: Mapped[str | None] = mapped_column(
        Text, server_default=_EMPTY
    )
    sunlight: Mapped[str | None] = mapped_column(Text, server_default=_EMPTY)
    soil_environment: Mapped[str | None] = mapped_column(
        Text, server_default=_EMPTY
    )
    featured_image_path: Mapped[str | None] = mapped_column(
        Text, server_default=_EMPTY
    )
    sketch_rotation: Mapped[int | None] = mapped_column(
        Integer, server_default=_ZERO
    )
    parent_pos_x: Mapped[float | None] = mapped_column(Double)
    parent_pos_y: Mapped[float | None] = mapped_column(Double)
    is_archived: Mapped[bool | None] = mapped_column(
        Boolean, server_default=_FALSE
    )


class WateringStation(Base):
    """v1 ``watering_stations`` — irrigation zones/stations."""

    __tablename__ = "watering_stations"
    __table_args__ = (Index("idx_watering_stations_archived", "is_archived"),)

    id: Mapped[int] = pk_column()
    garden_id: Mapped[int] = garden_fk()
    name: Mapped[str] = mapped_column(Text, nullable=False)
    notes: Mapped[str | None] = mapped_column(Text, server_default=_EMPTY)
    current_schedule: Mapped[str | None] = mapped_column(
        Text, server_default=_EMPTY
    )
    created_at: Mapped[datetime] = created_at_column()
    featured_image_path: Mapped[str | None] = mapped_column(
        Text, server_default=_EMPTY
    )
    is_archived: Mapped[bool | None] = mapped_column(
        Boolean, server_default=_FALSE
    )


class AreaStation(Base):
    """v1 ``area_stations`` — pure M2M areas ↔ watering_stations.

    Composite PK, CASCADE both ways, no garden_id (scope derives from the
    two parents).
    """

    __tablename__ = "area_stations"

    area_id: Mapped[int] = mapped_column(
        BigInteger,
        ForeignKey("areas.id", ondelete="CASCADE"),
        primary_key=True,
    )
    station_id: Mapped[int] = mapped_column(
        BigInteger,
        ForeignKey("watering_stations.id", ondelete="CASCADE"),
        primary_key=True,
    )


class Supply(Base):
    """v1 ``supplies`` — garden supplies inventory."""

    __tablename__ = "supplies"
    __table_args__ = (Index("idx_supplies_archived", "is_archived"),)

    id: Mapped[int] = pk_column()
    garden_id: Mapped[int] = garden_fk()
    name: Mapped[str] = mapped_column(Text, nullable=False)
    category: Mapped[str | None] = mapped_column(Text, server_default=_EMPTY)
    amount: Mapped[str | None] = mapped_column(Text, server_default=_EMPTY)
    notes: Mapped[str | None] = mapped_column(Text, server_default=_EMPTY)
    created_at: Mapped[datetime] = created_at_column()
    quantity_on_hand: Mapped[str | None] = mapped_column(
        Text, server_default=_EMPTY
    )
    quantity_needed: Mapped[str | None] = mapped_column(
        Text, server_default=_EMPTY
    )
    # v1 INTEGER 0/1 flag.
    needed: Mapped[bool | None] = mapped_column(Boolean, server_default=_FALSE)
    featured_image_path: Mapped[str | None] = mapped_column(
        Text, server_default=_EMPTY
    )
    is_archived: Mapped[bool | None] = mapped_column(
        Boolean, server_default=_FALSE
    )


class Year(Base):
    """v1 ``years`` — season/year containers for plantings."""

    __tablename__ = "years"
    __table_args__ = (
        # v1: year was globally UNIQUE; scoped per-garden in v2 (plan).
        UniqueConstraint("garden_id", "year"),
        Index("idx_years_archived", "is_archived"),
    )

    id: Mapped[int] = pk_column()
    garden_id: Mapped[int] = garden_fk()
    year: Mapped[int] = mapped_column(Integer, nullable=False)
    notes: Mapped[str | None] = mapped_column(Text, server_default=_EMPTY)
    created_at: Mapped[datetime] = created_at_column()
    featured_image_path: Mapped[str | None] = mapped_column(
        Text, server_default=_EMPTY
    )
    is_archived: Mapped[bool | None] = mapped_column(
        Boolean, server_default=_FALSE
    )