main.py

import openai
import json
import os
import pandas as pd
import re
import time
import threading
import queue
import hashlib
from concurrent.futures import ThreadPoolExecutor
from typing import List, Dict, Any
from qdrant_client import QdrantClient
from qdrant_client.http import models
from qdrant_client.http.models import PointStruct

from openai import OpenAI
key = "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"
# Set up OpenAI API key
client = OpenAI(
  api_key=key,
)



# Constants for Qdrant and embeddings
EMBEDDING_MODEL = "text-embedding-3-small"
EMBEDDING_DIMENSION = 1536  # Dimension for text-embedding-3-small
COLLECTION_NAME = "customer_support_qa"
MAX_THREADS = 30  # Maximum number of concurrent API calls
API_CALL_TIMEOUT = 30  # Timeout for API calls in seconds

# Thread-safe queue for results
results_queue = queue.Queue()

# Thread-local storage for OpenAI clients
thread_local = threading.local()

def get_client():
    """Get thread-local OpenAI client"""
    if not hasattr(thread_local, "client"):
        thread_local.client = OpenAI(api_key=key)
    return thread_local.client

def generate_chat_id(chat_history: str) -> str:
    """Generate a unique ID for a chat history using hash to eliminate duplicates"""
    # Create a hash of the chat history content
    hash_obj = hashlib.md5(chat_history.encode('utf-8'))
    return hash_obj.hexdigest()

def extract_standalone_qa(chat_history: str) -> str:
    """Extract Q&A pairs from chat history using GPT-4o-mini"""
    # System prompt with clear instructions and examples
    system_prompt = """
You are an AI assistant tasked with analyzing chat histories between customers and customer care agents to identify standalone questions that an AI can answer completely based on the chat, without requiring human interaction, similar to FAQs. A standalone question is one where:
- The customer's query (explicit or implicit) is fully addressed by the agent's response.
- The response provides information or instructions that the customer can use independently.
- No further interaction within the chat is required, such as providing additional information (e.g., verification codes) or the agent performing actions needing confirmation (e.g., manual resets).

**Example 1:**
Chat history:
2024-10-30 16:00:00 - Customer: How do I update my shipping address?
2024-10-30 16:01:00 - Human Agent: To update your shipping address, go to your account settings, select 'Addresses,' and edit your shipping address there.
Standalone Q&A:
[
    {"question": "How do I update my shipping address?", "answer": "To update your shipping address, go to your account settings, select 'Addresses,' and edit your shipping address there."}
]

**Example 2:**
Chat history:
2024-10-30 15:26:44 - Customer: This app is garbage! I've had an account for years but can't log in and I can't reset my password.
2024-10-30 15:27:40 - Human Agent: Hello, you've reached customer care. This is Marcela speaking. Could I have your Zmodo account email?
2024-10-30 15:29:28 - Customer: choens13@gmail.com
2024-10-30 15:31:44 - Human Agent: Okay do you have access to this email currently, I am going to try to reset it from my end, but I will need the 6 digit code from the verification email.
2024-10-30 15:31:58 - Customer: OK
Standalone Q&A:
[]
(Note: The agent's response requires a verification code, indicating human interaction, so no standalone Q&A exists.)

**Example 3:**
Chat history:
2024-10-30 17:00:00 - Customer: What are your business hours?
2024-10-30 17:01:00 - Human Agent: Our business hours are 9 AM to 5 PM, Monday to Friday.
Standalone Q&A:
[
    {"question": "What are your business hours?", "answer": "Our business hours are 9 AM to 5 PM, Monday to Friday."}
]

Analyze the following chat history and identify standalone Q&A pairs. Output the result in JSON format as a list of dictionaries, each with "question" and "answer" keys. If no standalone pairs exist, return an empty list. Your generated answer must be detailed enough.

IMPORTANT: Your response must be valid JSON only, with no additional text outside the JSON array.
"""

    # Trim chat history if it's too long
    if len(chat_history) > 10000:
        # #print(f"Chat history is very long ({len(chat_history)} chars), trimming...")
        chat_history = chat_history[:10000] + "...[truncated]"

    # User message with the chat history
    user_message = f"Analyze this chat history:\n\n{chat_history}"

    # Call OpenAI API
    try:
        local_client = get_client()
        response = local_client.chat.completions.create(
            model="gpt-4o-mini",
            messages=[
                {"role": "system", "content": system_prompt},
                {"role": "user", "content": user_message}
            ],
            temperature=0.0,  # Low temperature for consistent output
            timeout=API_CALL_TIMEOUT,
            response_format={"type": "json_object"}  # Request JSON format
        )

        # Extract the response
        content = response.choices[0].message.content
        
        return content

    except Exception as e:
        #print(f"Error in extract_standalone_qa: {e}")
        return "[]"

def create_embedding(text: str) -> List[float]:
    """Create an embedding for the given text using OpenAI's embedding model."""
    try:
        local_client = get_client()
        response = local_client.embeddings.create(
            input=text,
            model=EMBEDDING_MODEL,
            timeout=API_CALL_TIMEOUT
        )
        embedding = response.data[0].embedding
        return embedding
    except Exception as e:
        #print(f"Error creating embedding: {e}")
        # Return a zero vector of the correct dimension as fallback
        return [0.0] * EMBEDDING_DIMENSION

def extract_questions_from_qa_result(qa_result: str) -> List[Dict[str, str]]:
    """Extract question-answer pairs from the LLM response."""
    #print("qa_result", qa_result)
    
    # Clean up the response - sometimes GPT adds extra text before or after JSON
    try:
        # Try to find JSON content enclosed in square brackets
        json_pattern = r'\[.*\]'
        json_matches = re.search(json_pattern, qa_result, re.DOTALL)
        
        if json_matches:
            json_content = json_matches.group(0)
            #print("Extracted JSON content:", json_content)
            
            try:
                qa_data = json.loads(json_content)
                #print("Parsed qa_data:", qa_data)
                if isinstance(qa_data, list):
                    return qa_data
            except json.JSONDecodeError as e:
                pass
                #print(f"JSON parsing error after extraction: {e}")
        
        # If bracket extraction failed, try direct parsing
        try:
            qa_data = json.loads(qa_result)
            #print("Direct parsed qa_data:", qa_data)
            if isinstance(qa_data, list):
                return qa_data
        except json.JSONDecodeError as e:
            pass
            #print(f"Direct JSON parsing error: {e}")
    
        # If all JSON approaches fail, try regex extraction for individual pairs
        pattern = r'"question":\s*"(.*?)",\s*"answer":\s*"(.*?)"'
        matches = re.findall(pattern, qa_result, re.DOTALL)
        if matches:
            #print(f"Extracted {len(matches)} Q&A pairs via regex")
            return [{"question": q, "answer": a} for q, a in matches]
        
        # Try another pattern if the first one fails
        pattern2 = r'question:\s*(.+?)\nanswer:\s*(.+?)(?:\n\n|\Z)'
        matches2 = re.findall(pattern2, qa_result, re.DOTALL | re.IGNORECASE)
        if matches2:
            #print(f"Extracted {len(matches2)} Q&A pairs via second regex")
            return [{"question": q.strip(), "answer": a.strip()} for q, a in matches2]
    
    except Exception as e:
        pass
        #print(f"Error during extraction: {e}")
    
    #print("No questions could be extracted from the response")
    return []

def setup_qdrant_collection():
    """Set up a Qdrant collection for storing embeddings."""
    try:
        qdrant_client = QdrantClient(url="http://10.80.7.190:6333")
        
        
        # Check if collection exists and recreate if needed
        collections = qdrant_client.get_collections().collections
        collection_names = [c.name for c in collections]
        
        if COLLECTION_NAME in collection_names:
            #print(f"Collection '{COLLECTION_NAME}' already exists, recreating it...")
            qdrant_client.delete_collection(COLLECTION_NAME)
        
        # Create collection
        qdrant_client.recreate_collection(
            collection_name=COLLECTION_NAME,
            vectors_config=models.VectorParams(
                size=EMBEDDING_DIMENSION,
                distance=models.Distance.COSINE
            )
        )
        
        #print(f"Collection '{COLLECTION_NAME}' created successfully")
        
        # Test insert a dummy vector
        test_vector = [0.01] * EMBEDDING_DIMENSION
        try:
            qdrant_client.upsert(
                collection_name=COLLECTION_NAME,
                points=[
                    PointStruct(
                        id=0,
                        vector=test_vector,
                        payload={"test": "test"}
                    )
                ]
            )
            #print("Test vector inserted successfully")
            
            # Test search
            search_result = qdrant_client.search(
                collection_name=COLLECTION_NAME,
                query_vector=test_vector,
                limit=1
            )
            if search_result:
                #print("Test search successful")
                # Delete test vector
                qdrant_client.delete(
                    collection_name=COLLECTION_NAME,
                    points_selector=models.PointIdsList(
                        points=[0]
                    )
                )
        except Exception as e:
            #print(f"Error testing Qdrant operations: {e}")
            raise
            
        return qdrant_client
    except Exception as e:
        #print(f"Error setting up Qdrant: {e}")
        raise

def process_chat_for_qa(chat_id: int, chat_history: str):
    """Process a single chat history in a separate thread to extract Q&A pairs"""
    try:

        # Extract standalone Q&A pairs
        qa_result = extract_standalone_qa(chat_history)
        
        # Skip processing if the result is empty or contains error indicators
        if not qa_result or qa_result.strip() == "[]":
            # print(f"Chat #{chat_id}: No meaningful response from LLM")
            return []
            
        qa_pairs = extract_questions_from_qa_result(qa_result)
        
        if not qa_pairs:
            # print(f"Chat #{chat_id}: No question-answer pairs could be extracted")
            return []
            
        # print(f"Chat #{chat_id}: Found {len(qa_pairs)} question-answer pairs")
        
        # Process each question-answer pair
        local_results = []
        for qa_pair in qa_pairs:
            question = qa_pair.get("question", "")
            answer = qa_pair.get("answer", "")
            
            if not question or not answer:
                # print(f"Skipping incomplete QA pair: {qa_pair}")
                continue
                
            local_results.append({
                'question': question,
                'answer': answer
            })
            
        # print(f"Completed processing chat #{chat_id} with {len(local_results)} questions")
        return local_results
            
    except Exception as e:
        # print(f"Error processing chat #{chat_id}: {e}")
        import traceback
        traceback.print_exc()
        return []

def main():
    # Read the cleaned Excel file
    input_file = 'chat_history_cleaned.xlsx'
    df = pd.read_excel(input_file)
    
    # Since we dropped the first two columns, we should access the first column now
    column_name = df.columns[0]
    
    # Limit to first 100 rows (or all rows if less than 100)
    row_limit = len(df)
    print(f"Processing the first {row_limit} rows out of {len(df)} total rows")
    
    # Process chats using ThreadPoolExecutor
    all_qa_pairs = []
    with ThreadPoolExecutor(max_workers=MAX_THREADS) as executor:
        # Create a list to hold the futures
        futures = []
        
        # Submit tasks for the first row_limit rows
        for i in range(row_limit):
            chat_history = df.iloc[i][column_name]
            chat_id = i + 1
            
            future = executor.submit(
                process_chat_for_qa,
                chat_id,
                chat_history
            )
            futures.append(future)
        
        # Collect results as they complete
        for future in futures:
            result = future.result()
            if result:
                all_qa_pairs.extend(result)
    
    # Save all Q&A pairs to an Excel file
    if all_qa_pairs:
        qa_df = pd.DataFrame(all_qa_pairs)
        output_file = 'qa_pairs.xlsx'
        qa_df.to_excel(output_file, index=False)
        print(f"\nExtracted {len(all_qa_pairs)} Q&A pairs from {row_limit} chats. Results saved to '{output_file}'")
    else:
        print(f"No Q&A pairs were extracted from the {row_limit} chat histories.")

if __name__ == "__main__":
    main()