app.py

# =========================
# Tea Notes (Steeps) — UI updated for new Supabase schema
#   - buy_again removed
#   - to_buy is tri-state: "No" / "Maybe" / "Yes"
# =========================

import os
from datetime import datetime
from typing import List, Dict, Any, Optional

import numpy as np
import pandas as pd
import plotly.express as px
import streamlit as st
from streamlit import column_config  # noqa: F401

# -------------------- Supabase availability --------------------
try:
    from supabase import create_client, Client  # type: ignore
    SUPABASE_AVAILABLE = True
except Exception:
    SUPABASE_AVAILABLE = False


@st.cache_resource
def get_supabase() -> Optional["Client"]:
    if not SUPABASE_AVAILABLE:
        return None
    url = st.secrets.get("SUPABASE_URL") or os.getenv("SUPABASE_URL")
    key = (
        st.secrets.get("SUPABASE_KEY")
        or os.getenv("SUPABASE_KEY")
        or os.getenv("SUPABASE_ANON_KEY")
    )
    if not url or not key:
        return None
    try:
        return create_client(url, key)  # type: ignore
    except Exception:
        return None


@st.cache_data(ttl=60)
def load_data() -> Dict[str, pd.DataFrame]:
    """Load teas and steeps from Supabase."""
    if SUPABASE is None:
        return {"teas": pd.DataFrame(), "steeps": pd.DataFrame()}

    teas = pd.DataFrame(SUPABASE.table("teas").select("*").execute().data)  # type: ignore
    steeps = pd.DataFrame(SUPABASE.table("steeps").select("*").execute().data)  # type: ignore
    return {"teas": teas, "steeps": steeps}


def ensure_datetime(col: pd.Series) -> pd.Series:
    try:
        return pd.to_datetime(col, errors="coerce")
    except Exception:
        return pd.to_datetime(pd.Series([None] * len(col)))


def options_from_column(df: pd.DataFrame, col: str) -> List[str]:
    if col not in df.columns:
        return []
    vals = df[col].dropna().astype(str).str.strip()
    vals = vals[vals != ""]
    return sorted(vals.unique().tolist())


def safe_int(text: str) -> Optional[int]:
    text = (text or "").strip()
    if text == "":
        return None
    try:
        return int(float(text))
    except Exception:
        return None


def safe_float(text: str) -> Optional[float]:
    text = (text or "").strip()
    if text == "":
        return None
    try:
        return float(text)
    except Exception:
        return None


def safe_index(options: List[str], value: Any, default: int = 0) -> int:
    if value is None:
        return default
    try:
        v = str(value).strip()
    except Exception:
        return default
    return options.index(v) if v in options else default


def get_pk_column(df: pd.DataFrame, candidates: List[str]) -> Optional[str]:
    for c in candidates:
        if c in df.columns:
            return c
    return None


def _json_sanitize(value: Any) -> Any:
    if value is None:
        return None
    if isinstance(value, (pd.Timestamp,)):
        if pd.isna(value):
            return None
        try:
            return pd.to_datetime(value, utc=True).strftime("%Y-%m-%dT%H:%M:%SZ")
        except Exception:
            return str(value)
    if isinstance(value, float) and np.isnan(value):
        return None
    if isinstance(value, np.generic):
        return value.item()
    return value


def update_supabase_rows(table: str, pk_col: str, rows: List[Dict[str, Any]]) -> List[str]:
    """Update rows by primary key; only send columns that exist in remote table."""
    errs = []
    if SUPABASE is None:
        return ["Database is not configured."]
    existing_cols = set(teas_df.columns) if table == "teas" else set(steeps_df.columns)
    for r in rows:
        pk_val = r.get(pk_col)
        if pk_val is None or (isinstance(pk_val, float) and np.isnan(pk_val)):
            errs.append(f"Missing {pk_col} in row; skipped.")
            continue
        payload = {k: _json_sanitize(v) for k, v in r.items() if k != pk_col and k in existing_cols}
        try:
            SUPABASE.table(table).update(payload).eq(pk_col, pk_val).execute()  # type: ignore
        except Exception as e:
            errs.append(f"{table} update failed for {pk_col}={pk_val}: {e}")
    return errs


def diff_rows(original: pd.DataFrame, edited: pd.DataFrame, pk_col: str, editable_cols: List[str]) -> pd.DataFrame:
    """Return only the edited rows that changed in editable columns."""
    if original.empty or edited.empty:
        return edited.iloc[0:0].copy()
    left = original.set_index(pk_col)
    right = edited.set_index(pk_col)
    cols = [c for c in editable_cols if c in left.columns and c in right.columns]
    if not cols:
        return edited.iloc[0:0].copy()
    l = left[cols].applymap(lambda x: None if (isinstance(x, float) and np.isnan(x)) else x)
    r = right[cols].applymap(lambda x: None if (isinstance(x, float) and np.isnan(x)) else x)
    changed_mask = (l != r).any(axis=1)
    changed_idx = changed_mask[changed_mask].index
    return edited[edited[pk_col].isin(changed_idx)].copy()


# --- types for steeps & payload builder ---
INT_COLS_STEEP = ["initial_steep_time_sec", "temperature_c"]
FLOAT_COLS_STEEP = ["rating", "amount_used_g"]


def _to_iso_utc_or_none(value):
    if value is None or (isinstance(value, str) and value.strip() == "") or (isinstance(value, float) and np.isnan(value)):
        return None
    ts = pd.to_datetime(value, errors="coerce", utc=True)
    if pd.isna(ts):
        return None
    return ts.strftime("%Y-%m-%dT%H:%M:%SZ")


def build_steep_payloads(changed_df: pd.DataFrame, pk_col: str) -> List[Dict[str, Any]]:
    payloads: List[Dict[str, Any]] = []
    for _, r in changed_df.iterrows():
        rec: Dict[str, Any] = {pk_col: r[pk_col]}
        cols = [
            "session_at",
            "rating",
            "tasting_notes",
            "steep_notes",
            "initial_steep_time_sec",
            "steep_time_changes",
            "temperature_c",
            "amount_used_g",
        ]
        for c in cols:
            if c not in changed_df.columns:
                continue
            v = r[c]
            if c == "session_at":
                val = _to_iso_utc_or_none(v)
            elif c in INT_COLS_STEEP:
                if v is None or (isinstance(v, str) and v.strip() == "") or (isinstance(v, float) and np.isnan(v)):
                    val = None
                else:
                    try:
                        val = int(round(float(v)))
                    except Exception:
                        val = None
            elif c in FLOAT_COLS_STEEP:
                if v is None or (isinstance(v, str) and v.strip() == "") or (isinstance(v, float) and np.isnan(v)):
                    val = None
                else:
                    try:
                        val = float(v)
                    except Exception:
                        val = None
            else:
                if v is None or (isinstance(v, str) and v.strip() == "") or (isinstance(v, float) and np.isnan(v)):
                    val = None
                else:
                    val = v
            rec[c] = val
        payloads.append(rec)
    return payloads


# -------------------- Supabase + data --------------------
SUPABASE = get_supabase()
_db = load_data()
teas_df = _db["teas"].copy()
steeps_df = _db["steeps"].copy()

# ========================= UI SECTION =========================
st.set_page_config(page_title="Tea Notes (Steeps)", page_icon="🍵", layout="wide")

st.markdown(
    """
<style>
/* "tabs" look for radio */
[data-testid="stHorizontalBlock"] > div:has(> div[data-testid="stRadio"]) { margin-bottom: 0.5rem; }
div[data-testid="stRadio"] > div[role="radiogroup"] { display:flex; gap:.25rem; flex-wrap:wrap; }
div[data-testid="stRadio"] label {
  border:1px solid var(--secondary-background-color); padding:.4rem .8rem; border-radius:.5rem .5rem 0 0;
  background:var(--secondary-background-color); cursor:pointer; font-weight:500;
}
div[data-testid="stRadio"] label[data-checked="true"] {
  background:var(--background-color); border-bottom-color:var(--background-color);
  box-shadow:0 -2px 0 0 var(--primary-color) inset;
}
/* wrap table text */
[data-testid="stDataFrame"] div[role="gridcell"],
[data-testid="stDataFrame"] div[data-testid="cell-container"],
[data-testid="stDataFrame"] td, [data-testid="stDataFrame"] span, [data-testid="stDataFrame"] p {
  white-space:normal !important; overflow:visible !important; text-overflow:clip !important;
}
</style>
""",
    unsafe_allow_html=True,
)

st.title("🍵 Tea Notes — Sessions & Scores")

TEA_TYPES = ["Oolong", "Black", "White", "Green", "Pu-erh", "Dark", "Yellow"]
TO_BUY_OPTIONS = ["No", "Maybe", "Yes"]

# -------------------- Nav --------------------
NAV_ITEMS = ["📝 Add Session", "➕ Add Tea", "✏️ Edit tea", "📜 Steep history", "📊 Analysis"]
if "active_tab" not in st.session_state:
    st.session_state.active_tab = NAV_ITEMS[0]

st.session_state.active_tab = st.radio(
    "Tabs", NAV_ITEMS, index=NAV_ITEMS.index(st.session_state.active_tab),
    horizontal=True, label_visibility="collapsed", key="nav_radio",
)

# -------------------- Screens --------------------
if st.session_state.active_tab == "📝 Add Session":
    tea_choices = ["(select)"] + teas_df.get("name", pd.Series(dtype=str)).fillna("(unnamed)").tolist()
    tea_selected = st.selectbox("Tea", tea_choices, index=0, key="add_sess_tea")
    tea_selected_row = None
    if tea_selected != "(select)" and "name" in teas_df.columns:
        tea_selected_row = teas_df[teas_df["name"] == tea_selected].head(1)
    tea_id = None
    if tea_selected_row is not None and not tea_selected_row.empty:
        tea_id = tea_selected_row.iloc[0].get("tea_id") or tea_selected_row.iloc[0].get("id")

    initial_secs_txt = st.text_input("Initial steep time (seconds)", value="", key="add_sess_initial_secs")
    changes_text = st.text_input("Steep time changes", value="", key="add_sess_changes")
    temperature_c_txt = st.text_input("Water temperature (°C)", value="", key="add_sess_temp")
    amount_used_g_txt = st.text_input("Tea amount used (g)", value="", key="add_sess_amount")
    tasting_notes = st.text_area("Tasting notes", value="", key="add_sess_tnotes")
    steep_notes = st.text_area("Steep notes", value="", key="add_sess_snotes")
    overall_rating_txt = st.text_input("Overall rating (0–5)", value="", key="add_sess_rating")

    save_session_btn = st.button("Save Session", type="primary", use_container_width=True, key="add_sess_save")
    if save_session_btn:
        if tea_id is None:
            st.error("Please select a tea first.")
        elif SUPABASE is None:
            st.error("Database is not configured.")
        else:
            row = {
                "tea_id": tea_id,
                "tasting_notes": (tasting_notes or None),
                "steep_notes": (steep_notes or None),
                "rating": safe_float(overall_rating_txt),
                "steeps": None,
                "initial_steep_time_sec": safe_int(initial_secs_txt),
                "steep_time_changes": (changes_text or None),
                "temperature_c": safe_int(temperature_c_txt),
                "amount_used_g": safe_float(amount_used_g_txt),
                "session_at": datetime.utcnow().isoformat(),
            }
            try:
                SUPABASE.table("steeps").insert(row).execute()  # type: ignore
                st.success("Saved.")
            except Exception as e:
                st.error(f"Failed to save: {e}")

elif st.session_state.active_tab == "➕ Add Tea":
    colA, colB = st.columns(2)

    subtype_opts = options_from_column(teas_df, "subtype")
    supplier_opts_all = options_from_column(teas_df, "supplier")
    cultivar_opts = options_from_column(teas_df, "cultivar")
    REGION_COMPONENT_COLS = ["country", "province", "prefecture", "county", "mountain", "village"]
    loc_opts = {c: options_from_column(teas_df, c) for c in REGION_COMPONENT_COLS}

    with colA:
        tea_name = st.text_input("Tea name (required)", key="add_tea_name")
        tea_type = st.selectbox("Tea type", options=[""] + TEA_TYPES, index=0, key="add_tea_type")
        subtype_sel = st.selectbox("Subtype", options=[""] + subtype_opts, index=0, key="add_tea_subtype_sel")
        subtype_new = st.text_input("Or add new Subtype", key="add_tea_subtype_new")
        supplier_sel = st.selectbox("Supplier", options=[""] + supplier_opts_all, index=0, key="add_tea_supplier_sel")
        supplier_new = st.text_input("Or add new Supplier", key="add_tea_supplier_new")
        url = st.text_input("URL", key="add_tea_url")
        processing_notes = st.text_area("Processing notes", key="add_tea_processing_notes")
        have_already_cb = st.checkbox("Have already", value=False, key="add_tea_have_already")
        to_buy_sel = st.selectbox("To buy", options=TO_BUY_OPTIONS, index=0, key="add_tea_to_buy")
    with colB:
        cultivar_sel = st.selectbox("Cultivar", options=[""] + cultivar_opts, index=0, key="add_tea_cultivar_sel")
        cultivar_new = st.text_input("Or add new Cultivar", key="add_tea_cultivar_new")
        with st.expander("Location", expanded=True):
            l1, l2 = st.columns(2)
            with l1:
                country_sel = st.selectbox("Country", options=[""] + loc_opts["country"], index=0, key="add_tea_country_sel")
                country_new = st.text_input("Or add new Country", key="add_tea_country_new")
                province_sel = st.selectbox("Province / state", options=[""] + loc_opts["province"], index=0, key="add_tea_province_sel")
                province_new = st.text_input("Or add new Province / state", key="add_tea_province_new")
                prefecture_sel = st.selectbox("Prefecture / city", options=[""] + loc_opts["prefecture"], index=0, key="add_tea_prefecture_sel")
                prefecture_new = st.text_input("Or add new Prefecture / city", key="add_tea_prefecture_new")
            with l2:
                county_sel = st.selectbox("County / district", options=[""] + loc_opts["county"], index=0, key="add_tea_county_sel")
                county_new = st.text_input("Or add new County / district", key="add_tea_county_new")
                mountain_sel = st.selectbox("Mountain", options=[""] + loc_opts["mountain"], index=0, key="add_tea_mountain_sel")
                mountain_new = st.text_input("Or add new Mountain", key="add_tea_mountain_new")
                village_sel = st.selectbox("Village", options=[""] + loc_opts["village"], index=0, key="add_tea_village_sel")
                village_new = st.text_input("Or add new Village", key="add_tea_village_new")
        pick_year_txt = st.text_input("Pick year", value="", key="add_tea_pick_year")
        picking_season = st.text_input("Picking season", key="add_tea_picking_season")
        st.markdown("**Pricing / weights (NZD & grams)**")
        price_1 = st.text_input("Price 1 (NZD)", value="", key="add_tea_price1")
        weight_1 = st.text_input("Weight 1 (g)", value="", key="add_tea_weight1")
        price_2 = st.text_input("Price 2 (NZD)", value="", key="add_tea_price2")
        weight_2 = st.text_input("Weight 2 (g)", value="", key="add_tea_weight2")
        weight_per_session = st.text_input("Weight per session (g)", value="", key="add_tea_wps")

    subtype = (subtype_new.strip() or subtype_sel.strip() or None)
    supplier = (supplier_new.strip() or supplier_sel.strip() or None)
    cultivar = (cultivar_new.strip() or cultivar_sel.strip() or None)
    country = (country_new.strip() or country_sel.strip() or None)
    province = (province_new.strip() or province_sel.strip() or None)
    prefecture = (prefecture_new.strip() or prefecture_sel.strip() or None)
    county = (county_new.strip() or county_sel.strip() or None)
    mountain = (mountain_new.strip() or mountain_sel.strip() or None)
    village = (village_new.strip() or village_sel.strip() or None)
    pick_year = safe_int(pick_year_txt) if pick_year_txt else None
    tea_type_val = tea_type.strip() or None
    processing_notes_val = (processing_notes.strip() or None) if isinstance(processing_notes, str) else None
    picking_season_val = (picking_season.strip() or None) if isinstance(picking_season, str) else None

    add_tea_btn = st.button("Save Tea", type="primary", use_container_width=True, key="add_tea_save")
    if add_tea_btn:
        if not tea_name.strip():
            st.error("Tea name is required.")
        elif SUPABASE is None:
            st.error("Database is not configured.")
        else:
            tea_row = {
                "name": tea_name.strip(),
                "type": tea_type_val,
                "subtype": subtype,
                "supplier": supplier,
                "URL": (url.strip() or None),
                "cultivar": cultivar,
                "country": country,
                "province": province,
                "prefecture": prefecture,
                "county": county,
                "mountain": mountain,
                "village": village,
                "pick_year": pick_year,
                "processing_notes": processing_notes_val,
                "picking_season": picking_season_val,
                "have_already": bool(have_already_cb),
                "to_buy": to_buy_sel,  # "No", "Maybe", "Yes"
                "price_1_nzd": safe_float(price_1),
                "weight_1_g": safe_float(weight_1),
                "price_2_nzd": safe_float(price_2),
                "weight_2_g": safe_float(weight_2),
                "weight_per_session_g": safe_float(weight_per_session),
                "created_at": datetime.utcnow().isoformat(),
            }
            allowed = set(teas_df.columns)
            tea_row = {k: _json_sanitize(v) for k, v in tea_row.items() if k in allowed}
            try:
                SUPABASE.table("teas").insert(tea_row).execute()  # type: ignore
                st.success("Saved.")
                st.cache_data.clear()
            except Exception as e:
                st.error(f"Failed to save: {e}")

elif st.session_state.active_tab == "✏️ Edit tea":
    st.subheader("✏️ Edit tea")

    tea_pk = get_pk_column(teas_df, ["tea_id", "id"])
    if tea_pk is None:
        st.warning("Could not determine the tea primary key column (expected 'tea_id' or 'id').")
    else:
        tea_names = (
            teas_df.get("name", pd.Series(dtype=str)).dropna().astype(str).str.strip()
        )
        tea_names = tea_names[tea_names != ""].unique().tolist()
        tea_names_sorted = sorted(tea_names)

        selected_name = st.selectbox("Choose a tea to edit", options=["(select)"] + tea_names_sorted, index=0, key="edit_tea_select")
        if selected_name == "(select)":
            st.info("Select a tea above to edit its details.")
        else:
            row = teas_df[teas_df["name"] == selected_name].head(1)
            if row.empty:
                st.warning("Tea not found.")
            else:
                tea_pk_val = row.iloc[0][tea_pk]

                type_options = [""] + TEA_TYPES
                type_idx = safe_index(type_options, row.iloc[0].get("type", ""))

                # Map existing to_buy value (bool or string) into tri-state options
                raw_to_buy = row.iloc[0].get("to_buy", "No")
                if isinstance(raw_to_buy, bool):
                    current_to_buy = "Yes" if raw_to_buy else "No"
                else:
                    raw_str = str(raw_to_buy).strip().title()
                    current_to_buy = raw_str if raw_str in TO_BUY_OPTIONS else "No"
                to_buy_idx = safe_index(TO_BUY_OPTIONS, current_to_buy, default=0)

                colA, colB = st.columns(2)
                with colA:
                    name_new = st.text_input("Tea name", value=str(row.iloc[0].get("name", "") or ""))
                    type_new = st.selectbox("Tea type", options=type_options, index=type_idx, key=f"edit_tea_type_{tea_pk_val}")
                    subtype_new = st.text_input("Subtype", value=str(row.iloc[0].get("subtype", "") or ""))
                    supplier_new = st.text_input("Supplier", value=str(row.iloc[0].get("supplier", "") or ""))
                    url_new = st.text_input("URL", value=str(row.iloc[0].get("URL", "") or ""))
                    processing_notes_new = st.text_area(
                        "Processing notes",
                        value=str(row.iloc[0].get("processing_notes", "") or ""),
                        key=f"edit_tea_processing_notes_{tea_pk_val}",
                    )
                    have_already_new = st.checkbox(
                        "Have already",
                        value=bool(row.iloc[0].get("have_already", False)),
                        key=f"edit_tea_have_{tea_pk_val}",
                    )
                    to_buy_new = st.selectbox(
                        "To buy",
                        options=TO_BUY_OPTIONS,
                        index=to_buy_idx,
                        key=f"edit_tea_tobuy_{tea_pk_val}",
                    )
                with colB:
                    cultivar_new = st.text_input("Cultivar", value=str(row.iloc[0].get("cultivar", "") or ""))
                    st.markdown("**Location**")
                    country_new = st.text_input("Country", value=str(row.iloc[0].get("country", "") or ""))
                    province_new = st.text_input("Province / state", value=str(row.iloc[0].get("province", "") or ""))
                    prefecture_new = st.text_input("Prefecture / city", value=str(row.iloc[0].get("prefecture", "") or ""))
                    county_new = st.text_input("County / district", value=str(row.iloc[0].get("county", "") or ""))
                    mountain_new = st.text_input("Mountain", value=str(row.iloc[0].get("mountain", "") or ""))
                    village_new = st.text_input("Village", value=str(row.iloc[0].get("village", "") or ""))
                    pick_year_new = st.text_input("Pick year", value=str(row.iloc[0].get("pick_year", "") or ""))
                    picking_season_new = st.text_input(
                        "Picking season",
                        value=str(row.iloc[0].get("picking_season", "") or ""),
                        key=f"edit_tea_picking_season_{tea_pk_val}",
                    )
                    price_1_new = st.text_input("Price 1 (NZD)", value=str(row.iloc[0].get("price_1_nzd", "") or ""))
                    weight_1_new = st.text_input("Weight 1 (g)", value=str(row.iloc[0].get("weight_1_g", "") or ""))
                    price_2_new = st.text_input("Price 2 (NZD)", value=str(row.iloc[0].get("price_2_nzd", "") or ""))
                    weight_2_new = st.text_input("Weight 2 (g)", value=str(row.iloc[0].get("weight_2_g", "") or ""))
                    wps_new = st.text_input("Weight per session (g)", value=str(row.iloc[0].get("weight_per_session_g", "") or ""))

                    # per-session prices
                    pps1_new = st.text_input(
                        "Price per session 1 (NZD)",
                        value=str(row.iloc[0].get("price_per_session_1_nzd", "") or "")
                    )
                    pps2_new = st.text_input(
                        "Price per session 2 (NZD)",
                        value=str(row.iloc[0].get("price_per_session_2_nzd", "") or "")
                    )

                save_btn = st.button("Save changes", type="primary", key=f"edit_tea_save_{tea_pk_val}")
                if save_btn:
                    if SUPABASE is None:
                        st.error("Database is not configured.")
                    else:
                        payload = {
                            "name": name_new.strip() or None,
                            "type": (type_new.strip() or None),
                            "subtype": (subtype_new.strip() or None),
                            "supplier": (supplier_new.strip() or None),
                            "URL": (url_new.strip() or None),
                            "cultivar": (cultivar_new.strip() or None),
                            "country": (country_new.strip() or None),
                            "province": (province_new.strip() or None),
                            "prefecture": (prefecture_new.strip() or None),
                            "county": (county_new.strip() or None),
                            "mountain": (mountain_new.strip() or None),
                            "village": (village_new.strip() or None),
                            "pick_year": safe_int(pick_year_new),
                            "processing_notes": (processing_notes_new.strip() or None) if isinstance(processing_notes_new, str) else None,
                            "picking_season": (picking_season_new.strip() or None) if isinstance(picking_season_new, str) else None,
                            "have_already": bool(have_already_new),
                            "to_buy": to_buy_new,  # "No", "Maybe", "Yes"
                            "price_1_nzd": safe_float(price_1_new),
                            "weight_1_g": safe_float(weight_1_new),
                            "price_2_nzd": safe_float(price_2_new),
                            "weight_2_g": safe_float(weight_2_new),
                            "weight_per_session_g": safe_float(wps_new),
                            "price_per_session_1_nzd": safe_float(pps1_new),
                            "price_per_session_2_nzd": safe_float(pps2_new),
                        }
                        allowed = set(teas_df.columns)
                        payload = {k: _json_sanitize(v) for k, v in payload.items() if k in allowed}
                        try:
                            SUPABASE.table("teas").update(payload).eq(tea_pk, tea_pk_val).execute()  # type: ignore
                            st.success("Tea updated.")
                            st.cache_data.clear()
                        except Exception as e:
                            st.error(f"Failed to update: {e}")

elif st.session_state.active_tab == "📜 Steep history":
    st.subheader("📜 Steep history")

    # Join steeps to teas to show tea meta
    if "tea_id" in steeps_df.columns and ("tea_id" in teas_df.columns or "id" in teas_df.columns):
        teas_key = "tea_id" if "tea_id" in teas_df.columns else "id"
        keep_cols = [c for c in ["tea_id", "name", "type", "supplier", "cultivar", "country", "province", "prefecture", "county", "mountain", "village"] if c in teas_df.columns]
        joined = steeps_df.merge(
            teas_df.rename(columns={teas_key: "tea_id"})[keep_cols],
            on="tea_id",
            how="left",
        )
    else:
        joined = steeps_df.copy()
        for col in ["name", "type", "supplier", "cultivar", "country", "province", "prefecture", "county", "mountain", "village"]:
            if col not in joined.columns:
                joined[col] = None

    joined["session_at"] = ensure_datetime(joined.get("session_at", pd.Series(dtype="datetime64[ns]")))
    joined = joined.sort_values("session_at", ascending=False)

    st.markdown("### Recent steeps")
    recent_left, recent_right = st.columns([1, 1])
    with recent_left:
        recent_n = st.selectbox("Show last N", options=[10, 20, 50, 100], index=1, key="recent_steeps_n")
    with recent_right:
        tea_names_for_sel = teas_df.get("name", pd.Series(dtype=str)).dropna().astype(str).str.strip()
    tea_names = tea_names_for_sel[tea_names_for_sel != ""].unique().tolist() if not teas_df.empty else []
    tea_names_sorted = sorted(tea_names)

    selected_tea = st.selectbox("Find a tea", options=["(select a tea)"] + tea_names_sorted, index=0, key="hist_select_tea")

    only_selected = False
    if selected_tea != "(select a tea)":
        only_selected = st.checkbox("Only show selected tea in recent steeps", value=False, key=f"recent_only_{selected_tea}")

    recent_df = joined.copy()
    if only_selected and selected_tea != "(select a tea)":
        recent_df = recent_df[recent_df["name"] == selected_tea]
    recent_cols = [c for c in [
        "session_at","name","rating","tasting_notes","steep_notes",
        "initial_steep_time_sec","temperature_c","amount_used_g","supplier","type"
    ] if c in recent_df.columns]
    st.dataframe(
        recent_df.sort_values("session_at", ascending=False)[recent_cols].head(recent_n),
        use_container_width=True,
    )

    # Detailed table for selected tea (editable)
    if selected_tea == "(select a tea)":
        st.info("Select a tea above to view and edit its steeps.")
    else:
        rows = joined[joined["name"] == selected_tea].copy()
        if rows.empty:
            st.warning("No sessions found for this tea yet.")
        else:
            steep_pk = get_pk_column(rows, ["steep_id", "id"])
            if steep_pk is None:
                st.warning("Could not determine the steep primary key column (expected 'steep_id' or 'id'). Editing is disabled.")
                st.dataframe(rows.sort_values("session_at", ascending=False), use_container_width=True)
            else:
                display_cols = [
                    steep_pk, "session_at", "rating",
                    "tasting_notes", "steep_notes",
                    "initial_steep_time_sec", "steep_time_changes",
                    "temperature_c", "amount_used_g",
                    "type", "supplier", "cultivar", "country", "province", "prefecture", "county", "mountain", "village",
                ]
                present_cols = [c for c in display_cols if c in rows.columns]
                rows = rows[present_cols].copy()

                st.session_state["orig_steeps_df"] = rows.copy()

                readonly_cols = ["type", "supplier", "cultivar", "country", "province", "prefecture", "county", "mountain", "village"]
                col_conf = {}
                for c in present_cols:
                    if c in readonly_cols:
                        col_conf[c] = st.column_config.Column(c, disabled=True)
                    elif c in ["tasting_notes", "steep_notes"]:
                        col_conf[c] = st.column_config.TextColumn(c)
                    elif c in ["rating", "amount_used_g"]:
                        col_conf[c] = st.column_config.NumberColumn(c, step=0.1, format="%.2f")
                    elif c in ["initial_steep_time_sec", "temperature_c"]:
                        col_conf[c] = st.column_config.NumberColumn(c, step=1, format="%d")
                    elif c == "session_at":
                        col_conf[c] = st.column_config.Column(c)
                    elif c == steep_pk:
                        col_conf[c] = st.column_config.Column(c, disabled=True)

                edited = st.data_editor(
                    rows, key="steep_editor", use_container_width=True, hide_index=True, column_config=col_conf,
                )

                if st.button("Save changes", type="primary", key="save_steeps_btn"):
                    if SUPABASE is None:
                        st.error("Database is not configured.")
                    else:
                        orig = st.session_state.get("orig_steeps_df", rows)
                        editable_cols = [
                            "session_at","rating","tasting_notes","steep_notes",
                            "initial_steep_time_sec","steep_time_changes","temperature_c","amount_used_g",
                        ]
                        changed = diff_rows(orig, edited, steep_pk, editable_cols)
                        if changed.empty:
                            st.info("No changes to save.")
                        else:
                            payloads = build_steep_payloads(changed, steep_pk)
                            errors = update_supabase_rows("steeps", steep_pk, payloads)
                            if errors:
                                for e in errors:
                                    st.error(e)
                            else:
                                st.success(f"Saved {len(payloads)} change(s).")
                                st.cache_data.clear()

elif st.session_state.active_tab == "📊 Analysis":
    st.subheader("📊 Analysis")

    if "tea_id" in steeps_df.columns and ("tea_id" in teas_df.columns or "id" in teas_df.columns):
        teas_key = "tea_id" if "tea_id" in teas_df.columns else "id"
        keep_cols = [c for c in ["tea_id", "name", "type", "supplier", "cultivar", "country", "province", "prefecture", "county", "mountain", "village"] if c in teas_df.columns]
        joined = steeps_df.merge(
            teas_df.rename(columns={teas_key: "tea_id"})[keep_cols],
            on="tea_id", how="left",
        )
    else:
        joined = steeps_df.copy()
        for col in ["name", "type", "supplier", "cultivar", "country", "province", "prefecture", "county", "mountain", "village"]:
            if col not in joined.columns:
                joined[col] = None

    joined["session_at"] = ensure_datetime(joined.get("session_at", pd.Series(dtype="datetime64[ns]")))

    left, right = st.columns([1, 1])
    with left:
        tea_type_filter = st.selectbox("Tea type", options=["(all)"] + TEA_TYPES, index=0, key="analysis_type")
    with right:
        supplier_opts = sorted(
            teas_df.get("supplier", pd.Series(dtype=str))
            .dropna().astype(str).str.strip().replace("", pd.NA).dropna().unique().tolist()
        )
        supplier_filter = st.selectbox("Supplier", options=["(all)"] + supplier_opts, index=0, key="analysis_supplier")

    scope_mask = pd.Series(True, index=joined.index)
    if tea_type_filter != "(all)":
        scope_mask &= joined["type"].fillna("").str.lower() == tea_type_filter.lower()
    if supplier_filter != "(all)":
        scope_mask &= joined["supplier"].fillna("").str.lower() == supplier_filter.lower()
    scope_df = joined[scope_mask].copy()

    st.markdown("### Box & whisker by tea")

    def plot_box_by_tea(df, title: str = "Tea ratings — box & whisker"):
        if df is None or len(df) == 0 or "name" not in df.columns or "rating" not in df.columns:
            st.info("No ratings to chart yet.")
            return
        working = df.copy()
        working["rating"] = pd.to_numeric(working["rating"], errors="coerce")
        for col in ["supplier", "type", "session_at", "tasting_notes", "steep_notes"]:
            if col not in working.columns:
                working[col] = None
        working = working.dropna(subset=["rating"]) if not working.empty else working
        if working.empty:
            st.info("No ratings to chart yet.")
            return
        medians = working.groupby("name")["rating"].median().sort_values(ascending=False)
        fig = px.box(
            working, x="name", y="rating", points="all",
            hover_data=["supplier", "type", "session_at", "tasting_notes", "steep_notes"],
            title=title, category_orders={"name": medians.index.tolist()}
        )
        fig.update_layout(
            margin=dict(l=12, r=12, t=48, b=12),
            xaxis_title="Tea", yaxis_title="Rating", hovermode="closest"
        )
        fig.update_yaxes(range=[0, 5])
        st.plotly_chart(fig, use_container_width=True)

    plot_box_by_tea(scope_df)

    with st.expander("View data used in chart"):
        show_cols = ["name", "rating", "supplier", "type", "session_at", "tasting_notes", "steep_notes"]
        present_cols = [c for c in show_cols if c in scope_df.columns]
        st.data_editor(scope_df[present_cols].sort_values("name"), use_container_width=True, disabled=True)