/** * Shared SQL building blocks for the recommendation resolution services * (spec § 6.5, § 6.6). These are raw-SQL fragments rather than query-builder * nodes: the § 6.6 visibility policy needs window functions over the * candidate set and the dedup needs representative-key partitioning — * shapes the builder cannot express. Every fragment is either literal SQL or * parameter placeholders; no caller data is ever interpolated. * * The display column set mirrors featuredProducts * (FeaturedProduct.resolvers.js): Product field resolvers fill in * price/image/url from these columns, so recommendation rows are drop-in * `[Product]` items. */ /** SELECT list for display rows. Requires the § JOINS fragment aliases. */ export declare const RECOMMENDATION_DISPLAY_COLUMNS = "\n product.product_id,\n product.uuid,\n product.sku,\n product.price,\n product.variant_group_id,\n product.category_id,\n product.image,\n product_description.product_description_id,\n product_description.name,\n product_description.url_key"; /** Joins the display columns and the stock gate depend on. */ export declare const RECOMMENDATION_DISPLAY_JOINS = "\n LEFT JOIN product_description\n ON product_description.product_description_product_id = product.product_id\n INNER JOIN product_inventory\n ON product_inventory.product_inventory_product_id = product.product_id"; /** * Non-visibility § 6.5 gating: enabled, not excluded, in stock (when the * store hides out-of-stock products). Visibility is deliberately NOT here — * it is the § 6.6 policy, applied over a candidate set via the window * fragments below. */ export declare function baseRecommendationGatingSql(): string; /** * § 6.6 displayability, computed over whatever candidate set S the enclosing * CTE defines (rule matches for related rules; the whole variant group for * FBT display members and manual picks): * * 1. visible products pass; * 2. an invisible member of a group with variant_group.visibility = 't' * passes only when NO member of the group within S is visible AND it is * the MAX(product_id) member within S — mirroring the core carve-out * (ProductCollection.js:64-98) exactly. * * The partition key falls back to -product_id for ungrouped rows: PARTITION * BY a NULL group column would lump every ungrouped product into one * partition and corrupt the bool_or/MAX. * * Requires `LEFT JOIN variant_group ON variant_group.variant_group_id = * product.variant_group_id` in the enclosing CTE. */ export declare const VISIBILITY_POLICY_WINDOW_COLUMNS = "\n product.visibility AS _vis,\n variant_group.visibility AS _grp_vis,\n bool_or(product.visibility)\n OVER (PARTITION BY COALESCE(product.variant_group_id, -product.product_id)) AS _any_vis,\n MAX(product.product_id)\n OVER (PARTITION BY COALESCE(product.variant_group_id, -product.product_id)) AS _max_id"; /** * WHERE predicate over a candidate CTE aliased `s` that selected the window * columns above (`s.`-qualified: the joined product_stat also has a * product_id column). */ export declare const VISIBILITY_POLICY_PREDICATE = "\n (s._vis = TRUE OR (s._grp_vis = TRUE AND s._any_vis = FALSE AND s.product_id = s._max_id))"; /** * Representative-key map (§ 7.2): MIN(product_id) of the current variant * group. Join against a candidate row set aliased `s`; the key expression is * `COALESCE(rep_map.rep, s.product_id)`. */ export declare const REP_MAP_JOIN = "\n LEFT JOIN (\n SELECT variant_group_id, MIN(product_id) AS rep\n FROM product\n WHERE variant_group_id IS NOT NULL\n GROUP BY variant_group_id\n ) rep_map ON rep_map.variant_group_id = s.variant_group_id"; /** * Resolve a product's representative key (§ 7.2) — one index-only scan on * "FK_PRODUCT_VARIANT_GROUP" when grouped. */ export declare function resolveRepresentativeKey(pool: PoolLike, productId: number, variantGroupId: number | null): Promise; /** Minimal pg-compatible pool surface, injectable for the scratch-DB tests. */ export interface PoolLike { query: (text: string, values?: unknown[]) => Promise<{ rows: any[]; rowCount: number | null; }>; } /** * A gated, displayable recommendation row (snake_case, pre-camelCase). * Field-resolver contract: Product.image reads originImage (product_image * join — NOT the legacy product.image column), and Product.inventory * computes isInStock from qty/manageStock/stockAvailability on the parent * row without querying — all of those must therefore be selected here. */ export interface CandidateRow { product_id: number; uuid: string; sku: string; price: string; variant_group_id: number | null; category_id: number | null; origin_image: string | null; qty: number; manage_stock: boolean; stock_availability: boolean; product_description_id: number | null; name: string | null; url_key: string | null; rep_key: number; /** Group-level eligible-order count (product_stat via representative). */ sales_rank: number; /** Which waterfall rung produced the row — set by the resolution drivers. */ source?: string; } /** * Indexable "member of any of these representative keys" predicate. The naive * form — computing COALESCE(MIN(sibling id), product_id) for EVERY product row * and comparing to the key list — is a seq scan with a correlated subquery * per row (~100ms on a 2k catalog, § 13 bench). Resolving the reps' group ids * first turns it into two ANY() probes on the product PK and * "FK_PRODUCT_VARIANT_GROUP". */ export declare function buildRepMembershipPredicate(pool: PoolLike, params: SqlParams, repKeys: number[]): Promise; /** Collects bind values and hands out $n placeholders. */ export declare class SqlParams { private values; add(value: unknown): string; all(): unknown[]; } /** * The one query shape every recommendation source runs through (§ 6.3 * waterfall rules, the manual prefix, both fallback rungs): * * s — candidate set S: source predicate + non-visibility § 6.5 gating, * with the § 6.6 window columns computed over S; * d — § 6.6 displayability + representative key + sales rank, minus * already-excluded representatives (anchor group + earlier picks); * ranked — one displayable row per representative (best-ranked first); * final — display columns, ordered, limited. * * `fromSql` defaults to bare `product`; sources joining extra tables (the * manual prefix joins product_link) extend it and may add ordering columns * (`extraSelect`) referenced by `orderBy`. */ export declare function queryDisplayableCandidates(pool: PoolLike, opts: { params: SqlParams; /** SQL predicate over the aliases in `fromSql`; parameters via `params`. */ sourcePredicate: string; /** Extra SELECT items for CTE `s` (e.g. `pl.sort_order AS _o1`). */ extraSelect?: string; /** FROM clause; must expose a `product` alias. Defaults to `product`. */ fromSql?: string; /** ORDER BY items over CTE `d` columns. Default: sales rank. */ orderBy?: string; /** Representative keys to exclude (anchor group + already picked). */ excludedRepKeys: number[]; limit: number; }): Promise;