from flask import Flask, jsonify, request
from datetime import timedelta
from collections import defaultdict
from database import get_cached_connection, optimized_execute
from db_utils import fetch_as_dict
from utils import format_api_response, create_error_response, format_validation_error, format_not_found_error
import logging

logger = logging.getLogger(__name__)
app = Flask(__name__)


def search_obj(request):
    conn = None
    cursor = None
    try:
        # Get search parameters
        search_text = request.values.get('search_text')
        
        # Validate required parameters
        if not search_text:
            logger.warning("Missing search_text parameter in search request")
            return format_validation_error(
                {"search_text": "required"},
                "Missing required parameter"
            )
        
        logger.debug(f"Processing search request for text: {search_text}")
        
        # Get database connection
        conn = get_cached_connection()
        cursor = conn.cursor()

        # Prepare search pattern with wildcards
        search_pattern = f"%{search_text}%"

        # Execute the SQL query with parameterized inputs using optimized_execute
        optimized_execute(cursor, """
            SELECT * 
            FROM instructors 
            WHERE name LIKE %s
        """, (search_pattern,))

        # Fetch and process results as a dictionary
        mentors = fetch_as_dict(cursor)
        logger.debug(f"Found {len(mentors)} mentors matching search criteria")

        # Search for courses
        optimized_execute(cursor, """
            SELECT * 
            FROM courses 
            WHERE title LIKE %s 
            OR subtitle LIKE %s 
            OR description LIKE %s
        """, (search_pattern, search_pattern, search_pattern))

        courses = fetch_as_dict(cursor)
        logger.debug(f"Found {len(courses)} courses matching search criteria")

        # Fetch filter options (static for now, but can be expanded)
        filter_options = {
            "Categories": ["Design", "Business", "Marketing", "Development"],
            "Languages": ["English", "German", "French", "Spanish"],
            "Duration": ["30 Minutes", "60 Minutes", "90 Minutes"],
            "Tags": ["Beginner", "Intermediate", "Advanced"]
        }

        # Build the final response structure
        final_response = {
            "mentors": mentors,
            "courses": courses,
            "filter_options": filter_options
        }

        return jsonify(format_api_response(
            data=final_response,
            message=f"Found {len(courses)} courses and {len(mentors)} mentors"
        )), 200
        
    except Exception as e:
        logger.error(f"Error in search_obj: {str(e)}")
        return create_error_response(
            "Failed to process search request",
            status_code=500,
            error_code="SEARCH_ERROR",
            error_details=str(e) if logger.level == logging.DEBUG else None
        )
        
    finally:
        # Ensure proper cleanup of database resources
        if cursor:
            try:
                cursor.close()
            except Exception as e_cur:
                logger.error(f"Error closing cursor in search_obj: {str(e_cur)}")
                
        if conn and hasattr(conn, 'is_connected') and conn.is_connected():
            try:
                conn.close()
            except Exception as e_conn:
                logger.error(f"Error closing connection in search_obj: {str(e_conn)}")


@app.route('/mentor_details', methods=['GET'])
def mentor_details():
    conn = None
    cursor = None
    try:
        # Get mentor ID
        mentor_id = request.args.get('mentor_id')
        
        # Validate required parameters
        if not mentor_id:
            logger.warning("Missing mentor_id parameter in mentor_details request")
            return format_validation_error(
                {"mentor_id": "required"},
                "Missing required parameter"
            )
        
        logger.debug(f"Processing mentor details request for mentor_id: {mentor_id}")
        
        # Get database connection
        conn = get_cached_connection()
        cursor = conn.cursor()

        # Fetch mentor details using optimized_execute
        optimized_execute(cursor, """
            SELECT * FROM mentors
            WHERE id = %s
        """, (mentor_id,))
        mentor = fetch_as_dict(cursor)
        
        if not mentor:
            logger.warning(f"Mentor with ID {mentor_id} not found")
            return format_not_found_error("Mentor", mentor_id)

        # Fetch reviews for the mentor
        optimized_execute(cursor, """
            SELECT * FROM mentor_reviews
            WHERE mentor_id = %s
        """, (mentor_id,))
        reviews = fetch_as_dict(cursor)

        # Fetch courses taught by the mentor
        optimized_execute(cursor, """
            SELECT * FROM courses
            WHERE mentor_id = %s
        """, (mentor_id,))
        courses = fetch_as_dict(cursor)

        # Build the final response structure
        final_response = {
            "mentor": mentor,
            "reviews": reviews,
            "courses": courses
        }
        
        logger.debug(f"Successfully retrieved mentor details for mentor_id: {mentor_id}")
        return jsonify(format_api_response(
            data=final_response,
            message="Mentor details retrieved successfully"
        )), 200
        
    except Exception as e:
        logger.error(f"Error in mentor_details: {str(e)}")
        return create_error_response(
            "Failed to retrieve mentor details",
            status_code=500,
            error_code="MENTOR_DETAILS_ERROR",
            error_details=str(e) if logger.level == logging.DEBUG else None
        )
        
    finally:
        # Ensure proper cleanup of database resources
        if cursor:
            try:
                cursor.close()
            except Exception as e_cur:
                logger.error(f"Error closing cursor in mentor_details: {str(e_cur)}")
                
        if conn and hasattr(conn, 'is_connected') and conn.is_connected():
            try:
                conn.close()
            except Exception as e_conn:
                logger.error(f"Error closing connection in mentor_details: {str(e_conn)}")
