Files changed (8) hide show
  1. agents.py +14 -0
  2. app.py +0 -0
  3. app_22082025.py +0 -1289
  4. app_bak2108.py +0 -872
  5. chromadb_persistent/A.TXT +0 -1
  6. llm_utils.py +1 -1
  7. pdf_utils.py +3 -90
  8. pdf_utils_bak2108.py +0 -12
agents.py ADDED
@@ -0,0 +1,14 @@
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
+ from langchain.agents import initialize_agent, AgentType, Tool
2
+ from langchain.memory import ConversationBufferMemory
3
+
4
+ def get_conversation_memory():
5
+ return ConversationBufferMemory(memory_key="chat_history", return_messages=True)
6
+
7
+ def init_multi_agent(tools, llm, memory):
8
+ return initialize_agent(
9
+ tools=tools,
10
+ llm=llm,
11
+ agent=AgentType.CONVERSATIONAL_REACT_DESCRIPTION,
12
+ memory=memory,
13
+ verbose=True
14
+ )
app.py CHANGED
The diff for this file is too large to render. See raw diff
 
app_22082025.py DELETED
@@ -1,1289 +0,0 @@
1
- import os
2
- import pandas as pd
3
- import streamlit as st
4
-
5
-
6
- # ✅ Fix OpenMP library conflict (common with PyTorch/NumPy)
7
- os.environ['KMP_DUPLICATE_LIB_OK'] = 'TRUE'
8
-
9
- # --- Visualization imports ---
10
- try:
11
- import matplotlib.pyplot as plt
12
- import plotly.express as px
13
- import plotly.graph_objects as go
14
- VISUALIZATION_AVAILABLE = True
15
- except ImportError:
16
- st.warning("📊 Visualization libraries not available. Install: pip install matplotlib plotly")
17
- VISUALIZATION_AVAILABLE = False
18
-
19
- # --- Modular Imports ---
20
- from db_utils import get_engine, get_db
21
- from llm_utils import get_llm, get_sql_prompt
22
- from chroma_utils import get_chroma_client, get_chroma_collection, get_embedding_model
23
- from pdf_utils import extract_pdf_text, extract_csv_text, extract_excel_text, extract_json_text, extract_docx_text
24
-
25
- # --- Voice functionality imports (with error handling) ---
26
- # (No need to import from agents, these are defined locally)
27
- import json
28
- from difflib import SequenceMatcher
29
- from langchain.chains import LLMChain
30
-
31
- # === Document Storage Functions ===
32
-
33
- def chunk_text(text, chunk_size=800, overlap=100):
34
- """Split text into overlapping chunks"""
35
- chunks = []
36
- start = 0
37
- while start < len(text):
38
- end = min(start + chunk_size, len(text))
39
- chunk = text[start:end]
40
- chunks.append(chunk)
41
- start += chunk_size - overlap
42
- return chunks
43
-
44
- def store_document_chunks(filename, text, file_type, chroma_collection, embedding_model):
45
- """Store document chunks in vector database with metadata"""
46
- try:
47
- if not text or not text.strip():
48
- st.warning(f"No text content found in {filename}")
49
- return False
50
-
51
- # Create chunks based on file type
52
- if file_type in ['pdf', 'txt', 'docx']:
53
- # For text documents, use standard chunking
54
- chunks = chunk_text(text, chunk_size=800, overlap=100)
55
- chunk_type = "text_chunk"
56
- elif file_type in ['csv', 'excel']:
57
- # For structured data, treat entire content as context
58
- chunks = [text] # Store the summary as a single chunk
59
- chunk_type = "data_summary"
60
- else:
61
- chunks = chunk_text(text, chunk_size=800, overlap=100)
62
- chunk_type = "text_chunk"
63
-
64
- # Generate embeddings for all chunks
65
- chunk_embeddings = embedding_model.encode(chunks, convert_to_numpy=True)
66
-
67
- # Get existing IDs to avoid conflicts
68
- existing_data = chroma_collection.get()
69
- existing_ids = existing_data.get('ids', [])
70
-
71
- # Remove existing chunks for this file (if any)
72
- file_ids_to_remove = [id for id in existing_ids if id.startswith(f"doc_{filename}_")]
73
- if file_ids_to_remove:
74
- chroma_collection.delete(ids=file_ids_to_remove)
75
- st.info(f"Removed {len(file_ids_to_remove)} existing chunks for {filename}")
76
-
77
- # Add new chunks
78
- chunk_ids = []
79
- chunk_documents = []
80
- chunk_metadatas = []
81
- embeddings_list = []
82
-
83
- for i, (chunk, embedding) in enumerate(zip(chunks, chunk_embeddings)):
84
- chunk_id = f"doc_{filename}_{i}"
85
- chunk_ids.append(chunk_id)
86
- chunk_documents.append(chunk)
87
- chunk_metadatas.append({
88
- "filename": filename,
89
- "file_type": file_type,
90
- "chunk_index": i,
91
- "chunk_type": chunk_type,
92
- "total_chunks": len(chunks),
93
- "source": "uploaded_document"
94
- })
95
- embeddings_list.append(embedding.tolist())
96
-
97
- # Add to ChromaDB
98
- chroma_collection.add(
99
- embeddings=embeddings_list,
100
- documents=chunk_documents,
101
- metadatas=chunk_metadatas,
102
- ids=chunk_ids
103
- )
104
-
105
- st.success(f"✅ Stored {len(chunks)} chunks from {filename} in vector database")
106
- return True
107
-
108
- except Exception as e:
109
- st.error(f"❌ Error storing chunks for {filename}: {str(e)}")
110
- return False
111
-
112
- def create_csv_context(df, filename):
113
- """Create comprehensive context for CSV data that can be used for queries"""
114
- try:
115
- context_parts = []
116
-
117
- # Basic info
118
- context_parts.append(f"Dataset: {filename}")
119
- context_parts.append(f"Shape: {df.shape[0]} rows × {df.shape[1]} columns")
120
-
121
- # Column information with data types and sample values
122
- context_parts.append("\nColumns and Data Types:")
123
- for col in df.columns:
124
- dtype = str(df[col].dtype)
125
- non_null_count = df[col].count()
126
-
127
- # Get sample values (excluding nulls)
128
- sample_values = df[col].dropna().head(3).tolist()
129
- sample_str = ", ".join([str(v)[:50] for v in sample_values])
130
-
131
- context_parts.append(f"- {col} ({dtype}): {non_null_count}/{len(df)} non-null values")
132
- if sample_str:
133
- context_parts.append(f" Sample values: {sample_str}")
134
-
135
- # Summary statistics for numeric columns
136
- numeric_cols = df.select_dtypes(include=['number']).columns
137
- if len(numeric_cols) > 0:
138
- context_parts.append("\nNumeric Column Statistics:")
139
- for col in numeric_cols[:5]: # Limit to first 5 numeric columns
140
- stats = df[col].describe()
141
- context_parts.append(f"- {col}: min={stats['min']:.2f}, max={stats['max']:.2f}, mean={stats['mean']:.2f}")
142
-
143
- # Sample data
144
- context_parts.append(f"\nSample Data (first 5 rows):")
145
- context_parts.append(df.head().to_string(index=False))
146
-
147
- # Unique values for categorical columns (if small enough)
148
- categorical_cols = df.select_dtypes(include=['object']).columns
149
- for col in categorical_cols[:3]: # Limit to first 3 categorical columns
150
- unique_vals = df[col].unique()
151
- if len(unique_vals) <= 10:
152
- context_parts.append(f"\nUnique values in {col}: {', '.join(map(str, unique_vals))}")
153
- else:
154
- context_parts.append(f"\nUnique values in {col}: {len(unique_vals)} unique values")
155
- context_parts.append(f"Sample values: {', '.join(map(str, unique_vals[:5]))}")
156
-
157
- return "\n".join(context_parts)
158
-
159
- except Exception as e:
160
- return f"Error creating CSV context: {str(e)}"
161
-
162
- # ✅ Custom CSS for Styling
163
- st.markdown(
164
- """
165
- <style>
166
- /* Background and font styles */
167
- body {
168
- background-color: #f4f4f4;
169
- font-family: 'Arial', sans-serif;
170
- }
171
-
172
- /* Title Styling */
173
- .title {
174
- color: #2E86C1;
175
- text-align: center;
176
- font-size: 40px;
177
- font-weight: bold;
178
- }
179
-
180
- /* Text Area Styling */
181
- .stTextArea > div > textarea {
182
- border-radius: 10px;
183
- border: 2px solid #3498db;
184
- padding: 10px;
185
- font-size: 16px;
186
- height: 150px !important; /* Increased height */
187
- }
188
-
189
- /* Button Styling */
190
- .stButton > button {
191
- background-color: #3498db;
192
- color: white;
193
- font-size: 18px;
194
- border-radius: 10px;
195
- padding: 10px 20px;
196
- border: none;
197
- transition: 0.3s;
198
- }
199
-
200
- .stButton > button:hover {
201
- background-color: #2980b9;
202
- }
203
-
204
- /* SQL Query Box */
205
- .stCode {
206
- background-color: #1e272e;
207
- color: #ecf0f1;
208
- font-size: 16px;
209
- border-radius: 10px;
210
- }
211
-
212
- /* Dataframe Table */
213
- .stDataFrame {
214
- border-radius: 10px;
215
- overflow: hidden;
216
- box-shadow: 2px 2px 10px rgba(0, 0, 0, 0.1);
217
- }
218
- </style>
219
- """,
220
- unsafe_allow_html=True
221
- )
222
-
223
- # ✅ Streamlit Chat UI with Session History
224
- st.markdown('<h3 class="title">Multi-Model RAG System</h3>', unsafe_allow_html=True)
225
- st.write(" Chat with the agent. Your session history will be preserved below.")
226
-
227
- # Initialize session state for chat history
228
- if 'chat_history' not in st.session_state:
229
- st.session_state['chat_history'] = []
230
- # Each item: {'role': 'user'/'agent', 'content': str, 'sql': str or None, 'df': DataFrame or None}
231
-
232
- # Initialize session state for voice input
233
- if 'pending_voice_query' not in st.session_state:
234
- st.session_state['pending_voice_query'] = None
235
-
236
- ## --- Modular Setup ---
237
- engine = get_engine()
238
- db = get_db(engine)
239
- llm = get_llm()
240
- sql_prompt = get_sql_prompt()
241
-
242
- # ✅ Function to Format Schema as Markdown
243
- def format_schema_markdown(schema_info):
244
- import re
245
- tables = re.findall(r'CREATE TABLE \"([^\"]+)\" \((.*?)\)', schema_info, re.DOTALL)
246
- md = '**Database Tables and Columns:**\n'
247
- for table, cols in tables:
248
- col_names = re.findall(r'\"([^\"]+)\"', cols)
249
- md += f'- **{table}**: ' + ', '.join(f'`{col}`' for col in col_names) + '\\n'
250
- return md
251
-
252
- # Helper: Detect if a question is a follow-up
253
- followup_starters = [
254
- 'and ', 'also', 'from which', 'which', 'what about', 'where', 'who', 'how many', 'show me those', 'give me those'
255
- ]
256
- def is_followup_question(q):
257
- ql = q.strip().lower()
258
- return any(ql.startswith(st) for st in followup_starters)
259
-
260
- PROMPT_JSONL_PATH = 'prompts.jsonl'
261
- def load_prompt_examples(jsonl_path):
262
- examples = []
263
- try:
264
- with open(jsonl_path, 'r', encoding='utf-8') as f:
265
- for line in f:
266
- examples.append(json.loads(line))
267
- except Exception:
268
- pass
269
- return examples
270
-
271
- prompt_examples = load_prompt_examples(PROMPT_JSONL_PATH)
272
-
273
- # === Utility Functions ===
274
-
275
- def extract_llm_content(response):
276
- """
277
- Extract text content from various LLM response formats
278
- """
279
- if hasattr(response, 'content'):
280
- return response.content
281
- elif isinstance(response, dict):
282
- return response.get('text', response.get('content', str(response)))
283
- else:
284
- return str(response)
285
-
286
- # Initialize ChromaDB and embedding model with persistence
287
- try:
288
- with st.spinner("🔄 Initializing persistent vector database..."):
289
- chroma_client = get_chroma_client()
290
- chroma_collection = get_chroma_collection(chroma_client)
291
- embedding_model = get_embedding_model()
292
-
293
- # Try to initialize from existing JSONL if database is empty
294
- from chroma_utils import initialize_from_jsonl, check_database_stats
295
- initialize_from_jsonl(PROMPT_JSONL_PATH)
296
-
297
- # Show database statistics
298
- stats = check_database_stats()
299
- # Hide vector DB status/info messages unless error or warning
300
- if stats["status"] != "healthy":
301
- st.warning(f"⚠️ Database status: {stats['status']}")
302
-
303
- except Exception as e:
304
- st.error(f"❌ Vector search initialization failed: {str(e)}")
305
- chroma_client = None
306
- chroma_collection = None
307
- embedding_model = None
308
-
309
- # (No need to import run_query_chain; define and use locally)
310
-
311
- with st.sidebar:
312
- st.markdown('---')
313
- st.markdown('### Upload PDF, CSV, Excel, Word, or JSON files to query their content')
314
- uploaded_files = st.file_uploader(
315
- "Upload one or more files (PDF, CSV, Excel, DOCX, JSON)",
316
- type=["pdf", "csv", "xlsx", "xls", "json", "docx"],
317
- accept_multiple_files=True
318
- )
319
- if 'uploaded_dfs' not in st.session_state:
320
- st.session_state['uploaded_dfs'] = {}
321
- if 'uploaded_texts' not in st.session_state:
322
- st.session_state['uploaded_texts'] = {}
323
- for uploaded_file in uploaded_files or []:
324
- filename = uploaded_file.name
325
- if filename.endswith('.csv'):
326
- df = pd.read_csv(uploaded_file)
327
- st.session_state['uploaded_dfs'][filename] = df
328
-
329
- # Create comprehensive CSV context for vector storage
330
- csv_context = create_csv_context(df, filename)
331
- st.session_state['uploaded_texts'][filename] = csv_context
332
-
333
- # Store in vector database
334
- if chroma_collection is not None and embedding_model is not None:
335
- store_document_chunks(filename, csv_context, 'csv', chroma_collection, embedding_model)
336
-
337
- st.success(f"CSV loaded: {filename} (shape: {df.shape})")
338
-
339
- elif filename.endswith(('.xlsx', '.xls')):
340
- df = pd.read_excel(uploaded_file)
341
- st.session_state['uploaded_dfs'][filename] = df
342
-
343
- # Create comprehensive Excel context for vector storage
344
- excel_context = create_csv_context(df, filename) # Same logic as CSV
345
- st.session_state['uploaded_texts'][filename] = excel_context
346
-
347
- # Store in vector database
348
- if chroma_collection is not None and embedding_model is not None:
349
- store_document_chunks(filename, excel_context, 'excel', chroma_collection, embedding_model)
350
-
351
- st.success(f"Excel loaded: {filename} (shape: {df.shape})")
352
-
353
- elif filename.endswith('.json'):
354
- # Extract text summary for Q&A
355
- text, error = extract_json_text(uploaded_file)
356
- if text:
357
- st.session_state['uploaded_texts'][filename] = text
358
-
359
- # Store in vector database
360
- if chroma_collection is not None and embedding_model is not None:
361
- store_document_chunks(filename, text, 'json', chroma_collection, embedding_model)
362
-
363
- st.success(f"JSON loaded: {filename} (chars: {len(text)})")
364
- else:
365
- st.error(f"JSON error: {error}")
366
-
367
- elif filename.endswith('.pdf'):
368
- text, error = extract_pdf_text(uploaded_file)
369
- if text:
370
- st.session_state['uploaded_texts'][filename] = text
371
-
372
- # Store in vector database
373
- if chroma_collection is not None and embedding_model is not None:
374
- store_document_chunks(filename, text, 'pdf', chroma_collection, embedding_model)
375
-
376
- st.success(f"PDF loaded: {filename} (chars: {len(text)})")
377
- else:
378
- st.error(f"PDF error: {error}")
379
-
380
- elif filename.endswith('.docx'):
381
- text, error = extract_docx_text(uploaded_file)
382
- if text:
383
- st.session_state['uploaded_texts'][filename] = text
384
-
385
- # Store in vector database
386
- if chroma_collection is not None and embedding_model is not None:
387
- store_document_chunks(filename, text, 'docx', chroma_collection, embedding_model)
388
-
389
- st.success(f"Word document loaded: {filename} (chars: {len(text)})")
390
- else:
391
- st.error(f"DOCX error: {error}")
392
-
393
- # Show uploaded DataFrames
394
- if st.session_state['uploaded_dfs']:
395
- st.markdown('#### Uploaded DataFrames:')
396
- for fname, df in st.session_state['uploaded_dfs'].items():
397
- st.write(f"**{fname}**")
398
- st.dataframe(df.head())
399
- # Show uploaded PDF text (first 500 chars)
400
- if st.session_state['uploaded_texts']:
401
- st.markdown('#### Uploaded File Text (preview):')
402
- for fname, text in st.session_state['uploaded_texts'].items():
403
- st.write(f"**{fname}**: {text[:500]}{'...' if len(text) > 500 else ''}")
404
-
405
- # Vector Database Status
406
- if chroma_collection is not None:
407
- with st.expander("📊 Vector Database Status", expanded=False):
408
- try:
409
- # Get collection stats
410
- collection_data = chroma_collection.get(include=['metadatas'])
411
- total_items = len(collection_data.get('ids', []))
412
-
413
- if total_items > 0:
414
- # Count by source type
415
- metadatas = collection_data.get('metadatas', [])
416
- source_counts = {}
417
- doc_counts = {}
418
-
419
- for meta in metadatas:
420
- source = meta.get('source', 'unknown')
421
- filename = meta.get('filename', 'unknown')
422
-
423
- source_counts[source] = source_counts.get(source, 0) + 1
424
- if filename != 'unknown':
425
- doc_counts[filename] = doc_counts.get(filename, 0) + 1
426
-
427
- st.write(f"**Total chunks stored:** {total_items}")
428
-
429
- if doc_counts:
430
- st.write("**Documents in vector database:**")
431
- for doc, count in doc_counts.items():
432
- st.write(f"- {doc}: {count} chunks")
433
-
434
- if source_counts:
435
- st.write("**Content by source:**")
436
- for source, count in source_counts.items():
437
- if source == 'uploaded_document':
438
- st.write(f"- 📄 Uploaded documents: {count} chunks")
439
- elif source == 'jsonl_import':
440
- st.write(f"- 💾 Imported prompts: {count} items")
441
- else:
442
- st.write(f"- {source}: {count} items")
443
-
444
- # Add clear documents button
445
- col1, col2 = st.columns(2)
446
- with col1:
447
- if st.button("🗑️ Clear Document Chunks", help="Remove all uploaded document chunks from vector database"):
448
- try:
449
- # Get all document chunk IDs
450
- all_data = chroma_collection.get()
451
- doc_ids = [id for id in all_data.get('ids', []) if id.startswith('doc_')]
452
-
453
- if doc_ids:
454
- chroma_collection.delete(ids=doc_ids)
455
- st.success(f"Removed {len(doc_ids)} document chunks from vector database")
456
- st.rerun()
457
- else:
458
- st.info("No document chunks to remove")
459
- except Exception as e:
460
- st.error(f"Error clearing documents: {e}")
461
-
462
- with col2:
463
- if st.button("🔄 Refresh Status", help="Refresh vector database status"):
464
- st.rerun()
465
- else:
466
- st.write("No items in vector database yet.")
467
-
468
- except Exception as e:
469
- st.write(f"Error checking database status: {e}")
470
-
471
-
472
- # === Local Definition: run_query_chain ===
473
- def run_query_chain(user_query):
474
- schema_info = db.get_table_info()
475
- # Check for meta/schema questions
476
- meta_keywords = [
477
- 'list of tables', 'show tables', 'schema details', 'database schema', 'table names', 'what tables', 'describe schema', 'show schema', 'database structure', 'column', 'columns'
478
- ]
479
- if any(kw in user_query.lower() for kw in meta_keywords):
480
- st.write("[DEBUG] Detected meta/schema question.")
481
- return None, format_schema_markdown(schema_info)
482
- # Check for direct table details or table preview requests
483
- import re
484
- table_match = re.search(r'(?:give me|show|display|list|fetch|get) (.+?)(?: details)?$', user_query.lower())
485
- if table_match:
486
- stopwords = {'me', 'the', 'a', 'an', 'all', 'of', 'my', 'our', 'their', 'this', 'that'}
487
- table_candidate = table_match.group(1)
488
- table_candidate = ' '.join([w for w in table_candidate.split() if w not in stopwords]).strip()
489
- # Remove trailing 'table' or 'data' if present
490
- for suffix in [' table', ' data']:
491
- if table_candidate.lower().endswith(suffix):
492
- table_candidate = table_candidate[: -len(suffix)].strip()
493
- schema_tables = ['Location', 'Product', 'Sales']
494
- matched_table = None
495
- for t in schema_tables:
496
- if table_candidate.lower() == t.lower():
497
- matched_table = t
498
- break
499
- if matched_table:
500
- try:
501
- df = pd.read_sql(f'SELECT * FROM "{matched_table}" LIMIT 10', engine)
502
- st.session_state['last_result_df'] = df
503
- return f'SELECT * FROM "{matched_table}" LIMIT 10', df
504
- except Exception as e:
505
- st.write(f"[DEBUG] SQL Error: {e}")
506
- return None, f"SQL Error: {e}"
507
- # --- Enhanced follow-up logic ---
508
- if 'last_data_question' in st.session_state and is_followup_question(user_query):
509
- context = st.session_state['last_data_question']
510
- last_df = st.session_state.get('last_result_df')
511
- st.write(f"[DEBUG] Follow-up detected. Last context: {context}")
512
- if last_df is not None and 'Product Name' in last_df.columns:
513
- product_names = last_df['Product Name'].tolist()
514
- if product_names:
515
- in_clause = ', '.join([f'"{p}"' for p in product_names])
516
- sql = f'SELECT * FROM "Product" WHERE "Product Name" IN ({in_clause})'
517
- try:
518
- df = pd.read_sql(sql, engine)
519
- st.session_state['last_result_df'] = df
520
- return sql, df
521
- except Exception as e:
522
- st.write(f"[DEBUG] SQL Error (follow-up): {e}")
523
- return sql, f"SQL Error: {e}"
524
- user_query = f"Previous question: {context}\nFollow-up: {user_query}"
525
- # Check for similar prompt in ChromaDB (semantic search)
526
- # (You can add semantic search logic here if needed)
527
- # Check for similar prompt in prompts.jsonl (string similarity)
528
- best_example, score = None, 0
529
- for ex in prompt_examples:
530
- s = SequenceMatcher(None, user_query.lower(), ex['prompt'].lower()).ratio()
531
- if s > score:
532
- score = s
533
- best_example = ex
534
- if best_example and score > 0.85:
535
- try:
536
- df = pd.read_sql(best_example['sql'], engine)
537
- st.session_state['last_result_df'] = df
538
- return best_example['sql'], df
539
- except Exception as e:
540
- st.write(f"[DEBUG] SQL Error (string sim): {e}")
541
- return best_example['sql'], f"SQL Error: {e}"
542
- # Context-aware follow-up: prepend last non-meta user question if this is a follow-up
543
- if 'last_data_question' in st.session_state and is_followup_question(user_query):
544
- context = st.session_state['last_data_question']
545
- user_query = f"Previous question: {context}\nFollow-up: {user_query}"
546
- sql_generation_chain = LLMChain(llm=llm, prompt=sql_prompt)
547
- generated_sql = sql_generation_chain.invoke({"question": user_query, "schema": schema_info})
548
- if isinstance(generated_sql, dict):
549
- for key in ["sql", "text", "output", "result"]:
550
- if key in generated_sql and isinstance(generated_sql[key], str):
551
- generated_sql = generated_sql[key]
552
- break
553
- else:
554
- generated_sql = str(generated_sql)
555
- if isinstance(generated_sql, str) and not generated_sql.strip().lower().startswith("select"):
556
- for line in generated_sql.splitlines():
557
- if line.strip().lower().startswith("select"):
558
- generated_sql = line.strip()
559
- break
560
- if not (generated_sql.strip().lower().startswith("select")):
561
- fallback_query = f"Write a SQL query for: {user_query}"
562
- generated_sql = sql_generation_chain.invoke({"question": fallback_query, "schema": schema_info})
563
- if isinstance(generated_sql, dict):
564
- for key in ["sql", "text", "output", "result"]:
565
- if key in generated_sql and isinstance(generated_sql[key], str):
566
- generated_sql = generated_sql[key]
567
- break
568
- else:
569
- generated_sql = str(generated_sql)
570
- if isinstance(generated_sql, str) and not generated_sql.strip().lower().startswith("select"):
571
- for line in generated_sql.splitlines():
572
- if line.strip().lower().startswith("select"):
573
- generated_sql = line.strip()
574
- break
575
- if not (generated_sql.strip().lower().startswith("select")):
576
- st.write("[DEBUG] No valid SQL generated.")
577
- return None, "No SQL query could be generated for your request. Please rephrase or be more specific."
578
- try:
579
- df = pd.read_sql(generated_sql, engine)
580
- st.session_state['last_data_question'] = user_query if not is_followup_question(user_query) else st.session_state.get('last_data_question', user_query)
581
- st.session_state['last_result_df'] = df
582
- # --- Add new prompt/SQL to prompts.jsonl ---
583
- with open(PROMPT_JSONL_PATH, "a", encoding="utf-8") as f:
584
- f.write(json.dumps({"prompt": user_query, "sql": generated_sql}) + "\n")
585
- return generated_sql, df
586
- except Exception as e:
587
- return generated_sql, f"SQL Error: {e}"
588
-
589
- def create_data_visualization(df: pd.DataFrame, chart_key: str):
590
- """
591
- Create visualization options for DataFrame
592
- """
593
- if not VISUALIZATION_AVAILABLE or df.empty:
594
- return
595
-
596
- st.markdown("### 📊 Data Visualization")
597
-
598
- # Create visualization options
599
- col1, col2, col3 = st.columns(3)
600
-
601
- with col1:
602
- show_bar = st.checkbox("📊 Bar Chart", key=f"bar_{chart_key}")
603
- with col2:
604
- show_pie = st.checkbox("🥧 Pie Chart", key=f"pie_{chart_key}")
605
- with col3:
606
- show_line = st.checkbox("📈 Line Chart", key=f"line_{chart_key}")
607
-
608
- if show_bar or show_pie or show_line:
609
- # Get column options for visualization
610
- numeric_cols = df.select_dtypes(include=['number']).columns.tolist()
611
- text_cols = df.select_dtypes(include=['object', 'string']).columns.tolist()
612
-
613
- col_x, col_y = st.columns(2)
614
-
615
- with col_x:
616
- x_col = st.selectbox(
617
- "Select X-axis column:",
618
- options=df.columns.tolist(),
619
- key=f"x_col_{chart_key}"
620
- )
621
-
622
- with col_y:
623
- y_col = st.selectbox(
624
- "Select Y-axis column:",
625
- options=numeric_cols if numeric_cols else df.columns.tolist(),
626
- key=f"y_col_{chart_key}"
627
- )
628
-
629
- if x_col and y_col:
630
- try:
631
- # Create visualizations based on selection
632
- if show_bar:
633
- st.subheader("📊 Bar Chart")
634
- if len(df) > 20:
635
- # For large datasets, aggregate or show top 20
636
- plot_df = df.groupby(x_col)[y_col].sum().reset_index().head(20)
637
- else:
638
- plot_df = df
639
-
640
- fig_bar = px.bar(plot_df, x=x_col, y=y_col,
641
- title=f"{y_col} by {x_col}")
642
- st.plotly_chart(fig_bar, use_container_width=True)
643
-
644
- if show_pie:
645
- st.subheader("🥧 Pie Chart")
646
- # Aggregate data for pie chart
647
- if len(df) > 10:
648
- # Show top 10 categories for pie chart
649
- pie_df = df.groupby(x_col)[y_col].sum().reset_index()
650
- pie_df = pie_df.nlargest(10, y_col)
651
- else:
652
- pie_df = df.groupby(x_col)[y_col].sum().reset_index()
653
-
654
- fig_pie = px.pie(pie_df, values=y_col, names=x_col,
655
- title=f"{y_col} Distribution by {x_col}")
656
- st.plotly_chart(fig_pie, use_container_width=True)
657
-
658
- if show_line:
659
- st.subheader("📈 Line Chart")
660
- fig_line = px.line(df, x=x_col, y=y_col,
661
- title=f"{y_col} Trend by {x_col}")
662
- st.plotly_chart(fig_line, use_container_width=True)
663
-
664
- except Exception as e:
665
- st.error(f"Visualization error: {e}")
666
- st.info("💡 Tip: Make sure you select appropriate columns for the chart type")
667
-
668
- # === Chat Input Section ===
669
- st.markdown("---")
670
-
671
- user_query = None
672
- st.write("🎤 Voice recording functionality has been removed.")
673
- user_query = st.chat_input("💬 Ask about your business data or upload files to analyze...", key="main_chat_input")
674
- # If you want to convert speech to text, send this audio to an ASR model
675
-
676
-
677
-
678
- # Check for pending voice query from session state
679
- if 'pending_voice_query' in st.session_state and st.session_state['pending_voice_query']:
680
- user_query = st.session_state['pending_voice_query']
681
- # Clear the pending query to avoid reprocessing
682
- st.session_state['pending_voice_query'] = None
683
-
684
- # Regular text input
685
- text_query = st.chat_input("💬 Ask about your business data or upload files to analyze...", key="secondary_chat_input")
686
- if text_query:
687
- user_query = text_query
688
-
689
- # When user submits a query
690
- if user_query:
691
- generated_sql, df_results = run_query_chain(user_query)
692
- st.session_state['chat_history'].append({
693
- 'role': 'user',
694
- 'content': user_query,
695
- 'sql': None,
696
- 'df': None
697
- })
698
- st.session_state['chat_history'].append({
699
- 'role': 'agent',
700
- 'content': "Here is the generated SQL and results.",
701
- 'sql': generated_sql,
702
- 'df': df_results
703
- })
704
-
705
- # Add a button to display the schema in the chat UI
706
- if st.button('Show Database Schema'):
707
- schema_info = db.get_table_info()
708
- st.session_state['chat_history'].append({
709
- 'role': 'agent',
710
- 'content': format_schema_markdown(schema_info),
711
- 'sql': None,
712
- 'df': None
713
- })
714
-
715
- # Show all prompts in ChromaDB
716
- if st.button("Show all prompts in vector DB"):
717
- if chroma_collection is not None:
718
- try:
719
- chroma_data = chroma_collection.get()
720
- st.write("Prompts in Vector DB:")
721
- for i, doc in enumerate(chroma_data.get('documents', [])):
722
- sql = chroma_data.get('metadatas', [])[i].get('sql', '')
723
- st.write(f"{i+1}. Prompt: {doc}")
724
- st.code(sql, language='sql')
725
- except Exception as e:
726
- st.error(f"Error accessing vector DB: {e}")
727
- else:
728
- st.warning("Vector DB not available. Please check ChromaDB initialization.")
729
-
730
- # Display chat history
731
- for msg in st.session_state['chat_history']:
732
- if msg['role'] == 'user':
733
- with st.chat_message('user'):
734
- st.markdown(msg.get('content', ''))
735
- else:
736
- with st.chat_message('assistant'):
737
- sql = msg.get('sql')
738
- df = msg.get('df')
739
- response_content = msg.get('content', '')
740
-
741
- # If the agent's response is a SQL query, show it and the result
742
- if sql and isinstance(sql, str) and sql.strip().lower().startswith('select'):
743
- st.markdown(response_content)
744
- st.code(sql, language='sql')
745
-
746
- if isinstance(df, pd.DataFrame):
747
- if df.empty:
748
- st.info('No results found for this query.')
749
- else:
750
- # Add visualization options above the dataframe
751
- if VISUALIZATION_AVAILABLE:
752
- create_data_visualization(df, f"viz1_{hash(sql)}")
753
-
754
- st.subheader("📋 Query Results")
755
- st.dataframe(df)
756
-
757
- # --- Save as CSV and View buttons ---
758
- col1, col2 = st.columns(2)
759
- with col1:
760
- csv_filename = "chat_agent_output.csv"
761
- st.download_button(
762
- label="Download as CSV",
763
- data=df.to_csv(index=False).encode('utf-8'),
764
- file_name=csv_filename,
765
- mime='text/csv',
766
- key=f"csv_{sql}"
767
- )
768
- with col2:
769
- view_name = st.text_input(f"Enter view name to save as DB view (for this result):", key=f"view_{sql}")
770
- if st.button("Save as View", key=f"btn_view_{sql}") and view_name:
771
- safe_view_name = ''.join(c for c in view_name if c.isalnum() or c == '_')
772
- create_view_sql = f'''CREATE VIEW IF NOT EXISTS "{safe_view_name}" AS {sql.strip().rstrip(';')};'''
773
- try:
774
- engine.execute(create_view_sql)
775
- st.success(f"View '{safe_view_name}' created successfully!")
776
- except Exception as ve:
777
- st.error(f"Error creating view: {ve}")
778
- elif isinstance(df, str) and df.startswith('SQL Error:'):
779
- st.error(df)
780
- # If the agent's response is a plain message (not SQL), show as markdown only
781
- elif sql and not sql.strip().lower().startswith('select'):
782
- st.markdown(sql)
783
- else:
784
- st.markdown(response_content)
785
- if isinstance(df, pd.DataFrame):
786
- if df.empty:
787
- st.info('No results found for this query.')
788
- else:
789
- # Add visualization options above the dataframe
790
- if VISUALIZATION_AVAILABLE:
791
- create_data_visualization(df, f"viz2_{hash(msg.get('content', ''))}")
792
-
793
- st.subheader("📋 Query Results")
794
- st.dataframe(df)
795
-
796
- # --- Save as CSV and View buttons ---
797
- col1, col2 = st.columns(2)
798
- with col1:
799
- csv_filename = "chat_agent_output.csv"
800
- st.download_button(
801
- label="Download as CSV",
802
- data=df.to_csv(index=False).encode('utf-8'),
803
- file_name=csv_filename,
804
- mime='text/csv',
805
- key=f"csv_{msg.get('content', '')}"
806
- )
807
- with col2:
808
- view_name = st.text_input(f"Enter view name to save as DB view (for this result):", key=f"view_{msg.get('content', '')}")
809
- if st.button("Save as View", key=f"btn_view_{msg.get('content', '')}") and view_name:
810
- safe_view_name = ''.join(c for c in view_name if c.isalnum() or c == '_')
811
- # Use the last SQL if available
812
- sql_to_use = sql if sql else ""
813
- create_view_sql = f'''CREATE VIEW IF NOT EXISTS "{safe_view_name}" AS {sql_to_use.strip().rstrip(';')};'''
814
- try:
815
- engine.execute(create_view_sql)
816
- st.success(f"View '{safe_view_name}' created successfully!")
817
- except Exception as ve:
818
- st.error(f"Error creating view: {ve}")
819
- elif isinstance(df, str) and df.startswith('SQL Error:'):
820
- st.error(df)
821
-
822
- # === Prompt Tuning Agent Section ===
823
- from langchain.prompts import PromptTemplate
824
-
825
- # Helper: Suggest a better prompt using LLM
826
- prompt_tuning_template = PromptTemplate(
827
- input_variables=["draft", "schema"],
828
- template="""
829
- You are an expert SQL query writer and prompt improvement assistant for a SQLite database. Given the following database schema:
830
- {schema}
831
-
832
- The user has written this draft prompt/question:
833
- "{draft}"
834
-
835
- Your job is to:
836
- - Suggest a clearer, more specific, and effective prompt that will help generate a correct SQL query for SQLite.
837
- - Use the exact table and column names as shown in the schema (including case, spaces, and special characters).
838
- - If the draft is already good, return it as is. Otherwise, rewrite it to be more explicit, mentioning relevant tables/columns if possible.
839
- - If the user's intent requires columns from multiple tables, make sure the improved prompt is clear about the relationship (e.g., join keys) and the aggregation or ranking needed.
840
- - Only return the improved prompt, no explanations.
841
-
842
- Example:
843
- Draft: top 3 product with highest revenue
844
- Improved: Show the top 3 products by total revenue, using the "Product Name" from the "Product" table and summing "Revenue (USD)" from the "Sales" table. Join "Product" and "Sales" on "Product ID". Order by total revenue descending and limit to 3 results.
845
- """
846
- )
847
-
848
- def suggest_better_prompt(draft, schema_info):
849
- chain = LLMChain(llm=llm, prompt=prompt_tuning_template)
850
- improved = chain.invoke({"draft": draft, "schema": schema_info})
851
- # Extract string if dict
852
- if isinstance(improved, dict):
853
- for key in ["text", "output", "result"]:
854
- if key in improved and isinstance(improved[key], str):
855
- improved = improved[key]
856
- break
857
- else:
858
- improved = str(improved)
859
- return improved.strip()
860
-
861
- def generate_sql_for_prompt(prompt, schema_info):
862
- # --- Patch: Always refer to schema before generating SQL ---
863
- # If prompt does not mention schema, prepend schema info for LLM context
864
- schema_hint = f"Refer to this schema for table and column names:\n{schema_info}\n"
865
- prompt_with_schema = f"{schema_hint}{prompt}" if 'CREATE TABLE' not in prompt else prompt
866
- sql_chain = LLMChain(llm=llm, prompt=sql_prompt)
867
- sql = sql_chain.invoke({"question": prompt_with_schema, "schema": schema_info})
868
- if isinstance(sql, dict):
869
- for key in ["sql", "text", "output", "result"]:
870
- if key in sql and isinstance(sql[key], str):
871
- sql = sql[key]
872
- break
873
- else:
874
- sql = str(sql)
875
- # Extract SQL if explanation is included
876
- if isinstance(sql, str) and not sql.strip().lower().startswith("select"):
877
- for line in sql.splitlines():
878
- if line.strip().lower().startswith("select"):
879
- sql = line.strip()
880
- break
881
- # --- Patch: Auto-quote all table and column names for SQLite ---
882
- def auto_quote_sql(sql_str, schema_info):
883
- import re
884
- # Find all table and column names from schema
885
- tables = re.findall(r'CREATE TABLE "([^"]+)" \((.*?)\)', schema_info, re.DOTALL)
886
- table_names = [t[0] for t in tables]
887
- col_names = []
888
- for _, cols in tables:
889
- col_names += re.findall(r'"([^"]+)"', cols)
890
- # Quote table names not already quoted
891
- for t in table_names:
892
- # Only quote if not already quoted
893
- sql_str = re.sub(rf'(?<!["])\b{re.escape(t)}\b(?!["])', f'"{t}"', sql_str)
894
- # Quote column names not already quoted
895
- for c in col_names:
896
- # Only quote if not already quoted and not part of a string literal
897
- sql_str = re.sub(rf'(?<![\.\"])(?<!["])\b{re.escape(c)}\b(?!["])', f'"{c}"', sql_str)
898
- return sql_str
899
- sql = auto_quote_sql(sql, schema_info)
900
- return sql.strip()
901
-
902
- def save_prompt_sql_pair(prompt, sql):
903
- # Save to prompts.jsonl
904
- with open(PROMPT_JSONL_PATH, "a", encoding="utf-8") as f:
905
- f.write(json.dumps({"prompt": prompt, "sql": sql}) + "\n")
906
-
907
- # Save to ChromaDB (if available)
908
- if chroma_collection is not None and embedding_model is not None:
909
- try:
910
- query_emb = embedding_model.encode([prompt], convert_to_numpy=True)
911
- existing_ids = chroma_collection.get().get('ids', [])
912
- next_id = str(len(existing_ids) + 1)
913
-
914
- chroma_collection.add(
915
- embeddings=query_emb,
916
- documents=[prompt],
917
- metadatas=[{"sql": sql}],
918
- ids=[next_id]
919
- )
920
- except Exception as e:
921
- st.warning(f"Could not save to vector DB: {e}")
922
- else:
923
- st.info("Vector DB not available - saved to local file only")
924
-
925
- # === Streamlit Tabs: Main Agent and Prompt Tuning Agent ===
926
-
927
-
928
- # === Multi-Agent Integration ===
929
- from langchain.agents import initialize_agent, AgentType, Tool
930
-
931
- # --- SQL Query Tool ---
932
- def run_sql_query(sql: str) -> str:
933
- # Always limit result size for LLM safety
934
- import re
935
- sql_lc = sql.lower()
936
- if sql_lc.startswith("select") and "limit" not in sql_lc:
937
- # Add LIMIT 20 to queries without a limit
938
- if ";" in sql:
939
- sql = re.sub(r';\s*$', '', sql)
940
- sql += " LIMIT 20"
941
- try:
942
- df = pd.read_sql(sql, engine)
943
- return df.to_csv(index=False)
944
- except Exception as e:
945
- return f"SQL Error: {e}"
946
-
947
- sql_query_tool = Tool(
948
- name="run_sql_query",
949
- func=run_sql_query,
950
- description="Execute a SQL query on the business database and return results as CSV."
951
- )
952
-
953
- # --- SQL Generation Tool ---
954
- def generate_sql_tool(question: str) -> str:
955
- sql = generate_sql_for_prompt(question, db.get_table_info())
956
- return sql
957
-
958
- sql_gen_tool = Tool(
959
- name="generate_sql",
960
- func=generate_sql_tool,
961
- description="Convert a natural language question to an optimized SQL query for the business database."
962
- )
963
-
964
- # --- Document Q&A Tool (Enhanced with Vector DB) ---
965
- def ask_document_tool(question: str) -> str:
966
- """Answer questions using documents stored in vector database"""
967
- try:
968
- if chroma_collection is None or embedding_model is None:
969
- return "Vector database not available. Please check your setup."
970
-
971
- # Get question embedding
972
- question_embedding = embedding_model.encode([question], convert_to_numpy=True)
973
-
974
- # Search vector database for relevant document chunks
975
- try:
976
- results = chroma_collection.query(
977
- query_embeddings=question_embedding.tolist(),
978
- n_results=5, # Get top 5 most relevant chunks
979
- include=['documents', 'metadatas', 'distances']
980
- )
981
-
982
- if not results['documents'] or not results['documents'][0]:
983
- return "No relevant documents found. Please upload some documents first."
984
-
985
- # Process results to build context
986
- contexts = []
987
- sources = []
988
-
989
- for i, (doc, metadata, distance) in enumerate(zip(
990
- results['documents'][0],
991
- results['metadatas'][0],
992
- results['distances'][0]
993
- )):
994
- if distance < 1.5: # Only include reasonably relevant chunks
995
- file_type = metadata.get('file_type', 'unknown')
996
- filename = metadata.get('filename', 'unknown')
997
- chunk_type = metadata.get('chunk_type', 'text_chunk')
998
-
999
- if chunk_type == 'data_summary':
1000
- contexts.append(f"[Data from {filename}]:\n{doc}")
1001
- else:
1002
- contexts.append(f"[Text from {filename}]:\n{doc}")
1003
-
1004
- sources.append(f"{filename} ({file_type})")
1005
-
1006
- if not contexts:
1007
- return "No sufficiently relevant content found for your question."
1008
-
1009
- # Build comprehensive prompt
1010
- context_text = "\n\n".join(contexts[:3]) # Use top 3 contexts
1011
- unique_sources = list(set(sources[:3]))
1012
-
1013
- # Different prompts for data vs text questions
1014
- if any('data_summary' in str(meta.get('chunk_type', '')) for meta in results['metadatas'][0][:3]):
1015
- # Data-focused prompt
1016
- prompt = f"""Based on the following data sources, answer the question:
1017
-
1018
- Context from uploaded files:
1019
- {context_text}
1020
-
1021
- Question: {question}
1022
-
1023
- Instructions:
1024
- - Provide a clear, data-driven answer
1025
- - Reference specific data points when relevant
1026
- - If the question requires analysis not directly shown in the context, indicate what additional analysis would be needed
1027
- - Mention which files the answer is based on
1028
-
1029
- Answer:"""
1030
- else:
1031
- # Text-focused prompt
1032
- prompt = f"""Based on the following document content, answer the question:
1033
-
1034
- Context from uploaded documents:
1035
- {context_text}
1036
-
1037
- Question: {question}
1038
-
1039
- Instructions:
1040
- - Provide a comprehensive answer based on the context
1041
- - Quote relevant parts when helpful
1042
- - If the answer is not fully contained in the context, indicate this clearly
1043
- - Mention which documents the answer is based on
1044
-
1045
- Answer:"""
1046
-
1047
- # Get LLM response
1048
- answer = llm.invoke(prompt)
1049
- answer_text = extract_llm_content(answer)
1050
-
1051
- # Add source information
1052
- source_info = f"\n\n📁 Sources: {', '.join(unique_sources)}"
1053
- return answer_text + source_info
1054
-
1055
- except Exception as e:
1056
- st.error(f"Error querying vector database: {str(e)}")
1057
- return f"Error searching documents: {str(e)}"
1058
-
1059
- except Exception as e:
1060
- return f"Error in document query: {str(e)}"
1061
-
1062
- document_qa_tool = Tool(
1063
- name="ask_documents",
1064
- func=ask_document_tool,
1065
- description="Answer questions based on uploaded documents (PDF, CSV, Excel, JSON) using semantic search across the vector database."
1066
- )
1067
-
1068
- # --- Data Analysis Tool for CSV/Excel ---
1069
- def analyze_data_tool(question: str) -> str:
1070
- """Analyze uploaded CSV/Excel data using pandas operations"""
1071
- try:
1072
- uploaded_dfs = st.session_state.get('uploaded_dfs', {})
1073
- if not uploaded_dfs:
1074
- return "No CSV or Excel files uploaded for analysis."
1075
-
1076
- # For now, use the first uploaded DataFrame
1077
- filename = list(uploaded_dfs.keys())[0]
1078
- df = uploaded_dfs[filename]
1079
-
1080
- # Create a prompt to generate pandas code
1081
- columns_info = []
1082
- for col in df.columns:
1083
- dtype = str(df[col].dtype)
1084
- sample_vals = df[col].dropna().head(3).tolist()
1085
- columns_info.append(f"- {col} ({dtype}): sample values {sample_vals}")
1086
-
1087
- columns_str = "\n".join(columns_info)
1088
-
1089
- analysis_prompt = f"""Given a pandas DataFrame called 'df' with the following structure:
1090
-
1091
- Filename: {filename}
1092
- Shape: {df.shape}
1093
- Columns:
1094
- {columns_str}
1095
-
1096
- Question: {question}
1097
-
1098
- Generate Python code using pandas to answer this question. The code should:
1099
- 1. Use the variable 'df' for the DataFrame
1100
- 2. Be safe to execute (no file operations, imports, etc.)
1101
- 3. Return a result that answers the question
1102
- 4. Handle potential errors gracefully
1103
-
1104
- Provide only the Python code, no explanations:"""
1105
-
1106
- # Get code from LLM
1107
- code_response = llm.invoke(analysis_prompt)
1108
- code = extract_llm_content(code_response)
1109
-
1110
- # Clean up the code
1111
- code = code.strip()
1112
- if code.startswith('```python'):
1113
- code = code[9:]
1114
- if code.startswith('```'):
1115
- code = code[3:]
1116
- if code.endswith('```'):
1117
- code = code[:-3]
1118
- code = code.strip()
1119
-
1120
- # Execute the code safely
1121
- try:
1122
- # Create a safe execution environment
1123
- safe_globals = {
1124
- 'df': df,
1125
- 'pd': pd,
1126
- 'np': __import__('numpy'),
1127
- 'len': len,
1128
- 'str': str,
1129
- 'int': int,
1130
- 'float': float,
1131
- 'sum': sum,
1132
- 'max': max,
1133
- 'min': min,
1134
- 'round': round
1135
- }
1136
-
1137
- # Execute the code
1138
- local_vars = {}
1139
- exec(code, safe_globals, local_vars)
1140
-
1141
- # Try to get the result
1142
- if 'result' in local_vars:
1143
- result = local_vars['result']
1144
- else:
1145
- # If no explicit result variable, evaluate the last expression
1146
- lines = code.strip().split('\n')
1147
- last_line = lines[-1].strip()
1148
- if not (last_line.startswith('print') or last_line.startswith('df')):
1149
- result = eval(last_line, safe_globals, local_vars)
1150
- else:
1151
- result = "Code executed successfully, but no result to display."
1152
-
1153
- # Format the result
1154
- if hasattr(result, 'to_string'): # DataFrame or Series
1155
- if len(str(result)) > 2000: # Truncate if too long
1156
- result_str = str(result)[:2000] + "...\n(Result truncated)"
1157
- else:
1158
- result_str = str(result)
1159
- else:
1160
- result_str = str(result)
1161
-
1162
- return f"Analysis of {filename}:\n\n{result_str}\n\nCode used:\n```python\n{code}\n```"
1163
-
1164
- except Exception as e:
1165
- return f"Error executing analysis: {str(e)}\n\nGenerated code:\n```python\n{code}\n```"
1166
-
1167
- except Exception as e:
1168
- return f"Error in data analysis: {str(e)}"
1169
-
1170
- data_analysis_tool = Tool(
1171
- name="analyze_data",
1172
- func=analyze_data_tool,
1173
- description="Analyze uploaded CSV/Excel data using pandas operations. Can perform calculations, filtering, grouping, and statistical analysis."
1174
- )
1175
-
1176
- # --- Initialize Multi-Agent ---
1177
-
1178
- # --- Enhanced Multi-Agent with Context-Aware Reasoning ---
1179
-
1180
- from langchain.memory import ConversationBufferMemory
1181
- multi_tools = [sql_query_tool, sql_gen_tool, document_qa_tool, data_analysis_tool]
1182
- conversation_memory = ConversationBufferMemory(
1183
- memory_key="chat_history",
1184
- return_messages=True
1185
- )
1186
- multi_agent = initialize_agent(
1187
- tools=multi_tools,
1188
- llm=llm,
1189
- agent=AgentType.CONVERSATIONAL_REACT_DESCRIPTION,
1190
- memory=conversation_memory,
1191
- verbose=True
1192
- )
1193
-
1194
- # Helper: Analyze sales drop for a product using previous DataFrame
1195
- def analyze_sales_drop(product_name=None, product_id=None):
1196
- # Use last DataFrame and question from session state
1197
- last_df = st.session_state.get('last_result_df')
1198
- last_question = st.session_state.get('last_data_question', '')
1199
- if last_df is None:
1200
- return "No previous data available for analysis."
1201
- # Try to detect sales drop by comparing sales over time
1202
- if product_name and 'Product Name' in last_df.columns:
1203
- df_product = last_df[last_df['Product Name'] == product_name]
1204
- elif product_id and 'Product ID' in last_df.columns:
1205
- df_product = last_df[last_df['Product ID'] == product_id]
1206
- else:
1207
- df_product = last_df
1208
- # If sales data available
1209
- if 'Revenue (USD)' in df_product.columns and 'Month' in df_product.columns:
1210
- trend = df_product.groupby('Month')['Revenue (USD)'].sum().sort_index()
1211
- if len(trend) > 1:
1212
- diffs = trend.diff().fillna(0)
1213
- drop_months = [m for m, d in zip(trend.index, diffs) if d < 0]
1214
- if drop_months:
1215
- return f"Sales dropped in months: {', '.join(map(str, drop_months))}. Trend: {trend.to_dict()}"
1216
- else:
1217
- return f"No sales drop detected. Trend: {trend.to_dict()}"
1218
- else:
1219
- return "Not enough data to detect sales trend."
1220
- return "Sales data not available for this product."
1221
-
1222
- tab1, tab2, tab3, tab4 = st.tabs(["SQL Chat Agent", "Prompt Tuning Agent", "PDF Q&A Agent", "Multi-Agent Q&A"])
1223
-
1224
-
1225
- # --- PDF Q&A Agent Tab ---
1226
- with tab3:
1227
- st.markdown("### PDF Q&A Agent")
1228
- st.write("Ask questions about the uploaded PDF. The agent will answer using semantic search and LLM.")
1229
- pdf_files = list(st.session_state.get('uploaded_texts', {}).keys())
1230
- if not pdf_files:
1231
- st.warning("No PDF uploaded. Please upload a PDF in the sidebar.")
1232
- else:
1233
- selected_pdf = st.selectbox("Select PDF to query:", pdf_files)
1234
- pdf_text = st.session_state['uploaded_texts'][selected_pdf]
1235
- user_pdf_question = st.text_input("Ask a question about the PDF:")
1236
- if user_pdf_question:
1237
- def chunk_text(text, chunk_size=800, overlap=100):
1238
- chunks = []
1239
- start = 0
1240
- while start < len(text):
1241
- end = min(start + chunk_size, len(text))
1242
- chunk = text[start:end]
1243
- chunks.append(chunk)
1244
- start += chunk_size - overlap
1245
- return chunks
1246
- pdf_chunks = chunk_text(pdf_text)
1247
- chunk_embeddings = embedding_model.encode(pdf_chunks, convert_to_numpy=True)
1248
- from numpy import dot
1249
- from numpy.linalg import norm
1250
- q_emb = embedding_model.encode([user_pdf_question], convert_to_numpy=True)[0]
1251
- sims = [dot(q_emb, chunk_emb)/(norm(q_emb)*norm(chunk_emb)) for chunk_emb in chunk_embeddings]
1252
- best_idx = int(max(range(len(sims)), key=lambda i: sims[i]))
1253
- best_chunk = pdf_chunks[best_idx]
1254
- context_prompt = f"Context from PDF:\n{best_chunk}\n\nQuestion: {user_pdf_question}\nAnswer:"
1255
- answer = llm.invoke(context_prompt)
1256
- answer_text = extract_llm_content(answer)
1257
-
1258
- st.markdown("**PDF Q&A Response:**")
1259
- st.write(answer_text)
1260
-
1261
- with tab4:
1262
- st.markdown("### Multi-Agent Q&A")
1263
- st.write("Ask any complex question. The agent will use SQL, prompt tuning, and PDF Q&A tools as needed.")
1264
- user_multi_question = st.text_input("Ask a question across tools (SQL + PDF + Prompt):")
1265
- # multi_agent is already initialized above
1266
- if user_multi_question:
1267
- # Context-aware follow-up detection
1268
- followup_keywords = ["why sales got reduce", "why did sales drop", "reason for sales drop", "sales decrease"]
1269
- matched = any(k in user_multi_question.lower() for k in followup_keywords)
1270
- product_name = None
1271
- product_id = None
1272
- import re
1273
- name_match = re.search(r'product name ([\w\s]+)', user_multi_question.lower())
1274
- id_match = re.search(r'productid (\d+)', user_multi_question.lower())
1275
- if name_match:
1276
- product_name = name_match.group(1).strip()
1277
- if id_match:
1278
- product_id = id_match.group(1).strip()
1279
- if matched:
1280
- analysis = analyze_sales_drop(product_name=product_name, product_id=product_id)
1281
- st.markdown(f"**Sales Drop Analysis:**\n{analysis}")
1282
- st.session_state['chat_history'].append({'role': 'user', 'content': user_multi_question})
1283
- st.session_state['chat_history'].append({'role': 'agent', 'content': analysis})
1284
- else:
1285
- response = multi_agent.run(user_multi_question)
1286
- st.markdown("**Agent Response:**")
1287
- st.write(response)
1288
- st.session_state['chat_history'].append({'role': 'user', 'content': user_multi_question})
1289
- st.session_state['chat_history'].append({'role': 'agent', 'content': response})
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
app_bak2108.py DELETED
@@ -1,872 +0,0 @@
1
- import os
2
- import pandas as pd
3
- import streamlit as st
4
- #from st_mic_recorder import mic_recorder
5
- from streamlit_mic_recorder import mic_recorder
6
-
7
-
8
- # ✅ Fix OpenMP library conflict (common with PyTorch/NumPy)
9
- os.environ['KMP_DUPLICATE_LIB_OK'] = 'TRUE'
10
-
11
- # --- Visualization imports ---
12
- try:
13
- import matplotlib.pyplot as plt
14
- import plotly.express as px
15
- import plotly.graph_objects as go
16
- VISUALIZATION_AVAILABLE = True
17
- except ImportError:
18
- st.warning("📊 Visualization libraries not available. Install: pip install matplotlib plotly")
19
- VISUALIZATION_AVAILABLE = False
20
-
21
- # --- Modular Imports ---
22
- from db_utils import get_engine, get_db
23
- from llm_utils import get_llm, get_sql_prompt
24
- from chroma_utils import get_chroma_client, get_chroma_collection, get_embedding_model
25
- from pdf_utils import extract_pdf_text
26
-
27
- # --- Voice functionality imports (with error handling) ---
28
- # (No need to import from agents, these are defined locally)
29
- import json
30
- from difflib import SequenceMatcher
31
- from langchain.chains import LLMChain
32
-
33
- # ✅ Custom CSS for Styling
34
- st.markdown(
35
- """
36
- <style>
37
- /* Background and font styles */
38
- body {
39
- background-color: #f4f4f4;
40
- font-family: 'Arial', sans-serif;
41
- }
42
-
43
- /* Title Styling */
44
- .title {
45
- color: #2E86C1;
46
- text-align: center;
47
- font-size: 40px;
48
- font-weight: bold;
49
- }
50
-
51
- /* Text Area Styling */
52
- .stTextArea > div > textarea {
53
- border-radius: 10px;
54
- border: 2px solid #3498db;
55
- padding: 10px;
56
- font-size: 16px;
57
- height: 150px !important; /* Increased height */
58
- }
59
-
60
- /* Button Styling */
61
- .stButton > button {
62
- background-color: #3498db;
63
- color: white;
64
- font-size: 18px;
65
- border-radius: 10px;
66
- padding: 10px 20px;
67
- border: none;
68
- transition: 0.3s;
69
- }
70
-
71
- .stButton > button:hover {
72
- background-color: #2980b9;
73
- }
74
-
75
- /* SQL Query Box */
76
- .stCode {
77
- background-color: #1e272e;
78
- color: #ecf0f1;
79
- font-size: 16px;
80
- border-radius: 10px;
81
- }
82
-
83
- /* Dataframe Table */
84
- .stDataFrame {
85
- border-radius: 10px;
86
- overflow: hidden;
87
- box-shadow: 2px 2px 10px rgba(0, 0, 0, 0.1);
88
- }
89
- </style>
90
- """,
91
- unsafe_allow_html=True
92
- )
93
-
94
- # ✅ Streamlit Chat UI with Session History
95
- st.markdown('<h3 class="title">Multi-Model RAG System</h3>', unsafe_allow_html=True)
96
- st.write(" Chat with the agent. Your session history will be preserved below.")
97
-
98
- # Initialize session state for chat history
99
- if 'chat_history' not in st.session_state:
100
- st.session_state['chat_history'] = []
101
- # Each item: {'role': 'user'/'agent', 'content': str, 'sql': str or None, 'df': DataFrame or None}
102
-
103
- # Initialize session state for voice input
104
- if 'pending_voice_query' not in st.session_state:
105
- st.session_state['pending_voice_query'] = None
106
-
107
- ## --- Modular Setup ---
108
- engine = get_engine()
109
- db = get_db(engine)
110
- llm = get_llm()
111
- sql_prompt = get_sql_prompt()
112
-
113
- # ✅ Function to Format Schema as Markdown
114
- def format_schema_markdown(schema_info):
115
- import re
116
- tables = re.findall(r'CREATE TABLE \"([^\"]+)\" \((.*?)\)', schema_info, re.DOTALL)
117
- md = '**Database Tables and Columns:**\n'
118
- for table, cols in tables:
119
- col_names = re.findall(r'\"([^\"]+)\"', cols)
120
- md += f'- **{table}**: ' + ', '.join(f'`{col}`' for col in col_names) + '\\n'
121
- return md
122
-
123
- # Helper: Detect if a question is a follow-up
124
- followup_starters = [
125
- 'and ', 'also', 'from which', 'which', 'what about', 'where', 'who', 'how many', 'show me those', 'give me those'
126
- ]
127
- def is_followup_question(q):
128
- ql = q.strip().lower()
129
- return any(ql.startswith(st) for st in followup_starters)
130
-
131
- PROMPT_JSONL_PATH = 'prompts.jsonl'
132
- def load_prompt_examples(jsonl_path):
133
- examples = []
134
- try:
135
- with open(jsonl_path, 'r', encoding='utf-8') as f:
136
- for line in f:
137
- examples.append(json.loads(line))
138
- except Exception:
139
- pass
140
- return examples
141
-
142
- prompt_examples = load_prompt_examples(PROMPT_JSONL_PATH)
143
-
144
- # Initialize ChromaDB and embedding model with persistence
145
- try:
146
- with st.spinner("🔄 Initializing persistent vector database..."):
147
- chroma_client = get_chroma_client()
148
- chroma_collection = get_chroma_collection(chroma_client)
149
- embedding_model = get_embedding_model()
150
-
151
- # Try to initialize from existing JSONL if database is empty
152
- from chroma_utils import initialize_from_jsonl, check_database_stats
153
- initialize_from_jsonl(PROMPT_JSONL_PATH)
154
-
155
- # Show database statistics
156
- stats = check_database_stats()
157
- if stats["status"] == "healthy":
158
- st.success(f"✅ Vector database ready: {stats['total_items']} prompts loaded")
159
- else:
160
- st.warning(f"⚠️ Database status: {stats['status']}")
161
-
162
- except Exception as e:
163
- st.error(f"❌ Vector search initialization failed: {str(e)}")
164
- chroma_client = None
165
- chroma_collection = None
166
- embedding_model = None
167
-
168
- # (No need to import run_query_chain; define and use locally)
169
-
170
- with st.sidebar:
171
- st.markdown('---')
172
- st.markdown('### Upload PDF, CSV, or Excel files to query their content')
173
- uploaded_files = st.file_uploader(
174
- "Upload one or more files (PDF, CSV, Excel)",
175
- type=["pdf", "csv", "xlsx", "xls"],
176
- accept_multiple_files=True
177
- )
178
- if 'uploaded_dfs' not in st.session_state:
179
- st.session_state['uploaded_dfs'] = {}
180
- if 'uploaded_texts' not in st.session_state:
181
- st.session_state['uploaded_texts'] = {}
182
- for uploaded_file in uploaded_files or []:
183
- filename = uploaded_file.name
184
- if filename.endswith('.csv'):
185
- df = pd.read_csv(uploaded_file)
186
- st.session_state['uploaded_dfs'][filename] = df
187
- st.success(f"CSV loaded: {filename} (shape: {df.shape})")
188
- elif filename.endswith(('.xlsx', '.xls')):
189
- df = pd.read_excel(uploaded_file)
190
- st.session_state['uploaded_dfs'][filename] = df
191
- st.success(f"Excel loaded: {filename} (shape: {df.shape})")
192
- elif filename.endswith('.pdf'):
193
- text = extract_pdf_text(uploaded_file)
194
- st.session_state['uploaded_texts'][filename] = text
195
- st.success(f"PDF loaded: {filename} (chars: {len(text)})")
196
-
197
- # Show uploaded DataFrames
198
- if st.session_state['uploaded_dfs']:
199
- st.markdown('#### Uploaded DataFrames:')
200
- for fname, df in st.session_state['uploaded_dfs'].items():
201
- st.write(f"**{fname}**")
202
- st.dataframe(df.head())
203
- # Show uploaded PDF text (first 500 chars)
204
- if st.session_state['uploaded_texts']:
205
- st.markdown('#### Uploaded PDF Text (preview):')
206
- for fname, text in st.session_state['uploaded_texts'].items():
207
- st.write(f"**{fname}**: {text[:500]}{'...' if len(text) > 500 else ''}")
208
-
209
-
210
- # === Local Definition: run_query_chain ===
211
- def run_query_chain(user_query):
212
- schema_info = db.get_table_info()
213
- # Check for meta/schema questions
214
- meta_keywords = [
215
- 'list of tables', 'show tables', 'schema details', 'database schema', 'table names', 'what tables', 'describe schema', 'show schema', 'database structure', 'column', 'columns'
216
- ]
217
- if any(kw in user_query.lower() for kw in meta_keywords):
218
- st.write("[DEBUG] Detected meta/schema question.")
219
- return None, format_schema_markdown(schema_info)
220
- # Check for direct table details or table preview requests
221
- import re
222
- table_match = re.search(r'(?:give me|show|display|list|fetch|get) (.+?)(?: details)?$', user_query.lower())
223
- if table_match:
224
- stopwords = {'me', 'the', 'a', 'an', 'all', 'of', 'my', 'our', 'their', 'this', 'that'}
225
- table_candidate = table_match.group(1)
226
- table_candidate = ' '.join([w for w in table_candidate.split() if w not in stopwords]).strip()
227
- # Remove trailing 'table' or 'data' if present
228
- for suffix in [' table', ' data']:
229
- if table_candidate.lower().endswith(suffix):
230
- table_candidate = table_candidate[: -len(suffix)].strip()
231
- schema_tables = ['Location', 'Product', 'Sales']
232
- matched_table = None
233
- for t in schema_tables:
234
- if table_candidate.lower() == t.lower():
235
- matched_table = t
236
- break
237
- if matched_table:
238
- try:
239
- df = pd.read_sql(f'SELECT * FROM "{matched_table}" LIMIT 10', engine)
240
- st.session_state['last_result_df'] = df
241
- return f'SELECT * FROM "{matched_table}" LIMIT 10', df
242
- except Exception as e:
243
- st.write(f"[DEBUG] SQL Error: {e}")
244
- return None, f"SQL Error: {e}"
245
- # --- Enhanced follow-up logic ---
246
- if 'last_data_question' in st.session_state and is_followup_question(user_query):
247
- context = st.session_state['last_data_question']
248
- last_df = st.session_state.get('last_result_df')
249
- st.write(f"[DEBUG] Follow-up detected. Last context: {context}")
250
- if last_df is not None and 'Product Name' in last_df.columns:
251
- product_names = last_df['Product Name'].tolist()
252
- if product_names:
253
- in_clause = ', '.join([f'"{p}"' for p in product_names])
254
- sql = f'SELECT * FROM "Product" WHERE "Product Name" IN ({in_clause})'
255
- try:
256
- df = pd.read_sql(sql, engine)
257
- st.session_state['last_result_df'] = df
258
- return sql, df
259
- except Exception as e:
260
- st.write(f"[DEBUG] SQL Error (follow-up): {e}")
261
- return sql, f"SQL Error: {e}"
262
- user_query = f"Previous question: {context}\nFollow-up: {user_query}"
263
- # Check for similar prompt in ChromaDB (semantic search)
264
- # (You can add semantic search logic here if needed)
265
- # Check for similar prompt in prompts.jsonl (string similarity)
266
- best_example, score = None, 0
267
- for ex in prompt_examples:
268
- s = SequenceMatcher(None, user_query.lower(), ex['prompt'].lower()).ratio()
269
- if s > score:
270
- score = s
271
- best_example = ex
272
- if best_example and score > 0.85:
273
- try:
274
- df = pd.read_sql(best_example['sql'], engine)
275
- st.session_state['last_result_df'] = df
276
- return best_example['sql'], df
277
- except Exception as e:
278
- st.write(f"[DEBUG] SQL Error (string sim): {e}")
279
- return best_example['sql'], f"SQL Error: {e}"
280
- # Context-aware follow-up: prepend last non-meta user question if this is a follow-up
281
- if 'last_data_question' in st.session_state and is_followup_question(user_query):
282
- context = st.session_state['last_data_question']
283
- user_query = f"Previous question: {context}\nFollow-up: {user_query}"
284
- sql_generation_chain = LLMChain(llm=llm, prompt=sql_prompt)
285
- generated_sql = sql_generation_chain.invoke({"question": user_query, "schema": schema_info})
286
- if isinstance(generated_sql, dict):
287
- for key in ["sql", "text", "output", "result"]:
288
- if key in generated_sql and isinstance(generated_sql[key], str):
289
- generated_sql = generated_sql[key]
290
- break
291
- else:
292
- generated_sql = str(generated_sql)
293
- if isinstance(generated_sql, str) and not generated_sql.strip().lower().startswith("select"):
294
- for line in generated_sql.splitlines():
295
- if line.strip().lower().startswith("select"):
296
- generated_sql = line.strip()
297
- break
298
- if not (generated_sql.strip().lower().startswith("select")):
299
- fallback_query = f"Write a SQL query for: {user_query}"
300
- generated_sql = sql_generation_chain.invoke({"question": fallback_query, "schema": schema_info})
301
- if isinstance(generated_sql, dict):
302
- for key in ["sql", "text", "output", "result"]:
303
- if key in generated_sql and isinstance(generated_sql[key], str):
304
- generated_sql = generated_sql[key]
305
- break
306
- else:
307
- generated_sql = str(generated_sql)
308
- if isinstance(generated_sql, str) and not generated_sql.strip().lower().startswith("select"):
309
- for line in generated_sql.splitlines():
310
- if line.strip().lower().startswith("select"):
311
- generated_sql = line.strip()
312
- break
313
- if not (generated_sql.strip().lower().startswith("select")):
314
- st.write("[DEBUG] No valid SQL generated.")
315
- return None, "No SQL query could be generated for your request. Please rephrase or be more specific."
316
- try:
317
- df = pd.read_sql(generated_sql, engine)
318
- st.session_state['last_data_question'] = user_query if not is_followup_question(user_query) else st.session_state.get('last_data_question', user_query)
319
- st.session_state['last_result_df'] = df
320
- # --- Add new prompt/SQL to prompts.jsonl ---
321
- with open(PROMPT_JSONL_PATH, "a", encoding="utf-8") as f:
322
- f.write(json.dumps({"prompt": user_query, "sql": generated_sql}) + "\n")
323
- return generated_sql, df
324
- except Exception as e:
325
- return generated_sql, f"SQL Error: {e}"
326
-
327
- def create_data_visualization(df: pd.DataFrame, chart_key: str):
328
- """
329
- Create visualization options for DataFrame
330
- """
331
- if not VISUALIZATION_AVAILABLE or df.empty:
332
- return
333
-
334
- st.markdown("### 📊 Data Visualization")
335
-
336
- # Create visualization options
337
- col1, col2, col3 = st.columns(3)
338
-
339
- with col1:
340
- show_bar = st.checkbox("📊 Bar Chart", key=f"bar_{chart_key}")
341
- with col2:
342
- show_pie = st.checkbox("🥧 Pie Chart", key=f"pie_{chart_key}")
343
- with col3:
344
- show_line = st.checkbox("📈 Line Chart", key=f"line_{chart_key}")
345
-
346
- if show_bar or show_pie or show_line:
347
- # Get column options for visualization
348
- numeric_cols = df.select_dtypes(include=['number']).columns.tolist()
349
- text_cols = df.select_dtypes(include=['object', 'string']).columns.tolist()
350
-
351
- col_x, col_y = st.columns(2)
352
-
353
- with col_x:
354
- x_col = st.selectbox(
355
- "Select X-axis column:",
356
- options=df.columns.tolist(),
357
- key=f"x_col_{chart_key}"
358
- )
359
-
360
- with col_y:
361
- y_col = st.selectbox(
362
- "Select Y-axis column:",
363
- options=numeric_cols if numeric_cols else df.columns.tolist(),
364
- key=f"y_col_{chart_key}"
365
- )
366
-
367
- if x_col and y_col:
368
- try:
369
- # Create visualizations based on selection
370
- if show_bar:
371
- st.subheader("📊 Bar Chart")
372
- if len(df) > 20:
373
- # For large datasets, aggregate or show top 20
374
- plot_df = df.groupby(x_col)[y_col].sum().reset_index().head(20)
375
- else:
376
- plot_df = df
377
-
378
- fig_bar = px.bar(plot_df, x=x_col, y=y_col,
379
- title=f"{y_col} by {x_col}")
380
- st.plotly_chart(fig_bar, use_container_width=True)
381
-
382
- if show_pie:
383
- st.subheader("🥧 Pie Chart")
384
- # Aggregate data for pie chart
385
- if len(df) > 10:
386
- # Show top 10 categories for pie chart
387
- pie_df = df.groupby(x_col)[y_col].sum().reset_index()
388
- pie_df = pie_df.nlargest(10, y_col)
389
- else:
390
- pie_df = df.groupby(x_col)[y_col].sum().reset_index()
391
-
392
- fig_pie = px.pie(pie_df, values=y_col, names=x_col,
393
- title=f"{y_col} Distribution by {x_col}")
394
- st.plotly_chart(fig_pie, use_container_width=True)
395
-
396
- if show_line:
397
- st.subheader("📈 Line Chart")
398
- fig_line = px.line(df, x=x_col, y=y_col,
399
- title=f"{y_col} Trend by {x_col}")
400
- st.plotly_chart(fig_line, use_container_width=True)
401
-
402
- except Exception as e:
403
- st.error(f"Visualization error: {e}")
404
- st.info("💡 Tip: Make sure you select appropriate columns for the chart type")
405
-
406
- # === Chat Input Section ===
407
- st.markdown("---")
408
-
409
- user_query = None
410
- #Add voice icon
411
- st.write("🎤 Click to record your question")
412
- voice_input = mic_recorder(
413
- start_prompt="Start Recording",
414
- stop_prompt="Stop Recording",
415
- just_once=True,
416
- use_container_width=True
417
- )
418
-
419
- if voice_input:
420
- st.write("✅ Recorded audio!")
421
- # If you want to convert speech to text, send this audio to an ASR model
422
-
423
- #==========GPT5
424
- from transformers import pipeline
425
- import tempfile
426
-
427
- asr = pipeline("automatic-speech-recognition", model="openai/whisper-small")
428
-
429
- if voice_input:
430
- with tempfile.NamedTemporaryFile(delete=False, suffix=".wav") as tmp_file:
431
- tmp_file.write(voice_input["bytes"])
432
- tmp_file_path = tmp_file.name
433
- result = asr(tmp_file_path)
434
- query_text = result["text"]
435
- st.write(f"🗣 You said: {query_text}")
436
-
437
-
438
- # Check for pending voice query from session state
439
- if 'pending_voice_query' in st.session_state and st.session_state['pending_voice_query']:
440
- user_query = st.session_state['pending_voice_query']
441
- # Clear the pending query to avoid reprocessing
442
- st.session_state['pending_voice_query'] = None
443
-
444
- # Regular text input
445
- text_query = st.chat_input("💬 Ask about your business data or upload files to analyze...")
446
- if text_query:
447
- user_query = text_query
448
-
449
- # When user submits a query
450
- if user_query:
451
- generated_sql, df_results = run_query_chain(user_query)
452
- st.session_state['chat_history'].append({
453
- 'role': 'user',
454
- 'content': user_query,
455
- 'sql': None,
456
- 'df': None
457
- })
458
- st.session_state['chat_history'].append({
459
- 'role': 'agent',
460
- 'content': "Here is the generated SQL and results.",
461
- 'sql': generated_sql,
462
- 'df': df_results
463
- })
464
-
465
- # Add a button to display the schema in the chat UI
466
- if st.button('Show Database Schema'):
467
- schema_info = db.get_table_info()
468
- st.session_state['chat_history'].append({
469
- 'role': 'agent',
470
- 'content': format_schema_markdown(schema_info),
471
- 'sql': None,
472
- 'df': None
473
- })
474
-
475
- # Show all prompts in ChromaDB
476
- if st.button("Show all prompts in vector DB"):
477
- if chroma_collection is not None:
478
- try:
479
- chroma_data = chroma_collection.get()
480
- st.write("Prompts in Vector DB:")
481
- for i, doc in enumerate(chroma_data.get('documents', [])):
482
- sql = chroma_data.get('metadatas', [])[i].get('sql', '')
483
- st.write(f"{i+1}. Prompt: {doc}")
484
- st.code(sql, language='sql')
485
- except Exception as e:
486
- st.error(f"Error accessing vector DB: {e}")
487
- else:
488
- st.warning("Vector DB not available. Please check ChromaDB initialization.")
489
-
490
- # Display chat history
491
- for msg in st.session_state['chat_history']:
492
- if msg['role'] == 'user':
493
- with st.chat_message('user'):
494
- st.markdown(msg.get('content', ''))
495
- else:
496
- with st.chat_message('assistant'):
497
- sql = msg.get('sql')
498
- df = msg.get('df')
499
- response_content = msg.get('content', '')
500
-
501
- # If the agent's response is a SQL query, show it and the result
502
- if sql and isinstance(sql, str) and sql.strip().lower().startswith('select'):
503
- st.markdown(response_content)
504
- st.code(sql, language='sql')
505
-
506
- if isinstance(df, pd.DataFrame):
507
- if df.empty:
508
- st.info('No results found for this query.')
509
- else:
510
- # Add visualization options above the dataframe
511
- if VISUALIZATION_AVAILABLE:
512
- create_data_visualization(df, f"viz1_{hash(sql)}")
513
-
514
- st.subheader("📋 Query Results")
515
- st.dataframe(df)
516
-
517
- # --- Save as CSV and View buttons ---
518
- col1, col2 = st.columns(2)
519
- with col1:
520
- csv_filename = "chat_agent_output.csv"
521
- st.download_button(
522
- label="Download as CSV",
523
- data=df.to_csv(index=False).encode('utf-8'),
524
- file_name=csv_filename,
525
- mime='text/csv',
526
- key=f"csv_{sql}"
527
- )
528
- with col2:
529
- view_name = st.text_input(f"Enter view name to save as DB view (for this result):", key=f"view_{sql}")
530
- if st.button("Save as View", key=f"btn_view_{sql}") and view_name:
531
- safe_view_name = ''.join(c for c in view_name if c.isalnum() or c == '_')
532
- create_view_sql = f'''CREATE VIEW IF NOT EXISTS "{safe_view_name}" AS {sql.strip().rstrip(';')};'''
533
- try:
534
- engine.execute(create_view_sql)
535
- st.success(f"View '{safe_view_name}' created successfully!")
536
- except Exception as ve:
537
- st.error(f"Error creating view: {ve}")
538
- elif isinstance(df, str) and df.startswith('SQL Error:'):
539
- st.error(df)
540
- # If the agent's response is a plain message (not SQL), show as markdown only
541
- elif sql and not sql.strip().lower().startswith('select'):
542
- st.markdown(sql)
543
- else:
544
- st.markdown(response_content)
545
- if isinstance(df, pd.DataFrame):
546
- if df.empty:
547
- st.info('No results found for this query.')
548
- else:
549
- # Add visualization options above the dataframe
550
- if VISUALIZATION_AVAILABLE:
551
- create_data_visualization(df, f"viz2_{hash(msg.get('content', ''))}")
552
-
553
- st.subheader("📋 Query Results")
554
- st.dataframe(df)
555
-
556
- # --- Save as CSV and View buttons ---
557
- col1, col2 = st.columns(2)
558
- with col1:
559
- csv_filename = "chat_agent_output.csv"
560
- st.download_button(
561
- label="Download as CSV",
562
- data=df.to_csv(index=False).encode('utf-8'),
563
- file_name=csv_filename,
564
- mime='text/csv',
565
- key=f"csv_{msg.get('content', '')}"
566
- )
567
- with col2:
568
- view_name = st.text_input(f"Enter view name to save as DB view (for this result):", key=f"view_{msg.get('content', '')}")
569
- if st.button("Save as View", key=f"btn_view_{msg.get('content', '')}") and view_name:
570
- safe_view_name = ''.join(c for c in view_name if c.isalnum() or c == '_')
571
- # Use the last SQL if available
572
- sql_to_use = sql if sql else ""
573
- create_view_sql = f'''CREATE VIEW IF NOT EXISTS "{safe_view_name}" AS {sql_to_use.strip().rstrip(';')};'''
574
- try:
575
- engine.execute(create_view_sql)
576
- st.success(f"View '{safe_view_name}' created successfully!")
577
- except Exception as ve:
578
- st.error(f"Error creating view: {ve}")
579
- elif isinstance(df, str) and df.startswith('SQL Error:'):
580
- st.error(df)
581
-
582
- # === Prompt Tuning Agent Section ===
583
- from langchain.prompts import PromptTemplate
584
-
585
- # Helper: Suggest a better prompt using LLM
586
- prompt_tuning_template = PromptTemplate(
587
- input_variables=["draft", "schema"],
588
- template="""
589
- You are an expert SQL query writer and prompt improvement assistant for a SQLite database. Given the following database schema:
590
- {schema}
591
-
592
- The user has written this draft prompt/question:
593
- "{draft}"
594
-
595
- Your job is to:
596
- - Suggest a clearer, more specific, and effective prompt that will help generate a correct SQL query for SQLite.
597
- - Use the exact table and column names as shown in the schema (including case, spaces, and special characters).
598
- - If the draft is already good, return it as is. Otherwise, rewrite it to be more explicit, mentioning relevant tables/columns if possible.
599
- - If the user's intent requires columns from multiple tables, make sure the improved prompt is clear about the relationship (e.g., join keys) and the aggregation or ranking needed.
600
- - Only return the improved prompt, no explanations.
601
-
602
- Example:
603
- Draft: top 3 product with highest revenue
604
- Improved: Show the top 3 products by total revenue, using the "Product Name" from the "Product" table and summing "Revenue (USD)" from the "Sales" table. Join "Product" and "Sales" on "Product ID". Order by total revenue descending and limit to 3 results.
605
- """
606
- )
607
-
608
- def suggest_better_prompt(draft, schema_info):
609
- chain = LLMChain(llm=llm, prompt=prompt_tuning_template)
610
- improved = chain.invoke({"draft": draft, "schema": schema_info})
611
- # Extract string if dict
612
- if isinstance(improved, dict):
613
- for key in ["text", "output", "result"]:
614
- if key in improved and isinstance(improved[key], str):
615
- improved = improved[key]
616
- break
617
- else:
618
- improved = str(improved)
619
- return improved.strip()
620
-
621
- def generate_sql_for_prompt(prompt, schema_info):
622
- # --- Patch: Always refer to schema before generating SQL ---
623
- # If prompt does not mention schema, prepend schema info for LLM context
624
- schema_hint = f"Refer to this schema for table and column names:\n{schema_info}\n"
625
- prompt_with_schema = f"{schema_hint}{prompt}" if 'CREATE TABLE' not in prompt else prompt
626
- sql_chain = LLMChain(llm=llm, prompt=sql_prompt)
627
- sql = sql_chain.invoke({"question": prompt_with_schema, "schema": schema_info})
628
- if isinstance(sql, dict):
629
- for key in ["sql", "text", "output", "result"]:
630
- if key in sql and isinstance(sql[key], str):
631
- sql = sql[key]
632
- break
633
- else:
634
- sql = str(sql)
635
- # Extract SQL if explanation is included
636
- if isinstance(sql, str) and not sql.strip().lower().startswith("select"):
637
- for line in sql.splitlines():
638
- if line.strip().lower().startswith("select"):
639
- sql = line.strip()
640
- break
641
- # --- Patch: Auto-quote all table and column names for SQLite ---
642
- def auto_quote_sql(sql_str, schema_info):
643
- import re
644
- # Find all table and column names from schema
645
- tables = re.findall(r'CREATE TABLE "([^"]+)" \((.*?)\)', schema_info, re.DOTALL)
646
- table_names = [t[0] for t in tables]
647
- col_names = []
648
- for _, cols in tables:
649
- col_names += re.findall(r'"([^"]+)"', cols)
650
- # Quote table names not already quoted
651
- for t in table_names:
652
- # Only quote if not already quoted
653
- sql_str = re.sub(rf'(?<!["])\b{re.escape(t)}\b(?!["])', f'"{t}"', sql_str)
654
- # Quote column names not already quoted
655
- for c in col_names:
656
- # Only quote if not already quoted and not part of a string literal
657
- sql_str = re.sub(rf'(?<![\.\"])(?<!["])\b{re.escape(c)}\b(?!["])', f'"{c}"', sql_str)
658
- return sql_str
659
- sql = auto_quote_sql(sql, schema_info)
660
- return sql.strip()
661
-
662
- def save_prompt_sql_pair(prompt, sql):
663
- # Save to prompts.jsonl
664
- with open(PROMPT_JSONL_PATH, "a", encoding="utf-8") as f:
665
- f.write(json.dumps({"prompt": prompt, "sql": sql}) + "\n")
666
-
667
- # Save to ChromaDB (if available)
668
- if chroma_collection is not None and embedding_model is not None:
669
- try:
670
- query_emb = embedding_model.encode([prompt], convert_to_numpy=True)
671
- existing_ids = chroma_collection.get().get('ids', [])
672
- next_id = str(len(existing_ids) + 1)
673
-
674
- chroma_collection.add(
675
- embeddings=query_emb,
676
- documents=[prompt],
677
- metadatas=[{"sql": sql}],
678
- ids=[next_id]
679
- )
680
- except Exception as e:
681
- st.warning(f"Could not save to vector DB: {e}")
682
- else:
683
- st.info("Vector DB not available - saved to local file only")
684
-
685
- # === Streamlit Tabs: Main Agent and Prompt Tuning Agent ===
686
-
687
-
688
- # === Multi-Agent Integration ===
689
- from langchain.agents import initialize_agent, AgentType, Tool
690
-
691
- # --- SQL Query Tool ---
692
- def run_sql_query(sql: str) -> str:
693
- # Always limit result size for LLM safety
694
- import re
695
- sql_lc = sql.lower()
696
- if sql_lc.startswith("select") and "limit" not in sql_lc:
697
- # Add LIMIT 20 to queries without a limit
698
- if ";" in sql:
699
- sql = re.sub(r';\s*$', '', sql)
700
- sql += " LIMIT 20"
701
- try:
702
- df = pd.read_sql(sql, engine)
703
- return df.to_csv(index=False)
704
- except Exception as e:
705
- return f"SQL Error: {e}"
706
-
707
- sql_query_tool = Tool(
708
- name="run_sql_query",
709
- func=run_sql_query,
710
- description="Execute a SQL query on the business database and return results as CSV."
711
- )
712
-
713
- # --- SQL Generation Tool ---
714
- def generate_sql_tool(question: str) -> str:
715
- sql = generate_sql_for_prompt(question, db.get_table_info())
716
- return sql
717
-
718
- sql_gen_tool = Tool(
719
- name="generate_sql",
720
- func=generate_sql_tool,
721
- description="Convert a natural language question to an optimized SQL query for the business database."
722
- )
723
-
724
- # --- PDF Q&A Tool ---
725
- def ask_pdf_tool(question: str) -> str:
726
- pdf_files = list(st.session_state.get('uploaded_texts', {}).keys())
727
- if not pdf_files:
728
- return "No PDF uploaded."
729
- pdf_text = st.session_state['uploaded_texts'][pdf_files[0]]
730
- def chunk_text(text, chunk_size=800, overlap=100):
731
- chunks = []
732
- start = 0
733
- while start < len(text):
734
- end = min(start + chunk_size, len(text))
735
- chunk = text[start:end]
736
- chunks.append(chunk)
737
- start += chunk_size - overlap
738
- return chunks
739
- pdf_chunks = chunk_text(pdf_text)
740
- chunk_embeddings = embedding_model.encode(pdf_chunks, convert_to_numpy=True)
741
- from numpy import dot
742
- from numpy.linalg import norm
743
- q_emb = embedding_model.encode([question], convert_to_numpy=True)[0]
744
- sims = [dot(q_emb, chunk_emb)/(norm(q_emb)*norm(chunk_emb)) for chunk_emb in chunk_embeddings]
745
- best_idx = int(max(range(len(sims)), key=lambda i: sims[i]))
746
- best_chunk = pdf_chunks[best_idx]
747
- context_prompt = f"Context from PDF:\n{best_chunk}\n\nQuestion: {question}\nAnswer:"
748
- answer = llm.invoke(context_prompt)
749
- if isinstance(answer, dict):
750
- answer = answer.get('text', str(answer))
751
- return answer
752
-
753
- pdf_qa_tool = Tool(
754
- name="ask_pdf",
755
- func=ask_pdf_tool,
756
- description="Answer questions based on the uploaded PDF content using semantic search and LLM."
757
- )
758
-
759
- # --- Initialize Multi-Agent ---
760
-
761
- # --- Enhanced Multi-Agent with Context-Aware Reasoning ---
762
-
763
- from langchain.memory import ConversationBufferMemory
764
- multi_tools = [sql_query_tool, sql_gen_tool, pdf_qa_tool]
765
- conversation_memory = ConversationBufferMemory(
766
- memory_key="chat_history",
767
- return_messages=True
768
- )
769
- multi_agent = initialize_agent(
770
- tools=multi_tools,
771
- llm=llm,
772
- agent=AgentType.CONVERSATIONAL_REACT_DESCRIPTION,
773
- memory=conversation_memory,
774
- verbose=True
775
- )
776
-
777
- # Helper: Analyze sales drop for a product using previous DataFrame
778
- def analyze_sales_drop(product_name=None, product_id=None):
779
- # Use last DataFrame and question from session state
780
- last_df = st.session_state.get('last_result_df')
781
- last_question = st.session_state.get('last_data_question', '')
782
- if last_df is None:
783
- return "No previous data available for analysis."
784
- # Try to detect sales drop by comparing sales over time
785
- if product_name and 'Product Name' in last_df.columns:
786
- df_product = last_df[last_df['Product Name'] == product_name]
787
- elif product_id and 'Product ID' in last_df.columns:
788
- df_product = last_df[last_df['Product ID'] == product_id]
789
- else:
790
- df_product = last_df
791
- # If sales data available
792
- if 'Revenue (USD)' in df_product.columns and 'Month' in df_product.columns:
793
- trend = df_product.groupby('Month')['Revenue (USD)'].sum().sort_index()
794
- if len(trend) > 1:
795
- diffs = trend.diff().fillna(0)
796
- drop_months = [m for m, d in zip(trend.index, diffs) if d < 0]
797
- if drop_months:
798
- return f"Sales dropped in months: {', '.join(map(str, drop_months))}. Trend: {trend.to_dict()}"
799
- else:
800
- return f"No sales drop detected. Trend: {trend.to_dict()}"
801
- else:
802
- return "Not enough data to detect sales trend."
803
- return "Sales data not available for this product."
804
-
805
- tab1, tab2, tab3, tab4 = st.tabs(["SQL Chat Agent", "Prompt Tuning Agent", "PDF Q&A Agent", "Multi-Agent Q&A"])
806
-
807
-
808
- # --- PDF Q&A Agent Tab ---
809
- with tab3:
810
- st.markdown("### PDF Q&A Agent")
811
- st.write("Ask questions about the uploaded PDF. The agent will answer using semantic search and LLM.")
812
- pdf_files = list(st.session_state.get('uploaded_texts', {}).keys())
813
- if not pdf_files:
814
- st.warning("No PDF uploaded. Please upload a PDF in the sidebar.")
815
- else:
816
- selected_pdf = st.selectbox("Select PDF to query:", pdf_files)
817
- pdf_text = st.session_state['uploaded_texts'][selected_pdf]
818
- user_pdf_question = st.text_input("Ask a question about the PDF:")
819
- if user_pdf_question:
820
- def chunk_text(text, chunk_size=800, overlap=100):
821
- chunks = []
822
- start = 0
823
- while start < len(text):
824
- end = min(start + chunk_size, len(text))
825
- chunk = text[start:end]
826
- chunks.append(chunk)
827
- start += chunk_size - overlap
828
- return chunks
829
- pdf_chunks = chunk_text(pdf_text)
830
- chunk_embeddings = embedding_model.encode(pdf_chunks, convert_to_numpy=True)
831
- from numpy import dot
832
- from numpy.linalg import norm
833
- q_emb = embedding_model.encode([user_pdf_question], convert_to_numpy=True)[0]
834
- sims = [dot(q_emb, chunk_emb)/(norm(q_emb)*norm(chunk_emb)) for chunk_emb in chunk_embeddings]
835
- best_idx = int(max(range(len(sims)), key=lambda i: sims[i]))
836
- best_chunk = pdf_chunks[best_idx]
837
- context_prompt = f"Context from PDF:\n{best_chunk}\n\nQuestion: {user_pdf_question}\nAnswer:"
838
- answer = llm.invoke(context_prompt)
839
- if isinstance(answer, dict):
840
- answer = answer.get('text', str(answer))
841
- st.markdown("**PDF Q&A Response:**")
842
- st.write(answer)
843
-
844
- with tab4:
845
- st.markdown("### Multi-Agent Q&A")
846
- st.write("Ask any complex question. The agent will use SQL, prompt tuning, and PDF Q&A tools as needed.")
847
- user_multi_question = st.text_input("Ask a question across tools (SQL + PDF + Prompt):")
848
- # multi_agent is already initialized above
849
- if user_multi_question:
850
- # Context-aware follow-up detection
851
- followup_keywords = ["why sales got reduce", "why did sales drop", "reason for sales drop", "sales decrease"]
852
- matched = any(k in user_multi_question.lower() for k in followup_keywords)
853
- product_name = None
854
- product_id = None
855
- import re
856
- name_match = re.search(r'product name ([\w\s]+)', user_multi_question.lower())
857
- id_match = re.search(r'productid (\d+)', user_multi_question.lower())
858
- if name_match:
859
- product_name = name_match.group(1).strip()
860
- if id_match:
861
- product_id = id_match.group(1).strip()
862
- if matched:
863
- analysis = analyze_sales_drop(product_name=product_name, product_id=product_id)
864
- st.markdown(f"**Sales Drop Analysis:**\n{analysis}")
865
- st.session_state['chat_history'].append({'role': 'user', 'content': user_multi_question})
866
- st.session_state['chat_history'].append({'role': 'agent', 'content': analysis})
867
- else:
868
- response = multi_agent.run(user_multi_question)
869
- st.markdown("**Agent Response:**")
870
- st.write(response)
871
- st.session_state['chat_history'].append({'role': 'user', 'content': user_multi_question})
872
- st.session_state['chat_history'].append({'role': 'agent', 'content': response})
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
chromadb_persistent/A.TXT DELETED
@@ -1 +0,0 @@
1
- TEST
 
 
llm_utils.py CHANGED
@@ -5,7 +5,7 @@ import os
5
 
6
  def get_llm():
7
  return ChatGroq(
8
- model="llama-3.3-70b-versatile",
9
  temperature=0.0,
10
  max_retries=2,
11
  api_key=os.environ.get("groq_key")
 
5
 
6
  def get_llm():
7
  return ChatGroq(
8
+ model="llama3-8b-8192",
9
  temperature=0.0,
10
  max_retries=2,
11
  api_key=os.environ.get("groq_key")
pdf_utils.py CHANGED
@@ -4,96 +4,9 @@ try:
4
  except ImportError:
5
  PyPDF2 = None
6
 
7
- try:
8
- from docx import Document
9
- except ImportError:
10
- Document = None
11
-
12
- def extract_csv_text(uploaded_file):
13
- try:
14
- df = pd.read_csv(uploaded_file)
15
- # Create a summary: columns, shape, and first few rows
16
- summary = f"CSV Columns: {', '.join(df.columns)}\nShape: {df.shape}\nPreview:\n{df.head().to_string(index=False)}"
17
- return summary, None
18
- except Exception as e:
19
- return None, f"CSV extraction error: {e}"
20
-
21
- def extract_excel_text(uploaded_file):
22
- try:
23
- df = pd.read_excel(uploaded_file)
24
- summary = f"Excel Columns: {', '.join(df.columns)}\nShape: {df.shape}\nPreview:\n{df.head().to_string(index=False)}"
25
- return summary, None
26
- except Exception as e:
27
- return None, f"Excel extraction error: {e}"
28
-
29
- def extract_json_text(uploaded_file):
30
- try:
31
- import json
32
- # Read the JSON file
33
- json_data = json.load(uploaded_file)
34
-
35
- # Create a summary based on JSON structure
36
- if isinstance(json_data, list):
37
- # Array of objects
38
- summary = f"JSON Array with {len(json_data)} items\n"
39
- if json_data and isinstance(json_data[0], dict):
40
- keys = list(json_data[0].keys())
41
- summary += f"Keys in first object: {', '.join(keys)}\n"
42
- summary += f"Sample data:\n{json.dumps(json_data[:3], indent=2)}"
43
- else:
44
- summary += f"First few items:\n{json.dumps(json_data[:5], indent=2)}"
45
- elif isinstance(json_data, dict):
46
- # Single object
47
- summary = f"JSON Object with keys: {', '.join(json_data.keys())}\n"
48
- summary += f"Data:\n{json.dumps(json_data, indent=2)[:1000]}..."
49
- else:
50
- # Simple value
51
- summary = f"JSON Value:\n{json.dumps(json_data, indent=2)}"
52
-
53
- return summary, None
54
- except Exception as e:
55
- return None, f"JSON extraction error: {e}"
56
- import pandas as pd
57
- try:
58
- import PyPDF2
59
- except ImportError:
60
- PyPDF2 = None
61
-
62
  def extract_pdf_text(uploaded_file):
63
  if PyPDF2 is None:
64
  return None, "PyPDF2 not installed. PDF support unavailable."
65
-
66
- try:
67
- pdf_reader = PyPDF2.PdfReader(uploaded_file)
68
- text = "\n".join(page.extract_text() or '' for page in pdf_reader.pages)
69
-
70
- # Check if any text was extracted
71
- if not text or text.strip() == '':
72
- return None, "No text could be extracted from this PDF. The PDF might be image-based or encrypted."
73
-
74
- return text, None
75
- except Exception as e:
76
- return None, f"PDF extraction error: {str(e)}"
77
-
78
- def extract_docx_text(uploaded_file):
79
- """Extract text from DOCX files"""
80
- if Document is None:
81
- return None, "python-docx not installed. DOCX support unavailable."
82
-
83
- try:
84
- doc = Document(uploaded_file)
85
- paragraphs = []
86
-
87
- for paragraph in doc.paragraphs:
88
- if paragraph.text.strip():
89
- paragraphs.append(paragraph.text.strip())
90
-
91
- text = "\n".join(paragraphs)
92
-
93
- # Check if any text was extracted
94
- if not text or text.strip() == '':
95
- return None, "No text could be extracted from this DOCX file."
96
-
97
- return text, None
98
- except Exception as e:
99
- return None, f"DOCX extraction error: {str(e)}"
 
4
  except ImportError:
5
  PyPDF2 = None
6
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7
  def extract_pdf_text(uploaded_file):
8
  if PyPDF2 is None:
9
  return None, "PyPDF2 not installed. PDF support unavailable."
10
+ pdf_reader = PyPDF2.PdfReader(uploaded_file)
11
+ text = "\n".join(page.extract_text() or '' for page in pdf_reader.pages)
12
+ return text, None
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
pdf_utils_bak2108.py DELETED
@@ -1,12 +0,0 @@
1
- import pandas as pd
2
- try:
3
- import PyPDF2
4
- except ImportError:
5
- PyPDF2 = None
6
-
7
- def extract_pdf_text(uploaded_file):
8
- if PyPDF2 is None:
9
- return None, "PyPDF2 not installed. PDF support unavailable."
10
- pdf_reader = PyPDF2.PdfReader(uploaded_file)
11
- text = "\n".join(page.extract_text() or '' for page in pdf_reader.pages)
12
- return text, None