a 'Ni2@s8dZddlmZddlmZmZGdddZeZdS)z API Usage Tracker for BaoLife Tracks API usage and costs for OpenAI calls. Provides insights into spending patterns and enables budget enforcement. )get_database_connection)datetime timedeltac@sreZdZdZdddddddZdd Zd d Zdd dZdddZdddZ d ddZ d!ddZ d"ddZ dS)#APIUsageTrackerzI Track API usage and costs for monitoring and budget management. gv !>gv !>)inputoutputgh㈵>gh㈵>) gpt-4o-minizgpt-4ocCs d|_dS)zInitialize API usage trackerFN)_table_created)selfr 2/var/www/lichun.app/lichun/ws/api_usage_tracker.py__init__szAPIUsageTracker.__init__c Cst}zzF|*}|d|tdWdn1sB0YWn2ty}ztd|WYd}~n d}~00W|n |0dS)z*Create api_usage table if it doesn't exista CREATE TABLE IF NOT EXISTS api_usage ( id INT AUTO_INCREMENT PRIMARY KEY, player_id VARCHAR(36) NOT NULL, conversation_id VARCHAR(36), endpoint VARCHAR(50) NOT NULL, model VARCHAR(50) NOT NULL, prompt_tokens INT NOT NULL, completion_tokens INT NOT NULL, total_tokens INT NOT NULL, cost_usd DECIMAL(10, 6) NOT NULL, created_date DATETIME NOT NULL, purpose VARCHAR(100), INDEX idx_player (player_id), INDEX idx_created (created_date), INDEX idx_conversation (conversation_id), INDEX idx_model (model) ) z API usage table created/verifiedNz"Failed to create api_usage table: )rcursorexecutecommitprint Exceptionclose)r mydbrer r r create_table_if_not_exists"s  *&z*APIUsageTracker.create_table_if_not_exists conversationc CsD|js|d|_|dd}|dd}|dd}||jvrh||j|d||j|d} n4||jdd||jdd} td |d t} zzP| 4} | d ||||||| |f| Wd n1s0YWn4t y(} ztd | WYd } ~ n d } ~ 00W| n | 0| S)a Log API usage to database. Args: player_id: Player ID conversation_id: Conversation ID (optional) model: Model name (e.g., 'gpt-4o-mini') usage: Usage dict with 'prompt_tokens', 'completion_tokens', 'total_tokens' purpose: Purpose of the call (e.g., 'conversation', 'summarization', 'fact_extraction') T prompt_tokensrcompletion_tokens total_tokensrrrzWarning: Unknown model 'z', using gpt-4o-mini pricingaJ INSERT INTO api_usage (player_id, conversation_id, endpoint, model, prompt_tokens, completion_tokens, total_tokens, cost_usd, created_date, purpose) VALUES (%s, %s, 'openai_chat', %s, %s, %s, %s, %s, NOW(), %s) NzFailed to track API usage: ) r rgetPRICINGrrrrrrr) r player_idconversation_idmodelusagepurposerrrcostrrrr r r track_usageCsF      *&zAPIUsageTracker.track_usagec CsNt}z6z|jdd}|d||f|}|r|drt|dpJdt|dpXdt|dpfdt|dptd|d |d |d Wd WW|Sddddd d |d Wd WW|SWd n1s0YWn@ty2}z&td |WYd }~W|d Sd }~00W|n |0d S)z Get player's API usage statistics for last N days. Args: player_id: Player ID days: Number of days to look back Returns: Dict with usage statistics T dictionarya SELECT SUM(cost_usd) as total_cost, SUM(total_tokens) as total_tokens, COUNT(*) as total_calls, AVG(cost_usd) as avg_cost_per_call, MIN(created_date) as first_call, MAX(created_date) as last_call FROM api_usage WHERE player_id = %s AND created_date >= DATE_SUB(NOW(), INTERVAL %s DAY) total_calls total_costrravg_cost_per_call first_call last_call)r(rr'r)r*r+ period_daysNzFailed to get player usage: ) rrrfetchonefloatintrrr)r rdaysrrresultrr r r get_player_usagesD     $  z APIUsageTracker.get_player_usageNc Cst}zzv|jddV}|r.|d||fn|d|f|}dd|DWdWW|S1sr0YWn@ty}z(td|gWYd}~W|Sd}~00W|n |0dS) z Get usage breakdown by purpose. Args: player_id: Optional player ID (None for all players) days: Number of days to look back Returns: List of dicts with usage by purpose Tr%a6 SELECT purpose, COUNT(*) as call_count, SUM(total_tokens) as total_tokens, SUM(cost_usd) as total_cost, AVG(cost_usd) as avg_cost FROM api_usage WHERE player_id = %s AND created_date >= DATE_SUB(NOW(), INTERVAL %s DAY) GROUP BY purpose ORDER BY total_cost DESC a  SELECT purpose, COUNT(*) as call_count, SUM(total_tokens) as total_tokens, SUM(cost_usd) as total_cost, AVG(cost_usd) as avg_cost FROM api_usage WHERE created_date >= DATE_SUB(NOW(), INTERVAL %s DAY) GROUP BY purpose ORDER BY total_cost DESC c Ss@g|]8}|dt|dt|dt|dt|ddqS)r! call_countrr(avg_cost)r!r3rr(r4)r/r.).0rowr r r s    z8APIUsageTracker.get_usage_by_purpose..Nz Failed to get usage by purpose: )rrrfetchallrrr)r rr0rrresultsrr r r get_usage_by_purposes,     z$APIUsageTracker.get_usage_by_purposec Cst}zz|jdd}|d|f|}|rt|dp@dt|dpNdt|dp\dt|dpjdt|d pxd|d Wd WW|SWd n1s0YWn@ty}z&td |WYd }~W|d Sd }~00W|n |0d S) z Get total usage across all players. Args: days: Number of days to look back Returns: Dict with aggregate statistics Tr%a SELECT COUNT(DISTINCT player_id) as unique_players, SUM(cost_usd) as total_cost, SUM(total_tokens) as total_tokens, COUNT(*) as total_calls, AVG(cost_usd) as avg_cost_per_call FROM api_usage WHERE created_date >= DATE_SUB(NOW(), INTERVAL %s DAY) unique_playersrr(rr'r))r;r(rr'r)r,NzFailed to get total usage: ) rrrr-r/r.rrr)r r0rrr1rr r r get_total_usages.   $  zAPIUsageTracker.get_total_usage@cCsX|j|dd}|rT|d}||}|dkr6||dnd}||||||k|dkdSdS) z Check if player is within budget. Args: player_id: Player ID monthly_limit: Monthly budget limit in USD Returns: Dict with budget status r$)r0r(rdP)r( monthly_limit remainingpercentage_usedZ over_budgetwarningN)r2)r rr@r r(rArBr r r check_player_budget!s  z#APIUsageTracker.check_player_budgetc CsDtdd|r6td|d|d|||}ntd|d||}|r2tdtd|dd td |d d td |dd td|dd |sd|vrtd|dtddtd|||}|D]<}td|ddd|ddd|ddd|ddqtdddS) z Print usage report to console. Args: player_id: Optional player ID (None for total usage) days: Number of days to report  z<============================================================zAPI Usage Report for Player z (Last z days)zTotal API Usage Report (Last zTotal Cost: $r(z.4fzTotal Tokens: r,zTotal Calls: r'zAvg Cost/Call: $r)r;zUnique Players: z<------------------------------------------------------------zUsage by Purpose:z r!Z20sz | Calls: r3Z4dz | Cost: $z6.4fz | Avg: $r4N)rr2r<r:)r rr0r Z by_purposeitemr r r print_usage_report>s0     z"APIUsageTracker.print_usage_report)r)r$)Nr$)r$)r=)NrE) __name__ __module__ __qualname____doc__rr rr#r2r:r<rDrIr r r r r s  ! @ 9 : + rN)rMdatabase.db_operationsrrrrtrackerr r r r s  Y