genieprotech/development
0
1import os2import streamlit as st3import psycopg24from psycopg2.extras import RealDictCursor5import pandas as pd6from datetime import datetime, timedelta7import re8 9# Set page config10st.set_page_config(11 page_title="Neon DB Manager",12 page_icon="🦚",13 layout="wide"14)15 16# Your connection string (with sensitive parts masked for security)17CONNECTION_STRING = 'postgresql://neondb_owner:npg_Eil6jTIv4osb@ep-jolly-smoke-aiygb2rr-pooler.c-4.us-east-1.aws.neon.tech/neondb?sslmode=require&channel_binding=require'18 19class DatabaseManager:20 def __init__(self, connection_string):21 self.connection_string = connection_string22 self.conn = None23 self.connect()24 25 def connect(self):26 """Establish connection to Neon DB"""27 try:28 self.conn = psycopg2.connect(self.connection_string)29 return True30 except Exception as e:31 st.error(f"❌ Connection failed: {str(e)}")32 return False33 34 def execute_query(self, query, params=None, fetch=False):35 """Execute SQL query safely"""36 if not self.conn or self.conn.closed:37 self.connect()38 39 cursor = None40 try:41 cursor = self.conn.cursor(cursor_factory=RealDictCursor)42 cursor.execute(query, params or ())43 44 if fetch:45 if query.strip().upper().startswith('SELECT'):46 result = cursor.fetchall()47 else:48 self.conn.commit()49 if cursor.description:50 result = cursor.fetchall()51 else:52 result = cursor.rowcount53 else:54 self.conn.commit()55 result = cursor.rowcount56 57 return result58 except Exception as e:59 if self.conn:60 self.conn.rollback()61 st.error(f"Query error: {str(e)}")62 return None63 finally:64 if cursor:65 cursor.close()66######67 68 def get_filtered_products(self, filters=None, sort_by=None, sort_order='ASC', limit=100, offset=0):69 """Get products with advanced filtering"""70 query = "SELECT * FROM products WHERE 1=1"71 params = []72 73 if filters:74 # Text search filter75 if filters.get('search'):76 query += " AND product_description ILIKE %s"77 params.append(f"%{filters['search']}%")78 79 # ID range filter80 if filters.get('min_id'):81 query += " AND product_id >= %s"82 params.append(filters['min_id'])83 if filters.get('max_id'):84 query += " AND product_id <= %s"85 params.append(filters['max_id'])86 87 # Date range filter88 if filters.get('date_from'):89 query += " AND DATE(created_at) >= %s"90 params.append(filters['date_from'])91 if filters.get('date_to'):92 query += " AND DATE(created_at) <= %s"93 params.append(filters['date_to'])94 95 # Exact match filter96 if filters.get('exact_description'):97 query += " AND product_description = %s"98 params.append(filters['exact_description'])99 100 # Length filter101 if filters.get('min_length'):102 query += " AND LENGTH(product_description) >= %s"103 params.append(filters['min_length'])104 if filters.get('max_length'):105 query += " AND LENGTH(product_description) <= %s"106 params.append(filters['max_length'])107 108 # Contains all words filter109 if filters.get('all_words'):110 words = filters['all_words'].split()111 for word in words:112 query += " AND product_description ILIKE %s"113 params.append(f"%{word}%")114 115 # Contains any word filter116 if filters.get('any_word'):117 words = filters['any_word'].split()118 if words:119 query += " AND ("120 for i, word in enumerate(words):121 if i > 0:122 query += " OR "123 query += "product_description ILIKE %s"124 params.append(f"%{word}%")125 query += ")"126 127 # Sorting128 if sort_by:129 valid_columns = ['product_id', 'product_description', 'created_at', 'LENGTH(product_description)']130 if sort_by in valid_columns or sort_by == 'length':131 if sort_by == 'length':132 sort_column = 'LENGTH(product_description)'133 else:134 sort_column = sort_by135 query += f" ORDER BY {sort_column} {sort_order}"136 else:137 query += " ORDER BY product_id DESC"138 139 # Limit140 query += f" LIMIT {limit}"141 142 return self.execute_query(query, params, fetch=True) or []143 144 145######146 def check_connection(self):147 """Check if database connection is working"""148 try:149 cursor = self.conn.cursor()150 cursor.execute("SELECT 1;")151 cursor.close()152 return True153 except:154 return False155 156 def get_table_info(self):157 """Get information about tables in the database"""158 query = """159 SELECT 160 table_name,161 column_name,162 data_type,163 is_nullable,164 column_default165 FROM information_schema.columns166 WHERE table_schema = 'public'167 ORDER BY table_name, ordinal_position;168 """169 return self.execute_query(query, fetch=True)170 171 def create_products_table(self):172 """Create products table if it doesn't exist"""173 query = """174 CREATE TABLE IF NOT EXISTS products (175 product_id SERIAL PRIMARY KEY,176 product_description TEXT NOT NULL,177 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP178 );179 """180 return self.execute_query(query)181 182 def insert_product(self, description):183 """Insert a new product"""184 query = """185 INSERT INTO products (product_description) 186 VALUES (%s)187 RETURNING product_id, product_description, created_at;188 """189 result = self.execute_query(query, (description,), fetch=True)190 return result[0] if result else None191 192 def get_all_products(self, limit=None):193 """Get all products"""194 query = "SELECT * FROM products ORDER BY product_id"195 if limit:196 query += f" LIMIT {limit}"197 query += ";"198 return self.execute_query(query, fetch=True) or []199 200 def update_product(self, product_id, new_description):201 """Update a product"""202 query = """203 UPDATE products 204 SET product_description = %s 205 WHERE product_id = %s206 RETURNING product_id, product_description;207 """208 result = self.execute_query(query, (new_description, product_id), fetch=True)209 return result[0] if result else None210 211 def delete_product(self, product_id):212 """Delete a product"""213 query = """214 DELETE FROM products 215 WHERE product_id = %s 216 RETURNING product_id, product_description;217 """218 result = self.execute_query(query, (product_id,), fetch=True)219 return result[0] if result else None220 221 def search_products(self, search_term):222 """Search products by description"""223 query = """224 SELECT * FROM products 225 WHERE product_description ILIKE %s 226 ORDER BY product_id;227 """228 return self.execute_query(query, (f"%{search_term}%",), fetch=True) or []229 230 def get_product_count(self):231 """Get total count of products"""232 query = "SELECT COUNT(*) as count FROM products;"233 result = self.execute_query(query, fetch=True)234 return result[0]['count'] if result else 0235 236 def close(self):237 """Close the connection"""238 if self.conn and not self.conn.closed:239 self.conn.close()240####241 def get_date_range(self):242 """Get min and max dates from products table"""243 query = """244 SELECT 245 MIN(DATE(created_at)) as min_date,246 MAX(DATE(created_at)) as max_date247 FROM products;248 """249 result = self.execute_query(query, fetch=True)250 return result[0] if result else {'min_date': None, 'max_date': None}251 252####253# Initialize the app254def init_app():255 """Initialize the application"""256 """Initialize session state variables"""257 258 259 # Initialize database connection in session state260 if 'db' not in st.session_state:261 st.session_state.db = DatabaseManager(CONNECTION_STRING)262 263 # Create products table if it doesn't exist264 st.session_state.db.create_products_table()265 266 # Initialize other session state variables267 if 'refresh_trigger' not in st.session_state:268 st.session_state.refresh_trigger = False269 270 if 'last_refresh' not in st.session_state:271 st.session_state.last_refresh = datetime.now()272 273 if 'current_page' not in st.session_state:274 st.session_state.current_page = "📋 View Products"275 276 277 if 'filters' not in st.session_state:278 st.session_state.filters = {}279 280 if 'filter_count' not in st.session_state:281 st.session_state.filter_count = 0282 283 if 'selected_product' not in st.session_state:284 st.session_state.selected_product = None285 286 if 'show_filters' not in st.session_state:287 st.session_state.show_filters = True288 289 if 'filter_expand_state' not in st.session_state:290 st.session_state.filter_expand_state = {291 'advanced_text': False,292 'id_range': True,293 'date_range': True,294 'length_filter': False295 }296 297 if 'filter_presets' not in st.session_state:298 st.session_state.filter_presets = {}299 300 if 'active_preset' not in st.session_state:301 st.session_state.active_preset = None302 303 if 'sort_config' not in st.session_state:304 st.session_state.sort_config = {'column': 'product_id', 'order': 'ASC', 'column_label': 'ID'}305 306 # PAGINATION - Add this missing state307 if 'page_number' not in st.session_state:308 st.session_state.page_number = 1309 310 if 'results_per_page' not in st.session_state:311 st.session_state.results_per_page = 50312 313 # CACHE - Add this for better performance314 if 'filtered_products' not in st.session_state:315 st.session_state.filtered_products = None316 317 # PAGINATION STATE - You have page_number and results_per_page but missing:318 if 'total_products' not in st.session_state:319 st.session_state.total_products = 0320 321 if 'total_pages' not in st.session_state:322 st.session_state.total_pages = 1323 324 if 'offset' not in st.session_state:325 st.session_state.offset = 0326 327 # FILTERED PRODUCTS CACHE328 if 'filtered_products' not in st.session_state:329 st.session_state.filtered_products = None330 331 if 'last_query_time' not in st.session_state:332 st.session_state.last_query_time = None333 334 # SORT STATE - Fix: You have sort_config but missing sort_applied flag335 if 'sort_applied' not in st.session_state:336 st.session_state.sort_applied = False337 338 # FILTER PRESETS STATE - You have filter_presets but missing:339 if 'preset_names' not in st.session_state:340 st.session_state.preset_names = []341 342 # COLUMN VISIBILITY STATE - Missing entirely343 if 'column_visibility' not in st.session_state:344 st.session_state.column_visibility = {345 'product_id': True,346 'product_description': True,347 'created_at': True,348 'length': False349 }350 351 # EXPORT STATE - Missing352 if 'export_format' not in st.session_state:353 st.session_state.export_format = 'csv'354 355 # NOTIFICATION STATE - Missing356 if 'notification' not in st.session_state:357 st.session_state.notification = None358 359 # ERROR STATE - Missing360 if 'error_log' not in st.session_state:361 st.session_state.error_log = []362 363 if 'ai_query_input' not in st.session_state:364 st.session_state.ai_query_input = ""365 366# Main app function367def main():368 """Main Streamlit application"""369 370 init_app()371 372 # Title and header373 st.title("🦚 Neon Database Manager")374 st.markdown("Manage your PostgreSQL database with this intuitive interface")375 376 # Sidebar377 with st.sidebar:378 st.image("https://neon.tech/logo-dark.svg", width=150)379 380 st.header("📊 Dashboard")381 382 # Connection status383 if st.session_state.db.check_connection():384 st.success("✅ Connected to Neon DB")385 else:386 st.error("❌ Connection lost")387 if st.button("Reconnect"):388 st.session_state.db.connect()389 st.rerun()390 391 # Quick stats392 product_count = st.session_state.db.get_product_count()393 st.metric("Total Products", product_count)394 395 st.divider()396 397 # Navigation398 st.header("🔧 Operations")399 page = st.radio(400 "Choose operation:",401 ["📋 View Products", "🔍 Search", "➕ Add Product", "✏️ Update", "🗑️ Delete", "⚙️ Database Info"]402 )403 404 st.divider()405 406 # Filter407 st.header("🔍 Filters")408 409 # Clear filters button410 col1, col2 = st.columns([3, 1])411 with col1:412 if st.button("🧹 Clear All Filters", use_container_width=True):413 st.session_state.filters = {}414 st.session_state.filter_count = 0415 st.rerun()416 with col2:417 st.metric("Active", st.session_state.filter_count)418 419 st.divider()420#421 # Search filter422 st.subheader("🔎 Text Search")423 search = st.text_input(424 "Search in description",425 value=st.session_state.filters.get('search', ''),426 placeholder="Enter keywords...",427 key="search_filter"428 )429 if search:430 st.session_state.filters['search'] = search431 elif 'search' in st.session_state.filters:432 del st.session_state.filters['search']433 434 # Advanced text filters435 with st.expander("📝 Advanced Text Filters"):436 # Exact match437 exact = st.text_input(438 "Exact description",439 value=st.session_state.filters.get('exact_description', ''),440 placeholder="Match exactly...",441 key="exact_filter"442 )443 if exact:444 st.session_state.filters['exact_description'] = exact445 elif 'exact_description' in st.session_state.filters:446 del st.session_state.filters['exact_description']447 448 # Contains all words449 all_words = st.text_input(450 "Contains ALL words",451 value=st.session_state.filters.get('all_words', ''),452 placeholder="word1 word2 ...",453 key="all_words_filter"454 )455 if all_words:456 st.session_state.filters['all_words'] = all_words457 elif 'all_words' in st.session_state.filters:458 del st.session_state.filters['all_words']459 460 # Contains any word461 any_word = st.text_input(462 "Contains ANY word",463 value=st.session_state.filters.get('any_word', ''),464 placeholder="word1 word2 ...",465 key="any_word_filter"466 )467 if any_word:468 st.session_state.filters['any_word'] = any_word469 elif 'any_word' in st.session_state.filters:470 del st.session_state.filters['any_word']471 472 st.divider()473############## 474 # ID Range Filter475 st.subheader("🆔 ID Range")476 col1, col2 = st.columns(2)477 with col1:478 min_id = st.number_input(479 "Min ID",480 min_value=1,481 value=st.session_state.filters.get('min_id', 1),482 key="min_id_filter"483 )484 if min_id > 1:485 st.session_state.filters['min_id'] = min_id486 elif 'min_id' in st.session_state.filters:487 del st.session_state.filters['min_id']488 489 with col2:490 max_id = st.number_input(491 "Max ID",492 min_value=1,493 value=st.session_state.filters.get('max_id', 1000),494 key="max_id_filter"495 )496 if max_id < 1000:497 st.session_state.filters['max_id'] = max_id498 elif 'max_id' in st.session_state.filters:499 del st.session_state.filters['max_id']500 501 st.divider()502 503 # Date Range Filter504 st.subheader("📅 Date Range")505 date_range = st.session_state.db.get_date_range()506 507 if date_range['min_date'] and date_range['max_date']:508 col1, col2 = st.columns(2)509 with col1:510 date_from = st.date_input(511 "From",512 value=st.session_state.filters.get('date_from', date_range['min_date']),513 min_value=date_range['min_date'],514 max_value=date_range['max_date'],515 key="date_from_filter"516 )517 st.session_state.filters['date_from'] = date_from518 519 with col2:520 date_to = st.date_input(521 "To",522 value=st.session_state.filters.get('date_to', date_range['max_date']),523 min_value=date_range['min_date'],524 max_value=date_range['max_date'],525 key="date_to_filter"526 )527 st.session_state.filters['date_to'] = date_to528 else:529 st.info("No date data available")530 531 st.divider()532 533 # Description Length Filter534 st.subheader("📏 Description Length")535 col1, col2 = st.columns(2)536 with col1:537 min_length = st.number_input(538 "Min chars",539 min_value=0,540 value=st.session_state.filters.get('min_length', 0),541 key="min_length_filter"542 )543 if min_length > 0:544 st.session_state.filters['min_length'] = min_length545 elif 'min_length' in st.session_state.filters:546 del st.session_state.filters['min_length']547 548 with col2:549 max_length = st.number_input(550 "Max chars",551 min_value=0,552 value=st.session_state.filters.get('max_length', 500),553 key="max_length_filter"554 )555 if max_length < 500:556 st.session_state.filters['max_length'] = max_length557 elif 'max_length' in st.session_state.filters:558 del st.session_state.filters['max_length']559 560 st.divider()561 562 # Sorting options563 st.subheader("↕️ Sort By")564 col1, col2 = st.columns(2)565 with col1:566 sort_column = st.selectbox(567 "Column",568 options=['product_id', 'product_description', 'created_at', 'length'],569 format_func=lambda x: {570 'product_id': 'ID',571 'product_description': 'Description',572 'created_at': 'Date Created',573 'length': 'Description Length'574 }.get(x, x),575 index=0,576 key="sort_column"577 )578 st.session_state.sort_config['column'] = sort_column579 580 with col2:581 sort_order = st.selectbox(582 "Order",583 options=['DESC', 'ASC'],584 format_func=lambda x: 'Descending' if x == 'DESC' else 'Ascending',585 index=0,586 key="sort_order"587 )588 st.session_state.sort_config['order'] = sort_order589 590 # Results per page591 st.divider()592 st.subheader("📊 Results")593 limit = st.slider(594 "Products per page",595 min_value=10,596 max_value=200,597 value=50,598 step=10,599 key="results_limit"600 )601 602 # Update filter count603 st.session_state.filter_count = len([k for k in st.session_state.filters.keys() 604 if k not in ['date_from', 'date_to'] or 605 (k == 'date_from' and st.session_state.filters.get('date_from') != date_range.get('min_date')) or606 (k == 'date_to' and st.session_state.filters.get('date_to') != date_range.get('max_date'))])607 608############## 609# 610 # Quick actions611 st.header("⚡ Quick Actions")612 if st.button("🔄 Refresh All Data"):613 st.session_state.refresh_trigger = not st.session_state.refresh_trigger614 st.rerun()615 616 if st.button("📊 Add Sample Data"):617 sample_products = [618 "Compact Printer Air Adapter",619 "Wireless Bluetooth Mouse",620 "USB-C Fast Charging Cable",621 "Noise Cancelling Headphones",622 "Portable Power Bank 20000mAh",623 "Mechanical Keyboard RGB",624 "4K Webcam with Microphone",625 "Wireless Charging Pad",626 "Smart Watch Fitness Tracker",627 "Bluetooth Speaker Waterproof"628 ]629 for product in sample_products:630 st.session_state.db.insert_product(product)631 st.success("Added 10 sample products!")632 st.rerun()633 634 # Main content area635 if page == "📋 View Products":636 view_products_page()637 elif page == "🔍 Search":638 search_page()639 elif page == "➕ Add Product":640 add_product_page()641 elif page == "✏️ Update":642 update_product_page()643 elif page == "🗑️ Delete":644 delete_product_page()645 elif page == "⚙️ Database Info":646 database_info_page()647 648 649def view_products_page():650 """Display products with filters and pagination"""651 st.header("📋 Products")652 653 # Get filter parameters from session state654 filters = st.session_state.filters655 sort_column = st.session_state.sort_config['column']656 sort_order = st.session_state.sort_config['order']657 limit = st.session_state.results_per_page658 page = st.session_state.page_number659 660 # Refresh button661 col1, col2, col3, col4 = st.columns([2, 1, 1, 1])662 with col2:663 if st.button("🔄 Refresh", use_container_width=True):664 st.session_state.filtered_products = None665 st.rerun()666 667 # ===== ACTIVE FILTERS DISPLAY =====668 if st.session_state.filters:669 st.markdown("**Active Filters:**")670 filter_chips = []671 672 for key, value in st.session_state.filters.items():673 if key == 'search':674 filter_chips.append(f"🔍 Contains: '{value}'")675 elif key == 'exact_description':676 filter_chips.append(f"📝 Exact: '{value}'")677 elif key == 'all_words':678 filter_chips.append(f"📚 All words: '{value}'")679 elif key == 'any_word':680 filter_chips.append(f"🔤 Any word: '{value}'")681 elif key == 'min_id':682 filter_chips.append(f"🆔 ID ≥ {value}")683 elif key == 'max_id':684 filter_chips.append(f"🆔 ID ≤ {value}")685 elif key == 'date_from':686 filter_chips.append(f"📅 From: {value}")687 elif key == 'date_to':688 filter_chips.append(f"📅 To: {value}")689 elif key == 'min_length':690 filter_chips.append(f"📏 Min length: {value}")691 elif key == 'max_length':692 filter_chips.append(f"📏 Max length: {value}")693 694 # Display filter chips in columns695 cols = st.columns(min(len(filter_chips), 4))696 for i, chip in enumerate(filter_chips[:4]):697 with cols[i % 4]:698 st.markdown(f"`{chip}`")699 700 if len(filter_chips) > 4:701 st.caption(f"... and {len(filter_chips) - 4} more filters")702 703 # Clear filters button704 if st.button("🧹 Clear All Filters", key="clear_filters_view"):705 st.session_state.filters = {}706 st.session_state.filter_count = 0707 st.session_state.page_number = 1708 st.rerun()709 710 st.divider()711 712 # ===== FETCH FILTERED PRODUCTS =====713 with st.spinner("Loading products..."):714 try:715 products = st.session_state.db.get_filtered_products(716 filters=st.session_state.filters,717 sort_by=sort_column,718 sort_order=sort_order,719 limit=limit720 )721 except Exception as e:722 st.error(f"Error loading products: {str(e)}")723 products = []724 725 # Update total count for pagination726 st.session_state.total_products = len(products)727 st.session_state.total_pages = max(1, (st.session_state.total_products + limit - 1) // limit)728 729 if products:730 # Convert to DataFrame731 df = pd.DataFrame(products)732 733 # Add length column734 if 'product_description' in df.columns:735 df['length'] = df['product_description'].str.len()736 737 # Format datetime738 if 'created_at' in df.columns:739 df['created_at'] = pd.to_datetime(df['created_at']).dt.strftime('%Y-%m-%d %H:%M:%S')740 741 # ===== METRICS ROW =====742 col1, col2, col3, col4 = st.columns(4)743 with col1:744 start_idx = (page - 1) * limit + 1745 end_idx = min(page * limit, st.session_state.total_products)746 st.metric("Showing", f"{start_idx}-{end_idx}")747 with col2:748 st.metric("Total", st.session_state.total_products)749 with col3:750 st.metric("Page", f"{page}/{st.session_state.total_pages}")751 with col4:752 st.metric("Active Filters", st.session_state.filter_count)753 754 # ===== DATA TABLE =====755 st.dataframe(756 df[['product_id', 'product_description', 'created_at', 'length']],757 use_container_width=True,758 hide_index=True,759 column_config={760 "product_id": st.column_config.NumberColumn(761 "ID",762 width="small",763 help="Product ID"764 ),765 "product_description": st.column_config.TextColumn(766 "Description",767 width="large",768 help="Product description"769 ),770 "created_at": st.column_config.DatetimeColumn(771 "Created",772 width="medium",773 format="YYYY-MM-DD HH:mm:ss"774 ),775 "length": st.column_config.NumberColumn(776 "Len",777 width="small",778 help="Description length in characters"779 )780 }781 )782 783 # ===== AI ASSISTANT SECTION =====784 st.divider()785 st.subheader("🤖 AI Product Assistant")786 787 # Create two columns for AI chat788 ai_col1, ai_col2 = st.columns([3, 1])789 790 with ai_col1:791 # AI query input792 ai_query = st.text_area(793 "Ask about your products:",794 placeholder="E.g., 'Summarize these products', 'Find patterns in the descriptions', 'Suggest categories', 'Identify common features', 'What are the shortest descriptions?', etc.",795 height=80,796 key="ai_query_input"797 )798 799 with ai_col2:800 st.write("") # Spacer801 st.write("") # Spacer802 ask_button = st.button("🔮 Ask AI", type="primary", use_container_width=True)803 804 # Process AI query805 if ask_button and ai_query:806 with st.spinner("🤖 AI is analyzing your products..."):807 # Prepare product data for AI808 product_list = []809 for _, row in df.iterrows():810 product_list.append(f"ID {row['product_id']}: {row['product_description']}")811 812 products_text = "\n".join(product_list)813 814 # Generate AI response based on query type815 response = generate_ai_response(ai_query, products_text, df)816 817 # Display response in a nice container818 with st.container(border=True):819 st.markdown("### 🤖 AI Response")820 st.markdown(response)821 822 # Add copy button823 st.button(824 "📋 Copy Response",825 on_click=lambda: st.write("Response copied to clipboard!"),826 key="copy_ai_response"827 )828 829 # Quick AI suggestion chips830 st.markdown("**Quick questions:**")831 suggestion_cols = st.columns(4)832 suggestions = [833 "📊 Summarize products",834 "🔤 Shortest names",835 "📏 Longest names", 836 "🏷️ Suggest categories",837 "🔄 Find duplicates",838 "✨ Common words",839 "📈 Statistics",840 "🎯 Patterns"841 ]842 843 for i, suggestion in enumerate(suggestions):844 with suggestion_cols[i % 4]:845 if st.button(suggestion, key=f"ai_suggest_{i}", use_container_width=True):846 # Set the query and trigger AI847 st.session_state.ai_query_input = suggestion848 st.rerun()849 850 # ===== EXPORT OPTIONS =====851 with st.expander("📥 Export Data"):852 col1, col2 = st.columns(2)853 with col1:854 csv = df.to_csv(index=False).encode('utf-8')855 st.download_button(856 "📥 Download CSV",857 csv,858 f"products_filtered_{datetime.now().strftime('%Y%m%d_%H%M%S')}.csv",859 "text/csv",860 use_container_width=True861 )862 with col2:863 json_str = df.to_json(orient='records', indent=2)864 st.download_button(865 "📥 Download JSON",866 json_str,867 f"products_filtered_{datetime.now().strftime('%Y%m%d_%H%M%S')}.json",868 "application/json",869 use_container_width=True870 )871 else:872 # ===== NO RESULTS =====873 st.info("📭 No products found matching your filters.")874 875 if st.session_state.filters:876 if st.button("🧹 Clear Filters & Try Again"):877 st.session_state.filters = {}878 st.session_state.filter_count = 0879 st.rerun()880 else:881 if st.button("📊 Add Sample Data"):882 sample_products = [883 "Compact Printer Air Adapter",884 "Wireless Bluetooth Mouse",885 "USB-C Fast Charging Cable",886 "Noise Cancelling Headphones",887 "Portable Power Bank 20000mAh",888 "Mechanical Keyboard RGB",889 "4K Webcam with Microphone",890 "Wireless Charging Pad",891 "Smart Watch Fitness Tracker",892 "Bluetooth Speaker Waterproof"893 ]894 for product in sample_products:895 st.session_state.db.insert_product(product)896 st.success("✅ Added 10 sample products!")897 st.rerun()898################899 900# Add this new function to generate AI responses901def generate_ai_response(query, products_text, df):902 """Generate AI response based on product data"""903 query_lower = query.lower()904 905 # Product statistics906 total_products = len(df)907 avg_length = df['length'].mean() if 'length' in df.columns else 0908 min_length = df['length'].min() if 'length' in df.columns else 0909 max_length = df['length'].max() if 'length' in df.columns else 0910 911 # Extract all words for analysis912 all_words = []913 word_freq = {}914 for desc in df['product_description']:915 words = desc.lower().replace('-', ' ').replace(',', '').replace('.', '').split()916 all_words.extend(words)917 for word in words:918 word_freq[word] = word_freq.get(word, 0) + 1919 920 # Get common words (excluding very common words)921 stop_words = {'the', 'a', 'an', 'and', 'or', 'but', 'in', 'on', 'at', 'to', 'for', 'with', 'by', 'of'}922 common_words = {word: count for word, count in word_freq.items() 923 if word not in stop_words and len(word) > 2}924 top_words = sorted(common_words.items(), key=lambda x: x[1], reverse=True)[:10]925 926 # Generate response based on query type927 if 'summarize' in query_lower or 'summary' in query_lower:928 response = f"""📊 **Product Summary**929 930- **Total Products:** {total_products}931- **Description Length:** Avg {avg_length:.1f} chars (Min: {min_length}, Max: {max_length})932- **Most Common Words:** {', '.join([f'{w} ({c})' for w, c in top_words[:5]])}933- **Sample Products:** 934{chr(10).join([' • ' + desc[:60] + '...' for desc in df['product_description'].head(3)])}935 936**Quick Analysis:** This appears to be a collection of {categorize_products(df)}."""937 938 elif 'short' in query_lower or 'shortest' in query_lower:939 shortest = df.nsmallest(3, 'length')940 response = f"""🔤 **Shortest Product Descriptions**941 942{chr(10).join([f"• **ID {row['product_id']}:** {row['product_description']} ({row['length']} chars)" 943 for _, row in shortest.iterrows()])}944 945📝 **Average description length:** {avg_length:.1f} chars"""946 947 elif 'long' in query_lower or 'longest' in query_lower:948 longest = df.nlargest(3, 'length')949 response = f"""📏 **Longest Product Descriptions**950 951{chr(10).join([f"• **ID {row['product_id']}:** {row['product_description'][:80]}... ({row['length']} chars)" 952 for _, row in longest.iterrows()])}953 954📝 **Average description length:** {avg_length:.1f} chars"""955 956 elif 'categor' in query_lower or 'category' in query_lower or 'categories' in query_lower:957 categories = suggest_categories(df)958 response = f"""🏷️ **Suggested Product Categories**959 960{categories}961 962💡 **Tip:** You could add a 'category' column to your database for better organization."""963 964 elif 'duplicate' in query_lower:965 # Check for potential duplicates (similar descriptions)966 duplicates = find_potential_duplicates(df)967 if duplicates:968 response = f"""🔄 **Potential Duplicate Products Found**969 970{duplicates}971 972⚠️ Consider reviewing these products for possible consolidation."""973 else:974 response = "✅ **No obvious duplicate products found.** All descriptions appear to be unique."975 976 elif 'pattern' in query_lower or 'trend' in query_lower:977 response = f"""🎯 **Pattern Analysis**978 979- **Common Prefixes:** {detect_patterns(df)[:200]}...980- **Word Frequency:** Most common terms are {', '.join([w for w, _ in top_words[:5]])}981- **Naming Convention:** Products seem to follow '{detect_naming_pattern(df)}' pattern982 983📊 **Insight:** {generate_insight(df)}"""984 985 elif 'stat' in query_lower or 'metric' in query_lower:986 response = f"""📈 **Product Statistics**987 988| Metric | Value |989|--------|-------|990| Total Products | {total_products} |991| Avg Description Length | {avg_length:.1f} chars |992| Min Description Length | {min_length} chars |993| Max Description Length | {max_length} chars |994| Total Characters | {df['length'].sum()} |995| Unique Words | {len(set(all_words))} |996| Total Words | {len(all_words)} |997 998📊 **Distribution:** {generate_distribution_insight(df)}"""999 1000 else:1001 # Generic response for other queries1002 response = f"""🤖 **AI Analysis Complete**1003 1004Based on your query: "{query}"1005 1006I've analyzed {total_products} products. Here's what I found:1007 1008• **Product Range:** {categorize_products(df)}1009• **Description Quality:** {assess_description_quality(df)}1010• **Recommendation:** {generate_recommendation(df)}1011 1012**Sample of analyzed products:**1013{chr(10).join([f" • ID {row['product_id']}: {row['product_description'][:60]}..." 1014 for _, row in df.head(3).iterrows()])}1015 1016Is there anything specific you'd like to know about these products?"""1017 1018 return response1019 1020# Helper functions for AI analysis1021def categorize_products(df):1022 """Categorize products based on descriptions"""1023 descriptions = ' '.join(df['product_description'].str.lower())1024 1025 categories = []1026 if 'wireless' in descriptions or 'bluetooth' in descriptions:1027 categories.append('Wireless Devices')1028 if 'printer' in descriptions or 'scanner' in descriptions:1029 categories.append('Office Equipment')1030 if 'headphone' in descriptions or 'speaker' in descriptions:1031 categories.append('Audio Devices')1032 if 'cable' in descriptions or 'charger' in descriptions or 'adapter' in descriptions:1033 categories.append('Accessories')1034 if 'keyboard' in descriptions or 'mouse' in descriptions:1035 categories.append('Computer Peripherals')1036 1037 if categories:1038 return 'mixed ' + ', '.join(categories[:3])1039 else:1040 return 'various electronic products'1041 1042 1043def suggest_categories(df):1044 """Suggest categories for products"""1045 categories = {}1046 1047 for desc in df['product_description']:1048 desc_lower = desc.lower()1049 1050 if any(word in desc_lower for word in ['wireless', 'bluetooth', 'wifi']):1051 categories[desc] = '🔵 Wireless'1052 elif any(word in desc_lower for word in ['cable', 'usb', 'charger', 'adapter']):1053 categories[desc] = '🔌 Cables & Chargers'1054 elif any(word in desc_lower for word in ['headphone', 'speaker', 'earbud', 'audio']):1055 categories[desc] = '🎧 Audio'1056 elif any(word in desc_lower for word in ['keyboard', 'mouse', 'webcam', 'monitor']):1057 categories[desc] = '💻 Computer'1058 elif any(word in desc_lower for word in ['printer', 'scanner', 'copier']):1059 categories[desc] = '🖨️ Office'1060 elif any(word in desc_lower for word in ['watch', 'fitness', 'tracker', 'smart']):1061 categories[desc] = '⌚ Wearable'1062 else:1063 categories[desc] = '📱 Electronics'1064 1065 # Format output1066 result = ""1067 for desc, category in list(categories.items())[:5]:1068 result += f"• **{desc[:50]}...** → {category}\n"1069 1070 if len(categories) > 5:1071 result += f"... and {len(categories) - 5} more products"1072 1073 return result1074 1075def detect_patterns(df):1076 """Detect patterns in product names"""1077 words = []1078 for desc in df['product_description']:1079 words.extend(desc.split()[:2]) # First two words1080 1081 from collections import Counter1082 common_starts = Counter(words).most_common(3)1083 1084 patterns = []1085 for word, count in common_starts:1086 patterns.append(f"'{word}' ({count} products)")1087 1088 return ', '.join(patterns) if patterns else 'No strong patterns'1089 1090 1091def detect_naming_pattern(df):1092 """Detect naming convention pattern"""1093 has_brand = any('logitech' in desc.lower() or 'sony' in desc.lower() or 'samsung' in desc.lower() 1094 for desc in df['product_description'])1095 has_model = any(re.search(r'\b[A-Z0-9]{3,}\b', desc) for desc in df['product_description'])1096 has_features = any('with' in desc.lower() or 'plus' in desc.lower() for desc in df['product_description'])1097 1098 if has_brand and has_model:1099 return "Brand + Model"1100 elif has_features:1101 return "Feature-first"1102 else:1103 return "Descriptive"1104 1105 1106def generate_insight(df):1107 """Generate business insight"""1108 total = len(df)1109 avg_len = df['length'].mean()1110 1111 if avg_len < 30:1112 return "Product descriptions are very short. Consider adding more details to improve searchability."1113 elif avg_len < 50:1114 return "Descriptions are adequate but could be more descriptive to stand out."1115 else:1116 return "Product descriptions are detailed and informative, which is good for SEO."1117 1118 1119def assess_description_quality(df):1120 """Assess the quality of descriptions"""1121 avg_len = df['length'].mean()1122 1123 if avg_len < 25:1124 return "⚠️ Too short - needs more detail"1125 elif avg_len < 45:1126 return "📝 Adequate - could be improved"1127 elif avg_len < 70:1128 return "✅ Good length and detail"1129 else:1130 return "🌟 Very detailed descriptions"1131 1132def generate_recommendation(df):1133 """Generate recommendation based on analysis"""1134 avg_len = df['length'].mean()1135 unique_words = set()1136 for desc in df['product_description']:1137 unique_words.update(desc.lower().split())1138 1139 if avg_len < 30:1140 return "Add more descriptive words to improve search visibility"1141 elif len(unique_words) < total_keywords(df) * 0.5:1142 return "Use more varied vocabulary to differentiate products"1143 else:1144 return "Your product descriptions are well-optimized"1145 1146def total_keywords(df):1147 """Calculate total keywords used"""1148 return sum(len(desc.split()) for desc in df['product_description'])1149 1150def generate_distribution_insight(df):1151 """Generate insight about length distribution"""1152 lengths = df['length']1153 quartiles = lengths.quantile([0.25, 0.5, 0.75])1154 1155 return f"25% of products have ≤{quartiles[0.25]:.0f} chars, 50% have ≤{quartiles[0.5]:.0f} chars, 75% have ≤{quartiles[0.75]:.0f} chars"1156 1157def find_potential_duplicates(df):1158 """Find potential duplicate products"""1159 from difflib import SequenceMatcher1160 1161 duplicates = []1162 descriptions = df['product_description'].tolist()1163 ids = df['product_id'].tolist()1164 1165 for i in range(len(descriptions)):1166 for j in range(i + 1, len(descriptions)):1167 similarity = SequenceMatcher(None, descriptions[i].lower(), descriptions[j].lower()).ratio()1168 if similarity > 0.85: # High similarity threshold1169 duplicates.append(f"• ID {ids[i]} and ID {ids[j]}: {similarity:.0%} similar")1170 if len(duplicates) >= 3: # Limit to top 31171 break1172 if len(duplicates) >= 3:1173 break1174 1175 return '\n'.join(duplicates) if duplicates else None1176################1177def search_page():1178 """Search products"""1179 st.header("🔍 Search Products")1180 1181 search_term = st.text_input("Enter search term:", placeholder="Search by description...")1182 1183 if search_term:1184 with st.spinner("Searching..."):1185 results = st.session_state.db.search_products(search_term)1186 1187 if results:1188 st.success(f"Found {len(results)} product(s)")1189 1190 # Display results1191 for product in results:1192 with st.expander(f"ID: {product['product_id']} - {product['product_description'][:50]}..."):1193 col1, col2 = st.columns([1, 3])1194 with col1:1195 st.metric("ID", product['product_id'])1196 with col2:1197 st.text_area("Description", product['product_description'], disabled=True)1198 1199 if 'created_at' in product:1200 st.caption(f"Created: {product['created_at']}")