"""
Blog related routes and data functions
"""
import json
import logging
from datetime import datetime, date
from flask import Blueprint, request, jsonify, make_response
from utils import (require_api_key, encrypt_response, format_api_response, 
                   create_error_response, add_keep_alive)


logger = logging.getLogger(__name__)
blog_bp = Blueprint('blog', __name__)


def get_all_blogs_data(limit=None, institute_id=None):
    """Extract blog posts data for reuse in various endpoints.
    
    Args:
        limit: Optional limit on number of posts to return
        institute_id: Optional institute ID for multi-tenant filtering
    """
    try:
        # Create database connection
        from database import get_cached_connection, optimized_execute
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        # Query to fetch all blog posts with author information, categories, and tags
        query = """
        SELECT 
            p.*, 
            a.name as author_name, 
            a.avatar as author_avatar, 
            a.bio as author_bio,
            a.twitter as author_twitter,
            a.linkedin as author_linkedin,
            bc.id as category_id,
            bc.name as category_name,
            bc.slug as category_slug,
            COUNT(c.id) as comments
        FROM blog_posts p
        LEFT JOIN blog_authors a ON p.author_id = a.id
        LEFT JOIN blog_comments c ON p.id = c.post_id
        LEFT JOIN blog_categories bc ON p.category_id = bc.id
        WHERE 1=1
        """
        
        params = []
        
        # Add institute filter if provided
        if institute_id:
            query += " AND (p.institute_id = %s OR p.institute_id IS NULL)"
            params.append(institute_id)
        
        query += """
        GROUP BY p.id, bc.id, bc.name, bc.slug
        ORDER BY p.created_at DESC
        """
        
        # Add limit if specified
        if limit:
            query += " LIMIT {}".format(limit)
            
        # Execute the optimized query
        if params:
            optimized_execute(cursor, query, tuple(params))
        else:
            optimized_execute(cursor, query)
        blogs = cursor.fetchall()
        
        if not blogs:
            return []
            
        # Collect all blog IDs
        blog_ids = [blog['id'] for blog in blogs]
        
        # Fetch all tags for all blogs in a single query
        tags_by_blog_id = {}
        if blog_ids:
            format_strings = ','.join(['%s'] * len(blog_ids))
            tag_query = f"""
            SELECT pt.post_id, t.name 
            FROM blog_tags t
            JOIN blog_post_tags pt ON t.id = pt.tag_id
            WHERE pt.post_id IN ({format_strings})
            """
            optimized_execute(cursor, tag_query, tuple(blog_ids))
            all_tags_data = cursor.fetchall()
            
            # Organize tags by blog_id
            for tag_data in all_tags_data:
                post_id = tag_data['post_id']
                if post_id not in tags_by_blog_id:
                    tags_by_blog_id[post_id] = []
                tags_by_blog_id[post_id].append(tag_data['name'])
        
        # Process each blog: format dates, build author/category objects, and attach tags
        for blog in blogs:
            for key, value in blog.items():
                if isinstance(value, (datetime, date)):
                    blog[key] = value.isoformat()

            # Convert readTime to the expected format (if it exists)
            if 'read_time' in blog and blog['read_time'] is not None:
                blog['readTime'] = blog.pop('read_time')
            elif 'readTime' in blog and blog['readTime'] is not None:
                pass

            # Build the author object safely
            author_name = blog.pop("author_name", "Anonymous")
            author_avatar = blog.pop("author_avatar", "/images/mentors/default.jpg")
            author_bio = blog.pop("author_bio", "")
            author_twitter = blog.pop("author_twitter", "https://twitter.com")
            author_linkedin = blog.pop("author_linkedin", "https://linkedin.com")

            blog["author"] = {
                "name": author_name,
                "avatar": author_avatar,
                "bio": author_bio,
                "social": {
                    "twitter": author_twitter,
                    "linkedin": author_linkedin
                }
            }
            
            # Process category information
            category_id = blog.pop("category_id", None)
            category_name = blog.pop("category_name", None) 
            category_slug = blog.pop("category_slug", None)
            
            # Include category information in a consistent format
            if category_id and category_name:
                blog["category"] = {
                    "id": category_id,
                    "name": category_name,
                    "slug": category_slug or category_name.lower().replace(' ', '-')
                }
            else:
                # Fallback for uncategorized posts
                blog["category"] = {
                    "id": 0,
                    "name": "Uncategorized",
                    "slug": "uncategorized"
                }
            
            # Attach tags from pre-fetched dictionary
            blog["tags"] = tags_by_blog_id.get(blog["id"], [])
        
        return blogs
    except Exception as e:
        logger.error("Error in get_all_blogs_data: {}".format(str(e)))
        raise
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception as e_cur:
                logger.error("Error closing cursor in get_all_blogs_data: {}".format(e_cur))
        if 'conn' in locals() and conn and conn.is_connected():
            try:
                conn.close()
            except Exception as e_conn:
                logger.error("Error closing connection in get_all_blogs_data: {}".format(e_conn))


def get_blog_by_id_data(blog_id):
    """Extract blog post data by ID for reuse in various endpoints."""
    from database import optimized_execute
    conn = None
    cursor = None
    try:
        from database import get_cached_connection
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        query = """
        SELECT 
            p.*, 
            a.name AS author_name, 
            a.avatar AS author_avatar, 
            a.bio AS author_bio,
            a.twitter AS author_twitter,
            a.linkedin AS author_linkedin,
            bc.slug AS category_slug,
            bc.name AS category_name,
            COUNT(c.id) AS comments_count
        FROM blog_posts p
        LEFT JOIN blog_authors a ON p.author_id = a.id
        LEFT JOIN blog_comments c ON p.id = c.post_id
        LEFT JOIN blog_categories bc ON p.category_id = bc.id
        WHERE p.id = %s
        GROUP BY p.id, a.name, a.avatar, a.bio, a.twitter, a.linkedin, bc.slug, bc.name 
        """
        
        # Execute the optimized query
        optimized_execute(cursor, query, (blog_id,))
        blog = cursor.fetchone()
        
        if not blog:
            return None
            
        # Format all date/datetime fields to ISO 8601 string
        for key, value in list(blog.items()):
            if isinstance(value, (datetime, date)):
                blog[key] = value.isoformat()
            
        if 'read_time' in blog and blog['read_time'] is not None:
            blog['readTime'] = blog.pop('read_time')
        elif 'readTime' in blog and blog['readTime'] is not None:
            pass
            
        blog["author"] = {
            "name": blog.pop("author_name", "Anonymous"),
            "avatar": blog.pop("author_avatar", "/images/mentors/default.jpg"),
            "bio": blog.pop("author_bio", ""),
            "social": {
                "twitter": blog.pop("author_twitter", "https://twitter.com"),
                "linkedin": blog.pop("author_linkedin", "https://linkedin.com")
            }
        }
        blog["category"] = {
             "slug": blog.pop("category_slug", None),
             "name": blog.pop("category_name", None)
        }
        blog["comments_count"] = blog.pop("comments_count", 0)

        tag_query = """
        SELECT t.name FROM blog_tags t
        JOIN blog_post_tags pt ON t.id = pt.tag_id
        WHERE pt.post_id = %s
        """
        # Execute the optimized tag query
        optimized_execute(cursor, tag_query, (blog_id,))
        tag_rows = cursor.fetchall()
        
        if not isinstance(tag_rows, list):
            logger.error("cursor.fetchall() for tags (id: {}) did not return a list. Got: {}. Value: {}".format(blog_id, type(tag_rows), tag_rows))
            tags = []
        else:
            tags = [row["name"] for row in tag_rows if isinstance(row, dict) and "name" in row]
        blog["tags"] = tags
        
        return blog
    except Exception as e:
        logger.error("Error in get_blog_by_id_data (blog_id: {}): {}".format(blog_id, str(e)), exc_info=True)
        raise
    finally:
        if cursor:
            try:
                cursor.close()
            except Exception as e_cur:
                logger.error("Error closing cursor for blog_id {}: {}".format(blog_id, e_cur))
        if conn and conn.is_connected():
            try:
                conn.close()
            except Exception as e_conn:
                logger.error("Error closing connection for blog_id {}: {}".format(blog_id, e_conn))


def get_categories_data():
    """Get all blog categories data for reuse in various endpoints."""
    try:
        from database import get_cached_connection
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        # Query to fetch all categories
        query = "SELECT id, name FROM blog_categories ORDER BY name"
        cursor.execute(query)
        categories = cursor.fetchall()
        
        cursor.close()
        conn.close()
        return categories
    except Exception as e:
        logger.error("Error in get_categories_data: {}".format(str(e)))
        raise


def get_trending_posts_data(limit=None):
    """Get trending blog posts data for reuse in various endpoints."""
    try:
        # Set default limit if not provided
        if limit is None:
            limit = 3
            
        from database import get_cached_connection
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        # Query to get trending posts sorted by views
        query = """
        SELECT 
            p.*, 
            a.name as author_name, 
            a.avatar as author_avatar, 
            a.bio as author_bio,
            a.twitter as author_twitter,
            a.linkedin as author_linkedin,
            COUNT(c.id) as comments
        FROM blog_posts p
        LEFT JOIN blog_authors a ON p.author_id = a.id
        LEFT JOIN blog_comments c ON p.id = c.post_id
        GROUP BY p.id
        ORDER BY p.views DESC
        LIMIT %s
        """
        cursor.execute(query, (limit,))
        trending_posts = cursor.fetchall()
        
        # For each post, fetch its tags and format the data
        for post in trending_posts:
            # Format the date properly
            if isinstance(post.get('date'), datetime):
                post['date'] = post['date'].strftime('%B %d, %Y')
                
            # Convert readTime to the expected format
            if post.get('read_time'):
                post['readTime'] = post.pop('read_time')
                
            # Build the author object
            author = {
                "name": post.pop("author_name"),
                "avatar": post.pop("author_avatar", "/images/mentors/default.jpg"),
                "bio": post.pop("author_bio", ""),
                "social": {
                    "twitter": post.pop("author_twitter", "https://twitter.com"),
                    "linkedin": post.pop("author_linkedin", "https://linkedin.com")
                }
            }
            post["author"] = author
            
            # Fetch tags for this post
            tag_query = """
            SELECT t.name FROM blog_tags t
            JOIN blog_post_tags pt ON t.id = pt.tag_id
            WHERE pt.post_id = %s
            """
            cursor.execute(tag_query, (post["id"],))
            tags = [row["name"] for row in cursor.fetchall()]
            post["tags"] = tags
        
        cursor.close()
        conn.close()
        return trending_posts
    except Exception as e:
        logger.error("Error in get_trending_posts_data: {}".format(str(e)))
        raise


def get_popular_tags_data(limit=None):
    """Get popular tags data for reuse in various endpoints."""
    try:
        # Set default limit if not provided
        if limit is None:
            limit = 10
            
        from database import get_cached_connection
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        # Query to get tags with counts of associated posts, ordered by count
        query = """
        SELECT t.name, COUNT(pt.post_id) as count
        FROM blog_tags t
        JOIN blog_post_tags pt ON t.id = pt.tag_id
        GROUP BY t.id
        ORDER BY count DESC
        LIMIT %s
        """
        cursor.execute(query, (limit,))
        popular_tags = cursor.fetchall()
        
        cursor.close()
        conn.close()
        return popular_tags
    except Exception as e:
        logger.error("Error in get_popular_tags_data: {}".format(str(e)))
        raise


@blog_bp.route('/api/blogs/all', methods=['GET'])
@require_api_key
@encrypt_response
def get_all_blogs_route():
    # This route maps to the main blogs endpoint
    # This is needed because the frontend expects /api/blogs/all
    return get_all_blogs()


def get_all_blogs():
    """Get all blog posts from database with caching and ETags"""
    try:
        # Log the request
        limit = request.args.get('limit', type=int)
        
        # Get institute_id from header for multi-tenant filtering
        institute_id = request.headers.get('X-Institute-ID')
        if institute_id:
            try:
                institute_id = int(institute_id)
            except ValueError:
                institute_id = None
        
        logger.info(f"GET /api/blogs/all - limit: {limit}, institute_id: {institute_id}, IP: {request.remote_addr}")
        
        # Fetch the data with institute filtering
        blogs = get_all_blogs_data(limit=limit, institute_id=institute_id)
        
        # Log successful response
        logger.info(f"GET /api/blogs/all - Success: {len(blogs)} blogs returned")
        
        # Return raw data - let the @encrypt_response decorator handle Response creation
        return format_api_response(blogs)
    except Exception as e:
        logger.error("Error fetching blog posts: {}".format(str(e)))
        logger.error(f"GET /api/blogs/all - Error: {str(e)}, IP: {request.remote_addr}")
        return create_error_response(str(e), status_code=500, encrypt=True)


@blog_bp.route('/api/blogs/<string:blog_id>', methods=['GET'])
@require_api_key
# @encrypt_response
def get_blog_by_id(blog_id):
    # Try to convert ID to integer if possible
    numeric_id = None
    try:
        numeric_id = int(blog_id)
        # If conversion succeeds, use the numeric ID
        blog_id = numeric_id
    except ValueError:
        # Keep using string ID if conversion fails
        pass
    """Get a specific blog post by ID from database with caching and ETags"""
    try:
        # Log the request
        logger.info(f"GET /api/blogs/{blog_id} - IP: {request.remote_addr}")
        
        # Fetch the data (cache miss or caching disabled)
        blog = get_blog_by_id_data(blog_id)
        
        if not blog:
            logger.warning(f"GET /api/blogs/{blog_id} - Blog not found, IP: {request.remote_addr}")
            return create_error_response("Blog with ID {} not found".format(blog_id), status_code=404)
        
        # Log successful response
        logger.info(f"GET /api/blogs/{blog_id} - Success: Blog '{blog.get('title', 'N/A')}' returned")
        
        # Return raw data - let the @encrypt_response decorator handle Response creation
        return  format_api_response(blog)
    except Exception as e:
        logger.error("Error fetching blog post {}: {}".format(blog_id, str(e)))
        logger.error(f"GET /api/blogs/{blog_id} - Error: {str(e)}, IP: {request.remote_addr}")
        return create_error_response(str(e), status_code=500)


@blog_bp.route('/api/blogs/category/<string:category_slug>', methods=['GET'])
@require_api_key
@encrypt_response
def get_blogs_by_category(category_slug):
    """Get blog posts by category from database"""
    try:
        # Log the request
        logger.info(f"GET /api/blogs/category/{category_slug} - IP: {request.remote_addr}")
        
        # Get all blog posts using the centralized function
        all_blogs = get_all_blogs_data()
        
        # Filter by category
        if category_slug == "all":
            # Return all blogs without filtering
            blogs = all_blogs
        else:
            # Filter blogs by the requested category slug
            blogs = []
            for blog in all_blogs:
                # Check various ways the category might be stored
                category_matches = False
                
                # Case 1: blog.category is an object with slug property
                if isinstance(blog.get('category'), dict) and blog['category'].get('slug') == category_slug:
                    category_matches = True
                # Case 2: blog.category is a string matching the slug
                elif isinstance(blog.get('category'), str) and blog['category'] == category_slug:
                    category_matches = True
                # Case 3: blog has category_id that matches a category with this slug
                elif blog.get('category_id') is not None:
                    # Create database connection
                    from database import get_cached_connection
                    conn = get_cached_connection()
                    cursor = conn.cursor(dictionary=True)
                    
                    # Check if this category_id belongs to the requested slug
                    cursor.execute("SELECT id FROM blog_categories WHERE slug = %s", (category_slug,))
                    category = cursor.fetchone()
                    cursor.close()
                    conn.close()
                    
                    if category and category['id'] == blog['category_id']:
                        category_matches = True
                
                if category_matches:
                    blogs.append(blog)
        
        # Log successful response
        logger.info(f"GET /api/blogs/category/{category_slug} - Success: {len(blogs)} blogs returned")
        
        # Return raw data - let the @encrypt_response decorator handle Response creation
        return format_api_response(blogs)  
    
    except Exception as e:
        logger.error("Error fetching blog posts by category {}: {}".format(category_slug, str(e)))
        logger.error(f"GET /api/blogs/category/{category_slug} - Error: {str(e)}, IP: {request.remote_addr}")
        return create_error_response(str(e), status_code=500, encrypt=True)


@blog_bp.route('/api/blogs/search', methods=['GET'])
@require_api_key
@encrypt_response
def search_blogs():
    """Search blog posts based on query parameter from database"""
    try:
        # Get search parameters
        search_term = request.args.get('q', '').lower()
        category = request.args.get('category', 'all')
        
        # Log the request
        logger.info(f"GET /api/blogs/search - query: '{search_term}', category: '{category}', IP: {request.remote_addr}")
        
        # Create database connection
        from database import get_cached_connection
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        # If no search term and no category filter, return all blogs
        if not search_term and category == 'all':
            logger.info("GET /api/blogs/search - No search term, returning all blogs")
            return get_all_blogs()
        
        # Build the query based on search criteria
        if category == 'all':
            query = """
            SELECT 
                p.*, 
                a.name as author_name, 
                a.avatar as author_avatar, 
                a.bio as author_bio,
                a.twitter as author_twitter,
                a.linkedin as author_linkedin,
                COUNT(c.id) as comments
            FROM blog_posts p
            LEFT JOIN blog_authors a ON p.author_id = a.id
            LEFT JOIN blog_comments c ON p.id = c.post_id
            LEFT JOIN blog_post_tags pt ON p.id = pt.post_id
            LEFT JOIN blog_tags t ON pt.tag_id = t.id
            WHERE (LOWER(p.title) LIKE %s OR LOWER(p.excerpt) LIKE %s OR LOWER(t.name) LIKE %s)
            GROUP BY p.id
            ORDER BY p.created_at DESC
            """
            search_param = "%{}%".format(search_term)
            cursor.execute(query, (search_param, search_param, search_param))
        else:
            query = """
            SELECT 
                p.*, 
                a.name as author_name, 
                a.avatar as author_avatar, 
                a.bio as author_bio,
                a.twitter as author_twitter,
                a.linkedin as author_linkedin,
                COUNT(c.id) as comments
            FROM blog_posts p
            LEFT JOIN blog_authors a ON p.author_id = a.id
            LEFT JOIN blog_comments c ON p.id = c.post_id
            LEFT JOIN blog_post_tags pt ON p.id = pt.post_id
            LEFT JOIN blog_tags t ON pt.tag_id = t.id
            WHERE p.category = %s AND (LOWER(p.title) LIKE %s OR LOWER(p.excerpt) LIKE %s OR LOWER(t.name) LIKE %s)
            GROUP BY p.id
            ORDER BY p.created_at DESC
            """
            search_param = "%{}%".format(search_term)
            cursor.execute(query, (category, search_param, search_param, search_param))
        
        blogs = cursor.fetchall()
        
        # For each blog, fetch its tags and format the data
        for blog in blogs:
            # Format the date properly
            if isinstance(blog.get('date'), datetime):
                blog['date'] = blog['date'].strftime('%B %d, %Y')
                
            # Convert readTime to the expected format
            if blog.get('read_time'):
                blog['readTime'] = blog.pop('read_time')
                
            # Build the author object
            author = {
                "name": blog.pop("author_name"),
                "avatar": blog.pop("author_avatar", "/images/mentors/default.jpg"),
                "bio": blog.pop("author_bio", ""),
                "social": {
                    "twitter": blog.pop("author_twitter", "https://twitter.com"),
                    "linkedin": blog.pop("author_linkedin", "https://linkedin.com")
                }
            }
            blog["author"] = author
            
            # Fetch tags for this blog post
            tag_query = """
            SELECT t.name FROM blog_tags t
            JOIN blog_post_tags pt ON t.id = pt.tag_id
            WHERE pt.post_id = %s
            """
            cursor.execute(tag_query, (blog["id"],))
            tags = [row["name"] for row in cursor.fetchall()]
            blog["tags"] = tags
        
        cursor.close()
        conn.close()
        
        # Log successful response
        logger.info(f"GET /api/blogs/search - Success: {len(blogs)} blogs found for query '{search_term}' in category '{category}'")
        
        # Return raw data - let the @encrypt_response decorator handle Response creation
        return format_api_response(blogs)
    except Exception as e:
        logger.error("Error searching blog posts: {}".format(str(e)))
        logger.error(f"GET /api/blogs/search - Error: {str(e)}, IP: {request.remote_addr}")
        return create_error_response(str(e), status_code=500, encrypt=True)


@blog_bp.route('/api/blogs/categories', methods=['GET'])
@require_api_key
@encrypt_response
def get_categories():
    """Get all blog categories from database"""
    try:
        # Log the request
        logger.info(f"GET /api/blogs/categories - IP: {request.remote_addr}")
        
        # Create database connection directly
        from database import get_cached_connection
        conn = get_cached_connection()
        cursor = None
        try:
            cursor = conn.cursor(dictionary=True)
            
            # Query to fetch all categories
            query = "SELECT id, name FROM blog_categories ORDER BY name"
            cursor.execute(query)
            categories = cursor.fetchall()
        finally:
            # Always clean up resources
            if cursor and hasattr(cursor, 'close'):
                cursor.close()
            if conn and hasattr(conn, 'close'):
                conn.close()
        
        # Log successful response
        logger.info(f"GET /api/blogs/categories - Success: {len(categories)} categories returned")
        
        # Return raw data - let the @encrypt_response decorator handle Response creation
        return format_api_response(categories)
    except Exception as e:
        logger.error("Error fetching blog categories: {}".format(str(e)))
        logger.error(f"GET /api/blogs/categories - Error: {str(e)}, IP: {request.remote_addr}")
        return create_error_response(str(e), status_code=500, encrypt=True)


@blog_bp.route('/api/blogs/tags', methods=['GET'])
# @require_api_key
@encrypt_response
def get_popular_tags():
    """Get popular tags with counts from database"""
    try:
        # Get limit parameter
        limit = request.args.get('limit', default=10, type=int)
        
        # Log the request
        logger.info(f"GET /api/blogs/tags - limit: {limit}, IP: {request.remote_addr}")
        
        # Create database connection
        from database import get_cached_connection
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        # Query to get tags with counts of associated posts, ordered by count
        query = """
        SELECT t.name, COUNT(pt.post_id) as count
        FROM blog_tags t
        JOIN blog_post_tags pt ON t.id = pt.tag_id
        GROUP BY t.id
        ORDER BY count DESC
        LIMIT %s
        """
        cursor.execute(query, (limit,))
        popular_tags = cursor.fetchall()
        
        cursor.close()
        conn.close()
        
        # Log successful response
        logger.info(f"GET /api/blogs/tags - Success: {len(popular_tags)} tags returned")
        
        # Return raw data - let the @encrypt_response decorator handle Response creation
        return format_api_response(popular_tags)
    except Exception as e:
        logger.error("Error fetching popular tags: {}".format(str(e)))
        logger.error(f"GET /api/blogs/tags - Error: {str(e)}, IP: {request.remote_addr}")
        return create_error_response(str(e), encrypt=True)


@blog_bp.route('/api/blogs/trending', methods=['GET'])
@require_api_key
@encrypt_response
def get_trending_posts():
    """Get trending blog posts based on views from database"""
    try:
        # Get limit parameter
        limit = request.args.get('limit', default=3, type=int)
        
        # Log the request
        logger.info(f"GET /api/blogs/trending - limit: {limit}, IP: {request.remote_addr}")
        
        # Create database connection
        from database import get_cached_connection
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        # Query to get trending posts sorted by views
        query = """
        SELECT 
            p.*, 
            a.name as author_name, 
            a.avatar as author_avatar, 
            a.bio as author_bio,
            a.twitter as author_twitter,
            a.linkedin as author_linkedin,
            COUNT(c.id) as comments
        FROM blog_posts p
        LEFT JOIN blog_authors a ON p.author_id = a.id
        LEFT JOIN blog_comments c ON p.id = c.post_id
        GROUP BY p.id
        ORDER BY p.views DESC
        LIMIT %s
        """
        cursor.execute(query, (limit,))
        trending_posts = cursor.fetchall()
        
        # For each post, fetch its tags and format the data
        for post in trending_posts:
            # Format the date properly
            if isinstance(post.get('date'), datetime):
                post['date'] = post['date'].strftime('%B %d, %Y')
                
            # Convert readTime to the expected format
            if post.get('read_time'):
                post['readTime'] = post.pop('read_time')
                
            # Build the author object
            author = {
                "name": post.pop("author_name"),
                "avatar": post.pop("author_avatar", "/images/mentors/default.jpg"),
                "bio": post.pop("author_bio", ""),
                "social": {
                    "twitter": post.pop("author_twitter", "https://twitter.com"),
                    "linkedin": post.pop("author_linkedin", "https://linkedin.com")
                }
            }
            post["author"] = author
            
            # Fetch tags for this post
            tag_query = """
            SELECT t.name FROM blog_tags t
            JOIN blog_post_tags pt ON t.id = pt.tag_id
            WHERE pt.post_id = %s
            """
            cursor.execute(tag_query, (post["id"],))
            tags = [row["name"] for row in cursor.fetchall()]
            post["tags"] = tags
        
        cursor.close()
        conn.close()
        
        # Log successful response
        logger.info(f"GET /api/blogs/trending - Success: {len(trending_posts)} trending posts returned")
        
        # Return raw data - let the @encrypt_response decorator handle Response creation
        return format_api_response(trending_posts)
    except Exception as e:
        logger.error("Error fetching trending posts: {}".format(str(e)))
        logger.error(f"GET /api/blogs/trending - Error: {str(e)}, IP: {request.remote_addr}")
        # Use our standardized error response helper
        return create_error_response(str(e), status_code=500, encrypt=True)


@blog_bp.route('/api/blogs/<post_id>/track-view', methods=['POST', 'OPTIONS'])
@require_api_key
def track_blog_view(post_id):
    # Handle OPTIONS request for CORS preflight
    if request.method == 'OPTIONS':
        print("Received OPTIONS request for tracking endpoint")
        logger.info(f"Received OPTIONS request for tracking endpoint: {post_id}")
        return jsonify({'success': True}), 200, {
            'Access-Control-Allow-Origin': '*',
            'Access-Control-Allow-Methods': 'POST, OPTIONS',
            'Access-Control-Allow-Headers': 'Content-Type'
        }
    """Track a view for a blog post"""
    try:
        logger.info(f"Received view tracking request for post ID: {post_id}")
        print(f"Tracking view for post: {post_id}, type: {type(post_id)}")
        
        # Convert post_id to integer if possible
        try:
            post_id = int(post_id)
            logger.info(f"Converted post_id to integer: {post_id}")
        except ValueError:
            logger.error(f"Invalid post ID format: {post_id}")
            return jsonify({'success': False, 'message': 'Invalid post ID format'}), 400
            
        # Create database connection
        from database import get_cached_connection
        conn = get_cached_connection()
        cursor = conn.cursor()
        logger.info("Database connection established")
        
        # Check if post exists
        cursor.execute("SELECT id, title, views FROM blog_posts WHERE id = %s", (post_id,))
        post = cursor.fetchone()
        
        if not post:
            cursor.close()
            conn.close()
            logger.warning(f"Attempted to track view for non-existent post ID: {post_id}")
            return create_error_response('Post not found', status_code=404)
        
        logger.info(f"Found post: ID={post[0]}, current views={post[2]}")
        print(f"Post found with ID {post_id}, current views: {post[2]}")
        
        # Increment view count
        update_query = "UPDATE blog_posts SET views = views + 1 WHERE id = %s"
        rows_affected = cursor.execute(update_query, (post_id,))
        conn.commit()
        logger.info(f"Updated view count, rows affected: {rows_affected}")
        
        # Verify the update
        cursor.execute("SELECT views FROM blog_posts WHERE id = %s", (post_id,))
        updated_views = cursor.fetchone()[0]
        logger.info(f"New view count for post {post_id}: {updated_views}")
        print(f"Updated view count for post {post_id}: {updated_views}")
        
        # Log the view for analytics
        ip_address = request.remote_addr
        user_agent = request.headers.get('User-Agent', 'Unknown')
        
        logger.info(f"Logging view details: IP={ip_address}, Agent={user_agent[:30]}...")
        
        log_query = """
        INSERT INTO blog_view_logs (post_id, ip_address, user_agent, view_date) 
        VALUES (%s, %s, %s, NOW())
        """
        
        try:
            # Try to log the view, but don't fail if this part doesn't work
            cursor.execute(log_query, (post_id, ip_address, user_agent))
            log_id = cursor.lastrowid
            conn.commit()
            logger.info(f"View logged successfully with ID: {log_id}")
        except Exception as log_error:
            # Just log the error but continue with success response
            logger.error(f"Could not log view details: {str(log_error)}")
            conn.rollback()
        
        cursor.close()
        conn.close()
        logger.info("View tracking completed successfully")
        
        return format_api_response({'message': 'View tracked successfully', 'new_view_count': updated_views})
    except Exception as e:
        logger.error("Error tracking post view: {}".format(str(e)))
        print(f"Error tracking view: {str(e)}")
        return create_error_response(str(e), status_code=500)


@blog_bp.route('/api/blogs/<string:blog_id>/comments', methods=['GET', 'POST'])
@require_api_key
#@encrypt_response
def blog_comments(blog_id):
    # Try to convert ID to integer if possible
    numeric_id = None
    try:
        numeric_id = int(blog_id)
        # If conversion succeeds, use the numeric ID
        blog_id = numeric_id
    except ValueError:
        # Keep using string ID if conversion fails
        pass
    """Get or add comments for a blog post from database"""
    try:
        # Create database connection
        from database import get_cached_connection
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        # First check if the blog post exists
        cursor.execute("SELECT id FROM blog_posts WHERE id = %s", (blog_id,))
        if not cursor.fetchone():
            cursor.close()
            conn.close()
            return create_error_response("Blog with ID {} not found".format(blog_id), status_code=404)
        
        # GET method: Return comments for the blog post
        if request.method == 'GET':
            query = """
            SELECT id, author, email, content, created_at 
            FROM blog_comments 
            WHERE post_id = %s 
            ORDER BY created_at DESC
            """
            cursor.execute(query, (blog_id,))
            comments = cursor.fetchall()
            
            # Format dates in comments
            for comment in comments:
                if isinstance(comment.get('created_at'), datetime):
                    comment['date'] = comment.pop('created_at').strftime("%B %d, %Y")
            
            cursor.close()
            conn.close()
            return format_api_response(comments)
        
        # POST method: Add a new comment to the blog post
        # Expect JSON body with {author, email, content}
        data = request.get_json()
        
        # Basic validation
        if not data or not all(key in data for key in ['author', 'email', 'content']):
            cursor.close()
            conn.close()
            return create_error_response("Missing required fields", status_code=400)
        
        # Insert the new comment into the database
        insert_query = """
        INSERT INTO blog_comments (post_id, author, email, content) 
        VALUES (%s, %s, %s, %s)
        """
        cursor.execute(
            insert_query, 
            (blog_id, data['author'], data['email'], data['content'])
        )
        conn.commit()
        
        # Get the newly created comment
        new_comment_id = cursor.lastrowid
        cursor.execute(
            "SELECT id, author, email, content, created_at FROM blog_comments WHERE id = %s", 
            (new_comment_id,)
        )
        new_comment = cursor.fetchone()
        
        # Format the date
        if new_comment and isinstance(new_comment.get('created_at'), datetime):
            new_comment['date'] = new_comment.pop('created_at').strftime("%B %d, %Y")
        
        cursor.close()
        conn.close()
        
        return format_api_response(new_comment)
    except Exception as e:
        logger.error("Error handling comments for blog {}: {}".format(blog_id, str(e)))
        return create_error_response(str(e), status_code=500)


@blog_bp.route('/api/blogs/<string:blog_id>/like', methods=['POST'])
@require_api_key
#@encrypt_response
def like_blog(blog_id):
    # Try to convert ID to integer if possible
    numeric_id = None
    try:
        numeric_id = int(blog_id)
        # If conversion succeeds, use the numeric ID
        blog_id = numeric_id
    except ValueError:
        # Keep using string ID if conversion fails
        pass
    """Increment the like count for a blog post in database"""
    try:
        # Create database connection
        from database import get_cached_connection
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        # First check if the blog post exists
        cursor.execute("SELECT id, likes FROM blog_posts WHERE id = %s", (blog_id,))
        blog = cursor.fetchone()
        
        if not blog:
            cursor.close()
            conn.close()
            return create_error_response("Blog with ID {} not found".format(blog_id), status_code=404)
        
        # Increment like count in the database
        cursor.execute("UPDATE blog_posts SET likes = likes + 1 WHERE id = %s", (blog_id,))
        conn.commit()
        
        # Get the updated like count
        cursor.execute("SELECT likes FROM blog_posts WHERE id = %s", (blog_id,))
        updated_blog = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        return format_api_response({"likes": updated_blog["likes"]})
    except Exception as e:
        logger.error("Error liking blog post {}: {}".format(blog_id, str(e)))
        return create_error_response(str(e), status_code=500)


@blog_bp.route('/api/subscribe', methods=['POST'])
@require_api_key
def subscribe_newsletter():
    """Subscribe to the newsletter using database"""
    try:
        data = request.get_json()
        email = data.get('email')
        
        if not email:
            return create_error_response("Email is required", status_code=400)
        
        # Create database connection
        from database import get_cached_connection
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        # Check if already subscribed
        cursor.execute("SELECT id FROM blog_subscribers WHERE email = %s", (email,))
        if cursor.fetchone():
            cursor.close()
            conn.close()
            return format_api_response({"message": "Already subscribed"})
        
        # Add new subscriber
        cursor.execute("INSERT INTO blog_subscribers (email) VALUES (%s)", (email,))
        conn.commit()
        
        cursor.close()
        conn.close()
        
        return format_api_response({"message": "Subscribed successfully"})
    except Exception as e:
        logger.error("Error subscribing to newsletter: {}".format(str(e)))
        return create_error_response(str(e), status_code=500)