"""
Draft migration for the AI planner schema. NOT WIRED INTO init_db YET.

See /srv/apps/garden/PLANNING_ai_planner.md for the full reasoning and the
open questions that should be resolved before merging this into main.py.

To apply: paste the body of `migrate_ai_planner()` into init_db() in main.py,
just after the existing `comments.kind` block (~line 300), then restart the
garden container.

Idempotent — safe to run multiple times. Pure additive, no data rewrites.
"""


def migrate_ai_planner(con):
    # Species: structured planning/filter fields (2026-04 planner schema).
    # - mature_spacing_sqft: NULL = unknown. Enables area capacity calc.
    # - rotation_family: closed list enforced in app (brassica/solanaceae/
    #   legume/allium/cucurbit/umbellifer/chenopod/grass/perennial/other).
    # - sun_pref: full/part-sun/part-shade/shade. Matches areas.sunlight.
    # - life_cycle: annual/biennial/perennial. Tells AI if rotation applies.
    # - edible_part: fruit/leaf/root/stem/flower/tuber/none.
    # - notes_local: rian's personal notes/preferences for this species.
    species_cols = {r[1] for r in con.execute("PRAGMA table_info(species)")}
    if "mature_spacing_sqft" not in species_cols:
        con.execute("ALTER TABLE species ADD COLUMN mature_spacing_sqft REAL")
    if "rotation_family" not in species_cols:
        con.execute("ALTER TABLE species ADD COLUMN rotation_family TEXT DEFAULT ''")
    if "sun_pref" not in species_cols:
        con.execute("ALTER TABLE species ADD COLUMN sun_pref TEXT DEFAULT ''")
    if "life_cycle" not in species_cols:
        con.execute("ALTER TABLE species ADD COLUMN life_cycle TEXT DEFAULT ''")
    if "edible_part" not in species_cols:
        con.execute("ALTER TABLE species ADD COLUMN edible_part TEXT DEFAULT ''")
    if "notes_local" not in species_cols:
        con.execute("ALTER TABLE species ADD COLUMN notes_local TEXT DEFAULT ''")

    # Variety: overrides. NULL on spacing = inherit from parent species.
    variety_cols = {r[1] for r in con.execute("PRAGMA table_info(varieties)")}
    if "mature_spacing_sqft" not in variety_cols:
        con.execute("ALTER TABLE varieties ADD COLUMN mature_spacing_sqft REAL")
    if "harvest_window" not in variety_cols:
        con.execute("ALTER TABLE varieties ADD COLUMN harvest_window TEXT DEFAULT ''")
    if "flavor_notes" not in variety_cols:
        con.execute("ALTER TABLE varieties ADD COLUMN flavor_notes TEXT DEFAULT ''")

    # Plants: outcome ratings at instance level. All nullable — "not rated
    # yet" is distinct from "rated poorly". AI aggregates across instances
    # to form a variety impression when asked.
    plant_cols = {r[1] for r in con.execute("PRAGMA table_info(plants)")}
    if "flavor_rating" not in plant_cols:
        con.execute("ALTER TABLE plants ADD COLUMN flavor_rating INTEGER")
    if "vigor_rating" not in plant_cols:
        con.execute("ALTER TABLE plants ADD COLUMN vigor_rating INTEGER")
    if "would_plant_again" not in plant_cols:
        con.execute("ALTER TABLE plants ADD COLUMN would_plant_again INTEGER")
    if "outcome_summary" not in plant_cols:
        con.execute("ALTER TABLE plants ADD COLUMN outcome_summary TEXT DEFAULT ''")