j8dZddlZddlmZddlmZddlmZddlm Z ddl m Z m Z mZddlmZej$eZd Zd ed ed efd Zded efdZ d&dedededed ef dZdedefdZdeded efdZdZd'dededed efdZ ddddZ!dZ"d ed efdZ#d eded ed e$fd!Z% d(deded"ed eded#ed efd$Z& d)deded eded#ed ef d%Z'y)*a<Read models behind the pairing endpoints. The pairing job (pairing.py) and the decisions store (pairing_decisions.py) each own their own table and their own writes; this module only reads, and joins the two so the merchant-facing surface can show a pair together with its servability without duplicating that rule. N)sql)RealDictCursor)BLOCKING_FIELDS)get_db_connection) is_servableloadload_for_anchor) PAIR_TYPES)namecategory price_centscurrency image_urlin_stockrowprefixreturnc ^tDcic]}||j|d|c}Scc}w)N_) CARD_COLUMNSpop)rrcolumns ;/var/www/html/strategist-ai/app/services/pairing/queries.py_cardrs/@L MfFCGGvhax01 1 MM Ms* tenant_idct} |jt5}|jt j dj t j||jDcgc] }t|c}cddd|jScc}w#1swYnxYw |jy#|jwxYw)Ncursor_factoryzeSELECT category, COUNT(*) AS products FROM {}.strategist_products GROUP BY category ORDER BY category) rcursorrexecuterSQLformat Identifierfetchalldictclose)rconncurrs rlist_categoriesr*s  D  [[[ 7 53 KK6fS^^I./  1 &)\\^4DG4  5 5 5  5 5 5  s5C AB.;B) B. C )B..B73C Cr limitoffsetct} |jt5}|rtjdntjd}|r|gng}|j tjdj tj||||jd}|j tjdj tj|||||gz|jD cgc] } t| } } ddd|j dScc} w#1swY#xYw#|jwxYw)NrzWHERE category = %sz7SELECT COUNT(*) AS total FROM {}.strategist_products {}totalzSELECT product_key, name, category, brand, price_cents, currency, in_stock, image_url FROM {}.strategist_products {} ORDER BY product_key LIMIT %s OFFSET %s)productsr/) rrrrr!r r"r#fetchoner$r%r&) rr r+r,r'r(whereparamsr/r)r0s r list_productsr4-s#  D [[[ 7 936>CGG12CGGBKE#+hZF KKIfS^^I.6 @LLN7+E KK:fS^^I.6%8P  R *-8AQ8H8 9  5 119 9 9 s5EC.EE"E$EEE EE$ product_keyct} |jt5}|jt j dj t j||f|j}|r t|ndcddd|jS#1swYnxYw |jy#|jwxYw)Nrz;SELECT * FROM {}.strategist_products WHERE product_key = %s) rrrr rr!r"r#r1r%r&)rr5r'r(rs r get_productr7Es  D [[[ 7 .3 KKMfS^^I./+ A,,.C #49  . .  . . .  s#CA'B# C#B,(CCc t} |jt5}|jt j dj t j|t j||f|jDcgc] }t|}}ddd|jt|}tDcic]}|g}}D]a} t| d| d<|j| d| d| df} t| | | d<|j!| dgj#| c|Scc}w#1swYxYw#|jwxYwcc}w) a Every neighbor of one anchor, grouped by type, each carrying `servable`. Servability is recomputed here rather than trusted from the job's report: a decision recorded after the last run must be reflected immediately, not only after the next re-pair. ra SELECT n.anchor_key, n.neighbor_key, n.pair_type, n.score, n.confidence, n.source, n.reasons, n.computed_at, p.name AS neighbor_name, p.category AS neighbor_category, p.price_cents AS neighbor_price_cents, p.currency AS neighbor_currency, p.image_url AS neighbor_image_url, p.in_stock AS neighbor_in_stock FROM {}.strategist_product_neighbors n JOIN {}.strategist_products p ON p.product_key = n.neighbor_key WHERE n.anchor_key = %s ORDER BY n.score DESC Nneighbor anchor_key neighbor_key pair_typeservable)rrrr rr!r"r#r$r%r&load_decisionsr rgetr setdefaultappend) rr5r'r(r)rows decisionstgroupedrdecisions r pairings_forrGRsU  D [[[ 7 53 KK ! y13>>)3LM &)\\^4DG4D4 5" y)I()q"u)G)=Z0J==  N 3S5E FH%c84J3{+R077< = N5 5 5" *s;EA/E E#E %E E*E  EEE')similar complementc t} |jt5}|jt j dj t j|t j||ttttf|jDcgc] }t|}}t|||}ddd|jg}D]Y} | d| dd} j| d| d| d f} t!| | s8|j#| t%||k\sX|S|Scc}w#1swYxYw#|jwxYw) zWhole product rows a shopper may be shown for this anchor. Stock and missing_fields are filtered HERE rather than trusted from pairing time, because a product can sell out an hour after the graph was built. ra8 SELECT p.*, n.pair_type, n.score AS pair_score, n.confidence AS pair_confidence, n.source AS pair_source, n.neighbor_key, n.anchor_key FROM {}.strategist_product_neighbors n JOIN {}.strategist_products p ON p.product_key = n.neighbor_key WHERE n.anchor_key = %s AND n.pair_type = ANY(%s) AND p.in_stock -- Only a blocking gap hides a product from shoppers. A -- missing reference price is not one: it affects -- cross-currency comparison, not whether the card can be -- shown or clicked. AND NOT (p.missing_fields && %s) ORDER BY n.score DESC, n.neighbor_key Npair_confidence pair_source) confidencesourcer:r;r<)rrrr rr!r"r#listSERVED_PAIR_TYPESrr$r%load_decisions_for_anchorr&r?rrAlen) rr5r+r'r(r)rBrCr=rpairrFs rservable_neighboursrTs\  D [[[ 7 P3 KK!y13>>)3LMd#45tO7LM! O"&)\\^4DG4D4 2)[$OI/ P2 H!"34M@RS==  N 3S5E FH4*  x=E !  O O'5% P P2 s5EB E -E?E EE  EEE+)皙?g)\(?)333333?rU)rV)highmediumlow)pendingapprovedrejectedautoallc:|dk(ry|dk(ryt|drdSdS)z-What a merchant would call this pair's state.r\r]Nr^r[)r)rrFs r _state_ofras.::!d+6::statestatusc|dk7r||k7ry|r=|jdxs$|jdxsijd}||k7ry|rt|\}}||dcxkr|ksyyy)Nr_Fanchor_categoryanchorr scoreT)r? SCORE_BANDS)rrcrdr rhrfrZrXs r_matchesrjs 5F?77#45H"wwx06B;;JG  h & & Tc'l)T)* rbr:rhc |tvrtd|dt|r&|tvrtd|dttt } |j t 5}|jtjdjtj||jd}|jtjdjtj|tj|tj||jD cgc] } t| } } ddd|jt! z } t#|} g} | D]}| j%|d |d |d f}t'||}|r |d |k7r7t)|||||sG||d <||d <t+|d|d<t+|d|d<| j-|t!| |k\sn| | dScc} w#1swYxYw#|jwxYw)aMPairs, filtered the way a review screen wants them. One endpoint rather than one per view: a merchant working through matches wants to see what is waiting, then what they approved, then only the strong ones in a category, and swapping endpoints for each of those would push the same filtering into every client. unknown status: ; expected one of unknown score band: rz=SELECT COUNT(*) AS total FROM {}.strategist_product_neighborsr/a SELECT n.anchor_key, n.neighbor_key, n.pair_type, n.score, n.confidence, n.source, n.reasons, n.computed_at, a.name AS anchor_name, a.category AS anchor_category, a.price_cents AS anchor_price_cents, a.currency AS anchor_currency, a.image_url AS anchor_image_url, a.in_stock AS anchor_in_stock, p.name AS neighbor_name, p.category AS neighbor_category, p.price_cents AS neighbor_price_cents, p.currency AS neighbor_currency, p.image_url AS neighbor_image_url, p.in_stock AS neighbor_in_stock FROM {}.strategist_product_neighbors n JOIN {}.strategist_products a ON a.product_key = n.anchor_key JOIN {}.strategist_products p ON p.product_key = n.neighbor_key ORDER BY n.score DESC Nr:r;r<rcrFrgr9)pairingsdropped)DECISION_STATES ValueErrorrituplerrrr rr!r"r#r1r$r%r&rRr>r?rarjrrA)rr+r:rdr rhr'r(r/r)rBrprCmatchedrrFrcs r list_pairingsrus(_$+F83EoEVWXX k)/w6H{I[H\]^^  D [[[ 7 53 KKOfS^^I./ 1LLN7+E KK!"y13>>)3Ly13# 4&&)\\^4DG4D43 56 c$iGy)IG==  N 3S5E FH#x( #l+z9 UFHe< G "Jc8,H Z0Js w<5   G 44553 5 56 s7H2'CH&H!H&H2!H&&H/+H22IcZ|tvrtd|dt|r&|tvrtd|dttt |ddd}i}|D]}|j |d|d|d d d d d d d d gd }||d xxdz cc<t ||d |||sL|dxxdz cc<t|d|d|d<|dj|dg} |jD]N}|ds |jd} tt| t| z d|d<| j|P| jd| d|} | D cgc] } | d} |  }} } | t| |r#tt|t|z ddSd dScc} } w)a}Products with matches, and how many of each state they hold. A merchant thinks in products, not in pairs: "this dress has 4 to review" is reviewable, a flat list of 229 pairs from 80 different products is not. Every anchor carries the full breakdown, not just the filtered count, so a screen can show "4 pending, 2 approved" without asking again per product. rlrmrni'r_)r+rdror:rgrrW) r:rgmatchingr[r\r]r^ top_score avg_score_scoresrcrwrxrhrzryc|d |dfS)Nrxr:)as rzlist_anchors..Ns;<Arb)keyN)anchorsr/ry)rqrrrirsrur@rjmaxrAvaluesrroundsumrRsort)rr+rdr rh everythingrErentryrscoresshownrs all_scoress r list_anchorsrs_$+F83EoEVWXX k)/w6H{I[H\]^^yuEjQJG2""3|#4l+(maQ7   c'lq  CWvx ? *  " !$U;%7W!FE+  )  # #CL 12 G!Z 9%"3v;V#s"F')N2r))dNr[NN)rr[NN)(__doc__loggingpsycopg2rpsycopg2.extrasrapp.services.catalog.normaliserapp.services.infra.databaserapp.services.pairing.decisionsrrr>r rQapp.services.pairing.rulesr getLogger__name__loggerrr%strrrOr*intr4r7rGrPrTrirqraboolrjrurr~rbrrs*:92   8 $ NtNSNTN s t FH!"2S2C2s22'+20 3 S $C$c$d$T.-3-S--T-f   E;4;c;$sCT$GK;?#D5SD5D5D5D558D5D5(,D5NAJ486C66#66.16=A6rb