tj cTUdZddlmZddlZddlZddlZddlmZddlmZddl m Z m Z ddl m Z daded <d0d Zd1d Zd2d ZGd dZGddZdaedj/Zd3dZdZd4dZdZd5dZd4dZ d6dZd7d8dZ d9dZ!ddddddd d:dZ" d; dd Z%d!d"d?d#Z&ddd$d% d@d&Z'dAd'Z(dBd(Z)dCd)Z*dDdEd*Z+dFdGd+Z,dHd,Z-dId-Z.dId.Z/dId/Z0y)JuDatabase 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_PATHctjxsdj}|jdxs|jdSNz postgres://z postgresql://)r database_urlstrip startswithurls (/var/www/html/CA-Chatbot/app/database.py is_postgresrs: > & & ," 3 3 5C >>- ( KCNN?,KKctDt}|jjdz atjj ddtS)Nz chatbot.dbT)parentsexist_ok)r rchroma_persist_dirparentmkdir)settingss r_db_pathrsA>..55 DdT: Orc2ts|Stjd|rKtjdd|}d|j vr"|j j ddz}|j 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)sqls r _adapt_sqlr'#s =  xx:C@ff?QTU  +**,%%c*-FFC ++c4 C &&c "C Jrc eZdZddZdZdZy) _PgCursorcD||_d|_t|dd|_y)Nrowcount)_cur lastrowidgetattrr+)selfcurs r__init__z_PgCursor.__init__3s  %)Z4 rc6|jjSN)r-fetchoner0s rr5z_PgCursor.fetchone8yy!!##rc6|jjSr4)r-fetchallr6s rr9z_PgCursor.fetchall;r7rN)r1r)__name__ __module__ __qualname__r2r5r9rrr)r)2s5 $$rr)cPeZdZdZddd dZd d dZddZddZddZdd Z dd Z y) _PgConnectionz;Minimal sqlite3.Connection-compatible wrapper over psycopg.Npoolc ||_||_yr4)_conn_pool)r0connrAs rr2z_PgConnection.__init__Bs  rc\t|}| t|nd}d}tjd|rd|j vrsd|j vratjd|}|r|j dj nd}|d vr$|jjd d z}d }|jj||}t|}|r:|j} | r(tt| 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_logrz RETURNING idTidr)r'tupler r!r#grouplowerr$rCexecuter)r5int isinstancedictr.r+) r0r&paramsadapted returningtable_mtabler1wrappedrows rrOz_PgConnection.executeFsS/"("4v" HH4g >7==?2( ?hh@'JG18W]]1%++-bE11!..*11#6H jj  &1C. ,,.C$'ZT5JD PSTUPV$W!<<rctjdd|tj}|jdD]&}|j }|s|j |(y)Nz--.*?$r )flagsr)r r"MsplitrrO)r0scriptcleanedstmts r executescriptz_PgConnection.executescript_sG&&Bbdd;MM#&D::D <<>D Z  T    Ts!AA66A99B ctr't}|j}t||St j t t}tj|_ |jd|S)zEReturn a connection (Postgres or SQLite) usable as a context manager.r@zPRAGMA journal_mode=WAL) rrrr?sqlite3connectrorRowr~rO)rArawrEs rget_connectionrsX}~llnSt,, ??3xz? +D{{DLL*+ Krcbtrk|jd|fj}t}|D]9}t |t r|dn|d}|s |j t|;|S|jd|dj}|Dchc]}|d c}Scc}w)Nz SELECT column_name AS name FROM information_schema.columns WHERE table_schema = 'public' AND lower(table_name) = lower(?) namerzPRAGMA table_info()rH)rrOr9setrQrRaddro)rErWrowsoutrrs r table_columnsrs}||  H   (* A *1d 31V91DD " <<,UG15 6 ? ? AD $QAaD$  s B,ct}|rdnd}t5}|jd|d|dt|d}dD]}||vs|j d|d t|d }d |vr|j d  |r|j d n|j ddddy#t $rYwxYw#1swYyxYw)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); rI)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 != '' )rrrarrOr)pgid_typerEfb_colscol sess_colss rinit_dbrs B)+%1TG  T $I &%I & , . b j1 C'! ?uEJK "$ 3 ) # LLE F   ]  j   k  s/.C 8C%B11 B=:C<B==CC c tjsyt5}|jd|||||t f}|j xsd}ddd|r t ||txsdS#1swY'xYw)Nrz{INSERT INTO audit_log(session_id, question, answer, citations, duration_ms, created_at) VALUES (?,?,?,?,?,?)) title_hint)rpersist_user_datarrO_nowr. touch_sessionrP)questionanswercitations_json duration_ms session_idrEr1log_ids r log_queryr.s{ > + +  Tll ' 6>; O  #! jX6 v{   s 1A77Bct5}|jd||fj}|Dcgc] }t|c}cdddScc}w#1swYyxYw)Nz9SELECT * FROM audit_log ORDER BY id DESC LIMIT ? OFFSET ?)rrOr9rR)limitoffsetrErrs r get_audit_logrFs\  T|| G FO  (* "&&AQ&   '  s'AAAAActjsgSt5}|jd|fj }|Dcgc] }t |c}cdddScc}w#1swYyxYw)NzSELECT id, question, answer, created_at, duration_ms FROM audit_log WHERE session_id = ? ORDER BY id ASC)rrrrOr9rR)rrErrs rget_session_messagesrOsn > + +  T|| "M  (* "&&AQ&  '  s&A*A%A*%A**A3)rrrrrrc|dvr tdt} | xstj} t 5}d}| r!|j d| fj }|rMt|tr|dn|d}|j d|||||||||| | | |f t|cdddS|j d|||||||||| | | | | f}t|jxsdcdddS#1swYyxYw) z:Persist thumbs up/down for model training (always writes).>updownzrating must be 'up' or 'down'Nz,SELECT id FROM feedback WHERE message_id = ?rKraUPDATE 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_modelrrOr5rQrRrPr.)rrrrrating correctionrrrrrrnowmodelrEexistingeidr1s r save_feedbackr_s8 ^#899 &C  5,.55E  T ||> hj  $.x$>(4.HQKC LL ! 6s8K  Nll 9    .3==%A&}   sA+C318C33C<c,g}g}|r"|jd|j||"|jd|j||r"|jd|j||r"|jd|j||rddj|znd}t5} | jd|d g|||j } | D cgc] } t | c} cdddScc} w#1swYyxYw) Nz rating = ?z reviewed = ? org_id = ? agent_id = ?WHERE  AND r zSELECT * FROM feedback z" ORDER BY id DESC LIMIT ? OFFSET ?)appendjoinrrOr9rR) rrrreviewedrrclausesrSwhererErrs r get_feedbackrsGF |$ f~& h |$ f~& h29X W- -rE  T||%eW,N O $f $e $V $  (* "&&AQ&   '  s70D 'D9D D  D)rrc zg}g}|r"|jd|j||r"|jd|j||rddj|znd}t5}|jd|d|j }|sddddd cd d d St |}t |jd xsdt |jd xsdt |jd xsdt |jdxsdd cd d d S#1swYy xYw)z>Return {up, down, total, with_correction} for scoped feedback.rrrrr a 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)rrrrOr5rRrPget)rrrrSrrErds rfeedback_rating_countsrs% GF |$ f~& h29X W- -rE  T|| G      (* QqI   JaeeJ',1-l+0q1w,1-"155):#;#@qA  !   s/-D1&BD11D:c t5}|jd|fj}dddi}D]}t|}t |j dxsd}|s/t |j dxsdt |j dxsdt |j dxsdt |j d xsdd ||<|S#1swYxYw) 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 Nrr rrrrrr)rrOr9rRrorrP)rrErrrYraids rfeedback_counts_by_agentrs  T|| I   (*  &(C I!%% #)r* aeeJ',1-l+0q1w,1-"155):#;#@qA  C  J7  s "C&&C/daysc ddlm}m}tdt t |d}|j ||dz z }|j}t5}|jd||fj}dddDcic]>}t|jdstt|dt|@} }g} t|D]}} ||| z} | j} | j| i}| j| t |jd xsdt |jd xsdd | S#1swYxYwcc}w) z5Daily thumbs up/down for the last N days (inclusive).r)date timedeltarHr|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 Ndayrr)rrr)rrrmaxminrPtoday isoformatrrOr9rRrroranger)rrrrstartcutoffrErrby_dayrirkeyrYs rfeedback_daily_countsrsD( q#c$i$ %D JJL9$(3 3E __ F  T|| V    (*  59 ODqDGKK + +  T   (* fC'1#t'<#l##a&CLLghj <<^hj  Hhj '1D&AIcNyQR|E+0Zt5LeJ'_dSXYZS[jpG.3 5$8Ou\*bgV[\]V^mpH,0Zd5Kd<(]aRVWXRYgjG} I.w77ER  -.w77ER3>|| 8H; < (*= >"&&AQ&S  R'S  sFG<GGGG(ct5}|jd|f|jd|f|jd|fdddy#1swYyxYw)Nz*DELETE FROM audit_log WHERE session_id = ?z)DELETE FROM feedback WHERE session_id = ?z!DELETE FROM sessions WHERE id = ?)rrO)rrEs rdelete_conversationrsI  T AJ=Q @:-P 8:-H   s :AAc<ttjSr4)rouuiduuid4r=rrnew_session_idrs tzz| rctrdSdS)Npostgressqlite)rr=rr db_backendrs$:4H4rcPtjjddzS)Nseconds)timespecZ)rutcnowrr=rrrrs" ??  & & & :S @@r)rpbool)rpr)r&rorpro)rrorprorr)rWrorpzset[str]) rrorrorrorrPr str | NonerprP)dr)rrPrrPrp list[dict])rrorpr()r int | Nonerr&rrorrorrorr&rr&rr&rr&rr&rr&rr&rprP)r'rNNNN)rrPrrPrr&rr)rr&rr&rpr()rr&rr&rpzdict[str, int])rrorpzdict[str, dict[str, int]])rrorrPrpzlist[dict[str, int | str]])rr&rr&rrPrpr()rrPrrPrprq)rrPrr&rprq)rrorpror4)rrorr&rprq)()rrPrpr()rrorprq)rpro)1rs __future__rr rrrpathlibrtypingrr app.configrr __annotations__rrr'r)r?r __import__Lockrrwrrrrrrrrrrrrrrrrrrrrrrrr=rrr2s~ #   #+L   $ $;;| ;',,. .   (Y|      0' '0!!T' T'T'T'  T'  T'  T' T'T'T'T' T'T' T'p ' ' ' ' '  '  ''B$ $ $  $ N@78"N Q QQ  Q  Q  @L'^I5Ar