"""Weather cache table (NEW in v2 — not carried from v1).

One row per garden per calendar day per kind. ``raw`` keeps the provider's
full JSON payload for re-parsing without a re-fetch.
"""
from __future__ import annotations

import datetime as dt
from typing import Any

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

from .base import Base, garden_fk, pk_column


class WeatherDaily(Base):
    """Daily observed/forecast weather for a garden.

    ``kind`` vocabulary (enforced by CHECK, no native PG enum):
    'observed', 'forecast'. A day can hold one row of each kind
    (forecast rows get superseded-in-place by observed data upserts).
    """

    __tablename__ = "weather_daily"
    __table_args__ = (
        CheckConstraint("kind IN ('observed', 'forecast')", name="kind"),
        UniqueConstraint("garden_id", "date", "kind"),
    )

    id: Mapped[int] = pk_column()
    garden_id: Mapped[int] = garden_fk()
    date: Mapped[dt.date] = mapped_column(Date, nullable=False)
    kind: Mapped[str] = mapped_column(Text, nullable=False)
    temp_min_c: Mapped[float | None] = mapped_column(Double, nullable=True)
    temp_max_c: Mapped[float | None] = mapped_column(Double, nullable=True)
    precip_mm: Mapped[float | None] = mapped_column(Double, nullable=True)
    wind_max_kph: Mapped[float | None] = mapped_column(Double, nullable=True)
    conditions: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("''")
    )
    raw: Mapped[dict[str, Any] | None] = mapped_column(JSONB, nullable=True)
    fetched_at: Mapped[dt.datetime] = mapped_column(
        DateTime(timezone=True), nullable=False
    )