"""
AI Tools Module - Tool-based AI System
Provides tools for AI to search and retrieve data from the database
Implements cursor-style tool execution with markdown response format
"""

import json
import logging
import re
from typing import Dict, List, Any, Optional, Tuple
from database import get_cached_connection, optimized_execute

logger = logging.getLogger(__name__)


def fetch_as_dict(cursor) -> List[Dict]:
    """Fetch all results from cursor as list of dictionaries."""
    columns = [col[0] for col in cursor.description] if cursor.description else []
    rows = cursor.fetchall()
    return [dict(zip(columns, row)) for row in rows]


# ==================== TOOL DEFINITIONS ====================

AI_TOOLS = {
    # ===== SEARCH TOOLS =====
    'search_notes': {
        'description': 'Search notes and topics by keyword. Use when user asks to find notes, topics, or study materials.',
        'params': {
            'query': {'type': 'string', 'required': True, 'description': 'Search query'},
            'course_id': {'type': 'integer', 'required': False, 'description': 'Filter by course ID'},
            'limit': {'type': 'integer', 'required': False, 'default': 5, 'description': 'Max results'}
        }
    },
    'search_videos': {
        'description': 'Search course videos. Use when user asks for video content.',
        'params': {
            'query': {'type': 'string', 'required': True},
            'course_id': {'type': 'integer', 'required': False},
            'limit': {'type': 'integer', 'required': False, 'default': 5}
        }
    },
    'search_courses': {
        'description': 'Search available courses. Use when user asks about courses or wants to find a course.',
        'params': {
            'query': {'type': 'string', 'required': True},
            'limit': {'type': 'integer', 'required': False, 'default': 5}
        }
    },
    'search_questions': {
        'description': 'Search quiz questions. Use when user wants practice questions or quiz content.',
        'params': {
            'query': {'type': 'string', 'required': True},
            'topic_id': {'type': 'integer', 'required': False},
            'difficulty': {'type': 'string', 'required': False},
            'limit': {'type': 'integer', 'required': False, 'default': 5}
        }
    },
    'search_qa': {
        'description': 'Search Q&A questions (open-ended). Use for finding long-answer questions.',
        'params': {
            'query': {'type': 'string', 'required': True},
            'topic_id': {'type': 'integer', 'required': False},
            'limit': {'type': 'integer', 'required': False, 'default': 5}
        }
    },
    'search_blogs': {
        'description': 'Search blog posts and articles.',
        'params': {
            'query': {'type': 'string', 'required': True},
            'limit': {'type': 'integer', 'required': False, 'default': 5}
        }
    },
    'search_institutes': {
        'description': 'Search educational institutes.',
        'params': {
            'query': {'type': 'string', 'required': True},
            'limit': {'type': 'integer', 'required': False, 'default': 5}
        }
    },
    'search_instructors': {
        'description': 'Search instructors and teachers.',
        'params': {
            'query': {'type': 'string', 'required': True},
            'limit': {'type': 'integer', 'required': False, 'default': 5}
        }
    },
    
    # ===== DETAIL TOOLS =====
    'get_topic_details': {
        'description': 'Get detailed info about a specific topic including notes content.',
        'params': {
            'topic_id': {'type': 'integer', 'required': True}
        }
    },
    'get_course_details': {
        'description': 'Get detailed info about a course including topics and videos.',
        'params': {
            'course_id': {'type': 'integer', 'required': True}
        }
    },
    'get_video_details': {
        'description': 'Get video information.',
        'params': {
            'video_id': {'type': 'integer', 'required': True}
        }
    },
    
    # ===== USER DATA TOOLS =====
    'get_user_progress': {
        'description': 'Get learning progress for current user in a course or topic.',
        'params': {
            'course_id': {'type': 'integer', 'required': False},
            'topic_id': {'type': 'integer', 'required': False}
        }
    },
    'get_enrolled_courses': {
        'description': 'Get list of courses the user is enrolled in.',
        'params': {
            'limit': {'type': 'integer', 'required': False, 'default': 10}
        }
    },
    'get_recent_activity': {
        'description': 'Get user recent learning activity.',
        'params': {
            'limit': {'type': 'integer', 'required': False, 'default': 10}
        }
    },
    
    # ===== RECOMMENDATION TOOLS =====
    'get_recommendations': {
        'description': 'Get personalized content recommendations for the user.',
        'params': {
            'type': {'type': 'string', 'required': False, 'description': 'notes/videos/courses'},
            'limit': {'type': 'integer', 'required': False, 'default': 5}
        }
    },
    'get_related_content': {
        'description': 'Get content related to a specific topic.',
        'params': {
            'topic_id': {'type': 'integer', 'required': True},
            'type': {'type': 'string', 'required': False}
        }
    },
    
    # ===== QUIZ & PRACTICE TOOLS =====
    'get_practice_questions': {
        'description': 'Get practice questions for a topic.',
        'params': {
            'topic_id': {'type': 'integer', 'required': True},
            'difficulty': {'type': 'string', 'required': False},
            'count': {'type': 'integer', 'required': False, 'default': 5}
        }
    },
    
    # ===== LIVE SESSION TOOLS =====
    'get_live_classes': {
        'description': 'Get upcoming live classes.',
        'params': {
            'course_id': {'type': 'integer', 'required': False},
            'limit': {'type': 'integer', 'required': False, 'default': 5}
        }
    },
    'get_doubts': {
        'description': 'Get doubts/questions submitted by the user.',
        'params': {
            'status': {'type': 'string', 'required': False, 'description': 'pending/resolved/all'},
            'limit': {'type': 'integer', 'required': False, 'default': 10}
        }
    }
}


# ==================== TOOL SYSTEM PROMPT ====================

def get_tool_system_prompt() -> str:
    """Generate system prompt that teaches AI about available tools."""
    
    tool_docs = []
    for name, config in AI_TOOLS.items():
        params_str = ", ".join([
            f"{p}{'?' if not info.get('required', True) else ''}: {info['type']}"
            for p, info in config.get('params', {}).items()
        ])
        tool_docs.append(f"- `{name}({params_str})`: {config['description']}")
    
    return f"""
## 🔧 YOU HAVE ACCESS TO TOOLS - USE THEM!

When the user asks about content, courses, notes, videos, or anything that requires searching the database, YOU MUST use the tools provided below.

### Available Tools:

{chr(10).join(tool_docs)}

### ⚠️ CRITICAL: Tool Usage Format

YOU MUST use this EXACT XML format to call tools:

<tool name="tool_name">
{{"param1": "value1", "param2": "value2"}}
</tool>

### Examples:

User: "Find physics notes"
Your response MUST include:
<tool name="search_notes">
{{"query": "physics", "limit": 5}}
</tool>

User: "Show my courses"
Your response MUST include:
<tool name="get_enrolled_courses">
{{"limit": 10}}
</tool>

### Rules:
1. When user asks for content, ALWAYS use a search tool
2. Put the <tool> tag where results should appear
3. After tool execution, explain the results
4. For simple conversation, respond normally

DO NOT make up search results - USE THE TOOLS!
"""


# ==================== TOOL EXECUTORS ====================

def execute_tool(tool_name: str, params: Dict, user_id: int) -> Dict:
    """Execute a tool and return results."""
    
    executor_map = {
        'search_notes': _exec_search_notes,
        'search_videos': _exec_search_videos,
        'search_courses': _exec_search_courses,
        'search_questions': _exec_search_questions,
        'search_qa': _exec_search_qa,
        'search_blogs': _exec_search_blogs,
        'search_institutes': _exec_search_institutes,
        'search_instructors': _exec_search_instructors,
        'get_topic_details': _exec_get_topic_details,
        'get_course_details': _exec_get_course_details,
        'get_video_details': _exec_get_video_details,
        'get_user_progress': _exec_get_user_progress,
        'get_enrolled_courses': _exec_get_enrolled_courses,
        'get_recent_activity': _exec_get_recent_activity,
        'get_recommendations': _exec_get_recommendations,
        'get_related_content': _exec_get_related_content,
        'get_practice_questions': _exec_get_practice_questions,
        'get_live_classes': _exec_get_live_classes,
        'get_doubts': _exec_get_doubts,
    }
    
    executor = executor_map.get(tool_name)
    if not executor:
        return {'error': f'Unknown tool: {tool_name}', 'results': []}
    
    try:
        results = executor(params, user_id)
        return {'success': True, 'results': results}
    except Exception as e:
        logger.error(f"Tool execution error ({tool_name}): {e}")
        return {'error': str(e), 'results': []}


def parse_tool_calls(response_text: str) -> List[Dict]:
    """Parse tool calls from AI response."""
    
    pattern = r'<tool\s+name=["\']([^"\']+)["\']>\s*({[^}]+})\s*</tool>'
    matches = re.findall(pattern, response_text, re.DOTALL)
    
    tool_calls = []
    for name, params_json in matches:
        try:
            params = json.loads(params_json)
            tool_calls.append({
                'tool': name,
                'params': params,
                'raw': f'<tool name="{name}">{params_json}</tool>'
            })
        except json.JSONDecodeError as e:
            logger.warning(f"Failed to parse tool params: {e}")
            continue
    
    return tool_calls


def create_result_card(result: Dict, result_type: str) -> Dict:
    """Create a standardized result card with navigation info."""
    
    card = {
        'type': result_type,
        'id': result.get('id'),
        'title': result.get('title') or result.get('name') or result.get('question_text', 'Untitled'),
        'description': result.get('description') or result.get('explanation', ''),
        'thumbnail': result.get('thumbnail') or result.get('image_url') or result.get('course_thumbnail'),
        'full_path': result.get('full_path') or result.get('topic_path', ''),
        'metadata': {}
    }
    
    # Add type-specific metadata
    if result_type == 'note':
        card['metadata'] = {
            'course_id': result.get('course_id'),
            'course_title': result.get('course_title'),
            'difficulty': result.get('difficulty'),
            'has_notes': result.get('has_notes', 1)
        }
        card['navigation'] = {
            'flutter': {'screen': 'TopicNotesScreen', 'args': {'courseId': str(result.get('course_id')), 'topicId': str(result.get('id'))}},
            'nextjs': {'path': f"/dashboard/notes/{result.get('course_id')}/{result.get('id')}"}
        }
    
    elif result_type == 'video':
        card['metadata'] = {
            'course_id': result.get('course_id'),
            'course_title': result.get('course_title'),
            'duration': result.get('duration'),
            'video_url': result.get('video_url')
        }
        card['navigation'] = {
            'flutter': {'screen': 'EnhancedVideoPlayerScreen', 'args': {
                'courseId': str(result.get('course_id')),
                'videoId': str(result.get('id')),
                'videoUrl': result.get('video_url', ''),
                'title': result.get('title', '')
            }},
            'nextjs': {'path': f"/dashboard/courses/{result.get('course_id')}/video/{result.get('id')}"}
        }
    
    elif result_type == 'course':
        card['metadata'] = {
            'instructor': result.get('instructor_name'),
            'price': result.get('price'),
            'rating': result.get('rating'),
            'students': result.get('total_students')
        }
        card['navigation'] = {
            'flutter': {'screen': 'CourseDetailScreen', 'args': {'courseId': str(result.get('id'))}},
            'nextjs': {'path': f"/dashboard/courses/{result.get('id')}"}
        }
    
    elif result_type == 'question':
        card['metadata'] = {
            'course_id': result.get('course_id'),
            'topic_id': result.get('topic_id'),
            'topic_title': result.get('topic_title'),
            'difficulty': result.get('difficulty'),
            'marks': result.get('marks')
        }
        card['navigation'] = {
            'flutter': {'screen': 'QuizQuestionDetailScreen', 'args': {'questionId': str(result.get('id'))}},
            'nextjs': {'path': f"/dashboard/quizzes/{result.get('id')}"}
        }
    
    elif result_type == 'qa_question':
        card['metadata'] = {
            'course_id': result.get('course_id'),
            'topic_id': result.get('topic_id'),
            'topic_title': result.get('topic_title'),
            'expected_answer': result.get('expected_answer', '')[:200]
        }
        card['navigation'] = {
            'flutter': {'screen': 'QAQuestionDetailScreen', 'args': {'questionId': str(result.get('id')), 'courseId': str(result.get('course_id'))}},
            'nextjs': {'path': f"/dashboard/qa-library/{result.get('course_id')}/{result.get('id')}"}
        }
    
    elif result_type == 'blog':
        card['navigation'] = {
            'flutter': {'screen': 'BlogDetailScreen', 'args': {'blogId': str(result.get('id'))}},
            'nextjs': {'path': f"/blog/{result.get('slug') or result.get('id')}"}
        }
    
    elif result_type == 'institute':
        card['metadata'] = {'address': result.get('address')}
        card['navigation'] = {
            'flutter': {'screen': 'InstituteDetailScreen', 'args': {'instituteId': str(result.get('id'))}},
            'nextjs': {'path': f"/institutes/{result.get('id')}"}
        }
    
    elif result_type == 'instructor':
        card['metadata'] = {'expertise': result.get('expertise')}
        card['navigation'] = {
            'flutter': {'screen': 'InstructorProfileScreen', 'args': {'instructorId': str(result.get('id'))}},
            'nextjs': {'path': f"/instructors/{result.get('id')}"}
        }
    
    return card


# ==================== SEARCH EXECUTORS ====================

def _exec_search_notes(params: Dict, user_id: int) -> List[Dict]:
    """Search notes/topics."""
    query = params.get('query', '')
    course_id = params.get('course_id')
    limit = params.get('limit', 5)
    
    pattern = f"%{query}%"
    
    conn = get_cached_connection()
    cursor = conn.cursor()
    
    sql = """
        SELECT DISTINCT
            t.id, t.title, 
            COALESCE(t.description, SUBSTRING(cnc.content, 1, 200)) as description,
            t.level as difficulty,
            t.full_path,
            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 cnc.content LIKE %s)
    """
    params_list = [user_id, pattern, pattern, pattern]
    
    if course_id:
        sql += " AND c.id = %s"
        params_list.append(course_id)
    
    sql += " ORDER BY CASE WHEN t.title LIKE %s THEN 0 ELSE 1 END, t.id DESC LIMIT %s"
    params_list.extend([pattern, limit])
    
    optimized_execute(cursor, sql, tuple(params_list))
    results = fetch_as_dict(cursor)
    
    return [create_result_card(r, 'note') for r in results]


def _exec_search_videos(params: Dict, user_id: int) -> List[Dict]:
    """Search videos."""
    query = params.get('query', '')
    course_id = params.get('course_id')
    limit = params.get('limit', 5)
    
    pattern = f"%{query}%"
    
    conn = get_cached_connection()
    cursor = conn.cursor()
    
    sql = """
        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,
               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
    """
    params_list = [user_id, pattern, pattern]
    
    if course_id:
        sql += " AND c.id = %s"
        params_list.append(course_id)
    
    sql += " ORDER BY v.created_at DESC LIMIT %s"
    params_list.append(limit)
    
    optimized_execute(cursor, sql, tuple(params_list))
    results = fetch_as_dict(cursor)
    
    # Convert timedelta duration
    from datetime import timedelta
    for r in results:
        if r.get('duration') and isinstance(r['duration'], timedelta):
            r['duration'] = int(r['duration'].total_seconds())
    
    return [create_result_card(r, 'video') for r in results]


def _exec_search_courses(params: Dict, user_id: int) -> List[Dict]:
    """Search courses."""
    query = params.get('query', '')
    limit = params.get('limit', 5)
    
    pattern = f"%{query}%"
    
    conn = get_cached_connection()
    cursor = conn.cursor()
    
    sql = """
        SELECT c.id, c.title, c.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.instructor_id = i.id
        WHERE c.title LIKE %s OR c.description LIKE %s
        ORDER BY c.rating DESC, c.enrollments_count DESC
        LIMIT %s
    """
    
    optimized_execute(cursor, sql, (pattern, pattern, limit))
    results = fetch_as_dict(cursor)
    
    return [create_result_card(r, 'course') for r in results]


def _exec_search_questions(params: Dict, user_id: int) -> List[Dict]:
    """Search quiz questions."""
    query = params.get('query', '')
    topic_id = params.get('topic_id')
    difficulty = params.get('difficulty')
    limit = params.get('limit', 5)
    
    pattern = f"%{query}%"
    
    conn = get_cached_connection()
    cursor = conn.cursor()
    
    sql = """
        SELECT q.id, q.question_text as title, q.explanation as description,
               q.difficulty, q.marks,
               t.id as topic_id, t.title as topic_title, t.full_path,
               c.id as course_id, c.title as course_title
        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
    """
    params_list = [user_id, pattern]
    
    if topic_id:
        sql += " AND t.id = %s"
        params_list.append(topic_id)
    
    if difficulty:
        sql += " AND q.difficulty = %s"
        params_list.append(difficulty)
    
    sql += " ORDER BY q.id DESC LIMIT %s"
    params_list.append(limit)
    
    optimized_execute(cursor, sql, tuple(params_list))
    results = fetch_as_dict(cursor)
    
    return [create_result_card(r, 'question') for r in results]


def _exec_search_qa(params: Dict, user_id: int) -> List[Dict]:
    """Search Q&A questions."""
    query = params.get('query', '')
    topic_id = params.get('topic_id')
    limit = params.get('limit', 5)
    
    pattern = f"%{query}%"
    
    conn = get_cached_connection()
    cursor = conn.cursor()
    
    sql = """
        SELECT q.id, q.question_text as title, q.expected_answer as description,
               q.difficulty, q.marks, q.explanation,
               t.id as topic_id, t.title as topic_title, t.full_path,
               c.id as course_id, c.title as course_title
        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
    """
    params_list = [user_id, pattern, pattern]
    
    if topic_id:
        sql += " AND t.id = %s"
        params_list.append(topic_id)
    
    sql += " ORDER BY q.id DESC LIMIT %s"
    params_list.append(limit)
    
    optimized_execute(cursor, sql, tuple(params_list))
    results = fetch_as_dict(cursor)
    
    return [create_result_card(r, 'qa_question') for r in results]


def _exec_search_blogs(params: Dict, user_id: int) -> List[Dict]:
    """Search blogs."""
    query = params.get('query', '')
    limit = params.get('limit', 5)
    
    pattern = f"%{query}%"
    
    conn = get_cached_connection()
    cursor = conn.cursor()
    
    sql = """
        SELECT id, title, description, featured_image as thumbnail, slug
        FROM blogs
        WHERE title LIKE %s OR description LIKE %s
        ORDER BY created_at DESC
        LIMIT %s
    """
    
    optimized_execute(cursor, sql, (pattern, pattern, limit))
    results = fetch_as_dict(cursor)
    
    return [create_result_card(r, 'blog') for r in results]


def _exec_search_institutes(params: Dict, user_id: int) -> List[Dict]:
    """Search institutes."""
    query = params.get('query', '')
    limit = params.get('limit', 5)
    
    pattern = f"%{query}%"
    
    conn = get_cached_connection()
    cursor = conn.cursor()
    
    sql = """
        SELECT id, name as title, description, logo_url as thumbnail, address
        FROM institutes
        WHERE name LIKE %s OR description LIKE %s
        ORDER BY id DESC
        LIMIT %s
    """
    
    optimized_execute(cursor, sql, (pattern, pattern, limit))
    results = fetch_as_dict(cursor)
    
    return [create_result_card(r, 'institute') for r in results]


def _exec_search_instructors(params: Dict, user_id: int) -> List[Dict]:
    """Search instructors."""
    query = params.get('query', '')
    limit = params.get('limit', 5)
    
    pattern = f"%{query}%"
    
    conn = get_cached_connection()
    cursor = conn.cursor()
    
    sql = """
        SELECT id, name as title, bio as description, profile_image as thumbnail, expertise
        FROM instructors
        WHERE name LIKE %s OR bio LIKE %s OR expertise LIKE %s
        ORDER BY id DESC
        LIMIT %s
    """
    
    optimized_execute(cursor, sql, (pattern, pattern, pattern, limit))
    results = fetch_as_dict(cursor)
    
    return [create_result_card(r, 'instructor') for r in results]


# ==================== DETAIL EXECUTORS ====================

def _exec_get_topic_details(params: Dict, user_id: int) -> List[Dict]:
    """Get topic details."""
    topic_id = params.get('topic_id')
    
    conn = get_cached_connection()
    cursor = conn.cursor()
    
    sql = """
        SELECT t.id, t.title, t.description, t.level as difficulty, t.full_path,
               c.id as course_id, c.title as course_title, c.image_url as course_thumbnail,
               cnc.content as notes_content
        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.id = %s
    """
    
    optimized_execute(cursor, sql, (topic_id,))
    results = fetch_as_dict(cursor)
    
    if results:
        result = results[0]
        result['has_notes'] = 1 if result.get('notes_content') else 0
        # Truncate notes content for summary
        if result.get('notes_content'):
            result['description'] = result['notes_content'][:500] + '...'
        return [create_result_card(result, 'note')]
    
    return []


def _exec_get_course_details(params: Dict, user_id: int) -> List[Dict]:
    """Get course details."""
    course_id = params.get('course_id')
    
    conn = get_cached_connection()
    cursor = conn.cursor()
    
    sql = """
        SELECT c.id, c.title, c.description, c.image_url as thumbnail,
               c.price, c.rating, c.enrollments_count as total_students,
               i.name as instructor_name,
               (SELECT COUNT(*) FROM topics WHERE course_id = c.id) as topic_count,
               (SELECT COUNT(*) FROM videos WHERE course_id = c.id) as video_count
        FROM courses c
        LEFT JOIN instructors i ON c.instructor_id = i.id
        WHERE c.id = %s
    """
    
    optimized_execute(cursor, sql, (course_id,))
    results = fetch_as_dict(cursor)
    
    return [create_result_card(r, 'course') for r in results]


def _exec_get_video_details(params: Dict, user_id: int) -> List[Dict]:
    """Get video details."""
    video_id = params.get('video_id')
    
    conn = get_cached_connection()
    cursor = conn.cursor()
    
    sql = """
        SELECT v.id, v.title, v.description, v.video_duration as duration,
               v.image_url as thumbnail, v.video_url,
               c.id as course_id, c.title as course_title,
               CONCAT(c.title, ' > ', v.title) as full_path
        FROM videos v
        JOIN courses c ON v.course_id = c.id
        WHERE v.id = %s
    """
    
    optimized_execute(cursor, sql, (video_id,))
    results = fetch_as_dict(cursor)
    
    from datetime import timedelta
    for r in results:
        if r.get('duration') and isinstance(r['duration'], timedelta):
            r['duration'] = int(r['duration'].total_seconds())
    
    return [create_result_card(r, 'video') for r in results]


# ==================== USER DATA EXECUTORS ====================

def _exec_get_user_progress(params: Dict, user_id: int) -> List[Dict]:
    """Get user progress."""
    course_id = params.get('course_id')
    topic_id = params.get('topic_id')
    
    conn = get_cached_connection()
    cursor = conn.cursor()
    
    results = []
    
    if course_id:
        sql = """
            SELECT c.id, c.title, c.image_url as thumbnail,
                   e.progress_percentage,
                   (SELECT COUNT(*) FROM topics WHERE course_id = c.id) as total_topics,
                   (SELECT COUNT(*) FROM user_notes_progress unp 
                    JOIN topics t ON unp.topic_id = t.id 
                    WHERE t.course_id = c.id AND unp.user_id = %s AND unp.notes_completed = 1) as completed_topics
            FROM courses c
            JOIN enrollments e ON c.id = e.course_id AND e.payment_status IN ('free', 'paid')
            WHERE c.id = %s AND e.user_id = %s
        """
        optimized_execute(cursor, sql, (user_id, course_id, user_id))
        results = fetch_as_dict(cursor)
        
        for r in results:
            r['description'] = f"Progress: {r.get('progress_percentage', 0)}% | {r.get('completed_topics', 0)}/{r.get('total_topics', 0)} topics"
            r['metadata'] = {'progress': r.get('progress_percentage', 0)}
    
    return [create_result_card(r, 'course') for r in results] if results else [{'type': 'info', 'message': 'No progress data found'}]


def _exec_get_enrolled_courses(params: Dict, user_id: int) -> List[Dict]:
    """Get enrolled courses."""
    limit = params.get('limit', 10)
    
    conn = get_cached_connection()
    cursor = conn.cursor()
    
    sql = """
        SELECT c.id, c.title, c.description, c.image_url as thumbnail,
               e.progress_percentage, e.enrolled_at,
               i.name as instructor_name
        FROM enrollments e
        JOIN courses c ON e.course_id = c.id
        LEFT JOIN instructors i ON c.instructor_id = i.id
        WHERE e.user_id = %s
        ORDER BY e.enrolled_at DESC
        LIMIT %s
    """
    
    optimized_execute(cursor, sql, (user_id, limit))
    results = fetch_as_dict(cursor)
    
    for r in results:
        r['metadata'] = {'progress': r.get('progress_percentage', 0)}
    
    return [create_result_card(r, 'course') for r in results]


def _exec_get_recent_activity(params: Dict, user_id: int) -> List[Dict]:
    """Get recent activity."""
    limit = params.get('limit', 10)
    
    # This would need a proper activity log table
    # For now, return enrolled courses as placeholder
    return _exec_get_enrolled_courses({'limit': limit}, user_id)


def _exec_get_recommendations(params: Dict, user_id: int) -> List[Dict]:
    """Get recommendations."""
    content_type = params.get('type', 'all')
    limit = params.get('limit', 5)
    
    conn = get_cached_connection()
    cursor = conn.cursor()
    
    # Get courses user is NOT enrolled in, ordered by popularity
    sql = """
        SELECT c.id, c.title, c.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.instructor_id = i.id
        WHERE c.id NOT IN (SELECT course_id FROM enrollments WHERE user_id = %s)
        ORDER BY c.rating DESC, c.enrollments_count DESC
        LIMIT %s
    """
    
    optimized_execute(cursor, sql, (user_id, limit))
    results = fetch_as_dict(cursor)
    
    return [create_result_card(r, 'course') for r in results]


def _exec_get_related_content(params: Dict, user_id: int) -> List[Dict]:
    """Get related content."""
    topic_id = params.get('topic_id')
    content_type = params.get('type')
    
    conn = get_cached_connection()
    cursor = conn.cursor()
    
    # Get course of the topic
    sql = "SELECT course_id FROM topics WHERE id = %s"
    optimized_execute(cursor, sql, (topic_id,))
    result = fetch_as_dict(cursor)
    
    if not result:
        return []
    
    course_id = result[0]['course_id']
    
    # Get other topics in same course
    sql = """
        SELECT t.id, t.title, t.description, t.level as difficulty, t.full_path,
               c.id as course_id, c.title as course_title, c.image_url as course_thumbnail
        FROM topics t
        JOIN courses c ON t.course_id = c.id
        WHERE t.course_id = %s AND t.id != %s
        LIMIT 5
    """
    
    optimized_execute(cursor, sql, (course_id, topic_id))
    results = fetch_as_dict(cursor)
    
    return [create_result_card(r, 'note') for r in results]


def _exec_get_practice_questions(params: Dict, user_id: int) -> List[Dict]:
    """Get practice questions."""
    topic_id = params.get('topic_id')
    difficulty = params.get('difficulty')
    count = params.get('count', 5)
    
    conn = get_cached_connection()
    cursor = conn.cursor()
    
    sql = """
        SELECT q.id, q.question_text as title, q.explanation as description,
               q.difficulty, q.marks,
               t.id as topic_id, t.title as topic_title, t.full_path,
               c.id as course_id, c.title as course_title
        FROM questions q
        JOIN topics t ON q.topic_id = t.id
        JOIN courses c ON t.course_id = c.id
        WHERE t.id = %s
    """
    params_list = [topic_id]
    
    if difficulty:
        sql += " AND q.difficulty = %s"
        params_list.append(difficulty)
    
    sql += " ORDER BY RAND() LIMIT %s"
    params_list.append(count)
    
    optimized_execute(cursor, sql, tuple(params_list))
    results = fetch_as_dict(cursor)
    
    return [create_result_card(r, 'question') for r in results]


def _exec_get_live_classes(params: Dict, user_id: int) -> List[Dict]:
    """Get live classes."""
    course_id = params.get('course_id')
    limit = params.get('limit', 5)
    
    conn = get_cached_connection()
    cursor = conn.cursor()
    
    sql = """
        SELECT lc.id, lc.title, lc.description, lc.scheduled_at,
               c.id as course_id, c.title as course_title, c.image_url as thumbnail
        FROM live_classes lc
        JOIN courses c ON lc.course_id = c.id
        JOIN enrollments e ON c.id = e.course_id AND e.user_id = %s
        WHERE lc.scheduled_at > NOW()
    """
    params_list = [user_id]
    
    if course_id:
        sql += " AND c.id = %s"
        params_list.append(course_id)
    
    sql += " ORDER BY lc.scheduled_at ASC LIMIT %s"
    params_list.append(limit)
    
    try:
        optimized_execute(cursor, sql, tuple(params_list))
        results = fetch_as_dict(cursor)
        
        for r in results:
            r['full_path'] = r.get('course_title', '')
        
        return [create_result_card(r, 'note') for r in results]  # Use note type for now
    except:
        return []


def _exec_get_doubts(params: Dict, user_id: int) -> List[Dict]:
    """Get user doubts."""
    status = params.get('status', 'all')
    limit = params.get('limit', 10)
    
    conn = get_cached_connection()
    cursor = conn.cursor()
    
    sql = """
        SELECT d.id, d.question as title, d.answer as description, d.status,
               t.id as topic_id, t.title as topic_title,
               c.id as course_id, c.title as course_title
        FROM doubts d
        LEFT JOIN topics t ON d.topic_id = t.id
        LEFT JOIN courses c ON t.course_id = c.id
        WHERE d.user_id = %s
    """
    params_list = [user_id]
    
    if status != 'all':
        sql += " AND d.status = %s"
        params_list.append(status)
    
    sql += " ORDER BY d.created_at DESC LIMIT %s"
    params_list.append(limit)
    
    try:
        optimized_execute(cursor, sql, tuple(params_list))
        results = fetch_as_dict(cursor)
        
        for r in results:
            r['full_path'] = f"{r.get('course_title', '')} > {r.get('topic_title', '')}"
        
        return [create_result_card(r, 'note') for r in results]
    except:
        return []
