sp0jdZddlZddlZddlZddlZddlZddlmZddlm Z m Z dZ dZ dZ dZd Zdad Zd Zd Zd ZdZdZe dZdZdZdZ d3dZdefdZd4dZdZd5dZdZ d6dZ!d7dZ"d5d Z#d5d!Z$d5d"Z%d8d$Z&d%Z'd&Z(d'Z)d4d(Z*d4d)Z+d*Z,d+Z-d,Z.d-Z/d.Z0d/Z1d0Z2d1Z3d2Z4dS)9amSQLite store of the AI usage history: every call, its cost and the summaries the "Ceny AI" dialog shows. The only module that runs SQL of the usage history. One connection per call (the plugin, the service and the game all write), WAL journal, every write one transaction. `calls` holds one row per AI call (a failed call too, with its error). Rows older than RETENTION_MONTHS are rolled up into `daily` (one row per day, provider, model and purpose, kept for ever), so the totals stay right while the detail shrinks. Every summary reads the view `usage`, which is both tables together; every delete removes from both. N) defaultdict)closingcontextmanagerz ai_usage.dbzai_usage_stats.json legacyaI CREATE TABLE calls( id INTEGER PRIMARY KEY AUTOINCREMENT, ts TEXT NOT NULL, day TEXT NOT NULL, provider TEXT NOT NULL, model TEXT NOT NULL, purpose TEXT NOT NULL, session_id TEXT NOT NULL DEFAULT '', title TEXT NOT NULL DEFAULT '', status TEXT NOT NULL DEFAULT 'ok', error TEXT NOT NULL DEFAULT '', latency_ms INTEGER NOT NULL DEFAULT 0, requests INTEGER NOT NULL DEFAULT 1, input_tokens INTEGER NOT NULL DEFAULT 0, cached_tokens INTEGER NOT NULL DEFAULT 0, output_tokens INTEGER NOT NULL DEFAULT 0, input_cost REAL NOT NULL DEFAULT 0, cached_cost REAL NOT NULL DEFAULT 0, output_cost REAL NOT NULL DEFAULT 0, cost REAL NOT NULL DEFAULT 0, savings REAL NOT NULL DEFAULT 0); CREATE INDEX calls_day ON calls(day); CREATE INDEX calls_session ON calls(session_id); CREATE TABLE daily( day TEXT NOT NULL, provider TEXT NOT NULL, model TEXT NOT NULL, purpose TEXT NOT NULL, requests INTEGER NOT NULL DEFAULT 0, errors INTEGER NOT NULL DEFAULT 0, input_tokens INTEGER NOT NULL DEFAULT 0, cached_tokens INTEGER NOT NULL DEFAULT 0, output_tokens INTEGER NOT NULL DEFAULT 0, cost REAL NOT NULL DEFAULT 0, savings REAL NOT NULL DEFAULT 0, PRIMARY KEY(day, provider, model, purpose)); CREATE TABLE meta(key TEXT PRIMARY KEY, value TEXT); CREATE VIEW usage AS SELECT day, provider, model, purpose, requests, CASE WHEN status = 'error' THEN requests ELSE 0 END AS errors, input_tokens, cached_tokens, output_tokens, cost, savings FROM calls UNION ALL SELECT day, provider, model, purpose, requests, errors, input_tokens, cached_tokens, output_tokens, cost, savings FROM daily; zSUM(requests) AS requests, SUM(errors) AS errors, SUM(input_tokens) AS input_tokens, SUM(cached_tokens) AS cached_tokens, SUM(output_tokens) AS output_tokens, SUM(cost) AS cost, SUM(savings) AS savingsc |adS)z*Tests point the store at a temporary file.N)_override_path)paths `/Users/chudy/Library/Application Support/Kodi/addons/plugin.video.milionar/wcs/ai/usage_store.pyset_pathr :s NNNc4ddl}|dS)Nrz3special://profile/addon_data/plugin.video.milionar/)xbmcvfs translatePath)rs r _profile_dirr@s NNN  !V W WWrcrtp0tjt t SN)r osr joinrDB_NAMErr db_pathrEs!  BRW\\,..'BBBrctjtjt t Sr)rr rdirnamer LEGACY_JSONrrr legacy_json_pathrIs* 7<< 22K @ @@rc#Kt}tjtj|dt t j|d5}t j|_ | dt| |V| n##t$r|wxYw ddddS#1swxYwYdS)zOA connection with the schema ready; commits on success, rolls back on an error.T)exist_ok)timeoutzPRAGMA journal_mode=WALN)rrmakedirsr rrsqlite3connectRow row_factoryexecute_ensure_schemacommit Exceptionrollback)r cons r _dbr-Ms) 99DK%%5555 q111 2 2 c!+ -...s III JJLLLL    LLNNN                     s*'6C(B76C(7 CC((C,/C,cJ|ddtkrdS|tt ||dt|dS)NzPRAGMA user_versionrzPRAGMA user_version = {})r'fetchoneSCHEMA_VERSION executescriptSCHEMA_import_legacyformatr))r,s r r(r(^s {{())2244Q7>IIf3KK*11.AABBBJJLLLLLrc>tjSr)datetimedatetodayrrr _todayr9gs =    rcgg}}|r*|d|||r*|d|||rdd|znd|fS)zaWHERE clause and parameters for an inclusive range of days (YYYY-MM-DD); None leaves a side open.zday >= ?zday <= ?z WHERE z AND )appendr)sinceuntilclausesparamss r _rangesrAks"VG z""" e z""" e29 AI W-- - -r6 IIrr;okc|ptj}| | z|z}t5}|d||||||pd|pd||pdddt |pd|| | | | | |||f}|jcdddS#1swxYwYdS)z#Stores one AI call; returns its id.zINSERT INTO calls(ts, day, provider, model, purpose, session_id, title, status, error, latency_ms, requests, input_tokens, cached_tokens, output_tokens, input_cost, cached_cost, output_cost, cost, savings) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)r;N,r)r6nowr-r' isoformatr7int lastrowid)providermodelpurpose session_idtitlestatuserror latency_msrequests input_tokens cached_tokens output_tokens input_cost cached_cost output_costsavingsrFcostr,cursors r record_callr\xs0  ("&&((C  #k 1D  # <]]__chhjj2244hwPZP`^`bgbmkmoukr4C4 #joA"6"6,P]_l k4 B CC                  sBCC C cj|p t}|jdz|jzdz |z }tj|dz|dzdzd}t 5}|d|f|d|fjcdddS#1swxYwYdS)zmRolls the calls older than `months` months up into `daily` and removes them; returns how many were rolled up.rraINSERT INTO daily(day, provider, model, purpose, requests, errors, input_tokens, cached_tokens, output_tokens, cost, savings) SELECT day, provider, model, purpose, SUM(requests), SUM(CASE WHEN status = 'error' THEN requests ELSE 0 END), SUM(input_tokens), SUM(cached_tokens), SUM(output_tokens), SUM(cost), SUM(savings) FROM calls WHERE day < ? GROUP BY day, provider, model, purpose ON CONFLICT(day, provider, model, purpose) DO UPDATE SET requests = requests + excluded.requests, errors = errors + excluded.errors, input_tokens = input_tokens + excluded.input_tokens, cached_tokens = cached_tokens + excluded.cached_tokens, output_tokens = output_tokens + excluded.output_tokens, cost = cost + excluded.cost, savings = savings + excluded.savingszDELETE FROM calls WHERE day < ?N) r9yearmonthr6r7rGr-r'rowcount)r8months month_indexcutoffr,s r prunerds  VXXE*r/EK/!3f.BA F F P P R RF  R#  PSYQZ \ \ \{{i|]}||dvr |pdn|S))rJrKrLdayrr.0keyrgs r z_row..s9 { { {ilCC/V$V$V#c(-a\_`c\d { { {rkeys)rgs`r _rowrrs* { { { {pspxpxpzpz { { {{rc t||\}}t5}t|dt ||cdddS#1swxYwYdS)NzSELECT {} FROM usage{})rAr-rrr'r4_SUMSr/)r=r>wherer@r,s r totalsrvsE5))ME6 c#CKK 8 ? ?u M MvVV__aabbccccccccccccccccccsAA==BBc vt||\}}t5}d|dt||D}dddn #1swxYwYt j|t j|}}g}t||z j dzD]k} |t j | z } | | | pt| dddddddl|S) zVOne entry for every day of the range, empty days included (a chart wants all of them).c:i|]}|dt|Srkrrrmrgs r rozper_day..s6VVV3UT#YYVVVrz(SELECT day, {} FROM usage{} GROUP BY dayNr)daysrrC)rkrRerrorsrSrTrUrZrY)rAr-r'r4rtr6r7 fromisoformatranger| timedeltarGr<getdict) r=r>rur@r,foundfirstlastr|offsetrks r per_dayrsE5))ME6 V#VV#++ 6 = =eU K KV3U3UVVVVVVVVVVVVVVVVVV---e44hm6Q6QRW6X6X4E D+a/00TTx)v6666AACC EIIcNNSdsQqWXhi9:c'S'S'S T T T T Ks:A((A,/A,ct5}|dt|f}dddn #1swxYwYd|DS)Nz[SELECT substr(day, 1, 7) AS month, {} FROM usage GROUP BY month ORDER BY month DESC LIMIT ?cPg|]"fdD#S)c"i|] }|| Srrrls r roz(per_month... 1 1 1sS#c( 1 1 1rrpr{s @r zper_month..6 B B Bc 1 1 1 1chhjj 1 1 1 B B Br)r-r'r4rtfetchall)limitr,rowss r per_monthrs 0#{{xAFGG!8%%%-XZZ 000000000000000 C BT B B BBsAAA!$A! cost DESCc t||\}}t5}d|d|t|||DcdddS#1swxYwYdS)Nc,g|]}t|Srrzr{s r rz_grouped..s0NNNcS NNNrzHSELECT {cols}, {sums} FROM usage{where} GROUP BY {cols} ORDER BY {order})colssumsruorder)rAr-r'r4rt)columnsr=r>rrur@r,s r _groupedrsE5))ME6 N#NNS[[ V ] ]5U ^ D DEK&M&MNNNNNNNNNNNNNNNNNNNNNss r per_providerrs Ju - --rc$td||S)Nzprovider, modelrrs r per_modelrs %ue 4 44rc$td||S)NrLrrs r per_purposers Iue , ,,rrEc t||\}}t5}|d|||gz}dddn #1swxYwYd|DS)zNThe history: a game (all calls of one session) or a single call, newest first.a)SELECT CASE WHEN session_id = '' THEN 'c:' || id ELSE 's:' || session_id END AS key, MAX(ts) AS last_ts, MIN(ts) AS first_ts, MAX(title) AS title, MAX(purpose) AS purpose, MAX(provider) AS provider, MAX(model) AS model, SUM(requests) AS requests, SUM(CASE WHEN status = 'error' THEN requests ELSE 0 END) AS errors, SUM(input_tokens) AS input_tokens, SUM(cached_tokens) AS cached_tokens, SUM(output_tokens) AS output_tokens, SUM(cost) AS cost, SUM(savings) AS savings, SUM(latency_ms) AS latency_ms FROM calls{} GROUP BY key ORDER BY last_ts DESC LIMIT ?NcPg|]"fdD#S)c"i|] }|| Srrrls r roz'sessions...rrrpr{s @r rzsessions..rr)rAr-r'r4r)r=r>rrur@r,rs r sessionsrsE5))ME6 r#{{ F GMfUmmU[_d^eUe gg hpgogqgq rrrrrrrrrrrrrrr C BT B B BBsAA..A25A2c ,t5}|dr |d|ddf}n,|dt|ddf}d|DcdddS#1swxYwYdS)z>The calls of a history entry (`sessions()` key), oldest first.s:z4SELECT * FROM calls WHERE session_id = ? ORDER BY tsNz SELECT * FROM calls WHERE id = ?cPg|]"fdD#S)c"i|] }|| Srr)rmkrgs r roz'calls_of...s///qCF///rrpr{s @r rzcalls_of..s6@@@C////CHHJJ///@@@r)r- startswithr'rH)rnr,rs r calls_ofrs A# >>$   T;;UX[\]\^\^X_WabbDD;;ACABBLL?SSD@@4@@@ AAAAAAAAAAAAAAAAAAsA-B  B B ct5}|d}dddn #1swxYwY|dr|d|dfndS)z.(first day, last day) with any usage, or None.z5SELECT MIN(day) AS first, MAX(day) AS last FROM usageNrr)r-r'r/)r,rgs r spanrs ^#kkQRR[[]]^^^^^^^^^^^^^^^*-g, @CL#f+ & &D@s(AA AcX|dkr>|drdd|ddffSddt|ddffS|dkrddt|ffS|dkrd d |ffS|d krd d |ffS|d krd d |ffS|dkrdStd|)zY(WHERE for `calls`, WHERE for `daily` or None when only `calls` can hold it, parameters).entryrzsession_id = ?Nrzid = ?callrkzday = ?r_zsubstr(day, 1, 7) = ?rJz provider = ?all)1 = 1rrzunknown delete scope: {})rrH ValueErrorr4)kindrfs r _scopers w   D ! ! 8#TE!""I<7 7E!""I000 v~~E },, u}})eX-- w&(?%II z~x77 u}}## /66t<< = ==rct||\}}}t5}|d||}|r;|d||nd}dddn #1swxYwY|d|r|dndz|d|r|dndz|d|r|dndzd S) zoWhat deleting a scope would remove: {'records': rows (calls + rolled-up days), 'requests': calls, 'cost': USD}.zpSELECT COUNT(*) AS n, COALESCE(SUM(requests), 0) AS requests, COALESCE(SUM(cost), 0) AS cost FROM calls WHERE {}zpSELECT COUNT(*) AS n, COALESCE(SUM(requests), 0) AS requests, COALESCE(SUM(cost), 0) AS cost FROM daily WHERE {}NnrrRrZrC)recordsrRrZ)rr-r'r4r/)rrf calls_where daily_wherer@r,callsr|s r preview_deletersq'-dE':':$Kf r# 228&2E2EvOOOWxzz ]hrs{{117 1D1DfNNNVhjjjmq rrrrrrrrrrrrrrr Sz$%=T#YYA>ER\L]uybAaefpaqaq@AMB&MT%BT&\\sC E EEsA;B**B.1B.c@t||\}}}t5}|d||j}|r1||d||jz }|cdddS#1swxYwYdS)zERemoves a scope from both tables; returns how many rows were removed.zDELETE FROM calls WHERE {}zDELETE FROM daily WHERE {}N)rr-r'r4r`)rrfrrr@r,removeds r deleters'-dE':':$Kf #++:AA+NNPVWW`  f s{{#?#F#F{#S#SU[\\e eG sA#BBBc"td|S)Nrr)rns r delete_entryr$s '3  rc"td|S)Nrr)call_ids r delete_callr(s &' " ""rc"td|S)Nrkrrys r delete_dayr,s %  rc"td|S)Nr_r)r_s r delete_monthr0s '5 ! !!rc"td|Srr)rJs r delete_providerr4s *h ' ''rc tdS)Nrrrrr delete_allr8s %==r)tsrJrKrLrNrOrPrQrRrSrTrUrZrYct5}|ddt}|d}dddn #1swxYwYt |ddd5}tj|}| tt|t|zD](| fd tD) dddn #1swxYwYt|t|zS) zNWrites every call (and the rolled-up days) as CSV; returns the number of rows.z SELECT {} FROM calls ORDER BY tsz, zSELECT day AS ts, provider, model, purpose, '' AS title, 'summary' AS status, '' AS error, 0 AS latency_ms, requests, input_tokens, cached_tokens, output_tokens, cost, savings FROM daily ORDER BY dayNwutf-8r;)encodingnewlinecg|];}t|trt|dn|.LsYDDDjpjVe6T6TeU3v;222Z]^dZeDDDr) r-r'r4rEXPORT_COLUMNSropencsvwriterwriterowlistlen)r r,rdailyhandlerrgs @r export_csvrAs v# >EEdiiP^F_F_``aajjll  jkkkskskuku vvvvvvvvvvvvvvv dC'2 6 6 6E&F##''';;e, E EC OODDDDuCDDD E E E E EEEEEEEEEEEEEEEE u::E ""s%A:BBB4A7D88D<?D<ct}tj|sdS t |d5}t j|}dddn #1swxYwYn#ttf$rYdSwxYwtd}| dgD]}| dpd}| dd| dd}}|r|r|sMtd t| d pd } ||dd ||| d pd | dpd| | dd| dd| dd| ddf } | d| |||f} t| | dd| dd| dd| ddfD]\} } | | xx| z cc<| dpddd pt}| diD]t\}}| diD]D\}}|||f}td| dd|dz td| dd|d z td| dd|dz td| dd|dz td| dd|dz g}|ds |d dkrB| d|||t$|d|d|d|d|d f Fv tj||d zdS#t$rYdSwxYw)!Nr)rc gdS)N)rrCrrrrrrr z _import_legacy..Zs #4#4#4r call_history timestampr;rJrKrinference_count request_typeotherrMrSrrTrUrZrCzINSERT INTO calls(ts, day, provider, model, purpose, session_id, requests, input_tokens, cached_tokens, output_tokens, cost) VALUES(?,?,?,?,?,?,?,?,?,?,?)created providersmodelsrR total_costrg& .>zINSERT INTO daily(day, provider, model, purpose, requests, input_tokens, cached_tokens, output_tokens, cost) VALUES(?,?,?,?,?,?,?,?,?)z .migrated)rrr existsrjsonloadOSErrorrrrmaxrHr' enumerater9rGitemsLEGACY_PURPOSEreplace)r,r rdataimportedrecordstamprJrKrRvaluestotalindexrfrk provider_data model_datadonerests r r3r3Qs   D 7>>$   $ ) ) ) %V9V$$D % % % % % % % % % % % % % % % Z 4455H((>2.."" ;''-2 **Z44fjj"6M6M% ( u  q#fjj):;;@qAABBssXufjj6P6P6[T[]c]g]ght]u]u]{y{FJJ~q996::oWX;Y;Y[a[e[efuwx[y[y**VS))+ IJP R R R(E*+%xFC1H1H&**UcefJgJg'-zz/1'E'EvzzRacdGeGe'ghh " "LE5 %LLLE !LLLL " 88I   $"crc * Bfhh.@.@.B.BC#'88K#<#<#B#B#D#D q q-!.!2!28R!@!@!F!F!H!H q q E:Xu-.D:>>*a8847BCCSjnn]iknNoNorvwxryNyEzEz:>>.!<>/1==QGHHJDAw q$q'D.. M (E>47DQRGUYZ[U\^bcd^egklmgnoqqq  q  4 +,,,,,      sGA/A# A/#A''A/*A'+A//BB5Q QQ)r;r;rBr;rrrrrrCrCrCrCNr)NN)r)r)NNrE)5__doc__rr6rrr# collectionsr contextlibrrr0rrRETENTION_MONTHSrr r2rtr rrrr-r(r9rAr\rdrhrrrvrrrrrrrrrrrrrrrrrrrrr3rrr rs!  ######........ #  8 @ XXX CCCAAA    !!! J J Jrsor!%    "-RRRR$|||cccc   CCCCNNNN....5555---- C C C CAAAAAA>>>& E E E E   ###"""((( W # # # ' ' ' ' ' r