"""Starter-context snapshot for chat turns — port of v1 build_starter_context
(main.py ≈2774-3008), garden-scoped.

A compact garden state description prepended (via the system prompt) to
every chat turn, targeted at ~3-5K tokens: current date + season, area
names + dims, species catalogue, what's actually planted right now, staged
pools, plantings, and three recent-notes sections (care actions 60d /
issues 60d / other notes 30d). Anything deeper happens through tools.

Port notes:

- v1's planted/staged aggregation used SQLite ``CAST(NULLIF(quantity,'')
  AS INTEGER)``, which parses a leading integer and yields 0 for non-numeric
  text. Postgres CAST would ERROR on "a few", so the aggregation runs in
  Python with ``_sqlite_int`` reproducing SQLite's cast semantics.
- ``GROUP_CONCAT(DISTINCT ...)`` becomes Python set aggregation (variety
  and species name lists are sorted for determinism — SQLite's order was
  arbitrary).
- DESC ordering on nullable year adds NULLS LAST (SQLite's DESC order).
- Area tree/label helpers are reused from services/notes.py (the same
  v1-port helpers the inference engine uses); dims come from the Area rows.
"""
from __future__ import annotations

import re
from datetime import datetime, timedelta, timezone

from sqlalchemy import and_, select
from sqlalchemy.orm import Session

from ...models.catalog import Species, Variety
from ...models.garden import Area, Year
from ...models.notes import FieldNote, FieldNoteTarget
from ...models.planting import Plant, Planting
from ..notes import (
    area_path_name,
    load_area_maps,
    target_short_label,
)

_INT_PREFIX = re.compile(r"^[-+]?\d+")


def _sqlite_int(value) -> int | None:
    """SQLite ``CAST(NULLIF(x, '') AS INTEGER)``: '' -> None, leading
    integer parsed, anything else -> 0."""
    s = (value or "").strip() if isinstance(value, str) else value
    if s is None or s == "":
        return None
    m = _INT_PREFIX.match(str(s))
    return int(m.group()) if m else 0


def _short(value, limit: int = 80) -> str:
    if value is None:
        return ""
    s = str(value)
    if len(s) > limit:
        s = s[: limit - 1] + "…"
    return s


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


def _season_for(month: int) -> str:
    """v1's month -> season hint (northern-hemisphere default)."""
    return (
        "winter (dormant)" if month in (12, 1, 2) else
        "early spring (cool-season planting)" if month == 3 else
        "spring (last frost / transplant season)" if month in (4, 5) else
        "early summer (warm-season established)" if month == 6 else
        "summer (peak growth, harvest, fertilize heavy feeders)" if month in (7, 8) else
        "early fall (cool-crop second planting / harvest preserving)" if month == 9 else
        "fall (winding down, plant garlic/cover crops)" if month in (10, 11) else
        "unknown"
    )


def _primary_target_join():
    return and_(
        FieldNoteTarget.field_note_id == FieldNote.id,
        FieldNoteTarget.is_primary.is_(True),
    )


def _note_rows(db: Session, garden_id: int, *conds, limit: int):
    return db.execute(
        select(
            FieldNote.id,
            FieldNote.comment_date,
            FieldNote.body,
            FieldNote.kind,
            FieldNoteTarget.target_type,
            FieldNoteTarget.target_id,
        )
        .outerjoin(FieldNoteTarget, _primary_target_join())
        .where(
            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) —
            # same exclusion as the diary loaders in services/notes.py.
            FieldNote.kind.is_distinct_from("feedback"),
            *conds,
        )
        .order_by(
            FieldNote.comment_date.desc().nulls_last(), FieldNote.id.desc()
        )
        .limit(limit)
    ).all()


def _note_line(db: Session, n, by_id: dict, body_cap: int, show_kind: bool = False) -> str:
    try:
        tgt = (
            target_short_label(db, n.target_type, n.target_id, by_id)
            if n.target_type
            else "?"
        )
    except Exception:
        tgt = "?"
    kind = f" [{n.kind}]" if (show_kind and n.kind) else ""
    date_s = n.comment_date.isoformat() if n.comment_date else "?"
    return f"- {date_s} on {n.target_type or '?'}:{tgt}{kind}: {_short(n.body, body_cap)}"


def build_starter_context(db: Session, garden_id: int) -> str:
    """Compact garden snapshot prepended to every chat turn (v1 verbatim
    sections, garden-scoped)."""
    today = datetime.now(timezone.utc)
    today_iso = today.date().isoformat()
    season = _season_for(today.month)
    parts: list = []
    parts.append(f"Today: {today_iso} — {season} (northern hemisphere assumption)")

    # Areas: id, full path, dims. Keep the path compact.
    by_id = load_area_maps(db, garden_id, include_archived=False)
    area_rows = db.execute(
        select(
            Area.id, Area.short_name, Area.length_ft, Area.width_ft
        ).where(Area.garden_id == garden_id, _not_archived(Area.is_archived))
    ).all()
    parts.append("\n## Areas")
    for a in sorted(area_rows, key=lambda x: area_path_name(x.id, by_id).lower()):
        path = area_path_name(a.id, by_id)
        L, W = a.length_ft or 0, a.width_ft or 0
        dims = f" {L:g}×{W:g}ft" if (L and W) else ""
        sn = f" [{a.short_name}]" if a.short_name else ""
        parts.append(f"- #{a.id} {path}{sn}{dims}")

    # Species CATALOGUE: id, name, type/function. NOT the same as what's
    # currently planted — that's the next section.
    parts.append("\n## Species catalogue (types defined; not what's currently planted)")
    sp_rows = db.execute(
        select(
            Species.id, Species.name, Species.common_name,
            Species.type, Species.primary_function,
        )
        .where(Species.garden_id == garden_id, _not_archived(Species.is_archived))
        .order_by(Species.name)
    ).all()
    for s in sp_rows:
        cn = f" ({s.common_name})" if s.common_name else ""
        parts.append(f"- #{s.id} {s.name}{cn} — {s.type}/{s.primary_function}")

    # One fetch of all non-archived plants (+names) feeds the planted/
    # staged/plantings aggregations below (v1 ran three GROUP BY queries;
    # Python aggregation reproduces the same lines — see module docstring).
    plant_rows = db.execute(
        select(
            Plant.id,
            Plant.status,
            Plant.quantity,
            Plant.species_id,
            Plant.planting_id,
            Species.name.label("species_name"),
            Variety.name.label("variety_name"),
        )
        .outerjoin(Species, Species.id == Plant.species_id)
        .outerjoin(Variety, Variety.id == Plant.variety_id)
        .where(Plant.garden_id == garden_id, _not_archived(Plant.is_archived))
    ).all()

    # WHAT'S ACTUALLY PLANTED, aggregated by species.
    parts.append("\n## Currently planted (status=planted, by species)")
    planted: dict = {}
    for p in plant_rows:
        if p.status != "planted":
            continue
        agg = planted.setdefault(
            p.species_id,
            {"name": p.species_name, "qty": None, "groups": 0, "varieties": set()},
        )
        agg["groups"] += 1
        q = _sqlite_int(p.quantity)
        if q is not None:
            agg["qty"] = (agg["qty"] or 0) + q
        if p.variety_name:
            agg["varieties"].add(p.variety_name)
    if planted:
        for agg in sorted(
            planted.values(),
            key=lambda a: (-(a["qty"] or 0), (a["name"] or "").lower()),
        ):
            sp = agg["name"] or "(unknown species)"
            qty = agg["qty"] or "?"
            grps = agg["groups"]
            varieties_short = ""
            if agg["varieties"]:
                vs = sorted(agg["varieties"])
                shown = vs[:4]
                more = len(vs) - len(shown)
                varieties_short = f" — varieties: {', '.join(shown)}" + (
                    f" +{more} more" if more else ""
                )
            parts.append(
                f"- {sp}: {qty} plants in {grps} group"
                f"{'s' if grps != 1 else ''}{varieties_short}"
            )
    else:
        parts.append("- (nothing currently planted; check Unplanted/Ideas pools below)")

    # UNPLANTED + IDEA pools.
    pools: dict = {}
    for p in plant_rows:
        if p.status not in ("unplanted", "idea"):
            continue
        agg = pools.setdefault(
            (p.status, p.species_id),
            {"status": p.status, "name": p.species_name, "qty": None, "groups": 0},
        )
        agg["groups"] += 1
        q = _sqlite_int(p.quantity)
        if q is not None:
            agg["qty"] = (agg["qty"] or 0) + q
    if pools:
        parts.append("\n## Staged (not yet planted)")
        for agg in sorted(
            pools.values(), key=lambda a: (a["status"], -(a["qty"] or 0))
        ):
            sp = agg["name"] or "(unknown)"
            parts.append(
                f"- {agg['status']}: {sp} — {agg['qty'] or '?'} plants "
                f"({agg['groups']} groups)"
            )

    # Plantings: id, year, name, status, plant count + species breakdown.
    parts.append("\n## Plantings (each represents one ground-planting event)")
    pl_rows = db.execute(
        select(Planting.id, Planting.name, Planting.status, Year.year)
        .outerjoin(Year, Year.id == Planting.year_id)
        .where(
            Planting.garden_id == garden_id,
            _not_archived(Planting.is_archived),
        )
        .order_by(Year.year.desc().nulls_last(), Planting.name)
    ).all()
    plants_by_planting: dict = {}
    for p in plant_rows:
        if p.planting_id:
            info = plants_by_planting.setdefault(
                p.planting_id, {"count": 0, "species": set()}
            )
            info["count"] += 1
            if p.species_name:
                info["species"].add(p.species_name)
    for pl in pl_rows:
        info = plants_by_planting.get(pl.id, {"count": 0, "species": set()})
        yr = f"{pl.year} — " if pl.year else ""
        species_str = ""
        if info["species"]:
            sp_list = sorted(info["species"])
            shown = sp_list[:5]
            extra = len(sp_list) - len(shown)
            species_str = f" [{', '.join(shown)}" + (
                f" +{extra} more]" if extra else "]"
            )
        parts.append(
            f"- #{pl.id} {yr}{pl.name} ({pl.status or 'planted'}, "
            f"{info['count']} plants){species_str}"
        )

    # Recent CARE ACTIONS — done-tagged notes from the last 60 days. This is
    # the critical context for "should I fertilize / water / treat?"
    # questions: without it the model recommends things the user already did.
    care_cutoff = (today - timedelta(days=60)).date()
    parts.append(
        "\n## Recent care actions (done-tagged notes, last 60 days)\n"
        "Use this to know what the user has ALREADY done. Don't recommend "
        "actions that have already been taken recently."
    )
    care_rows = _note_rows(
        db, garden_id,
        FieldNote.kind == "done",
        FieldNote.comment_date >= care_cutoff,
        limit=80,
    )
    if care_rows:
        # Longer bodies (300 chars) so dosage/coverage detail survives.
        parts.extend(_note_line(db, n, by_id, 300) for n in care_rows)
    else:
        parts.append("- (no care actions logged in the last 60 days)")

    # Recent ISSUES — same window, problems the user flagged.
    issue_rows = _note_rows(
        db, garden_id,
        FieldNote.kind == "issue",
        FieldNote.comment_date >= care_cutoff,
        limit=30,
    )
    if issue_rows:
        parts.append("\n## Open / recent issues (issue-tagged notes, last 60 days)")
        parts.extend(_note_line(db, n, by_id, 300) for n in issue_rows)

    # Other recent notes: observations, questions, ideas, generic notes.
    # 30-day window — older than that, use search_notes.
    parts.append("\n## Other recent notes (observations/questions/ideas, last 30 days)")
    note_cutoff_30 = (today - timedelta(days=30)).date()
    from sqlalchemy import func as sa_func

    note_rows = _note_rows(
        db, garden_id,
        FieldNote.comment_date >= note_cutoff_30,
        sa_func.coalesce(FieldNote.kind, "").notin_(["done", "issue"]),
        limit=40,
    )
    if note_rows:
        parts.extend(_note_line(db, n, by_id, 180, show_kind=True) for n in note_rows)
    else:
        parts.append("- (no other notes in the last 30 days)")

    return "\n".join(parts)