260 lines
12 KiB
Python
260 lines
12 KiB
Python
import json
|
|
|
|
from fastapi import APIRouter, Depends, HTTPException, Query
|
|
from pydantic import BaseModel
|
|
from sqlalchemy import text
|
|
|
|
from .auth import require_token
|
|
from .db import get_db
|
|
|
|
# Collections = named crate zones + filing rules (genre/style/label/format + price/year) + priority.
|
|
# Migrated verbatim into virtual_collection. Powers the Collections tab AND the fallback locator:
|
|
# a record with no exact crate still resolves to "where it should be" via the highest-priority matching rule.
|
|
# See RECORDGOD_NAVIGATOR_PLAN.md §6b. (JSON list columns came across as text — parse in Python.)
|
|
router = APIRouter(prefix="/nav", tags=["collections"])
|
|
|
|
|
|
def _arr(v):
|
|
if isinstance(v, list):
|
|
return v
|
|
try:
|
|
return json.loads(v) if v else []
|
|
except Exception:
|
|
return []
|
|
|
|
|
|
def _ids(v):
|
|
"""Collection genre_ids/style_ids are stored as "ID,0" strings (WowPlatter's own ids) — take the lead int."""
|
|
out = []
|
|
for x in _arr(v):
|
|
try:
|
|
out.append(int(str(x).split(",")[0]))
|
|
except Exception:
|
|
pass
|
|
return out
|
|
|
|
|
|
class CollectionIn(BaseModel):
|
|
id: int | None = None
|
|
name: str
|
|
color: str = "#3498db"
|
|
crate_ids: list[int] = []
|
|
genre_ids: list[int] = []
|
|
style_ids: list[int] = []
|
|
label_ids: list[int] = []
|
|
format_descriptions: list[str] = []
|
|
price_min: float | None = None
|
|
price_max: float | None = None
|
|
year_min: int | None = None
|
|
year_max: int | None = None
|
|
sort_method: str = "alpha_artist"
|
|
priority: int = 0
|
|
is_active: bool = True
|
|
|
|
|
|
@router.get("/collections")
|
|
async def list_collections(ident=Depends(require_token), db=Depends(get_db)):
|
|
rows = (await db.execute(text("""
|
|
SELECT id, name, color, priority, (is_active::int <> 0) AS is_active,
|
|
crate_ids, style_ids, genre_ids, price_min::float, price_max::float, year_min, year_max
|
|
FROM virtual_collection ORDER BY priority DESC, name
|
|
"""))).mappings()
|
|
out = []
|
|
for r in rows:
|
|
d = dict(r)
|
|
out.append({"id": d["id"], "name": d["name"], "color": d["color"], "priority": d["priority"],
|
|
"is_active": d["is_active"], "crate_count": len(_arr(d["crate_ids"])),
|
|
"n_styles": len(_arr(d["style_ids"])), "n_genres": len(_arr(d["genre_ids"])),
|
|
"price_min": d["price_min"], "price_max": d["price_max"],
|
|
"year_min": d["year_min"], "year_max": d["year_max"]})
|
|
return {"collections": out}
|
|
|
|
|
|
@router.get("/collections/{cid}")
|
|
async def get_collection(cid: int, ident=Depends(require_token), db=Depends(get_db)):
|
|
c = (await db.execute(text("SELECT * FROM virtual_collection WHERE id=:i"), {"i": cid})).mappings().first()
|
|
if not c:
|
|
raise HTTPException(404, "not found")
|
|
crate_ids = _arr(c["crate_ids"])
|
|
crates = []
|
|
if crate_ids:
|
|
crates = [dict(r) for r in (await db.execute(text("""
|
|
SELECT id, name, label_text,
|
|
(SELECT count(*) FROM inventory i WHERE i.crate_id = c.id AND i.in_stock) AS items
|
|
FROM virtual_crate c WHERE c.id = ANY(:ids)
|
|
"""), {"ids": crate_ids})).mappings()]
|
|
order = {cid_: n for n, cid_ in enumerate(crate_ids)} # keep click order
|
|
crates.sort(key=lambda x: order.get(x["id"], 999))
|
|
gids, sids = _ids(c["genre_ids"]), _ids(c["style_ids"])
|
|
gn = {r["id"]: r["name"] for r in (await db.execute(text(
|
|
"SELECT id, name FROM disc_genre WHERE id = ANY(:i)"), {"i": gids})).mappings()} if gids else {}
|
|
sn = {r["id"]: r["name"] for r in (await db.execute(text(
|
|
"SELECT id, name FROM disc_style WHERE id = ANY(:i)"), {"i": sids})).mappings()} if sids else {}
|
|
return {"collection": {
|
|
"id": c["id"], "name": c["name"], "color": c["color"], "priority": c["priority"],
|
|
"is_active": bool(c["is_active"]), "sort_method": c["sort_method"],
|
|
"crate_ids": crate_ids, "genre_ids": gids, "style_ids": sids,
|
|
"genres": [{"id": g, "name": gn.get(g, str(g))} for g in gids],
|
|
"styles": [{"id": s, "name": sn.get(s, str(s))} for s in sids],
|
|
"price_min": float(c["price_min"]) if c["price_min"] is not None else None,
|
|
"price_max": float(c["price_max"]) if c["price_max"] is not None else None,
|
|
"year_min": c["year_min"], "year_max": c["year_max"]},
|
|
"crates": crates}
|
|
|
|
|
|
@router.post("/collections")
|
|
async def save_collection(body: CollectionIn, ident=Depends(require_token), db=Depends(get_db)):
|
|
p = {"name": body.name, "color": body.color, "crates": json.dumps(body.crate_ids),
|
|
"genres": json.dumps(body.genre_ids), "styles": json.dumps(body.style_ids),
|
|
"labels": json.dumps(body.label_ids), "formats": json.dumps(body.format_descriptions),
|
|
"pmin": body.price_min, "pmax": body.price_max, "ymin": body.year_min, "ymax": body.year_max,
|
|
"sort": body.sort_method, "prio": body.priority, "active": 1 if body.is_active else 0}
|
|
if body.id:
|
|
await db.execute(text("""
|
|
UPDATE virtual_collection SET name=:name, color=:color, crate_ids=:crates, genre_ids=:genres,
|
|
style_ids=:styles, label_ids=:labels, format_descriptions=:formats, price_min=:pmin,
|
|
price_max=:pmax, year_min=:ymin, year_max=:ymax, sort_method=:sort, priority=:prio,
|
|
is_active=:active, updated_at=now() WHERE id=:id
|
|
"""), {**p, "id": body.id})
|
|
cid = body.id
|
|
else:
|
|
cid = (await db.execute(text("""
|
|
INSERT INTO virtual_collection (name, color, crate_ids, genre_ids, style_ids, label_ids,
|
|
format_descriptions, price_min, price_max, year_min, year_max, sort_method, priority, is_active)
|
|
VALUES (:name,:color,:crates,:genres,:styles,:labels,:formats,:pmin,:pmax,:ymin,:ymax,:sort,:prio,:active)
|
|
RETURNING id
|
|
"""), p)).first()[0]
|
|
await db.commit()
|
|
return {"ok": True, "id": cid}
|
|
|
|
|
|
@router.delete("/collections/{cid}")
|
|
async def delete_collection(cid: int, ident=Depends(require_token), db=Depends(get_db)):
|
|
await db.execute(text("DELETE FROM virtual_collection WHERE id=:i"), {"i": cid})
|
|
await db.commit()
|
|
return {"ok": True}
|
|
|
|
|
|
@router.get("/genres")
|
|
async def genres(ident=Depends(require_token), db=Depends(get_db)):
|
|
rows = await db.execute(text("SELECT id, name FROM disc_genre ORDER BY name"))
|
|
return {"genres": [dict(r) for r in rows.mappings()]}
|
|
|
|
|
|
@router.get("/styles")
|
|
async def styles(q: str = Query(""), ident=Depends(require_token), db=Depends(get_db)):
|
|
if q.strip():
|
|
rows = await db.execute(text(
|
|
"SELECT id, name FROM disc_style WHERE name ILIKE :q ORDER BY name LIMIT 40"
|
|
), {"q": f"%{q.strip()}%"})
|
|
else:
|
|
rows = await db.execute(text("SELECT id, name FROM disc_style ORDER BY name LIMIT 40"))
|
|
return {"styles": [dict(r) for r in rows.mappings()]}
|
|
|
|
|
|
class StatsIn(BaseModel):
|
|
crate_ids: list[int] = []
|
|
genre_ids: list[int] = []
|
|
style_ids: list[int] = []
|
|
price_min: float | None = None
|
|
price_max: float | None = None
|
|
year_min: int | None = None
|
|
year_max: int | None = None
|
|
|
|
|
|
@router.post("/collections/stats")
|
|
async def collection_stats(body: StatsIn, ident=Depends(require_token), db=Depends(get_db)):
|
|
"""Live match panel for the editor: how many in-stock records the rules select, and how many of
|
|
those actually sit in the chosen crates (located) vs not (misfiled)."""
|
|
conds, p = ["i.in_stock"], {}
|
|
if body.price_min is not None:
|
|
conds.append("i.price >= :pmin"); p["pmin"] = body.price_min
|
|
if body.price_max is not None:
|
|
conds.append("i.price <= :pmax"); p["pmax"] = body.price_max
|
|
if body.year_min is not None:
|
|
conds.append("dr.year >= :ymin"); p["ymin"] = body.year_min
|
|
if body.year_max is not None:
|
|
conds.append("dr.year <= :ymax"); p["ymax"] = body.year_max
|
|
if body.genre_ids:
|
|
conds.append("dr.genre_ids && :genres"); p["genres"] = body.genre_ids
|
|
if body.style_ids:
|
|
conds.append("dr.style_ids && :styles"); p["styles"] = body.style_ids
|
|
if len(conds) == 1:
|
|
return {"matching": 0, "located": 0, "not_located": 0} # no rules = matches nothing
|
|
base = "FROM inventory i JOIN disc_release dr ON dr.id = i.release_id WHERE " + " AND ".join(conds)
|
|
matching = (await db.execute(text(f"SELECT count(*) {base}"), p)).scalar()
|
|
located = 0
|
|
if body.crate_ids:
|
|
located = (await db.execute(text(
|
|
f"SELECT count(*) {base} AND i.crate_id = ANY(:crates)"), {**p, "crates": body.crate_ids})).scalar()
|
|
return {"matching": matching, "located": located, "not_located": matching - located}
|
|
|
|
|
|
@router.get("/locate")
|
|
async def locate(release_id: int | None = Query(None), sku: str | None = Query(None),
|
|
ident=Depends(require_token), db=Depends(get_db)):
|
|
"""Where IS this record (exact crate+slot) and, failing that, where SHOULD it be (matching collection)."""
|
|
if sku:
|
|
inv = (await db.execute(text("SELECT * FROM inventory WHERE sku=:s"), {"s": sku})).mappings().first()
|
|
elif release_id:
|
|
inv = (await db.execute(text(
|
|
"SELECT * FROM inventory WHERE release_id=:r AND in_stock ORDER BY updated_at DESC LIMIT 1"
|
|
), {"r": release_id})).mappings().first()
|
|
else:
|
|
raise HTTPException(400, "release_id or sku required")
|
|
if not inv:
|
|
raise HTTPException(404, "not in inventory")
|
|
|
|
exact = None
|
|
if inv.get("crate_id"):
|
|
cr = (await db.execute(text("""
|
|
SELECT c.id, c.label_text, c.name, c.rack_id, c.rack_level_index::int AS level, r.name AS rack
|
|
FROM virtual_crate c LEFT JOIN virtual_rack r ON r.id=c.rack_id WHERE c.id=:i
|
|
"""), {"i": inv["crate_id"]})).mappings().first()
|
|
if cr:
|
|
exact = {"crate_id": cr["id"], "crate": cr["label_text"] or cr["name"], "rack": cr["rack"],
|
|
"level": cr["level"], "slot": inv.get("slot_number")}
|
|
|
|
# fallback: match the record against active collections by priority. genre/style needs the disc_cache
|
|
# enrichment (added but backfilled separately) — until then a style-ruled collection won't match.
|
|
meta = (await db.execute(text(
|
|
"SELECT year, genre_ids, style_ids FROM disc_release WHERE id=:r"
|
|
), {"r": inv.get("release_id")})).mappings().first() if inv.get("release_id") else None
|
|
price = float(inv["price"]) if inv.get("price") is not None else None
|
|
year = meta["year"] if meta else None
|
|
rg = set(meta["genre_ids"] or []) if meta else set() # the record's genre ids (WowPlatter id space)
|
|
rs = set(meta["style_ids"] or []) if meta else set()
|
|
|
|
suggestion = None
|
|
cols = (await db.execute(text("""
|
|
SELECT id, name, color, crate_ids, genre_ids, style_ids, price_min::float AS pmin,
|
|
price_max::float AS pmax, year_min, year_max
|
|
FROM virtual_collection WHERE is_active::int <> 0 ORDER BY priority DESC, name
|
|
"""))).mappings()
|
|
for c in cols:
|
|
gmin, gmax = c["pmin"], c["pmax"]
|
|
if gmin is not None and (price is None or price < gmin):
|
|
continue
|
|
if gmax is not None and (price is None or price > gmax):
|
|
continue
|
|
if c["year_min"] is not None and (year is None or year < c["year_min"]):
|
|
continue
|
|
if c["year_max"] is not None and (year is None or year > c["year_max"]):
|
|
continue
|
|
cgen, csty = set(_ids(c["genre_ids"])), set(_ids(c["style_ids"]))
|
|
if cgen and not (rg & cgen):
|
|
continue
|
|
if csty and not (rs & csty):
|
|
continue
|
|
if not (gmin or gmax or c["year_min"] or c["year_max"] or cgen or csty):
|
|
continue # a ruleless collection matches nothing
|
|
ids = _arr(c["crate_ids"])
|
|
names = [dict(r) for r in (await db.execute(text(
|
|
"SELECT id, label_text, name FROM virtual_crate WHERE id = ANY(:ids)"), {"ids": ids})).mappings()]
|
|
suggestion = {"id": c["id"], "name": c["name"], "color": c["color"],
|
|
"crates": [{"id": n["id"], "label": n["label_text"] or n["name"]} for n in names]}
|
|
break
|
|
|
|
return {"ok": True, "sku": inv["sku"], "release_id": inv.get("release_id"),
|
|
"exact": exact, "suggested": suggestion}
|