o $jU@sddlZddlZddlZddlZddlZddlmZmZddlZddlm Z ddl m Z ddl mZmZddlmZddlmZmZdd lmZeeZd Zd Zd Zd ZdZdZdZ dZ!dddZ"ddZ#ddZ$ddZ%e j&ddddZ'dS) N)datetimetimezone) celery_app)DB_PATH) query_xai_apiSTRATEGIES_FILE)get_offer_count_trend_from_flat)CURRENT_PRICING_PREDICATESTALE_PRICING_PREDICATE)get_db_connectioni,2iig?ic Cszttddd }t|}Wdn1swYWnty9}ztd|WYd}~dSd}~wwhd}d}|D]}||ddd |d dd 7}qBt }d |vre| d d d|D} |D]} g| | <qn|D]3} t | t sqw| dd} | dd} | |vr| dkr| |  | qw| |vr| dkr| |  | qwg} |D]#} | | d|D]} | d| d| ddd| ddqq|D]#} | | d|D]} | d| d| ddd| ddqqd| S)a Implements tiered strategy injection (Pass 2 payload reduction). - Takes only 'High' confidence strategies from core categories, capped at max_per_core_category. - Dynamically adds specific category strategies if they match candidate keywords. rzutf-8)encodingNzError reading strategies: >RiskBuyingGeneralPricingTitle Detailed_Seasonalitytextbook SeasonalitycSsi|]}|gqSr).0catrr7/var/www/agentarbitrage/keepa_deals/prime_picks_task.py 4sz)get_tiered_strategies..categoryr confidenceHighz - [Category: z] IF triggerzN/Az THEN advice )openrjsonload Exceptionloggererrorgetlowersetadd isinstancedictappendjoin) candidatesmax_per_core_categoryf strategiesecore_categoriescandidate_textcdynamic_categories categorizedrsconf formattedrrr get_tiered_strategiessP *     .. rCcCs|dtd|jS)a0Drop cached picks whose deals row no longer carries current pricing. Pass 1 decides what may ENTER the cache. This decides what may STAY in it, and it has to exist because two paths through `generate_prime_picks` return without touching the cache at all, by design (AGENTS.md 7.10, "Graceful Fallback"): Pass 1 finding no eligible candidates, and Pass 2 failing or returning nothing. A pick selected under superseded pricing logic survives every one of those runs, so without this it stays on the dashboard until a run happens to succeed - which is how ASIN 1418548839 (`Pricing_Logic_Version` NULL) was still showing as pick #4 on 2026-09-22, a day after the repair sweep had skipped it. It runs on EVERY invocation, before Pass 1, rather than only when a pick is chosen, because staleness is not a property a row acquires on its own: bumping `PRICING_LOGIC_VERSION` re-stales the entire table at once, and that bump is the documented mechanism for scheduling the next repair sweep (AGENTS.md 7.13). A pick whose deals row has been deleted outright is left alone - the Agent's Choice INNER JOIN in `/api/deals` already makes it unshowable, and reaping it is the Janitor's business, not this function's. Returns the number of cached picks removed. z DELETE FROM prime_picks WHERE EXISTS ( SELECT 1 FROM deals WHERE deals."ASIN" = prime_picks.asin AND z ) )executer rowcount)cursorrrr prune_stale_priced_picksVs rGc Cs|sdSz$t|dd}t}|jr|jdd}||}td|dWStyUzt|d}t}||}td|dWYStyTYYdSww)NrZz+00:00)tzinfog @z%m/%d/%y %H:%M) r fromisoformatreplaceutcnowrImax total_seconds ValueErrorstrptime)date_strdtnowdeltarrr get_hours_sinceys&    rUcCs6|r|dkrdStdt|}|rt|dSdS)N-rz(\d+)r)researchstrintgroup) offers_strmrrr parse_offerss r^z1keepa_deals.prime_picks_task.generate_prime_picks)namec?s tdzbttf}tj|_|}t|}|r&td|dt dd}d}d}d|d |d }d |d t d |d|d t d |dt dt d|d|dtdt d}||}dd|D} Wdn1stwYtdt| d| stdWdSg} d} d} d} | D]+}t|dddddd}t|d ddddd}zt|}t|}|dkr||d!nd}Wn tyd}d}Ynwt|d"p|d#}|d$}|dus|dkrd%}nz tt|dd}Wn tyd%}Ynwt|d&}d'|d(d)}|d*td+|d,}||d+|td-|}d.}t|d/d}|d0krk|tkrktd1|d2d3|d4|d5| d*7} d6}t|}|s|d7kr|tkrtd1|d2d8|d9| d*7} d6}n"|d:vrt }n|durd;}| d*7} td<|d2d=nd;}|s||}||d><| !|qtd?| d@t| dA| dB| | j"dCdDd6dE| ddF}tdGt|dHt#|}g} |D].}!| !|!d2|!dIddd!|!d$|!d&|!dJ|!d|!d/|!dKdLqdM|dNt$j%| dOdPdQ}"dRdSdTdU|"dTgdVd.dWdX}#ddl&}$t|"}%tdY|%dZ|$&}&t'|#}'|$&|&}(td[|(d\d]gd.})|'r{d^|'vrt(d_|'d^d`d6})n$da|'vr|'dar|'daddbi}*|*dcd}+tdd|+t)j*de|+t)j+df},|,r|,,d*}-n%|+}-|--dg}.|-.dh}/|.dikr|/dikr|/|.kr|-|.|/d*}-zt$/|-}0i}1gt0|0t1rp|0djg}2|0dkg}3t0|2t2r+t|2dkr+t0|2dtr+dld|2DD]}4dm|1|4<q"n&|2D]#}5t0|5t1rOdn|5vrOt|5dn}4!|4t|5dod|1|4<q-|3D]}5t0|5t1rndn|5vrnt|5dod|1t|5dn<qS| D]#}6t|6d2}4|4v}7|1|4dp}8tdq|4dr|7ds|8dtqrWnt$j3yt(du|+d6})Ynwd6})|)rtdvWdSfdwd|D}9|9stdxWdStdyt|9dztt45}:t67t8j9:};g}d|;|:fqttO}|}|d|z |d}|z|dt(d|>d}>~>wwWdWdS1sawYWdSt=y}>ztj(d|>d6dWYd}>~>dSd}>~>ww)z Executes the async Two-Pass Prime Picks pipeline. Pass 1: Smart Floor SQL/math filtering & scoring Pass 2: xAI Mastermind batched evaluation z#Starting Async Prime Picks PipelinezEvicted z: cached Prime Pick(s) whose pricing is no longer current (z).z:CAST(REPLACE(REPLACE("Profit", '$', ''), ',', '') AS REAL)z?CAST(REPLACE(REPLACE("All_in_Cost", '$', ''), ',', '') AS REAL)z;CAST(REPLACE(REPLACE("List_at", '$', ''), ',', '') AS REAL)z((z * 1.0 / z) * 100)z; SELECT * FROM deals WHERE z >= z AND z > 0 AND z <= zE AND CAST(REPLACE("Deal_Trust", '%', '') AS REAL) >= z? AND "List_at" IS NOT NULL AND z AND "1yr_Avg" IS NOT NULL AND "1yr_Avg" NOT IN ('-', 'N/A', '', '0', '0.00', '$0.00') AND "1yr_Avg" != 0 AND z cSg|]}t|qSr)r3)rrowrrr z(generate_prime_picks..NzPass 1: Found z! candidates matching Smart Floor.zNo candidates found. Exiting.rProfit0$r, All_in_Costd last_seen_utc Deal_foundSales_Rank_Currenti@BOffersg>Arg?g{Gz?gMbP?Frz year-roundz[Pass 1 Filter] Dropped ASIN=ASINz (seasonality=z , sales_rank=u) — year-round + high rankTrisingz (trend=rising, sales_rank=u#) — rising offers + weak velocity)fallingflatg?z)[Pass 1 Filter] No trend signal for ASIN=u% — passing through with no modifier_scorez+[Pass 1 Filter] Offer-trend filter dropped z of z candidates; z3 had no trend signal; Year-round high rank dropped cSs |ddS)Nrtr)r.)xrrr s z&generate_prime_picks..)keyreverserz Pass 1: Top z( candidates selected for xAI evaluation.rSales_Rank_Drops_last_180_days Percent_Down)rprrlrmryrdrrzat You are the xAI Mastermind evaluating the top candidate deals. **Evaluation Strategy:** You MUST evaluate candidates holistically against ALL strategies present in the provided text rules. However, recognize that not every deal will be a "perfect" match for every strategy (e.g. not everything needs to be seasonal). Strategies: z" **Candidates:** )indentu **Instructions:** Evaluate the items (ASINs) and determine if they represent solid arbitrage opportunities based on the strategies. Filter out any deals that violate key risk management rules or are obviously poor choices, but allow good standard deals to pass. You MUST return a JSON object containing two keys: "selected" and "rejected". The "selected" key should contain a list of objects with the ASIN and a brief 1-sentence reason. The "rejected" key should contain a list of objects with the ASIN and a brief 1-sentence reason. Example: { "selected": [{"asin": "0123456789", "reason": "High ROI and stable BSR trend."}], "rejected": [{"asin": "B01ABCD123", "reason": "Sales rank is too erratic indicating high risk."}] } You MUST return ONLY the JSON object. No markdown formatting, no other explanations. IMPORTANT — TEXTBOOK COUNTERFEIT RISK CORRECTION: Some strategies you have been given may suggest categorical rejection of textbooks (medical, engineering, AP, college subjects, etc.) due to counterfeit risk. This is overcautious and incorrect. Do NOT reject textbook candidates based on category alone or subject area alone. Counterfeit risk is genuinely HIGH only when ALL FOUR of the following conditions are met simultaneously: 1. Sales Rank is under 100,000 (high current demand) 2. Price is significantly below historical market average 3. Condition is New or Like-New 4. Seller has limited feedback or appears to be moving multiple new-condition copies at suspiciously low prices One, two, or three of these factors alone is NOT a counterfeit signal. Only all four together is the red flag pattern. Books with Sales Rank above 500,000 are very unlikely to be counterfeit regardless of category. Older editions of textbooks are also unlikely to be counterfeit. Apply the four-factor test specifically — do not reject on category. This correction takes priority over any general strategies suggesting textbook avoidance. IMPORTANT — SEASONAL HIGH-RANK CORRECTION: Some candidates will have a high current sales rank (over 1,000,000) but be flagged in `Detailed_Seasonality` as a seasonal title (e.g., "Medical School", "Christmas", "Summer Reading", "Spring Gardening", or any season-specific category). Do NOT reject seasonal candidates solely based on current high sales rank. A book that ranks 1.5M in the off-season may rank 200K-400K during its season, with strong ROI for buyers willing to hold inventory. The ROI on seasonal flips often exceeds high-velocity flips because the off-season buy price is depressed. The seller plans inventory by holding books at their own prep warehouse and shipping to Amazon 2-3 months before the selling season to minimize FBA aged-inventory surcharges (which begin at 181 days and escalate steeply at 271+ days). Apply the high-rank rejection rule strictly only when the candidate is classified as "Year-round" or has no clear seasonal classification. For seasonal candidates, prioritize offer-trend stability and discount depth over current rank. This correction takes priority over general velocity-based rejection rules. systemz'You are a precise JSON-only output bot.)rolecontentuserzgrok-4-fast-reasoningg?)messagesmodelstream temperaturez'Pass 2: Querying xAI API (prompt size: z chars)...zPass 2: xAI API call took z.2fz seconds.r-zxAI API returned an error: z Unknown ErrorchoicesmessagerzPass 2 Raw Response: z```(?:json)?\s*(.*?)\s*```)flags{}selectedrejectedcSr`r)rY)rrurrr rbrcz(No reason provided (legacy array format)asinreasonzSee raw responsez[Pass 2 Reasoning] ASIN=z Selected=z Reason=""z$Failed to parse xAI output as JSON: zIPass 2 failed. Preserving previous valid run instead of overriding cache.cs"g|] }t|dvr|qS)rp)rYr.)rdselected_asinsrr rbs"z:Pass 2 returned empty list. Preserving previous valid run.zPass 2 complete. Selected z Prime Picks.)startzBEGIN TRANSACTIONzDELETE FROM prime_picksz INSERT INTO prime_picks (asin, rank, score, generated_at, run_id) VALUES (?, ?, ?, ?, ?) COMMITzSuccessfully saved z" Prime Picks to database (run_id: ROLLBACKz"Failed to save Prime Picks to DB: z$Error in generate_prime_picks task: )exc_info)>r,infor rsqlite3Row row_factoryrFrGr PASS_1_MIN_PROFITPASS_1_MIN_ROIPASS_1_MAX_ROIPASS_1_MIN_DEAL_TRUSTPASS_1_MAX_LIST_ATrDfetchalllenrYr.rKfloatrOrUr^minrMr/stripPASS_1_YEAR_ROUND_VELOCITY_CAPr PASS_1_OFFER_TREND_VELOCITY_GATE#PASS_1_OFFER_TREND_BONUS_MULTIPLIERr4sortrCr)dumpstimerr-rWrXDOTALLr[findrfindloadsr2r3listJSONDecodeErroruuiduuid4rrSrutc isoformat enumerate executemanyr+)?connrFprunedsanitized_profitsanitized_costsanitized_list_atroi_exprquery deal_rows deals_listfiltered_dealsdropped_candidatesno_trend_candidatesdropped_year_round_high_rankdeal profit_strcost_strprofitcostroi hours_since sales_rankoffersbase_half_lifefinal_half_lifescoredrop_candidate seasonalitytrendoffer_trend_modifiertop_20strategies_textcandidates_for_airpromptpayloadr prompt_size start_time response_datalatency ai_failedrr json_match content_clean start_idxend_idxparsed reasoning_map selected_list rejected_listritemr= is_selectedr final_dealsrun_id generated_atrecords_to_insertrank_idxr:rrr generate_prime_pickss      /      $   &    0        *    "          ("r)r)(loggingrr)rWrrrosworkerrcelerydb_utilsr ava_advisorrr new_analyticsr pricing_versionr r keepa_deals.db_utilsr getLogger__name__r,rrrrrrrrrCrGrUr^taskrrrrr s8      ;#