tj cUdZddlmZddlZddlZddlZddlmZddlmZddl m Z m Z ddl m Z daded <dVd ZdWdZdXdZGddZGddZdaedZdYdZdZdZdZdZd[dZdZd Zd\d(Zd]d^d-Z d_d.Z!ddddddd/d`d:Z" dadbd<Z#ddd=dcd?Z$dddAZ%dBdCdedFZ&dddGdHdfdIZ'dgdKZ(dhdLZ)didMZ*djdkdOZ+dldmdQZ,dndRZ-dodSZ.dodTZ/dodUZ0dS)puDatabase layer — Postgres when DATABASE_URL is set, otherwise local SQLite. Supports feedback, audit logs, sessions, and SaaS tables via a thin sqlite-compatible wrapper (`?` placeholders, dict rows, executescript). ) annotationsN)datetime)Path)AnyIterable) get_settingsz Path | None_DB_PATHreturnboolctjpd}|dp|dSNz postgres://z postgresql://)r database_urlstrip startswithurls (/var/www/html/CA-Chatbot/app/database.py is_postgresrsB >> & ," 3 3 5 5C >>- ( ( KCNN?,K,KKrct>t}|jjdz atjddtS)Nz chatbot.dbT)parentsexist_ok)r rchroma_persist_dirparentmkdir)settingss r_db_pathrsA>>.5 DdT::: OrsqlstrcTts|Stjd|rVtjdd|}d|vr*|ddz}|dd}tjdd |}|S) z-Convert SQLite-ish SQL to the active dialect.z'(?is)^\s*INSERT\s+OR\s+IGNORE\s+INTO\s+z INSERT INTO z ON CONFLICT;z ON CONFLICT DO NOTHING%z%%z\?z%s)rrematchsubupperrstripreplace)rs r _adapt_sqlr*#s ==  x:C@@Gf?QTUU  + +**,,%%c**-FFC ++c4 C &c " "C Jrc"eZdZddZdZdZdS) _PgCursorcurrcN||_d|_t|dd|_dS)Nrowcount)_cur lastrowidgetattrr/)selfr-s r__init__z_PgCursor.__init__3s' %)Z44 rc4|jSN)r1fetchoner4s rr8z_PgCursor.fetchone8y!!###rc4|jSr7)r1fetchallr9s rr<z_PgCursor.fetchall;r:rN)r-r)__name__ __module__ __qualname__r5r8r<rrr,r,2sF5555 $$$$$$$$rr,cReZdZdZddddZddd ZddZddZddZddZ ddZ dS) _PgConnectionz;Minimal sqlite3.Connection-compatible wrapper over psycopg.NpoolconnrrDc"||_||_dSr7)_conn_pool)r4rErDs rr5z_PgConnection.__init__Bs  rrr paramsIterable[Any] | Nonect|}|t|nd}d}tjd|rd|vrd|vrptjd|}|r'|dnd}|d vr,|d d z}d }|j ||}t|}|rM| } | r7tt| tr| d n| d|_|j|_|S)Nr@Fz(?is)^\s*INSERT\s+INTO\s+\w+ RETURNINGzON CONFLICT DO NOTHINGz(?is)^\s*INSERT\s+INTO\s+(\w+)r>feedback audit_logr"z RETURNING idTidr)r*tupler$r%r'grouplowerr(rGexecuter,r8int isinstancedictr2r/) r4rrIadapted returningtable_mtabler-wrappedrows rrTz_PgConnection.executeFsMS//"("4v" H4g > > !7==??22( ??h@'JJG18@W]]1%%++---bE111!..**11#66H j  &11C..  X,,..C X$'ZT5J5J(VD PSTUPV$W$W!<rscriptr Nonectjdd|tj}|dD]-}|}|r||.dS)Nz--.*?$r)flagsr")r$r&MsplitrrT)r4r^cleanedstmts r executescriptz_PgConnection.executescript_si&Bbd;;;MM#&& # #D::<r?__doc__r5rTrfrhrkrmrsr@rrrBrB?sEE152#### rrB threadingrc|pd}|drd|tddzS|Sr )rrlenrs r_normalize_pg_urlrysN 9"    C ~~m$$;S%7%7%9%9!::: Jrc "ttSt5ttcdddSddlm}ddlm}t tj}||ddd|dd d atcdddS#1swxYwYdS) uBLazy singleton pool — avoids a new TCP handshake on every query.Nr)dict_row)ConnectionPoolrM ) row_factoryconnect_timeoutT)conninfomin_sizemax_sizetimeoutkwargsopen) _pg_pool _pg_pool_lock psycopg.rowsr{ psycopg_poolr|ryrr)r{r|rs r _get_pg_poolrs    *)))))//////  ;<<!>#+CC    sBABB Br_cftsdSt}|} |d|n##t $r|wxYw ||dS#||wxYw)zMOpen the pool early so the first UI request is not paying connect cost alone.NzSELECT 1)rrgetconnrTrh Exceptionrorj)rDrEs r warm_pg_poolrs == >>D <<>>D Z        T Ts)AB A>>BB0c.tr3t}|}t||St jt t}tj|_ | d|S)zEReturn a connection (Postgres or SQLite) usable as a context manager.rCzPRAGMA journal_mode=WAL) rrrrBsqlite3connectr rRowrrT)rDrawrEs rget_connectionrsr}}-~~llnnSt,,,, ?3xzz?? + +D{DLL*+++ Krr[set[str]ctr|d|f}t}|D]K}t |t r|dn|d}|r"|t|L|S|d|d}d|DS)Nz SELECT column_name AS name FROM information_schema.columns WHERE table_schema = 'public' AND lower(table_name) = lower(?) namerzPRAGMA table_info()ch|] }|d S)rMr@.0rs r z table_columns..s   QAaD   r)rrTr<setrVrWaddr )rEr[rowsoutrrs r table_columnsrs}}||  H    (**  # #A *1d 3 3=1V991D #D """ <<5U555 6 6 ? ? A AD  $   rct}|rdnd}t5}|d|d|dt|d}dD]}||vr|d|d  t|d }d |vr|d  |r|d n|dn#t $rYnwxYwddddS#1swxYwYdS)NzBIGSERIAL PRIMARY KEYz!INTEGER PRIMARY KEY AUTOINCREMENTzP CREATE TABLE IF NOT EXISTS audit_log ( id aI, session_id TEXT, question TEXT NOT NULL, answer TEXT NOT NULL, citations TEXT, duration_ms INTEGER, created_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS feedback ( id a, log_id INTEGER, session_id TEXT, question TEXT NOT NULL, answer TEXT NOT NULL, rating TEXT NOT NULL CHECK(rating IN ('up','down')), correction TEXT, reviewed INTEGER DEFAULT 0, created_at TEXT NOT NULL, org_id TEXT, agent_id TEXT, user_id TEXT, message_id TEXT, mode TEXT, model_name TEXT, updated_at TEXT ); CREATE TABLE IF NOT EXISTS sessions ( id TEXT PRIMARY KEY, title TEXT, created_at TEXT NOT NULL, last_seen TEXT NOT NULL, message_count INTEGER DEFAULT 0 ); CREATE INDEX IF NOT EXISTS idx_audit_session ON audit_log(session_id); CREATE INDEX IF NOT EXISTS idx_feedback_rating ON feedback(rating); CREATE INDEX IF NOT EXISTS idx_feedback_reviewed ON feedback(reviewed); CREATE INDEX IF NOT EXISTS idx_sessions_last_seen ON sessions(last_seen); CREATE INDEX IF NOT EXISTS idx_feedback_org_agent ON feedback(org_id, agent_id); CREATE INDEX IF NOT EXISTS idx_feedback_message ON feedback(message_id); rN)org_idagent_iduser_id message_idmode model_name updated_atz ALTER TABLE feedback ADD COLUMN z TEXTsessionstitlez*ALTER TABLE sessions ADD COLUMN title TEXTz CREATE UNIQUE INDEX IF NOT EXISTS idx_feedback_message_unique ON feedback(message_id) WHERE message_id IS NOT NULL AND message_id <> '' z CREATE UNIQUE INDEX IF NOT EXISTS idx_feedback_message_unique ON feedback(message_id) WHERE message_id IS NOT NULL AND message_id != '' )rrrfrrTr)pgid_typerEfb_colscol sess_colss rinit_dbrs B)+T%%1TG   VT , $, , %, , , . . . b j11 L LC'!! JJJJKKK!$ 33 ) # # LLE F F F        D kVVVVVVVVVVVVVVVVVVs6A8C'-C  C' CC'CC''C+.C+questionanswercitations_json duration_msrU session_id str | Nonec "tjsdSt5}|d|||||t f}|jpd}dddn #1swxYwY|rt ||t|pdS)Nrz{INSERT INTO audit_log(session_id, question, answer, citations, duration_ms, created_at) VALUES (?,?,?,?,?,?)) title_hint)rpersist_user_datarrT_nowr2 touch_sessionrU)rrrrrrEr-log_ids r log_queryr.s >> +q   $Tll ' 6>; O   #! $$$$$$$$$$$$$$$7jX6666 v{  s2A""A&)A&dlimitoffset list[dict]ct5}|d||f}d|DcdddS#1swxYwYdS)Nz9SELECT * FROM audit_log ORDER BY id DESC LIMIT ? OFFSET ?c,g|]}t|Sr@rWrs r z!get_audit_log..L&&&AQ&&&r)rrTr<)rrrErs r get_audit_logrFs   'T|| G FO   (** '&&&& ''''''''''''''''''s6AAActjsgSt5}|d|f}d|DcdddS#1swxYwYdS)NzSELECT id, question, answer, created_at, duration_ms FROM audit_log WHERE session_id = ? ORDER BY id ASCc,g|]}t|Sr@rrs rrz(get_session_messages..Zrr)rrrrTr<)rrErs rget_session_messagesrOs >> +   'T|| "M   (** '&&&&''''''''''''''''''s5A&&A*-A*)rrrrrrr int | Nonerating correctionrrrrrrcV|dvrtdt} | ptj} t 5}d}| r)|d| f}|rct|tr|dn|d}|d|||||||||| | | |f t|cdddS|d|||||||||| | | | | f}t|j pdcdddS#1swxYwYdS) z:Persist thumbs up/down for model training (always writes).>updownzrating must be 'up' or 'down'Nz,SELECT id FROM feedback WHERE message_id = ?rPraUPDATE feedback SET log_id=?, session_id=?, question=?, answer=?, rating=?, correction=?, org_id=COALESCE(?, org_id), agent_id=COALESCE(?, agent_id), user_id=COALESCE(?, user_id), mode=COALESCE(?, mode), model_name=COALESCE(?, model_name), updated_at=?, reviewed=0 WHERE id=?aINSERT INTO feedback( log_id, session_id, question, answer, rating, correction, org_id, agent_id, user_id, message_id, mode, model_name, created_at, updated_at ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)) ValueErrorrr ollama_modelrrTr8rVrWrUr2)rrrrrrrrrrrrnowmodelrEexistingeidr-s r save_feedbackr_s ^##8999 &&C  5,..5E   >'T  ||> hjj   $.x$>$>O(4..HQKC LL !   6s88K>'>'>'>'>'>'>'>'Nll 9     .3=%A&&}>'>'>'>'>'>'>'>'>'>'>'>'>'>'>'>'>'>'sBD9DD"%D"reviewedcpg}g}|r*|d|||*|d|||r*|d|||r*|d|||rdd|znd}t5} | d|d g|||R} d | DcdddS#1swxYwYdS) Nz rating = ?z reviewed = ? org_id = ? agent_id = ?WHERE  AND rzSELECT * FROM feedback z" ORDER BY id DESC LIMIT ? OFFSET ?c,g|]}t|Sr@rrs rrz get_feedback..rr)appendjoinrrTr<) rrrrrrclausesrIwhererErs r get_feedbackrsGF |$$$ f~&&& h |$$$ f ~&&& h29 AX W-- - -rE   'T|| Oe O O O $f $e $V $ $   (** '&&&& ''''''''''''''''''s?D++D/2D/)rrdict[str, int]c g}g}|r*|d|||r*|d|||rdd|znd}t5}|d|d|}|sddddd cd d d St |}t |d pdt |d pdt |d pdt |dpdd cd d d S#1swxYwYd S)z>Return {up, down, total, with_correction} for scoped feedback.rrrrra SELECT COALESCE(SUM(CASE WHEN rating = 'up' THEN 1 ELSE 0 END), 0) AS up_count, COALESCE(SUM(CASE WHEN rating = 'down' THEN 1 ELSE 0 END), 0) AS down_count, COUNT(*) AS total, COALESCE(SUM(CASE WHEN correction IS NOT NULL AND TRIM(correction) != '' THEN 1 ELSE 0 END), 0) AS with_correction FROM feedback z rrrtotalwith_correctionNup_count down_countrr)rrrrTr8rWrUget)rrrrIrrErds rfeedback_rating_countsrs GF |$$$ f ~&&& h29 AX W-- - -rE    T||         (**  JQqII         JJaeeJ'',1--l++0q11w,1--"155):#;#;#@qAA   !                  s5E3 BE33E7:E7dict[str, dict[str, int]]c Bt5}|d|f}dddn #1swxYwYi}|D]}t|}t |dpd}|s8t |dpdt |dpdt |dpdt |d pdd ||<|S) z)Map agent_id -> rating counts for an org.a  SELECT agent_id, COALESCE(SUM(CASE WHEN rating = 'up' THEN 1 ELSE 0 END), 0) AS up_count, COALESCE(SUM(CASE WHEN rating = 'down' THEN 1 ELSE 0 END), 0) AS down_count, COUNT(*) AS total, COALESCE(SUM(CASE WHEN correction IS NOT NULL AND TRIM(correction) != '' THEN 1 ELSE 0 END), 0) AS with_correction FROM feedback WHERE org_id = ? AND agent_id IS NOT NULL AND agent_id != '' GROUP BY agent_id Nrrrrrrrr)rrTr<rWr rrU)rrErrr]raids rfeedback_counts_by_agentrs_   T|| I    (** &(C    II!%% ##)r**  aeeJ'',1--l++0q11w,1--"155):#;#;#@qAA   C Js*AA  A daysrlist[dict[str, int | str]]c ddlm}m}tdt t |d}|||dz z }|}t5}| d||f }dddn #1swxYwYd|D}g} t|D]} ||| z} | } | | i} | | t | d pdt | d pdd | S) z5Daily thumbs up/down for the last N days (inclusive).r)date timedeltarMr~ra SELECT SUBSTR(created_at, 1, 10) AS day, COALESCE(SUM(CASE WHEN rating = 'up' THEN 1 ELSE 0 END), 0) AS up_count, COALESCE(SUM(CASE WHEN rating = 'down' THEN 1 ELSE 0 END), 0) AS down_count FROM feedback WHERE org_id = ? AND SUBSTR(created_at, 1, 10) >= ? GROUP BY SUBSTR(created_at, 1, 10) ORDER BY day ASC Nci|]T}t|d$tt|dt|US)day)rWrr rs r z)feedback_daily_counts..1sI O O OqDGGKK.rr) rrrrTr<rrVrWr8rr)rrEorphanrr]sidfirstlast count_rowcountfirst_qfirst_atlast_atrs rlist_conversationsr-s >> +   I'T    (** ff  C'1#t'<'<H#l###a&CLLghjj <<^hjj  Hhjj '1D&A&ASIcNNyQR|E+0qZt5L5LqeJ''_dSpSXYZS[S[jpG.3q 5$8O8Oqu\**bgVpV[\]V^V^mpH,0kZd5K5Kkd<((]aRjRVWXRYRYgjG}}  I.w777ER  -.w777ER || 8H;  < (**= >'&&&&SI'I'I'I'I'I'I'I'I'I'I'I'I'I'I'I'I'I'sHII I ct5}|d|f|d|f|d|fddddS#1swxYwYdS)Nz*DELETE FROM audit_log WHERE session_id = ?z)DELETE FROM feedback WHERE session_id = ?z!DELETE FROM sessions WHERE id = ?)rrT)rrEs rdelete_conversationr/s   IT AJ=QQQ @:-PPP 8:-HHHIIIIIIIIIIIIIIIIIIsAA""A&)A&cBttjSr7)r uuiduuid4r@rrnew_session_idr3s tz||  rc&trdndS)Npostgressqlite)rr@rr db_backendr7s$ 4::H4rcVtjddzS)Nseconds)timespecZ)rutcnowrr@rrrrs& ?   & & & : :S @@r)r r )r r)rr r r )rr r r rt)r[r r r) rr rr rr rrUrrr rU)rr)rrUrrUr r)rr r r)rrrrrr rr rr rrrrrrrrrrrrrrr rU)rrNNNN)rrUrrUrrrrrrrrr r)rrrrr r)rr r r)rr rrUr r)rrrrrrUr r)rrUrrUr r_)rrUrrr r_)rr r r r7)rr rrr r_)r)rrUr r)rr r r_)r r )1ru __future__rr$rr1rpathlibrtypingrr app.configrr __annotations__rrr*r,rBr __import__Lockrryrrrrrrrrrrrrr r rrrrr-r/r3r7rr@rrrDs #"""""   ######LLLL      $ $ $ $ $ $ $ $;;;;;;;;|  ;'',,.. .           (YYYY|0''''' ' ' ' '0!!T'T'T'T'T'T'p '''''B$ $ $ $ $ $ N@78""""""N QQQQQQ        @L'L'L'L'L'^IIII5555AAAAAAr