import os import sys import gradio as gr from dotenv import load_dotenv import pandas as pd from datetime import datetime import io import re import google.generativeai as genai import faiss import numpy as np import time import json import tempfile import pytz # Import pytz for timezone handling # --- Import WeasyPrint --- from weasyprint import HTML, CSS # --- End WeasyPrint import --- from docx import Document as DocxDocument from docx.shared import Inches, Pt, RGBColor from docx.enum.text import WD_ALIGN_PARAGRAPH, WD_PARAGRAPH_ALIGNMENT # --- Python Environment Diagnostics --- print("\n--- Python Environment Diagnostics ---", file=sys.stderr) print(f"Gradio version at runtime: {gr.__version__}", file=sys.stderr) print(f"Python executable: {sys.executable}", file=sys.stderr) print(f"Python PATH: {sys.path}", file=sys.stderr) try: import pkg_resources installed_packages = {p.project_name: p.version for p in pkg_resources.working_set} print(f"Installed packages (excerpt):", file=sys.stderr) for package_name in sorted(installed_packages.keys()): # List relevant packages for diagnostics if any(keyword in package_name.lower() for keyword in ["weasyprint", "gradio", "pandas"]): print(f" {package_name}: {installed_packages[package_name]}", file=sys.stderr) except Exception as e: print(f"Could not list installed packages: {e}", file=sys.stderr) print("--- End of Python Environment Diagnostics ---\n", file=sys.stderr) # Load environment variables load_dotenv() # --- RAG Setup --- rag_initialized = False gemini_llm_model = None gemini_embedding_model_name = "models/embedding-001" google_api_key = os.getenv("GOOGLE_API_KEY") if not google_api_key: print("\n" + "="*80) print("CRITICAL ERROR: GOOGLE_API_KEY environment variable is NOT SET.") print("RAG functionality will NOT be initialized. Please set it in your .env file (local) or Hugging Face Space secrets.") print("="*80 + "\n", file=sys.stderr) # Print to stderr for deployment logs else: print("GOOGLE_API_KEY successfully loaded from environment.") try: genai.configure(api_key=google_api_key) llm_model_id = "gemini-1.5-flash" gemini_llm_model = genai.GenerativeModel(llm_model_id) # Test embedding model access test_embedding = genai.embed_content(model=gemini_embedding_model_name, content=["test"]) if test_embedding and test_embedding['embedding']: rag_initialized = True print(f"Google Generative AI LLM ({llm_model_id}) and embeddings initialized successfully.") else: raise Exception("Failed to get a valid test embedding from the configured API key. Check API key permissions.") except Exception as e: rag_initialized = False print(f"Error initializing Google Generative AI components: {e}", file=sys.stderr) print(f"Please verify your GOOGLE_API_KEY's validity and ensure the specified AI model ('{llm_model_id}' for LLM) and embedding model ('{gemini_embedding_model_name}') are available to your API key.", file=sys.stderr) print("RAG functionality will not work without a valid Google API key and accessible model.", file=sys.stderr) # Global variables to store results for download functions global_analysis_df_for_export = pd.DataFrame() global_raw_markdown_for_export = "" global_html_report_for_export = "" # New global variable to store the full HTML report for PDF conversion # Global variables for filename versioning _last_uploaded_filename_base = None _file_versions = {} _original_total_tickets_count = 0 # New global variable to store the total ticket count from the original file # --- Custom Document Structure --- # Documents are dictionaries: {"page_content": "...", "metadata": {...}} # --- Custom Text Splitter (if ever needed for very long 'page_content') --- def custom_character_text_splitter(text_documents, chunk_size=1000, chunk_overlap=200): """ A simple character-based text splitter. Splits larger texts into smaller chunks with overlap. text_documents: list of dictionaries {"page_content": "...", "metadata": {...}} """ chunks = [] for doc in text_documents: content = doc["page_content"] metadata = doc["metadata"] if len(content) <= chunk_size: chunks.append({"page_content": content, "metadata": metadata}) continue current_position = 0 while current_position < len(content): chunk_end = min(current_position + chunk_size, len(content)) chunk_text = content[current_position:chunk_end] chunks.append({"page_content": chunk_text, "metadata": metadata}) if chunk_end == len(content): break current_position += (chunk_size - chunk_overlap) if current_position >= len(content): break return chunks # --- Custom FAISS Integration (for searching, not direct analysis flow for 800k tickets) --- # This class is included for RAG conceptual completeness, though not directly used in the current two-phase analysis class CustomFAISSVectorStore: def __init__(self, embedding_model_name, dimension=768): # Gemini embedding-001 has 768 dimensions self.embedding_model_name = embedding_model_name self.index = faiss.IndexFlatL2(dimension) # L2 for Euclidean distance self.documents = [] # Store original documents/chunks for retrieval def add_documents(self, documents): if not documents: return contents_to_embed = [doc["page_content"] for doc in documents] # Batch embedding for efficiency and to avoid API limits for large inputs batch_size = 100 # Adjust based on API limits and performance all_embeddings = [] for i in range(0, len(contents_to_embed), batch_size): batch_contents = contents_to_embed[i:i + batch_size] try: embeddings_response = genai.embed_content( model=self.embedding_model_name, content=batch_contents ) all_embeddings.extend(embeddings_response['embedding']) except Exception as e: raise Exception(f"Error during embedding generation for FAISS: {e}") embeddings_np = np.array(all_embeddings).astype('float32') if embeddings_np.shape[1] != self.index.d: raise ValueError(f"Embedding dimension mismatch: Expected {self.index.d}, got {embeddings_np.shape[1]}.") self.index.add(embeddings_np) self.documents.extend(documents) # Store documents in the same order as embeddings def search(self, query_text, k=4): if not query_text: return [] try: query_embedding_response = genai.embed_content( model=self.embedding_model_name, content=[query_text] ) query_embedding = np.array(query_embedding_response['embedding'][0]).astype('float32').reshape(1, -1) except Exception as e: raise Exception(f"Error embedding query: {e}") if query_embedding.shape[1] != self.index.d: raise ValueError(f"Query embedding dimension mismatch: Expected {self.index.d}, got {query_embedding.shape[1]}.") distances, indices = self.index.search(query_embedding, k) retrieved_docs = [] for i in indices[0]: if 0 <= i < len(self.documents): retrieved_docs.append(self.documents[i]) return retrieved_docs def get_ticket_documents_iterator(file_path, file_extension, filename, chunksize=5000): """ Loads and returns text from Excel or CSV files in chunks (iterator). For very large files, chunksize helps manage memory. Yields list of dictionaries: {"page_content": "...", "metadata": {...}} """ print(f"Attempting to load customer ticket data: {filename} with extension {file_extension}") if file_extension in ['.xlsx', '.xls']: try: # Read the entire Excel file into a DataFrame first df_full = pd.read_excel(file_path, engine='openpyxl') if df_full.empty: raise ValueError("The uploaded Excel file contains no data.") total_rows_processed = 0 # Manually chunk the DataFrame for i in range(0, len(df_full), chunksize): df_chunk = df_full.iloc[i : i + chunksize] chunk_documents = [] for index, row in df_chunk.iterrows(): ticket_content_parts = [] ticket_id = f"Row {index + 1}" # Default ticket ID based on original DataFrame index for col_name, value in row.items(): if pd.notna(value) and str(value).strip() != '': formatted_col_name = col_name.replace('_', ' ').title() ticket_content_parts.append(f"{formatted_col_name}: {value}") if col_name.lower() in ['ticketid', 'ticket_id', 'id']: ticket_id = str(value).strip() if not ticket_content_parts: ticket_content = f"Ticket {ticket_id}: No detailed content provided." else: ticket_content = "\n".join(ticket_content_parts) metadata = {"source": filename, "ticket_row_number": index + 1, "ticket_id": ticket_id} metadata.update(row.to_dict()) # Include all original row data in metadata chunk_documents.append({"page_content": ticket_content, "metadata": metadata}) total_rows_processed += 1 # Update count based on actual processed rows in this chunk yield chunk_documents, total_rows_processed # Yield chunk and running total if total_rows_processed == 0: raise ValueError("The uploaded Excel file contains no data or no rows could be parsed after processing chunks.") except Exception as e: raise ValueError(f"Could not process Excel file '{filename}': {e}. Ensure it's a valid Excel format for ticket data.") elif file_extension == '.csv': try: df_reader = pd.read_csv(file_path, chunksize=chunksize) total_rows_processed = 0 for i, df_chunk in enumerate(df_reader): if df_chunk.empty: continue chunk_documents = [] for index, row in df_chunk.iterrows(): ticket_content_parts = [] ticket_id = f"Row {total_rows_processed + 1}" # Default ticket ID for col_name, value in row.items(): if pd.notna(value) and str(value).strip() != '': formatted_col_name = col_name.replace('_', ' ').title() ticket_content_parts.append(f"{formatted_col_name}: {value}") if col_name.lower() in ['ticketid', 'ticket_id', 'id']: ticket_id = str(value).strip() if not ticket_content_parts: ticket_content = f"Ticket {ticket_id}: No detailed content provided." else: ticket_content = "\n".join(ticket_content_parts) metadata = {"source": filename, "ticket_row_number": total_rows_processed + 1, "ticket_id": ticket_id} metadata.update(row.to_dict()) # Include all original row data in metadata chunk_documents.append({"page_content": ticket_content, "metadata": metadata}) total_rows_processed += 1 yield chunk_documents, total_rows_processed # Yield chunk and running total if total_rows_processed == 0: raise ValueError("The uploaded CSV file contains no data or no rows could be parsed.") except Exception as e: raise ValueError(f"Could not process CSV file '{filename}': {e}. Ensure it's a valid CSV format for ticket data.") else: raise ValueError(f"Unsupported file type: {file_extension}. Only .xlsx, .xls, or .csv files are accepted for ticket data.") def generate_summary_html_by_category(df): """ Generates HTML summary blocks for each unique category in the DataFrame, following the executive summary format. Splits multi-line text into bullet points. Categories are sorted from max to min count. """ if df.empty or 'Category' not in df.columns: return "
No categories found or data is empty for detailed summarization. This could be due to issues with the AI's output parsing or content generation.
" summary_html = "" # Ensure all required columns exist, fill with N/A if missing required_cols = ['Issue Description (Summary)', 'Sub-Category', 'Potential Root Cause (Inferred)', # Changed to Potential Root Cause 'Potential Resolution Steps', 'Future Prediction', 'Proactive Measures', 'Count'] for col in required_cols: if col not in df.columns: df[col] = 'N/A' if col == 'Count': df[col] = 0 # Group by category and sum the counts, then sort by count in descending order category_counts = df.groupby('Category')['Count'].sum().sort_values(ascending=False) for category in category_counts.index: # Iterate through categories in sorted order (max to min count) category_df = df[df['Category'] == category] # Get the DataFrame for the current category total_count = category_df['Count'].sum() # --- Issue Description (Summary) --- issue_descriptions_list = [] for summary_text in category_df['Issue Description (Summary)'].astype(str).drop_duplicates().tolist(): lines = re.split(r'\s*[\*\-]\s*|\s*\n\s*', summary_text) for line in lines: line = line.strip() if line: issue_descriptions_list.append(f"No specific summary available.
" # --- Sub-Category Summary --- sub_category_summary = "No sub-categories identified.
" # --- Potential Root Cause (Inferred) --- Changed to Potential Root Cause root_causes_list = [] for rc_text in category_df['Potential Root Cause (Inferred)'].astype(str).drop_duplicates().tolist(): # Changed to Potential Root Cause lines = re.split(r'\s*[\*\-]\s*|\s*\n\s*', rc_text) for line in lines: line = line.strip() if line and line.lower() != 'undetermined from available data' and line.lower() != 'undetermined.': root_causes_list.append(f"Undetermined.
" # --- Potential Resolution Steps --- resolution_steps_list = [] for res_text in category_df['Potential Resolution Steps'].astype(str).drop_duplicates().tolist(): lines = re.split(r'\s*[\*\-]\s*|\s*\n\s*', res_text) for line in lines: line = line.strip() if line and line.lower() != 'n/a': resolution_steps_list.append(f"No specific steps identified.
" # --- Future Prediction & Proactive Measures (Revised) --- future_prediction_html_list = [] for pred_text in category_df['Future Prediction'].astype(str).drop_duplicates().tolist(): lines = re.split(r'\s*[\*\-]\s*|\s*\n\s*', pred_text) for line in lines: line = line.strip() if line and line.lower() != 'n/a': future_prediction_html_list.append(f"Future Prediction:
" prediction_and_proactive_html += f"Proactive Measures:
" prediction_and_proactive_html += f"No future prediction or proactive measures identified.
" summary_html += f"""Summary:
{issue_descriptions}Sub-Categories:
{sub_category_summary}Potential Root Cause:
{root_causes}Resolution Steps:
{resolution_steps}Insights & Recommendations:
{prediction_and_proactive_html}{error_message_markdown}
", visible=True), gr.update(value="\n".join(log_messages)) return if gemini_llm_model is None: error_message_markdown = "Google Generative AI LLM was not initialized. Please check startup logs for API key or model access issues." yield gr.update(value=f"{error_message_markdown}
", visible=True), gr.update(value="\n".join(log_messages)) return if file is None: error_message_markdown = "No file has been uploaded. Please upload a customer ticket file to proceed." yield gr.update(value=f"{error_message_markdown}
", visible=True), gr.update(value="\n".join(log_messages)) return file_path = file.name filename = os.path.basename(file_path) file_extension = os.path.splitext(filename)[1].lower() base_filename = os.path.splitext(filename)[0] # Update versioning if _last_uploaded_filename_base != base_filename: _last_uploaded_filename_base = base_filename _file_versions[_last_uploaded_filename_base] = 1 else: _file_versions[_last_uploaded_filename_base] = _file_versions.get(_last_uploaded_filename_base, 0) + 1 disclaimer_text_for_raw = "\n\n**Note:** 'Potential Root Cause', 'Future Prediction', and 'Proactive Measures' are AI-inferred based on descriptions provided. For critical decisions, human expert review is essential." try: # --- PHASE 1: Initial Categorization of ALL Tickets --- yield append_log_message("Phase 1/2: Initializing categorization...") # Corrected progress(0.1, desc="Phase 1/2: Initializing categorization...") time.sleep(0.01) # Small sleep for progress bar to render # Using a generator for `get_ticket_documents_iterator` for memory efficiency ticket_iterator = get_ticket_documents_iterator(file_path, file_extension, filename, chunksize=5000) all_categorized_tickets_data = [] # To accumulate all categorized ticket dictionaries # total_tickets_count = 0 # This will be updated by the iterator's last yield # Get the full DataFrame once for total rows, then iterate chunks df_tickets_full = None if file_extension in ['.xlsx', '.xls']: df_tickets_full = pd.read_excel(file_path, engine='openpyxl') elif file_extension == '.csv': df_tickets_full = pd.read_csv(file_path) if df_tickets_full is None or df_tickets_full.empty: raise ValueError("No data found in the uploaded file after initial read.") _original_total_tickets_count = len(df_tickets_full) # Store global total yield append_log_message(f"Detected {_original_total_tickets_count} total tickets in the file.") # Corrected chunk_size = 5000 # Must match the chunksize in get_ticket_documents_iterator processed_chunks_count = 0 total_tickets_processed_in_phase1 = 0 # Iterate manually through the DataFrame in chunks for consistent processing and progress for i in range(0, _original_total_tickets_count, chunk_size): df_chunk = df_tickets_full.iloc[i : i + chunk_size] chunk_documents = [] current_chunk_tickets_count = 0 for index, row in df_chunk.iterrows(): ticket_content_parts = [] ticket_id = f"Row {index + 1}" # Default ticket ID based on original DataFrame index for col_name, value in row.items(): if pd.notna(value) and str(value).strip() != '': formatted_col_name = col_name.replace('_', ' ').title() ticket_content_parts.append(f"{formatted_col_name}: {value}") if col_name.lower() in ['ticketid', 'ticket_id', 'id']: ticket_id = str(value).strip() if not ticket_content_parts: ticket_content = f"Ticket {ticket_id}: No detailed content provided." else: ticket_content = "\n".join(ticket_content_parts) metadata = {"source": filename, "ticket_row_number": index + 1, "ticket_id": ticket_id} metadata.update(row.to_dict()) # Include all original row data in metadata chunk_documents.append({"page_content": ticket_content, "metadata": metadata}) current_chunk_tickets_count += 1 processed_chunks_count += 1 total_tickets_processed_in_phase1 += current_chunk_tickets_count # Phase 1 progress (0.1 to 0.5) progress_val = 0.1 + (0.4 * (total_tickets_processed_in_phase1 / _original_total_tickets_count)) progress(progress_val, desc=f"Phase 1/2: Categorizing tickets... {total_tickets_processed_in_phase1}/{_original_total_tickets_count} processed.") time.sleep(0.01) # Small sleep for progress bar to render if not chunk_documents: continue categorization_context_parts = [] ticket_id_map = {} for doc in chunk_documents: ticket_id_for_prompt = str(doc["metadata"].get("ticket_id", f"Row {doc['metadata']['ticket_row_number']}")) categorization_context_parts.append(f"--- Ticket ID: {ticket_id_for_prompt} ---\n{doc['page_content']}\n") ticket_id_map[ticket_id_for_prompt] = doc["page_content"] categorization_context = "\n".join(categorization_context_parts) categorization_prompt = f""" You are an expert customer support ticket analyst. Your task is to categorize ALL provided customer tickets in this batch. For each ticket, identify a concise main category (e.g., 'Technical Issue', 'Billing Inquiry', 'Feature Request', 'Account Management', 'Order Status', 'Delivery Issue', 'Product Defect', 'General Inquiry', 'Performance Problem', 'Data Access'). You MUST aim for at least 5-6 distinct main categories, even if some categories only have a few tickets. Do not merge categories unless absolutely necessary. Your output MUST be a simple list, one ticket per line, in the format: Ticket ID: [Original Ticket ID from metadata] - Category: [Determined Category] - Issue Summary: [Very brief 1-2 sentence summary of the core issue] Example: Ticket ID: TKT-001 - Category: Technical Issue - Issue Summary: User cannot log in due to password error. Ticket ID: INV-567 - Category: Billing Inquiry - Issue Summary: Customer dispute charge for duplicate service. Ticket ID: FEAT-123 - Category: Feature Request - Issue Summary: Customer wants dark mode for mobile app. Ticket ID: ACC-987 - Category: Account Management - Issue Summary: User unable to update profile information. Ticket ID: DEL-456 - Category: Delivery Issue - Issue Summary: Package delivery delayed, tracking not updated. Context (Customer Tickets - analyze ALL of these, total {len(chunk_documents)} tickets in this batch): {categorization_context} """ try: yield append_log_message(f"Calling LLM for categorization of {len(chunk_documents)} tickets...") # Corrected categorization_response = gemini_llm_model.generate_content(categorization_prompt) categorization_raw_output = categorization_response.text yield append_log_message("LLM categorization response received. Parsing...") # Corrected for line in categorization_raw_output.split('\n'): line = line.strip() match = re.match(r'Ticket ID: (.*?) - Category: (.*?) - Issue Summary: (.*)', line) if match: ticket_id = match.group(1).strip() category = match.group(2).strip() issue_summary = match.group(3).strip() original_content = ticket_id_map.get(ticket_id, "Original content not found for this ID.") all_categorized_tickets_data.append({ "ticket_id": ticket_id, "category": category, "issue_summary_short": issue_summary, "original_content": original_content }) elif line: # Fallback for malformed lines: try to extract best effort print(f"Warning: Line did not fully match categorization format in chunk {processed_chunks_count}: {line}", file=sys.stderr) yield append_log_message(f"Warning: Malformed categorization line in chunk {processed_chunks_count}, attempting partial parse: {line}") # Corrected parts = line.split("Category:") ticket_id_part = parts[0].replace("Ticket ID:", "").strip() if len(parts) > 0 else f"Unparsed_Line_{len(all_categorized_tickets_data)+1}" category_part = parts[1].split("Issue Summary:")[0].strip() if len(parts) > 1 and "Issue Summary:" in parts[1] else "Uncategorized/Parsing Error" issue_summary_part = parts[1].split("Issue Summary:", 1)[1].strip() if len(parts) > 1 and "Issue Summary:" in parts[1] else line original_content = ticket_id_map.get(ticket_id_part, "Original content not found / Raw line.") all_categorized_tickets_data.append({ "ticket_id": ticket_id_part, "category": category_part, "issue_summary_short": issue_summary_part, "original_content": original_content }) except Exception as e: yield append_log_message(f"Error during categorization of chunk {processed_chunks_count}: {e}") # Corrected print(f"Error during categorization of chunk {processed_chunks_count}: {e}", file=sys.stderr) # If an error occurs during API call for a chunk, categorize its tickets as error for doc in chunk_documents: ticket_id = str(doc["metadata"].get("ticket_id", f"Row {doc['metadata']['ticket_row_number']}")) all_categorized_tickets_data.append({ "ticket_id": ticket_id, "category": "Error_Processing_Chunk", "issue_summary_short": f"Failed to categorize due to error: {e}", "original_content": doc["page_content"] }) final_categorized_df = pd.DataFrame(all_categorized_tickets_data) if final_categorized_df.empty: error_message_markdown = "AI failed to categorize any tickets. Please ensure the ticket data is clear or increase AI model's tolerance." yield gr.update(value=f"{error_message_markdown}
", visible=True), gr.update(value="\n".join(log_messages)) return # --- PHASE 2: Detailed Analysis per Category --- yield append_log_message("Phase 2/2: Detailing analysis for categories...") # Corrected progress(0.5, desc=f"Phase 2/2: Detailing analysis for categories...") time.sleep(0.01) # Small sleep for progress bar to render final_analysis_rows = [] unique_categories = final_categorized_df['category'].unique() yield append_log_message(f"Identified {len(unique_categories)} unique categories for detailed analysis.") # Corrected category_batch_size = 5 # Process categories in small batches if many unique categories exist # Calculate total number of category batches for progress bar total_category_batches = max(1, (len(unique_categories) + category_batch_size - 1) // category_batch_size) # Ceiling division for i, category_chunk in enumerate(np.array_split(unique_categories, total_category_batches)): # Progress from 0.5 to 0.95 progress_val = 0.5 + (0.45 * (i+1) / total_category_batches) progress(progress_val, desc=f"Phase 2/2: Detailing analysis for category batch {i+1} of {total_category_batches}...") time.sleep(0.01) # Small sleep for progress bar to render for category in category_chunk: category_specific_tickets_df = final_categorized_df[final_categorized_df['category'] == category] category_tickets_content = "\n\n".join(category_specific_tickets_df['original_content'].tolist()) category_count = len(category_specific_tickets_df) yield append_log_message(f"Analyzing category: '{category}' with {category_count} tickets.") # Corrected # --- START: OPTIMIZED PROMPT FOR DETAILED ANALYSIS --- detail_prompt = f""" You are an expert customer support ticket analyst, specializing in NLP and data interpretation. Deeply analyze the following raw customer support tickets, which all belong to the **'{category}'** category. Your goal is to synthesize the information into a single, comprehensive markdown table row, providing highly contextualized and actionable insights for this specific category. **Crucial Instructions:** 1. **Strict Output Format:** Your output MUST be a **single Markdown table row**. Do NOT include the header row, only the data row. **Table Columns (MUST be in this exact order):** `Category`, `Sub-Category`, `Count`, `Issue Description (Summary)`, `Potential Root Cause (Inferred)`, `Potential Resolution Steps`, `Future Prediction`, `Proactive Measures`. 2. **Category:** This MUST be '{category}'. 3. **Count:** This MUST be the **exact number of tickets provided for this category ({category_count})**. 4. **Issue Description (Summary):** * Synthesize the **common themes and recurring patterns** observed across ALL tickets in this category. * Identify and mention **key affected systems, user actions, or specific features** causing issues. * Present each distinct point as a new bullet point (e.g., "* Point 1\\n* Point 2"). * Keep it concise (3-5 bullet points), focusing on the most prevalent aspects. 5. **Sub-Category:** * Based on internal patterns, identify one or two **most prominent, granular sub-categories**. * If no clear sub-category emerges, state 'General {category}' or 'N/A'. * Examples: For 'Technical Issue' -> 'Login Failure', 'Software Bug'; for 'Billing Inquiry' -> 'Incorrect Charge', 'Subscription Error'. 6. **Potential Root Cause (Inferred):** * Perform an **in-depth inference** of the **underlying systemic or causal factors** leading to these issues. Do not just restate the symptom. * Present each distinct root cause as a new bullet point (e.g., "* Root cause 1\\n* Root cause 2"). * Think about why the problem exists. (e.g., "* System misconfiguration", "* User error due to lack of documentation", "* Third-party integration failure", "* Defect in recent software update"). * If truly undetermined from the data, state 'Undetermined from available data'. 7. **Potential Resolution Steps:** * Suggest **actionable, generic steps** that would effectively resolve or mitigate the issues in this category. * Present each distinct step as a new bullet point (e.g., "* Step 1\\n* Step 2"). * These should be **strategic resolutions**, not just individual ticket fixes. (e.g., "* Develop new API endpoint", "* Update user guides", "* Patch database system", "* Conduct user training"). 8. **Future Prediction:** * Based on the trends and nature of these tickets, predict **what might happen if the underlying issues are not addressed**. * Present each distinct prediction as a new bullet point (e.g., "* Prediction 1\\n* Prediction 2"). * Consider potential increase in ticket volume, customer churn, or emergence of new, related problems. (e.g., "* Increased call volume for billing disputes", "* Decline in user retention for feature X", "* Escalation to higher severity issues"). 9. **Proactive Measures:** * Suggest **specific, actionable steps to prevent recurrence or significantly reduce the volume** of these types of tickets in the future. * Present each distinct measure as a new bullet point (e.g., "* Measure 1\\n* Measure 2"). * Focus on **system improvements, process changes, documentation, or user education**. (e.g., "* Implement automated system health checks", "* Revise onboarding flow documentation", "* Roll out a mandatory security update", "* Train support staff on new refund process"). Context (Raw Customer Tickets for Category '{category}' - Total {category_count} tickets): {category_tickets_content} """ # --- END: OPTIMIZED PROMPT FOR DETAILED ANALYSIS --- try: yield append_log_message(f"Calling LLM for detailed analysis of category '{category}'...") # Corrected detail_response = gemini_llm_model.generate_content(detail_prompt) detail_raw_output = detail_response.text yield append_log_message(f"LLM detailed response for '{category}' received. Parsing...") # Corrected detailed_df, parsing_err = parse_markdown_table_to_df(detail_raw_output) if parsing_err or detailed_df.empty: yield append_log_message(f"Warning: Parsing error or empty DataFrame for category '{category}': {parsing_err or 'Empty DataFrame'}") # Corrected print(f"Warning: Parsing error or empty DataFrame for category '{category}': {parsing_err or 'Empty DataFrame'}", file=sys.stderr) print(f"Raw AI output for detail: {detail_raw_output}", file=sys.stderr) # Fallback row if parsing fails default_row = { "Category": category, "Sub-Category": "N/A", "Count": category_count, "Issue Description (Summary)": category_specific_tickets_df['issue_summary_short'].iloc[0] if not category_specific_tickets_df.empty else "Error in summary generation/parsing for this category.", "Potential Root Cause (Inferred)": "Undetermined due to parsing error or insufficient data.", # Changed "Potential Resolution Steps": "N/A", "Future Prediction": "N/A", "Proactive Measures": "N/A" } # Add a check to ensure all expected columns are present, add if missing for col in ['Sub-Category', 'Potential Root Cause (Inferred)', 'Potential Resolution Steps', 'Future Prediction', 'Proactive Measures']: # Changed if col not in default_row: default_row[col] = 'N/A' final_analysis_rows.append(default_row) else: detailed_row = detailed_df.iloc[0].to_dict() detailed_row['Category'] = category # Ensure category is correctly assigned # Correct the 'Count' if AI provides a different number if 'Count' in detailed_row: try: ai_reported_count = pd.to_numeric(detailed_row['Count'], errors='coerce') if pd.isna(ai_reported_count) or int(ai_reported_count) != category_count: yield append_log_message(f"Warning: AI-reported count {ai_reported_count} for '{category}' does not match actual count {category_count}. Correcting.") # Corrected print(f"Warning: AI-reported count {ai_reported_count} for '{category}' does not match actual count {category_count}. Correcting.", file=sys.stderr) detailed_row['Count'] = category_count except: # Catch any other conversion errors detailed_row['Count'] = category_count else: # Add 'Count' if missing from AI output detailed_row['Count'] = category_count # Ensure all required columns are present in the parsed row for col in ['Sub-Category', 'Potential Root Cause (Inferred)', 'Potential Resolution Steps', 'Future Prediction', 'Proactive Measures']: # Changed if col not in detailed_row: detailed_row[col] = 'N/A' # Default to N/A if LLM missed a column final_analysis_rows.append(detailed_row) except Exception as e: yield append_log_message(f"Error detailing analysis for category '{category}': {e}") # Corrected print(f"Error detailing analysis for category '{category}': {e}", file=sys.stderr) # Fallback row if API call fails for a category default_row = { "Category": category, "Sub-Category": "N/A", "Count": category_count, "Issue Description (Summary)": f"Error during detailed analysis for this category: {e}", "Potential Root Cause (Inferred)": "Undetermined due to error.", # Changed "Potential Resolution Steps": "N/A", "Future Prediction": "N/A", "Proactive Measures": "N/A" } final_analysis_rows.append(default_row) analysis_df = pd.DataFrame(final_analysis_rows) if 'Count' in analysis_df.columns: # Ensure 'Count' is integer type for accurate summation analysis_df['Count'] = pd.to_numeric(analysis_df['Count'], errors='coerce').fillna(0).astype(int) summarized_count = analysis_df['Count'].sum() if summarized_count != _original_total_tickets_count: # Use total_rows_in_file from the full dataframe read yield append_log_message(f"CRITICAL WARNING: Total count in summarized DF ({summarized_count}) does not match original tickets ({_original_total_tickets_count}). This might indicate a parsing or AI categorization issue.") # Corrected print(f"CRITICAL WARNING: Total count in summarized DF ({summarized_count}) does not match original tickets ({_original_total_tickets_count}). This might indicate a parsing or AI categorization issue.", file=sys.stderr) global_analysis_df_for_export = analysis_df.copy() global_raw_markdown_for_export = analysis_df.to_markdown(index=False) + disclaimer_text_for_raw except ValueError as e: error_message_markdown = f"Ticket Analysis Error: {e}" yield gr.update(value=f"{error_message_markdown}
", visible=True), gr.update(value="\n".join(log_messages)) global_raw_markdown_for_export = error_message_markdown + disclaimer_text_for_raw global_analysis_df_for_export = pd.DataFrame() # Clear dataframe on error return except Exception as e: error_message_markdown = f"An unexpected error occurred during analysis: {e}. Check server logs for details." yield gr.update(value=f"{error_message_markdown}
", visible=True), gr.update(value="\n".join(log_messages)) print(f"Error during RAG analysis: {e}", file=sys.stderr) # Typo fixed here: disclaimer_text_for_export changed to disclaimer_text_for_raw global_raw_markdown_for_export = error_message_markdown + disclaimer_text_for_raw global_analysis_df_for_export = pd.DataFrame() # Clear dataframe on error return yield append_log_message("Generating summary report for display...") # Corrected progress(0.98, desc="Generating summary report for display...") time.sleep(0.01) # Small sleep for progress bar to render summarized_analysis_html = "" try: if not analysis_df.empty: summarized_analysis_html = generate_summary_html_by_category(analysis_df) yield append_log_message("HTML summary generated successfully.") # Corrected else: summarized_analysis_html = "AI output parsed into an empty DataFrame or no categories were generated. Please check the uploaded file and try again.
" yield append_log_message("Warning: No categories generated, HTML summary might be empty.") # Corrected except Exception as e: yield append_log_message(f"Failed to generate HTML summary from DataFrame: {e}") # Corrected print(f"Failed to generate HTML summary from DataFrame: {e}", file=sys.stderr) summarized_analysis_html = f"""{global_raw_markdown_for_export}
This report presents a comprehensive analysis of recent customer support tickets, leveraging advanced AI to identify key trends, underlying root causes, and actionable recommendations.
Total Tickets Processed: {_original_total_tickets_count}
Total Categories Identified: {len(unique_categories) if 'unique_categories' in locals() else 0}
{'
WARNING: Summarized ticket count ({summarized_count}) does not match original total ({_original_total_tickets_count}). Data parsing or AI categorization may have occurred.
' if summarized_count != _original_total_tickets_count else ''}AI-generated insights, including inferred root causes, future predictions, and proactive measures, are based on the provided ticket data. These analyses are intended for guidance and require human expert validation for critical business decisions.
Derive actionable insights from your customer support data.
""") gr.Markdown( """ ### System Overview This tool provides structured insights from customer support tickets. Upload your data to receive a comprehensive analysis including categorization, root causes, strategic recommendations, and future outlooks. """ ) gr.Markdown( """ #### Important Note on AI Inferences: AI-generated insights, including inferred root causes, future predictions, and proactive measures, are based on the provided ticket data. These analyses are intended for guidance and require human expert validation for critical business decisions. """ ) gr.Markdown( """ #### Input File Requirements: * **Supported Formats:** `.xlsx` (Excel), `.xls` (Legacy Excel), and `.csv` (Comma Separated Values). * **Data Structure:** Files must contain tabular data with clear headers in the first row. Each row represents an individual customer ticket. * **Minimum Columns:** A minimum of two columns are recommended: one for a unique `Ticket ID` (or `ID`) and one for the `Issue` (or `Description`). * **Example:** ``` TicketID,IssueDescription TKT001,User cannot log in; password reset not working. TKT002,Billing error: duplicate charge for last month's service. ``` * **Recommended Columns:** For richer analysis, include columns suchs as `Status`, `Priority`, `CustomerName`, and `Timestamp`. More relevant details enhance AI inferences. * **Data Volume:** For large files (e.g., over 60,000 tickets), processing occurs in batches to optimize memory and API usage. Processing times may vary. * **Data Privacy:** Please do not upload highly sensitive or confidential customer information. This is a public demonstration environment. """ ) gr.Markdown( """ #### Output Structure: The AI will generate a clear, data-driven report based solely on your uploaded file. The visual output will feature: * **Category Name (Total Count for Category)** * **Summary:** Key issue descriptions and common themes. * **Sub-Categories:** More granular sub-categories and their counts. * **Potential Root Cause:** AI-inferred underlying reasons for issues. * **Resolution Steps:** Strategic actions to resolve issues. * **Insights & Recommendations:** * **Future Prediction:** Predictions if underlying issues persist. * **Proactive Measures:** Preventive steps to reduce recurrence. """ ) with gr.Row(): file_input = gr.File( label="Upload Customer Ticket Data", file_types=[".xlsx", ".xls", ".csv"], type="filepath", file_count="single", interactive=True ) query_input = gr.Textbox( label="Optional Analysis Request (Leave blank for full report)", placeholder="e.g., 'Summarize all high-priority issues' or leave empty for comprehensive analysis.", lines=2, interactive=True ) timezone_dropdown = gr.Dropdown( choices=COMMON_TIMEZONES, label="Select Local Timezone (for report timestamps)", value="America/Chicago", # Set a reasonable default allow_custom_value=False, interactive=True ) submit_btn = gr.Button("Generate Analysis Report", variant="primary") progress_log = gr.Textbox( label="Processing Log", interactive=False, lines=5, autoscroll=True, elem_id="progress-log-box", visible=False # Hidden on load ) output_html_display = gr.HTML( label="AI Ticket Analysis Report" ) with gr.Group(elem_id="download-buttons"): pdf_output_file = gr.File(label="Download PDF", visible=False) docx_output_file = gr.File(label="Download Word Document", visible=False) pdf_btn = gr.Button("Download as PDF Report", elem_classes="download-button") docx_btn = gr.Button("Download as Word Document", elem_classes="download-button") gr.HTML("""*All AI-generated insights are for informational purposes and should be verified by human experts.*
""") submit_btn.click( fn=perform_ticket_analysis, inputs=[file_input, query_input, timezone_dropdown], outputs=[output_html_display, progress_log], queue=True # Enable queueing for potentially long running tasks ) pdf_btn.click( fn=generate_pdf_report, inputs=[timezone_dropdown], outputs=pdf_output_file, api_name="download_pdf" ) docx_btn.click( fn=generate_docx_report, inputs=[timezone_dropdown], outputs=docx_output_file, api_name="download_docx" ) if __name__ == "__main__": if not os.getenv("GOOGLE_API_KEY"): print("\n" + "="*80) print("REMINDER: GOOGLE_API_KEY environment variable is NOT SET for local execution.") print("RAG functionality will not work locally without it. Please set it in a .env file or your environment.") print("Example: GOOGLE_API_KEY='your-gemini-api-key-here'") print("="*80 + "\n") demo.launch()