RECORDGOD/app/discogs_mp.py

"""Discogs Marketplace Manager — manage YOUR Discogs listings + orders from StoreGod (the WowPlatter version, but
heaps better: one source of truth, cross-channel inventory, DealGod pricing). Uses the seller's personal access token
(vault `discogs_token`) — authorises managing your own inventory/orders. Discogs rate-limits ~60/min authenticated.

Marketplace API: GET /users/{u}/inventory · POST/DELETE /marketplace/listings[/{id}] · GET /marketplace/orders[/{id}]
· GET /marketplace/price_suggestions/{release_id}. Condition strings must be Discogs' EXACT values (mapped below).
"""
import httpx
try:
    from sqlalchemy import text          # guarded so the standalone condition-map selfcheck runs without sqlalchemy
except ImportError:
    text = None

DISCOGS = "https://api.discogs.com"
UA = "StoreGod/1.0 +https://recordgod.com"

# Goldmine/loose grades → Discogs' EXACT condition strings (the API rejects anything else)
_COND = {
    "M": "Mint (M)", "MINT": "Mint (M)",
    "NM": "Near Mint (NM or M-)", "M-": "Near Mint (NM or M-)", "NEARMINT": "Near Mint (NM or M-)",
    "VG+": "Very Good Plus (VG+)", "VGPLUS": "Very Good Plus (VG+)", "VG +": "Very Good Plus (VG+)",
    "VG": "Very Good (VG)", "G+": "Good Plus (G+)", "GPLUS": "Good Plus (G+)",
    "G": "Good (G)", "GOOD": "Good (G)", "F": "Fair (F)", "FAIR": "Fair (F)", "P": "Poor (P)", "POOR": "Poor (P)",
}


def discogs_condition(v, default="Very Good Plus (VG+)"):
    if not v:
        return default
    k = str(v).strip().upper()
    if k in _COND:
        return _COND[k]
    k2 = k.replace("(", "").replace(")", "").replace(" ", "")
    return _COND.get(k2, _COND.get(k.split()[0] if k.split() else k, default))


async def _auth(db):
    from . import vault
    tok = await vault.get_secret(db, "discogs_token")
    return tok


async def _req(tok, method, path, **kw):
    async with httpx.AsyncClient(timeout=25, headers={
            "Authorization": f"Discogs token={tok}", "User-Agent": UA}) as c:
        return await c.request(method, DISCOGS + path, **kw)


async def seller(db):
    tok = await _auth(db)
    if not tok:
        return None
    r = await _req(tok, "GET", "/oauth/identity")
    return r.json().get("username") if r.status_code == 200 else None


async def status(db):
    """Seller + live counts — the cockpit header."""
    tok = await _auth(db)
    if not tok:
        return {"ok": False, "reason": "no Discogs token (add it in Connections)"}
    me = await _req(tok, "GET", "/oauth/identity")
    if me.status_code != 200:
        return {"ok": False, "reason": f"token rejected ({me.status_code})"}
    u = me.json()["username"]
    inv = await _req(tok, "GET", f"/users/{u}/inventory", params={"per_page": 1, "status": "For Sale"})
    orders = await _req(tok, "GET", "/marketplace/orders", params={"per_page": 1})
    return {"ok": True, "seller": u,
            "listings": inv.json().get("pagination", {}).get("items", 0) if inv.status_code == 200 else None,
            "orders": orders.json().get("pagination", {}).get("items", 0) if orders.status_code == 200 else None}


async def orders(db, page=1):
    tok = await _auth(db)
    if not tok:
        return {"ok": False, "reason": "no token"}
    r = await _req(tok, "GET", "/marketplace/orders", params={"per_page": 25, "page": page, "sort": "last_activity"})
    if r.status_code != 200:
        return {"ok": False, "reason": f"HTTP {r.status_code}"}
    d = r.json()
    return {"ok": True, "orders": [{"id": o["id"], "status": o["status"],
            "total": o.get("total", {}).get("value"), "buyer": (o.get("buyer") or {}).get("username"),
            "created": o.get("created"), "items": len(o.get("items", []))} for o in d.get("orders", [])],
            "pages": d.get("pagination", {}).get("pages", 1)}


async def price_suggestions(db, release_id):
    tok = await _auth(db)
    if not tok:
        return None
    r = await _req(tok, "GET", f"/marketplace/price_suggestions/{release_id}")
    return r.json() if r.status_code == 200 else None


async def create_listing(db, release_id, price, condition, sleeve=None, status="Draft", comments=None):
    """List one item. status='Draft' is SAFE (not live/sellable) — use it to test; 'For Sale' goes live."""
    tok = await _auth(db)
    if not tok:
        return {"ok": False, "error": "no token"}
    body = {"release_id": int(release_id), "condition": discogs_condition(condition),
            "price": round(float(price), 2), "status": status}
    if sleeve:
        body["sleeve_condition"] = discogs_condition(sleeve)
    if comments:
        body["comments"] = comments
    r = await _req(tok, "POST", "/marketplace/listings", json=body)
    if r.status_code in (200, 201):
        return {"ok": True, "listing_id": r.json().get("listing_id")}
    return {"ok": False, "error": r.text[:200], "status": r.status_code}


async def update_listing(db, listing_id, **fields):
    tok = await _auth(db)
    if not tok:
        return {"ok": False, "error": "no token"}
    if "condition" in fields:
        fields["condition"] = discogs_condition(fields["condition"])
    r = await _req(tok, "POST", f"/marketplace/listings/{listing_id}", json=fields)
    return {"ok": r.status_code in (200, 204), "status": r.status_code}


async def delete_listing(db, listing_id):
    tok = await _auth(db)
    if not tok:
        return {"ok": False, "error": "no token"}
    r = await _req(tok, "DELETE", f"/marketplace/listings/{listing_id}")
    return {"ok": r.status_code in (200, 204), "status": r.status_code}


async def delist_skus(db, skus):
    """Anti-oversell: any of these just-sold skus that have a LIVE Discogs listing → delist it. Best-effort —
    a Discogs hiccup must NEVER fail the sale (the sale's already committed when this runs)."""
    skus = [s for s in (skus or []) if s]
    if not skus:
        return 0
    rows = (await db.execute(text(
        "SELECT sku, discogs_listing_id FROM inventory WHERE sku = ANY(:s) AND discogs_listing_id IS NOT NULL"),
        {"s": skus})).mappings().all()
    n = 0
    for r in rows:
        try:
            if (await delete_listing(db, r["discogs_listing_id"])).get("ok"):
                await db.execute(text("UPDATE inventory SET discogs_listing_id = NULL WHERE sku = :s"), {"s": r["sku"]})
                n += 1
        except Exception:
            pass
    if n:
        await db.commit()
    return n


async def sync_orders(db):
    """Pull Discogs marketplace orders → mark the matching StoreGod item sold + record the sale. Idempotent
    (skips orders already imported via sales.discogs_order_id). The other half of anti-oversell: sell on Discogs,
    it comes off the StoreGod floor too."""
    tok = await _auth(db)
    if not tok:
        return {"ok": False, "reason": "no token"}
    r = await _req(tok, "GET", "/marketplace/orders",
                   params={"per_page": 50, "sort": "last_activity", "sort_order": "desc"})
    if r.status_code != 200:
        return {"ok": False, "reason": f"HTTP {r.status_code}"}
    seen = {str(x[0]) for x in (await db.execute(
        text("SELECT discogs_order_id FROM sales WHERE discogs_order_id IS NOT NULL"))).all()}
    imported = []
    for o in r.json().get("orders", []):
        oid = str(o["id"])
        if oid in seen:
            continue
        lines, total = [], 0.0
        for it in o.get("items", []):
            lid = it.get("id") or (it.get("listing") or {}).get("id")
            rid = (it.get("release") or {}).get("id")
            price = float((it.get("price") or {}).get("value") or 0)
            total += price
            inv = (await db.execute(text(
                "SELECT sku, title FROM inventory WHERE (discogs_listing_id = :l OR (:l IS NULL AND release_id = :r AND in_stock)) "
                "AND store_id = 1 LIMIT 1"), {"l": lid, "r": rid})).mappings().first()
            lines.append({"sku": inv["sku"] if inv else None, "rid": rid, "price": price,
                          "title": (inv and inv["title"]) or (it.get("release") or {}).get("description")})
        ototal = float((o.get("total") or {}).get("value") or total)
        st = o.get("status") or ""
        paid = "paid" if any(k in st for k in ("Payment Received", "Shipped", "Merged")) else "pending"
        sale_id = (await db.execute(text("""
            INSERT INTO sales (sale_number, subtotal, tax_amount, total, status, payment_method, payment_status,
                               amount_paid, discogs_order_id, notes, sale_date, created_at)
            VALUES (:sn, :t, 0, :t, 'publish', 'discogs', :ps, :t, :oid, :notes, now(), now()) RETURNING id"""),
            {"sn": f"DG{oid}", "t": ototal, "ps": paid, "oid": oid,
             "notes": f"Discogs order {oid} · {st} · buyer {(o.get('buyer') or {}).get('username') or '?'}"})).first()[0]
        matched = 0
        for ln in lines:
            await db.execute(text("""INSERT INTO sale_items (sale_id, sku, release_id, item_name, qty, unit_price, line_total)
                                     VALUES (:s, :sku, :rid, :nm, 1, :p, :p)"""),
                             {"s": sale_id, "sku": ln["sku"], "rid": ln["rid"], "nm": ln["title"], "p": ln["price"]})
            if ln["sku"]:
                await db.execute(text("UPDATE inventory SET in_stock=false, status='sold', sold_date=now(), "
                                      "discogs_listing_id=NULL WHERE sku=:s AND store_id=1"), {"s": ln["sku"]})
                matched += 1
        imported.append({"order": oid, "status": st, "items": len(lines), "matched": matched})
    await db.commit()
    return {"ok": True, "count": len(imported), "imported": imported}


def _selfcheck():
    assert discogs_condition("VG+") == "Very Good Plus (VG+)"
    assert discogs_condition("nm") == "Near Mint (NM or M-)"
    assert discogs_condition("Mint (M)") == "Mint (M)"
    assert discogs_condition(None) == "Very Good Plus (VG+)"
    assert discogs_condition("garbage") == "Very Good Plus (VG+)"   # safe default
    print("ok")


if __name__ == "__main__":
    _selfcheck()