"""Plantings domain service — planting CRUD, totals, year grouping, and the
auto-archive up-cascade.

Behavior port of v1 (/srv/apps/garden/app/main.py, read-only reference):

- list grouped by year with group/plant counts ........... v1 ~4969-5003
- create with inline plant rows .......................... v1 ~5187-5238
- detail (plants + totals) ............................... v1 ~5241-5315
- update incl. plant-row upsert + delete-missing ......... v1 ~5318-5397
- delete (plant target-ref cleanup + cascade logging) .... v1 ~5400-5414
- add plant group to planting ............................ v1 ~5438-5471
- planting_total_qty ..................................... v1 ~2665
- _maybe_archive_planting (up-cascade) ................... v1 ~1657

Imports shared primitives from services/plants.py (one-directional:
plantings -> plants, never the reverse).

Carried v1 warts (on purpose):
- ``PLANTING_STATUSES = ["planted"]`` with the legacy normalization: any
  submitted status that isn't 'planted' is stored as 'planned' (v1 form
  handler residue from the pre-2026-05-14 workflow-status model).
- plantings.status has no CHECK constraint (legacy values exist in
  history); reads default a missing status to 'planted'.

Deviations from v1 (deliberate, flagged):
- v1's planting update route wrote NO activity_log rows; v2's binding rule
  is "every mutation writes activity_log", so update logs per-field diffs
  and plant-row add/delete entries.
- Inline plant rows are created with status='planted' instead of v1's ''
  (see services/plants.py docstring).
- The variety-species mismatch guard also runs on CREATE (v1 only had it
  on update/add-plant; same guard, applied uniformly).
- year_id values are validated against the garden's years (v1 let the FK
  blow up).
- DELETE keeps v1's exact outcome: the planting row is deleted and its
  plants survive with planting_id=NULL (the FK is ON DELETE SET NULL and
  v1 ran with PRAGMA foreign_keys=ON) — even though v1 logged the plants
  as "deleted (cascade from planting delete)". Quirk carried for parity.
"""
from __future__ import annotations

from sqlalchemy import case, func, select
from sqlalchemy.orm import Session

from ..errors import AppError
from ..models.garden import Year
from ..models.planting import Plant, Planting
from ..schemas.plantings import PlantingCreate, PlantingUpdate, PlantRowIn
from .audit import log_add, log_archive, log_delete, log_edit
from .plants import (
    delete_target_refs,
    get_plant,
    load_plants_for_planting,
    parse_quantity,
    plant_label,
    reconcile_variety,
    snapshot_overview,
    utc_now,
)

# --- error codes (domain: planting) ---
PLANTING_NOT_FOUND = "planting_not_found"
PLANTING_NAME_REQUIRED = "planting_name_required"
PLANTING_YEAR_NOT_FOUND = "planting_year_not_found"

# v1 main.py:81 — 'planted' is the only meaningful value under the
# unplanted-plants model; kept as a list for the legacy normalization below.
PLANTING_STATUSES = ["planted"]

# v1 list-route sort order for legacy statuses (~4982).
_STATUS_ORDER = {"idea": 0, "planned": 1, "planted": 2, "skipped": 3}


def normalize_planting_status(value: str | None) -> str:
    """v1 form-handler wart, ported exactly: default 'planned'; anything
    not in PLANTING_STATUSES collapses to 'planned'."""
    status = (value or "planned").strip()
    return status if status in PLANTING_STATUSES else "planned"


def planting_label(db: Session, planting_id: int) -> str:
    name = db.scalar(select(Planting.name).where(Planting.id == planting_id))
    return name if name is not None else f"planting #{planting_id}"


def get_or_create_year(db: Session, garden_id: int, year_value: int) -> int:
    """Port of v1 get_or_create_year (~2753), garden-scoped."""
    year_id = db.scalar(
        select(Year.id).where(Year.garden_id == garden_id, Year.year == year_value)
    )
    if year_id is not None:
        return year_id
    year = Year(
        garden_id=garden_id, year=year_value, notes="", created_at=utc_now(),
        featured_image_path="", is_archived=False,
    )
    db.add(year)
    db.flush()
    log_add(db, garden_id, "year", year.id, str(year_value))
    return year.id


def _resolve_year_id(
    db: Session, garden_id: int, new_year: int | None, year_id: int | None
) -> int | None:
    """v1 precedence: an explicit new year value wins, else the picked id."""
    if new_year:
        return get_or_create_year(db, garden_id, int(new_year))
    if year_id:
        exists = db.scalar(
            select(Year.id).where(Year.id == year_id, Year.garden_id == garden_id)
        )
        if exists is None:
            raise AppError(PLANTING_YEAR_NOT_FOUND, "year not found", 400)
        return year_id
    return None


def _get_planting(db: Session, garden_id: int, planting_id: int) -> Planting:
    planting = db.scalar(
        select(Planting).where(
            Planting.id == planting_id, Planting.garden_id == garden_id
        )
    )
    if planting is None:
        raise AppError(PLANTING_NOT_FOUND, "planting not found", 404)
    return planting


def _planting_out(planting: Planting, year_value: int | None, **extra) -> dict:
    d = {
        "id": planting.id,
        "name": planting.name,
        "year_id": planting.year_id,
        "year_value": year_value,
        "source": planting.source,
        "notes": planting.notes,
        # v1 load_plantings (~2630): missing status reads as 'planted'.
        "status": planting.status or "planted",
        "created_at": planting.created_at,
        "featured_image_path": planting.featured_image_path,
        "is_archived": bool(planting.is_archived),
    }
    d.update(extra)
    return d


# ---------------------------------------------------------------------------
# Totals + up-cascade
# ---------------------------------------------------------------------------

def planting_total_qty(
    db: Session, garden_id: int, planting_id: int, include_archived: bool = False
) -> tuple[int, int]:
    """Port of v1 planting_total_qty (~2665): (group_count, total_quantity);
    a non-numeric quantity counts as 1."""
    stmt = select(Plant.quantity).where(
        Plant.planting_id == planting_id, Plant.garden_id == garden_id
    )
    if not include_archived:
        stmt = stmt.where(Plant.is_archived == False)  # noqa: E712 (v1: = 0)
    quantities = db.scalars(stmt).all()
    return len(quantities), sum(parse_quantity(q, 1) for q in quantities)


def maybe_archive_planting(db: Session, garden_id: int, planting_id: int) -> bool:
    """Port of v1 _maybe_archive_planting (~1657): if every plant in the
    planting is archived (and there is at least one), archive the planting
    too. The archive domain calls this after any plant archive; exported
    here because the up-cascade is planting business logic."""
    total, archived = db.execute(
        select(
            func.count(),
            func.coalesce(
                func.sum(case((Plant.is_archived == True, 1), else_=0)), 0  # noqa: E712
            ),
        ).where(Plant.planting_id == planting_id, Plant.garden_id == garden_id)
    ).one()
    if not total or total != archived:
        return False
    planting = db.scalar(
        select(Planting).where(
            Planting.id == planting_id, Planting.garden_id == garden_id
        )
    )
    if planting is None or planting.is_archived:
        return False  # v1: only archive if not already archived
    planting.is_archived = True
    db.flush()
    log_archive(
        db, garden_id, "planting", planting_id,
        planting.name + " (auto: all plants archived)",
    )
    return True


# ---------------------------------------------------------------------------
# CRUD
# ---------------------------------------------------------------------------

def list_plantings(db: Session, garden_id: int, include_archived: bool = False) -> dict:
    """Port of GET /plantings (~4969): plantings grouped by year (newest
    first), each with group_count + plant_count, sorted within a group by
    legacy status order then name."""
    stmt = (
        select(Planting, Year.year)
        .join(Year, Year.id == Planting.year_id, isouter=True)
        .where(Planting.garden_id == garden_id)
    )
    if not include_archived:
        stmt = stmt.where(Planting.is_archived == False)  # noqa: E712 (v1: = 0)
    rows = db.execute(stmt).all()

    # One aggregate pass instead of v1's per-planting queries (same numbers).
    counts: dict[int, tuple[int, int]] = {}
    ids = [p.id for p, _ in rows]
    if ids:
        cstmt = select(Plant.planting_id, Plant.quantity).where(
            Plant.planting_id.in_(ids), Plant.garden_id == garden_id
        )
        if not include_archived:
            cstmt = cstmt.where(Plant.is_archived == False)  # noqa: E712
        for pid, quantity in db.execute(cstmt):
            groups, total = counts.get(pid, (0, 0))
            counts[pid] = (groups + 1, total + parse_quantity(quantity, 1))

    by_year: dict[int, list[dict]] = {}
    no_year: list[dict] = []
    for planting, year_value in rows:
        groups, total = counts.get(planting.id, (0, 0))
        d = _planting_out(
            planting, year_value, group_count=groups, plant_count=total
        )
        if year_value:
            by_year.setdefault(year_value, []).append(d)
        else:
            no_year.append(d)
    for group in list(by_year.values()) + [no_year]:
        group.sort(
            key=lambda x: (
                _STATUS_ORDER.get(x.get("status") or "planted", 2),
                x["name"].lower(),
            )
        )
    year_groups = [
        {"year": year, "plantings": group}
        for year, group in sorted(by_year.items(), key=lambda x: x[0], reverse=True)
    ]
    return {
        "year_groups": year_groups,
        "no_year": no_year,
        "total_plantings": len(rows),
    }


def get_planting(
    db: Session, garden_id: int, planting_id: int, include_archived: bool = False
) -> dict:
    """Port of the GET /plantings/{id} payload (~5241): the planting, its
    plant groups (joined names + display_name), total_qty (non-numeric
    quantity counts as 1) and group_count. ``include_archived`` widens the
    plant list (v1's ?archived=1 view); the planting itself is returned
    regardless of its archive state (the redirect dance was UI behavior)."""
    row = db.execute(
        select(Planting, Year.year)
        .join(Year, Year.id == Planting.year_id, isouter=True)
        .where(Planting.id == planting_id, Planting.garden_id == garden_id)
    ).first()
    if row is None:
        raise AppError(PLANTING_NOT_FOUND, "planting not found", 404)
    planting, year_value = row
    plants = load_plants_for_planting(
        db, garden_id, planting_id, include_archived=include_archived
    )
    total_qty = sum(parse_quantity(p["quantity"], 1) for p in plants)
    return _planting_out(
        planting,
        year_value,
        plants=plants,
        total_qty=total_qty,
        plant_count=total_qty,
        group_count=len(plants),
    )


def _plant_row_is_empty(row: PlantRowIn) -> bool:
    """v1 skip-empty-row rule (~5226): a row with no qty, species, variety,
    area, or notes is ignored (source alone does not make a row)."""
    return (
        not (row.quantity or "").strip()
        and not row.species_id
        and not row.variety_id
        and not row.area_id
        and not (row.notes or "").strip()
    )


def _insert_plant_row(
    db: Session, garden_id: int, planting_id: int, row: PlantRowIn
) -> int | None:
    """Insert one inline plant row (create/update/add-plant flows). Returns
    the new id, or None when the row is empty and skipped."""
    if _plant_row_is_empty(row):
        return None
    variety_id = reconcile_variety(db, row.species_id, row.variety_id)
    plant = Plant(
        garden_id=garden_id,
        planting_id=planting_id,
        species_id=row.species_id,
        variety_id=variety_id,
        area_id=row.area_id,
        quantity=(row.quantity or "").strip(),
        source=(row.source or "").strip(),
        # v1 inserted '' here (normalized to 'planted' at startup); v2 has a
        # CHECK constraint, so the normalized value is written directly.
        status="planted",
        notes=(row.notes or "").strip(),
        created_at=utc_now(),
        featured_image_path="",
        positions="",
        is_archived=False,
    )
    db.add(plant)
    db.flush()
    log_add(db, garden_id, "plant", plant.id, plant_label(db, plant.id))
    return plant.id


def create_planting(db: Session, garden_id: int, payload: PlantingCreate) -> dict:
    """Port of POST /plantings (~5187)."""
    name = (payload.name or "").strip()
    if not name:
        raise AppError(PLANTING_NAME_REQUIRED, "name required", 400)
    year_id = _resolve_year_id(db, garden_id, payload.new_year, payload.year_id)
    planting = Planting(
        garden_id=garden_id,
        name=name,
        year_id=year_id,
        source=(payload.source or "").strip(),
        notes=(payload.notes or "").strip(),
        status=normalize_planting_status(payload.status),
        created_at=utc_now(),
        featured_image_path="",
        is_archived=False,
    )
    db.add(planting)
    db.flush()
    log_add(db, garden_id, "planting", planting.id, name)
    for row in payload.plants:
        _insert_plant_row(db, garden_id, planting.id, row)
    return get_planting(db, garden_id, planting.id)


_UPDATABLE_ROW_FIELDS = ("species_id", "variety_id", "area_id", "quantity", "source", "notes")


def update_planting(
    db: Session, garden_id: int, planting_id: int, payload: PlantingUpdate
) -> dict:
    """Port of POST /plantings/{id} (~5318) with PATCH semantics: only
    provided fields change. When ``plants`` is provided it is the FULL set
    of rows, exactly like the v1 form: rows with an id update that plant,
    rows without insert a new one, and any existing plant NOT present in the
    array is deleted (v1's seen_plant_ids behavior). Empty rows are skipped
    — which means an existing row submitted empty gets deleted."""
    planting = _get_planting(db, garden_id, planting_id)
    provided = payload.model_fields_set

    changes: dict = {}
    if "name" in provided:
        name = (payload.name or "").strip()
        if not name:
            raise AppError(PLANTING_NAME_REQUIRED, "name required", 400)
        changes["name"] = name
    if "status" in provided:
        changes["status"] = normalize_planting_status(payload.status)
    if "source" in provided:
        changes["source"] = (payload.source or "").strip()
    if "notes" in provided:
        notes = (payload.notes or "").strip()
        # v1 snapshots the old overview text before overwriting (~5340).
        snapshot_overview(db, "planting", planting_id, planting.notes, notes)
        changes["notes"] = notes
    if "new_year" in provided and payload.new_year:
        changes["year_id"] = get_or_create_year(db, garden_id, payload.new_year)
    elif "year_id" in provided:
        changes["year_id"] = _resolve_year_id(db, garden_id, None, payload.year_id)

    label = planting.name
    for field, new_val in changes.items():
        old_val = getattr(planting, field)
        if (old_val if old_val is not None else "") != (
            new_val if new_val is not None else ""
        ):
            # v1 wrote no log rows here; v2 rule: every mutation is logged.
            log_edit(
                db, garden_id, "planting", planting_id, field, old_val, new_val,
                label=label,
            )
        setattr(planting, field, new_val)
    db.flush()

    if payload.plants is not None:
        _sync_plant_rows(db, garden_id, planting_id, payload.plants)
    return get_planting(db, garden_id, planting_id)


def _sync_plant_rows(
    db: Session, garden_id: int, planting_id: int, rows: list[PlantRowIn]
) -> None:
    """The v1 plant_qty_N form-row semantics on a clean JSON array."""
    existing = {
        p.id: p
        for p in db.scalars(
            select(Plant).where(
                Plant.planting_id == planting_id, Plant.garden_id == garden_id
            )
        )
    }
    seen: set[int] = set()
    for row in rows:
        if _plant_row_is_empty(row):
            continue  # an existing row submitted empty falls through to delete
        if row.id:
            plant = existing.get(row.id)
            if plant is None:
                continue  # v1: UPDATE ... WHERE planting_id=? no-ops foreign ids
            seen.add(row.id)
            variety_id = reconcile_variety(db, row.species_id, row.variety_id)
            new_values = {
                "species_id": row.species_id,
                "variety_id": variety_id,
                "area_id": row.area_id,
                "quantity": (row.quantity or "").strip(),
                "source": (row.source or "").strip(),
                "notes": (row.notes or "").strip(),
            }
            snapshot_overview(db, "plant", plant.id, plant.notes, new_values["notes"])
            old_values = {f: getattr(plant, f) for f in _UPDATABLE_ROW_FIELDS}
            for field, value in new_values.items():
                setattr(plant, field, value)
            db.flush()
            label = plant_label(db, plant.id)
            for field, new_val in new_values.items():
                old_val = old_values[field]
                if (old_val if old_val is not None else "") != (
                    new_val if new_val is not None else ""
                ):
                    log_edit(
                        db, garden_id, "plant", plant.id, field, old_val, new_val,
                        label=label,
                    )
        else:
            new_id = _insert_plant_row(db, garden_id, planting_id, row)
            if new_id is not None:
                seen.add(new_id)

    # Delete plant rows that were removed from the submitted set (v1 ~5388).
    for plant_id, plant in existing.items():
        if plant_id not in seen:
            label = plant_label(db, plant_id)
            delete_target_refs(db, "plant", plant_id)
            db.delete(plant)
            log_delete(
                db, garden_id, "plant", plant_id, label + " (removed from planting)"
            )
    db.flush()


def delete_planting(db: Session, garden_id: int, planting_id: int) -> dict:
    """Port of POST /plantings/{id}/delete (~5400). v1 quirk carried: the
    plants themselves are NOT deleted — the FK sets their planting_id to
    NULL — but their field-note target refs are removed and a delete log
    row is written for each (exactly what v1 did)."""
    planting = _get_planting(db, garden_id, planting_id)
    label = planting.name
    plant_ids = db.scalars(
        select(Plant.id).where(
            Plant.planting_id == planting_id, Plant.garden_id == garden_id
        )
    ).all()
    for plant_id in plant_ids:
        plant_lbl = plant_label(db, plant_id)
        delete_target_refs(db, "plant", plant_id)
        log_delete(
            db, garden_id, "plant", plant_id,
            plant_lbl + " (cascade from planting delete)",
        )
    delete_target_refs(db, "planting", planting_id)
    db.delete(planting)
    db.flush()  # FK ON DELETE SET NULL detaches the plants
    log_delete(db, garden_id, "planting", planting_id, label)
    return {"ok": True}


def add_plant_to_planting(
    db: Session, garden_id: int, planting_id: int, row: PlantRowIn
) -> dict:
    """Port of POST /plantings/{id}/plants (~5438): inline plant-group add
    from the planting detail page."""
    _get_planting(db, garden_id, planting_id)  # 404 when unknown
    variety_id = reconcile_variety(db, row.species_id, row.variety_id)
    plant = Plant(
        garden_id=garden_id,
        planting_id=planting_id,
        species_id=row.species_id,
        variety_id=variety_id,
        area_id=row.area_id,
        quantity=(row.quantity or "").strip(),
        source=(row.source or "").strip(),
        status="planted",  # v1 '' (see _insert_plant_row note)
        notes=(row.notes or "").strip(),
        created_at=utc_now(),
        featured_image_path="",
        positions="",
        is_archived=False,
    )
    db.add(plant)
    db.flush()
    log_add(db, garden_id, "plant", plant.id, plant_label(db, plant.id))
    return get_plant(db, garden_id, plant.id)