streamlit_app.py

import streamlit as st
import pandas as pd
import numpy as np
import openai
from io import StringIO
import json
import re
import os
from typing import Dict, List, Any, Tuple, Optional
import plotly.express as px
import plotly.graph_objects as go
from collections import Counter
import nltk
import string
from datetime import datetime
import warnings
import logging
import time
import hashlib
import gc  # FIXED: Added for memory management

# Chroma imports
try:
    import chromadb
    from chromadb.config import Settings
    CHROMA_AVAILABLE = True
except ImportError:
    CHROMA_AVAILABLE = False

# Environment configuration
ENABLE_PERSISTENCE = os.getenv('ENABLE_PERSISTENCE', 'true').lower() == 'true'
CHROMA_PERSIST_PATH = os.getenv('CHROMA_PERSIST_PATH', '/data/chroma_db')

def get_openai_api_key():
    """Get OpenAI API key from Streamlit secrets or environment variables"""
    try:
        if hasattr(st, 'secrets') and 'OPENAI_API_KEY' in st.secrets:
            return st.secrets['OPENAI_API_KEY']
        return os.getenv('OPENAI_API_KEY')
    except Exception as e:
        st.error(f"Error accessing API key: {str(e)}")
        return None

@st.cache_resource
def setup_nltk_data():
    """Download NLTK data with optimized error handling"""
    try:
        import ssl
        try:
            _create_unverified_https_context = ssl._create_unverified_context
        except AttributeError:
            pass
        else:
            ssl._create_default_https_context = _create_unverified_https_context
        
        nltk.download('punkt', quiet=True, force=False)
        nltk.download('wordnet', quiet=True, force=False)
        nltk.download('stopwords', quiet=True, force=False)
        nltk.download('averaged_perceptron_tagger', quiet=True, force=False)
        return True
    except Exception as e:
        st.warning(f"NLTK download issue (will use fallback): {str(e)}")
        return False

logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)

try:
    from nltk.tokenize import word_tokenize, sent_tokenize
    from nltk.corpus import stopwords
    from nltk.stem import PorterStemmer, WordNetLemmatizer
    NLTK_AVAILABLE = True
except ImportError as e:
    logger.warning(f"NLTK not available: {e}")
    NLTK_AVAILABLE = False

try:
    from usearch.index import Index
    USEARCH_AVAILABLE = True
except ImportError:
    USEARCH_AVAILABLE = False

try:
    from sentence_transformers import SentenceTransformer
    SENTENCE_TRANSFORMERS_AVAILABLE = True
except ImportError:
    SENTENCE_TRANSFORMERS_AVAILABLE = False

try:
    import tiktoken
    TIKTOKEN_AVAILABLE = True
except ImportError:
    TIKTOKEN_AVAILABLE = False

warnings.filterwarnings('ignore')

st.set_page_config(
    page_title="Creative Job RAG Analyzer",
    page_icon="🎨",
    layout="wide",
    initial_sidebar_state="expanded"
)

# FIXED: Memory monitoring helpers
def get_memory_usage():
    """Get current memory usage in MB"""
    try:
        import psutil
        process = psutil.Process()
        return process.memory_info().rss / 1024 / 1024
    except:
        return None

def log_memory_usage(stage: str):
    """Log memory usage at different stages"""
    mem = get_memory_usage()
    if mem:
        logger.info(f"Memory usage at {stage}: {mem:.2f} MB")
        if mem > 1000:
            logger.warning(f"High memory usage detected: {mem:.2f} MB")

# Software and AI Tools Mapping
CREATIVE_SOFTWARE_MAPPING = {
    "adobe_suite": {
        "photoshop": ["Adobe Photoshop", "Photoshop", "PSD", "photoshop", "PHOTOSHOP"],
        "lightroom": ["Adobe Lightroom", "Lightroom", "LR", "lightroom"],
        "illustrator": ["Adobe Illustrator", "Illustrator", "illustrator"],
        "indesign": ["Adobe InDesign", "InDesign", "indesign"],
        "premiere": ["Adobe Premiere Pro", "Premiere Pro", "Premiere", "premiere"],
        "after_effects": ["Adobe After Effects", "After Effects", "AE", "after effects"],
        "xd": ["Adobe XD", "XD", "xd"],
        "creative_suite": ["Creative Suite", "CS", "Adobe CS", "Creative Cloud", "CC"],
        "dimension": ["Adobe Dimension", "Dimension"],
        "animate": ["Adobe Animate", "Animate", "Flash"],
        "audition": ["Adobe Audition", "Audition"],
        "substance": ["Adobe Substance", "Substance Painter", "Substance Designer"]
    },
    "non_adobe": {
        "final_cut_pro": ["Final Cut Pro", "FCP", "FCPX", "Final Cut", "final cut pro"],
        "davinci_resolve": ["DaVinci Resolve", "Resolve", "davinci resolve"],
        "avid": ["Avid Media Composer", "Media Composer", "Avid", "avid"],
        "pro_tools": ["Pro Tools", "Protools", "pro tools"],
        "logic_pro": ["Logic Pro", "Logic", "logic pro"],
        "sketch": ["Sketch", "sketch"],
        "figma": ["Figma", "figma"],
        "canva": ["Canva", "canva"],
        "gimp": ["GIMP", "gimp"],
        "blender": ["Blender", "blender"],
        "maya": ["Autodesk Maya", "Maya", "maya"],
        "3ds_max": ["3ds Max", "3D Studio Max", "3ds max"],
        "cinema_4d": ["Cinema 4D", "C4D", "cinema 4d"],
        "zbrush": ["ZBrush", "zbrush"],
        "unity": ["Unity", "unity"],
        "unreal": ["Unreal Engine", "UE4", "UE5", "Unreal", "unreal engine"],
        "procreate": ["Procreate", "procreate"],
        "affinity": ["Affinity Designer", "Affinity Photo", "Affinity"],
        "capture_one": ["Capture One", "capture one"],
        "corel": ["CorelDRAW", "Corel Painter", "Corel"],
        "keynote": ["Keynote", "keynote"],
        "powerpoint": ["PowerPoint", "PPT", "powerpoint"],
        "invision": ["InVision", "invision"],
        "webflow": ["Webflow", "webflow"],
        "wordpress": ["WordPress", "wordpress"]
    },
    "ai_tools": {
        "chatgpt": ["ChatGPT", "GPT-4", "GPT-4o", "chatgpt", "Chat GPT", "gpt-4"],
        "claude": ["Claude", "Anthropic Claude", "Claude AI", "claude"],
        "gemini": ["Gemini", "Google Gemini", "Bard", "gemini"],
        "copilot": ["GitHub Copilot", "Microsoft Copilot", "Copilot", "copilot"],
        "midjourney": ["Midjourney", "midjourney", "MidJourney"],
        "dall_e": ["DALL-E", "DALLE", "Dall-E", "dall-e", "DALL-E 2", "DALL-E 3"],
        "stable_diffusion": ["Stable Diffusion", "stable diffusion", "StableDiffusion"],
        "adobe_firefly": ["Adobe Firefly", "Firefly", "firefly"],
        "leonardo_ai": ["Leonardo AI", "Leonardo.ai", "leonardo"],
        "runway": ["Runway ML", "Runway", "runway"],
        "synthesia": ["Synthesia", "synthesia"],
        "descript": ["Descript", "descript"],
        "canva_ai": ["Canva AI", "Magic Write", "Magic Design"],
        "jasper": ["Jasper AI", "Jasper", "jasper"],
        "copy_ai": ["Copy.ai", "copy.ai", "Copy AI"],
        "eleven_labs": ["ElevenLabs", "Eleven Labs", "elevenlabs"],
        "perplexity": ["Perplexity", "Perplexity AI", "perplexity"],
        "ai_general": ["AI tools", "AI software", "generative AI", "gen AI"]
    }
}

JOB_ROLE_PATTERNS = {
    "designer": {
        "patterns": [
            r'\b(graphic|visual|ui|ux|web|digital|brand|creative|product|motion)\s*(designer?s?)\b',
            r'\bdesigner?s?\b',
            r'\b(art\s*director|creative\s*director)\b',
            r'\bcreative\s*(professional|specialist|lead|manager)\b'
        ],
        "keywords": ["design", "designer", "graphic", "visual", "ui", "ux", "creative"]
    },
    "video_professional": {
        "patterns": [
            r'\bvideographer\b',
            r'\bvideo\s+editor\b',
            r'\bvideo\s+producer\b',
            r'\bmotion\s+(graphics|designer|artist)\b',
            r'\bfilm\s+(editor|producer|maker)\b',
            r'\bcinematographer\b'
        ],
        "keywords": ["videographer", "video editor", "motion graphics", "film"]
    },
    "photo_professional": {
        "patterns": [
            r'\bphotographer\b',
            r'\bphotography\b',
            r'\bphoto\s+editor\b',
            r'\bphoto\s+retoucher\b',
            r'\bretoucher?s?\b'
        ],
        "keywords": ["photographer", "photography", "photo editor", "retoucher"]
    }
}

def get_enhanced_technical_skills():
    """Return comprehensive technical skills list"""
    return [
        "color theory", "typography", "layout", "composition", "branding", "logo design",
        "ux", "ui", "ux/ui", "user experience", "user interface", "ux design", "ui design",
        "interaction design", "wireframing", "prototyping", "mockups",
        "web design", "website design", "mobile design", "responsive design",
        "html", "html5", "css", "css3", "javascript", "js", "react", "vue", "angular",
        "photo retouching", "image retouching", "photo editing", "image editing",
        "color correction", "color grading",
        "video editing", "video production", "motion graphics", "motion design",
        "animation", "2d animation", "3d animation", "visual effects", "vfx",
        "compositing", "sound design", "audio editing",
        "3d modeling", "rendering", "3d rendering", "texturing", "lighting", "rigging",
        "illustration", "digital illustration", "digital art", "character design",
        "print design", "packaging design", "editorial design",
        "social media design", "advertising design", "marketing design",
        "design systems", "style guides", "visual identity",
        "storyboarding", "concept development", "cinematography",
        "seo", "google analytics", "a/b testing",
        "data visualization", "infographic design",
        "presentation design", "accessibility"
    ]

def get_enhanced_soft_skills():
    """Return comprehensive soft skills list"""
    return [
        "collaboration", "collaborative", "team collaboration",
        "communication", "communication skills", "presentation", "active listening",
        "project management", "time management", "deadline management",
        "prioritization", "organization", "planning",
        "creativity", "creative thinking", "innovation", "innovative thinking",
        "problem solving", "critical thinking", "analytical thinking",
        "teamwork", "team work", "team player", "interpersonal skills",
        "adaptability", "flexibility", "agile",
        "client management", "stakeholder management", "customer service",
        "leadership", "mentoring", "coaching",
        "attention to detail", "detail-oriented", "self-motivated", "initiative",
        "work ethic", "reliability", "accountability", "professionalism",
        "empathy", "emotional intelligence", "conflict resolution",
        "continuous learning", "growth mindset", "resilience",
        "decision making", "judgment"
    ]

def get_enhanced_creative_tasks():
    """Return comprehensive creative tasks list"""
    return [
        "brand identity", "logo creation", "marketing materials",
        "social media graphics", "website design", "app design",
        "print design", "packaging design", "illustration",
        "photo shoot", "product photography", "portrait photography",
        "video production", "commercial videos", "explainer videos",
        "promotional content", "advertising campaigns", "content creation",
        "banner design", "poster design", "brochure design",
        "email design", "landing page design", "interface design",
        "icon design", "character design", "concept art",
        "motion design", "video transitions", "3d animation"
    ]

SKILL_NORMALIZATION_MAP = {
    'ux': 'UX/UI Design', 'ui': 'UX/UI Design', 'ux/ui': 'UX/UI Design',
    'color theory': 'Color Theory', 'html': 'HTML/CSS', 'css': 'HTML/CSS',
    'motion graphics': 'Motion Graphics', '3d modeling': '3D Modeling',
    'project management': 'Project Management', 'time management': 'Time Management',
    'communication': 'Communication Skills', 'problem solving': 'Problem Solving',
    'teamwork': 'Teamwork', 'leadership': 'Leadership'
}

def normalize_skill(skill: str) -> str:
    """Normalize skill to canonical form"""
    skill_lower = skill.lower().strip()
    return SKILL_NORMALIZATION_MAP.get(skill_lower, skill.title())

def detect_creative_software_and_skills(text: str) -> Dict[str, List[str]]:
    """Enhanced detection with skill normalization"""
    if not text or pd.isna(text):
        return {"adobe_apps": [], "non_adobe_apps": [], "ai_tools": [], 
                "technical_skills": [], "soft_skills": [], "creative_tasks": []}
    
    text_str = str(text).strip()
    if not text_str:
        return {"adobe_apps": [], "non_adobe_apps": [], "ai_tools": [], 
                "technical_skills": [], "soft_skills": [], "creative_tasks": []}
    
    results = {
        "adobe_apps": [], "non_adobe_apps": [], "ai_tools": [],
        "technical_skills": [], "soft_skills": [], "creative_tasks": []
    }
    
    text_lower = text_str.lower()
    
    # Detect Adobe apps
    for app_key, variations in CREATIVE_SOFTWARE_MAPPING["adobe_suite"].items():
        for variation in variations:
            if re.search(r'\b' + re.escape(variation.lower()) + r'\b', text_lower):
                canonical_name = variations[0]
                if canonical_name not in results["adobe_apps"]:
                    results["adobe_apps"].append(canonical_name)
                break
    
    # Detect non-Adobe apps
    for app_key, variations in CREATIVE_SOFTWARE_MAPPING["non_adobe"].items():
        for variation in variations:
            if re.search(r'\b' + re.escape(variation.lower()) + r'\b', text_lower):
                canonical_name = variations[0]
                if canonical_name not in results["non_adobe_apps"]:
                    results["non_adobe_apps"].append(canonical_name)
                break
    
    # Detect AI tools
    for ai_key, variations in CREATIVE_SOFTWARE_MAPPING["ai_tools"].items():
        for variation in variations:
            if re.search(r'\b' + re.escape(variation.lower()) + r'\b', text_lower):
                canonical_name = variations[0]
                if canonical_name not in results["ai_tools"]:
                    results["ai_tools"].append(canonical_name)
                break
    
    # Detect technical skills
    detected_tech_skills = set()
    for skill in get_enhanced_technical_skills():
        if re.search(r'\b' + re.escape(skill.lower()) + r'\b', text_lower):
            normalized = normalize_skill(skill)
            detected_tech_skills.add(normalized)
    results["technical_skills"] = list(detected_tech_skills)
    
    # Detect soft skills
    detected_soft_skills = set()
    for skill in get_enhanced_soft_skills():
        if re.search(r'\b' + re.escape(skill.lower()) + r'\b', text_lower):
            normalized = normalize_skill(skill)
            detected_soft_skills.add(normalized)
    results["soft_skills"] = list(detected_soft_skills)
    
    # Detect creative tasks
    for task in get_enhanced_creative_tasks():
        if re.search(r'\b' + re.escape(task.lower()) + r'\b', text_lower):
            if task not in results["creative_tasks"]:
                results["creative_tasks"].append(task)
    
    return results

def categorize_job_role(job_title: str, job_description: str = "") -> Dict[str, bool]:
    """Improved job role categorization"""
    title_str = str(job_title) if pd.notna(job_title) else ""
    desc_str = str(job_description) if pd.notna(job_description) else ""
    combined_text = f"{title_str} {desc_str}".lower().strip()

    if not combined_text:
        return {"is_designer": False, "is_video_professional": False, 
                "is_photo_professional": False, "is_creative_professional": False}

    categories = {"is_designer": False, "is_video_professional": False, 
                  "is_photo_professional": False, "is_creative_professional": False}

    for role_key in ['designer', 'video_professional', 'photo_professional']:
        patterns = JOB_ROLE_PATTERNS[role_key]['patterns']
        category_key = f"is_{role_key}"
        for pattern in patterns:
            if re.search(pattern, combined_text, re.IGNORECASE):
                categories[category_key] = True
                break

    categories["is_creative_professional"] = any([
        categories["is_designer"], categories["is_video_professional"], 
        categories["is_photo_professional"]
    ])

    return categories

class ChromaVectorStore:
    """Enhanced Chroma-based persistent vector store - FIXED for large datasets (5000+ rows)"""
    
    def __init__(self, collection_name: str = "creative_jobs"):
        self.collection_name = collection_name
        self.client = None
        self.collection = None
        self.embedder = None
        self.dimension = 384
        self._initialization_error = None
        self.documents_count = 0
        self.auto_detected = False
        self.existing_metadata = None
        
        self._init_chroma_client()
        self._init_embedder()
        self._init_collection()
        self._auto_detect_existing_index()
    
    def _init_chroma_client(self):
        """Initialize Chroma client with persistence support"""
        if not CHROMA_AVAILABLE:
            self._initialization_error = "ChromaDB not available"
            return
        
        try:
            if ENABLE_PERSISTENCE:
                os.makedirs(CHROMA_PERSIST_PATH, exist_ok=True)
                self.client = chromadb.PersistentClient(
                    path=CHROMA_PERSIST_PATH,
                    settings=Settings(anonymized_telemetry=False, allow_reset=True)
                )
                logger.info(f"Chroma persistent client initialized at: {CHROMA_PERSIST_PATH}")
            else:
                self.client = chromadb.Client(
                    settings=Settings(anonymized_telemetry=False, allow_reset=True)
                )
                logger.info("Chroma in-memory client initialized")
        except Exception as e:
            self._initialization_error = f"Chroma client error: {str(e)}"
            logger.error(f"Failed to initialize Chroma client: {e}")
    
    def _init_embedder(self):
        """Initialize sentence transformer for embeddings"""
        if not SENTENCE_TRANSFORMERS_AVAILABLE:
            self._initialization_error = "SentenceTransformers not available"
            return
        
        try:
            model_name = 'all-MiniLM-L6-v2'
            with st.spinner(f"Loading {model_name} model..."):
                self.embedder = SentenceTransformer(model_name)
                test_embedding = self.embedder.encode(["test"], show_progress_bar=False)
                self.dimension = len(test_embedding[0])
                logger.info(f"Embedder initialized with dimension: {self.dimension}")
        except Exception as e:
            self._initialization_error = f"Embedder error: {str(e)}"
            logger.error(f"Failed to initialize embedder: {e}")
    
    def _init_collection(self):
        """Initialize or get existing collection"""
        if not self.is_available():
            return
        
        try:
            try:
                self.collection = self.client.get_collection(
                    name=self.collection_name, embedding_function=None
                )
                self.documents_count = self.collection.count()
                logger.info(f"Loaded existing collection '{self.collection_name}' with {self.documents_count} documents")
            except Exception:
                self.collection = self.client.create_collection(
                    name=self.collection_name, embedding_function=None,
                    metadata={"description": "Creative job analysis vector store"}
                )
                self.documents_count = 0
                logger.info(f"Created new collection '{self.collection_name}'")
        except Exception as e:
            self._initialization_error = f"Collection error: {str(e)}"
            logger.error(f"Failed to initialize collection: {e}")
    
    def _auto_detect_existing_index(self):
        """Auto-detect existing RAG index and extract metadata"""
        if not self.collection_exists():
            return False
        
        try:
            collection_info = self.collection.get(limit=5, include=['documents', 'metadatas'])
            
            if collection_info and collection_info.get('metadatas'):
                self.auto_detected = True
                self.existing_metadata = {
                    'documents_count': self.documents_count,
                    'sample_metadata': collection_info.get('metadatas', [])[:3],
                    'sample_documents': collection_info.get('documents', [])[:2],
                    'collection_created': True,
                    'last_detected': datetime.now().isoformat()
                }
                logger.info(f"Auto-detected existing RAG index with {self.documents_count} documents")
                return True
            return False
        except Exception as e:
            logger.warning(f"Error in auto-detection: {e}")
            return False
    
    def get_existing_index_summary(self) -> Dict:
        """Get summary of existing index for display"""
        if not self.auto_detected or not self.existing_metadata:
            return {}
        
        try:
            summary = {
                'detected': True,
                'documents_count': self.existing_metadata.get('documents_count', 0),
                'collection_name': self.collection_name,
                'backend': 'chroma',
                'persistent': ENABLE_PERSISTENCE,
                'last_detected': self.existing_metadata.get('last_detected', 'Unknown')
            }
            
            sample_metadata = self.existing_metadata.get('sample_metadata', [])
            if sample_metadata:
                role_counts = {'designers': 0, 'video_professionals': 0, 'photo_professionals': 0}
                software_found = set()
                companies_found = set()
                skills_found = set()
                
                for metadata in sample_metadata:
                    if metadata.get('is_designer'):
                        role_counts['designers'] += 1
                    if metadata.get('is_video_professional'):
                        role_counts['video_professionals'] += 1
                    if metadata.get('is_photo_professional'):
                        role_counts['photo_professionals'] += 1
                    
                    if metadata.get('company'):
                        companies_found.add(metadata['company'])
                    
                    if metadata.get('adobe_apps'):
                        if isinstance(metadata['adobe_apps'], str):
                            adobe_apps = [app.strip() for app in metadata['adobe_apps'].split(',') if app.strip()]
                            software_found.update(adobe_apps)
                    
                    if metadata.get('technical_skills'):
                        if isinstance(metadata['technical_skills'], str):
                            tech_skills = [skill.strip() for skill in metadata['technical_skills'].split(',') if skill.strip()]
                            skills_found.update(tech_skills)
                
                summary['sample_stats'] = {
                    'role_distribution': role_counts,
                    'unique_software_found': len(software_found),
                    'unique_skills_found': len(skills_found),
                    'companies_sample': list(companies_found)[:3],
                    'software_sample': list(software_found)[:5],
                    'skills_sample': list(skills_found)[:5]
                }
            
            return summary
        except Exception as e:
            logger.error(f"Error getting existing index summary: {e}")
            return {'detected': False, 'error': str(e)}
    
    def reconstruct_analysis_from_index(self) -> Dict:
        """Reconstruct dataset analysis from vector store metadata"""
        if not self.collection_exists():
            return {}
        
        try:
            st.info("📊 Reconstructing dataset statistics from indexed data...")
            progress_bar = st.progress(0)
            
            all_results = self.collection.get(include=['metadatas'])
            
            if not all_results or 'metadatas' not in all_results:
                return {}
            
            metadatas = all_results['metadatas']
            total_jobs = len(metadatas)
            
            # Initialize counters
            designer_count = 0
            video_count = 0
            photo_count = 0
            creative_count = 0
            adobe_only_count = 0
            non_adobe_only_count = 0
            both_apps_count = 0
            no_software_count = 0
            ai_tools_count = 0
            
            adobe_apps_list = []
            non_adobe_apps_list = []
            ai_tools_list = []
            technical_skills_list = []
            soft_skills_list = []
            creative_tasks_list = []
            has_technical_skills_count = 0
            has_soft_skills_count = 0
            
            photoshop_count = 0
            video_editing_count = 0
            
            # Process all metadata
            for i, metadata in enumerate(metadatas):
                # Role counts
                if metadata.get('is_designer'):
                    designer_count += 1
                if metadata.get('is_video_professional'):
                    video_count += 1
                if metadata.get('is_photo_professional'):
                    photo_count += 1
                if metadata.get('is_creative_professional'):
                    creative_count += 1
                
                # Software combinations
                has_adobe = metadata.get('has_adobe', False)
                has_non_adobe = metadata.get('has_non_adobe', False)
                has_ai = metadata.get('has_ai_tools', False)
                
                if has_adobe and not has_non_adobe:
                    adobe_only_count += 1
                elif has_non_adobe and not has_adobe:
                    non_adobe_only_count += 1
                elif has_adobe and has_non_adobe:
                    both_apps_count += 1
                elif not has_adobe and not has_non_adobe:
                    no_software_count += 1
                
                if has_ai:
                    ai_tools_count += 1
                
                # Collect software lists
                if metadata.get('adobe_apps'):
                    adobe_str = metadata['adobe_apps']
                    if isinstance(adobe_str, str):
                        apps = [app.strip() for app in adobe_str.split(',') if app.strip()]
                        adobe_apps_list.extend(apps)
                        if any('photoshop' in app.lower() for app in apps):
                            photoshop_count += 1
                        if any('premiere' in app.lower() or 'after effects' in app.lower() for app in apps):
                            video_editing_count += 1
                
                if metadata.get('non_adobe_apps'):
                    non_adobe_str = metadata['non_adobe_apps']
                    if isinstance(non_adobe_str, str):
                        apps = [app.strip() for app in non_adobe_str.split(',') if app.strip()]
                        non_adobe_apps_list.extend(apps)
                        if any(tool in apps for tool in ['Final Cut Pro', 'DaVinci Resolve', 'Avid Media Composer']):
                            video_editing_count += 1
                
                if metadata.get('ai_tools'):
                    ai_str = metadata['ai_tools']
                    if isinstance(ai_str, str):
                        tools = [tool.strip() for tool in ai_str.split(',') if tool.strip()]
                        ai_tools_list.extend(tools)
                
                # Collect skills
                if metadata.get('technical_skills'):
                    tech_str = metadata['technical_skills']
                    if isinstance(tech_str, str):
                        skills = [skill.strip() for skill in tech_str.split(',') if skill.strip()]
                        if skills:
                            has_technical_skills_count += 1
                            technical_skills_list.extend(skills)
                
                if metadata.get('soft_skills'):
                    soft_str = metadata['soft_skills']
                    if isinstance(soft_str, str):
                        skills = [skill.strip() for skill in soft_str.split(',') if skill.strip()]
                        if skills:
                            has_soft_skills_count += 1
                            soft_skills_list.extend(skills)
                
                if metadata.get('creative_tasks'):
                    tasks_str = metadata['creative_tasks']
                    if isinstance(tasks_str, str):
                        tasks = [task.strip() for task in tasks_str.split(',') if task.strip()]
                        creative_tasks_list.extend(tasks)
                
                if i % 10 == 0:
                    progress_bar.progress(min(1.0, (i + 1) / total_jobs))
            
            progress_bar.progress(1.0)
            
            # Build analysis structure
            reconstructed_analysis = {
                'total_jobs': total_jobs,
                'adobe_analysis': {
                    'adobe_only_count': adobe_only_count,
                    'non_adobe_only_count': non_adobe_only_count,
                    'both_apps_count': both_apps_count,
                    'no_software_count': no_software_count
                },
                'role_analysis': {
                    'designer_count': designer_count,
                    'video_professional_count': video_count,
                    'photo_professional_count': photo_count,
                    'creative_professional_count': creative_count
                },
                'software_analysis': {
                    'adobe_apps_frequency': Counter(adobe_apps_list),
                    'non_adobe_apps_frequency': Counter(non_adobe_apps_list),
                    'photoshop_count': photoshop_count,
                    'video_editing_count': video_editing_count
                },
                'ai_tools_analysis': {
                    'ai_tools_count': ai_tools_count,
                    'ai_tools_frequency': Counter(ai_tools_list)
                },
                'skills_analysis': {
                    'technical_skills_count': has_technical_skills_count,
                    'soft_skills_count': has_soft_skills_count,
                    'creative_tasks_count': len(creative_tasks_list),
                    'technical_skills_frequency': Counter(technical_skills_list),
                    'soft_skills_frequency': Counter(soft_skills_list),
                    'creative_tasks_frequency': Counter(creative_tasks_list),
                    'top_technical_skills': Counter(technical_skills_list).most_common(20),
                    'top_soft_skills': Counter(soft_skills_list).most_common(20),
                    'top_creative_tasks': Counter(creative_tasks_list).most_common(20)
                },
                'cross_disciplinary_analysis': {
                    'non_video_with_video_tools_count': 0,
                    'non_photo_with_photo_tools_count': 0,
                    'non_design_with_design_tools_count': 0
                },
                'source': 'reconstructed_from_index'
            }
            
            st.success(f"✅ Successfully reconstructed statistics from {total_jobs} indexed jobs")
            logger.info(f"Reconstructed analysis from index: {total_jobs} jobs")
            
            return reconstructed_analysis
        except Exception as e:
            st.error(f"Error reconstructing analysis: {str(e)}")
            logger.error(f"Analysis reconstruction error: {e}")
            return {}
    
    def display_existing_index_info(self):
        """Display information about detected existing index"""
        if not self.auto_detected:
            return
        
        summary = self.get_existing_index_summary()
        if not summary.get('detected'):
            return
        
        st.success(f"🔍 Auto-Detected Existing RAG Index")
        
        col1, col2, col3, col4 = st.columns(4)
        with col1:
            st.metric("Documents", f"{summary['documents_count']:,}")
        with col2:
            st.metric("Backend", summary['backend'].title())
        with col3:
            if summary['persistent']:
                st.metric("Storage", "Persistent")
            else:
                st.metric("Storage", "Memory")
        with col4:
            st.metric("Collection", self.collection_name)
        
        sample_stats = summary.get('sample_stats', {})
        if sample_stats:
            st.info("**Sample Content Preview:**")
            col1, col2 = st.columns(2)
            with col1:
                if sample_stats.get('role_distribution'):
                    role_dist = sample_stats['role_distribution']
                    st.write("**Role Types Found:**")
                    for role, count in role_dist.items():
                        if count > 0:
                            st.write(f"- {role.replace('_', ' ').title()}: {count}")
            with col2:
                if sample_stats.get('software_sample'):
                    st.write("**Software Found:**")
                    for software in sample_stats['software_sample']:
                        st.write(f"- {software}")
                if sample_stats.get('skills_sample'):
                    st.write("**Skills Found:**")
                    for skill in sample_stats['skills_sample']:
                        st.write(f"- {skill}")

    def is_available(self) -> bool:
        """Check if Chroma vector store is available"""
        return (self.client is not None and self.embedder is not None and 
                self._initialization_error is None)
    
    def collection_exists(self) -> bool:
        """Check if collection exists and has documents"""
        return (self.is_available() and self.collection is not None and 
                self.documents_count > 0)
    
    def get_dataset_fingerprint(self, df: pd.DataFrame) -> str:
        """Generate unique fingerprint for dataset to detect changes"""
        if df is None or df.empty:
            return ""
        
        fingerprint_data = {
            'shape': df.shape,
            'columns': sorted(df.columns.tolist()),
            'sample_hash': hashlib.md5(str(df.head().values.tobytes()).encode()).hexdigest()
        }
        
        fingerprint_str = json.dumps(fingerprint_data, sort_keys=True)
        return hashlib.sha256(fingerprint_str.encode()).hexdigest()
    
    def check_dataset_changed(self, df: pd.DataFrame) -> bool:
        """Check if dataset has changed since last indexing"""
        if not self.collection_exists():
            return True
        
        try:
            current_fingerprint = self.get_dataset_fingerprint(df)
            collection_info = self.collection.get()
            stored_metadata = collection_info.get('metadatas', [])
            
            if stored_metadata and len(stored_metadata) > 0:
                stored_fingerprint = stored_metadata[0].get('dataset_fingerprint', '')
                return current_fingerprint != stored_fingerprint
            
            return True
        except Exception as e:
            logger.warning(f"Error checking dataset fingerprint: {e}")
            return True
def create_optimized_chunks(self, processed_df: pd.DataFrame) -> List[Dict]:
        """Create optimized document chunks - FIXED for large datasets (5000+ rows)"""
        if not self.is_available():
            return []
        
        chunks = []
        
        try:
            total_rows = len(processed_df)
            st.info(f"Creating optimized chunks from {total_rows} creative job records...")
            progress_bar = st.progress(0)
            
            dataset_fingerprint = self.get_dataset_fingerprint(processed_df)
            
            # FIXED: Process in smaller batches to avoid memory issues
            chunk_batch_size = 100  # Process 100 rows at a time
            
            for batch_start in range(0, total_rows, chunk_batch_size):
                batch_end = min(batch_start + chunk_batch_size, total_rows)
                batch_df = processed_df.iloc[batch_start:batch_end]
                
                logger.info(f"Processing chunk batch {batch_start}-{batch_end} of {total_rows}")
                
                for idx, row in batch_df.iterrows():
                    try:
                        # Handle missing company gracefully
                        company = 'Unknown Company'
                        if 'company' in row and pd.notna(row['company']):
                            company = str(row['company']).strip()
                            if not company or company.lower() in ['unknown', 'nan', 'none', '']:
                                company = 'Unknown Company'
                        
                        # More robust title detection
                        title = None
                        title_columns = [
                            'summary_job_title', 'Summary job title', 
                            'displayed_job_title', 'Displayed job title', 
                            'job_title', 'title', 'Title'
                        ]
                        
                        for title_col in title_columns:
                            if title_col in row and pd.notna(row[title_col]):
                                title_value = str(row[title_col]).strip()
                                if title_value and title_value.lower() not in ['unknown', 'nan', 'none', '']:
                                    title = title_value
                                    break
                        
                        if not title:
                            title = f'Position {idx}'
                        
                        # FIXED: More robust description detection with AGGRESSIVE truncation
                        description = None
                        desc_columns = [
                            'job_description', 'Job Description', 
                            'description', 'Description', 'desc'
                        ]
                        
                        for desc_col in desc_columns:
                            if desc_col in row and pd.notna(row[desc_col]):
                                desc_value = str(row[desc_col]).strip()
                                if desc_value and desc_value.lower() not in ['unknown', 'nan', 'none', '']:
                                    # FIXED: Truncate EARLY to prevent memory issues
                                    description = desc_value[:600]  # Reduced from 800
                                    break
                        
                        if not description:
                            description = 'No description provided'
                        
                        # Optional fields handling
                        location_parts = []
                        for loc_col in ['city_job_location', 'City job location', 'city', 'location']:
                            if loc_col in row and pd.notna(row[loc_col]):
                                city = str(row[loc_col]).strip()
                                if city and city.lower() not in ['unknown', 'nan', 'none', '']:
                                    location_parts.append(city)
                                break
                        
                        location = ', '.join(location_parts) if location_parts else 'Location not specified'
                        
                        salary = 'Not Specified'
                        for salary_col in ['job_salary', 'Job salary', 'salary']:
                            if salary_col in row and pd.notna(row[salary_col]):
                                salary_value = str(row[salary_col]).strip()
                                if salary_value and salary_value.lower() not in ['unknown', 'not specified', 'nan', 'none', '']:
                                    salary = salary_value
                                break
                        
                        # Perform creative analysis
                        creative_analysis = detect_creative_software_and_skills(description)
                        job_categories = categorize_job_role(title, description)
                        
                        # Build job text - FIXED: More concise to reduce memory
                        job_text_parts = []
                        
                        job_text_parts.append(f"Job Title: {title}")
                        job_text_parts.append(f"Company: {company}")
                        
                        if location != 'Location not specified':
                            job_text_parts.append(f"Location: {location}")
                        
                        # FIXED: Truncate description MORE aggressively
                        desc_truncated = description[:400] if len(description) > 400 else description
                        job_text_parts.append(f"Description: {desc_truncated}")
                        
                        # Add software info - FIXED: Limit to top 3 to reduce size
                        if creative_analysis['adobe_apps']:
                            top_adobe = creative_analysis['adobe_apps'][:3]
                            job_text_parts.append(f"Adobe: {', '.join(top_adobe)}")
                        
                        if creative_analysis['non_adobe_apps']:
                            top_non_adobe = creative_analysis['non_adobe_apps'][:3]
                            job_text_parts.append(f"Non-Adobe: {', '.join(top_non_adobe)}")
                        
                        if creative_analysis['ai_tools']:
                            top_ai = creative_analysis['ai_tools'][:3]
                            job_text_parts.append(f"AI Tools: {', '.join(top_ai)}")
                        
                        # Add role types
                        role_types = []
                        if job_categories['is_designer']:
                            role_types.append('Designer')
                        if job_categories['is_video_professional']:
                            role_types.append('Video Professional')
                        if job_categories['is_photo_professional']:
                            role_types.append('Photo Professional')
                        
                        if role_types:
                            job_text_parts.append(f"Role: {', '.join(role_types)}")
                        
                        # FIXED: Limit skills to top 3 each
                        if creative_analysis['technical_skills']:
                            top_tech = creative_analysis['technical_skills'][:3]
                            job_text_parts.append(f"Tech Skills: {', '.join(top_tech)}")
                        
                        if creative_analysis['soft_skills']:
                            top_soft = creative_analysis['soft_skills'][:3]
                            job_text_parts.append(f"Soft Skills: {', '.join(top_soft)}")
                        
                        if creative_analysis['creative_tasks']:
                            top_tasks = creative_analysis['creative_tasks'][:3]
                            job_text_parts.append(f"Tasks: {', '.join(top_tasks)}")
                        
                        job_text = ". ".join(job_text_parts)
                        
                        # FIXED: Ensure job_text itself isn't too long
                        if len(job_text) > 1000:
                            job_text = job_text[:1000] + "..."
                        
                        # FIXED: Simplified metadata to reduce size
                        metadata = {
                            'row_index': int(idx),
                            'company': str(company)[:100],  # Limit length
                            'title': str(title)[:100],  # Limit length
                            'location': str(location)[:50],  # Limit length
                            'is_designer': bool(job_categories.get('is_designer', False)),
                            'is_video_professional': bool(job_categories.get('is_video_professional', False)),
                            'is_photo_professional': bool(job_categories.get('is_photo_professional', False)),
                            'is_creative_professional': bool(job_categories.get('is_creative_professional', False)),
                            # FIXED: Limit list lengths in metadata
                            'adobe_apps': ','.join(creative_analysis.get('adobe_apps', [])[:5]) if creative_analysis.get('adobe_apps') else '',
                            'non_adobe_apps': ','.join(creative_analysis.get('non_adobe_apps', [])[:5]) if creative_analysis.get('non_adobe_apps') else '',
                            'ai_tools': ','.join(creative_analysis.get('ai_tools', [])[:5]) if creative_analysis.get('ai_tools') else '',
                            'has_adobe': bool(len(creative_analysis.get('adobe_apps', [])) > 0),
                            'has_non_adobe': bool(len(creative_analysis.get('non_adobe_apps', [])) > 0),
                            'has_ai_tools': bool(len(creative_analysis.get('ai_tools', [])) > 0),
                            'software_combination': str(self._get_software_combination(creative_analysis)),
                            'technical_skills': ','.join(creative_analysis.get('technical_skills', [])[:5]) if creative_analysis.get('technical_skills') else '',
                            'soft_skills': ','.join(creative_analysis.get('soft_skills', [])[:5]) if creative_analysis.get('soft_skills') else '',
                            'creative_tasks': ','.join(creative_analysis.get('creative_tasks', [])[:5]) if creative_analysis.get('creative_tasks') else '',
                            'has_technical_skills': bool(len(creative_analysis.get('technical_skills', [])) > 0),
                            'has_soft_skills': bool(len(creative_analysis.get('soft_skills', [])) > 0),
                            'dataset_fingerprint': str(dataset_fingerprint)
                        }
                        
                        chunks.append({
                            'text': job_text,
                            'type': 'job_listing',
                            'job_id': int(idx),
                            'metadata': metadata
                        })
                        
                    except Exception as row_error:
                        logger.warning(f"Error processing row {idx}: {row_error}")
                        continue
                
                # FIXED: Update progress after each batch
                progress_bar.progress(min(1.0, batch_end / total_rows))
                
                # FIXED: Force garbage collection after each batch
                gc.collect()
            
            progress_bar.progress(1.0)
            st.success(f"✅ Created {len(chunks)} optimized job document chunks")
            return chunks
            
        except Exception as e:
            st.error(f"Error creating document chunks: {str(e)}")
            logger.error(f"Chunk creation error: {e}")
            return []
    
    def _get_software_combination(self, creative_analysis: Dict) -> str:
        """Determine software combination type for easier filtering"""
        has_adobe = len(creative_analysis.get('adobe_apps', [])) > 0
        has_non_adobe = len(creative_analysis.get('non_adobe_apps', [])) > 0
        has_ai = len(creative_analysis.get('ai_tools', [])) > 0
        
        if has_adobe and has_non_adobe and has_ai:
            return 'adobe_non_adobe_ai'
        elif has_adobe and has_non_adobe:
            return 'adobe_non_adobe'
        elif has_adobe and has_ai:
            return 'adobe_ai'
        elif has_non_adobe and has_ai:
            return 'non_adobe_ai'
        elif has_adobe:
            return 'adobe_only'
        elif has_non_adobe:
            return 'non_adobe_only'
        elif has_ai:
            return 'ai_only'
        else:
            return 'no_software_specified'

    def clear_collection(self):
        """Clear existing collection data"""
        if not self.is_available() or not self.collection:
            return False
        
        try:
            self.client.delete_collection(name=self.collection_name)
            
            self.collection = self.client.create_collection(
                name=self.collection_name,
                embedding_function=None,
                metadata={"description": "Creative job analysis vector store"}
            )
            self.documents_count = 0
            self.auto_detected = False
            self.existing_metadata = None
            logger.info(f"Cleared collection '{self.collection_name}'")
            return True
        except Exception as e:
            logger.error(f"Error clearing collection: {e}")
            return False
    
    def build_index(self, chunks: List[Dict]) -> bool:
        """Build Chroma vector index - FIXED for large datasets (5000+ rows)"""
        if not self.is_available():
            st.error("Chroma vector store not available")
            return False
        
        if not chunks:
            st.error("No chunks provided for indexing")
            return False
        
        try:
            self.clear_collection()
            
            st.info(f"Building Chroma vector index for {len(chunks)} documents...")
            progress_bar = st.progress(0)
            
            texts = [chunk['text'] for chunk in chunks]
            metadatas = [chunk.get('metadata', {}) for chunk in chunks]
            
            # FIXED: Reduced batch size from 64 to 16 for large datasets
            batch_size = 16  # CRITICAL FIX: Smaller batches for memory management
            
            successful_batches = 0
            failed_batches = 0
            
            for i in range(0, len(texts), batch_size):
                batch_texts = texts[i:i + batch_size]
                batch_metadatas = metadatas[i:i + batch_size]
                
                try:
                    # FIXED: Generate embeddings with explicit memory management
                    embeddings = self.embedder.encode(
                        batch_texts,
                        convert_to_tensor=False,
                        show_progress_bar=False,
                        normalize_embeddings=True,
                        batch_size=8  # FIXED: Internal batch size for encoder
                    )
                    
                    # FIXED: Convert to list immediately and clear original
                    embeddings_list = embeddings.tolist()
                    del embeddings  # Free memory
                    
                    doc_ids = [f"doc_{i+j}" for j in range(len(batch_texts))]
                    
                    # FIXED: Sanitize metadata more carefully
                    sanitized_metadatas = []
                    for md in batch_metadatas:
                        clean_md = {}
                        for k, v in md.items():
                            try:
                                if isinstance(v, (list, tuple, set)):
                                    # FIXED: Limit list size in metadata
                                    v_list = list(v)[:10]
                                    clean_md[k] = ', '.join([str(x) for x in v_list])
                                elif isinstance(v, dict):
                                    clean_md[k] = json.dumps(v, ensure_ascii=False)[:200]
                                elif isinstance(v, str):
                                    # FIXED: Limit string length
                                    clean_md[k] = v[:200]
                                else:
                                    clean_md[k] = v
                            except Exception as meta_error:
                                logger.warning(f"Error sanitizing metadata key {k}: {meta_error}")
                                clean_md[k] = str(v)[:100]
                        sanitized_metadatas.append(clean_md)
                    
                    # FIXED: Add to collection with error handling
                    self.collection.add(
                        embeddings=embeddings_list,
                        documents=batch_texts,
                        metadatas=sanitized_metadatas,
                        ids=doc_ids
                    )
                    
                    successful_batches += 1
                    
                    # FIXED: Clear memory after each batch
                    del embeddings_list
                    del batch_texts
                    del batch_metadatas
                    del sanitized_metadatas
                    gc.collect()
                    
                    progress_bar.progress(min(1.0, (i + len(batch_texts)) / len(texts)))
                    
                    # FIXED: Log progress every 10 batches
                    if (i // batch_size) % 10 == 0:
                        logger.info(f"Processed {i + batch_size}/{len(texts)} documents")
                    
                except Exception as batch_error:
                    failed_batches += 1
                    logger.error(f"Error processing batch {i//batch_size + 1}: {batch_error}")
                    # FIXED: Continue processing remaining batches
                    continue
            
            # FIXED: Final garbage collection
            gc.collect()
            
            self.documents_count = self.collection.count()
            self._auto_detect_existing_index()
            
            if self.documents_count > 0:
                st.success(f"✅ Chroma vector index built with {self.documents_count} documents")
                st.info(f"Successful batches: {successful_batches}, Failed batches: {failed_batches}")
                return True
            else:
                st.error("No documents were successfully indexed")
                return False
                
        except Exception as e:
            st.error(f"Error building Chroma vector index: {str(e)}")
            logger.error(f"Index building error: {e}")
            # FIXED: Ensure cleanup even on error
            gc.collect()
            return False
    
    def search(self, query: str, k: Optional[int] = None) -> List[Dict]:
        """Search using Chroma vector store"""
        if not self.collection_exists():
            return []
        
        try:
            query_embedding = self.embedder.encode([query], normalize_embeddings=True)[0]
            
            results = self.collection.query(
                query_embeddings=[query_embedding.tolist()],
                n_results=(self.documents_count if k is None else min(k, self.documents_count)),
                include=['documents', 'metadatas', 'distances']
            )
            
            formatted_results = []
            
            if results and 'documents' in results and results['documents']:
                documents = results['documents'][0]
                metadatas = results.get('metadatas', [[]])[0]
                distances = results.get('distances', [[]])[0]
                
                for i, (doc, metadata, distance) in enumerate(zip(documents, metadatas, distances)):
                    similarity_score = max(0.0, 1.0 - distance)
                    
                    # Convert string lists back to actual lists
                    if isinstance(metadata.get('adobe_apps'), str):
                        metadata['adobe_apps'] = [app.strip() for app in metadata['adobe_apps'].split(',') if app.strip()]
                    if isinstance(metadata.get('non_adobe_apps'), str):
                        metadata['non_adobe_apps'] = [app.strip() for app in metadata['non_adobe_apps'].split(',') if app.strip()]
                    if isinstance(metadata.get('ai_tools'), str):
                        metadata['ai_tools'] = [tool.strip() for tool in metadata['ai_tools'].split(',') if tool.strip()]
                    if isinstance(metadata.get('technical_skills'), str):
                        metadata['technical_skills'] = [skill.strip() for skill in metadata['technical_skills'].split(',') if skill.strip()]
                    if isinstance(metadata.get('soft_skills'), str):
                        metadata['soft_skills'] = [skill.strip() for skill in metadata['soft_skills'].split(',') if skill.strip()]
                    if isinstance(metadata.get('creative_tasks'), str):
                        metadata['creative_tasks'] = [task.strip() for task in metadata['creative_tasks'].split(',') if task.strip()]
                    
                    formatted_results.append({
                        'text': doc,
                        'score': similarity_score,
                        'rank': i + 1,
                        'metadata': metadata,
                        'distance': distance
                    })
            
            formatted_results.sort(key=lambda x: x['score'], reverse=True)
            return formatted_results
        except Exception as e:
            logger.error(f"Chroma search error: {e}")
            return []
    
    def get_stats(self) -> Dict:
        """Get vector store statistics with auto-detection info"""
        return {
            'backend': 'chroma',
            'documents_count': self.documents_count,
            'collection_name': self.collection_name,
            'persistent': ENABLE_PERSISTENCE,
            'available': self.is_available(),
            'auto_detected': self.auto_detected,
            'existing_index_summary': self.get_existing_index_summary() if self.auto_detected else None
        }

def analyze_creative_job_dataset(df: pd.DataFrame) -> Dict:
    """ENHANCED comprehensive analysis - FIXED for large datasets"""
    
    analysis_results = {
        'total_jobs': len(df),
        'adobe_analysis': {},
        'role_analysis': {},
        'software_analysis': {},
        'ai_tools_analysis': {},
        'skills_analysis': {},
        'industry_analysis': {},
        'cross_disciplinary_analysis': {},
        'detailed_breakdowns': {}
    }
    
    try:
        # Initialize storage lists
        adobe_only_jobs = []
        non_adobe_only_jobs = []
        both_apps_jobs = []
        ai_tool_jobs = []
        
        designer_jobs = []
        video_jobs = []
        photo_jobs = []
        creative_jobs = []
        
        # FIXED: Use Counter objects instead of lists for frequency counting
        adobe_apps_counter = Counter()
        non_adobe_apps_counter = Counter()
        ai_tools_counter = Counter()
        technical_skills_counter = Counter()
        soft_skills_counter = Counter()
        creative_tasks_counter = Counter()
        
        jobs_with_technical_skills = 0
        jobs_with_soft_skills = 0
        
        companies_hiring_creatives = {}
        
        # FIXED: Process in batches to manage memory
        batch_size = 500
        total_rows = len(df)
        
        progress_text = st.empty()
        progress_bar = st.progress(0)
        
        for batch_start in range(0, total_rows, batch_size):
            batch_end = min(batch_start + batch_size, total_rows)
            batch_df = df.iloc[batch_start:batch_end]
            
            progress_text.text(f"Analyzing rows {batch_start}-{batch_end} of {total_rows}...")
            
            for idx, row in batch_df.iterrows():
                try:
                    company = str(row.get('company', 'Unknown')).strip()[:100]
                    
                    title = None
                    for title_col in ['summary_job_title', 'Summary job title', 'displayed_job_title', 
                                     'Displayed job title', 'job_title', 'title', 'Title']:
                        if title_col in row and pd.notna(row[title_col]):
                            title = str(row[title_col]).strip()[:100]
                            break
                    
                    description = None
                    for desc_col in ['job_description', 'Job Description', 'description', 'Description']:
                        if desc_col in row and pd.notna(row[desc_col]):
                            # FIXED: Truncate description early
                            description = str(row[desc_col]).strip()[:600]
                            break
                    
                    if not title:
                        title = 'Unknown'
                    if not description:
                        description = ''
                    
                    creative_analysis = detect_creative_software_and_skills(description)
                    job_categories = categorize_job_role(title, description)
                    
                    has_adobe = len(creative_analysis.get('adobe_apps', [])) > 0
                    has_non_adobe = len(creative_analysis.get('non_adobe_apps', [])) > 0
                    has_ai_tools = len(creative_analysis.get('ai_tools', [])) > 0
                    
                    has_technical = len(creative_analysis.get('technical_skills', [])) > 0
                    has_soft = len(creative_analysis.get('soft_skills', [])) > 0
                    
                    # FIXED: Only store LIGHT job records, not full data
                    job_record_light = {
                        'index': idx,
                        'title': title[:100],
                        'company': company[:100]
                    }
                    
                    # FIXED: Store only counts in records, not full lists
                    if has_non_adobe and not has_adobe:
                        non_adobe_only_jobs.append(job_record_light)
                    elif has_adobe and not has_non_adobe:
                        adobe_only_jobs.append(job_record_light)
                    elif has_adobe and has_non_adobe:
                        both_apps_jobs.append(job_record_light)
                    
                    if has_ai_tools:
                        ai_tool_jobs.append(job_record_light)
                    
                    if job_categories.get('is_designer', False):
                        designer_jobs.append(job_record_light)
                    if job_categories.get('is_video_professional', False):
                        video_jobs.append(job_record_light)
                    if job_categories.get('is_photo_professional', False):
                        photo_jobs.append(job_record_light)
                    if job_categories.get('is_creative_professional', False):
                        creative_jobs.append(job_record_light)
                    
                    # FIXED: Use Counter.update() instead of extending lists
                    adobe_apps_counter.update(creative_analysis.get('adobe_apps', []))
                    non_adobe_apps_counter.update(creative_analysis.get('non_adobe_apps', []))
                    ai_tools_counter.update(creative_analysis.get('ai_tools', []))
                    
                    if has_technical:
                        technical_skills_counter.update(creative_analysis.get('technical_skills', []))
                        jobs_with_technical_skills += 1
                    
                    if has_soft:
                        soft_skills_counter.update(creative_analysis.get('soft_skills', []))
                        jobs_with_soft_skills += 1
                    
                    creative_tasks_counter.update(creative_analysis.get('creative_tasks', []))
                    
                    # Company tracking
                    if job_categories.get('is_creative_professional', False):
                        if company not in companies_hiring_creatives:
                            companies_hiring_creatives[company] = {
                                'total_jobs': 0,
                                'designer_jobs': 0,
                                'video_jobs': 0,
                                'photo_jobs': 0
                            }
                        companies_hiring_creatives[company]['total_jobs'] += 1
                        if job_categories.get('is_designer', False):
                            companies_hiring_creatives[company]['designer_jobs'] += 1
                        if job_categories.get('is_video_professional', False):
                            companies_hiring_creatives[company]['video_jobs'] += 1
                        if job_categories.get('is_photo_professional', False):
                            companies_hiring_creatives[company]['photo_jobs'] += 1
                    
                except Exception as row_error:
                    logger.warning(f"Error analyzing row {idx}: {row_error}")
                    continue
            
            # FIXED: Update progress and cleanup after each batch
            progress_bar.progress(min(1.0, batch_end / total_rows))
            gc.collect()
        
        progress_text.text("Finalizing analysis...")
        
        # FIXED: Store only necessary data, use counters for frequencies
        analysis_results['adobe_analysis'] = {
            'adobe_only_count': len(adobe_only_jobs),
            'non_adobe_only_count': len(non_adobe_only_jobs),
            'both_apps_count': len(both_apps_jobs),
            'no_software_count': total_rows - len(adobe_only_jobs) - len(non_adobe_only_jobs) - len(both_apps_jobs),
            # FIXED: Store only first 100 job records to save memory
            'adobe_only_jobs': adobe_only_jobs[:100],
            'non_adobe_only_jobs': non_adobe_only_jobs[:100],
            'both_apps_jobs': both_apps_jobs[:100]
        }
        
        analysis_results['role_analysis'] = {
            'designer_count': len(designer_jobs),
            'video_professional_count': len(video_jobs),
            'photo_professional_count': len(photo_jobs),
            'creative_professional_count': len(creative_jobs),
            # FIXED: Limit stored jobs
            'designer_jobs': designer_jobs[:100],
            'video_jobs': video_jobs[:100],
            'photo_jobs': photo_jobs[:100],
            'creative_jobs': creative_jobs[:100]
        }
        
        analysis_results['software_analysis'] = {
            'adobe_apps_frequency': adobe_apps_counter,
            'non_adobe_apps_frequency': non_adobe_apps_counter,
            'photoshop_count': adobe_apps_counter.get('Adobe Photoshop', 0) + adobe_apps_counter.get('Photoshop', 0),
            'video_editing_count': sum([
                adobe_apps_counter.get('Adobe Premiere Pro', 0),
                non_adobe_apps_counter.get('Final Cut Pro', 0),
                non_adobe_apps_counter.get('DaVinci Resolve', 0),
                non_adobe_apps_counter.get('Avid Media Composer', 0)
            ])
        }
        
        analysis_results['ai_tools_analysis'] = {
            'ai_tools_count': len(ai_tool_jobs),
            'ai_tools_jobs': ai_tool_jobs[:100],  # FIXED: Limit
            'ai_tools_frequency': ai_tools_counter
        }
        
        analysis_results['skills_analysis'] = {
            'technical_skills_count': jobs_with_technical_skills,
            'soft_skills_count': jobs_with_soft_skills,
            'creative_tasks_count': sum(creative_tasks_counter.values()),
            'technical_skills_frequency': technical_skills_counter,
            'soft_skills_frequency': soft_skills_counter,
            'creative_tasks_frequency': creative_tasks_counter,
            'top_technical_skills': technical_skills_counter.most_common(20),
            'top_soft_skills': soft_skills_counter.most_common(20),
            'top_creative_tasks': creative_tasks_counter.most_common(20)
        }
        
        analysis_results['industry_analysis'] = {
            'companies_hiring_creatives': companies_hiring_creatives,
            'top_creative_hiring_companies': sorted(
                companies_hiring_creatives.items(), 
                key=lambda x: x[1]['total_jobs'], 
                reverse=True
            )[:10]
        }
        
        # Cross-disciplinary analysis (simplified for large datasets)
        analysis_results['cross_disciplinary_analysis'] = {
            'non_video_with_video_tools_count': 0,
            'non_photo_with_photo_tools_count': 0,
            'non_design_with_design_tools_count': 0
        }
        
        analysis_results['detailed_breakdowns'] = {
            'photoshop_vs_competitors': {
                'photoshop_mentions': adobe_apps_counter.get('Adobe Photoshop', 0),
                'photoshop_competitors': {
                    'GIMP': non_adobe_apps_counter.get('GIMP', 0),
                    'Affinity Photo': non_adobe_apps_counter.get('Affinity Photo', 0),
                    'Corel Painter': non_adobe_apps_counter.get('Corel Painter', 0)
                }
            },
            'video_vs_photo_demand': {
                'video_editing_mentions': analysis_results['software_analysis']['video_editing_count'],
                'photo_editing_mentions': analysis_results['software_analysis']['photoshop_count']
            }
        }
        
        progress_bar.empty()
        progress_text.empty()
        
        # FIXED: Final cleanup
        gc.collect()
        
        return analysis_results
        
    except Exception as e:
        logger.error(f"Error in creative job analysis: {str(e)}")
        # FIXED: Cleanup on error
        gc.collect()
        return analysis_results

def generate_qa_answers(analysis_results: Dict, processed_df: pd.DataFrame) -> Dict[str, str]:
    """Generate specific answers to questions from q.txt"""
    
    answers = {}
    
    try:
        non_adobe_only = analysis_results['adobe_analysis']['non_adobe_only_jobs']
        apps_list = []
        occupations = []
        for job in non_adobe_only:
            occupations.append(job['title'])
        
        answers['non_adobe_only'] = {
            'count': len(non_adobe_only),
            'occupations': list(set(occupations))
        }
        
        both_apps = analysis_results['adobe_analysis']['both_apps_jobs']
        both_occupations = []
        for job in both_apps:
            both_occupations.append(job['title'])
        
        answers['both_apps'] = {
            'count': len(both_apps),
            'occupations': list(set(both_occupations))
        }
        
        cross_analysis = analysis_results['cross_disciplinary_analysis']
        
        answers['cross_disciplinary'] = {
            'non_video_with_video_tools': {
                'count': cross_analysis['non_video_with_video_tools_count']
            },
            'non_photo_with_photo_tools': {
                'count': cross_analysis['non_photo_with_photo_tools_count']
            },
            'non_design_with_design_tools': {
                'count': cross_analysis['non_design_with_design_tools_count']
            }
        }
        
        ai_analysis = analysis_results['ai_tools_analysis']
        ai_occupations = []
        for job in ai_analysis['ai_tools_jobs']:
            ai_occupations.append(job['title'])
        
        answers['ai_tools'] = {
            'count': ai_analysis['ai_tools_count'],
            'occupations': list(set(ai_occupations))
        }
        
        designer_analysis = analysis_results['role_analysis']
        answers['designer_roles'] = {
            'count': designer_analysis['designer_count'],
            'jobs': designer_analysis['designer_jobs'][:20]
        }
        
        software_analysis = analysis_results['software_analysis']
        answers['photoshop'] = {
            'photoshop_count': software_analysis['photoshop_count'],
            'video_editing_count': software_analysis['video_editing_count']
        }
        
        skills_analysis = analysis_results.get('skills_analysis', {})
        
        answers['skills'] = {
            'technical_skills_count': skills_analysis.get('technical_skills_count', 0),
            'soft_skills_count': skills_analysis.get('soft_skills_count', 0),
            'creative_tasks_count': skills_analysis.get('creative_tasks_count', 0),
            'top_technical_skills': skills_analysis.get('top_technical_skills', [])[:10],
            'top_soft_skills': skills_analysis.get('top_soft_skills', [])[:10],
            'top_creative_tasks': skills_analysis.get('top_creative_tasks', [])[:10]
        }
        
        return answers
        
    except Exception as e:
        logger.error(f"Error generating Q&A answers: {str(e)}")
        return answers

class EnhancedRAGVectorStore:
    """Modified vector store that uses Chroma as primary backend with fallback and auto-detection"""
    
    def __init__(self):
        self.chroma_store = None
        self.legacy_store = None
        self.use_chroma = CHROMA_AVAILABLE
        self._initialization_error = None
        self.auto_detected_index = False
        
        if self.use_chroma:
            try:
                self.chroma_store = ChromaVectorStore()
                if not self.chroma_store.is_available():
                    self.use_chroma = False
                    self._init_legacy_store()
                else:
                    logger.info("Using Chroma vector store")
                    self.auto_detected_index = self.chroma_store.auto_detected
            except Exception as e:
                logger.warning(f"Chroma initialization failed, falling back to legacy: {e}")
                self.use_chroma = False
                self._init_legacy_store()
        else:
            self._init_legacy_store()
    
    def _init_legacy_store(self):
        """Initialize legacy in-memory vector store as fallback"""
        try:
            self.legacy_store = {
                'embedder': None,
                'index': None,
                'documents': [],
                'metadata': [],
                'dimension': 384,
                '_index_built': False,
                'backend': 'memory'
            }
            
            if SENTENCE_TRANSFORMERS_AVAILABLE:
                model_name = 'all-MiniLM-L6-v2'
                self.legacy_store['embedder'] = SentenceTransformer(model_name)
                test_embedding = self.legacy_store['embedder'].encode(["test"], show_progress_bar=False)
                self.legacy_store['dimension'] = len(test_embedding[0])
            
            logger.info("Using legacy in-memory vector store")
        except Exception as e:
            self._initialization_error = f"Legacy store error: {str(e)}"
            logger.error(f"Failed to initialize legacy store: {e}")
    
    def is_available(self) -> bool:
        """Check if vector store is available"""
        if self.use_chroma and self.chroma_store:
            return self.chroma_store.is_available()
        elif self.legacy_store:
            return (self.legacy_store['embedder'] is not None and 
                   self._initialization_error is None)
        return False
    
    def collection_exists(self) -> bool:
        """Check if collection exists and has documents"""
        if self.use_chroma and self.chroma_store:
            return self.chroma_store.collection_exists()
        elif self.legacy_store:
            return (self.legacy_store['_index_built'] and 
                   len(self.legacy_store['documents']) > 0)
        return False
    
    def was_auto_detected(self) -> bool:
        """Check if existing index was auto-detected"""
        if self.use_chroma and self.chroma_store:
            return self.chroma_store.auto_detected
        return False
    
    def display_existing_index_info(self):
        """Display auto-detected index information"""
        if self.use_chroma and self.chroma_store and self.chroma_store.auto_detected:
            self.chroma_store.display_existing_index_info()
    
    def get_existing_index_summary(self) -> Dict:
        """Get summary of existing auto-detected index"""
        if self.use_chroma and self.chroma_store and self.chroma_store.auto_detected:
            return self.chroma_store.get_existing_index_summary()
        return {'detected': False}
    
    def reconstruct_analysis_from_index(self) -> Dict:
        """Reconstruct dataset analysis from indexed metadata"""
        if self.use_chroma and self.chroma_store:
            return self.chroma_store.reconstruct_analysis_from_index()
        return {}
    
    def check_dataset_changed(self, df: pd.DataFrame) -> bool:
        """Check if dataset has changed since last indexing"""
        if self.use_chroma and self.chroma_store:
            return self.chroma_store.check_dataset_changed(df)
        else:
            return True
    
    def create_optimized_chunks(self, processed_df: pd.DataFrame) -> List[Dict]:
        """Create optimized document chunks"""
        if self.use_chroma and self.chroma_store:
            return self.chroma_store.create_optimized_chunks(processed_df)
        else:
            return self._create_legacy_chunks(processed_df)
    
    def _create_legacy_chunks(self, processed_df: pd.DataFrame) -> List[Dict]:
        """Legacy chunk creation for fallback"""
        chunks = []
        try:
            st.info(f"Creating chunks from {len(processed_df)} records (legacy mode)...")
            progress_bar = st.progress(0)
            
            for idx, row in processed_df.iterrows():
                try:
                    title = str(row.get('summary_job_title', row.get('Summary job title', 
                                row.get('title', 'Unknown'))))
                    company = str(row.get('company', 'Unknown'))
                    description = str(row.get('job_description', row.get('Job Description', 
                                      row.get('description', ''))))
                    
                    if pd.isna(title):
                        title = 'Unknown Position'
                    if pd.isna(description):
                        description = 'No description provided'
                    
                    job_text = f"Job Title: {title}. Company: {company}. Description: {description[:500]}"
                    
                    chunks.append({
                        'text': job_text,
                        'type': 'job_listing',
                        'job_id': idx,
                        'metadata': {'row_index': idx, 'title': title, 'company': company}
                    })
                except Exception as row_error:
                    logger.warning(f"Error processing row {idx}: {row_error}")
                    continue
                
                if idx % 5 == 0:
                    progress_bar.progress(min(1.0, (idx + 1) / len(processed_df)))
            
            progress_bar.progress(1.0)
            st.success(f"Created {len(chunks)} chunks (legacy mode)")
            return chunks
        except Exception as e:
            st.error(f"Error creating chunks: {str(e)}")
            return []
    
    def build_index(self, chunks: List[Dict]) -> bool:
        """Build vector index"""
        if self.use_chroma and self.chroma_store:
            return self.chroma_store.build_index(chunks)
        else:
            return self._build_legacy_index(chunks)
    
    def _build_legacy_index(self, chunks: List[Dict]) -> bool:
        """Build legacy in-memory index"""
        if not self.legacy_store or not self.legacy_store['embedder']:
            st.error("Legacy vector store not available")
            return False
        
        try:
            self.legacy_store['documents'] = []
            self.legacy_store['metadata'] = []
            self.legacy_store['_memory_embeddings'] = []
            
            st.info(f"Building legacy index for {len(chunks)} documents...")
            progress_bar = st.progress(0)
            
            texts = [chunk['text'] for chunk in chunks]
            batch_size = 8
            
            for i in range(0, len(texts), batch_size):
                batch_texts = texts[i:i + batch_size]
                batch_chunks = chunks[i:i + batch_size]
                
                try:
                    embeddings = self.legacy_store['embedder'].encode(
                        batch_texts,
                        convert_to_tensor=False,
                        show_progress_bar=False,
                        normalize_embeddings=True
                    )
                    
                    for j, embedding in enumerate(embeddings):
                        self.legacy_store['_memory_embeddings'].append(embedding.tolist())
                        self.legacy_store['documents'].append(batch_texts[j])
                        self.legacy_store['metadata'].append(batch_chunks[j].get('metadata', {}))
                    
                    progress_bar.progress(min(1.0, (i + len(batch_texts)) / len(texts)))
                except Exception as batch_error:
                    logger.error(f"Error processing batch: {batch_error}")
                    continue
            
            if len(self.legacy_store['documents']) > 0:
                self.legacy_store['_index_built'] = True
                st.success(f"Legacy index built with {len(self.legacy_store['documents'])} documents")
                return True
            else:
                st.error("No documents indexed")
                return False
        except Exception as e:
            st.error(f"Error building legacy index: {str(e)}")
            return False
    
    def search(self, query: str, k: Optional[int] = None) -> List[Dict]:
        """Search vector store"""
        if self.use_chroma and self.chroma_store:
            return self.chroma_store.search(query, k)
        else:
            return self._search_legacy(query, k)
    
    def _search_legacy(self, query: str, k: Optional[int] = None) -> List[Dict]:
        """Search legacy in-memory store"""
        if (not self.legacy_store or not self.legacy_store['_index_built'] or 
            not self.legacy_store['embedder']):
            return []
        
        try:
            query_embedding = self.legacy_store['embedder'].encode([query], 
                                                                   normalize_embeddings=True)[0]
            
            if not hasattr(self.legacy_store, '_memory_embeddings') or not self.legacy_store['_memory_embeddings']:
                return []
            
            embeddings_array = np.array(self.legacy_store['_memory_embeddings'], dtype=np.float32)
            similarities = embeddings_array @ query_embedding
            top_indices = np.argsort(similarities)[::-1][:k]
            
            results = []
            for i, idx in enumerate(top_indices):
                similarity_score = float(similarities[idx])
                results.append({
                    'text': self.legacy_store['documents'][idx],
                    'score': similarity_score,
                    'rank': i + 1,
                    'metadata': self.legacy_store['metadata'][idx] if idx < len(self.legacy_store['metadata']) else {},
                    'distance': 1.0 - similarity_score
                })
            
            return results
        except Exception as e:
            logger.error(f"Legacy search error: {e}")
            return []
    
    def get_stats(self) -> Dict:
        """Get vector store statistics"""
        if self.use_chroma and self.chroma_store:
            return self.chroma_store.get_stats()
        elif self.legacy_store:
            return {
                'backend': 'legacy_memory',
                'documents_count': len(self.legacy_store.get('documents', [])),
                'available': self.is_available(),
                'persistent': False,
                'auto_detected': False
            }
        else:
            return {
                'backend': 'none',
                'documents_count': 0,
                'available': False,
                'persistent': False,
                'auto_detected': False
            }


class EnhancedOpenAIProcessor:
    def __init__(self, api_key: str):
        self._api_key = api_key
        self.client = None
        self.tokenizer = None
        self.max_context_length = 16000
        self.max_completion_tokens = 2000
        self.max_input_tokens = self.max_context_length - self.max_completion_tokens - 200
        self._initialization_error = None
        
        self._init_client()
        self._init_tokenizer()
    
    def _init_client(self):
        """Initialize OpenAI client with error handling"""
        try:
            self.client = openai.OpenAI(api_key=self._api_key)
            test_response = self.client.models.list()
            logger.info("OpenAI client initialized successfully")
        except Exception as e:
            self._initialization_error = f"OpenAI initialization error: {str(e)}"
            logger.error(f"OpenAI initialization failed: {e}")
    
    def _init_tokenizer(self):
        """Initialize tokenizer with fallback"""
        if TIKTOKEN_AVAILABLE:
            try:
                self.tokenizer = tiktoken.encoding_for_model("gpt-3.5-turbo")
            except Exception as e:
                logger.warning(f"Tiktoken initialization failed: {e}")
                self.tokenizer = None
    
    def is_available(self) -> bool:
        """Check availability"""
        return self.client is not None and self._initialization_error is None
    
    def count_tokens(self, text: str) -> int:
        """Count tokens with fallback"""
        if not text:
            return 0
        
        if self.tokenizer and TIKTOKEN_AVAILABLE:
            try:
                return len(self.tokenizer.encode(text))
            except:
                pass
        
        return int(len(text.split()) * 1.3)
    
    def create_enhanced_system_prompt(self, dataset_analysis: Dict) -> str:
        """Create system prompt optimized for q.txt questions"""
        
        total_jobs = dataset_analysis.get('total_jobs', 0)
        adobe_analysis = dataset_analysis.get('adobe_analysis', {})
        role_analysis = dataset_analysis.get('role_analysis', {})
        ai_analysis = dataset_analysis.get('ai_tools_analysis', {})
        skills_analysis = dataset_analysis.get('skills_analysis', {})
        
        system_prompt = f"""You are an expert Creative Professionals (CPros) job market analyst with deep expertise in creative software, job roles, skills requirements, and industry trends. You have access to a comprehensive dataset of {total_jobs} creative job listings.

**Your Core Specializations:**
1. **Adobe vs Non-Adobe Software Analysis** - Track requirements for Adobe Creative Suite vs alternatives
2. **Creative Role Categorization** - Identify designers, video professionals, photo professionals
3. **Cross-Disciplinary Requirements** - Analyze when non-video jobs need video tools, etc.
4. **AI Tools Integration** - Track adoption of AI tools in creative workflows
5. **Skills Analysis** - Technical skills, soft skills, and creative competencies
6. **Industry Trends** - Company hiring patterns and market demands

**CRITICAL INSTRUCTIONS FOR ANSWERING QUESTIONS:**

1. **For "How many" or count-based questions:**
   - ALWAYS use the PRE-COMPUTED STATISTICS provided at the top of the context
   - DO NOT count from the sample job listings shown
   - DO NOT estimate or extrapolate from samples
   - State the exact number from the pre-computed statistics

2. **For "What are" or "Which" questions:**
   - Use the sample job listings as representative examples
   - Combine with pre-computed statistics for comprehensive answers

3. **Dataset Overview (PRE-COMPUTED - USE THESE FOR COUNTS):**
   - Total Jobs Analyzed: {total_jobs:,}
   - Designer Roles: {role_analysis.get('designer_count', 0):,}
   - Video Professionals: {role_analysis.get('video_professional_count', 0):,}
   - Photo Professionals: {role_analysis.get('photo_professional_count', 0):,}
   - Adobe-only Jobs: {adobe_analysis.get('adobe_only_count', 0):,}
   - Non-Adobe-only Jobs: {adobe_analysis.get('non_adobe_only_count', 0):,}
   - Both Adobe & Non-Adobe: {adobe_analysis.get('both_apps_count', 0):,}
   - AI Tools Mentioned: {ai_analysis.get('ai_tools_count', 0):,}
   - Jobs with Technical Skills: {skills_analysis.get('technical_skills_count', 0):,}
   - Jobs with Soft Skills: {skills_analysis.get('soft_skills_count', 0):,}

**Response Structure:**
1. **Executive Summary** (Direct answer with EXACT pre-computed numbers)
2. **Detailed Analysis** (Breakdown with examples from sample listings)
3. **Software/Skills Breakdown** (Specific tools AND skills from both statistics and samples)
4. **Industry Insights** (Trends and recommendations)

**NEVER say "based on the N job listings provided" when the actual dataset is larger. Always reference the full dataset size of {total_jobs} jobs.**"""

        return system_prompt
    
    def prepare_rag_context(self, retrieved_docs: List[Dict], query: str, 
                          dataset_analysis: Dict = None, max_docs: int = 50) -> str:
        """Prepare enhanced RAG context"""
        context_parts = []
        
        try:
            if dataset_analysis:
                context_parts.append("=" * 80)
                context_parts.append("PRE-COMPUTED DATASET STATISTICS (USE THESE FOR ALL COUNTS):")
                context_parts.append("=" * 80)
                
                total_jobs = dataset_analysis.get('total_jobs', 0)
                context_parts.append(f"\n📊 TOTAL DATASET SIZE: {total_jobs} job listings analyzed\n")
                
                role_analysis = dataset_analysis.get('role_analysis', {})
                context_parts.append("**ROLE COUNTS:**")
                context_parts.append(f"- Designer Roles: {role_analysis.get('designer_count', 0)}")
                context_parts.append(f"- Video Professionals: {role_analysis.get('video_professional_count', 0)}")
                context_parts.append(f"- Photo Professionals: {role_analysis.get('photo_professional_count', 0)}")
                
                adobe_analysis = dataset_analysis.get('adobe_analysis', {})
                context_parts.append("\n**SOFTWARE REQUIREMENT COUNTS:**")
                context_parts.append(f"- Adobe-only: {adobe_analysis.get('adobe_only_count', 0)} jobs")
                context_parts.append(f"- Non-Adobe-only: {adobe_analysis.get('non_adobe_only_count', 0)} jobs")
                context_parts.append(f"- Both Adobe & Non-Adobe: {adobe_analysis.get('both_apps_count', 0)} jobs")
                
                ai_analysis = dataset_analysis.get('ai_tools_analysis', {})
                context_parts.append(f"\n**AI TOOLS:** {ai_analysis.get('ai_tools_count', 0)} jobs mention AI tools")
                
                skills_analysis = dataset_analysis.get('skills_analysis', {})
                context_parts.append(f"\n**SKILLS REQUIREMENTS:**")
                context_parts.append(f"- Jobs with Technical Skills: {skills_analysis.get('technical_skills_count', 0)}")
                context_parts.append(f"- Jobs with Soft Skills: {skills_analysis.get('soft_skills_count', 0)}")
                
                top_technical = skills_analysis.get('top_technical_skills', [])
                if top_technical:
                    context_parts.append(f"\n**TOP TECHNICAL SKILLS:**")
                    for skill, count in top_technical[:5]:
                        context_parts.append(f"  - {skill}: {count} mentions")
                
                software_analysis = dataset_analysis.get('software_analysis', {})
                context_parts.append(f"\n**SPECIFIC SOFTWARE COUNTS:**")
                context_parts.append(f"- Photoshop: {software_analysis.get('photoshop_count', 0)} jobs")
                context_parts.append(f"- Video Editing Software: {software_analysis.get('video_editing_count', 0)} jobs")
                
                context_parts.append("\n" + "=" * 80)
                context_parts.append("⚠️  IMPORTANT: Use the above PRE-COMPUTED numbers for ALL count questions!")
                context_parts.append("=" * 80 + "\n")
            
            if retrieved_docs:
                docs_to_include = min(len(retrieved_docs), max_docs)
                context_parts.append(f"\n📋 REPRESENTATIVE JOB LISTINGS ({docs_to_include} of {len(retrieved_docs)} most relevant):")
                context_parts.append("(These are EXAMPLES - use pre-computed statistics above for counts)\n")
                
                for i, doc in enumerate(retrieved_docs[:docs_to_include], 1):
                    score = doc.get('score', 0)
                    text = doc.get('text', '')
                    metadata = doc.get('metadata', {})
                    
                    context_parts.append(f"\n--- Job Example {i} (Relevance: {score:.3f}) ---")
                    context_parts.append(text)
                    
                    if metadata:
                        meta_info = []
                        
                        if metadata.get('adobe_apps'):
                            adobe_apps = metadata['adobe_apps']
                            if isinstance(adobe_apps, str):
                                adobe_apps = [app.strip() for app in adobe_apps.split(',') if app.strip()]
                            if adobe_apps:
                                meta_info.append(f"Adobe: {', '.join(adobe_apps)}")
                        
                        if metadata.get('technical_skills'):
                            tech_skills = metadata['technical_skills']
                            if isinstance(tech_skills, str):
                                tech_skills = [skill.strip() for skill in tech_skills.split(',') if skill.strip()]
                            if tech_skills:
                                meta_info.append(f"Technical Skills: {', '.join(tech_skills[:5])}")
                        
                        if meta_info:
                            context_parts.append(f"📌 Details: {' | '.join(meta_info)}")
            
            full_context = "\n".join(context_parts)
            
            max_context_tokens = int(self.max_input_tokens * 0.7)
            if self.count_tokens(full_context) > max_context_tokens:
                full_context = self._truncate_text(full_context, max_context_tokens)
            
            return full_context
        except Exception as e:
            logger.error(f"Error preparing RAG context: {e}")
            return ""
    
    def _truncate_text(self, text: str, max_tokens: int) -> str:
        """Truncate text to fit token limit"""
        if not text or max_tokens <= 0:
            return ""
        
        current_tokens = self.count_tokens(text)
        if current_tokens <= max_tokens:
            return text
        
        paragraphs = text.split('\n\n')
        truncated = ""
        
        for para in paragraphs:
            test_text = truncated + "\n\n" + para if truncated else para
            if self.count_tokens(test_text) > max_tokens:
                break
            truncated = test_text
        
        return truncated + "\n[Content truncated for length...]" if truncated else text[:max_tokens*4]
    
    def query_with_enhanced_rag(self, question: str, vector_store, 
                               dataset_analysis: Dict = None, k_results: Optional[int] = None) -> str:
        """Process queries with enhanced RAG"""
        
        if not self.is_available():
            return f"OpenAI client not available: {self._initialization_error}"

        dataset_analysis = dataset_analysis or {}
        
        try:
            retrieved_docs = []
            try:
                retrieved_docs = vector_store.search(question, k=k_results)
                logger.info(f"Retrieved {len(retrieved_docs)} documents for query")
            except Exception as search_error:
                logger.warning(f"Vector search failed: {search_error}")
            
            system_prompt = self.create_enhanced_system_prompt(dataset_analysis)
            
            max_docs = k_results if k_results is not None else min(len(retrieved_docs), 100)
            
            rag_context = self.prepare_rag_context(
                retrieved_docs, question, dataset_analysis, max_docs=max_docs
            )
            
            user_message = f"Question: {question}"
            if rag_context:
                user_message += f"\n\n{rag_context}"
            
            system_tokens = self.count_tokens(system_prompt)
            user_tokens = self.count_tokens(user_message)
            total_input_tokens = system_tokens + user_tokens
            
            logger.info(f"Token usage: system={system_tokens}, user={user_tokens}, total={total_input_tokens}")
            
            if total_input_tokens > self.max_input_tokens:
                excess_tokens = total_input_tokens - self.max_input_tokens
                if rag_context:
                    current_context_tokens = self.count_tokens(rag_context)
                    reduced_tokens = max(500, current_context_tokens - excess_tokens)
                    rag_context = self._truncate_text(rag_context, reduced_tokens)
                    user_message = f"Question: {question}\n\n{rag_context}"
            
            final_input_tokens = self.count_tokens(system_prompt) + self.count_tokens(user_message)
            available_tokens = self.max_context_length - final_input_tokens - 100
            completion_tokens = min(self.max_completion_tokens, max(500, available_tokens))
            
            try:
                response = self.client.chat.completions.create(
                    model="gpt-3.5-turbo",
                    messages=[
                        {"role": "system", "content": system_prompt},
                        {"role": "user", "content": user_message}
                    ],
                    max_tokens=completion_tokens,
                    temperature=0.2,
                    timeout=30
                )
                
                answer = response.choices[0].message.content
                logger.info(f"Successfully processed query")
                return answer
            except openai.APIError as api_error:
                error_msg = f"OpenAI API Error: {str(api_error)}"
                logger.error(error_msg)
                return f"API Error: {error_msg}"
            except Exception as api_error:
                error_msg = f"API call failed: {str(api_error)}"
                logger.error(error_msg)
                return f"Error: {error_msg}"
        except Exception as e:
            logger.error(f"Error in enhanced RAG query: {e}")
            return f"Processing error: {str(e)}"

class EnhancedCreativeJobProcessor:
    """Main processor class optimized for creative job analysis - FIXED for large datasets"""
    
    def __init__(self):
        self.df = None
        self.processed_df = None
        self.data_summary = None
        self.dataset_analysis = None
        self.qa_answers = None
        self.vector_store = EnhancedRAGVectorStore()
        self.dataset_fingerprint = None
        self.auto_detected_ready = False
        
    def load_csv(self, uploaded_file) -> bool:
        """Load CSV with enhanced encoding detection"""
        try:
            encodings = ['utf-8', 'latin-1', 'cp1252', 'iso-8859-1']
            
            for encoding in encodings:
                try:
                    uploaded_file.seek(0)
                    self.df = pd.read_csv(uploaded_file, encoding=encoding)
                    logger.info(f"CSV loaded with {encoding} encoding: {len(self.df)} rows, {len(self.df.columns)} columns")
                    break
                except UnicodeDecodeError:
                    continue
                except Exception as e:
                    logger.error(f"Error with {encoding}: {e}")
                    continue
            
            if self.df is None:
                raise ValueError("Could not read file with any supported encoding")
            
            if self.df.empty:
                raise ValueError("CSV file is empty")
            
            expected_patterns = ['company', 'job', 'title', 'description', 'location']
            found_matches = 0
            
            for col in self.df.columns:
                col_lower = str(col).lower()
                for pattern in expected_patterns:
                    if pattern in col_lower:
                        found_matches += 1
                        break
            
            if found_matches < 3:
                st.warning("Dataset may not be optimized for creative job analysis, but processing will continue")
            else:
                st.success(f"Creative job dataset detected with {found_matches}/{len(expected_patterns)} expected column types")
            
            return True
        except Exception as e:
            st.error(f"Error loading CSV: {str(e)}")
            logger.error(f"CSV loading error: {e}")
            return False
    
    def check_auto_detected_index(self) -> bool:
        """Check if existing RAG index was auto-detected and is ready for immediate use"""
        if not self.vector_store.is_available():
            return False
        
        if self.vector_store.was_auto_detected():
            self.auto_detected_ready = True
            st.success("🔍 **Auto-Detected Existing RAG Index** - Ready for immediate querying!")
            self.vector_store.display_existing_index_info()
            return True
        
        return False
    
    def use_auto_detected_index(self) -> bool:
        """Set up system to use auto-detected index without reprocessing"""
        if not self.auto_detected_ready:
            return False
        
        try:
            self.auto_detected_ready = True
            
            with st.spinner("🔄 Reconstructing dataset statistics from indexed metadata..."):
                self.dataset_analysis = self.vector_store.reconstruct_analysis_from_index()
            
            if not self.dataset_analysis or self.dataset_analysis.get('total_jobs', 0) == 0:
                st.warning("⚠️ Could not reconstruct full statistics. Please upload the original CSV for accurate counts.")
                self.dataset_analysis = {
                    'total_jobs': self.vector_store.get_stats().get('documents_count', 0),
                    'adobe_analysis': {}, 'role_analysis': {}, 'software_analysis': {},
                    'ai_tools_analysis': {}, 'skills_analysis': {},
                    'cross_disciplinary_analysis': {}, 'source': 'minimal_fallback'
                }
            else:
                st.success(f"✅ Successfully reconstructed statistics from {self.dataset_analysis['total_jobs']} indexed jobs")
            
            existing_summary = self.vector_store.get_existing_index_summary()
            if existing_summary.get('detected'):
                self.data_summary = {
                    'source': 'auto_detected',
                    'auto_detected_info': existing_summary,
                    'ready_for_queries': True,
                    'has_full_statistics': bool(self.dataset_analysis.get('total_jobs', 0) > 0)
                }
            
            st.success("✅ **Ready to query existing RAG index!**")
            
            if self.dataset_analysis.get('total_jobs', 0) > 0:
                st.info(f"📊 Statistics available for {self.dataset_analysis['total_jobs']} jobs")
                
                col1, col2, col3, col4, col5 = st.columns(5)
                with col1:
                    designer_count = self.dataset_analysis.get('role_analysis', {}).get('designer_count', 0)
                    st.metric("Designer Roles", designer_count)
                with col2:
                    video_count = self.dataset_analysis.get('role_analysis', {}).get('video_professional_count', 0)
                    st.metric("Video Pros", video_count)
                with col3:
                    adobe_only = self.dataset_analysis.get('adobe_analysis', {}).get('adobe_only_count', 0)
                    st.metric("Adobe Only", adobe_only)
                with col4:
                    ai_count = self.dataset_analysis.get('ai_tools_analysis', {}).get('ai_tools_count', 0)
                    st.metric("AI Tools", ai_count)
                with col5:
                    skills_count = self.dataset_analysis.get('skills_analysis', {}).get('soft_skills_count', 0)
                    st.metric("With Skills", skills_count)
            else:
                st.info("💡 For full statistics, upload the original CSV in Admin Mode")
            
            return True
        except Exception as e:
            logger.error(f"Error setting up auto-detected index: {e}")
            st.error(f"Error reconstructing statistics: {str(e)}")
            return False
    
    def check_existing_index(self) -> bool:
        """Enhanced check for existing RAG index with auto-detection"""
        if not self.vector_store.is_available():
            return False
        
        if self.check_auto_detected_index():
            return True
        
        if not self.vector_store.collection_exists():
            st.info("No existing RAG index found. Will build new index after processing.")
            return False
        
        if hasattr(self.vector_store, 'use_chroma') and self.vector_store.use_chroma and self.processed_df is not None:
            if self.vector_store.check_dataset_changed(self.processed_df):
                st.warning("Dataset appears to have changed since last indexing. Will rebuild index.")
                return False
        
        stats = self.vector_store.get_stats()
        st.success(f"Found existing RAG index with {stats.get('documents_count', 0)} documents using {stats.get('backend', 'unknown')} backend")
        return True
    
    def clean_and_process(self) -> bool:
        """Enhanced data cleaning and processing with dataset change detection"""
        if self.df is None:
            st.error("No data loaded")
            return False
        
        try:
            self.processed_df = self.df.copy()
            
            if hasattr(self.vector_store, 'use_chroma') and self.vector_store.use_chroma and self.vector_store.chroma_store:
                self.dataset_fingerprint = self.vector_store.chroma_store.get_dataset_fingerprint(self.processed_df)
            
            column_mapping = {}
            for col in self.processed_df.columns:
                original_col = col
                clean_col = str(col).strip().lower().replace(' ', '_')
                
                if 'company' in clean_col:
                    clean_col = 'company'
                elif 'summary' in clean_col and 'job' in clean_col and 'title' in clean_col:
                    clean_col = 'summary_job_title'
                elif 'displayed' in clean_col and 'job' in clean_col and 'title' in clean_col:
                    clean_col = 'displayed_job_title'
                elif 'job' in clean_col and 'description' in clean_col:
                    clean_col = 'job_description'
                elif 'description' in clean_col:
                    clean_col = 'job_description'
                elif 'title' in clean_col:
                    clean_col = 'displayed_job_title'
                elif 'city' in clean_col and ('job' in clean_col or 'location' in clean_col):
                    clean_col = 'city_job_location'
                elif 'state' in clean_col and ('job' in clean_col or 'location' in clean_col):
                    clean_col = 'state_job_location'
                elif 'country' in clean_col and ('job' in clean_col or 'location' in clean_col):
                    clean_col = 'country_job_location'
                elif 'salary' in clean_col:
                    clean_col = 'job_salary'
                elif 'date' in clean_col:
                    clean_col = 'date'
                
                column_mapping[original_col] = clean_col
            
            self.processed_df = self.processed_df.rename(columns=column_mapping)
            
            for col in self.processed_df.columns:
                if self.processed_df[col].dtype == 'object':
                    self.processed_df[col] = self.processed_df[col].astype(str)
                    self.processed_df[col] = self.processed_df[col].str.strip()
                    self.processed_df[col] = self.processed_df[col].replace(['nan', 'NaN', 'None', ''], 'Unknown')
                else:
                    self.processed_df[col] = self.processed_df[col].fillna(0)
            
            logger.info(f"Data cleaning completed: {len(self.processed_df)} rows, {len(self.processed_df.columns)} columns")
            return True
        except Exception as e:
            st.error(f"Error cleaning data: {str(e)}")
            logger.error(f"Data cleaning error: {e}")
            return False
    
    def analyze_dataset(self) -> bool:
        """Perform comprehensive creative job analysis with SKILLS - FIXED for large datasets"""
        if self.processed_df is None:
            st.error("Please clean data first")
            return False
        
        try:
            st.info("Performing comprehensive creative job analysis with skills tracking...")
            
            # FIXED: This now uses the optimized analyze_creative_job_dataset function
            self.dataset_analysis = analyze_creative_job_dataset(self.processed_df)
            
            self.qa_answers = generate_qa_answers(self.dataset_analysis, self.processed_df)
            
            self.data_summary = self._generate_data_summary()
            
            st.success("Dataset analysis completed successfully with skills tracking!")
            return True
        except Exception as e:
            st.error(f"Error during dataset analysis: {str(e)}")
            logger.error(f"Dataset analysis error: {e}")
            return False
    
    def _generate_data_summary(self) -> Dict:
        """Generate comprehensive data summary"""
        if not self.dataset_analysis:
            return {}
        
        return {
            'total_jobs': (self.dataset_analysis or {}).get('total_jobs', 0),
            'columns': list(self.processed_df.columns) if self.processed_df is not None else [],
            'analysis_results': self.dataset_analysis,
            'qa_answers': self.qa_answers,
            'dataset_fingerprint': self.dataset_fingerprint
        }
    
    def build_rag_index(self, force_rebuild: bool = False) -> bool:
        """Build optimized RAG index with persistence support and SKILLS - FIXED for large datasets"""
        if self.processed_df is None or not self.dataset_analysis:
            st.error("Please clean and analyze data first")
            return False
        
        if not force_rebuild and self.check_existing_index():
            return True
        
        if self.vector_store.collection_exists() and not force_rebuild:
            st.warning("⚠️ Existing RAG index will be overwritten with new dataset")
            if not st.button("Continue with Index Rebuild", type="secondary"):
                st.info("Index rebuild cancelled. Using existing index.")
                return True
        
        try:
            st.info("Building optimized RAG vector index with skills tracking...")
            
            # FIXED: This now uses the optimized create_optimized_chunks method
            chunks = self.vector_store.create_optimized_chunks(self.processed_df)
            
            if not chunks:
                st.error("No document chunks created")
                return False
            
            # FIXED: This now uses the optimized build_index method
            if self.vector_store.build_index(chunks):
                st.success("✅ RAG index built successfully with skills tracking!")
                
                stats = self.vector_store.get_stats()
                if stats.get('persistent', False):
                    st.info(f"💾 Index persisted to disk at: {CHROMA_PERSIST_PATH}")
                else:
                    st.info("💾 Index stored in memory (not persistent)")
                
                return True
            else:
                st.error("Failed to build RAG index")
                return False
        except Exception as e:
            st.error(f"Error building RAG index: {str(e)}")
            logger.error(f"RAG index building error: {e}")
            return False
    
    def process_all(self, force_rebuild_index: bool = False) -> bool:
        """Complete processing workflow with persistence awareness and SKILLS"""
        try:
            if not self.clean_and_process():
                return False
            
            if not self.analyze_dataset():
                return False
            
            if not force_rebuild_index and self.check_existing_index():
                st.info("✅ Using existing persistent RAG index")
                return True
            
            if not self.build_rag_index(force_rebuild_index):
                return False
            
            return True
        except Exception as e:
            st.error(f"Error in complete processing: {str(e)}")
            return False

    def get_analysis_summary(self) -> Dict:
        """Get analysis summary for display - ENHANCED with SKILLS"""
        if not self.dataset_analysis:
            return {}
        
        try:
            skills_analysis = (self.dataset_analysis or {}).get('skills_analysis', {})
            
            summary = {
                'total_jobs': (self.dataset_analysis or {}).get('total_jobs', 0),
                'role_counts': {
                    'designers': (self.dataset_analysis or {}).get('role_analysis', {}).get('designer_count', 0),
                    'video_professionals': (self.dataset_analysis or {}).get('role_analysis', {}).get('video_professional_count', 0),
                    'photo_professionals': (self.dataset_analysis or {}).get('role_analysis', {}).get('photo_professional_count', 0)
                },
                'software_counts': {
                    'adobe_only': (self.dataset_analysis or {}).get('adobe_analysis', {}).get('adobe_only_count', 0),
                    'non_adobe_only': (self.dataset_analysis or {}).get('adobe_analysis', {}).get('non_adobe_only_count', 0),
                    'both_adobe_non_adobe': (self.dataset_analysis or {}).get('adobe_analysis', {}).get('both_apps_count', 0),
                    'ai_tools': (self.dataset_analysis or {}).get('ai_tools_analysis', {}).get('ai_tools_count', 0)
                },
                'skills_counts': {
                    'technical_skills': skills_analysis.get('technical_skills_count', 0),
                    'soft_skills': skills_analysis.get('soft_skills_count', 0),
                    'creative_tasks': skills_analysis.get('creative_tasks_count', 0)
                },
                'top_adobe_apps': (self.dataset_analysis or {}).get('software_analysis', {}).get('adobe_apps_frequency', Counter()).most_common(5),
                'top_non_adobe_apps': (self.dataset_analysis or {}).get('software_analysis', {}).get('non_adobe_apps_frequency', Counter()).most_common(5),
                'top_technical_skills': skills_analysis.get('top_technical_skills', [])[:5],
                'top_soft_skills': skills_analysis.get('top_soft_skills', [])[:5],
                'top_creative_tasks': skills_analysis.get('top_creative_tasks', [])[:5],
                'cross_disciplinary': {
                    'non_video_with_video_tools': (self.dataset_analysis or {}).get('cross_disciplinary_analysis', {}).get('non_video_with_video_tools_count', 0),
                    'non_photo_with_photo_tools': (self.dataset_analysis or {}).get('cross_disciplinary_analysis', {}).get('non_photo_with_photo_tools_count', 0),
                    'non_design_with_design_tools': (self.dataset_analysis or {}).get('cross_disciplinary_analysis', {}).get('non_design_with_design_tools_count', 0)
                },
                'persistence_info': {
                    'backend': self.vector_store.get_stats().get('backend', 'unknown'),
                    'persistent': self.vector_store.get_stats().get('persistent', False),
                    'dataset_fingerprint': self.dataset_fingerprint,
                    'auto_detected': self.auto_detected_ready
                }
            }
            
            return summary
        except Exception as e:
            logger.error(f"Error generating analysis summary: {e}")
            return {}
    
    def get_persistence_status(self) -> Dict:
        """Get current persistence status"""
        stats = self.vector_store.get_stats()
        return {
            'chroma_available': CHROMA_AVAILABLE,
            'persistence_enabled': ENABLE_PERSISTENCE,
            'backend': stats.get('backend', 'unknown'),
            'persistent': stats.get('persistent', False),
            'documents_count': stats.get('documents_count', 0),
            'collection_exists': self.vector_store.collection_exists(),
            'persist_path': CHROMA_PERSIST_PATH if ENABLE_PERSISTENCE else None,
            'auto_detected': stats.get('auto_detected', False),
            'auto_detected_ready': self.auto_detected_ready
        }
    
    def is_ready_for_queries(self) -> bool:
        """Check if system is ready for queries (either processed or auto-detected)"""
        if (self.processed_df is not None and 
            self.dataset_analysis is not None and 
            self.vector_store.collection_exists()):
            return True
        
        if self.auto_detected_ready:
            return True
        
        return False

def initialize_session_state():
    """Initialize session state variables for multi-view application"""
    if 'current_view' not in st.session_state:
        st.session_state.current_view = 'master'
    
    if 'processor' not in st.session_state:
        st.session_state.processor = EnhancedCreativeJobProcessor()
    
    if 'openai_processor' not in st.session_state:
        st.session_state.openai_processor = None
    
    if 'rag_ready' not in st.session_state:
        st.session_state.rag_ready = False
    
    if 'auto_detected_mode' not in st.session_state:
        st.session_state.auto_detected_mode = False
    
    if 'query_history' not in st.session_state:
        st.session_state.query_history = []
    
    if 'last_query' not in st.session_state:
        st.session_state.last_query = ""
    
    if 'last_response' not in st.session_state:
        st.session_state.last_response = ""


def show_master_view():
    """Master view for selecting between Admin and Client modes"""
    st.title("🎨 Creative Job RAG Analyzer")
    st.markdown("### Choose Your Mode")
    
    processor = st.session_state.processor
    stats = processor.vector_store.get_stats()
    
    if stats.get('documents_count', 0) > 0:
        st.success(f"✅ Existing RAG Index Found: {stats['documents_count']:,} documents")
        st.info(f"Backend: {stats['backend']} | Persistent: {'Yes' if stats.get('persistent') else 'No'}")
    else:
        st.warning("⚠️ No RAG index found. Please create one in Admin Mode first.")
    
    st.markdown("---")
    
    col1, col2 = st.columns(2)
    
    with col1:
        st.markdown("### 🔧 Admin Mode")
        st.markdown("""
        **For Administrators:**
        - Upload creative job datasets (CSV)
        - Build and manage RAG indexes
        - View dataset statistics
        - Configure vector storage
        """)
        
        if st.button("Enter Admin Mode", type="primary", use_container_width=True):
            st.session_state.current_view = 'admin'
            st.rerun()
    
    with col2:
        st.markdown("### 💬 Client Mode")
        st.markdown("""
        **For Users:**
        - Query the creative jobs database
        - Get AI-powered insights
        - Analyze industry trends
        - Follow-up questions supported
        """)
        
        if stats.get('documents_count', 0) > 0:
            if st.button("Enter Client Mode", type="primary", use_container_width=True):
                st.session_state.current_view = 'client'
                st.rerun()
        else:
            st.button("Enter Client Mode", type="primary", use_container_width=True, disabled=True)
            st.caption("⚠️ Create RAG index in Admin Mode first")
    
    st.markdown("---")
    st.markdown("### 📊 System Information")
    
    info_col1, info_col2, info_col3, info_col4 = st.columns(4)
    
    with info_col1:
        if CHROMA_AVAILABLE:
            st.success("✅ ChromaDB")
        else:
            st.error("❌ ChromaDB")
    
    with info_col2:
        if ENABLE_PERSISTENCE:
            st.success("✅ Persistence")
        else:
            st.info("💾 Memory Only")
    
    with info_col3:
        api_key = get_openai_api_key()
        if api_key:
            st.success("✅ OpenAI API")
        else:
            st.error("❌ No API Key")
    
    with info_col4:
        if SENTENCE_TRANSFORMERS_AVAILABLE:
            st.success("✅ Embeddings")
        else:
            st.error("❌ Embeddings")


def show_admin_view():
    """Admin view for RAG index creation and management"""
    
    col1, col2 = st.columns([6, 1])
    with col1:
        st.title("🔧 Admin Mode - RAG Index Management")
    with col2:
        if st.button("← Back", type="secondary"):
            st.session_state.current_view = 'master'
            st.rerun()
    
    processor = st.session_state.processor
    
    st.markdown("### 📊 Current RAG Index Status")
    
    stats = processor.vector_store.get_stats()
    
    if stats.get('documents_count', 0) > 0:
        col1, col2, col3, col4 = st.columns(4)
        with col1:
            st.metric("Documents", f"{stats['documents_count']:,}")
        with col2:
            st.metric("Backend", stats['backend'].title())
        with col3:
            if stats.get('persistent'):
                st.metric("Storage", "Persistent", delta="✓")
            else:
                st.metric("Storage", "Memory", delta="⚠")
        with col4:
            st.metric("Status", "Active", delta="✓")
        
        if stats.get('auto_detected'):
            processor.vector_store.display_existing_index_info()
        
        st.markdown("---")
        st.markdown("### ⚙️ Index Management")
        
        mgmt_col1, mgmt_col2, mgmt_col3 = st.columns(3)
        
        with mgmt_col1:
            if st.button("🔄 Rebuild Index", type="secondary", use_container_width=True):
                if processor.processed_df is not None and processor.dataset_analysis is not None:
                    with st.spinner("Rebuilding RAG index..."):
                        if processor.build_rag_index(force_rebuild=True):
                            st.success("✅ Index rebuilt successfully!")
                            st.rerun()
                else:
                    st.error("No processed dataset available. Please upload and process data first.")
        
        with mgmt_col2:
            if st.button("🗑️ Clear Index", type="secondary", use_container_width=True):
                if st.button("⚠️ Confirm Clear", type="secondary"):
                    if processor.vector_store.use_chroma and processor.vector_store.chroma_store:
                        if processor.vector_store.chroma_store.clear_collection():
                            st.success("✅ Index cleared")
                            st.session_state.rag_ready = False
                            st.rerun()
        
        with mgmt_col3:
            if st.button("📊 View Details", type="secondary", use_container_width=True):
                with st.expander("Detailed Index Information", expanded=True):
                    st.json(stats)
    else:
        st.error("❌ No RAG index found")
        st.info("👇 Upload a CSV file below to create a new RAG index")
    
    st.markdown("---")
    
    with st.sidebar:
        st.header("🔧 Configuration")
        
        st.subheader("🔑 API Configuration")
        if st.session_state.openai_processor and st.session_state.openai_processor.is_available():
            st.success("✅ OpenAI API Ready")
        else:
            st.error("❌ OpenAI API Not Available")
            st.caption("Check your API key configuration")
        
        st.markdown("---")
        
        st.subheader("💾 Storage Settings")
        if ENABLE_PERSISTENCE:
            st.success("✅ Persistence Enabled")
            st.caption(f"Path: {CHROMA_PERSIST_PATH}")
        else:
            st.info("💾 Memory Mode Active")
        
        if CHROMA_AVAILABLE:
            st.success("✅ ChromaDB Available")
        else:
            st.error("❌ ChromaDB Missing")
        
        st.markdown("---")
        
        st.subheader("⚙️ Processing Options")
        force_rebuild = st.checkbox("Force Index Rebuild", help="Rebuild index even if data hasn't changed")
    
    st.markdown("### 📁 Upload Creative Job Dataset")
    
    uploaded_file = st.file_uploader(
        "Choose a CSV file containing creative job data",
        type="csv",
        help="Expected columns: Company, Job Title, Job Description, Location, etc."
    )
    
    if uploaded_file is not None:
        if processor.vector_store.collection_exists():
            st.warning("⚠️ **Data Overwrite Warning**: Uploading a new CSV will replace the existing RAG index.")
            if not st.checkbox("I understand existing data will be overwritten", key="overwrite_confirm"):
                st.stop()
        
        if processor.load_csv(uploaded_file):
            st.success("✅ Dataset loaded successfully!")
            
            col1, col2, col3 = st.columns(3)
            with col1:
                st.metric("Total Records", f"{len(processor.df):,}")
            with col2:
                st.metric("Columns", len(processor.df.columns))
            with col3:
                memory_usage = processor.df.memory_usage(deep=True).sum() / 1024**2
                st.metric("Memory Usage", f"{memory_usage:.1f} MB")
            
            with st.expander("📋 Sample Data Preview"):
                st.dataframe(processor.df.head(), use_container_width=True)
            
            st.markdown("---")
            st.markdown("### ⚙️ Processing Workflow")
            
            col1, col2, col3 = st.columns(3)
            
            with col1:
                if st.button("🔧 Clean & Process", type="secondary", use_container_width=True):
                    with st.spinner("Cleaning and processing data..."):
                        if processor.clean_and_process():
                            st.success("✅ Data processed!")
                            st.rerun()
                        else:
                            st.error("❌ Processing failed")
            
            with col2:
                if st.button("📊 Analyze Dataset", type="secondary", use_container_width=True):
                    if processor.processed_df is not None:
                        with st.spinner("Analyzing creative job patterns with skills tracking..."):
                            if processor.analyze_dataset():
                                st.success("✅ Analysis complete with skills!")
                                st.rerun()
                            else:
                                st.error("❌ Analysis failed")
                    else:
                        st.error("Please clean data first")
            
            with col3:
                if st.button("🧠 Build RAG Index", type="primary", use_container_width=True):
                    if (processor.processed_df is not None and 
                        processor.dataset_analysis is not None):
                        
                        with st.spinner("Building RAG vector index with skills..."):
                            if processor.build_rag_index(force_rebuild):
                                st.session_state.rag_ready = True
                                st.success("✅ RAG system ready with skills tracking!")
                                st.rerun()
                            else:
                                st.error("❌ RAG build failed")
                    else:
                        st.error("Please process and analyze data first")
            
            st.markdown("### 🚀 Complete Workflow")
            if st.button("⚡ Process Everything (Clean → Analyze → Build RAG)", 
                        type="primary", use_container_width=True):
                with st.spinner("Running complete processing workflow with skills tracking..."):
                    if processor.process_all(force_rebuild):
                        st.session_state.rag_ready = True
                        st.balloons()
                        st.success("🎉 Complete workflow successful with skills tracking!")
                        st.rerun()
                    else:
                        st.error("❌ Workflow failed")

def show_client_view():
    """Client view for querying the RAG system"""
    
    col1, col2 = st.columns([6, 1])
    with col1:
        st.title("💬 Client Mode - Query Interface")
    with col2:
        if st.button("← Back", type="secondary"):
            st.session_state.current_view = 'master'
            st.rerun()
    
    processor = st.session_state.processor
    openai_processor = st.session_state.openai_processor
    
    st.markdown("### 📊 RAG Index Status")
    
    stats = processor.vector_store.get_stats()
    
    if stats.get('documents_count', 0) > 0:
        col1, col2, col3, col4 = st.columns(4)
        with col1:
            st.metric("Documents", f"{stats['documents_count']:,}")
        with col2:
            st.metric("Backend", stats['backend'].title())
        with col3:
            if stats.get('persistent'):
                st.success("💾 Persistent Storage")
            else:
                st.info("💾 Memory Storage")
        with col4:
            st.success("✅ Index Active")
        
        if stats.get('auto_detected'):
            st.info("🔍 Using auto-detected existing index")
            
            if processor.dataset_analysis is None or processor.dataset_analysis.get('total_jobs', 0) == 0:
                st.warning("⚠️ Statistics not available - reconstructing from index...")
                with st.spinner("Reconstructing dataset statistics..."):
                    processor.dataset_analysis = processor.vector_store.reconstruct_analysis_from_index()
                    
                    if processor.dataset_analysis and processor.dataset_analysis.get('total_jobs', 0) > 0:
                        st.success(f"✅ Reconstructed statistics for {processor.dataset_analysis['total_jobs']} jobs")
                        st.rerun()
                    else:
                        st.error("❌ Could not reconstruct statistics. Please upload the original CSV in Admin Mode.")
    else:
        st.error("❌ No RAG index found")
        st.warning("⚠️ Please create a RAG index in Admin Mode before querying")
        st.info("👈 Return to Master View and enter Admin Mode to create an index")
        st.stop()
    
    st.markdown("---")
    
    if processor.dataset_analysis is None or processor.dataset_analysis.get('total_jobs', 0) == 0:
        st.warning("⚠️ **Statistics Missing**: Dataset analysis not available")
        st.info("For accurate counts in query responses, statistics are required.")
        
        col1, col2 = st.columns(2)
        with col1:
            if st.button("🔄 Reconstruct Statistics from Index", type="primary", use_container_width=True):
                with st.spinner("Reconstructing statistics from indexed metadata..."):
                    processor.dataset_analysis = processor.vector_store.reconstruct_analysis_from_index()
                    if processor.dataset_analysis and processor.dataset_analysis.get('total_jobs', 0) > 0:
                        st.success("✅ Statistics reconstructed successfully!")
                        st.rerun()
                    else:
                        st.error("❌ Reconstruction failed")
        
        with col2:
            st.info("**Alternative**: Upload the original CSV in Admin Mode for full statistics")
    else:
        st.success("✅ **Statistics Available**")
        stats_col1, stats_col2, stats_col3, stats_col4, stats_col5 = st.columns(5)
        with stats_col1:
            st.metric("Total Jobs", f"{processor.dataset_analysis.get('total_jobs', 0):,}")
        with stats_col2:
            designer_count = processor.dataset_analysis.get('role_analysis', {}).get('designer_count', 0)
            st.metric("Designers", designer_count)
        with stats_col3:
            adobe_only = processor.dataset_analysis.get('adobe_analysis', {}).get('adobe_only_count', 0)
            st.metric("Adobe Only", adobe_only)
        with stats_col4:
            ai_count = processor.dataset_analysis.get('ai_tools_analysis', {}).get('ai_tools_count', 0)
            st.metric("AI Tools", ai_count)
        with stats_col5:
            skills_count = processor.dataset_analysis.get('skills_analysis', {}).get('soft_skills_count', 0)
            st.metric("With Skills", skills_count)
    
    st.markdown("---")
    
    with st.sidebar:
        st.header("⚙️ Query Settings")
        
        k_choice = st.selectbox(
            "Retrieved Documents", 
            options=["All records (recommended)", "10", "20", "50", "100"], 
            index=0, 
            help="Choose number of relevant documents to retrieve. 'All records' retrieves all available documents for most accurate counting."
        )
        k_results = None if k_choice.startswith("All") else int(k_choice)
        
        show_retrieved = st.checkbox("Show Retrieved Context", value=True, help="Display the documents used to answer your question")
        show_debug = st.checkbox("Show Debug Information", value=False, help="Display technical debugging information")
        
        st.markdown("---")
        
        st.header("🔑 API Status")
        if openai_processor and openai_processor.is_available():
            st.success("✅ OpenAI API Ready")
        else:
            st.error("❌ OpenAI API Not Available")
            st.caption("Check API key configuration")
        
        st.markdown("---")
        
        st.header("📚 Query History")
        if st.session_state.query_history:
            st.caption(f"Recent queries: {len(st.session_state.query_history)}")
            if st.button("Clear History", type="secondary", use_container_width=True):
                st.session_state.query_history = []
                st.session_state.last_query = ""
                st.session_state.last_response = ""
                st.rerun()
            
            with st.expander("View History"):
                for i, query in enumerate(reversed(st.session_state.query_history[-5:]), 1):
                    st.caption(f"{i}. {query[:50]}...")
        else:
            st.caption("No queries yet")
    
    st.markdown("### 🤖 Intelligent Creative Job Analysis")
    st.markdown("Ask sophisticated questions about creative professionals, software requirements, skills, and industry trends!")
    
    with st.expander("💡 Example Questions from Your Analysis Requirements"):
        st.markdown("""
        **Adobe vs Non-Adobe Analysis:**
        - How many postings ask for non-Adobe apps but not Adobe apps? What are those apps?
        - How many postings ask for both Adobe and non-Adobe apps? What are those combinations?
        - How many job listings request experience with Photoshop? And how many request Photoshop's competitors?
        
        **Creative Role Analysis:**
        - How many records describe a designer role?
        - Find all designer roles and summarize their creative job requirements
        - What are the top job titles among designer roles?
        
        **Cross-Disciplinary Requirements:**
        - Which jobs are not video jobs but still require video editing tools? What video tools are they?
        - Which jobs are not photo jobs but still require photo editing tools? What photo tools are they?
        - Which jobs are not design jobs but still require design editing tools? What design tools are they?
        
        **AI Tools and Modern Workflows:**
        - How many posts ask for AI skills? What are those AI tools? What are those occupations?
        - What industries are hiring more creative professionals? What kind of creative professionals?
        
        **Skills-Related Queries:**
        - What soft skills are mentioned most frequently in the postings for creative professionals?
        - What are the top technical skills required for designer roles?
        - Which jobs require both Photoshop and project management skills?
        - What creative tasks are most commonly mentioned in video professional roles?
        - Show me jobs that require collaboration and communication skills
        - What's the overlap between jobs requiring technical skills and soft skills?
        """)
    
    if st.session_state.last_response:
        st.info("💬 **Previous Response Available** - You can ask a follow-up question or start a new query")
        
        col1, col2 = st.columns([1, 1])
        with col1:
            if st.button("🔄 Ask Follow-up Question", type="secondary", use_container_width=True):
                st.session_state.query_mode = 'followup'
        with col2:
            if st.button("✨ Start New Query", type="secondary", use_container_width=True):
                st.session_state.query_mode = 'new'
                st.session_state.last_query = ""
                st.session_state.last_response = ""
    
    query_mode = st.session_state.get('query_mode', 'new')
    
    if query_mode == 'followup' and st.session_state.last_response:
        st.markdown("#### Follow-up Question")
        st.caption(f"Previous query: {st.session_state.last_query[:100]}...")
        question = st.text_area(
            "Ask a follow-up question about the previous response:",
            placeholder="e.g., Can you provide more details about those designer roles?",
            height=100,
            key="followup_query"
        )
    else:
        st.markdown("#### New Query")
        question = st.text_area(
            "Ask about creative jobs, software requirements, skills, or industry trends:",
            placeholder="e.g., How many designer roles require Adobe software vs non-Adobe alternatives?",
            height=100,
            key="new_query"
        )
    
    if st.button("🚀 Analyze with Enhanced RAG", type="primary", use_container_width=True) and question:
        if not openai_processor or not openai_processor.is_available():
            st.error("OpenAI processor not available. Please check your API key configuration.")
            st.stop()
        
        st.session_state.query_history.append(question)
        
        if query_mode == 'followup' and st.session_state.last_query:
            contextual_question = f"Previous question: {st.session_state.last_query}\n\nFollow-up: {question}"
        else:
            contextual_question = question
        
        with st.spinner("Performing intelligent analysis with skills tracking..."):
            try:
                response = openai_processor.query_with_enhanced_rag(
                    contextual_question,
                    processor.vector_store,
                    processor.dataset_analysis,
                    k_results=k_results
                )
                
                st.session_state.last_query = question
                st.session_state.last_response = response
                
                st.markdown("---")
                st.subheader("📊 Analysis Results")
                st.write(response)
                
                if show_retrieved:
                    with st.expander("📄 Retrieved Context"):
                        retrieved_docs = processor.vector_store.search(contextual_question, k=k_results)
                        if retrieved_docs:
                            st.info(f"Retrieved {len(retrieved_docs)} documents")
                            for i, doc in enumerate(retrieved_docs[:20], 1):
                                st.write(f"**Document {i}** (Score: {doc['score']:.3f})")
                                st.write(doc['text'][:300] + "..." if len(doc['text']) > 300 else doc['text'])
                                
                                metadata = doc.get('metadata', {})
                                if metadata:
                                    info_parts = []
                                    if metadata.get('company'):
                                        info_parts.append(f"Company: {metadata['company']}")
                                    
                                    adobe_apps = metadata.get('adobe_apps', [])
                                    if isinstance(adobe_apps, str):
                                        adobe_apps = [app.strip() for app in adobe_apps.split(',') if app.strip()]
                                    if adobe_apps:
                                        info_parts.append(f"Adobe: {', '.join(adobe_apps)}")
                                    
                                    tech_skills = metadata.get('technical_skills', [])
                                    if isinstance(tech_skills, str):
                                        tech_skills = [skill.strip() for skill in tech_skills.split(',') if skill.strip()]
                                    if tech_skills:
                                        info_parts.append(f"Tech Skills: {', '.join(tech_skills[:3])}")
                                    
                                    soft_skills = metadata.get('soft_skills', [])
                                    if isinstance(soft_skills, str):
                                        soft_skills = [skill.strip() for skill in soft_skills.split(',') if skill.strip()]
                                    if soft_skills:
                                        info_parts.append(f"Soft Skills: {', '.join(soft_skills[:3])}")
                                    
                                    if info_parts:
                                        st.caption(" | ".join(info_parts))
                                st.markdown("---")
                        else:
                            st.write("No relevant documents retrieved")
                
                if show_debug:
                    with st.expander("🔍 Debug Information"):
                        st.write("**Query Mode:**", query_mode)
                        st.write("**Original Question:**", question)
                        st.write("**Contextual Question:**", contextual_question)
                        st.write("**k_results parameter:**", k_results)
                        st.write("**Retrieved Documents:**", len(retrieved_docs) if retrieved_docs else 0)
                        st.write("**Vector Store Stats:**", stats)
                
                st.session_state.query_mode = 'new'
                
            except Exception as e:
                st.error(f"Query processing error: {str(e)}")
                if show_debug:
                    st.exception(e)
    
    st.markdown("---")
    st.subheader("⚡ Quick Analysis")
    
    quick_col1, quick_col2, quick_col3, quick_col4 = st.columns(4)
    
    with quick_col1:
        if st.button("Count Designer Roles", use_container_width=True):
            if processor.dataset_analysis:
                count = processor.dataset_analysis['role_analysis'].get('designer_count', 0)
                st.success(f"**{count}** designer roles found")
            else:
                st.info("Analyzing designer roles from index...")
    
    with quick_col2:
        if st.button("Adobe vs Non-Adobe", use_container_width=True):
            if processor.dataset_analysis:
                adobe_analysis = processor.dataset_analysis['adobe_analysis']
                st.success(f"Adobe only: **{adobe_analysis.get('adobe_only_count', 0)}** | Non-Adobe only: **{adobe_analysis.get('non_adobe_only_count', 0)}** | Both: **{adobe_analysis.get('both_apps_count', 0)}**")
            else:
                st.info("Analyzing software requirements from index...")
    
    with quick_col3:
        if st.button("AI Tools Count", use_container_width=True):
            if processor.dataset_analysis:
                count = processor.dataset_analysis['ai_tools_analysis'].get('ai_tools_count', 0)
                st.success(f"**{count}** jobs mention AI tools")
            else:
                st.info("Analyzing AI tool mentions from index...")
    
    with quick_col4:
        if st.button("Top Skills", use_container_width=True):
            if processor.dataset_analysis:
                skills_analysis = processor.dataset_analysis.get('skills_analysis', {})
                tech_count = skills_analysis.get('technical_skills_count', 0)
                soft_count = skills_analysis.get('soft_skills_count', 0)
                st.success(f"Tech: **{tech_count}** | Soft: **{soft_count}**")
            else:
                st.info("Analyzing skills from index...")
    
    if st.session_state.last_response and not question:
        st.markdown("---")
        st.markdown("### 📝 Last Response")
        with st.expander("View Last Response", expanded=False):
            st.write(f"**Query:** {st.session_state.last_query}")
            st.markdown("---")
            st.write(st.session_state.last_response)


def main():
    """Main Streamlit application with multi-view support"""
    
    setup_nltk_data()
    
    initialize_session_state()
    
    api_key = get_openai_api_key()
    
    if api_key and st.session_state.openai_processor is None:
        try:
            st.session_state.openai_processor = EnhancedOpenAIProcessor(api_key)
        except Exception as e:
            logger.error(f"Failed to initialize OpenAI processor: {e}")
    
    if st.session_state.current_view == 'master':
        show_master_view()
    elif st.session_state.current_view == 'admin':
        show_admin_view()
    elif st.session_state.current_view == 'client':
        show_client_view()


if __name__ == "__main__":
    main()