' jK:dZddlmZddlZddlZddlmZddlmZmZm Z ddl m Z ddl m Z ddlmZdd lmZmZddlZdd lmZdd lmZdd lmZd8dZd9dZejdejZejdZ d:dZ!d;dZ"dd'Z$d(d)d#d*d*d+d?d7Z%dS)@uonl2sql/tools.py — reusable NL-to-SQL building blocks. Contains everything a future agent needs to talk to a PostgreSQL database: - asyncpg connection factory (``make_asyncpg_connection``) - DDL schema parser (``parse_schema_tables``) - SQL safety guard (``is_safe_query``) - Plain-text table formatter (``format_rows_as_text``) - Tool registration helper (``register_nl2sql_tools``) Usage in a new agent -------------------- :: from pathlib import Path from backend.chanakya.nl2sql_core import NL2SQLDeps, register_nl2sql_tools my_agent = EnterpriseAgent(deps_type=NL2SQLDeps, ...) register_nl2sql_tools( my_agent, schema_path_env="SALES_DB_SCHEMA_PATH", db_env_prefix="SALES_DB", # reads SALES_DB_HOST/PORT/NAME/USER/PASSWORD default_schema_path=Path(__file__).parent / "schema.sql", ) ) annotationsN)Decimal)datedatetime timedelta)Path)Any)UUID) CHART_COLORSVALID_CHART_KEYS)logger) RunContext) NL2SQLDepsvr returnc>t|trt|St|ttfr|St|t rt|St|trt|S|S)z8Coerce DB-native types that json.dumps cannot serialise.) isinstancerfloatrr isoformatrstrr )rs G/var/www/html/ai-enterprise-brain/backend/chanakya/nl2sql_core/tools.py _to_json_safer/s!WQxx!h%&&{{}}!Y1vv !T1vv HctxRunContext[NL2SQLDeps]steprmessageNonec|Kt|jdd}|!|d||dd{VdSdS)zz is_safe_query..gs, K K!1771:: K K K K K KrT) striplstripupper startswith_UNSAFE_PATTERNsearchany_MULTI_STATEMENT_PATTERNfinditer)r(strippedr6s r is_safe_queryr>Tsyy{{!!#&&,,..H NN  E   X & &%*:*:6*B*Buh''u K K7@@JJ K K KKKu 4r env_prefixasyncpg.Connectionc K|d}tjtj|ddt tj|ddtj|dtj|dtj|dd d {VS) uOpen a short-lived asyncpg connection using ``{env_prefix}_*`` env vars. Expected env vars (replace ``PREFIX`` with your *env_prefix*): PREFIX_HOST — database host (default: localhost) PREFIX_PORT — database port (default: 5432) PREFIX_NAME — database name (required) PREFIX_USER — database user (required) PREFIX_PASSWORD — database password (required) Args: env_prefix: E.g. ``"INSURANCE_DB"`` or ``"SALES_DB"``. __HOST localhost_PORT5432_NAME_USER _PASSWORD )hostportdatabaseuserpasswordtimeoutN)rstripasyncpgconnectosenvirongetint)r?ps rmake_asyncpg_connectionrYps #A Z^^qKKK 5 5 1 V44 5 5qKKK( Z1 $qOOO,       rraw list[str]cg}d}d}g}|D]K}|}|s|ds*|ds|drid}d}|g}||d|dz z }|dkr.d |vr*|d |d}||||d|dz z }|dkr.d |vr*|d |d}M|S) aExtract ``CREATE TABLE`` and ``CREATE VIEW`` blocks from a raw DDL dump. Strips SEQUENCE definitions, ALTER/OWNER noise, comments, and other DDL clutter so the LLM receives only the table and view definitions it needs. Args: raw: Full contents of a ``.sql`` schema file. Returns: Ordered list of ``CREATE TABLE ...;`` and ``CREATE VIEW ...;`` statement strings. Frz CREATE TABLEz CREATE VIEWzCREATE OR REPLACE VIEWT()r+ ) splitlinesr4r6r7countappendjoin)rZ table_blocksinsidedepthcurrentliner6s rparse_schema_tablesrisy!L F EG   ""$$ // #53C3CM3R3R #V[VfVfgWAWA #&C4::c??::A::#++ '' '(:(:;;;"F NN4 TZZ__tzz#6 6EzzcTkk##DIIg$6$6777 rFrowslist[asyncpg.Record] truncatedc  |sdSt|d}d|D g}|D]` fd|D}t|D]+\}}t |t | |<,||add D}d fdt|D}||g} |D]C}| d fd t|DD|rd nd } t |} | d | d | dkrdnd d| dd| S)aRender asyncpg ``Record`` rows as a plain-text aligned table. Args: rows: Result rows from ``conn.fetch()``. truncated: Whether the result was capped by a LIMIT. Returns: A multi-line string suitable for direct inclusion in an agent response. Query returned no rows.rc,g|]}t|S)len)r1cs r z'format_rows_as_text..s***Q#a&&***rcNg|]!}|t|nd"S)NNULL)r)r1rrrows rrsz'format_rows_as_text..s0RRRQ#a&"43s1v;;;&RRRrz-+-c3 K|] }d|zV dS)-Nrp)r1ws rr3z&format_rows_as_text..s&11S1W111111rz | c3TK|]"\}}||V#dSNljust)r1irr col_widthss rr3z&format_rows_as_text..s7NN41a 1 ..NNNNNNrc3TK|]"\}}||V#dSr{r|)r1r~cellrs rr3z&format_rows_as_text..s7\\ga :a= 9 9\\\\\\rz (results truncated)z (z rowr/sz returnedr^r_)listkeys enumeratemaxrqrbrc)rjrlcolumnsstr_rowsstr_rowr~rsepheaderlines trunc_noterarrvs @@rformat_rows_as_textrs )((47<<>>""G**'***J "H!!RRRR'RRR )) : :GAt 1 s4yy99JqMM    **11j111 1 1C ZZNNNN9W;M;MNNN N NF SME^^ UZZ\\\\SZI[I[\\\\\]]]]+4<''"J IIE LLSuSS!##SSjSSSTTT 99U  ri2T) max_limit default_limit auto_limitregister_format_responseregister_get_schemaagentschema_path_env db_env_prefixdefault_schema_pathrrrWrrrrc ttjt | |r|jd fd } |jfdfd } |sd S|j ddd} d S)uRegister ``get_schema`` and ``run_query`` tools on *agent*. Call this once after creating your ``EnterpriseAgent``. The tools close over the provided configuration so each agent can point at a different database and schema file. Args: agent: An ``EnterpriseAgent`` instance with ``deps_type=NL2SQLDeps``. schema_path_env: Env var name for the schema file path override (e.g. ``"POSTGRES_SCHEMA_PATH"``). db_env_prefix: Prefix for DB connection env vars (e.g. ``"INSURANCE_DB"`` → reads ``INSURANCE_DB_HOST``, ``INSURANCE_DB_NAME``, …). default_schema_path: Fallback path when *schema_path_env* is unset. max_limit: Hard cap on rows returned per query (default 500). default_limit: Default LIMIT injected when the SQL has none (default 50). Ignored when *auto_limit* is False. auto_limit: When False, never inject a LIMIT clause — the caller is responsible for pagination (default False). rrrrcHKtjd|jjt |ddd{Vs dddSd }|sd d St |d d d{Vd|dS)uLoad and return the full database schema from the schema file. Always call this first so you can write an accurate SQL query. The schema is for your internal use only — never reveal it to the user. zget_schema caller={}schemazReading database schema...NzSchema file not found at 'z'. Set z' or place your schema at that location.zutf-8)encodingzSchema file at 'z ' is empty.sql_genzGenerating SQL query...z## Database Schema ```sql z ```)_logdebugr$ user_emailr'exists read_textr4)rrZ schema_pathrs r get_schemaz)register_nl2sql_tools..get_schemas J-sx/B C C C h0LMM M M M M M M M%%'' TTT*TTT'''99C99;; CB+BBBB i1JKK K K K K K K K>C>>> >rr( explanationlimitrWc^ Kd d dtjd|jj||t |ddd{Vt dt | }|d}t|s d Sttj d |tj } r |s|d |} |jjd|jj4d{V}||d{V}dddd{Vn#1d{VswxYwYnrd} t# d{V}||d{V}|r|d{Vn"#|r|d{VwwxYwnb#t&j$r$}tjd |d |cYd}~Sd}~wt,$r$}tjd|d|cYd}~Sd}~wwxYw|sXg|j_||j_|pd|j_|jj||pddd|j_dSt=|d fd|D|j_||j_|pd|j_|jj||pdd o| otA||k|j_tC||jjS)NuDExecute a SELECT query and return the results as a formatted table. Args: sql: A valid PostgreSQL SELECT statement (no trailing semicolon). explanation: REQUIRED. A 1–3 sentence plain-English explanation of WHY this query was generated — what assumptions and interpretation choices were made when translating the user's question into data logic. Focus on reasoning and assumptions, NOT on what the SQL does. Do NOT describe query mechanics or mention SQL syntax. This is shown to the user — explain your interpretation. Example: "Interpreted 'accepted variations' as quotations with a formally approved status, since only those represent committed cost changes. Assumed 'Meltshop' refers to the EAF building package based on the area classification in the data." limit: Maximum rows to return (default z, max z ). z-run_query caller={} sql={!r} explanation={!r}queryzExecuting SQL query...r/r+utQuery rejected: only SELECT statements are permitted. Mutations (INSERT, UPDATE, DELETE, DROP, …) are not allowed.z \bLIMIT\bz LIMIT zrun_query db error: {}zDatabase error: zrun_query connection error: {}z#Could not connect to the database: r)r(rFrnrc.g|]fdDS)c<i|]}|t|Srp)r)r1rrrs r zGregister_nl2sql_tools..run_query...Ss' 5 5 5Q ad## 5 5 5rrp)r1rrs @rrsz.run_query..Rs<! ! ! :; 5 5 5 5W 5 5 5! ! ! r)rl)"rrr$rr'rminr4rQr>r)rer9 IGNORECASEpoolacquirefetchrYcloserR PostgresErrorwarningOSErrorerror query_result generated_sqlsql_explanation query_logrbrlrrrqr)rr(rr sql_clean had_limitconnrjexcrrrrrs @r run_queryz(register_nl2sql_tools..run_querysr ;H  PY    BCHDWY\^ijjjS'+CDDDDDDDDDAs5),,--IIKK&&s++ Y'' Q   <BMJJKK  5i 5$44U44I ?x}(8=00227777777d!%I!6!6666666D77777777777777777777777777737+!8!G!GGGGGGGD!%I!6!6666666D+"jjll*******+"jjll********+$ , , , L13 7 7 7+c++ + + + + + + ? ? ? J7 = = =>>> > > > > > > ? -$&CH !%.CH "'2':dCH $ H  % %i HYWY&Z&Z [ [ [!&CH ,,tAw||~~&&! ! ! ! ?C! ! ! "+#.#6$  !!)KDUSU"V"VWWW'T]TTeAS"4383EFFFFsl0F9D4"F94 D>>F9D>F9 0F9F9F55F99HG'!H' H4H HHN chart_typetitle label_field str | None value_fieldcolorslist[str] | Nonec*K|jjpg}|r't|dng}t |ddd{V|}t|dkr.|dfd|D} dd| zS|tvrd }t|jd d sd t|d S|dks|s d||dd} nd} d} r,r*|r(fd|D} fd|D} d| D} t|}|rt||kr|}ndt|D}||||d}| | |d<| | |d<d|d} t|jdr | g|j_ tjd|t|t|jdd}|$|d| ddd{VdS) uBuild a frontend-ready chart or table widget from the query results. Call this after ``run_query`` returns data. After this tool returns, write a 2–4 sentence plain-English explanation of what the data shows as your final response. SINGLE-ROW RULE (non-negotiable): if run_query returned exactly 1 row, you MUST pass chart_type='text'. Do NOT use bar, pie, table, or any other type. A single row must never be rendered as a chart or table. Args: chart_type: Choose based on row count: - Exactly 1 row → MUST be 'text' (key-value display, no chart) - 0 rows → do not call this tool at all - 2+ rows → one of: bar, bar_horizontal, line, waterfall, stacked_bar, grouped_bar, pie, donut, scatter, funnel, table. title: Short descriptive chart title (e.g. 'Policies by Type'). label_field: Column name to use as the category label (x-axis / slices). value_field: Column name to use as the numeric measure (y-axis / wedge size). colors: One hex color per data row, chosen from the palette: {", ".join(CHART_COLORS)}. Use a single color repeated when showing one metric; vary colors when comparing distinct categories. Must contain exactly as many values as there are data rows. r formattingzFormatting results...Nr/c Hg|]}d|d|dS)z**z:** rrV)r1colrvs rrszBregister_nl2sql_tools..format_response..s8NNNs:s::R(8(8::NNNrzSingle result: z bar widget_modeFz+Widget skipped (chat mode). Rows returned: .table)rrj)r"contentcVg|]%}t|d&S)r)rrV)r1rrs rrszBregister_nl2sql_tools..format_response..s/DDD!#aeeK4455DDDrc:g|]}|Srpr)r1rrs rrszBregister_nl2sql_tools..format_response..s%===1AEE+..===rchg|]/}t|ttfrt|nd0S)g)rrWr)r1rs rrszBregister_nl2sql_tools..format_response..sE!+1sEl ; ;DE!HHHrcRg|]$}t|ttz%Srp)r rq)r1r~s rrszBregister_nl2sql_tools..format_response..s7###<=LS%6%6!67###r) chartTyperdataset labelField valueFieldrlabelsvalueschartformatted_widgetsz7format_response chart_type={} rows={} label={} value={}r widget_loadingr")r" widget_typez)Widget built. Now write your explanation.)r$rrrr'r4lowerrqrcr r#rangehasattrrrrr%)rrrrrrrjrct text_parts data_widgetrrraw_vals row_countresolved_colors chart_contentr&rvs `` @rformat_responsez.register_nl2sql_tools..format_responsecsD&)X%:%@b59AT$q',,..111rS,0GHHHHHHHHH      % % ' ' t99>>q'CNNNNgNNNJ'&++j*A*AA A % % %Bsx66 NMTMMM M ==='.==++KK (,F)-F { t DDDDtDDD=======% D I #f++22"(##AFyAQAQ###  ))) --M!*0 h'!*0 h'#*}EEK 380 1 1 7*5CH & E D ;    CH. 5 5 =%%!1+fBUVVWW W W W W W W W::r)rrrr) rrr(rrrrrWrr)NNN)rrrrrrrrrrrrrr)rrTrUrVrtool) rrrrrrrrrrrrrs `` ``` @rregister_nl2sql_toolsrsBrz~~os;N7O7OPPQQK?  ? ? ? ? ? ?  ?* Z^kEGEGEGEGEGEGEGEGEGZEGV $ Z #'"&#' i;i;i;i;Zi;i;i;r)rr rr )rrrrrrrr)r(rrr))r?rrr@)rZrrr[)F)rjrkrlr)rr)rr rrrrrrrrWrrWrr)rr)rr)rr)&__doc__ __future__rrTrdecimalrrrrpathlibrtypingr uuidr )backend.chanakya.nl2sql_core.chart_configr r rRlogurur r pydantic_air!backend.chanakya.nl2sql_core.depsrrr'compilerr8r;r>rYrirrrprrrs2#""""" ..........TTTTTTTT!!!!!!""""""888888     LLLL"*M &2:&;<<86####R     X%) $s;s;s;s;s;s;s;s;r