h9!ddlmZmZmZddlZddlmZddlm Z m Z ddl m Z ddl mZmZmZmZmZddlmZmZmZGdd eZGd d eZGd d Zy))DictAnyOptionalN)Field) create_enginetext)Engine) AtomicAgent AgentConfig BaseIOSchemaBaseToolBaseToolConfig) ChatHistorySystemPromptGeneratorBaseDynamicContextProviderc^eZdZUdZeddZeed<eddZe e ee fed<y) SQLQueryInputSchemazGSchema for user input requiring SQL query generation and summarization..z!User's query in natural language. description user_inputNzJOptional dictionary of filtering conditions, passed as context to the LLM.additional_filters) __name__ __module__ __qualname____doc__rrstr__annotations__rrrr8/var/www/html/hubwallet-dev/src/core/chatbot_SQLAgent.pyrr s<QC-PQJQ38 f4c3h0rrcleZdZUdZeddZeed<eddZeed<eddZ eed <y ) SQLQueryOutputSchemaz Schema for summarizing the query results. Attributes: summary: A concise and structured summary of the records as per user requirements. .zThe generated SQL query.rgenerated_queryz8Preview of retrieved records in pandas DataFrame format.dataframe_previewz+A summarized version of the retrieved data.summaryN) rrrrrr#rrr$r%rrr r"r"s@ !2LMOSM"34noso*WXGSXrr"cleZdZd dededefdZdedejfdZ d d ed e e ee fde fd Zy) SQLQueryAgent db_enginemodel temperaturecd}tddd|ggdddg}t|||| }tttf| |_||_y ) aa Initialize the SQLQueryAgent with schema constraints. Args: client: The LLM client (e.g., from instructor.from_openai). db_engine: SQLAlchemy database engine for connecting to PostgreSQL. model: The identifier for the model to use. temperature: Sampling temperature for the LLM. a_ Your task is to generate optimized SQL queries based on the following database schema: Table: feedbacks - feedback_id (Integer, PK) - store_id (Integer, FK -> stores.store_id) - datasource_id (Integer, FK -> datasource.ds_id) - branch_id (Integer, FK -> branches.branch_id) - customer_name (String) - feedback_posting_date (DateTime) - feedback_source (String) # e.g., Google, Yelp, Hubwallet - feedback_rating (Integer) # Rating between 1-5 - feedback_type (String) # e.g., text, voice - original_content (Text) # Raw feedback data - transcription (Text) # If voice feedback is provided - sentiment (String) # positive, neutral, negative, mixed - confidence_score (Float) - emotion (String) # e.g., frustration, happiness, disappointment - arousal (String) # passive, active - created_at (DateTime) Table: emotions - emotion_id (Integer, PK) - store_id (Integer, FK -> stores.store_id) - datasource_id (Integer, FK -> datasource.ds_id) - branch_id (Integer, FK -> branches.branch_id) - feedback_id (Integer, FK -> feedbacks.feedback_id) - emotions (String) # e.g., happiness, sadness, anger - created_at (DateTime) Table: words - word_id (Integer, PK) - store_id (Integer, FK -> stores.store_id) - datasource_id (Integer, FK -> datasource.ds_id) - branch_id (Integer, FK -> branches.branch_id) - feedback_id (Integer, FK -> feedbacks.feedback_id) - words (String) - sentiment (String) # positive, neutral, negative, mixed - created_at (DateTime) Table: review_topics - rt_id (Integer, PK) - store_id (Integer, FK -> stores.store_id) - datasource_id (Integer, FK -> datasource.ds_id) - branch_id (Integer, FK -> branches.branch_id) - feedback_id (Integer, FK -> feedbacks.feedback_id) - topic_id (Integer, FK -> topics.topic_id) - topic_name (String) # e.g., service, food, ambiance - topic_sentiment (String) # positive, neutral, negative, mixed - created_at (DateTime) -- All queries must include a WHERE clause to filter by branch_id. -- branch_id should always be included in every SQL query. z;You are an expert SQL query generator and database analyst.zDYou understand database schemas and can write optimized SQL queries.z5You help users extract insights from structured data.)z+Receive the user query in natural language.zNAnalyze additional filters provided and include them in the query if relevant.zNGenerate a structured SQL query using the appropriate schema for the database.z?Ensure the query always includes a WHERE clause with branch_id.zJExecute the SQL query on the PostgreSQL database and retrieve the records.z>Convert the retrieved data into a structured pandas DataFrame.zSummarize the extracted records into a concise, structured response for the user. Do not mention branch_id, any SQL queires or any techinal term in the summaryziReturn only a JSON object with the following keys: 'generated_query', 'dataframe_preview', and 'summary'.zEnsure the summary is user-friendly, concise, and includes key insights from the retrieved data. Do not mention branch_id, any SQL queires or any techinal term in the summary) backgroundstepsoutput_instructions)clientr)r*system_prompt_generator)configN)rr r rr"agentr()selfr/r(r)r*database_schemar0r1s r __init__zSQLQueryAgent.__init__!sx5 n#8MVG  |A!!# ,#$;   !!45I!IJRXY "rqueryreturnc|jj5}|jt|}|j }|j }t j||cdddS#1swYyxYw)z Execute the generated SQL query on PostgreSQL and return results as a pandas DataFrame. Args: query: The SQL query to be executed. Returns: A pandas DataFrame containing the retrieved records. )columnsN)r(connectexecuterfetchallkeyspd DataFrame)r3r6 connectionresultrecordsr9s r execute_queryzSQLQueryAgent.execute_querysd^^ # # % :''U 4Foo'GkkmG<<9  : : :s AA66A?Nrrct||}|jj|}|j}|j |}|j j d}d|j dd|jd}|jjt|} t||| jS) ah Process user input, generate an SQL query, execute it, retrieve results, and summarize. Args: user_input: The user's query in natural language. additional_filters: Optional dictionary of filters, passed to the LLM. Returns: An instance of SQLQueryOutputSchema containing only the summary. )rrF)indexz!Summarize the following dataset: z as per user's question: zJ. IMPORTANT: The answer to the user's question is in the provided dataset.)r)r#r$r%) rr2runr#rChead to_stringrr"r%) r3rr input_datarAr#dfr$ summary_inputsummary_results r process_queryzSQLQueryAgent.process_querys)JSef  + 00   0GGI//e/<=R\\PU\=Vr?rCrrrr"rMrrr r'r' sba#&a#a#[`a#F:3:2<<:   $sTWx.AY ey rr')typingrrrpandasr>pydanticr sqlalchemyrrsqlalchemy.enginer atomic_agentsr r r r ratomic_agents.contextrrrrr"r'rrr rVsL&&*$[Z``, Y< YS S r