"""
Advanced System-Wide Search API
Unified search across all models with AI vision integration
"""

from flask import Blueprint, request, jsonify, g
import logging
import base64
import io
from datetime import datetime

from database import get_cached_connection, optimized_execute
from db_utils import fetch_as_dict
from utils import format_api_response, create_error_response
from auth.jwt_manager import require_auth

logger = logging.getLogger(__name__)

search_bp = Blueprint('search', __name__, url_prefix='/api/search')


# =============================================================================
# UNIFIED SEARCH
# =============================================================================

@search_bp.route('', methods=['POST'])
@require_auth
def unified_search():
    """
    Unified system-wide search across all models.
    
    Request:
    {
        "query": "calculus",
        "content_types": ["courses", "notes", "quizzes", "videos", "blogs", "institutes"],
        "page": 1,
        "per_page": 20,
        "filters": {
            "difficulty": "intermediate",
            "institute_id": null
        }
    }
    """
    try:
        user_id = g.current_user_id  # Get user_id from Flask g object
        data = request.get_json() or {}
        query = data.get('query', '').strip()
        
        if not query:
            return create_error_response("Search query is required", status_code=400)
        
        content_types = data.get('content_types', [
            'courses', 'topics', 'questions', 'qa_questions', 
            'videos', 'blogs', 'institutes', 'instructors'
        ])
        page = data.get('page', 1)
        per_page = min(data.get('per_page', 20), 50)  # Max 50 results per page
        filters = data.get('filters', {})
        
        offset = (page - 1) * per_page
        
        conn = get_cached_connection()
        cursor = conn.cursor()
        
        all_results = []
        facets = {}
        
        search_pattern = f"%{query}%"
        
        # Search each content type - pass user_id to filter by enrolled courses
        if 'courses' in content_types:
            courses, count = _search_courses(cursor, search_pattern, per_page, offset, filters, user_id)
            all_results.extend([{**c, 'type': 'course', 'relevance_score': _calculate_relevance(query, c.get('title', ''))} for c in courses])
            facets['courses'] = count
        
        if 'videos' in content_types:
            videos, count = _search_videos(cursor, search_pattern, per_page, offset, filters, user_id)
            all_results.extend([{**v, 'type': 'video', 'relevance_score': _calculate_relevance(query, v.get('title', ''))} for v in videos])
            facets['videos'] = count
        
        if 'notes' in content_types or 'topics' in content_types:
            notes, count = _search_notes(cursor, search_pattern, per_page, offset, filters, user_id)
            # Return as 'note' type for proper navigation (handles both topics and notes content)
            all_results.extend([{**n, 'type': 'note', 'relevance_score': _calculate_relevance(query, n.get('title', ''))} for n in notes])
            facets['notes'] = count
        
        if 'quizzes' in content_types:
            quizzes, count = _search_quizzes(cursor, search_pattern, per_page, offset, filters, user_id)
            all_results.extend([{**q, 'type': 'quiz', 'relevance_score': _calculate_relevance(query, q.get('title', ''))} for q in quizzes])
            facets['quizzes'] = count
        
        if 'blogs' in content_types:
            blogs, count = _search_blogs(cursor, search_pattern, per_page, offset, filters)
            all_results.extend([{**b, 'type': 'blog', 'relevance_score': _calculate_relevance(query, b.get('title', ''))} for b in blogs])
            facets['blogs'] = count
        
        if 'institutes' in content_types:
            institutes, count = _search_institutes(cursor, search_pattern, per_page, offset, filters)
            all_results.extend([{**i, 'type': 'institute', 'relevance_score': _calculate_relevance(query, i.get('name', ''))} for i in institutes])
            facets['institutes'] = count
        
        if 'instructors' in content_types:
            instructors, count = _search_instructors(cursor, search_pattern, per_page, offset, filters)
            all_results.extend([{**i, 'type': 'instructor', 'relevance_score': _calculate_relevance(query, i.get('name', ''))} for i in instructors])
            facets['instructors'] = count
        
        if 'questions' in content_types:
            questions, count = _search_questions(cursor, search_pattern, per_page, offset, filters, user_id)
            all_results.extend([{**q, 'type': 'question', 'relevance_score': _calculate_relevance(query, q.get('question_text', ''))} for q in questions])
            facets['questions'] = count
        
        if 'qa_questions' in content_types:
            qa_questions, count = _search_qa_questions(cursor, search_pattern, per_page, offset, filters, user_id)
            all_results.extend([{**q, 'type': 'qa_question', 'relevance_score': _calculate_relevance(query, q.get('question_text', ''))} for q in qa_questions])
            facets['qa_questions'] = count
        
        # Sort by relevance score
        all_results.sort(key=lambda x: x.get('relevance_score', 0), reverse=True)
        
        # Paginate combined results
        paginated_results = all_results[:per_page]
        
        # Log search for analytics
        _log_search(cursor, conn, user_id, query, 'text', len(all_results))
        
        cursor.close()
        
        return jsonify(format_api_response(
            data={
                'results': paginated_results,
                'facets': facets,
                'total': sum(facets.values()),
                'page': page,
                'per_page': per_page,
                'query': query
            },
            message=f"Found {sum(facets.values())} results"
        )), 200
        
    except Exception as e:
        logger.error(f"Error in unified_search: {e}")
        return create_error_response("Search failed", status_code=500)


# =============================================================================
# IMAGE/VISION SEARCH
# =============================================================================

@search_bp.route('/image', methods=['POST'])
@require_auth
def image_search():
    """
    Process image for text extraction and search using AI.
    
    Request:
    {
        "image_base64": "...",
        "prompt": "Extract all text from this image",
        "search_extracted": true
    }
    """
    try:
        user_id = g.current_user_id  # Get user_id from Flask g object
        data = request.get_json() or {}
        image_base64 = data.get('image_base64')
        prompt = data.get('prompt', 'Extract all readable text from this image. Return only the extracted text, no explanations.')
        search_extracted = data.get('search_extracted', True)
        
        if not image_base64:
            return create_error_response("Image is required", status_code=400)
        
        # Process image with AI
        extracted_text, description = _process_image_with_ai(image_base64, prompt)
        
        results = []
        facets = {}
        
        # If text was extracted and user wants to search
        if extracted_text and search_extracted:
            conn = get_cached_connection()
            cursor = conn.cursor()
            
            search_pattern = f"%{extracted_text[:100]}%"  # Use first 100 chars for search
            
            # Search notes (most relevant for educational content)
            notes, count = _search_notes(cursor, search_pattern, 10, 0, {})
            results.extend([{**n, 'type': 'note', 'relevance_score': 0.9} for n in notes])
            facets['notes'] = count
            
            # Search courses
            courses, count = _search_courses(cursor, search_pattern, 5, 0, {})
            results.extend([{**c, 'type': 'course', 'relevance_score': 0.8} for c in courses])
            facets['courses'] = count
            
            # Log search
            _log_search(cursor, conn, user_id, extracted_text[:100], 'image', len(results))
            
            cursor.close()
        
        return jsonify(format_api_response(
            data={
                'extracted_text': extracted_text,
                'description': description,
                'results': results,
                'facets': facets,
                'suggestions': _generate_search_suggestions(extracted_text)
            },
            message="Image processed successfully"
        )), 200
        
    except Exception as e:
        logger.error(f"Error in image_search: {e}")
        return create_error_response("Image search failed", status_code=500)


@search_bp.route('/suggestions', methods=['GET'])
@require_auth
def get_suggestions():
    """Get search suggestions based on popular searches and user history."""
    try:
        user_id = g.current_user_id  # Get user_id from Flask g object
        query = request.args.get('q', '').strip()
        
        conn = get_cached_connection()
        cursor = conn.cursor()
        
        suggestions = []
        
        # Get user's recent searches
        optimized_execute(cursor, """
            SELECT DISTINCT query 
            FROM search_history 
            WHERE user_id = %s 
            ORDER BY created_at DESC 
            LIMIT 5
        """, (user_id,))
        recent = fetch_as_dict(cursor)
        suggestions.extend([r['query'] for r in recent])
        
        # Get popular searches if query provided
        if query:
            optimized_execute(cursor, """
                SELECT term, frequency 
                FROM search_suggestions 
                WHERE term LIKE %s 
                ORDER BY frequency DESC 
                LIMIT 10
            """, (f"%{query}%",))
            popular = fetch_as_dict(cursor)
            suggestions.extend([p['term'] for p in popular])
        
        cursor.close()
        
        # Remove duplicates while preserving order
        seen = set()
        unique_suggestions = []
        for s in suggestions:
            if s not in seen:
                seen.add(s)
                unique_suggestions.append(s)
        
        return jsonify(format_api_response(
            data={'suggestions': unique_suggestions[:10]},
            message="Suggestions retrieved"
        )), 200
        
    except Exception as e:
        logger.error(f"Error in get_suggestions: {e}")
        return create_error_response("Failed to get suggestions", status_code=500)


@search_bp.route('/history', methods=['GET'])
@require_auth
def get_search_history():
    """Get user's search history."""
    try:
        user_id = g.current_user_id  # Get user_id from Flask g object
        conn = get_cached_connection()
        cursor = conn.cursor()
        
        optimized_execute(cursor, """
            SELECT id, query, search_type, results_count, created_at
            FROM search_history 
            WHERE user_id = %s 
            ORDER BY created_at DESC 
            LIMIT 20
        """, (user_id,))
        history = fetch_as_dict(cursor)
        
        cursor.close()
        
        return jsonify(format_api_response(
            data={'history': history},
            message="Search history retrieved"
        )), 200
        
    except Exception as e:
        logger.error(f"Error in get_search_history: {e}")
        return create_error_response("Failed to get history", status_code=500)


# =============================================================================
# HELPER FUNCTIONS
# =============================================================================

def _search_courses(cursor, pattern, limit, offset, filters, user_id=None):
    """Search courses table - only returns user's enrolled courses."""
    try:
        if user_id:
            # Only search courses the user is enrolled in
            optimized_execute(cursor, """
                SELECT c.id, c.title, c.subtitle as description, c.image_url as thumbnail, 
                       c.price, c.rating, c.enrollments_count as total_students,
                       i.name as instructor_name
                FROM courses c
                LEFT JOIN instructors i ON c.primary_instructor_id = i.id
                JOIN enrollments e ON c.id = e.course_id AND e.user_id = %s AND e.payment_status IN ('free', 'paid')
                WHERE c.status = 'active' AND (c.title LIKE %s OR c.subtitle LIKE %s OR c.description LIKE %s)
                ORDER BY c.rating DESC, c.enrollments_count DESC
                LIMIT %s OFFSET %s
            """, (user_id, pattern, pattern, pattern, limit, offset))
            results = fetch_as_dict(cursor)
            
            # Get total count
            optimized_execute(cursor, """
                SELECT COUNT(*) as count FROM courses c
                JOIN enrollments e ON c.id = e.course_id AND e.user_id = %s AND e.payment_status IN ('free', 'paid')
                WHERE c.status = 'active' AND (c.title LIKE %s OR c.subtitle LIKE %s OR c.description LIKE %s)
            """, (user_id, pattern, pattern, pattern))
        else:
            # Fallback: search all active courses
            optimized_execute(cursor, """
                SELECT c.id, c.title, c.subtitle as description, c.image_url as thumbnail, 
                       c.price, c.rating, c.enrollments_count as total_students,
                       i.name as instructor_name
                FROM courses c
                LEFT JOIN instructors i ON c.primary_instructor_id = i.id
                WHERE c.status = 'active' AND (c.title LIKE %s OR c.subtitle LIKE %s OR c.description LIKE %s)
                ORDER BY c.rating DESC, c.enrollments_count DESC
                LIMIT %s OFFSET %s
            """, (pattern, pattern, pattern, limit, offset))
            results = fetch_as_dict(cursor)
            
            # Get total count
            optimized_execute(cursor, """
                SELECT COUNT(*) as count FROM courses 
                WHERE status = 'active' AND (title LIKE %s OR subtitle LIKE %s OR description LIKE %s)
            """, (pattern, pattern, pattern))
        
        count_result = fetch_as_dict(cursor)
        count = count_result[0]['count'] if count_result else 0
        
        return results, count
    except Exception as e:
        logger.error(f"Error searching courses: {e}")
        return [], 0


def _search_videos(cursor, pattern, limit, offset, filters, user_id=None):
    """Search course videos - only from enrolled courses."""
    try:
        if user_id:
            optimized_execute(cursor, """
                SELECT v.id, v.title, v.description, v.video_duration as duration, v.image_url as thumbnail,
                       v.video_url, c.title as course_title, c.id as course_id, c.image_url as course_thumbnail,
                       CONCAT(c.title, ' > ', v.title) as full_path
                FROM videos v
                JOIN courses c ON v.course_id = c.id
                JOIN enrollments e ON c.id = e.course_id AND e.user_id = %s AND e.payment_status IN ('free', 'paid')
                WHERE v.title LIKE %s OR v.description LIKE %s
                ORDER BY v.created_at DESC
                LIMIT %s OFFSET %s
            """, (user_id, pattern, pattern, limit, offset))
        else:
            optimized_execute(cursor, """
                SELECT v.id, v.title, v.description, v.video_duration as duration, v.image_url as thumbnail,
                       v.video_url, c.title as course_title, c.id as course_id, c.image_url as course_thumbnail,
                       CONCAT(c.title, ' > ', v.title) as full_path
                FROM videos v
                JOIN courses c ON v.course_id = c.id
                WHERE v.title LIKE %s OR v.description LIKE %s
                ORDER BY v.created_at DESC
                LIMIT %s OFFSET %s
            """, (pattern, pattern, limit, offset))
        
        results = fetch_as_dict(cursor)
        
        # Convert timedelta to seconds for JSON serialization
        from datetime import timedelta
        for r in results:
            if r.get('duration') and isinstance(r['duration'], timedelta):
                r['duration'] = int(r['duration'].total_seconds())
        
        if user_id:
            optimized_execute(cursor, """
                SELECT COUNT(*) as count FROM videos v
                JOIN courses c ON v.course_id = c.id
                JOIN enrollments e ON c.id = e.course_id AND e.user_id = %s AND e.payment_status IN ('free', 'paid')
                WHERE v.title LIKE %s OR v.description LIKE %s
            """, (user_id, pattern, pattern))
        else:
            optimized_execute(cursor, """
                SELECT COUNT(*) as count FROM videos 
                WHERE title LIKE %s OR description LIKE %s
            """, (pattern, pattern))
        
        count_result = fetch_as_dict(cursor)
        count = count_result[0]['count'] if count_result else 0
        
        return results, count
    except Exception as e:
        logger.error(f"Error searching videos: {e}")
        return [], 0


def _search_notes(cursor, pattern, limit, offset, filters, user_id=None):
    """Search notes/topics - includes topic titles, descriptions, full_path, and notes content. Only from enrolled courses."""
    try:
        if user_id:
            # Search topics and notes content for enrolled courses
            optimized_execute(cursor, """
                SELECT DISTINCT
                    t.id, t.title, 
                    COALESCE(t.description, SUBSTRING(cnc.content, 1, 200)) as description, 
                    t.level as difficulty,
                    t.full_path as topic_path,
                    t.parent_topic_id,
                    c.title as course_title, c.id as course_id, c.image_url as course_thumbnail,
                    CASE WHEN cnc.id IS NOT NULL THEN 1 ELSE 0 END as has_notes
                FROM topics t
                JOIN courses c ON t.course_id = c.id
                JOIN enrollments e ON c.id = e.course_id AND e.user_id = %s AND e.payment_status IN ('free', 'paid')
                LEFT JOIN course_notes_content cnc ON t.id = cnc.topic_id
                WHERE t.title LIKE %s 
                   OR t.description LIKE %s 
                   OR t.full_path LIKE %s
                   OR cnc.content LIKE %s
                ORDER BY 
                    CASE WHEN t.title LIKE %s THEN 0 ELSE 1 END,
                    t.id DESC
                LIMIT %s OFFSET %s
            """, (user_id, pattern, pattern, pattern, pattern, pattern, limit, offset))
            results = fetch_as_dict(cursor)
            
            optimized_execute(cursor, """
                SELECT COUNT(DISTINCT t.id) as count FROM topics t
                JOIN courses c ON t.course_id = c.id
                JOIN enrollments e ON c.id = e.course_id AND e.user_id = %s AND e.payment_status IN ('free', 'paid')
                LEFT JOIN course_notes_content cnc ON t.id = cnc.topic_id
                WHERE t.title LIKE %s 
                   OR t.description LIKE %s 
                   OR t.full_path LIKE %s
                   OR cnc.content LIKE %s
            """, (user_id, pattern, pattern, pattern, pattern))
        else:
            # Fallback: search all topics
            optimized_execute(cursor, """
                SELECT DISTINCT
                    t.id, t.title, 
                    COALESCE(t.description, SUBSTRING(cnc.content, 1, 200)) as description, 
                    t.level as difficulty,
                    t.full_path as topic_path,
                    t.parent_topic_id,
                    c.title as course_title, c.id as course_id, c.image_url as course_thumbnail,
                    CASE WHEN cnc.id IS NOT NULL THEN 1 ELSE 0 END as has_notes
                FROM topics t
                JOIN courses c ON t.course_id = c.id
                LEFT JOIN course_notes_content cnc ON t.id = cnc.topic_id
                WHERE t.title LIKE %s 
                   OR t.description LIKE %s 
                   OR t.full_path LIKE %s
                   OR cnc.content LIKE %s
                ORDER BY 
                    CASE WHEN t.title LIKE %s THEN 0 ELSE 1 END,
                    t.id DESC
                LIMIT %s OFFSET %s
            """, (pattern, pattern, pattern, pattern, pattern, limit, offset))
            results = fetch_as_dict(cursor)
            
            optimized_execute(cursor, """
                SELECT COUNT(DISTINCT t.id) as count FROM topics t
                JOIN courses c ON t.course_id = c.id
                LEFT JOIN course_notes_content cnc ON t.id = cnc.topic_id
                WHERE t.title LIKE %s 
                   OR t.description LIKE %s 
                   OR t.full_path LIKE %s
                   OR cnc.content LIKE %s
            """, (pattern, pattern, pattern, pattern))
        
        count_result = fetch_as_dict(cursor)
        count = count_result[0]['count'] if count_result else 0
        
        return results, count
    except Exception as e:
        logger.error(f"Error searching notes: {e}")
        return [], 0


def _search_quizzes(cursor, pattern, limit, offset, filters, user_id=None):
    """Search quizzes - using questions table as proxy, only from enrolled courses."""
    try:
        if user_id:
            # Since quizzes table doesn't exist, search questions grouped by topic
            optimized_execute(cursor, """
                SELECT t.id, t.title, t.title as description, 'general' as difficulty,
                       COUNT(q.id) as total_questions,
                       c.title as course_title, c.id as course_id
                FROM topics t
                JOIN questions q ON t.id = q.topic_id
                JOIN courses c ON t.course_id = c.id
                JOIN enrollments e ON c.id = e.course_id AND e.user_id = %s AND e.payment_status IN ('free', 'paid')
                WHERE t.title LIKE %s
                GROUP BY t.id
                HAVING COUNT(q.id) > 0
                ORDER BY COUNT(q.id) DESC
                LIMIT %s OFFSET %s
            """, (user_id, pattern, limit, offset))
            results = fetch_as_dict(cursor)
            
            optimized_execute(cursor, """
                SELECT COUNT(DISTINCT t.id) as count 
                FROM topics t
                JOIN questions q ON t.id = q.topic_id
                JOIN courses c ON t.course_id = c.id
                JOIN enrollments e ON c.id = e.course_id AND e.user_id = %s AND e.payment_status IN ('free', 'paid')
                WHERE t.title LIKE %s
            """, (user_id, pattern))
        else:
            optimized_execute(cursor, """
                SELECT t.id, t.title, t.title as description, 'general' as difficulty,
                       COUNT(q.id) as total_questions,
                       c.title as course_title
                FROM topics t
                JOIN questions q ON t.id = q.topic_id
                JOIN courses c ON t.course_id = c.id
                WHERE t.title LIKE %s
                GROUP BY t.id
                HAVING COUNT(q.id) > 0
                ORDER BY COUNT(q.id) DESC
                LIMIT %s OFFSET %s
            """, (pattern, limit, offset))
            results = fetch_as_dict(cursor)
            
            optimized_execute(cursor, """
                SELECT COUNT(DISTINCT t.id) as count 
                FROM topics t
                JOIN questions q ON t.id = q.topic_id
                WHERE t.title LIKE %s
            """, (pattern,))
        
        count_result = fetch_as_dict(cursor)
        count = count_result[0]['count'] if count_result else 0
        
        return results, count
    except Exception as e:
        logger.error(f"Error searching quizzes: {e}")
        return [], 0


def _search_blogs(cursor, pattern, limit, offset, filters):
    """Search blog posts - skipped if table doesn't exist."""
    # Blog/pages table doesn't exist in this database, return empty results
    return [], 0


def _search_institutes(cursor, pattern, limit, offset, filters):
    """Search institutes."""
    try:
        optimized_execute(cursor, """
            SELECT i.id, i.name, i.description, i.logo_url as thumbnail,
                   i.address, COALESCE(i.average_rating, 0) as rating
            FROM institutes i
            WHERE i.status = 'active' AND (i.name LIKE %s OR i.description LIKE %s OR i.address LIKE %s)
            ORDER BY i.average_rating DESC
            LIMIT %s OFFSET %s
        """, (pattern, pattern, pattern, limit, offset))
        results = fetch_as_dict(cursor)
        
        optimized_execute(cursor, """
            SELECT COUNT(*) as count FROM institutes 
            WHERE status = 'active' AND (name LIKE %s OR description LIKE %s OR address LIKE %s)
        """, (pattern, pattern, pattern))
        count_result = fetch_as_dict(cursor)
        count = count_result[0]['count'] if count_result else 0
        
        return results, count
    except Exception as e:
        logger.error(f"Error searching institutes: {e}")
        return [], 0


def _search_instructors(cursor, pattern, limit, offset, filters):
    """Search instructors."""
    try:
        optimized_execute(cursor, """
            SELECT i.id, i.name, i.bio as description, u.profile_img_url as thumbnail,
                   i.specializations
            FROM instructors i
            LEFT JOIN user u ON i.user_id = u.id
            WHERE i.status = 'active' AND (i.name LIKE %s OR i.bio LIKE %s)
            ORDER BY i.created_at DESC
            LIMIT %s OFFSET %s
        """, (pattern, pattern, limit, offset))
        results = fetch_as_dict(cursor)
        
        optimized_execute(cursor, """
            SELECT COUNT(*) as count FROM instructors 
            WHERE status = 'active' AND (name LIKE %s OR bio LIKE %s)
        """, (pattern, pattern))
        count_result = fetch_as_dict(cursor)
        count = count_result[0]['count'] if count_result else 0
        
        return results, count
    except Exception as e:
        logger.error(f"Error searching instructors: {e}")
        return [], 0


def _search_questions(cursor, pattern, limit, offset, filters, user_id=None):
    """Search quiz questions with full context - only from enrolled courses."""
    try:
        if user_id:
            optimized_execute(cursor, """
                SELECT q.id, q.question_text, q.difficulty, q.marks, q.explanation,
                       q.is_ai_generated,
                       t.id as topic_id, t.title as topic_title, t.full_path as topic_path,
                       c.id as course_id, c.title as course_title, c.image_url as course_thumbnail
                FROM questions q
                JOIN topics t ON q.topic_id = t.id
                JOIN courses c ON t.course_id = c.id
                JOIN enrollments e ON c.id = e.course_id AND e.user_id = %s AND e.payment_status IN ('free', 'paid')
                WHERE q.question_text LIKE %s OR q.explanation LIKE %s
                ORDER BY q.id DESC
                LIMIT %s OFFSET %s
            """, (user_id, pattern, pattern, limit, offset))
            results = fetch_as_dict(cursor)
            
            optimized_execute(cursor, """
                SELECT COUNT(*) as count FROM questions q
                JOIN topics t ON q.topic_id = t.id
                JOIN courses c ON t.course_id = c.id
                JOIN enrollments e ON c.id = e.course_id AND e.user_id = %s AND e.payment_status IN ('free', 'paid')
                WHERE q.question_text LIKE %s OR q.explanation LIKE %s
            """, (user_id, pattern, pattern))
        else:
            optimized_execute(cursor, """
                SELECT q.id, q.question_text, q.difficulty, q.marks, q.explanation,
                       q.is_ai_generated,
                       t.id as topic_id, t.title as topic_title, t.full_path as topic_path,
                       c.id as course_id, c.title as course_title, c.image_url as course_thumbnail
                FROM questions q
                JOIN topics t ON q.topic_id = t.id
                JOIN courses c ON t.course_id = c.id
                WHERE q.question_text LIKE %s OR q.explanation LIKE %s
                ORDER BY q.id DESC
                LIMIT %s OFFSET %s
            """, (pattern, pattern, limit, offset))
            results = fetch_as_dict(cursor)
            
            optimized_execute(cursor, """
                SELECT COUNT(*) as count FROM questions q
                JOIN topics t ON q.topic_id = t.id
                WHERE q.question_text LIKE %s OR q.explanation LIKE %s
            """, (pattern, pattern))
        
        count_result = fetch_as_dict(cursor)
        count = count_result[0]['count'] if count_result else 0
        
        return results, count
    except Exception as e:
        logger.error(f"Error searching questions: {e}")
        return [], 0


def _search_qa_questions(cursor, pattern, limit, offset, filters, user_id=None):
    """Search open-ended QA questions with full context - only from enrolled courses."""
    try:
        if user_id:
            optimized_execute(cursor, """
                SELECT q.id, q.question_text, q.expected_answer, q.answer_keywords,
                       q.difficulty, q.marks, q.explanation, q.is_ai_generated,
                       t.id as topic_id, t.title as topic_title, t.full_path as topic_path,
                       c.id as course_id, c.title as course_title, c.image_url as course_thumbnail
                FROM qa_questions q
                JOIN topics t ON q.topic_id = t.id
                JOIN courses c ON t.course_id = c.id
                JOIN enrollments e ON c.id = e.course_id AND e.user_id = %s AND e.payment_status IN ('free', 'paid')
                WHERE q.question_text LIKE %s OR q.expected_answer LIKE %s OR q.explanation LIKE %s
                ORDER BY q.id DESC
                LIMIT %s OFFSET %s
            """, (user_id, pattern, pattern, pattern, limit, offset))
            results = fetch_as_dict(cursor)
            
            optimized_execute(cursor, """
                SELECT COUNT(*) as count FROM qa_questions q
                JOIN topics t ON q.topic_id = t.id
                JOIN courses c ON t.course_id = c.id
                JOIN enrollments e ON c.id = e.course_id AND e.user_id = %s AND e.payment_status IN ('free', 'paid')
                WHERE q.question_text LIKE %s OR q.expected_answer LIKE %s OR q.explanation LIKE %s
            """, (user_id, pattern, pattern, pattern))
        else:
            optimized_execute(cursor, """
                SELECT q.id, q.question_text, q.expected_answer, q.answer_keywords,
                       q.difficulty, q.marks, q.explanation, q.is_ai_generated,
                       t.id as topic_id, t.title as topic_title, t.full_path as topic_path,
                       c.id as course_id, c.title as course_title, c.image_url as course_thumbnail
                FROM qa_questions q
                JOIN topics t ON q.topic_id = t.id
                JOIN courses c ON t.course_id = c.id
                WHERE q.question_text LIKE %s OR q.expected_answer LIKE %s OR q.explanation LIKE %s
                ORDER BY q.id DESC
                LIMIT %s OFFSET %s
            """, (pattern, pattern, pattern, limit, offset))
            results = fetch_as_dict(cursor)
            
            optimized_execute(cursor, """
                SELECT COUNT(*) as count FROM qa_questions q
                JOIN topics t ON q.topic_id = t.id
                WHERE q.question_text LIKE %s OR q.expected_answer LIKE %s OR q.explanation LIKE %s
            """, (pattern, pattern, pattern))
        
        count_result = fetch_as_dict(cursor)
        count = count_result[0]['count'] if count_result else 0
        
        return results, count
    except Exception as e:
        logger.error(f"Error searching QA questions: {e}")
        return [], 0


def _calculate_relevance(query: str, text: str) -> float:
    """Calculate simple relevance score based on text matching."""
    if not text:
        return 0.0
    
    query_lower = query.lower()
    text_lower = text.lower()
    
    # Exact match gets highest score
    if query_lower == text_lower:
        return 1.0
    
    # Title starts with query
    if text_lower.startswith(query_lower):
        return 0.9
    
    # Query words in text
    query_words = query_lower.split()
    text_words = text_lower.split()
    
    matches = sum(1 for word in query_words if word in text_words)
    if query_words:
        return 0.5 + (0.4 * matches / len(query_words))
    
    # Partial match
    if query_lower in text_lower:
        return 0.6
    
    return 0.3


def _process_image_with_ai(image_base64: str, prompt: str) -> tuple:
    """Process image using AI provider for OCR/text extraction."""
    try:
        from services.ai_provider import ai_generate_vision
        
        # Use AI provider for text extraction
        result = ai_generate_vision(
            messages=[{"role": "user", "content": prompt}],
            images=[image_base64]
        )
        
        extracted_text = result.content if result else ""
        
        # Also get description/understanding of the image
        desc_result = ai_generate_vision(
            messages=[{"role": "user", "content": "Briefly describe what is shown in this image in one sentence."}],
            images=[image_base64]
        )
        
        description = desc_result.content if desc_result else ""
        
        return extracted_text, description
        
    except Exception as e:
        logger.error(f"Error processing image with AI: {e}")
        return "", ""


def _generate_search_suggestions(text: str) -> list:
    """Generate search suggestions based on extracted text."""
    if not text:
        return []
    
    # Extract key phrases (simple approach)
    words = text.split()
    
    # Get 2-3 word phrases
    suggestions = []
    for i in range(len(words) - 1):
        phrase = ' '.join(words[i:i+2])
        if len(phrase) > 5:
            suggestions.append(phrase)
    
    return suggestions[:5]


def _log_search(cursor, conn, user_id, query, search_type, results_count):
    """Log search for analytics."""
    try:
        optimized_execute(cursor, """
            INSERT INTO search_history (user_id, query, search_type, results_count, created_at)
            VALUES (%s, %s, %s, %s, %s)
        """, (user_id, query[:500], search_type, results_count, datetime.now()))
        conn.commit()
        
        # Update search suggestions frequency
        optimized_execute(cursor, """
            INSERT INTO search_suggestions (term, frequency, category)
            VALUES (%s, 1, 'general')
            ON DUPLICATE KEY UPDATE frequency = frequency + 1
        """, (query[:255],))
        conn.commit()
    except Exception as e:
        logger.warning(f"Failed to log search: {e}")
