import asyncio
import html
import json
import logging
import re
import secrets
from datetime import datetime

import httpx
from fastapi import APIRouter, Depends, Query, HTTPException, Header
from fastapi.responses import HTMLResponse
from pydantic import BaseModel
from sqlalchemy import text

from . import vault

_YT = re.compile(r"(?:v=|youtu\.be/|embed/|/v/)([\w-]{11})")


def _ytid(uri):
    if not uri:
        return None
    m = _YT.search(uri)
    if m:
        return m.group(1)
    return uri if re.fullmatch(r"[\w-]{11}", uri) else None

from .db import get_db
from .layout_routes import DEFAULTS

# PUBLIC storefront API — the customer-facing shop reads its theme/layout + catalog here.
# No auth (theme + in-stock catalog aren't secret); driven by what the builder saves.
router = APIRouter(prefix="/shop", tags=["shop"])
log = logging.getLogger("recordgod")


async def _tracking_head(db) -> str:
    """Analytics/marketing snippets for the storefront <head> — Meta Pixel, Umami, GA4 (all opt-in via settings)."""
    rows = await db.execute(text(
        "SELECT setting_key, setting_value FROM sales_setting "
        "WHERE setting_key IN ('meta_pixel_id','umami_website_id','umami_src','ga4_id') AND is_active"))
    s = {r["setting_key"]: (r["setting_value"] or "").strip() for r in rows.mappings()}
    out = []
    if s.get("meta_pixel_id"):
        pid = s["meta_pixel_id"]
        out.append("<script>!function(f,b,e,v,n,t,s){if(f.fbq)return;n=f.fbq=function(){n.callMethod?"
                   "n.callMethod.apply(n,arguments):n.queue.push(arguments)};if(!f._fbq)f._fbq=n;n.push=n;"
                   "n.loaded=!0;n.version='2.0';n.queue=[];t=b.createElement(e);t.async=!0;t.src=v;"
                   "s=b.getElementsByTagName(e)[0];s.parentNode.insertBefore(t,s)}(window,document,'script',"
                   f"'https://connect.facebook.net/en_US/fbevents.js');fbq('init','{pid}');fbq('track','PageView');</script>"
                   f'<noscript><img height="1" width="1" style="display:none" '
                   f'src="https://www.facebook.com/tr?id={pid}&ev=PageView&noscript=1"/></noscript>')
    if s.get("umami_website_id"):
        src = s.get("umami_src") or "https://cloud.umami.is/script.js"
        out.append(f'<script defer src="{src}" data-website-id="{s["umami_website_id"]}"></script>')
    if s.get("ga4_id"):
        g = s["ga4_id"]
        out.append(f'<script async src="https://www.googletagmanager.com/gtag/js?id={g}"></script>'
                   "<script>window.dataLayer=window.dataLayer||[];function gtag(){dataLayer.push(arguments);}"
                   f"gtag('js',new Date());gtag('config','{g}');</script>")
    return "".join(out)


@router.get("/page/{slug}", response_class=HTMLResponse)
async def shop_page(slug: str, db=Depends(get_db)):
    """Server-rendered content page (About/Shipping/Returns/…) — SEO-friendly HTML, the WP Pages public view."""
    p = (await db.execute(text(
        "SELECT title, body, seo_title, seo_description FROM page WHERE slug = :s AND published"),
        {"s": slug})).mappings().first()
    if not p:
        raise HTTPException(404, "page not found")
    name = (await db.execute(text("SELECT setting_value FROM sales_setting WHERE setting_key='store_name'"))).scalar() or "Store"
    e = html.escape
    title = p["seo_title"] or f'{p["title"]} · {name}'
    desc = p["seo_description"] or ""
    trk = await _tracking_head(db)
    page = (f'<!doctype html><html lang="en"><head><meta charset="utf-8">'
            f'<meta name="viewport" content="width=device-width,initial-scale=1">'
            f'<title>{e(title)}</title><meta name="description" content="{e(desc)}">'
            f'<meta property="og:title" content="{e(title)}"><meta property="og:description" content="{e(desc)}">'
            f'<style>body{{font:16px/1.6 system-ui,-apple-system,sans-serif;max-width:720px;margin:40px auto;'
            f'padding:0 20px;color:#1a1a1a}}h1{{font-size:28px}}a{{color:#c0267e}}img{{max-width:100%}}</style>'
            f'{trk}</head><body><p><a href="/">← {e(name)}</a></p><h1>{e(p["title"])}</h1>{p["body"]}</body></html>')
    return HTMLResponse(page)


@router.get("/config")
async def shop_config(db=Depends(get_db)):
    row = (await db.execute(text("SELECT config FROM store_config WHERE store_id=1"))).first()
    cfg = row[0] if row else None
    if isinstance(cfg, str):
        cfg = json.loads(cfg)
    return cfg or DEFAULTS


@router.get("/catalog")
async def shop_catalog(q: str = Query(""), page: int = Query(1, ge=1), db=Depends(get_db)):
    per = 24
    where = ["i.in_stock", "i.store_id = 1"]
    params = {"per": per, "off": (page - 1) * per}
    if q:
        where.append("(coalesce(i.title, dc.title) ILIKE :q OR dc.artist ILIKE :q)")
        params["q"] = f"%{q}%"
    w = " AND ".join(where)
    items = [dict(r) for r in (await db.execute(text(f"""
        SELECT i.sku, coalesce(i.title, dc.title) AS title, dc.artist, dc.thumb,
               i.price::float AS price, i.condition, i.kind
        FROM inventory i LEFT JOIN disc_cache dc ON dc.release_id = i.release_id
        WHERE {w}
        ORDER BY i.updated_at DESC NULLS LAST
        LIMIT :per OFFSET :off"""), params)).mappings()]
    cparams = {k: v for k, v in params.items() if k not in ("per", "off")}
    total = (await db.execute(text(
        f"SELECT count(*) FROM inventory i LEFT JOIN disc_cache dc ON dc.release_id=i.release_id WHERE {w}"
    ), cparams)).scalar()
    return {"items": items, "page": page, "per": per, "total": total}


@router.get("/artist/{artist_id}")
async def shop_artist(artist_id: int, db=Depends(get_db)):
    r = (await db.execute(text("SELECT id, name, realname FROM disc_artist WHERE id=:i"),
                          {"i": artist_id})).mappings().first()
    return {"artist": dict(r) if r else {"id": artist_id, "name": f"Artist {artist_id}"}}


@router.get("/label/{label_id}")
async def shop_label(label_id: int, db=Depends(get_db)):
    r = (await db.execute(text("SELECT id, name, profile FROM disc_label WHERE id=:i"),
                          {"i": label_id})).mappings().first()
    return {"label": dict(r) if r else {"id": label_id, "name": f"Label {label_id}"}}


@router.get("/browse")
async def shop_browse(q: str = Query(""), genre: str = Query(""), style: str = Query(""),
                      fmt: str = Query(""), artist: int | None = None, label: int | None = None,
                      year_min: int | None = None, year_max: int | None = None,
                      price_min: float | None = None, price_max: float | None = None,
                      sort: str = Query("new"), page: int = Query(1, ge=1), db=Depends(get_db)):
    """Faceted, release-grouped catalog — the public Browse page. One row per release with its cheapest copy."""
    per = 24
    where = ["i.in_stock", "i.store_id = 1", "i.release_id IS NOT NULL"]
    params = {"per": per, "off": (page - 1) * per}
    if q:
        where.append("(coalesce(i.title, dr.title) ILIKE :q OR dr.artists_sort ILIKE :q)"); params["q"] = f"%{q}%"
    if genre:
        where.append("EXISTS (SELECT 1 FROM disc_release_genre g WHERE g.release_id=i.release_id AND g.genre_name=:genre)"); params["genre"] = genre
    if style:
        where.append("EXISTS (SELECT 1 FROM disc_release_style s WHERE s.release_id=i.release_id AND s.style_name=:style)"); params["style"] = style
    if fmt:
        where.append("EXISTS (SELECT 1 FROM disc_release_format f WHERE f.release_id=i.release_id AND f.name ILIKE :fmt)"); params["fmt"] = f"%{fmt}%"
    if artist:
        where.append("EXISTS (SELECT 1 FROM disc_release_artist ra WHERE ra.release_id=i.release_id AND ra.artist_id=:artist)"); params["artist"] = artist
    if label:
        where.append("EXISTS (SELECT 1 FROM disc_release_label rl WHERE rl.release_id=i.release_id AND rl.label_id=:label)"); params["label"] = label
    if year_min:
        where.append("dr.year >= :ymin"); params["ymin"] = year_min
    if year_max:
        where.append("dr.year <= :ymax"); params["ymax"] = year_max
    if price_min:
        where.append("i.price >= :pmin"); params["pmin"] = price_min
    if price_max:
        where.append("i.price <= :pmax"); params["pmax"] = price_max
    w = " AND ".join(where)
    order = {"new": "max(i.updated_at) DESC NULLS LAST", "price_asc": "min(i.price) ASC",
             "price_desc": "min(i.price) DESC", "year": "dr.year DESC NULLS LAST",
             "artist": "dr.artists_sort", "title": "min(coalesce(i.title, dr.title))"}.get(sort, "max(i.updated_at) DESC NULLS LAST")
    items = [dict(r) for r in (await db.execute(text(f"""
        SELECT i.release_id, coalesce(min(i.title), dr.title) AS title, dr.artists_sort AS artist,
               dr.year, dr.country, min(i.price)::float AS price, count(*) AS copies,
               (SELECT thumb FROM disc_cache dc WHERE dc.release_id=i.release_id) AS thumb,
               (SELECT artist_id FROM disc_release_artist ra WHERE ra.release_id=i.release_id ORDER BY ra.position LIMIT 1) AS artist_id,
               (SELECT string_agg(DISTINCT genre_name, ', ') FROM disc_release_genre g WHERE g.release_id=i.release_id) AS genre
        FROM inventory i JOIN disc_release dr ON dr.id = i.release_id
        WHERE {w}
        GROUP BY i.release_id, dr.title, dr.artists_sort, dr.year, dr.country
        ORDER BY {order} LIMIT :per OFFSET :off"""), params)).mappings()]
    cp = {k: v for k, v in params.items() if k not in ("per", "off")}
    total = (await db.execute(text(
        f"SELECT count(DISTINCT i.release_id) FROM inventory i JOIN disc_release dr ON dr.id=i.release_id WHERE {w}"
    ), cp)).scalar()
    return {"items": items, "page": page, "per": per, "total": total}


@router.get("/facets")
async def shop_facets(db=Depends(get_db)):
    async def top(tbl, col):
        return [dict(r) for r in (await db.execute(text(f"""
            SELECT x.{col} AS name, count(DISTINCT i.release_id) AS n
            FROM {tbl} x JOIN inventory i ON i.release_id = x.release_id
            WHERE i.in_stock AND i.store_id=1 AND x.{col} IS NOT NULL AND x.{col} <> ''
            GROUP BY x.{col} ORDER BY n DESC LIMIT 24"""))).mappings()]
    return {"genres": await top("disc_release_genre", "genre_name"),
            "styles": await top("disc_release_style", "style_name"),
            "formats": await top("disc_release_format", "name")}


@router.get("/release/{release_id}")
async def shop_release(release_id: int, db=Depends(get_db)):
    """Public release detail — metadata + tracklist + in-stock copies (with the Woo buy link)."""
    dr = (await db.execute(text("""
        SELECT dr.id, dr.title, dr.artists_sort AS artist, dr.country, dr.year, dr.notes, dr.master_id,
          (SELECT thumb FROM disc_cache dc WHERE dc.release_id=dr.id) AS thumb,
          (SELECT artist_id FROM disc_release_artist ra WHERE ra.release_id=dr.id ORDER BY ra.position LIMIT 1) AS artist_id,
          (SELECT string_agg(label_name || coalesce(' ('||catno||')',''), ', ') FROM disc_release_label WHERE release_id=dr.id) AS label,
          (SELECT string_agg(name || coalesce(' '||descriptions,''), ', ') FROM disc_release_format WHERE release_id=dr.id) AS format,
          (SELECT string_agg(DISTINCT genre_name, ', ') FROM disc_release_genre WHERE release_id=dr.id) AS genre,
          (SELECT string_agg(DISTINCT style_name, ', ') FROM disc_release_style WHERE release_id=dr.id) AS style
        FROM disc_release dr WHERE dr.id = :r"""), {"r": release_id})).mappings().first()
    tracks = [dict(r) for r in (await db.execute(text("""
        SELECT position, title, duration FROM disc_release_track WHERE release_id=:r ORDER BY sequence NULLS LAST, position
    """), {"r": release_id})).mappings()]
    copies = [dict(r) for r in (await db.execute(text("""
        SELECT i.sku, i.price::float AS price, i.condition, i.sleeve_cond, i.product_url,
               c.label_text AS crate, r.name AS rack
        FROM inventory i
        LEFT JOIN virtual_crate c ON c.id = i.crate_id
        LEFT JOIN virtual_rack r ON r.id = c.rack_id
        WHERE i.release_id=:r AND i.in_stock AND i.store_id=1 ORDER BY i.price
    """), {"r": release_id})).mappings()]
    return {"release": dict(dr) if dr else None, "tracks": tracks, "copies": copies}


def _apple_id(raw):
    """apple_id is country-scoped {"<cc>":"<album_id>"} (some rows double-JSON-encoded). Prefer AU
    (this is an AU store), then US, then whatever country is present → a real music.apple.com link."""
    if not raw:
        return None
    try:
        v = json.loads(raw)
        if isinstance(v, str):
            v = json.loads(v)
    except (ValueError, TypeError):
        return None
    if not isinstance(v, dict) or not v:
        return None
    cc = "au" if "au" in v else ("us" if "us" in v else next(iter(v)))
    aid = v.get(cc)
    return {"country": cc, "album_id": aid,
            "url": f"https://music.apple.com/{cc}/album/{aid}" if aid else None}


@router.get("/local-previews/{release_id}")
async def shop_local_previews(release_id: int):
    """Local preview MP3s for the DECKS mixer — files in PREVIEWS_DIR named {release_id}.mp3
    or {release_id}-<track>.mp3 (also .m4a). Full-length + CORS-free, so they beat Apple 30s clips."""
    import os
    import pathlib
    root = pathlib.Path(os.getenv("PREVIEWS_DIR",
                                  pathlib.Path(__file__).resolve().parent.parent / "previews"))
    if not root.is_dir():
        return {"files": []}
    names = sorted(f.name for f in root.iterdir()
                   if f.suffix.lower() in (".mp3", ".m4a")
                   and (f.stem == str(release_id) or f.stem.startswith(f"{release_id}-")))
    return {"files": ["/previews/" + n for n in names]}


# Apple's preview CDN sends no CORS headers, so Web Audio can't decode it cross-origin.
# Same-origin streaming proxy, STRICTLY whitelisted to Apple preview hosts. ~1MB clips.
_PREVIEW_HOSTS = re.compile(r"(\.itunes\.apple\.com|\.mzstatic\.com)$")


@router.get("/preview-proxy")
async def shop_preview_proxy(u: str):
    from urllib.parse import urlparse
    from fastapi.responses import Response
    p = urlparse(u)
    if p.scheme != "https" or not _PREVIEW_HOSTS.search(p.hostname or ""):
        raise HTTPException(403, "host not allowed")
    async with httpx.AsyncClient(timeout=20, follow_redirects=True) as c:
        r = await c.get(u)
        if r.status_code != 200 or not _PREVIEW_HOSTS.search(r.url.host or ""):
            raise HTTPException(502, "upstream failed")
        if len(r.content) > 12_000_000:
            raise HTTPException(502, "too large")
        return Response(r.content, media_type=r.headers.get("content-type", "audio/mp4"),
                        headers={"Cache-Control": "public, max-age=86400"})


@router.get("/audio/{release_id}")
async def shop_audio(release_id: int, db=Depends(get_db)):
    """Playable / embeddable previews — Apple 30s preview + country-scoped Apple link, Beatport/Bandcamp, YouTube."""
    a = (await db.execute(text("""
        SELECT apple_id, apple_url, apple_preview_url, beatport_id, beatport_url, beatport_embed,
               beatport_genre, spotify_id, bandcamp_url, bandcamp_embed, soundcloud_url
        FROM disc_release_audio WHERE release_id = :r"""), {"r": release_id})).mappings().first()
    youtube = await _live_youtube(db, release_id)
    if not a:
        return {"apple": None, "apple_preview": None, "youtube": youtube}
    pv = a["apple_preview_url"]
    previews = []
    if isinstance(pv, str) and pv.lstrip().startswith("["):
        try:
            previews = [u for u in json.loads(pv) if u]
        except (ValueError, TypeError):
            previews = []
    elif pv:
        previews = [pv]
    return {"apple": _apple_id(a["apple_id"]), "apple_url": a["apple_url"],
            "apple_preview": previews[0] if previews else None, "apple_previews": previews,
            "beatport_url": a["beatport_url"], "beatport_embed": a["beatport_embed"],
            "beatport_id": a["beatport_id"], "bandcamp_url": a["bandcamp_url"],
            "bandcamp_embed": a["bandcamp_embed"], "soundcloud_url": a["soundcloud_url"],
            "spotify_id": a["spotify_id"], "youtube": youtube}


async def _live_youtube(db, release_id):
    """Pick a NON-dead YouTube clip — validates unchecked ones via YouTube oembed (200=live) and caches `dead`."""
    rows = [dict(r) for r in (await db.execute(text(
        "SELECT DISTINCT uri, dead FROM disc_release_video WHERE release_id=:r AND uri<>'' ORDER BY uri LIMIT 8"
    ), {"r": release_id})).mappings()]
    unchecked = [v for v in rows if v["dead"] is None][:4]
    if unchecked:
        async with httpx.AsyncClient(timeout=8) as c:
            async def chk(v):
                yid = _ytid(v["uri"])
                if not yid:
                    return v["uri"], True
                try:
                    rr = await c.get("https://www.youtube.com/oembed",
                                     params={"url": "https://youtu.be/" + yid, "format": "json"})
                    return v["uri"], rr.status_code != 200
                except Exception:
                    return v["uri"], None   # network blip — leave unchecked for next time
            checked = await asyncio.gather(*[chk(v) for v in unchecked])
        wrote = False
        for uri, dead in checked:
            if dead is not None:
                await db.execute(text("UPDATE disc_release_video SET dead=:d, checked_at=now() "
                                      "WHERE release_id=:r AND uri=:u"), {"d": dead, "r": release_id, "u": uri})
                for v in rows:
                    if v["uri"] == uri:
                        v["dead"] = dead
                wrote = True
        if wrote:
            await db.commit()
    for v in rows:
        if v["dead"] is False:
            yid = _ytid(v["uri"])
            if yid:
                return yid
    return None


@router.get("/audio-tracks/{release_id}")
async def shop_audio_tracks(release_id: int, db=Depends(get_db)):
    """Per-track 30s previews — lazily resolved from each track's Apple song id via the iTunes lookup API, then cached."""
    rows = [dict(r) for r in (await db.execute(text("""
        SELECT sequence, position, title, apple_id, apple_preview
        FROM disc_release_track WHERE release_id=:r AND apple_id ~ '^[0-9]+$'
        ORDER BY sequence NULLS LAST, position"""), {"r": release_id})).mappings()]
    need = [t["apple_id"] for t in rows if not t["apple_preview"]]
    if need:
        found = {}
        try:
            async with httpx.AsyncClient(timeout=15) as c:
                rr = await c.get("https://itunes.apple.com/lookup",
                                 params={"id": ",".join(need[:190]), "country": "au", "entity": "song"})
            found = {str(x.get("trackId")): x.get("previewUrl")
                     for x in rr.json().get("results", []) if x.get("previewUrl")}
        except Exception:
            found = {}
        for aid, prev in found.items():
            await db.execute(text("UPDATE disc_release_track SET apple_preview=:p WHERE release_id=:r AND apple_id=:a"),
                             {"p": prev, "r": release_id, "a": aid})
        if found:
            await db.commit()
        for t in rows:
            if not t["apple_preview"]:
                t["apple_preview"] = found.get(t["apple_id"])
    return {"tracks": [{"position": t["position"], "title": t["title"], "preview": t["apple_preview"]}
                       for t in rows if t["apple_preview"]]}


class WantIn(BaseModel):
    email: str
    artist: str = ""
    title: str = ""
    name: str = ""
    phone: str = ""
    format: str = ""
    max_price: float | None = None
    delivery_preference: str = "either"
    postcode: str = ""
    notes: str = ""
    release_id: int | None = None


@router.post("/wantlist")
async def shop_wantlist(body: WantIn, db=Depends(get_db)):
    """Public 'request a record we don't stock' intake — no auth, keyed by email (guest or known customer)."""
    if "@" not in body.email or not (body.artist.strip() or body.title.strip()):
        raise HTTPException(422, "email and an artist or title are required")
    pref = body.delivery_preference if body.delivery_preference in ("pickup", "post", "either") else "either"
    rid = (await db.execute(text("""
        INSERT INTO wantlist (release_id, artist, title, name, phone, email, format, max_price,
                              delivery_preference, postcode, notes)
        VALUES (:release_id, :artist, :title, :name, :phone, :email, :format, :max_price, :pref, :postcode, :notes)
        RETURNING id"""), {**body.model_dump(), "pref": pref})).scalar()
    await db.commit()
    return {"ok": True, "id": rid}


# ─── WordPress / Woo bridge ──────────────────────────────────────────────────────────────────
# The thin RecordGod WP plugin renders the storefront from /shop/*, creates Woo products on the fly
# at add-to-cart (via /shop/item), quotes postage (/shop/shipping/quote), and posts back completed
# orders (/shop/woo-order, bridge-key gated) so RecordGod stays the single source of truth.
@router.get("/item/{sku}")
async def shop_item(sku: str, db=Depends(get_db)):
    """One inventory item by SKU — what the bridge needs to mint a Woo product on the fly."""
    r = (await db.execute(text("""
        SELECT i.sku, coalesce(i.title, dc.title) AS title, dc.artist, dc.thumb,
               i.price::float AS price, i.in_stock, i.condition, i.release_id
        FROM inventory i LEFT JOIN disc_cache dc ON dc.release_id = i.release_id
        WHERE i.sku = :s AND i.store_id = 1"""), {"s": sku})).mappings().first()
    if not r:
        raise HTTPException(404, "item not found")
    return dict(r)


@router.get("/shipping/quote")
async def shop_shipping_quote(units: int | None = None, weight_g: int | None = None, db=Depends(get_db)):
    """Public AusPost quote for the WC shipping method (rates aren't secret)."""
    if weight_g is None:
        weight_g = max(1, units or 1) * 280
    parcels = max(1, -(-weight_g // 5000))
    per = -(-weight_g // parcels)
    rows = await db.execute(text(
        "SELECT service_key, price::float AS price FROM post_flat_rate "
        "WHERE :w BETWEEN weight_min_g AND weight_max_g"), {"w": per})
    seen = {r["service_key"]: r["price"] for r in rows.mappings()}
    opts = [{"service": k, "label": k.replace("_", " ").title(), "price": round(v * parcels, 2)}
            for k, v in sorted(seen.items(), key=lambda x: x[1])]
    return {"weight_g": weight_g, "parcels": parcels, "options": opts}


class CheckoutItem(BaseModel):
    sku: str
    qty: int = 1


class CheckoutIn(BaseModel):
    items: list[CheckoutItem]
    provider: str = "mock"
    token: str                       # tokenised card from the frontend SDK (never raw card data)
    email: str | None = None
    name: str | None = None
    pickup: bool = False             # skip postage


@router.post("/checkout")
async def checkout(body: CheckoutIn, db=Depends(get_db)):
    """Online checkout — the WooCommerce replacement. SERVER-SIDE priced (never trusts the client), GST-inclusive
    (AU), cheapest AusPost postage, charged via ANY configured processor, writes the order + marks stock sold."""
    from . import payments
    if not body.items:
        raise HTTPException(400, "empty cart")
    if body.provider not in await payments.enabled_providers(db):
        raise HTTPException(403, f"payment provider '{body.provider}' is not available")
    rows = {r["sku"]: dict(r) for r in (await db.execute(text("""
        SELECT i.sku, coalesce(i.title, dc.title) AS title, i.price::float AS price, i.in_stock,
               i.weight_g::float AS weight_g, i.release_id
        FROM inventory i LEFT JOIN disc_cache dc ON dc.release_id = i.release_id
        WHERE i.sku = ANY(:skus) AND i.store_id = 1"""), {"skus": [i.sku for i in body.items]})).mappings()}
    lines, subtotal, weight = [], 0.0, 0.0
    for it in body.items:
        r = rows.get(it.sku)
        if not r:
            raise HTTPException(400, f"unknown item {it.sku}")
        if not r["in_stock"]:
            raise HTTPException(409, f"{r.get('title') or it.sku} is already sold")
        qty, price = 1, float(r["price"] or 0)   # ponytail: one-row-per-copy — a sku IS one physical item; qty>1 can't be honoured
        subtotal += price * qty
        weight += (r["weight_g"] or 280) * qty
        lines.append({"sku": it.sku, "title": r.get("title"), "release_id": r.get("release_id"), "qty": qty, "price": price})
    shipping = 0.0
    if not body.pickup:
        wg = int(weight) + 50 * len(lines)               # +packaging
        parcels = max(1, -(-wg // 5000)); per = -(-wg // parcels)
        cheapest = ((await db.execute(text(
            "SELECT min(price)::float AS p FROM post_flat_rate WHERE :w BETWEEN weight_min_g AND weight_max_g"),
            {"w": per})).mappings().first() or {}).get("p")
        shipping = round((cheapest or 0) * parcels, 2)
    total = round(subtotal + shipping, 2)
    gst = round(total / 11, 2)                            # AU GST-inclusive component (for the order record)
    ref = "WEB" + datetime.now().strftime("%y%m%d%H%M%S")
    # CLAIM stock atomically BEFORE charging — flip each copy sold in one statement so two concurrent
    # buyers of a unique copy can't both charge. A missed claim releases the ones we already took.
    # ponytail: claim-then-charge; a crash between claim and charge strands the copy sold with no sale — manual release, no sweeper. Add a sweeper if crashes get common.
    claimed = []
    for li in lines:
        got = (await db.execute(text(
            "UPDATE inventory SET in_stock=false, status='sold', sold_date=now() "
            "WHERE sku=:sku AND store_id=1 AND in_stock RETURNING sku"), {"sku": li["sku"]})).first()
        if got:
            claimed.append(li["sku"])
        else:                                        # someone just bought it — release our partial claim, bail
            if claimed:
                await db.execute(text("UPDATE inventory SET in_stock=true, status='publish', sold_date=NULL "
                                      "WHERE sku=ANY(:claimed) AND store_id=1"), {"claimed": claimed})
            await db.commit()
            raise HTTPException(409, f"{li['sku']} just sold")
    await db.commit()                                # claims are locked in before any money moves
    pay = await payments.charge(db, body.provider, body.token, int(round(total * 100)), "AUD", ref)
    if not pay.get("ok"):
        await db.execute(text("UPDATE inventory SET in_stock=true, status='publish', sold_date=NULL "
                              "WHERE sku=ANY(:claimed) AND store_id=1"), {"claimed": claimed})   # decline → back on the floor
        await db.commit()
        return {"ok": False, "stage": "payment", "error": pay.get("error")}
    try:   # money has left the customer — if we can't record the order, log for manual refund/reconciliation
        sn = "W" + datetime.now().strftime("%y%m%d") + "-" + secrets.token_hex(2).upper()
        sale_id = (await db.execute(text("""
            INSERT INTO sales (sale_number, subtotal, discount_amount, tax_amount, total, status, payment_method,
                               payment_status, amount_paid, notes, sale_date, created_at)
            VALUES (:sn, :sub, 0, :tax, :total, 'publish', :pm, 'paid', :total, :notes, now(), now()) RETURNING id"""),
            {"sn": sn, "sub": round(subtotal, 2), "tax": gst, "total": total, "pm": pay["provider"],
             "notes": f"online · pay {pay.get('payment_id')} · ship ${shipping}" + (f" · {body.email}" if body.email else "")})).first()[0]
        for li 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,:q,:up,:lt)"""),
                             {"s": sale_id, "sku": li["sku"], "rid": li["release_id"], "nm": li["title"],
                              "q": li["qty"], "up": li["price"], "lt": round(li["price"] * li["qty"], 2)})
            # stock already claimed sold above (atomic claim before the charge)
        await db.commit()
    except Exception:
        log.error("checkout: PAYMENT TAKEN but order write failed — manual refund/reconcile. "
                  "payment_id=%s provider=%s ref=%s", pay.get("payment_id"), pay.get("provider"), ref)
        raise
    try:   # anti-oversell: delist these from Discogs now they've sold online (best-effort)
        from . import discogs_mp
        await discogs_mp.delist_skus(db, [li["sku"] for li in lines])
    except Exception:
        pass
    return {"ok": True, "order": sn, "sale_id": sale_id, "subtotal": round(subtotal, 2),
            "shipping": shipping, "gst": gst, "total": total,
            "payment": {"provider": pay["provider"], "id": pay.get("payment_id")}}


class WooOrderIn(BaseModel):
    order_number: str
    email: str | None = None
    name: str | None = None
    total: float = 0
    items: list[dict] = []          # [{sku, name, qty, price}]


@router.post("/woo-order")
async def woo_order(body: WooOrderIn, x_bridge_key: str = Header(None), db=Depends(get_db)):
    """Record a completed Woo order back into RecordGod (mark sold + log the online sale). Bridge-key gated."""
    key = await vault.get_secret(db, "bridge_key")
    if not key or x_bridge_key != key:
        raise HTTPException(401, "bad or missing bridge key")
    sn = "WC-" + str(body.order_number)
    if (await db.execute(text("SELECT 1 FROM sales WHERE sale_number = :n"), {"n": sn})).first():
        return {"ok": True, "duplicate": True}
    cust = None
    if body.email:
        c = (await db.execute(text("SELECT id FROM customer WHERE lower(email)=lower(:e) ORDER BY id LIMIT 1"),
                              {"e": body.email})).first()
        cust = c[0] if c else None
    sale_id = (await db.execute(text("""
        INSERT INTO sales (sale_number, customer_id, subtotal, total, status, payment_method, payment_status,
                           amount_paid, sale_date, created_at)
        VALUES (:sn, :cust, :tot, :tot, 'completed', 'woo', 'paid', :tot, now(), now()) RETURNING id"""),
        {"sn": sn, "cust": cust, "tot": body.total})).scalar()
    for it in body.items:
        await db.execute(text("""
            INSERT INTO sale_items (sale_id, sku, item_name, qty, unit_price, line_total)
            VALUES (:s, :sku, :n, :q, :p, :lt)"""),
            {"s": sale_id, "sku": it.get("sku"), "n": it.get("name") or it.get("sku"),
             "q": it.get("qty", 1), "p": it.get("price", 0),
             "lt": float(it.get("price", 0)) * int(it.get("qty", 1))})
    skus = [it["sku"] for it in body.items if it.get("sku")]
    if skus:
        await db.execute(text("UPDATE inventory SET in_stock=false, status='sold', sold_date=now() "
                              "WHERE sku = ANY(:s) AND store_id = 1"), {"s": skus})
    await db.commit()
    return {"ok": True, "sale_id": sale_id, "sale_number": sn}
