"""Years domain service: season/year containers for plantings.

Port of v1 behavior (main.py):

- year routes (~4848-4964): year-DESC listing with per-year planting and
  plant counts, detail with the year's plantings (each with group/plant
  counts), notes snapshotting on update, hard delete that also cleans the
  polymorphic field-note target refs (v1 delete_target_refs, ~2548-2579).
- get_or_create_year (~2753-2762): composable helper used by other domains
  (plantings). Flush-only — the caller owns the transaction.

Deviations from v1 (documented):

- Plant totals use the Python int-else-1 rule everywhere (v1's
  planting_total_qty helper). v1's year LIST page used a SQLite
  ``CAST(quantity AS INTEGER)`` which maps non-numeric text to 0 — that
  SQLite-ism does not survive Postgres and contradicted v1's own detail
  page; the Python rule is the one v1 applied to detail counts.
- v1's UNIQUE(year) meant a duplicate POST /years bubbled up as a raw
  IntegrityError (500); v2 turns it into a structured 409.
- ``year`` is range-checked 1900-2100 (v1's /api/edit constraint for the
  field; the v1 form route didn't check).

Every mutation writes activity_log via services/audit.py and commits
(except get_or_create_year, which flushes so it can compose).
"""
from __future__ import annotations

from datetime import datetime, timezone

import sqlalchemy as sa
from sqlalchemy import delete, func, select
from sqlalchemy.orm import Session

from ..errors import AppError, VALIDATION_FAILED
from ..models.garden import Year
from ..models.notes import FieldNote, FieldNoteTarget
from ..models.planting import Plant, Planting
from . import audit
from .overviews import snapshot_overview

# --- error codes (domain-owned, per errors.py convention) ---
YEAR_NOT_FOUND = "years_not_found"
YEAR_EXISTS = "years_duplicate"
YEAR_OUT_OF_RANGE = "years_out_of_range"

# v1 EDITABLE_FIELDS["year"]["year"]: int, min 1900, max 2100.
YEAR_MIN, YEAR_MAX = 1900, 2100

_YEAR_PATCH_FIELDS = {"year", "notes"}


def _now() -> datetime:
    return datetime.now(timezone.utc)


def _year_payload(y: Year) -> dict:
    return {
        "id": y.id,
        "year": y.year,
        "notes": y.notes or "",
        "featured_image_path": y.featured_image_path or "",
        "is_archived": bool(y.is_archived),
        "created_at": y.created_at,
    }


def _get_year_row(db: Session, garden_id: int, year_id: int) -> Year:
    y = db.get(Year, year_id)
    if y is None or y.garden_id != garden_id:
        raise AppError(YEAR_NOT_FOUND, f"year {year_id} not found", 404)
    return y


def _validate_year_value(value) -> int:
    try:
        year = int(value)
    except (TypeError, ValueError):
        raise AppError(VALIDATION_FAILED, "year must be an integer", 400)
    if not (YEAR_MIN <= year <= YEAR_MAX):
        raise AppError(
            YEAR_OUT_OF_RANGE,
            f"year must be between {YEAR_MIN} and {YEAR_MAX}", 400,
        )
    return year


def _plant_total(quantities: list) -> int:
    """v1 planting_total_qty rule: int(quantity), non-numeric counts as 1."""
    total = 0
    for q in quantities:
        try:
            total += int(q)
        except (ValueError, TypeError):
            total += 1
    return total


# --- reads --------------------------------------------------------------------

def list_years(
    db: Session, garden_id: int, include_archived: bool = False
) -> list[dict]:
    """v1 GET /years: year-DESC + per-year planting_count and plant_count.
    Archive filter applies to the PLANTINGS only (v1 arch_clause was on
    pl.is_archived for both counts)."""
    stmt = select(Year).where(Year.garden_id == garden_id)
    if not include_archived:
        stmt = stmt.where(Year.is_archived == sa.false())
    years = db.scalars(stmt.order_by(Year.year.desc())).all()

    pl_arch = [] if include_archived else [Planting.is_archived == sa.false()]
    planting_counts = dict(
        db.execute(
            select(Planting.year_id, func.count())
            .where(
                Planting.garden_id == garden_id,
                Planting.year_id.is_not(None),
                *pl_arch,
            )
            .group_by(Planting.year_id)
        ).all()
    )
    qty_rows = db.execute(
        select(Planting.year_id, Plant.quantity)
        .join(Plant, Plant.planting_id == Planting.id)
        .where(
            Planting.garden_id == garden_id,
            Planting.year_id.is_not(None),
            *pl_arch,
        )
    ).all()
    plant_totals: dict[int, list] = {}
    for year_id, qty in qty_rows:
        plant_totals.setdefault(year_id, []).append(qty)

    return [
        {
            **_year_payload(y),
            "planting_count": planting_counts.get(y.id, 0),
            "plant_count": _plant_total(plant_totals.get(y.id, [])),
        }
        for y in years
    ]


def get_year(
    db: Session, garden_id: int, year_id: int, include_archived: bool = False
) -> dict:
    """v1 GET /years/{id}: the row + its plantings (name-ordered), each with
    group_count and plant_count (v1 planting_total_qty)."""
    y = _get_year_row(db, garden_id, year_id)

    pl_stmt = select(Planting).where(
        Planting.year_id == year_id, Planting.garden_id == garden_id
    )
    if not include_archived:
        pl_stmt = pl_stmt.where(Planting.is_archived == sa.false())
    plantings = db.scalars(pl_stmt.order_by(Planting.name)).all()

    qty_stmt = select(Plant.planting_id, Plant.quantity).where(
        Plant.planting_id.in_([p.id for p in plantings] or [-1])
    )
    if not include_archived:
        qty_stmt = qty_stmt.where(Plant.is_archived == sa.false())
    by_planting: dict[int, list] = {}
    for pid, qty in db.execute(qty_stmt).all():
        by_planting.setdefault(pid, []).append(qty)

    return {
        **_year_payload(y),
        "plantings": [
            {
                "id": p.id,
                "name": p.name,
                "source": p.source or "",
                "status": p.status or "planted",
                "is_archived": bool(p.is_archived),
                "group_count": len(by_planting.get(p.id, [])),
                "plant_count": _plant_total(by_planting.get(p.id, [])),
            }
            for p in plantings
        ],
    }


# --- writes -------------------------------------------------------------------

def _find_year(db: Session, garden_id: int, year_value: int) -> Year | None:
    return db.scalar(
        select(Year).where(
            Year.garden_id == garden_id, Year.year == year_value
        )
    )


def create_year(db: Session, garden_id: int, *, year, notes: str = "") -> dict:
    """v1 POST /years — duplicate years become a structured 409 (v1's
    UNIQUE(year) raised raw)."""
    year_value = _validate_year_value(year)
    if _find_year(db, garden_id, year_value) is not None:
        raise AppError(YEAR_EXISTS, f"year {year_value} already exists", 409)
    y = Year(
        garden_id=garden_id,
        year=year_value,
        notes=(notes or "").strip(),
        created_at=_now(),
        featured_image_path="",
        is_archived=False,
    )
    db.add(y)
    db.flush()
    audit.log_add(db, garden_id, "year", y.id, str(year_value))
    db.commit()
    return _year_payload(y)


def get_or_create_year(db: Session, garden_id: int, year_value: int) -> Year:
    """v1 get_or_create_year — composable helper for other domains
    (plantings). FLUSHES but does NOT commit: the caller owns the
    transaction boundary."""
    existing = _find_year(db, garden_id, int(year_value))
    if existing is not None:
        return existing
    y = Year(
        garden_id=garden_id,
        year=int(year_value),
        notes="",
        created_at=_now(),
        featured_image_path="",
        is_archived=False,
    )
    db.add(y)
    db.flush()
    audit.log_add(db, garden_id, "year", y.id, str(year_value))
    return y


def update_year(
    db: Session, garden_id: int, year_id: int, changes: dict
) -> dict:
    """PATCH semantics over the v1 year form fields; notes edits snapshot
    the previous overview (v1 snapshot_overview)."""
    unknown = set(changes) - _YEAR_PATCH_FIELDS
    if unknown:
        raise AppError(
            VALIDATION_FAILED, f"unknown fields: {sorted(unknown)}", 400
        )
    y = _get_year_row(db, garden_id, year_id)

    normalized: dict = {}
    if "year" in changes:
        new_year = _validate_year_value(changes["year"])
        if new_year != y.year and _find_year(db, garden_id, new_year):
            raise AppError(
                YEAR_EXISTS, f"year {new_year} already exists", 409
            )
        normalized["year"] = new_year
    if "notes" in changes:
        new_notes = (changes["notes"] or "").strip()
        snapshot_overview(db, "year", y.id, y.notes, new_notes)
        normalized["notes"] = new_notes

    label = str(y.year)
    for field, new in normalized.items():
        old = getattr(y, field)
        if old != new:
            setattr(y, field, new)
            audit.log_edit(db, garden_id, "year", y.id, field, old, new,
                           label=label)
    db.commit()
    return _year_payload(y)


def _delete_field_note_target_refs(
    db: Session, target_type: str, target_id: int
) -> None:
    """Port of v1 delete_target_refs (~2548-2579): remove all field-note
    targets pointing at this entity, delete field notes orphaned by that
    (no remaining targets), and re-point a primary if the primary was lost.

    NOTE: v1 SQLite's ``ORDER BY is_primary DESC`` sorts NULLs last; Postgres
    sorts NULLs first on DESC, hence the explicit COALESCE."""
    affected = db.scalars(
        select(FieldNoteTarget.field_note_id).where(
            FieldNoteTarget.target_type == target_type,
            FieldNoteTarget.target_id == target_id,
        )
    ).all()
    db.execute(
        delete(FieldNoteTarget).where(
            FieldNoteTarget.target_type == target_type,
            FieldNoteTarget.target_id == target_id,
        )
    )
    for note_id in dict.fromkeys(affected):
        remaining = db.scalars(
            select(FieldNoteTarget)
            .where(FieldNoteTarget.field_note_id == note_id)
            .order_by(
                func.coalesce(FieldNoteTarget.is_primary, sa.false()).desc(),
                FieldNoteTarget.id,
            )
        ).all()
        if not remaining:
            db.execute(delete(FieldNote).where(FieldNote.id == note_id))
            continue
        if not any(bool(t.is_primary) for t in remaining):
            remaining[0].is_primary = True


def delete_year(db: Session, garden_id: int, year_id: int) -> None:
    """v1 POST /years/{id}/delete — clean polymorphic note-target refs, then
    hard delete. Plantings keep existing (year_id FK is ON DELETE SET NULL)."""
    y = _get_year_row(db, garden_id, year_id)
    label = str(y.year)
    _delete_field_note_target_refs(db, "year", year_id)
    db.delete(y)
    audit.log_delete(db, garden_id, "year", year_id, label)
    db.commit()