o
    tiym                     @   s  d dl Z d dlZd dlZd dlZd dlmZmZ ddlm	Z	 d dl
Z
d dlZd dlZd dlmZ ddlmZ ddlmZ e Ze ZedddZd)d	d
Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zi add Zdd Zdd Z dd  Z!d!d" Z"d#d$ Z#d%d& Z$d'd( Z%dS )*    N)datetime	timedelta   )SEASONAL_KEYWORD_MAP)XaiTokenManager)XaiCachei  N/Ac                 C   sV  |s	t d dS d|  d| d| d|dd| d| d|	 d|
 }t|}|durA| dk}t d| d	|  d
 |S t sPt d|  d dS d|dd| d|  d| d| d| d| d| d|	 d|
 d| d}d|dgdddd}d| d d!}t d"|  d# zutj	d$d%d}|j
d&||d'}|  | d( d) d* d+   }d,|v }t d-|  d.|dd/| d0 |st d1|  d2|dd3| d4| d5| d6| d7|	 d8|
 d0 t|t| |W  d   W S 1 sw   Y  W dS  tjtjtfy* } zt d9|  d:|  W Y d}~dS d}~ww );z
    Queries the XAI API to act as a reasonableness check for a calculated price,
    now with caching and token management.
    z8XAI_API_KEY not provided. Skipping reasonableness check.Tzreasonableness:|.2fNtruez)XAI Cache HIT for reasonableness. Found 'z' for title 'z'.zBXAI daily limit reached. Cannot perform reasonableness check for 'z'. Defaulting to reasonable.aG  
    You are an expert Arbitrage Advisor.
    CONTEXT: The "3-Year Average Price" is a simple mean of all sales, including off-season lows.
    For seasonal items (especially Textbooks), the "Peak Season" price can validly be 200-400% higher than the average.

    Given the following book details, is a peak selling price of $z reasonable during z:?
    Respond with only "Yes" or "No".

    - **Title:** "z"
    - **Category:** "z%"
    - **Identified Peak Season:** "z"
    - **Binding:** "z"
    - **Page Count:** "z"
    - **Sales Rank Info:** "z"
    - **3-Year Trend:** "z$"
    - **3-Year Average Price:** "$z"
    - **Image URL:** "z"
    user)rolecontentzgrok-4-fast-reasoningg?
   )messagesmodeltemperature
max_tokenszBearer zapplication/json)AuthorizationzContent-Typez XAI Reasonableness Request for 'z' (Cache MISS)g      >@)timeoutz$https://api.x.ai/v1/chat/completions)headersjsonchoicesr   messager   yeszXAI Reasonableness Check for 'z' at $z: AI responded ''zXAI REJECTED: Title='z
', Price=$z, Category='z', Season='z', Binding='	', Rank='
', Trend='', 3yrAvg='$zBAn unexpected error occurred during XAI reasonableness check for 'z': )loggingwarning	xai_cachegetlowerinfoxai_token_managerrequest_permissionhttpxClientpostraise_for_statusr   stripsetstrHTTPStatusErrorRequestError	Exceptionerror)titlecategoryseason	price_usdapi_keybinding
page_count	image_url	rank_info
trend_infoavg_3yr_usd	cache_keycached_resultis_reasonablepromptpayloadr   clientresponser   e rE   :/var/www/agentarbitrage/keepa_deals/stable_calculations.py_query_xai_for_reasonableness   sr   
6
	

  >(rG   c           
      C   s  |  dd}td| d |  di }|s$td| d dd	iS | d
g }| dg }d}t|dkrB|d durB|d }d}t|dkrT|d durT|d }td| d| d|  |dksj|dk r}td| d| d| d dd	iS z.|| | d }|dd}td| d|d dd|d dd|dd| 
 d|iW S  ty   td| d| d dd	i Y S  ty }	 ztd| dt	|	 d | d| d	 dd	iW  Y d}	~	S d}	~	ww )!z
    Calculates the percentage difference between the current used price and the 
    365-day average used price. Prepends symbols for above/below average.
    asinunknownz percent_down_365 input for ASIN z: product data received.statsASIN zH: 'stats' object is missing or empty. Cannot calculate Percent Down 365.zPercent Down 365-currentavg365   Nz': Raw current_used (stats.current[2]): z!, Raw avg_365 (stats.avg365[2]): r   zL: Invalid or missing prices for Percent Down 365 calculation. current_used: z, avg_365: z. Returning '-'d   .0f%z(: Percent Down 365 calculated. Current: r
   z
, Avg365: z, Diff: z%, Result: z5: ZeroDivisionError in percent_down_365 (avg_365 was z). Returning '-'z!: Exception in percent_down_365: z. current_used: )
r"   r   debugr    lenr$   ZeroDivisionErrorr1   r0   r-   )
productrH   rJ   current_used_price_rawavg_365_price_rawcurrent_usedavg_365percentage_diff
result_strrD   rE   rE   rF   percent_down_365h   s>   	6
(r^   c                 C   s*   t j| d dd}t j|dtd| d< | S )zTConverts a DataFrame's timestamp column from Keepa Time Minutes to datetime objects.	timestampcoerce)errorsm)unitorigin)pd
to_numericto_datetimeKEEPA_EPOCH)dfnumeric_timestampsrE   rE   rF   _convert_ktm_to_datetime   s   rk   c           *   
      st  |  dd}tt}|d| d z |  dg }t|tr't|dk r5|d| d g dfW S t|d	 trHt|d	 d
krH|d	 nd}t|d tr]t|d d
kr]|d nd}t|d
 trrt|d
 d
krr|d
 nd}t|d trt|d d
kr|d nd}t|dkrt|d trt|d d
kr|d nd}|r|s|s|d| d g dfW S tj	t
|ddddgdt}	|rtj	t
|ddddgdtnd}
|rtj	t
|ddddgdtnd}t tdd }|	|	d |k }	g }d}|rQtj	t
|ddddgdt}||d |k }|d  |d< ||d dk   }|jsQd|d< || |t|7 }|rtj	t
|ddddgdt}||d |k }|d  |d< ||d dk   }|jsd|d< || |t|7 }|s|d| d g dfW S t|djdd}|d| dt| d  g }td!d"}|	djdd}	|	d  |	d#< | D ]\}}|d }|| }|	|	d |k|	d |k@  }|j o|d# dk  }|td$d" }|	|	d |k|	d |k@  }|j o#|d# dk  }|sO|rO||d# dk  jd d }||  d% } |d| d&| d'd(| d) |r|d dkr`|dur`|n|
}!|!du rw|d| d*|d  d) qtjt	|g|!dd+d,d jd }"|"dkr|d| d-| d.|" d/ q|||"d0 q|s|d| d1| d2 g |fW S d3d4 |D }#t
 |#d5}$t
 |#d6}%|%|$ }&|%d7|&  |$d7|&    fd8d4|D }'t|t|' }(|(dkr|d| d9|( d: |d| dt|' d; |'|fW S  t!y9 }) z|j"d| d<|) dd= g dfW  Y d})~)S d})~)ww )>z
    Analyzes historical product data to infer sale events using a search-window logic.
    A sale is inferred when a drop in used or new offer count is followed by a drop
    in sales rank within a defined time window.
    rH   r   rK   zF: Starting sale event inference with search-window logic (New & Used).csv   z%: 'csv' data is missing or too short.r      r   NrP         z9: Rank history or both offer count histories are missing.rO   r_   rank)columnsprice_centsiG  )daysoffer_count
offer_diffUsed
offer_typeNewz8: No instances of any offer count decreasing were found.T)dropz: Found z2 potential sale trigger points (New & Used drops).   )hours	rank_diffH   i  z#: Near Miss - A rank drop occurred r
   z6 hours after the 168-hour window for an offer drop at .z(: No suitable price data for offer type nearest)on	directionz: Ignoring inferred sale at z+ because its associated price was invalid ().)event_timestampinferred_sale_price_centsz': Found 0 confirmed sale events out of z offer drops.c                 S      g | ]}|d  qS r   rE   .0salerE   rE   rF   
<listcomp>      z%infer_sale_events.<locals>.<listcomp>   K   g      ?c                    s,   g | ]} |d    krkrn n|qS r   rE   r   lower_boundupper_boundrE   rF   r   #  s   , z: Rejected z; outlier(s) from inferred sales list using symmetrical IQR.z* sane sale events after outlier rejection.z%: Error during sale event inference: )exc_info)#r"   r   	getLogger__name__rT   
isinstancelistrU   re   	DataFramenparrayreshapepiperk   r   nowr   diffcopyemptyappendr$   concatsort_valuesreset_indexiterrowsanyiloctotal_secondsr    
merge_asof
percentiler0   r1   )*rW   rH   loggercsv_datarank_historyused_price_historynew_price_historyused_offer_count_historynew_offer_count_historydf_rankdf_used_pricedf_new_pricehistory_window_startall_offer_drops_listtotal_offer_drops_countdf_used_offers
used_dropsdf_new_offers	new_dropsoffer_dropsconfirmed_salessearch_window_rz   
start_timeend_timerank_changes_in_windowhas_rank_dropnear_miss_window_endnear_miss_rank_changeshas_near_miss_rank_dropfirst_miss_timehours_missed_byprice_df_to_useprice_at_sale_timepricesq1q3iqr
sane_salesoutliers_foundrD   rE   r   rF   infer_sale_events   s   

****6
&..&
&


  
$



r   c                 C   s^   t t}t| \}}|sddiS |d }|dd}|r+|dkr+dd|d diS ddiS )	z3
    Gets the most recent inferred sale price.
    zRecent Inferred Sale PricerL   rO   r   r   $rQ   r
   )r   r   r   r   r"   )rW   r   sale_eventsr   most_recent_eventrs   rE   rE   rF   recent_inferred_sale_price0  s   
r   c              
   C   s  | rt | dk r
dS z_t| }|d dd |d< t|d |d \}}}}}|d  }|d  }|| | }	|| | }
|	dkrIW d	S |
|	 |	 d
 }d}|dkrZd}n|dk r`d}| d|ddW S  ty } zt	
td|  W Y d}~dS d}~ww )zFCalculates the long-term price trend slope over the available history.rn   zInsufficient datar   c                 S   s   |   S )N)r_   )xrE   rE   rF   <lambda>J  s    z+calculate_long_term_trend.<locals>.<lambda>timestamp_valr   r   FlatrQ   FLAT   UPDOWNz (z.1fz% over 3 years)zError calculating trend: NError)rU   re   r   applyst
linregressminmaxr0   r   r   r   r1   )r   ri   slope	interceptr_valuep_valuestd_errr   r   start_price	end_pricepercent_changer   rD   rE   rE   rF   calculate_long_term_trendB  s*   
r   c                 C   s&   | sdS dd | D }t |t| S )zACalculates the average price over the full history (now 3 years).rO   c                 S   r   r   rE   )r   srE   rE   rF   r   c  r   z%calculate_3yr_avg.<locals>.<listcomp>)sumrU   )r   r   rE   rE   rF   calculate_3yr_avg`  s   r   c           '      C   s
  t t}| dd}td}d}|rt||k r.|d| dt| d dd	d	d
S t	|}t
|d |d< |d jj|d< |dd ddg}t|dk r\dd	d	d
S |d  }|d  }	tdt|dd}
tdt|	dd}||d |k d  }||d |	k d  }d}|rtt|}|d| d|d dd|	 d n|d| d|	 d d}|s|d| d| d d|
|d
S t|}|jdkrt|j}|d| d|d dd|j d ntt|}|d| d|d dd | di }|dd gd! d" }|d#g }|r-|d" nd }|d$g }|r<|d" nd }g }|rM|d"krM|| |rZ|d"krZ|| |rg|d"krg|| |rt|}|d% }||kr|d| d&|d dd'|d dd( |}n|d| d&|d dd)|d dd n	|d| d* | d+d}| d,g }|rd-d.d/ |D nd}| d0d}| d1d}| d2r| d2d3 d4d" nd}|dkrd5| }| di }|dd gd6 d7 } |d8d gd6 d7 }!d9|  d:|! }"t!|}#t"|}$|$d"kr)|$d dnd}%|d| d;| d<| d=|
 d>|d? dd@|" dA|# dB|% dC t#|||
|d? |||||"|#|%dD}&|&su|d| dE|d ddF| dG d}n	|d| dH ||
||dIS )Jz
    Analyzes inferred sale events to determine peak/trough seasons and calculate
    the mode of peak season prices, with an XAI verification step. This replaces
    the previous `analyze_seasonality` function.
    rH   r   	XAI_TOKENr   rK   z: Not enough sale events (z) for performance analysis.rO   rL   )peak_price_mode_centspeak_seasontrough_seasonr   monthr   mediancounti  z%bz$: Calculated expected trough price: rQ   r
   z (Median of trough month r   z#: No prices found for trough month r   z1: No prices found for the determined peak month (z: Calculated peak price mode: z (occurred z times).zD: No distinct mode found. Falling back to peak season median price: rJ   rM   NrP   r   avg180rN   g?z: Calculated List at ($z) exceeds Amazon ceiling ($z). Capping price.z) is within Amazon ceiling ($zX: No valid Amazon prices found for ceiling calculation. Proceeding with un-capped price.r2   categoryTreez > c                 s   s    | ]}|d  V  qdS )nameNrE   )r   catrE   rE   rF   	<genexpr>  s    z,analyze_sales_performance.<locals>.<genexpr>r7   numberOfPages	imagesCSV ,z1https://images-na.ssl-images-amazon.com/images/I/   rn   avg90zCurrent Rank: z, 90-day Avg Rank: z": Preparing for XAI check. Title='z', Category='z', Peak Season='z', Price='$g      Y@r   r   r   r   )r7   r8   r9   r:   r;   r<   z: XAI check FAILED. Price $z was deemed unreasonable for 'z'. Invalidating price.z3: XAI check PASSED. Price is considered reasonable.)r   r   r   expected_trough_price_cents)$r   r   r   r"   osgetenvrU   rT   re   r   rg   dtr   groupbyaggidxmaxidxminr   intstrftimetolistfloatr   r   r$   r    r   moder   r   r   joinsplitr   r   rG   )'rW   r   r   rH   xai_api_keyMIN_SALES_FOR_ANALYSISri   monthly_stats
peak_monthtrough_monthpeak_season_strtrough_season_strpeak_season_pricestrough_season_pricesr  r   mode_resultrJ   amz_currentamz_180_avgamz_180amz_365_avgamz_365valid_amz_pricesmin_amz_priceceiling_price_centsr2   category_treer3   r7   r8   r9   rank_currentrank_90r:   r;   avg_3yr_centsr<   r?   rE   rE   rF   analyze_sales_performancef  s   


&


(
*,&

B$r'  c                   C   s   i a td dS )z0Clears the memoization cache for sales analysis.z2Sales analysis memoization cache has been cleared.N)_analysis_cacher   r$   rE   rE   rE   rF   clear_analysis_cache  s   r)  c                 C   sD   |  d}|r|tv rt| S t| \}}t| |}|r |t|< |S )z
    Helper to get or compute sales performance analysis, caching the result.
    Uses the new analyze_sales_performance function.
    rH   )r"   r(  r   r'  )rW   rH   r   r   analysisrE   rE   rF   _get_analysis  s   

r+  c                 C      t | }d|ddiS )z5Wrapper to get the Peak Season from the new analysis.zPeak Seasonr   rL   r+  r"   rW   r*  rE   rE   rF   get_peak_season
     r/  c                 C   s`   t | }|dd}|r|dkrdd|d diS tt}| dd	}|d
| d dS )z
    Wrapper to get the 'List at' price, which is the mode of peak season prices.
    Returns None if the price is invalid, signaling for exclusion.
    r   rO   r   zList atr   rQ   r
   rH   r   rK   zK: No valid 'List at' price could be determined. This deal will be excluded.N)r+  r"   r   r   r   r$   )rW   r*  rs   r   rH   rE   rE   rF   get_list_at_price  s   
r1  c                 C   r,  )z7Wrapper to get the Trough Season from the new analysis.zTrough Seasonr   rL   r-  r.  rE   rE   rF   get_trough_season  r0  r2  c                 C   s<   t | }|dd}|r|dkrdd|d diS ddiS )	z
    Wrapper to get the 'Expected Trough Price', which is the median of trough season prices.
    Returns None if the price is invalid.
    r  rO   r   zExpected Trough Pricer   rQ   r
   Nr-  )rW   r*  rs   rE   rE   rF   get_expected_trough_price"  s
   r3  c                 C   s<   t | \}}|dkrddiS t|| d }d|ddiS )zWCalculates a confidence score based on how many offer drops correlate with a rank drop.r   zProfit ConfidencerL   rQ   rR   rS   )r   rU   )rW   r   total_offer_drops
confidencerE   rE   rF   profit_confidence-  s
   r6  c           	      C   s   |dkrdS | | }|}d}z0||d d|   |t |d|  | |d d|d      }d|d |  }|| }|W S  ty_ } ztd|  d| d	|  W Y d
}~dS d
}~ww )zP
    Calculates the Wilson Score Confidence Interval for a seller's rating.
    r   g        g\(\?rP   r   r   z#Error calculating Wilson score for z positive ratings and z total ratings: N)mathsqrtr0   r   r1   )	positive_ratingstotal_ratingsp_hatnz	numeratordenominatorscorerD   rE   rE   rF   calculate_seller_quality_score6  s   BrA  )r   r   r   r   r   r   )&r   r7  pandasre   numpyr   r   r   seasonal_configr   r  r'   timescipy.statsrJ   r   r%   r   r!   r   rh   rG   r^   rk   r   r   r   r   r'  r(  r)  r+  r/  r1  r2  r3  r6  rA  rE   rE   rE   rF   <module>   sD   
M=  	