o
    VÅiÃp  ã                   @   sž  d dl mZ d dlZd dlZd dlZd dlZd dlmZmZmZ d dl	m
Z
 d dlZd dlmZ ddlmZmZmZmZmZ ddlmZmZmZmZ dd	lmZmZ dd
lmZ ddlm Z  ddl!m"Z#m$Z$m%Z%m&Z& ddl'm(Z(m)Z)m*Z* ddl+m,Z,m-Z- ddl.m/Z/m0Z0m1Z1 ee2ƒZ3e
ƒ  dZ4dZ5ej6 7ej6 8e9¡d¡Z:dZ;dZ<dZ=dZ>dZ?dZ@dZAdd„ ZBdd„ ZCdd„ ZDdd„ ZEd d!„ ZFd)d#d$„ZGejHd%d&d'd(„ ƒZIdS )*é    )Ú	getLoggerN)ÚdatetimeÚtimezoneÚ	timedelta)Úload_dotenv)Ú
celery_appé   )Ú create_deals_table_if_not_existsÚsanitize_col_nameÚload_watermarkÚsave_watermarkÚDB_PATH)Úfetch_deals_for_dealsÚfetch_product_batchÚvalidate_asinÚfetch_current_stats_batch)ÚTokenManagerÚTokenRechargeError)ÚFUNCTION_LIST)Úget_seller_info_for_single_deal)Úload_settingsÚcalculate_all_in_costÚcalculate_profit_and_marginÚcalculate_min_listing_price)Úget_1yr_avg_sale_priceÚget_percent_discountÚ	get_trend)Úclassify_seasonalityÚget_sells_period)Ú_process_single_dealÚclean_numeric_valuesÚ_process_lightweight_updatez3.0-ConsolidatedÚdealszheaders.jsoné   é2   Úsmart_ingestor_locki  éÈ   c                 C   s(   t dddtjd}|t| d }| ¡ S )zFConverts Keepa time (minutes since 2011-01-01) to ISO 8601 UTC string.éÛ  r   ©Útzinfo)Úminutes)r   r   Úutcr   Ú	isoformat)Úkeepa_minutesÚkeepa_epochÚ	dt_object© r0   ú5/var/www/agentarbitrage/keepa_deals/smart_ingestor.pyÚ_convert_keepa_time_to_iso/   s   r2   c                 C   sD   | sdS t  | ¡ tj¡}t dddtjd}|| }t| ¡ d ƒS )zIConverts an ISO 8601 UTC string to Keepa time (minutes since 2011-01-01).r   r'   r   r(   é<   )r   ÚfromisoformatÚ
astimezoner   r+   ÚintÚtotal_seconds)Úiso_strr/   r.   Údeltar0   r0   r1   Ú_convert_iso_to_keepa_time5   s   r:   c              
   C   s˜   z+t  | ¡ tj¡}t  tj¡}||tdd kr*t d| › d| 	¡ › ¡ | 	¡ } W n t
yE } zt d|› ¡ W Y d}~nd}~ww t| ƒ dS )z5Saves watermark with protection against future dates.é   ©Úhoursz#Clamping Extreme Future Watermark: z -> z!Error checking watermark safety: N)r   r4   r5   r   r+   Únowr   ÚloggerÚwarningr,   Ú	ExceptionÚerrorr   )Úiso_timestampÚwm_dtÚnow_utcÚer0   r0   r1   Úsave_safe_watermark>   s   €€ÿrG   c           	      C   s|  | sdS |   dg ¡}|   dg ¡}|   dg ¡}d}t|ƒdkr)|d dkr)|d }nt|ƒdkr9|d dkr9|d }|dkr?dS g }t|ƒdkrT|d dkrT| |d ¡ t|ƒdkrg|d dkrg| |d ¡ t|ƒd	krz|d	 dkrz| |d	 ¡ t|ƒd
kr|d
 dkr| |d
 ¡ t|ƒdkr |d dkr | |d ¡ t|ƒdkr³|d dkr³| |d ¡ t|ƒdkrÆ|d dkrÆ| |d ¡ t|ƒd	krÙ|d	 dkrÙ| |d	 ¡ t|ƒd
krì|d
 dkrì| |d
 ¡ t|ƒdkrÿ|d dkrÿ| |d ¡ |sdS |   dd¡}|dkr|dk rdS t|ƒ}|dk r!dS ||d kr*dS |dkr<|| | }|dk r<dS dS )z’
    Heuristic check to see if a deal is worth a heavy fetch (20 tokens).
    Returns True if potentially profitable, False if obviously bad.
    FÚcurrentÚavg90Úavg365éÿÿÿÿé   r   é   é   é   é   ÚsalesRankDrops365i°  gš™™™™™ñ?r   gš™™™™™É?T)ÚgetÚlenÚappendÚmax)	ÚstatsrH   rI   rJ   Ú	buy_priceÚsell_candidatesÚdrops365Úest_sellÚ	gross_roir0   r0   r1   Úcheck_peek_viabilityM   sJ   
&&&&&&&&&&


r\   c               
   C   sÜ   zRt  t¡B} |  ¡ }d}| |¡ dd„ | ¡ D ƒ}|r1t dt|ƒ› d¡ t	j
d|gd nt d¡ W d	  ƒ W d	S W d	  ƒ W d	S 1 sKw   Y  W d	S  tym } zt d
|› ¡ W Y d	}~d	S d	}~ww )z_
    Finds deals stuck in Pending (is_restricted IS NULL) for > 1 hour and re-queues them.
    zó
                SELECT r.asin
                FROM user_restrictions r
                JOIN deals d ON r.asin = d.asin
                WHERE r.is_restricted IS NULL
                AND d.last_seen_utc < datetime('now', '-1 hour')
            c                 S   ó   g | ]}|d  ‘qS )r   r0   ©Ú.0Úrowr0   r0   r1   Ú
<listcomp>²   ó    z.requeue_stuck_restrictions.<locals>.<listcomp>z!Ghost Restriction Sweeper: Found z stuck ASINs. Re-queueing.ú4keepa_deals.sp_api_tasks.check_restriction_for_asins©Úargsz7Ghost Restriction Sweeper: No stuck restrictions found.Nz%Error in requeue_stuck_restrictions: )Úsqlite3Úconnectr   ÚcursorÚexecuteÚfetchallr?   ÚinforS   ÚceleryÚ	send_taskrA   rB   )Úconnrh   ÚqueryÚasinsrF   r0   r0   r1   Úrequeue_stuck_restrictions£   s"   
ð&ò€ÿrq   rN   c              
      sX  zŒ| j dk rt d| j › d¡ W dS g }t t¡}tj|_| ¡ }d}| 	||f¡ | 
¡ }W d  ƒ n1 s;w   Y  |sEW dS dd„ |D ƒ}t dt|ƒ› d	|› ¡ |  d
t|ƒ ¡ t d¡}t||ddd\}}	}	}
|
rz|  |
¡ |r€d|vrˆt d¡ W dS g }ttƒ}t |¡}W d  ƒ n1 sžw   Y  |d D ]Z}| d¡‰ t‡ fdd„|D ƒdƒ}|s½q§t|ƒ}t||ƒ}|rt|ƒ}t tj¡ ¡ |d< d|d< | d¡}|durüt |t!t"fƒrü|dkrüt dˆ › d|d›d¡ | #|¡ q§|r‹t t¡u}| ¡ }dd„ |D ƒ}| $ddg¡ g }|D ]‰t%‡fdd„|D ƒƒ}| #|¡ q!d &d d„ |D ƒ¡}d &d!gt|ƒ ¡}d &d"d„ |D ƒ¡}d#t'› d$|› d%|› d&|› }| (||¡ | )¡  t d't|ƒ› d(¡ W d  ƒ W dS 1 sƒw   Y  W dS W dS  t*y« } ztj+d)|› d*d+ W Y d}~dS d}~ww ),z«
    Finds deals that are approaching the Janitor's 72h deadline (e.g. > 48h old)
    and forces a refresh to prevent them from being deleted if they are still valid.
    é
   z(Skipping Stale Rescue: Low Refill Rate (z/min).Nz·
                SELECT * FROM deals
                WHERE last_seen_utc < datetime('now', '-48 hours')
                ORDER BY last_seen_utc ASC
                LIMIT ?
            c                 S   r]   ©ÚASINr0   r^   r0   r0   r1   ra   Ù   rb   z&rescue_stale_deals.<locals>.<listcomp>zStale Deal Rescue: Found z deals > 48h old. Refreshing: é   ÚKEEPA_API_KEYé´   rN   ©ÚdaysÚoffersÚproductsz.Stale Deal Rescue: Keepa returned no products.Úasinc                 3   s     | ]}|d  ˆ kr|V  qdS )rt   Nr0   ©r_   Úr©r|   r0   r1   Ú	<genexpr>ó   s   € z%rescue_stale_deals.<locals>.<genexpr>Úlast_seen_utcÚstale_rescueÚsourceÚProfitr   zStale Rescue: ASIN z updated but Profit is now $z.2fz'. It will be hidden from the dashboard.c                 S   ó   g | ]}t |ƒ‘qS r0   ©r
   ©r_   Úhr0   r0   r1   ra   
  rb   c                 3   ó    | ]}ˆ   |¡V  qd S ©N©rR   r‡   ©Úrow_dictr0   r1   r€     ó   € ú, c                 s   ó    | ]	}d |› d V  qdS ©ú"Nr0   r‡   r0   r0   r1   r€     ó   € Ú?c                 s   ó*    | ]}|d krd|› d|› dV  qdS ©rt   r’   z"=excluded."Nr0   r‡   r0   r0   r1   r€     ó   €( úINSERT INTO ú (ú
) VALUES (ú") ON CONFLICT(ASIN) DO UPDATE SET z*Stale Deal Rescue: Successfully refreshed ú deals.zError in rescue_stale_deals: T©Úexc_info),ÚREFILL_RATE_PER_MINUTEr?   rk   rf   rg   r   ÚRowÚrow_factoryrh   ri   rj   rS   Úrequest_permission_for_callÚosÚgetenvr   Úupdate_after_callr@   ÚopenÚHEADERS_PATHÚjsonÚloadrR   ÚnextÚdictr!   r    r   r>   r   r+   r,   Ú
isinstancer6   ÚfloatrT   ÚextendÚtupleÚjoinÚ
TABLE_NAMEÚexecutemanyÚcommitrA   rB   )Útoken_managerÚlimitÚ
stale_rowsrn   rh   ro   Ústale_asinsÚapi_keyÚ	prod_respÚ_Útokens_leftÚrows_to_upsertÚfÚheaders_listÚpÚexisting_rowÚexisting_row_dictÚprocessed_rowÚprofitÚsanitized_headersÚdata_for_upsertÚ	row_tupleÚcols_strÚvals_strÚ
update_strÚ
upsert_sqlrF   r0   )r|   r   r1   Úrescue_stale_deals½   s„   

ö



ÿ



€(îÿ"€ÿrË   zkeepa_deals.smart_ingestor.run)Únamec            C         sž  t j tjj¡} | jttd}|j	ddst
 d¡ d S z¡z^t
 dt› d¡ tƒ  tƒ  t d¡}t d¡}|sSt
 d	¡ W W | ¡ rQ| ¡  t
 d
¡ d S d S t|ƒ}| ¡ rat
 d¡ n| ¡  t|dd t
 d¡ | d¡ t}|jdk r‡d}t
 d|› d¡ tƒ }|d u r­t
 d¡ t tj¡t dd  !¡ }t
 d|› ¡ t"|ƒ nPz5t #|¡ $tj¡}t tj¡}||t dd krát
 %d|› d| !¡ › d¡ |t dd  !¡ }t"|ƒ W n t&yü }	 zt
 d|	› ¡ W Y d }	~	nd }	~	ww t'|ƒ}
t
 d|› d|
› d¡ g }d}d}t
 d¡ 	 | (¡  |t)kr,t
 %d!t)› d"¡ nÒt*|ƒ|krAt
 %d#t*|ƒ› d$¡ d }n½| d¡ d }d%}t+|ƒD ]R}z#t,||d&|d'\}}}|d urf| -|¡ |rqd(|v rqW  n0W qN t&y  }	 z t
 %d)|› d*|d+ › d,|	› ¡ t. /d-|d+  ¡ W Y d }	~	qNd }	~	ww |r©d(|vrªnT|d( d. s·t
 d/¡ nGd0d1„ |d( d. D ƒ}|j0d2d3„ d d4 d}|D ]}|d5 |
krèt
 d6| 1d7¡› d8¡ d } n| 2|¡ qÏ|rón|d+7 }t. /d+¡ q|st
 d9¡ W W | ¡ r| ¡  t
 d
¡ d S d S t
 d:t*|ƒ› d;¡ |j0d<d3„ d= d>d1„ |D ƒ}t3ƒ ‰ i }zbt4j5t6d?d}t4j7|_8| 9¡ }d@ :dAt*|ƒ ¡}| ;dBt<› dC|› d|¡ | =¡ }|D ]0}|dD }|dE }|dF }d} | r…t
 dG|dH › dI¡ qeˆ  >|dH ¡ t?|ƒ||dH < qe| @¡  W n t&y¶ }	 zt
 %dJ|	› ¡ W Y d }	~	nd }	~	ww tA}!|jdKk rÏd+}!t
 dL|j› dM|!› dN¡ n|jdk räd}!t
 dO|j› dM|!› dP¡ tBtCƒ}"tD E|"¡}#W d   ƒ n	1 sùw   Y  d}$t+dt*|ƒ|!ƒD ]d}%| (¡  ||%|%|! … }&dQd1„ |&D ƒ}'‡ fdRd1„|'D ƒ}(‡ fdSd1„|'D ƒ})i }*g }+|(r€| dTt*|(ƒ ¡ tF||(dUddV\},}}}|rT| -|¡ |,r€dW|,v r€|,dW D ]}-tG|- 1dX¡ƒrs|+ 2|-d7 ¡ q`t
 dY|- 1d7¡› ¡ q`|+rËt+dt*|+ƒtHƒD ]?}.|+|.|.tH … }/| dt*|/ƒ ¡ tI||/dUd+ddZ\}0}}}|r³| -|¡ |0rÉdW|0v rÉ|0dW D ]	}-|-|*|-d7 < q¿q‹|)r| dt*|)ƒ ¡ tF||)d[ddV\}1}}}|rë| -|¡ |1rdW|1v r|1dW D ]	}-|-|*|-d7 < q÷g }2|&D ]d}|d7 }3|3|*vrq|*|3 }4|4 J|¡ d }5|3ˆ v r?tK||3 |4ƒ}5|5r>tL|5ƒ}5t tj¡ !¡ |5d\< d]|5d^< n!tM|4||ƒ}6tN|4|6|ƒ}5|5r`tL|5ƒ}5t tj¡ !¡ |5d\< d_|5d^< |5rh|2 2|5¡ q|2rVt
 d`t*|2ƒ› dadbd1„ |2d dK… D ƒ› dc¡ z³t4j5t6d?d }7|7 9¡ }8ddd1„ |#D ƒ}9|9 Od\d^g¡ g }:|2D ]‰tP‡fdedf„|#D ƒƒˆ 1d\¡ˆ 1d^¡f };|: 2|;¡ q£dg :dhdf„ |9D ƒ¡}<dg :dAgt*|9ƒ ¡}=dg :didf„ |9D ƒ¡}>djt<› dk|<› dl|=› dm|>› }?|8 Q|?|:¡ |7 R¡  |$t*|2ƒ7 }$dnd1„ |2D ƒ}@|@rtjSdo|@gdp |&dq }AtT|Ad5 ƒ}BtU|Bƒ t
 dr|B› ¡ W d   ƒ n	1 s0w   Y  W q t&yU }	 zt
jds|	› d dt W Y d }	~	qd }	~	ww |&dq }AtT|Ad5 ƒ}BtU|Bƒ t
 du|B› ¡ qt
 dvt*|ƒ› dw|$› d¡ W n. tVy« }	 z!t
 %dx|	› dy¡ W Y d }	~	W | ¡ r¥| ¡  t
 d
¡ d S d S d }	~	ww W | ¡ r½| ¡  t
 d
¡ d S d S | ¡ rÎ| ¡  t
 d
¡ w w )zN)ÚtimeoutF)ÚblockingzD--- Task: smart_ingestor is already running. Skipping execution. ---z+--- Task: smart_ingestor started (Version: z) ---rv   Ú	XAI_TOKENz KEEPA_API_KEY not set. Aborting.z+--- Task: smart_ingestor lock released. ---z6Skipping sync (Recharge active, estimated tokens low).rN   )rµ   zStep 1: Initializing Sync...r#   r$   z-Low Refill Rate. Reducing NEW_DEALS limit to Ú.zYCRITICAL: Watermark not found. Assuming fresh start/reset required but not handling here.r;   r<   zWatermark defaulted to z-CRITICAL: Extreme Future Watermark Detected (z > z1). Resetting to 24h ago to restore functionality.zError checking watermark date: zLoaded watermark: z (Keepa time: ú)r   z4Step 2: Paginating through deals to find new ones...Tz/Safety Limit Reached: Stopped pagination after z pages.zNew Deal Limit Reached: Found rœ   ru   é   )Ú	sort_typer´   r"   zFetch failed on page z
 (Attempt r   z): é   Údrz!No more deals found (empty list).c                 S   s   g | ]}t | d ¡ƒr|‘qS r   )r   rR   ©r_   Údr0   r0   r1   ra   ’  s    zrun.<locals>.<listcomp>c                 S   ó   | d S ©NÚ
lastUpdater0   ©Úxr0   r0   r1   Ú<lambda>—  ó    zrun.<locals>.<lambda>)ÚkeyÚreverserÚ   zStop Trigger: Deal r|   z <= Watermark.zNo new deals found.zFound z? new deals. Sorting by Oldest First for incremental processing.c                 S   rØ   rÙ   r0   rÛ   r0   r0   r1   rÝ   ®  rÞ   )rß   c                 S   r]   r   r0   rÖ   r0   r0   r1   ra   ±  rb   r3   ú,r”   zSELECT * FROM z WHERE ASIN IN (zList atz	1yr. Avg.r„   zASIN rt   z6: Detected as ZOMBIE/BAD DATA. Forcing heavy re-fetch.z Failed to check existing ASINs: rr   zCritically Low Refill Rate (z#/min). Reducing SCAN_BATCH_SIZE to z  (Max potential cost 22 tokens).zLow Refill Rate (z to prevent Deficit Lockout.c                 S   r]   r   r0   rÖ   r0   r0   r1   ra   ê  rb   c                    s   g | ]}|ˆ vr|‘qS r0   r0   ©r_   Úa©Úexisting_asins_setr0   r1   ra   ì  ó    c                    s   g | ]}|ˆ v r|‘qS r0   r0   râ   rä   r0   r1   ra   í  ræ   rL   im  rx   r{   rV   zPeek Rejected: ASIN )ry   Úhistoryrz   rw   r   Úsmart_ingestor_lightrƒ   Úsmart_ingestorz
Upserting z deals to DB. ASINs: c                 S   s   g | ]}|  d ¡‘qS rs   r‹   r}   r0   r0   r1   ra   6  s    z...c                 S   r…   r0   r†   r‡   r0   r0   r1   ra   :  rb   c                 3   r‰   rŠ   r‹   r‡   rŒ   r0   r1   r€   ?  rŽ   zrun.<locals>.<genexpr>r   c                 s   r   r‘   r0   r‡   r0   r0   r1   r€   B  r“   c                 s   r•   r–   r0   r‡   r0   r0   r1   r€   D  r—   r˜   r™   rš   r›   c                 S   s   g | ]
}d |v r|d  ‘qS rs   r0   r^   r0   r0   r1   ra   L  s    rc   rd   rK   zWatermark ratcheted to z Chunk processing/upsert failed: r   z'Watermark advanced (no deals saved) to zTask Complete: Processed z scanned, upserted z--- Task Paused: z$. Releasing lock to free worker. ---)WÚredisÚRedisÚfrom_urlrl   ÚconfÚ
broker_urlÚlockÚLOCK_KEYÚLOCK_TIMEOUTÚacquirer?   rk   ÚSMART_INGESTOR_VERSIONrq   r	   r£   r¤   rB   ÚlockedÚreleaser   Úshould_skip_syncÚsync_tokensrË   r¢   ÚMAX_NEW_DEALS_PER_RUNrŸ   r   r   r>   r   r+   r   r,   r   r4   r5   r@   rA   r:   Úemit_heartbeatÚMAX_PAGES_PER_RUNrS   Úranger   r¥   ÚtimeÚsleepÚsortrR   rT   Úsetrf   rg   r   r    r¡   rh   r°   ri   r±   rj   Úaddr«   ÚcloseÚSCAN_BATCH_SIZEr¦   r§   r¨   r©   r   r\   ÚCOMMIT_BATCH_SIZEr   Úupdater!   r    r   r   r®   r¯   r²   r³   rm   r2   rG   r   )CÚredis_clientrï   r¸   Úxai_api_keyr´   Úcurrent_max_dealsÚwatermark_isorD   rE   rF   Úwatermark_keepa_timeÚall_new_dealsÚpageÚhit_new_deal_limitÚdeal_responseÚmax_page_retriesÚattemptrº   r»   Údeals_on_pageÚfound_older_dealÚdealÚ	asin_listÚexisting_rows_mapÚ
conn_checkÚc_checkÚplaceholdersÚrowsr~   Úlist_atÚyr_avgrÃ   Ú	is_zombieÚcurrent_batch_sizer½   ÚheadersÚtotal_upsertedÚiÚchunk_dealsÚchunk_asinsÚchunk_new_asinsÚchunk_existing_asinsÚchunk_productsÚnew_candidatesÚ	peek_respr¿   ÚjÚ	sub_batchr¹   Úprod_resp_lightr¼   r|   Úproduct_datarÂ   Úseller_data_cachern   rh   rÄ   rÅ   rÆ   rÇ   rÈ   rÉ   rÊ   Ú	new_asinsÚlast_deal_in_chunkÚ
new_wm_isor0   )rå   r   r1   Úrun  s  



  9þ  É




€€ÿ




€  €þ

È:
 
Cþ ¿€ÿ
ÿ€

€
€.*â€ "€ÿ 
þ€ûþ
þþr/  )rN   )JÚloggingr   r£   r¨   rf   rü   r   r   r   Údotenvr   rê   Úworkerr   rl   Údb_utilsr	   r
   r   r   r   Ú	keepa_apir   r   r   r   r´   r   r   Úfield_mappingsr   Úseller_infor   Úbusiness_calculationsr   Úbusiness_load_settingsr   r   r   Únew_analyticsr   r   r   Úseasonality_classifierr   r   Ú
processingr   r    r!   Ú__name__r?   ró   r±   Úpathr°   ÚdirnameÚ__file__r§   ÚMAX_ASINS_PER_BATCHr  r  rð   rñ   rú   rø   r2   r:   rG   r\   rq   rË   Útaskr/  r0   r0   r0   r1   Ú<module>   sL    	V

b