"""The nine v1 chat tools, ported EXACTLY (main.py ≈3026-3591) — garden-scoped.

get_planting_detail, get_area_detail, get_species_detail, find_plants,
find_empty_space, get_recent_notes, search_notes, list_artifacts,
read_artifact.

v1's tenth tool, ``suggest_new_tool``, is NOT ported (approved stale-drop;
the ``tool_suggestions`` table is migrated for history only — see
models/assistant.py).

Port notes:

- Every query gains the D1 garden scope; archived rows are excluded exactly
  where v1 excluded them.
- ``search_notes`` uses ILIKE — SQLite's LIKE is case-insensitive for
  ASCII, Postgres's is not; ILIKE preserves the v1 behavior.
- Ordering on nullable years/dates adds ``NULLS LAST`` — SQLite sorts NULLs
  last on DESC, Postgres first; the explicit modifier preserves v1 order.
- The "currently planted" style quantity parsing keeps v1 semantics per
  call site (documented inline).
- Area tree work reuses services/notes.py's maps (same v1-port helpers used
  by the inference engine); artifacts reuse services/artifacts.py.

Output shapes are v1-verbatim (the model-facing contract). Keep tool output
compact — tokens still cost money, and large blobs make the model lose
focus (v1 comment, still true).
"""
from __future__ import annotations

import os
from typing import Annotated, Optional

from pydantic import Field
from sqlalchemy import and_, func, select
from sqlalchemy.orm import Session

from ....models.catalog import Species, Variety
from ....models.garden import Area, AreaStation, WateringStation, Year
from ....models.notes import FieldNote, FieldNoteTarget
from ....models.planting import Plant, Planting
from ... import artifacts as artifacts_svc
from ...notes import (
    COMMENT_TARGET_TYPES,
    area_path_name,
    get_descendant_ids,
    load_area_maps,
)
from ..registry import tool

_ARTIFACT_CONTENT_CAP = 8000


def _truncate(s, limit: int = 600) -> str:
    """v1 ``_chat_truncate_text`` (main.py:3019)."""
    if not s:
        return ""
    s = str(s)
    return s if len(s) <= limit else s[: limit - 1] + "…"


def _not_archived(col):
    return col.isnot(True)


def _area_maps(db: Session, garden_id: int) -> dict:
    """Non-archived area map (v1 tools built theirs from ``load_areas(con)``,
    which excludes archived areas by default)."""
    return load_area_maps(db, garden_id, include_archived=False)


@tool(
    "get_planting_detail",
    "Full detail for one planting: status, source, overview, and every "
    "plant group in it (with species/variety/area/qty).",
)
def get_planting_detail(
    db: Session,
    garden_id: int,
    *,
    planting_id: Annotated[int, Field(description="id from the planting list")],
) -> dict:
    row = db.execute(
        select(Planting, Year.year.label("year_value"))
        .outerjoin(Year, Year.id == Planting.year_id)
        .where(Planting.id == planting_id, Planting.garden_id == garden_id)
    ).first()
    if not row:
        return {"error": f"planting #{planting_id} not found"}
    p_row, year_value = row
    plants = db.execute(
        select(
            Plant.id,
            Plant.quantity,
            Plant.status,
            Plant.area_id,
            Plant.notes,
            Species.name.label("species"),
            Variety.name.label("variety"),
        )
        .outerjoin(Species, Species.id == Plant.species_id)
        .outerjoin(Variety, Variety.id == Plant.variety_id)
        .where(
            Plant.planting_id == planting_id,
            Plant.garden_id == garden_id,
            _not_archived(Plant.is_archived),
        )
        .order_by(Plant.id)
    ).all()
    by_id = _area_maps(db, garden_id)
    return {
        "id": p_row.id,
        "name": p_row.name,
        "year": year_value,
        "status": p_row.status,
        "source": p_row.source or "",
        "notes": _truncate(p_row.notes, 1500),
        "plants": [
            {
                "id": p.id,
                "qty": p.quantity,
                "status": p.status or "",
                "species": p.species,
                "variety": p.variety,
                "area_id": p.area_id,
                "area_path": (
                    area_path_name(p.area_id, by_id) if p.area_id else None
                ),
                "notes": _truncate(p.notes, 300),
            }
            for p in plants
        ],
    }


@tool(
    "get_area_detail",
    "Full detail for one area: dimensions, sunlight, soil, structure, "
    "sub-areas, every plant in the subtree, and stations serving it.",
)
def get_area_detail(db: Session, garden_id: int, *, area_id: int) -> dict:
    by_id = _area_maps(db, garden_id)
    if area_id not in by_id:
        return {"error": f"area #{area_id} not found"}
    area = db.execute(
        select(Area).where(Area.id == area_id, Area.garden_id == garden_id)
    ).scalar_one()
    desc_ids = get_descendant_ids(area_id, by_id) | {area_id}
    plants = db.execute(
        select(
            Plant.id,
            Plant.quantity,
            Plant.area_id,
            Plant.status,
            Species.name.label("species"),
            Variety.name.label("variety"),
            Planting.name.label("planting"),
            Planting.id.label("planting_id"),
        )
        .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,
            _not_archived(Plant.is_archived),
        )
        .order_by(Plant.id)
    ).all()
    # v1 load_stations_for_area: stations DIRECTLY assigned to this area
    # (assignment is fully explicit in the station form), non-archived.
    stations = db.execute(
        select(WateringStation.id, WateringStation.name)
        .join(AreaStation, AreaStation.station_id == WateringStation.id)
        .where(
            AreaStation.area_id == area_id,
            _not_archived(WateringStation.is_archived),
        )
        .order_by(WateringStation.name)
    ).all()
    return {
        "id": area.id,
        "name": area.name,
        "short_name": area.short_name or "",
        "path": area_path_name(area_id, by_id),
        "length_ft": area.length_ft or 0,
        "width_ft": area.width_ft or 0,
        "garden_area_sqft": area.garden_area_sqft or 0,
        "structure_features": _truncate(area.structure_features, 500),
        "sunlight": _truncate(area.sunlight, 500),
        "soil_environment": _truncate(area.soil_environment, 500),
        "notes": _truncate(area.notes, 1500),
        "subareas": [
            {"id": cid, "name": by_id[cid]["name"]}
            for cid in by_id[area_id]["children"]
        ],
        "plants": [
            {
                "id": p.id,
                "qty": p.quantity,
                "species": p.species,
                "variety": p.variety,
                "planting": p.planting,
                "planting_id": p.planting_id,
                "area_id": p.area_id,
            }
            for p in plants
        ],
        "stations": [{"id": s.id, "name": s.name} for s in stations],
    }


@tool(
    "get_species_detail",
    "Species info: type/function, spacing, varieties, and which plantings "
    "currently grow it.",
)
def get_species_detail(db: Session, garden_id: int, *, species_id: int) -> dict:
    s = db.execute(
        select(Species).where(
            Species.id == species_id, Species.garden_id == garden_id
        )
    ).scalar_one_or_none()
    if s is None:
        return {"error": f"species #{species_id} not found"}
    varieties = db.execute(
        select(Variety.id, Variety.name, Variety.description)
        .where(
            Variety.species_id == species_id,
            _not_archived(Variety.is_archived),
        )
        .order_by(Variety.name)
    ).all()
    plantings = db.execute(
        select(Planting.id, Planting.name, Year.year)
        .distinct()
        .select_from(Plant)
        .join(Planting, Planting.id == Plant.planting_id)
        .outerjoin(Year, Year.id == Planting.year_id)
        .where(
            Plant.species_id == species_id,
            Plant.garden_id == garden_id,
            _not_archived(Plant.is_archived),
            _not_archived(Planting.is_archived),
        )
        .order_by(Year.year.desc().nulls_last(), Planting.name)
    ).all()
    return {
        "id": s.id,
        "name": s.name,
        "common_name": s.common_name or "",
        "type": s.type or "plant",
        "primary_function": s.primary_function or "edible",
        "plants_per_unit": s.plants_per_unit or 1,
        "space_per_unit_sqft": s.space_per_unit_sqft or 1.0,
        "description": _truncate(s.description, 1500),
        "varieties": [
            {
                "id": v.id,
                "name": v.name,
                "description": _truncate(v.description, 200),
            }
            for v in varieties
        ],
        "current_plantings": [
            {"id": p.id, "name": p.name, "year": p.year} for p in plantings
        ],
    }


@tool(
    "find_plants",
    "Browse plant groups. All filters optional — call with no args to get "
    "up to 50 non-archived plants across the whole garden. Filter "
    "species/variety by substring (case-insensitive). Filter status by "
    "exact value: 'planted' (in the ground), 'unplanted' (staged), 'idea' "
    "(planning), 'removed' (terminal).",
)
def find_plants(
    db: Session,
    garden_id: int,
    *,
    species: str = "",
    variety: str = "",
    status: Annotated[
        str, Field(description="planted | unplanted | idea | removed")
    ] = "",
) -> dict:
    species = (species or "").strip()
    variety = (variety or "").strip()
    status = (status or "").strip()
    conds = [Plant.garden_id == garden_id, _not_archived(Plant.is_archived)]
    if species:
        conds.append(Species.name.ilike(f"%{species}%"))
    if variety:
        conds.append(Variety.name.ilike(f"%{variety}%"))
    if status:
        conds.append(Plant.status == status)
    rows = db.execute(
        select(
            Plant.id,
            Plant.quantity,
            Plant.area_id,
            Species.name.label("species"),
            Variety.name.label("variety"),
            Planting.id.label("planting_id"),
            Planting.name.label("planting"),
            Year.year.label("year"),
        )
        .outerjoin(Species, Species.id == Plant.species_id)
        .outerjoin(Variety, Variety.id == Plant.variety_id)
        .outerjoin(Planting, Planting.id == Plant.planting_id)
        .outerjoin(Year, Year.id == Planting.year_id)
        .where(*conds)
        .order_by(Year.year.desc().nulls_last(), Plant.id)
        .limit(50)
    ).all()
    by_id = _area_maps(db, garden_id)
    return {
        "matches": [
            {
                "plant_id": r.id,
                "qty": r.quantity,
                "species": r.species,
                "variety": r.variety,
                "planting_id": r.planting_id,
                "planting": r.planting,
                "year": r.year,
                "area_id": r.area_id,
                "area_path": (
                    area_path_name(r.area_id, by_id) if r.area_id else None
                ),
            }
            for r in rows
        ],
        "count": len(rows),
    }


@tool(
    "find_empty_space",
    "List leaf areas with free space (capacity minus occupied sqft). Pass "
    "min_sqft to filter; pass area_id to restrict to one subtree.",
)
def find_empty_space(
    db: Session,
    garden_id: int,
    *,
    area_id: Optional[int] = None,
    min_sqft: Annotated[float, Field(description="default 1.0")] = 1.0,
) -> dict:
    """Heuristic (v1 verbatim): per LEAF area, sum the effective
    space_per_unit_sqft of plants occupying it; subtract from the explicit
    garden_area_sqft (or length x width) to get rough free space."""
    by_id = _area_maps(db, garden_id)
    if area_id is not None and area_id not in by_id:
        return {"error": f"area #{area_id} not found"}
    if area_id is not None:
        candidate_ids = list(get_descendant_ids(area_id, by_id) | {area_id})
    else:
        candidate_ids = list(by_id)
    if not candidate_ids:
        return {"matches": [], "count": 0}
    occ_rows = db.execute(
        select(
            Plant.area_id,
            func.coalesce(
                Variety.space_per_unit_sqft, Species.space_per_unit_sqft, 1.0
            ).label("sqft"),
            func.coalesce(
                Variety.plants_per_unit, Species.plants_per_unit, 1
            ).label("ppu"),
            Plant.quantity,
        )
        .outerjoin(Species, Species.id == Plant.species_id)
        .outerjoin(Variety, Variety.id == Plant.variety_id)
        .where(
            Plant.area_id.in_(candidate_ids),
            Plant.garden_id == garden_id,
            _not_archived(Plant.is_archived),
        )
    ).all()
    occ_by_area: dict = {}
    for r in occ_rows:
        # v1 quantity parse for THIS tool: int((qty or "1").strip()) or 1,
        # ValueError -> 1 ('' and free-form both land on 1; '0' -> 1).
        try:
            qty = int((r.quantity or "1").strip()) or 1
        except (TypeError, ValueError):
            qty = 1
        ppu = max(int(r.ppu or 1), 1)
        sqft = float(r.sqft or 1.0) * (qty / ppu)
        occ_by_area[r.area_id] = occ_by_area.get(r.area_id, 0) + sqft
    # Dimensions for the candidates (v1 had them on the loaded area dicts).
    dims = {
        a.id: a
        for a in db.execute(
            select(
                Area.id,
                Area.garden_area_sqft,
                Area.length_ft,
                Area.width_ft,
                Area.sunlight,
                Area.soil_environment,
            ).where(Area.id.in_(candidate_ids))
        ).all()
    }
    matches: list = []
    for aid in candidate_ids:
        node = by_id[aid]
        d = dims.get(aid)
        if d is None:
            continue
        # Prefer explicit garden_area_sqft; else compute from L x W; else skip.
        cap = float(d.garden_area_sqft or 0)
        if not cap:
            cap = float(d.length_ft or 0) * float(d.width_ft or 0)
        if not cap:
            continue
        # Skip non-leaf areas with sub-areas — their plants live in children.
        if node["children"]:
            continue
        used = occ_by_area.get(aid, 0)
        free = cap - used
        if free >= min_sqft:
            matches.append(
                {
                    "area_id": aid,
                    "path": area_path_name(aid, by_id),
                    "capacity_sqft": round(cap, 1),
                    "used_sqft": round(used, 1),
                    "free_sqft": round(free, 1),
                    "sunlight": _truncate(d.sunlight, 200),
                    "soil_environment": _truncate(d.soil_environment, 200),
                }
            )
    matches.sort(key=lambda m: m["free_sqft"], reverse=True)
    return {"matches": matches[:25], "count": len(matches)}


@tool(
    "get_recent_notes",
    "Up to 100 recent field notes attached to a specific entity. Pass "
    "target_type ('area', 'planting', 'plant', 'species', 'variety', "
    "'station', 'year') and target_id.",
)
def get_recent_notes(
    db: Session,
    garden_id: int,
    *,
    target_type: str,
    target_id: int,
    limit: Annotated[int, Field(description="default 20")] = 20,
) -> dict:
    if target_type not in COMMENT_TARGET_TYPES:
        return {"error": f"unknown target_type {target_type!r}"}
    limit = max(1, min(int(limit), 100))
    rows = db.execute(
        select(
            FieldNote.id,
            FieldNote.comment_date,
            FieldNote.body,
            FieldNote.kind,
            FieldNoteTarget.is_primary,
        )
        .join(FieldNoteTarget, FieldNoteTarget.field_note_id == FieldNote.id)
        .where(
            FieldNoteTarget.target_type == target_type,
            FieldNoteTarget.target_id == target_id,
            FieldNote.garden_id == garden_id,
            _not_archived(FieldNote.is_archived),
            FieldNote.body.isnot(None),
            FieldNote.body != "",
            # App feedback is QA channel, not garden context (NULL-safe).
            FieldNote.kind.is_distinct_from("feedback"),
        )
        .order_by(
            FieldNote.comment_date.desc().nulls_last(), FieldNote.id.desc()
        )
        .limit(limit)
    ).all()
    return {
        "notes": [
            {
                "id": r.id,
                "date": r.comment_date.isoformat() if r.comment_date else None,
                "kind": r.kind or "",
                "body": _truncate(r.body, 800),
                "is_primary": bool(r.is_primary),
            }
            for r in rows
        ],
        "count": len(rows),
    }


@tool(
    "search_notes",
    "Substring search across all field-note bodies (case-insensitive). "
    "Returns recent matches first.",
)
def search_notes(
    db: Session,
    garden_id: int,
    *,
    query: str,
    limit: Annotated[int, Field(description="default 25")] = 25,
) -> dict:
    q = (query or "").strip()
    if not q:
        return {"error": "supply a non-empty query"}
    limit = max(1, min(int(limit), 100))
    primary = FieldNoteTarget
    rows = db.execute(
        select(
            FieldNote.id,
            FieldNote.comment_date,
            FieldNote.body,
            FieldNote.kind,
            primary.target_type,
            primary.target_id,
        )
        .outerjoin(
            primary,
            and_(
                primary.field_note_id == FieldNote.id,
                primary.is_primary.is_(True),
            ),
        )
        .where(
            FieldNote.garden_id == garden_id,
            _not_archived(FieldNote.is_archived),
            FieldNote.body.ilike(f"%{q}%"),
            # App feedback is QA channel, not garden context (NULL-safe).
            FieldNote.kind.is_distinct_from("feedback"),
        )
        .order_by(
            FieldNote.comment_date.desc().nulls_last(), FieldNote.id.desc()
        )
        .limit(limit)
    ).all()
    return {
        "matches": [
            {
                "id": r.id,
                "date": r.comment_date.isoformat() if r.comment_date else None,
                "primary_type": r.target_type,
                "primary_id": r.target_id,
                "body": _truncate(r.body, 400),
            }
            for r in rows
        ],
        "count": len(rows),
    }


@tool(
    "list_artifacts",
    "List AI-generated artifacts (planting plans, fertilization schedules, "
    "watering plans, etc.).",
)
def list_artifacts(db: Session, garden_id: int) -> dict:
    rows = artifacts_svc.list_artifacts(db, garden_id)["artifacts"]
    return {
        "artifacts": [
            {
                "id": a["id"],
                "kind": a["kind"],
                "title": a["title"],
                "filename": a["filename"],
                "generated_at": a["generated_at"],
                "notes": _truncate(a["notes"], 200),
            }
            for a in rows
        ],
        "count": len(rows),
    }


@tool(
    "read_artifact",
    "Read the full markdown content of one artifact by id (truncated to "
    "~8K chars).",
)
def read_artifact(db: Session, garden_id: int, *, artifact_id: int) -> dict:
    from ....errors import AppError
    from ....models.history import Artifact

    a = db.execute(
        select(Artifact).where(
            Artifact.id == artifact_id, Artifact.garden_id == garden_id
        )
    ).scalar_one_or_none()
    if a is None:
        return {"error": f"artifact #{artifact_id} not found"}
    path = os.path.join(artifacts_svc._artifacts_dir(), a.filename)
    try:
        with open(path, encoding="utf-8") as f:
            content = f.read()
    except OSError as e:
        return {"error": f"could not read file: {e}"}
    # Cap length so a giant plan doesn't blow the context (v1 comment).
    return {
        "id": a.id,
        "kind": a.kind,
        "title": a.title,
        "generated_at": a.generated_at.isoformat() if a.generated_at else None,
        "content": _truncate(content, _ARTIFACT_CONTENT_CAP),
        "truncated": len(content) > _ARTIFACT_CONTENT_CAP,
        "full_length": len(content),
    }