"""Areas domain service — port of the v1 area helpers + route logic.

v1 source (/srv/apps/garden/app/main.py, READ-ONLY reference):

- ``load_areas`` / ``build_tree`` / ``get_ancestors`` / ``get_descendant_ids``
  (~1248-1305)
- ``get_plant_count_map`` / ``get_species_summary_map`` /
  ``get_area_plant_summary`` (~1308-1428)
- ``area_path_name`` / ``area_compact_path`` / ``area_tree_payload`` /
  ``flattened_area_options`` (~1431-1501)
- ``load_plants_by_area`` (~1504-1540), ``delete_area_recursive`` (~1543-1552)
- route logic: reorder (~3942-3990), ``_normalize_rotation`` (~3996-4002),
  ``_parse_float`` (~4164-4171), ``_fmt_dims`` (~3818-3828), create
  (~4174-4213), detail (~4216-4359), update (~4682-4735), delete
  (~4830-4843), parent-position (~5605-5637).

v1 semantics preserved exactly: ``COALESCE(sort_order, 999999), name``
ordering; archived filtering default-off with an ``include_archived`` flag;
rotation snapped to the 45-degree steps (else 0); dims parsing tolerating
blank/garbage strings (-> 0.0); cycle checks built over the NON-archived
tree (as v1 did); sketch repositioning (reorder / parent-position / plant
positions) intentionally NOT audit-logged (v1 decision: too noisy).

v2 deviations (all garden-scoping, deviation D1):
- every query is scoped by ``garden_id``;
- a non-NULL ``parent_id`` must exist within the same garden
  (``area_parent_not_found``) — v1 was single-garden and accepted any int;
- create/update/delete write the activity log via services/audit.py
  (v1 logged adds/deletes plus per-field edits through its inline-edit
  endpoint; the PATCH route here is that endpoint's successor).
"""
from __future__ import annotations

from datetime import datetime, timezone
from typing import Optional

import sqlalchemy as sa
from sqlalchemy import delete as sa_delete, func, select, update as sa_update
from sqlalchemy.orm import Session, aliased

from ..errors import AppError
from ..models.catalog import Species, Variety
from ..models.garden import Area, Year
from ..models.planting import Plant, Planting
from .audit import log_add, log_delete, log_edit

# --- error codes (service-owned; see errors.py docstring) -------------------
AREA_NOT_FOUND = "area_not_found"
AREA_CYCLE_REJECTED = "area_cycle_rejected"
AREA_PARENT_NOT_FOUND = "area_parent_not_found"
AREA_NAME_REQUIRED = "area_name_required"
AREA_NO_PARENT = "area_no_parent"

# v1: OVERVIEW_STATUS_CHOICES (~3835-3841). Values map to a set of
# plants.status values; None means "no filter" (show everything incl. removed).
OVERVIEW_STATUS_CHOICES: dict[str, Optional[set]] = {
    "planted":   {"planted"},
    "unplanted": {"unplanted"},
    "idea":      {"idea"},
    "active":    {"planted", "unplanted"},
    "all":       None,
}

# v1: SKETCH_ROTATION_STEPS (~3993).
SKETCH_ROTATION_STEPS = (0, 45, 90, 135, 180, 225, 270, 315)

_TEXT_FIELDS = (
    "name", "notes", "short_name", "structure_features", "sunlight",
    "soil_environment",
)
_DIM_FIELDS = ("length_ft", "width_ft", "garden_area_sqft")


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


# ---------------------------------------------------------------------------
# Pure parsing helpers (v1 semantics)
# ---------------------------------------------------------------------------

def normalize_rotation(v) -> int:
    """v1 ``_normalize_rotation``: int-parse, mod 360, snap to a 45-degree
    step or fall back to 0."""
    try:
        n = int(v)
    except (TypeError, ValueError):
        return 0
    n = n % 360
    return n if n in SKETCH_ROTATION_STEPS else 0


def parse_dim(v) -> float:
    """v1 ``_parse_float``: blank / unparseable -> 0.0."""
    if isinstance(v, (int, float)):
        return float(v)
    s = (v or "").strip() if isinstance(v, str) else ""
    if not s:
        return 0.0
    try:
        return float(s)
    except ValueError:
        return 0.0


def parse_parent_id(v) -> Optional[int]:
    """v1 accepted parent_id as a form string: '' -> None, else int()."""
    if v is None:
        return None
    if isinstance(v, int):
        return v
    s = str(v).strip()
    return int(s) if s else None


def format_dims(area: dict) -> str:
    """v1 ``_fmt_dims``: '10x4.5 ft * 45 sqft' style display string."""
    parts = []
    l, w = area.get("length_ft") or 0, area.get("width_ft") or 0
    if l and w:
        def _n(x):
            return str(int(x)) if float(x).is_integer() else f"{x:.1f}"
        parts.append(f"{_n(l)}×{_n(w)} ft")
    ga = area.get("garden_area_sqft") or 0
    if ga:
        parts.append(f"{int(ga)} sqft")
    return " · ".join(parts)


def _row_dict(obj) -> dict:
    """Plain-dict view of an ORM row (the tree helpers below are pure
    functions over dicts, exactly like v1's sqlite3.Row dicts)."""
    return {c.key: getattr(obj, c.key) for c in obj.__table__.columns}


# ---------------------------------------------------------------------------
# Tree loading + pure tree helpers (verbatim v1 ports over dicts)
# ---------------------------------------------------------------------------

def load_areas(db: Session, garden_id: int, include_archived: bool = False) -> list:
    """v1 ``load_areas``: SELECT * ... ORDER BY COALESCE(sort_order, 999999), name."""
    stmt = select(Area).where(Area.garden_id == garden_id)
    if not include_archived:
        stmt = stmt.where(Area.is_archived == sa.false())
    stmt = stmt.order_by(func.coalesce(Area.sort_order, 999999), Area.name)
    return [_row_dict(a) for a in db.scalars(stmt)]


def build_tree(area_rows: list):
    """v1 ``build_tree`` — verbatim."""
    by_id = {a["id"]: dict(a, children=[]) for a in area_rows}
    roots = []
    for a in by_id.values():
        pid = a.get("parent_id")
        if pid and pid in by_id:
            by_id[pid]["children"].append(a)
        else:
            roots.append(a)

    def sort_key(x):
        order = x.get("sort_order")
        return (order if order is not None else 999999, x["name"].lower())

    def sort_children(node):
        node["children"].sort(key=sort_key)
        for c in node["children"]:
            sort_children(c)

    roots.sort(key=sort_key)
    for r in roots:
        sort_children(r)
    return roots, by_id


def get_ancestors(area_id: int, by_id: dict) -> list:
    """v1 ``get_ancestors`` — verbatim (root-first path incl. the area)."""
    path = []
    cur = by_id.get(area_id)
    while cur:
        path.append(cur)
        pid = cur.get("parent_id")
        cur = by_id.get(pid) if pid else None
    return list(reversed(path))


def get_descendant_ids(area_id: int, by_id: dict) -> set:
    """v1 ``get_descendant_ids`` — verbatim."""
    result = set()

    def walk(aid):
        node = by_id.get(aid)
        if not node:
            return
        for child in node["children"]:
            result.add(child["id"])
            walk(child["id"])

    walk(area_id)
    return result


def _qty(plant: dict) -> int:
    """v1 ``_qty``: free-form quantity Text -> int, fallback 1."""
    try:
        return int(plant["quantity"])
    except (ValueError, TypeError):
        return 1


def get_plant_count_map(areas_tree: list, plants_by_area: dict) -> dict:
    """v1 — sum of quantities for each area including all descendants."""
    result = {}

    def walk(node):
        total = sum(_qty(p) for p in plants_by_area.get(node["id"], []))
        for c in node["children"]:
            total += walk(c)
        result[node["id"]] = total
        return total

    for root in areas_tree:
        walk(root)
    return result


def get_species_summary_map(areas_tree: list, plants_by_area: dict) -> dict:
    """v1 — per-area [{name, count}] species summaries over the full subtree,
    sorted by count desc then name asc."""
    result = {}

    def walk(node):
        own = {}
        for p in plants_by_area.get(node["id"], []):
            sp = p.get("species_name")
            if not sp:
                continue
            own[sp] = own.get(sp, 0) + _qty(p)
        merged = dict(own)
        for c in node["children"]:
            walk(c)
            for name, count in result[c["id"]]["_counts"].items():
                merged[name] = merged.get(name, 0) + count
        ordered = sorted(
            ((n, c) for n, c in merged.items() if c > 0),
            key=lambda kv: (-kv[1], kv[0]),
        )
        result[node["id"]] = {
            "_counts": merged,
            "summary": [{"name": n, "count": c} for n, c in ordered],
        }

    for root in areas_tree:
        walk(root)
    return {aid: info["summary"] for aid, info in result.items()}


def area_path_name(area_id: int, by_id: dict) -> str:
    """v1 — 'Home > Back Yard > Fenced Garden'."""
    return " > ".join(a["name"] for a in get_ancestors(area_id, by_id))


def area_compact_path(area_id: int, by_id: dict) -> dict:
    """v1 — {parent, name, full} for compact display; skips top-level 'Home'."""
    ancestors = get_ancestors(area_id, by_id)
    if not ancestors:
        return {"parent": "", "name": "?", "full": "?"}
    area = ancestors[-1]
    name = area["name"]
    parents = ancestors[:-1]
    if parents and parents[0]["name"].lower() == "home":
        parents = parents[1:]
    parts = []
    for p in parents:
        sn = (p.get("short_name") or "").strip()
        parts.append(sn if sn else p["name"])
    parent = " > ".join(parts)
    full = (parent + " > " + name) if parent else name
    return {"parent": parent, "name": name, "full": full}


def area_tree_payload(areas_tree: list, by_id: dict, exclude_ids: Optional[set] = None) -> dict:
    """v1 — compact {"roots": [ids], "areas": {id: {...}}} for the drill-down picker."""
    exclude_ids = exclude_ids or set()
    areas = {}
    for aid, a in by_id.items():
        if aid in exclude_ids:
            continue
        children = [c["id"] for c in a["children"] if c["id"] not in exclude_ids]
        ancestors = get_ancestors(aid, by_id)[:-1]
        path_short = " › ".join(
            (p.get("short_name") or p["name"])
            for p in ancestors
            if p["id"] not in exclude_ids
        )
        areas[aid] = {
            "id": aid,
            "name": a["name"],
            "short_name": (a.get("short_name") or "").strip(),
            "parent_id": a.get("parent_id"),
            "children": children,
            "path_short": path_short,
        }
    roots = [a["id"] for a in areas_tree if a["id"] not in exclude_ids]
    return {"roots": roots, "areas": areas}


def flattened_area_options(by_id: dict, exclude_ids: Optional[set] = None) -> list:
    """v1 — flat [{id, path, parent, name, compact}] sorted by lowercase path."""
    exclude_ids = exclude_ids or set()
    options = []
    for aid in by_id:
        if aid in exclude_ids:
            continue
        cp = area_compact_path(aid, by_id)
        options.append({
            "id": aid,
            "path": area_path_name(aid, by_id),
            "parent": cp["parent"],
            "name": cp["name"],
            "compact": cp["full"],
        })
    options.sort(key=lambda x: x["path"].lower())
    return options


# ---------------------------------------------------------------------------
# Plant loading (area-scoped views)
# ---------------------------------------------------------------------------

def load_plants_by_area(
    db: Session,
    garden_id: int,
    plant_statuses: Optional[set] = None,
    include_archived: bool = False,
) -> dict:
    """v1 ``load_plants_by_area``: map area_id -> list of plant dicts with
    species/variety names, resolved spacing (COALESCE variety -> species ->
    default) and planting name. Status filter applied in Python, as in v1.
    Plants with NULL area_id land under the ``None`` key, as in v1."""
    stmt = (
        select(
            Plant,
            Species.name.label("species_name"),
            Variety.name.label("variety_name"),
            func.coalesce(
                Variety.plants_per_unit, Species.plants_per_unit, 1
            ).label("species_plants_per_unit"),
            func.coalesce(
                Variety.space_per_unit_sqft, Species.space_per_unit_sqft, 1.0
            ).label("species_space_per_unit_sqft"),
            Planting.name.label("planting_name"),
        )
        .select_from(Plant)
        .outerjoin(Species, Species.id == Plant.species_id)
        .outerjoin(Variety, Variety.id == Plant.variety_id)
        .outerjoin(Planting, Planting.id == Plant.planting_id)
        .where(Plant.garden_id == garden_id)
        .order_by(func.coalesce(Variety.name, Species.name, ""))
    )
    if not include_archived:
        # v1: WHERE p.is_archived = 0 AND COALESCE(pl.is_archived, 0) = 0
        stmt = stmt.where(
            Plant.is_archived == sa.false(),
            func.coalesce(Planting.is_archived, sa.false()) == sa.false(),
        )
    result: dict = {}
    for plant, sp_name, v_name, ppu, spu, pl_name in db.execute(stmt):
        p = _row_dict(plant)
        p["species_name"] = sp_name
        p["variety_name"] = v_name
        p["species_plants_per_unit"] = ppu
        p["species_space_per_unit_sqft"] = spu
        p["planting_name"] = pl_name
        ps = (p["status"] or "planted")
        if plant_statuses is not None and ps not in plant_statuses:
            continue
        result.setdefault(p["area_id"], []).append(p)
    return result


def get_area_plant_summary(
    db: Session,
    garden_id: int,
    area_id: int,
    by_id: dict,
    plant_statuses: Optional[set] = None,
) -> dict:
    """v1 ``get_area_plant_summary``: totals + consolidated (species, variety)
    quantity list for an area and all descendants."""
    desc_ids = [area_id] + list(get_descendant_ids(area_id, by_id))
    rows = db.execute(
        select(
            Plant.quantity,
            Plant.status.label("plant_status"),
            Species.name.label("species_name"),
            Variety.name.label("variety_name"),
            Species.id.label("species_id"),
            Variety.id.label("variety_id"),
            Planting.id.label("planting_id"),
            Planting.name.label("planting_name"),
        )
        .select_from(Plant)
        .outerjoin(Species, Species.id == Plant.species_id)
        .outerjoin(Variety, Variety.id == Plant.variety_id)
        .outerjoin(Planting, Planting.id == Plant.planting_id)
        .where(Plant.area_id.in_(desc_ids), Plant.garden_id == garden_id)
    ).all()

    total_qty = 0
    species_set = set()
    variety_set = set()
    planting_set = set()
    consolidated: dict = {}

    for r in rows:
        if (
            plant_statuses is not None
            and (r.plant_status or "planted") not in plant_statuses
        ):
            continue
        qty = 1
        try:
            qty = int(r.quantity)
        except (ValueError, TypeError):
            pass
        total_qty += qty
        sp = r.species_name or "Unknown"
        vr = r.variety_name or "Unknown"
        if r.species_id:
            species_set.add(r.species_id)
        if r.variety_id:
            variety_set.add(r.variety_id)
        if r.planting_id:
            planting_set.add(r.planting_id)
        consolidated[(sp, vr)] = consolidated.get((sp, vr), 0) + qty

    plant_list = sorted(
        [{"species": sp, "variety": vr, "qty": q} for (sp, vr), q in consolidated.items()],
        key=lambda x: (-x["qty"], x["species"], x["variety"]),
    )

    return {
        "total_qty": total_qty,
        "species_count": len(species_set),
        "variety_count": len(variety_set),
        "planting_count": len(planting_set),
        "plant_list": plant_list,
    }


# ---------------------------------------------------------------------------
# Mutations
# ---------------------------------------------------------------------------

def _get_area_or_404(db: Session, garden_id: int, area_id: int) -> Area:
    area = db.scalar(
        select(Area).where(Area.id == area_id, Area.garden_id == garden_id)
    )
    if area is None:
        raise AppError(AREA_NOT_FOUND, "area not found", 404)
    return area


def _require_parent_in_garden(db: Session, garden_id: int, parent_id: int) -> None:
    """v2 garden-scoping addition (documented deviation): a non-NULL parent
    must be an area in the same garden. v1 (single-garden) accepted any int."""
    ok = db.scalar(
        select(Area.id).where(Area.id == parent_id, Area.garden_id == garden_id)
    )
    if ok is None:
        raise AppError(AREA_PARENT_NOT_FOUND, "parent area not found", 400)


def _check_cycle(db: Session, garden_id: int, area_id: int, parent_id: int) -> None:
    """v1 cycle check: parent may not be the area itself or any descendant.
    Built over the NON-archived tree, exactly as v1 did."""
    area_rows = load_areas(db, garden_id)
    _, by_id = build_tree(area_rows)
    exclude = {area_id} | get_descendant_ids(area_id, by_id)
    if parent_id in exclude:
        raise AppError(
            AREA_CYCLE_REJECTED, "Invalid parent (would create a cycle)", 400
        )


def _area_out(area_row: dict) -> dict:
    """Response dict for one area (AreaOut shape): all columns minus
    garden_id, plus the v1 display dims string."""
    d = {k: v for k, v in area_row.items() if k not in ("garden_id", "children")}
    d["short_name"] = (d.get("short_name") or "").strip()
    d["is_archived"] = bool(d.get("is_archived"))
    d["sketch_rotation"] = d.get("sketch_rotation") or 0
    d["dims"] = format_dims(area_row)
    return d


def create_area(
    db: Session,
    garden_id: int,
    *,
    name: str,
    notes: str = "",
    parent_id=None,
    short_name: str = "",
    length_ft=None,
    width_ft=None,
    garden_area_sqft=None,
    structure_features: str = "",
    sunlight: str = "",
    soil_environment: str = "",
    sketch_rotation=0,
) -> dict:
    """v1 POST /areas: sort_order = sibling max + 10, rotation snapped,
    dims blank-tolerant. Featured-image upload is the media domain (v2) and
    is not handled here."""
    clean_name = (name or "").strip()
    if not clean_name:
        raise AppError(AREA_NAME_REQUIRED, "name is required", 400)
    pid = parse_parent_id(parent_id)
    if pid is not None:
        _require_parent_in_garden(db, garden_id, pid)
    rotation = normalize_rotation(sketch_rotation)
    sibling_cond = Area.parent_id.is_(None) if pid is None else Area.parent_id == pid
    max_order = db.scalar(
        select(func.coalesce(func.max(Area.sort_order), 0)).where(
            Area.garden_id == garden_id, sibling_cond
        )
    ) or 0
    area = Area(
        garden_id=garden_id,
        name=clean_name,
        notes=(notes or "").strip(),
        parent_id=pid,
        sort_order=max_order + 10,
        short_name=(short_name or "").strip(),
        length_ft=parse_dim(length_ft),
        width_ft=parse_dim(width_ft),
        garden_area_sqft=parse_dim(garden_area_sqft),
        structure_features=(structure_features or "").strip(),
        sunlight=(sunlight or "").strip(),
        soil_environment=(soil_environment or "").strip(),
        featured_image_path="",
        sketch_rotation=rotation,
        is_archived=False,
        created_at=now_utc(),
    )
    db.add(area)
    db.flush()
    log_add(db, garden_id, "area", area.id, clean_name)
    db.commit()
    return _area_out(_row_dict(area))


def update_area(db: Session, garden_id: int, area_id: int, fields: dict) -> dict:
    """v1 POST /areas/{id} + inline-edit semantics, as a PATCH: only the
    provided fields are touched. Text fields stripped, dims blank-tolerant,
    rotation snapped, parent change cycle-checked. Each changed field is
    audit-logged (v1's inline-edit endpoint behavior, which this replaces).
    """
    area = _get_area_or_404(db, garden_id, area_id)
    updates: dict = {}
    for f, raw in fields.items():
        if f in _TEXT_FIELDS:
            val = (raw or "").strip()
            if f == "name" and not val:
                raise AppError(AREA_NAME_REQUIRED, "name is required", 400)
            updates[f] = val
        elif f in _DIM_FIELDS:
            updates[f] = parse_dim(raw)
        elif f == "sketch_rotation":
            updates[f] = normalize_rotation(raw)
        elif f == "parent_id":
            pid = parse_parent_id(raw)
            if pid is not None:
                _check_cycle(db, garden_id, area_id, pid)
                _require_parent_in_garden(db, garden_id, pid)
            updates[f] = pid
        # Unknown fields never arrive (schema whitelist); ignore defensively.

    label = updates.get("name") or area.name
    for f, new in updates.items():
        old = getattr(area, f)
        if old == new:
            continue
        # TODO(notes-domain): v1 snapshot_overview() kept an overview_versions
        # revision when `notes` changed — wire that in when the notes domain
        # lands its revisions service.
        log_edit(db, garden_id, "area", area_id, f, old, new, label=label)
        setattr(area, f, new)
    db.commit()
    return _area_out(_row_dict(area))


def delete_area_recursive(db: Session, garden_id: int, area_id: int) -> None:
    """v1 ``delete_area_recursive``: depth-first delete of the subtree,
    unlinking plants (they belong to plantings, never deleted here)."""
    child_ids = db.scalars(
        select(Area.id).where(
            Area.parent_id == area_id, Area.garden_id == garden_id
        )
    ).all()
    for cid in child_ids:
        delete_area_recursive(db, garden_id, cid)
    db.execute(
        sa_update(Plant)
        .where(Plant.area_id == area_id, Plant.garden_id == garden_id)
        .values(area_id=None)
    )
    # TODO(notes-domain): v1 called delete_target_refs(con, "area", area_id)
    # here (drop field_note_targets rows pointing at this area, prune orphaned
    # notes, re-point lost primaries). Out of scope for the areas port — the
    # notes domain owns that helper; call it from here once it lands.
    db.execute(
        sa_delete(Area).where(Area.id == area_id, Area.garden_id == garden_id)
    )


def delete_area(db: Session, garden_id: int, area_id: int) -> dict:
    """v1 POST /areas/{id}/delete."""
    area = _get_area_or_404(db, garden_id, area_id)
    parent_id = area.parent_id
    label = area.name
    delete_area_recursive(db, garden_id, area_id)
    log_delete(db, garden_id, "area", area_id, label)
    db.commit()
    return {"ok": True, "id": area_id, "parent_id": parent_id}


def reorder_area(
    db: Session, garden_id: int, area_id: int, parent_id, new_index: int
) -> dict:
    """v1 POST /areas/reorder: re-parent (cycle-validated) + resequence the
    destination siblings at sort_order = index * 10. Not audit-logged
    (v1 decision: repositioning is too noisy for the activity log)."""
    _get_area_or_404(db, garden_id, area_id)
    pid = parse_parent_id(parent_id)
    if pid is not None:
        _check_cycle(db, garden_id, area_id, pid)
        _require_parent_in_garden(db, garden_id, pid)

    db.execute(
        sa_update(Area)
        .where(Area.id == area_id, Area.garden_id == garden_id)
        .values(parent_id=pid)
    )

    sibling_cond = Area.parent_id.is_(None) if pid is None else Area.parent_id == pid
    sibling_ids = list(
        db.scalars(
            select(Area.id)
            .where(Area.garden_id == garden_id, sibling_cond, Area.id != area_id)
            .order_by(func.coalesce(Area.sort_order, 999999), Area.name)
        )
    )
    idx = max(0, min(int(new_index), len(sibling_ids)))
    sibling_ids.insert(idx, area_id)
    for i, sid in enumerate(sibling_ids):
        db.execute(
            sa_update(Area)
            .where(Area.id == sid, Area.garden_id == garden_id)
            .values(sort_order=i * 10)
        )
    db.commit()
    return {"ok": True}


def set_parent_position(
    db: Session, garden_id: int, area_id: int, x: float, y: float
) -> dict:
    """v1 POST /api/areas/{id}/parent-position: save the child's CENTER
    position (feet, parent natural-orientation rect-local), clamped to the
    parent's dimensions. Not audit-logged (v1 parity)."""
    parent = aliased(Area)
    row = db.execute(
        select(Area.parent_id, parent.length_ft, parent.width_ft)
        .select_from(Area)
        .outerjoin(parent, parent.id == Area.parent_id)
        .where(Area.id == area_id, Area.garden_id == garden_id)
    ).first()
    if row is None:
        raise AppError(AREA_NOT_FOUND, "area not found", 404)
    if not row.parent_id:
        raise AppError(
            AREA_NO_PARENT, "area has no parent — nothing to position within", 400
        )
    parent_L = float(row.length_ft or 0) or 1.0
    parent_W = float(row.width_ft or 0) or 1.0
    cx = max(0.0, min(parent_L, float(x)))
    cy = max(0.0, min(parent_W, float(y)))
    db.execute(
        sa_update(Area)
        .where(Area.id == area_id, Area.garden_id == garden_id)
        .values(parent_pos_x=round(cx, 3), parent_pos_y=round(cy, 3))
    )
    db.commit()
    return {"ok": True, "id": area_id, "x": round(cx, 3), "y": round(cy, 3)}


# ---------------------------------------------------------------------------
# Read payload assembly (router-facing)
# ---------------------------------------------------------------------------

def _statuses_for(status: str) -> Optional[set]:
    return OVERVIEW_STATUS_CHOICES.get(status, OVERVIEW_STATUS_CHOICES["planted"])


def _status_filter_name(status: str) -> str:
    return status if status in OVERVIEW_STATUS_CHOICES else "planted"


def _tree_node(a: dict, plant_counts: dict, species_summary: dict) -> dict:
    return {
        "id": a["id"],
        "name": a["name"],
        "short_name": (a.get("short_name") or "").strip(),
        "parent_id": a.get("parent_id"),
        "sort_order": a.get("sort_order"),
        "length_ft": a.get("length_ft"),
        "width_ft": a.get("width_ft"),
        "garden_area_sqft": a.get("garden_area_sqft"),
        "sketch_rotation": a.get("sketch_rotation") or 0,
        "featured_image_path": a.get("featured_image_path") or "",
        "is_archived": bool(a.get("is_archived")),
        "dims": format_dims(a),
        "child_count": len(a["children"]),
        "plant_count": plant_counts.get(a["id"], 0),
        "summary": species_summary.get(a["id"], []),
        "children": [_tree_node(c, plant_counts, species_summary) for c in a["children"]],
    }


def get_areas_index(
    db: Session,
    garden_id: int,
    include_archived: bool = False,
    status: str = "planted",
) -> dict:
    """GET /api/areas payload: nested tree (with per-node subtree plant counts
    + species summaries, as on v1's overview tiles), flat picker options, and
    the compact drill-down picker payload."""
    statuses = _statuses_for(status)
    area_rows = load_areas(db, garden_id, include_archived=include_archived)
    tree, by_id = build_tree(area_rows)
    plants_by_area = load_plants_by_area(
        db, garden_id, plant_statuses=statuses, include_archived=include_archived
    )
    plant_counts = get_plant_count_map(tree, plants_by_area)
    species_summary = get_species_summary_map(tree, plants_by_area)
    return {
        "tree": [_tree_node(r, plant_counts, species_summary) for r in tree],
        "options": flattened_area_options(by_id),
        "picker": area_tree_payload(tree, by_id),
        "status_filter": _status_filter_name(status),
    }


def get_area_detail(
    db: Session,
    garden_id: int,
    area_id: int,
    status: str = "planted",
    include_archived: bool = False,
) -> dict:
    """v1 GET /areas/{id} payload (areas-domain slice: area + breadcrumbs +
    enriched child summaries + direct plants + sketch payload). Stations,
    comments and species/variety pickers are other domains' routers in v2.

    v1 redirected an archived area's URL to ?archived=1; the API equivalent
    is auto-including archived rows when the target itself is archived, so
    the payload always loads consistently.
    """
    # Local import: layout.py imports this module's loaders (one-way at module
    # level); the detail sketch needs layout's pure geometry, so import here.
    from .layout import (
        resolve_child_area_positions,
        resolve_plant_positions,
        sketch_geometry,
        species_icon_color,
    )

    statuses = _statuses_for(status)
    target = db.execute(
        select(Area.is_archived).where(
            Area.id == area_id, Area.garden_id == garden_id
        )
    ).first()
    if target is None:
        raise AppError(AREA_NOT_FOUND, "area not found", 404)
    show_archived = include_archived or bool(target[0])

    area_rows = load_areas(db, garden_id, include_archived=show_archived)
    tree, by_id = build_tree(area_rows)
    area = by_id.get(area_id)
    if not area:
        raise AppError(AREA_NOT_FOUND, "area not found", 404)

    plants_by_area = load_plants_by_area(
        db, garden_id, plant_statuses=statuses, include_archived=show_archived
    )
    summary = get_area_plant_summary(
        db, garden_id, area_id, by_id, plant_statuses=statuses
    )
    plants = plants_by_area.get(area_id, [])

    # Sketch shows ALL plant_groups in this area regardless of ?status=
    # (v1 comment: idea/planned plantings still visible on the sketch).
    sketch_plants = load_plants_by_area(db, garden_id, plant_statuses=None).get(
        area_id, []
    )
    L = float(area.get("length_ft") or 0)
    W = float(area.get("width_ft") or 0)
    for p in sketch_plants:
        p["positions_resolved"] = (
            resolve_plant_positions(p, L, W) if (L > 0 and W > 0) else []
        )
    sketch_children = resolve_child_area_positions(area, area["children"])
    # v1: uniform label font size in SVG user units (feet).
    parent_dim = max(L or 0, W or 0)
    child_label_size_ft = round(parent_dim * 0.014, 3) if parent_dim > 0 else 1.5

    # Planting info for the direct-plants table + v1's cluster sort.
    planting_map: dict = {}
    planting_ids = {p["planting_id"] for p in plants if p["planting_id"] is not None}
    if planting_ids:
        rows = db.execute(
            select(Planting, Year.year.label("year_value"))
            .outerjoin(Year, Year.id == Planting.year_id)
            .where(Planting.id.in_(planting_ids), Planting.garden_id == garden_id)
        ).all()
        for planting, year_value in rows:
            d = _row_dict(planting)
            d["year_value"] = year_value
            planting_map[d["id"]] = d

        def _planting_sort_key(p):
            pid = p["planting_id"]
            if pid is None:
                return (1, "", "", "", p["id"])
            pl = planting_map.get(pid, {})
            yr = pl.get("year_value") or 0
            return (0, -int(yr or 0), (pl.get("name") or "").lower(),
                    (p.get("species_name") or "").lower(),
                    (p.get("variety_name") or "").lower())

        plants = sorted(plants, key=_planting_sort_key)

    breadcrumbs = get_ancestors(area_id, by_id)
    plant_counts = get_plant_count_map(tree, plants_by_area)
    species_summary = get_species_summary_map(tree, plants_by_area)

    enriched_children = [
        {
            "id": c["id"],
            "name": c["name"],
            "dims": format_dims(c),
            "child_count": len(c["children"]),
            "plant_count": plant_counts.get(c["id"], 0),
            "summary": species_summary.get(c["id"], []),
            "featured_image_path": c.get("featured_image_path") or "",
        }
        for c in area["children"]
    ]

    return {
        "area": _area_out(area),
        "breadcrumbs": [
            {
                "id": b["id"],
                "name": b["name"],
                "short_name": (b.get("short_name") or "").strip(),
            }
            for b in breadcrumbs
        ],
        "children": enriched_children,
        "current_dims": format_dims(area),
        "current_total": plant_counts.get(area_id, 0),
        "current_summary": species_summary.get(area_id, []),
        "summary": summary,
        "plants": [
            {
                "id": p["id"],
                "planting_id": p["planting_id"],
                "planting_name": p.get("planting_name"),
                "planting_year": planting_map.get(p["planting_id"], {}).get("year_value"),
                "species_id": p["species_id"],
                "species_name": p.get("species_name"),
                "variety_id": p["variety_id"],
                "variety_name": p.get("variety_name"),
                "quantity": p.get("quantity") or "",
                "status": p.get("status") or "planted",
                "notes": p.get("notes") or "",
                "featured_image_path": p.get("featured_image_path") or "",
            }
            for p in plants
        ],
        "sketch": {
            "geometry": (
                sketch_geometry(L, W, area.get("sketch_rotation") or 0)
                if (L > 0 and W > 0)
                else None
            ),
            "plants": [
                {
                    "plant_id": p["id"],
                    "species_id": p.get("species_id"),
                    "species_name": p.get("species_name") or "",
                    "variety_id": p.get("variety_id"),
                    "variety_name": p.get("variety_name") or "",
                    "status": p.get("status") or "planted",
                    "quantity": p.get("quantity") or "",
                    "color": species_icon_color(p.get("species_name") or ""),
                    "positions_resolved": [
                        {"x": x, "y": y, "plant_count": n, "sqft": sqft}
                        for (x, y, n, sqft) in p["positions_resolved"]
                    ],
                }
                for p in sketch_plants
            ],
            "children": [
                {
                    "id": c["id"],
                    "name": c["name"],
                    "short_name": (c.get("short_name") or "").strip(),
                    "length_ft": c.get("length_ft"),
                    "width_ft": c.get("width_ft"),
                    "sketch_rotation": c.get("sketch_rotation") or 0,
                    "resolved_x": c["resolved_x"],
                    "resolved_y": c["resolved_y"],
                }
                for c in sketch_children
            ],
            "child_label_size_ft": child_label_size_ft,
        },
        "status_filter": _status_filter_name(status),
        "show_archived": show_archived,
    }