SlabUploader/backend/slabs.py

"""Slab CRUD service + FastAPI router (S2).

Client-derived numbers are stored as sent (never recomputed). Optimistic
concurrency via a single compare-and-set UPDATE on server_rev. Soft delete
sets deleted_at; published slabs cannot be deleted.
"""

import json
import logging
from datetime import datetime, timezone
from typing import Any, Dict, List

from fastapi import APIRouter, HTTPException
from fastapi.responses import JSONResponse, Response

from .db import get_connection
from .thickness_band import load_thickness_terms, resolve_thickness_band_id

logger = logging.getLogger("slab.backend")

router = APIRouter(prefix="/api/v1/slabs")

# Client-settable slab columns (explicit allowlist; never build SQL from client keys).
ALLOWED_FIELDS = [
    "id", "sku", "length_in", "thickness_in", "sqft", "bdft",
    "width_min_in", "width_max_in", "width_avg_in", "species_id",
    "wood_category_ids", "edge_type_term_id", "figure_term_ids", "grade_term_id",
    "thickness_term_id", "moisture_term_id", "fig_tag_ids", "feat_tag_ids",
    "price", "price_source", "title", "short_description", "description",
    "content_source", "species_confidence", "figure_confidence", "grade_confidence",
    "inference_status", "calibration_photo_id",
]
JSON_ARRAY_FIELDS = {"wood_category_ids", "figure_term_ids", "fig_tag_ids", "feat_tag_ids"}
# Columns a client may set on update (id/status/revs/timestamps/deleted_at excluded).
ALLOWED_UPDATE_FIELDS = set(ALLOWED_FIELDS) - {
    "id", "status", "client_rev", "server_rev", "deleted_at", "created_at", "updated_at",
}
TAXONOMY_TABLES = {
    "species_id": "woo_categories",
    "wood_category_ids": "woo_categories",
    "edge_type_term_id": "woo_attribute_terms",
    "figure_term_ids": "woo_attribute_terms",
    "grade_term_id": "woo_attribute_terms",
    "thickness_term_id": "woo_attribute_terms",
    "moisture_term_id": "woo_attribute_terms",
    "fig_tag_ids": "woo_tags",
    "feat_tag_ids": "woo_tags",
}
# Owner-facing labels for stale_field messages (QUALITY-120 — never raw column names).
FIELD_LABELS = {
    "species_id": "Species",
    "wood_category_ids": "Wood category",
    "edge_type_term_id": "Edge type",
    "figure_term_ids": "Figure",
    "grade_term_id": "Grade",
    "thickness_term_id": "Thickness",
    "moisture_term_id": "Moisture",
    "fig_tag_ids": "Figure tags",
    "feat_tag_ids": "Feature tags",
}
PRICE_SOURCES = {"recommendation", "override", "manual"}
INFERENCE_STATUSES = {"idle", "inferring", "done", "failed", "skipped"}


def _now_iso() -> str:
    return datetime.now(timezone.utc).replace(microsecond=0, tzinfo=None).isoformat()


def _error(code: str, message: str, detail: Any = None) -> Dict[str, Any]:
    return {"error": {"code": code, "message": message, "detail": detail}}


def _err(code: str, message: str, detail: Any = None, status: int = 400) -> JSONResponse:
    return JSONResponse(status_code=status, content=_error(code, message, detail))


def _encode(payload: Dict[str, Any]) -> Dict[str, Any]:
    out = dict(payload)
    for f in JSON_ARRAY_FIELDS:
        if f in out and isinstance(out[f], list):
            out[f] = json.dumps(out[f])
    return out


# #89: server-managed fields that a plain client PUT must not set directly.
# (metadata_locked was dropped — the deterministic title/short-desc are built by
# the review endpoint and written back by the client's Create buttons.)
CLIENT_FORBIDDEN_FIELDS = set()


def _row_to_slab(row) -> Dict[str, Any]:
    d = dict(row)
    for f in JSON_ARRAY_FIELDS:
        if d.get(f) is not None and isinstance(d[f], str):
            try:
                d[f] = json.loads(d[f])
            except (ValueError, TypeError):
                pass
    return d


def _sku_taken(conn, sku: str, exclude_id: str | None = None) -> bool:
    if exclude_id:
        return conn.execute(
            "SELECT 1 FROM slabs WHERE sku=? AND deleted_at IS NULL AND id<>?", (sku, exclude_id)
        ).fetchone() is not None
    return conn.execute(
        "SELECT 1 FROM slabs WHERE sku=? AND deleted_at IS NULL", (sku,)
    ).fetchone() is not None


def _check_taxonomy(conn, table: str, val: Any) -> bool:
    if isinstance(val, list):
        if not val:
            return True
        ph = ",".join(["?"] * len(val))
        rows = conn.execute(f"SELECT id FROM {table} WHERE id IN ({ph})", val).fetchall()
        return {r[0] for r in rows} == set(val)
    return conn.execute(f"SELECT 1 FROM {table} WHERE id=?", (val,)).fetchone() is not None


def _current_options(conn, table: str) -> list[dict[str, Any]]:
    """Live taxonomy options for a stale_field response (PUBLISH-20 / #82)."""
    rows = conn.execute(f"SELECT id, name FROM {table} ORDER BY name, id").fetchall()
    return [{"id": r["id"], "name": r["name"]} for r in rows]


def _normalize_tax_val(val: Any) -> Any:
    if isinstance(val, str):
        try:
            return json.loads(val)
        except (ValueError, TypeError):
            return []
    return val


def _validate_taxonomy_fields(conn, payload: Dict[str, Any]) -> JSONResponse | None:
    """Return a 422 stale_field response, or None if all present taxonomy ids are live."""
    for field, table in TAXONOMY_TABLES.items():
        if field not in payload or payload[field] is None:
            continue
        val = _normalize_tax_val(payload[field])
        if not _check_taxonomy(conn, table, val):
            label = FIELD_LABELS.get(field, field)
            return _err(
                "stale_field",
                f"{label} is no longer in the store taxonomy",
                {"field": field, "current_options": _current_options(conn, table)},
                status=422,
            )
    return None


def _validate_enums(payload: Dict[str, Any]) -> JSONResponse | None:
    if "price_source" in payload and payload["price_source"] is not None:
        if payload["price_source"] not in PRICE_SOURCES:
            return _err(
                "validation",
                "price_source is not a known value",
                {"field": "price_source", "allowed": sorted(PRICE_SOURCES)},
                status=400,
            )
    if "inference_status" in payload and payload["inference_status"] is not None:
        if payload["inference_status"] not in INFERENCE_STATUSES:
            return _err(
                "validation",
                "inference_status is not a known value",
                {"field": "inference_status", "allowed": sorted(INFERENCE_STATUSES)},
                status=400,
            )
    return None


# Capture-only fields required to create a slab. The Review screen fills the
# taxonomy/content fields after create (CREATE.md §1: "every other field stays
# editable until create"), so create must accept a capture-math payload.
# client_rev is server-managed (set to 1 at create) — never required from the client.
REQUIRED_CREATE_FIELDS = [
    "id", "sku", "length_in", "thickness_in", "sqft", "bdft",
    "width_min_in", "width_max_in", "width_avg_in",
]


@router.post("", status_code=201)
async def create_slab(payload: Dict[str, Any]) -> Dict[str, Any]:
    missing = [f for f in REQUIRED_CREATE_FIELDS if f not in payload]
    if missing:
        return _err("validation", "missing fields", {"fields": missing}, status=400)
    # #83: reject unknown keys (client sent a field that is not client-settable).
    # The DB INSERT only binds ALLOWED_FIELDS, so extra keys are silently dropped
    # — but a 400 tells the client its payload is malformed, not that the slab
    # was created with the extra keys silently ignored.
    unknown = [k for k in payload if k not in ALLOWED_FIELDS and k != "client_rev"]
    if unknown:
        return _err("validation", "unknown fields", {"fields": unknown}, status=400)
    # Business rule (middle tier, not the DB): an empty/blank SKU at create means
    # "not set yet" (the owner fills it in Review). Store NULL — the DB CHECK only
    # governs present SKUs, and NULL is the honest "absent" representation.
    sku = payload.get("sku")
    if isinstance(sku, str) and sku.strip() == "":
        payload = {**payload, "sku": None}
    enum_err = _validate_enums(payload)
    if enum_err is not None:
        return enum_err
    conn = get_connection()
    tax_err = _validate_taxonomy_fields(conn, payload)
    if tax_err is not None:
        return tax_err
    if payload.get("sku") is not None and _sku_taken(conn, payload["sku"]):
        return _err("duplicate_sku", "SKU already exists", {"sku": payload["sku"]}, status=409)
    # METADATA-15 / QUALITY-95: the thickness band is derived, never owner-chosen.
    # Resolve it from the measured thickness against the live synced Woo bands and
    # overwrite whatever the client sent (the UI has no band control).
    band = resolve_thickness_band_id(payload.get("thickness_in"), load_thickness_terms(conn))
    if band is not None:
        payload = {**payload, "thickness_term_id": band}
    now = _now_iso()
    data = _encode(payload)
    allowed_cols = [f for f in ALLOWED_FIELDS if f in data]
    server_cols = ["status", "client_rev", "server_rev", "deleted_at", "created_at", "updated_at"]
    server_vals = ["calibrated", 1, 1, None, now, now]
    cols = allowed_cols + server_cols
    # One value per col, in cols order: data values for the allowed fields, then the 6 server values.
    vals = [data[f] for f in allowed_cols] + server_vals
    ph = ",".join(["?"] * len(cols))
    assert len(vals) == len(cols), f"create_slab binding mismatch: {len(vals)} vals vs {len(cols)} cols"
    try:
        conn.execute(f"INSERT INTO slabs ({', '.join(cols)}) VALUES ({ph})", vals)
        conn.commit()
    except Exception as exc:
        # QUALITY-120: never surface a driver stack to the owner, but log it
        # server-side so a failing create is diagnosable.
        logger.error("create_slab insert failed: %s", exc, exc_info=True)
        return _err("validation", "Could not create the slab. Check the fields and try again.", status=400)
    row = conn.execute("SELECT * FROM slabs WHERE id=?", (payload["id"],)).fetchone()
    return _row_to_slab(row)


@router.get("")
async def list_slabs() -> List[Dict[str, Any]]:
    conn = get_connection()
    rows = conn.execute("SELECT * FROM slabs WHERE deleted_at IS NULL ORDER BY updated_at DESC, id DESC").fetchall()
    return [_row_to_slab(r) for r in rows]


@router.get("/{id}")
async def get_slab(id: str) -> Dict[str, Any]:
    conn = get_connection()
    row = conn.execute("SELECT * FROM slabs WHERE id=? AND deleted_at IS NULL", (id,)).fetchone()
    if row is None:
        raise HTTPException(status_code=404, detail="Slab not found")
    return _row_to_slab(row)


@router.put("/{id}")
async def update_slab(id: str, payload: Dict[str, Any]) -> Dict[str, Any]:
    if "client_rev" not in payload:
        return _err("validation", "client_rev required", status=400)
    # #89: server-managed fields a plain PUT must not set.
    for field in CLIENT_FORBIDDEN_FIELDS:
        if field in payload:
            return _err("validation", f"{field} is set by the review lock action, not by update", status=400)
    enum_err = _validate_enums(payload)
    if enum_err is not None:
        return enum_err
    conn = get_connection()
    current = conn.execute("SELECT * FROM slabs WHERE id=? AND deleted_at IS NULL", (id,)).fetchone()
    if current is None:
        raise HTTPException(status_code=404, detail="Slab not found")
    client_rev = payload["client_rev"]
    if client_rev != current["server_rev"]:
        return _err("revision_conflict", "client_rev mismatch",
                    {"current_server_rev": current["server_rev"]}, status=409)
    if "sku" in payload and payload["sku"] != current["sku"]:
        if payload["sku"] is not None and _sku_taken(conn, payload["sku"], exclude_id=id):
            return _err("duplicate_sku", "SKU already exists", {"sku": payload["sku"]}, status=409)
    tax_err = _validate_taxonomy_fields(conn, payload)
    if tax_err is not None:
        return tax_err
    # METADATA-15 / QUALITY-95: thickness band is derived, never owner-chosen.
    eff_thickness = payload.get("thickness_in", current["thickness_in"])
    band = resolve_thickness_band_id(eff_thickness, load_thickness_terms(conn))
    if band is not None:
        payload = {**payload, "thickness_term_id": band}
    data = _encode(payload)
    cols = [f for f in ALLOWED_UPDATE_FIELDS if f in data]
    if not cols:
        return _row_to_slab(current)
    set_clause = ", ".join(f"{f} = ?" for f in cols)
    vals = [data[f] for f in cols] + [client_rev + 1, _now_iso(), id, client_rev]
    cur = conn.execute(
        f"UPDATE slabs SET {set_clause}, server_rev=?, updated_at=? WHERE id=? AND server_rev=?",
        vals,
    )
    conn.commit()
    if cur.rowcount == 0:
        cur_server = conn.execute("SELECT server_rev FROM slabs WHERE id=?", (id,)).fetchone()
        return _err("revision_conflict", "client_rev mismatch",
                    {"current_server_rev": cur_server[0] if cur_server else None}, status=409)
    row = conn.execute("SELECT * FROM slabs WHERE id=?", (id,)).fetchone()
    return _row_to_slab(row)


@router.delete("/{id}", status_code=204)
async def delete_slab(id: str):
    conn = get_connection()
    row = conn.execute("SELECT * FROM slabs WHERE id=? AND deleted_at IS NULL", (id,)).fetchone()
    if row is None:
        raise HTTPException(status_code=404, detail="Slab not found")
    if row["status"] == "published":
        return _err("publish_conflict", "Published slab cannot be deleted", status=409)
    conn.execute("UPDATE slabs SET deleted_at=? WHERE id=?", (_now_iso(), id))
    conn.commit()
    return Response(status_code=204)