"""Parts data access — the read layer the rest of the migration reuses.
Parameterized queries only. Mirrors the legacy site's data shape
(parts/scan_count) so behavior matches once pointed at prod."""

from .db import get_db


# --- module-level constants (hoisted so recent_* and in_stock can
# reference them in their default arg values; default args evaluate
# at def-time, not call-time, so the constants must already exist). ---

# Sort options exposed to the filter UI. Map names → ORDER BY clause.
# DEFAULT_SORT (price_desc) is the everywhere default — type/category pages,
# build part-picking, fragments. price high→low opens a category with
# substantial/complete items first instead of $3 trivia, which is what you want
# when picking parts for a build. The ONE exception is the bare /browse landing
# (no type selected): there price_desc led with $6-7k complete rifles across all
# categories (a bad first impression), so the browse view overrides the default
# to "newest" via main._default_sort() — newest surfaces fresh + just-restocked
# items (the site's core value, since `added` resets on restock). See
# main._default_sort + _browse_canonical_url. (Once a "most popular" sort exists
# it likely becomes the better landing default — see attribute-extraction.md.)
# Dict order here mirrors the dropdown order for readability; the UI
# order itself is set in partials/filter_bar.html.
_SORT_CLAUSES = {
    "price_desc": "price DESC, added DESC",
    "price_asc":  "price ASC, added DESC",
    "newest":     "added DESC",
    "oldest":     "added ASC",
}
DEFAULT_SORT = "price_desc"
# Bare-/browse-landing default (no type selected). main._default_sort() returns
# this when sel_type is empty, DEFAULT_SORT otherwise.
LANDING_SORT = "newest"

# Page size for /browse and /instock/* pages. Was 60 (a legacy
# carryover from the old Perl CGI); Mike asked for 50 — round number,
# cleaner math when computing the 4-page caps on /instock/* below.
# Past the first ~10 pages the offset cost on parts (~28k rows) is
# still negligible; revisit only when result sets grow significantly.
PAGE_SIZE = 50


def _safe_img_url(img):
    """Return the img URL if it uses a safe scheme; else None.
    Scraper-derived URLs occasionally contain garbage (javascript:,
    data:, malformed values); whitelist what we render to head off
    XSS-via-future-img-wrapping (security finding M3). <img src> in
    modern browsers won't execute javascript: today, but the same
    URL might end up in <a href> / CSS url() / etc. later and the
    sanitization belongs at the data source."""
    if not img:
        return None
    s = str(img).strip()
    if not s:
        return None
    if (s.startswith("https://") or s.startswith("http://")
            or s.startswith("//") or s.startswith("/")):
        return s
    return None


def _sanitize_imgs(rows):
    """In-place: replace each row's `img` with the safe-scheme version
    (or None). Works on a list/iterable of dict-like rows OR a dict
    of {id: row} — figures out which. Returns the argument for chaining."""
    if isinstance(rows, dict):
        iterable = rows.values()
    else:
        iterable = rows
    for r in iterable:
        if "img" in r:
            r["img"] = _safe_img_url(r["img"])
    return rows


def list_types():
    """In-stock count per type, sorted alphabetically by the human
    label so the dropdown is findable (was sorted by count — useful
    for the marketing-style nav but hostile for a test UI). The label
    comes from TYPE_LABELS in labels.py, which is a Python dict — the
    sort happens in Python, not SQL.

    Folds TYPE_ALIASES into their canonical buckets the same way
    list_types_grouped does — otherwise scraper-side variants like
    AR15Discounts' "optics" plural produce a duplicate homepage chip
    ("Optics 111" + "optics 95" side-by-side). The sidebar correctly
    collapses them; this function is what the homepage uses, so it
    has to match."""
    from .labels import TYPE_ALIASES, TYPE_LABELS
    cur = get_db().cursor()
    cur.execute(
        "SELECT type, COUNT(*) AS n FROM parts "
        "WHERE instock=1 AND price>0 AND hidden=0 GROUP BY type")
    raw = cur.fetchall()
    # Fold scraper-side aliases (e.g. "optics" → "optic") so the
    # canonical bucket gets the combined count and the variant
    # doesn't show up as its own row.
    folded = {}
    for r in raw:
        canon = TYPE_ALIASES.get(r["type"], r["type"])
        folded[canon] = folded.get(canon, 0) + int(r["n"])
    rows = [{"type": t, "n": n} for t, n in folded.items()]
    rows.sort(key=lambda r: TYPE_LABELS.get(r["type"], r["type"]).lower())
    return rows


def list_types_grouped():
    """Sidebar-ready: types grouped by section (Lower / Upper / Other)
    in legacy `partsnav.tmpl` order, each with the human label, current
    in-stock count, and total tracked count.

    Returns a list of dicts:
        [{"section": "Lower Parts", "entries": [
            {"type": "strippedlower", "label": "Stripped Lowers",
             "in_stock": 96, "total": 750},
            …
        ]}, …]

    Key name is `entries` (not `items`) so Jinja doesn't resolve
    `sec.items` to the dict's `.items()` method — Jinja's attribute
    lookup checks attrs before keys, and `items` collides.

    Both counts in one query: `SUM(instock=1)` for in-stock,
    `COUNT(*)` for total. Hidden rows are excluded from both sides so
    the denominator matches what's renderable.

    Sections that resolve to zero items overall are dropped (so an
    empty "Other" doesn't appear in the rail). Types with zero
    in-stock are still listed — they're a legit "we track this, just
    nothing's available right now" signal, exactly like the legacy
    site's `(0/750)` rendering."""
    from .labels import TYPE_ALIASES, TYPE_LABELS, TYPE_SECTIONS
    cur = get_db().cursor()

    # Both counts come from the `category_count` cache (one row per type:
    # in_stock + total), materialized from parts.type by the scanner's
    # `refresh-category-counts` job (daily timer + on demand). Reading the
    # ~35-row cache avoids a ~200ms full scan of `parts` on every page
    # render. It REPLACES the old split (live parts in-stock + `scan_count`
    # total): scan_count counted items-found-per-scan-PAGE-type, which
    # diverges from parts.type under reclassification/legacy data and
    # produced the absurd "(371/131)". See scanner-ng/ngscan/category_counts.py
    # + memory [[scan-count-future]].
    #
    # Fallback: if the cache is empty (not yet populated / refresh lagging),
    # compute live off `parts` so the nav is never blank — identical source
    # + filter to what the cache materializes, so the numbers match once the
    # cache fills.
    cur.execute("SELECT type, in_stock, total FROM category_count")
    rows = cur.fetchall()
    if not rows:
        cur.execute(
            "SELECT type, SUM(instock=1) AS in_stock, COUNT(*) AS total "
            "FROM parts WHERE hidden=0 AND price>0 GROUP BY type")
        rows = cur.fetchall()
    instock = {r["type"]: int(r["in_stock"] or 0) for r in rows}
    total = {r["type"]: int(r["total"] or 0) for r in rows}

    # Fold alias counts into the canonical bucket — e.g. AR15Discounts'
    # "optics" plural rolls into "optic" — for both maps independently.
    def _fold(m):
        out = {}
        for code, n in m.items():
            canon = TYPE_ALIASES.get(code, code)
            out[canon] = out.get(canon, 0) + n
        return out
    instock = _fold(instock)
    total = _fold(total)

    out = []
    for section, type_codes in TYPE_SECTIONS:
        entries = []
        for t in type_codes:
            n_in = instock.get(t, 0)
            n_tot = total.get(t, 0)
            # Skip types that have no signal anywhere and no nav label.
            if n_in == 0 and n_tot == 0 and t not in TYPE_LABELS:
                continue
            entries.append({
                "type": t,
                "label": TYPE_LABELS.get(t, t),
                "in_stock": n_in,
                "total": n_tot,
            })
        if entries:
            out.append({"section": section, "entries": entries})
    return out


def companies(type_=None):
    """Retailers + in-stock counts for the secondary filter dropdown.

    Returns a list of dicts: `[{"name": ..., "count": ...}, ...]`,
    only retailers with `count > 0` (so the dropdown never offers
    a retailer with zero items in the current view), sorted by name.

    When `type_` is set, counts and inclusion are scoped to that
    category — picks up TYPE_ALIASES, so /browse?type=optic sees
    AR15Discounts' `optics` rows too.

    Filters mirror `in_stock()`: hidden=0 + price>0 so the count
    matches what /browse will actually render."""
    from .labels import aliases_for
    where = ["instock=1", "hidden=0", "price>0"]
    args = []
    if type_:
        aliases = aliases_for(type_)
        if len(aliases) == 1:
            where.append("type=%s")
            args.append(type_)
        else:
            placeholders = ",".join(["%s"] * len(aliases))
            where.append(f"type IN ({placeholders})")
            args.extend(aliases)
    cur = get_db().cursor()
    cur.execute(
        "SELECT company, COUNT(*) AS n FROM parts "
        "WHERE " + " AND ".join(where) +
        " GROUP BY company ORDER BY company", args)
    return [{"name": r["company"], "count": int(r["n"])}
            for r in cur.fetchall()]


# ---------------------------------------------------------------------------
# /instock/<slug>/ saved-query pages.
#
# Each `recent_*` function returns rows shaped like `in_stock()` plus a
# page-specific extra field that the partials render as a small subline
# under the description (cards) or an extra column (table). Window
# defaults mirror the legacy values from site/instock/index.cgi.
# ---------------------------------------------------------------------------

# Day windows for the /instock/<slug> saved-query pages. Without
# caps these pages would grow unbounded as the catalog ages — and
# 31-day-old "Recently OOS" stops being interesting anyway. Legacy
# site used these same windows for new/missed/pricedrops.
#
# "popular" diverges from legacy — bumped to 31 days because click
# popularity is genuinely informative on a monthly window (what's
# been driving traffic?), while a 4-day window is too short to be a
# stable signal AND the page is capped to ONE page (top 50 only)
# so the wider window doesn't bloat the result set.
RECENT_DAYS = {
    "new":        7,
    "missed":     7,
    "popular":    31,
    "pricedrops": 7,
}

# Page-count caps for the /instock/<slug> saved-query pages. Two
# shapes:
#   - None  → no cap; the day window in RECENT_DAYS is the natural
#             ceiling. Right for the archive-style pages (new /
#             missed / pricedrops) where the user might want to
#             scroll back through everything in the window.
#   - int N → hard cap to N pages. Right for Popular which is a
#             Top-N list, not an archive — 1 page (top 50) is what
#             "popular" means, not "all 5,000 things with clicks".
MAX_RECENT_PAGES = {
    # `new` is hard-capped at 10 pages (≈500 items) as a stopgap.
    # The full 7-day window flooded with 3,915 items / 79 pages right
    # after the Bear Creek Arsenal launch (2026-05-29) because every
    # part of a freshly-onboarded retailer has parts.added=today, so a
    # whole catalog gets treated as "newly in stock" even though it's
    # just newly-visible-to-us. Real fix (deferred): exclude retailers
    # whose MIN(added) is within a grace window from /instock/new so
    # the page shows genuine new SKUs from established retailers. See
    # TODO.md "Newest In-Stock bulk-onboarding flood".
    "new":        10,
    "missed":     None,
    "popular":    1,
    "pricedrops": None,
}


def recent_new(page=1, per_page=PAGE_SIZE, days=None,
               type_=None, companies_=None, q=None,
               price_min=None, price_max=None, excludes_=None):
    """Items added (or restocked) to in-stock within the last N days.
    Mirrors legacy /instock/new. Extra: `since_added_secs` per row.
    Optional /browse-style filters compose with the window; the recency
    order is preserved."""
    days = days if days is not None else RECENT_DAYS["new"]
    offset = (max(1, int(page)) - 1) * int(per_page)
    fwhere, fargs = _filter_where(type_, companies_, q, price_min, price_max,
                                  excludes_=excludes_)
    where = ["instock=1", "hidden=0", "price>0",
             "TIMESTAMPDIFF(DAY, added, NOW()) < %s"] + fwhere
    cur = get_db().cursor()
    cur.execute(
        "SELECT id, company, type, description, price, url, img, added, "
        "       TIMESTAMPDIFF(SECOND, added, NOW()) AS since_added_secs "
        "FROM parts WHERE " + " AND ".join(where) +
        " ORDER BY added DESC LIMIT %s OFFSET %s",
        [int(days)] + fargs + [int(per_page), int(offset)])
    return _sanitize_imgs(cur.fetchall())


def count_recent_new(days=None, type_=None, companies_=None, q=None,
                     price_min=None, price_max=None, excludes_=None):
    days = days if days is not None else RECENT_DAYS["new"]
    fwhere, fargs = _filter_where(type_, companies_, q, price_min, price_max,
                                  excludes_=excludes_)
    where = ["instock=1", "hidden=0", "price>0",
             "TIMESTAMPDIFF(DAY, added, NOW()) < %s"] + fwhere
    cur = get_db().cursor()
    cur.execute("SELECT COUNT(*) AS n FROM parts WHERE " + " AND ".join(where),
                [int(days)] + fargs)
    return int(cur.fetchone()["n"])


def recent_missed(page=1, per_page=PAGE_SIZE, days=None, settle_secs=300,
                  type_=None, companies_=None, q=None,
                  price_min=None, price_max=None, excludes_=None):
    """Items that went OOS within the last N days. Extra:
    `since_oos_secs` + `was_instock_secs` (how long it lasted in stock).
    The `settle_secs` floor (legacy 300) skips items OOS for less than
    5 minutes — protects against a scanner glitch wiping a category.
    Optional /browse-style filters compose with the window."""
    days = days if days is not None else RECENT_DAYS["missed"]
    offset = (max(1, int(page)) - 1) * int(per_page)
    fwhere, fargs = _filter_where(type_, companies_, q, price_min, price_max,
                                  excludes_=excludes_)
    where = ["instock=0", "hidden=0", "price>0", "removed IS NOT NULL",
             "TIMESTAMPDIFF(SECOND, removed, NOW()) > %s",
             "TIMESTAMPDIFF(DAY, removed, NOW()) < %s"] + fwhere
    cur = get_db().cursor()
    cur.execute(
        "SELECT id, company, type, description, price, url, img, removed, "
        "       TIMESTAMPDIFF(SECOND, removed, NOW()) AS since_oos_secs, "
        "       TIMESTAMPDIFF(SECOND, added, removed) AS was_instock_secs "
        "FROM parts WHERE " + " AND ".join(where) +
        " ORDER BY removed DESC LIMIT %s OFFSET %s",
        [int(settle_secs), int(days)] + fargs + [int(per_page), int(offset)])
    return _sanitize_imgs(cur.fetchall())


def count_recent_missed(days=None, settle_secs=300, type_=None,
                        companies_=None, q=None, price_min=None,
                        price_max=None, excludes_=None):
    days = days if days is not None else RECENT_DAYS["missed"]
    fwhere, fargs = _filter_where(type_, companies_, q, price_min, price_max,
                                  excludes_=excludes_)
    where = ["instock=0", "hidden=0", "price>0", "removed IS NOT NULL",
             "TIMESTAMPDIFF(SECOND, removed, NOW()) > %s",
             "TIMESTAMPDIFF(DAY, removed, NOW()) < %s"] + fwhere
    cur = get_db().cursor()
    cur.execute("SELECT COUNT(*) AS n FROM parts WHERE " + " AND ".join(where),
                [int(settle_secs), int(days)] + fargs)
    return int(cur.fetchone()["n"])


def _popularity_ready(cur) -> bool:
    """True if the materialized part_popularity cache (per-part click totals,
    rebuilt nightly by the scanner) is populated. When empty or missing (e.g.
    before the first rebuild, or a dev DB without the table), the Popular
    queries fall back to the live jump_log join so the page still works."""
    try:
        cur.execute("SELECT 1 FROM part_popularity LIMIT 1")
        return cur.fetchone() is not None
    except Exception:
        return False


def recent_popular(page=1, per_page=PAGE_SIZE, days=None,
                   type_=None, companies_=None, q=None,
                   price_min=None, price_max=None, excludes_=None):
    """Items with the most clicks via jump.cgi over the popularity window.
    Reads the materialized `part_popularity` cache (parts_id -> clicks, rebuilt
    nightly by the scanner) and orders by it, avoiding the ~600ms live
    parts x jump_log join (full scan + temp + filesort) on every render. Falls
    back to that live join if the cache is empty. `days` applies only on the
    fallback path (the cache is built for the configured window). Extra:
    `click_count`. /browse-style filters compose (qualified against `p`)."""
    offset = (max(1, int(page)) - 1) * int(per_page)
    fwhere, fargs = _filter_where(type_, companies_, q, price_min, price_max,
                                  excludes_=excludes_, prefix="p.")
    base = ["p.instock=1", "p.hidden=0", "p.price>0"]
    cur = get_db().cursor()
    if _popularity_ready(cur):
        # STRAIGHT_JOIN + ordering by the pp columns is deliberate and load-
        # bearing: prod's MySQL 8 optimizer otherwise full-scans `parts` and
        # filesorts (~220ms). Forcing pp first lets idx_clicks serve the order
        # via a backward index scan (no temp/filesort, ~49ms). Order by
        # pp.parts_id (= p.id, in idx_clicks) NOT p.id, or the index can't
        # cover the sort. Do NOT "simplify" to a plain JOIN. (MariaDB dev picks
        # the pp-first plan on its own; this just makes prod match.)
        cur.execute(
            "SELECT p.id, p.company, p.type, p.description, p.price, p.url, "
            "       p.img, p.added, pp.clicks AS click_count "
            "FROM part_popularity pp STRAIGHT_JOIN parts p ON p.id = pp.parts_id "
            "WHERE " + " AND ".join(base + fwhere) +
            " ORDER BY pp.clicks DESC, pp.parts_id DESC LIMIT %s OFFSET %s",
            fargs + [int(per_page), int(offset)])
        return _sanitize_imgs(cur.fetchall())
    # Fallback: live jump_log join (slow; only until the cache is first built).
    days = days if days is not None else RECENT_DAYS["popular"]
    where = base + ["j.day BETWEEN CURDATE() - INTERVAL %s DAY AND CURDATE()"] + fwhere
    cur.execute(
        "SELECT p.id, p.company, p.type, p.description, p.price, p.url, p.img,"
        "       p.added, SUM(j.count) AS click_count "
        "FROM parts p JOIN jump_log j ON p.url = j.url "
        "WHERE " + " AND ".join(where) +
        " GROUP BY p.id ORDER BY click_count DESC, p.id DESC LIMIT %s OFFSET %s",
        [int(days)] + fargs + [int(per_page), int(offset)])
    return _sanitize_imgs(cur.fetchall())


def count_recent_popular(days=None, type_=None, companies_=None, q=None,
                         price_min=None, price_max=None, excludes_=None):
    fwhere, fargs = _filter_where(type_, companies_, q, price_min, price_max,
                                  excludes_=excludes_, prefix="p.")
    base = ["p.instock=1", "p.hidden=0", "p.price>0"]
    cur = get_db().cursor()
    if _popularity_ready(cur):
        cur.execute(
            "SELECT COUNT(*) AS n "
            "FROM part_popularity pp JOIN parts p ON p.id = pp.parts_id "
            "WHERE " + " AND ".join(base + fwhere), fargs)
        return int(cur.fetchone()["n"])
    days = days if days is not None else RECENT_DAYS["popular"]
    where = base + ["j.day BETWEEN CURDATE() - INTERVAL %s DAY AND CURDATE()"] + fwhere
    # COUNT(DISTINCT p.id) over the join — same grouping as the main query.
    cur.execute(
        "SELECT COUNT(DISTINCT p.id) AS n "
        "FROM parts p JOIN jump_log j ON p.url = j.url "
        "WHERE " + " AND ".join(where),
        [int(days)] + fargs)
    return int(cur.fetchone()["n"])


def recent_pricedrops(page=1, per_page=PAGE_SIZE, days=None,
                      type_=None, companies_=None, q=None,
                      price_min=None, price_max=None, excludes_=None):
    """In-stock items whose current price is below `last_price.price`,
    where the drop was logged in the last N days. Extra: `prev_price`,
    `drop_amount`, `drop_pct`, `since_drop_secs`.

    Threshold: real drop must be >= 1%. The legacy CGI used
    `ROUND(...) > 0` which lets a 0.6% drop through (rounds to 1).
    Mike 2026-05-27: filter on the unrounded percentage so a sub-1%
    twitch never qualifies. Displayed badge still uses ROUND() so
    a 1.4% drop reads as "-1%"; only the WHERE is stricter."""
    days = days if days is not None else RECENT_DAYS["pricedrops"]
    offset = (max(1, int(page)) - 1) * int(per_page)
    fwhere, fargs = _filter_where(type_, companies_, q, price_min, price_max,
                                  excludes_=excludes_, prefix="p.")
    where = ["p.instock=1", "p.hidden=0", "p.price>0",
             "lp.updated > NOW() - INTERVAL %s DAY",
             "p.price < lp.price",
             "(100 - ((100 * p.price) / lp.price)) >= 1"] + fwhere
    cur = get_db().cursor()
    cur.execute(
        "SELECT p.id, p.company, p.type, p.description, p.price, p.url, "
        "       p.img, p.added, "
        "       lp.price AS prev_price, "
        "       (lp.price - p.price) AS drop_amount, "
        "       ROUND(100 - ((100 * p.price) / lp.price)) AS drop_pct, "
        "       TIMESTAMPDIFF(SECOND, lp.updated, NOW()) AS since_drop_secs "
        "FROM last_price lp JOIN parts p ON p.id = lp.id "
        "WHERE " + " AND ".join(where) +
        " ORDER BY lp.updated DESC LIMIT %s OFFSET %s",
        [int(days)] + fargs + [int(per_page), int(offset)])
    return _sanitize_imgs(cur.fetchall())


def count_recent_pricedrops(days=None, type_=None, companies_=None, q=None,
                            price_min=None, price_max=None, excludes_=None):
    """Count companion for recent_pricedrops. Same >=1% real-drop
    threshold so pagination + headline counts agree."""
    days = days if days is not None else RECENT_DAYS["pricedrops"]
    fwhere, fargs = _filter_where(type_, companies_, q, price_min, price_max,
                                  excludes_=excludes_, prefix="p.")
    where = ["p.instock=1", "p.hidden=0", "p.price>0",
             "lp.updated > NOW() - INTERVAL %s DAY",
             "p.price < lp.price",
             "(100 - ((100 * p.price) / lp.price)) >= 1"] + fwhere
    cur = get_db().cursor()
    cur.execute(
        "SELECT COUNT(*) AS n FROM last_price lp JOIN parts p ON p.id = lp.id "
        "WHERE " + " AND ".join(where),
        [int(days)] + fargs)
    return int(cur.fetchone()["n"])


def recent_companies(kind, days=None):
    """Per-retailer counts scoped to a recent /instock page's WINDOW —
    the analogue of `companies()` for the recent pages. `companies()`
    counts all in-stock items per retailer, which is wrong on these pages
    (it shows a retailer's whole catalog, e.g. Faxon 176, when only ~20
    are new in 7 days). This counts within the same window the page uses,
    so the dropdown numbers match the results and a retailer with 0 in
    the window doesn't appear. Like `companies()`, it does NOT apply the
    user's retailer/price/search filters — it's the baseline scope.

    Returns [{"name": company, "count": n}, ...] sorted by name."""
    cur = get_db().cursor()
    if kind == "new":
        days = days if days is not None else RECENT_DAYS["new"]
        cur.execute(
            "SELECT company AS name, COUNT(*) AS count FROM parts "
            "WHERE instock=1 AND hidden=0 AND price>0 "
            "  AND TIMESTAMPDIFF(DAY, added, NOW()) < %s "
            "GROUP BY company ORDER BY company", (int(days),))
    elif kind == "missed":
        days = days if days is not None else RECENT_DAYS["missed"]
        cur.execute(
            "SELECT company AS name, COUNT(*) AS count FROM parts "
            "WHERE instock=0 AND hidden=0 AND price>0 AND removed IS NOT NULL "
            "  AND TIMESTAMPDIFF(SECOND, removed, NOW()) > 300 "
            "  AND TIMESTAMPDIFF(DAY, removed, NOW()) < %s "
            "GROUP BY company ORDER BY company", (int(days),))
    elif kind == "popular":
        if _popularity_ready(cur):
            cur.execute(
                "SELECT p.company AS name, COUNT(*) AS count "
                "FROM part_popularity pp JOIN parts p ON p.id = pp.parts_id "
                "WHERE p.instock=1 AND p.hidden=0 AND p.price>0 "
                "GROUP BY p.company ORDER BY p.company")
        else:
            days = days if days is not None else RECENT_DAYS["popular"]
            cur.execute(
                "SELECT p.company AS name, COUNT(DISTINCT p.id) AS count "
                "FROM parts p JOIN jump_log j ON p.url = j.url "
                "WHERE p.instock=1 AND p.hidden=0 AND p.price>0 "
                "  AND j.day BETWEEN CURDATE() - INTERVAL %s DAY AND CURDATE() "
                "GROUP BY p.company ORDER BY p.company", (int(days),))
    elif kind == "pricedrops":
        days = days if days is not None else RECENT_DAYS["pricedrops"]
        cur.execute(
            "SELECT p.company AS name, COUNT(*) AS count "
            "FROM last_price lp JOIN parts p ON p.id = lp.id "
            "WHERE p.instock=1 AND p.hidden=0 AND p.price>0 "
            "  AND lp.updated > NOW() - INTERVAL %s DAY "
            "  AND p.price < lp.price "
            "  AND (100 - ((100 * p.price) / lp.price)) >= 1 "
            "GROUP BY p.company ORDER BY p.company", (int(days),))
    else:
        return []
    return [dict(r) for r in cur.fetchall()]


def _filter_where(type_=None, companies_=None, q=None,
                  price_min=None, price_max=None, excludes_=None, prefix=""):
    """User-supplied filter conditions (type / retailer-include /
    retailer-exclude / description search / price range), shared by
    `/browse` (via `_in_stock_where`) AND the /instock recent pages.
    Returns (where_clauses, args) for ONLY the user filters; each caller
    supplies its own base (instock/hidden/price>0) + time-window clauses.

    `prefix` qualifies the column for queries that join `parts` under an
    alias — e.g. 'p.' for the popular / pricedrops joins, where bare
    `company`/`price` would be ambiguous against jump_log / last_price.

    `companies_` (include, URL ?r=) → company IN (…); `excludes_`
    (exclude, URL ?rx=) → company NOT IN (…). They compose. Type alias-
    collapse (optics → optic) happens here, at the type filter only."""
    from .labels import aliases_for
    c = prefix
    where, args = [], []
    if type_:
        aliases = aliases_for(type_)
        if len(aliases) == 1:
            where.append(f"{c}type=%s")
            args.append(type_)
        else:
            ph = ",".join(["%s"] * len(aliases))
            where.append(f"{c}type IN ({ph})")
            args.extend(aliases)
    if companies_:
        comps = list(companies_)
        ph = ",".join(["%s"] * len(comps))
        where.append(f"{c}company IN ({ph})")
        args.extend(comps)
    if excludes_:
        excs = list(excludes_)
        ph = ",".join(["%s"] * len(excs))
        where.append(f"{c}company NOT IN ({ph})")
        args.extend(excs)
    if q:
        q = q.strip()
        if q:
            where.append(f"{c}description LIKE %s")
            args.append("%" + q + "%")
    if price_min is not None:
        where.append(f"{c}price >= %s")
        args.append(float(price_min))
    if price_max is not None:
        where.append(f"{c}price <= %s")
        args.append(float(price_max))
    return where, args


def _in_stock_where(type_=None, companies_=None, q=None,
                    price_min=None, price_max=None, excludes_=None):
    """Base in-stock filter (instock=1, hidden=0, price>0) plus the shared
    user filters. Used by `in_stock` and `count_in_stock`; the paginator's
    total-count matches the result set because both go through here."""
    fwhere, args = _filter_where(type_, companies_, q, price_min, price_max,
                                 excludes_=excludes_)
    return ["instock=1", "hidden=0", "price>0"] + fwhere, args


def in_stock(type_=None, companies_=None, q=None,
             price_min=None, price_max=None, sort=DEFAULT_SORT,
             page=1, per_page=PAGE_SIZE, excludes_=None):
    """In-stock parts filtered for the /browse view, paginated.

    See `_in_stock_where` for filter semantics. `page` is 1-indexed;
    `per_page` defaults to the page-level constant. Aliases (e.g.
    optics → optic) collapse at the type filter only."""
    where, args = _in_stock_where(type_, companies_, q, price_min, price_max,
                                  excludes_=excludes_)
    order_by = _SORT_CLAUSES.get(sort, _SORT_CLAUSES[DEFAULT_SORT])
    page = max(1, int(page or 1))
    per_page = int(per_page)
    offset = (page - 1) * per_page
    args.extend([per_page, offset])
    cur = get_db().cursor()
    cur.execute(
        "SELECT id, company, type, description, price, url, img, added "
        "FROM parts WHERE " + " AND ".join(where) +
        f" ORDER BY {order_by} LIMIT %s OFFSET %s", args)
    return _sanitize_imgs(cur.fetchall())


def count_in_stock(type_=None, companies_=None, q=None,
                   price_min=None, price_max=None, excludes_=None):
    """Total row count for the same filter set `in_stock` returns.
    Drives the paginator + the "N results" anchor in the chip row."""
    where, args = _in_stock_where(type_, companies_, q, price_min, price_max,
                                  excludes_=excludes_)
    cur = get_db().cursor()
    cur.execute(
        "SELECT COUNT(*) AS n FROM parts WHERE " + " AND ".join(where),
        args)
    return int(cur.fetchone()["n"])


def part_detail(part_id):
    """One product row for the /p/<id> detail page, or None if the id is
    unknown. Selects a touch more than parts_by_ids (adds `added`, the
    first-seen timestamp) for the detail page's facts block. img is run
    through the same safety sanitizer as the listing queries."""
    cur = get_db().cursor()
    cur.execute(
        "SELECT id, company, type, description, price, url, img, instock, added "
        "FROM parts WHERE id=%s", (part_id,))
    row = cur.fetchone()
    if not row:
        return None
    row["img"] = _safe_img_url(row.get("img"))
    return row


def price_history_capped(part_id, limit):
    """For the /p detail page: the most-recent `limit` price_history points
    (ascending) PLUS the true total count and first-seen date — without ever
    materializing more than `limit` rows.

    Returns (points_asc, total_count, first_seen). A pathological id (e.g. the
    empty-url collision sentinel with ~1M rows) is bounded to `limit` for the
    chart while the count/first-seen still report honest totals via a cheap
    aggregate (the (id, updated) index makes COUNT/MIN per-id light for real
    products; the detail route 404s the blank-row sentinel before it ever gets
    here, so the heavy id isn't reached in practice)."""
    cur = get_db().cursor()
    cur.execute("SELECT COUNT(*) AS n, MIN(updated) AS first "
                "FROM price_history WHERE id=%s", (part_id,))
    agg = cur.fetchone()
    total = int(agg["n"] or 0)
    first = agg["first"] if agg else None
    cur.execute("SELECT price, updated FROM price_history WHERE id=%s "
                "ORDER BY updated DESC LIMIT %s", (part_id, int(limit)))
    rows = cur.fetchall()
    rows.reverse()                            # newest-first fetch -> ascending
    points = [{"price": r["price"], "updated": r["updated"]} for r in rows]
    return points, total, first


def parts_by_ids(ids):
    """Live parts rows for a list of parts.id values. Returns a dict
    {id: row}. Powers the working-build view (re-fetches current price
    and in-stock status every render — no stale snapshots in the kit
    while the user is composing)."""
    if not ids:
        return {}
    cur = get_db().cursor()
    placeholders = ",".join(["%s"] * len(ids))
    cur.execute(
        "SELECT id, company, type, description, price, url, img, instock "
        f"FROM parts WHERE id IN ({placeholders})", list(ids))
    return _sanitize_imgs({r["id"]: r for r in cur.fetchall()})


def price_history_for_ids(ids, since_days=None):
    """All price_history points for a list of parts ids. Returns
    {id: [{'price': Decimal, 'updated': datetime}, ...]} sorted by
    `updated` ascending so callers can draw lines and compute deltas
    without re-sorting.

    `since_days`: if set, filters to rows whose `updated` is within
    the last N days. Used by the per-card sparkline path so all
    sparklines share the same time-window (without it, a part with
    10 years of history and one with 1 week look identical in width,
    making cross-card comparison misleading). Leave None when the
    caller really wants the full history (e.g. the /build page's
    full-history Chart.js panel). Server-side filter is index-
    friendly thanks to the (id, updated) composite index."""
    if not ids:
        return {}
    cur = get_db().cursor()
    placeholders = ",".join(["%s"] * len(ids))
    sql = ("SELECT id, price, updated FROM price_history "
           f"WHERE id IN ({placeholders})")
    params = list(ids)
    if since_days is not None:
        sql += " AND updated >= DATE_SUB(NOW(), INTERVAL %s DAY)"
        params.append(int(since_days))
    sql += " ORDER BY id, updated"
    cur.execute(sql, params)
    out = {}
    for r in cur.fetchall():
        out.setdefault(r["id"], []).append(
            {"price": r["price"], "updated": r["updated"]})
    return out


def last_price_for_ids(ids):
    """Most recent previous price per id from `last_price`. Returns
    {id: {'price': Decimal, 'updated': datetime}}. The updated column
    is when the previous price stopped being current — i.e., the
    timestamp of the most recent price change."""
    if not ids:
        return {}
    cur = get_db().cursor()
    placeholders = ",".join(["%s"] * len(ids))
    cur.execute(
        "SELECT id, price, updated FROM last_price "
        f"WHERE id IN ({placeholders})", list(ids))
    return {r["id"]: {"price": r["price"], "updated": r["updated"]}
            for r in cur.fetchall()}


def stockometer():
    """Percent of the renderable catalog currently in stock.

    Sums the `category_count` cache (per-type in_stock/total, materialized
    from `parts` by the scanner). That cache is exactly `SUM(instock=1)`
    over `COUNT(*)` on `parts WHERE hidden=0 AND price>0`, precomputed —
    one filter, clean fraction, "of every row /browse can show, what
    percent is in stock right now" — without scanning parts per request.

    The legacy site (and an earlier ng version) used SUM(scan_count.
    count) as the denominator. That made the headline look healthier
    (~43% vs ~30%) but mixed two data sources and froze stale counts
    behind dead retailers. Memory: [[stockometer-formula-decision]].

    Known caveat: this denominator carries historical rows (products
    from dropped retailers, items long since discontinued). Until the
    deferred DB cleanup runs, the percentage trends lower than the
    real "current supply" picture. Cleanup will lift it naturally
    without a formula change. TODO.md tracks both."""
    cur = get_db().cursor()
    # Sum the per-type `category_count` cache (materialized from parts by
    # the scanner's refresh-category-counts job) instead of scanning parts
    # live on every page render. Identical math to the old live query —
    # SUM(instock=1) over COUNT(*) on parts WHERE hidden=0 AND price>0 —
    # just precomputed. Falls back to the live scan if the cache is empty.
    cur.execute(
        "SELECT SUM(in_stock) AS instk, SUM(total) AS total "
        "FROM category_count")
    r = cur.fetchone()
    if not r or not r["total"]:
        cur.execute(
            "SELECT SUM(instock=1) AS instk, COUNT(*) AS total FROM parts "
            "WHERE hidden=0 AND price>0")
        r = cur.fetchone()
    if not r or not r["total"]:
        return 0.0
    return round(100.0 * int(r["instk"]) / int(r["total"]), 1)


# --- Cross-merchant product matching pool (for the /p/ page's price-compare
# panel). The candidate pool for a product is every in-stock, visible part of
# the SAME type; scoring happens in Python (ngsite.product_match). The pool is
# a few hundred rows per type, so a short module-level TTL cache keyed by type
# lets a burst of product-page views for one type share a single DB fetch. ---
import time as _time

_POOL_CACHE: dict = {}          # {type_code: (fetched_at, rows)}
_POOL_TTL = 120                 # seconds


def same_type_pool(type_code):
    """In-stock, visible parts of `type_code` (id, company, type, description,
    price, url, img) for cross-merchant matching. img is run through the same
    safety sanitizer as the listing queries. TTL-cached ~2 min per type."""
    now = _time.time()
    hit = _POOL_CACHE.get(type_code)
    if hit and now - hit[0] < _POOL_TTL:
        return hit[1]
    with get_db().cursor() as cur:
        cur.execute(
            "SELECT id, company, type, description, price, url, img FROM parts "
            "WHERE type=%s AND instock=1 AND hidden=0", (type_code,))
        rows = cur.fetchall()
    for r in rows:
        r["img"] = _safe_img_url(r.get("img"))
    _POOL_CACHE[type_code] = (now, rows)
    return rows
