qujo> 4dZddlZddlZddlmZmZmZmZddlm Z ddl m Z m Z ddl mZddlmZddlmZmZmZdd lmZdd lmZmZej4eZhd Zd Zd Zde de fdZ!de de"fdZ#de de$de$de"de%f dZ&de de de'de(de$f dZ)de de de%fdZ*de de$de$fdZ+d2de de de(de$fdZ,de d e$de$fd!Z-d3de d"e de(de$fd#Z.d3de d"e de(de$fd$Z/d3de d%e%de(de$fd&Z0d'e$d(e$de1fd)Z2d4d*e$de d+e de$fd,Z3d5de de(d+e de(fd-Z4de d.e d/e$ddfd0Z5d%e%de%fd1Z6y)6zStoring and querying the tenant's products. No parsing here -- that is product_extraction. This module owns the table, the URL matching rule, and the shape products take when they leave the backend. N) parse_qsl urlencodeurlparse urlunparse)sql)JsonRealDictCursor)settings)client)get_db_connectioninsert_llm_usagesearch_vector_data)get_embeddings) check_quotaestimate_text_tokens> refgclidfbclidmc_cidmc_eidutm_idmsclkidreferrerutm_term utm_medium utm_source utm_content utm_campaign@iurlreturnc t|xsdj}|syt|}t|jdDcgc]\}}|j t vr||f!}}}|jjdxsd}t|j|j|dt|dfScc}}w)aMake two spellings of the same page match. Strips the fragment, a trailing slash, and known tracking parameters. Keeps every other query parameter: plenty of sites identify the product there (?product_id=123), and dropping the query wholesale would collapse a whole catalogue onto one URL. Tolerant of non-string input (e.g. None): callers that pass a bad value get "" back rather than an AttributeError, so lookups return empty rather than raise. T)keep_blank_values/) strstriprrquerylower_TRACKING_PARAMSpathrstriprschemenetlocr)r partskvr(r+s 4/var/www/html/strategist-ai/app/services/products.py normalise_urlr3#s ciR.   C  SME )%++ N 31 !11V 3E 3 ::  S ! (SD u||U\\4Yu=MrR SS 3s$B: tenant_idc|j5}|jd|f|jdcdddS#1swYyxYw)aWhether the tenant has a products table. Opens its own plain cursor rather than taking the caller's, because callers use different cursor factories (`save_products` and `save_related` use a plain cursor, `_query` uses a `RealDictCursor`) and a row read as `row[0]` breaks under a dict cursor while `row["exists"]` breaks under a plain one. A private cursor keeps this function correct under either. ztSELECT EXISTS (SELECT FROM information_schema.tables WHERE table_schema = %s AND table_name = 'strategist_products')rN)cursorexecutefetchone)connr4curs r2 _table_existsr;9sK !#  N L ||~a !!!s &AA products crawled_urlscrawl_completecdx}}t} |j5}t||s7tj d|ddddcddd|j S|D]}|j tjdjtj||d|d|jd |jd t|d |jd t|jd xsgt|jdxsgt|jdxsif |dz }|rm|rk|j tjdjtj||D cgc] } t| c} f|j}ddd|jtj!d|d|d|d|d ||d|j Scc} w#1swYUxYw#t"$rL} |j%tj'd|d| ddddcYd} ~ |j Sd} ~ wwxYw#|j wxYw)aUpsert products, and delete stale ones only when it is safe to. A crawl that failed halfway, or that was truncated by max_pages, would otherwise delete products that are still on sale. When `crawl_complete` is false, this writes and updates but deletes nothing. rzNo products table for z; skipping save.)saveddeletedNa@ INSERT INTO {}.strategist_products (product_key, name, description, image_url, product_url, category, ctas, options, raw, extracted_at) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s, CURRENT_TIMESTAMP) ON CONFLICT (product_key) DO UPDATE SET name = EXCLUDED.name, description = EXCLUDED.description, image_url = EXCLUDED.image_url, product_url = EXCLUDED.product_url, category = EXCLUDED.category, ctas = EXCLUDED.ctas, options = EXCLUDED.options, raw = EXCLUDED.raw, extracted_at = CURRENT_TIMESTAMP product_keyname description image_url product_urlcategoryctasoptionsrawz?DELETE FROM {}.strategist_products WHERE product_url <> ALL(%s)z Products for : z saved, z deleted (crawl_complete=)zCould not save products for Texc_info)r r6r;loggerwarningcloser7rSQLformat Identifiergetr3rrowcountcommitinfo Exceptionrollbackerror) r4r<r=r>r@rAr9r:productuexs r2 save_productsr`KsREG  D- [[]" 'c y1!7 {BRST!"q1" '" 'X O$  CGG%VCNN956M*GFOW[[=WKK ,mGMC" '" 'N *  3I;bEPT Uq)) * seH"*H H"$D8HH .H>H"HHH"" I7+1I2I7I:2I77I::J whereparamslimitct} |jt5}t||sgcddd|j S|j t jd|zdzjt j|||fz|jDcgc] }t|c}cddd|j Scc}w#1swYnxYwnE#t$r9}tjd|d|dgcYd}~|j Sd}~wwxYw |j y#|j wxYw)Ncursor_factoryz+SELECT * FROM {}.strategist_products WHERE z$ ORDER BY extracted_at DESC LIMIT %szProduct query failed for rLTrN)r r6r r;rRr7rrSrTrUfetchalldictrZrPr\)r4rarbrcr9r:rr_s r2_queryrjs*  D  [[[ 7 53 y1 5 5  KK=E67fS^^I./5(1B D&)\\^4DG4 5 5 5 5 5 5  0 2bTBT R   5  sjC-C  C- A#C -C?C  C-C  C)%C-,E- D/6D*D/E*D//EEcDt|dt|fd}|r|dSiS)Nzproduct_url = %srKr)rjr3)r4r rowss r2 find_by_urlrms, )/-2D1F JD47""urlsc r|sgSt|d|Dcgc] }t|c}ft|Scc}w)Nzproduct_url = ANY(%s))rjr3len)r4ror^s r2 find_by_urlsrrs=  )4.23M!$35s4y BB3s4 rGcBd\}}|r |dz }||fz }t||||S)N)TRUEz AND lower(category) = lower(%s))rj)r4rGrcrarbs r2 list_productsrvs6ME6 338+ )UFE 22rnkeyscL|sgSt|dt|ft|S)Nzproduct_key = ANY(%s))rjlistrq)r4rws r2 find_by_keysrzs&  )4tDzmSY OOrnr(c<|xsdj}|sgS t||}t||d}gt }}|xsgD]]}t|jd xsijd xsd} | s7| |vs<|j| |j| _t||D cic]} | d |  } } |D cgc] } | | vs| | c} d |S#t$r)}tj d|d|dgcYd }~Sd }~wwxYwcc} wcc} w) aProducts matching what the visitor actually meant. The product pages are already chunked and embedded in the knowledge base, so the search happens there and the winning chunks' source_url is mapped back onto products. This is a semantic match: "something light for jogging" finds a running shoe whose description uses neither word, where a category or keyword filter would return nothing. r#)r4 )rczProduct search failed for rLTrNNmetadata source_urlrF) r'rrrZrPr\setr3rVaddappendrr) r4r(rc embeddinghitsr_roseenhitr rirlr^s r2search_productsrs*[b   !E  "5I> !)YbA SU$DzrSWWZ06B;;LIORP 3d? HHSM KK   *6i)F GAAm a  GD G! /Q$YDG / 77  1)BrdCd S  H /s/C5 D DD D(D D Dc |xsdj}|sgSt} |jt5}t ||sgcddd|j Sd|d}|j tjdjtj|||||||f|jDcgc] }t|c}cddd|j Scc}w#1swYnxYwnE#t$r9}tjd|d|d gcYd}~|j Sd}~wwxYw |j y#|j wxYw) aProducts whose text plainly contains the visitor's search term. A single ILIKE query against name, description and category, ordered so a name match outranks a description match and a description match outranks a category match. No embedding call and no quota check: unlike `search_products`, this is meant to run on every page view of every visitor, so it has to be a deterministic database lookup rather than a model round-trip. Never raises -- `_query` already returns [] on failure. r#reN%a SELECT *, CASE WHEN name ILIKE %s THEN 0 WHEN description ILIKE %s THEN 1 ELSE 2 END AS _rank FROM {}.strategist_products WHERE name ILIKE %s OR description ILIKE %s OR category ILIKE %s ORDER BY _rank, extracted_at DESC LIMIT %s zProduct text match failed for rLTrN)r'r r6r r;rRr7rrSrTrUrgrhrZrPr\)r4r(rcr9r:likerir_s r2match_productsrsO[b   !E   D [[[ 7 53 y1 5 5* %ugQz_cosine..s *1a!e *sc3&K|] }||z ywrru)rrs r2rz_cosine.. qQU g?c3&K|] }||z ywrru)rrs r2rz_cosine..rrg)sumzip)rrdotnanbs r2_cosinersY *Aq * *C A  # %B A  # %B R3"r'?0S0rntextsuser_idc g} tdt|tD]}|||tz}|r!td|D}t |d||t j j|tjd}|j}tjdt|d|jd |j|r2t|d tj|jd|j|j!d |j"D|S#t$$r)} tj'd |d | dgcYd} ~ Sd} ~ wwxYw)aEmbed many texts in as few API calls as possible, in input order. One call per _EMBED_BATCH_SIZE texts instead of one call per text, so computing relatedness for a real catalogue is dozens of round-trips rather than thousands. Usage is logged once per batch, the same way `get_embeddings` logs once per call. Never raises: any failure returns [] and the caller treats that as "skip relatedness this run". rc32K|]}t|ywr)r)rts r2rz_embed_batch..sGA 4Q 7Gs ai_tokens)rrequested_amountg>@)inputmodeltimeoutzLLM Usage (Embedding batch of z ): Prompt: z , Total: Embeddingc34K|]}|jywr)r)ritems r2rz_embed_batch..%sDd4>>DszBatch embedding failed for rLTrNN)rangerq_EMBED_BATCH_SIZErrr embeddingscreater EMBED_MODELusagerPrY prompt_tokens total_tokensr extenddatarZr\) rr4rvectorsstartbatch requestedresponserr_s r2 _embed_batchr sKG1c%j*;< EE%(9 9:EGGG I{GV_`((//8#7#70GHNNE KK8U E##(#6#6"7yASAS@TV W K9M9M!&!4!4a9K9KM NNDhmmD D E  29+RtDt T sDD"" E+E EEc  t|ddd t dkryt tkDr=t tz } dt tj |d|dtd  Dcgc]-}|d d |j d xsd j /}}t||| t t k7rtjd|yd}t D]Z\ }t fdtt Dd}t||d|d|D cgc]\}} |  c} }|dz }\tjd|d||Scc}wcc} }w)aWork out each product's nearest neighbours, once, at ingestion. Category matching is a reflex: it cannot tell that a running shoe goes with running socks but not a running-themed poster, and it fails outright on the many products that carry no category at all. Comparing what the products actually say does better. Doing it here rather than per request is what lets the proactive endpoint stay a fast lookup while still being a real recommendation. Never raises. Capped at MAX_RELATED_PRODUCTS (the newest rows -- `_query` already orders by extracted_at DESC): a catalogue larger than that would otherwise turn a single ingestion request into thousands of sequential embedding calls and an O(n^2) comparison. Skipping the rest is logged, not silent. rtrui'rNz has z products beyond the z(-product relatedness cap; skipping them.rCz. rDr#)r4rzCould not embed products for c3\K|]#}|k7rt||df%yw)rBN)r)rjirlrs r2rz&compute_relatedness..Os< 2!q&gaj'!*-tAw}/E F 2s),T)reverserBrKzComputed relatedness for z products in )rjrqMAX_RELATED_PRODUCTSrPrQrVr'rr\ enumeratesortedr save_relatedrY) r4rcrskippedrrupdatedscored_r0rrlrs @@@r2compute_relatednessr,s )VR /D 4y1} 4y''d)22))*)E'2G.//WY ZRV V#F }Bsww}5;< = C C E VE V5IwGG 7|s4y  4YK@AGD/3 2CI& 2 YM 26&5>4R41aQ4RS1   KK+G9M)MN N! W5Ss 42E-5 E2rBrelatedc@t} |j5}t||s ddd|jy|j t j djt j|t||fddd|j|jy#1swY*xYw#t$r5}|jtjd|d|Yd}~Vd}~wwxYw#|jwxYw)z3Store a product's nearest neighbours. Never raises.NzJUPDATE {}.strategist_products SET related_keys = %s WHERE product_key = %sz$Could not save related products for rL)r r6r;rRr7rrSrTrUryrXrZr[rPr\)r4rBrr9r:r_s r2rrYs  D  [[] Oc y1 O  KK\fS^^I./$w-1M O O  O O Q  ;K=2$OPPQ sLC B>C AB>C >CC D+D>D DD Dc|jdxsg}t|trtj|}|jdxsg}t|trtj|}|jd|jd|jd|jd|jd||dS) aMThe shape a product takes when it leaves the backend. Carries no price, currency or stock level: those go stale fastest between crawls, and a wrong price on a card shown to a customer is the worst thing this feature could do. The CTA takes the visitor to the page, where those numbers are correct by definition. rHrIrBrCrDrErF) product_idrCrDrEr rHrI)rV isinstancer&jsonloads)rrHrIs r2to_cardrks 776? bD$zz$ggi &BG'3**W%ggm,ww}-WW[)ww}% rn)N)r)NN)N)7__doc__rlogging urllib.parserrrrpsycopg2rpsycopg2.extrasrr app.core.configr app.core.llm_clientr app.services.databaser r rapp.services.embeddingrapp.services.quotasrr getLogger__name__rPr*rrr&r3boolr;ryrhr`tupleintrjrmrrrvrzrrrfloatrrrrrrurnr2rsD  CC0$&YY1A   8 $;  TsTsT,!3!4!$7S7D77"&7+/7tc#uST$#3#S#T# BCBtBB3S3C3s343PCPtPP 8s838s848>$c$#$c$$$N 2 2$ 2s 24 21t111cT@*3*s**PS*ZCcDT$$rn